Variable Declaration

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

Variable Declaration

Stephen Chrzanowski
Tonight, I wanted to write up a small database to keep tabs on certain
things I've done in a game, and show me new tasks I can do based on what
I've completed.  I have a full list of items, and what pairs of items I
need to poses to get the next thing, but, getting the data into the DB is
tedious.   I'm still going to do it, but, the thought of using variables
came to mind, and apparently this isn't something that's supported?

I've seen long wind answers to how this can be completed on stackoverflow,
and other sites, and for this one-time thing I'm doing, I'm not really
interested in those proposals.

So for now, trudging on without, but, has there been recent discussions or
any plans on introducing variables to SQLite?  I know it's a bit of fluff,
and the whole "Keeping it light" thing, but, it's kind of useful in some
cases.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

Keith Medcalf

SQLite3 does have variables.  

You set them by binding values to an SQL statement, and retrieve them by retrieving column values resulting from the execution of an SQL Statement.  For example:

sqlite3_prepare('select var1, var2 from table1 where val3=?;')

You bind a value to the val3 variable using one of the bind functions, then when you sqlite3_step the statement, you can use sqlite3_column_xxx to retrieve the value of the var1 and var2 variables.

Or did you have something else in mind?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Stephen Chrzanowski
>Sent: Friday, 18 January, 2019 18:52
>To: General Discussion of SQLite Database
>Subject: [sqlite] Variable Declaration
>
>Tonight, I wanted to write up a small database to keep tabs on
>certain
>things I've done in a game, and show me new tasks I can do based on
>what
>I've completed.  I have a full list of items, and what pairs of items
>I
>need to poses to get the next thing, but, getting the data into the
>DB is
>tedious.   I'm still going to do it, but, the thought of using
>variables
>came to mind, and apparently this isn't something that's supported?
>
>I've seen long wind answers to how this can be completed on
>stackoverflow,
>and other sites, and for this one-time thing I'm doing, I'm not
>really
>interested in those proposals.
>
>So for now, trudging on without, but, has there been recent
>discussions or
>any plans on introducing variables to SQLite?  I know it's a bit of
>fluff,
>and the whole "Keeping it light" thing, but, it's kind of useful in
>some
>cases.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Variable Declaration

Stephen Chrzanowski
More simple than that.  In MSSQL there's the DECLARE and SET commands (
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-2017
)

I was thinking that while I'm going through my list of "To make this, you
need that and the other thing", I was going to have three variable set with
three different strings I could just copy/paste to generate that
comparative list.  But then I got to thinking I could do the same kind of
thing with Excel, export to CSV, and just import it into SQLite.  Then I
got really lazy and decided to write an actual application to handle the
database instead, and use a GUI with Combo boxes and lists to show me what
I can make with what I have, etc.  Saves typing and keeps up accuracy, so
my request for this project is kind of moot.

But the principal for having this for some things would be nice. It'd
mostly fix the going in-and-out of quotes to create the SQL statements.  I
know binding is available in most places, but this just "sounds" more
easier.

On Fri, Jan 18, 2019 at 9:12 PM Keith Medcalf <[hidden email]> wrote:

>
> SQLite3 does have variables.
>
> You set them by binding values to an SQL statement, and retrieve them by
> retrieving column values resulting from the execution of an SQL Statement.
> For example:
>
> sqlite3_prepare('select var1, var2 from table1 where val3=?;')
>
> You bind a value to the val3 variable using one of the bind functions,
> then when you sqlite3_step the statement, you can use sqlite3_column_xxx to
> retrieve the value of the var1 and var2 variables.
>
> Or did you have something else in mind?
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Stephen Chrzanowski
> >Sent: Friday, 18 January, 2019 18:52
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Variable Declaration
> >
> >Tonight, I wanted to write up a small database to keep tabs on
> >certain
> >things I've done in a game, and show me new tasks I can do based on
> >what
> >I've completed.  I have a full list of items, and what pairs of items
> >I
> >need to poses to get the next thing, but, getting the data into the
> >DB is
> >tedious.   I'm still going to do it, but, the thought of using
> >variables
> >came to mind, and apparently this isn't something that's supported?
> >
> >I've seen long wind answers to how this can be completed on
> >stackoverflow,
> >and other sites, and for this one-time thing I'm doing, I'm not
> >really
> >interested in those proposals.
> >
> >So for now, trudging on without, but, has there been recent
> >discussions or
> >any plans on introducing variables to SQLite?  I know it's a bit of
> >fluff,
> >and the whole "Keeping it light" thing, but, it's kind of useful in
> >some
> >cases.
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

