Magic Nodes 4.2 w/ 380 masks & real GUI (2011-07-01)[MM2+]

[mattisse]

Great to see that there is further development of Magic Nodes. Two thumbs up!

Also got a request:
What I'm really missing is the all qualifier, which is not working in 1.3b. It's suppossed to add an "All" node under a Magic node.

Is there any chance to reimplement that qualifier. That'd be really cool.

[Bex]

When nobody wants to help me, I need to help myself I found a solution to replace InStr function which was near of my eye sight, but I was blind to see it, even when I already implemented similar approach with left of and right of qualifiers. If someone is interested, here is a InStr(start, string1, string2) with SQLite functions:

Code: Select all

case
when replace(substr(string1, start, 9999), string2, zeroblob(1)) = substr(string1, start, 9999) then 0
when length(string2) = 0 then start
else length(substr(string1, 1, start - 1) || replace(substr(string1, start, 9999), string2, zeroblob(1))) + 1 
end

Thank you, thank you, thank you!
I have actually tried to help you and was thinking that Replace & Substr together with Case could be the way to go but instead of trying, I spent the time googling for a solution. Now I have to figure out how your code works how efficient it is.

I'm trying to convert my scripts to MM3 but the lack of string manipulation functions makes it, if not impossible, a very difficult task. I'm struggling a bit with the date/time functions as well but I think I will solve them.

/Bex

[nojac]

What I'm really missing is the all qualifier, which is not working in 1.3b. It's suppossed to add an "All" node under a Magic node.

I am not sure why you want the "All" subnode. It seems to be default to show all tracks when selecting the main node (may be switched off using |show tracks:no) To me this is a better system than a separate "all"-subnode.

[ZvezdanD]

What I'm really missing is the all qualifier, which is not working in 1.3b. It's suppossed to add an "All" node under a Magic node.

Is there any chance to reimplement that qualifier.

Well, I'll try to fix this. But honestly, I don't like such node at all. No one music catalog software has such approach within a tree list. In every software which I tried, you get the same result when you click on the parent node. So, if you has an Artists node, and if you click on it, you should get all songs from all artists. Now, if you expand Artist node and click on some artist node, let say The Beatles, you should get all songs from them.

Could someone tell me why MM approach with All node is better then that?

[Bex]

Hmm, I get "SQL Error: No such function: zeroblob"

Btw, what is zeroblob and what does it do?

[ZvezdanD]

Now I have to figure out how your code works how efficient it is.

I'm trying to convert my scripts to MM3 but the lack of string manipulation functions makes it, if not impossible, a very difficult task. I'm struggling a bit with the date/time functions as well but I think I will solve them.

Well, if there is not another solution, then this is most efficient Number of date/time functions are limited, but I think that they are more powerful then MS Jet's, so I suppose that you shouldn't have a problems with them. Lack of string/number functions is more critical.

In my opinion, MM developers should implement all MS Jet related functions as additional code, or at least most needed. They should implement even those functions which have different syntax in SQLite, and in this case most of VB scripts wouldn't needed to be modified (except those which manipulate with non-existing fields).

Hmm, I get "SQL Error: No such function: zeroblob"

Btw, what is zeroblob and what does it do?

zeroblob(N) - Return a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O.

