SQL 2003 in sqlite

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL 2003 in sqlite

Sylvain Pointeau
Hello,

The merge statement is really missing in sqlite...
Is there any plan to integrate this SQL 2003 syntax in sqlite?

Best regards,
Sylvain
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Simon Slavin-3

On 12 Nov 2013, at 8:45am, Sylvain Pointeau <[hidden email]> wrote:

> The merge statement is really missing in sqlite...

SQLite does have this form of the INSERT statement:

INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2

Do you have specific requirements for the UPDATE features of MERGE ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Petite Abeille-2

On Nov 12, 2013, at 9:49 AM, Simon Slavin <[hidden email]> wrote:

>> The merge statement is really missing in sqlite…

Yes, very much so.

> SQLite does have this form of the INSERT statement:

http://www.sqlite.org/lang_conflict.html

Sadly, none of these ‘on conflit’ options are of any use for a merge, which, in SQLite, simply cannot be achieved in SQL. One has to resort  to custom, procedural logic to implement this. Oh, well… sigh...


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Sylvain Pointeau
Hi,

> INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2
> Do you have specific requirements for the UPDATE features of MERGE ?

the issue with "insert or replace" is that it will match on primary keys,
additionally (I am not 100% sure) it deletes first the row to replace it.
we cannot update then only 1 column.

furthermore, the merge can delete as well, it does the join on the fields
we want (not only PK), it is really wonderful.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Hick Gunter
Actually, replace will delete any and all currently present rows that cause a UNIQUE violation.

asql> create temp table x (prim integer UNIQUE, seco integer UNIQUE, val integer);
asql> insert into x values (1,1,1);
rows inserted
-------------
1
asql> insert into x values (2,2,2);
rows inserted
-------------
1
asql> insert into x values (1,2,3);
Error: column seco is not unique
asql> replace into x values (1,2,3);
rows inserted
-------------
1
asql> select * from x;
prim        seco        val
----------  ----------  ----------
1           2           3

Gunter

-----Ursprüngliche Nachricht-----
Von: Sylvain Pointeau [mailto:[hidden email]]
Gesendet: Mittwoch, 13. November 2013 15:16
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] SQL 2003 in sqlite

Hi,

> INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2 Do you
> have specific requirements for the UPDATE features of MERGE ?

the issue with "insert or replace" is that it will match on primary keys, additionally (I am not 100% sure) it deletes first the row to replace it.
we cannot update then only 1 column.

furthermore, the merge can delete as well, it does the join on the fields we want (not only PK), it is really wonderful.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--------------------------------------------------------------------------
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: [hidden email]

This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Simon Slavin-3
In reply to this post by Sylvain Pointeau

On 13 Nov 2013, at 2:15pm, Sylvain Pointeau <[hidden email]> wrote:

> Hi,
>
>> INSERT (OR REPLACE/IGNORE) INTO table1 SELECT * FROM table2
>> Do you have specific requirements for the UPDATE features of MERGE ?
>
> the issue with "insert or replace" is that it will match on primary keys,
> additionally (I am not 100% sure) it deletes first the row to replace it.
> we cannot update then only 1 column.

It's possible you should be using UPDATE OR REPLACE then.

> furthermore, the merge can delete as well, it does the join on the fields
> we want (not only PK), it is really wonderful.

I still don't know what function that MERGE does that you can't do in SQLite.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Petite Abeille-2

On Nov 13, 2013, at 4:38 PM, Simon Slavin <[hidden email]> wrote:

> I still don't know what function that MERGE does that you can't do in SQLite.

Are you asking what MERGE does? Or if it’s possible to somehow emulate that functionality in SQLite? If the later, then yes,  surely one could patch various statements and procedural logic to achieve the same effect. But see Truth № 3 as per RFC 1925.

[1] http://tools.ietf.org/html/rfc1925



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Simon Slavin-3

On 13 Nov 2013, at 6:51pm, Petite Abeille <[hidden email]> wrote:

>
> On Nov 13, 2013, at 4:38 PM, Simon Slavin <[hidden email]> wrote:
>
>> I still don't know what function that MERGE does that you can't do in SQLite.
>
> Are you asking what MERGE does?