Simon Slavin-3
On 19 Jan 2019, at 3:57am, Stephen Chrzanowski <[hidden email]> wrote:

> I was going to have three variable set with
> three different strings I could just copy/paste to generate that
> comparative list.

You know SQLite does binding, right ?  You don't have to construct your command like

command$ = "INSERT INTO MyTable VALUES ("+a$+","+b$+","+c$+")"

You can do this:

command$ = "INSERT INTO MyTable VALUES (?,?,?)"

or

command$ = "INSERT INTO MyTable VALUES ($name,$address,$phone)"

and maintain the variables in whatever programming language you're using.  You just have to remember to bind before you tell SQLite to execute command$.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

Stephen Chrzanowski
I know about the bindings.  I don't know about all languages supporting it.

On Fri, Jan 18, 2019 at 11:26 PM Simon Slavin <[hidden email]> wrote:

> On 19 Jan 2019, at 3:57am, Stephen Chrzanowski <[hidden email]>
> wrote:
>
> > I was going to have three variable set with
> > three different strings I could just copy/paste to generate that
> > comparative list.
>
> You know SQLite does binding, right ?  You don't have to construct your
> command like
>
> command$ = "INSERT INTO MyTable VALUES ("+a$+","+b$+","+c$+")"
>
> You can do this:
>
> command$ = "INSERT INTO MyTable VALUES (?,?,?)"
>
> or
>
> command$ = "INSERT INTO MyTable VALUES ($name,$address,$phone)"
>
> and maintain the variables in whatever programming language you're using.
> You just have to remember to bind before you tell SQLite to execute
> command$.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

Jesse Rittner
What language do you want to use? Parameter binding is a feature of the
SQLite C API. So pretty much any language with a C FFI will suffice. There's
a list on Wikipedia, not sure how authoritative it is.
https://en.wikipedia.org/wiki/SQLite#Programming_language_support (Note:
When it says they "provide bindings for SQLite", it's referring to the FFI,
not parameter binding.)



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

Luuk

On 19-1-2019 14:23, Jesse Rittner wrote:
> What language do you want to use? Parameter binding is a feature of the
> SQLite C API. So pretty much any language with a C FFI will suffice. There's
> a list on Wikipedia, not sure how authoritative it is.
> https://en.wikipedia.org/wiki/SQLite#Programming_language_support (Note:
> When it says they "provide bindings for SQLite", it's referring to the FFI,
> not parameter binding.)
>

This question is not about: 'parameter binding'!

It's about 'variable decalaration'...


In MS-SQL you can do things like:

C:\temp>PSQL.EXE -E -S SQL2017DEV

1> declare @count int = 10;
2> select * from test where i<=@count
3> go
  i
  -----------
            1
            2
            3
            4
            5
            6
            7
            8
            9
           10

(10 rows affected)
1>



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

Re: Variable Declaration

Luuk

On 19-1-2019 14:49, Luuk wrote:
>
>
> This question is not about: 'parameter binding'!
>
> It's about 'variable decalaration'...
oops 'variable declaration'

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

Re: Variable Declaration

Simon Slavin-3
In reply to this post by Stephen Chrzanowski
On 19 Jan 2019, at 4:49am, Stephen Chrzanowski <[hidden email]> wrote:

> I know about the bindings.  I don't know about all languages supporting it.

