Database format changed
Moderators: Gurus, Addon Administrators
Re: Database format changed
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
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
Re: Database format changed
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.
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.1 ● 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.3 ● Add Currently Playing/Selected Track(s) to Playlist 1.2
Re: Database format changed
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!
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!
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
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
Re: Database format changed
This is actually supported!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.
Code: Select all
SELECT Album AS Alb, Count(Songs.Album)
FROM Songs
GROUP BY Alb
Code: Select all
SELECT Count(Songs.Album) AS CountField FROM Songs GROUP BY CountField
Code: Select all
SELECT Count(Songs.Album) AS CountField FROM Songs GROUP BY Count(Songs.Album)
I've used it successfully with one level subquery. Do you have any example which doesn't work?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
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
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
Re: Database format changed
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):Bex wrote:This is actually supported!
Code: Select all
SELECT Count(Songs.Album) AS CountField FROM Songs ORDER BY CountField
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:Bex wrote:I've used it successfully with one level subquery. Do you have any example which doesn't work?
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.1 ● 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.3 ● Add Currently Playing/Selected Track(s) to Playlist 1.2
Re: Database format changed
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. 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
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
Re: Database format changed
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:The statement ... Is only applicable to compound queries, i.e. use of UNION, UNION ALL, INTERSECT, or EXCEPT
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.Bex wrote:Limit in Inlines/Subqueries works just fine!
● 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.1 ● 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.3 ● Add Currently Playing/Selected Track(s) to Playlist 1.2
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
Re: Database format changed
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
ie. blah blah blah limit ---> temp virtual table
blah blah blah from temp virtual table limit ---> temp virtual table2 etc
Re: Database format changed
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.1 ● 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.3 ● Add Currently Playing/Selected Track(s) to Playlist 1.2
Re: Database format changed
Thank you!acondios wrote:select Artist from Artists Order By Artist collate nocase desc
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
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????
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
IUNICODE is necessary for some properly working international support, i.e. for all characters of Unicode data set.
Jiri
Jiri
Re: Database format changed
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 :
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
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.
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
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
Re: Database format changed
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
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
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