Re: [sqlite] Can it (should it) be done in SQL?

[David Bicking]

 Thanks. I figured the solution would use CTE (this is a CTE, isn't it??) 
Unfortunately, they were neither in Sqlite, nor mentioned in any of the sql 
stuff I read when I taught myself to do SQL.so it took me a while to figure out 
how it works.
Unfortunately, I extend the goals to cover all 12 months, leavings actuals for 
just periods 1 to 3. The result has UNK lines for periods 4 to 12, with a null 
for the aount. I am guessing that this is because the in the unks cte it is 
subtracting the sum from data, but there is nothing in data, so it is 
subtracting a null resulting in a null.
I was able to put a coalesce around the data sum, and that does work.
The line strftime('%m') seemed very strange. I guess without a time string, 
sqlite defaults the the current date and time. the sqlite docs don't mention 
this.
David

On Saturday, January 18, 2020, 04:32:52 PM EST, Keith Medcalf 
 wrote:  
 
 Ooops.  Wrong query pasted, should be this one:

with p (period) as (
        values (cast(strftime('%m') as integer))
    ),
unks (period, type, amount) as (
        select p.period,
              'UNK',
              (
                select sum(amount)
                  from goals
                where period between 1 and p.period
              ) - (
                select sum(amount)
                  from data
                where period between 1 and p.period
              )
          from p
        where p.period <= (select max(period) from goals)
    union all
        select period+1,
              'UNK',
              (
                select amount
                  from goals
                where period == unks.period+1
              ) - (
                select sum(amount)
                  from data
                where period == unks.period+1
              ) + case when unks.amount < 0 then unks.amount else 0 end
          from unks
        where period < (select max(period) from goals)
    )
  select period,
          type,
          amount
    from data
union all
  select period,
          type,
          max(0, amount)
    from unks
order by 1, 2;



-- 
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  On
>Behalf Of David Bicking
>Sent: Saturday, 18 January, 2020 11:20
>To: SQLite Mailing List 
>Subject: [sqlite] Can it (should it) be done in SQL?
>
>I suspect the answer is that it is best to do this in the application
>program. However, the platform I want to use is dumb as a brick.
>It basically can call sqlite3_get_table, mildly reformat the data
>and send it to the display.
>
>Anyway, there are two tables
>CREATE TABLE Goals (period integer primary key, amount int);
>CREATE TABLE Data (period int, type text, amount int);
>
>periods above are 1 to 12, and amounts are zero or positive.
>
>INSERT INTO Goals Values (1,10), (2,10), (3,10);
>INSERT INTO Data Values (1,'A',3), (2, 'A', 5) (2, 'B', 6), (3, 'A', 2);
>
>Okay, last time I tried to send a list to the group it came out
>unreadable. Crossing fingers:
>
>PERIOD | TYPE | AMOUNT
>1  | A    | 3
>1  | UNK  | 7   -- the goal of 10 - data of 3
>2  | A    | 5
>2  | B    | 6
>2  | UNK  | 0   -- goals of 1&2 = 20 - previous lines of 21
>    -- with the negative result changed to 0
>3  | A    | 2
>3  | UNK  | 7   -- goals 1-3 = 30 - previous lines of 23
>
>Hopefully that makes sense. I need to insert a row into the output
>that ends each period at or above the accumulated goals.
>
>A further complication. The "UNK" row only gets inserted for the
>current or future periods. (i.e in two weeks it will be February,
>so the January UNK will be removed and the Feb UNK would
>become 6 (20 - 3 - 11).
>
>The best I got is
>
>select period, type, amt from Data
>UNION
>select period, 'UNK', (select sum(amount) from Goals G
>   where g.period <= goals.period)
> -(select sum(amount) from Data D
>   where d.period <= goals.period)
>from Goals;
>
>But it doesn't pick up the calculated "UNK" values from the prior
>periods.
>nor does it deal with negatives or not calculating UNK from periods in
>the past.
>
>So can this report be done within a query?
>
>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

[sqlite] Can it (should it) be done in SQL?

[David Bicking]

I suspect the answer is that it is best to do this in the application 
program. However, the platform I want to use is dumb as a brick. 
It basically can call sqlite3_get_table, mildly reformat the data 
and send it to the display.

Anyway, there are two tables
CREATE TABLE Goals (period integer primary key, amount int);
CREATE TABLE Data (period int, type text, amount int);

periods above are 1 to 12, and amounts are zero or positive.

INSERT INTO Goals Values (1,10), (2,10), (3,10);
INSERT INTO Data Values (1,'A',3), (2, 'A', 5) (2, 'B', 6), (3, 'A', 2);

Okay, last time I tried to send a list to the group it came out 
unreadable. Crossing fingers:

PERIOD | TYPE | AMOUNT
1  | A    | 3
1  | UNK  | 7   -- the goal of 10 - data of 3
2  | A    | 5
2  | B    | 6
2  | UNK  | 0   -- goals of 1&2 = 20 - previous lines of 21
    -- with the negative result changed to 0
3  | A    | 2
3  | UNK  | 7   -- goals 1-3 = 30 - previous lines of 23

Hopefully that makes sense. I need to insert a row into the output 
that ends each period at or above the accumulated goals.

A further complication. The "UNK" row only gets inserted for the 
current or future periods. (i.e in two weeks it will be February,
so the January UNK will be removed and the Feb UNK would 
become 6 (20 - 3 - 11).

The best I got is

select period, type, amt from Data
UNION
select period, 'UNK', (select sum(amount) from Goals G 
   where g.period <= goals.period)
 -(select sum(amount) from Data D
   where d.period <= goals.period)
from Goals;
  
But it doesn't pick up the calculated "UNK" values from the prior periods.
nor does it deal with negatives or not calculating UNK from periods in the past.

So can this report be done within a query? 

Thanks,
David
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lazy JSON data to sqlite database

[David Bicking]

>> Is there a tool out there that will more or less automate the task for


>> me? Hopefully free, as no one is paying me to do this. (The other
>> volunteers have maybe a dozen records in total and are doing their
>> reports by hand. )

>The automation is at a lower level than you seem to realize.  JSON I/O is a 
>solved problem, but actually >doing anything with that data, such as 
>transforming it into a relational database form, is up to you.

I thought I got lucky. I found an web site called SQLify, that lets you upload 
a JSON file, and it will examine it, figure out a schema, and create  CREAT 
TABLE and INSERT lines with the data. Kind of neat. Unfortunately, it flattened 
the data in to one table, which didn't matter for the venue and group data, but 
it flattened the  hosts data by only using the first one and discarding the 
rest.
>> A cursory look at the data: there is a top level "event" table.  "Group"
>> and "venue" tables that are one-to-one with the event table, and a
>> "hosts" tables with many hosts to the event table.
>
>JSON doesn't have tables, nor does JSON pre-declare a schema.  JSON intermixes 
>the schema with the >data, and the schema is allowed to vary over the course 
>of the document.

Yeah, sloppy writing on my part. JSON has objects, which can be stored in SQL 
tables.

>> Oh, to complicate things, the source data is not valid JSON, as the>> 
>> upstream source did not escape quote marks within the text.  So is there
>> a tool that can clean up the quotes, hopefully there won't be many bad
>> quotes... maybe three or four in the 600 record.>
>Then it's not really JSON and can't be processed with true-blue JSON tools.  
>Everything you're saying is >calling for a custom tool.  Custom tools do not 
>have to be hard or expensive though.
>
>But in the case of quotes not being escaped, that all by itself could be 
>difficult to automate, since heuristics >will need to be applied to figure out 
>whether any given quote mark is a string delimiter.  Human >intervention might 
>be required.

I kind of solved this by running the data through a parser. I would then take 
the error message, and find the text in the source file, and escape the quotes. 
Then parse again and fix the next error. Tedious but that works.
David


   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Lazy JSON data to sqlite database

[David Bicking]

Okay, I know this is just me being lazy, but I have a data file with JSON data, 
and I want to get the data into an sqlite database so I can run queries against 
it. It is not a large file, only about 600 records in the main table. I've 
never worked with JSON before, and really don't want to write my own parser to 
extract he data.
Is there a tool out there that will more or less automate the task for me? 
Hopefully free, as no one is paying me to do this. (The other volunteers have 
maybe a dozen records in total and are doing their reports by hand. )

A cursory look at the data: there is a top level "event" table.  "Group" and 
"venue" tables that are one-to-one with the event table, and a "hosts" tables 
with many hosts to the event table. 

This is something that I will need to do once a year, so like I said, I really 
don't want to write a custom program.
Any ideas?
(Oh, to complicate things, the source data is not valid JSON, as the upstream 
source did not escape quote marks within the text.  So is there a tool that can 
clean up the quotes, hopefully there won't be many bad quotes... maybe three or 
four in the 600 record.)
Thanks,David
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Task management schema

[David Bicking]

Okay, this is a "help me with my homework" type request, so feel free to tell 
me to go away.
But I have been tasked with creating a simple task management system. For it, I 
need to store tasks: who is assigned, what is assigned, and when it is due. 
Where the task could be recurring, ie due the 15th of each month, or April 15th 
of each year.  No problem, I could just use a vcalendar/ical library and store 
the data in there.
However, I need to store status information for each instance of each recurring 
task. I am not sure how to bridge the gap between one entry for each recurring 
task and detail for individual instances, where the task could continue forever 
and thus there would be an "infinite" number of detail records. (Well, 
actually, that would be a simple relation between tables, but I don't know of a 
library that can store recurring data in a database table, I've only seen ones 
that store it is calendar data files.)

So I would appreciate any clues to how I should set that up? Or links to open 
source programs/libraries that already do that (with code simple enough that I 
could understand what they are doing I'm not the smartest tool in the 
toolbox)
Thanks,David
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Protecting databases

[David Bicking]

If you are trying to protect against casual snooping, you could probably 
zip the sqlite data with a password then have your application unzip the 
data to a temp location on open, then re-zip at the end, deleting the 
unzipped file. Your application then would be able to use the normal 
sqlite calls to use the data.


Or for field by field encryption, you could create an encryt/decrypt 
function so that only encrypted data is stored in the db.


INSERT INTO myTable (mySensitiveField) VALUES( encrypt(@ClearText, 
@password) );


SELECT * FROM myTable where decrypt(mySensitiveField, @password) LIKE 
@SearchPattern;


Hope those ideas help.

I've written plenty of Freebasic programs using sqlite, but never had 
the need to encrypt or control access.


David


On 2016-10-07 10:46 PM, Damien Sykes-Lindley wrote:

Hi there,
My name is Damien Lindley, and I am, among other things, an 
independent, hobbiest programmer. I have been blind since birth and 
thus all my computer work relies on screenreader software and keyboard.
I have only just come through the brink of scripting into compiled 
programming and so I guess I am still a beginner in many respects. 
However I don’t work in C or C++, so most of my programming, if 
using a library, relies on precompiled static or dynamic libraries. 
Or of course libraries that are written or converted specifically 
for the language I work in (FreeBASIC).
Recently, I decided I needed to create a piece of software that 
could manage family trees, since there seems to be a lack of 
screenreader accessible genealogy managers out there. I was advised 
the best way to do this is to use a database engine. I was also 
informed that SQLite is always a good choice for databases.
I must admit, I have never worked with databases before and so now I 
am in the process of learning SQL. However looking at the 
programming API for SQLite I cannot see any means of password 
protecting the database without either buying a commercial extension 
to do this, or recompiling SQLite with the authentication extension. 
Due to financial constraints and unfamiliarity with compiling in C 
both of these are not an option for me. Also I need a secure way to 
do this, as I think I read that the SQLite version simply uses a 
table to store the user data, which of course can be read and 
accessed elsewhere.

Are there any other options available for doing this?
Any help appreciated.
Thanks.
Damien.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Complicated join

[David Bicking]

Thanks.
Yeah, sometimes I hate SQL. Some of my queries for this project take 2 or 3 
pages of paper to print out. Unfortunately the only alternatives approved by 
the bosses are even worse.
I think I can add the Not exists clause to my query and that should do it.
Which means I need to load the 70,000+ records to M and the 200,000+ records to 
E and see how long it takes to run!
David


  From: David Raymond 
 To: SQLite mailing list  
 Sent: Monday, September 19, 2016 3:30 PM
 Subject: Re: [sqlite] Complicated join
   
Something that works, but is ugly so I hesitate to post it. Again, working with 
the results of a simpler query outside of SQL would be preferred. Just because 
you  make ASCII-art of the Mandelbrot set using SQL doesn't mean it's the 
best choice. (Would that now be UTF-8-art? Doesn't have quite the same ring to 
it)

Playing around does leave me with one question by the way: Do temp tables 
created via "with a as b" not have a rowid field? I tried referencing that, but 
kept getting issues.


But in any case:


create table E (CombinedKeyField text, EvtNbr int, primary key 
(CombinedKeyField, EvtNbr));
create table M (CombinedKeyField text, EvtNbr int, primary key 
(CombinedKeyField, EvtNbr));
insert into E values ('A', 1), ('A', 2), ('A', 3), ('A', 4), ('A', 5), ('A', 
6), ('A', 7),
('A', 8), ('A', 9), ('B', 1);
insert into M values ('A', 1), ('A', 5);

--explain query plan
with x as (
select CombinedKeyField, E.EvtNbr as EEvtNbr, M.EvtNbr as MEvtNbr, E.EvtNbr != 
M.EvtNbr as neq
from E inner join M using (CombinedKeyField))
--order by CombinedKeyField, EEvtNbr, neq, MEvtNbr)
--Use x instead of M for the outer join, and take only the first record (if 
any) that matches.
--Had the "order by" in there while trying to use the rowid in the later 
compare,
--just realized I could take it out since I gave up on using rowid.

select E.CombinedKeyField, E.EvtNbr as EEvtNbr, x.MEvtNbr
from E left outer join x
on E.CombinedKeyField = x.CombinedKeyField and E.EvtNbr = x.EEvtNbr

--The "take only the first one" part.
where not exists (
select * from x as xt
where xt.CombinedKeyField = E.CombinedKeyField and xt.EEvtNbr = E.EvtNbr
and (xt.neq < x.neq or (xt.neq = x.neq and xt.MEvtNbr < x.MEvtNbr)));


Output is:
CombinedKeyField|EEvtNbr|MEvtNbr
A|1|1
A|2|1
A|3|1
A|4|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|


explain query plan output:
selectid|order|from|detail
1|0|0|SCAN TABLE E
1|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1 
(CombinedKeyField=?)
0|0|0|SCAN TABLE E
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (EEvtNbr=? AND 
CombinedKeyField=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE E USING COVERING INDEX sqlite_autoindex_E_1 
(CombinedKeyField=? AND EvtNbr=?)
2|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1 
(CombinedKeyField=?)


The compares and such are going to blow up in size when translated to your real 
version with the 8 fields, which is what makes me cringe.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of David Bicking
Sent: Monday, September 19, 2016 1:34 PM
To: SQLite mailing list
Subject: Re: [sqlite] Complicated join

INSERT INTO M (CombinedKeyField, EvtNbr)

VALUES ('A', 1),
('A', 5);

INSERT INTO E (CombineKeyField, EvtNbr)
VALUES ('A', 1)
, ('A', 2)
, ('A', 3)
, ('A', 4)
, ('A', 5)
, ('A', 6)
, ('A', 7)
, ('A', 8)
, ('A', 9)
, ('B', 1);

I'm ignoring the TransDate part for now.

This is what I want:

SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A  1  1
A  2  1
A  3  1
A  4  1
A  5  5  -- matches the (A,5) record in the M table.

A  6  1
A  7  1
A  8  1
A  9  1
B  1  NULL  -- no match found for CombinedKeyfield in M


This is the closest I have got

select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
from E left join M
on E.CombinedKeyField = M.CombinedKeyField
and (E.EvtNbr = M.EvtNbr
or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
                          WHERE M1.CombinedKeyField = E.CombinedKeyField 
              ) 
)

But it doubles up on A,5, matching both on A,1 and A,5 in M
And it doesn't return B,1 with no match even though it is a left join.

Hopefully that comes out readable, and my needs are clearer.

Thanks,
David

- Original Message -
From: James K. Lowden 
To: sqlite-users@mailinglists.sqlite.org
Sent: Monday, September 19, 2016 10:57 AM
Subject: Re: [sqlite] Complicated join

On Thu, 15 Sep 2016 15:53:10 + (UTC)



David Bicking  wrote:

> (1) The CombinedKeyFields must always match in each table(2) Match
> using the EvtNbr, but if no match, use the lowest M.EvtNbr that
> matches the CombinedKeyFields
> 
> (

Re: [sqlite] Complicated join

[David Bicking]

This is what I want:

SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A  1  1
A  2  1
A  3  1
A  4  1
A  5  5  -- matches the (A,5) record in the M table.
A  6  1
A  7  1
A  8  1
A  9  1
B  1  NULL  -- no match found for CombinedKeyfield in M

Did this part of my post not make it to your reader?
Your output is almost what I want, except that the A.5 line is matching 1 and 5 
in the M table, and I only want it to match the 5.

Now, can you suggest how I can get the query to return A,5,5 but not A,5,1?
Thanks,David
 From: Luuk  To: sqlite-users@mailinglists.sqlite.org 
 Sent: Monday, September 19, 2016 2:43 PM
 Subject: Re: [sqlite] Complicated join
   
On 19-09-16 19:33, David Bicking wrote:
> select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
> from E left join M
> on E.CombinedKeyField = M.CombinedKeyField
> and (E.EvtNbr = M.EvtNbr
> or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
>                            WHERE M1.CombinedKeyField = E.CombinedKeyField
>                )
> )

expected output is missing

now we need to guess at what you want the output to look like

> But it doubles up on A,5, matching both on A,1 and A,5 in M
which line is correct? (or are they both correct?)
> And it doesn't return B,1 with no match even though it is a left join.
In my output i do see 'B|1|' ..


sqlite> select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
    ...> from E left join M
    ...> on E.CombinedKeyField = M.CombinedKeyField
    ...> and (E.EvtNbr = M.EvtNbr
    ...> or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
    ...>                            WHERE M1.CombinedKeyField = 
E.CombinedKeyField
    ...>                )
    ...> );
A|1|1
A|2|1
A|3|1
A|4|1
A|5|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
sqlite> .version
SQLite 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7
sqlite>



___
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] Complicated join

[David Bicking]

"CombinedKeyFields", is in fact about 7 or 8 fields in the natural key. If I 
mistype 17 letters, I'd hate to see how I mangle the whole thing. 


  From: Luuk 
 To: sqlite-users@mailinglists.sqlite.org 
 Sent: Monday, September 19, 2016 2:23 PM
 Subject: Re: [sqlite] Complicated join
   
On 19-09-16 19:33, David Bicking wrote:
> INSERT INTO M (CombinedKeyField, EvtNbr)
>
> VALUES ('A', 1),
> ('A', 5);
>
> INSERT INTO E (CombineKeyField, EvtNbr)
> VALUES ('A', 1)
> , ('A', 2)
> , ('A', 3)
> , ('A', 4)
> , ('A', 5)
> , ('A', 6)
> , ('A', 7)
> , ('A', 8)
> , ('A', 9)
> , ('B', 1);
>
>

What is the name of this field?

CombinedKeyField
CombineKeyField
CombinedKeyFields


And why is it not possible to keep this the SAME/UNCHANGED between posts?

___
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] Complicated join

[David Bicking]

INSERT INTO M (CombinedKeyField, EvtNbr)

VALUES ('A', 1),
('A', 5);

INSERT INTO E (CombineKeyField, EvtNbr)
VALUES ('A', 1)
, ('A', 2)
, ('A', 3)
, ('A', 4)
, ('A', 5)
, ('A', 6)
, ('A', 7)
, ('A', 8)
, ('A', 9)
, ('B', 1);

I'm ignoring the TransDate part for now.

This is what I want:

SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A   1   1
A   2   1
A   3   1
A   4   1
A   5   5  -- matches the (A,5) record in the M table.

A   6   1
A   7   1
A   8   1
A   9   1
B   1   NULL  -- no match found for CombinedKeyfield in M


This is the closest I have got

select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
from E left join M
on E.CombinedKeyField = M.CombinedKeyField
and (E.EvtNbr = M.EvtNbr
or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
   WHERE M1.CombinedKeyField = E.CombinedKeyField 
   ) 
)

But it doubles up on A,5, matching both on A,1 and A,5 in M
And it doesn't return B,1 with no match even though it is a left join.

Hopefully that comes out readable, and my needs are clearer.

Thanks,
David

- Original Message -
From: James K. Lowden 
To: sqlite-users@mailinglists.sqlite.org
Sent: Monday, September 19, 2016 10:57 AM
Subject: Re: [sqlite] Complicated join

On Thu, 15 Sep 2016 15:53:10 + (UTC)



David Bicking  wrote:

> (1) The CombinedKeyFields must always match in each table(2) Match
> using the EvtNbr, but if no match, use the lowest M.EvtNbr that
> matches the CombinedKeyFields
> 
> (3) Match using the TransDate but if no exact match, match on the
> M.TransDate that is less than the E.TransDate but greater than the
> prior E.TransDate

I think this is what you describe: 

select E.CombinedKeyFields, max(M.EvtNbr) as EvtNbr, max(M.TransDate)
as TransDate from E left join M
on E.CombinedKeyFields = M.CombinedKeyFields
and E.EvtNbr >= M.EvtNbr
and E.TransDate >= M.TransDate

--jkl
___
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] Complicated join

[David Bicking]

