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: [fb2k v2] SQLite Utilities (foo_sqlite) (Read 55648 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

[fb2k v2] SQLite Utilities (foo_sqlite)

foo_sqlite is a foobar2000 component for viewing the contents of the media library and the playlists by using SQL queries. Additionally, foo_sqlite provides the possibility to store tag information in a database by defining attributes for each tag individually.

Upgrade Note: If you're upgrading from an older version than 3.0.0, it is highly recommended to backup your current configuration before. If you are also using foo_uie_sql_tree you must upgrade this component also.

Prerequisites:
  • foobar 2000 2.0+

Download
Version history

Re: foo_sqlite

Reply #1
Thank you for this component.

Question: Why are SELECT statements not case sensitive? A query like:
Code: [Select]
select composer from medialibrary where composer IS 'LUDWIG VAN BEETHOVEN'
still returns rows with 'Ludwig van Beethoven'.

Re: foo_sqlite

Reply #2
Because the default collation of the columns of the MediaLibrary table is NaturalNoCase. You can change the collation for each column in the preferences : Media Library -> SQLite viewer -> MediaLibrary table to Binary, if you want to have always a case sensitive comparison, or you could rewrite your query as:
Code: [Select]
select composer from medialibrary where composer IS 'LUDWIG VAN BEETHOVEN' collate binary
This won't return "Ludwig van Beethoven" anymore.

Re: foo_sqlite

Reply #3
Thank you, this solves it. I didn't think of collation.

Re: foo_sqlite

Reply #4
How can I implement my own icons?
Is there an example?

Re: foo_sqlite

Reply #5
How can I implement my own icons?
Is there an example?
There are not any icons in foo_sqlite. Do you mean perhaps foo_uie_sql_tree?

Re: foo_sqlite

Reply #6
Oh sorry!!
Yes, I mean foo_uie_sql_tree!
I can not view custom icons there.
I Have already set the path under 'Config' + 'Advanced'

Re: foo_sqlite

Reply #7
What is the content of the path=
Only custom icons, which are placed into a bitmap file with a height of 16px and a with of multiple of 16px, for example a bitmap file with 16x48 pixels would contain 3 icons.

Re: foo_sqlite

Reply #8
Thank you very much for this info!
Can you get me an example?
That would help me a lot.
Thanks for the quick replies.

Re: foo_sqlite

Reply #9
First of all you need a set of icons with the dimensions 16x16 px. With an image processing application you can combine these icons to one image file and save it as BMP file.

This can easily done with IrfanView:
- open "Image -> Create panorama image ..."
- Click on "Add images" and add your icon files
- Click on "Create image" and save the result as "BMP - Windows Bitmap".
- Add the full path of the created image to foobar2000 under "Preferences -> Advanced -> Tools -> SQL tree -> Icon file paths". You can add multiple of such images by separating their paths with |.
- Restart foobar2000 to get the images loaded.

Re: foo_sqlite

Reply #10
Thank you very much for this instruction!!
Now, I can see my own Icons in the SQL-Tree!  :)

Re: foo_sqlite

Reply #11
Are there any examples on how to select songs from a playlist?

What I want to do is generate a playlist C which contains every song in playlist B, except the songs which are in playlist A.

Re: foo_sqlite

Reply #12
Are there any examples on how to select songs from a playlist?

What I want to do is generate a playlist C which contains every song in playlist B, except the songs which are in playlist A.

So I also got foo_uie_sql_tree which comes with an SQL statement creating a "Playlist" table, which I queried.

Figured out that comparing filenames between playlists works best (since some of the tags are empty) and the resulting SQL statement is:

Code: [Select]
SELECT filename FROM Playlist WHERE playlist_index=3
EXCEPT
SELECT filename FROM Playlist WHERE playlist_index=2

I have no clue how to create a Playlist from SQL, but foo_uie_sql_tree allows for creation of Playlists from SQL statements.

Re: foo_sqlite

Reply #13
I have no clue how to create a Playlist from SQL, but foo_uie_sql_tree allows for creation of Playlists from SQL statements.
First you neead to create a virtual table, which allows modifying playlists:
Code: [Select]
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);
Then you can add items to a playlist (T), which belong to one playlist (A) but don't belong to another playlist (B) with this statement:
Code: [Select]
INSERT INTO Playlist_Updatable (metadb_handle,playlist_name)
SELECT metadb_handle,'Playlist T'
FROM Playlist a
WHERE playlist_name='Playlist A'
  AND NOT EXISTS (SELECT 1
                  FROM Playlist
                  WHERE playlist_name='Playlist B'
                    AND filename=a.filename)

