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: foo_sqlite (Read 11512 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Re: foo_sqlite

Reply #50
Ok, thank you. I will try to reproduce this. I never stumbled upon such a problem, but this might be related to my usage pattern as I have a fixed set of playlist, which I usually don't change. The playlist index is a dynamic value which might change, if the playlists are reordered or if a playlist is removed.

Re: foo_sqlite

Reply #51
Back to SQL matters, this works but it makes me feel dirty. I'm parsing opus numbers and the like from the titles, and the current album includes two different numbering schemes. So I'm trying to construct three semicolon-separated fields for eventual splitting and correlating with one another: work, worknumber, and worksortorder. The last is a concatenation of the numeric portions of the other two, each of those numerals left-padded with zeros to take the general shape like '0000-0000'. However, if it is all zeroes, it should be nulled out. For example, a track with "Op. 4 No. 1, RV 383" will have "Op. 4; RV 383", "No. 1;", and "0004-0001; 0383-0000" in those three fields.

Yes, it gets more complicated than this. Some RV numbers on this album end in "a" or "b", and the next album has Fanna numbers with Roman numerals and slashes. Heavens, this is madness, but one thing at a time.

Like I said, this works, but I had to resort to a hairy eval() that makes me think there must be a better solution. Is there?

Base regex:
Code: [Select]
DROP TABLE IF EXISTS regexes;
CREATE TABLE         regexes (
                        name TEXT UNIQUE PRIMARY KEY,
                  expression TEXT
                             );

INSERT INTO regexes
     VALUES (
            'work'      -- opus and number
          , '(?i)('
         || '?(DEFINE)'
         || '(?<titlecase>(?:\b(?:op)(?=[\s\d\.])\.?))'
         || '(?<uppercase>(?:\b(?:bb|bwv|kv?|rv)(?=[\s\d\.])\.?))'
         || '(?<mixedcase>(?:\b(?:WoO|BuxWV)(?=[\s\d\.])\.?))'
         || '(?<posth>\s*(?:\bposth?(?=[\s\d\.])\.?)?)'
         || '(?<sp>(?:\s*))'
         || '(?<num>(?:\bn(?:o|º)(?=[\s\d\.])\.?))'
         || ')'
         || '(?:'
         || '(?:'
         || '(?<titlecase>(?&titlecase))'
         || '|(?<uppercase>(?&uppercase))'
         || '|(?<mixedcase>(?&mixedcase))'
         || ')'
         || '(?<posth>(?&posth))'
         || '(?<workspace>(?&sp))'
         || '(?<worknum>(\d+)?)'
         || '(?:'
         || '\s*(?<numbertext>(?&num))'
         || '\s*(?<numbernum>(\d+))'
         || ')?'
         || ')'
            )
SELECT queries referring to regex table
 
Code: [Select]
DROP VIEW IF EXISTS view_workfromtitle;
CREATE VIEW         view_workfromtitle AS
WITH   reg AS (
       SELECT expression ex
         FROM regexes
        WHERE name = 'work'
              )
SELECT regexp_replace(
                      reg.ex
                    , '\u$+{titlecase}\U$+{uppercase}\E$+{mixedcase}'
                   || '$+{posth}$+{workspace}$+{worknum}; '
                    , title
                    , 1
                      )
    AS work
     , replace(regexp_replace(
                              reg.ex
                            , '$+{numbertext} $+{numbernum}; '
                            , title
                            , 1
                              )
            , 'º', 'o'
               )
    AS worknumber
     , regexp_replace(reg.ex, 'nullif\(padl\(''$+{worknum}'',4,''0''\)||''-''||padl\(''$+{numbernum}'',4,''0''\),''0000-0000''\)||''; ''||', title, 1)
    AS worksortorder
     , title
  FROM Playlist_Updatable, reg
 WHERE playlist_index = active_playlist()
   AND item_is_selected;

SELECT substr(work, 1, length(work) - 2)
    AS work
     , substr(worknumber, 1, length(worknumber) - 2)
    AS worknumber
     , eval('SELECT '||substr(worksortorder, 1, length(worksortorder) - 8))
    AS worksortorder
     , title
  FROM view_workfromtitle
Sample output
work                worknumber      worksortorder           title
Op. 4; RV 383       No 1;           0004-0001; 0383-0000    Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, I. Allegro
Op. 4; RV 383       No 1;           0004-0001; 0383-0000    Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, II. Largo
Op. 4; RV 383       No 1;           0004-0001; 0383-0000    Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, III. Allegro
Op. 4; RV 279       No 2;           0004-0002; 0279-0000    Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, I. Allegro
Op. 4; RV 279       No 2;           0004-0002; 0279-0000    Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, II. Largo
Op. 4; RV 279       No 2;           0004-0002; 0279-0000    Concierto en Mi menor para Violín, Cuerdas y Continuo, Op. 4 Nº 2, RV 279, III. Allegro
Op. 4; RV 301       No 3;           0004-0003; 0301-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, I. Allegro
Op. 4; RV 301       No 3;           0004-0003; 0301-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, II. Largo
Op. 4; RV 301       No 3;           0004-0003; 0301-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 3, RV 301, III. Allegro assai
Op. 4; RV 357       No 4;           0004-0004; 0357-0000    Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, I. Allegro
Op. 4; RV 357       No 4;           0004-0004; 0357-0000    Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, II. Grave
Op. 4; RV 357       No 4;           0004-0004; 0357-0000    Concierto en La menor para Violín, Cuerdas y Continuo, Op. 4 Nº 4, RV 357, III. Allegro
Op. 4; RV 347       No 5;           0004-0005; 0347-0000    Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, I. Allegro
Op. 4; RV 347       No 5;           0004-0005; 0347-0000    Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, II. Largo
Op. 4; RV 347       No 5;           0004-0005; 0347-0000    Concierto en La Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 5, RV 347, III. Allegro
Op. 4; RV 316       No 6;           0004-0006; 0316-0000    Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, I. Allegro
Op. 4; RV 316       No 6;           0004-0006; 0316-0000    Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, II. Largo
Op. 4; RV 316       No 6;           0004-0006; 0316-0000    Concierto en Sol menor para Violín, Cuerdas y Continuo, Op. 4 Nº 6, RV 316a, III. Allegro
Op. 4; RV 185       No 7;           0004-0007; 0185-0000    Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, I. Largo - Allegro
Op. 4; RV 185       No 7;           0004-0007; 0185-0000    Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, II. Largo
Op. 4; RV 185       No 7;           0004-0007; 0185-0000    Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 7, RV 185, III. Allegro
Op. 4; RV 249       No 8;           0004-0008; 0249-0000    Concierto en Do Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, I. Allegro - Adagio - Presto - Adagio
Op. 4; RV 249       No 8;           0004-0008; 0249-0000    Concierto en Re menor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, II. Adagio
Op. 4; RV 249       No 8;           0004-0008; 0249-0000    Concierto en Re menor para Violín, Cuerdas y Continuo, Op. 4 Nº 8, RV 249, III. Allegro
Op. 4; RV 284       No 9;           0004-0009; 0284-0000    Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, I. Allegro
Op. 4; RV 284       No 9;           0004-0009; 0284-0000    Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, II. Largo
Op. 4; RV 284       No 9;           0004-0009; 0284-0000    Concierto en Fa Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 9, RV 284, III. Allegro
Op. 4; RV 196       No 10;          0004-0010; 0196-0000    Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, I. Spiritoso
Op. 4; RV 196       No 10;          0004-0010; 0196-0000    Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, II. Adagio
Op. 4; RV 196       No 10;          0004-0010; 0196-0000    Concierto en Do menor para Violín, Cuerdas y Continuo, Op. 4 Nº 10, RV 196, III. Allegro
Op. 4; RV 204       No 11;          0004-0011; 0204-0000    Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, I. Allegro
Op. 4; RV 204       No 11;          0004-0011; 0204-0000    Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, II. Largo
Op. 4; RV 204       No 11;          0004-0011; 0204-0000    Concierto en Re Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 11, RV 204, III. Allegro assai
Op. 4; RV 298       No 12;          0004-0012; 0298-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, I. Spiritoso e non presto
Op. 4; RV 298       No 12;          0004-0012; 0298-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, II. Largo
Op. 4; RV 298       No 12;          0004-0012; 0298-0000    Concierto en Sol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 12, RV 298, III. Allegro



Re: foo_sqlite

Reply #52
Like I said, this works, but I had to resort to a hairy eval() that makes me think there must be a better solution. Is there?
I don't think that it is worth the effort to find one or the other small structural optimization, if ever. Just imagine how the regular expression would look like, if you couldn't use named subexpressions. It would become completely unmaintainable. You should also not forget, that you are parsing text, which is following more or less loose rules. The more alternatives are possible the more complex will become the RE.

Re: foo_sqlite

Reply #53
I'm having trouble with a corner case in the regexp_replace function. Sometimes, as in this album, it is the style to write the abbreviation of "Number" as "Nº", with an ordinal symbol. I can write the replace string as something like '$+{number}', and it returns that string just fine, provided that the regular expression finds it. I can also make the first character lowercase with '\l$+{number}'. However, I cannot make the entire result lowercase, such as with '\L$+{number}\E'. In that instance, the rest of the string is truncated after that first "n". I'd like to be able to change the case to ease lookups in another table based on that value, which could be "no", "No", "NO", or whatever else comes up.

I see that there is a lower function provided by the unicode module, but I haven't found the right way to use that on the result. Is there an example of that somewhere?

I suppose I can just replace all the symbols with o's in the properties window, but I'd prefer to be able to take the titles as they are. Are there any other ways to do this?

Re: foo_sqlite

Reply #54
I see that there is a lower function provided by the unicode module, but I haven't found the right way to use that on the result. Is there an example of that somewhere?
No, but it's just lower(<string>), where <string> can be any string also the return value of regexp_replace(). Do you have a simplified example for which lower() is not working as expected?

Re: foo_sqlite

Reply #55
Simplified. Heh. Oh dear.  :)

