MediaSQL - a fast media library-plugin which easily manages a huge amount of data

[MrKiss]

Hi,
do you know this problem? You got a lot of mp3 files (like 50.000 or more files) but the Winamp media library is not able to manage this amount of data!
Heres the solution:
This little plugin uses a MySQL Database to manage your files.
The only thing you have to do is setting up a MySQL server (visit mysql.com), the rest (creating database/table, adding entries, searching, deleting...) will be done by this plugin.
You can search for files in the way you do it with the Winamp media library and easily send them or drag'n'drop them to the playlist.

apart from that the program offers:

• you can choose whether to look for music or videos
• explore item folder
• delete non-existing files from database
• if you want a live-search: you dont have to press "go", you see the results while typing
• you can decide in which id3-tags you want to look for the specified keyword (look in all id3 tags is standard configuration)
• results are sorted by filename/album/tracks

what i want to implement:

• search history
• support for every file type
• sticky window
• list albums/artists etc...
• update function

known bugs:

• sometimes problems with connecting, will be solved soon
• it happened one time that winamp crashed - i dont know why

install notes:

• set up an mysql server (if you dont have already)
• put the gen_mediasql.dll file in your plugins directory (e.g. C:\Program Files\Winamp\Plugins)
• you need the libmysql.dll file, you can download it from here: h*tp://www.dll-files.com/dllindex/dll-files.shtml?libmysql . Put it in your winamp directory (e.g. C:\Program Files\Winamp) - not the plugin directory!
• you also need the id3lib.dll file, you can download it from here: h*tp://www.dll-files.com/dllindex/dll-files.shtml?id3lib . It also comes in your winamp directory.
• the MediaSQLLangEn.dll file also comes in your winamp directory.

Maybe you could tell me a webspace where I could upload all files in a zip...

If you want to have the sourcecode just write an email to christiankiss at gmx.de. But attention: it is a really bad mixture of C and C++ code in a VC++6 project .
This is my first plugin so dont hesitate to give tipps for improvements!

Greetings,
MrKiss

[MrKiss]

Hi guys,
i was hoping for a little feedback but i think that setting up a MySQL server is too much work when testing a plugin.
So I decided to use SQLite, a small library which doesnt need a SQL server or other installations.
Im working on it and will publish a new version when its done .

Greetings,
Chris

[CraigF]

Be very interested to see how sqlite fairs. The original intention was to change winamp's NDE over to sqlite, but it has so far not shown to be beneficially any faster with the types of queries being done in winamp internally.

[MrKiss]

Originally posted by CraigF
Be very interested to see how sqlite fairs. The original intention was to change winamp's NDE over to sqlite, but it has so far not shown to be beneficially any faster with the types of queries being done in winamp internally.

