SQL option for CPDL

[laurent_tkn]

Hello CPDL community and admins!

This is my first post here. I have been visiting CPDL for over a decade.
There are advanced search options for composers and multiple selection search for sheet music, which are helpful if you know what you are looking for specifically.

What would be really interesting for me as member of the music commission in my choir, is a way to directly search the database via SQL. This would allow connecting a lot of data together and get results for works you are not specifically looking for. The result is a table that allows further inspection.

For example, the choir wants to create a concert program with music from German composers from the 18th century only, with a length of 50 minutes per piece maximum, with the option to either have an orchestral accompaniment or organ only. In what I have tried so far, it is only possible to search in general terms or on names, and then you get results that point to a composer or a piece. In SQL however, it is possible to directly browse the database without knowing the values (like composers name, or name of piece).

I have already found a post about using an API, but the connection to advanced search options is not directly clear to me and it seems to target the developers on CDPL as a Wiki itself: https://forums.cpdl.org/phpBB3/viewtopi ... sql#p29398

Is there a possibility to make such a SQL solution available or does this already exist (in the backlog)?
Many thanks for helping me out.

[choralia]

The CPDL ChoralWiki makes use of the MediaWiki software, i.e., the same software used by Wikipedia and by many other wiki websites. The MediaWiki software is actually conceived to manage a collaborative encyclopedia, not a music library, and therefore the data structure underneath is not natively searchable for items pursuant to the characteristics of music. You can find information about the MediaWiki database structure here.

In order to search for music-related items, CPDL administrators have made extensive use of categories. General information about categories in MediaWiki can be found here. Many categories and subcategories have been defined in ChoralWiki so far, however those that are probably the most important ones are listed here.

So, searching for music that meets certain requirements implies that one has to identify the categories that correspond to the requirements, and then identify the work pages that fall in all the categories selected. A basic tool to perform searches in categories is the Multi-Category Search. A much more powerful tool is the use of Dynamic Page List (DPL) queries. The MediaWiki API is also a very powerful tool.

Behind the scenes we have a database where information regarding compositions, editions associated to each composition, and files associated to each edition are stored. It is not completely accurate because it fetches the information from the text content of the pages, and if the text content of the page is not reasonably close to the standard template that is used on most (but not on all) pages, the significant music data may not be correctly captured. The following data are available:

• For each composition: title, composer name, lyricist, voicing, genre, language, instruments, publication dates, number of editions available;
• For each edition: CPDL edition #, title, composer name, editor/contributor name, submission/publication date, edition notes;
• For each file: filename, CPDL edition #, file location (internal/external), file type, file icon, file size, copyright, page format, number of pages.

A dump of this database can be provided upon request, and the use will be limited to specific purposes that do not conflict with CPDL policies, especially regarding personal information and copyright.

Max

[laurent_tkn]

Thank you very much for the elaborate reply. I will investigate the options you have named.