Database format changed

Post a reply

Smilies
:D :) :( :o :-? 8) :lol: :x :P :oops: :cry: :evil: :roll: :wink:

BBCode is ON
[img] is ON
[url] is ON
Smilies are ON

Topic review
   

Expand view Topic review: Database format changed

Re: Database format changed

by Owyn » Tue Nov 10, 2009 7:13 am

FYI: I just now had a need to do some Genre cleanups myself.

I used Bex's Tagging Inconsistencies script to find low usage genres.

Navigate to Library->Files to Edit
Expand Tagging Inconsistencies node
Expand Genres node
Expand Few Tracks node

Bob's your uncle.
Now you have a review list and can edit track data/genres completely within MM.

Sort of off topic, but, it is best to look for a native or integrated (e.g. the Tagging Inconsistencies script) solution to an update problem.

If the list is a bit long, I just pick an arbritary point to stop cleaning up for a while and go back to the "node" later.

Re: Database format changed

by Bex » Mon Nov 09, 2009 2:28 pm

Thanks, Owyn!

An update of that script is actually in the workshop right now. We'll see when it gets released.

Re: Database format changed

by Owyn » Mon Nov 09, 2009 2:24 pm

Yep. The SQL Viewer is an invaluable tool.

I use it almost daily and at times extensively. Just about every SELECT I have written for a script has been unit-tested in the viewer. And, a lot of scripts are not required because I just need to see the llibrary data in a way that MM can't show me via the UI.

PS: Remember, you can't break anything with a SELECT, it just reads the data. UPDATE and DELETE however are for experienced use only, and preferably with a recent backup. :)

Re: Database format changed

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

Re: Database format changed

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.

Re: Database format changed

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);

            }

        }
    }
}

Re: Database format changed

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

Re: Database format changed

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????

Re: Database format changed

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

Re: Database format changed

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.

Re: Database format changed

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

Re: Database format changed

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.

Re: Database format changed

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!

Re: Database format changed

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.

Re: Database format changed

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?

Top