Hm thats strange. Using MySQL a search query using 50.000 mp3 files and searching in filepath/filename/title/album/artist/genre requires like 0.1 seconds (when we have like 20 search results) on my PC, which isnt the newest. The winamp media library needs like 7 seconds going through 50.000 mp3s!
When having a look at the speed comparison table on sqlite.org (h*tp://sqlite.org/speed.html) SQLite is even faster than MySQL.
But youre right, my own testing results show a pretty bad performance which is even slower than winamps .

I'm now trying to improve my program with sqlite. E.g., do "synchronious" off or use FTS (full text search, see h*tp://www.sqlite.org/cvstrac/wiki?p=FullTextIndex ).

Greetings,
Chris

[CraigF]

I think its not so much the speed of the engines, understandably, NDE may be slower than these older and more refined specialized products, but rather the way that people utilize the database makes the database design an issue which cannot take advantage of speedup mechanisms such as indexes etc.

Anyway, I still think there is some merit to exploring the sqlite path. Winamp itself already comes with sqlite compiled in, so it may be that a viable media library plugin could utilise the built in wasabi service or api. But benski would know more about that than I.

[DrO]

i'd heard talk of dropping the sqlite stuff altogether (is only used for the local cddb data) seeing as there were the other implementation improvements regarding nde and just using that outright as needed for what is required with the core winamp parts - since that's the main point of dev concentration. but is all just speculation. really, with the few recent changes to the nde support, there's really little reason to not keep using it since it's working effectively (especially with the unicode stuff which was already there, just never enabled) but that's just my opinion

-daz

[CraigF]

well, there are other benefits to using an sql based engine as opposed to NDE, structured query language for one, it would be capable of whatever people wanted to do, unlike NDE's language right now.

[MrKiss]

Hi,
I now implemented SQLite.
You can download the package from here (~300 kbyte) and you dont need any further downloads/installations:
h*tp://rapidshare.com/files/19525505/MediaSQL.zip.html
[edit] a little change - use this link:
h*tp://rapidshare.com/files/19533513/MediaSQL.zip.html
gen_mediasql.dll comes in your plugins directory, the rest in your winamp directory.
But: Because of putting everything into classes not all Buttons are bound to a function yet. But the "core" works so far.

@CraigF: As soon as I turned the "synchronious" parameter in SQLite off the performance was increased by 100 times. I think now it is as fast as MySQL.
You say that the main reason of the media librarys lower speed is not based on the database engine ... but wheres the bottleneck then?
Im not that familiar with the winamp system, so it is good possible that heres a missunderstanding.

Greetings,
Chris

[CraigF]

Just to repost, the urls from above are as follows:

http://rapidshare.com/files/19525505/MediaSQL.zip.html
http://rapidshare.com/files/19533513/MediaSQL.zip.html

[Benski]

From our own experiments, we found SQLite to initially be much faster.

However, once we implemented the following
1) proper international, case-insensitive searching
2) searched all the fields (filename, title, artist, album, genre, albumartist, publisher, composer)
3) storing the results in a data structure for use in a virtual listview (as opposed to just displaying them sequentially)
4) sorting by multiple fields

the speed advantage quickly went away. Although it's still faster than NDE for large datasets (>40,000 tracks, give or take)

[MrKiss]

Originally posted by Benski
the speed advantage quickly went away. Although it's still faster than NDE for large datasets (>40,000 tracks, give or take)

Hm i cant agree with that.
The SQL query the MediaSQL plugin does looks like following when we search for "foo bar" (pseudocode):
----------------
SELECT FROM media WHERE
(filename LIKE '%foo%' OR title LIKE '%foo%' OR album LIKE '%foo%' OR artist LIKE '%foo%' OR genre LIKE '%foo%')
AND
(filename LIKE '%bar%' OR title LIKE '%bar%' OR album LIKE '%bar%' OR artist LIKE '%bar%' OR genre LIKE '%bar%')
ORDER BY filename, album, track
----------------

(im not so good at SQL, isnt there a way doing WHERE (filename or title or album or artist or genre) LIKE '%foo%' ? Or is this optimized by the SQL interpreter so that I dont have to worry about it?)

The results are stored in an array of char* pointers (this is done by sqlite3_get_table()) and the OwnerData-ListView control gets them with an OnDispInfo-Message.
As far as I can see it is pretty international ... the German characters äöüß work fine. Contrary to that, my friend claims that the Winamp Media Library has problems with strings like "Die Ärzte" (a German band), but I didnt make my own experiences. But I think that this code will not be able to handle non-latin alphabets (russian, japanese/chinese etc...), I should use things like Unicode.

Apart from searching in albumartist/publisher/composer this code snippet fulfills all your demands and still doesnt slow noticeable down on my PC.

Greetings,
MrKiss

[neFAST]

Hmmm it may sound stupid but I can't understand how to use your plugin just after installing it
Where do I find my new library with that "sql flavour" ?

EDIT : By the way I'm interested in your source code. I don't have much time but I'd like to help you a little. The crappy handling of big libraries is the major flaw of winamp IMHO and that's why I tried foobar, Itunes ... (and reverted back to winamp ).

[MrKiss]

