Hi list,
sorry for the late reply, Christmas holidays :-)
> Am 21.12.2015 um 09:15 schrieb Jan Nijtmans <jan.nijtmans at gmail.com>:
>
> 2015-12-19 21:57 GMT+01:00 Jeremy Boy <jeremy.boy at student.uni-luebeck.de>:
>> I would like to use the optional LIMIT and ORDER BY clause for DELETE
>> statements in SQLite. I can verify that SQLite was built with
>> SQLITE_ENABLE_UPDATE_DELETE_LIMIT:
>
> This doesn't work when sqlite is buillt from the amalamation, which is
> clearly stated
> in the docs. You need to build it from the sources, then it will work.
Thanks for the pointer. I read about this in the docs, but thought that I built
from sources. I used the FreeBSD ports tree (and didn't quite get the point of
the amalgamation, yet). How can I verify where SQLite was built from?
> The problem is
> that the produced amalgamation is different depending on this flag.
>
> My solution would be the patch below (to the SQLite sources). With this
> patch in place, the resulting amalgamation can be build either with or without
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT, both work as expected. The
> produced amalgamation doesn't depend on the SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> flag any more.
>
> I'm donating this patch to the public domain, in case anyone is interested
This should be either implemented or the config flag should be removed from the
amalgamation, IMHO. Thanks for the patch.
>
> Needless to say: All unit-test (in the SQLite sources) run fine when
> this patch is applied.
>
> Could I add this to the 2016 wish-list? ;-)
>
> Regards,
> Jan Nijtmans
>
> ===========================================
> --- src/parse.y
> +++ src/parse.y
> @@ -740,54 +740,56 @@
> limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y).
> {A.pOffset = X.pExpr; A.pLimit
> = Y.pExpr;}
>
> /////////////////////////// The DELETE statement /////////////////////////////
> //
> -%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W)
> orderby_opt(O) limit_opt(L). {
> sqlite3WithPush(pParse, C, 1);
> sqlite3SrcListIndexedBy(pParse, X, &I);
> +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) &&
> !defined(SQLITE_OMIT_SUBQUERY)
> W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
> - sqlite3DeleteFrom(pParse,X,W);
> -}
> -%endif
> -%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> -cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
> - sqlite3WithPush(pParse, C, 1);
> - sqlite3SrcListIndexedBy(pParse, X, &I);
> +#else
> + if( O || L.pLimit ){
> + sqlite3ErrorMsg(pParse, "%s on DELETE not supported", O?"ORDER
> BY":"LIMIT");
> + sqlite3ExprDelete(pParse->db, W);
> + sqlite3ExprListDelete(pParse->db, O);
> + sqlite3ExprDelete(pParse->db, L.pLimit);
> + sqlite3ExprDelete(pParse->db, L.pOffset);
> + W = 0;
> + }
> +#endif
> sqlite3DeleteFrom(pParse,X,W);
> }
> -%endif
>
> %type where_opt {Expr*}
> %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
>
> where_opt(A) ::= . {A = 0;}
> where_opt(A) ::= WHERE expr(X). {A = X.pExpr;}
>
> ////////////////////////// The UPDATE command ////////////////////////////////
> //
> -%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
> where_opt(W) orderby_opt(O) limit_opt(L). {
> sqlite3WithPush(pParse, C, 1);
> sqlite3SrcListIndexedBy(pParse, X, &I);
> sqlite3ExprListCheckLength(pParse,Y,"set list");
> +#if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) &&
> !defined(SQLITE_OMIT_SUBQUERY)
> W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
> - sqlite3Update(pParse,X,Y,W,R);
> -}
> -%endif
> -%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> -cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
> - where_opt(W). {
> - sqlite3WithPush(pParse, C, 1);
> - sqlite3SrcListIndexedBy(pParse, X, &I);
> - sqlite3ExprListCheckLength(pParse,Y,"set list");
> +#else
> + if( O || L.pLimit ){
> + sqlite3ErrorMsg(pParse, "%s on UPDATE not supported", O?"ORDER
> BY":"LIMIT");
> + sqlite3ExprDelete(pParse->db, W);
> + sqlite3ExprListDelete(pParse->db, O);
> + sqlite3ExprDelete(pParse->db, L.pLimit);
> + sqlite3ExprDelete(pParse->db, L.pOffset);
> + W = 0;
> + }
> +#endif
> sqlite3Update(pParse,X,Y,W,R);
> }
> -%endif
>
> %type setlist {ExprList*}
> %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}
>
> setlist(A) ::= setlist(Z) COMMA nm(X) EQ expr(Y). {