I'm wondering what particular thing MERGE does that this person needs, which doesn't happen if they use just the single commands INSERT OR REPLACE or UPDATE OR REPLACE.

In other words, whether they want the MERGE command just because it exists in a later spec (and if they get it are they going to ask for all the other new commands one by one), or do they need some specific functionality which MERGE has and SQLite doesn't.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Petite Abeille-2

On Nov 13, 2013, at 8:03 PM, Simon Slavin <[hidden email]> wrote:

> or do they need some specific functionality which MERGE has and SQLite doesn’t.

There are no equivalent in SQLite at all. One cannot do even the most basic of upsert with the SQL available. The functionality is just not there. In other words, one cannot perform an insert or update combo without resorting to procedural programming outside of SQLite.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

decalek
In reply to this post by Sylvain Pointeau
On 12.11.2013 10:45, Sylvain Pointeau wrote:
> The merge statement is really missing in sqlite...

Definitely it is missing ... for maybe 0.05% of the (advanced) SQLite
users :-). Much large group missing UPDATE and DELETE statements over
joins at first place.

> Is there any plan to integrate this SQL 2003 syntax in sqlite?

Your question is already 36+ hours old. Because the SQLite[*] core team
(consisting of *3 developers* including the leading architect) is
usually very responsive when the subject is considered important (we
often have seen bugfixes and improvements done literally over the
night), the answer is obviously "No" - at least from the core team side.

But ... SQLite is very simple and smart architecture. Almost every part
is plugable and the interfaces between the moving parts are rigorously
documented.

http://www.sqlite.org/arch.html

For the MERGE RFE implementation you need just an extension of the SQL
frontend (first tier of the architecture) which translates SQL to the
simple and well evolved bytecode.

http://www.sqlite.org/opcode.html
sqlite3 :memory: 'explain select name from sqlite_master'

Actually SQLite is close to MERGE support in the sense that hypothetical
MERGE VDBE bytecode is relatively simple function of the bytecodes of
the three "elementary" statements which MERGE combines (insert, update,
delete).

Naturally, SQLite already generates MERGE "sub" statements bytecode for
every version of the engine. I.e. you have valid input to the bytecode
morphing transformation at hand.

So, if you are really like MERGE, and you are hacker with few dozens of
free hours - give it a go, many people here will (at least) follow with
interest your experiment.

If you are not - help the listening hackers (they are many here, but
believe me - 2 .. 5 max of them are regular MERGE users :-) ) to
understand the benefits of your RFE.

Cheers,
Alek

[*] The most used DB in the world

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

James K. Lowden
In reply to this post by Simon Slavin-3
On Wed, 13 Nov 2013 19:03:44 +0000
Simon Slavin <[hidden email]> wrote:

> I'm wondering what particular thing MERGE does that this person
> needs, which doesn't happen if they use just the single commands
> INSERT OR REPLACE or UPDATE OR REPLACE.

On Wed, 13 Nov 2013 20:31:25 +0100
Petite Abeille <[hidden email]> wrote:

> There are no equivalent in SQLite at all. One cannot do even the most
> basic of upsert with the SQL available. The functionality is just not
> there. In other words, one cannot perform an insert or update combo
> without resorting to procedural programming outside of SQLite.

http://www.schemamania.org/sql/#some.rows

The logical equivalent of MERGE is accomplished by one INSERT and one
UPDATE inside a user-defined transaction.  Given SQLite's locking
semantics, it's atomic.  Nothing procedural about it.  

INSERT OR REPLACE and UPDATE OR REPLACE, on the other hand, have
terrible, nonstandard semantics.  They should be avoided now
and abolished later.  They are not equivalent to MERGE, and seem mostly
to confuse the issue.  

I don't see a need for SQLite to support MERGE, given that UPDATE &
INSERT do the job just as well, with hardly any more effort.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Simon Slavin-3

On 16 Nov 2013, at 3:11pm, James K. Lowden <[hidden email]> wrote:

> http://www.schemamania.org/sql/#some.rows
>
> The logical equivalent of MERGE is accomplished by one INSERT and one
> UPDATE inside a user-defined transaction.  Given SQLite's locking
> semantics, it's atomic.  Nothing procedural about it.  

