Re: [sqlite] [BUG] shell: .import: no special characters in table name

2017-10-20 Thread Clemens Ladisch
Clemens Ladisch wrote: > Simon Slavin wrote: >> On 19 Oct 2017, at 7:38pm, Clemens Ladisch wrote: >>> the .import command does not work (and can show misleading error messages) >>> if the table name contains special characters: >>> >>> sqlite> .import test.csv temp.t >> >> What format is the file

Re: [sqlite] [EXTERNAL] virtual tables, and theTrue meaning of pIdxInfo->estimatedCost, pIdxInfo->estimatedRows, and pIdxInfo->idxFlags...

2017-10-20 Thread dave
> Behalf Of Hick Gunter > Sent: Friday, October 20, 2017 1:55 AM > > I can provide some info coming from our experience with SQLite 3.7.14: > > Since most SQl processing is IO bound, the "estimated cost" > should be the number of disk IO operations required to > retrieve the rows. The later addi

[sqlite] vfs question: how to pass binary/blob arg to xCreate()?

2017-10-20 Thread Liam Staskawicz
Hi, I'm just getting started exploring the vfs capabilities of sqlite, and am interested in providing a binary blob argument to xCreate(), as that blob includes a schema description used to generate the CREATE TABLE statement. The existing argument interface is string based, so I'm wondering if

Re: [sqlite] vfs question: how to pass binary/blob arg to xCreate()?

2017-10-20 Thread Richard Hipp
On 10/20/17, Liam Staskawicz wrote: > Hi, > > I'm just getting started exploring the vfs capabilities of sqlite, and > am interested in providing a binary blob argument to xCreate(), No. The arguments to xCreate() are copies of the arguments to the CREATE VIRTUAL TABLE statement that creates th

Re: [sqlite] vfs question: how to pass binary/blob arg to xCreate()?

2017-10-20 Thread Darko Volaric
You could try Unicode binary encoding to increase the efficiency when passing binary as strings: https://qntm.org/unicodings > On Oct 20, 2017, at 7:20 PM, Liam Staskawicz wrote: > > Hi, > > I'm just getting started exploring the vfs capabilities of sqlite, and >

[sqlite] Any change to make this query better?

2017-10-20 Thread jose isaias cabrera
Greetings! This takes about 1.5 minutes to run with sqlite v3.20.1 with about 200K records sqlite> explain query plan ...> SELECT ...> O.XtraF AS PortalID, ...> O.ProjID, ...> O.A_No AS GTXNo, ...> O.proj AS ProjName, ...> O.lang AS Target, ...> (SELECT max(edate) fr

Re: [sqlite] Any change to make this query better?

2017-10-20 Thread Simon Slavin
On 20 Oct 2017, at 7:21pm, jose isaias cabrera wrote: > This takes about 1.5 minutes to run with sqlite v3.20.1 with about 200K > records Can you please time each of the sub-SELECTs ? Do you have any indexes defined on LSOpenJobs ? Can you give LSOpenJobs.cust an affinity of NOCASE ? If so

Re: [sqlite] Any change to make this query better?

2017-10-20 Thread David Raymond
Make sure it gets the same results, but how about something like the below. The collate nocase on the cust field was also a good suggestion. select XtraF as PortalID, ProjID, A_No as GTXNo, proj as ProjName, lang as Target, QuoteAvailable, DeliveryDate, sum(Xtra8) as PriceUSD, 0 as PriceCAD, sum(

[sqlite] How to create primary key from two another PK's?

2017-10-20 Thread csanyipal
Hi, I have a small and simple database MyStudents.db . It has three tables: *student*, *workpiecelist*, *uniqueworkpc*. How can I manage to get primary key (pk) automatically for *uniqueworkpc* table which is composed by pk of *student* table and pk of *workpiecelist* table like below? 03256789415

Re: [sqlite] How to create primary key from two another PK's?

2017-10-20 Thread David Raymond
I don't know about automatically, but you can use foreign keys to help. create table student ( student_id integer primary key, blah ); create table workpiecelist ( workpiecelist_id integer primary key, blah ); create table uniqueworkpc ( student_id int references student, workpiecelis

Re: [sqlite] How to create primary key from two another PK's?

2017-10-20 Thread Darko Volaric
You don't, that's not how relational databases work. You need to create a separate field for each foreign key (student and workpiecelist) and together they form the primary key for the uniqueworkpc table. See David's reply for details. > On Oct 20, 2017, at 9:56 PM, csanyipal wrote: > > Hi,

Re: [sqlite] How to create primary key from two another PK's?

2017-10-20 Thread Eugene Mirotin
Yeah, use two FKs, then you can obtain this "combined" value on select: select printf("%s-%s", student_id, workpiecelist_id) as id from uniqueworkpc On Fri, Oct 20, 2017 at 11:05 PM David Raymond wrote: > I don't know about automatically, but you can use foreign keys to help. > > create table s

Re: [sqlite] Any change to make this query better?

2017-10-20 Thread jose isaias cabrera
Below are four runs of each version. The old one is still faster... Original version: Run Time: real 126.549 user 0.265202 sys 1.045207 Run Time: real 123.742 user 0.655204 sys 2.776818 Run Time: real 125.947 user 0.795605 sys 4.648830 Run Time: real 130.343 user 1.622410 sys 7.035645 your vers

Re: [sqlite] How to create primary key from two another PK's?

2017-10-20 Thread J Decker
Or you could do something really fancy https://en.wikipedia.org/wiki/Z-order_curve http://www.forceflow.be/2013/10/07/morton-encodingdecoding-through-bit-interleaving-implementations/ - (x,y,z) = *(5,9,1)* = (0101,1001,0001) - Interleaving the bits results in: 010001000111 = *1095* th

Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....

2017-10-20 Thread dave
> Behalf Of Dan Kennedy > Sent: Tuesday, October 17, 2017 11:58 AM ... > > I think the exception is queries with OR terms. With > FTS[345], if you do > something like: > >CREATE VIRTUAL TABLE t1 USING fts5(x); >EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def'; > > You ca