Re: [sqlite] New word to replace "serverless"
"When I use a word,' Humpty Dumpty said in rather a scornful tone, 'it means just what I choose it to mean - neither more nor less.' 'The question is,' said Alice, 'whether you can make words mean so many different things.' 'The question is,' said Humpty Dumpty, 'which is to be master - that's all." - Lewis Carroll, Through the Looking Glass On 28/01/2020 09:18, Richard Hipp wrote: For many years I have described SQLite as being "serverless", as a way to distinguish it from the more traditional client/server design of RDBMSes. "Serverless" seemed like the natural term to use, as it seems to mean "without a server". But more recently, "serverless" has become a popular buzz-word that means "managed by my hosting provider rather than by me." Many readers have internalized this new marketing-driven meaning for "serverless" and are hence confused when they see my claim that "SQLite is serverless". How can I fix this? What alternative word can I use in place of "serverless" to mean "without a server"? Note that "in-process" and "embedded" are not adequate substitutes for "serverless". An RDBMS might be in-process or embedded but still be running a server in a separate thread. In fact, that is how most embedded RDBMSes other than SQLite work, if I am not much mistaken. When I say "serverless" I mean that the application invokes a function, that function performs some task on behalf of the application, then the function returns, *and that is all*. No threads are left over, running in the background to do housekeeping. The function does send messages to some other thread or process. The function does not have an event loop. The function does not have its own stack. The function (with its subfunctions) does all the work itself, using the callers stack, then returns control to the caller. So what do I call this, if I can no longer use the word "serverless" without confusing people? "no-server"? "sans-server"? "stackless"? "non-client/server"? -- Regards John McMahon li...@jspect.fastmail.fm When people say "The climate has changed before," these are the kinds of changes they're talking about. https://xkcd.com/1732/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New word to replace "serverless"
Define what "serverless" means to you in the SQLite context and provide a link or pop-up to that definition wherever "serverless" occurs in the documentation. Perhaps also include what it doesn't mean if you think this is becoming an issue. How others choose to define "serverless" should not be your problem. Just my pennies worth, John On 28/01/2020 09:18, Richard Hipp wrote: For many years I have described SQLite as being "serverless", as a way to distinguish it from the more traditional client/server design of RDBMSes. "Serverless" seemed like the natural term to use, as it seems to mean "without a server". But more recently, "serverless" has become a popular buzz-word that means "managed by my hosting provider rather than by me." Many readers have internalized this new marketing-driven meaning for "serverless" and are hence confused when they see my claim that "SQLite is serverless". How can I fix this? What alternative word can I use in place of "serverless" to mean "without a server"? Note that "in-process" and "embedded" are not adequate substitutes for "serverless". An RDBMS might be in-process or embedded but still be running a server in a separate thread. In fact, that is how most embedded RDBMSes other than SQLite work, if I am not much mistaken. When I say "serverless" I mean that the application invokes a function, that function performs some task on behalf of the application, then the function returns, *and that is all*. No threads are left over, running in the background to do housekeeping. The function does send messages to some other thread or process. The function does not have an event loop. The function does not have its own stack. The function (with its subfunctions) does all the work itself, using the callers stack, then returns control to the caller. So what do I call this, if I can no longer use the word "serverless" without confusing people? "no-server"? "sans-server"? "stackless"? "non-client/server"? -- Regards John McMahon li...@jspect.fastmail.fm When people say "The climate has changed before," these are the kinds of changes they're talking about. https://xkcd.com/1732/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell commands for controlling headers
On 26/11/2019 02:49, David Raymond wrote: Dr Hipp replied to this 2 days ago with this: Documentation fix https://www.sqlite.org/docsrc/info/a2762f031964e774 will appears in the next release. ".header" is an abbreviation for ".headers" and does exactly the same thing. AFAIK all dot commands can be abbreviated to the shortest distinct partial word, thus ".headers on" can be shortened to ".hea on". This has been the case at least back to sqlite2 and back then, this was mentioned in the CLI documentation and is probably also somewhere in the current documentation. I leave finding it as an exercise for the reader. John -Original Message- From: sqlite-users On Behalf Of John McKown Sent: Monday, November 25, 2019 9:51 AM To: SQLite mailing list Subject: Re: [sqlite] Shell commands for controlling headers On Mon, Nov 25, 2019 at 8:42 AM Craig Maynard wrote: All, Could someone clarify the difference between the two sqlite3 shell commands .header and .headers? The relevant documentation page: https://www.sqlite.org/cli.html On the cli page, .header is discussed in section 5 but does not appear in Section 3. Thanks, Craig -- Craig H Maynard Rhode Island, USA In the sqlite cli itself, doing an ".help", I see: .header(s) So I am guessing that they are the same things, perhaps for compatibility with something in the past. -- Regards John McMahon li...@jspect.fastmail.fm When people say "The climate has changed before," these are the kinds of changes they're talking about. https://xkcd.com/1732/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Option to control implicit casting
On 11/04/2019 00:28, Joshua Thomas Wise wrote: This is not enough. Because of implicit casting, an integer (a precise value) could be passed through a series of operations that outputs an integer, satisfying the check constraint, but it still could’ve been converted to a floating point (imprecise value) at some intermediate step due to integer overflow, potentially resulting in an incorrect answer. There’s currently no way to guarantee that a value will always yield precise results in SQLite3. Here’s an example: CREATE TABLE squares ( x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'), y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer') ); INSERT INTO squares VALUES (1 << 40, 1 << 40); SELECT x * y & ~1 AS even_numbered_area FROM squares; Suggestion: "Don't Do That", use database purely as a storage medium. If the Integer values you want to store are greater than the 64bit values accepted by SQLite then store them as BLOBs. If the mathematical manipulations you wish to apply in your queries are beyond the scope of the built-in functions, then just return the stored values to your external programming environment and manipulate them there. You would seem to be working in an edge case environment, in which case it is your responsibility to make the adjustments. In many cases, it’s better for the above SELECT statement to return an error or NULL, but currently it gives an incorrect answer. Checking its type won’t help either, because it does indeed return an integer. On Apr 9, 2019, at 2:06 PM, James K. Lowden wrote: On Mon, 8 Apr 2019 23:08:18 -0400 Joshua Thomas Wise wrote: I propose there should be a compile-time option to disable all implicit casting done within the SQL virtual machine. You can use SQLite in a "strict" way: write a CHECK constraint for every numerical column. Just don't do that for tables that are loaded by the .import comand. As I reported here not long ago, .import rejects numeric literals. Apparently, the value is inserted as a string and rejected, instead of being converted to a number first. --jkl ___ 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 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
On 10/04/2019 18:28, Kees Nuyt wrote: On Wed, 10 Apr 2019 13:17:23 +1000, John wrote: I have not used extensions before. I understand that some are included in the amalgamation source file and that some of these are enabled by default. So, which ones are built-in and which of those are enabled in the standard downloadable Win32 SQLite CLI? By this above, I meant the pre-compiled CLI. sqlite> .mode column sqlite> .header on sqlite> .width 28 8 sqlite> select * from pragma_function_list; Thank you Kees, that didn't work (as noted by Luuk, Graham and Shawn) but it got me looking in the Pragma document and this did: sqlite> pragma compile_options: compile_options COMPILER=gcc-5.2.0 ENABLE_DBSTAT_VTAB ENABLE_FTS3 ENABLE_FTS5 ENABLE_JSON1 ENABLE_RTREE ENABLE_STMTVTAB ENABLE_UNKNOWN_SQL_FUNCTION THREADSAFE=0 sqlite> also this: sqlite> select * from pragma_compile_options; compile_options COMPILER=gcc-5.2.0 ENABLE_DBSTAT_VTAB ENABLE_FTS3 ... same thing, different method. If an extension is built-in and enabled, what do I need to do to use it. The instructions seem to be for the case where an extension is built as an external library (.dll) to be loaded by eg. .load ./csv where csv would be csv.dll in the current directory. If the csv extension was built-in, would I still need to load it to activate it? I don't think so. -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?
Hi, I have not used extensions before. I understand that some are included in the amalgamation source file and that some of these are enabled by default. So, which ones are built-in and which of those are enabled in the standard downloadable Win32 SQLite CLI? If an extension is built-in and enabled, what do I need to do to use it. The instructions seem to be for the case where an extension is built as an external library (.dll) to be loaded by eg. .load ./csv where csv would be csv.dll in the current directory. If the csv extension was built-in, would I still need to load it to activate it? John -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple way to import GPX file?
I know I am coming to this a couple of weeks late, but I have been doing this for several years and thought I would add my 2 cents worth. Probably too late for OP, but may be useful for someone else later. I used gpsbabel like this initially (in a JPSoft 4nt/tcmd script), gpsbabel -i gpx ^ -f %fname ^ -x nuketypes,tracks,routes ^ -o xcsv,style=G7W-xcsv.style ^ -F "%@name[%fname].csv" %fname - variable containing source file name %@name[ ... ] - function to extract basename from full filename with this style sheet to generate .csv files in my desired format. # gpsbabel XCSV style file # # Format: G7toWin csv format # Author: John McMahon # Date: 2005may24 # Update: 2006jun02jmcm # DESCRIPTION G7toWin csv file format # # FILE LAYOUT DEFINITIONS # FIELD_DELIMITER COMMA RECORD_DELIMITER NEWLINE BADCHARS COMMA SHORTLEN 10 PROLOGUE Version 2:CSV PROLOGUE Datum:,WGS-84 PROLOGUE ZoneOffset:,0.00 PROLOGUE "Type","Name","Lat","Long","Month\#","Day#","Year","Hour","Min","Sec","Comment","Symbol#","SymbolColor","SymbolDisplay","Altitude (Meters)","Depth (Meters)","Ref Dist","Ref units" # # INDIVIDUAL DATA FIELDS, IN ORDER OF APPEARANCE # IFIELD CONSTANT, "W", "%s"# "Type", IFIELD SHORTNAME, "", "%s"# "Name", IFIELD LAT_DECIMAL, "", "%f"# "Lat", IFIELD LON_DECIMAL, "", "%f"# "Long", IFIELD IGNORE,"", "%s"# "Month#", IFIELD IGNORE,"", "%s"# "Day#", IFIELD IGNORE,"", "%s"# "Year", IFIELD IGNORE,"", "%s"# "Hour", IFIELD IGNORE,"", "%s"# "Min", IFIELD IGNORE,"", "%s"# "Sec", IFIELD IGNORE,, "", "%s"# "Comment", IFIELD IGNORE,"", "%s"# "Symbol#", IFIELD IGNORE,"", "%s"# "SymbolColor", IFIELD IGNORE,"", "%s"# "SymbolDisplay", IFIELD IGNORE,"", "%s"# "Altitude (Meters)", IFIELD IGNORE,"", "%s"# "Depth (Meters)", IFIELD IGNORE,"", "%s"# "Ref Dist", IFIELD IGNORE,"", "%s"# "Ref units" However, I have recently replaced that with a perl script using the Geo::GPX module. John On 10/12/2018 10:17, no...@null.net wrote: On Sun Dec 09, 2018 at 03:16:15PM -0700, Winfried wrote: Good call, thank you. For others' benefit: 1. Copy the file, open the copy in a text editor, use a regex to turn the data into tab-separated columns If you are running some kind of unix-like environment this is something Perl can be quite useful for: grep '^(.*)!$1\t$2\t$3!' \ > waypoints.tsv 2. Create a new file, and create the table: sqlite3 waypoints.sqlite sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id INTEGER PRIMARY KEY); 3. Import data: sqlite> .separator "\t" sqlite> .import waypoints.tsv waypoints select * from waypoints where id=1; -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation Query/Correction
David My point point was that in one section of the documentation 'Type Affinity' was changed from 'NONE' to 'BLOB' with an explanatory note as to why and in another section it was unchanged. AFAIK type affinity of 'NONE' is the same as 'BLOB' as per the explanatory note. I was just bringing to attention what I thought was an inconsistency in the documentation. John On 05/04/2018 06:25, David Raymond wrote: Looks like when it goes and makes the table it doesn't give it an explicit "blob" type, as you would think from the phrase "When an expression is a simple reference to a column of a real table (not a VIEW or subquery) then the expression has the same affinity as the table column." It gives it no explicit type at all. However, according to... http://www.sqlite.org/datatype3.html#determination_of_column_affinity "3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB." ...that lack of any explicit column type will results in an implicit blob affinity. So I guess it still winds up as blob in the end, but in a roundabout way. Though it doesn't show up in things like pragma table_info. SQLite version 3.23.0 2018-04-02 11:04:16 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table foo (i int, nu numeric, r real, t text, b blob, n); sqlite> create table bar as select i, nu, r, t, b, n from foo; sqlite> select * from sqlite_master; type|name|tbl_name|rootpage|sql table|foo|foo|2|CREATE TABLE foo (i int, nu numeric, r real, t text, b blob, n) table|bar|bar|3|CREATE TABLE bar( i INT, nu NUM, r REAL, t TEXT, b, n ) sqlite> pragma table_info(foo); cid|name|type|notnull|dflt_value|pk 0|i|int|0||0 1|nu|numeric|0||0 2|r|real|0||0 3|t|text|0||0 4|b|blob|0||0 5|n||0||0 sqlite> pragma table_info(bar); cid|name|type|notnull|dflt_value|pk 0|i|INT|0||0 1|nu|NUM|0||0 2|r|REAL|0||0 3|t|TEXT|0||0 4|b||0||0 5|n||0||0 -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John McMahon Sent: Wednesday, April 04, 2018 3:54 PM To: SQLite Users Subject: [sqlite] Documentation Query/Correction In documentation for version 3.21.0: in datatypes3.html ... 3. Type Affinity ... Each column in an SQLite 3 database is assigned one of the following type affinities: TEXT NUMERIC INTEGER REAL BLOB (Historical note: The "BLOB" type affinity used to be called "NONE". But that term was easy to confuse with "no affinity" and so it was renamed.) and in lang_createtable.html ... CREATE TABLE ... AS SELECT Statements ... The declared type of each column is determined by the expression affinity of the corresponding expression in the result set of the SELECT statement, as follows: Expression Affinity Column Declared Type TEXT"TEXT" NUMERIC "NUM" INTEGER "INT" REAL"REAL" NONE"" (empty string) In the Expression Affinity table above, should the Expression Affinity 'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical note:' as per section '3. Type Affinity' in datatypes.html above. NOTE: I have checked the current on line documents and they match the above. For consideration. Regards, John -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation Query/Correction
In documentation for version 3.21.0: in datatypes3.html ... 3. Type Affinity ... Each column in an SQLite 3 database is assigned one of the following type affinities: TEXT NUMERIC INTEGER REAL BLOB (Historical note: The "BLOB" type affinity used to be called "NONE". But that term was easy to confuse with "no affinity" and so it was renamed.) and in lang_createtable.html ... CREATE TABLE ... AS SELECT Statements ... The declared type of each column is determined by the expression affinity of the corresponding expression in the result set of the SELECT statement, as follows: Expression Affinity Column Declared Type TEXT"TEXT" NUMERIC "NUM" INTEGER "INT" REAL"REAL" NONE"" (empty string) In the Expression Affinity table above, should the Expression Affinity 'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical note:' as per section '3. Type Affinity' in datatypes.html above. NOTE: I have checked the current on line documents and they match the above. For consideration. Regards, John -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] checking if a table exists
On 23/09/2017 05:36, mikeegg1 wrote:
I’m using the statement:
select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;
This statement works fine in the sqlite3 shell. This statement does not work in
my API.
Is there a PRAGMA I need to issue so I can check for table existence?
TIA
Mike
PERL code to check for table existence:
#> sub tableexists($$) {
prototyping depricated practice carried over from perl 4
sub tableexists {
my $dbh = shift;
my $name = shift;
my $tableexists = 0;
#> $dbh->do("pragma writable_schema = 'on';");
# neither recommended nor needed
#> my $sql = "select count(*) from sqlite_master where type =
'table' and name = '$name';";
my $sql = "select count(*) from sqlite_master where type = 'table' and
name = ?;";
my $stmt = $dbh->prepare($sql);
#> $stmt->execute or die "$0: verifying table name failed:
$DBI::errstr";
$stmt->execute($name)
or die "$0: verifying table name failed: $DBI::errstr";
while(my @row = $stmt->fetchrow_array) {
$tableexists = $row[0];
}
$stmt->finish;
#> $dbh->do("pragma writable_schema = 'off';");
return $tableexists;
}
Alternatively (not tested)
sub tableexists {
my $dbh = shift;
my $name = shift;
my $sql = "select count(*) from sqlite_master where type = 'table'
and name = '$name';";
# stmt only executed once, $name only evaluated once
my ($tableexists) = $dbh->selectrow_array($sql);
# selectrow returns 1 row, the stmt returns 1 element in list context
return $tableexists;
}
my $check_table = tableexists($dbh, $name);
John
--
Regards
John McMahon
li...@jspect.fastmail.fm
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Update With CTE
Thanks Keith On 23/08/2017 00:06, Keith Medcalf wrote: You could also -- if using a version of SQLite3 that supports row values (3.15.0 and later) -- do something like this: SQLite version 3.15.1 2016-11-04 12:08:49, I usually update near the end of year unless I see something particularly interesting, eg. CTEs when they were introduced. UPDATE CUSTOMERS SET (cust1, cust2, street, town, postcode) = (SELECT customer, NULL, address, town, postcode FROM test WHERE custnum = customers.custnum) WHERE custnum in (select custnum from test); It will get all the updates in a single correlated subquery rather than four ... Thank you, I just compared the drawings in "lang_update.html" for versions 3.10 and 3.15, I had missed that and it is functionality I was wishing for. --- 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 John McMahon Sent: Monday, 21 August, 2017 22:25 To: SQLite Users Subject: [sqlite] SQLite Update With CTE Hi I am rewriting an old Perl script that selectively updates data from one table to another using this statement: UPDATE CUSTOMERS SET cust1= ?, cust2= NULL, street = ?, town = ?, postcode = ? WHERE custnum = ? I am intending to replace it with something like this where 'test' is the CTE: UPDATE CUSTOMERS as c SET cust1= (select customer from test where custnum = c.custnum), cust2= NULL, street = (select address from test where custnum = c.custnum), town = (select town from test where custnum = c.custnum), postcode = (select postcode from test where custnum = c.custnum) WHERE custnum = (select custnum from test where custnum = c.custnum) My question is, do I need this part of the statement: WHERE custnum = (select custnum from test where custnum = c.custnum) when I have the other 'where custnum = c.custnum' clauses. I came across some Web examples that suggest that I might not. I haven't tested yet and am a little unsure. Any guidance would be appreciated. John -- Regards John McMahon li...@jspect.fastmail.fm ___ 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 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Update With CTE
On 22/08/2017 16:41, Clemens Ladisch wrote: John McMahon wrote: UPDATE CUSTOMERS as c SET cust1= (select customer from test where custnum = c.custnum), cust2= NULL, street = (select address from test where custnum = c.custnum), town = (select town from test where custnum = c.custnum), postcode = (select postcode from test where custnum = c.custnum) WHERE custnum = (select custnum from test where custnum = c.custnum) My question is, do I need this part of the statement: WHERE custnum = (select custnum from test where custnum = c.custnum) when I have the other 'where custnum = c.custnum' clauses. The WHERE clause on the UPDATE itself filters the rows that will be updated. If you know that "test" contains new values for all customers, you do not need the WHERE. But if you (might) update only a subset of customers, you need it. it doesn't And that last subquery is not used for assignment, so writing it in a different form might be clearer: WHERE EXISTS (SELECT * FROM test WHERE custnum = c.custnum) or WHERE custnum IN (SELECT custnum FROM test) And UPDATE does not support AS. So this UPDATE CUSTOMERS as c SET cust1= (select customer from test where custnum = c.custnum), cust2= NULL, ... should be UPDATE CUSTOMERS -- remove 'as c' SET cust1= (select customer from test where custnum = c.custnum), change to cust1= (select customer from test where custnum = CUSTOMERS.custnum), -- excuse the line wrap or perhaps cust1= (select customer from test as t where t.custnum = custnum), cust2= NULL, ... and end with WHERE custnum IN (SELECT custnum FROM test) Thank you, John Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Update With CTE
Hi I am rewriting an old Perl script that selectively updates data from one table to another using this statement: UPDATE CUSTOMERS SET cust1= ?, cust2= NULL, street = ?, town = ?, postcode = ? WHERE custnum = ? I am intending to replace it with something like this where 'test' is the CTE: UPDATE CUSTOMERS as c SET cust1= (select customer from test where custnum = c.custnum), cust2= NULL, street = (select address from test where custnum = c.custnum), town = (select town from test where custnum = c.custnum), postcode = (select postcode from test where custnum = c.custnum) WHERE custnum = (select custnum from test where custnum = c.custnum) My question is, do I need this part of the statement: WHERE custnum = (select custnum from test where custnum = c.custnum) when I have the other 'where custnum = c.custnum' clauses. I came across some Web examples that suggest that I might not. I haven't tested yet and am a little unsure. Any guidance would be appreciated. John -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
On 06/07/2017 17:01, Domingo Alvarez Duarte wrote: I already did this before but it was not accepted. For myself I did a modification on sqlite3 to allow the use of "AS" on delete/update statements. You can see the parser part here https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y . Cheers ! Thank you Domingo, but that option is beyond my programming competence. On 06/07/17 05:16, John McMahon wrote: Hi Wondering if someone else can spot the syntax error in the following statement. "locns" is an attached database. There are four "AS" terms in the statement, they all alias tables. Ok, found it. It seems that an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this? I would think it a convenience especially when using long table names and attached databases. John sqlite> UPDATE locns.xxx_last_delivery AS tgt ... > SET ... > tgt.del_date = ( ... > SELECT src.last_del_d ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum), ... > tgt.del_qty = ( ... > SELECT src.last_del_q ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum) ... > WHERE ... > tgt.custnum = ( ... > SELECT src.custnum ... > FROM main.updates AS src ... > WHERE src.last_del_d IS NOT NULL ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
On 06/07/2017 16:04, Paul Sanderson wrote: The SQLite syntax diagrams are my first point of call when looking at an error in my code like this. https://sqlite.org/lang_update.html "AS" and an alias are clearly not part of the statement. And that is how (with testing) I eventually worked out that I was on the wrong track. Thank you, Paul. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 6 July 2017 at 06:03, Keith Medcalf wrote: Do you know of any implementation of SQL that accepts an AS clause for the updated table? I don't think any do. Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however, as far as I know the update table cannot be aliased and the "set = ..." the must always be a column in the updated table and while you may be allowed to "adorn" it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as the updated table). -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John McMahon Sent: Wednesday, 5 July, 2017 21:17 To: SQLite Users Subject: [sqlite] syntax error near AS Hi Wondering if someone else can spot the syntax error in the following statement. "locns" is an attached database. There are four "AS" terms in the statement, they all alias tables. Ok, found it. It seems that an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this? I would think it a convenience especially when using long table names and attached databases. John sqlite> UPDATE locns.xxx_last_delivery AS tgt ... > SET ... > tgt.del_date = ( ... > SELECT src.last_del_d ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum), ... > tgt.del_qty = ( ... > SELECT src.last_del_q ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum) ... > WHERE ... > tgt.custnum = ( ... > SELECT src.custnum ... > FROM main.updates AS src ... > WHERE src.last_del_d IS NOT NULL ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error -- Regards John McMahon li...@jspect.fastmail.fm ___ 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 -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
On 06/07/2017 16:33, Clemens Ladisch wrote: John McMahon wrote: an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this? The UPDATE statement affects a single table. While an alias might be a convenience, it is not necessary (any naming conflicts in subqueries can be resolved by using an alias on the table(s) used there). Thank you Clemens, I see now, the need for no ambiguities when updating. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
On 06/07/2017 15:03, Keith Medcalf wrote: Do you know of any implementation of SQL that accepts an AS clause for the updated table? I don't think any do. No Keith, I don't. My only exposure to SQL is sqlite. Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however, as far as I know the update table cannot be aliased and the "set = ..." the must always be a column in the updated table and while you may be allowed to "adorn" it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as the updated table). -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
On 06/07/2017 17:01, Domingo Alvarez Duarte wrote: I already did this before but it was not accepted. For myself I did a modification on sqlite3 to allow the use of "AS" on delete/update statements. You can see the parser part here https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y . Cheers ! Thank you Domingo, but that option is beyond my programming competence. On 06/07/17 05:16, John McMahon wrote: Hi Wondering if someone else can spot the syntax error in the following statement. "locns" is an attached database. There are four "AS" terms in the statement, they all alias tables. Ok, found it. It seems that an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this? I would think it a convenience especially when using long table names and attached databases. John sqlite> UPDATE locns.xxx_last_delivery AS tgt ... > SET ... > tgt.del_date = ( ... > SELECT src.last_del_d ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum), ... > tgt.del_qty = ( ... > SELECT src.last_del_q ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum) ... > WHERE ... > tgt.custnum = ( ... > SELECT src.custnum ... > FROM main.updates AS src ... > WHERE src.last_del_d IS NOT NULL ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
On 06/07/2017 16:33, Clemens Ladisch wrote: John McMahon wrote: an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this? The UPDATE statement affects a single table. While an alias might be a convenience, it is not necessary (any naming conflicts in subqueries can be resolved by using an alias on the table(s) used there). Thank you Clemens, I see now, the need for no ambiguities when updating. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
On 06/07/2017 16:04, Paul Sanderson wrote: The SQLite syntax diagrams are my first point of call when looking at an error in my code like this. https://sqlite.org/lang_update.html "AS" and an alias are clearly not part of the statement. And that is how (with testing) I eventually worked out that I was on the wrong track. Thank you, Paul. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 6 July 2017 at 06:03, Keith Medcalf wrote: Do you know of any implementation of SQL that accepts an AS clause for the updated table? I don't think any do. Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however, as far as I know the update table cannot be aliased and the "set = ..." the must always be a column in the updated table and while you may be allowed to "adorn" it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as the updated table). -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John McMahon Sent: Wednesday, 5 July, 2017 21:17 To: SQLite Users Subject: [sqlite] syntax error near AS Hi Wondering if someone else can spot the syntax error in the following statement. "locns" is an attached database. There are four "AS" terms in the statement, they all alias tables. Ok, found it. It seems that an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this? I would think it a convenience especially when using long table names and attached databases. John sqlite> UPDATE locns.xxx_last_delivery AS tgt ... > SET ... > tgt.del_date = ( ... > SELECT src.last_del_d ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum), ... > tgt.del_qty = ( ... > SELECT src.last_del_q ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum) ... > WHERE ... > tgt.custnum = ( ... > SELECT src.custnum ... > FROM main.updates AS src ... > WHERE src.last_del_d IS NOT NULL ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error -- Regards John McMahon li...@jspect.fastmail.fm ___ 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 -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] syntax error near AS
On 06/07/2017 15:03, Keith Medcalf wrote:
Do you know of any implementation of SQL that accepts an AS clause for the
updated table? I don't think any do.
No Keith, I don't. My only exposure to SQL is sqlite.
Some versions have a FROM extension and you CAN specify an alias for the updated table in that clause, however,
as far as I know the update table cannot be aliased and the "set = ..." the
must always be a column in the updated table and while you may be allowed to "adorn"
it in some implementations, any adornments are ignored (or trigger an error message if they are not the same as
the updated table).
--
Regards
John McMahon
j...@jspect.fastmail.com.au
04 2933 4203
--
Regards
John McMahon
li...@jspect.fastmail.fm
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] syntax error near AS
Hi Wondering if someone else can spot the syntax error in the following statement. "locns" is an attached database. There are four "AS" terms in the statement, they all alias tables. Ok, found it. It seems that an alias for an "UPDATE" table name is not permitted. Is there a particular reason for this? I would think it a convenience especially when using long table names and attached databases. John sqlite> UPDATE locns.xxx_last_delivery AS tgt ... > SET ... > tgt.del_date = ( ... > SELECT src.last_del_d ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum), ... > tgt.del_qty = ( ... > SELECT src.last_del_q ... > FROM main.updates AS src ... > WHERE src.custnum = tgt.custnum) ... > WHERE ... > tgt.custnum = ( ... > SELECT src.custnum ... > FROM main.updates AS src ... > WHERE src.last_del_d IS NOT NULL ... > ANDsrc.last_del_d > tgt.del_date) ... > ; Error: near "AS": syntax error -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Request for ISO Week in strftime()
Sorry, re-sending to list. Point of Clarification: The ISO Week begins as day 1 on Monday and ends as day 7 on Sunday, hump day (colloq.) is Thursday. There may be other repercussions in terms of week counts if this has not been implemented correctly. I haven't checked, I do not use this personally. John On 17/05/2017 19:07, no...@null.net wrote: The current '%W' week substitution appears to be US-specific. I would like to make a feature request for a '%V' (or similar) substitution that inserts the ISO-8601 week number. -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with rename table
Thanks Scott for that explanation. John On 11/09/2016 01:27, Scott Robison wrote: On Sep 10, 2016 2:54 AM, "John McMahon" wrote: On 08/09/2016 10:09, Bob McFarlane wrote: Please reply if you sent this. Thanks. Hmm, looks like a fishing exercise to me. Same message in several threads. This reply only to mailing list. It's an anti-spam measure. Most spam will either not get the auto generated message, or if it is a forgery the victim has a chance to disavow the content. I think it is too heavy handed a technique personally, but to each their own. I sent the sender an email letting him know that his anti-spam system was spamming the crap out of the list and he fixed it. Easy peasy. John -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, September 7, 2016 7:49 PM To: SQLite mailing list Subject: Re: [sqlite] Problem with rename table On 9/6/16, Radovan Antloga wrote: Hi Richard ! I can't find a solution how to fix my database after I have renamed table DOKUMENTI to DOKUMENTI2. Table DOKUMENTI had trigger dokumenti_trigger1 and after renaming table I cant execute any sql. I forgot to drop trigger first. So now I always get error: malformed database schema (dokumenti_trigger1) - no such table main.dokumenti. Rename the table back to its old name? Worst case: You can drop all the triggers like this: PRAGMA writable_schema=ON; DELETE FROM sqlite_master WHERE type='trigger'; Then close and reopen your database, and you have no more triggers. The same will work for views. But if you try the above with tables or indexes, you'll end up with a database that fails "PRAGMA integrity_check" - though the corruption can be fixed with a VACUUM. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users TMGID:S1141121912621011 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon li...@jspect.fastmail.fm ___ 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 -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with rename table
On 08/09/2016 10:09, Bob McFarlane wrote: Please reply if you sent this. Thanks. Hmm, looks like a fishing exercise to me. Same message in several threads. This reply only to mailing list. John -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, September 7, 2016 7:49 PM To: SQLite mailing list Subject: Re: [sqlite] Problem with rename table On 9/6/16, Radovan Antloga wrote: Hi Richard ! I can't find a solution how to fix my database after I have renamed table DOKUMENTI to DOKUMENTI2. Table DOKUMENTI had trigger dokumenti_trigger1 and after renaming table I cant execute any sql. I forgot to drop trigger first. So now I always get error: malformed database schema (dokumenti_trigger1) - no such table main.dokumenti. Rename the table back to its old name? Worst case: You can drop all the triggers like this: PRAGMA writable_schema=ON; DELETE FROM sqlite_master WHERE type='trigger'; Then close and reopen your database, and you have no more triggers. The same will work for views. But if you try the above with tables or indexes, you'll end up with a database that fails "PRAGMA integrity_check" - though the corruption can be fixed with a VACUUM. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users TMGID:S1141121912621011 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Regards John McMahon li...@jspect.fastmail.fm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing date string 'YYYYMMDD'
On 01/04/2012 12:34 PM, Igor Tandetnik wrote: John wrote: Can the date time functions in SQLite correctly interpret a date string like '20120331'? No. Recognized formats are documented here: http://sqlite.org/lang_datefunc.html Is there a format or modifier that will help the function interpret/convert that as '2012-03-31'? Well, you could write something like select substr(d, 1, 4) || '-' || substr(d, 5, 2) || '-' || substr(d, 7) from (select '20120331' as d); Thanks Igor (and Simon) That is pretty much the path I was thinking of taking (or possibly externally as I import the data), was just wondering if I had missed something in the date functions. John -- Regards John McMahon j...@jspect.fastmail.com.au 04 2933 4203 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
