Query observers

[mbektimirov]

Is it possible to observe query changes like in lovefiled? https://github.com/google/lovefield/blob/master/docs/spec/04_query.md#46-observers

[mathiasrw]

Hi

That is a great idea. It's not supported in the moment - directly.

I am thinking if it could be done indirectly with a trigger for a stored procedureish approach combined with a user defined SQL function that could be created with a fixed call back.

Update: I guess the question here is if you can give the library a query + a function to call then the output of that query changes. No that is not possible at the moment.

I was thinking that observing changes to tables might do the trick for you but I realise this is a very different concept. I will close the issue. Please reopen if you feel I did not answer your question.

[mbektimirov]

Thanks for the update, @mathiasrw. One step to observable queries could be query results caching. Is it possible to have equal references to unchanged data?

db.exec('select * from asset') === db.exec('select * from asset') // now returns false

[mathiasrw]

hmm. I see.

So on every insert/update/delete thats not part of a transaction one could run the same query and compare it to last time it was ran.

Interesting...

I bit consuming in many cases though... one might want to specify exactly what tables to observe.

Array.observe could maybe be used on the table content arrays

The Array.observe() method is used for asynchronously observing changes to Arrays, similar to Object.observe() for objects. It provides a stream of changes in order of occurrence. It's equivalent to Object.observe() invoked with the accept type list ["add", "update", "delete", "splice"].

sound this example code:

var myArray = ["a", "b", "c"];

// subscribe to add, update, delete, and splice changes
Array.observe(myArray, function(changes) {

  // handle changes... in this case, we'll just log them 
  changes.forEach(function(change) {
    console.log(Object.keys(change).reduce(function(p, c) {
      if (c !== "object" && c in change) {
        p.push(c + ": " + JSON.stringify(change[c]));
      }
      return p;
    }, []).join(", "));
  });

});

// down-shift the entire array and delete the last index
myArray.shift();

// set a value at a specific index
myArray[0] = "1";

// add a value
myArray.push("2");

// truncate array
myArray.length = 0;

but looks like it only works in node, chrome and opera

[agershun]

We can do it in two ways:

• Add TRIGGERs to AlaSQL
• Use observable on table.data array

Here is the example:

    alasql('CREATE TABLE one (a INT, b STRING)');

    Array.observe(alasql.databases.test392.tables.one.data,function(args){
      console.log('changed',arguments);
    });

    alasql('INSERT INTO one VALUES (10,"Ten")');
    alasql('UPDATE one SET a = 20 WHERE a = 10');
    alasql('DELETE FROM one WHERE a = 20');

You can use observable() right now on array of table data, but... only for INSERT and UPDATE, because AlaSQL recreates data array from the scratch on DELETE operation (because it uses .filter() function to filter WHERE records.

I will check, can we add triggers...

[mbektimirov]

Object/Array.observe is now deprecated: https://esdiscuss.org/topic/an-update-on-object-observe

[agershun]

@mbektimirov I almost finished with triggers for AlaSQL and will release this functionality tomorrow. Probably, it will help with the solution of this observation problem.

Here is the example of future functionality:

    alasql.fn.onchange = function(r) {
        if(r.a == 123) console.log('Observable event!');
    };;
    alasql('CREATE TABLE one (a INT)');
    alasql('CREATE TRIGGER two BEFORE INSERT ON one CALL onchange()');
    alasql('INSERT INTO one VALUES (123)');  // This will fire onchange()

[agershun]

According documentation this observation is very costly procedure...

Internally Lovefield will run the query again if the scope overlaps, therefore please be aware of performance consequences of complex SELECT.

[mathiasrw]

Nice work @agershun - really Nice...

[agershun]

@mathiasrw Good news: I just added triggers to the latest develop version... with JavaScript integration and preventions on INSERT and UPDATE (this is over standard SQL realizations).

Bad news:

• it does not support INSTEAD OF DELETE, AFTER DELETE
• BEFORE DELETE can not prevent record from deletion (now it works as regular SQL trigger)
• I changed localStorage module, but have broken ROLLBACK TRANSACTION :-1:

I swear I will fix it... May I ask you to play with new AlaSQL triggers (when you have time, not at the NewYear midnight :) )? May be add some tests?

I also worried about the syntax: I realized SQLite syntax with small extension (see test393.js as example). What do you think do we need to add other syntax?

[mathiasrw]

@Agershun

Sure! Ill dig into the World of triggers :)

[agershun]

Triggers do not works for DELETE FROM mytable without WHEN clause.

[agershun]

I think we can finish with SQL functionality with TRANSACTIONs block, and then switch to database backends, modular structure and speed/size/quality.

[mathiasrw]

Sounds like a really awesome plan.

If you - in between - can have a look at #471 at some point that would open up for a lot of tests in SQLligictest - so we could get even better quality :)

[mathiasrw]

I have

• Created https://github.com/agershun/alasql/wiki/TRIGGER
• Updated https://github.com/agershun/alasql/wiki/SQL-keywords
• Updated https://github.com/agershun/alasql/wiki/AlaSQL%20Keywords
• Noted the following in a little black book:

I changed localStorage module, but have broken ROLLBACK TRANSACTION :-1: I swear I will fix it...

:)

[agershun]

471 is fixed.

[nickdeis]

:+1: this is awesome

[agershun]

@mbektimirov Marat, may I ask you about the purpose of this observe in your case? Should it be something like SQL trigger on view?

[mbektimirov]

@agershun Yes, I need to have a way to know if the data was changed for particular view or query. I am trying to use alasql with React so it is the first step to bind a database returned data to react views. Moreover I need to know all concrete changes in the final json object, it is more like diff between previous and actual query result. Is it possible to have something like this and do not hurt performance?

[mathiasrw]

(wont have time to look at the trigger thing before next weekend)

[mathiasrw]

Closed per request from #628