IPB

Welcome Guest ( Log In | Register )

4 Pages V   1 2 3 > »   
Reply to this topicStart new topic
foo_customdb: Custom Database, [was “[…] How to transfer tags to/from DB, A solution”]
odyssey
post Oct 8 2009, 10:39
Post #1





Group: Members
Posts: 2296
Joined: 18-May 03
From: Denmark
Member No.: 6695



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.

This post has been edited by shakey_snake: Nov 11 2009, 23:55
Reason for edit: Emphasized Warning


--------------------
Can't wait for a HD-AAC encoder :P
Go to the top of the page
+Quote Post
2E7AH
post Oct 8 2009, 18:28
Post #2





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



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

Go to the top of the page
+Quote Post
2E7AH
post Oct 8 2009, 20:47
Post #3





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



Here is hotness algorithm for foo_custom:

CODE
$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
Go to the top of the page
+Quote Post
odyssey
post Oct 8 2009, 20:53
Post #4





Group: Members
Posts: 2296
Joined: 18-May 03
From: Denmark
Member No.: 6695



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 wink.gif


--------------------
Can't wait for a HD-AAC encoder :P
Go to the top of the page
+Quote Post
2E7AH
post Oct 8 2009, 21:11
Post #5





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



QUOTE (odyssey @ Oct 8 2009, 21:53) *
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.
Go to the top of the page
+Quote Post
2E7AH
post Oct 9 2009, 07:07
Post #6





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



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
$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 wink.gif

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
Go to the top of the page
+Quote Post
odyssey
post Oct 9 2009, 07:49
Post #7





Group: Members
Posts: 2296
Joined: 18-May 03
From: Denmark
Member No.: 6695



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

smile.gif


--------------------
Can't wait for a HD-AAC encoder :P
Go to the top of the page
+Quote Post
fbuser
post Oct 9 2009, 07:53
Post #8





Group: Developer
Posts: 682
Joined: 26-September 07
Member No.: 47369



@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.
Go to the top of the page
+Quote Post
2E7AH
post Oct 9 2009, 08:40
Post #9





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



QUOTE (odyssey @ Oct 9 2009, 08:49) *
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 (fbuser @ Oct 9 2009, 08:53) *
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.
Go to the top of the page
+Quote Post
odyssey
post Oct 9 2009, 08:55
Post #10





Group: Members
Posts: 2296
Joined: 18-May 03
From: Denmark
Member No.: 6695



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
Go to the top of the page
+Quote Post
2E7AH
post Oct 9 2009, 09:00
Post #11





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



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

This post has been edited by 2E7AH: Oct 9 2009, 09:04
Go to the top of the page
+Quote Post
odyssey
post Oct 9 2009, 10:01
Post #12





Group: Members
Posts: 2296
Joined: 18-May 03
From: Denmark
Member No.: 6695



The SQLiteStudio you linked to seems to be a handy tool wink.gif


--------------------
Can't wait for a HD-AAC encoder :P
Go to the top of the page
+Quote Post
Kent Wang
post Oct 20 2009, 16:10
Post #13





Group: Members
Posts: 78
Joined: 12-June 03
Member No.: 7145



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.
Go to the top of the page
+Quote Post
2E7AH
post Oct 20 2009, 16:18
Post #14





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



Just use the field name: if it is RATING than something like this:

CODE
$if(%rating%,$repeat(★,%rating%)$repeat(☆,$sub(5,%rating%)))
Go to the top of the page
+Quote Post
Kent Wang
post Oct 21 2009, 15:43
Post #15





Group: Members
Posts: 78
Joined: 12-June 03
Member No.: 7145



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.
Go to the top of the page
+Quote Post
2E7AH
post Oct 21 2009, 18:18
Post #16





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



QUOTE (odyssey @ Oct 8 2009, 11:39) *
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 $if($or($stricmp(%codec%,mid),$stricmp(%codec%,pcm)),%field name%). Tried to restart foobar as suggested but nothing happens. I guess it's not my day today wink.gif

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 smile.gif

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

This post has been edited by 2E7AH: Oct 21 2009, 18:35
Go to the top of the page
+Quote Post
fbuser
post Oct 21 2009, 19:15
Post #17





Group: Developer
Posts: 682
Joined: 26-September 07
Member No.: 47369



QUOTE (2E7AH @ Oct 21 2009, 18:18) *
$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%,)
Go to the top of the page
+Quote Post
2E7AH
post Oct 21 2009, 19:20
Post #18





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



With "nothing happened" I meant foobar doesn't start.

$if($or($stricmp(%codec%,mid),$stricmp(%codec%,pcm)),%field name%)

is the same as:

$if($or($stricmp(%codec%,mid),$stricmp(%codec%,pcm)),%field name%,)

and the problem is in the fields names used I think: ARTIST, ALBUM and TITLE
Go to the top of the page
+Quote Post
fbuser
post Oct 21 2009, 19:38
Post #19





Group: Developer
Posts: 682
Joined: 26-September 07
Member No.: 47369



Good to know, I never recognized that the last comma is optional.
Go to the top of the page
+Quote Post
2E7AH
post Oct 21 2009, 19:44
Post #20





Group: Validating
Posts: 2424
Joined: 21-May 08
Member No.: 53675



OK smile.gif
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
Go to the top of the page
+Quote Post
fbuser
post Oct 21 2009, 19:46
Post #21





Group: Developer
Posts: 682
Joined: 26-September 07
Member No.: 47369



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

This post has been edited by fbuser: Oct 21 2009, 19:47
Go to the top of the page
+Quote Post
mgl2k
post Oct 31 2009, 18:58
Post #22





Group: Members
Posts: 3
Joined: 28-January 08
Member No.: 50857



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.
Go to the top of the page
+Quote Post
Tonto
post Nov 9 2009, 04:08
Post #23





Group: Members
Posts: 13
Joined: 20-May 07
Member No.: 43633



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
Go to the top of the page
+Quote Post
odyssey
post Nov 9 2009, 10:34
Post #24





Group: Members
Posts: 2296
Joined: 18-May 03
From: Denmark
Member No.: 6695



QUOTE (Tonto @ Nov 9 2009, 04:08) *
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 (Tonto @ Nov 9 2009, 04:08) *
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? smile.gif


--------------------
Can't wait for a HD-AAC encoder :P
Go to the top of the page
+Quote Post
drmrbrewer
post Nov 11 2009, 21:36
Post #25





Group: Members
Posts: 109
Joined: 22-October 06
Member No.: 36669



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 (odyssey @ Oct 8 2009, 09:39) *
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
Go to the top of the page
+Quote Post

4 Pages V   1 2 3 > » 
Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



RSS Lo-Fi Version Time is now: 22nd September 2014 - 04:26