Thanks very much for the URL and explanation.  I now understand why someone asked for MERGE.  And also that its functions can be duplicated using the correct combination of existing commands.  Though incorporating it in a newer version of SQLite wouldn't be impossible -- just some parsing, virtual instructions, and a large number of test cases.  Hmm.  A huge number of test cases.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Petite Abeille-2
In reply to this post by James K. Lowden

On Nov 16, 2013, at 4:11 PM, James K. Lowden <[hidden email]> wrote:

> The logical equivalent of MERGE is accomplished by one INSERT and one
> UPDATE inside a user-defined transaction.  Given SQLite's locking
> semantics, it's atomic.  Nothing procedural about it.  

Well, one would still need to wrap these transaction and branching in some kind of procedural code. So procedural it is.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Kees Nuyt
On Sat, 16 Nov 2013 17:19:06 +0100, Petite Abeille
<[hidden email]> wrote:

>
>On Nov 16, 2013, at 4:11 PM, James K. Lowden <[hidden email]> wrote:
>
>> The logical equivalent of MERGE is accomplished by one INSERT and one
>> UPDATE inside a user-defined transaction.  Given SQLite's locking
>> semantics, it's atomic.  Nothing procedural about it.  
>
>Well, one would still need to wrap these transaction and branching in some kind of procedural code. So procedural it is.

A possible solution might be:

Create a VIEW "merge_t" on the table "t" you want to merge into.

Create an INSTEAD OF TRIGGER that handles the gory details of
INSERT OR IGNORE INTO t .... ;
UPDATE t set ....

For the application, the merge would look like a single
INSERT INTO merge_t statement.

(untested)

Remark: The view and the trigger are pretty straightforward, and the SQL
could probably be generated by a smart m4 macro.

--
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Petite Abeille-2

On Nov 16, 2013, at 11:02 PM, Kees Nuyt <[hidden email]> wrote:

> For the application, the merge would look like a single
> INSERT INTO merge_t statement.

Hmmmm…. clever lateral thinking, but I doubt this will fly in practice :)

Two main issues:

(1) ‘or ignore’ is most likely inappropriate as unrelated constraint violations will trigger it (e.g. null constraint). Resulting in a no-op altogether as neither insert nor update will do anything in practice. Leading to loss of data.

(2) Both insert & update statement will execute irrespectively of the state of the data, doubling the workload.

And, really, at this point (additional views, instead of triggers, highjacking of DML semantics, silent loss of data), the cure might seem worst than the disease.

As far as I can tell, there is no way to reasonably emulate MERGE in SQLite, short of resorting to some external programming logic.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL 2003 in sqlite

Kees Nuyt
On Sat, 16 Nov 2013 23:59:35 +0100, Petite Abeille
<[hidden email]> wrote:

>
>On Nov 16, 2013, at 11:02 PM, Kees Nuyt <[hidden email]> wrote:
>
>> For the application, the merge would look like a single
>> INSERT INTO merge_t statement.
>
>Hmmmm…. clever lateral thinking, but I doubt this will fly in practice :)
>
>Two main issues:
>
>(1) ‘or ignore’ is most likely inappropriate as unrelated
>    constraint violations will trigger it (e.g. null constraint).
>    Resulting in a no-op altogether as neither insert nor update
>    will do anything in practice. Leading to loss of data.

I would mitigate that by only offering key columns (primary and unique,
probably also foreign keys) to the insert, and rely on default
constraints for the value of all other columns.
Default values should not violate any check constraints.
The update would take care of the other columns.
The  INSERT INTO merge_t would use OR ABORT or OR ROLLBACK to handle the
case that the triggered update fails due to check constraints.


>(2) Both insert & update statement will execute irrespectively
>    of the state of the data, doubling the workload.

After the insert attempt, all related pages will be in the cache, so it
would double the computation workload at most, not the I/O workload.

> And, really, at this point (additional views, instead of triggers,
> highjacking of DML semantics, silent loss of data), the cure might
> seem worst than the disease.
>
> As far as I can tell, there is no way to reasonably emulate MERGE
> in SQLite, short of resorting to some external programming logic.

I agree my solution would only be a workaround, and the required DML is
not really "elegant", but from the applications perspective it's not too
bad. Perhaps better than having to maintain external programming logic.

I'll implement this workaround some day, when I have a use case, and be
punished by reality :)

--
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users