Database format changed

This forum is for questions / discussions regarding development of addons / tweaks for MediaMonkey.

Moderator: Gurus

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: Database format changed

Post by Bex » Tue Jan 27, 2009 2:51 pm

:lol: Yes, but it actually has some features which Jet Sql lacked. Like Replace(), Group_Concat() and and especially CREATE IF NOT EXISTS! But others are missing for sure.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

ZvezdanD
Posts: 3094
Joined: Thu Jun 08, 2006 7:40 pm

Re: Database format changed

Post by ZvezdanD » Tue Jan 27, 2009 4:13 pm

Don't forget on DISTINCT with aggregate functions, e.g. Count(DISTINCT Songs.Album). One more thing which I really missed in Jet: you could use aliases defined in SELECT part into WHERE/GROUP BY/ORDER BY expressions, e.g. SELECT Count(Songs.Album) AS CountField FROM Songs GROUP BY CountField.

However, I really hate that it don't allow several LIMIT statements in one complex query (with sub-queries), even more than its lack of functions.

By the way, Group_Concat() is great, but it would be nice if it has opposite Split function as well.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: Database format changed

Post by Bex » Tue Jan 27, 2009 4:48 pm

Agreed!

If you know C it is possible to write your own functions (Loadable Extensions) and add them to MM:
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
http://sqlite.org/contrib (see extension-functions.c)

I have no knowledge about C but if you do, or anyone else, it would be great if a "MM-Functions pack" could be added. I have a few wishes! :wink:
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: Database format changed

Post by Bex » Thu Jan 29, 2009 1:41 pm

ZvezdanD wrote:One more thing which I really missed in Jet: you could use aliases defined in SELECT part into WHERE/GROUP BY/ORDER BY expressions, e.g. SELECT Count(Songs.Album) AS CountField FROM Songs GROUP BY CountField.
This is actually supported! :D

Code: Select all

SELECT Album AS Alb, Count(Songs.Album) 
FROM Songs 
GROUP BY Alb
The reason why this doesn't work:

Code: Select all

SELECT Count(Songs.Album) AS CountField FROM Songs GROUP BY CountField
is that you can't GROUP BY an Aggregate function. I.e. this gives the same error:

Code: Select all

SELECT Count(Songs.Album) AS CountField FROM Songs GROUP BY Count(Songs.Album)
ZvezdanD wrote:However, I really hate that it don't allow several LIMIT statements in one complex query (with sub-queries), even more than its lack of functions
I've used it successfully with one level subquery. Do you have any example which doesn't work?
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

ZvezdanD
Posts: 3094
Joined: Thu Jun 08, 2006 7:40 pm

Re: Database format changed

Post by ZvezdanD » Thu Jan 29, 2009 4:27 pm

Bex wrote:This is actually supported! :D
I think that you didn't understand. I mentioned this as an advantage of the SQLite against MS Jet, not as its deficiency. I wrote example which I haven't tested, just as an example what I mean. Here is another example with the alias for the aggregate function which works correctly (if you turn on the option "Allow editing of SQL queries" in the Magic Nodes script, you would see that I am using such aliases whenever it is possible):

Code: Select all

SELECT Count(Songs.Album) AS CountField FROM Songs ORDER BY CountField
Bex wrote:I've used it successfully with one level subquery. Do you have any example which doesn't work?
I cannot remember exactly, but I tried one LIMIT for the main query and another for the sub-query. Here is what the SQLite site says:
The limit is applied to the entire query not to the individual SELECT statement to which it is attached.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: Database format changed

Post by Bex » Thu Jan 29, 2009 4:58 pm

Ah, got it!

Re. limit.
I'm getting it to work correctly with inlines. e.g.
Select * from songs where id in
(select id from songs order by id desc limit 5)
limit 2

The statement:
In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached.

Is only applicable to compound queries, i.e. use of UNION, UNION ALL, INTERSECT, or EXCEPT
Limit in Inlines/Subqueries works just fine!
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

ZvezdanD
Posts: 3094
Joined: Thu Jun 08, 2006 7:40 pm

Re: Database format changed

Post by ZvezdanD » Thu Jan 29, 2009 6:27 pm

Bex wrote:The statement ... Is only applicable to compound queries, i.e. use of UNION, UNION ALL, INTERSECT, or EXCEPT
Well, even such limitation is very serious. Why should I not be able to write SELECT ... LIMIT ... UNION SELECT ... LIMIT ...? By the way, I forgot to mention one more limitation: "There may be only a single ORDER BY clause at the end of the compound SELECT.", so I could not write SELECT ... ORDER BY ... UNION SELECT ... ORDER BY ... Very unpleasant if you ask me.
Bex wrote:Limit in Inlines/Subqueries works just fine!
I am not saying that sub-queries could not have LIMIT. I am saying that you cannot have several LIMITs in same query. Because of that I cannot get correct values for Statistic in Magic Nodes when I have several nested nodes, each with own Top qualifier. It was a year ago when I tried that and cannot remember how problematic mask looked exactly (maybe it was in combination with the Min/Max Tracks qualifier), but when I find a time I'll return to this problem. Probably I did something wrong, but I think that this was a consequence of mentioned limitations.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