Since this is a going into what will be a multivalue tag, the way I finally got these things working in general was to do my manipulations inside each regexp_replace call by wrapping the text of a subquery around the replace parameter. This takes place in a preliminary view, and then the UPDATE query (I'm using just a SELECT below) runs eval(SELECT … on the whole field of semicolon-separated subqueries (the semicolons being foobar2000 multivalue delimiters, not SQL statement terminators) and strips the final semicolon and space from the end.

And trying to put a simple example together, I seem to have gotten that mess working, or at least not failing in this way. So I guess I'll look for my problem elsewhere.  :)

But to finish the thought, here's what I'm doing. The lookup table is used to standardize the output from the variously-written title tags, and I wanted to make the lookup field lowercase for easier comparisons.
Code: [Select]
DROP TABLE IF EXISTS works;
CREATE TABLE         works (
                  id INTEGER PRIMARY KEY
            , lookup TEXT UNIQUE
            ,   work TEXT
            ,   sort TEXT
                     );

CREATE UNIQUE INDEX idx_workslookup
                 ON works(lookup);

INSERT INTO works(lookup, work, sort)
     VALUES ('op'   , 'Op.'   , 'OPUS')
          , ('no'   , 'No.'   , NULL  )
          , ('n°'   , 'No.'   , NULL  )
          , ('nº'   , 'No.'   , NULL  )
          , ('post' , 'posth.', NULL  )
          , ('posth', 'posth.', NULL  )
          , ('woo'  , 'WoO'   , 'WOOP')
          , ('bb'   , 'BB'    , 'BBAR')
          , ('sz'   , 'Sz.'   , 'SZOL')
          , ('bwv'  , 'BWV'   , 'BAWV')
          , ('buxwv', 'BuxWV' , 'BUWV')
          , ('k'    , 'KV'    , 'KOCH')
          , ('kv'   , 'KV'    , 'KOCH')
          , ('rv'   , 'RV'    , 'RYOM')
          , ('f'    , 'F.'    , 'FANN')
          ;

DROP VIEW IF EXISTS view_workfromtitle;
CREATE VIEW         view_workfromtitle AS
SELECT regexp_replace('(N(?:o|º))'
                    , '\(SELECT work FROM works WHERE lookup = lower\(''\1''\)\); '
                    , title
                    , 1
                      )
    AS simple
  FROM Playlist_Updatable
 WHERE playlist_index = active_playlist()
   AND item_is_selected;

SELECT eval('SELECT '||substr(simple, 1, length(simple) - 2))
    AS simple
  FROM view_workfromtitle

Re: foo_sqlite

Reply #56
Ok, with a simplified example I meant more something like this: ;D

Code: [Select]
SELECT regexp_replace('(N(?:o|º))'
                    , '\L\1\E'
                    , 'Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 Nº 1, RV 383a, I. Allegro');

The result is indeed this:
Code: [Select]
Concierto en Si bemol Mayor para Violín, Cuerdas y Continuo, Op. 4 n
And the explanation is like I thought quite simple: regexp_replace() is implemented without unicode support. I already added this for the next version a while ago and with this version the result is as expected.

Re: foo_sqlite

Reply #57
Had runtime errors and foobar2000 crashes that seemed to hinge on trying to update multivalue fields in Playlist_Updatable. Yes, I know that multivalue fields are not allowed in Playlist_Updatable, but I wanted to see if they could exist in Playlist or MediaLibrary and were simply treated as single value in Playlist_Updatable.

These fields had previously been single value, but I changed them to multivalue in the Playlist table, and used the first of them as an associated column of the other two. I also added the fields to the list in Preferences>Advanced>Display>Properties dialog>Multivalue fields. After making this change, my update query ran with no effect.

I then changed the fields back to single value. This is when all the runtime errors began. I made sure to remove the associated columns. More errors. I removed the tags from the advanced preferences. More errors. I rebuilt the Playlist and Playlist_Updatable tables. More errors.

I was only able to stop the errors and run the update query after removing the offending tags from the Playlist table and adding them again.

Is this the expected behavior? I would at some point like to make these tags multivalue and have them exist as such in MediaLibrary while being able to update those tags in new albums added to the collection. I know that Playlist_Updatable won't make them multivalue, but I was hoping that I could just split the tags in the properties window after I'm done updating them with SQL.

Re: foo_sqlite

Reply #58
Yes, I know that multivalue fields are not allowed in Playlist_Updatable
This is actually not correct. You can't use the "Split multivalue tag" functionality for an updatable virtual table as it simply does not make sense here.

Consider the following example for one track:

album: "The Best"
artist: "The Greatest"
genre: "Rock", "Pop" (multivalue field with two values)

Without setting "split multivalue tags" for genre, the result of SELECT * FROM MediaLibrary (or Playlist, of course) will return this result for this track:

Code: [Select]
album           artist               genre
-------------------------------------------------
The Best        The Greatest         Rock · Pop
" · " is the standard multivalue separator used by foo_sqlite (in opposite to just ";" for the properties dialog), but you can define it for each tag by yourself.

With setting "split multivalue tags" for genre you will get this result:
Code: [Select]
album           artist               genre
-------------------------------------------------
The Best        The Greatest         Rock
The Best        The Greatest         Pop
So you will get one record for each value of a multivalue field.

But updating a virtual table allows you set multiple values for a single tag. This SQL
Code: [Select]
UPDATE Playlist_Updatable
SET genre='Rock · Pop'
WHERE album='The Best'
    AND artist='The Greatest';
will update the genre tag with two values. You just need to use the multivalue separator, which is defined for the relevant tag.

Is this the expected behavior?
Of course, not. Crashing is never an exprected behavior.  Thanks for the detailed error description. I will try to reproduce the problem and I think I have to disallow somehow what you did. Besides this, could you please also post one of the crash reports (txt and dmp file) as it might give some more information?

Re: foo_sqlite

Reply #59
I don't seem to have any crash reports; the folder is empty.

Re: foo_sqlite

Reply #60
But updating a virtual table allows you set multiple values for a single tag. This SQL
Code: [Select]
UPDATE Playlist_Updatable
SET genre='Rock · Pop'
WHERE album='The Best'
    AND artist='The Greatest';
will update the genre tag with two values. You just need to use the multivalue separator, which is defined for the relevant tag.

I am having trouble getting this to work as you describe. If I create a tag in Playlist called test, select Split multivalue tag and rebuild Playlist_Updatable, my update query won't do anything. If I do not select Split multivalue tag, then it will write the unsplit value to to the tag. If I create test with that checkbox selected first, and then edit it to unselect it (and rebuild Playlist_Updatable), running the update query will crash foobar without generating a crash report. I've added the crash dialog in case there's anything to be gleaned from that.

Code: [Select]
UPDATE Playlist_Updatable
   SET (
       test
       ) = (
       '#1 · #2'
       )
 WHERE Playlist_Updatable.playlist_index = active_playlist()
   AND Playlist_Updatable.item_is_selected

Code: [Select]
SELECT    title
        , test
     FROM Playlist
    WHERE playlist_index = active_playlist()
      AND item_is_selected

Re: foo_sqlite

Reply #61
I am having trouble getting this to work as you describe. If I create a tag in Playlist called test, select Split multivalue tag and rebuild Playlist_Updatable, my update query won't do anything. If I do not select Split multivalue tag, then it will write the unsplit value to to the tag. If I create test with that checkbox selected first, and then edit it to unselect it (and rebuild Playlist_Updatable), running the update query will crash foobar without generating a crash report.
First of all, please don't touch "Split multivalue tags", when you are using an updatable virtual table. Just leave it empty. Please try writing multi value tags as described by me on a clean installation without any experiments of selecting or unselecting the "split multivalue tags" checkbox. If this is still not working, please post screenshots with all relevant settings or maybe better post the files foo_sqlite.db and foo_sqlite.dll.cfg from your configuration folder.

Re: foo_sqlite

Reply #62
OK, fresh portable install of foobar2000 1.6.6 with SQLite viewer added. I dragged in a track and ran this script. I did not touch Split multivalue tag:D

Code: [Select]
DROP TABLE IF EXISTS Playlist_Updatable;
CREATE VIRTUAL TABLE Playlist_Updatable
               USING MetaDB_Module(no_multivalue_split,add_defaults,playlist);

UPDATE Playlist_Updatable
   SET (
       genre
       ) = (
       'Rock · Pop'
       )
 WHERE Playlist_Updatable.playlist_index = active_playlist()
   AND Playlist_Updatable.item_is_selected

The genre tag is filled in the properties view with "Rock; Pop", so that looks like it should, I'm guessing.

However, back in the SQLite console, this query returns just one record with "Rock · Pop".

Code: [Select]
SELECT    title
        , genre
     FROM Playlist a
    WHERE playlist_index = active_playlist()
      AND item_is_selected

I need this to be two records with the genre split. Having done whatever I need to do to accomplish that, I need to then be able to update subsequent files with multiple values in genre.

I have done nothing else since, so the installation is still unsullied for any further instructions. :D

Re: foo_sqlite

Reply #63
I need this to be two records with the genre split. Having done whatever I need to do to accomplish that, I need to then be able to update subsequent files with multiple values in genre.

I have done nothing else since, so the installation is still unsullied for any further instructions. :D
Ok, then you need to add a second genre column (e.g. genremv) to the playlist table which now has "split multivalue tags" checked like this:

Type: Tag
Name: genremv
Tag
   Name: genre
   Split multivalue tag: checked

All other column settings remain unchanged.

And your query would be:

Code: [Select]
SELECT    title
        , genremv
     FROM Playlist a
    WHERE playlist_index = active_playlist()
      AND item_is_selected

So you will have two genre columns: "genre" for updating and "genremv" for splitting multi value tags. There is no need to rebuild the virtual playlist. It's done automatically.

Re: foo_sqlite

Reply #64
Well all right, that works. :D Thank you for the help. I was wondering what that second "Name" box was used for, and now I know that this is one application for it. I would not have guessed this solution though. There is still a lot I have to learn about SQL in general.

 
SimplePortal 1.0.0 RC1 © 2008-2021