Bindings are part of the SQLite API.  Any language which can make SQLite calls should be supporting binding.

Using binding means you can have the variables you want -- as many as you want -- in whatever programming language you're using.  SQLite doesn't need variables before your programming language has variables.  This is why SQLite doesn't need variables.

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

Re: Variable Declaration

Roger Schlueter
before -> because to help those for whom English is not their first
language. Cheers.

On 1/19/2019 5:53, Simon Slavin wrote:

> On 19 Jan 2019, at 4:49am, Stephen Chrzanowski <[hidden email]> wrote:
>
>> I know about the bindings.  I don't know about all languages supporting it.
> Bindings are part of the SQLite API.  Any language which can make SQLite calls should be supporting binding.
>
> Using binding means you can have the variables you want -- as many as you want -- in whatever programming language you're using.  SQLite doesn't need variablesbefore  your programming language has variables.  This is why SQLite doesn't need variables.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

Keith Medcalf
In reply to this post by Luuk

The reason for this is historical and is because Microsoft SQL Server is a derivative work of Sybase SQL Server and their TRANSACT-SQL language.   Sybase was the first (I believe) SQL-based RDBMS that supported a complete and ONLY dynamic execution model for SQL statements.  All other DBMS systems at the time were either navigational or required that the SQL statements be pre-compiled into the host application, usually via EXEC SQL macro's of the like.

This was because of the time and computation required to prepare the execution plan for the SQL and efficiency.  While most SQL RDBMS more-or-less supported dynamic execution of SQL (as in pass the SQL statement at execution time and prepare the statement at that time, it was very very very inefficient).  Sybase TRANSACT was designed from the get-go to be able to execute SQL Statement blocks (not a single statement but a block of statements, that would otherwise be part of a pre-compiled EXEC SQL block) dynamically by preparing the plan at runtime -- though it did also support the standard pre-compiler EXEC SQL preparation syntax -- but converted that into the dynamic equivalent code performed at runtime rather than the normal pre-prepare and plan binding).

With the introduction of OS/2 EE IBM added the necessary plumbing to support SNA on their version of IBM OS/2 Extended Edition so they could also port Communications Manager and their mainframe DB2 product to OS/2.  The "Extended Edition" enhancements were not covered by the JAD and were not included in the generic version of OS/2 marketted by Microsoft as "Microsoft OS/2".  Not wanting to be left out, Microsoft entered into a technology agreement with Sybase to port the Sybase Database engine (which at that time only ran on Unixlike systems) to MS OS/2.  Thus was born "Microsoft SQL Server".  The core "OS/2" was covered by the JAD between Microsoft and IBM and was the same.  IBM had "IBM OS/2 Extended Edition" which included integration with other IBM products and included DB/2.  Microsoft had "Microsoft OS/2" and kept feature parity by adding "Microsoft SQL Server" to end up with an equivalent offering for shops that did not or want to integrate with IBM Big Iron.

Under the IBM and Microsoft JAD work began on creating the next version of OS/2, OS/2 3.0, which would be based on a different memory model.  Somewhere around beta 2 of OS/2 3.0 Microsoft and IBM had a major disagreement regarding "Windows" support, the WIMP interface that ran overtop of the old MS-DOS operating system that OS/2 was being built to replace.  IBM believed that OS/2 should use a new Object-based gooey layer, and Microsoft wanted to use the "Windows" interface that they had been so successful at giving away in boxes of cornflakes and such.  IBM thought that Windows, being merely a WIMP layer on top of DOS (which it was in all versions Microsoft ever released) belonged in the "penalty box" (the DOS emulation layer inside OS/2).

