Re: [sqlite] .exit in script is not working
On Fri, 31 Aug 2018 19:29:47 +0200, Stéphane AULERY wrote: > When I use the -init option the .exit statement > at the end of my script is never executed. > > I launch sqlite from a batch file on Windows Server 2012 and Windows 7 64 bit. > > Command line : > sqlite3.exe -init myscript.scr If you want a script to exit, use redirection: sqlite3.exe http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lemon Parser vs bubble-generator.tcl
On 8/31/18, Warren Young wrote: > They’re separate. Here’s the Tcl source for the bubble diagrams: > > https://www.sqlite.org/docsrc/file/?name=art/syntax/bubble-generator-data.tcl > > …and here’s the Lemon grammar for SQLite’s SQL parser: > > https://www.sqlite.org/src/file?name=src/parse.y > The parse.y file is designed to generate a fast parser automaton. The bubble-generator-data.tcl file is designed to generate output that is easily understandable by humans. These two files are therefore at cross-purposes. One little-noticed aspect of SQLite that the source code to the LALR(1) parser generator (Lemon) is included in the source tree. The fact that SQLite uses its own parser generator, rather than relying yacc/bison or similar, has proven very helpful over the years. We've been able to add features, fix bugs, and make optimizations to the parser that would not have been possible had we been dependent on an outside tool. -- 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
Re: [sqlite] Lemon Parser vs bubble-generator.tcl
On Fri, Aug 31, 2018 at 2:59 PM Warren Young wrote: > > On Aug 31, 2018, at 1:55 PM, Scott Robison wrote: > > > > Is one generated from the other, or are they maintained separately? > > They’re separate. Here’s the Tcl source for the bubble diagrams: As I suspected having looked at them in the source tree. Thanks for the confirmation. -- Scott Robison ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lemon Parser vs bubble-generator.tcl
On Aug 31, 2018, at 1:55 PM, Scott Robison wrote: > > Is one generated from the other, or are they maintained separately? They’re separate. Here’s the Tcl source for the bubble diagrams: https://www.sqlite.org/docsrc/file/?name=art/syntax/bubble-generator-data.tcl …and here’s the Lemon grammar for SQLite’s SQL parser: https://www.sqlite.org/src/file?name=src/parse.y Here’s a brief article on the script that may explain a few things: https://wiki.tcl.tk/21708 > I...want to do something related to > identifying SQL syntax at C++ compile time. In that case, I’d study how ./src/parse.y gets turned into ./parse.[ch] in the SQLite source tree, then try to reuse as much of parse.y and its associated bits as you can. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .exit in script is not working
Hello, When I use the -init option the .exit statement at the end of my script is never executed. I launch sqlite from a batch file on Windows Server 2012 and Windows 7 64 bit. Command line : sqlite3.exe -init myscript.scr myscript.scr : .log sqlite.log .bail off .nullvalue "" CREATE TABLE STOCKSMAG ( Article TEXT, CodeDepot TEXT, QteStock INTEGER, ECclient INTEGER, Dispo INTEGER, ECfour INTEGER, Proj INTEGER, DateGen DATETIME, SeuilReappro INTEGER ); .import STOCKSMAG.txt STOCKSMAG .output STOCKSCUMUL.txt SELECT Article, SUM (QteStock) AS QteStock, SUM (ECclient) AS ECclient, SUM (Dispo) AS Dispo, SUM (ECfour) AS ECfour, SUM (Proj) AS Proj FROM STOCKSMAG GROUP BY Article ORDER BY Article; .exit STOCKSMAG.txt : Article|CodeDepot|QteStock|ECclient|Dispo|ECfour|Proj|DateGen|SeuilReappro 01|117|0|0|0|0|0|30/08/2018| 01|118|0|0|0|0|0|30/08/2018| 01|121|0|0|0|0|0|30/08/2018| 01|125|0|0|0|0|0|30/08/2018| Regards, -- Stéphane Aulery Développeur Service informatique Narbonne Accessoires 04.68.44.16.54 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lemon Parser vs bubble-generator.tcl
SQLite language as implemented uses a Lemon based parser. The syntax diagrams are created from the bubble-generator.tcl script. Is one generated from the other, or are they maintained separately? Is one (or another file that I don't know exists yet) the canonical description of the "complete" SQLite syntax? I ask because I'm working on a project idea for a senior project class I'll be taking this fall and want to do something related to identifying SQL syntax at C++ compile time. In doing a little research / planning, I started by creating my own syntax description from some of the diagrams, then realized "I should just use portions of the syntax diagram script directly", then started wondering about the parser vs the diagram script. -- Scott Robison ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies to reduce page-loads?
Right. Was trying it out now, compiling some osm-dbs with primary key generated with this morton encoding from lat,lon and the performance is even worse. Debugging with the sqlite-tool shows, that the page counts for specific queries are almost double then before. Seems like, from the sqlite-side the only options is to have page size as big as possible and line-data in the blob-field as much compressed as possible. -- Originalnachricht -- Von: "Simon Slavin" An: "SQLite mailing list" Gesendet: 31.08.2018 19:07:36 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 31 Aug 2018, at 2:46pm, J Decker wrote: > >>There was a voxel engine that was claiming they were going to move to >>a >>morton encoding; and I was working with a different engine, so I built >>a >>simulator to test averge lookup distances; it was far more efficient >>to >>keep sectors of voxels (32x32x32) in flat indexing, which made the >>maximum >>distance 1025 > >I can confirm that SatNav units do not keep their maps in Morton code >order. It's not a no-brainer go-to solution for mapping. However, the >analysis done to figure out a good storage order is rather complicated >and off-topic for this list. > >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] Strategies to reduce page-loads?
On 31 Aug 2018, at 2:46pm, J Decker wrote: > There was a voxel engine that was claiming they were going to move to a > morton encoding; and I was working with a different engine, so I built a > simulator to test averge lookup distances; it was far more efficient to > keep sectors of voxels (32x32x32) in flat indexing, which made the maximum > distance 1025 I can confirm that SatNav units do not keep their maps in Morton code order. It's not a no-brainer go-to solution for mapping. However, the analysis done to figure out a good storage order is rather complicated and off-topic for this list. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best practices for forward conversion of database formats?
Randall wrote: ~ ~ > Any application that involves a "persistent" database, i.e., one where the data is > long-lived and expected to exist and evolve over time, sooner or later has the issue > of moving customers from a V1 database to a V2 database. Obviously at least one > technical issue (there are probably others) is how to convert existing data from one > DB format to another, given some arbitrary set of changes in the database schema in > the interim. > Are there accepted or best practices in the industry for handling this in general, > or with SQLite in particular? Can anyone who has implemented this make useful > suggestions? Or are there published resources I am missing? Hello, I should suppose pretense with I'm wholly ignorant to such a process, but even has such may have some wisdom. The only reason I'm replying because I have been working on I guess an ORM, data converter it seems. One, it seems data is best preserved in a standard raw format such as CSV. SQL statements from one database do not translate to another databases well and perhaps of the same database v1 to v2. Two, with changes to schema it seems the simple way to go would be export data in a query fashion, to match the new schema. Leave out columns not needed and a default for new. Three, this in hindsight, but data should be always be in it simplest form as possible, Integer, Char, etc. The most intolerant data is temporal information. For these ALWAYS use a SQL or a common appropriate standard. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies to reduce page-loads?
On Thu, Aug 30, 2018 at 4:48 AM Richard Hipp wrote: > On 8/30/18, t...@qvgps.com wrote: > > > > Structure is simple: > > CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates > > BLOB, Flags INT, StyleId INT); > > And an rtree-index: > > CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0 > > FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT); > > Three points that might help, either separately or in combination: > > (1) Use exactly "INTEGER PRIMARY KEY". "LONG PRIMARY KEY" and "INT > PRIMARY KEY" are not the same thing and do not work as well. > > (2) In the very latest versions of SQLite, 3.24,0 and the beta for > 3.25.0, you can put the "Lines" information directly in the RTree: > >CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0, > z1, +Label, +Coordinates, +Flags, +StyleId); > > The extra columns in r-tree are prefaced by a "+" character so that > the r-tree module knows that they are auxiliary columns and not extra > coordinates. > > (3) Consider deliberating choosing INTEGER PRIMARY KEY values based on > a "Morton code" or "Z-Order curve" of the coordinates. > Morton code and zorder curves look, initially, to be good for keeping near things near, but the average distance for two elements is much greater than the row skip in a similar linear col + n*rows type flat array; and more often will span +4 distinct pages instead of being just 1 or 2 for here and +/- 1 page for rows above/below On the 2^n boundaries, the skips become very significant, and grabbing random queries is guaranteed to cover more space. so like at 15,15,15 to 16,16,16 there is a huge jump in space ( 4096) instead of a constant offset. There was a voxel engine that was claiming they were going to move to a morton encoding; and I was working with a different engine, so I built a simulator to test averge lookup distances; it was far more efficient to keep sectors of voxels (32x32x32) in flat indexing, which made the maximum distance 1025 ( but along that row is +1023, 1024, 1025, which are all in the same page, where morton would be like +512, +1024, +2048, which makes it much more likely to overflow to yet another page. (since the cells arent' just bytes, all indexes should be mulitplied by cell structure size) (https://en.wikipedia.org/wiki/Z-order_curve) That will cause > features that are close together geographically to tend to be close > together within the file. There is are two extension functions in the > https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the > SQLite source tree that might help you with this. Or you can do the > same using your own functions. > -- > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies to reduce page-loads?
On 8/31/18, t...@qvgps.com wrote: > Ok, then WITHOUT ROWID will most properly fit best in our use case. > Then I can fill the PRIMARY KEY with the z-order and store the osm-id > just in another column. I would think that your best approach is to make the INTEGER PRIMARY KEY be the Morton code and then store the osm-id in a separate UNIQUE column. > > But do I still need to fill the table in the correct order according to > z-order? > I mean, we are talking about 1mio rows or so. > At which point during insert are the pages actually written? The table-fill will go much faster if you do the inserts in ROWID or INTEGER PRIMARY KEY order, then do a "CREATE UNIQUE INDEX" on the osm-id after all inserts have completed. But it will work in either case. -- 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
Re: [sqlite] Strategies to reduce page-loads?
Ok, then WITHOUT ROWID will most properly fit best in our use case. Then I can fill the PRIMARY KEY with the z-order and store the osm-id just in another column. But do I still need to fill the table in the correct order according to z-order? I mean, we are talking about 1mio rows or so. At which point during insert are the pages actually written? -- Originalnachricht -- Von: "Richard Hipp" An: "SQLite mailing list" Gesendet: 31.08.2018 15:10:15 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/31/18, t...@qvgps.com wrote: >> >>So is it just the value of the primary key controlling in which page >>the >>row is stored? > >The page on which content is stored is determine (approximately) by >the value of the ROWID, which is the same as the INTEGER PRIMARY KEY >as long as you declare the primary key to be of type "INTEGER". If >you declare the PRIMARY KEY to be something other than "INTEGER" (for >example: "INT" or "LONG") then the ROWID and the primary key are >different and the primary key has no influence over where the content >is stored. > >Or if you create a WITHOUT ROWID table, then the PRIMARY KEY is the >value that determines (approximately) on which page the content is >stored. >-- >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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies to reduce page-loads?
On 8/31/18, t...@qvgps.com wrote: > > So is it just the value of the primary key controlling in which page the > row is stored? The page on which content is stored is determine (approximately) by the value of the ROWID, which is the same as the INTEGER PRIMARY KEY as long as you declare the primary key to be of type "INTEGER". If you declare the PRIMARY KEY to be something other than "INTEGER" (for example: "INT" or "LONG") then the ROWID and the primary key are different and the primary key has no influence over where the content is stored. Or if you create a WITHOUT ROWID table, then the PRIMARY KEY is the value that determines (approximately) on which page the content is stored. -- 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
Re: [sqlite] Strategies to reduce page-loads?
>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve) That will cause
>features that are close together geographically to tend to be close
>together within the file.
My primary key is actually the Id of the specific object in the
OpenStreetMap-database, and we also need this osm-id in the app.
I was trying just to order all rows by the "Z-Order curve"-value first
before inserting them.
for each (line in lines.ordered.by.zvalue)
{
insert line into lines
insert line into lines_rtree
}
But this doesn't seem to work.
At least, I don't see any improvements in page-usage.
So is it just the value of the primary key controlling in which page the
row is stored?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [O] A strange problem with org-babel and SQLite
Oops, wrong group. Sorry. :'-( 2018-08-31 12:21 GMT+02:00 Cecil Westerhof : > 2018-08-31 11:17 GMT+02:00 Robert Klein : > >> Hi Cecil, >> >> On Fri, 31 Aug 2018 10:47:50 +0200 >> Cecil Westerhof wrote: >> >> > I have a strange problem with org-babel and SQLite. >> > >> > I have a database that is created with: >> > CREATE TABLE "quotes" ( >> > quoteID TEXT PRIMARY KEY, >> > quote TEXT NOT NULL UNIQUE, >> > lastUsedTEXT, >> > totalUsed INT DEFAULT 'unused' >> > ) >> > >> > When using: >> > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes >> > SELECT lastUsed >> > ,totalUsed >> > FROM quotes >> > ORDER BY lastused ASC >> > ,totalUsed DESC >> > LIMIT40 >> > #+END_SRC >> > >> > Everything is fine. But when I use (add the quote field in the >> > select): #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes >> > SELECT quote >> > ,lastUsed >> > ,totalUsed >> > FROM quotes >> > ORDER BY lastused ASC >> > ,totalUsed DESC >> > LIMIT40 >> > #+END_SRC >> > >> > I get: >> > executing Sqlite code block... >> > Wrote /tmp/babel-27920y_/ob-input-2792BTG >> > org-babel-read: End of file during parsing >> > >> > What could be the problem? >> > >> >> does it work outside of org/babel/emacs, that is, when you use the >> query in a command line sqlite session, does it work? “quote” is also >> a function in sqlite, so this might be your issue. >> > > Yes, in sqlite3 and sqlitebrowser it works without problems. > In org-babel even 'SELECT *' goes wrong. > > -- > Cecil Westerhof > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [O] A strange problem with org-babel and SQLite
2018-08-31 11:17 GMT+02:00 Robert Klein : > Hi Cecil, > > On Fri, 31 Aug 2018 10:47:50 +0200 > Cecil Westerhof wrote: > > > I have a strange problem with org-babel and SQLite. > > > > I have a database that is created with: > > CREATE TABLE "quotes" ( > > quoteID TEXT PRIMARY KEY, > > quote TEXT NOT NULL UNIQUE, > > lastUsedTEXT, > > totalUsed INT DEFAULT 'unused' > > ) > > > > When using: > > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes > > SELECT lastUsed > > ,totalUsed > > FROM quotes > > ORDER BY lastused ASC > > ,totalUsed DESC > > LIMIT40 > > #+END_SRC > > > > Everything is fine. But when I use (add the quote field in the > > select): #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes > > SELECT quote > > ,lastUsed > > ,totalUsed > > FROM quotes > > ORDER BY lastused ASC > > ,totalUsed DESC > > LIMIT40 > > #+END_SRC > > > > I get: > > executing Sqlite code block... > > Wrote /tmp/babel-27920y_/ob-input-2792BTG > > org-babel-read: End of file during parsing > > > > What could be the problem? > > > > does it work outside of org/babel/emacs, that is, when you use the > query in a command line sqlite session, does it work? “quote” is also > a function in sqlite, so this might be your issue. > Yes, in sqlite3 and sqlitebrowser it works without problems. In org-babel even 'SELECT *' goes wrong. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best practices for forward conversion of database formats?
> This is not strictly a SQLite question, though in my case it is. > > Any application that involves a "persistent" database, i.e., one where the > data is long-lived and expected to exist and evolve over time, sooner or later > has the issue of moving customers from a V1 database to a V2 database. Several have given examples of holding a schema version number in the database and having your application know how to upgrade from one to the next. I have used similar mechanisms, but one thing I would say. If you have to maintain older versions of your app, make a jump in the schema version when you switch major version of you app. For example, if you have version 1.5 of your app and you move to version 2.0, then you need to make a version 1.6 for some reason and it needs a schema change. If you jumped to say, schema version 100 for app 2.0, it is much easier to add a new schema version for 1.6 that can then be handled by 2.x Also, one system I worked on used SQL script files to make the database changes. e.g. There would be a script that updated from schema 2 to 3. To go with this there would also be a script that downgraded from schema 3 to 2. These scripts where left by the installer, so if you needed to install an earlier version of code, it could find the "downgrade" scripts and run them without the earlier version of code having to know how to downgrade. HTH Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] beginners question
email signature Klaus Maas On 2018-08-30 21:39, Jürgen Palm wrote: Richard Hipp wrote: On 8/30/18, Jürgen Palm wrote: Richard Hipp wrote: On 8/30/18, Klaus Maas wrote: Same issues on Xubuntu 16.04 and 18.04 US international keyboard layout Actually 2 questions: 1) The precompiled binary for Linux of sqlite3 does not run, but sqldiff and sqlite3_analyzer do. ./sqlite3: error while loading shared libraries: libz.so.1: cannot open shared object file: No such file or directory What steps can I take to provide the missing library? You have a desktop linux machine on which libz is not installed? I didn't even know that was possible. What flavor of linux are you running? He mentioned it in the first sentence: Xubuntu 16.04 and 18.04. Of course, libz.so.1 is installed. I've currently running a virtual machine with Xubuntu 18.04 and tried also to execute the precompiled binary and it didn't work, but I didn't actually expect it as I'm running a 64bit system. Most likely the problem from Klaus is caused by the same reason. So, perhaps the solution is for Klaus to compile it himself from sources? If he is indeed running a 64bit system he could also just install the package zlib1g:i386, which contains the 32bit version of libz.so.1. With this package installed, the precompiled sqlite binary is running properly on my 64bit system. Dear Jürgen, I am running 64-bit versions of Xubuntu as you assumed correctly. Thank you for the tip - and for providing the lib name explicitly. "sudo apt install zlib1g:i386" did the trick. The precompiled Linux sqlite version 3.24.0 starts as expected and the keyboard codes of the navigation keys are interpreted correctly. Tried it on Xubuntu 16.04 so far only - still my main system. What puzzled me at first was that neither apt policy nor apt search would show me the lib, even when requesting it litterally. Then I remembered that on a 64-bit OS the 32-bit libraries are filtered out by default. Now that the 32bit lib is installed apt policy will show its status. Thank you all for helping me so swiftly. Klaus ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
