Re: [sqlite] speeding up an FTS5 search with a JOIN

[Puneet Kishor]

Update: so, after much hitting of my head against the wall of sql, I came up 
with the following – as noted above, I really have two distinct set of queries 
I can do separately like so

Q1: (SELECT t1Id FROM t1 WHERE …) AS a

Q2: (SELECT t1Id FROM vt1 WHERE vt1 MATCH ‘bar’) AS b

Then, I can do the following -

SELECT Count(*) FROM a WHERE a.t1Id IN b

Of course, in reality, I don’t do this separately but all in one go to make a 
really messy SQL but a really fast query, a couple of hundred ms as opposed to 
> 25s

You might notice that in my Q2 above I MATCHed for ‘bar’ instead of ‘foo’. That 
is because ‘bar’ returns fewer rows than ‘foo’ does. The problem remains when 
there are too many matches in the FTS query in which case the FTS query itself 
is slow, for example, with ‘foo’ which matches > 80K rows.

Now, one interesting point of comparison – the same kind of query (from the 
user point-of-view) against an ElasticSearch instance (that is, all the rows 
with ‘foo’ anywhere in the text) is very fast, in the order of sub-hundred ms. 
I realize it might be unfair comparing SQLite with ElasticSearch, but still. 
(Or, is it a fair comparison?)


> On Mar 7, 2020, at 8:59 AM, P Kishor  wrote:
> 
> I asked this question on Stackoverflow with not much success, and a 
> suggestion to ask it on the list. So here I am. I have two tables, t1(id, 
> t1Id, … other cols …, fullText) and a FTS5 virtual table vt1(t1Id, fullText)
> 
> ```
> sqlite> EXPLAIN QUERY PLAN
>   ...> SELECT Count(*) as num FROM t1 WHERE deleted = 0;
> 
> QUERY 
> PLAN
> --SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=?)
> 
> sqlite> SELECT Count(*) as num FROM t1 WHERE deleted = 0;
> 308498
> 
> Run Time
> : real 0.043 user 0.023668 sys 0.009005
> ```
> 
> 
> As can be see above, the actual query takes ~43ms
> 
> ```
> sqlite> EXPLAIN QUERY PLAN
>   ...> SELECT Count(*) as num FROM vt1 WHERE vt1 MATCH 'foo';
> 
> QUERY 
> PLAN
> --SCAN TABLE vt1 VIRTUAL TABLE INDEX 131073:
> 
> sqlite> SELECT Count(*) as num FROM vt1 WHERE vt1 MATCH 'foo';
> 80789
> 
> Run Time
> : real 0.047 user 0.008021 sys 0.009640
> ```
> 
> The actual query, in this case, takes ~47ms. So far so good. But the problem 
> occurs when I join the two tables
> 
> ```
> sqlite> EXPLAIN QUERY PLAN
>   ...> SELECT Count(*) as num
>   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>   ...> WHERE t1.deleted = 0 AND vt1 MATCH 'foo';
> 
> QUERY 
> PLAN
> |--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
> --SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (t1Id=?)
> 
> sqlite> SELECT Count(*) as num
>   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>   ...> WHERE t1.deleted = 0 AND vt1 MATCH 'foo';
> 80789
> 
> Run Time
> : real 26.218 user 1.396376 sys 5.413630
> ```
> 
> The answer is correct but the query takes more than 26 seconds! Of course, I 
> would like to speed up this query by several orders of magnitude, but I would 
> also like to understand why this join is causing the slowdown.
> 
> Now, the reason I have constructed a query like this is because users can add 
> further constraints for the table t1. For example, 
> 
> ```
> sqlite> SELECT Count(*) as num
>   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>   ...> WHERE t1.deleted = 0 AND 
>   ...> WHERE t1.frob = ‘bar' AND 
>   ...> WHERE t1.nob = ‘baz' AND 
>   ...> vt1 MATCH 'foo’;
> ```
> 
> Also, in every operation, for every given constraint, two queries are 
> performed, one that returns the count and the other that returns the actual 
> columns. And, finally, only a subset of the results are returned using LIMIT 
> and OFFSET but *after* a sort ORDER has been prescribed. So, in reality, the 
> last constraint above would result in the following
> 
> ```
> sqlite> SELECT Count(*) as num
>   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>   ...> WHERE t1.deleted = 0 AND 
>   ...> WHERE t1.frob = ‘bar' AND 
>   ...> WHERE t1.nob = ‘baz' AND 
>   ...> vt1 MATCH 'foo’;
> 20367
> 
> sqlite> SELECT t1.id, t1.t1Id, … other cols …, 
>   ...> snippet(vt1, 1, "", "", "", 50) AS context,
>   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>   ...> WHERE t1.deleted = 0 AND 
>   ...> WHERE t1.frob = ‘bar' AND 
>   ...> WHERE t1.nob = ‘baz' AND 
>   ...> vt1 MATCH ‘foo’
>   ...> ORDER BY 
>   ...> LIMIT 30 OFFSET ;
> ```
> 
> When no t1 columns are prescribed in the constraint, the default count (shown 
> above) and default cols are returned with the FTS search
> 
> ```
> sqlite> SELECT Count(*) as num
>   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>   ...> WHERE t1.deleted = 0 
>   ...> vt1 MATCH 'foo’;
> 20367
> 
> sqlite> SELECT t1.id, t1.t1Id, … other cols …, 
>   ...> snippet(vt1, 1, "", "", "", 50) AS context,
>   ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>   ...> WHERE t1.deleted = 0 
>   ...> vt1 MATCH ‘foo’
>   ...> ORDER BY 
>   ...> LIMIT 30 OFFSET 0;
> ```
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

[Mr. Puneet Kishor]

On Apr 18, 2012, at 11:20 PM, starvingpilot wrote:

> 
> 
> 
> Puneet Kishor-2 wrote:
>> 
>> 
>> On Apr 18, 2012, at 11:10 PM, starvingpilot wrote:
>> 
>>> 
>>> Here's a query that works 
>>> 
>>> sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE
>>> state like '%@ %'",theState]; <--- this yields a result: 0
>>> 
>>> sqlStatement = [NSString stringWithFormat:@"SELECT state, power || ' kW'
>>> power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power
>>> FROM
>>> stations ORDER BY power)",theState]; 
>>> 
>>> I get a result: 1 from that last one... "theState" in this code is a
>>> variable for that specific state I need.
>> 
>> 
>> I don't see a question above. It's not clear if you are asking something.
>> Wrt to filtering by state, I already sent you an example for that. Hope
>> that helped.
>> 
>> 
>> --
>> Puneet Kishor
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> Your query netted a result of ALL states... I'd like to search a specific
> state for example..  I'd like to find antennas in CA only and order by
> power.


didn't you get the following? It returns rows for only 'CA'

SELECT state, power || ' kW' power 
FROM (
SELECT  state, Cast(Rtrim(power, 'kW') AS 'numeric') power 
FROMstations 
WHERE   state = 'CA' 
ORDER BYpower
);



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

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

[Mr. Puneet Kishor]

On Apr 18, 2012, at 11:10 PM, starvingpilot wrote:

> 
> Here's a query that works 
> 
> sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE
> state like '%@ %'",theState]; <--- this yields a result: 0
> 
> sqlStatement = [NSString stringWithFormat:@"SELECT state, power || ' kW'
> power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM
> stations ORDER BY power)",theState]; 
> 
> I get a result: 1 from that last one... "theState" in this code is a
> variable for that specific state I need.


I don't see a question above. It's not clear if you are asking something. Wrt 
to filtering by state, I already sent you an example for that. Hope that helped.


--
Puneet Kishor

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

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

[Mr. Puneet Kishor]

On Apr 18, 2012, at 11:04 PM, starvingpilot wrote:

> 
> I actually did have the parenthesis there... I didnt copy and paste the last
> one.  It was there however and I am still having problems
> 


Well, it is hard to help you unless you tell us exactly what problem you are 
having. You might want to include the actual command you are running and the 
actual response you are getting. Without that its just a guessing game. As I 
showed you with the entire transcript, the query runs just fine.


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

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

[Mr. Puneet Kishor]

On Apr 18, 2012, at 11:01 PM, starvingpilot wrote:

> 
> Also, I need to select from a specific state like "CA"  Where is that part in
> the query?


SELECT state, power || ' kW' power 
FROM (
SELECT  state, Cast(Rtrim(power, 'kW') AS 'numeric') power 
FROMstations 
WHERE   state = 'CA' 
ORDER BY    power
);


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

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

[Mr. Puneet Kishor]

On Apr 18, 2012, at 10:47 PM, starvingpilot wrote:

> 
> Thanks for the quick reply Puneet.  However I had a syntax error on App as
> well as when I typed that query on my sqlite browser.  Here's my syntax
> "SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power,
> 'kW') AS 'numeric') power FROM stations ORDER BY power"
> 


balance your parens. you are missing a closing parens after "ORDER BY power<---"

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

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

[Mr. Puneet Kishor]

On Apr 18, 2012, at 10:27 PM, starvingpilot wrote:

> 
> Greetings!  I am currently working on an iOS app and not too well versed in
> SQLite.  Here's what I am trying to do.
> 
> I have a table "stations" and the fields "state" populated by US States and
> "power" which is populated with strings similar to 1.0 kW, 50.0 kW 10.0 kW
> etc...
> 
> so the query I need... I need something along the lines of "SELECT * FROM
> stations WHERE state like '%x%' ORDER BY ..." Here lies the issue.  I need
> to trim kW from the number and turn it into an integer/double/float what
> have you so that I can order it by power lowest to highest or vice versa... 
> 
> So essentially I searching for a antennas in a specific state like "CA"
> Where x is my placeholder and I'd like to sort them by their power.
> 


sqlite> CREATE TABLE t (s TEXT, p TEXT);
sqlite> INSERT INTO t VALUES ('WI', '10.3 kW');
sqlite> INSERT INTO t VALUES ('CA', '2.13 kW');
sqlite> INSERT INTO t VALUES ('MI', '31.4 kW');
sqlite> SELECT * FROM t ORDER BY p;
WI|10.3 kW
CA|2.13 kW
MI|31.4 kW
sqlite> SELECT s, p || ' kW' p FROM (SELECT s, Cast(Rtrim(p, 'kW') AS 
'numeric') p FROM t ORDER BY p);
CA|2.13 kW
WI|10.3 kW
MI|31.4 kW
sqlite> 



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

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 5:51 PM, Simon Slavin wrote:

> 
> On 16 Apr 2012, at 11:25pm, Puneet Kishor <punk.k...@gmail.com> wrote:
> 
>> I absolutely don't get any of the above. Why is "keeping the data" worse 
>> than keeping the commands? I am not even sure what is a command vs. what is 
>> a data. A SQL command has data embedded in it. I mean, if I have
>> 
>>  UPDATE t 
>>  SET name = 'foo', val = 3.1415
>>  WHERE id = 22;
>> 
>> which part was the command and which part was the data?
> 
> The command is the three rows above.  Imagine you'd passed that command to 
> _exec().  So as well as executing that command you write it to a file 
> somewhere:
> 
> logid timestamp   command
> 1637422347634.133 UPDATE t SET name = 'foo', val = 3.1415 WHERE 
> id = 22;
> 
> In terms of the data, I'm not sure whether you're keeping copies of the 
> entire row, or just triplets.  If you're keeping a copy of the row every time 
> the row changes then you would need to store
> 
> logid timestamp   tablename   id  nameval col3col4
> col5...
> 2138762347634.133 t   22  foo 3.1415  6   
> fredx   ...
> 
> if instead you are storing triplets then you would need to store two rows of 
> data
> 
> logid timestamp   tablename   id  column  value
> 8247242347634.133 t   22  namefoo
> 8247252347634.133 t   22  val 3.1415


all that is fine, but how does that solve my problem? So, I want to find out 
row 22 "AS OF", to use Oracle's Total Recall functionality (thanks PA), at a 
time before the update happened. How do I do that?

This just seems way too elaborate with no gains toward the capability I desire. 
Maybe I am missing something.

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

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 5:04 PM, Simon Slavin wrote:

> 
> On 16 Apr 2012, at 10:31pm, Peter Aronson <pbaron...@att.net> wrote:
> 
>> You might want to look at the book Temporal Data and the Relational Model by 
>> Date, Darwin and Lorentzos, which goes into the subject in fairly great 
>> detail.  
>> There are subtleties.
> 
> Doctor Darwen teaches this as a course.  You might like to read the free 
> detailed course notes here:
> 
> <http://www.dcs.warwick.ac.uk/~hugh/TTM/TemporalData.Warwick.pdf>
> 
> Unfortunately he uses terms aimed at an academic mathematical understanding 
> of attributes (relvars), which are difficult to understand if you're just a 
> programmer at the rock face.  What it comes down to is that if the only data 
> you have is the result of SQL commands you don't have enough data to 
> understand the facts implicit in the contents of your tables.
> 
> I urge again the different approach I mentioned earlier.  Forget keeping the 
> data, and instead keep the commands used to change the data.  That way, 
> instead of keeping the /results/ of your SQL commands, you're keeping the 
> commands yourself, which is rawer (more raw ?) data, and therefore more 
> faithful to what you know, rather than what you're trying to deduce.

I absolutely don't get any of the above. Why is "keeping the data" worse than 
keeping the commands? I am not even sure what is a command vs. what is a data. 
A SQL command has data embedded in it. I mean, if I have

UPDATE t 
SET name = 'foo', val = 3.1415
WHERE id = 22;

which part was the command and which part was the data? 

Why is just making a copy of the existing row with id = 22 and then modifying 
the copy not good enough? I don't have to deduce anything. All I have to do is 
make a copy of any row that is "more than a trivial update". Theoretically I 
could do that with every single table, and if the id didn't change (assuming I 
had a PK that was different from the id, say, a composite PK), then I could 
reconstruct exact queries easily.

> 
> Whether you are keeping copies of the rows in the table, or timestamping SQL 
> commands, I suggest that for SQLite your timestamps should be unixepoch 
> stored as a REAL rather than a text expression of seconds.
> 

Yes, that is a useful advice.

Thanks,

--
Puneet Kishor

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

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 3:47 PM, Petite Abeille wrote:

> 
> On Apr 16, 2012, at 10:37 PM, Puneet Kishor wrote:
> 
>> Thanks for your wise words. I am not at all under any illusion that this is 
>> going to be easy, but it is worthy of an honest try. Two reactions --
>> 
>> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
>> `created_on <= :provided_date`? The latter requires storing only a single 
>> date value for every row. Although, after some studying of the Pg timetravel 
>> docs, seems like they too use a start and stop date.
> 
> Although the end date is not strictly speaking necessary, and can be derived 
> from a previous start date, it make the query more natural: "date between 
> start and end", as opposed to some other peculiar oddities…
> 
> It also allows to express deletion in one fell swoop: delete a record by 
> closing its  end date.
> 
>> 2. Yes, most constraint mechanisms might be useless or difficult to 
>> implement, but I do need a PK.
> 
> Well, I suspect you need the equivalent of, say, a "business key". Something 
> that uniquely identify a record *outside* of its versioning. But such an 
> identifier is most likely not going to be a primary key, in the traditional 
> relational constraint sense of it.
> 


and hence, my original question: given

id INTEGER,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_on)

how do I get the effect of `id INTEGER AUTOINCREMENT`. Guess I can't, not in 
sqlite3, because AUTOINCREMENT only works with the PK invocation. So, I have to 
use some other manual mechanism. Fwiw, in Pg I can do

id SERIAL,
created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_on)

where `SERIAL` does the right thing by way of setting up the sequences, etc.


--
Puneet Kishor

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

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 2:45 PM, Petite Abeille wrote:

> 
> On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote:
> 
>> I am trying to create a data versioning system so that a query done at a 
>> particular time can be reproduced identically as to the original query even 
>> if the data have been modified in the interim time.
> 
> My 2¢ worth…
> 
> (1) Proper historization/versioning is not a piece of cake
> (2) Most constraint mechanisms do not help with it
> 
> Regarding (1), I would suggest a relatively straightforward setup where all 
> you versioned tables include a date range specifying the point in time a 
> record is valid. This is more conveniently expressed as two fields, along the 
> lines of valid_from and valid_to, so you can then query it with a between 
> clause.
> 
> Each DML operations need to maintain that date range so it stays logically 
> consistent (e.g. no overlaps, not gaps, no delete, etc).
> 
> At the end of the day, you should be able to query your data for any point in 
> time consistently:
> 
> select  *
> fromfoo
> 
> joinbar
> on  bar.bar_key = foo.bar_key
> 
> where   foo.foo_key = 1
> and julianday( ... ) between foo.valid_from and foo.valid_to
> and julianday( ... ) between bar.valid_from and bar.valid_to
> 
> 
> Regarding (2), I would suggest to forgo traditional integrity constraint 
> mechanisms (primary, unique, referential, etc) as they simply don't play well 
> with (1). For example, one cannot express a meaningful, and useful, primary, 
> nor unique key on versioned data. Ditto for referential constraints. Which 
> also means you have to re-implement  all of the above by yourself. Which is a 
> pain and rather error prone.
> 


Thanks for your wise words. I am not at all under any illusion that this is 
going to be easy, but it is worthy of an honest try. Two reactions --

1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
`created_on <= :provided_date`? The latter requires storing only a single date 
value for every row. Although, after some studying of the Pg timetravel docs, 
seems like they too use a start and stop date.

2. Yes, most constraint mechanisms might be useless or difficult to implement, 
but I do need a PK.


--
Puneet Kishor

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

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 1:14 PM, Kit wrote:

> 2012/4/16 Puneet Kishor <punk.k...@gmail.com>:
>> I am experimenting with a home-grown versioning system where every 
>> "significant" modification to row would be performed on a copy of the row, 
>> the original being preserved.
>> Any other suggestions to achieve a similar functionality would be welcome.
>> --
>> Puneet Kishor
> 
> 1. Use Git or Mercurial


My statement might have been misunderstood. I am not trying to create a 
versioning system a la Git, Mercurial or Fossil. I am trying to create a data 
versioning system so that a query done at a particular time can be reproduced 
identically as to the original query even if the data have been modified in the 
interim time.

So, if a query returns one or more rows today, the same query (that is, the 
same query params with an additional time stamp param) should return exactly 
the same result 3 years from now even if the rows themselves may have been 
modified.

In Postgres world they call it timetravel. See "F.39.2. timetravel — Functions 
for Implementing Time Travel" at 
http://www.postgresql.org/docs/9.1/static/contrib-spi.html for reference.




> 2. Try this:
> 
> CREATE TABLE instance  (
> filename TEXT,
> version INT,
> size INT,
> md5sum TEXT,
> creation_date TEXT,
> last_write_time TEXT,
> PRIMARY KEY(filename,version),
> FOREIGN KEY (md5sum) REFERENCES resource (md5sum)
> );
> 
> CREATE TABLE resource (
> md5sum TEXT,
> data BLOB,
> primary key(md5sum)
>   );




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

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 1:08 PM, Simon Slavin wrote:

> 
> On 16 Apr 2012, at 6:58pm, Puneet Kishor <punk.k...@gmail.com> wrote:
> 
>> I am experimenting with a home-grown versioning system where every 
>> "significant" modification to row would be performed on a copy of the row, 
>> the original being preserved. So, if I have 
>> 
>>  CREATE TABLE t (
>>  id INTEGER,
>>  created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
>>  name TEXT,
>>  is_trivial_update BOOLEAN DEFAULT 0,
>>  PRIMARY KEY (id, created_on)
>>  );
>> 
>> today I can have
>> 
>>  1, 2012-04-16 12:51:00, John, 0
>> 
>> and in the coming days I can make it
>> 
>>  1, 2012-04-16 12:51:00, John, 0
>>  1, 2012-04-17 10:00:00, Johnny, 0
>>  1, 2012-04-17 10:00:00, Johnnie, 1
>>  1, 2012-04-17 22:12:00, John Walker, 0
> 
> Have one table which holds just the current data.  Use the standard primary 
> key mechanism with that table, allowing it to supply an autoincrementing 
> integer primary key for that table.
> 
> Have another table which lists all the changes for the first table.  The 
> primary key for the second table can also be an autoincrementing integer 
> primary key, but that has nothing to do with one with all the current values 
> in it.  The 'id' column of the first table should be a different column of 
> the second table.  Use a TRIGGER mechanism so that every INSERT and UPDATE 
> for the first table makes an entry in the second table.
> 

Thanks. That is one approach I have considered. I will try it out, but I am 
less enthusiastic about it as it would involve creating a shadow table for 
every table in the db. I am planning to try both approaches, evaluate, and 
choose among them after real experimentation.


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

Re: [sqlite] auto-incrementing integer in composite primary key

[Puneet Kishor]

On Apr 16, 2012, at 12:32 PM, Igor Tandetnik wrote:

> On 4/16/2012 12:51 PM, Mr. Puneet Kishor wrote:
>> 
>> On Apr 16, 2012, at 11:47 AM, Patrik Nilsson wrote:
>> 
>>> You can use:
>>> 
>>> create table t ( id integer primary key autoincrement, created_on
>>> DATETIME DEFAULT CURRENT_TIMESTAMP )
>>> 
>> 
>> No, the above will create a PK on only the 'id' column. I want a composite 
>> PK with 'id' and 'created_on' columns
> 
> Why?  What purpose do you believe a composite key would serve, that would not 
> be served equally well with a primary key on id column alone?
> 


I am experimenting with a home-grown versioning system where every 
"significant" modification to row would be performed on a copy of the row, the 
original being preserved. So, if I have 

CREATE TABLE t (
id INTEGER,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
name TEXT,
is_trivial_update BOOLEAN DEFAULT 0,
PRIMARY KEY (id, created_on)
);

today I can have

1, 2012-04-16 12:51:00, John, 0

and in the coming days I can make it

1, 2012-04-16 12:51:00, John, 0
1, 2012-04-17 10:00:00, Johnny, 0
1, 2012-04-17 10:00:00, Johnnie, 1
1, 2012-04-17 22:12:00, John Walker, 0

Then, I can get the value of id 1 on any given datetime with something like

SELECT name, created_on 
FROM t 
WHERE 
id = 1 AND 
is_trivial_update = 0 AND 
created_on <= '2012-04-17 09:00:00' 
ORDER DESC 
LIMIT 1;

which would yield 

    John, 2012-04-16 12:51:00

Any other suggestions to achieve a similar functionality would be welcome.


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

Re: [sqlite] auto-incrementing integer in composite primary key

[Mr. Puneet Kishor]

On Apr 16, 2012, at 11:47 AM, Patrik Nilsson wrote:

> You can use:
> 
> create table t ( id integer primary key autoincrement, created_on
> DATETIME DEFAULT CURRENT_TIMESTAMP )
> 
> 



No, the above will create a PK on only the 'id' column. I want a composite PK 
with 'id' and 'created_on' columns, but 'autoincrement' keyword seems to work 
only with 'primary key' invocation.



> 
> On 04/16/2012 06:27 PM, Mr. Puneet Kishor wrote:
>> Given
>> 
>>  CREATE TABLE t (
>>  id INTEGER NOT NULL,
>>  created_on DATETIME DEFAULT CURRENT_TIMESTAMP
>>  PRIMARY KEY (id, created_on)
>>  );
>> 
>> how can I make just the 'id' column auto-increment?
>> 
>> 
>> --
>> Puneet Kishor

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

[sqlite] auto-incrementing integer in composite primary key

[Mr. Puneet Kishor]

Given

CREATE TABLE t (
id INTEGER NOT NULL,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (id, created_on)
);

how can I make just the 'id' column auto-increment?


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

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Puneet Kishor]

On Apr 13, 2012, at 3:14 PM, Steinar Midtskogen wrote:

> Puneet Kishor <punk.k...@gmail.com> writes:
> 
>> If you want the results in separate columns, you can do something like
>> 
>> SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' 
>> minimum, Max(a) minimum FROM t;
> 
> Then it does a full scan again.
> 
> But Igor's suggestion "SELECT (SELECT min(unix_time) FROM table),
> (SELECT max(unix_time) FROM table)" works fine, and means less code.
> 


Yes, Igor's suggestion is definitely better, but where is the full table scan?

sqlite> EXPLAIN QUERY PLAN SELECT Min(a) FROM t UNION ALL SELECT Max(a) 
FROM t;
selectid|order|from|detail
1|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows)
2|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

Am I missing something?


--
Puneet Kishor

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

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Puneet Kishor]

Try the following

sqlite> EXPLAIN QUERY PLAN SELECT Min(a) FROM t UNION ALL SELECT Max(a) FROM t;
selectid|order|from|detail
1|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows)
2|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)

Should be a lot faster than a single query without UNION.

If you want the results in separate columns, you can do something like

SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' minimum, 
Max(a) minimum FROM t;

On Apr 13, 2012, at 2:44 PM, Steinar Midtskogen wrote:

> Alessandro Marzocchi  writes:
> 
>> What does EXPLAIN QUERY PLAN says?
> 
> sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time) FROM table;
> 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows)
> 
> sqlite> EXPLAIN QUERY PLAN SELECT max(unix_time) FROM table;
> 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows)
> 
> sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time), max(unix_time) FROM table;
> 0|0|0|SCAN TABLE table (~100 rows)
> 
> I suppose a query for a single min/max gets optimised, while a query
> involving multiple columns doesn't.
> 
> I have a much bigger table as well, and on that one the speedup is in
> the millions to run two SELECTs.  It's hard to guess that there will
> be such a difference, but I suppose I should be happy that there is at
> least an optimised way to get min and max for the integer primary key.
> 
> -- 
> Steinar
> ___
> 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] brain failed: help needed

[Puneet Kishor]

You need LEFT JOIN. 

Also, keep in mind that no operator other than IS NULL or IS NOT NULL works on 
NULL.

On Mar 17, 2012, at 9:06 AM, RhinosoRoss  wrote:

