Hi,

After having installed the mod foo_playcount by kode54 that uses sqlite.dll to store the playcount information, so no tagging your file, I wonder if someone (musicmusic?) would be interested in implementing the same feature for foo_quicktag. This way the users that want to use it to rate their songs but want this information to be kept outside of their file itself, will have the choice. In the same time, such a implementation will make other tags written by quiktag also out of the file, so at the end, it depends what you want this component to do (really tag the file, or just a database).

Just out of curiousity, how do I get this sqlite thing to work? It doesn't seem to do anything... is there something that I have to set up somewhere or something?

Hmm.. maybe this plugin could be expanded into a more general database component, called foo_sql perhaps? There could be a general framework in place so that other plugins could be modified to use the external database accordingly. OTOMH, foo_skip, foo_quicktag, foo_playcount, are all plugins where having an external db makes sense.

Just a few things that could stand to be added to a foo_sql dealie..

-list of everything in the db - each file path and whatever tags have been associated with it, option to delete/modify certain entries, etc.
-option to merge tag data with db data, configurable with TAGZ
-Automatic notification for missing files - if I move a file outside of Foobar, I'd like to retain the db data associated with it. Maybe an option to search your HD/a certain folder for missing files? This feature's kinda important to me, I'd hate to lose everything because I had to move something outside of Foobar.

1. why do people dont like to save info in the file tag?
2. the sql saves other tags as artist, album, etc. ? could it be useful to other things too? like generated playlist?

1. Some people like to keep their files unchanged, primarily when file sharing. Otherwise, the data will change, resulting in a different hash and requiring people to regenerate any sfvs (or have people stratching their heads as to why the files are getting shared seperately from the rest of the network) Additionally, since ratings are subjective and playcounts vary from user to user, it doesn't make sense to keep this data in the file when it is transferred to another user.

2. Foobar already has an internal database for the storage of data, which does exactly what you're talking about. The problem here is that some of us want to modify the actual tags, but keep certain tags from getting added to the file, which is currently impossible.

That's an excellent idea, hope someone will be interested. Don't know how difficult could it be to make it

Sorry to bump this thread but I find this idea quite interesting and really nice for having out of your files personal info while keeping the ability to use tagging fb2k functions. One of the issues that fb2k brings me is the fact that I can't just use the 'block updates operations' in my files, because if I ever wants to tag my files in fb2k (mainly using masstagger, which is very powerful and I can't find something similar out of fb2k) then I have to unblock and my files will also be tagged with any already 'blocked' tags.

I think that having the possiblity of writing (as mod playcount does) to a sql database is really a good solution, the sqlitle approach is really as simple as copying two dll files into your PC and that's it. From a user point of view is simple, fast and it works without problems.

I don't have the impression that someone is really interesting in implementing this (i.e. foo_quicktag using the same sqlitle approach, or the foo_sql proposed idea), but let's see if I can give some arguments to make someone a little more interested :

- It'll store in a external DB personal fields like %rating%, and you could also make use of other custom fields like (just an example) %favourite%.

- It'll make possible to different people using the same PC under different accounts to rate their music differently.

- It'll not make your files change everytime you rate it (which I believe it's a personal taste that shouldn't stick to the file itself).

- It'll reduce problems about duplicates (or even more) songs using fb2k and external players like the ipod. Not a problem for a 256mb key, but for a 20 GB of music it's really hard to keep track of duplicate songs.

I would like to hear critics, comments, etc...

I second this request. Maybe the external database (sql or whatever) support/SDK could be considered as a foobar2000 core change/addition to the next version(s), I mean for storing usage related info and plugin development.

I'm just speaking my mind out and please bear with me if I said anything stupid/unfeasible...

Did peter say "absolutly not" to database filtering, so that user specific tag info isnt stored in the file and only in the database? This seems to me like the most sensable solution.