EvtNbr cannot be null, and can be 0. (Though I understand if there is any 
Evtnbr > 0, there can't be an EvtNbr 0, the lowest can be one or higher.)


I need to come up with some test data, if only to test the "I'm pretty sure it 
doesn't work" solution I came up with.

One good bit of news is my boss came back saying I can skip the closest date 
bit, either the dates match or they don't, but I don't believe that decision 
will stick, so I am still trying to come up with an answer.
David


PS. I switched this to send out "plain text", but in the past I have been told 
reader end up seeing an unable to read small font on their end.


From: R Smith 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Saturday, September 17, 2016 7:25 AM
Subject: Re: [sqlite] Complicated join



On 2016/09/15 5:53 PM, David Bicking wrote:
> I have two tables:
> CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY 
> KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E  ( 
> CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY 
> KEY(CombinedKeyFields, EvtNbr, TransDate));
> "CombinedKeyFields" is shorthand for a combination of about a half dozen 
> fields in the primary key."TransDate" is an integer storing a proprietary 
> date sequence number, where an older date is always less than a newer date
> Now, I want to do E LEFT JOIN M
> (1) The CombinedKeyFields must always match in each table(2) Match using the 
> EvtNbr, but if no match, use the lowest M.EvtNbr that matches the 
> CombinedKeyFields
>
> (3) Match using the TransDate but if no exact match, match on the M.TransDate 
> that is less than the E.TransDate but greater than the prior E.TransDate
> For M.TransDate = 94E.TransDate = 96 will match to 94but E.TransDate = 98 
> will have no match because 94 is less than the prior trans at 96..The idea is 
> to find the closest date that matches that couldn't be matched to another 
> record.

Hi David,

I was going to do this for fun, but some anomalies exist in your 
description which needs to be understood first.

1 - Does every entry have an EvtNbr? Or can it be null? What is the 
lowest EvtNbr in every set - 1 or 0 or something else or indeterminate?  
If an EvtNbr cannot be Null, then point (3) above is moot because there 
will be no item with a date that matches CombinedKeyFields that doesn't 
also have a lowest EvtNbr as can be matched by requirement (2). If the 
lowest EvtNbr for any entry is 0 or 1 then you can simply look for that 
EvtNbr if a match is not found for the exact EvtNbr - which would always 
exist unless there are NO matches on CombinedKeyFields in which case 
there should be no matching lines at all as given by (1).

Perhaps if you could (as suggested by another poster) send a full schema 
with some data and an example of the output needed (highlighting all the 
possible check cases as described above).

If clarity on these can be had, the SQL is quite possible and not too 
convoluted even (though the jury is out on efficiency as yet).



> Saying a prayer that the email gods won't wrap all these lines together in to 
> an unreadable mess like the last time I asked for advice here...

Apparently prayer is not an effective technology :)  The e-mail Gods are 
not at fault here, perhaps the "Send as" settings in your mail 
composition client can be explored?  Many formats should accommodate 
correct formatting. I'm guessing your client has a plain-text editor 
that wraps the result into an HTML paragraph (or some other weirdness 
that you can hopefully deduce and fix by checking the settings).

___
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] Complicated join

[David Bicking]

I have two tables:
CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY 
KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E  ( CombineKeyFields, 
EvtNbr, TransDate, OtherFields, PRIMARY KEY(CombinedKeyFields, EvtNbr, 
TransDate));
"CombinedKeyFields" is shorthand for a combination of about a half dozen fields 
in the primary key."TransDate" is an integer storing a proprietary date 
sequence number, where an older date is always less than a newer date
Now, I want to do E LEFT JOIN M
(1) The CombinedKeyFields must always match in each table(2) Match using the 
EvtNbr, but if no match, use the lowest M.EvtNbr that matches the 
CombinedKeyFields

(3) Match using the TransDate but if no exact match, match on the M.TransDate 
that is less than the E.TransDate but greater than the prior E.TransDate
For M.TransDate = 94E.TransDate = 96 will match to 94but E.TransDate = 98 will 
have no match because 94 is less than the prior trans at 96..The idea is to 
find the closest date that matches that couldn't be matched to another record.
All this data is coming from upstream data, so this is the data we have on 
hand, though the schema for this reporting package can still be changed, but I 
would have to justify the change by saying the report can only be done with the 
change...
Can this join be done in SQL?
If this were an inner join, I believe I could use CASE statements in the WHERE 
clause, but I'm not sure that would work moving it to the JOIN... ON clause.
Any advice or help is much appreciated.
Thanks,David
Saying a prayer that the email gods won't wrap all these lines together in to 
an unreadable mess like the last time I asked for advice here...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite3 have variables like Oracle?

[David Bicking]

Sqlite doesn't have variable.  While last row id is available other ways, a 
trick to emulate a variable is to create a temp table with one field. You put 
the value in to the that field. You can then cross join with the rest of your 
table as need be, or do a sub-select to value a SET command.

David
  From: mikeegg1 
 To: SQLite mailing list  
 Sent: Sunday, September 11, 2016 5:48 PM
 Subject: [sqlite] Does sqlite3 have variables like Oracle?
   
I think Oracle (a long distant memory) has variables like @variable or 
@@variable where you can do something like '@variable = select rowid from table 
where field = 4' and then later do 'insert into othertable (field2) 
value(@variable)’. Does this make sense? I’m wanting to in the shell select 
lastrowid then update a bunch of inserted rows in a different table with the 
previously inserted lastrowid.

I don’t have an example at the moment of what I’m trying to do. I’m generating 
a bunch of statements into a file that I will then ‘sqlite3 data.sqlite3 < 
data.sql’. I’m using the value of -14 (just a number) as a place holder in a 
bunch of insert statements then at the end of each group I do an update to the 
actual rowid.

Mike
___
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] MIN/MAX query

[David Bicking]

Whatever mangled the text must have put a 2 in front of the 7, cause the copy 
in my sent mail box has a 1 in front of the 7.?? I never got a copy of my email 
from the mailing list, so I never saw the mangled version, just quotes of it. I 
assumed you made the typo and didn't want to nit-pick someone trying to help me.

At any rate,? Igor gave me the answer to my question, so unless someone comes 
up with a different solution, the problem is solved. (in that sql is not a good 
place to solve the problem...)

Thanks,
David


  From: R Smith 
 To: sqlite-users at mailinglists.sqlite.org 
 Sent: Thursday, February 18, 2016 12:11 PM
 Subject: Re: [sqlite] MIN/MAX query



On 2016/02/18 4:59 PM, Igor Tandetnik wrote:
> On 2/18/2016 4:55 AM, R Smith wrote:
>> First of, your intended results require a fundamentally wrong assumption
>> about Sets. (SQL is essentially operating on SETs and sets have no 
>> order).
>> You should really have another column, like a primary key ID that notes
>> the position of each line
>
> Looks like L is precisely such an ID. The OP is looking for runs of 
> identical (I, V) pairs when the table is scanned in order by (I, L) 
> (or, in other words, for each value of I, runs of V's in order by L).
>
> If that's the case, then //...

That was my first impression too Igor, but look again, I goes to 2 while 
L is still lingering on 7... the very next line sees L going to 1 while 
I is 2 to produce a moment when the order is not at all as sensible and 
breaks any formal ordering that can be seen as a run. L will again get 
to 7... will it definitely be after I changes?

Perhaps it was late and the OP was tired, but the example is not 
sensible as given without another column.


___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MIN/MAX query

[David Bicking]

Thanks. You understood my intention, and confirmed my fear that I couldn't do 
it efficiently in SQL.
David


  From: Igor Tandetnik 
 To: sqlite-users at mailinglists.sqlite.org 
 Sent: Thursday, February 18, 2016 9:59 AM
 Subject: Re: [sqlite] MIN/MAX query

On 2/18/2016 4:55 AM, R Smith wrote:
> First of, your intended results require a fundamentally wrong assumption
> about Sets. (SQL is essentially operating on SETs and sets have no order).
> You should really have another column, like a primary key ID that notes
> the position of each line

Looks like L is precisely such an ID. The OP is looking for runs of 
identical (I, V) pairs when the table is scanned in order by (I, L) (or, 
in other words, for each value of I, runs of V's in order by L).

If that's the case, then

select I, min(L) MinL, max(L) maxL, V
from MyTable t
group by I,
? (select min(L) from MyTable t2 where t2.I=t.I and t2.V=t.V and t2.L 
<= t.L
? ? and not exists (
? ? ? select 1 from MyTable t3 where t3.I=t.I and t3.V != t.V
? ? ? and t3.L between t2.L and t.L)
? )

Though frankly, I'd implement this logic in application code. Just scan 
the table with "select * from MyTable order by I, L" and compute the 
runs in one pass, in linear time. The query above runs in O(N^3) time 
(though an index on (I, L) might improve things) - only really suitable 
as a toy example.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MIN/MAX query

[David Bicking]

Um, I understand sets; which is why I knew the naive group by wouldn't work.
I guess I should have stated my question is HOW do I define the group so that 
in the order of I,L, clusters of common V values are a "group". I need to 
return the value of I and V, with the minimum and maximum L in that cluster. 

I, L and V are data coming from an upstream source. I have no control over it, 
and would rather not write a mid-stream filter program to add an additional 
column, but can do that if there is no way to implement the "grouping" I want 
with the data as is.I is an arbitrary value, and L are sequential numbers for a 
given I. V is related properties for the given I.

(Hopefully it doesn't make a difference, but I am simplifying the upstream 
data. V is actually a half dozen or so columns, but the cluster changes if any 
of the columns change, so for simplification, I just noted them as one value 
column.)
Thanks for your help.
David
From: R Smith 
 To: sqlite-users at mailinglists.sqlite.org 
 Sent: Thursday, February 18, 2016 4:55 AM
 Subject: Re: [sqlite] MIN/MAX query



Let me see if I can unpuzzle this question - kindly say if it is not 
correctly assumed:
-
I have a table:
I? L V
1? 1 A
1? 2 A
1? 3 A
1? 4 B
1? 5 B
1? 6 A
2? 7 A
2? 1 C
2? 2? C

I want to return the minimal and maximum L for each "group" of V in a 
given I.
The result I want:
I? MinL? ? MaxL V
1? ? 1? ? ? 3? ? ? A
1? ? 4? ? ? 5? ? ? B
1? ? 6? ? ? 7? ? ? A
2? ? 1? ? ? 2? ? ? C

I know I can't just do? ? SELECT I, MIN(L) as MinL, MAX(L) as MaxL, V? 
FROM T? GROUP BY I, Vsince that would return min/max of 1 and 7 for A, 
instead of the 2 lines I need.
So should I word the SELECT statement?
-

First of, your intended results require a fundamentally wrong assumption 
about Sets. (SQL is essentially operating on SETs and sets have no order).
You should really have another column, like a primary key ID that notes 
the position of each line (so that the order of your lines matter).
Luckily, in SQLite there is a hidden column named row_id that should 
have that order already pegged - but you should really explicitly add 
one since your results depend on where in the list the values appear, 
not just what they are named.

You should then, try to imply a group identifier, i.e another column 
that adds a group set or some way to know which A values belong 
together. This is not obvious in a SET.

We might be able to find a way to determine grouping after-the-fact, 
based on data shape perhaps, but need more information then.
What's the maximum number of lines that will be in a group?
Does the L counter determine or exclude groups in any way?
Might I and L when considered together offer some clue as to a next 
group starting?

We could use the row-ID distance to narrow it into groups with items 
that have a maximum distance from each other, but then we need to know 
what maximum and minimum distances would be, etc.

Howcome the current data set is ordered like that? Is it a time-based 
thing? like a logger?

Some of these answers might help to make a query that can do it. This 
would be trivial in program code though.

Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MIN/MAX query

[David Bicking]

On 02/17/2016 03:22 PM, nomad at null.net wrote:
> On Wed Feb 17, 2016 at 06:17:40PM +0000, David Bicking wrote:
>> I have a table
>> I  L  V1 1   A1 2   A1 3   A1 4   B1 5   B1 6   A1 7   A2 1   C2 2   C
> The formatting of this (and your desired results) does not make the
> question clear enough.  Can you resend with each row on a separate line
> and perhaps the text "NULL" to represent empty values?
>
> Mark

Each row was on a separate line. Why it got squished on to one line is 
beyond me.
Nor do I know how to make it not be squished.

But thanks for looking anyway.

David

[sqlite] MIN/MAX query

[David Bicking]

I have a table 
I? L? V1 1?? A1 2?? A1 3?? A1 4?? B1 5 ? B1 6?? A1 7?? A2 1 ? C2 2?? C
I want to return the minimal and maximum L for each "group" of V in a given I.

The result I want:
I? MinL? MaxL? V1? 1??? 3?  A1? 4??? 5?? B1? 6??? 
7?? A2? 1??? 2?? C
I know I can't just do 
?? SELECT I, MIN(L) as MinL, MAX(L) as MaxL, V? FROM T
? GROUP BY I, Vsince that would return min/max of 1 and 7 for A, instead of the 
2 lines I need.
So should I word the SELECT statement?
Thanks,David

Re: [sqlite] Performance gain in SQLite

[David Bicking]

I recall there is or once was a way to compile sqlite so that you could
embed in your program the pre-generated sql. This was for embedded
programs, not to speed things up, but to remove the parser and save
memory in very small embedded systems. For it to work, the sql and the
database schema couldn't change.

I haven't heard this mentioned in a very long time, so I don't know that
it is still supported anymore. But it might be worth your searching the
sqlite web site, or the user list from a few years back.

David

On 10/06/2014 07:50 AM, Prakash Premkumar wrote:
> Will I get any performance benefits if I directly generate the Vdbe program
> instead of generating the sql string ?
>
> My application emits out sql strings for execution. Instead if I generate
> the Vdbe program myself , will I get performance benefits since, I am
> bypassing the parser.
>
> Thanks
> Prakash
> ___
> 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] Application with 'grid' form for entering table data wanted

[David Bicking]

I don't know php, but this sounds like what I think you want: phpGrid | PHP 
Datagrid Made Easy.

 
 phpGrid | PHP Datagrid Made Easy.
phpGrid is a simple, powerful and fully customizable PHP component for 
generating PHP AJAX datagrid for create, read, update, delete (CRUD) records.  
View on phpgrid.com Preview by Yahoo  
David



 From: "c...@isbd.net" 
To: sqlite-users@sqlite.org 
Sent: Wednesday, June 4, 2014 6:29 AM
Subject: Re: [sqlite] Application with 'grid' form for entering table   data
wanted
 

Stephen Chrzanowski  wrote:
> By the sounds of it, the OP wants a spreadsheet app, that isn't a
> spreadsheet app, acts like a database, but isn't quite a database, looks
> simple to use, but provide editable information in a reasonable format.
> 
You're almost right.  Many people use spreadsheets for the sort of
data entry task that I'm trying to do but really spreadsheets aren't
optimum for the job.  

Yes, I basically want a spreadsheet data entry screen with a database
behind it.  However the *detail* of how spreadsheets work for data
entry isn't really ideal.


> To bring up a point about Google that RSmith brought up, Google Docs has a
> spreadsheet app that has zero install, accessible by many (If you want) and
> available from anywhere provided an internet connection is available, and
> allows you to edit, search, sort, filter, and all the other bells and
> whistles other spreadsheet and database applications have.  Mind you
> linking an external data requester application might be a bit of a PITA (If
> that is a requirement) might start throwing additional complexities into
> the mix.
> 
That might be of interest, can you point me at it please.


> Since the LAMP setup is available, there are other spreadsheet type
> applications that allow you to move from Google Docs to in-house.  Off the
> top of my head I can't think of the names I've used in the past (I write
> databases, not spreadsheets) but they are out there, free for all, just
> short of public domain (MIT maybe?), and in a
> 'download-extract-run-configure' format usually, which is GREAT with a LAMP
> setup.
> 
Hmmm, why is it that all these apps are "... can't think of the names
..." ?  :-)


> The other option is to go ground up with PHP and some very basic AJAX
> calls, but even then, you'd need SOME kind of management.  How many rows to
> display, how many columns to display, do we add rows, remove rows, what
> field is what cast, validation requirements, data storage considerations,

No validation, just text fields.  Number of columns is defined by the
number of columns in the database table.  Open to 'all' because it's
on a home LAN with no (HTTP) access from outside.

> data VOLUME considerations, who accesses the data, what is read only and
> who/what can write to the table, are we talking one database, one table,
> one user, or, many databases, many tables, many users, does encryption come
> into play and if so at what level?  Some kind of management HAS to happen,
> like it or not.  And if basic management comes into play, you might as well
> stick with something that already exists (Google Docs, OpenOffice,
> LibreOffice, Excel, Notepad).
> 

-- 
Chris Green
·

___
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] Joining different databases

[David Bicking]

Thanks, I figured that would be the answer, but was hoping that I was missing 
something obvious.

David




 From: Igor Tandetnik 
To: sqlite-users@sqlite.org 
Sent: Thursday, May 29, 2014 11:58 AM
Subject: Re: [sqlite] Joining different databases
 

On 5/29/2014 10:42 AM, David Bicking wrote:
>> How complicated is the join? Could you show a hypothetical SQL statement
> you would have used had both tables been in the same database?
>
> Not complicated:   Select b.id, b.name, b.otherfields from a inner join b on 
> a.id = b.id where a.name<>b.name or a.otherfields<>b.otherfields;  -- where 
> "otherfields" is about four fields of information.

Ah, I see. It's not very selective - it requires a full scan of both 
tables. What I had in mind was something like "run as much as you can 
against SQLite, figure out which (hopefully, few) rows you need to 
retrieve from SQL Server, then run a separate query there". But your 
statement doesn't lend itself to such a manual execution plan.

There exist an ODBC driver for SQLite: 
http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc (disclaimer: haven't 
used it myself). But you still need some SQL engine - such as MS Access 
- that can run queries against multiple ODBC sources. SQLite itself 
can't do that.
-- 
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Joining different databases

[David Bicking]

 From: Igor Tandetnik 
To: sqlite-users@sqlite.org 
Sent: Thursday, May 29, 2014 10:34 AM
Subject: Re: [sqlite] Joining different databases
 

On 5/29/2014 10:26 AM, David Bicking wrote:



>> I have a somewhat large table in an sqlite database and another large table 
>> on an MS SQL Server database (on a slow network).  I want to query both 
>> tables in a join.

>How complicated is the join? Could you show a hypothetical SQL statement 
you would have used had both tables been in the same database?

Not complicated:   Select b.id, b.name, b.otherfields from a inner join b on 
a.id = b.id where a.name<>b.name or a.otherfields<>b.otherfields;  -- where 
"otherfields" is about four fields of information.

David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Joining different databases

[David Bicking]

I have a somewhat large table in an sqlite database and another large table on 
an MS SQL Server database (on a slow network).  I want to query both tables in 
a join.


The join is likely to produce from zero to a dozen rows.

First thought was to copy the data from the SQL Server table to the sqlite 
file, but that alone takes over 4 minutes to run.

What I am currently doing is to copy the data from the sqlite table to an MS 
Access table, then have Access link to SQL Server, and run the query in Access. 
The copy takes just under a minute, and the join also takes just under a 
minute. So a total under 2 minutes. Twice as fast!  But, I want to move this to 
a different computer which does not have an MS Access license, and corporate IT 
doesn't want to pay for that license.

So what other options do I have?

I haven't been able to find anything to say how I could use ODBC to connect to 
two different data sources.
I haven't seen how I could ATTACH to an ODBC link within sqlite. (I would guess 
a vfs could be written to do that, but if even feasible that is far beyond my 
minimal coding skills.)

Thanks for any suggestions you can give.
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows user app to display input form + reporting?

[David Bicking]

On 03/12/2014 08:05 AM, Gilles Ganault wrote:

On Wed, 12 Mar 2014 07:59:39 -0400, David Bicking
 wrote:

Not exactly "lite" in size, but kexi does have most of the features of
Access and uses sqlite to store its data:

http://kexi-project.org/

Thanks for the link. It's currently not available for Windows, but
I'll keep an eye on it.

Sorry, I have used it under windows. Didn't know that the windows port 
was no longer available.


David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows user app to display input form + reporting?

[David Bicking]

On 03/11/2014 07:31 PM, Gilles Ganault wrote:

Hello

A friend needs to move from Excel to a database. The school won't
pay for the full version of MS Office that includes Access, so
recommended that she use LibreOffice Base instead.

I just checked it out, and it seems to only be a front-end to the
HSQLDB database which is written in Java.

Before I check it out, I'd like to make sure there's no SQLite-based
lighter solution where...
1. I'll run the commands originally to build the DB + tables
2. The Windows application will either display forms for her to enter
data, or provide an easy way to import data from Excel
3. It will then display data through some reporting tool.

Is there a good SQLite-based alternative to LibreOffice Base?

I found this:
http://sqlitestudio.pl
http://www.valentina-db.com
http://www.navicat.com/products/navicat-for-sqlite

Thank you.




Not exactly "lite" in size, but kexi does have most of the features of 
Access and uses sqlite to store its data:


http://kexi-project.org/

David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partially excluding records

[David Bicking]

Thanks everyone who helped me.

This is what I ended up with:

SELECT
  key
, COALESCE(SUM(CASE WHEN STATUS!='C' THEN 1 END)
,SUM(CASE WHEN STATUS='C' THEN 1 END)) CNT
, COALESCE(MIN(CASE WHEN STATUS!='C' THEN STATUS END) || CASE WHEN 
COUNT(DISTINCT CASE WHEN STATUS!='C' THEN STATUS END)>1 THEN '+' END
,'C') STATUSES
, COALESCE(MIN(CASE WHEN STATUS!='C' THEN ACTION END) || CASE WHEN 
COUNT(DISTINCT CASE WHEN STATUS!='C' THEN ACTION END)>1 THEN '+' END
,MIN(CASE WHEN STATUS='C' THEN ACTION END) || CASE WHEN 
COUNT(DISTINCT CASE WHEN STATUS='C' THEN ACTION END)>1 THEN '+' END) ACTIONS
, COALESCE(SUM(CASE WHEN STATUS!='C' THEN VALUE END), SUM(CASE WHEN STATUS='C' 
THEN VALUE END)) VALUES
FROM T1
GROUP BY T;