Hi,
when testing this plugin on a modern skin the plugin window is not visible .
I fixed this problem and a few other buggs. But unfortunately Winamp now causes an error on closing when using a modern skin?! I dont understand that .

Heres the new gen_mediasql.dll:
h*tp://rapidshare.com/files/20799247/gen_mediasql.dll.html

When doing a rightclick on an item the popup menu is still in German because the language strings arent loaded from the Language-Resource-Dll-file, I'll fix this but currently I dont have the time.

Heres the sourcecode:
h*tp://rapidshare.com/files/20800017/MediaSQL.rar.html

As I said: MediaSQLLangEn.dll, id3lib.dll, sqlite3.dll comes in your Winamp directory, gen_mediasql.dll in your plugins directory. I hope your problem was because of this modern skin issue .

[neFAST]

Yes, it was a modern skin problem.
I'll test it tonight with my library. I'm starting a todo list (column choice, column order, letter selection with keyboard, "smart" view, ...)

I wonder why your plugin is not much popular!? I guess there are a lot of users who can't stand waiting for their library to refresh.

[CraigF]

I'm a bit perplexed as to why you require id3lib as part of your package. Are there not appropriate api's within Winamp to acquire tag data?

[DrO]

IPC_GET_EXTENDED_FILE_INFO and there's the W variant as well

-daz

[MrKiss]

Originally posted by CraigF
I'm a bit perplexed as to why you require id3lib as part of your package. Are there not appropriate api's within Winamp to acquire tag data?

Lol yes youre right - i didnt know that.
I'll fix this right now

[MrKiss]

Hi guys,
now the id3lib.dll is no longer needed, the plugin now gets the data using Winamp (IPC_GET_EXTENDED_FILE_INFO).
The changed sourcecode:
h*tp://rapidshare.com/files/20842294/MediaSQL.rar.html
the new plugin-dll:
h*tp://rapidshare.com/files/20842472/gen_mediasql.dll.html

To those who downloaded the sourcecode from my previous posting: This new code package now doesnt contain unused/old cpp-files which could confuse you . I recommend using this new package.
@neFAST: do you have s.th. like ICQ?

Greetings,
MrKiss

[neFAST]