It desn't seems that kind of features (separate DB and file tags info) is going to be implemented anytime soon, at least I've never seen any fb2k developper talk about that in this forum (if it has been I've missed it and I'll appretiate if someone can post links). So I only see as a solution if someone would like to implement an external database plugin (even a simple one) based in SQL or whatever and let the user the choice of what to store in it.

I did some coding (and copy pasting kode54's code ) this weekend and I noticed that it was a bad idea trying to do it with SQLite. AFAIK it doesn't support adding new columns to existing database table, so either you have to use a set of predefined tags, or regenerate the table everytime a new kind of tag is added. Well, at least I have a perfect quicktagger for my own needs, because there are only couple of fields I need it for. I think I'll try again soon with MySQL.

Could you make it available? I mean, i'll only use it for playcounts, rating and maybe a favourite tag. If this can be done with your mod version then I'll interested in this kind of compoentn (and I'm sure many people around here as well)

Easy to solve with a bit of creative thinking...

Use a table like: "fileurl","fieldname","value"

That's brilliant! And I'm an idiot.. but I have to say that I have only 2 days of experience with databases. I'll try that as soon as I have time.

jkwarras: I'll think about it. Still needs work.

Ok. But do you have plans to make it available if it becomes stable and usuable?

Ok here it is. But this version is meant only for testing! I would appreciate if you could check that all the features below are working properly. I'd recommend taking backups before experimenting. There are some parts of kode54's code that are hard for me to understand, and I'm not yet very familiar with foobar API, so there may be bugs. Any feedback is welcome.

Download
No longer available, check here
Features
- Uses external SQLite database to save the applied tags. (quicktag.db in foobar directory). You can use a database analyzer tool or something to check the contents. For example SQLite Analyzer
- The tags in audio files are not modified.
- Tags are shown in tech info, and can be accessed in tagz-scripts using two underscores like "__RATING" etc.
- Also the tags from ext. database are reloaded when foobar reloads tags from files.
- When a file is removed from foobar database, the entries in external database are also removed.
- When a file is moved (trough foobar), the entries in the external database are modified to the new file name.
- Has all the same functionality as the original quicktag plugin.
Installation
Copy foo_quicktag_sqlite.dll to foobar2000\components\ directory and sqlite.dll to foobar2000\ directory.
Thanks to
Musicmusic (foo_quicktag), kode54 (sql functionality in foo_playcount), thoehrer (foo_playcount)

Excellent. thanks a lot for this. I'll try it as soon as I get home.

in the future could you please consider supporting MySQL? For me it would be much more manageable, but thanks anyway.

Thanks you for this! I'll try asap and report any problems.

Damn, I'm so happy... finally, DB only personal info tags...

Cool, I really need to test this one. Thanks!

edit: I just did a "%rating% to %__rating%" conversion with this plugin for 5128 tracks so I'm ready for some real testing now. It sure took some time converting those tags..

i've been trying it and so far so good!!

I haven't tried your component yet, but one thing that kode54's component missed was an implementation for metadb_callback::on_info_edited(). This prevents outside component from modifying the metadata and having it stored to the database.

The particular component I'm thinking of is my foo_pod. Certain people (hi jkwarras!) have been asking for a way to sync up the iPod's rating with something that work with Foobar. So what will happen is when the iPod's playlist is loaded into Foobar, foo_pod will extract the rating from the iPod and find the corresponding song in Foobar and set the rating metadata. Without metadb_callback::on_info_edited(), your component won't notice that foo_pod has set the rating.

So would it be a good solution for example to have a user defined list of the tags to be updated to the external database automatically in on_info_edited()? And after that what should be done with the metadata values in foobar database, delete them or just let them be? Because there's not very much point in having an external database, if the same data is also in the foobar database or in the files. Or is it possible for foo_pod to set the values straight to the tech info?

Fixed version is here. Please remove the test version and the old database file (quicktag.db).

Great plugin. Thanks!

Is that possibility to add "resync" button as in modified foo_playcount? I notice that if I copy my foobar or clean my database, I can't retrieve the information that is stored in quicktab.db. It will be handy to have "resync" button as in foo_playcount which I can have all my information back.

So far everythign works fine for me. I've trasnfered all my %rating% to %__rating%

I have some requests, if you could have the time to take alook at them I'll appretiate:

1) Could it be possible to allow lower-case, I mean instead of %__RATING%, to have %__rating%. I know it's not a big deal, but it's just that I don't like having these values in caps in the tech info properties page, but I can live with it

2) Could you add the possibility of using system date and hour values as playcount mod? I mean, having the possiblity of using i.e. %Y-%m-%d %H:%M:%S (that will show up as 2005-03-16 10:22:10). In my case I would like that to use system info to create a %__added% field (added to database info). But it could be used for other things.