I didn't include the Actions and values bit in my original question, but the 
final solution will does this with a half dozen different fields. (And I am 
already getting static because "MIN" isn't necessarily picking the filed value 
they would rather see.

Anyway like Igor said, no where clause needed, which is a good thing since I 
need to fit this in to a sql statements that takes two pages to print in full, 
and I can't even remember what some of the where clauses are meant to do, let 
alone how I would combine them with new ones.

David


On Fri, 2/21/14, Igor Tandetnik  wrote:

 Subject: Re: [sqlite] partially excluding records
 To: sqlite-users@sqlite.org
 Date: Friday, February 21, 2014, 3:25 PM
 
 On 2/21/2014 3:11 PM,
 David Bicking wrote:
 > But I am curious,
 wouldn't this yield a "Statuses" for key 2 of
 'C+', when it should be 'O'?
 
 You could use the same
 technique there. The technique lets you know 
 whether you have only C's, only
 non-C's, or a mix of the two, and act 
 accordingly.
 
 For example, you could replace min(STATUS) with
 something like
 
 case when
 sum(STATUS != 'C') then min(case when
 STATUS='C' then 'ZZZ' 
 else
 STATUS end) else 'C' end
 
 This returns the smallest of statuses other
 than C if any, and C otherwise.
 -- 
 Igor
 Tandetnik
 
 ___
 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] partially excluding records

[David Bicking]

On Fri, 2/21/14, Igor Tandetnik  wrote:

 Subject: Re: [sqlite] partially excluding records
 To: sqlite-users@sqlite.org
 Date: Friday, February 21, 2014, 2:58 PM
 
 On 2/21/2014 1:23 PM,
 David Bicking wrote:
 >> SELECT Key, COUNT(STATUS) Cnt
 >> , MIN(STATUS) || CASE WHEN COUNT(STATUS)>1 THEN '+' ELSE '' END Statuses
 >> FROM T1
 >> WHERE ...
 >> GROUP BY KEY;
 >>
 
> You might be looking for something like this:
> select key, sum(STATUS != 'C') + (case when sum(STATUS != 'C') = 0 then  
> sum(STATUS
> 'C') else 0 end) Cnt, ...
 
> No special WHERE clause needed.
 
*** found another typo in my example, that should have been WHEN COUNT(DISTINCT 
STATUS)>1 ***

*** Annoying that they make up their minds that they want it to work like this 
today, then end the meeting with... and you can have this done by Monday, 
right? I need to calm down. ***

Anyway, there are other fields and messy CASE statements that probably wouldn't 
work with this solution.

But I am curious, wouldn't this yield a "Statuses" for key 2 of 'C+', when it 
should be 'O'? 

Thanks,
David

 ___
 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] partially excluding records

[David Bicking]

On Fri, 2/21/14, Clemens Ladisch  wrote:

 Subject: Re: [sqlite] partially excluding records
 To: sqlite-users@sqlite.org
 Date: Friday, February 21, 2014, 1:38 PM
 
 David Bicking wrote:
 >> The complication is that if a given key has any non-C value, the C values 
 >> are to be excluded.
 
> First, just exclude all C values:
> ... WHERE Status <> 'C' ...
 
 > If there are only C values, they are to be included.
 >Then do the same query again, but with the all-C keys:
 
>   ...
>   UNION ALL
>   SELECT ...
>   WHERE Key NOT IN (SELECT Key FROM T1 WHERE Status <> 'C')
>   ...
 
Reality is that the "Key" is a combination of 3 or 4 fields.  I suppose I can 
concatenate the fields, but that seems overly ugly.
And the select and existing part of the where clause are complicated, so if I 
can avoid repeating all of that, I'd rather avoid doing it as a union query. 

But if needs must, I will go with this idea.


Thanks,
David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] partially excluding records

[David Bicking]

On Fri, 2/21/14, RSmith  wrote:

 Subject: Re: [sqlite] partially excluding records
 To: sqlite-users@sqlite.org
 Date: Friday, February 21, 2014, 1:34 PM
 
 
 On
 2014/02/21 20:23, David Bicking wrote:
 >
 I have a table like
 >
 > SELECT * FROM T1;
 >
 Key  Status
 > 1      O
 > 1      O
 > 2  O
 > 2      C
 > 3     C
 > 3      C
 > 4      O
 > 4    P
 >
 >
 > Now, I need to consolidate that data.
 >
 > SELECT Key,
 COUNT(STATUS) Cnt
 > , MIN(STATUS) || CASE
 WHEN COUNT(STATUS)>1 THEN '+' ELSE '' END
 Statuses
 > FROM T1
 >
 WHERE ...
 > GROUP BY KEY;
 >
 > Key  Cnt  Statuses
 > 1      2O
 > 2      1     C
 > 4      2     O+
 >
 >> The complication is that if a given key has any non-C value, the C values 
 >> are to be excluded. If there are only C values, they are to be included.
 >> How can I state the WHERE clause to do that?
 
 >This last statement contradicts your example completely. You say: "if a given 
 >key
 >has any non-C value, the C values are to be excluded"
 
 >But looking at the table the Key-value 2 has one non-C value, yet it is
 >included and showing the C.
 
 >You then say: "If there are only C values, they are to be included"
 
 >But Key "3" clearly contains only C values, yet they are explicitly excluded 
 >from the result list
 >If you could kindly fix either the statement or the example so we know which 
 >is accurate, then will gladly try to solve the
 >WHERE riddle for you!

Sorry, stupid typos on my part.
 

 Key  Cnt  Statuses
  1  2 O
  2  1 O
  3  2 C
  4  2 O+
 

 ___
 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

[sqlite] partially excluding records

[David Bicking]

I have a table like

SELECT * FROM T1;
Key  Status
1  O
1  O
2  O
2  C
3  C
3  C
4  O
4  P


Now, I need to consolidate that data.

SELECT Key, COUNT(STATUS) Cnt
, MIN(STATUS) || CASE WHEN COUNT(STATUS)>1 THEN '+' ELSE '' END Statuses
FROM T1
WHERE ...
GROUP BY KEY;

Key  Cnt  Statuses
1  2 O
2  1 C
4  2 O+

The complication is that if a given key has any non-C value, the C values are 
to be excluded. If there are only C values, they are to be included.
How can I state the WHERE clause to do that?

Thanks,
David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

[David Bicking]

The first time I saw sqlite demonstrated at a linux user group, the presenter 
didn't realize he was using a memory database. I had to explain why all his 
work was lost, then proceeded to continue the demo since I knew more about the 
product. (This was years ago, I think we were still at sqlite 2 at the time.)

Personally, I don't like the idea of a default db. I would rather see a warning 
message sent to the console if no filename was specified saying that data is 
being saved in memory and will be lost on exit. And perhaps suggesting how to 
open a file if that is how they want to proceed.

I really don't like a bizarre solution that has sqlite3.exe or (sqlite3 on 
linux/mac) behaving differently between a double click or from the command 
line. That just seems wrong to me.

David


On Mon, 2/10/14, Stephan Beal  wrote:

 Subject: Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line 
shell
 To: "General Discussion of SQLite Database" 
 Date: Monday, February 10, 2014, 10:46 AM
 
 On Mon, Feb 10, 2014 at
 4:41 PM, Simon Slavin 
 wrote:
 
 > Why ?  I
 suspect some Mac user would find it just as useful.  And
 then why
 > leave Unix users out ?
 >
 
 Speaking
 as a member of the Unix-only crowd: please don't! While
 i admit
 that the current behaviour has
 caused minor amounts of annoyance on my part
 (my own fault, as i've been using it long
 enough to know better), i also
 feel that the
 current behaviour is "correct." i think it's
 an interesting
 idea for Windows, though.
 Don't have an opinion on Apple installations.
 
 If it could be configured via
 environment variable, i'd be happy to see it
 in Unix, too. e.g. SQLITE3_DEFAULT_DB, if set
 to a non-empty string, would
 be the db which
 gets automatically opened at startup IFF 1==argc (or some
 similar heuristic - maybe always load it if no
 filename args are provided).
 
 
 -- 
 -
 stephan beal
 http://wanderinghorse.net/home/stephan/
 http://gplus.to/sgbeal
 "Freedom is sloppy. But since
 tyranny's the only guaranteed byproduct of
 those who insist on a perfect world, freedom
 will have to do." -- Bigby Wolf
 ___
 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] (no subject)

[David Bicking]

On Tue, 1/28/14, Igor Tandetnik  wrote:

 Subject: Re: [sqlite] (no subject)
 To: sqlite-users@sqlite.org
 Date: Tuesday, January 28, 2014, 2:41 PM
 
 On 1/28/2014 2:26 PM,
 David Bicking wrote:
 > I have two tables:
 >
 > ARB
 >   KEY (PRIMARY KEY)
 >   ASSIGN (NOT NECESSARILY
 UNIQUE)
 >
 > DMC
 >   KEY (NOT UNIQUE)
 >   ASSIGN (NOT UNIQUE)
 >   VALUE
 >
 > I need to report all the records from ARB,
 and sum up the values if the keys match OR if the keys
 don't match, then sum up the values where the ASSIGN
 matches, but only if the ASSIGN is unique in ARB.
 >
 > SELECT ARB.KEY
 > , ARB.ASSIGN
 > ,
 COALESCE((SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.KEY =
 ARB.KEY)
 >                   
     , (SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.ASSIGN =
 ARB.ASSIGN AND ...), 0)
 > FROM ARB;
 >
 > I can't think of
 >what to put after that AND
 
 >Something like this perhaps:
 
 >and 1 = (select count(*) from
 >ARB t2 where t2.ASSIGN=ARB.ASSIGN)
 
> Igor Tandetnik

Thanks.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[David Bicking]

I have two tables:

ARB 
 KEY (PRIMARY KEY)
 ASSIGN (NOT NECESSARILY UNIQUE)

DMC
 KEY (NOT UNIQUE)
 ASSIGN (NOT UNIQUE)
 VALUE

I need to report all the records from ARB, and sum up the values if the keys 
match OR if the keys don't match, then sum up the values where the ASSIGN 
matches, but only if the ASSIGN is unique in ARB.

SELECT ARB.KEY
, ARB.ASSIGN
, COALESCE((SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.KEY = ARB.KEY)
  , (SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.ASSIGN = 
ARB.ASSIGN AND ...), 0)
FROM ARB;

I can't think of what to put after that AND

I don't think it matters, but I simplified things up there. DMC is actually a 
view of a table with KEY/ASSIGN and another with KEY/VALUE.

Any help is appreciated.

Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does not detect invalid column name when subexpression optimized away

[David Bicking]

But isn't NULL and 0 a NULL? So wouldn't it need to evaluate X to determine if 
it was null, and thus discover it wasn't a valid column name and return an 
error?

David


On Thu, 12/19/13, Richard Hipp  wrote:

 Subject: Re: [sqlite] Does not detect invalid column name when subexpression 
optimized away
 To: "R Smith" , "General Discussion of SQLite Database" 

 Cc: amb...@math.bme.hu
 Date: Thursday, December 19, 2013, 9:27 PM
 
 On Thu, Dec 19, 2013 at
 6:36 PM, RSmith 
 wrote:
 
 >  my guess is
 the optimiser pounces directly on the fact that (X and 0)
 > will always be 0
 >
 
 Correct.  The code is at
 
     http://www.sqlite.org/src/artifact/962c2988?ln=556-559
 
 sqlite3ExprAnd() gets called
 to build the AND operator directly from the
 parser, long before any name resolution has
 occurred, and indeed before the
 expression
 has been fully parsed.  As long as one side or the other of
 the
 AND operator does not have an egregious
 syntax error, if the other side
 evaluates to
 FALSE then the whole expression is coded as a FALSE
 literal.
 
 That code was
 added here:
 
     http://www.sqlite.org/src/info/f9a7e179cbbeeab5
 
 
 -- 
 D. Richard Hipp
 d...@sqlite.org
 ___
 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] Is this a proper syntax?

[David Bicking]

On 10/24/2013 07:34 PM, Igor Korot wrote:

Igor,


On Thu, Oct 24, 2013 at 12:57 PM, Igor Tandetnik  wrote:


On 10/24/2013 3:23 PM, Igor Korot wrote:


Will this query work:

UPDATE a SET a.field1 = (SELECT b.field1 FROM b AS myfield), a.field2 =
myfield...

or I will have to repeat subquery for a.field2?


I'm pretty sure you will have to repeat the subquery. In your statement,
myfield is an (unused) alias for table b, not for a value produced by the
subquery (which I don't know of any way to name or reuse).


Well, let me try to explain myself.
Consider following schema:

CREATE TABLE players( playerid INTEGER, name TEXT, rank INTEGER, PRIMARY
KEY playerid);
CREATE TABLE leagueplayers(playerid INTEGER, leagueid INTEGER,
original_rank INTEGER, current_rank INTEGER);

The players table is populated.
What I'm trying to do is to populate the second table with the last 2
fields: original_rank and current_rank.

So I was thinking of something like this:

UPDATE leagueplayers SET original_rank = (SELECT rank FROM players AS a),
current_rank= a WHERE players.playerid = leagueplayers.playerid;

But I guess this query will not work. ;-)

Any idea how to make the proper query?

Thank you.


You could try
REPLACE LEAGUEPLAYERS (playerid, leaqueid, original_rank, current_rank)
 SELECT L.playerid, L.leagueid, P.rank, P.rank
 FROM leagueplayers L INNER JOIN players P ON L.playerid = P.playerid;

That would replace the existing record with the new data.
But I think sqlite would notice that the subquery was the same in the 
UPDATE statement that Igor T gave and not run it twice, so that would 
probably be faster.


David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert statement

[David Bicking]

You might be able to store your "variable" in a table:

CREATE TABLE table_lastid  (id INTEGER);
INSERT INTO table_lastid (id) VALUES(0); 


Then in your sequence:

INSERT INTO table_a (val) VALUES ('xx');

UPDATE table_lastid SET id = last_insert_rowid();

INSERT INTO table_b (id, key, val)
       SELECT id, 'yyy', 'zzz' from table_lastid;

David




 From: Joseph L. Casale 
To: "sqlite-users@sqlite.org"  
Sent: Sunday, September 8, 2013 10:17 PM
Subject: Re: [sqlite] Insert statement
 

> If I understand the question, and there is no key other than the 
> auto-incrementing
> integer, there might not be a good way.  It sounds like the database's design 
> may
> have painted you into a corner.  

Hi James,
Well, after inserting one row into table A which looks like (without specifying 
the id
and letting it auto generate):

CREATE TABLE table_a ( 
    val    VARCHAR COLLATE "nocase" NOT NULL,
    id INTEGER NOT NULL,
    PRIMARY KEY ( id ) 
);

(forgive that odd looking format, its SQLAlchemy output...)

I have for example 20 rows in table B to insert referencing the above:

CREATE TABLE table_b ( 
    val VARCHAR COLLATE "nocase",
    key VARCHAR COLLATE "nocase" NOT NULL,
    id   INTEGER,
    seqno   INTEGER NOT NULL,
    PRIMARY KEY ( seqno ),
    FOREIGN KEY ( id ) REFERENCES table_a ( id ) 
);

So selecting last_insert_rowid() always gives me the 'id' of the previous row 
from table_a
after an insert. So I would insert into table_a, get that rowid, and build the 
remaining 20
inserts. For the sake of keeping the entire sql statement manageable, I was 
hoping not to
build the next 20 statements based on SELECT id FROM table_a WHERE val='xxx' as 
that string
will be very long.

So this works for one insert:

INSERT INTO table_a (val) VALUES ('xx');
INSERT INTO table_b (id, key, val)
       SELECT last_insert_rowid(), 'yyy', 'zzz';

Just not sure how to perform 20 or 30 of those inserts into table_b after the 
one into table_a
yields the id value I need.

Thanks!
jlc
___
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] SQLite Input with validation and lookup

[David Bicking]

I've never used Visual FoxPro, but I suspect that it allows you to create forms 
to insert data in to the database.

Sqlite doesn't do that. Sqlite only provides the library to store the data via 
SQL statements that you execute via sqlite3_prepare()/sqlite3_step() function 
calls. 


You are expected to acquire or write your own front end software that will let 
you enter the data and validate that it is correct. The CHECK constraints are a 
final, just in case the front end messed up, way to make sure the database 
doesn't store bad data, but your front end form should validate the data before 
it sends it to sqlite to store.

Perhaps the better question for you to be asking is what software would best 
meet your needs in creating a form that will interface with sqlite's db back 
end. And the answer to that probably depends on what language you are using to 
write your application and what platform it is going to be run on.

Hope that helps.
David




 From: Stephen Hughes 

But as I said in my last post I
got confused between Visual FoxPto SQL and Visual FoxPro built in
programming language. I could certainly achieve what I want by using a
spreadsheet, such as LibreOffice Calc, to create a validated and extended
temporary file which I could then use to import the relevant validated
fields to my "Order items" table in my SQLite database; but this seems to
be a rather long-winded and circuitous approach. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fuzzy joins

[David Bicking]

Yeah, one benefit of a long commute is that I could puzzle it out and 
understand why it works.

select t1.*,
(
 select v from t2 where t2.k1=t1.k1 and t2.k2=t1.k2 and t2.k3=t1.k3 and 
t2.k4=t1.k4 and t2.k5=t1.k5
 group by k1,k2,k3,k4,k5
 having count(*)=1
 union
 select v from t2 where t2.k1=t1.k1 and t2.k2=t1.k2 and t2.k3=t1.k3 and 
t2.k4=t1.k4
 group by k1,k2,k3,k4
 having count(*)=1
 union
 select v from t2 where t2.k1=t1.k1 and t2.k2=t1.k2 and t2.k3=t1.k3
 group by k1,k2,k3
 having count(*)=1
 union
 select v from t2 where t2.k1=t1.k1 and t2.k2=t1.k2
 group by k1,k2
 having count(*)=1
 union
 select v from t2 where t2.k1=t1.k1
 group by k1
 having count(*)=1
)
from t1;

I haven't fully tested it, but it looks like this does almost what I want. 
Ideally if something is duplicated in t1, but is unique in t2, this will match 
the t2 record to both records in t1, and it shouldn't.

But I am really close now.

David




 From: Petite Abeille 
To: General Discussion of SQLite Database  
Sent: Monday, March 11, 2013 5:45 PM
Subject: Re: [sqlite] Fuzzy joins
 

On Mar 11, 2013, at 10:32 PM, David Bicking  wrote:

> Um, I am wrong, cause I just tried it and sqlite only returns the level 5 
> result. I have no clue why!

The joins are setup from most specific to broadest. Each join is evaluated only 
if the previous one returns null (i.e. all these 'and PreviousLevel.Key is 
null').

___
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] Fuzzy joins

[David Bicking]

Ouch, I certainly did forget the where clauses!

Table1 and Table2 are going to be roughly the same size.
What I am trying to do, btw, is to take an extract from a prior period, which 
has had the "Value" added to it, and move that value in to the matching record 
in the new extract. There is a natural key in the table that is being 
extracted, but the columns that make up that natural key are not exposed to us 
mere mortals. Of the fields I have to play with, only key1 is actually part of 
the natural key in the source table. The others are fields which could be 
freely changed from one month to another. The higher the key number, the most 
likely it is to be changed from one month to another.

In your selects below, lets say we have two records in table2 which have 
exactly the same keys as a record in Table1, won't the select then spit out two 
values in the select? Or am I missing how that case is excluded?

And should I take it the two last Left joins should have been Level2 and 
Level1, and not repeat the use of Level4 and Level5?

And your option (a) is only one of 5 selects, each having one less key?


For (b), lets say:

Table1
Rowid|Key1 | Key2 | Key3 | Key4 | Key5
1 1  2 3 4 5

Table2
Rowid|Key1 | Key2 | Key3 | Key4 | Key5|Value
11    1 2 3 4 5 x

12        1     2 3 4   -5  y

Just  looking at levels 4 and 5 wouldn't the results be...

Table1.Rowid |   Level5.Rowid  | Level4.Rowid |  Value

1 11    12                        x    
(from level 5)1 null    11              
         y    (from level 4)
1 null    12   y    
(from level 4)


Or am I mis-interpreting what you wrote?

Um, I am wrong, cause I just tried it and sqlite only returns the level 5 
result. I have no clue why!

David



 From: Petite Abeille 
To: General Discussion of SQLite Database  
Sent: Monday, March 11, 2013 3:24 PM
Subject: Re: [sqlite] Fuzzy joins
 

On Mar 11, 2013, at 4:54 PM, David Bicking  wrote:

> Am I missing an obviously better way to do it? 
> A way that can easily be expanded when they come back to me and say if I 
> looked at a fifth column, you'd have been able to match it….

As they stand, your updates will always match whatever was computed last as you 
don't have a where clause in your update statements. Most likely not what you 
want.

If only SQLite had a merge statement, this would all be much easier.

Anyhow, personally, I would decompose the problem into two steps: 


(1) How to join Table1 to Table2 considering these various keys.
(2) How to update Table2 with Table2's value given (1)


For (1), you have two main options: (a) keys concatenation  or (b) a series of 
left joins 


(warning: pseudo code ahead)


(a)

select    Table1.row_id as t1_row_id,
          Table2.value
from      (
            select  rowid as row_id,
                    key1 || '.' || key2 || '.' || key3 || '.' || key4 || '.' || 
key5 as key
            from    Table1
          )
as        Table1

left join (
            select  rowid as row_id,
                    key1 || '.' || key2 || '.' || key3 || '.' || key4 || '.' || 
key5 as key,
                    value
            from    Table2
          )
as        Table2
on        Table2.key = Table1.key

The above will always result in two full table scan.


(b) 

select    Table1.rowid as t1_row_id,
          coalesce( Level5.value, Level4.value, Level3.value, Level2.value, 
Level1.value ) as value
from      Table1

left join Table2
as        Level5
on        Level5.key1 = Table1.key1
and       Level5.key2 = Table1.key2
and       Level5.key3 = Table1.key3
and       Level5.key4 = Table1.key4
and       Level5.key5 = Table1.key5

