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: [fb2k v2] SQLite Utilities (foo_sqlite) (Read 55654 times) previous topic - next topic
0 Members and 2 Guests 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.

Re: foo_sqlite

Reply #65
Am I right in thinking that I can update tags from within a playlist using foo_sqlite? I'd like to be able to create an album_duration tag that will contain the duration of the album the tracks belong to.
I've created a new portable install, added a folder of songs to the media library, created a playlist with some tracks in it and selected a few of them, and then I've attempted to run this code from the above post and all I see is 'Executing SQL' and then foobar crashes.

I know SQL but I'm struggling to understand how to use and reference the playlist modules.

Any help would be much appreciated.

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

Re: foo_sqlite

Reply #66
I had created a new, clean, portable install, but then - on autopilot I guess - added to that environment my other components, which are obviously to blame for causing it to crash!

Re: foo_sqlite

Reply #67
Thanks for the feedback. I can confirm, that the SQL, which you are using, normally should not crash the application. I can only guess, if foo_sqlite is not causing the crash, that the tag update is causing another component to crash.

But if you don't mind, it would be good to verify, that the crash is caused by another component. In case of a crash you should get a crash report, which in most cases can identify the crashing component. If you didn't delete the relevant portable installation, you should have the crash report in the profile folder of this installation. Otherwise it should be probably not difficult to recreate the crash scenario.

So, if you have the crash report available, could you please post it here? TIA.

Re: foo_sqlite

Reply #68
But if you don't mind, it would be good to verify, that the crash is caused by another component. In case of a crash you should get a crash report, which in most cases can identify the crashing component. If you didn't delete the relevant portable installation, you should have the crash report in the profile folder of this installation. Otherwise it should be probably not difficult to recreate the crash scenario.
Hi fbuser,

I had a quick look through the .txt file at the time and nothing jumped out at me, and I've just removed what I thought could be causing the problem (foo_tagbox) but it's not that either, so would appreciate you having a look for me. I've used foo_sqlite for lots of other purposes just not writing, so I guess it's that causing the problem.

The dump files are attached, and thanks for the invaluable plugin!

Re: foo_sqlite

Reply #69
In the end it turned out to be the foo_sqlite.db that was causing the problem, no doubt it's something I did ages ago whilst playing with it initially.
I thought it would be something to do with the definition of the Playlist or MediaLibrary tables, but I can't seem to set them to defaults.
With a clean install, I added album_duration to the Playlist definition, ran a normal select query and closed foobar down. I went into .\profile\configuration and removed foo_sqlite.db and foo_sqlite.dll.cfg, and restarted foobar.
I then added the component back into foobar, restarted, and went back to the Playlist definition and album_duration is still there. I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?

I've attached the foo_sqlite.db just in case it's useful.


Re: foo_sqlite

Reply #70
Thank you for your efforts. I already saw from the crash log, that the problem is coming from foo_sqlite. I will look into it. If it is not already fixed for the next, partially internally rewritten, version, I will do it, if I can reproduce the issue.

I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?
Yes, in your files. Besides some basic columns "Add defaults" adds also all tags it can find in your files as columns.

Re: foo_sqlite

Reply #71
I then went through and removed each column in the Playlist and MediaLibrary definition, clicked 'Add defaults' and it adds album_duration back again.
I assume it's stored elsewhere and maybe they're not to blame?
Yes, in your files. Besides some basic columns "Add defaults" adds also all tags it can find in your files as columns.
I hadn't considered that. It's not really a default then, but I supposed it's what 99% of people want so that's fine at least it explains it.

I have one further question if you will. In the MetaDb_Module documentation it states "The UPDATE statement on such a table will only prepare the update of the tags. The actual update will be done with committing the changes. This means reverting changes with a rollback is not possible."

Are you referring to within a single execution here i.e. Should I add a COMMIT to the code, or is the successful run of the code sufficient for the tag changes to be committed (I've read that SQLite autocommits by default unless you declare a BEGIN statement).

Thank you for your efforts. I already saw from the crash log, that the problem is coming from foo_sqlite. I will look into it. If it is not already fixed for the next, partially internally rewritten, version, I will do it, if I can reproduce the issue.
I'm glad to see the plugin is still alive and kicking!

Re: foo_sqlite

Reply #72
Should I add a COMMIT to the code
No, it's not necessary. Furthermore adding a COMMIT without a BEGIN would throw an error.

is the successful run of the code sufficient for the tag changes to be committed (I've read that SQLite autocommits by default unless you declare a BEGIN statement).
Autocommit mode is exactly the reason, why you don't need to supply a COMMIT by yourself.

Re: foo_sqlite

Reply #73
Hi fbuser,

Last question I promise :-)
Is there any way of changing the session collation type, or a quick way of changing the collation of all the variables in the MediaLibrary and Playlist table to BINARY?
I use this component for various QA checks so it'd be nice to have the default be "correct" (it's my first time using a database with such a default).

Thanks again.

Re: foo_sqlite

Reply #74
Is there any way of changing the session collation type
No, because there is no session collation type. The collation is always bound to the table columns and it defaults indeed to BINARY. So, if you create tables of your own, the collation of all columns is BINARY.

quick way of changing the collation of all the variables in the MediaLibrary and Playlist table to BINARY?
Officially: No. Inofficially: The collation types for the MediaLibrary and Playlist table columns are stored in the database. So, if you are familiar with SQL and databases, it is probably quite easy to find out, what to do. But, of course, don't do this without a backup.

But you can also always override the collation of a column explicitely in the SQL statement, e.g

Code: [Select]
SELECT * FROM MediaLibrary WHERE album COLLATE BINARY BETWEEN 'a' AND 'z';
to list all albums which start with a lowercase ascii letter

or
Code: [Select]
SELECT * FROM MediaLibrary ORDER BY album COLLATE BINARY;
to sort the result using the BINARY collation.

it's my first time using a database with such a default.
As already said above, the default for the database is BINARY and it is not possible to change it.