3) A nice feature would be to allow to add more than one field at a time. i.e. when you press on Quick tag SQL>Rate 5 will fill the two fields %__rating% and %__rating_date%.


In anycase, thanks you very much because your plugin is really useful

foo_pod will set the metadata directly - the only thing you have to handle is the on_info_edited(), which should be fired as soon as foo_pod modifies the metadata.

Maybe this is because it would require changes to the core - which means it would require a new fb2k version. Combine this with the fact that peter is keeping a low profile when it comes to what will be in the next version, and there you have the reason why you rarely read about implementation on these forums.

The fact that there is no talk about implementation of this feature is no indication of the propability of implementation. Having DB-only tags and DB-autoupdate is among the most wanted features for the core, so you can bet that peter is aware of it - if he will implement it of course is a different question.

- Lyx

Not only that, such a change would also permeate all metadata related parts of the API. This is nothing one would do with a little hand-waving.

Wouldnt it be possible to just have a list of tags which shouldnt be updated to the file? Or better, a list of tags that should be updated to the file? (I personnally would have an empty list) This could be done with just a small configuration change to the database component AFAIK. Though admittedly Id like to see a nicer solution, which would indeed permeate the metadata related parts of the API.

Id really like to see this done. For me, that, and no media library which suits my needs, are the main reasons for not using or developing for foobar2000, though Id really like to. Id even write a media library myself, though the current database/tag implementation in fb2k would need to be altered before Id consider starting.

This SQL stuff seems closer to what Id like to see, though itd be good to see it implemented natively and supported by the database. Having two lots of database code seems like a waste. And Im not sure how heavyweight the sqllite stuff is compared to the native database. Admittedly I havent experimented much with it, so it could be better than I think.

Does Quicktag+sqllite support multiple values in a field (or multiple fields with the same name)?

I don't think so. i.e. rate a song, and rate it again with another value and it'll have the first rate replaced by the last one.