> 
> Hi everyone,
> Sorry to be a pest, but I'm drowning in manual pages and clearly missing
> something simple. I don't think I'm going to progress without some help.
> 
> I noticed at work that they were using excel to store serial numbers of
> devices sold in orders... Should be a database! So I knocked one up in MS
> Access in a couple of minutes - but I'm trying to learn SQLite at home, I'm
> a C++ programmer so the actual embedding is straightforward, it's the SQL
> I'm struggling with.
> In MS Access, you simply create tables and join them together in the
> graphical ER-digaram, then creating queries is trivial - all the joins are
> done for you.
> 
> It's taken me a week to figure out how to get all the joins in one schema to
> list the records as they were in the original excel spreadsheet:
> 
> Date,Order Number, Customer, Serial Number, Notes
> 
> Oh, this should be so easy!
> 
> I have two remaining issues that I can't figure out.
> 1) My SQL to list everything won't print records with NULL Notes.
> 2) I don't understand if I'm supposed to be using the indexes I've created
> explicitly, or if they're used automagically.
> 
> Here's the database Schema:
> 
> 
> CREATE TABLE [Customer] (
>  [ID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
>  [Name] TEXT  UNIQUE NOT NULL);
> 
> CREATE TABLE [Notes] (
>  [ID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
>  [Note] TEXT  UNIQUE NOT NULL);
> 
> CREATE TABLE [Order] (
>  [ID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
>  [Number] TEXT  UNIQUE NOT NULL,
>  [Customer] INTEGER NOT NULL REFERENCES Customer(ID) ON UPDATE CASCADE,
> [Date] DATE);
> 
> CREATE TABLE [SoldItem] (
>  [ID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
>  [Serial] TEXT  UNIQUE NULL,
>  [Order] INTEGER  NOT NULL REFERENCES [Order](ID) ON UPDATE CASCADE,
>  [Notes] INTEGER  REFERENCES Notes(ID) ON UPDATE CASCADE);
> 
> CREATE UNIQUE INDEX NotesNote ON Notes(Note);
> 
> CREATE UNIQUE INDEX OrderNumber ON [Order](Number);
> 
> CREATE UNIQUE INDEX SoldItemSerial ON SoldItem(Serial);
> __
> This is the SQL that Access provided:
> 
> SELECT Order.Date, Order.Number, Customer.Name, SoldItem.Serial, Notes.Note
> FROM Notes INNER JOIN (
>  (Customer INNER JOIN [Order]  ON Customer.ID = Order.Customer)
>  INNER JOIN SoldItem ON Order.ID = SoldItem.Order)
>  ON  Notes.ID = SoldItem.Notes;
> __
> This is what I think should work in SQLite:
> 
> SELECT Date, Number, Name, Serial, Note
> FROM (SoldItem JOIN
> (Customer JOIN [Order]  ON Customer.ID =[Order].Customer) [Order]
>  ON[Order].ID = SoldItem.[Order]) SoldItem
>  JOIN Notes ON SoldItem.Notes = Notes.ID;
> 
> but if SoldItem.Notes is NULL, no record is produced. I've tried
> CROSS,INNER,OUTER and rearranging the order but to no avail.
> 
> If I remove the Notes table from the SQuirreL, it all works, but I want the
> notes in the result!
> Works:
> SELECT Date, Number, Name, Serial
> FROM SoldItem JOIN
> (Customer JOIN [Order]  ON Customer.ID =[Order].Customer) [Order]
>  ON [Order].ID = SoldItem.[Order]
> 
> So could somebody please write me the SQL to to list all the records in the
> database as they were originally (Date, Number, Name, Serial, Note)
> including records with no notes?
> 
> Thanks for your time - much pain will be alleviated :-)
> -- 
> View this message in context: 
> http://old.nabble.com/brain-failed%3A-help-needed-tp33522661p33522661.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? Empty string stored as NULL?

[Puneet Kishor]

On Mar 8, 2012, at 1:48 PM, Marc L. Allen wrote:

> Is that expected?  To me, '' is different than NULL.
> 
> create table a
> (
>a text
> );
> 
> insert into a select  '';
> select * from a;
> 
> Is there a way to store an empty string?
> 


works for me

punkish@mumbai ~$sqlite3
SQLite version 3.7.10 2012-01-16 13:28:40
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t (a TEXT NOT NULL);
sqlite> INSERT INTO t SELECT '';
sqlite> SELECT * FROM t;

sqlite> SELECT Count(*) FROM t;
1
sqlite> 

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

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table

[Puneet Kishor]

On Feb 20, 2012, at 12:46 PM, Jay A. Kreibich wrote:

> On Mon, Feb 20, 2012 at 05:01:17PM +, Simon Slavin scratched on the wall:
>> 
>> On 20 Feb 2012, at 4:11pm, Puneet Kishor <punk.k...@gmail.com> wrote:
>> 
>>> is there are way to force the query to return only the "first" match
>>> so the resulting query has only as many rows as the left table?
>> 
>> You have two problems:
>> 
>> A) You are JOINing two columns which contain some null values.  null is
>> interpreted as 'I don't know', so the joins tend to match many
>> different rows.
> 
>  No, that's not how it works.  If you have a join condition like
>  (A.col == B.col), and either A.col or B.col (or both) is NULL, then
>  the result of the comparison itself is NULL ("Are these values
>  equal?"  "I don't know.").  JOIN operation will only match rows where
>  the join condition is TRUE.  NULL is not "good enough" to match rows.
> 
>  Think about NATURAL JOINs where the two columns are reduced to only
>  one column.  This reduction can only take place if the values are
>  exactly the same.
> 
>> B) You are using a LEFT JOIN which is explicitly defined to include
>> many matches, not just one match.  Instead you might try NATURAL JOIN
>> or CROSS JOIN to see what difference they make.
> 
>  That's not the issue.  Or rather, it is, but that isn't going to show
>  us anything we don't already know.  NATURAL JOIN should return the
>  exact same results as example #5: 329743  (assuming "col" is the only
>  column name shared between tables), except it will merge the two
>  "col" columns into a single column.
> 
>  A CROSS JOIN has no join condition and is going to return
>  2,066,471,848 rows.  That tells us nothing.
> 
>> And you have a meta-problem too: the dataset you're experimenting with
>> is so big you can't see why it's doing what it's doing.
> 
>  Databases are designed to work with sets.  If you want to debug the
>  issue, you need to think in sets as well.  Use the database to answer
>  your questions, rather than just scanning the results and doing it in
>  your head.
> 
>  The statement...
> 
>SELECT COUNT(*) as ct, col FROM a GROUP BY 2 HAVING ct > 1
> 
>  ...will tell you which rows have duplicate columns.  You can run this
>  on either table.
> 
>  If you want to have unique values in the JOIN, the best approach is
>  to put a unique index across each "col" column, so that the columns
>  have unique values.
> 
>  Failing that, replace the table reference in example #6 with
>  something like "(SELECT * FROM a GROUP BY col) as a".  That's going
>  to give you random rows (there is no concept of a "first" row), but
>  it will get you the row count you're looking for.


Thanks Jay... brilliant explanation. I ended up finding the dupes in table "b" 
and re-encoding the column so it had only unique values. You are right, "first" 
doesn't mean much in this case... I was referring to "the first matching row, 
and to hell with the subsequent matches." Fortunately, the problem was not too 
laborious to correct manually.


> 
>  You might need to do it with both tables, depending on which table(s)
>  has the duplicate "col" values.

Yup, fortunately I know that; it was table "b". Table "a" has geographic 
features, and neither do I want to "create new" nor "lose existing" features. 
Table "b" has attributes to categorize features in table "a". Cleaning up table 
"b" cleared the problem.



--
Puneet Kishor

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

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table

[Puneet Kishor]

On Feb 20, 2012, at 9:34 AM, Igor Tandetnik wrote:

> Puneet Kishor <punk.k...@gmail.com> wrote:
>> But, why is #5 329743 and not 329686? Where are the extra 57 rows coming 
>> from? I thought a LEFT JOIN was supposed to include
>> *all* the rows from the left table with NULLs for the columns of the right 
>> table where there was no match. So, at most, the
>> resulting table would have as many rows as the rows in the left table. 
>> Certainly not more.  
> 
> The conclusion doesn't follow from the premise. Yes, a row from the left 
> table is included in the resultset even if there are no matching rows in the 
> right table. Which means that the resultset would contain *at least* as many 
> rows as there are in the left table - but it certainly may contain more. For 
> each row in the left table that has N matching rows in the right table (with 
> N > 0), there will be N rows in the resultset.

Thanks for the great explanation. Now, the follow up question --

is there are way to force the query to return only the "first" match so the 
resulting query has only as many rows as the left table?



--
Puneet Kishor

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

[sqlite] why is count of rows in LEFT JOIN more than the rows in the left table

[Puneet Kishor]

Could someone please shed some light on this mystery for me?

1.  SELECT Count(*) FROM a;
329686

2.  SELECT Count(*) FROM a WHERE a.col IS NULL;
3

3.  SELECT Count(*) FROM b;
6268

4.  SELECT Count(*) FROM b WHERE b.col IS NULL;
0

5.  SELECT Count(*) FROM a LEFT JOIN b ON a.col = b.col;
329743

6.  SELECT Count(*) FROM a LEFT JOIN b ON a.col = b.col WHERE a.col 
IS NOT NULL;
329740

Of course, given #5, #6 is as expected because of #3.

But, why is #5 329743 and not 329686? Where are the extra 57 rows coming from? 
I thought a LEFT JOIN was supposed to include *all* the rows from the left 
table with NULLs for the columns of the right table where there was no match. 
So, at most, the resulting table would have as many rows as the rows in the 
left table. Certainly not more.

Seems like my understanding is wrong.


--
Puneet Kishor


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

[sqlite] replacing several nested queries and UNION ALL with a single query

[Puneet Kishor]

(this could be a potentially duplicate post -- I was sure I sent it out, but I 
can't find it in the out box. So, here goes again)

Say I have table like so

CREATE TABLE t (
a1, a2, 
b1, b2, 
c1, c2
);

I want 

a1 AS foo WHERE a1 != '' AND a2 != '' AND a1 = a2

from the remaining

b1 AS foo WHERE b1 != '' AND b2 != '' AND b1 = b2

from the remaining

c1 AS foo WHERE c1 != '' AND c2 != '' AND c1 = c2

from the remaining

'none' AS foo

My current query is very cumbersome. I am doing one query, then UNIONing it 
with the results of second query from the complement of the first query then 
UNIONing that with with results of the third query from the complement of the 
first and the second query and so on.


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

[sqlite] replacing several nested queries and UNION ALLs with one query

[Puneet Kishor]

Say I have table like so

CREATE TABLE t (
a1, a2, 
b1, b2, 
c1, c2
);

I want 

a1 AS foo WHERE a1 != '' AND a2 != '' AND a1 = a2

from the remaining

b1 AS foo WHERE b1 != '' AND b2 != '' AND b1 = b2

from the remaining

c1 AS foo WHERE c1 != '' AND c2 != '' AND c1 = c2

from the remaining

'none' AS foo

Right now I am doing a bunch of UNION ALLs of separate queries, but am 
wondering if there is a better way. Oh, in reality there are three more such 
pairs (d1, d2, e1, e2, f1, f2), but the above should be sufficient to develop a 
pattern. My current query

SELECT a1 AS foo 
FROM t 
WHERE a1 != '' AND a2 != '' AND a1 = a2

UNION ALL

SELECT b2 AS foo
FROM (
SELECT b1, b2  
FROM t 
WHERE a1 != '' OR a2 != '' OR a1 != a2 
) tmp 
WHERE tmp.b1 != '' AND tmp.b2 != AND tmp.b1 = tmp.b2

UNION ALL

and so on...


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

Re: [sqlite] Explanation

[Puneet Kishor]

On Feb 9, 2012, at 4:17 PM, Marco Bambini wrote:

> sqlite 3.7.8 on MacOS X
> 
> This query works fine:
> SELECT rowid, * FROM 'customer' WHERE rowid < 100;
> 
> While this query does not return anything:
> SELECT rowid, * FROM 'customer' WHERE 'rowid' < 100;
> 
> Do you have an explanation?


perhaps because in the second query 'rowid' is being treated as a string and 
not a column. You might want to try "rowid" instead (double quotes).

--
Puneet Kishor

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

Re: [sqlite] ALTER TABLE

[Puneet Kishor]

On Feb 6, 2012, at 9:36 AM, Bill McCormick wrote:

> Is there no way to force columns added to a table with alter table to be 
> added at certain column positions?
> 
> Alternatively, if there is some way to save the data in an existing table; 
> drop the table; re-create the table with the desired schema; and then reload 
> the data, this would be useful as well. However, I cannot see how to do this 
> simply.

just do it exactly as you state above... 

dump
drop
recreate
reload

script it and it couldn't be any simpler. Although, ordinarily neither you nor 
the db should care about the order of the column. The results come out in the 
order you specify.

CREATE TABLE t (a, b, c);
SELECT b, c, a FROM t WHERE...

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

Re: [sqlite] DBI->connect doesn't fail if no DB

[Puneet Kishor]

On Feb 2, 2012, at 2:18 PM, Bill McCormick wrote:

> In the following statement ...
> 
>my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","") or croak 
> $DBI::errstr;
> 
> if $dbfile does not exist, it is created. I would like to croak if this 
> happens.
> 
> Is there some option for this?
> 

I don't think so. You could simply check for the existence of the file before 
you connect

croak "I am dying" unless -e $dbfile;
my $dbh etc. etc.

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

Re: [sqlite] FIRST/LAST function

[Mr. Puneet Kishor]

On Feb 2, 2012, at 9:46 AM, Petite Abeille wrote:

> 
> On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote:
> 
>> Does SQLite have FIRST and LAST  aggregate function?
> 
> No, sadly, SQLite doesn't support any analytic functions (aka window 
> function) such as first, last, lead, lag, rank, etc, etc... [1]
> 
> To achieve the same, you will have to roll your own, which is not always a 
> piece of cake. Oh, well...
> 
> [1] http://orafaq.com/node/55

to the OP... since you are using Perl (I believe, based on your earlier 
emails), you can get your data out and then use the most excellent List::Util 
(https://metacpan.org/module/List::Util). List::MoreUtils 
(https://metacpan.org/module/List::MoreUtils) and Scalar::Util 
(https://metacpan.org/module/Scalar::Util)

--
Puneet Kishor

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

Re: [sqlite] Perl script to output to file

[Mr. Puneet Kishor]

On Feb 1, 2012, at 10:20 AM, Bill McCormick wrote:

> From the sqlite command line interface, the .output command will output the 
> result of a query to a file.
> 
> How can I do this from a Perl script? I'm not finding any built-in facility 
> to do this in the Perl module.
> 


pseudocode ahead

1. open filehandle $fh
2. prepare and execute query $query
3. write results to filehandle `say $fh $query`
4. close filehandle $fh

--
Puneet Kishor

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

Re: [sqlite] SQLite and Perl

[Puneet Kishor]

On Jan 26, 2012, at 2:53 PM, Bill McCormick wrote:

> Puneet Kishor wrote, On 1/26/2012 12:02 PM:
>> On Jan 26, 2012, at 11:59 AM, Bill McCormick wrote:
>> 
>>> I need to get going with Perl&  SQLite and I'm wondering what I need to get 
>>> off CPAN, if anything.
>> 
>> 
>> $ cpanm DBD::SQLite
>> 
>> there is no step two
>> 
> Actually, that's not correct. In my case it took the following:
> Step 1: cpan DBI
> Step 2: cpan DBD:SQLite
> 
> What is cpanm? A typo?
> 

http://search.cpan.org/~miyagawa/App-cpanminus-1.5007/lib/App/cpanminus.pm

yes, I kinda assumed that you had DBI already... so, indeed, install DBI, then 
install DBD::SQLite. That is all.

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

Re: [sqlite] SQLite and Perl

[Puneet Kishor]

On Jan 26, 2012, at 11:59 AM, Bill McCormick wrote:

> I need to get going with Perl & SQLite and I'm wondering what I need to get 
> off CPAN, if anything.



$ cpanm DBD::SQLite

there is no step two


> 
> Here's what I think I have now:
> 
> root@fiberio:/etc/cron.daily# locate perl | grep SQLite
> /usr/share/perl/5.10.1/CPANPLUS/Internals/Source/SQLite
> /usr/share/perl/5.10.1/CPANPLUS/Internals/Source/SQLite.pm
> /usr/share/perl/5.10.1/CPANPLUS/Internals/Source/SQLite/Tie.pm
> 
> Since it's been awhile since I've done anything with Perl, is there some 
> gettin' goin' guide?
> 
> 
> Thanks!!!
> ___
> 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] finding pairwise similarity

[Mr. Puneet Kishor]

I have two tables like so

CREATE TABLE c (c_no INTEGER PRIMARY KEY, c_name TEXT);
CREATE TABLE t (t_no INTEGER PRIMARY KEY, t_name TEXT);
CREATE TABLE c_t (c_no INTEGER, t_no INTEGER);

Every row in `c` has one or more `t`. I want, in a single SQL, the following

source_c, target_c, similarity

where `similarity` is a measure of overlap of `t` between each pair of `c` and 
is calculated as the number of `t` common between a given pair divided by the 
total number of unique `t` in the pair. For example, given the following records

c   t
--- ---
1   1
1   2
1   3
2   1
2   5

I want the result to be

source_c target_c similarity
  --
1   2 0.2 (= 1 common `t` between the 2 `c` / by total 4 `t`)

Would appreciate any nudge toward a solution.

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

Re: [sqlite] Database Diagram

[Mr. Puneet Kishor]

On Dec 6, 2011, at 6:45 AM, priya786 wrote:

> Hello i want to know how to get the database diagram from sqlite.Please tell
> me the solution.


If you are on a Mac, SQL Editor is a very nice product for about $80.
http://www.malcolmhardie.com/sqleditor/

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

Re: [sqlite] Slow INDEX

[Mr. Puneet Kishor]

On Nov 2, 2011, at 11:31 AM, Fabian wrote:

> 2011/11/2 Mr. Puneet Kishor <punk.k...@gmail.com>
> 
>> 
>> 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.


ahh, so you *are* getting expected behavior, just not what *you* expected. Did 
you have a different number in mind instead of a factor of 300? And, if so, 
why? I am genuinely curious -- I know nothing about Windows. I rarely reboot my 
computer -- my desktop iMac hasn't been rebooted in several weeks now. I did 
reboot my MacBook Air a few days ago for a software update, but usually that 
too goes through a few weeks before it is rebooted... when I do reboot them, I 
experience everything to be slow for the first 10-15 mins or so.

--
Puneet Kishor

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

Re: [sqlite] Slow INDEX

[Mr. Puneet Kishor]

On Nov 2, 2011, at 11:24 AM, Fabian wrote:

> Now if I re-open the database, I can add an additional 10.000 rows very
> fast (<1 sec). But if I reboot the (Windows) PC, and insert an additional
> 10.000 rows, it takes at least 30 secs, which seems very slow, if I can add
> the first 1 million in under 10 seconds.


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.



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

Re: [sqlite] Tables as ASCII - is it possible?

[Mr. Puneet Kishor]

On Oct 22, 2011, at 11:34 PM, Paul Linehan wrote:

> If I could go with
> a scripting language, it would be Python - vastly superior IMHO
> to Perl - YMMV.


Yup. My mileage does vary.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tables as ASCII - is it possible?

[Mr. Puneet Kishor]

On Oct 22, 2011, at 11:06 PM, Paul Linehan wrote:

> Hi all,
> 
> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?
> 
> I want to be able to run scripts against my data as well as use SQLite.
> ..

Are you suggesting that you want to treat text data as a SQL data store? You 
might want to look at DBD::CSV 
[http://search.cpan.org/~hmbrand/DBD-CSV-0.33/lib/DBD/CSV.pm]

--
Puneet Kishor

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

Re: [sqlite] Error Message near ".": syntax error

[Mr. Puneet Kishor]

On Oct 8, 2011, at 8:28 PM, James Brison wrote:

> I am receiving the following error message when running a query against a 
> sqlite db:
> 
> Error Message near ".": syntax error
> 
> What does this mean? and how do I debug it?  I'm new to sqlite and don't 
> understand the 'near'.  Is it saying that I have syntax error somewhere 
> involving '.'?
> 


Wouldn't it have been super-easy to actually show us your query? How can anyone 
divine what is wrong when no knows what your query is. You are likely missing a 
comma, or have an extra space... please resend with the actual query that 
causes the above error.

--
Puneet Kishor

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Puneet Kishor]

On Oct 7, 2011, at 11:52 AM, Simon Slavin wrote:

> 
> On 7 Oct 2011, at 5:31pm, Puneet Kishor wrote:
> 
>> assuming you have some other application level language you are using to get 
>> the data, you could stuff the result set into an array and then report the 
>> highest index of the array which might be faster than doing a second query 
>> for just the count.
> 
> I'm trying to save the resources necessary for having SQLite fetch the values 
> which the SELECT would return.  Any solution mentioned so far has not done 
> that.
> 
> To see what I'm after, imagine a use interface which asked the user "Do you 
> really want to see all 50,000 entries that that search would return ?".  If 
> this kind of search returns more than 100 records, there's no point in doing 
> it at all.  I want to know the "50,000" number while placing the minimum load 
> on the system.
> 

As a person way more knowledgable than I once stated (I believe it was Igor), 
the database can't magically know there are 50,000 entries in the table. It has 
to sometime, somewhere, somehow count it. It can't just divine it.

You could track the count of each table in a counts_table that you 
increment/decrement with TRIGGERs, and then query just that table. But that 
still wouldn't solve your specific problem of "how many queries are returned by 
a specific SELECT." Somewhere, somehow you will have to count it.

Especially, note Pavel's recent, wonderful explanation of how SQLite steps 
through the result set handing you the data row by row. It *has* to go through 
the set to know how big the set is... there is no way around it.

Unless you are making an app in a device with limited resources, who cares what 
load you put on SQLite... it won't complain.

--
Puneet Kishor.

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

Re: [sqlite] Using COUNT(*) in unusual circumstances

[Puneet Kishor]

On Oct 7, 2011, at 9:46 AM, Simon Slavin wrote:

> 
> On 7 Oct 2011, at 2:19pm, Igor Tandetnik wrote:
> 
>> SQLite has a non-standard extension whereby aliases assigned to expressions 
>> in the SELECT clause may be used in the WHERE and other clauses:
>> 
>> select 1+2 as alias from mytable where alias > 0;
> 
> Arg !
> 
> Okay, so I guess the form
> 
>> select count(*) from (
>> select ...
>> );
> 
> is necessary.  But that defeats the point of me doing it in the first place: 
> I was hoping that COUNT(*) would be less resource-hungry than the real query,



assuming you have some other application level language you are using to get 
the data, you could stuff the result set into an array and then report the 
highest index of the array which might be faster than doing a second query for 
just the count.


> and I suppose this form doesn't realise it doesn't need the values.
> 
> Bah.  Okay, I'll abandon that idea, then.
> 
> Thanks, Igor.  You saved me some hugely annoying support calls long after the 
> program was in the field.
> 
> 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] how to disable a trigger

[Mr. Puneet Kishor]

On Oct 2, 2011, at 10:29 PM, Igor Tandetnik wrote:

> Sam Carleton <scarle...@miltonstreet.com> wrote:
>> Is there any way to "disable" a trigger in sqlite?
> 
> If you have control over the trigger's definition, you could do something 
> like this:
> 
> create trigger MyTrigger on ...
> when (select enabled from TriggerControl where name='MyTrigger')
> begin
>  ...
> end;
> 
> where TriggerControl(name text, enabled integer) is a table with a row for 
> each trigger you want to manage. You can effectively turn a trigger on and 
> off with
> 
> update TriggerControl set enabled=? where name='MyTrigger';
> 


very clever.

--
Puneet Kishor

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

Re: [sqlite] how to disable a trigger

[Mr. Puneet Kishor]

On Oct 2, 2011, at 10:12 PM, Sam Carleton wrote:

> Ok, how do I list what a trigger is so that I can add it back once I want to
> "reactive" it?


You are looking to temporarily deactivate a TRIGGER, but there is no such 
mechanism. You could simply copy the code for the TRIGGER, then DROP it, and 
then add it back again when you need it. Generally the idea behind a TRIGGER is 
that it fires on the set event without any intervention. Giving the ability to 
deactivate it would make it discretional, so there isn't any SUSPEND TRIGGER 
command.


> 
> On Sun, Oct 2, 2011 at 9:07 PM, Igor Tandetnik  wrote:
> 
>> Sam Carleton  wrote:
>>> Is there any way to "disable" a trigger in sqlite?
>> 
>> DROP TRIGGER
>> --
>> Igor Tandetnik
>> 

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

Re: [sqlite] speeding up FTS4

[Puneet Kishor]

all of the below is really good advice that I shall follow over this weekend.

Many thanks.


On Sep 29, 2011, at 10:05 AM, Petite Abeille wrote:

> 
> On Sep 29, 2011, at 3:30 PM, Mr. Puneet Kishor wrote:
> 
>> Well, defeated by FTS4 for now, I will try the following approach --
> 
> [didn't follow the thread blow by blow, so apologies if this was already 
> covered and dismissed :)]
> 
> Before you jump to the deep end...
> 
> FTS tables are meant to be accessed by either their rowid or queried with a 
> match qualifier. Anything else will be rather slow, as it will result in a 
> full table scan. See section 1.4. "Simple FTS Queries" of the fine manual [1].
> 
> Looking at the DDL you posted original, you have:
> 
>   CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content); 
> 
> That uri_id is not helping anything, as you cannot really use it to lookup 
> the table (see section "1.3. Populating FTS Tables"  [2]) . You should 
> instead drop it, and simply set the rowid of your FTS table to the 
> uris.uri_id.
> 
> So:
> 
>   CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_content);  -- don't bother 
> with uri_id here, as it cannot be used for lookups
> 
> Now populate fts_uri with the content of  uris, using the same rowid for both:
> 
> insert
> into  fts_uri
>   (
>   rowid,
>   uri_content
>   )
> selecturis.uri_id,
>   uris. uris
> from  uris
> 
> Also, it would appear that you are storing your content twice: once in uris. 
> uri_content and once again in fts_uri. uri_content. That's once too many.
> 
> From uris, you can retrieve your content directly from fts_uri:
> 
> select  *
> fromuris
> join fts_uri
> on   fts_uri.rowid = uris.uri_id
> 
> No point in storing the data twice as you can retrieve the text verbatim from 
> fts_uri without much ado.
> 
> And now you can access uris directly from fts_uri as well as they share the 
> same rowid.
> 
> Now, for your search, decompose the problem:
> 
> (1) do the FTS first, assuming it's the most selective part of your query
> 
> select  fts_uri.rowid as uri_id,
>snippet(fts_uri, '', '', '', -1, 
> 64) snippet 
> fromfts_uri
> 
> where   fts_uri.uri_content match 'education,school' 
> 
> Is that slow? 
> 
> (2) Add joins one by one
> 
> select  fts_uri.rowid as uri_id,
>snippet(fts_uri, '', '', '', -1, 
> 64) snippet, 
>uris.uri as uri, 
>uris.downloaded_on as downloaded_on
> fromfts_uri
> 
> joinuris
> on  uris.uri_id = fts_uri.rowid
> 
> where   fts_uri.uri_content match 'education,school' 
> 
> Is that slow?
> 
> Repeat and rinse :)
> 
> 
> 
> 
> [1] http://www.sqlite.org/fts3.html#section_1_4
> [2] http://www.sqlite.org/fts3.html#section_1_3
> ___
> 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] speeding up FTS4

[Puneet Kishor]

On Sep 29, 2011, at 8:52 AM, Black, Michael (IS) wrote:

> Why do you think postgres would be any better?
> 

I don't. That is why I have two options.


> Have you thought about writing your own code to process the tables rather 
> than letting the database do all the work?

Well, that kinda defeats the purpose of having a database... I want the 
database to do all the work (where "all" has a sliding scale definition).

In any case, I think (again, "think"... no scientific tests here) that FTS4 
itself may not be the problem. I can see fts searches are slow, but not that 
slow. The problem is throttling the fts searches based on criteria that are 
coming from joined tables.

So, I do have my work cut out for me. I have to go through my SQL code, really 
narrow down the query that is gumming up the works, then try to eliminate that 
or code a work around.

We'll see. The database is not that large when it comes to rows (less than 45K 
rows), but it is definitely gargantuan when it comes to size (because of all 
the nonsense I have stuffed in it). I have to figure out a workaround for that 
as well.


> 
> Load your data into memory and then slog through the uris to winnow out the 
> matches?
> Probably a LOT faster than letting the db do it.
> 
> 
> 
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Mr. Puneet Kishor [punk.k...@gmail.com]
> Sent: Thursday, September 29, 2011 8:30 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] speeding up FTS4
> 
> 
> Well, defeated by FTS4 for now, I will try the following approach --
> 
> 1. drop the fts tables and rebuild them and test.
> 
> 2. if the above doesn't work, then either migrate the data to Postgres and 
> use its fts, or implement e-Swish or httpdig for full text search.
> 
> On Sep 28, 2011, at 4:35 PM, Puneet Kishor wrote:
> 
>> 
>> On Sep 28, 2011, at 4:18 PM, Black, Michael (IS) wrote:
>> 
>>> I have no idea if this would work...but...here's some more thoughts...
>>> 
>>> 
>>> 
>>> #1 How long does this take:
>>> 
>>>   select count(*) from fts_uri match 'education school';
>>> 
>>> 
>>> 
>>> #2 Create a view on uris with just what you need and use that in your join 
>>> (I'm guessing that uri_content takes up most of your database space).
>>> 
>>> 
>>> 
>>> create view v_uris as select uri_id,feed_history_id from uri;
>>> 
>>> 
>>> ..
>> 
>> 
>> 
>> 
>> I did a query on just the fts table and got the answers relatively quickly. 
>> Not instantly, but very fast compared to all the attempts so far. So, 
>> assuming that the bottleneck is the multiple JOINs to get the data for the 
>> correct project_id, I created a temp table with all that JOIN nonsense
>> 
>>   sqlite> CREATE TEMP TABLE tmp_uris AS SELECT u.uri_id, u.uri uri, 
>> u.u_downloaded_on
>>  ...> FROM projects p
>>  ...>   JOIN feeds f ON f.project_id = p.project_id
>>  ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id
>>  ...>   JOIN uris u ON fh.feed_history_id = u.feed_history_id
>>  ...> WHERE p.project_id = 3 AND u.u_downloaded_on >= 
>> p.u_project_start;
>>   CPU Time: user 16.369556 sys 81.393235
>> 
>> 
>>   sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id uri_id, u.uri uri, 
>> u.u_downloaded_on,
>>  ...>   Snippet(fts_uri, '', '', 
>> '', -1, 64) snippet
>>  ...> FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id
>>  ...> WHERE fts_uri MATCH 'education school'
>>  ...> ORDER BY u.uri_id, u_downloaded_on DESC;
>>   0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows)
>>   0|1|1|SEARCH TABLE tmp_uris AS u USING INDEX tmp_uris_uri_id 
>> (uri_id=?) (~10 rows)
>>   0|0|0|USE TEMP B-TREE FOR ORDER BY
>>   CPU Time: user 0.86 sys 0.06
>> 
>> and yet
>> 
>>   sqlite> SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on,
>>  ...>   Snippet(fts_uri, '', '', 
>> '', -1, 64) snippet
>>  ...> FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id
>>  ...> WHERE fts_uri MATCH 'education school'
>>  ...> ORDER BY u.uri_id, u_downloaded_on DESC;
>> 
>>   CPU Time: user 21.871541 sys 26.414337
>> 
>> 
>> A lot better, but simply not usable for a web application.
>> 
>> --
>> Puneet Kishor
> 
> ___
> 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] speeding up FTS4

[Mr. Puneet Kishor]

Well, defeated by FTS4 for now, I will try the following approach --

1. drop the fts tables and rebuild them and test.

