SQL Update, Syntax problem

[Andre_H]

Hi folks,

i want to update - changing one filter criteria - in a bunch of auto-playlists, and instead of manually checking and editing 500+ playlists, i thought of doing this with the sql addon. basically, i want to change an rating-filter into an playlist-filter, but i guess i'm running in some kind of syntax error:

Code: Select all

UPDATE PLAYLISTS SET QueryDataJSON = replace(QueryDataJSON, "{""field"":""rating"",""operator"":"">="",""value"":""80""}", "{""field"":"",""operator"":""="",""value"":""592""}");

does not throw any errors, but also no changes to the playlists i would excpect an change.
can someone help me with the syntax, or another smart way to do the task?

[Andre_H]

so, i learned a few things about QueryData, QueryDataJSON and SQLLiteDatabase Browser.

Question: What is the reason that the filter criteria for (auto) playlists are maintained in both QueryData and QueryDataJSON? What is used for what and what can be safely changed using the SQL editor?

(Not a criticism, I just want to understand)

[whipdancer]

Here's what I've found so far... still working on a similar issue.

The SQL Editor uses

Code: Select all

app.db.getQueryResultAsync(sql)

I've been playing around with other

Code: Select all

app.db.<commands>

(what SQL Editor uses to do what it does), and have found that

Code: Select all

app.db.executeQueryAsync(sql)

will execute an update or delete command.

In my local environment (against a backup of my database), I modified the SQL Editor addon and was able to run update & delete commands successfully. I'm still working through the problem I'm hoping to solve, but it's the first real forward progress I've made so far.

[Ludek]

Hi Andre,
QueryDataJSON has been added later after QueryData as the JSON sctructure is DB independent and thus more portable in sense of syncing multiple databases..

But looking into code the Playlist object has setCriteriaJSON(JSON: string) method, but this method is public, but not published, so not accessible via JS code atm :-/

I'll add the published variant for the next version, but until then you need either edit the QueryData variant in DB (which is cumbersome) or via the SearchEditor component (as in PlaylistHeader)

[Andre_H]

QueryDataJSON has been added later after QueryData as the JSON sctructure is DB independent and thus more portable in sense of syncing multiple databases..

So, if i do changes only to QueryData (to edit filter and filter-sequences and maybe sort orders), i can ignore QueryDataJSON without any errors in MMW and MMA?