SQLite Forum

Timeline
Login

2 forum posts by user decalek

2020-07-17
23:59 Reply: UPDATE in version 3.33 (artifact: ad0a86c79e user: decalek)
> The SQL Server form of the query is the same as the PG form except
> that with SQL Server you repeat the target table in the FROM clause,
> but in PG you do not.

Before my note - Just for the sake of correct attribution: This form of
update is not one of the Microsoft additions to the Transact SQL
language (which was rabranded as Microsoft Transact-SQL - T-SQL for
their rebranded fork of the Sybase engine - "Microsoft SQL Server") -
I have used this form at least with the Sybase (ASE) 11.0.3 for Linux
~1998.

The symbol after keyword "update" in the Transact SQL syntax *is not* a
repetition, it is just a symbolic *reference* (as in many other
points-to relations in the SQL AST, where both alias or aliased symbol
could be used).

With Sybase or MSSQL engine try the following:

create table table1(c int)
insert into table1(c) values(0)
update t1 set c=c+2 from table1 t1 where c=0

Actually, as many other things in this SQL dialect, the update syntax is
solely "cooked" for developer convenience:

Developer first ask:

select c, c+2 c_next
  from table1 t1
    where c=0

After the check that, the (potentially complex) join forming the scope
of destructive operation do not seems obviously buggy anymore :-), the
developer issues (or permanently adds to the application code):

update t1 set c=c+2
  from table1 t1
    where c=0

replacing only the select line - the "from" relational expression
remains the same.

My proposal: SQLite implements PostgreSQL dialect, as always. Just (in
the upcoming release or some future version) allow exactly this
*compatible extension* of the syntax: "update alias".

Kind regards,
Alek
2020-06-18
21:24 Reply: How to Extension SQLite to support Stored Procedure´╝č (artifact: a45b0b6676 user: decalek)
Wow, Second post about Stored Procedures in the same year - Amazing!
:-).

Generally, the SQL scripting is considered excessive and redundant in
the sqlite (i.e. embedding) context by the core team leader and most of
the distinguished members of the forum (and preceding sqlite-users
mailing list). Furthermore, very few of the list participants have some
accountable (like L Carl practice expressed in [1]) experience with SP
codebases in past projects.

What kind is your interest in the SQL scripting - You need to use it
incidentally in your current work; Or you have principal confidence that
sufficient number of sqlite based projects needs such feature for logic
encapsulation?

If you need something this year, perhaps it will be more reasonable to
check some sqlite alternative, e.g. Tarantool, SQL part of which could
be considered as sqlite fork [2]. Tarantool supports engine-side Lua [3]
(and C) which feels more like .Net in MSSQL than Transact SQL (i.e.
project do not have data oriented DSL). Tarantool is well-established
project which powers big cloud services provider (mail.ru).

Another (much lighter and easy embeddable) project in the same vein
(modulo our topic) is jsish [4] which develops own JS engine for SQL/WEB
services scripting [5].

If you have principal interest on the topic, for your convenience I
extracted the list posts from the past 10-ish years, containing the
string "stored procedure" [6], so you can enumerate the argumentation of
the view points from previous threads. In addition to my own humble
opinions in the past (few of which you could find in the archive), now I
think that for future SQL (and XQuery too) developments it would be a
good idea some well thought macro/tree rewriting system to be promoted,
and the CSTs/ASTs of the codebase to be (structurally) persisted in both
unexpanded and expanded form (think app_code_node tree table(s) which
are enough for easy code base mangling implementations (i.e. IDEs
support) and also for unparsing the DDLs stored in sqlite_master)

Kind Regards,
Alek

[1] https://sqlite.org/forum/forumpost/538fbde154
[2] https://sourcegraph.com/github.com/tarantool/tarantool@2.5.0/-/blob/src/box/sql/vdbe.c
[3] https://www.tarantool.io/en/doc/2.4/tutorials/sql_tutorial/#create-a-million-row-table
[4] https://jsish.org/fossil/jsi3/doc/tip/md/DBQuery.md#Javascript
[5] https://jsish.org/fossil/jsi3/doc/ckout/README.md
[6] http://source.declera.com/sqlite-stored-procedure.zip
    Password: i-will-try-fossil