Skip to main content ↑ ↓

Notice

If you are using a Hotmail or Outlook email address, please change it now, as Microsoft is rejecting all email from our service outright.
Topic: foo_sqlite (Read 8790 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_sqlite

Reply #25 –
Quote from: kugel on
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 –
Quote from: kugel on
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 –
Quote from: fbuser on
Quote from: kugel on
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.
 
SimplePortal 1.0.0 RC1 © 2008-2021