Re: [sqlite] Common Table Expression
hi again, With the version sqlite-amalgamation-201401242258 of this night. I found my error so timings are : timing with medium sudoku example : '17.9..3..2...8..96..553..9...1..8...264...3..1..4..7..7...3..' 2 nested with = 3.06 sec (3.32 sec previous beta of 21rst) 8% faster 1 nested with = 1.7 sec (1.7 sec previous beta of 21rst) 0% 3 nested with = 2.65 sec (2.5 previous beta of 21rst) 5% faster (1 nested with which could be 3 nested with) = 1.05 sec (1.09 sec previous beta of 21rst) 3% faster remark1 : - On my non-cte workload, it 24th version seems quicker by 3% faster than 21rst) == It's curious, were there speed improvement commits since beta 2 ? remark 2: - I'm using Keith buildMSVC.cmd file to compile (that I'm using without knowing what it does exactly) I have one more compile error than with 21th version. c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' == Maybe it's normal. *** full compile report *** buildMSVC.cmd CompileOptions=/nologo /O2 /GLFAy /fp:precise Creating library file: SQLite3.dll sqlite3.c Création de la bibliothèque SQLite3.lib et de l'objet SQLite3.exp Génération de code en cours Fin de la génération du code Creating executable; CSVImport.exe CSVImport.c c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' : No such file or directory Creating executable: SQLite3d.exe shell.c Génération de code en cours Fin de la génération du code Creating executable: SQLite3s.exe shell3x.c shell3x.c(146240) : warning C4005: 'ArraySize' : redéfinition de macro shell3x.c(8545) : voir la définition précédente de 'ArraySize' Génération de code en cours Fin de la génération du code Copying Build to DIST ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On 25/01/14 03:37, James K. Lowden wrote: On Fri, 24 Jan 2014 23:51:11 +0100 Petite Abeille petite.abei...@gmail.com wrote: It's exactly the same as SELECT ?, but a little bit easier to write. (It behaves like with INSERT, but is now available in every place where a SELECT would be allowed.) Hmmm? seems rather pointless to me. select 1 as value /* from thin air */ union all select 2 as value /* from thin air */ ? etc ? Seems to be good enough. No point in hijacking a totally unrelated construct. I would drop such complication if I had a say. There is already a perfectly fine construct to conjure constants out of thin air: select. VALUES is a row constructor. You should be able to do VALUES ( ( 'a', 'b', 'c' ) , ('d', 'e', 'f') ) as ( A, B, C ) as T Wouldn't it be better instead of creating a new concept row constructor, to use the existing row constructors, also known as virtual tables? If we had the option of using virtual tables without first creating them, and we also were able to have them at the front of the query (automatically prepending a select * from ...) then VALUES above, could be a virtual table [*]. So think of a virtual table named VALUES that gets as parameters the values that you want it to emit. Then you can select from it, insert from it and so on. l. [*] In addition if we permitted queries as parameters in virtual tables, it would also enable virtual table composition. An example of how this is done in madIS is below (XMLPARSE and FILE are virtual tables): XMLPARSE FILE 'xmldata.xml.gz'; or expanded: select * from (XMLPARSE select * from FILE('xmldata.xml')); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Sat, 25 Jan 2014 11:56:32 +0200 Elefterios Stamatogiannakis est...@gmail.com wrote: Wouldn't it be better instead of creating a new concept row constructor, to use the existing row constructors, also known as virtual tables? Perhaps. I didn't make up the term; it exists in various other DBMSs, sometimes called table value constructor. I just wanted to make clear that it's not new, and opens the potential for (I would say) better queries, because they can be expressed in terms of tuples instead of just scalars. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On 25/01/14 18:41, James K. Lowden wrote: On Sat, 25 Jan 2014 11:56:32 +0200 Elefterios Stamatogiannakis est...@gmail.com wrote: Wouldn't it be better instead of creating a new concept row constructor, to use the existing row constructors, also known as virtual tables? Perhaps. I didn't make up the term; it exists in various other DBMSs, sometimes called table value constructor. I just wanted to make clear that it's not new, and opens the potential for (I would say) better queries, because they can be expressed in terms of tuples instead of just scalars. Yes i see what you mean and i agree. Furthermore, and to carry on a little more that train of thought, i hope that tuples will become possible as return values from SQLite's user defined functions. If ever that becomes the case, then i suggest to go for tuple generators instead of materialized tuples as the return primitive. Materialized tuples as a primitive, have a lot of nasty side effects in a DB engine. l. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 25, 2014, at 2:37 AM, James K. Lowden jklow...@schemamania.org wrote: Funny, we find ourselves on the opposite side of the compexity question this time. Ehehehe… yes… the irony is duly noted :) But, ok, then, let welcome our new VALUES overlord. May it have a long and prosperous carrier! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 25, 2014, at 6:05 AM, Keith Medcalf kmedc...@dessus.com wrote: Read the docs. It explains how recursive CTEs are computed and how UNION ALL vs UNION work in CTEs. Hmmm… perhaps… doing is believing… so will experiment once the next SQLite release is officially out. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
remark 2: - I'm using Keith buildMSVC.cmd file to compile (that I'm using without knowing what it does exactly) I have one more compile error than with 21th version. c1 : fatal error C1083: Impossible d'ouvrir le fichier source : 'CSVImport.c' == Maybe it's normal. Yes. If you delete file containing the source code then that code cannot be compiled. CSVImport.c is code written by Michael D. Black which implements a stand-alone CSV import utility, just as there are other source files (other than just the amalgamation) which implement a number of add-in features if you enable them with BuildMSVC ext the default build (without ext) contains the base engine only without any extensions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] export table to csv
Hello, I want to ask you if is any way to call sqlite special commands programatically from c#. E.g. I need something like this in code not in command line: sqlite .mode csv sqlite .output test.csv sqlite select * from tbl1; sqlite .output stdout If not, what is the quicker way to export table programatically? Now I`m doing it with DbDataReader, but it is slow. Ps: There is not any support user forum?? Thank you, Fratnisek ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECTing from another SELECT
St. B. wrote... SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill. If I where to run your query, I would do a select A.* from A inner join B on A.a = b.ProjId where b.ptask='a' the join may optimize better than the in (select ...) Thanks. I will give this a try also. As we say in the Spanish language: Muchas gracias. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] csv export
can I run sql shell also on windows CE and windows Mobile? could you please give me little example how to create virual table to file export it and to pc? I can find it. Can I also delete it from file explorer without problems? Many thanks. On Fri, Jan 24, 2014 at 9:10 AM, Hick Gunter h...@scigames.at wrote: You can fork off a child process, run the sqlite shell in that and pipe your commands to it. Or you could write a CSV virtual table and execute something like insert into csv select ... -Ursprüngliche Nachricht- Von: Frantisek Cerven [mailto:feri...@gmail.com] Gesendet: Freitag, 24. Jänner 2014 07:45 An: sqlite-users@sqlite.org Betreff: [sqlite] csv export Hello, I want to ask you if is any way to call sqlite special commands programatically from c#. E.g. I need something like this in code not in command line: sqlite .mode csv sqlite .output test.csv sqlite select * from tbl1; sqlite .output stdout If not, what is the quicker way to export table programatically? Now I`m doing it with DbDataReader, but it is slow. Ps: There is not any support user forum?? Thank you, Fratnisek ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECTing from another SELECT
Igor Tandetnik wrote... On 1/23/2014 2:26 PM, St. B. wrote: SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill. If I where to run your query, I would do a select A.* from A inner join B on A.a = b.ProjId where b.ptask='a' Careful - this will produce a different result if B has multiple rows with the same ptask and projid. May or may not be a concern in the OP's case. What would be the correct inner join syntax to provide the same output as the original select? I like that shorter syntax. Will any of these will be faster? Thanks so much. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECTing from another SELECT
Igor Tandetnik wrote on Friday, January 24, 2014 9:48 AM... On 1/24/2014 9:28 AM, jose isaias cabrera wrote: Igor Tandetnik wrote... On 1/23/2014 2:26 PM, St. B. wrote: SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill. If I where to run your query, I would do a select A.* from A inner join B on A.a = b.ProjId where b.ptask='a' Careful - this will produce a different result if B has multiple rows with the same ptask and projid. May or may not be a concern in the OP's case. What would be the correct inner join syntax to provide the same output as the original select? I like that shorter syntax. Will any of these will be faster? Thanks so much. SELECT DISTINCT ... perhaps. But that would kill any alleged performance improvement that a join may have over IN (if any; personally, I'm not sure it's necessarily true that the join would perform better in the first place). You are right. Your original IN command is much faster. It instantly responds, while the INNER JOIN takes a few second before responding. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting Error at line 16265 (v3.8.2)
Help, not sure if this is the right place to post, I’m getting error from sqlite3.c when i’m trying to run my application in development…… I’m developing using vs 2012 for Windows Store App, running for winrt and desktop. Installed “SQLite for Windows Runtime and SQLite-net 1.0.7 nugget package. never have this problem before… just added 50 records and it dies. Please help urgent ….. need to release soon Sent from Windows Mail___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query executes in sqlite manager but not sqlite database in android
Hello Community Hope to get some pointers here because i have a hit a snag! I have 4 tables. Meters:_id, SerialNumber Tenants: _id, FirstName. TenantsMeters: _id,Tenant_id,Meter_id, (basically junction table for linking many to many relationship btn tenants and meters) MeterReading: _id, ReadingDate, Reading, TenantMeter_id(FK for TenantMeters table) This query works in sqlite manager *SELECT M._id as Meter_id, M.MeterNumber, R1.ReadingDate as ReadingDate, R1.Reading AS CurrentReading, R2.ReadingDate AS PrevReadingDate, R2.Reading AS PrevMeterReading, R2.Rate as Rate, R2._id as _id,R1.TenantMeter_id FROM (Meters AS M INNER JOIN TenantMeters ON M._id = TenantMeters.Meter_id) INNER JOIN (MeterReading AS R1 INNER JOIN MeterReading AS R2 ON R1.TenantMeter_id = R2.TenantMeter_id) ON TenantMeters._id = R1.TenantMeter_id WHERE (((R2.ReadingDate)=(SELECT Max(R3.ReadingDate) FROM [MeterReading] AS R3 WHERE (R3.TenantMeter_id = R1.TenantMeter_id) AND (R3.ReadingDate R1.ReadingDate) ))) OR (((R2.TenantMeter_id) Is Null)) * But fails in sqlite database in android with the error no such column:R1.ReadingDate It fails in android app and also fails when i connect to the android sqlite db from command prompt . What can be the likely cause? Any pointers please. Ronald -- View this message in context: http://sqlite.1065341.n5.nabble.com/Query-executes-in-sqlite-manager-but-not-sqlite-database-in-android-tp73466.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] export table to csv
On 24 Jan 2014, at 6:24am, Frantisek Cerven cerv...@gmail.com wrote: I want to ask you if is any way to call sqlite special commands programatically from c#. These 'special commands' are not in SQLite at all. They are features of the command-line shell application. SQLite does not understand any command starting with a dot. E.g. I need something like this in code not in command line: sqlite .mode csv sqlite .output test.csv sqlite select * from tbl1; sqlite .output stdout If not, what is the quicker way to export table programatically? Now I`m doing it with DbDataReader, but it is slow. I'm afraid that the way to do that quickly is to write some C# code which does it. A slower way to do it would be to have C# issue an operating system command that makes the shell tool do it. Ps: There is not any support user forum?? This is it ! Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting Error at line 16265 (v3.8.2)
On 24 Jan 2014, at 5:09pm, Hiew William buloh...@hotmail.com wrote: I’m getting error from sqlite3.c when i’m trying to run my application in development…… Which error ? In other words, what's the error message ? And what API function is your application trying to execute when you get that error ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query executes in sqlite manager but not sqlite database in android
Hi Kamulegs, Your SQLiteManager includes a version of SQLite =3.7.16 , and your android application does not. Indeed the syntax (b JOIN c ON b.id = c.id) is only accepted without this errror after 3.7.16. == If you can rewrite your syntax without these parenthesis (like below), all should be fine. *SELECT M._id as Meter_id, M.MeterNumber, R1.ReadingDate as ReadingDate, R1.Reading AS CurrentReading, R2.ReadingDate AS PrevReadingDate, R2.Reading AS PrevMeterReading, R2.Rate as Rate, R2._id as _id,R1.TenantMeter_id FROM Meters AS M INNER JOIN TenantMeters ON M._id = TenantMeters.Meter_id INNER JOIN MeterReading AS R1 INNER JOIN MeterReading AS R2 ON R1.TenantMeter_id = R2.TenantMeter_id ON TenantMeters._id = R1.TenantMeter_id WHERE (((R2.ReadingDate)=(SELECT Max(R3.ReadingDate) FROM [MeterReading] AS R3 WHERE (R3.TenantMeter_id = R1.TenantMeter_id) AND (R3.ReadingDate R1.ReadingDate) ))) OR (((R2.TenantMeter_id) Is Null)) * ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query executes in sqlite manager but not sqlite database in android
It seems Android use a pretty outdated SQlite motor : SQlite 3.7.1 = march 20th, 2012 http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android SQLite 3.7.11: 19-4.4-KitKat 18-4.3-Jelly Bean 17-4.2-Jelly Bean 16-4.1-Jelly Bean SQLite 3.7.4: 15-4.0.3-Ice Cream Sandwich 14-4.0-Ice Cream Sandwich 13-3.2-Honeycomb 12-3.1-Honeycomb 11-3.0-Honeycomb SQLite 3.6.22: 10-2.3.3-Gingerbread 9-2.3.1-Gingerbread 8-2.2-Froyo SQLite 3.5.9: 7-2.1-Eclair 4-1.6-Donut 3-1.5-Cupcake ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users