Re: [sqlite] Bug: float granularity breaking unique contraint?
It is not a bug AFAIK. SQLite uses what the documentation calls dynamic typing for its actual values. So if you are inserting integers into a real column, you are going to store integers. However, when you select from it they are presented as reals and mix up (looking as if there were duplicates, even though the stored values are truly unique). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite for datalogging - best practices
On Wednesday, 31 October, 2018 13:22, Gerlando Falauto wrote: >first of all let me tell you I cannot thank you enough for going >through all this trouble for me! No problem. I still really do not know if the answer is correct however it does let you get data about how much data is actually being moved around. >It did not occur to me it would help to run checkpoints manually. >There's a couple of things I don't understand though. >- After a checkpoint runs to completion (assuming no other process is >reading nor writing), what is the value of walpages (i.e. *pnLog)? Is >it the number of pages *originally* in the logfile (i.e. the same as >copypages), or is it the number of pages *remaining* in the logfile >(i.e. zero)? Also, does it refer to the number of *valid* pages or the >number of pages allocated by the file (i.e. fllesize divided by pagesize)? The table generated by the passive checkpoints looks like this: sqlite> select * from tracer order by timestamp desc limit 5; timestamp status walpagescopypages -- -- -- -- 1541032800 0 14621462 1541032740 0 14571457 1541032680 0 14521452 1541032620 0 14541454 1541032560 0 14491449 I presume that the walpages means the number of frames (pages) in the WAL file BEFORE the checkpoint and that copypages is the number of those frames copied to the database (the difference being the number left in the wal file). If not all pages can be copied (for example a read transaction is blocking them) then I would expect that eventually they will be copied. Therefore the copypages number is really the only useful one (all those pages must have been written to the WAL and must eventually be copied to the main database) and the fact that some of the pages could not be copied at some particular instant is not really useful for determining the actual total amount of data moved. Whatever is in copypages had to be written to the wal and has to be written to the db (written twice, erasing the equivalent number of pages). When exactly that happens is not important, I don't think. >- How often are you running checkpoints? Do you expect this variable >to have an impact on the end result? Currently I let it run once per minute with the following set for the database right after it is opened: pragma cache_size=262144;# 1 Gigabyte pragma journal_mode=wal; # Set WAL Mode pragma wal_autocheckpoint=0; # Disable Autocheckpoint pragma journal_size_limit=8388608; # Truncate the WAL on checkpoint if possible to 8 MB pragma synchronous=NORMAL; # Only force sync on checkpoint not each commit I don't think that the frequency of checkpointing will have much of a total effect on the overall rate of change, however, it does affect the size of each checkpoint and the amount of data that could be lost (the amount between checkpoints) on system failure. >Anyway, I'll definitely try the same approach on the current schema >(which >is *way* different than your super-optimized version) just to see if >it is >more or less consistent with the trend reported by smartmontools. If >that's >the case, that makes for a much more convenient metric to estimate >performance and compare implementations. I just have an asynchronous generator that generates the incoming data in a queue, and I simply sit waiting on the queue for stuff to process, and log the checkpoint statistics into the same database. That is, I wait up to 250 ms for something to arrive and if it does not then "commit" if a transaction is open (which results in a commit once per second) and then I check if the unix epoch time is a multiple of 60 (time % 60 == 0) and if so do a passive wal_checkpoint and record the results then block waiting for data. If I did get data from one or the other waits I open a transaction if necessary and insert the data. Repeat forever. The checkpoint log is in the same database and takes up a few bytes, but you then always have data available to see how many database pages are being moved around. The tables are "trimmed" (delete old data) each time a transaction is opened. Also, the page size of the database is 4K and each row is 4K or a bit more. I wonder if either compressing the data somehow or increasing the database page size to ensure each row fits on a page may make any significant difference. >So really, thanks a lot! No problem. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: float granularity breaking unique contraint?
Dear SQLite people, Please bless me with your infinite wisdom. I'm using SQLite 3.25.2 on Windows, downloaded the latest precompiled binaries from the official page https://sqlite.org/download.htmlExecuting the following code DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL UNIQUE);INSERT INTO TestReal values (9223372036854775807.0);INSERT INTO TestReal values (9223372036854775807.0 - 1);INSERT INTO TestReal values (9223372036854775807.0 - 2);INSERT INTO TestReal values (9223372036854775807.0 - 3); fails as expected, since 9223372036854775807.0 is 2^63, these numbers are way out of the range where all integers are exactly representable as doubles. I mean sqlite> select 9223372036854775807.0 = 9223372036854775807.0 - 1;1 sqlite> select 9223372036854775807.0 = 9223372036854775807.0 - 512;1 And column A is unique, so it makes perfect sense to print a 'UNIQUE constraint failed: TestReal.A' message. But there seems to be an unintended workaround DROP TABLE IF EXISTS TestReal;CREATE TABLE TestReal(A REAL UNIQUE);INSERT INTO TestReal values (9223372036854775807);INSERT INTO TestReal values (9223372036854775807 - 1);INSERT INTO TestReal values (9223372036854775807 - 2);INSERT INTO TestReal values (9223372036854775807 - 3); runs without any problems. The following queries confirm that the table now has exactly 4 values inserted, but only one distinct value, despite of having a unique constraint sqlite> SELECT * FROM TestReal;9.22337203685478e+189.22337203685478e+189.22337203685478e+189.22337203685478e+18 sqlite> SELECT DISTINCT(A) FROM TestReal;9.22337203685478e+18 sqlite> .schemaCREATE TABLE TestReal(A REAL UNIQUE); So my question is: is this a bug in SQLite? Or do I not understand correctly what 'unique' actually means? I posted an identical question on StackOverflow.com, because I'm still not 100% sure it is a bug. If so, sorry for wasting you time. Sincerely, Máté Szabó ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] use column alias in same SELECT
Thank you very much for the detailed answer. I now understand things better and can better accept that they are how they are :-)) - Original Message - From: R Smith To: sqlite-users@mailinglists.sqlite.org Sent: Wednesday, October 31, 2018, 21:34:30 Subject: [sqlite] use column alias in same SELECT On 2018/10/31 9:21 PM, Simon Slavin wrote: > On 31 Oct 2018, at 7:14pm, Thomas Kurz wrote: >> may I ask whether there is a deeper reason why using a column alias in the >> same SELECT query doesn't work, e.g. >> SELECT column1 AS a, 5*column2 AS b, a+b FROM... > The canonical answer to this is that there's nothing in the SQL specification > that says the columns have to be evaluated in left-to-right order. > Theoretically a computer which had massive parallelism could fetch the data > from the database, then work out all the columns at the same time. Simon is correct, and to add to it with some bit of expansion (I assume you asked because you are interested in the answer): The rules for identifiers used in expressions are very strict (it's math after all), and the rules for aliases are by design very loose. Set algebra further imposes no order on items. If a set contains A, B and C... and you asked for everything in the set, you can receive it in any order. (Most DB engines stick to the table-template order as a courtesy, but they are not forced to do so). To explain the strictness problem, imagine a query like SELECT C+A as x, B+A as 'x', C+B as x FROM t Those are all perfectly valid Aliases - but you can see how it will never conform to the strict expression identifier rules. You cannot for instance write a simple Algebraic expression that would explain what value the added x2 column will hold if that query was adjusted to be: SELECT C+A as x, B+A as 'x', C+B as x, x+x as x2 FROM t Further, as Simon mentioned, you cannot be be sure that x+x won't (try to) be evaluated before C+A for the same query. "But..." I hear you ask, "can we not just use the Aliases if they do conform? and can we not simply force left to right evaluation?" Sure we can, but the price we pay in losing parallel processing, multi-threading, and the stupendous inconvenience of either having to use only strict aliases, or worse, having to code verification of aliases as suitable expression identifiers in the same query level, is a disaster in efficiency (and possibly mathematical integrity). Incidentally, when the query evaluated, any aliases you've used may well be referenced from the next level (outer) query, and here we do check suitability. Doing this: SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as x); will simply result in an "Ambiguous column x" error, while: SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as y); will work perfectly. Note also that most DB engines evaluate ORDER BY sorting after any other Query parts, almost like a higher level outer query, and as such do allow using a column aliases in the ORDER BY clause. I'm not sure that SQLite does though, but it surely allows column indexing in the ORDER BY: SELECT X*2, Y+7 FROM t ORDER BY 2,1; is equivalent to the more convoluted: SELECT X*2, Y+7 FROM t ORDER BY Y+7, X*2; Cheers, Ryan PS: Pardon the verbose post - Once when I was young and wanted to change the way the World programs, I had questions exactly like the above, and it took me a lot longer to figure out the truth than reading a forum post - so this is for all the younger-me's out there. :) ___ 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] SQLite for datalogging - best practices
Hi Keith, first of all let me tell you I cannot thank you enough for going through all this trouble for me! It did not occur to me it would help to run checkpoints manually. There's a couple of things I don't understand though. - After a checkpoint runs to completion (assuming no other process is reading nor writing), what is the value of walpages (i.e. *pnLog)? Is it the number of pages *originally* in the logfile (i.e. the same as copypages), or is it the number of pages *remaining* in the logfile (i.e. zero)? Also, does it refer to the number of *valid* pages or the number of pages allocated by the file (i.e. fllesize divided by pagesize)? - How often are you running checkpoints? Do you expect this variable to have an impact on the end result? Anyway, I'll definitely try the same approach on the current schema (which is *way* different than your super-optimized version) just to see if it is more or less consistent with the trend reported by smartmontools. If that's the case, that makes for a much more convenient metric to estimate performance and compare implementations. So really, thanks a lot! Gerlando On Wed, Oct 31, 2018 at 4:46 AM Keith Medcalf wrote: > > Based on the block erase rate required to maintain I/O churn using the > following database schema and assumptions: > > - SSD size is 32 GB > - erase size is 2 MB > - 3000 erase cycles per block (49,152,000 erase operations total) > > -- data returned from the wal_checkpoint operations > create table Tracer > ( > timestamp integer primary key, > status integer not null, > walpages integer not null, > copypages integer not null > ); > > -- Tags table > create table Tags > ( > id integer primary key, > source text collate nocase unique > ); > > -- History table > create table History > ( > id integer primary key, > sid integer not null references Tags, > timestamp integer not null, > dtype text collate nocase, > data, > unique (timestamp, sid), > unique (sid, timestamp) > ); > > -- operating parameters > pragma cache_size=262133; -- the bigger the better so that page changes do > not need to be spilled until commit > pragma journal_mode=wal; > pragma wal_autocheckpoint=262144; -- large so that checkpointing is only a > manual operation > > with the following operational parameters: > - data arrives in "clusters" so that each second can be committed within > a transaction > - vector data once per second from each of 6 3-axis accelerometers > containing an average 4KB data payload each > - scalar arriving randomly (15 - 45 seconds, avg 30 seconds) from a > random subset of 16 sources data payload is a double > - ID in the History table is the integer seconds since the unix epoch << > 16 + sid > - "old" data deletion is performed at the start of every transaction > > The Tracer table has data written whenever a wal_checkpoint is done. > walpages is the number of pages in the wal file at checkpoint and copypages > is the number of pages that were copied from the wal file into the main > database file. Basically, every page in the wal file must be overwritten > (eventually) and every page written to the db file is a page that must be > erased from the db file. If you add the two together, you get the number > of pages that have been written (more or less) and have to be eventually > erased. There are 512 4K pages in a 2M erase block, so adding these up and > dividing by 512 gives you a rough estimate of number of erases. Given that > we know how many erases we have available (based on the device size and an > estimate of the number of erase operations per erase block) we can estimate > how long the device will last until an erase fails and the device becomes > useless. > > How long do you need the device to last? Based on the data collected so > far I can estimate that the device will last quite a long time (a decade or > more). Of course, it will take a while for the database to reach > steady-state ... to see if as it gets bigger the pagechange set per > checkpoint increases much. > > --- > 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 Gerlando Falauto > >Sent: Tuesday, 30 October, 2018 01:46 > >To: SQLite mailing list > >Subject: Re: [sqlite] SQLite for datalogging - best practices > > > >On Tue, Oct 30, 2018 at 4:58 AM Keith Medcalf > >wrote: > > > >> > >> If you don't mind me asking, what sort of data are you collecting? > >> Are you the master (ie, scanning) or a slave (getting async data > >pushed to > >> you). > >> Are you "compressing" the returned data (storing only changes > >exceeding > >> the deadband) or are you storing every value (or is the source > >instrument > >> doing compression)? > >> > >I presume you need to store the TimeStamp, Point, Value and > >Confidence. > >> What is the data rate (#
Re: [sqlite] use column alias in same SELECT
On 2018/10/31 9:21 PM, Simon Slavin wrote: On 31 Oct 2018, at 7:14pm, Thomas Kurz wrote: may I ask whether there is a deeper reason why using a column alias in the same SELECT query doesn't work, e.g. SELECT column1 AS a, 5*column2 AS b, a+b FROM... The canonical answer to this is that there's nothing in the SQL specification that says the columns have to be evaluated in left-to-right order. Theoretically a computer which had massive parallelism could fetch the data from the database, then work out all the columns at the same time. Simon is correct, and to add to it with some bit of expansion (I assume you asked because you are interested in the answer): The rules for identifiers used in expressions are very strict (it's math after all), and the rules for aliases are by design very loose. Set algebra further imposes no order on items. If a set contains A, B and C... and you asked for everything in the set, you can receive it in any order. (Most DB engines stick to the table-template order as a courtesy, but they are not forced to do so). To explain the strictness problem, imagine a query like SELECT C+A as x, B+A as 'x', C+B as x FROM t Those are all perfectly valid Aliases - but you can see how it will never conform to the strict expression identifier rules. You cannot for instance write a simple Algebraic expression that would explain what value the added x2 column will hold if that query was adjusted to be: SELECT C+A as x, B+A as 'x', C+B as x, x+x as x2 FROM t Further, as Simon mentioned, you cannot be be sure that x+x won't (try to) be evaluated before C+A for the same query. "But..." I hear you ask, "can we not just use the Aliases if they do conform? and can we not simply force left to right evaluation?" Sure we can, but the price we pay in losing parallel processing, multi-threading, and the stupendous inconvenience of either having to use only strict aliases, or worse, having to code verification of aliases as suitable expression identifiers in the same query level, is a disaster in efficiency (and possibly mathematical integrity). Incidentally, when the query evaluated, any aliases you've used may well be referenced from the next level (outer) query, and here we do check suitability. Doing this: SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as x); will simply result in an "Ambiguous column x" error, while: SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as y); will work perfectly. Note also that most DB engines evaluate ORDER BY sorting after any other Query parts, almost like a higher level outer query, and as such do allow using a column aliases in the ORDER BY clause. I'm not sure that SQLite does though, but it surely allows column indexing in the ORDER BY: SELECT X*2, Y+7 FROM t ORDER BY 2,1; is equivalent to the more convoluted: SELECT X*2, Y+7 FROM t ORDER BY Y+7, X*2; Cheers, Ryan PS: Pardon the verbose post - Once when I was young and wanted to change the way the World programs, I had questions exactly like the above, and it took me a lot longer to figure out the truth than reading a forum post - so this is for all the younger-me's out there. :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] use column alias in same SELECT
On 31 Oct 2018, at 7:14pm, Thomas Kurz wrote: > may I ask whether there is a deeper reason why using a column alias in the > same SELECT query doesn't work, e.g. > > SELECT column1 AS a, 5*column2 AS b, a+b FROM... The canonical answer to this is that there's nothing in the SQL specification that says the columns have to be evaluated in left-to-right order. Theoretically a computer which had massive parallelism could fetch the data from the database, then work out all the columns at the same time. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] use column alias in same SELECT
Dear all, may I ask whether there is a deeper reason why using a column alias in the same SELECT query doesn't work, e.g. SELECT column1 AS a, 5*column2 AS b, a+b FROM... This is not an SQlite issue, it doesn't work in MariaDB either. It would, however, be very handy if it worked. Sometimes I have complicated sub-expressions (calculations or sub-queries) which I need to re-use. Currently, as a workaround, I do something like this: SELECT a, b, a+b FROM (SELECT column1 AS a, 5*column2 AS b FROM .) But nested SELECTs don't make things clearer. Thanks, Thomas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying row count
My typical use case is trying to determine how many rows I just wrote out to a file. Window functions are no help because they alter the output format, which is usually undesirable. It's sort of absurd to run `wc -l` over the newest 25MiB file in my system to get an answer that's probably already in RAM. Nathan On Wed, Oct 31, 2018 at 12:27 PM R Smith wrote: > On 2018/10/31 4:52 PM, David Fletcher wrote: > > Hi all, > > > > Is there a mode in the sqlite shell, or some fancy extension, that > > will display a row > > number when outputting results? You know, something like this: > > > > sqlite> .row on > > sqlite> select * from SomeTable where ... ; > > 1. a|17|93|... > > 2. b|212|104|... > > Well you're in luck, SQLite has just recently adopted the great > Windowing functions addition which provides a way to achieve this. > (Note: this will only work from sqlite version 3.25 onward) > > Simply add a column to any select like this: "row_number() OVER > (partition by 1)", et voila... > > Example: >-- SQLite version 3.25.1 [ Release: 2018-09-18 ] on SQLitespeed > version 2.1.1.37. >-- > > > CREATE TABLE t(a,b); > > INSERT INTO t VALUES > (1,'AAA') > ,(6,'BBB') > ,(2,'CCC') > ,(4,'DDD') > ; > > SELECT row_number() OVER (PARTITION BY 1) No, * >FROM t > ; >-- No | a | b >-- | | --- >-- 1 | 1 | AAA >-- 2 | 2 | CCC >-- 3 | 4 | DDD >-- 4 | 6 | BBB > > > You can get further creative by adding an ORDER BY clause inside the > window function if you like to have the same row number in a repeatable > order on subsequent queries. > See here: > > https://www.sqlite.org/windowfunctions.html#introduction_to_window_functions > > Or, as others suggested, simply use a GUI - in https://sqlitespeed.com > as an example, the query has a simple setting switching row numbering on > and off - but it is blind to order, it will number any row the DB engine > spits out in the order it is spat out. Use the Windowing functions if > you need a repeatable/reference-able solution or one that will work in > the CLI. > > > Good luck! > Ryan > > > > > I tend to use the sqlite shell for debugging new queries and seeing a row > > number would save me from always doing a 'select count(*) from (... > > previous select ...)' > > command. > > > > Thanks, > > > > David > > ___ > > 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] Displaying row count
On 2018/10/31 4:52 PM, David Fletcher wrote: Hi all, Is there a mode in the sqlite shell, or some fancy extension, that will display a row number when outputting results? You know, something like this: sqlite> .row on sqlite> select * from SomeTable where ... ; 1. a|17|93|... 2. b|212|104|... Well you're in luck, SQLite has just recently adopted the great Windowing functions addition which provides a way to achieve this. (Note: this will only work from sqlite version 3.25 onward) Simply add a column to any select like this: "row_number() OVER (partition by 1)", et voila... Example: -- SQLite version 3.25.1 [ Release: 2018-09-18 ] on SQLitespeed version 2.1.1.37. -- CREATE TABLE t(a,b); INSERT INTO t VALUES (1,'AAA') ,(6,'BBB') ,(2,'CCC') ,(4,'DDD') ; SELECT row_number() OVER (PARTITION BY 1) No, * FROM t ; -- No | a | b -- | | --- -- 1 | 1 | AAA -- 2 | 2 | CCC -- 3 | 4 | DDD -- 4 | 6 | BBB You can get further creative by adding an ORDER BY clause inside the window function if you like to have the same row number in a repeatable order on subsequent queries. See here: https://www.sqlite.org/windowfunctions.html#introduction_to_window_functions Or, as others suggested, simply use a GUI - in https://sqlitespeed.com as an example, the query has a simple setting switching row numbering on and off - but it is blind to order, it will number any row the DB engine spits out in the order it is spat out. Use the Windowing functions if you need a repeatable/reference-able solution or one that will work in the CLI. Good luck! Ryan I tend to use the sqlite shell for debugging new queries and seeing a row number would save me from always doing a 'select count(*) from (... previous select ...)' command. Thanks, David ___ 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] Optmize queries on ranges
> On Oct 25, 2018, at 10:45 AM, Keith Medcalf wrote: > > There is an extra column load and compare when using the between version of > the query (this is because although the optimization of the index use is the > same, the use of x BETWEEN y AND z adds both the y <= x and x <= z checks as > where clause tests that are executed within the loop, whereas when using the > devolved query (the later form) one of the constraints is used against the > index and only the other one is tested. This seems like an optimization opportunity … is it already a known issue, to be addressed in the query optimizer at some point? —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying row count
> that will display a row number when outputting results? Is this for your schema, or a 'general-could-be-anything' schema? If your own, any reason why you don't use the rowid or _rowid_ columns? They provide a unique reference for each row in a table. Thanks, Chris On Wed, Oct 31, 2018 at 2:54 PM David Fletcher wrote: > Hi all, > > Is there a mode in the sqlite shell, or some fancy extension, that will > display a row > number when outputting results? You know, something like this: > > sqlite> .row on > sqlite> select * from SomeTable where ... ; > 1. a|17|93|... > 2. b|212|104|... > > I tend to use the sqlite shell for debugging new queries and seeing a row > number would save me from always doing a 'select count(*) from (... > previous select ...)' > command. > > Thanks, > > David > ___ > 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] Displaying row count
If you're going to change the sqlite3 command shell, add a ".count" and maybe ".changes" display option. On Wed, Oct 31, 2018 at 11:23 AM Don V Nielsen wrote: > I really enjoy using JetBrains DataGrip. It connects to everything and has > great intellisense, find and replace tools, sql templates, all the goodies > a big IDE brings to the table. > > On Wed, Oct 31, 2018 at 11:05 AM Dominique Devienne > wrote: > > > On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch > > wrote: > > > > > David Fletcher wrote:> Hi all, > > > > Is there a mode in the sqlite shell, or some fancy extension, that > will > > > display a row > > > > number when outputting results? > > > > > > No. You'd have to modify the shell, or add the row_number() window > > > function to the query. > > > > > > Unless you need to use bleeding edge new features, use some GUI SQLite > tool > > instead. > > > > I use SQliteSpy myself for that. Gives me the time to process the query, > > how many rows, > > how many VM steps, Sort steps, that sort of thing. And shows me the > result > > in tabular fashion, > > with color-coding based on value types (useful for a ducktyping DB like > > SQLite). > > > > Windows only, very simple, but very fast. Uses SQLite 3.21, but updated > > once in a while. > > > > I typically keep several queries I'm playing with, and CTRL-F9 the > selected > > one to run it. > > > > Don't get me wrong, the CLI shell is great, but for many rows, or wide > > rows, > > or plain convenience, a GUI tool is often more practical IMHO. My $0.02. > > --DD > > ___ > > 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] Displaying row count
I really enjoy using JetBrains DataGrip. It connects to everything and has great intellisense, find and replace tools, sql templates, all the goodies a big IDE brings to the table. On Wed, Oct 31, 2018 at 11:05 AM Dominique Devienne wrote: > On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch > wrote: > > > David Fletcher wrote:> Hi all, > > > Is there a mode in the sqlite shell, or some fancy extension, that will > > display a row > > > number when outputting results? > > > > No. You'd have to modify the shell, or add the row_number() window > > function to the query. > > > Unless you need to use bleeding edge new features, use some GUI SQLite tool > instead. > > I use SQliteSpy myself for that. Gives me the time to process the query, > how many rows, > how many VM steps, Sort steps, that sort of thing. And shows me the result > in tabular fashion, > with color-coding based on value types (useful for a ducktyping DB like > SQLite). > > Windows only, very simple, but very fast. Uses SQLite 3.21, but updated > once in a while. > > I typically keep several queries I'm playing with, and CTRL-F9 the selected > one to run it. > > Don't get me wrong, the CLI shell is great, but for many rows, or wide > rows, > or plain convenience, a GUI tool is often more practical IMHO. My $0.02. > --DD > ___ > 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] Displaying row count
On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch wrote: > David Fletcher wrote:> Hi all, > > Is there a mode in the sqlite shell, or some fancy extension, that will > display a row > > number when outputting results? > > No. You'd have to modify the shell, or add the row_number() window > function to the query. Unless you need to use bleeding edge new features, use some GUI SQLite tool instead. I use SQliteSpy myself for that. Gives me the time to process the query, how many rows, how many VM steps, Sort steps, that sort of thing. And shows me the result in tabular fashion, with color-coding based on value types (useful for a ducktyping DB like SQLite). Windows only, very simple, but very fast. Uses SQLite 3.21, but updated once in a while. I typically keep several queries I'm playing with, and CTRL-F9 the selected one to run it. Don't get me wrong, the CLI shell is great, but for many rows, or wide rows, or plain convenience, a GUI tool is often more practical IMHO. My $0.02. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite intarray issue
On 10/31/18, Roman Ivasyshyn wrote: > > I faced with an issue of creating int array with the same name without > closing connection. That is not allowed. But you can use sqlite3_intarray_bind() to change the array to which the intarray table is bound, or the length of the array. Another option is to use the carray() table-valued-function instead of the intarray virtual table. The carray() function requires you to bind the array, the array length, and the array datatype at run-time. Multiple instances of the carray() table valued function, each with different array bindings, can participate in the same join. You do not need to create multiple instances of carray(), one for each array. Indeed, that is not even allowed. Instead in the single carray() table valued function can be reused for each array. More information on carray here: https://www.sqlite.org/carray.html The intarray virtual table predates the ability to have table valued functions in SQLite. Intarray continues to be used for testing purposes but is no longer recommended for production use. I have added a warning to this effect in the header comment. -- 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] forming sqlite3_statements using stored sql
Hi all,
In a web-based application I developed, I have a table that holds SQL for
various statement used in the application. It's simple and looks something
like this:
create table if not exists StmtSQL (
StmtName TEXT NOT NULL UNIQUE,
SQL TEXT NOT NULL);
That table is populated with several hundred different SQL statements that
perform various tasks in the application. You know, create a table if
it doesn't
exist, get data from one or more tables, etc, etc.
The obvious benefit is that the SQL isn't hard-coded into the application,
I can make changes to the application without having to recompile, even on
the fly, etc. All the application developer has to do is get a new
sqlite3_statement
and execute it:
sqlite3_statement* pAddWidgetsStmt =
db->GetPreparedStmt("AddWidgets", ... add'l params here ...);
ExecuteStmt(pAddWidgetStmt);
The GetPreparedStmt() function retrieves the SQL from table, creates a new
sqlite3_statement object (or retrieves this from a cache).
It strikes me that this would be nicer if sqlite offered this as an
intrinsic capability.
If it was easy to dump or load this statement table, people might find
it easier to
share collections of SQL statements designed for various tasks.
Thoughts? Maybe someone has already developed a tool or extension like
this?
Thanks,
David
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Displaying row count
Hi all, Is there a mode in the sqlite shell, or some fancy extension, that will display a row number when outputting results? You know, something like this: sqlite> .row on sqlite> select * from SomeTable where ... ; 1. a|17|93|... 2. b|212|104|... I tend to use the sqlite shell for debugging new queries and seeing a row number would save me from always doing a 'select count(*) from (... previous select ...)' command. Thanks, David ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Displaying row count
David Fletcher wrote:> Hi all, > Is there a mode in the sqlite shell, or some fancy extension, that will > display a row > number when outputting results? No. You'd have to modify the shell, or add the row_number() window function to the query. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Displaying row count
Hi all, Is there a mode in the sqlite shell, or some fancy extension, that will display a row number when outputting results? You know, something like this: sqlite> .row on sqlite> select * from SomeTable where ... ; 1. a|17|93|... 2. b|212|104|... I tend to use the sqlite shell for debugging new queries and seeing a row number would save me from always doing a 'select count(*) from (... previous select ...)' command. Thanks, David ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite intarray issue
Hello sqlite team, I faced with an issue of creating int array with the same name without closing connection. I use sqlite3_intarray_create to create int array on a second call I receive SQLITE_MISUSE error. What I found inside create function that it tries to create module that already exist with sqlite3_create_module_v2 function and drop table, before second call, makes no effect on that module. Please advice if it’s expected behavior and it there any workarounds on that? Regards, Roman Ivasyshyn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] curious discovery about geopoly module
On Wed, Oct 31, 2018 at 12:51 PM Graham Holden wrote: > > There are, of course, multiple apps on my system that use sqlite3.dll - > including the Bricscad app that I am running my vba code from.Speculating > somewhat: Have you tried updating the copy Briscad is using? If one version > is already in memory, you _may_ have problems persuading Windows to load a > different version, and -- even if you can -- I've a feeling that SQLite > might not like that.Graham. > From past discussions on this list, I remember that loading SQLite several times in the same process, even with renamed symbols, would be problematic on Posix system, because SQLite uses a "singleton" of some kind (as a workaround for broken POSIX semantic around file IO if I recall correctly). But on Windows, there was no such restriction that I can recall. Dan or Richard can probably shed more light on this. Seems like Graham already has a custom build, so renaming the symbols sounds possible at least. FWIW. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] curious discovery about geopoly module
> There are, of course, multiple apps on my system that use sqlite3.dll - > including the Bricscad app that I am running my vba code from.Speculating > somewhat: Have you tried updating the copy Briscad is using? If one version > is already in memory, you _may_ have problems persuading Windows to load a > different version, and -- even if you can -- I've a feeling that SQLite might > not like that.Graham. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] curious discovery about geopoly module
Hi, and thanks for the suggestion. I am familiar with the vbRichClient solution. There are also odbc drivers that are free to use but in the end I am looking for system that has to work as 64 bit inside 64-bit Autocad where vba runs in-process. The system that I am using seems ideal in that I can build my custom library to include the geopoly module and that of course doesnt get enabled in the standard releases from the sqlite.org site. See my reply to Richard Hipp's message. Once I overcome the problem and get my custom library loaded I expect to make good progress. regards, Graham On 31-10-2018 12:35 am, Vladimir Vissoultchev wrote: You can try some other sqlite wrapper for VBA, for instance vbRichClient ships with 3.24 -- http://www.vbrichclient.com/#/en/Downloads.htm Unfortunately as it's primary target is VB6 the stdcall port of sqlite is compiled to x86 binary only. cheers, -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Graham Hardman Sent: Tuesday, October 30, 2018 11:49 AM To: SQLite mailing list Subject: [sqlite] curious discovery about geopoly module Hi everybody, regarding the geopoly module in version 3.25.2 a couple of weeks ago I wrote about the fact that the 'pragma compile_options' command failed to display when I was trying to check that my special build of the sqlite dll library and the command line shell was working corrrectly. Richard Hipp's reply was to announce that he had forgotten to fully broadcast the new module to the rest of the code. Fair enough i thought, since i was still able to test out my ideas using the shell interface and in my SQLiteExpert program on my windows 10 pc. Since my previous message I have been doing some tests in 64-bit vba by adapting code released on github by Govert: SQLiteForExcel [1] It has been performing well except that, to my great surprise I have been unable to get it to create a virtual table using geopoly. Tonight I have documented this in a module that runs some general tests that all succeed (creating normal tables, inserting records, and querying the results. A simple Rtree virtual table was also part of the testing). The final part of my testing was to try to create a geopoly vitual table. The prepared statement succeeded, but the step process failed - returning 1. The extended error code was also 1, and the error message was "no such module: geopoly" I guess that makes sense in one way, but it begs the question of why the shell and my version of SQLiteExpert find a way to understand what is required. In the meantime it seems I shall have to suspend my vba coding until the next release where hopefully the above issue will be fixed. I can provide my code and the library if wished. The last few lines from my debug printing to the vba intermediate window is pasted below: --begin test with a simple geopoly virtual table opening an in memory database SQLite3Open returned 0 open the rtree virtual table sqlcmd is: 'create virtual table newtab using geopoly(a,b,c)' SQLite3PrepareV2 returned 0 SQLite3Step failed returning 1 Extended error code is: 1 Extended error message is: no such module: geopoly SQLite3Finalize returned 1 forced to abandon testing since geopoly table could not be created- assertion documented Regards, Graham Hardman. Links: -- [1] https://github.com/govert/SQLiteForExcel ___ 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] curious discovery about geopoly module
Hi Richard, I have discovered that there is a problem in that the vba code (I got from the github site) is somehow failing to load my custom library from my specified location and in fact keeps referencing a file with version 3.8.5 from 2014. I am not sure how to overcome this right now apart from renaming my custom library to something else apart from sqlite3.dll and updating the references in the sqlite module. The module uses the declare keyword to hook into externally declared functions in another dll, and also adds references to some windows functions - one of which is the LoadLibraryA function from kernel32 and that is the one that is asked to load my library. There are, of course, multiple apps on my system that use sqlite3.dll - including the Bricscad app that I am running my vba code from. Regarding the other questions: My custom shell and library (referenced in sqliteexpert) return the correct string for sqlite_source_id(), and they do know that geopoly is active because I can create the virtual table and use all of the geopoly special functions. I welcome any help you can provide. Graham On 31-10-2018 12:34 am, Richard Hipp wrote: On 10/30/18, Graham Hardman wrote: To clarify: I built my own versions of the library and shell using the latest amalgamation (3.25.2) specifically to test the geopoly Are you certain that the third-party tool is picking up your custom DLL? Verify by looking at the results of "SELECT sqlite_source_id();" Are you certain that you enabled GeoPoly when you built your custom DLL? Remember that GeoPoly is an extension that defaults off. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database identified as zip
It opens correctly now. 2018-10-30 22:37, Richard Hipp: > On 10/30/18, Dingyuan Wang wrote: >> >> Is this considered a bug? > > Should be fixed on trunk, now. > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