If the playlist 'Playlist T' does not exist, it will be created (unless it was already existing and was deleted before the insert statement was executed. This is a bug and will be fixed with the next release).

More infomation for this topic you can find here.


Re: foo_sqlite

Reply #15
Doesnt work.

 

Re: foo_sqlite

Reply #16
I have a small graphical problem with "SQL Tree"
Whenever "Database activity ..." appears at the bottom, the graphical view of the vertical scroll bars will briefly change.
Is this a known problem?

Re: foo_sqlite

Reply #17
There is a bug in the online documentation at http://www.palm-internet.eu/foobar2000/components/foo_sqlite/foo_sqlite_help.html. The links to the help pages are absolute file:// URLs. Relative links would work better. ;)
Thanks for the hint. Of course, the links should've been relative. Will be fixed soon.

Doesnt work.
What does not work?

I have a small graphical problem with "SQL Tree"
Whenever "Database activity ..." appears at the bottom, the graphical view of the vertical scroll bars will briefly change.
Is this a known problem?
This has again nothing to do with foo_sqlite. But to answer the question: It's not a problem, it's intended. During database activities, the scrollbars will be disabled.

Re: foo_sqlite

Reply #18
I was trying a couple of things but I was not able to achieve the following:
I wanted to list and insert all the content from an play list A which is not part of media library.
How to achieve this?

Thanks in advance.

Re: foo_sqlite

Reply #19
I wanted to list and insert all the content from an play list A which is not part of media library.
How to achieve this?
The listing can be done with this query:
Code: [Select]
SELECT *
          FROM Playlist p
          WHERE playlist_name='A'
               AND NOT EXISTS (SELECT 1
                                           FROM MediaLibrary
                                           WHERE path=p.path)

In case you have many items in your media library the following sequence of sql statements might be faster:
Code: [Select]
CREATE TEMPORARY TABLE tmp_Path
AS SELECT DISTINCT path
FROM MediaLibrary;

CREATE UNIQUE INDEX tmp_Path_in_unique ON tmp_Path(path);

SELECT *
FROM Playlist p
WHERE playlist_name='A'
  AND NOT EXISTS (SELECT 1
                  FROM tmp_Path
                  WHERE path=p.path)

Where do you want to insert the result?

Re: foo_sqlite

Reply #20
Thanks for the reply. I played a little bit around with the code.
Unfortunately the code doesn't suit my purpose. Your example compares on path but the path will be different because these are different files.

I had difficulties with the exists syntax and I tried a join instead. For speed reasons I created two temp tables:

Code: [Select]
DROP TABLE IF EXISTS ml_temp;
CREATE TEMPORARY TABLE ml_temp
AS SELECT DISTINCT artist,album,title,codec
FROM MediaLibrary;

DROP TABLE IF EXISTS Test;
CREATE TEMPORARY TABLE Test
AS SELECT DISTINCT artist,album,title,codec
FROM Playlist p
WHERE playlist_name='Test';

SELECT DISTINCT p.artist,p.album,ml.codec AS lib_codec,p.codec AS logs_codec
FROM Test p
JOIN ml_temp ml
ON ml.artist=p.artist
AND
ml.album=p.album

So this gives me a visual list of all albums which exist in Playlist "Test" and the media library.

Is there any way to have this fed back into a new Playlist "Result"?

Re: foo_sqlite

Reply #21
Unfortunately the code doesn't suit my purpose.
But it's doing what you actually asked for.  :)
So this gives me a visual list of all albums which exist in Playlist "Test" and the media library.
Which is the opposite from your original question.  :)

Is there any way to have this fed back into a new Playlist "Result"?
No, this is logically not possible, because only tracks can be added to a playlist, not summarized results like in your query. However you could either remove the tracks from the test playlist, which don't exist in the media library or add the matching tracks to another playlist.

To do this you first need to create another virtual table for the playlists, which allows you to manipulate them:
Code: [Select]
DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable USING MetaDB_Module(no_multivalue_split,playlist);