This split and terminated the JAD between Microsoft and IBM.   Microsoft took the OS/2 3.0 Beta 2 code and generated their OS/2 New Technology.  The "New Technology" part was considered to be a bit to long, so Microsoft shortened it to NT, replaced the Presentation Manager with with Windows layer, added the "Windows Subsystem" and released it as Windows NT.  IBM took the OS/2 3.0 Beta 2 code, changed the UI interface to their new Workplace Shell, and released an IBM version of OS/2 which eventually became OS/2 WARP.  The IBM side kept all the proprietary IBM stuff and the DB2 database.  The Microsoft side kept the common codebase and it became Windows NT.  As part of the JAD separation Microsoft was required to replace all the proprietary IBM technology with their own, which Microsoft did by calling it "ActiveX" (Active this and Active that) re-writing those bits.

Somewhere in all the splitting up and divorce proceedings between Microsoft and IBM there was also a split between Microsoft and Sybase.  Up until "Windows NT" Microsoft had been getting codebase updates from Sybase which kept "Microsoft SQL Server" and "Sybase SQL Server" pretty much at feature parity.  This also split at the time the new "Windows NT" product and the Sybase and Microsoft "SQL Server" products started to diverge, however both retained the common TRANSACT-SQL underpinnings.

What, you may wonder, does this have to do with support for "local variables" in MS SQL Server?

Well, as I mentioned at the beginning TRANSACT does not execute single SQL Statements, it dynamically executes EXEC SQL statement blocks (TRANSACT-SQL) which may consist of MULTIPLE SQL STATEMENTS which are dynamically prepared and executed as a single statement at the server.  

Compare this to SQLite3 which executes only SINGLE STATMENTS locally.

Therefore in TRANSACT you may have a statement block thus:

"declare @v char
 select @v=CustName from customers where custno=:custno
 update onhand set qty=qty+1 where custname=@v"

where the variable @v is local to the block and two sql statements are executed together in the same plan on the server.  Oracle has a similar capability to execute statement blocks that they call PL/SQL that execute on the server.  DB2 requires that you declare an EXEC SQL block (what is called a stored procedure) to do the same thing on the server.

In SQLite3 each statement executes within its own local context and there is no functionality to provide a "context wrapper" which would allow for context (server side) variables.  This makes perfect sense because SQLite3 has no server in which to create such a context -- your appplication is the server and you can create local variables in your application programming language to your own hearts desire.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Luuk
>Sent: Saturday, 19 January, 2019 06:49
>To: [hidden email]
>Subject: Re: [sqlite] Variable Declaration
>
>
>On 19-1-2019 14:23, Jesse Rittner wrote:
>> What language do you want to use? Parameter binding is a feature of
>the
>> SQLite C API. So pretty much any language with a C FFI will
>suffice. There's
>> a list on Wikipedia, not sure how authoritative it is.
>> https://en.wikipedia.org/wiki/SQLite#Programming_language_support
>(Note:
>> When it says they "provide bindings for SQLite", it's referring to
>the FFI,
>> not parameter binding.)
>>
>
>This question is not about: 'parameter binding'!
>
>It's about 'variable decalaration'...
>
>
>In MS-SQL you can do things like:
>
>C:\temp>PSQL.EXE -E -S SQL2017DEV
>
>1> declare @count int = 10;
>2> select * from test where i<=@count
>3> go
>  i
>  -----------
>            1
>            2
>            3
>            4
>            5
>            6
>            7
>            8
>            9
>           10
>
>(10 rows affected)
>1>
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Variable Declaration

Scott Robison-2
In reply to this post by Simon Slavin-3
On Sat, Jan 19, 2019, 6:53 AM Simon Slavin <[hidden email] wrote:
>
> On 19 Jan 2019, at 4:49am, Stephen Chrzanowski <[hidden email]>
wrote:
>
> > I know about the bindings.  I don't know about all languages supporting
it.
>
> Bindings are part of the SQLite API.  Any language which can make SQLite
calls should be supporting binding.
>
> Using binding means you can have the variables you want -- as many as you
want -- in whatever programming language you're using.  SQLite doesn't need
variables before your programming language has variables.  This is why
SQLite doesn't need variables.


Unless you want to write a script to run from the sqlite3 shell. Then you
don't have access to "variables" in the way the OP suggested.

