Skip to main content ↑ ↓

Notice

Please note that most of the software linked on this forum is likely to be safe to use. If you are unsure, feel free to ask in the relevant topics, or send a private message to an administrator or moderator. To help curb the problems of false positives, or in the event that you do find actual malware, you can contribute through the article linked here.
Topic: foo_customdb: Custom Database (Read 81776 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

foo_customdb: Custom Database

I really love tagging and sometimes I want to tag the most outrages things. Even assignments for autoplaylists, so my playlists wouldn't get lost even if I moves my files (which I do a lot)...

So I've taken a look into the foo_customdb component that is a separate database holding your tags without writing them to files.

First you should decide how you want to define a key (a unique identifier) for a tag. This is customizable which is cool, because then you can even define a key to match more than just one file/item. Here are a few examples:

ALBUMRATING: "%artist% %album%"
RATING: "%artist% %title%"

If you don't move your files around a lot, you could simply use "location" to store values for each file, but personally I would like a better ability to track a file no matter where it is. To achieve this, I use foo_biometrics which can store a unique fingerprint in the %fingerprint_fooid% tag. However the tag itself is quite long and you might want to use a shorter key, so you can use the function $crc32(%fingerprint_fooid%) to define the key. However, it's important that you only allow writing to files that contain this tag, so make sure that you under Action define the "Set value:" to "$if(%fingerprint_fooid%,value,)".

Since I already have a lot of tags that I may need to store in foo_customdb, I was wondering if it was possible to transfer those tags back and forth if I ever decides maybe even to abandon the component or something else. This is actually possible! You simply define an action and define the "Set value:" to "$if(%fingerprint_fooid%,%tag_stored_in_file%,)".

Also it's possible to move the tags back in the files using the normal Properties dialog and "Automatically Fill Values". You define a Source from "Other" and sets the tagname you have defined in foo_customdb and defines the tag that it should be written to in the "Pattern". Unfortunately I have not been able to make this work using Masstagger.

Note that if you want to store a value in foo_customdb, you need to use it's own context menu. If you try to set a tag anywhere else (CUI, Properties etc.), it will be written directly to the file.

Please note that this component (0.0.9a) is currently listed under Problematic Components and it may cause crashes. I have experienced crashes after configuration if I don't restart foobar2000 before using the changes.
Can't wait for a HD-AAC encoder :P

foo_customdb: Custom Database

Reply #1 –
It's good for me to see foo_custom usage examples here. The component has open database format which can be easily manipulated and if there is reason for some action there seems to be quite possibilities open.

I found this page in the past and may be useful if someone is interested - example how to transfer some tag info to custom db and manipulate db

foo_customdb: Custom Database

Reply #2 –
Here is hotness algorithm for foo_custom:

Code: [Select]
$puts(baselinefrequency,90)$puts(baselinedecay,28)$puts(lp,[%last_played%])$puts(fp,[%first_played%])$puts(pc,[%play_count%])$puts(rating,[%rating%])$puts(avgrating,3)$puts(lp_mod,$sub($add($add($mul($year($get(lp)),365),$mul($month($get(lp)),30)),$day_of_month($get(lp))),730000))$puts(fp_mod,$sub($add($add($mul($year($get(fp)),365),$mul($month($get(fp)),30)),$day_of_month($get(fp))),730000))$puts(date_diff,$sub($add($add($mul($year(%now%),365),$mul($month(%now%),30)),$day_of_month(%now%)),730000))$puts(baselinefrequency,$mul($get(baselinefrequency),24))$puts(baselinedecay,$mul($get(baselinedecay),24))$puts(lp_age,$add($substr($get(lp),12,13),$mul(24,$get(lp_mod))))$puts(fp_age,$add($substr($get(fp),12,13),$mul(24,$get(fp_mod))))$puts(age,$sub($get(lp_age),$get(fp_age)))$puts(now_age,$add($time(%now%),$mul(24,$get(date_diff))))$puts(recentness,$sub($get(now_age),$get(lp_age)))$puts(decay,$div($div($mul($get(pc),$get(baselinefrequency),$get(baselinedecay),$if2($get(rating),$get(avgrating)),100),$mul($max($get(age),$get(baselinefrequency)),$get(avgrating))),100))$puts(rawhotness,$div($mul($max($sub($get(decay),$get(recentness)),0),100),$get(decay)))$puts(forecast,$div($mul($max($sub($get(decay),$add($div($max(0,$sub($get(baselinedecay),$get(recentness))),2),$get(recentness))),0),100),$get(decay)))$put(hotness,$div($add($get(rawhotness),$get(forecast)),2))




It uses: %last_played%, %first_played%, %play_count%, %rating%
It can be used for creating autoplaylists: i.e. %HOT% GREATER 90
It can be used for creating Autorating (also in custom_db): $div($add(%HOT%,4),20)

Following odyssey's "Album rating" idea, which also come to my mind in past but had no idea how to manage it, I can't make it work without context menu. What I was thinking is that "Album rating" should be some average of tracks ratings or auto ratings, but I can't make it work automatically - don't know if it's possible or is there some trick

foo_customdb: Custom Database

Reply #3 –
As you can only define one value using the context menus, you can't get an average rating from a set of rating on a album. However, I think you can combine your first idea here with a neat select (or grouping maybe) to determine an average as such. I will have to find out the way the guide works, because I'm not familar with SQLite SQL usage - just some other proprietary SQL i work with everyday, but I'll probably manage it
Can't wait for a HD-AAC encoder :P

foo_customdb: Custom Database

Reply #4 –
Quote from: odyssey on
However, I think you can combine your first idea here with a neat select (or grouping maybe) to determine an average as such.

You mean in the custom_db itself? It won't be hard but it doesn't sound like automatic solution

I tried using key: "%artist%, %album%" and hoped that it would solve by itself, but unfortunately not. If update is set to "Media Library (modified)" it accepts the value of only the last track modified.

But maybe using "playback" for update action and some script (like simplified hotness) can make this Album rating field - and that would only count from the time the field is made and on (because of playback update) - So this may sound like automatic solution: when Album rating field is created with "playback" update, populate the Album rating fields in custom_db by hand and than let the field autoupdate on playcount. I will try that and see if it works.

foo_customdb: Custom Database

Reply #5 –
OK, I've tried that: First it wasn't very easy as I thought. Lacking SQL skills, I exported custom_db to CSV and done the calculations in Excel.

I needed items with "fieldname" HOT and HOT_ALBUM so I can average the HOT_ALBUM with HOT values from it's tracks. (I wouldn't explain how I've done that, but if someone is interested I could, although I would like to know how this can be done using SQL)

After the averaging I imported the values in custom_db. Now what to use as value for HOT_ALBUM?
I ended using this simple code:
Code: [Select]
$if(%totaltracks%,$div($add($mul(%hot_album%,$sub(%totaltracks%,1)),%hot%),%totaltracks%))

It's not perfect but it generally works and I done all this quickly so if we involve little thinking it can be more correctly presented. Only consideration that timing should be set to more than 60s (i.e. 65s) so it can catch the playback statistics update

So we can now use this HOT_ALBUM as indicator for Album hotness, in autoplaylists, or for Album autorating (which was the idea): i.e. $div($add(%HOT_ALBUM%,4),20), but it's not good for using as column as the update is not for that use, but we can have it as a tag field which can be accessed from anywhere.



Only that setted HOT_ALBUM value will not auto-decay - for that we'll need update on "Media Library (modified)" which can be used instead "playback" update if someone wants, but as I said it's not perfect and it can be improved with modifying hotness code for Album use, and for now this is just example

foo_customdb: Custom Database

Reply #6 –
SQL isn't really that hard when you get into it. You should be able to sum data with a select string. Something like:

SELECT SUM(column_name) FROM TABLE WHERE something=something_else GROUP BY whatever

Can't wait for a HD-AAC encoder :P

foo_customdb: Custom Database

Reply #7 –
@2E7AH

Does the timing work accurately for you?
I have the problem, that I'm not able to find a setting, where playcount is updated, when a track is completely played. If I you choose 9999 seconds as threshold, not all tracks are updated. The same problem occurs for using a percentage threshold. 100% doesn't work at all, and for 99% it is a similar problem as for a seconds threshold. The latter one is apperantly a resolution problem. It occurs mainly for short tracks less then 100 seconds.

foo_customdb: Custom Database

Reply #8 –
Quote from: odyssey on
SELECT SUM(column_name) FROM TABLE WHERE something=something_else GROUP BY whatever

It seems easy but can you make that example to work for this table (average HOT_ALBUM with it HOT tracks):



Quote from: fbuser on
Does the timing work accurately for you?

I use official statistics, but trying now it's probably bug: 98, 99 and 100% doesn't update the field for length <= 90s and 100% doesn't update any length.

foo_customdb: Custom Database

Reply #9 –
You will have to perform it outside foobar (or by a shortcut, or maybe even using a script). You need to extract the info using a SELECT and then adding the calculated values using INSERT INTO. customdb will just return the value from the db, so you can't sum when you view it. As I said before, I need to take a closer look into SQLite, and it probably won't be before this weekend.
Can't wait for a HD-AAC encoder :P

foo_customdb: Custom Database

Reply #10 –
I don't mean to do that with foobar.
OK, I'll look in some SQL books on my PC - it's good to know the basic syntax

foo_customdb: Custom Database

Reply #11 –
The SQLiteStudio you linked to seems to be a handy tool
Can't wait for a HD-AAC encoder :P

foo_customdb: Custom Database

Reply #12 –
How do you show the fields from customdb in playlist view, in the columns? E.g., I want to show ratings in the columns view.
Everything I've learned about space, I've learned from psytrance.

foo_customdb: Custom Database

Reply #13 –
Just use the field name: if it is RATING than something like this:

Code: [Select]
$if(%rating%,$repeat(★,%rating%)$repeat(☆,$sub(5,%rating%)))

foo_customdb: Custom Database

Reply #14 –
Thanks.

The problem was very strange. If I restart once, it would show, but if I restart again, it wouldn't, and then it would alternate. I think this was because I had RATING set to Location (foo_custominfo compatibility) instead of the custom value. I solved this by creating a TEMP value, setting TEMP to RATING, change RATING to custom value (the customdb default, instead of the foo_custominfo compatibility), then setting RATING to TEMP.

Now that I have this working I think it's a lot better than using the Playback Statistics component and slightly better than the outdated foo_custominfo.
Everything I've learned about space, I've learned from psytrance.

foo_customdb: Custom Database

Reply #15 –
Quote from: odyssey on
Please note that this component (0.0.9a) is currently listed under Problematic Components and it may cause crashes. I have experienced crashes after configuration if I don't restart foobar2000 before using the changes.

I tried to manage custom tags (artist, album, title and comment) for non-taggable files (MID, WAV...). So I added the fields and in actions set value to [font= "Courier New"]$if($or($stricmp(%codec%,mid),$stricmp(%codec%,pcm)),%field name%)[/font]. Tried to restart foobar as suggested but nothing happens. I guess it's not my day today

I deleted customdb_sqlite.db and tried again: now new db is created with size or 4096 bytes, then failure.dmp with size 0 bytes and failure.txt with size 3 bytes (EF BB BF). And that's it.

I'm glad that I didn't deleted some file tags that I managed thorough this custom db, but I definitely lost all it settings. So be careful, again

[edit] and thanks to foo_jesus I didn't saw too much trouble

foo_customdb: Custom Database

Reply #16 –
Quote from: 2E7AH on
$if($or($stricmp(%codec%,mid),$stricmp(%codec%,pcm)),%field name%). Tried to restart foobar as suggested but nothing happens.
If there isn't a typo in your post, nothing happened because of a syntax error. Probably you wanted to use: $if($or($stricmp(%codec%,mid),$stricmp(%codec%,pcm)),%field name%,)

foo_customdb: Custom Database

Reply #17 –
With "nothing happened" I meant foobar doesn't start.

[font= "Courier New"]$if($or($stricmp(%codec%,mid),$stricmp(%codec%,pcm)),%field name%)[/font]

is the same as:

[font= "Courier New"]$if($or($stricmp(%codec%,mid),$stricmp(%codec%,pcm)),%field name%,)[/font]

and the problem is in the fields names used I think: ARTIST, ALBUM and TITLE

foo_customdb: Custom Database

Reply #18 –
Good to know, I never recognized that the last comma is optional.

foo_customdb: Custom Database

Reply #19 –
OK
The problem was in field names indeed: if I use ARTIST, ALBUM and TITLE it happens again and with _ARTIST, _ALBUM and _TITLE i.e. it's all right. Maybe the cause is in just one of this names, but I would suggest avoiding field names that conflicts tag names

foo_customdb: Custom Database

Reply #20 –
I just wanted to suggest you something like that, because I successfully tested it that way. But you were faster.

foo_customdb: Custom Database

Reply #21 –
Hello,

first of all thanks for this great component.

I'm trying to get tags with multiple values to work with this component so I can use these tags in the Album List panel of the DUI, but I haven't had any luck so far.
Example: A file with a tag named %style% and the value "a;b" should appear in the Album List panel in the two branches with the names "a" and "b".
Problem: The %<style>% syntax of Album List doen't show anything for CustomDB-tags. Showing %style% is no problem.

Is this possible? Is there anything you can do about it or can this problem only be addressed in the Album List component?

Thanks again for this component, very much appreciated.

foo_customdb: Custom Database

Reply #22 –
This component is way cool and really important to me, so this insight is really cool (in fact, reading this thread helped me understand the component much better!)

But I am wondering! WHY DO I, or, DO I have to use the customdb context menu?

I want to use the lyricsdb component to download the lyrics to the customdb, and also for tagging/properties to write information to the customdb, and similar things. Is there any way that I am able to do this?

Thanks

foo_customdb: Custom Database

Reply #23 –
Quote from: Tonto on
But I am wondering! WHY DO I, or, DO I have to use the customdb context menu?

Yes. This also makes it easier to understand that this component works separately from other tags, but still provides great integration with foobar2000.

Quote from: Tonto on
I want to use the lyricsdb component to download the lyrics to the customdb, and also for tagging/properties to write information to the customdb, and similar things. Is there any way that I am able to do this?

In this case I recommend you to create a folder to store your lyrics in. lyricsdb component also allows you to define an identifier to associate specific lyrics, so you can link a file the same way I explained in the first post. This is probably the best way, as it's not stored in the tags, but still possible to access through foobar2000.

Alternatively you have to do a lot of work, first tagging to files and then moving the tags to customdb. Another alternative is to store them in files and create a script or something that will update the SQLite database for you, but this requires that foobar2000 is shut down. Also I don't see what you will gain from this compared to just storing the lyrics in files, but maybe you can elaborate?
Can't wait for a HD-AAC encoder :P

foo_customdb: Custom Database

Reply #24 –
I've tried this out and find it very interesting indeed.  Seems to work.  Couple of questions.

This for me is the most important part -- the ability to get at my data if/when required, and write it into the files themselves:

Quote from: odyssey on
Since I already have a lot of tags that I may need to store in foo_customdb, I was wondering if it was possible to transfer those tags back and forth if I ever decides maybe even to abandon the component or something else. This is actually possible! You simply define an action and define the "Set value:" to "$if(%fingerprint_fooid%,%tag_stored_in_file%,)".

Also it's possible to move the tags back in the files using the normal Properties dialog and "Automatically Fill Values". You define a Source from "Other" and sets the tagname you have defined in foo_customdb and defines the tag that it should be written to in the "Pattern". Unfortunately I have not been able to make this work using Masstagger.


However, I can't get this to work.  For the first option above, could you be so kind as to post a screenshot showing the settings you use in the Action tab, for the example custom tag "RATING_CD"?  I couldn't get the second option to work either.

And is there a good (free) SQLite database viewer / editor you can recommend?

Thanks,

Mike