MoDementia
Posts: 1321
Joined: Thu Jun 15, 2006 3:26 pm
Location: Geelong, Victoria, Australia

Re: Database format changed

Post by MoDementia » Fri Jan 30, 2009 1:12 am

I asked about this in the sqlite news group and they replied that I should send the results to a temp virtual table? and run seconday etc from there.

ie. blah blah blah limit ---> temp virtual table
blah blah blah from temp virtual table limit ---> temp virtual table2 etc

ZvezdanD
Posts: 3094
Joined: Thu Jun 08, 2006 7:40 pm

Re: Database format changed

Post by ZvezdanD » Fri Jan 30, 2009 5:34 am

First of all, after reading http://www.sqlite.org/cvstrac/wiki?p=VirtualTables I am not sure if virtual tables could be created with a VBSCript. I suppose that instead of virtual tables could be used temporary created tables, but anyway there are several reasons why I could not use virtual/temporary tables. Most important to me is that I am dynamically creating queries during a run-time, consisting of/containing user's entered queries. My two scripts have such approach: the Magic Nodes with the Filter qualifier (although not full SELECT query, it contains only WHERE part) and the RegExp Find & Replace with its custom SQLQuery function (which user could type into the Replace With edit box). Both of them execute user's defined queries using a single SDB.Database.OpenSQL method. So, my only option is to work with a single query, not matter how it is complex.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

XanderX

Re: Database format changed

Post by XanderX » Fri Jun 05, 2009 2:48 am

acondios wrote:select Artist from Artists Order By Artist collate nocase desc
Thank you!

My 1$, same for column names in 'WHERE' statements:

SELECT Artist FROM Artists WHERE Artist COLLATE NOCASE = 'MOBY' ORDER BY Artist COLLATE NOCASE desc

P.S. lol@monkeys

Guest

Re: Database format changed

Post by Guest » Thu Oct 15, 2009 3:44 am

Hello, I have messing around this problem, and I found a simple way to workaround the collation problem: Just recreating the tables without the collation option, dropping the old table and renaming the new. After this, we can update and reorder using ODBC and any other SQLite tool.

I have been testing MM behaviour after this change and have not observed anything weird. Anyone knows the consequences of not having the IUNICODE collation in these fields????

jiri
Posts: 5394
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Re: Database format changed

Post by jiri » Thu Oct 15, 2009 3:20 pm

IUNICODE is necessary for some properly working international support, i.e. for all characters of Unicode data set.

Jiri

urbandive
Posts: 2
Joined: Sat Jun 13, 2009 3:41 pm

Re: Database format changed

Post by urbandive » Sat Nov 07, 2009 4:33 am

Hi folks,

I have been working on my music genres and it was a bit of a headache to do it manually in MM because I had thousands of them. So I decided to get them, sort / clean in Excel and then flag the genres that I needed to delete.

I can manage C#, so I downloaded SQLLite, but encountered this issue with IUNICODE collation, and was very disappointed at not being able to transfer my changes to MediaMonkey after so much work.

Luckily, by surfing the web, I managed to put together this piece of code which worked wonderfully. Thought I'd share it around in case someone else needs it :

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQL=System.Data.SQLite;

namespace MediaMonkeyDB
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Int32> listIDGenresToDelete = new List<int> { 17, 67, 214, ...};

            SQL.SQLiteConnection cn = new System.Data.SQLite.SQLiteConnection(@"data source=C:\Temp\MediaMonkey\MM.DB");
            cn.Open();
            
            SQL.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(cn);

            try
            {
                foreach (Int32 IDGenre in listIDGenresToDelete)
                {
                    cmd.CommandText = string.Format("DELETE FROM [GenresSongs] WHERE [IDGenre] = {0};", IDGenre);
                    cmd.ExecuteNonQuery();
                }
                Console.WriteLine("Finished");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Console.ReadLine();
            }


        }

        /// <summary>

        /// User-defined collating sequence.

        /// </summary>

        [SQL.SQLiteFunction(Name = "IUNICODE", FuncType = SQL.FunctionType.Collation)]

        class IUNICODE : SQL.SQLiteFunction
        {

            public override int Compare(string param1, string param2)
            {

                return String.Compare(param1.ToLower(), param2.ToLower(), true);

            }

        }
    }
}

Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: Database format changed

Post by Owyn » Sat Nov 07, 2009 7:25 pm

Ummm.
The good news is that you got around the COLLATE IUNICODE.
The bad news is that you have corrupted your MM library database.

Songs.Genre still contains the original genre text and later property edits can yield some bizarre results.

Edit(1): The genres will have only half disappeared. I think that if you use Basic Search on Genre it will not find the Songs, but, if you use searchbar "genre:string" it will find them. WHERE LIKE on Genres versus WHERE MATCH on SongsText.

Edit(2): A complete fix for your problem will also require re-tagging your source music files. Their Genre tag also contains the text.
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: Database format changed

Post by Bex » Mon Nov 09, 2009 2:07 pm

If anyone wants to query or update MM's database then use my SQL_Viewer script which makes exactly that possible:
http://mediamonkey.com/forum/viewtopic.php?f=2&t=24841
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

Post Reply