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 11075 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.

 
SimplePortal 1.0.0 RC1 © 2008-2021