Yep, check my profile. But my internet connection (I mean, my neighbourg's one) is really bad.

[MrKiss]

ok
I just fixed a bug which causes a crash when doing a live-search after another while selecting items.
h*tp://rapidshare.com/files/20846751/gen_mediasql.dll.html
source (changes only in CMainWnd.cpp):
h*tp://rapidshare.com/files/20847029/MediaSQL.rar.html

What I want to do next:
- Write file-type flags (or s.th. like that) into the SQL-library so that we e.g. can search for music files only without having to filter them AFTER doing the SQL query.
- add an abort button/routine while adding or deleting files

[DrO]

just as a quick point, you might want to look into unicode support sooner rather than later (seems to be a thing a lot of people would want if you're going for a media-library style of thing)

-daz

[jtbse]

Originally posted by neFAST
I wonder why your plugin is not much popular!? I guess there are a lot of users who can't stand waiting for their library to refresh.

Well there's at least one non-coding, dirty, stinking lurker who's very interested

Just paying attention to progress for the time being and waiting for things to get a little more stable.

Also trying to figure out if there might be a way that I can script SQL Lite's command line to keep a Winamp library synched up with my mt-daapd server....hmmm

[MrKiss]

Hi, I'm trying to implement Unicode but I'have got a problem with IPC_GET_EXTENDED_FILE_INFO: The W variant (IPC_GET_EXTENDED_FILE_INFOW) is not declared in one of my SDK files, nor which structure to pass as WPARAM.

Could s.o. help me?

Greetings

[DrO]

make sure you've got the lastest version of the sdk (id is 3026 and is just a char* to wchar_t* as needed if you can't see it)

-daz

[Lord Darius]

Hi MrKiss,

I've downloaded your code, and had a quick look at it (CDatabase.cpp).

I have noticed that all of the queries you use are built at call-time, and that you mostly use the "sqlite3_exec" function to execute those queries.
I just thought I could point out the fact that this can be a drain on performance, as queries have to be compiled each time they're executed.

Of course, in the case of "dynamic" queries, where search conditions are added on the fly, there's no choice, the query has to be constructed.

But when the only thing that has to be interpolated in a query is data, like in the "DoesEntryExist" and "AddEntry" methods, you should use prepared statements and parameter binding, and then use "sqlite3_step", which is way faster than "sqlite3_exec", especially as you don't expect several rows to be returned.
"sqlite3_prepare_v2" is the function that pre-compiles a statement which be a big performance boost if the query is called many times.

ex:

code:
sqlite3_prepare_v2(m_db, "SELECT count(*) FROM " DB_TABLE_NAME " WHERE file = ?", -1, &pStmt, &pzTail);

// ...

sqlite3_bind_text(pStmt, 1, pszFileName, strlen(pszFileName), SQLITE_STATIC);



sqlite3_step(pStmt);



int iNumExisting = sqlite3_column_int(pStmt, 0);



sqlite3_reset(pStmt);

It can also be noted that this approach lets sqlite escape the parameters itself, so the probability of having sql injections is null.

Another thing is that it would allow arbitrarily big data size in the insertion query, and not a total of 2000 characters as it is the case now

[MrKiss]

DrO: thx, I'll try it with that. I downloaded the SDK from this web page (development), but there isnt a file where the W version is declared ?!
Lord Darius: cool, i didnt know this, im not that familiar with sql, but i`ll focus on this after this unicode thing. But i really fear that doing a sql-query-built like i do only makes 0.5% of the performance. Compare: The whole sql thing (including getting all results) is much faster than the listview-output!

Greets

[Benski]

Originally posted by MrKiss
DrO: thx, I'll try it with that. I downloaded the SDK from this web page (development), but there isnt a file where the W version is declared ?!

http://forums.winamp.com/showthread....hreadid=252090

[Lord Darius]

Originally posted by MrKiss
Lord Darius: cool, i didnt know this, im not that familiar with sql, but i`ll focus on this after this unicode thing. But i really fear that doing a sql-query-built like i do only makes 0.5% of the performance. Compare: The whole sql thing (including getting all results) is much faster than the listview-output!

Yes of course, sqlite3 is fairly fast even by using the high-level functions.

It's just that I have never really liked building SQL queries by hand, because of escaping problems, injection issues, memory management (especially in C/C++), etc...

Prepared statements come in handy because they leave that business to the database engine.

[neFAST]

Yop
I'm sorry my job takes to much time to allow me to code at home.
Do you plan to release a new beta ?

[MrKiss]

Originally posted by neFAST
Yop
I'm sorry my job takes to much time to allow me to code at home.
Do you plan to release a new beta ?

Yeah I'm working on it , but I currently dont have much time, too ^^.

[bitcore]

Hmm, this is interesting.
I've written a massive web media library with a user system that I use for myself and friends on my college network, I've got it running a boatload of music and movies via http streaming (SMB ports are blocked at each switch port on our network) and this project caught my eye. I'm using GetID3 (well, an extremely old & hacked apart version, mid 2005) to aggregate the data from my files and generate an SQL database to work off of. (5 years ago, I was running off of file names and directory structure, igck) You might want to check that project out and examine how they have their tables and queries structured - they've gone though lots of revisions on every aspect of the project for many different reasons. It'd be really slick if these two projects could be written to be compatible with each other from the get-go, (IE: capable of using the same database) rather than not. just wanted to point getid3 out so you wouldn't notice it way down the line and be faced with the decision of either ignoring each other's project, or spending lots of time and headaches making converters or merging to one DB structure or another. (development has been a little stagnant but it's been honed quite a lot over the years. I suggest you download a copy, fire up a webserver and play with it for a while - it's very fast, reads and writes mp3 ID3 tags, etc.)


Also, just a few tips from someone who's crafted a media library from scratch, if you can get the database software to do something that you already know how to do in C, it WILL be faster if you let the database do it before it ships out the data to your code.
Secondly, using fulltext searches and creating the correct key indexes for the tables is very important with regards to speed for any database access.
Thirdly, from the beginning, instead of having to modify the database structure and require members to run table modifications later and keep track of database structure 'versions' as this plugin matures, settle on a very expandable and thorough database structure VERY SOON so future code revisions will not require the erasure of old structures to accommodate a different methodology.

Using a dedicated database can be much quicker than rolling your own data structure, you have other things to code and worry about, whereas the database programmers only worry about making the database reliable and fast. It's quite clear how fast relational databases can be, take a look at this very forum system, deviant art, facebook, they deal with massive datasets and are very capable of handling hundreds of requests per second and obviously do this on a daily basis. (yes the dedicated & expensive hardware helps, but when you get down to it, you still are getting extremely quick response times with these massive datasets - and I'm not exactly comparing apples to oranges or a 486 to a 64 blade server)

