Re: [sqlite] Variable Declaration
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 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
Re: [sqlite] Variable Declaration
On Sat, 19 Jan 2019 12:01:34 -0700 "Keith Medcalf" 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
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
Re: [sqlite] Variable Declaration
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 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
On Sat, Jan 19, 2019, 6:53 AM Simon Slavin > On 19 Jan 2019, at 4:49am, Stephen Chrzanowski 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
es 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- >boun...@mailinglists.sqlite.org] On Behalf Of Luuk >Sent: Saturday, 19 January, 2019 06:49 >To: sqlite-users@mailinglists.sqlite.org >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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
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 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
On 19 Jan 2019, at 4:49am, Stephen Chrzanowski 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
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 wrote: > On 19 Jan 2019, at 3:57am, Stephen Chrzanowski > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
On 19 Jan 2019, at 3:57am, Stephen Chrzanowski 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
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 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- > >boun...@mailinglists.sqlite.org] 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 > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
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- >boun...@mailinglists.sqlite.org] 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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users