left join Table2
as        Level4
on        Level4.key1 = Table1.key1
and       Level4.key2 = Table1.key2
and       Level4.key3 = Table1.key3
and       Level4.key4 = Table1.key4
and       Level5.key1 is null

left join Table2
as        Level3
on        Level3.key1 = Table1.key1
and       Level3.key2 = Table1.key2
and       Level3.key3 = Table1.key3
and       Level4.key1 is null

left join Table2
as        Level4
on        Level4.key1 = Table1.key1
and       Level4.key2 = Table1.key2
and       Level3.key1 is null

left join Table2
as        Level5
on        Level5.key1 = Table1.key1
and       Level4.key1 is null


While the second option looks more verbose, it may be more appropriate if 
Table2 is small in relation to Table1, and Table1 can be pruned by key1 at the 
very least.


(2) Once you have the data joined, the update itself is much more 
straightforward. Wrap one of the select as a 'create temporary table t2t as' 
and use t

[sqlite] Fuzzy joins

[David Bicking]

This is a weird request. I have a table of data with no natural primary key. I 
need to update this table from a prior table, but some of the data fields can 
change over time, so I must be flexible on how I match.

So the matching I need to do is something like this,

if Key1 is unique in both table, then match these records
if Key1||Key2 is unique in both tables, then match these.
if Key1||Key2||Key3 is unique in both tables, then match.
if Key1||Key2||Key3||Key4 is unique in both tables, then match.

The best I can think is to run 4 queries:

UPDATE Table1 Set Value = (Select Value from Table2
                where Key1 = Table1.Key1
                and    Key2 = Table1.Key2
                and    Key3 = Table1.Key3
                and    Key4 = Table1.Key4
                Group By Key1,
                Key2,
                Key3,
                Key4
                Having Count(*)=1);
UPDATE Table1 Set Value = (Select Value from Table2
                where Key1 = Table1.Key1
                and    Key2 = Table1.Key2
                and    Key3 = Table1.Key3
                Group By Key1,
                Key2,
                Key3
                Having Count(*)=1);
UPDATE Table1 Set Value = (Select Value from Table2
                where Key1 = Table1.Key1
                and    Key2 = Table1.Key2
                Group By Key1,
                Key2
                Having Count(*)=1);
UPDATE Table1 Set Value = (Select Value from Table2
                where Key1 = Table1.Key1
                Group By Key1
                Having Count(*)=1);


It doesn't check that the match is unique on the Table1 side, but I think I can 
live with that.

Am I missing an obviously better way to do it? 
A way that can easily be expanded when they come back to me and say if I looked 
at a fifth column, you'd have been able to match it

And if this is something better handled in the application code, can you show 
me some pseudo-code showing what that algorithm would look like? (Application 
will be written in Basic.)

Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[David Bicking]

 From: Ryan Johnson 
To: sqlite-users@sqlite.org 
Sent: Monday, January 28, 2013 12:54 PM
Subject: Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands 
ending in semicolons while rejecting others.
 
On 28/01/2013 12:08 PM, Larry Brasfield wrote:
> Nathan Chung wrote:
>> *Summary:
>> The SQLite3 shell accepts some dot commands ending in semicolons while
>> rejecting others without displaying proper error messages. The
>> behavior of the dot commands could be more consistent. Examples
>> include "header off;" and "header on;". The shell accepts "header
>> off;" while rejecting "header on;" without a proper error message.
>
> 7. There may be other dot commands displaying other inconsistent
>> behaviors. I have only verified the issue with "header on;" and
>> "header off;".
> 
> The code for the shell command decoder is not written to perform as you 
> expect.  It is very simple, (which is also a virtue), and >focused on 
> accepting correctly composed commands. The shell is considered to be a tool 
> for easily performing basic operations >upon a SQLite database, in the hands 
> of more software-savvy people than those who would be lost without detailed 
> and >"consistent" error reporting.  I dare say that said code, if modified to 
> meet your expectations, would be less suited to its purpose, >harder to 
> maintain and understand, and would divert effort from SQLite itself.  I say 
> this as one who has found reason to modify >that code and who is glad that 
> part of the task consumed little time.
>".header on;" -- does nothing
>".header off;" -- disables headers

>I'm with OP on this one. The error message doesn't need to be "helpful" -- in 
>fact I'm fine if it accepts semicolons, given that >most things you type in 
>the shell require them. Turning apparently-successful commands into no-ops is 
>never a good idea, though.