The deleting of the unmatched tracks could be done with (not possible in this case without NOT EXISTS):
Code: [Select]
DELETE FROM Playlist_Updatable pu
WHERE playlist_name='Test'
    AND NOT EXISTS (SELECT 1
                                FROM ml_tmp
                                WHERE artist=pu.artist
                                     AND album=pu.album)

If you don't want to list the result it's not necessary to add the columns title and codec to the table ml_tmp and also an index to speed up the lookup on this table should be created. It could look like this:
Code: [Select]
DROP TABLE IF EXISTS ml_temp;
CREATE TEMPORARY TABLE ml_temp
AS SELECT DISTINCT artist,album
FROM MediaLibrary;

CREATE UNIQUE INDEX ml_temp_in_unique on ml_tmp(artist,album);

Adding the matching tracks to another playlist could look like this (if the target playlist, doesn't exist, i will be automatically created):
Code: [Select]
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT metadb_handle,'New playlist'
FROM Playlist_Updatable /* or just Playlist, it doesn't matter */ pu JOIN ml_temp ml ON ml.artist=pu.artist AND ml.album=pu.album
WHERE playlist_name='Test'

Note1: I didn't test the SQL statements, so they might be not 100% correct.

Note2: You will run into troubles, if the combination of artist and album is not unique, i.e. you have more than one album with the same combination, but this depends on the content of your media library and your individual tagging guidelines.


Re: foo_sqlite

Reply #22
What I wanted to do with this, remove old crap data that foobar was holding on to instead of rescanning the files, doesn't appear to be possible. Whether I try to update or delete the data, I always get 'SQLite Error: (8) attempt to write a readonly database'. Is that an absolute restriction? Do I have to use external SQLite editors to do this?

As an aside, I checked the entire documentation looking for just one single sample query for the most basic media library query, but there was not one. I don't know the structure of the database, and I guessed at a few things based on this thread. I feel like the entire documentation is a bit bereft without the fundamental basics, or at a bare minimum a link to its structure explained elsewhere. It doesn't do any good to have a tool to query if you don't know what you're querying!

Re: foo_sqlite

Reply #23
What I wanted to do with this, remove old crap data that foobar was holding on to instead of rescanning the files, doesn't appear to be possible.
No, it isn't.

Whether I try to update or delete the data, I always get 'SQLite Error: (8) attempt to write a readonly database'. Is that an absolute restriction?
No, but you should have a look at the documentation here. It's explained there, which data manipulation commands are available and what needs to be done to use them.

Do I have to use external SQLite editors to do this?
You can't. The MediaLibrary and Playlist virtual tables are only available inside of foobar2000.

As an aside, I checked the entire documentation looking for just one single sample query for the most basic media library query, but there was not one.
Yes, you're right. Originally foo_sqlite was a part of foo_uie_sql_tree, which comes with some example nodes and there relevant documented queries.

I don't know the structure of the database,
There is nothing you need to know besides what you can find in the documentation. There are only a very few internal tables, which are not meant to be used by the user.

It doesn't do any good to have a tool to query if you don't know what you're querying!
It's all written in the documentation:
Quote
For querying the foobar2000 media library a SQLite virtual table is provided which includes besides all available tags several other fields per default. The fields of the virtual table can be customized in the preferences. Similarily also a virtual table is provided for querying the playlists. The virtual table names are: MediaLibrary and Playlist.

Re: foo_sqlite

Reply #24
I want to use foo_sqlite to generate playlist for syncing with my iPod. The playlist should contain all albums with an average rating > 3 and all albums that have at least one track with missing rating. Additionally, I want to reduce the number of items in a playlist by filtering the files by their path. This is the code that I hacked together:
Code: [Select]
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, b.playlist_name, a.path, minr, avgr
FROM MediaLibrary a, (SELECT 'SQL_>M' "playlist_name") b
JOIN
  (
  SELECT a.metadb_handle, a.album, min(a.rating) minr, avg(a.rating) avgr, a.path
  FROM MediaLibrary a
  WHERE substr(a.path, 1, 9) = 'D:\Metal\' AND substr(a.path, 10, 1) > 'M'
  GROUP BY a.album
  HAVING minr='' OR avgr>3
  ) albums
ON a.album = albums.album

It seems to work. However, it also returns tracks that do not match the WHERE clause, e.g. there are some tracks from "D:\Rock\" and some from "D:\Electronic\". Any idea what my mistake is? I have no clue about SQL queries so most likely there is a better query for my intended use.