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 BYpower
);


--
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

[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] 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

[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] how to disable a trigger

[Mr. Puneet Kishor]

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

> Sam Carleton  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

[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] 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 
> 
>  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] 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