Skip to main content
Topic: foo_sqlite (Read 3460 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

foo_sqlite

foo_sqlite is a foobar2000 component for viewing the contents of the media library and the playlists by using SQL queries.

Latest changes:
1.0:
- initial version, SQL functionality extracted from SQL Tree
- fixed: two or more update statements on the virtual tables in a batch were executed in parallel instead of one after another in autocommit mode (outside transaction)
- fixed: tracknumber was missing as default column for the virtual tables
-added: STRG-A selects the whole content in the SQLite console
- changed: Don't show an error anymore in the SQLite console, if the error code is "not an error"
- other: upgrade to SQLite 3.13.0

Download the latest version: here

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?

 
SimplePortal 1.0.0 RC1 © 2008-2018