It is relatively new (I don't know exactly from which version of SQLite is introduced). It is working in MM, as well as in sqlite3.exe, official SQLite command line utility. Where are you trying query with this? Most of SQLite GUIs don't support that. My recommendation is SQLite Spy.

[mattisse]

What I'm really missing is the all qualifier, which is not working in 1.3b. It's suppossed to add an "All" node under a Magic node.

Is there any chance to reimplement that qualifier.

Well, I'll try to fix this. But honestly, I don't like such node at all. No one music catalog software has such approach within a tree list. In every software which I tried, you get the same result when you click on the parent node. So, if you has an Artists node, and if you click on it, you should get all songs from all artists. Now, if you expand Artist node and click on some artist node, let say The Beatles, you should get all songs from them.

Could someone tell me why MM approach with All node is better then that?

You are right. An All node wouldn't be necessary if the parent node showed showed only the songs or which the filters apply. Unfortunately under Magic Node, the parent node shows all songs in my library, not only the filtered songs.

What I'd like to do is to filter all tracks by artists from which I have 5 or more songs in my library:

min. 5 tracks|SQL filter:Songs.SongPath LIKE '%Musik_Songs%'\<Artist|min tracks:5|all:yes|statistic:count(all)>

I want to apply a different auto-sort mask to those songs. But right now, I have to click every single artist node, select the songs and apply auto-sort.

[ZvezdanD]

Unfortunately under Magic Node, the parent node shows all songs in my library, not only the filtered songs.

What I'd like to do is to filter all tracks by artists from which I have 5 or more songs in my library:

min. 5 tracks|SQL filter:Songs.SongPath LIKE '%Musik_Songs%'\<Artist|min tracks:5|all:yes|statistic:count(all)>

I am not sure that I understand what you want. If you apply a global filter, the parent node would show only the filtered songs. But, a condition which you are mentioning (min tracks:5) is not global, because you wrote that on the second node level. If you want to apply this condition to the node on the first level, you should integrate it into the global filter. Something like this:

Code: Select all

min. 5 tracks|SQL filter:Songs.SongPath LIKE '%Musik_Songs%' AND (SELECT COUNT(*) FROM Songs GROUP BY Songs.Artist) >= 5\<Artist|statistic:count(all)>

Or even better (with regard to multi-artist):

Code: Select all

min. 5 tracks|SQL filter:Songs.SongPath LIKE '%Musik_Songs%' AND (SELECT COUNT(*) FROM Songs INNER JOIN ArtistsSongs ON Songs.ID = ArtistsSongs.IDSong INNER JOIN Artists ON ArtistsSongs.IDArtist = Artists.ID GROUP BY Artists.ID) >= 5\<Artist|statistic:count(all)>

I want to apply a different auto-sort mask to those songs. But right now, I have to click every single artist node, select the songs and apply auto-sort.

I am sorry, but I don't know what is an auto-sort. Please, could you be more specific?

[Bex]

Well, if there is not another solution, then this is most efficient Number of date/time functions are limited, but I think that they are more powerful then MS Jet's, so I suppose that you shouldn't have a problems with them. Lack of string/number functions is more critical.

I'm struggling with FirstDayOfWeek settings or rather the lack of it. Sundays is always 0 but the weeks always starts on a Monday. It seems that it isn't possible to change this behaviour or maybe I'm just missing something obvious? I miss SQL Jet's FORMAT Function.

In my opinion, MM developers should implement all MS Jet related functions as additional code, or at least most needed. They should implement even those functions which have different syntax in SQLite, and in this case most of VB scripts wouldn't needed to be modified (except those which manipulate with non-existing fields).

Only those that is missing. I'd like InstrRev and Val for example.

zeroblob(N) - Return a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O.

It is relatively new (I don't know exactly from which version of SQLite is introduced). It is working in MM, as well as in sqlite3.exe, official SQLite command line utility. Where are you trying query with this? Most of SQLite GUIs don't support that. My recommendation is SQLite Spy.

Yeah, read that too but i don't even know what a blob is..

Thanks for the SQLite Spy tip. Works great!

[ZvezdanD]

I'm struggling with FirstDayOfWeek settings or rather the lack of it. Sundays is always 0 but the weeks always starts on a Monday. It seems that it isn't possible to change this behaviour or maybe I'm just missing something obvious? I miss SQL Jet's FORMAT Function.

Yeah, there are too many missing functions. I already talked about that in another thread. But I think that most of date/time computing can be replaced with strftime. Could you tell me which is your expression with FirstDayOfWeek function?

Only those that is missing. I'd like InstrRev and Val for example.

Well, if they want to keep existing base of user scripts, they should replace all functions, not only missing. In that way, users don't need to replace, let say, Left, Right or Mid with substr, and something similar.

Yeah, read that too but i don't even know what a blob is..

You should consider about zeroblob(N) as replacement for String(N, Chr(0)). So, replace(string1, string2, zeroblob(1)) has same effects as Left(string1, InStr(1, string1, string2) - 1), because 0x00 is treated as end of string. In that way I implemented left of qualifier where string2 is a delimiter for parsing.

[mattisse]

I am sorry, but I don't know what is an auto-sort. Please, could you be more specific?

The proper English term would have been "auto-organize". Sorry about that.

And thanks for hinting me in the right direction. I'm getting pretty close to what I want with the following node:

Code: Select all

min. 5 tracks|SQL filter: Songs.IDArtist IN (SELECT Songs.IDArtist  FROM Songs WHERE Songs.SongPath LIKE '%Musik_Songs%' GROUP BY Songs.IDArtist  HAVING Count (1) >= 5)\<Artist|statistic: count(all)>

The query is a bit slow and it shows all songs of an artist independent of the songpath as long as min. 5 songs were found in the queried path. But I can work around that.

[Bex]

@ZvezdanD
There is none!
With JET sql I can define how the weeknr should be calculated in respect of firstdayofweek.

Code: Select all

Format(Date,'ww','firstdayofweek')

In SQLite I can use:

Code: Select all

strftime('%W',Date)

But it always assume that the firstdayofweek is Monday. I want to be able to change that.


Thanks for the explanation of zeroblob. It makes sense now and your formula is very clever!

[nojac]

Matisse: I tried your mask, but it came up as "not valid" on my system (MM3) And ZvezdanD's 2. resulted in an empty node.

This is my mask for albums with min 5 tracks (Based on a suggestion by Teknojnky):

EDIT: DELETED Worked in Alpha, not Beta (see later in thread)

[ZvezdanD]

There is none!
With JET sql I can define how the weeknr should be calculated in respect of firstdayofweek.

Code: Select all

Format(Date,'ww','firstdayofweek')

In SQLite I can use:

Code: Select all

strftime('%W',Date)

But it always assume that the firstdayofweek is Monday. I want to be able to change that.

If I understand correctly, you want to calculate week of year with regard of firstdayofweek. Please, try the following code, and let me know if it works:

Code: Select all

strftime('%W', Date, 
cast(case when strftime('%w', Date, 'start of year') + firstdayofweek = 0 then 0
when strftime('%w', Date, 'start of year') = 1 and firstdayofweek < 2 then 1
when firstdayofweek > strftime('%w', Date, 'start of year')
and strftime('%w', 'Date, 'start of year') > 1 then 15
else 8 end - firstdayofweek as text) || ' days')

Here you have two arguments, Date and firstdayofweek. firstdayofweek should have value as in SQLite, e.g. 0 - Sunday, 1 - Monday... This is contrary to MS Jet value: 1 - Sunday, 2 - Monday... So, you should decrement your firstdayofweek argument by 1 before this calculation.

Thanks for the explanation of zeroblob. It makes sense now and your formula is very clever!

Thanks

[ZvezdanD]

And thanks for hinting me in the right direction. I'm getting pretty close to what I want with the following node:

Code: Select all

min. 5 tracks|SQL filter: Songs.IDArtist IN (SELECT Songs.IDArtist  FROM Songs WHERE Songs.SongPath LIKE '%Musik_Songs%' GROUP BY Songs.IDArtist  HAVING Count (1) >= 5)\<Artist|statistic: count(all)>

The query is a bit slow and it shows all songs of an artist independent of the songpath as long as min. 5 songs were found in the queried path. But I can work around that.

You should told me that you are trying MM2. If I undestand correctly, you need to display only songs inside of Musik_Songs by artists which have more than 5 songs. Please, try the following mask, and let me know if it works:

Code: Select all

min. 5 tracks|SQL filter:Songs.SongPath LIKE '%Musik_Songs%' AND Songs.IDArtist IN (SELECT Songs.IDArtist  FROM Songs GROUP BY Songs.IDArtist  HAVING Count (1) >= 5)\<Artist|statistic:count(all)>

Of course this query is a bit slow. There we have a sub-query inside of another sub-query (invisible in mask). One nested sub-query is by default slow, but two of them... MM3 version would have only one sub-query, so I suppose it will run faster. This is a consequence of the new MM3 design with modified AddTracksFromQuery.