Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order
On 2018/12/05 5:20 AM, Ryan Schmidt wrote: Hello, I have a corrupt SQLite database about which I'd appreciate your advice. The data is not critical but I'd like to fix it if it's possible and not too time-consuming. Even just knowing why the problem occurred or how to prevent it in the future would be helpful. If there's something the application should be doing differently in its use of the SQLite library to ensure the database doesn't get corrupted that would be good to know. The application that is using the SQLite library (MacPorts) is experiencing this error:// Hi Ryan, the question has two parts: 1 - Can it be fixed? Very probably - by simply dumping it to text and re-importing again. It may need a scan by eye to make sure there are no serious broken text, but usually the Index errors as per your error dump will not be too complicated and easily/automatically corrected. See the .dump command in the sqlite CLI documentation: https://sqlite.org/cli.html#converting_an_entire_database_to_an_ascii_text_file 2 - How did it happen? Hard to say, almost certainly a write that somehow didn't make it to the physical layer. Here is a list of why that can happen (which you may have already encountered in your research): https://sqlite.org/howtocorrupt.html Good luck! Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order
On 5 Dec 2018, at 5:16am, Ryan Schmidt wrote: > https://kb.vmware.com/s/article/1008542 > > "VMware ESX acknowledges a write or read to a guest operating system only > after that write or read is acknowledged by the hardware controller to ESX. > Applications running inside virtual machines on ESX are afforded the same > crash consistency guarantees as applications running on physical machines or > physical disk controllers." Interesting. That paragraph is a well-written piece of text explaining the opposite of what I thought. Maybe things have changed in the past decade. I suppose the lie "Your changes have been made on physical hardware, you can now proceed." may be somewhere else in your system (e.g. the hardware controller). Or maybe I'm barking up the wrong tree. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order
On Dec 4, 2018, at 22:42, Simon Slavin wrote: > On 5 Dec 2018, at 3:20am, Ryan Schmidt wrote: > >> $ sqlite3 /opt/local/var/macports/registry/registry.db >> SQLite version 3.25.2 2018-09-25 19:08:10 >> Enter ".help" for usage hints. >> sqlite> .load /tmp/macports.sqlext >> sqlite> pragma integrity_check; >> *** in database main *** >> On tree page 76852 cell 303: Rowid 18741471 out of order >> On tree page 76852 cell 301: Rowid 18741430 out of order >> On tree page 76852 cell 299: Rowid 18741387 out of order >> On tree page 76852 cell 296: Rowid 18741324 out of order > > It is possible that the rows reported as missing are actually deleted rows, > and that tree page 76852 just hasn't been moved from "part of TABLE ports" to > "freed pages". > > Try using ".dump" to dump that database to a text file. Can you read it and > tell whether anything is missing ? You can either read the SQL commands by > eye, or use command-line-tool to ".read" the .sql file to create another > database, and use database tools to interrogate that one. > > Does your database have any relations, either explicitly declared using > FOREIGN KEY or implicit in how your software handles the data ? Presumably > every row in TABLE file should be part of a row in TABLE port, or something > like that. Can you use your understanding of the relation to prove that > certain rows are missing from certain tables ? Does it give you any idea how > much data is missing ? Thanks very much for taking the time to respond. I'll look into what you suggested. >> the VMware virtual machine the builder runs on froze and had to be manually >> powered off. Upon restarting the VM, registry operations began failing with >> the above error. > > I'm 95% certain that your VMware software was caching changes made to the > database file rather than flushing them to disk when the software told it to. > Under default configuration they all do this, and this does not mean that > VMWare is worse than any competing product. > > In other words, the cause of corruption was that you were using a virtual > machine and not real hardware, and that the virtual machine violates ACID for > speed. It's possible that WMware has settings which correct this problem. > If it does, using them will, of course, slow it down. That occurred to me as well. But from what I can tell initially, writes should not be cached: https://kb.vmware.com/s/article/1008542 "VMware ESX acknowledges a write or read to a guest operating system only after that write or read is acknowledged by the hardware controller to ESX. Applications running inside virtual machines on ESX are afforded the same crash consistency guarantees as applications running on physical machines or physical disk controllers." ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order
On 5 Dec 2018, at 3:20am, Ryan Schmidt wrote: > $ sqlite3 /opt/local/var/macports/registry/registry.db > SQLite version 3.25.2 2018-09-25 19:08:10 > Enter ".help" for usage hints. > sqlite> .load /tmp/macports.sqlext > sqlite> pragma integrity_check; > *** in database main *** > On tree page 76852 cell 303: Rowid 18741471 out of order > On tree page 76852 cell 301: Rowid 18741430 out of order > On tree page 76852 cell 299: Rowid 18741387 out of order > On tree page 76852 cell 296: Rowid 18741324 out of order It is possible that the rows reported as missing are actually deleted rows, and that tree page 76852 just hasn't been moved from "part of TABLE ports" to "freed pages". Try using ".dump" to dump that database to a text file. Can you read it and tell whether anything is missing ? You can either read the SQL commands by eye, or use command-line-tool to ".read" the .sql file to create another database, and use database tools to interrogate that one. Does your database have any relations, either explicitly declared using FOREIGN KEY or implicit in how your software handles the data ? Presumably every row in TABLE file should be part of a row in TABLE port, or something like that. Can you use your understanding of the relation to prove that certain rows are missing from certain tables ? Does it give you any idea how much data is missing ? > the VMware virtual machine the builder runs on froze and had to be manually > powered off. Upon restarting the VM, registry operations began failing with > the above error. I'm 95% certain that your VMware software was caching changes made to the database file rather than flushing them to disk when the software told it to. Under default configuration they all do this, and this does not mean that VMWare is worse than any competing product. In other words, the cause of corruption was that you were using a virtual machine and not real hardware, and that the virtual machine violates ACID for speed. It's possible that WMware has settings which correct this problem. If it does, using them will, of course, slow it down. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order
Hello, I have a corrupt SQLite database about which I'd appreciate your advice. The data is not critical but I'd like to fix it if it's possible and not too time-consuming. Even just knowing why the problem occurred or how to prevent it in the future would be helpful. If there's something the application should be doing differently in its use of the SQLite library to ensure the database doesn't get corrupted that would be good to know. The application that is using the SQLite library (MacPorts) is experiencing this error: sqlite error: library routine called out of sequence (21) I ran an integrity check on the database, and the output began like this: $ sqlite3 /opt/local/var/macports/registry/registry.db SQLite version 3.25.2 2018-09-25 19:08:10 Enter ".help" for usage hints. sqlite> .load /tmp/macports.sqlext sqlite> pragma integrity_check; *** in database main *** On tree page 76852 cell 303: Rowid 18741471 out of order On tree page 76852 cell 301: Rowid 18741430 out of order On tree page 76852 cell 299: Rowid 18741387 out of order On tree page 76852 cell 296: Rowid 18741324 out of order Many similar lines follow. The full output is here: https://trac.macports.org/ticket/57570 This was on macOS Sierra 10.12.6 on a Mac OS Extended (case-sensitive, journaled) filesystem. macports.sqlext provides a custom collation for the version number column. Some background: I run the MacPorts build farm. MacPorts uses SQLite to store its "registry", which keeps track of what ports MacPorts has installed and what files each port provides. In the build farm we keep the latest version of each port installed, which amounts to about 17,000 ports providing over 3 million total files, so the registry gets rather large, about 1.5GB, and operations that change the registry take a little time to complete. Recently, I manually uninstalled a few ports from one of the builders. That command would have rewritten the registry to remove the entries for those ports. MacPorts also occasionally vacuums the registry, when it determines that doing so would be worthwhile, but it doesn't print a message if it does so, so it's possible that was happening but I'm not certain. While MacPorts was dealing with the registry, the VMware virtual machine the builder runs on froze and had to be manually powered off. Upon restarting the VM, registry operations began failing with the above error. I've been running this build farm for two years, currently with 11 different builders, and I haven't seen this or any other registry corruption there before. The VMware host servers have ECC memory and the builders' virtual disks are stored on SSDs. I've seen the document "How To Corrupt An SQLite Database File". There certainly are a lot of possibilities. Does this particular failure stand out to anyone as an obvious example of a particular problem, ideally with a particular solution? If not, I can nuke the MacPorts installation and its SQLite registry and start over. Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suitability for Macintosh OS 9.2?
On Nov 19, 2018, at 07:58, Charles Hudson wrote: > I come from an ANSI SQL client / server background (Oracle, MS SQL) but am > interested in finding a SQL database to install on an old Macintosh G3 Power > PC that is running OS 9.2. I don't need network connectivity as this would > be limited to a single machine. > > I am pursuing this mostly out of curiosity; a learning experience for > investigating the capabilities of the Mac. > > Rather than sign up for your mailing list I thought I might ask this one > question: Which, if any, versions of SQLite might be suitable for this > task? You would probably have an easier time getting SQLite working on that hardware by installing a UNIX-like operating system. Classic Mac OS (Mac OS 9 and earlier) are not related to UNIX at all and SQLite is not designed for it, but Mac OS X (which was subsequently renamed to OS X and now macOS) is a BSD-derived UNIX operating system, and an old version of Mac OS X could be installed on your G3. Which version depends on which model of G3 you have. Blue & white Power Macintosh G3s can run up to Mac OS X 10.4 Tiger. Beige Power Macintosh G3s may need a RAM upgrade but can run up to Mac OS X 10.2 Jaguar officially, and 10.4 can be installed with XPostFacto. Those versions of Mac OS X already come with an old version of SQLite; you may be able to compile a newer version if needed. If you're running at least Mac OS X 10.4, you can use MacPorts to install a newer SQLite for you. (Mac OS X 10.5 and later do not run on PowerPC G3 processors.) You can keep your Mac OS 9 installation if you want, either on the same partition or on a separate partition or separate disk. You can run your Mac OS 9 programs within Mac OS X by using the "Classic" application, or you can reboot into Mac OS 9. Alternately, you might be able to install another UNIX-like operating system, such as a Linux distribution or one of the other BSD variants. Installing Mac OS X or any other UNIX-like system would also make it more likely that you could install other common SQL databases like MySQL/MariaDB and PostgreSQL. Or if you just need a database and don't need it to be SQL, FileMaker Pro is a database system that was available for Mac OS 9 and is still available for Mac OS X. Even HyperCard for Mac OS 9 and earlier can be used as a simple database and was pretty fun. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Allow inclusion of generate_series function
On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp wrote: > > On 12/4/18, Digital Dog wrote: > > > > I vote for GENERATE_SERIES to be included in official sqlite3 binary and > > libraries. > > We are under pressure to keep SQLite as small and compact as possible. > We cannot go adding every feature that everyone requests without the > size of the library growing out of control. Trade-offs have to be > made. We try to provide the ability for people who actually want lots > of features to include them in their own builds. But we do no think it > is appropriate to add things that grow the size of the library unless > there is a compelling need. Okay - keeping the core library small - that makes sense. But for the Command Line tools for general purpose OS like Windows, Linux, BSD, macOS which you offer for download? I'm not so sure. It could contain more features because there are no restrictions. Windows, macOS, typical Linux server or desktop won't work without gigabytes of RAM. Mobile devices are joining the club. I think the scenario can be completely reversed - majority of platforms is capable enough that they can include almost all features, and only some, embedded, esoteric, have real constraints and require trimmed feature set. I think they need to be custom-built anyway. Or maybe it is possible to offer different builds - minimal shell and fully-packed shell on the download page? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Support function_list in pre-built binaries from SQLite download page
My introspection pragma's work, and always have. Then again, I compile with them turned on. Perhaps if they are available the option should appear in the compile_options output, at least? --- 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 Dominique Devienne >Sent: Tuesday, 4 December, 2018 07:37 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Support function_list in pre-built binaries >from SQLite download page > >On Tue, Dec 4, 2018 at 3:30 PM Richard Hipp wrote: > >> On 12/4/18, Dominique Devienne wrote: >> > Wrong pragma never give errors AFAIK, >> >> That is how pragmas are designed to work, yes. Unknown pragmas are >> silently ignored. > > >Thanks for confirming my "AFAIK" Richard. > >But no comment on my request to compile-in pragma_function_list by >default >in the pre-built Windows shell? >Or ehther to add it as a .dbconfig option? >Or my question about finding out which compile-time features are used >in >the library? > >--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] Allow inclusion of generate_series function
On 12/4/18, Digital Dog wrote: > > I vote for GENERATE_SERIES to be included in official sqlite3 binary and > libraries. That seems unlikely, since you can accomplish the same thing using a recursive common table expression. We are under pressure to keep SQLite as small and compact as possible. We cannot go adding every feature that everyone requests without the size of the library growing out of control. Trade-offs have to be made. We try to provide the ability for people who actually want lots of features to include them in their own builds. But we do no think it is appropriate to add things that grow the size of the library unless there is a compelling need. -- 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] Allow inclusion of generate_series function
On Thu, 25 Oct 2018 09:32:05 -0700 Nathan Green wrote: > According to the online documentation (https://www.sqlite.org/series.html ), > generate_series is compiled into the command line shell. As it turns out, > this is not so. It is not even an option in the build system from what I > can tell. It would be nice to at least have a build flag to enable it. > I searched the list archives and noticed an email from late 2015 that > indicates that generate_series was never actually a part of the shell. > Apparently no one ever took up the task of making that happen. Would it be > okay if I put together a patch for this? I would prefer it to be enabled by > default, but I can implement it either way. Here it also doesn't work: SQLite version 3.26.0 2018-12-01 12:34:55 Enter ".help" for usage hints. sqlite> select * from generate_series(1,1000); Error: no such table: generate_series It's a pity because it's a very nice tally table and fast method of generating sequential data, random data, intended number of rows etc. which would be a useful addition in the shell one-liners or other sqlite scripts. It's also useful for simple performance assessments. I vote for GENERATE_SERIES to be included in official sqlite3 binary and libraries. Thanks in advance. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to
WHERE filtering happens before aggregates, so you can't use it on an aggregate column, only a HAVING clause works for that. Or you need to make it a sub-query and surround it with another select to get the where so something like... select c, group_concat(p) as P from t group by c having P = '10'; ...or if it doesn't recognize P, then select c, group_concat(p) as P from t group by c having group_concat(p) = '10'; -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Mark Wagner Sent: Tuesday, December 04, 2018 11:34 AM To: SQLite mailing list Subject: Re: [sqlite] how to Prior to my original message I was playing around with group concat (even though I know the order is considered arbitrary) but found I couldn't put a where clause on that column. Is there anything wrong with this code? sqlite> select c, group_concat(p) as P from t group by c; c P -- -- 1 10,11,12 2 11,12 3 10 BUT... select c, group_concat(p) as P from t where P='10,11,12' group by c ; select c, group_concat(p) as P from t where P='10' group by c ; On Tue, Dec 4, 2018 at 6:04 AM R Smith wrote: > I've mixed up the adding orders to make sure they have no affect on the > outcome - and in the final results examples I've left all the columns so > you can see what is going on, but you of course need only one of the > columns in your desired output. > > > CREATE TABLE t (id integer primary key, c, p); > INSERT INTO t VALUES(1, 1,11); > INSERT INTO t VALUES(2, 1,12); > INSERT INTO t VALUES(3, 1,10); > INSERT INTO t VALUES(4, 2,11); > INSERT INTO t VALUES(5, 2,12); > INSERT INTO t VALUES(6, 3,10); > > -- Base Query: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * >FROM SETS > ; > >-- PSet | PContent | PCount >-- | | -- >-- 1 | 10,11,12 |3 >-- 2 | 11,12 |2 >-- 3 |10|1 > > > -- Example one - finding the set that contains all of 10,11,12 and > nothing else: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * >FROM SETS > WHERE PContent = '10,11,12' > ; > >-- PSet | PContent |PCount >-- | | >-- 1 | 10,11,12 | 3 > > > -- Example two: Finding any set that contains 11,12: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * >FROM SETS > WHERE PContent LIKE '%11,12%' > ; > >-- PSet | PContent | PCount >-- | | -- >-- 1 | 10,11,12 |3 >-- 2 | 11,12 |2 > > > > On 2018/12/04 6:17 AM, Mark Wagner wrote: > > Given a table with two columns, A and B, with no constraints what would > be > > the best way to query for those values of A such that there are > > corresponding values of B in a specified set. > > > > For example, given this data, below, and ignoring the primary key, I > would > > want the following results: > > > > for p values 10,11,12 ==> 1 > > for p values 11,12 ==> 2 > > for p values 10 ==> 3 > > > > For all other "input" we should get no result/null/whatever. > > > > CREATE TABLE t (id integer primary key, c, p); > > > > INSERT INTO t VALUES(1, 1,10); > > > > INSERT INTO t VALUES(2, 1,11); > > > > INSERT INTO t VALUES(3, 1,12); > > > > INSERT INTO t VALUES(4, 2,11); > > > > INSERT INTO t VALUES(5, 2,12); > > > > INSERT INTO t VALUES(6, 3,10); > > > > > > For all other "input" we should get no result/null/whatever. > > > > I can concoct a query based on the "input" like this but it seems like > > there must be a better way? > > > > SELECT DISTINCT c as C FROM t WHERE > > EXISTS (SELECT c FROM t AS x WHERE x.p = 10) > > AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11) > > AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12) > > > > AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3; > > ___ > > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org
Re: [sqlite] how to
Prior to my original message I was playing around with group concat (even though I know the order is considered arbitrary) but found I couldn't put a where clause on that column. Is there anything wrong with this code? sqlite> select c, group_concat(p) as P from t group by c; c P -- -- 1 10,11,12 2 11,12 3 10 BUT... select c, group_concat(p) as P from t where P='10,11,12' group by c ; select c, group_concat(p) as P from t where P='10' group by c ; On Tue, Dec 4, 2018 at 6:04 AM R Smith wrote: > I've mixed up the adding orders to make sure they have no affect on the > outcome - and in the final results examples I've left all the columns so > you can see what is going on, but you of course need only one of the > columns in your desired output. > > > CREATE TABLE t (id integer primary key, c, p); > INSERT INTO t VALUES(1, 1,11); > INSERT INTO t VALUES(2, 1,12); > INSERT INTO t VALUES(3, 1,10); > INSERT INTO t VALUES(4, 2,11); > INSERT INTO t VALUES(5, 2,12); > INSERT INTO t VALUES(6, 3,10); > > -- Base Query: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * >FROM SETS > ; > >-- PSet | PContent | PCount >-- | | -- >-- 1 | 10,11,12 |3 >-- 2 | 11,12 |2 >-- 3 |10|1 > > > -- Example one - finding the set that contains all of 10,11,12 and > nothing else: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * >FROM SETS > WHERE PContent = '10,11,12' > ; > >-- PSet | PContent |PCount >-- | | >-- 1 | 10,11,12 | 3 > > > -- Example two: Finding any set that contains 11,12: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * >FROM SETS > WHERE PContent LIKE '%11,12%' > ; > >-- PSet | PContent | PCount >-- | | -- >-- 1 | 10,11,12 |3 >-- 2 | 11,12 |2 > > > > On 2018/12/04 6:17 AM, Mark Wagner wrote: > > Given a table with two columns, A and B, with no constraints what would > be > > the best way to query for those values of A such that there are > > corresponding values of B in a specified set. > > > > For example, given this data, below, and ignoring the primary key, I > would > > want the following results: > > > > for p values 10,11,12 ==> 1 > > for p values 11,12 ==> 2 > > for p values 10 ==> 3 > > > > For all other "input" we should get no result/null/whatever. > > > > CREATE TABLE t (id integer primary key, c, p); > > > > INSERT INTO t VALUES(1, 1,10); > > > > INSERT INTO t VALUES(2, 1,11); > > > > INSERT INTO t VALUES(3, 1,12); > > > > INSERT INTO t VALUES(4, 2,11); > > > > INSERT INTO t VALUES(5, 2,12); > > > > INSERT INTO t VALUES(6, 3,10); > > > > > > For all other "input" we should get no result/null/whatever. > > > > I can concoct a query based on the "input" like this but it seems like > > there must be a better way? > > > > SELECT DISTINCT c as C FROM t WHERE > > EXISTS (SELECT c FROM t AS x WHERE x.p = 10) > > AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11) > > AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12) > > > > AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3; > > ___ > > 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] Support function_list in pre-built binaries from SQLite download page
On Tue, Dec 4, 2018 at 3:30 PM Richard Hipp wrote: > On 12/4/18, Dominique Devienne wrote: > > Wrong pragma never give errors AFAIK, > > That is how pragmas are designed to work, yes. Unknown pragmas are > silently ignored. Thanks for confirming my "AFAIK" Richard. But no comment on my request to compile-in pragma_function_list by default in the pre-built Windows shell? Or ehther to add it as a .dbconfig option? Or my question about finding out which compile-time features are used in the library? --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Support function_list in pre-built binaries from SQLite download page
On 12/4/18, Dominique Devienne wrote: > Wrong pragma never give errors AFAIK, That is how pragmas are designed to work, yes. Unknown pragmas are silently ignored. -- 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] Support function_list in pre-built binaries from SQLite download page
On Tue, Dec 4, 2018 at 10:08 AM Dominique Devienne wrote: > On Fri, Nov 30, 2018 at 4:11 PM Dominique Devienne > wrote: > >> Nothing returned, so obviously the -DSQLITE_INTROSPECTION_PRAGMAS >> compile-time option is not used [1]. Could it be ON by default please? >> TIA, --DD >> > C:\Users\ddevienne>sqlite3 SQLite version 3.26.0 2018-12-01 12:34:55 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> pragma function_list; sqlite> Tried latest 3.26 from pre-built Windows binaries at [1], and still no function listing :( Am I misusing that feature? Wrong pragma never give errors AFAIK, so hard to say. Also, I tried using .dbconfig, and function_list is not listed there either. sqlite> .dbconfig enable_fkey off enable_trigger on fts3_tokenizer off load_extension on no_ckpt_on_close off enable_qpsg off trigger_eqp off reset_database off defensive off sqlite> Should it be? Thanks, --DD [1] https://www.sqlite.org/download.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to
I've mixed up the adding orders to make sure they have no affect on the outcome - and in the final results examples I've left all the columns so you can see what is going on, but you of course need only one of the columns in your desired output. CREATE TABLE t (id integer primary key, c, p); INSERT INTO t VALUES(1, 1,11); INSERT INTO t VALUES(2, 1,12); INSERT INTO t VALUES(3, 1,10); INSERT INTO t VALUES(4, 2,11); INSERT INTO t VALUES(5, 2,12); INSERT INTO t VALUES(6, 3,10); -- Base Query: WITH SETS(PSet, PContent, PCount) AS ( SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER BY c,p) GROUP BY c ) SELECT * FROM SETS ; -- PSet | PContent | PCount -- | | -- -- 1 | 10,11,12 | 3 -- 2 | 11,12 | 2 -- 3 | 10 | 1 -- Example one - finding the set that contains all of 10,11,12 and nothing else: WITH SETS(PSet, PContent, PCount) AS ( SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER BY c,p) GROUP BY c ) SELECT * FROM SETS WHERE PContent = '10,11,12' ; -- PSet | PContent | PCount -- | | -- 1 | 10,11,12 | 3 -- Example two: Finding any set that contains 11,12: WITH SETS(PSet, PContent, PCount) AS ( SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER BY c,p) GROUP BY c ) SELECT * FROM SETS WHERE PContent LIKE '%11,12%' ; -- PSet | PContent | PCount -- | | -- -- 1 | 10,11,12 | 3 -- 2 | 11,12 | 2 On 2018/12/04 6:17 AM, Mark Wagner wrote: Given a table with two columns, A and B, with no constraints what would be the best way to query for those values of A such that there are corresponding values of B in a specified set. For example, given this data, below, and ignoring the primary key, I would want the following results: for p values 10,11,12 ==> 1 for p values 11,12 ==> 2 for p values 10 ==> 3 For all other "input" we should get no result/null/whatever. CREATE TABLE t (id integer primary key, c, p); INSERT INTO t VALUES(1, 1,10); INSERT INTO t VALUES(2, 1,11); INSERT INTO t VALUES(3, 1,12); INSERT INTO t VALUES(4, 2,11); INSERT INTO t VALUES(5, 2,12); INSERT INTO t VALUES(6, 3,10); For all other "input" we should get no result/null/whatever. I can concoct a query based on the "input" like this but it seems like there must be a better way? SELECT DISTINCT c as C FROM t WHERE EXISTS (SELECT c FROM t AS x WHERE x.p = 10) AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11) AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12) AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3; ___ 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] how to
So reading your example I think you mean Given a table with two columns, c and p, with no constraints what would be the best way to query for those values of c such that the set of corresponding values of p is equal to a specified set. If p isn't text then I'd say something using group_concat select c from t group by c having group_concat(p, '|') = '10|11|12'; Only problem being that group_concat is non-deterministic(?) in the sense that the order in which it gets the values matters, and while you might get it to work with an "implementation detail" of a specific release to give it the values in the right order, it's not in spec and might change. So ...from (select * from t order by p) group by c ... might work now, but also might not work later. I "think" there's a way to do it with the fancy new window function functionality which will guarantee ordered, but haven't figured that out yet. On a tangent: There are no links to the "Window Functions" page (https://www.sqlite.org/windowfunctions.html) from any of the following at the moment: "SQLite Documentation": (https://www.sqlite.org/docs.html) "Query Language Understood by SQLite": (https://www.sqlite.org/lang.html) "SQLite Query Language: SELECT": (https://www.sqlite.org/lang_select.html) There's only a single, one word link on the Expression page (https://www.sqlite.org/lang_expr.html) Would it be feasable to get some links added in some of those spots? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Mark Wagner Sent: Monday, December 03, 2018 11:17 PM To: SQLite mailing list Subject: [sqlite] how to Given a table with two columns, A and B, with no constraints what would be the best way to query for those values of A such that there are corresponding values of B in a specified set. For example, given this data, below, and ignoring the primary key, I would want the following results: for p values 10,11,12 ==> 1 for p values 11,12 ==> 2 for p values 10 ==> 3 For all other "input" we should get no result/null/whatever. CREATE TABLE t (id integer primary key, c, p); INSERT INTO t VALUES(1, 1,10); INSERT INTO t VALUES(2, 1,11); INSERT INTO t VALUES(3, 1,12); INSERT INTO t VALUES(4, 2,11); INSERT INTO t VALUES(5, 2,12); INSERT INTO t VALUES(6, 3,10); For all other "input" we should get no result/null/whatever. I can concoct a query based on the "input" like this but it seems like there must be a better way? SELECT DISTINCT c as C FROM t WHERE EXISTS (SELECT c FROM t AS x WHERE x.p = 10) AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11) AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12) AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3; ___ 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] Misleading error message on missing function
On Fri, Nov 30, 2018 at 4:16 PM Dominique Devienne
wrote:
> sqlite> select json_each('[1, 3, 5]');
> Error: no such function: json_each
> [...]
> Any chance we might get a more user-friendly error message?
> Like perhaps "Error: table-valued function not usable here: json_each"?
>
ping.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Support function_list in pre-built binaries from SQLite download page
On Fri, Nov 30, 2018 at 4:11 PM Dominique Devienne wrote: > Nothing returned, so obviously the -DSQLITE_INTROSPECTION_PRAGMAS > compile-time option is not used [1]. Could it be ON by default please? > TIA, --DD > ping. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