2. if the above doesn't work, then either migrate the data to Postgres and use 
its fts, or implement e-Swish or httpdig for full text search.

On Sep 28, 2011, at 4:35 PM, Puneet Kishor wrote:

> 
> On Sep 28, 2011, at 4:18 PM, Black, Michael (IS) wrote:
> 
>> I have no idea if this would work...but...here's some more thoughts...
>> 
>> 
>> 
>> #1 How long does this take:
>> 
>>select count(*) from fts_uri match 'education school';
>> 
>> 
>> 
>> #2 Create a view on uris with just what you need and use that in your join 
>> (I'm guessing that uri_content takes up most of your database space).
>> 
>> 
>> 
>> create view v_uris as select uri_id,feed_history_id from uri;
>> 
>> 
>> ..
> 
> 
> 
> 
> I did a query on just the fts table and got the answers relatively quickly. 
> Not instantly, but very fast compared to all the attempts so far. So, 
> assuming that the bottleneck is the multiple JOINs to get the data for the 
> correct project_id, I created a temp table with all that JOIN nonsense
> 
>   sqlite> CREATE TEMP TABLE tmp_uris AS SELECT u.uri_id, u.uri uri, 
> u.u_downloaded_on  
>  ...> FROM projects p 
>  ...>   JOIN feeds f ON f.project_id = p.project_id 
>  ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id 
>  ...>   JOIN uris u ON fh.feed_history_id = u.feed_history_id 
>  ...> WHERE p.project_id = 3 AND u.u_downloaded_on >= 
> p.u_project_start;
>   CPU Time: user 16.369556 sys 81.393235
>   
> 
>   sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id uri_id, u.uri uri, 
> u.u_downloaded_on, 
>  ...>   Snippet(fts_uri, '', '', 
> '', -1, 64) snippet
>  ...> FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id 
>  ...> WHERE fts_uri MATCH 'education school'
>  ...> ORDER BY u.uri_id, u_downloaded_on DESC;
>   0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows)
>   0|1|1|SEARCH TABLE tmp_uris AS u USING INDEX tmp_uris_uri_id (uri_id=?) 
> (~10 rows)
>   0|0|0|USE TEMP B-TREE FOR ORDER BY
>   CPU Time: user 0.86 sys 0.06
> 
> and yet
> 
>   sqlite> SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on, 
>  ...>   Snippet(fts_uri, '', '', 
> '', -1, 64) snippet
>  ...> FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id 
>  ...> WHERE fts_uri MATCH 'education school'
>  ...> ORDER BY u.uri_id, u_downloaded_on DESC;
> 
>   CPU Time: user 21.871541 sys 26.414337
> 
> 
> A lot better, but simply not usable for a web application.
> 
> --
> Puneet Kishor

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

Re: [sqlite] speeding up FTS4

[Puneet Kishor]

On Sep 28, 2011, at 4:18 PM, Black, Michael (IS) wrote:

> I have no idea if this would work...but...here's some more thoughts...
> 
> 
> 
> #1 How long does this take:
> 
> select count(*) from fts_uri match 'education school';
> 
> 
> 
> #2 Create a view on uris with just what you need and use that in your join 
> (I'm guessing that uri_content takes up most of your database space).
> 
> 
> 
> create view v_uris as select uri_id,feed_history_id from uri;
> 
> 
> ..




I did a query on just the fts table and got the answers relatively quickly. Not 
instantly, but very fast compared to all the attempts so far. So, assuming that 
the bottleneck is the multiple JOINs to get the data for the correct 
project_id, I created a temp table with all that JOIN nonsense

sqlite> CREATE TEMP TABLE tmp_uris AS SELECT u.uri_id, u.uri uri, 
u.u_downloaded_on  
   ...> FROM projects p 
   ...>   JOIN feeds f ON f.project_id = p.project_id 
   ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id 
   ...>   JOIN uris u ON fh.feed_history_id = u.feed_history_id 
   ...> WHERE p.project_id = 3 AND u.u_downloaded_on >= 
p.u_project_start;
CPU Time: user 16.369556 sys 81.393235


sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id uri_id, u.uri uri, 
u.u_downloaded_on, 
   ...>   Snippet(fts_uri, '', '', 
'', -1, 64) snippet
   ...> FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id 
   ...> WHERE fts_uri MATCH 'education school'
   ...> ORDER BY u.uri_id, u_downloaded_on DESC;
0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows)
0|1|1|SEARCH TABLE tmp_uris AS u USING INDEX tmp_uris_uri_id (uri_id=?) 
(~10 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
CPU Time: user 0.86 sys 0.06

and yet

sqlite> SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on, 
   ...>   Snippet(fts_uri, '', '', 
'', -1, 64) snippet
   ...> FROM fts_uri f JOIN tmp_uris u ON f.uri_id = u.uri_id 
   ...> WHERE fts_uri MATCH 'education school'
   ...> ORDER BY u.uri_id, u_downloaded_on DESC;

CPU Time: user 21.871541 sys 26.414337


A lot better, but simply not usable for a web application.

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

Re: [sqlite] speeding up FTS4

[Puneet Kishor]

On Sep 28, 2011, at 2:26 PM, Roger Andersson wrote:

> On 09/28/11 21:10, Black, Michael (IS) wrote:
>> 
>> 'scuse meI was wrong (again)...I guess strftime does return an 
>> integerseems to me that belies the name as it's a mismatch to the unix 
>> function.
>> 
>> 
> ?
> SQLite version 3.7.8 2011-09-19 14:49:19
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select typeof(strftime('%s','now','localtime'));
> text
> 

Perhaps, but I have inserted that in my table where the column is INTEGER.

sqlite> SELECT typeof(u_downloaded_on) FROM uris LIMIT 1;
integer

--
Puneet Kishor

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

Re: [sqlite] speeding up FTS4

[Puneet Kishor]

On Sep 28, 2011, at 2:41 PM, Black, Michael (IS) wrote:

> What happens if you create an index on uris(feed_history_id)
> 
> 
> 

Yeah, I noticed that lacking as well.


sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id  
   ...> FROM projects p 
   ...>   JOIN feeds f ON f.project_id = p.project_id 
   ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id 
   ...>   JOIN uris u ON fh.feed_history_id = u.feed_history_id 
   ...> WHERE p.project_id = 3;
0|0|0|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|1|3|SCAN TABLE uris AS u (~46608 rows)
0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY (rowid=?) (~1 
rows)
0|3|1|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
CPU Time: user 0.79 sys 0.14



sqlite> CREATE INDEX uris_feed_history_id ON uris (feed_history_id);
CPU Time: user 12.766977 sys 82.766372


sqlite> EXPLAIN QUERY PLAN SELECT u.uri_id  
   ...> FROM projects p 
   ...>   JOIN feeds f ON f.project_id = p.project_id 
   ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id 
   ...>   JOIN uris u ON fh.feed_history_id = u.feed_history_id 
   ...> WHERE p.project_id = 3;
0|0|0|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|1|1|SCAN TABLE feeds AS f (~7 rows)
0|2|2|SEARCH TABLE feed_history AS fh USING AUTOMATIC COVERING INDEX 
(feed_id=?) (~5 rows)
0|3|3|SEARCH TABLE uris AS u USING COVERING INDEX uris_feed_history_id 
(feed_history_id=?) (~10 rows)
CPU Time: user 0.000123 sys 0.15


But, no joy.


SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on 
FROM fts_uri f 
JOIN uris u ON f.uri_id = u.uri_id 
JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id 
JOIN feeds f ON fh.feed_id = f.feed_id 
JOIN projects p ON f.project_id = p.project_id 
WHERE p.project_id = 3
AND u.u_downloaded_on >= p.u_project_start 
AND fts_uri MATCH 'education school'
ORDER BY u.uri_id, u_downloaded_on DESC;
..
CPU Time: user 28.599581 sys 108.518205




> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Puneet Kishor [punk.k...@gmail.com]
> Sent: Wednesday, September 28, 2011 2:00 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] speeding up FTS4
> 
> 
> On Sep 28, 2011, at 1:14 PM, Black, Michael (IS) wrote:
> 
>> strftime returns a text representation.  So you didn't really change 
>> anything.
>> 
> 
> 
> That's not true at all. I added u_downloaded_on (u_ for unixtime)
> 
> CREATE TABLE uris (
>uri_id INTEGER PRIMARY KEY,
>uri TEXT,
>uri_html TEXT,
>uri_content TEXT,
>downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
>feed_history_id INTEGER,
>u_downloaded_on INTEGER
> );
> 
> sqlite> SELECT u_downloaded_on FROM uris LIMIT 5;
> 1306450769
> 1306450769
> 1306450770
> 1306450774
> 1306450776
> 
> 
>> You need to use juliandays() as I said.
>> 
>> 
>> 
>> And you want a REAL number...not integer...though SQLite doesn't really care 
>> what you call it.  It's more for your own reference.
>> 
>> 
>> 
>> You just added a bunch more strings increasing the size of your 
>> database...ergo it ran slower.
>> 
>> 
> 
> 
> If I understand correctly, the *size* of the database should not matter. Or, 
> at least not matter as much. Imagine a database with one table with only one 
> row but with so much content in that row that it is 27 GB vs. a database with 
> a few million rows, each with small amount of content but together totaling 
> 27 GB.
> 
> What should matter is using the indexes correctly. In this case, my query 
> plan shows that I am hitting all the indexes. But, I think I am getting 
> closer to understanding this.
> 
> I started backward with
> 
> sqlite> SELECT p.project_id
>   ...> FROM projects p
>   ...> WHERE p.project_id = 3;
> 3
> CPU Time: user 0.80 sys 0.89
> 
> sqlite> SELECT f.feed_id, p.project_id
>   ...> FROM projects p JOIN feeds f ON f.project_id = p.project_id
>   ...> WHERE p.project_id = 3;
> ..
> CPU Time: user 0.000239 sys 0.000170
> 
> SELECT fh.feed_history_id, f.feed_id, p.project_id
>   ...> FROM projects p
>   ...>   JOIN feeds f ON f.project_id = p.project_id
>   ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id
>   ...> WHERE p.project_id = 3;
> ..
> CPU Time: user 0.008491 sys 0.008054
> 
> SELECT u.uri_id, fh.feed_history_id, f.feed_id, p.project_id
>   ...> FROM projects p
>   ...>   JOIN feeds f ON f.project_id = p.project_id
>   ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id
>   ...>   JOIN u

Re: [sqlite] speeding up FTS4

[Puneet Kishor]

On Sep 28, 2011, at 1:14 PM, Black, Michael (IS) wrote:

> strftime returns a text representation.  So you didn't really change anything.
> 


That's not true at all. I added u_downloaded_on (u_ for unixtime)

CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY, 
uri TEXT,
uri_html TEXT,
uri_content TEXT,
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
feed_history_id INTEGER, 
u_downloaded_on INTEGER
);

sqlite> SELECT u_downloaded_on FROM uris LIMIT 5;
1306450769
1306450769
1306450770
1306450774
1306450776


> You need to use juliandays() as I said.
> 
> 
> 
> And you want a REAL number...not integer...though SQLite doesn't really care 
> what you call it.  It's more for your own reference.
> 
> 
> 
> You just added a bunch more strings increasing the size of your 
> database...ergo it ran slower.
> 
> 


If I understand correctly, the *size* of the database should not matter. Or, at 
least not matter as much. Imagine a database with one table with only one row 
but with so much content in that row that it is 27 GB vs. a database with a few 
million rows, each with small amount of content but together totaling 27 GB.

What should matter is using the indexes correctly. In this case, my query plan 
shows that I am hitting all the indexes. But, I think I am getting closer to 
understanding this.

I started backward with

sqlite> SELECT p.project_id 
   ...> FROM projects p
   ...> WHERE p.project_id = 3;
3
CPU Time: user 0.80 sys 0.89

sqlite> SELECT f.feed_id, p.project_id 
   ...> FROM projects p JOIN feeds f ON f.project_id = p.project_id 
   ...> WHERE p.project_id = 3; 
..
CPU Time: user 0.000239 sys 0.000170

SELECT fh.feed_history_id, f.feed_id, p.project_id 
   ...> FROM projects p 
   ...>   JOIN feeds f ON f.project_id = p.project_id 
   ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id 
   ...> WHERE p.project_id = 3;
..
CPU Time: user 0.008491 sys 0.008054

SELECT u.uri_id, fh.feed_history_id, f.feed_id, p.project_id 
   ...> FROM projects p 
   ...>   JOIN feeds f ON f.project_id = p.project_id 
   ...>   JOIN feed_history fh ON f.feed_id = fh.feed_id 
   ...>   JOIN uris u ON fh.feed_history_id = u.feed_history_id 
   ...> WHERE p.project_id = 3;


BOOM! Adding that last table makes my query way too slow.

Taking out the uris table and querying only the fts table

sqlite> SELECT uri_id 
   ...> FROM fts_uri 
   ...> WHERE fts_uri MATCH 'education school';


starts producing the results immediately, but gives me back way too many 
matches. I am now getting all the matches, but I want only the matches for 
project id = 3 or matches throttled by u_downloaded_on (both of which are 
achievable only via a JOIN with uris table).


> 
> Michael D. Black
> 
> Senior Scientist
> 
> NG Information Systems
> 
> Advanced Analytics Directorate
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Puneet Kishor [punk.k...@gmail.com]
> Sent: Wednesday, September 28, 2011 12:44 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] speeding up FTS4
> 
> 
> On Sep 28, 2011, at 11:00 AM, Black, Michael (IS) wrote:
> 
>> Your change to numeric date/time may not take a long as you think.
>> 
>> 
>> 
> 
> 
> Took an hour and a half.
> 
> Step 1: Alter all tables with datetime columns, converting those columns to 
> integer;
> 
> Step 2: Update all tables setting new datetime columns to unixtime
> 
> UPDATE table SET new_column = strftime('%s', old_column);
> 
> Step 3: CREATE INDEX uris_downloaded_on ON uris (u_downloaded_on);
> 
> Step 4: Run the following query
> 
>SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on
>FROM fts_uri f
>JOIN uris u ON f.uri_id = u.uri_id
>JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id
>JOIN feeds f ON fh.feed_id = f.feed_id
>JOIN projects p ON f.project_id = p.project_id
>WHERE p.project_id = 3
>AND u.u_downloaded_on >= p.u_project_start
>AND fts_uri MATCH 'education school'
>ORDER BY u.uri_id, u_downloaded_on DESC;
> 
> Terrible time.
>>> CPU Time: user 27.584849 sys 115.219293
> 
> Step 5: EXPLAIN QUERY PLAN (above SELECT query)
> 
>0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows)
>0|1|1|SEARCH TABLE uris AS u USING INTEGER PRIMARY KEY (rowid=?) (~1 
> rows)
>0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY 
> (rowid=?) (~1 rows)
>0|3|3|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 
> rows)
>0|4|4|SEAR

Re: [sqlite] speeding up FTS4

[Puneet Kishor]

On Sep 28, 2011, at 11:00 AM, Black, Michael (IS) wrote:

> Your change to numeric date/time may not take a long as you think.
> 
> 
> 


Took an hour and a half.

Step 1: Alter all tables with datetime columns, converting those columns to 
integer;

Step 2: Update all tables setting new datetime columns to unixtime

UPDATE table SET new_column = strftime('%s', old_column);

Step 3: CREATE INDEX uris_downloaded_on ON uris (u_downloaded_on);

Step 4: Run the following query

SELECT u.uri_id uri_id, u.uri uri, u.u_downloaded_on 
FROM fts_uri f 
JOIN uris u ON f.uri_id = u.uri_id 
JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id 
JOIN feeds f ON fh.feed_id = f.feed_id 
JOIN projects p ON f.project_id = p.project_id 
WHERE p.project_id = 3
AND u.u_downloaded_on >= p.u_project_start 
AND fts_uri MATCH 'education school'
ORDER BY u.uri_id, u_downloaded_on DESC;

Terrible time.
>> CPU Time: user 27.584849 sys 115.219293

Step 5: EXPLAIN QUERY PLAN (above SELECT query)

0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows)
0|1|1|SEARCH TABLE uris AS u USING INTEGER PRIMARY KEY (rowid=?) (~1 
rows)
0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY 
(rowid=?) (~1 rows)
0|3|3|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 
rows)
0|4|4|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) 
(~1 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
CPU Time: user 0.99 sys 0.08

Step 6: ANALYZE;

Step 7: Run the above SELECT query again

Terrible time.
>> CPU Time: user 27.703538 sys 116.684390



This train is going nowhere. The times are actually worse than they were when I 
was using a non-text column for date time.

By the way, have tried this on two machines -- the previous one was a dual-Xeon 
Xserve with 12 GB RAM. The current machine of choice is the top of the line 
iMac (quad core 3.4 GHz Intel Core i7) with 12 GB RAM and a 7200 RPM SATA drive.


> drop any indexes on project_start and downloaded_on;
> 
> 
> 
> update projects set project_start=julianday(project_start);
> 
> update uris set downloaded_on=julianday(downloaded_on);
> 
> 
> 
> Recreate indexes.
> 
> 
> 
> Modify your code to insert julianday('now','localtime') instead of taking the 
> default current_timestamp.
> 
> 
> 
> I wasn't really aware before the the datetime functions stored as text all 
> the time...that's bad for searches...
> 
> 
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> NG Information Systems
> 
> Advanced Analytics Directorate
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Puneet Kishor [punk.k...@gmail.com]
> Sent: Wednesday, September 28, 2011 9:48 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] EXT : speeding up FTS4
> 
> 
> On Sep 28, 2011, at 9:44 AM, Simon Slavin wrote:
> 
>> 
>> On 28 Sep 2011, at 3:41pm, Puneet Kishor wrote:
>> 
>>>  WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start)
>> 
>> Why are you doing 'Datetime' here ?  Not only does the conversion take time, 
>> but it means you can't usefully index either of those two columns.
>> 
>> Can you instead store your stamps in a format which is readily sortable ?  
>> Either in text form or as julian days.
> 
> 
> Could I? Sure, if I had known better. Should I? I would be happy to create a 
> new column, convert the values to julian days, and try that, but on a 27 GB 
> db, that would take a bit of a while.
> 
> But, if I understood [http://www.sqlite.org/datatype3.html] correctly, there 
> really is no such thing as DATETIME value. Internally, it is stored as TEXT 
> anyway.
> 
> 

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

Re: [sqlite] EXT : speeding up FTS4

[Puneet Kishor]

On Sep 28, 2011, at 9:44 AM, Simon Slavin wrote:

> 
> On 28 Sep 2011, at 3:41pm, Puneet Kishor wrote:
> 
>>   WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start) 
> 
> Why are you doing 'Datetime' here ?  Not only does the conversion take time, 
> but it means you can't usefully index either of those two columns.
> 
> Can you instead store your stamps in a format which is readily sortable ?  
> Either in text form or as julian days.


Could I? Sure, if I had known better. Should I? I would be happy to create a 
new column, convert the values to julian days, and try that, but on a 27 GB db, 
that would take a bit of a while.

But, if I understood [http://www.sqlite.org/datatype3.html] correctly, there 
really is no such thing as DATETIME value. Internally, it is stored as TEXT 
anyway.


> 
> 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] EXT : speeding up FTS4

[Puneet Kishor]

On Sep 28, 2011, at 8:02 AM, Black, Michael (IS) wrote:

> Have you done "ANALYZE"?  That might help.
> 
> Also...try to arrange your joins based on record count (both high-to-low and 
> low-to-high) and see what difference it makes.
> 
> Since you have only one WHERE clause I'm guessing having project_ids as the 
> first join makes sense.
> 
> 


So, I ran ANALYZE. Then, with the query as is, I got

CPU Time: user 24.742481 sys 79.120486

If I flip the WHERE clause to

WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start) 
AND p.project_id = 3
AND fts_uri MATCH 'education school'

I still get

CPU Time: user 24.726792 sys 79.240780



Yesterday, in desperation, I tried to pare down my fts_uri table which brought 
the file size to 17 GB, but ended up creating a "malformed disk image" whatever 
the heck that means. Thankfully I had a backup (clever boy).

So, I am now back with a 27 GB file, and a query that takes forever thereby 
locking up my web app.


> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Puneet Kishor [punk.k...@gmail.com]
> Sent: Tuesday, September 27, 2011 5:46 PM
> To: General Discussion of SQLite Database
> Subject: EXT :[sqlite] speeding up FTS4
> 
> 
> I have
> 
>CREATE TABLE uris (
>uri_id INTEGER PRIMARY KEY,
>uri TEXT,
>uri_content TEXT,
>downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
>feed_history_id INTEGER
>);
> 
> with 46608 rows
> 
>CREATE TABLE feed_history (
>feed_history_id INTEGER PRIMARY KEY,
>feed_id INTEGER,
>scanned_on DATETIME DEFAULT CURRENT_TIMESTAMP
>);
> 
> with 3276 rows
> 
>CREATE TABLE feeds (feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id 
> INTEGER);
> 
> with 79 rows
> 
>CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT);
> 
> with 3 rows
> 
>CREATE INDEX idx_uris_downloaded_on ON uris (downloaded_on);
>CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content);
> 
> The database file is about 27 GB.
> 
> 
> The following query takes (CPU Time: user 23.952698 sys 73.188765) returning 
> 46608 rows
> 
>SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on,
>Snippet(fts_uri, '', '', '', -1, 
> 64) snippet
>FROM fts_uri f
>JOIN uris u ON f.uri_id = u.uri_id
>JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id
>JOIN feeds f ON fh.feed_id = f.feed_id
>JOIN projects p ON f.project_id = p.project_id
>WHERE p.project_id = 3
>AND Datetime(u.downloaded_on) >= Datetime(p.project_start)
>AND fts_uri MATCH 'education,school'
>ORDER BY u.uri_id, downloaded_on DESC;
> 
> 
> The EXPLAIN QUERY PLAN for the above query tells me
> 
>0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows)
>0|1|1|SEARCH TABLE uris AS u USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY (rowid=?) 
> (~1 rows)
>0|3|3|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|4|4|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 
> rows)
>0|0|0|USE TEMP B-TREE FOR ORDER BY
> 
> 
> Is there anything I can do to speed this up?
> 
> --
> Puneet Kishor

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

Re: [sqlite] speeding up FTS4

[Puneet Kishor]

On Sep 28, 2011, at 8:19 AM, Black, Michael (IS) wrote:

> P.S.  Your projects table is missing project_start.  So apparently these 
> aren't the real create statements you are using.
> 
> 
> 
> 


Sorry, I think that is the only table from which I snipped off information to 
make the post brief(er). These are the actual statements. The projects table is 

CREATE TABLE projects (
project_id INTEGER PRIMARY KEY, 
project_name TEXT,
website_tags TEXT,
twitter_tags TEXT,
flickr_tags TEXT, 
project_start DATETIME, 
project_en DATETIME, 
project_end DATETIME
);

> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Puneet Kishor [punk.k...@gmail.com]
> Sent: Tuesday, September 27, 2011 5:46 PM
> To: General Discussion of SQLite Database
> Subject: EXT :[sqlite] speeding up FTS4
> 
> I have
> 
>CREATE TABLE uris (
>uri_id INTEGER PRIMARY KEY,
>uri TEXT,
>uri_content TEXT,
>downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
>feed_history_id INTEGER
>);
> 
> with 46608 rows
> 
>CREATE TABLE feed_history (
>feed_history_id INTEGER PRIMARY KEY,
>feed_id INTEGER,
>scanned_on DATETIME DEFAULT CURRENT_TIMESTAMP
>);
> 
> with 3276 rows
> 
>CREATE TABLE feeds (feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id 
> INTEGER);
> 
> with 79 rows
> 
>CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT);
> 
> with 3 rows
> 
>CREATE INDEX idx_uris_downloaded_on ON uris (downloaded_on);
>CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content);
> 
> The database file is about 27 GB.
> 
> 
> The following query takes (CPU Time: user 23.952698 sys 73.188765) returning 
> 46608 rows
> 
>SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on,
>Snippet(fts_uri, '', '', '', -1, 
> 64) snippet
>FROM fts_uri f
>JOIN uris u ON f.uri_id = u.uri_id
>JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id
>JOIN feeds f ON fh.feed_id = f.feed_id
>JOIN projects p ON f.project_id = p.project_id
>WHERE p.project_id = 3
>AND Datetime(u.downloaded_on) >= Datetime(p.project_start)
>AND fts_uri MATCH 'education,school'
>ORDER BY u.uri_id, downloaded_on DESC;
> 
> 
> The EXPLAIN QUERY PLAN for the above query tells me
> 
>0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows)
>0|1|1|SEARCH TABLE uris AS u USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY (rowid=?) 
> (~1 rows)
>0|3|3|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
>0|4|4|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 
> rows)
>0|0|0|USE TEMP B-TREE FOR ORDER BY
> 
> 
> Is there anything I can do to speed this up?
> 
> --
> Puneet Kishor
> ___
> 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] speeding up FTS4

[Puneet Kishor]

I have 

CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY, 
uri TEXT,
uri_content TEXT,
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
feed_history_id INTEGER
);

with 46608 rows

CREATE TABLE feed_history (
feed_history_id INTEGER PRIMARY KEY,
feed_id INTEGER,
scanned_on DATETIME DEFAULT CURRENT_TIMESTAMP
);

with 3276 rows

CREATE TABLE feeds (feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id 
INTEGER);

with 79 rows

CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT);

with 3 rows

CREATE INDEX idx_uris_downloaded_on ON uris (downloaded_on);
CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content);

The database file is about 27 GB.


The following query takes (CPU Time: user 23.952698 sys 73.188765) returning 
46608 rows

SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on, 
Snippet(fts_uri, '', '', '', -1, 
64) snippet 
FROM fts_uri f 
JOIN uris u ON f.uri_id = u.uri_id 
JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id 
JOIN feeds f ON fh.feed_id = f.feed_id 
JOIN projects p ON f.project_id = p.project_id 
WHERE p.project_id = 3
AND Datetime(u.downloaded_on) >= Datetime(p.project_start) 
AND fts_uri MATCH 'education,school' 
ORDER BY u.uri_id, downloaded_on DESC;


The EXPLAIN QUERY PLAN for the above query tells me

0|0|0|SCAN TABLE fts_uri AS f VIRTUAL TABLE INDEX 4: (~0 rows)
0|1|1|SEARCH TABLE uris AS u USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|2|2|SEARCH TABLE feed_history AS fh USING INTEGER PRIMARY KEY (rowid=?) 
(~1 rows)
0|3|3|SEARCH TABLE feeds AS f USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|4|4|SEARCH TABLE projects AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 
rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY


Is there anything I can do to speed this up?

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

Re: [sqlite] new column

[Mr. Puneet Kishor]

On Sep 25, 2011, at 10:53 PM, 守株待兔 wrote:

> there are tow column x1,x2 in table t ,all real
> alter table  t  add  column  c1 real;
> select  x1+x2 as  c1 from t;
> there ara output in my screen,but in the database,
> c1  column has no value,how can i make the value of c1 column =x1+x2?

UPDATE t SET c1 = x1 + x2;

That said, you have a basic misunderstanding of SQL, so some tutorials, many 
freely available on the internet, would help.

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Mr. Puneet Kishor]

On Sep 17, 2011, at 1:06 PM, Simon Slavin wrote:

> 
> On 17 Sep 2011, at 6:42pm, Petite Abeille wrote:
> 
>> On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote:
>> 
>>> As was clear from my post, I was referring to SQL standards.  What various 
>>> implementation vendors choose to do is up to them.  But the multi-spec 
>>> syntax referred to in the OP is not in any SQL standard I've seen.
>> 
>> In BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99), under 
>> contextually typed row value expression list:
>> 
>> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list
> 
>  ::=|  | 
> 
> 
> ? Which one, and where is the expansion that allows for multiple sets of 
> brackets after "VALUES" ?
> 

All this chattering among us doesn't really matter (other than for academic 
purposes). All that matters is whether or not Richard and co. deem it worth 
including as a capability in factory provided SQLite. While I can't attest to 
it, there are probably instances where SQLite deviates from the so called 
standard ("the problem with standards is there are plenty of them" and all 
that). For me, if Pg does it, the SQLite could do it if those who make SQLite 
could be convinced of its usefulness.

For me, I don't care either way. I am glad Pg has it because I am trying to 
convert MySQL data to Pg. While I am failing to do so painlessly for the most 
part, a few of the (simpler) tables convert fine because both MySQL and Pg 
support multi-line INSERTs.

Puneet.

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Mr. Puneet Kishor]

On Sep 17, 2011, at 10:33 AM, Simon Slavin wrote:

> 
> On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote:
> 
>> The multiline INSERT capability may not be a SQL standard, but it is not 
>> only highly convenient, it is also supported by Pg, the ostensible role 
>> model and inspiration for SQLite. Not that I particularly care either way 
>> because I almost always use Perl to deal with Pg and SQLite, but it 
>> would/might be nice to have for some. Perhaps those folks should continue to 
>> ask Richard nicely instead of seeming to "demand" it on the basis of some 
>> political claim.
> 
> How would you support multiple INSERTs with binding ?  



I wouldn't. Multiple INSERT statement seems to be better suited for large scale 
dump/restore, or even for manual entry, but without binding.

Essentially, if I had my druthers, I would support whatever Pg supports and be 
done with it. For most part SQLite seems to follow "good for Pg good for the 
gander" philosophy.



> Given a statement like
> 
>> INSERT INTO table(digit,dialpadstr) VALUES
>> (2,'abc'),(3,'def'),(4,'ghi'),(5,'jkl'),(6,'mno'),(7,'pqrs'),(8,'tuv'),(9,'wxyz');
> 
> would you expect 16 binding values ?
> 
> Simon.

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

Re: [sqlite] INSERT syntax missing key SQL syntax

[Mr. Puneet Kishor]

On Sep 17, 2011, at 10:11 AM, Igor Tandetnik wrote:

> Jim Michaels 
> <jimm-VsnNql4zhRrV7NJZ79vff+jYdJvx94icpqFO/160wmvqt0dzr+a...@public.gmane.org>
>  wrote:
>> INSERT is supposed to handle multiple rows for VALUES.
> 
> Supposed by whom? What is the basis for this claim?

The multiline INSERT capability may not be a SQL standard, but it is not only 
highly convenient, it is also supported by Pg, the ostensible role model and 
inspiration for SQLite. Not that I particularly care either way because I 
almost always use Perl to deal with Pg and SQLite, but it would/might be nice 
to have for some. Perhaps those folks should continue to ask Richard nicely 
instead of seeming to "demand" it on the basis of some political claim.

--
Puneet Kishor

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

Re: [sqlite] Does coalesce terminate early?

[Puneet Kishor]

On Sep 15, 2011, at 11:00 AM, Sam Carleton wrote:

> On Thu, Sep 15, 2011 at 10:05 AM, Simon Slavin  wrote:
>> 
>> Documentation for COALESCE is here:
>> 
>> http://www.sqlite.org/lang_corefunc.html
>> 
>> It does not say whether it does short-circuit evaluation but the description 
>> does imply testing one by one, rather than evaluating all the conditions 
>> first.
> 
> Simon,
> 
> I don't mean to be difficult, but I simply don't get any indication of
> how exactly COALESCE actually functions from this description:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. "


While your suggested documentation won't harm, and will likely help, actually 
the above does suggest to me a short-circuit-ish kind of logic from the 
assertion that "The coalesce() function returns a copy of its first non-NULL 
argument."

On the other hand, yeah, I am all for potentially redundant documentation if it 
can help.

> 
> I get there has to be at least two arguments, but no where do I see
> where it states it only executes an argument if all the previous one
> fail.  Now it would be crystal clear if it said something like:
> 
> "coalesce(X,Y,...)The coalesce() function returns a copy of its first
> non-NULL argument, or NULL if all arguments are NULL. Coalesce() must
> be at least 2 arguments. Coalesce() does use short-circuit
> evaluation."
> 
> Sam
> ___
> 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] help with a complicated join of two tables

[Mr . Puneet Kishor]

On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote:

> Mr. Puneet Kishor <punk.k...@gmail.com> wrote:
>>>> The table geo can also have rows with min_age = max_age. I want a result 
>>>> set with geo.id, min_age, max_age, age_bottom, age_top,
>>>> name, color like so:
>>>> 
>>>> - every row should be for one and only one geo record. I have 39K rows in 
>>>> "geo" table, so the result set should have 39K rows.
>>>> 
>>>> - when min_age = max_age, list the corresponding intervals.name and color
>>>> 
>>>> - when min_age != max_age, find the interval with the smallest different 
>>>> between age_bottom and age_top that would span min_age
>>>> and max_age of geo. In other words, the interval whose age_bottom is 
>>>> bigger than the age_bottom of the max_age and whose age_top
>>>> is smaller than the age_top of the min_age.
> 
> Something like this:
> 
> select geo.id, min_age, max_age, age_bottom, age_top, name, color
> from geo left join intervals i on i.id = (
>select id from intervals
>where age_bottom >=
>(select age_bottom from intervals where name = geo.max_age)
>and age_top <= 
>(select age_top from intervals where name = geo.min_age)
>order by (age_bottom - age_top) limit 1
> );
> 


Thanks Igor. The above does work and produces the correct result. The query 
speed, however, is pretty slow ~ 75 seconds. So, I created indexes on 
intervals.name, geo.max_age, and geo.min_age, and that brought the query time 
to ~ 11 seconds. Still too slow. So, I created an intermediate table to hold 
the results, and the speed is more satisfactory.

Many thanks again.

Puneet.

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

Re: [sqlite] help with a complicated join of two tables

[Mr. Puneet Kishor]

On Sep 11, 2011, at 9:58 PM, Igor Tandetnik wrote:

> Mr. Puneet Kishor <punk.k...@gmail.com> wrote:
>> geo table: 39K rows
>> id max_age min_age
>> --- --- 
>> 1 Holocene Holocene
>> 5 Cambrian Silurian
>> 12 Cambrian Ordovician
>> 229 Cretaceous Quaternary
>> 
>> intervals table: ~450 rows
>> id age_bottom age_top name color
>> --- --  --- --- ---
>> 3 0.0117 0. Holocene #FEF2E0
>> 105 443.7000 416. Silurian #B3E1B6
>> 112 488.3000 443.7000 Ordovician #009270
>> 421 2.5880 0. Quaternary #F9F97F
>> 122 542. 488.3000 Cambrian #7FA056
>> 33 145.5000 65.5000 Cretaceous #7FC64E
>> 
>> Keep in mind, max_age is older than min_age, and age_bottom is older than 
>> age_top.
>> 
>> The table geo can also have rows with min_age = max_age. I want a result set 
>> with geo.id, min_age, max_age, age_bottom, age_top,
>> name, color like so: 
>> 
>> - every row should be for one and only one geo record. I have 39K rows in 
>> "geo" table, so the result set should have 39K rows.
>> 
>> - when min_age = max_age, list the corresponding intervals.name and color
>> 
>> - when min_age != max_age, find the interval with the smallest different 
>> between age_bottom and age_top that would span min_age
>> and max_age of geo. In other words, the interval whose age_bottom is bigger 
>> than the age_bottom of the max_age and whose age_top
>> is smaller than the age_top of the min_age.  
> 
> I'm not sure I understand. Let's take geo.id = 5, max_age=Cambrian, 
> min_age=Silurian. You say you want a record whose age_bottom is greater than 
> that corresponding to Cambrian, that is 542.; and whose age_top is 
> smaller than that corresponding to Silurian, or 416.. I don't seem to see 
> any such record in your example.


Hi Igor, I muddied the issue by providing limited sample data that doesn't have 
an answer that fits my needs. Needless to say, in the complete tables there 
would be answers. My (slightly simplified) query below shows the kind of result 
I want

SELECT g.id, max_age, min_age, Min(i.age_bottom - i.age_top) age_range, 
i.interval_name, i.interval_color
FROM geo g 
 LEFT JOIN intervals i_max ON g.max_age = i_max.interval_name 
 LEFT JOIN intervals i_min ON g.min_age = i_min.interval_name
 JOIN intervals i ON 
i.age_bottom >= i_max.age_bottom AND 
i.age_top <= i_min.age_top 
GROUP BY g.gid, g.max_age, g.min_age, i.interval_name, i.interval_color
ORDER BY g.gid, age_range;

produces the following ---

g.idmax_age min_age age_range   interval_name   
interval_color
--- --- --- --- --- 
--
1   Paleozoic   Paleozoic   291.Paleozoic   #99C08D
1   Paleozoic   Paleozoic   542.Phanerozoic #9AD9DD
4   Precambrian Precambrian 3458.   Precambrian #F04370
5   CambrianSilurian291.Paleozoic   #99C08D
5   CambrianSilurian542.Phanerozoic #9AD9DD
6   SilurianSilurian27.7000 Silurian#B3E1B6
6   SilurianSilurian291.Paleozoic   #99C08D
..
94K+ rows

What I want from the above table is only the first row of each g.id group 
because that has the interval that has the smallest "age_range" that spans the 
"max_age" and "min_age"

Sorry, I am not able to articulate this more clearly, but I hope the above 
example makes things clearer.

Puneet.

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

[sqlite] help with a complicated join of two tables

[Mr. Puneet Kishor]

Apologies in advance for a terrible subject line -- I didn't know quite how to 
phrase it better.

I have the following two tables (with sample data)

CREATE TABLE geo (
id INTEGER PRIMARY KEY,
max_age TEXT,
min_age TEXT
);

geo table: 39K rows
id  max_age min_age
--- --- 
1   HoloceneHolocene
5   CambrianSilurian
12  CambrianOrdovician
229 Cretaceous  Quaternary

CREATE TABLE intervals (
id INTEGER PRIMARY KEY,
age_bottom REAL,
age_top REAL,
name TEXT,
color TEXT
);

intervals table: ~450 rows
id  age_bottom  age_top namecolor
--- --  --- --- ---
3   0.0117  0.  Holocene#FEF2E0
105 443.7000416.Silurian#B3E1B6
112 488.3000443.7000Ordovician  #009270
421 2.5880  0.  Quaternary  #F9F97F
122 542.488.3000Cambrian#7FA056
33  145.500065.5000 Cretaceous  #7FC64E

Keep in mind, max_age is older than min_age, and age_bottom is older than 
age_top.

The table geo can also have rows with min_age = max_age. I want a result set 
with geo.id, min_age, max_age, age_bottom, age_top, name, color like so: 

- every row should be for one and only one geo record. I have 39K rows in "geo" 
table, so the result set should have 39K rows.