Of course, you can use temp tables as though they are variables, though the
syntax isn't as elegant as the provided example.

1> declare @count int = 10;
2> select * from test where i<=@count

can be reformulated as

1> create temp table vars(name, value);
2> insert into vars values('count', 10);
3> select * from test where i <= (select value from vars where
name='count');

That's just one example, of course. Multiple variations on the theme are
possible if one needs variables that live exclusively in SQL without using
a host language and that also survive for more than a single statement.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

Stephen Chrzanowski
But then in BASH, for example, the temp table would only last for the life
of the initial connection, so you'd have to throw everything in one
execution.  This goes along the lines of just setting a variable.  A BIG
time question comes to scope of the life of the variable.  Does it live for
the entirety of the databases life?  Does it live for that connection?
Does it live for that transaction?  Too many ways to have that play out
that it just may not be worth while coding it into the SQLite base anyways.

I guess there are many different ways to skin this cat.  Temp tables, going
in and out of quotes, binding, etc.  I was just curious whether variables
were something to be, or are being considered, or, if it's something that's
been outright "Will not do".  I don't know of Postgres has variables or
not, but that's server side, and Keith mentions, SQLite works off of single
statements.

On Sat, Jan 19, 2019 at 2:12 PM Scott Robison <[hidden email]>
wrote:

>
>
> 1> create temp table vars(name, value);
> 2> insert into vars values('count', 10);
> 3> select * from test where i <= (select value from vars where
> name='count');
>
> That's just one example, of course. Multiple variations on the theme are
> possible if one needs variables that live exclusively in SQL without using
> a host language and that also survive for more than a single statement.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

Andrew.Goth
In reply to this post by Stephen Chrzanowski
I'm jumping into the middle of the conversation, possibly without seeing the beginning, but from what I've seen so far, it seems to me that the key is recognizing there are many tools in a programmer's toolbox.  Each tool covers a certain area of functionality really well, then there are areas where it can be applied but with difficulty, and then the rest is off-limits for any serious applications.  There is considerable overlap between the applicability of different tools, so you get to pick your favorites.  But you have to be willing to incorporate additional tools when you get to the limits of comfort for your favorite tool.

However, instead what I often see is people trying to expand the scope of their favorite.  Sometimes this is the right choice, but usually it is not.  It takes considerable experience and good taste to be able to wisely judge the difference and to chart a roadmap that legitimately enhances tools in a way that justifies the cost to maintenance, documentation, ease of learning, and so forth.  I know I've been on the wrong side of this many times, but I try to learn from my mistakes.

SQLite is a special sort of tool in that it expressly acknowledges its limitations, and it lets those limitations be its strength.  I mean, "Lite" is right there in the name.  The idea of having a larger number of more limited tools, with a stronger focus on interoperability, is a very powerful and transformative engineering mindset and is central to the Unix design philosophy (which, by the way, is often at odds with the Unix design practice).  The point of SQLite, in contrast to SQL servers, is that it be embeddable in your application.  Thus, SQLite has no need to replicate the capabilities already present in every reasonable programming language.  Namely, variables.

I do see a valid objection earlier in the thread: the SQLite shell.  It indeed lacks variables, though they can be emulated with temporary tables.  This is what I was talking about in the first paragraph.  It's possible to stretch tools beyond their core competency, but it can be difficult, so you're better off letting other tools fill the gaps.  But when you're using the SQLite shell, that's not really a good option.  You can let the SQLite shell be the backend to a shell script, but you're not going to want to call it more than once or twice per complete run of a program.

So rather than discuss adding variable declarations to SQLite, maybe we should be talking about adding variable declarations to the SQLite shell.  Please don't think I'm making a serious proposal.  Instead it's just for the sake of argument.  Consider what would come of doing that.  We'd be taking the SQLite shell in the direction of becoming a programming language unto itself, complementary to yet distinct from SQLite.  Once the SQLite shell gets variables, there would also be clamor for loops and conditionals and function declarations and fine-grained I/O and so forth.  That would be cool, I suppose.  And then, once all that is implemented, why not migrate the new functionality from the shell into SQLite proper?  But, umm, don't we already have dozens or hundreds of programming languages to choose from?  Does SQLite really need to compete in that arena?