I'd love to see where this goes.
I hope you get running with fulltext searches soon, you will love the results you get with them (you can do some very interesting things with them too, and pre-sort by 'relevance' of the search terms)

*(Subscribed)

[CraigF]

bitcore, very interesting. I was planning on developing something similar to what you have, and was hoping that an sql backend within winamp would allow me to shortcut, or at least auto-manage the interface between friends and family and myself in some way. Either have me system auto-sync my winamp library, pump uploaded data into my library, etc etc.

Is your software available anywhere?

[MrKiss]

Hi,
today I want to release a new beta version. I would be happy if s.o. could test it a little and tell me bugs he found.
New features:
- full unicode support (winamp 5.33 needed!)
- list video/album now works fine.
- 3 tabs: files, albums and artists. the album and artist register displays every album/artist and the number of entries in it. you can also sort by the number of entries. double clicking on an album/artist item will show you the files connected with this item.
- you can now sort by title/album/artist etc by clicking on the list view column header. Note: first come the alphabetical values, then empty values, number, brackets and other characters...
- you can drag and drop files to the explorer, they will be copied to this location (Note: this doesnt work when dropping into the same hard drive because the explorer then wants to delete the original file). you can also drop files or folders into the MediaSQL window, then they will be added to the media list. you can also drag and drop files from the media list to other applications (like VLC).
- some minor changes

If you used the old beta:
You have to delete our old database file (mediasql.db) and add your files again (because of unicode and one more column in the database). Apart from that you have to update your sqlite3.dll and your language file.

The unicode version:
h*tp://rapidshare.com/files/24348701/MediaSQLUnicode.zip.html
ANSI version:
h*tp://rapidshare.com/files/24348782/MediaSQLANSI.zip.html
(both packages include the needed language dll and sqlite3 dll file.

sourcecode:
h*tp://rapidshare.com/files/24349170/MediaSQLProject.zip.html

You have to put the MediaSQLLangEn.dll and sqlite3.dll in your winamp directory, gen_mediasql.dll in your plugins directory

To all coders: dont hesitate to help this project go on, i unfortunately cant spend much time on it.

@bitcore: this sounds very interesting, but like aforementioned - for me coding is only a hobby and I dont have the time doing that stuff .
What I maybe want to implement next is, like you said, a full text search, I currently even dont know what this exactly is .
Maybe it is only a feeling but I think that the Unicode version is slower concerning sorting the items. So if you dont need Unicode, maybe youre happy with the ANSI version.
If you want to compile the project file, you just have to add or delete the line #define UNICODE in GlobalDef.h .


Well, to all those who think my plugin is useful for them - have fun with it .

Greetings,
MrKiss

[foxyshadis]

I'm interested in media library tools, always. I'm even more interested in open source. So I wanted to ask, have you considered setting up shop on sourceforge? I can't guarantee that I could contribute, but it has the benefit of the latest source always being quickly available to anyone who wants to. (You also get not-rapidshare webspace, trackers, forums or mailing lists... not that you necessarily need all of that for a small project, but it's there.) All you need is TortoiseSVN to keep the source sync'd up. I've managed a few projects out of forum threads, but particularly here at winamp, it's hard because the first few posts are so critical, but you quickly lose the access to edit them. And who wants to pester the mods every week? >.>