- when min_age = max_age, list the corresponding intervals.name and color

- when min_age != max_age, find the interval with the smallest different 
between age_bottom and age_top that would span min_age and max_age of geo. In 
other words, the interval whose age_bottom is bigger than the age_bottom of the 
max_age and whose age_top is smaller than the age_top of the min_age.

I've gotten so far

SELECT g.id, max_age, min_age, 
i.age_bottom, i.age_top, i.interval_name, i.interval_color
FROM geo g 
  LEFT JOIN intervals i_max ON g.max_age = i_max.interval_name 
  LEFT JOIN intervals i_min ON g.min_age = i_min.interval_name
  JOIN intervals i ON 
i.age_bottom >= i_max.age_bottom AND 
i.age_top <= i_min.age_top 
WHERE g.id = ?
ORDER BY i.age_bottom - i.age_top
LIMIT 1;

The above query finds the correct values for a single g.id, but I want a result 
set with all the rows.

Suggestions?

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

Re: [sqlite] PRAGMA user_version

[Mr. Puneet Kishor]

On Sep 3, 2011, at 8:21 PM, Walter wrote:

> 
> On 9/2/2011 11:02 PM, Walter wrote:
> 
>> >  Is there any way to get the user_version from an Attached database
>>   
> PRAGMA attachedName.user_version;
> 
> -- Igor Tandetnik Thank you Igor I had the database name but did not think of 
> the the dot in between. Perhaps some one could update the documentation to 
> show this Cheers everyone Walter
> 


it is a pretty common SQL convention, using periods to separate the 
database_from.schema_from.table_from.column

You don't have to specify the left-side qualifier if the entity on the right of 
the period can be located unambiguously.

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

[sqlite] speeding up an fts query

[Mr. Puneet Kishor]

I have the following schema (slightly simplified for this post)

CREATE TABLE projects (
project_id INTEGER PRIMARY KEY, 
project_start DATETIME
);
CREATE TABLE feeds (
feed_id INTEGER PRIMARY KEY, 
feed_uri TEXT, 
project_id INTEGER
);
CREATE TABLE feed_history (
feed_history_id INTEGER PRIMARY KEY,
feed_id INTEGER
);
CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY, 
uri_content TEXT,
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP,
feed_history_id INTEGER
);
CREATE VIRTUAL TABLE fts_uri USING fts4 (uri_id, uri_content);

The db file is about 3 GB, with 79 entries in the `feeds` table and 6847 
entries in the `uris` table. The following query takes way too long --

SELECT u.uri_id uri_id, u.uri uri, u.downloaded_on  
FROM fts_uri f 
JOIN uris u ON f.uri_id = u.uri_id 
JOIN feed_history fh ON u.feed_history_id = fh.feed_history_id 
JOIN feeds f ON f.feed_id = f.feed_id 
JOIN projects p ON f.project_id = p.project_id 
WHERE p.project_id = 3
AND Datetime(u.downloaded_on) >= Datetime(p.project_start) 
AND fts_uri MATCH ? 
ORDER BY u.uri_id, downloaded_on DESC;

EXPLAIN on the above query is as below, but I don't know how to read EXPLAIN's 
output. Could one of you suggest on what index I might have to make/use to 
speed up the query? 

0|Trace|0|0|0||00|
1|OpenEphemeral|5|4|0|keyinfo(2,BINARY,-BINARY)|00|
2|Integer|3|1|0||00|
3|Goto|0|58|0||00|
4|VOpen|0|0|0|vtab:7FF44A407B38:10D01E5C0|00|
5|OpenRead|1|9|0|6|00|
6|OpenRead|2|6|0|0|00|
7|OpenRead|4|2|0|6|00|
8|OpenRead|3|3|0|3|00|
9|String8|0|4|0|education|00|
10|Integer|4|2|0||00|
11|Integer|1|3|0||00|
12|VFilter|0|43|2||00|
13|VColumn|0|0|6||00|
14|MustBeInt|6|42|0||00|
15|NotExists|1|42|6||00|
16|Column|1|5|7||00|
17|MustBeInt|7|42|0||00|
18|NotExists|2|42|7||00|
19|MustBeInt|1|42|0||00|
20|NotExists|4|42|1||00|
21|Column|1|4|2||00|
22|Function|0|2|9|datetime(-1)|01|
23|Column|4|5|3||00|
24|Function|0|3|10|datetime(-1)|01|
25|Lt|10|42|9||6a|
26|Rewind|3|42|0||00|
27|Rowid|3|8|0||00|
28|Ne|8|41|8||6b|
29|Column|3|2|10||00|
30|Ne|1|41|10|collseq(BINARY)|6b|
31|Rowid|1|11|0||00|
32|Column|1|1|12||00|
33|Column|1|4|13||00|
34|MakeRecord|11|3|10||00|
35|Rowid|1|14|0||00|
36|Column|1|4|15||00|
37|Sequence|5|16|0||00|
38|Move|10|17|1||00|
39|MakeRecord|14|4|8||00|
40|IdxInsert|5|8|0||00|
41|Next|3|27|0||01|
42|VNext|0|13|0||00|
43|Close|0|0|0||00|
44|Close|1|0|0||00|
45|Close|2|0|0||00|
46|Close|4|0|0||00|
47|Close|3|0|0||00|
48|OpenPseudo|6|10|3||00|
49|Sort|5|56|0||00|
50|Column|5|3|10||00|
51|Column|6|0|11||20|
52|Column|6|1|12||00|
53|Column|6|2|13||00|
54|ResultRow|11|3|0||00|
55|Next|5|50|0||00|
56|Close|6|0|0||00|
57|Halt|0|0|0||00|
58|Transaction|0|0|0||00|
59|VerifyCookie|0|21|0||00|
60|TableLock|0|9|0|uris|00|
61|TableLock|0|6|0|feed_history|00|
62|TableLock|0|2|0|projects|00|
63|TableLock|0|3|0|feeds|00|
64|Goto|0|4|0||00|
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it okay to use SQLite + PHP for Library Software?

[Mr. Puneet Kishor]

On Aug 16, 2011, at 1:23 AM, Darren Duncan wrote:

> Raouf Athar wrote:
>> I have to develop a Library Management System using *PHP* for a medium sized
>> college library. The library has about 5,000 members and 50,000 books. On an
>> average, about 500 members will look for books and will be issued books on
>> daily basis.
>> 
>> *Kindly let me know if it is okay to use SQLite?**Kindly add a few words in
>> support of your answer.*
>> *Can you give me links/ references of a few applications supported by
>> SQLite?*
> 
> Have you looked at existing projects before starting a new one?
> 
> Look at "Evergreen", http://open-ils.org/ which is an open source project 
> developed by and for libraries, and that has been used in production for a 
> few 
> years now in many libraries.
> 
> You should adapt this or modify it to meet your needs rather than start a new 
> one, unless you can justify otherwise.
> 

Yeah, while SQLite can be used to happily power your library system, try 
finding an existing open source solution, adapting it, making it better, and 
contributing your enhancements back for everyone to benefit. Consider 
"Evergreen." Also look at bibapp.org, and I am sure there are many other.



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

Re: [sqlite] Will SQLite supports UnQL?

[Mr. Puneet Kishor]

On Jul 31, 2011, at 6:04 AM, Roger Binns wrote:

> A different JSON database server product used 4GB and 6
> minutes and I never bothered optimising for it.  Perhaps more shocking is
> that the other product would usually answer unindexed queries faster than
> CouchDB did with indices, mostly helped by this database fitting entirely in
> memory.

could you please tell what this other "JSON database server product" was?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple schema design help

[Mr. Puneet Kishor]

On Jun 29, 2011, at 10:35 AM, Ian Hardingham wrote:

> Hey guys.
> 
> I have an existing table, the matchTable, where each entry holds a lot 
> of information about a "match".
> 
> I am adding a tournament system, and each match will either be in a 
> tournament or not in a tournament.
> 
> Should I add a "tournamentID" column to matchTable?  Or should I create 
> a new "tournamentMembershipTable" which holds a simple relationship 
> between matchid and tournament id?




If each "match" will belong to only one "tournament" while each "tournament" 
may relate to more than one "match" then create a "tournatmentID" in the 
"match" table. If you leave that NULL or, say, 0, then that match will not be 
in any tournament, else will be in the tournament with that tournamentID.

On the other hand, if each match may relate to zero or more tournaments, and 
each tournament may relate to zero or more matches, then create a separate 
tournamentMembershipTable.


> 
> Thanks,
> Ian
> ___
> 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] Compute percentage?

[Mr. Puneet Kishor]

On Jun 29, 2011, at 6:53 AM, Roger Andersson wrote:

>  On 06/29/11 12:34 PM, Gilles Ganault wrote:
>> Thanks, that worked:
>> SELECT COUNT(*) FROM people;
>> 400599
>> 
>> SELECT COUNT(*) FROM people WHERE zip="12345";
>> 12521
>> 
>> SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) FROM people
>> WHERE zip="12345";
>> 3
>> 
>> Is it possible to display the number with decimals instead of an
>> integer?
> SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM 
> people),2) FROM people WHERE zip="12345";

SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) * 1.00 AS percentage 
FROM people 
WHERE zip="12345";

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

Re: [sqlite] Selecting indexes to use & NOT INDEXED

[Mr. Puneet Kishor]

On Jun 27, 2011, at 11:18 AM, Mohit Sindhwani wrote:

> select * FROM Objects, Objects_Index
> WHERE Objects.id = Objects_Index.id
> AND minx <= 668632 + 250 AND maxx >= 668632 - 250
> AND  miny <= 1518661 + 250 AND maxy >= 1518661 - 250
> AND CAT=25;
> 
> Doing an explain query plan revealed that SQlite was scanning Objects 
> with the IDX_OBJ_CAT first and then using the R-Tree.  Without 
> restricting by CAT, we found the query was much faster and was using 
> only the virtual R-Tree index.
> 
> We wanted to get SQLite to use the R-Tree first, so we tried a few 
> different things - eventually, we killed the IDX_OBJ_CAT index and the 
> query became (yes, wait for it) almost 100x faster!


Try something like

SELECT * 
FROM (
SELECT * FROM Objects, Objects_Index
WHERE Objects.id = Objects_Index.id
AND minx <= 668632 + 250 AND maxx >= 668632 - 250
AND  miny <= 1518661 + 250 AND maxy >= 1518661 - 250
)
WHERE CAT=25;

You might have to prefix the columns with the appropriate table names (I can't 
tell which table cat and the bounds are coming from). If the bounds are a part 
of the Objects table, you could try

SELECT * 
FROM (
SELECT * FROM Objects 
WHERE  minx <= 668632 + 250 AND maxx >= 668632 - 250
AND  miny <= 1518661 + 250 AND maxy >= 1518661 - 250
) foo, Objects_Index
WHERE foo.Id = Objects_Index.id AND CAT=25;

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

Re: [sqlite] ISNULL in sqlite

[Mr. Puneet Kishor]

On Jun 25, 2011, at 3:33 PM, logan...@gmail.com wrote:

> Hello,
> 
> How do I check for a null or empty string in SQLite. SQL server has ISNULL
> but it doesn't seem to be supported in SQLite.

ifnull() and nullif() [http://www.sqlite.org/lang_corefunc.html]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Indexes on columns

[Mr. Puneet Kishor]

On Jun 24, 2011, at 1:58 PM, logan...@gmail.com wrote:

> Sorry, but seems like I'm missing something here.
> 
> From my understanding it looks like for Integer ID columns that are PK
> SQLite doesn't generate any indexes. Is this true?
> 

No, what you think is not true. SQLite does generate an index for INTEGER 
PRIMARY KEY columns.

> If the above is true then I want to create an index to improve the perf of
> my queries that are run against it.
> 
> Thanks,
> Hitesh
> 
> On Fri, Jun 24, 2011 at 5:31 AM, Igor Tandetnik  wrote:
> 
>> logan...@gmail.com wrote:
>>> Yes, that's exactly what it is. Here is the definition of one of the
>> table:
>>> 
>>> CREATE TABLE [Attributes] (
>>> [Id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
>>> [Name] VARCHAR(50)  NOT NULL
>>> )
>>> 
>>> Will creating explicit index on Id fix this issue?
>> 
>> What issue? Why is having an explicit index, separate from that built into
>> the table itself, important to you? What exactly do you feel is wrong with
>> the way things are now?
>> --
>> 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] lol: sqlite3 db smaller than the sum of its contents...

[Mr. Puneet Kishor]

On Jun 23, 2011, at 10:49 AM, Jean-Denis Muys <jdm...@kleegroup.com> wrote:

> 
> On 23 juin 2011, at 16:22, Mr. Puneet Kishor wrote:
> 
>> 
>> 
>> 
>> On Jun 23, 2011, at 10:18 AM, Stephan Beal <sgb...@googlemail.com> wrote:
>> 
>>> Hi, all!
>>> 
>>> Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db
>>> and the db file is only 400kb.
>>> 
>>> HTF can that possibly be?
>>> 
>>> After poking around i found that the wiki files actually total 360kb (when i
>>> added up their sizes manually, as opposed to using 'df' to get it), and the
>>> extra 80kb were from the hard drive's large block size (slack space reported
>>> by 'df').
>>> 
>>> Kinda funny, though, that sqlite3 actually decreases the amount of storage
>>> required in this case.
>> 
>> 
>> Lots of small files will take up more space because of the fixed minimum 
>> block size. For large corpuses this won't matter. Putting them all in one db 
>> makes logistical management easier, but you will lose the ability to update 
>> just a single file individually. I used to store all my wiki files 
>> (punkish.org) in one SQLite db, but now I have them as separate files which 
>> allows me to just ssh and edit a single file easily. Six of one, and all 
>> that.
>> 
> 
> Let me add two other drawbacks as well:
> 
> - incremental backups: now everytime you change one small file, the whole 
> database needs to be backed up, increasing needlessly storage size, and 
> backup time. This applies to system that do versioning as well as backups 
> (such as Time Machine).
> 
> - system level indexing: it now becomes much more difficult, if not 
> impossible, to do system level indexing and searching (as eg in Spotlight). 
> This is the reason why Apple stopped using a monolithic database for its 
> email application, now storing each mail individually: so that system-wide 
> user search can hit emails too.


Yup. Very good points, both of them.

I still use the db for metadata, but my files are stored in a tree directory 
structure much like CPAN's directories -- /path/<1>/<12>/<123>/filename.txt 
where 1, 2, and 3 are the first, second and third letters of the filename. I 
could store the metadata per file within each file, however, I haven't yet 
found a way to "find the ten most recently edited files" or "find all files 
edited by ".


> 
> These two drawbacks may or may not apply to your situation.
> 
> Jean-Denis
> 
> ___
> 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] lol: sqlite3 db smaller than the sum of its contents...

[Mr. Puneet Kishor]

On Jun 23, 2011, at 10:18 AM, Stephan Beal  wrote:

> Hi, all!
> 
> Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db
> and the db file is only 400kb.
> 
> HTF can that possibly be?
> 
> After poking around i found that the wiki files actually total 360kb (when i
> added up their sizes manually, as opposed to using 'df' to get it), and the
> extra 80kb were from the hard drive's large block size (slack space reported
> by 'df').
> 
> Kinda funny, though, that sqlite3 actually decreases the amount of storage
> required in this case.


Lots of small files will take up more space because of the fixed minimum block 
size. For large corpuses this won't matter. Putting them all in one db makes 
logistical management easier, but you will lose the ability to update just a 
single file individually. I used to store all my wiki files (punkish.org) in 
one SQLite db, but now I have them as separate files which allows me to just 
ssh and edit a single file easily. Six of one, and all that.


> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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] Help with CASE WHEN

[Mr. Puneet Kishor]

On Jun 19, 2011, at 10:04 PM, jose isaias cabrera wrote:

> "Mr. Puneet Kishor" wrote...
>> 
>> On Jun 19, 2011, at 5:12 PM, jose isaias cabrera wrote:
>> 
>>> 
>>> "Nico Williams" wrote...
>>> 
>>>> On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor <punk.k...@gmail.com>
>>>> wrote:
>>>>> The above is not SQL. You can't have a SQL statement begin with CASE. 
>>>>> SQL
>>>>> statements can only begin with either SELECT or UPDATE or CREATE or
>>>>> DELETE or ALTER, etc. CASE is an expression, and has to be a 
>>>>> replacement
>>>>> for a column. I can't even begin to help you rewrite your statement, 
>>>>> but
>>>>> what you are trying to accomplish is something like --
>>>>> 
>>>>> if (some condition)
>>>>> UPDATE this
>>>>> else
>>>>> UPDATE that
>>>>> 
>>>>> Either accomplish the above in a programming language, or rewrite it as
>>>>> separate queries, or hope Igor or someone can help you rewrite the 
>>>>> above
>>>>> into a single query.
>>>> 
>>>> Rewrite the statements as:
>>>> 
>>>> INSERT ... WHERE ... AND ;
>>>> 
>>>> Similarly for SELECT, UPDATE, and DELETE.
>>>> 
>>> 
>>> I have rewritten the code:
>>> 
>>> BEGIN IMMEDIATE TRANSACTION;
>>> 
>>> UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .10,2) 
>>> FROM
>>> LSOpenJobs
>>> WHERE subProjID = 9144 AND lang = 'ES-LA'
>>> AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'),
>>> Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs
>>> WHERE subProjID = 9144 AND lang = 'ES-LA'
>>> AND PSubClass != 'PM'
>>> AND PSubClass != 'Portal-Fee'
>>> AND PSubClass != 'Rush-Job'),
>>> Xtra6 = '10% of total',
>>> XtraB = '2011-06-19 18:02:16'
>>> WHERE subProjID = 9144 AND lang = 'ES-LA'
>>> AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job';
>>> 
>>> UPDATE LSOpenJobs SET ProjFund =
>>>   CASE PSubClass
>>>   WHEN 'Portal-Fee' THEN
>>> CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs
>>>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>>>AND PSubClass != 'Portal-Fee'
>>> WHEN < 5000 THEN
>>>   SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs
>>> WHERE subProjID = 9144 AND lang = 'ES-LA'
>>> AND PSubClass != 'Portal-Fee'
>>> WHEN BETWEEN 5000 AND 2 THEN
>>>   SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs
>>> WHERE subProjID = 9144 AND lang = 'ES-LA'
>>> AND PSubClass != 'Portal-Fee'
>>> ELSE
>>>   0.0
>>> END
>>>   END;
>>> COMMIT TRANSACTION;
>>> 
>>> This is what I get when I run that code:
>>> 
>>> sqlite>
>>> sqlite> BEGIN IMMEDIATE TRANSACTION;
>>> sqlite>
>>> sqlite> UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) *
>>> .10,2) FR
>>> OM LSOpenJobs
>>>  ...> WHERE subProjID = 9144 AND lang = 'ES-LA'
>>>  ...> AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'),
>>>  ...> Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs
>>>  ...> WHERE subProjID = 9144 AND lang = 'ES-LA'
>>>  ...> AND PSubClass != 'PM'
>>>  ...> AND PSubClass != 'Portal-Fee'
>>>  ...> AND PSubClass != 'Rush-Job'),
>>>  ...> Xtra6 = '10% of total',
>>>  ...> XtraB = '2011-06-19 18:02:16'
>>>  ...> WHERE subProjID = 9144 AND lang = 'ES-LA'
>>>  ...> AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 
>>> 'Rush-Job';
>>> sqlite>
>>> sqlite>   UPDATE LSOpenJobs SET ProjFund =
>>>  ...> CASE PSubClass
>>>  ...> WHEN 'Portal-Fee' THEN
>>>  ...>   CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs
>>>  ...>WHERE subProjID = 9144 AND lang = 'ES-LA'
>>>  ...>  AND PSubClass != 'Portal-Fee'
>>>  ...>   WHEN < 5000 THEN
>>>  ...> SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs
>>>  ...>   WHERE subProjID = 9144 AND lang = 'ES-LA'
>>>  ...>   AND PSubClass != 'Portal-Fee'
>>>  ...>   WHEN