I agree about rating but I was thinking that it could have been a clever way to get round that WMA problem with multiple values fields (it seems you can't have them).

If only we could choose if a field must be unique or not....

I implemented a simple component to export the contents of the foobar2000 database to a SQLite 3 database. The SQLite database uses the following schema:
The trackid column in the meta and info tables holds the rowid of the associated track. While this is certainly not the nicest database schema, it is capable of storing arbitrary field names and multiple values in one field*. Needless to say, queries can get quite ugly with this, for example to get the locations of all songs by Queen you would have to use something like
I don't know how fast SQL queries on this database are compared to queries on the native foobar2000 database (using playlist generator style filters or another method). I have however measured the time to create and populate the SQL database (done in a single transaction of course); it is more than ten times slower than writing an FPL playlist containing the same information.

*: Exactly what makes the database schema so cumbersome, but also key features of foobar2000.

As you say, that schema is pretty inefficient, and is the biggest contributor to the performance difference. I think a better schema could give you similar if not better performance. Perhaps it would be possible to have something like (not a db wiz here):

TABLE url, trackid, extraid (primary key trackid)
TABLE trackid, title, artist, album, year, genre, rating, playcount
TABLE extraid, metaname, metavalue

ie, all the most common stuff could be stored directly in the main table or reference table, and use a separate table for any extra uncommon tags. Its expected that populating is going to be a fair bit slower, as it needs to do all the key cross referencing and hashing etc, esp if you are using the url as a primary key. But the slowness in populating can also aid in the retrieval, as it builds indexes etc for faster lookups (AFAIK). Also remember to do an 'unsynched' insertions, as otherwise the file is flushed everytime. Though with a single transaction I would assume this doesnt matter.

Retrievals and edits could possibly be much faster, afterall, that is pretty much the primary goal of sqllite. If it can't do that faster than a flatfile, you gotta wonder why you would use it. Populating a very basic db that isnt much more than a flatfile is pretty much a given that its going to be fast, but it will have drawbacks doing anything else.


Besides all this, is it possible to build a foo_database component that completely replaces the database? Id certainly like to see a better database that doesnt automatically assume you want everything synched to the file, and other nitpicks.

But I guess we should wait and see what fb2k 0.9 brings.

v.1.1 available, with database synchronization, setting multiple fields at a time, and lowercase tech info support.

The metadb service is implemented in the core (read: foobar2000.exe). It cannot be replaced by a separate component.

To prevent misconceptions about the database/metadb, let me recap what it is designed to do: First and foremost, it caches metadata about the currently used tracks in memory, so that this data can be accessed efficiently when needed. The primary place where tracks are used are the open playlists.
The second aspect of the metadb is that of a library; it basically manages a set of tracks that are known to the system, even if they are currently not on a playlist. This part is optional an can be enabled on the "Database" page in preferences.

Back on the topic of a completely SQL based database: I know comparing the time needed to populate an SQL database with the time needed to write an FPL file is not very meaningful; it is however currently the best thing (for me) to get a feeling for the performance of SQLite. I initially planned to do some more performance tests (like searching tracks based on metadata or retrieving metadata for given tracks), but after experimenting a bit with the exported database using the command line version of SQLite3, I realized how cumbersome this sort of task would be and gave up on the whole idea.

Lastly, I'm not in the position to decide that the implementation of the metadb should be changed, but I would currently advise against using a solution based on relational databases due to the following points: Using a relational database would mean reducing the functionality (restricting the kind and number of supported tags) or reducing the performance (full metadata support like in my experiment). Hybrid schemes are possible, but only offer a trade-off between reduced functionality and reduced performance while adding complexity.
Regarding better support for searching offered by relational databases: This is only a benefit in the reduced-functionality setting. With support for arbitrary tags and duplicate tags, writing anything but very simple queries is a real chore, and the resulting queries are not very efficient (nested SELECT statements or similar constructs).
The current implementation on the other hand offers very fast access to the metadata of a given track (unless the data is not up-to-date and has to be retrieved from the actual file, which can also occur with the SQL approach). The time needed to access a particular field on a track only depends on the number of fields stored for that track, not on the total number of tracks or fields known to the system. Based on this, a search operation can be implemented as a linear sweep over the tracks in the "database" (or any other list of tracks). By using library functions (for example, the filter code used by the playlist generator will be available in the helper library in the 0.9 SDK), filtering a list of tracks and sorting the results can be implemented in a few lines of code.

I wanted to let the author know that there is a problem updating tags in flac files with embedded cuesheets. It is making foobar2000 v0.8.3 crash, by hanging after clicking the "Update" button. Actually, it was a "Not Responding". It is also happening with kode's "foo_playcount", as well. Could the sqlite.dll be causing a conflict? Also, I tried running each separately, but still the same problem. Removing them both cleared the problem right up.

@Fermion,

Is there any chances to know if there's any news in the implemention of this:



...I can't wait to transfer my%added% tag to %_added% tag

I've also the feeling that foo_playcount_mod and your foo_quicktag_sql belong somehow together as both stores info on an external sql database, and the only difference between them is:

- foo_playcount can sotre systemdate values.
- foo_playcount update the meta-data info in the db everytime a track is played.

Implementing these two features (store systemdate values + an option to update a value everytime is played) will be great and will only make use of 1 external db file.

Working perfectly for me.

Speaking of saving to db though.

Is there a way to save replay gain info to database?

Because atm every time I redo my mp3s it looses the replay gain info. Since I am now using this verison of quicktag and sqlite plus the new playtime one that uses sqlite I dont loose that info anymore like I use to but I still loose replay gain.

Thanks,
Will

Hi,

Sorry to bump this thread (I hope I'm not bothering you Fermion) but is there any chances that this could be implemented?

foo_quicktag_sql v.1.2


Thanks. I believe i fixed it now. Updating cuesheets caused the metadata to reload while the metadb handle was still in use, resulting in a deadlock with another critical section. There's still the same bug in playcount plugin, so perhaps i should fix that too...


Propably not the best solution, but it could be done by using replaygain to scan the values when "block tag update operations" is enabled, and then force quicktag to update the values to the external database with the following syntax:
field name
REPLAYGAIN_TRACK_GAIN|REPLAYGAIN_TRACK_PEAK|REPLAYGAIN_ALBUM_GAIN|REPLAYGAIN_ALBUM_PEAK
value
%__REPLAYGAIN_TRACK_GAIN%|%__REPLAYGAIN_TRACK_PEAK%|%__REPLAYGAIN_ALBUM_GAIN%|%__REPLAYGAIN_ALBUM_PEAK%

Hi Fermion,

The FLAC tags updating is indeed fixed. It is working as expected. Thank you for fixing the plugin! Also, it would be very nice of you to fix the foo_playcount SQL Version. It would be most appreciated. Thanks again.

@Fermion

Thank you very much. The system dates are working perfectly.

Thanks you very much; it's really appretiated

Why 1.2 asks for MSVCRTD?

It's only me?

Till 1.1 no problems.

--------------------------

Is there a way to build a quick tag that asks me for a value (or part of it)?
Something like: value $user_input()
I'm thinking to use it as a bookmarker so one could add whatever he likes without having to thinking in advance.
E.g: I'm listening to those files I tagged as "Sunny" (in bulk) and I come across a not too sunny track so I just need to bookmark it with something as "Remove from Sunny" where "Remove from" is the written value of a BOOKMARK 1 field and Sunny" is the text I add when I trigger the tagging.

Thanks.

I had the same problem. Download it from here and put it in your foobar root dir.

v.1.3
Please test it well, I had to change some things quite a lot to make the user input work..

fabiospark:
does it work now the way you wanted?

Question to anybody. I use the original foo_quicktag. For visual indication, I add via context menu and with help of foo_quicktag,

Description: flag track, FLAG=1
and
Description: unflag track, FLAG=[EMPTY] (which deletes FLAG)

I'd like, for some visual reasons, to also remove another tag. Is there any way?

I mean, on: "unflag track" I'd like to have TRACKNUMBER and FLAG removed. 2 Actions. Possible?

Yes, it does.

In Italy we say: "l'appetito vien mangiando" (eating, one gets hungrier) - so, let me go further with some feature requests:

1) It would be nice having more than one field editable in the same dialog window(and then you can think of removing "Quick" from the plugin name and call it just tagger)

2) If you do that, you should also think to modify the normal (not MySql) QuickTag so to be able to work the same way but into files directly (the properties window way of tagging it's really cumbersome)

3 If you do that, you should also think to enable multiple values fields (a key feature of FB).
This point 3, would be very useful even with Mod QuickTag.

It comes to me right now: why don't mix the sql and the not one in just one plugin and add a checkmark to each quicktag string so to let the user decide if it has to be in or out the audio files?

Thanks.

That will be really nice.