That's my pitch. Now the plugin. First, I think you should restructure the binary zip a bit; have everything in a winamp folder, and the plugin itself in a plugins folder under that, so dumbasses like me aren't wondering why nothing shows up. (That, or you can change it to also check the plugins folder if not found on the path.)

I hardly have anything, well under 10k songs, and it still took a good 10-20 minutes to parse them all. I wasn't keeping too much track. But I figure, you have a whole media library sitting right there, why bother duplication? Either dump the whole library into yours and scan for duplicate/missing files, or scan for files and pull each individual one's info from the ml (probably the slower method, but lookup by file is enormously faster than its fulltext lookup speed). If I get into the code, that's probably what I'd be interested in doing.

The library is way faster than the ml, I'll hand it that, even given that it's searching less fields. Wow. It actually runs much faster than I can type, compared to lagging behind like the ml (where a delay-search like firefox's is almost critically necessary with the lag), some of the speed might come from the standard listview. Excellent work. SQLite really screams. Thanks for putting it out, at least for testing with.

[MrKiss]

I had to reupload the files because there was a bug (which I thought I already fixed in the files of my last post):
h*tp://rapidshare.com/files/24589729/MediaSQLUnicode.zip.html
h*tp://rapidshare.com/files/24589775/MediaSQLANSI.rar.html

It fixes a problem that when you dont search anything for a long time windows saves the SQLite memory to the hard disk and when then doing a lot of searches in a short time the plugin crashes.

@foxyshadis: thank you very much for testing my plugin!
I didnt consider to set it up on sourceforge, but thats a good idea! I guess its free or?

Hm as far as I know theres no way doing every file in one folder or so ... gen_mediasql.dll has to be in the plugins folder (so that winamp loads it), the other dll files have to be in the winamp folder, because I cant link them dynamically and the winamp folder is the plugins environment folder.
A solution would be doing a little installer or ... i dont know. Is it really that difficult?

I dont understand what you mean with your next part. Do you think that I should make a copy of the files in media library so that the user doesnt have to add the files in my plugin, too? That would be an idea!

Well, Winamps media library is a standard list view control, too and as far as I know it is also a OwnerData listview-control. I should do a little research on how much time I lose where. E.g., on my 1000 mhz cpu having a 4000 files output the preparing of the sqlite search query requires like 0 ms (though using stringstreams ^^^), but deleting the last search result (its a free() call on every string) requires 35 ms, mallocing memory for the new search is like 50 ms and now the funny thing: sorting these items is 150 ms . I hope that theres only little time owned by the list view control. And if not we should think of doing the following: always have only like 30 items in it and doing the scrolling manually, then "scrolling" in the intern item table and not in the list view control. Do you know what I mean?. Well, but SQLite rules .

Greetings!

[bitcore]

Is this project still alive?

[MrKiss]

On thing: ANSI of course has to mean ASCII! ... sry, I couldnt edit it (it was too late).

Originally posted by bitcore
Is this project still alive?

Hi, I'm currently not working on it (job, studies...), but I'll go on with it when i have time.
Do you have an idea I could implement?
I was experimenting a bit with full text search (using the match-operator), but then the programm searches only for full words. I dont have s.th. like %bla% . So this is useless or?
Secondly I implemented a faster sorting by writing indexes after adding files and then sorting after this index.
Apart from that I'm now using 2 tables (one for music and one vor video) so that the programm does not have to check the video flag. These things are only speed improvements, so that I didnt release it.

Greetings!

[dmdm12]

Is this Plugin still downloadable somewhere?Does anyone have a link, or can provide it for me?