Re: [sqlite] Help with CASE WHEN

[Mr. Puneet Kishor]

On Jun 19, 2011, at 5:12 PM, jose isaias cabrera wrote:

> 
> "Nico Williams" wrote...
> 
>> On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor <punk.k...@gmail.com> 
>> wrote:
>>> The above is not SQL. You can't have a SQL statement begin with CASE. SQL 
>>> statements can only begin with either SELECT or UPDATE or CREATE or 
>>> DELETE or ALTER, etc. CASE is an expression, and has to be a replacement 
>>> for a column. I can't even begin to help you rewrite your statement, but 
>>> what you are trying to accomplish is something like --
>>> 
>>> if (some condition)
>>> UPDATE this
>>> else
>>> UPDATE that
>>> 
>>> Either accomplish the above in a programming language, or rewrite it as 
>>> separate queries, or hope Igor or someone can help you rewrite the above 
>>> into a single query.
>> 
>> Rewrite the statements as:
>> 
>> INSERT ... WHERE ... AND ;
>> 
>> Similarly for SELECT, UPDATE, and DELETE.
>> 
> 
> I have rewritten the code:
> 
> BEGIN IMMEDIATE TRANSACTION;
> 
> UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .10,2) FROM 
> LSOpenJobs
> WHERE subProjID = 9144 AND lang = 'ES-LA'
> AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'),
> Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs
> WHERE subProjID = 9144 AND lang = 'ES-LA'
> AND PSubClass != 'PM'
> AND PSubClass != 'Portal-Fee'
> AND PSubClass != 'Rush-Job'),
> Xtra6 = '10% of total',
> XtraB = '2011-06-19 18:02:16'
> WHERE subProjID = 9144 AND lang = 'ES-LA'
> AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job';
> 
>  UPDATE LSOpenJobs SET ProjFund =
>CASE PSubClass
>WHEN 'Portal-Fee' THEN
>  CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs
>   WHERE subProjID = 9144 AND lang = 'ES-LA'
> AND PSubClass != 'Portal-Fee'
>  WHEN < 5000 THEN
>SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee'
>  WHEN BETWEEN 5000 AND 2 THEN
>SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee'
>  ELSE
>0.0
>  END
>END;
> COMMIT TRANSACTION;
> 
> This is what I get when I run that code:
> 
> sqlite>
> sqlite> BEGIN IMMEDIATE TRANSACTION;
> sqlite>
> sqlite> UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * 
> .10,2) FR
> OM LSOpenJobs
>   ...> WHERE subProjID = 9144 AND lang = 'ES-LA'
>   ...> AND PSubClass != 'PM' AND PSubClass != 'Rush-Job'),
>   ...> Xtra8 = (SELECT round(sum(Xtra8) * .10,2) FROM LSOpenJobs
>   ...> WHERE subProjID = 9144 AND lang = 'ES-LA'
>   ...> AND PSubClass != 'PM'
>   ...> AND PSubClass != 'Portal-Fee'
>   ...> AND PSubClass != 'Rush-Job'),
>   ...> Xtra6 = '10% of total',
>   ...> XtraB = '2011-06-19 18:02:16'
>   ...> WHERE subProjID = 9144 AND lang = 'ES-LA'
>   ...> AND XtraD = '10%' AND PSubClass = 'PM' AND PSubClass != 'Rush-Job';
> sqlite>
> sqlite>   UPDATE LSOpenJobs SET ProjFund =
>   ...> CASE PSubClass
>   ...> WHEN 'Portal-Fee' THEN
>   ...>   CASE SELECT round(sum(Xtra8),2) FROM LSOpenJobs
>   ...>WHERE subProjID = 9144 AND lang = 'ES-LA'
>   ...>  AND PSubClass != 'Portal-Fee'
>   ...>   WHEN < 5000 THEN
>   ...> SELECT round(sum(ProjFund) * .0175,2) FROM LSOpenJobs
>   ...>   WHERE subProjID = 9144 AND lang = 'ES-LA'
>   ...>   AND PSubClass != 'Portal-Fee'
>   ...>   WHEN BETWEEN 5000 AND 2 THEN
>   ...> SELECT round(sum(ProjFund) * .0125,2) FROM LSOpenJobs
>   ...>   WHERE subProjID = 9144 AND lang = 'ES-LA'
>   ...>   AND PSubClass != 'Portal-Fee'
>   ...>   ELSE
>   ...> 0.0
>   ...>   END
>   ...> END;
> Error: near "SELECT": syntax error
> sqlite> COMMIT TRANSACTION;
> sqlite>
> 


Try to do the two queries separately and see which one croaks. Most likely it 
is the second UPDATE query. You might need to enclose the sub-SELECTs (the ones 
in the CASE and WHENs) in parens. From the docs

Scalar Subqueries

A SELECT statement enclosed in parentheses may appear as a 
scalar quantity. A SELECT used as a scalar quantity must 
return a result set with a single column. The result of the 
expression is the value of the only column in the first row 
returned by t

Re: [sqlite] Help with CASE WHEN

[Mr. Puneet Kishor]

On Jun 17, 2011, at 4:56 PM, jose isaias cabrera wrote:

> CASE (SELECT round(sum(Xtra8),2) FROM LSOpenJobs
>   WHERE subProjID = 9144 AND lang = 'ES-LA'
> AND PSubClass != 'Portal-Fee')
> 
> WHEN < 5000 THEN
>UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0175,2) 
> FROM LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM 
> LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee') <= 5000),
>  Xtra8 = (SELECT round(sum(Xtra8) * .0175,2) FROM LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM 
> LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee') <= 5000),
>  Xtra6 = '1.75% of total',
>  XtraB = '2011-06-17 17:40:05',
>  XtraD = '1.75%'
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass = 'Portal-Fee'
> 
> WHEN BETWEEN 5000 AND 2 THEN
>UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0125,2) 
> FROM LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM 
> LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee') > 5000),
>  Xtra8 = (SELECT round(sum(Xtra8) * .0125,2) FROM LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM 
> LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee') > 5000),
>  Xtra6 = '1.25% of total',
>  XtraB = '2011-06-17 17:40:05',
>  XtraD = '1.25%'
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass = 'Portal-Fee'
> 
> ELSE
>UPDATE LSOpenJobs SET ProjFund = (SELECT round(sum(ProjFund) * .0,2) 
> FROM LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM 
> LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee') >= 2),
>  Xtra8 = (SELECT round(sum(Xtra8) * .0,2) FROM LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee' AND (SELECT round(sum(Xtra8),2) FROM 
> LSOpenJobs
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass != 'Portal-Fee') >= 2),
>  Xtra6 = 'No fee charged',
>  XtraB = '2011-06-17 17:40:05',
>  XtraD = '0.00%'
>  WHERE subProjID = 9144 AND lang = 'ES-LA'
>  AND PSubClass = 'Portal-Fee'


The above is not SQL. You can't have a SQL statement begin with CASE. SQL 
statements can only begin with either SELECT or UPDATE or CREATE or DELETE or 
ALTER, etc. CASE is an expression, and has to be a replacement for a column. I 
can't even begin to help you rewrite your statement, but what you are trying to 
accomplish is something like --

if (some condition)
UPDATE this
else
UPDATE that

Either accomplish the above in a programming language, or rewrite it as 
separate queries, or hope Igor or someone can help you rewrite the above into a 
single query.

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

Re: [sqlite] Do I need to migrate to MySQL?

[Mr. Puneet Kishor]

On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote:

> Mr. Puneet Kishor wrote:
>> On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote:
>>> MySQL should be avoided like the plague.
>> why?
>> This is a long standing (un)conventional wisdom to which I too have hewed.
>> Now, it so happens, I will be starting work on a project that uses MySQL
>> exclusively, and has done so for years. They have been very happy with it.
>> And, even though I feel like telling them that they should move to Pg, I
>> don't really know what the reasons are. I am not sure if all the reasons that
>> might be, are still valid.
>> Of course, I don't want this to become a long, religious threat that might be
>> inappropriate for this list, or bore most of the other readers to tears. But,
>> it merits asking, why should MySQL be avoided like the plague? It is a strong
>> statement that requires backing evidence, else it would be construed FUD.
> 
> Perhaps my statement was a bit strong, so I will clarify a bit.
> 

You don't say. "Should be avoided like the plague" is way more than a bit, 
hence, my response.


> *And* I'll give concrete examples.
> 
> 1.  Firstly, the context for my statement is someone who is not currently 
> using MySQL, and so they don't already have an investment in it and codebase 
> designed for it.
> 
> If one is already using MySQL, then that is the status quo and the question 
> is on whether benefits from a change to something else is worth the effort or 
> not.  But if one is not already using it, and their current DBMS can't be 
> used how they need, then they have to change anyway and the question is 
> between whether to move to MySQL or to something else instead; I am 
> addressing this latter situation, and you'll notice I also said sticking with 
> SQLite is even better if its usage can be fixed.


The same could be said of anything... "if its usage can be fixed" is a clause 
that can be interpreted widely.


> 
> 2.  I consider MySQL to be an 80% solution.
> 
> It does the job for which it is used adequately in many cases, and it is 
> successfully used in many places, including to drive many businesses and 
> organizations for mission-critical purposes.
> 


If "it is does the job for which it is used adequately" then it is a 100% 
solution.


> ..
> 
> a.  MySQL silently ignores all CHECK constraints in all engines, so for 
> example you can't even tell it you want a column to only hold values between 
> 1 and 10.
> Its in the MySQL docs:  "The CHECK clause is parsed but ignored by all 
> storage engines."


Yes and no. Apparently the above was true before 5.0.2, but apparently it has 
been fixed since then. From the docs, 

"Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and 
coerces them to legal values for data entry. In MySQL 5.0.2 and up, that 
remains the default behavior, but you can change the server SQL mode to select 
more traditional treatment of bad values such that the server rejects them and 
aborts the statement in which they occur."

and

"In MySQL 5.0.2 and up, you can select stricter treatment of input values by 
using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes:

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';




> 
> b.  That's just an example of how MySQL silently ignores lots of errors or 
> silently changes data on you, such as silently truncating text values that 
> are too long for a field when you insert them, so you've lost data without 
> even knowing it.  (Okay, I knew about this one previously.)


Shown by another poster to not be true.

No point in going through all the issues you pointed out. I am sure some of 
them are true, while others no longer true, or true but have work-arounds.


> ..
> 
> I say avoid MySQL like the plague because it will bite you in so many ways, 
> while an alternative like Postgres will only bite you in relatively few and 
> less painful ways.  Postgres is more of a 90-95% solution relative to MySQL's 
> 80%, assuming that there is no 100% solution.
> 
> I also know quite a number of savvy people in the developer communities who 
> have used both Postgres and MySQL, and a vast majority of those prefer 
> Postgres and strongly recommend it over MySQL when one has a choice, and so 
> do I.
> 

The main thing is to be aware of as much as possible, and then figure out 
work-arounds. No technology is perfect. There could be serious, 
project-specific show-stoppers that would cause one to choose a particular tech 
over another, but those would be project-specific.

I have no love for MySQL. All I want to emphasize is that statements like 
"avoid it like the plague" become sound-bytes with 

Re: [sqlite] Do I need to migrate to MySQL?

[Mr. Puneet Kishor]

On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote:

> MySQL should be avoided like the plague.

why?

This is a long standing (un)conventional wisdom to which I too have hewed. Now, 
it so happens, I will be starting work on a project that uses MySQL 
exclusively, and has done so for years. They have been very happy with it. And, 
even though I feel like telling them that they should move to Pg, I don't 
really know what the reasons are. I am not sure if all the reasons that might 
be, are still valid.

Of course, I don't want this to become a long, religious threat that might be 
inappropriate for this list, or bore most of the other readers to tears. But, 
it merits asking, why should MySQL be avoided like the plague? It is a strong 
statement that requires backing evidence, else it would be construed FUD.




>  Use Postgres instead if you have to 
> switch to a larger SQL DBMS.  But hopefully the help you've gotten so far 
> will 
> extend your mileage with SQLite and you won't have to switch to anything yet. 
> -- 
> Darren Duncan
> 
> Ian Hardingham wrote:
>> Guys, the server for this game -
>> 
>> http://www.frozensynapse.com
>> 
>> uses SQLite.  We've had an unexpectedly successful launch which has 
>> resulted in the server being swamped with players, and I'm trying to 
>> optimise everywhere I can.   I've always been under the impression that 
>> SQLite is pefectly fast and it's the scripting language I wrote the 
>> server in which is too blame.  (Yes, I know writing a back-end in a 
>> single-threaded scripting language is an absolutely terrible idea).  
>> However, everyone in the industry I talk to says that SQLite must be one 
>> of the problems.
>> 
>> I may be looking at a complete re-write.  I may also need to have a 
>> solution which scales beyond one machine.  Can anyone give me advice on 
>> this matter specifically?
>> 
>> (The video on that website at 2.04 gives a good idea of what kind of 
>> functions are being powered by the database).
>> 
>> Thanks,
>> Ian
> 
> ___
> 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] alter table add column

[Mr. Puneet Kishor]

On May 31, 2011, at 10:11 AM, Fabio Spadaro wrote:

> "Alter table add column" command drop data from table.
> Can you keep the data or should I store the data before the alter and then put
> them in the table?

ALTER TABLE ADD COLUMN does not drop data from the table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] using a view for fts

[Mr. Puneet Kishor]

Consider 

CREATE VIRTUAL TABLE fts_text USING fts4 (id, content);

.. populate the above table, then ..

SELECT rowid, Snippet(fts_text) FROM fts_text WHERE fts_text MATCH ?;

If I try to do something like

CREATE VIEW v_find AS 
  SELECT rowid, Snippet(fts_text) content FROM fts_text;

SELECT rowid, content FROM v_find WHERE fts_text MATCH ?;

I understandably get an error "no such column fts_text".

The above is a contrived example, but can I build a view out of a complicated 
fts query so I can then pass parameters to the view later on?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] compartmentalizing FTS4 searches

[Mr. Puneet Kishor]

My program stores a bunch of text in an FTS4 table and makes it available for 
search. The wrinkle is, there are conceptually different projects for which the 
search has to be compartmentalized.

CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT);
CREATE TABLE documents (document_id INTEGER PRIMARY KEY, document_name TEXT .., 
project_id INTEGER);
CREATE VIRTUAL TABLE fts_docs USING fts4 (document_id, content);

Now, when I search for terms, I would like to target only the content for a 
particular project. Would something like the following work --

SELECT Snippet(fts_docs), f.document_id 
FROM fts_docs f JOIN 
 documents d ON f.document_id = d.document_id JOIN 
 projects p ON p.project_id = d.project_id 
WHERE fts_docs MATCH ? AND p.project_name = 'this old project';

or, is there some other way to restrict the MATCH search to only certain rows 
in the fts table?

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

Re: [sqlite] time in AM/PM?

[Mr. Puneet Kishor]

On May 15, 2011, at 9:24 PM, Igor Tandetnik wrote:

> Mr. Puneet Kishor <punk.k...@gmail.com> wrote:
>> I am trying to get time stamps to be reported as '10:33 AM' and '1:27 PM' 
>> instead of '10:33' or '13:27'. I don't see any
>> formatting options to return the time in 12-hour format with AM/PM suffixed. 
>> Am I missing something, or do I have to roll my own? 
> 
> You are not missing anything. You'll have to roll your own.

Assuming 't' is a DATETIME column, the following seems work --

CASE
WHEN CAST(strftime('%H', t, 'localtime') AS INTEGER) = 12 
THEN strftime('%H:%M', t, 'localtime') || ' PM'  
WHEN CAST(strftime('%H', t, 'localtime') AS INTEGER) > 12 
THEN strftime('%H:%M', t, '-12 Hours', 'localtime') || ' PM' 
ELSE strftime('%H:%M', t, 'localtime') || ' AM' 
END

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

Re: [sqlite] Need to be able to rename attributes

[Mr. Puneet Kishor]

On May 15, 2011, at 10:49 PM, romtek wrote:

> Let's not make this issue into something that it's
> not.


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

[sqlite] time in AM/PM?

[Mr. Puneet Kishor]

I am trying to get time stamps to be reported as '10:33 AM' and '1:27 PM' 
instead of '10:33' or '13:27'. I don't see any formatting options to return the 
time in 12-hour format with AM/PM suffixed. Am I missing something, or do I 
have to roll my own?

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

[sqlite] FTS4 ranking function in Perl

[Mr. Puneet Kishor]

Has anyone implemented the search result ranking function in Perl?


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

Re: [sqlite] Need to be able to rename attributes

[Mr. Puneet Kishor]

On May 15, 2011, at 5:05 PM, romtek wrote:

> On Sun, May 15, 2011 at 4:39 PM, Simon Slavin  wrote:
> 
>> 
>> On 15 May 2011, at 10:33pm, romtek wrote:
>> 
>>> So, I am asking developers of SQLite to make it easy for tool developers
>> to
>>> offer the ability to rename attributes.
>> 
>> The SQL specification does not use the term 'attribute' in any way that
>> would give them names.  Can you explain what you mean by 'rename attributes'
>> ?  Perhaps give an example.
>> 
>> Simon.
>> 
> 
> 
> OK, I will give you an example, and you correct my use of the terms, please.
> 
> A table:
> 
> id, eventName, date
> 
> I want to rename date to dateAdded.
> 


sqlite doesn't support changing the name of a table column (and, neither you 
nor your user should be doing this -- there is something strange with your app 
requirements). That said, you can "rename" a column by creating a new table 
with the new column definitions and copy data from the old table to the new 
table.

CREATE TABLE new_table (id, eventName, dateAdded);
INSERT INTO new_table (id, eventName, dateAdded) SELECT id, eventName, date 
FROM old_table;

> People currently jump through hoops in order to achieve such a simple (from
> the user's point of view) and needed goal:
> http://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table
> .
> ___
> 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] selecting unique list of latest timestamps

[Mr. Puneet Kishor]

I have a bunch of uris stored in a table

CREATE TABLE uris (
uri_id INTEGER PRIMARY KEY, 
uri TEXT
);

uri_id  uri 
--  --
1   http://foo.com
2   http://bar.com
3   http://baz.com
4   http://qux.com

A program periodically downloads the content of the above web sites. Another 
table stores the download history, that is, when the content was last 
downloaded.

CREATE TABLE history (
history_id INTEGER PRIMARY KEY, 
uri_id INTEGER, 
downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP
);

history_id  uri_id  downloaded_on  
--  --  ---
1   4   2011-05-04 02:25:09
2   3   2011-05-03 02:34:08
3   1   2011-05-01 02:50:43
4   2   2011-05-02 02:50:45
6   4   2011-05-14 02:50:48

The content itself is stored in an FTS4 table linked by history_id.