>My guess is that it's trivial to fix, especially if the decision is to allow 
>semicolons and other trailing garbage (though `.header foo' >would still 
>silently "succeed"). Probably a one-liner along the lines of 
>s/strcmp/strncmp/, or some such.

>$0.02
>Ryan

Actually, it looks like anything except ".header on" will turn headers off. 
That includes ".header on;" (with semicolon - it doesn't do nothing, it turns 
the headers off) or ".header off;" (with or without semincolon.) or ".header ;" 
(with a space and semicolon but no text.)

I could only get it to return error text with .header or .header; (no space 
before semicolon.)

.explain behaves the same way. "on;" is treated the same as "off" or "foo".

(At least with version 3.7.0 which is what I had handy to test with.)

David
 ___
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] unique combination of concatenated column query

[David Bicking]

assuming you have a fixed number of bin values, you can do something like this:

CREATE TABLE myTable (component REAL,bin INTEGER,prd INTEGER);
INSERT INTO myTable VALUES (2.1,1,217);
INSERT INTO myTable VALUES (6.5,4,217);
INSERT INTO myTable VALUES (7.1,3,217);
INSERT INTO myTable VALUES (7.6,5,217);
INSERT INTO myTable VALUES (7.7,5,217);
INSERT INTO myTable VALUES (1.3,2,217);
INSERT INTO myTable VALUES (1.1,1,298);
INSERT INTO myTable VALUES (3.1,1,298);
INSERT INTO myTable VALUES (6.2,2,298);
INSERT INTO myTable VALUES (7.3,5,298);
INSERT INTO myTable VALUES (8.1,3,298);
INSERT INTO myTable VALUES (8.4,4,298);
INSERT INTO myTable VALUES (1.1,5,298);
SELECT distinct m0.prd, 
m1.component ||'|'|| m2.component ||'|'|| m3.component ||'|'|| m4.component 
||'|'|| m5.component  as "combo"
FROM myTable as m0 
LEFT JOIN myTable as m1 on m1.prd = m0.prd and m1.bin=1
LEFT JOIN myTable as m2 on m2.prd = m0.prd and m2.bin=2
LEFT JOIN myTable as m3 on m3.prd = m0.prd and m3.bin=3
LEFT JOIN myTable as m4 on m4.prd = m0.prd and m4.bin=4
LEFT JOIN myTable as m5 on m5.prd = m0.prd and m5.bin=5;


217|2.1|1.3|7.1|6.5|7.6
217|2.1|1.3|7.1|6.5|7.7
298|1.1|6.2|8.1|8.4|1.1
298|1.1|6.2|8.1|8.4|7.3
298|3.1|6.2|8.1|8.4|1.1
298|3.1|6.2|8.1|8.4|7.3


David




 From: Rick Guizawa 
To: sqlite-users@sqlite.org 
Sent: Sunday, January 27, 2013 9:33 PM
Subject: [sqlite] unique combination of concatenated column query
 
Hi, All. Could you, please, help me with sql query ?

For example, if I have a table1 like the following
(fields/values separated by commas):

component, "bin", "prd"
2.1    , "1",     "217"
6.5    , "4",     "217"
7.1     ,"3",     "217"
7.6     ,"5",     "217"
7.7    ,"5",     "217"
1.3     ,"2",     "217"
1.1    ,"1",     "298"
3.1     ,"1",     "298"
6.2     ,"2",     "298"
7.3     ,"5",     "298"
8.1     ,"3",     "298"
8.4     ,"4",     "298"
1.1     ,"5",     "298"

and I want to produce a table2 below:

prd     ,    combo
217    ,    2.1|1.3|7.1|6.5|7.6
217     ,    2.1|1.3|7.1|6.5|7.7
298     ,    1.1|6.2|8.1|8.4|1.1
298     ,    1.1|6.2|8.1|8.4|7.3
298     ,    3.1|6.2|8.1|8.4|1.1
298    ,    3.1|6.2|8.1|8.4|7.3

whereas column 'combo' contains all possible combinations of components in
table1 written in ascending order of table1 'bin' values.

Thank you in advance as  your help is very much appreciated.
Cheers,
ric
___
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] Subject: Re: Simple SQLite-based spreadsheet?

[David Bicking]

I will say one of the spreadsheet like functions I have wanted, and haven't 
really seen, is the ability to copy a value in to the column in multiple rows. 
MS Access doesn't allow that, but it is trival in a spreadsheet, just highlight 
the cells, and Ctrl-D to copy the value down.. I don't recall seeing any 
general purpose database manager that can do that. hell, most don't even let 
you modify a value in the cell itself.

I'd also say that the autofilters that a spreadsheet offer are way easier for 
the non-technical to use than for them trying to craft a sql where clause to 
limit the results shown.

I also see users highlighting rows within the excel sheet. That would be 
invisible to my link to the data in the table, but is meaningful to them. But I 
don't see how that could be made generic, so I don't think it could be done for 
something that really is an sqlite table.

And this needs to be something that any user with some experience using a 
program like excel could just pick up. Not something that someone has to 
"program" for them.

David


PS: What I would love to see is that the display would be smart enough to
insert subtotal rows in the data. I know I hate looking at the totals
in a report, then having to go to another view/tool to fix the
underlying data. But that would be kind of advanced. Stilll, I guess could be 
the information to insert the subtotals could be saved in a meta-data table.




- Original Message -
From: Gilles Ganault 
To: sqlite-users@sqlite.org
Cc: 
Sent: Saturday, December 8, 2012 4:09 PM
Subject: Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

On Sat, 8 Dec 2012 20:58:17 +, Simon Slavin
 wrote:
>On the other hand, if you put in the great amount of effort to write
>a general tool and clean it up so other people can use it,
>it requires enough time that you'll want to charge for your work. 
>Hence the large number of tools out there that aren't free.

Ok, but where are the large number of tools that would do that?

Features:
- very fast, very basic spreadsheet (not based on Excel or
Libre/OpenOffice)
- saves data in SQLite
- very easy to create new tables + columns
- lets the user edit rows/columns as easily as in Excel
- data can be sorted by any column
- access to SQL a plus, but must not be required, as the app is meant
to non-techies

___
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] just a test

[David Bicking]

I don't know what gmail is doing, but this is the first of your messages that I 
have seen for a long time. I thought you had left, except I'd occasionally see 
you quoted in someone's else email. Yahoo mail was completely dropping your 
email; not in spam, just not there.


David


- Original Message -
From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Cc: 
Sent: Saturday, December 8, 2012 5:40 PM
Subject: Re: [sqlite] just a test

Clive Hayward  wrote:
> Igor's messages sometimes get marked as spam by gmail.

Now trying to send from a different email address. Please let me know if this 
still gets marked as spam. Thanks.
-- 
Igor Tandetnik

___
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] Simple SQLite-based spreadsheet?

[David Bicking]

>From: Gilles Ganault 

>On Fri, 7 Dec 2012 13:37:45 +, Simon Slavin
 wrote:
>Use the free SQLite shell tool downloadable from the SQLite site.
>
>Write your own interface in PHP using the sqlite3 interface which does exactly 
>what you want.

>Thanks but before I build my own, I wanted to check that no one had
>already built a Windows application with SQLite embedded, available
>either as free- or share-ware.

It is a bit heavy, and not really what I think you are after, but you might 
want to look at Kexi (http://www.kexi-project.org/) It attempts to be an open 
source replacement for Access. It has the simpler bits of Access, but doesn't 
have VBA, which is a deal breaker for me.


OpenOffice / LibreOffice has a built in database tool, including a spreadsheet 
like interface. I believe it can be set up to use an sqlite database for its 
back end. Frankly I could never get it to work for me.

I asked something similar years ago, and I believe D.R. Hipps pointed me at a 
TCL spreadsheet widget that connected directly to Sqlite, but I don't recall 
its exact name. You would have to install TCL to use it.


Hope that helps.

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting from another table...

[David Bicking]

Have you tried to replace the "Insert into values(" with just "Select (" to 
make sure the values are what you expect them to be. That might also point out 
if any of them are null.

David




 From: Sam Carleton 
To: General Discussion of SQLite Database  
Sent: Thursday, July 5, 2012 11:03 PM
Subject: [sqlite] Inserting from another table...
 
I am working on converting my system table from one form to another.  The
old form was one row per value with a category/key/value (DBLookup) , the
new form is a separate column for each value (PP_VIEWER_SETTINGS).  I am
trying to create an insert statement to run when the new table is created,
but when I run it, it does not work nor do I get any errors in SQLite
manager:

/* The OLD one */
CREATE TABLE DBLookup (
    Category VARCHAR(32) NOT NULL,
    ItemName VARCHAR(128) NOT NULL,
    ItemValue VARCHAR(3000) NOT NULL,
    PRIMARY KEY(Category, ItemName))

/* The NEW one */
CREATE TABLE PP_VIEWER_SETTINGS
(
  VIEWER_SETTINGS_ID                      INTEGER PRIMARY KEY
AUTOINCREMENT,
  COMPANY_NAME                            VARCHAR(   260) NOT NULL,
  DSPNEXTPREVIOUS                        SMALLINT         NOT NULL,
  ENABLE_CARTS                           SMALLINT         NOT NULL,
  ENABLE_DEBUGINFO                       SMALLINT         NOT NULL,
  ENABLE_FAVORITES                       SMALLINT         NOT NULL,
  ENABLE_RIGHTCLICK                      SMALLINT         NOT NULL,
  ENABLE_SLIDESHOW                       SMALLINT         NOT NULL,
  ENABLE_TIMEOUT                         SMALLINT         NOT NULL,
  EXIT_KVS                               SMALLINT         NOT NULL,
  EXIT_PASSWORD                           VARCHAR(    20) NOT NULL,
  IS_CART_FAVORITES                      SMALLINT         NOT NULL,
  IS_LOGIN_REQUIRED                      SMALLINT         NOT NULL,
  IMAGE_SIZE                              INTEGER         NOT NULL,
  PHONE_NUM_FORMAT                        VARCHAR(    20) NOT NULL,
  THEME_ID                                INTEGER         NOT NULL,
  THUMBNAIL_SIZE                         SMALLINT         NOT NULL,
  TICKER_MSG                              VARCHAR(   260) NOT NULL,
  TO_AFTER                               SMALLINT         NOT NULL,
  TO_STARTS                              SMALLINT         NOT NULL,
  TO_TRANSITION_SECS                     SMALLINT         NOT NULL,
  SS_COUNT                               SMALLINT         NOT NULL,
  SS_DEFAULT_IS_IN_SLIDESHOW             SMALLINT         NOT NULL,
  SS_DISPLAY_SECONDS             DOUBLE PRECISION         NOT NULL,
  SS_ZOOM_FACTOR                 DOUBLE PRECISION         NOT NULL,
  USERLAN                                 VARCHAR(   260) NOT NULL
);

/* The insert script */

insert into PP_VIEWER_SETTINGS
    ( COMPANY_NAME, DSPNEXTPREVIOUS, ENABLE_CARTS, ENABLE_DEBUGINFO,
ENABLE_FAVORITES, ENABLE_RIGHTCLICK, ENABLE_SLIDESHOW,
      ENABLE_TIMEOUT, EXIT_KVS, EXIT_PASSWORD, IS_CART_FAVORITES,
IS_LOGIN_REQUIRED, IMAGE_SIZE, PHONE_NUM_FORMAT, THEME_ID,
      THUMBNAIL_SIZE, TICKER_MSG, TO_AFTER, TO_STARTS, TO_TRANSITION_SECS,
SS_COUNT, SS_DEFAULT_IS_IN_SLIDESHOW,
      SS_DISPLAY_SECONDS, SS_ZOOM_FACTOR, USERLAN)
      values (
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "Company"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "DspNextPrevious"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableCarts"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableDebugInfo"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableFavorites"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableRightClick"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "EnableSlideShow"),
        1,
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ExitKvs"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ExitPassword"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "IsCartFavorites"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "IsLoginRequired"),
        900,
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "PhoneNumberFormat"),
        0,
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "ThumbnailSize"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TickerMsg"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TimeoutAfter"),
        (select ItemValue from dblookup where Category = "KvsSettings" and
ItemName = "TimeoutStarts"),
        (select ItemValue from dbloo

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[David Bicking]

If Uniform has a given EmployeeName twice, you will get the Employee.Name twice 
in this query. Thus it would be a different result than if you did not join 
with Uniform.

David




 From: Charles Samuels 
To: General Discussion of SQLite Database  
Sent: Tuesday, May 29, 2012 8:21 PM
Subject: [sqlite] Why can't sqlite disregard unused outer joins?
 

Suppose you have a query like this:

select Employee.name from Employees left join Uniform on 
    (EmployeeSize.name=Uniform.employeeName)

This query's result should be identical weather or not we have that join; it's 
an outer join, not an inner join, afterall. However, explain query plan (and 
my time measurements) seem to indicate that the the query with the join is far 
slower/more complex.

Is it hypothetically possible that the optimizer could avoid the join? Is 
there a way to get sqlite to do so? Is this a planned feature?

Why do I ask? I have a big view that joins a whole bunch of a tables 
(including with a virtual table), but most queries use only a subset of those 
queries actual data and I'd rather not get hit by that performance penalty.

Since my queries come from the user, I don't want to have them do the joins on 
their own, it'd be tedious.

Charles
___
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] rearranging 2 fields into 1 based on an order indicator in 2 other fields

[David Bicking]

Create Table table2 (Field3, FieldC);
insert into table2 (Field3, FieldC) Select Field1x, FieldAx from Table1;
insert into table2 (Field3, FieldC) Select Field2y, FieldBy from Table1;

this will put your data in to the new table. I suspect the rowids won't match 
what you want, but you can always order by FieldC to get the order you want.

If the rowids are critical, then do the inserts in to a temp table, then insert 
in to the final table using an order by FieldC.


David



 From: Gert Van Assche 
To: sqlite-users@sqlite.org 
Sent: Tuesday, May 29, 2012 4:44 PM
Subject: [sqlite] rearranging 2 fields into 1 based on an order indicator in 2 
other fields
 
All,

I have a strange table like this

rowid; Field1x; Field2y; FieldAx; FieldBy
1;     A;       a;       0;       1
2;     B;       b;       4;       2

and I would need this:

rowid; Field3; FieldC
1;     A;      0
2;     a;      1
3;     b;      2
4;     B;      4

So:
- the contents of Field1x and Field2y should go into Field3
- the order in the new table/view depends on values FieldAx and FieldBy
that should go into FieldC
- the order indicator in the first table of Field1x is in FieldAx;  the
order indicator of  Field2y is in FieldBy

I have absolutely no clue how to do this. I hope this is possible.

Thanks for your brains on this.


Gert
___
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] don't understand what "query" returns...

[David Bicking]

I don't know python, but because you have count(*) and no group by, it 
will only return one row, with the total rows that matched your where 
clause. The Item1, Item2, Item3 are arbitrary values that were in the 
two rows of your data.


You can either remove the count(*) and get both rows, or as someone else 
said, add a group by and get a row for each distinct values of Items 1-3.


David

On 05/12/2012 05:24 AM, philherna wrote:

Hi,


I am using sqlite commands in my python script to output data from a sqlite
database. My problem is however a Sqlite coding one.

I can open, and select simple elements from the database easily but I have
trouble for one specific issue.
For example, to select the content of the rows Item1 , Item2 and Item3 for
which Item55 is equal to 888, I type:

query = "select Item1,Item2,Item3, count(*) from %s where Item55 in (%s)" %
(Database,888)
c.execute(query)
results = c.fetchone()

If I type in Python:
   

print results# I get a the unique object which satisfies Item55=888.
   

(0.2, 0.5, 0.9, 1)
in which the 3 first elements are the values for Item1 , Item2 and Item3,
and the last element tells me that I have done "oneobject matching the
query", i.e. the selected columns for which Item55=888.
However, i am not quite sure that I really well understand why I am returned
this last element...

And for instance,
   

print results[1] # gives
   

0.5 # as expected

Now, if I want to select to more objects contained in an array, I can do:
Array = [888,999]
query = "select Item1,Item2,Item3, count(*) from %s where Item55 in (%s)" %
(Database,Array)
c.execute(query)
results = c.fetchall()

The fetchall command is supposed to return all the rows, as far as I
understand Sqlite... My problem is that i don't understand the query results
in this case. Indeed,
   

print results # gives
   

(0.2, 0.5, 0.9, 2)
in which the last element tells me (again, as far as I understand ...) that
I have submitted "2 queries", i.e. selected rows for which Item55=888 and
999. I would have expected a result to be a matrix with 2 rows, not a 1D
array...
   

print results[1][0] # gives
   

IndexError: list index out of range


Does anyone has an idea of what I am doing wrong here?
Thanks in advance !
   


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is SQLite the right tool to analyze a 44GB file

[David Bicking]

Did you remember to specify the file name when you started sqlite3. If you 
don't give the file name, everything is done to the memory database and is 
discarded when you exit.

David




 From: peter korinis 
To: sqlite-users@sqlite.org 
Sent: Monday, May 7, 2012 5:01 PM
Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file
 
I can't find a table I just created and imported data.

With .databases I can see 'main'  but with no location on disk and with .
tables I see the table.

Instructions says they are save . but where. I can't find them with windows
search?



pk



___
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] is SQLite the right tool to analyze a 44GB file

[David Bicking]

>From the docs:

.separator STRING  Change separator used by output mode and .import
replace string with a comma, without quotes, then do your import. It should 
work.

David



 From: peter korinis 
To: sqlite-users@sqlite.org 
Sent: Monday, May 7, 2012 1:34 PM
Subject: Re: [sqlite] is SQLite the right tool to analyze a 44GB file
 
On 7 May 2012, at 4:41pm, peter korinis  wrote:



> My input file is a comma-delimited text file

> When I run .import I get the following "Error: FILE line 1: expected 46

> columns of data but found 1"

> It seems .import is not recognizing comma delimiter.







"The default separator is a pipe symbol ("|")."



Simon.



So, if I use gawk to change my comma separated file to | (pipe) delimiter  .
will it work? 

Do I have to use ".mode csv" as Jonas just advised?



Thanks,

peter

___
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] How to access values of a columns with specific row id.

[David Bicking]

On 01/31/2012 04:05 AM, bhaskarReddy wrote:


Hi Friends,


Can any one tell me how to access values of a table column
with its particular row id.

  I tried with "select * from ontTable where rowid=2;"

  I am getting the output as 1|2|3|XYZ234|4|ABCD123|5.

 Here i want the values of column should store in separate
variables.


Can any one tell me how to access column by column.


Regards,
Bhaskar Reddy.


How are you calling sqlite?  What operating system are you using, what 
programming language?


I'm guessing you are getting that output from the command line program. 
While you can do useful things using cmd.exe on windows or bash on 
unix-like systems, it is difficult and not really recommended.


To really give you a meaningful answer, we really need to know what you 
are trying to accomplish and what limits you have in what you can do.


David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Calculating MSO

[David Bicking]

Sorry I messed up the parens. the actual formula is more complicated, so I 
decided to hastily take the outer most layers away messing up the formula in 
the process.
MSO = (Per + (AR-sum(Sales))/Sales)

And yes, result should be 2.7 not 2.3. Stupid typo on my part. 


Thankfully I just got out of a meeting where I was told "it can't be done 
efficiently" is an acceptable answer at this time.I just need to make sure I'm 
not missing some obvious way to get the answer out of the data.
David




 From: Igor Tandetnik 
To: sqlite-users@sqlite.org 
Sent: Friday, December 16, 2011 4:05 PM
Subject: Re: [sqlite] Calculating MSO
 
David Bicking  wrote:
> The calculation is that for each customer:
> MSO = (Per + (AR-sum(Sales)/Sales))
> 
> Result
> Cust MSO
> 01 2.3 = (3+(100-120)/60))

You have more closing parentheses here than opening ones. If we ignore the 
rightmot closing paren, the grouping of actual numbers doesn't match the 
grouping of variables in the formula. What is being divided by Sales: 
sum(Sales) or (AR - sum(Sales)) ?

Further, (3+(100-120)/60) == 2.7, not 2.3. Your example doesn't add up.

> Hopefully I described that in a meaningful way. Is it possible to do that 
> efficiently or at all using SQL?

I'd bet it is possible (I might take a stab at it once you clarify the problem 
statement), but I doubt it'd be efficient.
-- 
Igor Tandetnik

___
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

[sqlite] Calculating MSO

[David Bicking]

I need to calculate Months Sales Outstanding.

CREATE TABLE  AR
    Cust    Text
    AR    Double

CREATE TABLE Sales
    Cust    Text
    Per    Integer -- runs 1, 2, 3, etc
    Sales    Double 


The calculation is that for each customer: 
MSO = (Per + (AR-sum(Sales)/Sales))    
    Where Per and Sales are for the lowest 
    Period where Sum(Sales) is greater than the AR 

So 

AR
Cust        AR
01    100
02     50

Sales
Cust        Per    Sales
01    1    60
01    3    60
01    4    60
02    1    15
02    2    15
02    3    15
02    4    15

Result
Cust        MSO
01    2.3   = (3+(100-120)/60))
02    3.3   = (4+(50-60)/15))

    Hopefully I described that in a meaningful way. Is it possible to do that 
efficiently or at all using SQL?

Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow INDEX

[David Bicking]

On 11/02/2011 12:31 PM, Fabian wrote:

2011/11/2 Mr. Puneet Kishor



Others will have better answers, but methinks that when you reboot the
computer, the operating system's caches are flushed out, which slows the
operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.)
and notice if the speed increases again to what you expect.



The reason I reboot the PC for the test, is because I want to have the
caches flushed out, and I fully expect it to make things slower, but not by
the degree (factor 300) i'm experiencing.


In the past I've seen recommendations to open and read the file, perhaps 
by making a copy of the file, then try your inserts. If the time does 
goes back down, then you know it is file cache issues.


David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow JOIN on ROWID

[David Bicking]

On 10/14/2011 06:39 AM, Fabian wrote:



Exactly.



I still don't have optimal performance in the query (although it's much
better now), and it seems to be related to ORDER BY.

When I execute:

SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50

It's very fast, but it's get much slower (10 times) when I add an ORDER BY
clause, like rowid ASC or rowid DESC.

I'm trying to understand why this is. It seems like SQLite is actually
performing an actual sort behind the scenes, while I expected it to just
iterate in reverse order (because rowid is always incremental), which should
give comparable performance as the first query?


What does EXPLAIN QUERY PLAN say? I believe Sqlite can only use one 
index per table. If you have an index on the data field, it is probably 
using that index to quickly get the data=10 condition, in which case it 
can't use the rowid index, and thus has to sort behind the scenes as you 
say.


If it is using an index to find the data, I believe you can do +data = 
10, which will invalidate the index use on data. (Hope I remember that 
right..)


David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[David Bicking]

Is an untrusted end user writing queries?

Are there clients other than proc_host and proc_client1?  If there is a 
proc_cleint2, would it also see all rows except rowid = 1?

If there is only proc_clent1, then create a permanent view of:
  Select * from mytable where mytable.rowid<>1
But I deeply suspect that is not the complete story.

David

On 07/16/2011 12:18 PM, san long wrote:
> Thanks for replies.
> I want to make things clear. there are some rules in my system, such
> as : process whose name is  proc_host can see all the records, and
> process whose name is proc_client1 can see all the records except
> rowid 1.
> It looks like temporary view and temporary table is good solutions,
> which one is better and which one could be more efficient?
>
>
>
> 2011/7/16, David Bicking:
>> I don't know I have much to offer here as I still really don't
>> understand what you are trying to accomplish.
>>
>> But I looked and it appears that sqlite supports TEMPORARY VIEW, which,
>> I believe, is only visible to the process that created it. And it will
>> automatically go away when that process ends.
>>
>> Sqlite doesn't allow you to insert, update or delete records in a view,
>> but you can create INSTEAD OF INSERT/UPDATE/DELETE triggers on the view,
>> which you then have update the underlying real table. If you go with
>> temporary views, you'll have to recreate the triggers each time you
>> recreate the view.
>>
>> In some of your emails you say that hidden records are to be hidden from
>> all processes, yet you seem to not like the idea of deleting them. I am
>> assuming that our statement is really, "some records are to be hidden
>> from all processes AT THIS TIME." In the future, a process may be
>> allowed to see the visible record, and thus you can't just delete it. Am
>> I right in this?
>>
>> Who is creating the queries that you want to hide records from? If you,
>> then just don't include those records in the WHERE clause. If the end
>> user is typing in adhoc queries, than even if you go with using views,
>> you are going to having to make sure they don't figure out the
>> underlying real table name. I've never used it, but I gather that is
>> what the authorizer callback is for.
>>
>> How do you determine which records should be visible to which process?
>> Are you looking at the user information captured in the process? The
>> actual pid, as I understand it, changes each time a program is run, so I
>> don't see you doing a lookup on that number.
>>
>> At any rate, I suspect you will want to create a temporary table in
>> which you will either populate the real table rows that you want to
>> allow or disallow for the given process. You could then create the
>> temporary view as a join between the real table and the temp table. You
>> also need to create the associated triggers for the user in that process
>> to use.
>>
>> But in the end, I am just guessing what you are really trying to do, so
>> I may be off by a mile.
>>
>> David
>>
>>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hidding records from the application

[David Bicking]

I don't know I have much to offer here as I still really don't 
understand what you are trying to accomplish.

But I looked and it appears that sqlite supports TEMPORARY VIEW, which, 
I believe, is only visible to the process that created it. And it will 
automatically go away when that process ends.

Sqlite doesn't allow you to insert, update or delete records in a view, 
but you can create INSTEAD OF INSERT/UPDATE/DELETE triggers on the view, 
which you then have update the underlying real table. If you go with 
temporary views, you'll have to recreate the triggers each time you 
recreate the view.

In some of your emails you say that hidden records are to be hidden from 
all processes, yet you seem to not like the idea of deleting them. I am 
assuming that our statement is really, "some records are to be hidden 
from all processes AT THIS TIME." In the future, a process may be 
allowed to see the visible record, and thus you can't just delete it. Am 
I right in this?

Who is creating the queries that you want to hide records from? If you, 
then just don't include those records in the WHERE clause. If the end 
user is typing in adhoc queries, than even if you go with using views, 
you are going to having to make sure they don't figure out the 
underlying real table name. I've never used it, but I gather that is 
what the authorizer callback is for.

How do you determine which records should be visible to which process? 
Are you looking at the user information captured in the process? The 
actual pid, as I understand it, changes each time a program is run, so I 
don't see you doing a lookup on that number.

At any rate, I suspect you will want to create a temporary table in 
which you will either populate the real table rows that you want to 
allow or disallow for the given process. You could then create the 
temporary view as a join between the real table and the temp table. You 
also need to create the associated triggers for the user in that process 
to use.

But in the end, I am just guessing what you are really trying to do, so 
I may be off by a mile.

David


On 07/16/2011 12:01 AM, san long wrote:
> sqlite3 support a trigger on SELECT ? View is a good solution, but I
> want to let different process see different records, like:
> pid A sees rowid 1,2
> pid B sees rowid 1,3
>
>
> 2011/7/16, san long:
>> haha, if I CREATE VIEW in process A and DROP VIEW when A dies. Process
>> B could see this VIEW or not?
>> A and B run at the same thime.
>>
>> 2011/7/16, Simon Slavin:
>>>
>>> On 16 Jul 2011, at 4:23am, san long wrote:
>>>
 Thanks for advice, delete or update the record in a view could affect
 the
 true table in sqlite now?
>>>
>>> No.  Either use the VIEW for SELECT and the TABLE for changes, or
>>> implement
>>> triggers so attempts to change your VIEW actually change the underlying
>>> TABLE.
>>>
 And, if I create a view dynamically using sqlite3_exec, is it visible to
 other process who opens the same database?
>>>
>>> Yes.  A VIEW is a stored SELECT statement (or it has a similar effect).
>>>
>>>
>>> On 16 Jul 2011, at 4:25am, san long wrote:
>>>
 right, but now I just want to hide these records to all processes.
>>>
>>> Then DELETE them !
>>>
>>> Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[David Bicking]

Part of the problem here is that you are misusing the term BETWEEN. In SQL txt 
BETWEEN 1990 AND 1991 means those rows where the value of txt is >= 1990 AND <= 
1991. It has nothing to do with the order of the rows in the table, it is 
purely a comparison of the value of txt in a particular row and the minimum and 
maximum value given in the BETWEEN statement.

Without testing it, I guess your query needs to be:

SELECT d from T_d WHERE 
xpos BETWEEN (SELECT pos FROM T_x WHERE txt = '1990')
 AND (SELECT pos FROM T_x WHERE txt = '1991')
AND
ypos BETWEEN (SELECT pos FROM T_y WHERE txt = 'cogs')
 AND (SELECT pos FROM T_y WHERE txt = 'sg&a expenses');

David

--- On Fri, 7/1/11, e-mail mgbg25171  wrote:

> From: e-mail mgbg25171 
> Subject: Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> To: "General Discussion of SQLite Database" 
> Date: Friday, July 1, 2011, 1:38 PM
> Just to clarify further "pos BETWEEN
> txt = 1990 and 1991" as its stands
> looks (to my naive eye) like its going to return 1 3 2 and
> if you ORDER BY
> pos then it's going to return 1 2 3
> Neither of which is what I want.
> By contrast if you were to "sort" the table FIRST then "pos
> BETWEEN txt =
> 1990 and 1991" would return 1 2 and this is what I want.
> 
> 
> 
> On 1 July 2011 18:24, e-mail mgbg25171 
> wrote:
> 
> > Here's an example of what I'm trying to do with my
> query
> > t_x
> > rowid=1,pos=1, txt=1990
> > rowid=2,pos=3, txt=1992
> > rowid=3,pos=2, txt=1991
> >
> > t_y
> > rowid=1,pos=3,txt="sg&a expenses"
> > rowid=2,pos=2,txt="cogs"
> > rowid=3,pos=1,txt='revenue'
> >
> > t_d
> > rowid=1,xpos=1,ypos=1,d=$1
> > rowid=2,xpos=1,ypos=2,d=$2
> > rowid=3,xpos=1,ypos=3,d=$3
> > rowid=4,xpos=3,ypos=1,d=$7
> > rowid=5,xpos=3,ypos=2,d=$8
> > rowid=6,xpos=3,ypos=3,d=$9
> > rowid=7,xpos=2,ypos=1,d=$4
> > rowid=8,xpos=2,ypos=2,d=$5
> > rowid=9,xpos=2,ypos=3,d=$6
> >
> >
> > So in the GUI you'd see t_x as 1990...1991,,,1992 cos
> that's pos order
> >
> > you'd see t_y as
> > revenue
> > cogs
> > sg&a expenses
> > cos that's pos order
> >
> > and you'd see t_d as
> >         
>    1990  1991   1992
> > revenue    1      2 
>       3
> > cogs        4     
> 5        6
> > sg&a        7 
>    8        9
> >
> > ie the order in which rows are added i.e. rowid order
> is not the order
> > of the row's position in the GUI (pos order is the GUI
> order)
> >
> > The query is to return those data cells encapsulated
> by the margin text
> > values so...
> > Select all cells BETWEEN 1990 and 1991 and cogs and
> sg&a.
> > The answer would be 4 5 7 8.
> >
> > Does this help to visualise what I'm trying to do?
> >
> >
> >
> >
> > On 1 July 2011 18:14, Pavel Ivanov 
> wrote:
> >
> >> > Its not a very good example because the two
> are adjacent and 'x1' and
> >> 'x2'
> >> > sound like they're adjacent too.
> >>
> >> They are not adjacent - 'x1123456' and a lot of
> other strings starting
> >> with 'x1' are between them.
> >>
> >> > I'm only interested in the results of BETWEEN
> when you're looking at x1
> >> and
> >> > x2 from the pos order perspective
> >>
> >> Then David's query is a way to go.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
> >> 
> wrote:
> >> > Pavel, David
> >> > Thanks for bearing with me...
> >> >> "txt BETWEEN 'x1' AND 'x2'" you mean
> those rows between the row where
> >> txt
> >> > = 'x1' and the row where txt = 'x2'
> >> > Yes that's right.
> >> > Its not a very good example because the two
> are adjacent and 'x1' and
> >> 'x2'
> >> > sound like they're adjacent too.
> >> > I'm only interested in the results of BETWEEN
> when you're looking at x1
> >> and
> >> > x2 from the pos order perspective
> >> > ie doing the BETWEEN query on 'x1' and 'x2'
> would be useless from the
> >> rowid
> >> > perspective/order.
> >> > I'll write a better description of what I'm
> trying to do and come back.
> >> >
> >> > On 1 July 2011 17:48, Pavel Ivanov 
> wrote:
> >> >
> >> >> > I'll certainly try
> >> >> >>SELECT pos FROM t_x WHERE txt
> BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >> >> > but I need x1 and x2 to be ordered
> before BETWEEN sees them rather
> >> than
> >> >> the
> >> >> > results just sorted by pos.
> >> >>
> >> >> Maybe I've missed something in this
> conversation? Please clarify how
> >> >> "results sorted by pos" will be different
> from "x1 and x2 to be
> >> >> ordered before BETWEEN sees". And tell us
> more clearly what results
> >> >> you want to see from your query. We
> certainly see that you want to get
> >> >> value of pos from all rows where value of
> txt lies between 'x1' and
> >> >> 'x2'. Now do you want those results to be
> order by value of pos (add
> >> >> ORDER BY pos), or by value of txt (add
> ORDER BY txt), or you want them
> >> >> in a random order (do not add ORDER BY at
> all)? If you believe that
> >> >> result of a query differs depending on
> what order SQLite processes
> >> >> rows in then you are wrong.
> >> >>
> >> >>
> >> >> Pavel
> >

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[David Bicking]

I'm not entirely sure what your data looks like, but I am thinking that when 
you say "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where 
txt = 'x1' and the row where txt = 'x2'

If that is the case, maybe this will give you what you want:

SELECT POS FROM T_x WHERE POS BETWEEN 
  (SELECT POS FROM T_x WHERE txt = 'x1')
AND
  (SELECT POS FROM T_x WHERE txt = 'x2');

Hopefully I have guessed your need somewhat correctly,
David


--- On Fri, 7/1/11, e-mail mgbg25171  wrote:

> From: e-mail mgbg25171 
> Subject: Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> To: "General Discussion of SQLite Database" 
> Date: Friday, July 1, 2011, 12:02 PM
> Thx for your suggestion...
> Yes "BY ORDER BY pos" has to be in there somewhere.
> It strikes me that
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> needs to be operating on the results returned by
> SELECT * FROM t_x BY ORDER BY pos
> ie another level of query is required but I'm not sure of
> how you insert it.
> I'll have a play.
> 
> 
> 
> On 1 July 2011 16:12, Pavel Ivanov 
> wrote:
> 
> > > What I want to do is...make sure that when I say
> BETWEEN I really mean eg
> > > BETWEEN x1 and x2 when you look at the table as
> if it's ordered by pos
> > and
> > > not rowid.
> >
> > So, can you add "ORDER BY pos" to your queries?
> >
> >
> > Pavel
> >
> >
> > On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
> > 
> wrote:
> > > Thank you all for your responses.
> > > I had to go out after posting and have just come
> back.
> > > My concern is with...
> > > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND
> 'x2'
> > > and
> > > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND
> 'y2'.
> > >
> > > t_x and t_y are dimension tables.
> > > that hold the x and y margins of a spreadsheet.
> > > The margins will have an implied order shown by
> pos
> > > which will differ from the order in which rows
> are added (represented by
> > > rowid).
> > >
> > > What I want to do is...make sure that when I say
> BETWEEN I really mean eg
> > > BETWEEN x1 and x2 when you look at the table as
> if it's ordered by pos
> > and
> > > not rowid. I hope that helps explain why pos
> exists and is not rowid i.e.
> > I
> > > want to be able to "insert" and "delete" records
> "!in between" the
> > existing
> > > ones or at least make it look like that even if
> the records are
> > physically
> > > appended to the tables.
> > > Hope this clarifies things and look forward to
> your thoughts.
> > >
> > >
> > > On 1 July 2011 15:30, Pavel Ivanov 
> wrote:
> > >
> > >> >> Putting the 'ORDER BY' clause in
> view won't work?
> > >> >
> > >> > It will work just fine, in that the
> results you see will appear in the
> > >> ORDER you asked for.
> > >>
> > >> I believe that's not always true and is not
> required by SQL standard.
> > >> Most probably 'select * from view_name' will
> return rows in the order
> > >> written in the view. But 'select * from
> view_name where some_column =
> > >> some_value' can already return rows in
> completely different order. And
> > >> 'select * from table_name, view_name where
> some_condition' will almost
> > >> certainly ignore any ORDER BY in the view.
> > >>
> > >> So ORDER BY in the view doesn't guarantee you
> anything.
> > >>
> > >>
> > >> Pavel
> > >>
> > >>
> > >> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin
> 
> > >> wrote:
> > >> >
> > >> > On 1 Jul 2011, at 3:07pm, Alessandro
> Marzocchi wrote:
> > >> >
> > >> >> 2011/7/1 Simon Slavin 
> > >> >>
> > >> >>> On 1 Jul 2011, at 11:20am,
> Alessandro Marzocchi wrote:
> > >> >>>
> > >>  Isn't it possible to use a
> view for that?
> > >> >>>
> > >> >>> You can use a VIEW if you want,
> but VIEWs don't sort the table
> > either.
> > >>  A
> > >> >>> VIEW is just a way of saving a
> SELECT query.  When you consult the
> > VIEW
> > >> >>> SQLite executes the SELECT.
> > >> >>
> > >> >> Putting the 'ORDER BY' clause in
> view won't work?
> > >> >
> > >> > It will work just fine, in that the
> results you see will appear in the
> > >> ORDER you asked for.
> > >> >
> > >> > However, it has no influence on how data
> is stored.  In fact no table
> > >> data is stored for a VIEW at all.  The
> thing stored is the parameters
> > given
> > >> when you created the VIEW.  Every time
> you refer to a VIEW in a SQL
> > >> statement SQL goes back and looks at the VIEW
> specification again.
> > >> >
> > >> > Simon.
> > >> >
> ___
> > >> > 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
> > >>
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[David Bicking]

Since none of the statements is a SELECT, as far as I know the callback 
would never be called. You can pass a zero as the callback address.

get_table will also handle all the statements in one pass, but will 
return an empty able, so you might as well use exec.

David

On 06/21/2011 07:59 AM, e-mail mgbg25171 wrote:
> Thank you for the clarification re...
> sqlite3_prepareXXX() only processing 1 statement at a time as opposed to
> sqlite3_exec() which...
> can handle "combined multi statements" in one shot.
> I was looking at prepare/step/finalise as a means of avoiding the callback
> inherent in sqlite3_exec().
> In the example I saw...the "combined multi statements" string was processed
> by SQLite3_Get_Table which...
> I assume can also handle "combined multi statements"
> Thank you both for your assistance.
> As you can probably gather...this is very new to me.
>
> On 21 June 2011 12:48, David Bicking  wrote:
>
>> On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote:
>>> The commented out lines work.
>>> I'm wondering...
>>> a) is it possible to do what's not commented out
>>> b) what's the syntax re the "sql =..." and "sql +=..." lines
>>> Any help much appreciated!
>>>   >
>>> sql = "BEGIN";   //you need to add newline here
>>> sql += "create table episodes (id integer primary key, season int, name
>>> text)";
>>> sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
>>> sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
>>> sql += "COMMIT";
>>> rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ),&stmt,&tail);
>>> rc = sqlite3_step(stmt);
>>
>> You will need to add semicolons within the quotes between each statement
>> as someone has already pointed out.
>>
>> Secondly, prepare only prepares one statement, so you would have to loop
>> through the statements. My C is rusty, but I think it is something like:
>>
>> tail = sql.c_str();
>> while (tail)
>> {
>>rc = sqlite3_prepare(db, tail, strlen(tail),&stmt,&tail);
>>rc = sqlite3_step(stmt);
>>rc = sqlite3_finalize(stmt);
>> }
>>
>>
>> Alternatively, you can run the combined multi statements through
>> sqlite3_exec() in one shot.
>>
>> David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

[David Bicking]

On 06/21/2011 07:22 AM, e-mail mgbg25171 wrote:
> The commented out lines work.
> I'm wondering...
> a) is it possible to do what's not commented out
> b) what's the syntax re the "sql =..." and "sql +=..." lines
> Any help much appreciated!
> >
> sql = "BEGIN";   //you need to add newline here
> sql += "create table episodes (id integer primary key, season int, name
> text)";
> sql += "insert into episodes(id, season, name) Values(1,2,'bill')";
> sql += "insert into episodes(id, season, name) Values(2,3,'bob')";
> sql += "COMMIT";
> rc = sqlite3_prepare(db, sql.c_str(), strlen( sql.c_str() ),&stmt,&tail);
> rc = sqlite3_step(stmt);

You will need to add semicolons within the quotes between each statement 
as someone has already pointed out.

Secondly, prepare only prepares one statement, so you would have to loop 
through the statements. My C is rusty, but I think it is something like:

tail = sql.c_str();
while (tail)
{
   rc = sqlite3_prepare(db, tail, strlen(tail), &stmt, &tail);
   rc = sqlite3_step(stmt);
   rc = sqlite3_finalize(stmt);
}


Alternatively, you can run the combined multi statements through 
sqlite3_exec() in one shot.

David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query to find mixed values

[David Bicking]

I have two tables:

CREATE TABLE INV
( REQ int,
  INV char,
  AMT float );

CREATE TABLE REP
( REQ int,
  ACCT char
  AMT float );

I need to flag any combinations where for a given REQ value, one table has a 
list of numbers of mixed signs, and the other table has one and only one value.

So
REQ|INV|AMT
1|i1|1
1|i2|2
2|i3|3
2|i4|-6
3|i5|5
3|i6|-4

REQ|ACCT|AMT
1|a1|3
2|a2|-2
2|a3|-1
3|a4|1

REQ1 is okay because the values are the same sign.
REQ2 is okay because there are more than one record in each table
REQ3 is a problem because you have mixed signs in INV and only one record in REP

How would I craft a query to return REQ3?

At this point, I haven't a clue, so any help would be appreciated.

Thanks,
David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

[David Bicking]

--- On Thu, 2/10/11, Puneet Kishor  wrote:
> Date: Thursday, February 10, 2011, 10:35 AM
> 
> On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard
> wrote: 
> > Hi,
> > 
> > I'm sorry Pavel, I think you've got me wrong.
> > 
> > > It's not "buggy". Name of the column in result
> set is not defined
> > > unless you use "as".
> > 
> Pavel is not wrong. SQLite is not buggy. Your expectation
> of what it should do is at fault here.
> > ..
> 
> 
> I used to think as you too, but then learned otherwise. The
> developers simply didn't add the code to dependably
> determine what the column should be called, because,
> surprisingly (or not surprisingly, if you think about it a
> bit), that code would be very complex to cover all the
> possible cases. The simplest solution is to depend upon AS
> aliasing, else, do something undependable (to us).
> 
> Puneet. 
> 

Not only is it complex, but as I recall, back when the developers did try to be 
consistent in how column names were presented, no matter how they coded it, 
someone would pipe up with an opinion that it should be done another way. There 
were pragmas to try to get the column names to match different expectations.

In the end, they decided to just call it undefined and let the user use AS to 
get what they wanted. I think that was the right decision.

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[David Bicking]

I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you 
would fail to insert proper pairs. Or am I missing something?  (At least 
I assume that the integers are not limited to just 1 2 or 3 as in the 
examples.

David

On 02/09/2011 05:58 PM, Samuel Adam wrote:
> On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)
>   wrote:
>
>
>> I have a need to create a unique bi-directional relationship.
>>
>> You can think of it as pairings of people who eat dinner together.
>>  
> Two questions come to mind:
>
>   (a) Do you trust app-level code to maintain data integrity, or do you
> need SQLite to do this?
>
>   (b) How much relational rigor do you need?  Will the values be used for
> some kind of relational algebra, or is SQLite simply serving as an ACID
> reliability layer?
>
> Since you’ve been considering the bit-math tricks suggested by Mr.
> Wilcoxson, the answers to these questions may let you consider some XOR
> cleverness.  Unfortunately, I halfway wrote this up before I realized that
> SQLite lacks a ^ operator[1]; so you will need a trivial SQL user function
> and/or app-land code.  Still, with the following, you can store any pairs
> of 63-bit integers>= 0.  In pure SQL:
>
>   CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
>   -- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
>   INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
>   -- Faster on the app level; you understand.
>   SELECT "x", xor("k", "x") AS "y" FROM "";
>
> (Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.  N.b.,
> I *think* the above binding scenario will work but have not tested it.)
>
> [1] 2009·12·15 thread with reference to ^ patch by Will Clark:
> Message-ID:
> 
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html
>
> Key points:
>
>   * Pair uniqueness is enforced for free.  At least, I think it’s really
> for free because SQLite always requires a unique rowid.  Somebody please
> correct me if there is any penalty for user-selected rowids, which would
> make the performance impact nonzero.
>
>   * Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y will
> be a pain, though.
>
>   * No extra indices are required.
>
>   * I don’t see a reasonable way to stop arbitrary data from being stuffed
> in from within SQLite, even with a user function; for although :y is being
> bound on INSERT, a CHECK constraint has no way to touch it.  But see below
> for a modified table with a different set of tradeoffs.
>
>   * Since two small integers XORed will be another small integer, you do
> not suffer the loss of variable-length integer storage as spoken of by
> Messrs. Vlasov and Tandetnik.
>
>   * XOR is *fast*.  And the number of integers is kept to a bare minimum
> (for keeping up to 63 bits for each), cutting cache pressure at all
> levels—from SQLite’s page-cache to the processor caches.  I am no expert
> in optimization, but the foregoing practically begs to be benchmarked.
>
>   * If for some reason you can’t use xor("k", "x") for all your SQL needs
> (foreign keys come to mind), add another explicit "y" column.  You then
> lose some of the foregoing advantages.  But then, a trivial (and probably
> quite fast) pure-SQL constraint could then be used to enforce some
> integrity:
>
>   CREATE TABLE "" (
>   "k" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
>   CHECK ("k" IS xor("x", "y")) -- NOT NULL for free!
>   );
> i
>   * If you try to use negative integers, your database will trigger a HCF
> instruction.  At the cost of some more performance, CHECK("x">= 0 AND
> xor("k", "x")>= 0) will *partially* solve that.  I say “partially”
> because per the foregoing, SQLite cannot guarantee that "y" = "k"^"x"
> unless you use the modified table, anyway.
>
> Bear in mind, this suggestion stems from a personal bias toward clever XOR
> tricks; at that, I once wrote a set of endian-swab functions with no
> (explicit) temporary variables, purely using XOR-swap and shifts.  I found
> it the most pleasant way to satisfy aliasing rules; yet I am to this day
> uncertain whether the result qualifies as abstract art.
>
> P.S.:  Consider the foregoing a real-life use case in support of adding a
> bitwise ^ operator to SQLite.
>
> Very truly,
>
> Samuel Adam ◊
> 763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
> Legal advice from a non-lawyer: “If you are sued, don’t do what the
> Supreme Court of New Jersey, its agents, and its officers did.”
> http://www.youtube.com/watch?v=iT2hEwBfU1g
>
>
>
>> create table t(i int, j int);
>>
>> insert into t(1,2);
>>
>> insert into t(2,1);<<  should give an error because the pairing of 1-2
>> already exists.
>>
>> insert into t(3,2);<<  OK
>>
>> insert into t(3,1);<<  OK
>>
>> insert into t(1,3);<<  should be error
>>
>>
>>
>> You can't guarantee that one column is less than the other s

Re: [sqlite] does sqlite differ between char, varchar and text?

[David Bicking]

Oh, and as I recall, sqlite2 completely ignored the type declaration. It stored 
what you typed in the schema, but did nothing with it.

I am pretty sure that sqlite3 treats text, char and varchar completely the 
same. It ignores the number after char(x) or varchar(x).

David

--- On Thu, 12/16/10, Artur Reilin  wrote:

> From: Artur Reilin 
> Subject: [sqlite] does sqlite differ between char, varchar and text?
> To: "SQLite User Mailing list" 
> Date: Thursday, December 16, 2010, 1:50 PM
> Does sqlite differ between char,
> varchar and text? I currently just always
> use text. So I wonder if there is any difference if I would
> use varchar or
> not. I read that there are differences in mysql, but I know
> that sqlite
> has it's type affinity and such. And does it also count for
> sqlite(2).
> 
> (Yeah, I know SQLite(2) is old and such, but I'm still at
> the position,
> that I only can use this version for my site.)
> 
> with best wishes
> 
> 
> Artur Reilin
> sqlite.yuedream.de
> ___
> 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] does sqlite differ between char, varchar and text?

[David Bicking]

If I recall correctly, sqlite2 stores everything as text. It doesn't have a 
concept of affinity. Everythign is text and it will convert anything as needed.

David

--- On Thu, 12/16/10, Artur Reilin  wrote:

> From: Artur Reilin 
> Subject: [sqlite] does sqlite differ between char, varchar and text?
> To: "SQLite User Mailing list" 
> Date: Thursday, December 16, 2010, 1:50 PM
> Does sqlite differ between char,
> varchar and text? I currently just always
> use text. So I wonder if there is any difference if I would
> use varchar or
> not. I read that there are differences in mysql, but I know
> that sqlite
> has it's type affinity and such. And does it also count for
> sqlite(2).
> 
> (Yeah, I know SQLite(2) is old and such, but I'm still at
> the position,
> that I only can use this version for my site.)
> 
> with best wishes
> 
> 
> Artur Reilin
> sqlite.yuedream.de
> ___
> 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] Reading a text file and insert to sqlite tables

[David Bicking]

I suck at writing C, so I can't help you there, but what you basically need to 
do is read the lines of text that consist of one record in to variables for 
each field. You then bind these to placeholders for the insert statements. 



open file
sql1 = prepare( "INSERT INTO myTABLE1 (field1, field2, field3) VALUES(?,?,?);")
sql2 = prepare( "INSERT INTO myTABLE2 (field4, field5, filed6) VALUES(?,?,?);")
sql3 = prepare("INSERT INTO myTABLE3 (field7, field8, filed9) VALUES(?,?,?);")

while text left in file
  read line into field1 variable
  read line into field2 variable
  read line into field3 variable
  read line into field4 variable
  read line into field5 variable
  read line into field6 variable
  read line into field7 variable
  read line into field8 variable
  read line into field9 variable

  sqlite3_bind(sql1,1,field1)
  sqlite3_bind(sql1,2,field2)
  sqlite3_bind(sql1,3,field3)
  sqlite3_bind(sql2,1,field4)
  sqlite3_bind(sql2,2,field5)
  sqlite3_bind(sql2,3,field6)
  sqlite3_bind(sql3,1,field7)
  sqlite3_bind(sql3,2,field8)
  sqlite3_bind(sql3,3,field9)

  sqlite3_step(sql1)
  sqlite3_step(sql2)
  sqlite3_step(sql3)
  sqlite3_reset(sql1)
  sqlite3_reset(sql2)
  sqlite3_reset(sql3)
loop
close file
sqlite3_finalize(sql1)
sqlite3_finalize(sql2)
sqlite3_finalize(sql3)


I suspect in reality, the fifth line probably needs to be broken in to 4 
values, each of which goes in to an author table or the like. 

Without knowing how the text maps to the fields you want to store, this is the 
best pseudo code I can give you.

Hopefully it helps,
David

--- On Thu, 12/9/10, yazdan asgari  wrote:

> From: yazdan asgari 
> Subject: Re: [sqlite] Reading a text file and insert to sqlite tables
> To: "General Discussion of SQLite Database" 
> Date: Thursday, December 9, 2010, 12:13 PM
> Hi
> I use C Programming Language and I also know that I could
> use INSERT command. But my problem is whether any one could
> show me a simple code which demonstrate how an INSERT
> command should be written during reading a text file. I have
> searched Google but I could not find any useful link.  
> Yazdan
> 
> --- On Thu, 12/9/10, Simon Slavin 
> wrote:
> 
> From: Simon Slavin 
> Subject: Re: [sqlite] Reading a text file and insert to
> sqlite tables
> To: "General Discussion of SQLite Database" 
> Date: Thursday, December 9, 2010, 2:57 AM
> 
> 
> On 9 Dec 2010, at 9:58am, yazdan asgari wrote:
> 
> > I want to write a program in order to insert data to a
> sqlite database from a text file. The text file contains
> data in each line as below which repeats about 200 lines:
> > 
> > P05075
> > 01-JAN-1988
> > 16
> > TOBACCO RATTLE VIRUS (STRAIN PSG).
> > CORNELISSEN B.J.C., LINTHORST H.J.M., BREDERODE F.T.,
> BOL J.F.;
> > NUCL. ACIDS RES. 14:2157-2169(1986).
> > 141
> > 16297
> > 93420
> >
> MTCVLKGCVNEVTVLGHETCSIGHANKLRKQVADMVGVTRRCAENNCGWFVCIIINDFTFDVYNCCGRSHLEKCRKRVEARNREIWKQIRRIQAESSSATRKKSHNSKNSKKKFKEDREFGAPKRFLRDDVPLGIDQLFVF
> > ...
> > 
> > 
> > I want to read this file and insert each line into a
> value of a database (database contains 3 tables). I can
> write a code to read text file but I do not know how to
> insert these data to sqlite tables during reading process.
> Could any one suggest me a simple program to do this?
> 
> Just use the INSERT command, which is part of the SQL
> standard.
> 
> http://www.sqlite.org/lang_insert.html
> 
> How to access SQLite from your programming language depends
> on what programming language you're using.  If Google
> doesn't find anything helpful for you, tell us what language
> you're using and we might be able to help.
> 
> However, if you are doing this once only, to get your
> database started, it will be simpler to convert your text
> file to .csv format, and use the SQLite command-line tool to
> make a table out of it.
> 
> http://www.sqlite.org/sqlite.html
> 
> Simon.
> ___
> 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
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] gui for data entry

[David Bicking]

Take a look at Kexi. (kexi-project.org) It is part of the Koffice suite, but 
can be used standalone. I had tested it on Windows and had no trouble 
installing it.

It uses sqlite as its back end storage and allows you to create forms, etc. Its 
aim is to be "like" MS Access.

David


--- On Tue, 10/12/10, Graham Smith  wrote:

> From: Graham Smith 
> Subject: Re: [sqlite] gui for data entry
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, October 12, 2010, 12:09 PM
> Oliver
> 
> > OpenOffice Base
> >
> > you need an ODBC driver too (http://www.ch-werner.de/sqliteodbc/)
> 
> Thanks, I did consider this, but the last time I tried, it
> was very
> clunky,  and crashed a lot, but I will have another
> look since you
> have suggested it.
> 
> Graham
> ___
> 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] Getting unique years from a timestamp column

[David Bicking]

yeah, total brain failure on my part. Should have been strftime('%Y',date).  
Sorry.

--- On Wed, 9/29/10, J. Bobby Lopez  wrote:

> From: J. Bobby Lopez 
> Subject: Re: [sqlite] Getting unique years from a timestamp column
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, September 29, 2010, 12:42 PM
> This one doesn't seem to return the
> desired result, instead it returned a
> blank line?
> 
> On Wed, Sep 29, 2010 at 12:40 PM, David Bicking 
> wrote:
> 
> > How about trying
> >
> > select distinct datetime(date,'%Y') as year from
> data;
> >
> > David
> >
> > --- On Wed, 9/29/10, J. Bobby Lopez 
> wrote:
> >
> > > From: J. Bobby Lopez 
> > > Subject: [sqlite] Getting unique years from a
> timestamp column
> > > To: sqlite-users@sqlite.org
> > > Date: Wednesday, September 29, 2010, 12:34 PM
> > > Hello,
> > >
> > > I'm looking to do something completely in SQLite,
> without
> > > the assistance of
> > > Perl.
> > >
> > > The problem is this:
> > >
> > > I have a table with a text column, and the column
> contains
> > > unix timestamps.
> > > I would like to get a list of the unique years
> from that
> > > timestamp column.
> > >
> > > Here's what the raw dataset looks like:
> > >
> > > 1|blopez|somekinda.log|2010-07-10
> > > 13:21:10|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 2|blopez|somekinda.log|2010-09-28
> > > 06:18:51|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 3|blopez|somekinda.log|2010-06-28
> > > 17:58:37|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 4|blopez|somekinda.log|2011-06-28
> > > 17:58:37|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > >
> > > What I'd like to do is write a query that would
> return
> > > "2010" and "2011",
> > > the two unique years in the listing.
> > >
> > > It's pretty easy to get all records which match a
> single
> > > year, for example:
> > >
> > > SELECT id FROM data WHERE datetime(date) LIKE
> '2010%';
> > >
> > > I'm sure I could use a BEGIN/COMMIT block and
> test for each
> > > year
> > > individually, but I don't want to hard-code the
> year that
> > > I'm looking for,
> > > if you get my meaning.
> > >
> > > Any assistance on this would be
> appreciated.  Thanks!
> > >
> > > Bobby
> > > ___
> > > 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
> >
> ___
> 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] Getting unique years from a timestamp column

[David Bicking]

How about trying 

select distinct datetime(date,'%Y') as year from data;

David

--- On Wed, 9/29/10, J. Bobby Lopez  wrote:

> From: J. Bobby Lopez 
> Subject: [sqlite] Getting unique years from a timestamp column
> To: sqlite-users@sqlite.org
> Date: Wednesday, September 29, 2010, 12:34 PM
> Hello,
> 
> I'm looking to do something completely in SQLite, without
> the assistance of
> Perl.
> 
> The problem is this:
> 
> I have a table with a text column, and the column contains
> unix timestamps.
> I would like to get a list of the unique years from that
> timestamp column.
> 
> Here's what the raw dataset looks like:
> 
> 1|blopez|somekinda.log|2010-07-10
> 13:21:10|10.100.0.1|make_db.pl
> |usage_reporter()
> 2|blopez|somekinda.log|2010-09-28
> 06:18:51|10.100.0.1|make_db.pl
> |usage_reporter()
> 3|blopez|somekinda.log|2010-06-28
> 17:58:37|10.100.0.1|make_db.pl
> |usage_reporter()
> 4|blopez|somekinda.log|2011-06-28
> 17:58:37|10.100.0.1|make_db.pl
> |usage_reporter()
> 
> What I'd like to do is write a query that would return
> "2010" and "2011",
> the two unique years in the listing.
> 
> It's pretty easy to get all records which match a single
> year, for example:
> 
> SELECT id FROM data WHERE datetime(date) LIKE '2010%';
> 
> I'm sure I could use a BEGIN/COMMIT block and test for each
> year
> individually, but I don't want to hard-code the year that
> I'm looking for,
> if you get my meaning.
> 
> Any assistance on this would be appreciated.  Thanks!
> 
> Bobby
> ___
> 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

[sqlite] Error message in RAISE(ABORT,...)

[David Bicking]

Can the Error message returned by a trigger be an expression? My testing seems 
to indicate that you can only put a string literal in the Raise function.

What I am trying to do is return the data that the trigger was looking at when 
it raised the error. 

My application can insert one to four records at a time. I do an "INSERT ... 
SELECT ... WHERE BATCH=?" The select is a complicated join of 4 tables, so 
there is no rowid that I could select against.

I would like to know which of the inserted records caused the error. What I 
would ideally like to return is the concatenation of the text in two column 
along with the problem encountered. 

Is there another way I can I determine the bad record? (Other than selecting 
each source record individually then binding and inserting them individually.)

Thanks,
David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the original rows after 'group by'?

[David Bicking]

you can use:

select col1, col2 from test where col1 in (select col1 from test group by col1 
having count(*)<=2);

David

--- On Thu, 8/26/10, Peng Yu  wrote:

> From: Peng Yu 
> Subject: [sqlite] How to get the original rows after 'group by'?
> To: "General Discussion of SQLite Database" 
> Date: Thursday, August 26, 2010, 4:53 PM
> Hi,
> 
> In the following sql query, I want to get all the lines
> which
> satisfies that the first column appears less or equal to 2
> times. Is
> there a way to do it other than using inner join of the
> original table
> and the query in main.sql?
> 
> $ cat main.sql
> #!/usr/bin/env bash
> 
> #sqlite3 foods.db < 
> rm -rf main.db
> sqlite3 main.db < 
> create table test (col1 text, col2 text);
> insert into test values('a1', 'b1');
> insert into test values('a1', 'b2');
> insert into test values('a1', 'b3');
> insert into test values('a1', 'b4');
> insert into test values('a2', 'b5');
> insert into test values('a2', 'b6');
> insert into test values('a3', 'b8');
> 
> .mode column
> .headers on
> .echo on
> select col1, col2 from test group by col1 having count(*)
> <= 2;
> 
> EOF
> 
> $ ./main.sql
> select col1, col2 from test group by col1 having count(*)
> <= 2;
> col1        col2
> --  --
> a2          b6
> a3          b8
> 
> -- 
> Regards,
> Peng
> ___
> 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] playing with triggers

[David Bicking]

--- On Thu, 8/19/10, Simon Slavin  wrote:

> 
> On 19 Aug 2010, at 8:10pm, David Bicking wrote:
> 
> > The way it is set up, if any of the updates/inserts
> done by the triggers fail, everything rolls back, including
> the original data that caused the triggers. What I want to
> happen is that while everything else gets rolled back,
> Table1 still has its data, along with the error messages
> returned by the triggers.
> > 
> > Nearest I can tell you can't do that with triggers,
> but I really don't understand how they work, so maybe I am
> wrong.
> 
> You may be able to do this with ON CONFLICT and
> RAISE.  See
> 
> http://www.sqlite.org/lang_createtrigger.html
> 

I did read that, and think I mostly understand it, With it, and help from this 
list, I have gotten as far as I have. 

I haven't tried RAISE(ROLLBACK... as that seems to severe. 
RAISE(ABORT... removes the initial insert to Table1, which I want to avoid.
RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave 
the first three there, which I can't let happen. It is all or nothing for data 
changes below Table1.

Which leads me to believe I can't do what I want without application code 
supervising the changes.

> with special regard to those two constructions and see
> 
> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
> 

I do have foreign keys declared. Mind you in my tests they don't work if I 
forget to issue Pragma foreign_keys=on. Since I am a forgetful person, it seems 
like I really can't trust foreign keys and it is best to never enable them. 

Or is there a way to force them to be enabled at all times? (i.e. not trust me 
to remember to have any and all applications that talk to the data file to 
remember to issue the pragma statement.)

Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] playing with triggers

[David Bicking]

I am more or less playing with triggers trying to learn what they can do.

I have a setup where I write data to Table1. An after insert trigger looks up 
the newly written data codes in Table1, and writes 1 to 4 records to Table2. An 
after insert trigger on Table2 looks at the new data and updates a handful of 
other table, creating records if need be.

It all works. It is kind of neat to insert one record, and see changes across a 
half dozen other tables.

But... I want it to do more. 

The way it is set up, if any of the updates/inserts done by the triggers fail, 
everything rolls back, including the original data that caused the triggers. 
What I want to happen is that while everything else gets rolled back, Table1 
still has its data, along with the error messages returned by the triggers.

Nearest I can tell you can't do that with triggers, but I really don't 
understand how they work, so maybe I am wrong.

So, is there a way to do the following:

CREATE TRIGGER table1_insert AFTER INSERT ON TABLE1
BEGIN
   -- write to other tables (which can fire triggers or return
   --   constraint failures)
   -- if writes were not successful (constraint failures, etc.)
   --update table1 with message in Errors column
   -- else
   --write 'ok' to Errors column.
END;

Thanks,
David


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] trigger or application code

[David Bicking]

Actually, the amount in items is a running balance of the changes. Mind you, 
there are other fields in items (and in changes) that I did not show as they 
didn't seem to impact this decision.

In that 8% case where an item is to be created, the "change" is in fact the 
creation of the item. 

Originally, I would check to see if the item existed, and create it if needed. 
But given it is such an infrequent event, it seemed better to try to update and 
only create if the update failed. Though looking at Igor's suggested trigger, 
it is just doing the whole select where not exists thing anyway, actually 
twice, once to test if the ABORT should be raised, and second time to see if 
the record should be inserted. 

So maybe I should drop this down to the application:

'pseudo code
exec update items
if sqlite3_changes = 0 
  if creatable = 'N'
return error
  else
exec insert into items
exec insert into changes

It just seemed that if I let sqlite handle this, I could just do one insert in 
whatever host language makes sense, and not have to rewrite that pseudo code in 
whatever language I chose to use for a particular task.


David


PS. Thanks Igor. Your solution was, of course, a thousand times better than the 
best solution I had come up with.

--- On Tue, 8/10/10, Tim Romano  wrote:

> From: Tim Romano 
> Subject: Re: [sqlite] trigger or application code
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, August 10, 2010, 2:08 PM
> David,
> Your approach contravenes "best practice"  by
> violating the core referential
> integrity paradigm: your CHANGES table refers to an item
> not yet in the
> ITEMS table and actually governs whether an ITEM item can
> be created.  The
> child is giving birth to the parent. This is unnecessarily
> convoluted.
> 
> In your example,  you have the ITEMS table track the
> most recent amount.
>  That's all it's doing. Now, if that's all you want this
> table to do ( you
> don't want to have a full ITEMS master table with
> item-description, UPC
> codes, etc etc, for example), you can eliminate the ITEMS
> table. You could
> always get the most recent amount with a simple query.
> 
> select amount from changes where code = ?
>    and changedate =
>   ( select max(changedate) from changes where code = ?
> )
> 
> or in the alternative
> 
> select amount from changes where code = ?
> order by changedate desc limit 1
> 
> 
> The problem with this approach is that any [code] value
> under the sun is
> acceptable; there's no ITEMS table to prevent invalid codes
> via a foreign
> key declaration.
> 
> Regards
> Tim Romano
> 
> 
> 
> 
> On Tue, Aug 10, 2010 at 1:20 PM, Igor Tandetnik 
> wrote:
> 
> > David Bicking 
> wrote:
> > > I am building an application with these two
> tables:
> > >
> > > CREATE TABLE changes(ChangeDate, Code, AdjAmount,
> Descr, Creatable);
> > > CREATE TABLE items(Code, Amount)
> > >
> > > Now, what I would like to happen, I insert in to
> changes, and it updates
> > the Amount in items.
> > >
> > > I can get that with
> > >
> > > CREATE TRIGGER changes_after_insert on changes
> > > BEGIN
> > >  Update items set Amount = Amount +
> new.AdjAmount where code = new.code;
> > > END;
> > >
> > > And for 90% of the time, that will do what I
> want.
> > > But for 8% of the time, the items entry won't be
> there, so I would like
> > to insert a new items entry but only if the Creatable
> > > flag is not 'N'.
> > > The remaining 2% of the time, the items entry
> doesn't exist and the
> > Createable flag is 'N', and I need to ABORT the insert
> and
> > > report the error back to the application.
> > >
> > > My question is can all this be done in a
> trigger?
> >
> > Yes, but it's pretty awkward:
> >
> > BEGIN
> >  select raise(ABORT, 'Item does not exist')
> >  where new.Creatable = 'N' and new.Code not in
> (select Code from items);
> >
> >  insert into items(Code, Amount)
> >  select new.Code, 0
> >  where new.Code not in (select Code from items);
> >
> >  update items set Amount = Amount +
> new.AdjAmount
> >  where code = new.code;
> > END;
> >
> > > Or is this type of logic better handled at the
> application level?
> >
> > Quite possibly.
> > --
> > Igor Tandetnik
> >
> >
> > ___
> > 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
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] trigger or application code

[David Bicking]

I am building an application with these two tables:

CREATE TABLE changes(ChangeDate, Code, AdjAmount, Descr, Creatable);
CREATE TABLE items(Code, Amount)

Now, what I would like to happen, I insert in to changes, and it updates the 
Amount in items.

I can get that with

CREATE TRIGGER changes_after_insert on changes
BEGIN
  Update items set Amount = Amount + new.AdjAmount where code = new.code;
END;

And for 90% of the time, that will do what I want.
But for 8% of the time, the items entry won't be there, so I would like to 
insert a new items entry but only if the Creatable flag is not 'N'.
The remaining 2% of the time, the items entry doesn't exist and the Createable 
flag is 'N', and I need to ABORT the insert and report the error back to the 
application.

My question is can all this be done in a trigger? Or is this type of logic 
better handled at the application level?

I kind of want to have it in a trigger because I would like to have different 
client applications update the database and I don't want to have the Create 
items entry logic sitting in the code of each application. (Especially since 
one possible application could be a simple shell script that uses the command 
line tool to insert the values.)

What would be a "best practice" in this type of situation?

Thanks,
David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] list table structure

[David Bicking]

You would use pragma table_info();

http://www.sqlite.org/pragma.html#pragma_table_info

--- On Mon, 8/2/10, Chris Hare  wrote:

> From: Chris Hare 
> Subject: [sqlite] list table structure
> To: sqlite-users@sqlite.org
> Date: Monday, August 2, 2010, 12:11 PM
> I read on the SQLite how to get the
> list of tables in the SQLite database.
> 
> How do I get the table structure from within a
> program?  For example "describe " would be
> how I would do it in Oracle.
> 
> Thanks,
> Chris
> 
> ___
> 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] create table {table-name} as select.... table definition is imperfectly cloned

[David Bicking]

On Wed, 2010-06-30 at 09:04 -0400, Tim Romano wrote:
> 

> The EXAMPLE: If you create a database in the authoritative version of SQLite
> using INT PRIMARY KEY (rather than INTEGER PRIMARY KEY), when you share the
> database with your Adobe-using affiliate, all hell will break loose. I will
> repeat the example I gave above:
> 
> Let's say you had created this table in authoritative SQLite using INT
> rather than INTEGER PRIMARY KEY:
> 
> CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT)
> insert into FOO(1,'Groucho')
> insert into FOO(2,'Chico')
> Insert into FOO(999, 'Harpo')
> 
> And then you have another table MOVIES where FOOID is a foreign key pointing
> back to FOO.id:
> 
> MOVIES
> fooid|moviename
> 1|Duck Soup
> 2|Duck Soup
> 3|Duck Soup
> 
> 
> ** Note that Harpo's id is 999 in FOO and that table MOVIES contains no such
> foreign key.
> 
> Now your Adobe affiliate executes this query:
> 
> select MOVIES.moviename, FOO.name
> from MOVIES INNER JOIN FOO
> ON FOO.id = MOVIES.fooid
> 
> Unless Adobe has since changed the behavior they told me they were not going
> to change, the query above will produce this result in Adobe:
> 
> Duck Soup|Groucho
> Duck Soup|Chico
> Duck Soup|Harpo
> 
> But Harpo should *not* appear in the resultset. Adobe looks for the row in
> FOO whose rowid =3 and finds Harpo. If Adobe were following authoritative
> SQLite, it would look for the row in FOO whose "ordinary column" id = 3 and
> find no such row.
> 
> 

All things considered, I would say this is clearly and 100% a bug in
Adobe's code. The given SQL nowhere mentioned rowid, yet they are
joining on rowid... that has got to be an error.

And how is Adobe Air doing this? Did they write there own sql parser?
There own VDBE? (Hopefully I got those initials correct.) Are they
calling Sqlite or did they write their own library claiming it is
compatible with the Sqlite file format?

While I don't necessarily disagree with what you are saying, in this
example you appear to be asking the Sqlite developers to emulate an
error in someone else's code.

David

PS. Probably should remove my opinions from this, because the only
reason I am sending this is because I am curious what the hell Adobe is
doing that is causing this confusion.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marking transaction boundaries

[David Bicking]

Did I miss Sqlite getting nested transactions? 

I thought it only had one transaction, in which you can have multiple 
savepoints, which are kind of sort of like nested transactions, but they use 
SAVEPOINT and not BEGIN. Doesn't an abort rollback the full transaction? 

David


--- On Wed, 6/23/10, Simon Slavin  wrote:

> From: Simon Slavin 
> Subject: Re: [sqlite] marking transaction boundaries
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, June 23, 2010, 1:02 PM
> 
> On 23 Jun 2010, at 5:56pm, b s wrote:
> 
> > I get the point that transactions can be nested and i
> am assuming
> > the begin transaction trigger only happens at the
> outer most transaction.
> > It still is the logical envelope which will be rolled
> back on a default abort.
> > (yes, i know one can back off to a save point).
> 
> No.  An abort will not roll back to the outmost
> transaction.  Generally it rolls back the innermost
> transaction.  And if there was a trigger when a
> transaction began, it would be triggered as each nested
> transaction began.  Though you could probalby somehow
> run a counter yourself which could figure out whether the
> BEGIN was for an outmost transaction.
> 
> Simon.
> ___
> 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] Parametrized Queries issue (Possible bug)

[David Bicking]

I think in your first example, the :a comes first, so it is assigned the first 
index value. You then use ?1, which also uses the first parameter index.

In the second, you use ?1 first, then :b, which sees the first index has been 
used and thus assigns it to the second index.

As I believe Jay said, you should not mix named and numbered parameter syntax.

David

--- On Mon, 6/14/10, Israel Lins Albuquerque  
wrote:

> From: Israel Lins Albuquerque 
> Subject: Re: [sqlite] Parametrized Queries issue (Possible bug)
> To: "General Discussion of SQLite Database" 
> Date: Monday, June 14, 2010, 10:55 AM
> Once more detail... 
> 
> Doing this... 
> 
> SELECT * 
> FROM a 
> WHERE a.a = :a 
> AND a.b = ?1; 
> 
> and this... 
> 
> SELECT * 
> FROM a 
> WHERE a.a = ?1 
> AND a.b = :b; 
> 
> Will have different behavior! 
> 
> 
> 
> - Mensagem original - 
> De: "Israel Lins Albuquerque" 
> 
> Para: "sqlite-users" 
> 
> Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 
> Assunto: [sqlite] Parametrized Queries issue (Possible bug)
> 
> 
> Using the example above I see one not documented issue.
> When I use parameters ?NNN and : or @ or $ , 
> in same query as above some of then will be omitted. I
> don't know if this is the expected behavior or not. 
> 
> CREATE TABLE a (a, b); 
> CREATE TABLE b (a, b); 
> 
> INSERT INTO a VALUES (0, 1); 
> INSERT INTO a VALUES (1, 0); 
> INSERT INTO a VALUES (1, 1); 
> INSERT INTO b VALUES (0, 1); 
> INSERT INTO b VALUES (1, 0); 
> INSERT INTO b VALUES (1, 1); 
> 
> SELECT * 
> FROM a 
> JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) 
> WHERE a.a = ?1; 
> 
> In this query as 2 parameters but will be see like only
> one! 
> 
> -- 
> 
> Atenciosamente/Regards, 
> 
> Israel Lins Albuquerque 
> Desenvolvimento/Development 
> Polibrás Brasil Software Ltda. 
> 
> 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org
> 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> -- 
> 
> Atenciosamente/Regards, 
> 
> Israel Lins Albuquerque 
> Desenvolvimento/Development 
> Polibrás Brasil Software Ltda. 
> 
> 
> ___
> 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] Get a specific sequence of rows...

[David Bicking]

On Fri, 2010-03-26 at 10:00 +0100, Fredrik Karlsson wrote:
> Hi,
> 
> I have a list of id:s stored in a field. I would now like to get some
> information from a table by these id:s, but exactly in this order. So,
> if I have a table
> 
> 1 One
> 2 Two
> 3 Three
> 
> and the sequence "3,1,2" stored somewhere, how do I get a neat list like
> 
> Three
> One
> Two
> 
> ?
> I I can see a solution where I split the string outside of sqlite and
> then construct a query consisting of lots of UNIONs, like (not a full
> example, just an illustration)
> 
> (select name from mytab where id = 3)
> UNION ALL
> (select name from mytab where id = 1)
> UNION ALL
> (select name from mytab where id = 2)
> ...
> ...
> 
> but maybe there is a better option somewhere?
> I would be thankful for any help I can get.
> 
> /Fredrik
> 

I'd create a second table
CREATE TABLE sequence (seq integer primary key, id);

Then insert your desired sequence in to that table
seq | id
1   | 3
2   | 1
3   | 2

Select * From datatable, sequence where datatable.id = sequence.id order
by sequence.seq;

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about binding

[David Bicking]

--- On Fri, 3/19/10, Vance E. Neff  wrote:

 
> UPDATE table1 set (?, ?, ?) WHERE col1=? and col2=?;
> 
> I've never used binding before but have known it is a good
> idea in order
> to avoid injection of bad stuff.
> 
> Vance
> 

You count the question marks from left to right. 

> UPDATE table1 set (<1>, <2>, <3>) WHERE col1=<4> and col2=<5>;

You can also put the index number you want to use after the ? so they can be in 
any order you want.

> UPDATE table1 set (?3, ?4, ?5) WHERE col1=?1 and col2=?2;

Which comes in handy if you need to use a value in more than one spot.

As far as I know "set (?, ?, ?)" is incorrect SQL.

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

[David Bicking]

As a test, have you tried wrapping your updates in a transaction?  That
would isolate if the slow down is the actual writing of the data to
disk. 

Where is the file sitting: A local drive, or something across a network
connection?

David
 

On Wed, 2010-02-03 at 08:51 -0800, a1rex wrote:
> >- Original Message 
> >From: Pavel Ivanov 
> >To: General Discussion of SQLite Database 
> >Sent: Wed, February 3, 2010 11:37:17 AM
> 
> >Just first thought came to my mind: are you sure that 2 versions of
> >code mentioned do the same thing? In particular I'm asserting that
> >second version (under #if 1) doesn't do any actual updating and
> >doesn't change your database because you have wrong parameter indexes.
> 
> You are right! 
> Retraction. Mea Culpa. Back to square one… 
> The modified code did not write anything to the drive! But there was no error 
> message from the SQLITE.
> After proper indexing the writing time is about the same!
> 
> >And one more question: why don't you store your prepared statement
> >somewhere and do not prepare it for each row again and again?
> 
> I will. But the problem highlighted by my mistake with indexes is not with 
> the prepared statement,
> but extremely slow write to the drive.
> 
> int UpdateNotesRecord(sqlite3 *handle,
>  int idArg,
>  CString note)
> {
> sqlite3_stmt *stmt;
> int rc;
> 
> #if 1 // 100 updates with 5 character string take 12 seconds
>  
> char *sql = "UPDATE notes SET note=? WHERE id=?";
> rc = sqlite3_prepare_v2(handle, sql, (int) strlen(sql), &stmt, 
> NULL/*&tail*/);
> if(rc) PrintError("UPDATE prepare_v2",rc,handle);
> 
> // idArg
>rc = sqlite3_bind_int(stmt, 1, idArg); 
> // was: rc = sqlite3_bind_int(stmt, 2, idArg);
> if(rc) PrintError("bind idArg error",rc,handle);
> 
> // note
> int byteCount = note.GetLength();
> char *p = note.GetBuffer();
> 
>   rc = sqlite3_bind_text(stmt, 1, p, byteCount, SQLITE_STATIC); 
> // was rc = sqlite3_bind_text(stmt, 2, p, byteCount, SQLITE_STATIC);
> if(rc) PrintError("bind note text error",rc,handle);
> #endif
> 
> rc = sqlite3_step(stmt);
> if(rc != SQLITE_DONE)
> PrintError("update step error",rc,handle);
> 
> rc = sqlite3_finalize(stmt);
> if(rc) PrintError("finalize update error",rc,handle);
> return rc;
> }
> 
> Is there any way to configure database for efficient updates of small amount 
> of text?
> 
> Thank you for reading. Any comment greatly appreciated.
> Samuel 
> 
> 
>   __
> Make your browsing faster, safer, and easier with the new Internet Explorer® 
> 8. Optimized for Yahoo! Get it Now for Free! at 
> http://downloads.yahoo.com/ca/internetexplorer/
> ___
> 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] uninstalling sqlite

[David Bicking]

On Sun, 2010-01-03 at 03:09 +, Simon Slavin wrote:
> On 3 Jan 2010, at 2:44am, Ervin Sebag wrote:
> 
> > I installed sqlite myself from a compressed tar package, using the
> > funpkg -i command.
> 
> if -i does install, perhaps -u does uninstall.  I can't find the 
> documentation for funpkg on the web but perhaps 'funpkg -h' or 'funpkg -?' 
> will tell you something useful.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Package removals
To remove a package completely, use:

funpkg -r packagename.tgz


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BACK API Questions

[David Bicking]

Raghu,

Can you either use the backup API or a simple attach to copy the data
from the memory database to a new file based db. A second process could
then much more slowly poll to see if a new "temporary" file was
available, and attach it, and insert its data albeit slowly in to the
consolidated file-db, then deleting the "temporary file"

Insert would be fast in to the memory db, moving data to disk would be
fast. Creating a master db would be slow, but unless someone can figure
out why that is slowing down so much, I don't think you can ever avoid
the time cost of consolidating the data.

David

On Wed, 2009-12-16 at 08:20 -0600, Raghavendra Thodime wrote:
> Max,
>   I agree with what you are saying. To eliminate such stuff, this is what I 
> have done:  I created schema for multiple tables with no indexing, no joins, 
> no foreign key constraints.  Basically I wanted to keep it as simple as 
> possible for inserts to be faster. Then I simulated the inserts with as 
> minimum C code as possible. I had around 50 inserts within each transaction 
> (between BEGIN and END). Also I had synchronous=OFF and had my journaling 
> also turned off (even tried with MEMORY based journaling). So basically I 
> eliminated all external dependencies that I could think of to make it faster.
> 
>   I simulated for thousands of inserts to see how the system performs on 
> heavy load conditions. I printed the time consumption stats periodically. In 
> such a test case, I see that there is a considerable improvement seen. But 
> what I observed is as I kept running simulations for longer periods, the 
> inserts were taking lot more time in later stages than they were taking 
> initially. For ex, If I ran for 200,000 inserts, first 20,000 inserts were 
> done in 9 secs, but last 20,000 inserts (from 180,000th to 200,000) took 
> almost 110 secs. It is more than 10 times than what it was initially. These 
> results were consistent across all iterations of simulation I did.
> 
>   So I am afraid this won't scale as I keep inserting into same db file. 
> 
>   So my question is, Is this behavior expected? Am I doing something wrong? 
> Is my assumption that time to insert is proportional to size of the db file 
> correct? Since I am simulating with the same application code and with no 
> schema complications, I am assuming there should not be many external 
> dependencies that will affect the system in the long run. 
> 
>   Your help is appreciated...
> 
> Thanks
> Raghu
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Max Vlasov
> Sent: Wednesday, December 16, 2009 2:38 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] BACK API Questions
> 
> On Wed, Dec 16, 2009 at 9:30 AM, Raghavendra Thodime 
> wrote:
> 
> >  I did try using batch of transactions with synchronous=OFF PRAGMA set. The
> > performance improved slightly. But as db file started to grow larger and
> > larger in size, the performance degraded considerably. Is it expected? Or Is
> > there a work around for this?
> >
> 
> If you did everything according this topic:
> 
> (19) INSERT is really slow - I can only do few dozen INSERTs per second (
> http://www.sqlite.org/faq.html#q19 )
> 
> from FAQ, you probably have extra overload with your development
> language/library. Also be aware that you will probably never get the same
> speed as the general copying of same amount of data with the file system
> routines since the db engine have to deal with indexes and other extra job.
> So the performance high or low might be a subjective estimate.
> ___
> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ideal way to check whether a table has a specific column

[David Bicking]

You could try to prepare a statement like "SELECT YourColumnName FROM 
YourTable;" If prepare returns an error, then likely the error message will say 
the column doesn't exist. If no error, it does exist. Such a simple query 
shouldn't take long to prepare. Probably less time then to grab the results of 
the pragma and test the strings therein.

David

--- On Tue, 11/3/09, Shaun Seckman (Firaxis)  wrote:

> From: Shaun Seckman (Firaxis) 
> Subject: [sqlite] Ideal way to check whether a table has a specific column
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, November 3, 2009, 4:25 PM
> Hello all,
> 
>                 I
> just wanted to find out whether or not this is the
> most ideal method for determining if a column exists in a
> table.  My
> current technique is to do execute "pragma
> table_info(tableName)" then
> step through the results and perform a string comparison
> against the
> "name" column until I hit a match or I've finished stepping
> through the
> record set. 
> 
>  
> 
> Is there a better way?
> 
>  
> 
> Shaun Seckman
> 
> Firaxis Games
> Programmer
> 
>  
> 
> ___
> 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] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[David Bicking]

I probably should have explicitly stated that my suggestion only worked
up to 24 hours. Unfortunately I couldn't think of a solution for greater
values.

Yesterday Igor posted a solution that works with days. You never
responded to him so perhaps you didn't see it. I'll copy it here:
**
SELECT
cast(secs/86400 as integer) || '.' || 
strftime('%H:%M:%f', secs ,'unixepoch') from
(select SUM(tripSeconds) as secs from mytable);

Igor Tandetnik
**

You really can't go higher than "days" because months come in different
sizes, as do years, i.e. months can be either 28, 29, 30 or 31 days
depending on the time of year, etc.
Years can be 365 or 366 days.

If you want to make every MONTH=30 DAYS, well, you would need to do the
math. Probably would be much easier to do in your application program
than in SQL.

Hopefully that helps.

David


On Mon, 2009-11-02 at 03:22 -0800, DaleEMoore wrote:
> Hi David;
> 
> That's LOVELY for small numbers like:
> 
> SELECT STRFTIME('%H:%M:%f',62.5,'unixepoch')
> 00:01:02.500
> 
> What do you think I should do with larger periods of time? In the following
> I hoped for 00-00-00.01:02.500.
> 
> SELECT STRFTIME('%Y-%m-%d.%H:%M:%f',62.5,'unixepoch')
> 1970-01-01.00:01:02.500
> 
> I really appreciate hearing from you,
> Dale

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[David Bicking]

On Sun, 2009-11-01 at 12:15 -0800, DaleEMoore wrote:
> I'd like to SUM(tripSeconds) and format output as
> days.hours:minutes:seconds.hundredths, but have not been able to figure out
> how to do that with sqlite. This didn't seem to come close:
> 
> SELECT 
> STRFTIME('%d', SUM(tripSeconds)) + '.' +
> STRFTIME('%H', SUM(tripSeconds)) + ':' +
> STRFTIME('%M', SUM(tripSeconds)) + ':' +
> STRFTIME('%f', SUM(tripSeconds)) AS Duration, 
> SUM(tripSeconds)
> 
> Any ideas are appreciated,
> Dale E. Moore

Assuming you have seconds in tripSeconds, this seems to come close to
what you want:

SELECT STRFTIME('%H:%M:%f',duration,'unixepoch') from (SELECT
SUM(tripSeconds) FROM yourtable);

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[David Bicking]

Indeed, stupid typo in my sql.  Sorry for the noise and the lack of a subject 
on the subject line in my first email.

David

--- On Tue, 10/27/09, Pavel Ivanov  wrote:

> From: Pavel Ivanov 
> Subject: Re: [sqlite] (no subject)
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, October 27, 2009, 3:26 PM
> Something screwed up in your
> application:
> 
> sqlite> create table assets (Code Text, Acct1 Text,
> Acct2 Text);
> sqlite> insert into assets values ('C0', 'name1',
> 'name2');
> sqlite> select * from assets;
> C0|name1|name2
> sqlite> insert into assets select 'C0', 'name1', 'name3'
> where not
> exists (select 1 from assets where Code='C0' and
> acct1='name1');
> sqlite> select * from assets;
> C0|name1|name2
> sqlite>
> 
> 
> Pavel
> 
> On Tue, Oct 27, 2009 at 2:44 PM, David Bicking 
> wrote:
> > I have a table ASSETS with fields (Code Text, Acct1
> Text, Acct2 Text).
> > (There are other fields, and the primary key is a
> combination of 5 columns)
> >
> > For one code ('C0') I want to implement a rule that if
> I attempt to insert a combination of 'C0'/Acct1/Acct2 it
> will be ignored if the first two keys are already in the
> table. (for non-'C0' codes, this rule doesn't apply.)
> >
> > select * from assets;
> > Code   Acct1     Acct2
> > 'C0'   'name1'   'name2'
> >
> >
> > insert into assets values('C0', 'name1', 'name3');
>  -- ignore
> > insert into assets values('C0', 'name3', 'name4');
>  -- succeed
> > insert into assets values('C0', 'name1', 'name2');
>  -- ignore
> > insert into assets values('C1', 'name1', 'name2');
>  -- succeed
> >
> > I tried:
> >
> > insert into assets select 'C0', 'name1', 'name3' where
> not exists (select 1 from assets where Code='C0' and
> acct1='name1');
> >
> > but it went ahead and inserted the row anyway. My hope
> was that since the where clause was false, the select would
> return nothing and thus nothing would be inserted.
> >
> > Is there a way I can craft the insert to do this, or
> do I need to set up a trigger to ignore the insert? If the
> latter, how should the trigger read?
> >
> > Thanks,
> > David
> > ___
> > 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
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[David Bicking]

I have a table ASSETS with fields (Code Text, Acct1 Text, Acct2 Text).
(There are other fields, and the primary key is a combination of 5 columns)

For one code ('C0') I want to implement a rule that if I attempt to insert a 
combination of 'C0'/Acct1/Acct2 it will be ignored if the first two keys are 
already in the table. (for non-'C0' codes, this rule doesn't apply.)

select * from assets;
Code   Acct1 Acct2
'C0'   'name1'   'name2'


insert into assets values('C0', 'name1', 'name3');  -- ignore
insert into assets values('C0', 'name3', 'name4');  -- succeed
insert into assets values('C0', 'name1', 'name2');  -- ignore
insert into assets values('C1', 'name1', 'name2');  -- succeed
 
I tried:

insert into assets select 'C0', 'name1', 'name3' where not exists (select 1 
from assets where Code='C0' and acct1='name1');

but it went ahead and inserted the row anyway. My hope was that since the where 
clause was false, the select would return nothing and thus nothing would be 
inserted.

Is there a way I can craft the insert to do this, or do I need to set up a 
trigger to ignore the insert? If the latter, how should the trigger read?

Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] controlling changes

[David Bicking]

On Sat, 2009-10-10 at 19:31 +0100, Simon Slavin wrote:
> On 10 Oct 2009, at 5:08pm, David Bicking wrote:
> 
> > I have a table:
> > CREATE TABLE Assets
> > (   ControlDate Date
> > ,   Amt
> > )
> 
> There is no such column type as 'Date' in SQLite.  You got TEXT,  
> INTEGER, REAL.  Make sure you know what's going into that field.  Also  

Yes, I know there is no Date type in Sqlite. I would likely be storing
the date as text in a '-mm-dd' format.

> I don't understand your primary key for the Asset TABLE.  Is  
> 'ControlDate' a primary key ?  In other words can you have no more  
> than one record for any one day ?  Your answer to this changes how the  
> TRIGGER has to work.
> 
I haven't entirely decided what the primary key would be. Possibly an
artificial autoincrement value, but probably a composit key of a half
dozen fields not shown as I didn't think them important to the problem.
The control date likely would be part of the composite key. But
certainly you can have more than one record with a given ControlDate.

> > Now, the business rules are you can INCREASE the Amt if the Current  
> > Date
> > is the ControlDate in the record. You can DECREASE the amount if the
> > Current Date is greater than or equal to the ControlDate.
> 
> You mean 'greater than or less than', right ?  Otherwise you  
> contradict yourself.
> 
Um... I do believe I have gotten it wrong. There are in fact two
controldates, on controldate #1, you can increase the value. ON or after
controldate #2, which will always be after controldate #1, you can
decrease the amount. I guess controldate#1 would be better called the
creation date, and controldate #2 would be maturity date.  They are a
function of each other, but I am not exactly sure about that yet. More
research to do.

> > Can this be enforced via a trigger, or must I enforce that business  
> > rule
> > at the application layer?
> 
> You can absolutely use a TRIGGER for this, but you need some way of  
> making the current date (you mean 'right now' ?) available to SQLite.   
> Something like
> 
>CREATE TRIGGER Assets_change_amt
>BEFORE UPDATE ON Assets
>FOR EACH ROW BEGIN
>SELECT RAISE(ROLLBACK, 'Amount can only increase on a  
> particular day.')
>WHERE  (new.amt <= old.amt) AND (new.ControlDate = currentDate);
>END;
> 

I'll test if I can do date functions in triggers. But this tells me what
I need to know.  Sort of a D'oh! moment as "new.amt <= old.amt" NEVER
occurred to me. Thanks for pointing out what should have been plainly
obvious even to me!

> But I just wrote 'currentDate' in there and you won't be able to use  
> that.  What I don't know is how you have formatted the contents of the  
> ControlDate field.  If you have been consistent with SQLite, then you  
> can use something like
> 
> date('now')
> strftime('%s','now')
> 
> That is assuming you can use date functions inside TRIGGERs.  I think  
> that depends on which version of SQLite you're using.  For that you  
> need someone more expert than I am.
> 
> Simon.
> ___
> 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] controlling changes

[David Bicking]

Can you show me what the constraint would look like?

Thanks,
David

On Sat, 2009-10-10 at 09:11 -0700, Harold Wood & Meyuni Gani wrote:
> You can do it as a constraint.
> 
> -Original Message-
> From: David Bicking 
> Sent: Saturday, October 10, 2009 9:08 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] controlling changes
> 
> I have a table:
> CREATE TABLE Assets
> ( ControlDate Date
> , Amt
> )
> 
> Now, the business rules are you can INCREASE the Amt if the Current Date
> is the ControlDate in the record. You can DECREASE the amount if the
> Current Date is greater than or equal to the ControlDate.
> 
> Can this be enforced via a trigger, or must I enforce that business rule
> at the application layer?
> 
> Thanks,
> David
> 
> 
> ___
> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] controlling changes

[David Bicking]

I have a table:
CREATE TABLE Assets
(   ControlDate Date
,   Amt
)

Now, the business rules are you can INCREASE the Amt if the Current Date
is the ControlDate in the record. You can DECREASE the amount if the
Current Date is greater than or equal to the ControlDate.

Can this be enforced via a trigger, or must I enforce that business rule
at the application layer?

Thanks,
David


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] exceptions to check constraints

[David Bicking]

I've never had to deliberately use check constraints in my projects, but
I would like to use them on a new projects. I've googled the subject,
but found no good explanations.

I have a table 
CREATE TABLE Assets
(   Nametext PRIMARY KEY
,   Amt integer
);

Now, for 99% of the records, the Amt can never be negative.

Can I put CHECK (Amt >=0), yet somehow allow the one or two accounts
that can be negative to be negative?   I am assuming the answer is NO.
Am I correct?

Thinking about it, I came up with this solution:
CREATE TABLE Assets
(   Nametext PRIMARY KEY
,   Amt integer
,   CF  text -- Y if account can be negative, else N
CHECK   (CF='Y' OR Amt>= 0)
);

Is this the correct way to handle this, or is there a better way?

David



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems with max(datestamp) in subquery

[David Bicking]

On Mon, 2009-08-17 at 15:05 -0700, Leo Freitag wrote:
> David Bicking-2 wrote:
> > 
> > As written, you were selecting any record with the correct date
> > regardless of Ensemble or Steuck.
> > 
> > David

> The following seem to work:
> 
> SELECT * FROM tblZO_Haupt AS hpt
> WHERE
> hpt.zo_tblEnsemble = 
> AND hpt.zo_tblStueck = ...
> AND hpt.datum = (
> SELECT MAX(hpt_sub.datum)
> FROM tblZO_Haupt AS hpt_sub
> WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger
> AND hpt_sub.zo_tblEnsemble = hpt.zo_tblEnsemble
> AND hpt_sub.zo_tblStueck = hpt.zo_tblStueck
> AND hpt_sub.datum <= '2009-08-03 00:00:00'); 
> 
> But I didn't really get the deeper meaning of the three conditions in the
> subquery: hpt_sub.xyz = hpt.xyz?
> 
> Leo
> 
> 
That will work because it forces the subquery and main query to look at
the same values for tblSaenger, tblEnsemble and tblStueck.

David


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT * from ?

[David Bicking]

On Thu, 2009-08-13 at 01:12 +0100, Simon Slavin wrote:
> On 12 Aug 2009, at 11:35pm, Igor Tandetnik wrote:
> 
> > Bill Welbourn 
> > wrote:
> >> I am new to the forum and have a question regarding querying data
> >> from a table, where the table name is random. For example, I have a
> >> database possessing upwards of 41 tables named, TAB10, TAB11,...,
> >> TAB50, and would like to query each table.
> >
> > Consider instead having a single table with an extra column containing
> > values 10, 11, ... , 50. You'll have no end of trouble with your  
> > current
> > design.
> 
> I second this advice.

I also agree it is best to reconsider your schema.

> >> Question: Within the C programming environment, is it possible to
> >> prepare a sqlite SELECT query with a random table name as a
> >> variable/parameter?
> >
> > No. Which is precisely why you should reconsider your design.
> 
> Although you can do it by executing your queries as simple text  
> commands using _execute rather than using _prepare and _step.
> 
> Simon.

You could prepare 41 queries, and in code step the correct one.

sqlite3_prepare_v2(db, "SELECT * FROM TAB10", -1, &q10, 0)

sqlite3_prepare_v2(db, "SELECT * FROM TAB50", -1, &q50, 0)


You could do it in a single query, but it would be really ugly:

SELECT * FROM TAB10 WHERE 'TAB10' = :TAB
UNION ALL
SELECT * FROM TAB11 WHERE 'TAB11' = :TAB
UNION ALL

SELECT * FROM TAB50 WHERE 'TAB50' = :TAB;


David






___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems with max(datestamp) in subquery

[David Bicking]

On Wed, 2009-08-12 at 07:02 -0700, Leo Freitag wrote:
> 
> I modified the code a get the following results. Unfortunetly only a little
> more as expected:
> 
> DROP TABLE 'tblZO_Haupt'; 
> CREATE TABLE 'tblZO_Haupt' ('id' INTEGER PRIMARY KEY, 'zo_tblEnsemble'
> INTEGER, 'zo_tblSaenger' INTEGER, 'zo_tblStueck' INTEGER, 'zo_tblStimmbez'
> INTEGER, 'datum' TEXT);
> INSERT INTO "tblZO_Haupt" VALUES(1,1,1,1,11,'2009-08-01 00:00:00'); 
> INSERT INTO "tblZO_Haupt" VALUES(2,1,2,1,11,'2009-08-01 00:00:00'); 
> INSERT INTO "tblZO_Haupt" VALUES(3,1,1,1,20,'2009-08-05 00:00:00'); 
> INSERT INTO "tblZO_Haupt" VALUES(4,1,2,1,20,'2009-08-05 00:00:00'); 
> INSERT INTO "tblZO_Haupt" VALUES(5,2,1,1,11,'2009-08-01 00:00:00'); 
> INSERT INTO "tblZO_Haupt" VALUES(6,2,1,1,20,'2009-08-05 00:00:00'); 
> INSERT INTO "tblZO_Haupt" VALUES(7,2,1,1,14,'2009-08-02 00:00:00'); 
> INSERT INTO "tblZO_Haupt" VALUES(8,2,2,1,14,'2009-08-02 00:00:00'); 
> 
> SELECT * FROM tblZO_Haupt AS hpt
> WHERE hpt.datum = (
> SELECT MAX(hpt_sub.datum)
> FROM tblZO_Haupt AS hpt_sub
> WHERE hpt_sub.zo_tblSaenger = hpt.zo_tblSaenger
>  AND hpt_sub.zo_tblEnsemble = 1
>  AND hpt_sub.zo_tblStueck = 1
>  AND hpt_sub.datum <= '2009-08-03 00:00:00')
  AND hpt.zo_tblEnsemble = 1
  AND hpt.zo_tblStueck = 1;

As written, you were selecting any record with the correct date
regardless of Ensemble or Steuck.


David




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Comparisons SQL

[David Bicking]

I think your problem is that you need to put the value in sDateTemp in quotes.

"WHERE Date < '" & sDateTemp & "'"

or "WHERE Format$(Date, "-mm-dd") < '"& sDateTemp & "'"

Without the quote, I think sqlite is subtracting the day from the month from 
the year, and comparing that number with the Date string.

David


--- On Mon, 8/3/09, Rick Ratchford  wrote:

> From: Rick Ratchford 
> Subject: Re: [sqlite] Date Comparisons SQL
> To: "'General Discussion of SQLite Database'" 
> Date: Monday, August 3, 2009, 2:51 PM
> Okay, I think I understand what you
> are saying.
> 
> The native Date in a table without any additional
> expressions is '-mm-dd
> 00:00:00'.
> 
> Those "00:00:00" must be my problem.
> 
> Rick
> 
> 
> 
> 
> #>-Original Message-
> #>From: sqlite-users-boun...@sqlite.org
> 
> #>[mailto:sqlite-users-boun...@sqlite.org]
> On Behalf Of Rick Ratchford
> #>Sent: Monday, August 03, 2009 1:45 PM
> #>To: 'General Discussion of SQLite Database'
> #>Subject: Re: [sqlite] Date Comparisons SQL
> #>
> #>That's the clincer.
> #>
> #>The resulting DATE column is actually the format of
> the 
> #>equation as well.
> #>
> #>I've attached a view of the results from the working
> SQL 
> #>statement that does not perform the WHERE.
> #>
> #>"WHERE Format$(Date, '-mm-dd') < sDateTemp"
> does not work.
> #>
> #>Also, as stated in my previous post, I have sDateTemp
> 
> #>formatted in the same format as that which is in the
> table.
> #>
> #>That's why I'm puzzled.
> #>
> #>Rick
> #> 
> #> 
> #>
> #>#>-Original Message-
> #>#>From: sqlite-users-boun...@sqlite.org
> 
> #>#>[mailto:sqlite-users-boun...@sqlite.org]
> On Behalf Of Igor Tandetnik
> #>#>Sent: Monday, August 03, 2009 1:38 PM
> #>#>To: sqlite-users@sqlite.org
> #>#>Subject: Re: [sqlite] Date Comparisons SQL #>
> #>Rick 
> #>Ratchford wrote:
> #>#>> The Date is being stored as -mm-dd. Note
> the 
> #>"Format$(Date, #>> '-mm-dd') as Date" that
> assures this.
> #>#>
> #>#>The "Date" that appears in the WHERE clause is
> the value of 
> #>#>the Date column in the table, not the value of
> the 
> #>expression #>with the "Date" 
> #>#>alias. You can't actually use aliases in the
> WHERE clause. 
> #>#>You are confusing yourself by using the same
> identifier 
> #>both #>for the column name and for the alias.
> #>#>
> #>#>You could write
> #>#>
> #>#>WHERE Format$(Date, '-mm-dd') < sDateTemp
> #> #>Or else, 
> #>express sDateTemp in the same format that you have
> #>dates 
> #>stored in the table - the format you get when you just
> #>run 
> #>"SELECT Date from mytable".
> #>#>
> #>#>Igor Tandetnik
> #>#>
> #>#>
> #>#>
> #>#>___
> #>#>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
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite version 2.8 vs 3.x

[David Bicking]

On Thu, 2009-07-30 at 09:18 -0430, An wrote:
> my question stayed unanswered, so that is why i'm repeating it on the
> mailinglist:
> 
> if sqlite2.8 will be supported of bugs the following years, as the web page
> says, what is another reason for working with version 3.x instead of 2.8 ?
> 
> i'm working with xampp-lite server that comes bundled with 2.8... I guess if
> there's no mayor reason i could stay working on 2.8 ?
> 
> is this recommended ?

> thanx

> An M

If I recall correctly, sqlite 2.8 only stored values as text, so if you
are storing lots of numbers, you will be converting back and forth
between text and numeric a lot. Ditto for blobs.

IIRC, bound parameters was introduced in sqlite 3.x, so you run a
greater risk of sql injection attacks using 2.8.

3.x can better handle schema changes invalidating prepared (though I
think they were called "compiled" in 2.8) statements.

3.x I believe handles multiple threads better than 2.8.

I have written program using 2.8 and since they work, I see no reason to
go back and change them.

Hope that helps.
David



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users