Database format changed

[Bex]

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.

[ZvezdanD]

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.

[Bex]

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!

[Bex]

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!

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)

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?

[ZvezdanD]

This is actually supported!

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

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.

[Bex]

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!

[ZvezdanD]

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.

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.

[MoDementia]

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]

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.

[XanderX]

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]

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]

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

Jiri

[urbandive]

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]

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.

[Bex]

If anyone wants to query or update MM's database then use my SQL_Viewer script which makes exactly that possible:
viewtopic.php?f=2&t=24841