Instead, here's what I think.  SQLite is extensible by virtue of being embeddable; it can be embedded in a wrapper that expands its capabilities.  Furthermore, the sources are maximally open and free, including the Lemon parser.  If you want to add new syntax to SQLite to ease its integration with another programming language, start by duplicating and extending its parser to recognize variable declaration and usage.  The output of this new parser would be plain-Jane SQLite SQL, augmented with all the necessary variable declarations, statement compiles, parameter bindings, statement steps, column extractions, error checks, and so on needed to make it happen.

So, figure out exactly what you want the new syntax to look like, and write a lot of sample queries in the updated language.  For each example, write the equivalent C code.  Then, it will be your task to make a parser that knows how to translate the former into the latter.  Or more realistically, into data structures that can be fed at runtime into the latter, having the same net result but without the need to call a C compiler in the middle.

People can then choose to use this wrapper around SQLite wherever it suits them.

One good place to do so would be an enhanced shell, which can be forked from the original in order to spin up development quickly.

Another would be in new language bindings, though it will be interesting finding ways for the augmented parser to relate the variables in its string input with variables present in its host environment.  For languages like Tcl, that will be easy.  For languages like C, that will be impossible to do automatically, and a manual association might be more difficult than just using baseline SQLite.  So instead, for C, keep the SQL variables inside a dynamic data structure in which the host environment can peek and poke by means of typed accessor functions, much like the current SQLite bind/column API.

All this is possible today, right now, with no need for any SQLite developers to get involved.  Anyone can start this task, anyone can write and publish this code.
------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving..
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Variable Declaration

James K. Lowden
In reply to this post by Keith Medcalf
On Sat, 19 Jan 2019 12:01:34 -0700
"Keith Medcalf" <[hidden email]> wrote:

> Microsoft took the OS/2 3.0 Beta 2 code and generated their OS/2 New
> Technology.  The "New Technology" part was considered to be a bit to
> long, so Microsoft shortened it to NT, replaced the Presentation
> Manager with with Windows layer, added the "Windows Subsystem" and
> released it as Windows NT.  

The whole message was fun to read; I just wanted to correct this part.
You'll remember Microsoft hired David Cutler from DEC to create Windows
NT.  AFAIK there was no OS/2 technology per se in NT.  Some of the
Windows API was shared across all three -- DOS/Windows, OS/2, and NT --
but the underlying OS functionality -- scheduling, memory model, I/O --
was utterly different.  Of the 3, NT was the only one with demand-paged
virtual memory and isolated per-process virtual addressing on the i386
platform.  

Notably, early versions of NT put the video driver in userspace, not in
the kernel.  That made processing more reliable and games slower.
History has since shown that Microsoft, when faced with a choice
between correct and fast, always chose fast.  

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

Re: Variable Declaration

Jake
In reply to this post by Andrew.Goth
There does exist an experimental branch which introduces the concept
of shell variables.

https://www.sqlite.org/src/timeline?r=shell-bindings

The check-in comment describes the usage:

"Add the ability to use bind parameters in the CLI. The new ".set
KEY=VALUE" dot-command works to set bindings. Or use the "-Dkey=value"
command-line option. Or use the built-in shell_bindings(k,v) virtual
table to set, delete, or changing bindings."

Note: A bug exists when setting text/blob values via the
shell_bindings virtual table. The bindvtabUpdate method fails to set
p->len. Adding p->len = len resolves the issue.

On Sun, Jan 20, 2019 at 1:15 PM <[hidden email]> wrote:

>
> I'm jumping into the middle of the conversation, possibly without seeing the beginning, but from what I've seen so far, it seems to me that the key is recognizing there are many tools in a programmer's toolbox.  Each tool covers a certain area of functionality really well, then there are areas where it can be applied but with difficulty, and then the rest is off-limits for any serious applications.  There is considerable overlap between the applicability of different tools, so you get to pick your favorites.  But you have to be willing to incorporate additional tools when you get to the limits of comfort for your favorite tool.
>
> However, instead what I often see is people trying to expand the scope of their favorite.  Sometimes this is the right choice, but usually it is not.  It takes considerable experience and good taste to be able to wisely judge the difference and to chart a roadmap that legitimately enhances tools in a way that justifies the cost to maintenance, documentation, ease of learning, and so forth.  I know I've been on the wrong side of this many times, but I try to learn from my mistakes.
>
> SQLite is a special sort of tool in that it expressly acknowledges its limitations, and it lets those limitations be its strength.  I mean, "Lite" is right there in the name.  The idea of having a larger number of more limited tools, with a stronger focus on interoperability, is a very powerful and transformative engineering mindset and is central to the Unix design philosophy (which, by the way, is often at odds with the Unix design practice).  The point of SQLite, in contrast to SQL servers, is that it be embeddable in your application.  Thus, SQLite has no need to replicate the capabilities already present in every reasonable programming language.  Namely, variables.
>
> I do see a valid objection earlier in the thread: the SQLite shell.  It indeed lacks variables, though they can be emulated with temporary tables.  This is what I was talking about in the first paragraph.  It's possible to stretch tools beyond their core competency, but it can be difficult, so you're better off letting other tools fill the gaps.  But when you're using the SQLite shell, that's not really a good option.  You can let the SQLite shell be the backend to a shell script, but you're not going to want to call it more than once or twice per complete run of a program.
>
> So rather than discuss adding variable declarations to SQLite, maybe we should be talking about adding variable declarations to the SQLite shell.  Please don't think I'm making a serious proposal.  Instead it's just for the sake of argument.  Consider what would come of doing that.  We'd be taking the SQLite shell in the direction of becoming a programming language unto itself, complementary to yet distinct from SQLite.  Once the SQLite shell gets variables, there would also be clamor for loops and conditionals and function declarations and fine-grained I/O and so forth.  That would be cool, I suppose.  And then, once all that is implemented, why not migrate the new functionality from the shell into SQLite proper?  But, umm, don't we already have dozens or hundreds of programming languages to choose from?  Does SQLite really need to compete in that arena?
>
> Instead, here's what I think.  SQLite is extensible by virtue of being embeddable; it can be embedded in a wrapper that expands its capabilities.  Furthermore, the sources are maximally open and free, including the Lemon parser.  If you want to add new syntax to SQLite to ease its integration with another programming language, start by duplicating and extending its parser to recognize variable declaration and usage.  The output of this new parser would be plain-Jane SQLite SQL, augmented with all the necessary variable declarations, statement compiles, parameter bindings, statement steps, column extractions, error checks, and so on needed to make it happen.
>
> So, figure out exactly what you want the new syntax to look like, and write a lot of sample queries in the updated language.  For each example, write the equivalent C code.  Then, it will be your task to make a parser that knows how to translate the former into the latter.  Or more realistically, into data structures that can be fed at runtime into the latter, having the same net result but without the need to call a C compiler in the middle.
>
> People can then choose to use this wrapper around SQLite wherever it suits them.
>
> One good place to do so would be an enhanced shell, which can be forked from the original in order to spin up development quickly.
>
> Another would be in new language bindings, though it will be interesting finding ways for the augmented parser to relate the variables in its string input with variables present in its host environment.  For languages like Tcl, that will be easy.  For languages like C, that will be impossible to do automatically, and a manual association might be more difficult than just using baseline SQLite.  So instead, for C, keep the SQL variables inside a dynamic data structure in which the host environment can peek and poke by means of typed accessor functions, much like the current SQLite bind/column API.
>
> All this is possible today, right now, with no need for any SQLite developers to get involved.  Anyone can start this task, anyone can write and publish this code.
> ------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving..
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users