CREATE VIRTUAL TABLE fts_uri (
history_id, content
);

I am looking for an efficient way to select the uris, and the latest 
'downloaded_on' time stamp for each uri, but am drawing a blank. The list 
should look like so

uri_id  uri downloaded_on
--  --  ---
1   http://foo.com  2011-05-01 02:50:43
2   http://bar.com  2011-05-02 02:50:45
3   http://baz.com  2011-05-03 02:34:08
4   http://qux.com  2011-05-14 02:50:48

Suggestions?

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

Re: [sqlite] Dynamic SQL for SQLite?

[Mr. Puneet Kishor]

On May 11, 2011, at 7:37 PM, John wrote:

> Igor,
> What you are suggesting will not work. You can only select values not
> columns using case.
> 
> 
> select case strftime('%w', 'now')
>   when 0 then sunday_value
>   when 1 then monday_value
>   ...
>   else saturday_value
>  end
> from seven_days;
> 
> 


You must be new here. You have to understand the first rule of SQL. Igor is 
never wrong. His query works just fine. See below --

sqlite> CREATE TABLE seven_days (
   ...>   mon INTEGER,
   ...>   tue INTEGER,
   ...>   wed INTEGER
   ...> );
sqlite> INSERT INTO seven_days VALUES (5, 3, 2);
sqlite> INSERT INTO seven_days VALUES (1, 4, 3);
sqlite> INSERT INTO seven_days VALUES (7, 8, 3);
sqlite> SELECT CASE Strftime('%w', 'now') 
   ...>   WHEN 1 THEN mon
   ...>   WHEN 2 THEN tue
   ...>   ELSE wed
   ...> END AS day_val
   ...> FROM seven_days;
day_val
--
2 
3 
3 
sqlite> 


> 
> 
> On Wed, May 11, 2011 at 8:30 PM, Igor Tandetnik  wrote:
> 
>> On 5/11/2011 8:14 PM, John wrote:
>>> I am in situation where I need to keep as much logic as possible within
>>> SQLite. However the query that I need to perform does not seem to be
>>> possible  to perform.
>>> 
>>> let's say I have a table with columns for each day of the week
>>> 
>>> create table seven_days
>>> (monday_value integer,
>>>  tueday_value integer,
>>>  wednesday_value integer,
>>>  ...   );
>>> 
>>> I want to select value from whatever day it is today. So if today is
>>> Tuesday, select from tuesday_value.
>>> 
>>> Can I do it with pure SQlite?
>> 
>> select case strftime('%w', 'now')
>>   when 0 then sunday_value
>>   when 1 then monday_value
>>   ...
>>   else saturday_value
>>  end
>> from seven_days;
>> 
>> --
>> Igor Tandetnik
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> ~John
> ___
> 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] Licensing and copyright info?

[Mr. Puneet Kishor]

On May 10, 2011, at 1:59 PM, Don Ireland wrote:

> I am writing an app and plan to embed SQLite in my app as a means to store 
> the data.
> 
> What licensing/copyright statements do I need to make RE SQLite?


Nothing. sqlite, the program, is in the Public Domain. SQLite, the term, is 
trademarked. Embed sqlite in your app, don't call your app "SQLite" or 
something that can be confused with the term "SQLite," make loads of money, 
become a millionaire, and then do some public good with it. Go forth and 
multiply.


> 
> TIA!
> 
> Don Ireland
> ___
> 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] Determining how many columns were returned in a query

[Mr. Puneet Kishor]

On May 8, 2011, at 11:46 AM, Sam Carleton wrote:

> On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps  
> wrote:
> 
>> 
>>> How does one go about finding out how many rows a query returns?
>> 
>> This is the number of time sqlite3_step can be called successfully 
>> until it returns SQLITE_DONE.
> 
> I had it wrong in the email body, I meant how many columns are in query?
> 

Since, ostensibly, you are the one who queried in the first place, shouldn't 
you know that already? Maybe there is more to this question that you are not 
stating.

Puneet.

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

Re: [sqlite] Need help understanding how to post to this list

[Mr. Puneet Kishor]

On May 3, 2011, at 6:04 PM, Rolf Marsh wrote:

> Hello.. I just joined today and can't seem to figure out how to start a 
> new thread... Can someone please enlighten me?


You just did.

Just post a question with the subject line indicating clearly what is bothering 
you and take a seat. The doctor will be with you shortly.


> 
> Regards,
> Rolf
> ___
> 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 Use an Apostrophe in a Text Field?

[Mr. Puneet Kishor]

On Apr 17, 2011, at 5:54 PM, Alan Holbrook  wrote:

> I'm using SQLite with VBE2008.  I've defined a table with a number of text
> fields in it.  If the information I want to write to the database contains
> an embedded apostrophe, the program throws an error.  That is, if I set
> textfield1 to *Going Down the Road Feeling Bad*, the data gets written
> correctly and the program continues.  But if I set textfield1 to *Goin' Down
> the Road Feelin' Bad*, I get an error.
> 
> Is there a way I can use an apostrophe in the data to be written?


Escape the apostrophe using the conventions of your VBE2008, or better yet, use 
bind values.


> ___
> 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] Question:how to insert row with multiple values from same field of different rows of another table?

[Mr. Puneet Kishor]

On Apr 4, 2011, at 7:59 AM, Colin Cuthbert wrote:

> 
> 
> 
>> From: punk.k...@gmail.com
>> Date: Sun, 3 Apr 2011 07:52:42 -0500
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Question:how to insert row with multiple values from   
>> same field of different rows of another table?
>> 
>> 
>> On Apr 3, 2011, at 7:50 AM, Luuk wrote:
>> 
>>> On 03-04-2011 14:43, Colin Cuthbert wrote:
 First time I've used this (or any!) mailing list, so sorry if I've done 
 something wrong.
 
 Pretty sure my question (in the subect) is phrased badly but it's the best 
 I could do!
 
 create table People(id integer primary key, name text);
 insert into People (name) values ('bob');
 insert into People (name) values ('fred');
 
 create table Cars(id integer primary key, name text);
 insert into Cars (name) values ('ford');
 insert into Cars (name) values ('volvo');
 
 create table CarOwners(id integer primary key, carId integer references 
 Cars(id), ownerId integer references People(id));
 insert into CarOwners (carId, ownerId) select Cars.id, People.id from 
 Cars, People where Cars.name='ford' and People.name='bob';
 
 create table Couples(id integer primary key, personId1 integer references 
 People(id), personId2 integer references People(id));
 
 The last 'insert' statement seems to work for inserting a row into the 
 'CarOwners' table, but I'm not sure that's the right/best way to do it.
 
 But how can I do a similar insert into the 'Couples' table?  ie, how can I 
 insert a row (specifying 'personId1' and 'personId2' via queries based on 
 'People.name') into the 'Couples' table?
>>> 
>>> You forgot to define 'Couples'.
 Does it start something like this?
 
 insert into Couples (personId1, personId2) select id, id from People 
 where...
>>> 
>>> select id, id from People will return the same id (from the same record)
>>> twice
>>> 
>>> somehting like:
>>> select a.id, b.id from People a join People b on a.id<>b.id
>>> will give other results, but what youactually want to be returned
>>> depends on the definition of a 'Couple'...
>>> 
>> 
>> You also want to do all of the above in a TRANSACTION, preferably with a 
>> TRIGGER, to ensure the correct relationships are preserved.
> 
> Ok I looked into transactions (I'm new to sql!).  Isn't a transaction 
> automatically created with the insert statement?  Or are you saying I need to 
> explicitly begin/end one as part of the solution to my problem?


Yes, you need explicit BEGIN/END to perform a transaction.

> 
> And regarding a trigger to ensure the correct relationships are preserved... 
> yeah you're right, but that's another issue isn't it?  Or is it related to 
> this issue in a way that I'm not seeing?

Another, but related issue.

Regarding your original problem, the following works

INSERT INTO Couples (personId1, personId2) VALUES ((SELECT id FROM People WHERE 
name = 'bob'), (SELECT id FROM People WHERE name = 'fred'));
sqlite> SELECT * FROM Couples;
id  personId1   personId2 
--  --  --
1   1   2 


> 
> Thanks.
> 
> 
> ___
> 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] Question:how to insert row with multiple values from same field of different rows of another table?

[Mr. Puneet Kishor]

On Apr 3, 2011, at 7:50 AM, Luuk wrote:

> On 03-04-2011 14:43, Colin Cuthbert wrote:
>> First time I've used this (or any!) mailing list, so sorry if I've done 
>> something wrong.
>> 
>> Pretty sure my question (in the subect) is phrased badly but it's the best I 
>> could do!
>> 
>> create table People(id integer primary key, name text);
>> insert into People (name) values ('bob');
>> insert into People (name) values ('fred');
>> 
>> create table Cars(id integer primary key, name text);
>> insert into Cars (name) values ('ford');
>> insert into Cars (name) values ('volvo');
>> 
>> create table CarOwners(id integer primary key, carId integer references 
>> Cars(id), ownerId integer references People(id));
>> insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars, 
>> People where Cars.name='ford' and People.name='bob';
>> 
>> create table Couples(id integer primary key, personId1 integer references 
>> People(id), personId2 integer references People(id));
>> 
>> The last 'insert' statement seems to work for inserting a row into the 
>> 'CarOwners' table, but I'm not sure that's the right/best way to do it.
>> 
>> But how can I do a similar insert into the 'Couples' table?  ie, how can I 
>> insert a row (specifying 'personId1' and 'personId2' via queries based on 
>> 'People.name') into the 'Couples' table?
> 
> You forgot to define 'Couples'.
>> Does it start something like this?
>> 
>> insert into Couples (personId1, personId2) select id, id from People where...
> 
> select id, id from People will return the same id (from the same record)
> twice
> 
> somehting like:
> select a.id, b.id from People a join People b on a.id<>b.id
> will give other results, but what youactually want to be returned
> depends on the definition of a 'Couple'...
> 

You also want to do all of the above in a TRANSACTION, preferably with a 
TRIGGER, to ensure the correct relationships are preserved.

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

Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)

[Mr. Puneet Kishor]

On Mar 31, 2011, at 2:28 PM, Doug Currie wrote:

> On Mar 31, 2011, at 2:27 PM, Mike Rychener wrote:
> 
>> I have tried the latest Explorer and it gets a syntax error on STDEV.  
>> However, that function works in Eclipse just fine, to take the standard 
>> deviation of a column (like min, max, avg).  Is there a workaround or 
>> other fix available?  
> 
> See http://www.sqlite.org/contrib  extension-functions.c 
> 

Is there a guide on how to compile the above extension-functions.c into sqlite, 
so it is always available without have to do load the extension explicitly?

Pointers appreciated.

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

Re: [sqlite] using sqlite3_get_table --additional info...

[Mr. Puneet Kishor]

On Mar 31, 2011, at 9:30 AM, john darnell wrote:

> BTW, if there is a better way to get a row count without using 
> sqlite3_get_table() that would also work.


maybe I am missing something, but what is wrong with "SELECT Count(*) FROM 
table"?


> 
> _
> From: john darnell
> Sent: Thursday, March 31, 2011 9:27 AM
> To: 'General Discussion of SQLite Database'
> Subject: using sqlite3_get_table
> 
> 
> 
> All I need to do is see how many rows a table has.  I stumbled across this 
> function and used it thusly in my code (I removed the error checking for the 
> sake of brevity):
> 
>   Result = sqlite3_initialize();
> 
>   sqlite3 *db_ptr;
>   Result = 0;
> 
>   Result = sqlite3_open_v2(DBEnginePath, _ptr, SQLITE_OPEN_READWRITE, 
> NULL);
> 
>   char ***CArray = NULL;
>   int iRow, iCol;
>   char **err = NULL;
>   sqlite3_get_table(db_ptr, "Select * from Admin", CArray, , , err);
> 
> Unfortunately, when I execute the sqlite_get_table call, I get an error 
> message telling me that I have an "unhandled error."  It says that the error 
> is occurring here:
> 
> struct unixShm {
>  unixShmNode *pShmNode; /* The underlying unixShmNode object */
>  unixShm *pNext;/* Next unixShm with the same unixShmNode */
>  u8 hasMutex;   /* True if holding the unixShmNode mutex */
>  u16 sharedMask;/* Mask of shared locks held */
>  u16 exclMask;  /* Mask of exclusive locks held */
> #ifdef SQLITE_DEBUG
>  u8 id; /* Id of this connection within its unixShmNode */
> #endif
> 
> I am QUITE CERTAIN that my problem lies with the way I am declaring the 
> arrays, but not having an example to teach me, I have no idea what the 
> correct method is.  If anyone has an example of how he or she is using 
> sqlite3_get_table that he or she wouldn't mind sharing, I would be 
> appreciative.
> 
> TIA!
> 
> R,
> John A.M. Darnell
> Senior Programmer
> Walsworth Publishing Company
> Brookfield, MO
> John may also be reached at 
> johnamdarn...@gmail.com
> 
> Trivia question:  Who saved Gandalf from his imprisonment at the Tower of 
> Isengard in book 1 of The Lord of the Rings (i.e. The Fellowship of the Ring)?
> 
> 
> 
> ___
> 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] Creation and modification timestamps with "insert or replace" possible?

[Mr. Puneet Kishor]

On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote:

> Hi there,
> 
> I'm trying this for quite some time... and I'm totally stuck.
> 
> I have the following table:
> 
> CREATE TABLE example(
>  date integer primary key not null,
>  text text,
>  ctime TIMESTAMP,
>  mtime TIMESTAMP);
> 
> ctime=creation time (should be set only once)
> mtime=modification time (should be set every time the row is updated)
> 

change ctime definition to

ctime DEFAULT CURRENT_TIMESTAMP

and use trigger for only the mtime


> Important: I also want to be able to "insert or replace" rows and keep ctime.
> 
> Currently I'm trying with 2 triggers:
> 
> CREATE TRIGGER insert_example_timestamp AFTER INSERT ON example
> BEGIN
>  UPDATE example SET ctime = DATETIME('now','localtime')
>  WHERE ( rowid = new.rowid AND ctime IS NULL);
> END;
> 
> CREATE TRIGGER update_example_timestamp AFTER UPDATE ON example
> BEGIN
>  UPDATE example SET mtime=DATETIME('now','localtime') WHERE rowid = new.rowid;
> END;
> 
> The update trigger works fine, but the insert trigger ALWAYS updates ctime.
> I tried various combinations to query a unset/empty ctime and only
> update if ctime is NULL or ''.
> It doesn't work inside the trigger.
> Either it updates always or never.
> Where is my mistake?
> 
> Is there another way to achieve this? Maybe without triggers?
> 
> Many thanks!
> ___
> 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] SELECT (string field) returns part of contents, then asterisks

[Puneet Kishor]

On Tue, Feb 22, 2011 at 04:03:22PM +0100, Haldrup Office wrote:
> Hello list,
> 
> I'm in the process of writing a little interface tool for notes and 
> adress databases from an iPad.
> 
> Using MS Word VBA and SQLite3_StdCall.dll.
> My query looks quite simply put:
>  SELECT ROWID,creation_date,title FROM Note
> 
> and it runs fine and returns w/o problems.
> 
> When I iterate through it, though, and I try to read a long note (saved 
> in field 'title') I happen to get some 2700 characters back plus a 
> sequence of asterisks.

Probably a limitation of your "MS Word VBA and SQLite3_StdCall.dll"


> 
> Am I missing something here?
> 
> Thank you for your efforts,
> 
> /T
> 
> -- 
> Med venlig hilsen,
> 
> Thomas Besand
> 
> 
> ___
> 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] COUNT() extremely slow first time!

[Puneet Kishor]

On Mon, Feb 21, 2011 at 10:17:03AM -0500, Jim Wilcoxson wrote:
> This is a common issue on the mailing list.  The first time you do
> count(*), SQLite (actually your OS) has to load data into memory.
> Most OS's will keep the file in a buffer cache, so the 2nd count(*)
> doesn't have to read from disk.


One question I have related to the above -- how long does that buffer
cache remain filled with the data? I am assuming it is until new stuff
to be cached pushes out old stuff, no?

I was doing some R*Tree selects, and the first query was dog slow,
although benchmarking showed that the actual CPU time was very small.
Subsequent queries were lightning fast. I am assuming that the buffer is
not getting filled with the results as much as it is getting filled with
whatever part of the db that the program needs to open to do its work.


> 
> Here's a timing from my own system, after a purge command to clear the
> buffer cache:
> 
> $ time sqlite3 hb.db 'select count(*) from logs'
> -- Loading resources from /Users/jim/.sqliterc
> count(*)
> --
> 734909
> 
> real  0m0.580s
> user  0m0.190s
> sys   0m0.034s
> 
> Same command again, with the file cached:
> 
> $ time sqlite3 hb.db 'select count(*) from logs'
> -- Loading resources from /Users/jim/.sqliterc
> count(*)
> --
> 734909
> 
> real  0m0.189s
> user  0m0.165s
> sys   0m0.019s
> 
> This time is consistent no matter how many times I run it, because the
> file is still cached.  Doing a purge command to clear the cache and
> re-running the query, we get:
> 
> $ purge
> $ time sqlite3 hb.db 'select count(*) from logs'
> -- Loading resources from /Users/jim/.sqliterc
> count(*)
> --
> 734909
> 
> real  0m0.427s
> user  0m0.175s
> sys   0m0.024s
> 
> On my system, there is not a huge difference, but it is consistent.
> Now, if you have a fragmented file system, you will see a much larger
> difference.  There are many posts on the mailing list about both file
> system fragmentation and logical fragmentation within the SQLite file
> itself.  Your first count(*) is subject to these fragmentation
> effects, while your 2nd usually is not, because the file is in memory.
> 
> Some people on the list believe fragmentation is an unimportant detail
> you shouldn't worry about, because you have little control over it.
> That may be true, but it's useful to understand how it can affect
> performance.  I think you are seeing this first hand.
> 
> Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://www.hashbackup.com
> 
> 
> 
> On Mon, Feb 21, 2011 at 9:37 AM, Sven L  wrote:
> >
> > Same result :(
> > Note that I have compiled SQLite with the following switches:
> > SQLITE_ENABLE_STAT2
> > SQLITE_THREADSAFE=2
> >
> > I've learnt that COUNT(*) is slower than COUNT(ID), since * means the 
> > engine has to traverse all columns and it might even return another value 
> > if there are NULL-values...
> >
> > Also, this is quite interesting:
> >
> > sqlite> EXPLAIN QUERY PLAN SELECT COUNT(ItemID) FROM Items;
> > 0|0|0|SCAN TABLE Items (~100 rows)
> > sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM Items;
> > sqlite>
> >
> > I would expect an index scan on the first statement. The second statement 
> > tells me nada?!
> >
> > Thanks for your help!
> >
> >
> >> From: slav...@bigfraud.org
> >> Date: Mon, 21 Feb 2011 14:24:50 +
> >> To: sqlite-users@sqlite.org
> >> Subject: Re: [sqlite] COUNT() extremely slow first time!
> >>
> >>
> >> On 21 Feb 2011, at 2:23pm, Sven L wrote:
> >>
> >> > SELECT COUNT(ItemID) FROM Items;
> >> >
> >> > This takes around 40 seconds the first time! WHY?!
> >>
> >> Try again, doing everything identically except that instead of the above 
> >> line use
> >>
> >> SELECT COUNT(*) FROM Items;
> >>
> >> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users