Skip to main content

Notice

Please be aware that much of the software linked to or mentioned on this forum is niche and therefore infrequently downloaded. Lots of anti-virus scanners and so-called malware detectors like to flag infrequently downloaded software as bad until it is either downloaded enough times, or its developer actually bothers with getting each individual release allow listed by every single AV vendor. You can do many people a great favor when encountering such a "problem" example by submitting them to your AV vendor for examination. For almost everything on this forum, it is a false positive.
Topic: foo_sqlite (Read 9290 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_sqlite

Reply #25
It seems to work.
Well, at least the statement, which you posted here, won't be executed as the syntax is wrong. Anyway, try this:

Code: [Select]
WITH Albums AS (
  SELECT album
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album

Re: foo_sqlite

Reply #26
Oh sorry I forgot to remove the extra columns I used for debugging without "INSTER INTO..."-line. I used the columns to directly verify whether the criteria where applied.
 
Your code looks much more clean, but it does also pull in tracks from other folders. There are more tracks in the list than there are in the Metal-folder. With your code I can also not see the results in the console if I remove the "INSTER INTO..."-line. I do not understand how the tracks from other folders make it into the list...

Re: foo_sqlite

Reply #27
but it does also pull in tracks from other folders.
This is only possible, if you have have albums from the metal folder with the same name in other folders. In this case you could try this (assuming that all selected album names are distinct):

Code: [Select]
WITH Albums AS (
  SELECT album, max(substr(path,1,10)) path
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album AND a.path LIKE Albums.path||'%'

 

Re: foo_sqlite

Reply #28
but it does also pull in tracks from other folders.
This is only possible, if you have have albums from the metal folder with the same name in other folders. In this case you could try this (assuming that all selected album names are distinct):

Code: [Select]
WITH Albums AS (
  SELECT album, max(substr(path,1,10)) path
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album AND a.path LIKE Albums.path||'%'

Ah that's it! Thank you for your help. I am happy with your latest query suggestion :)

Re: foo_sqlite

Reply #29
I have what I think is a very simple use case but can't quite figure out how to get it to work. I want to simply extract all fields from my database and save off to a CSV to use elsewhere.

What's the syntax for using the writefile command? Is it possible to save the output of a query into a file?

I can just copy the results right out of the console as well but then the data comes without the headers which is less than ideal. If it's possible to easily add/get the headers that'd be a solution for me too.

Re: foo_sqlite

Reply #30
Although not impossible writefile is probably not suitable for your purpose, as you can only write one single value for each record to a different file with it.

But you can just do a "SELECT * FROM MediaLibrary" in the SQLite console, write click on the result and copy all. You will get the content of the result window in a CSV format (separator is a comma and field delimiter a single quote) copied to the clipboard.

You might need to increase the SQLite console row limit in "Preferences -> Advanced -> Tools ->SQLite viewer" to get all your data listed. In case you have a large library you also might consider to split the result sets to a reasonable size to not run into memory problems.

Re: foo_sqlite

Reply #31
As I said I did try what you suggest - but the headers aren't extracted with the results. Any easy way to grab the headers? Adding them manually will be a pain.

Re: foo_sqlite

Reply #32
Directly it's not possible, but although it is a bit of a hack and might not work anymore in the future, you can get the headers separately with this SQL:

Code: [Select]
select group_concat('''metadb_handle'',''||vtc_column_name||'''')
from mdb_VTableColumn
where vtc_table_type='0';

Re: foo_sqlite

Reply #33
Reply removed, was meant for SQL Playlist.

Re: foo_sqlite

Reply #34
Hi, I'm trying to write a component (my first attempt at one), that would involve running a few queries against your playlist virtual table.  I'm thinking that for my component to query the virtual table, I would need the pointer to your module (functions) MetaDB_Module, to use in the sqlite function sqlite3_create_module(), (I'm also learning SQLite).  Is my assumption correct?  Can you provide any guidance for how to enable my component to utilize what you've so wonderfully done?

My hope is to be able to get a few fields from/about the prior song and the next song, and then place them into Title Formatting variables.  Then I can display using the foo_osd component on a secondary monitor.  30 years ago I was a fair coder, so this project will give me something to do while I wait for covid to be over. 

Any advice is very much appreciated.

Re: foo_sqlite

Reply #35
Hi, I'm trying to write a component (my first attempt at one), that would involve running a few queries against your playlist virtual table.
I'm sorry, but this is not possible.
I'm thinking that for my component to query the virtual table, I would need the pointer to your module (functions) MetaDB_Module, to use in the sqlite function sqlite3_create_module(), (I'm also learning SQLite).
No, it would work a bit different, if it would be possible.
Is my assumption correct?  Can you provide any guidance for how to enable my component to utilize what you've so wonderfully done?
I've created a SDK to make such things possible, for example for foo_uie_sqlite_tree, but this SDK is not ready to be published and probably will never be.

Re: foo_sqlite

Reply #36
No, it would work a bit different, if it would be possible.
I've created a SDK to make such things possible, for example for foo_uie_sqlite_tree, but this SDK is not ready to be published and probably will never be.
Thank you for replying.  It was easy to write the queries to get the data I wanted for my project.  I guess instead, I'll have to figure out where/how foobar stores the running playlist internally, and how to get the data that way.  I'm guessing it will take a while for me to figure that all out.  FWIW, if you ever decide that you want someone to test your SDK, let me know.  ;)
Take care.

 
SimplePortal 1.0.0 RC1 © 2008-2021