Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[P Kishor]

In a way it already does… not to the mailing list but to the email address of 
everyone registered on the forum. The key thing you can’t do is post via email. 

My personal view is there never be a solution that will please everyone. But it 
is Richard’s software and Richard’s game, and we have to take his word that the 
web-based forum is better and give it a chance. That way we can focus on 
solving SQLite-related problems instead of web/email/forum related problems.

Fwiw, I like the new forum because of the few key differences already mentioned 
by Richard – editing, formatting, threading (and don’t like a few things – 
readability, styling – but these can be tweaked). I know many don’t like it, 
but that is how it is. I hope we can give the new forum a chance.

> On Mar 13, 2020, at 7:50 PM, Thomas Kurz  wrote:
> 
> Why can't the forum just forward all new postings to this mailing list and 
> vice versa? Then everyone could chose what to use ;)

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

Re: [sqlite] No feedback for executing a mathematical expression

[P Kishor]

Hi, 

Besides the most excellent explanation given by Keith Medcalf, I want to point 
out a couple of (hopefully) helpful things –

1. Contrary to your subject line, SQLite actually does give a feedback/returns 
something. It is just not good enough (for many of us). Consider the following:

```
○ → sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT 30 / 2;
15
sqlite> SELECT 30 / 57;
0
sqlite> SELECT 55 / 0;

sqlite>
```

See that blank line after the last operation? That is SQLite “printing” out a 
NULL value. The `.nullvalue STRING` setting in the command line client can 
change that blank like to something more visual/meaningful.

2. The `typeof()` operator is super. It is like the detective cousin of CAST(). 
The latter allows you to change the type of a data value and the former allows 
you to find out the typeof data.

```
sqlite> SELECT typeof(55 / 0);
null
sqlite> SELECT typeof(30 / 2);
integer
sqlite> SELECT typeof(30.0 / 55);
real
sqlite> SELECT 30.0 / 55;
0.545454545454545
sqlite>
```

Good luck. And nice question as it reminded us of this math idiosyncrasy of 
SQLite.

> On Mar 10, 2020, at 8:21 AM, Octopus ZHANG  wrote:
> 
> I try to run a simple math expression, but SQLite gives no feedback :
> 
> sqlite> select 99-(55/(30/57));
> 
> 
> 
> 
> Should I expect it to return nothing?





--
Puneet Kishor
Just Another Creative Commoner
http://punkish.org/About

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

Re: [sqlite] No feedback for executing a mathematical expression

[P Kishor]

A very helpful and clear explanation to many of us not familiar with SQLite’s 
math idiosyncracies, or simply needing a refresher. Many thanks Keith.

> On Mar 10, 2020, at 8:57 AM, Keith Medcalf  wrote:
> 
> 
> On Tuesday, 10 March, 2020 01:22, Octopus ZHANG  
> wrote:
> 
>> I try to run a simple math expression, but SQLite gives no feedback :
> 
>> sqlite> select 99-(55/(30/57));
> 
>> Should I expect it to return nothing?
> 
> It is returning something.  It is returning NULL.
> 
> sqlite> .nullvalue 
> sqlite> select 99-(55/(30/57));
> 
> sqlite>
> 
> 99 - (55 / (30 / 57))
> 
> 30 / 57 -> 0
> 
> 55 / 0 -> NULL
> 
> 99 - NULL -> NULL
> 
> If you want the result of 30/57 to be a floating point number (ie, not zero), 
> you need to have one of those numbers be floating point, after which each 
> successive operation will be carried out in floating point rather than 
> integer arithmetic.
> 
> 30. / 57 == 30 / 57. == 30. / 57. -> 0.526315789473684
> 
> 55 / 0.526315789473684 -> 104.5
> 
> 99 - 104.5 -> -5.5
> 




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

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

[P Kishor]

following up on my own response to Dan…

I see what you mean Dan. I am not an expert at reading the query plans, but I 
do see that effectively my new query has the same query plan as the last two 
queries combined as well as the original query. The only difference is the 
order in which it proceeds. So, the original (slow query)

|--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
|--SEARCH TABLE t2 USING INDEX ix_t2_scm (deleted=? AND deleted=?)
`--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)

The two sub-queries separately that run very fast

`--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m

and 
|--SEARCH TABLE t2 USING INDEX ix_t2_scm (deleted=? AND deleted=?)
`--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)

and the new query that also runs very fast using the two sub-queries

QUERY PLAN
|--CO-ROUTINE 1
|  |--SEARCH TABLE t2 USING INDEX ix_t2_scm (deleted=? AND deleted=?)
|  `--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)
|--SCAN SUBQUERY 1 AS a
`--LIST SUBQUERY 2
  `--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m


No idea what is going on.


> On Mar 9, 2020, at 2:08 PM, P Kishor  wrote:
> 
> 
> 
>> On Mar 9, 2020, at 1:04 PM, Dan Kennedy  wrote:
>> 
>> 
>> On 9/3/63 01:44, Puneet Kishor wrote:
>>> 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
>> 
>> 
>> I don't see why that would be any different from your join query. What does 
>> EXPLAIN QUERY PLAN say for the version that takes a few hundred ms?
> 
> 
> Here it is, the new query made up of two queries derived from the original 
> query (summarize again below). The query itself takes 301ms. 
> 
> ```
> SQLite version 3.30.0 2019-10-04 15:03:17
> Enter ".help" for usage hints.
> sqlite> EXPLAIN QUERY PLAN
>   ...> SELECT Count(*) FROM
>   ...> 
>   ...> -- this one is 'QUERY a'
>   ...> (SELECT Count(DISTINCT t1.t1Id) t1Id 
>   ...> FROM t2 JOIN t1 ON t2.t1Id = t1.t1Id 
>   ...> WHERE t1.deleted = 0 AND t2.deleted = 0 AND scm != '') a
>   ...>
>   ...> WHERE a.t1Id IN 
>   ...> 
>   ...> -- and this one is 'QUERY b'
>   ...> (SELECT Count(DISTINCT t1Id) FROM vt1 WHERE vt1 MATCH "foo");
> QUERY PLAN
> |--CO-ROUTINE 1
> |  |--SEARCH TABLE t2 USING INDEX ix_t2 (deleted=? AND deleted=?)
> |  `--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)
> |--SCAN SUBQUERY 1 AS a
> `--LIST SUBQUERY 2
>   `--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
> sqlite>
> ```
> 
> Here is the original query (QUERY zero). This is the one that takes a very 
> long time, 10.611s. Note, in this one I’ve added an additional table ’t2’ but 
> the concept is the same. Normal tables JOINed to each other, and then JOINed 
> to a virtual table.
> 
> ```
> sqlite> EXPLAIN QUERY PLAN
>   ...> SELECT Count(DISTINCT t1.t1Id)
>   ...> FROM t2 JOIN t1 ON t2.t1Id = t1.t1Id JOIN vt1 ON t1.t1Id = vt1.t1Id
>   ...> WHERE t1.deleted = 0 AND t2.deleted = 0 AND scm != '' AND vt1 MATCH 
> "foo";
> QUERY PLAN
> |--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
> |--SEARCH TABLE t2 USING INDEX ix_t2_scm (deleted=? AND deleted=?)
> `--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)
> sqlite>
> ```
> 
> Here are the two queries derived from QUERY zero. The first one (QUERY a) 
> takes 324ms
> 
> ```
> sqlite> EXPLAIN QUERY PLAN
>   ...> SELECT Count(DISTINCT t1.t1Id)
>   ...> FROM t2 JOIN t1 ON t2.t1Id = t1.t1Id
>   ...> WHERE t1.deleted = 0 AND t2.deleted = 0 AND scm != '';
> QUERY PLAN
> |--SEARCH TABLE t2 USING INDEX ix_t2_scm (deleted=? AND deleted=?)
> `--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)
> sqlite>
> ```
> 
> The second query (QUERY b) takes: 27ms
> 
> ```
> sqlite> EXPLAIN QUERY PLAN
>   ...> SELECT Count(DISTINCT t1Id) FROM vt1 WHERE vt1 MATCH “foo";
> QUERY PLAN
> `--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
> sqlite>
> ```
> 
> One more thing: All of this goes to hell if the virtual table returns way too 
> many matches. For example, I have a term that returns 80K rows from the FTS 
> MATCH. In that case, even my new query very slow because, well, because the 
> QUERY b above is slow.

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

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

[P Kishor]

> On Mar 9, 2020, at 1:04 PM, Dan Kennedy  wrote:
> 
> 
> On 9/3/63 01:44, Puneet Kishor wrote:
>> 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
> 
> 
> I don't see why that would be any different from your join query. What does 
> EXPLAIN QUERY PLAN say for the version that takes a few hundred ms?


Here it is, the new query made up of two queries derived from the original 
query (summarize again below). The query itself takes 301ms. 

```
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT Count(*) FROM
   ...> 
   ...> -- this one is 'QUERY a'
   ...> (SELECT Count(DISTINCT t1.t1Id) t1Id 
   ...> FROM t2 JOIN t1 ON t2.t1Id = t1.t1Id 
   ...> WHERE t1.deleted = 0 AND t2.deleted = 0 AND scm != '') a
   ...>
   ...> WHERE a.t1Id IN 
   ...> 
   ...> -- and this one is 'QUERY b'
   ...> (SELECT Count(DISTINCT t1Id) FROM vt1 WHERE vt1 MATCH "foo");
QUERY PLAN
|--CO-ROUTINE 1
|  |--SEARCH TABLE t2 USING INDEX ix_t2 (deleted=? AND deleted=?)
|  `--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)
|--SCAN SUBQUERY 1 AS a
`--LIST SUBQUERY 2
   `--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
sqlite>
```

Here is the original query (QUERY zero). This is the one that takes a very long 
time, 10.611s. Note, in this one I’ve added an additional table ’t2’ but the 
concept is the same. Normal tables JOINed to each other, and then JOINed to a 
virtual table.

```
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT Count(DISTINCT t1.t1Id)
   ...> FROM t2 JOIN t1 ON t2.t1Id = t1.t1Id JOIN vt1 ON t1.t1Id = vt1.t1Id
   ...> WHERE t1.deleted = 0 AND t2.deleted = 0 AND scm != '' AND vt1 MATCH 
"foo";
QUERY PLAN
|--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
|--SEARCH TABLE t2 USING INDEX ix_t2_scm (deleted=? AND deleted=?)
`--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)
sqlite>
```

Here are the two queries derived from QUERY zero. The first one (QUERY a) takes 
324ms

```
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT Count(DISTINCT t1.t1Id)
   ...> FROM t2 JOIN t1 ON t2.t1Id = t1.t1Id
   ...> WHERE t1.deleted = 0 AND t2.deleted = 0 AND scm != '';
QUERY PLAN
|--SEARCH TABLE t2 USING INDEX ix_t2_scm (deleted=? AND deleted=?)
`--SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=? AND t1Id=?)
sqlite>
```

The second query (QUERY b) takes: 27ms

```
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT Count(DISTINCT t1Id) FROM vt1 WHERE vt1 MATCH “foo";
QUERY PLAN
`--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m
sqlite>
```

One more thing: All of this goes to hell if the virtual table returns way too 
many matches. For example, I have a term that returns 80K rows from the FTS 
MATCH. In that case, even my new query very slow because, well, because the 
QUERY b above is slow.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] speeding up an FTS5 search with a JOIN

[P Kishor]

Hi Dan,


> On Sat Mar 7 13:32:54 UTC 2020,Dan Kennedy danielk1977 at gmail.com wrote:

>> On 7/3/63 14:58, P Kishor wrote: 

[snipped]

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

>That does seem slow. Are there many rows in table "t1" with t1.deleted set to 
>something other than 0? 

No, none of them have `deleted != 0`. Explanation: `deleted` is a flag field 
that will track (in the future) when any of the rows are tagged as deleted, no 
longer in use. The idea is to exclude “deleted” rows from searches, but not 
really delete them. As of now, all the rows are active, so the column is set to 
0 (false) for all the rows. In other words,

```
sqlite> SELECT count(*) FROM t1 WHERE deleted = 0;
308498

sqlite> SELECT count(*) FROM t1 WHERE deleted != 0;
0
```

> What does:   SELECT count(*) AS num FROM t1 JOIN vt1 ON t1.t1Id=vt1.t1Id 
> WHERE vt1 MATCH 'foo'; return? Dan. 

sqlite> SELECT count(*) AS num FROM t1 JOIN vt1 ON t1.t1Id=vt1.t1Id WHERE vt1 
MATCH 'foo';
80789


[snipped]

Please also see my follow-up email with an update sent yesterday, Mar 8 at 
18:44:14 UTC 2020

Many thanks.

Note: I had accidentally set the wrong mail-delivery flag on my subscription so 
I didn’t get earlier emails. It has been set right now. There is a possibility 
this email may not be a part of the original thread, in which case, my 
apologies in advance.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] speeding up an FTS5 search with a JOIN

[P Kishor]

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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] escaping search terms in queries with bind params

[P Kishor]

Using FTS5 (sqlite3 3.29.x), the following works

> SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda 
> awaiting allocation’;

but the following fails

> SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda 
> (awaiting allocation)’;
Error: fts5: syntax error near “"

Since I am doing these queries in a program, and I can’t predict what 
characters might be present in my search term, how can I properly escape the 
query so the following works (showing JavaScript syntax below)

function res(q) {
const s = 'SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v 
MATCH ?’;
return db.prepare(s).get(q);
}

res('Trematoda (awaiting allocation)’);




--
Puneet Kishor
Just Another Creative Commoner
http://punkish.org/About

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

Re: [sqlite] Feature request: extend the IN operator

[P Kishor]

On Mon, Aug 8, 2011 at 2:19 AM, Igor Tandetnik  wrote:
> Alexey Pechnikov  wrote:
>> 2011/8/7 Simon Slavin :
>>> You don't need to. The SQLite expressions I listed tell you how to achieve 
>>> the result without doing that.
>>
>> Really? And how can you perform the query like to:
>>
>> sqlite> create table t1(ids text);
>> sqlite> insert into t1 (ids) values ('1 2 3');
>> sqlite> insert into t1 (ids) values ('2 3 4');
>> sqlite> insert into t1 (ids) values ('3 4 5');
>
> I can normalize this table, then use joins.
>
>> A simple calculation: if each list of identifiers have about 1000
>> items and there are
>> 1 000 000 lists than the table of relations (t1.rowid, t2.rowid) will
>> have 1 000 000 000
>> rows!
>
> One way or the other, you need to store 1,000,000,000 pieces of information. 
> Why is it that storing them in 1,000,000 rows holding 1000 items each is 
> unremarkable, but storing them in 1,000,000,000 rows holding one item each is 
> exclamation point-worthy?
>


I will let you heavyweights duke it out, but re. the above point,
SQLite (and more databases) have a per row system overhead that can
very quickly overweight the actual data if the data are too granular
with each row storing just a tiny amount. For example, the overhead
for the cells in a raster dataset, if stored one per row, will quickly
surpass the size of the actual data.


> If reducing the number of rows is your ultimate goal, why don't you create a 
> table with one row, holding the whole data structure encoded into one huge 
> string or blob? That'll best optimize the one metric you seem to believe 
> matters the most.
>
>> It's too slow
>
> ... when compared to what alternative? Linearly scanning all those lists?
>
>> Of cource all
>> systems store lists of
>> identifiers in similar situations.
>
> I find it hard to believe that every single system does - surely systems 
> exist that do not denormalize their data this way. In fact, I doubt the 
> design you describe is common, let alone universally accepted.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about SQLite features.

[P Kishor]

On Thu, Nov 11, 2010 at 5:36 PM, Petite Abeille
 wrote:
>
> On Nov 12, 2010, at 12:31 AM, Jay A. Kreibich wrote:
>
>>  There have been many proposals to do just this, and in specific,
>>  with Lua.  Outside of some moderate technical issues, the
>>  big problem is the license.  Something like that would *never*
>>  be part of the SQLite core because the Lua license is not
>>  compatible with SQLite's license-- i.e. it is not in the public
>>  domain.
>
> hmmm... not wanting to go down lawyer lane, but... isn't Lua's MIT license as 
> liberal as it gets? Just curious.
>

Public domain is "as liberal as it gets." Nothing else is.

-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full text search FTS3 of files

[P Kishor]

On Sun, Oct 17, 2010 at 2:54 PM, pipilu  wrote:
> Hi:
> I am trying to build a sqlite3 database to index files. What I want to do is
> to keep the files in the file system on the disk (not in the database) and
> index the files with keywords such that when a search is performed, the
> right file names are returned.
>
> My question is:
> Is it possible to use FTS3 for search without storing the actual file
> contents/search terms/keywords in a row. In other words, create a FTS3
> tables with rows that only contains an ID and populate the B-Tree with
> keywords for search.

No.

Use something like e-Swish, or htdig

>
> Thanks a lot
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple prepared statements

[P Kishor]

On Fri, Oct 15, 2010 at 8:43 AM, Andrew Davison
 wrote:
> In my database I do lots of inserts, of exactly the same nature so I use
> a prepared statement, which I cache, always reseting after use. Works fine.
>
> Now I decide that I want a second type of insert, so I try to use a
> prepared statement for that as well. However it always fails. As long as
> the other prepared statement is hanging round I can't prepare a new one.
> Does this seem right or am I really soing something wrong?
>

Which language?

> Can I not have multiple prepared statements created?
>

At least with Perl DBI I can have as many prepared statements as I
want or care. Don't know if that is a Perl capability or sqlite
capability.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nth row of on sqlite DB

[P Kishor]

On Thu, Oct 14, 2010 at 11:15 AM, Kavita Raghunathan
<kavita.raghunat...@skyfiber.com> wrote:
> Please see comment
>
>
> On 10/14/10 11:02 AM, "P Kishor" <punk.k...@gmail.com> wrote:
>
>
>>> Hello,
>>> I¹ve been adding and deleting rows from the sqlite database. Now the primary
>>> ID is non-sequential.
>>>
>>>
>>>  1.  How do I get the nth entry in the database
>>>  2.  AND, How do I get the first n rows from the database ?
>>>
>>
>> Both "nth" and "first n" depend upon the sort order. Sort the table
>> the way you want, and then get the nth or the first n (use
>> LIMIT/OFFSET) to get the desired rows.
>>
>> The db by itself has no sort order, although, if you have numeric PK,
>> there is an implicit ascending sort on PK.
>>
>
> Yes, I have numeric PK. I don't want to sort the table, the way it was added
> in ascending order of PK, I want it the same way, because the entries are
> historical events based on order of occurance.


The database has no concept of "order of occurrence," you do. So,
create a column in which you can store the timestamp for when the row
was created. Then, use that to order the result and use LIMIT/OFFSET
to restrict the rows you want returned.

> Now, when I added, the PK was
> 1,2,3,...100. I have since say deleted 2,5,11 because the events are no
> longer relavant. Now I want the first 10 entries, which will be
> 1,3,4,6,7,8,9,10,12,13. You are saying I can use LIMIT/OFFSET to do that? I
> do not need to order. I'm looking for the simplest and most efficient way to
> do this. I know, arent we all :)

There is nothing complicated or inefficient about enforcing the sort
order that you want. You do need an order, because without an order
there is no sense to "first" or "first n."

PK is generally controlled by the db (although, it doesn't have to
be). PK definitely should be immutable, and non-reusable, unless you
are enforcing FKs with cascade DELETEs because it might be a foreign
key in another table.


>
> Kavita
>>>
>>> Thanks,
>>> Kavita
>>> ___
>>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nth row of on sqlite DB

[P Kishor]

On Thu, Oct 14, 2010 at 10:54 AM, Kavita Raghunathan
 wrote:
> 3. How can I make my primary ID remain sequential even after a delete of row. 
> Can sqlite somehow realign the indices after a row in the middle is deleted ?
>

If you can change the "primary ID" (sic), by which, I am assuming you
are referring to the primary key, then it won't really be the primary
key. PK should be immutable.

Create a separate ID that can be under your control, and change it as you wish.

>
> On 10/14/10 10:53 AM, "Kavita Raghunathan"  
> wrote:
>
> Hello,
> I’ve been adding and deleting rows from the sqlite database. Now the primary 
> ID is non-sequential.
>
>
>  1.  How do I get the nth entry in the database
>  2.  AND, How do I get the first n rows from the database ?
>

Both "nth" and "first n" depend upon the sort order. Sort the table
the way you want, and then get the nth or the first n (use
LIMIT/OFFSET) to get the desired rows.

The db by itself has no sort order, although, if you have numeric PK,
there is an implicit ascending sort on PK.

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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding a string in front of what is already in a field

[P Kishor]

José,

Please note Igor's very important cautionary note below --

On Mon, Oct 11, 2010 at 7:05 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> P Kishor <punk.k...@gmail.com> wrote:
>> UPDATE OpenJobs
>> SET notes = 'string to add in front\r\n' || notes
>> WHERE spid = 32;
>
> Note that SQLite doesn't understand C-style escapes. '\r' is a string 
> consisting of two characters, a backslash and a letter r. If you want to 
> insert CRLF pair, you'd need something like this:
>
> SET notes = 'string to add in front' || cast(x'0d0a' as text) || notes
>

So, if you are using a programming language, you can do like so

UPDATE OpenJobs
SET notes = ? || notes
WHERE spid = ?

and then, in your application (for example, Perl code below; note, use
of double quotes)

$sth->execute( "string to add in front\r\n", 32);

Or, in the command line sqlite3 program, you can simply hit "enter" on
your keyboard and then close-single-quote the string.

sqlite > UPDATE OpenJobs SET notes = 'string to add in front
sqlite > ' || notes WHERE spid = 32;


> Or, you can use a parameter in place of the string literal, and bind a string 
> to it in your program. Such a string can contain any characters you want.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding a string in front of what is already in a field

[P Kishor]

On Sun, Oct 10, 2010 at 11:05 PM, jose isaias cabrera
 wrote:
>
> Greetings.
>
> I would like some help with this scenario... DB name OpenJobs.
>
> id,pid,spid,notes
> 100, 24,32,'this is a test'
> 101, 24,32,'a different note'
> 102, 24,32,'yet, another different note'
> ...
>
> What I dould like to do is to update each of those notes by adding a string
> to the front of data contained in the notes.  The final DB data would look
> like this:
>
> id,pid,spid,notes
> 100, 24,32,'string to add in front\r\nthis is a test'
> 101, 24,32,'string to add in front\r\na different note'
> 102, 24,32,'string to add in front\r\nyet, another different note'
> ...
>
> I have come up with this:
>
> UPDATE OpenJobs SET notes = 'string to add in front\r\n' ||
>      (SELECT notes from OpenJobs where spid = 32) WHERE spid = 32;
>

UPDATE OpenJobs
SET notes = 'string to add in front\r\n' || notes
WHERE spid = 32;

> But I don't think it's going to work.
>
> Any help would be greatly appreciated.
>
> thanks.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADD UNIQUE CONSTRAINT

[P Kishor]

On Sun, Oct 10, 2010 at 3:08 AM, Fadhel Al-Hashim <fad...@gmail.com> wrote:
> Thank you,
>
> I was wondering about adding a new constraint to an existing column that is
> currently holding data.

As you can see from the syntax diagram, you can't just add a
constraint. You have to add a column. You can always update the new
constraint-full column with the value from the old constraint-less
column.

Or, recreate the table and copy data from the old table.


>
> On Sun, Oct 10, 2010 at 10:54 AM, P Kishor <punk.k...@gmail.com> wrote:
>
>> On Sun, Oct 10, 2010 at 2:45 AM, Fadhel Al-Hashim <fad...@gmail.com>
>> wrote:
>> > Good day,
>> >
>> > is it possible to Alter a table and add a unique constraint on one or
>> more
>> > columns?
>> >
>>
>> See http://www.sqlite.org/lang_altertable.html
>>
>> In particular --
>>
>> "The ADD COLUMN syntax is used to add a new column to an existing
>> table. The new column is always appended to the end of the list of
>> existing columns. The column-def rule defines the characteristics of
>> the new column. The new column may take any of the forms permissible
>> in a CREATE TABLE statement, with the following restrictions:
>>
>> The column may not have a PRIMARY KEY or UNIQUE constraint.
>> The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
>> CURRENT_TIMESTAMP, or an expression in parentheses.
>> If a NOT NULL constraint is specified, then the column must have a
>> default value other than NULL.
>> If foreign key constraints are enabled and a column with a REFERENCES
>> clause is added, the column must have a default value of NULL.
>> Note also that when adding a CHECK constraint, the CHECK constraint is
>> not tested against preexisting rows of the table. This can result in a
>> table that contains data that is in violation of the CHECK constraint.
>> Future versions of SQLite might change to validate CHECK constraints
>> as they are added."
>>
>> > thanks,
>> >
>> > fadhel
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> ___
>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADD UNIQUE CONSTRAINT

[P Kishor]

On Sun, Oct 10, 2010 at 2:45 AM, Fadhel Al-Hashim  wrote:
> Good day,
>
> is it possible to Alter a table and add a unique constraint on one or more
> columns?
>

See http://www.sqlite.org/lang_altertable.html

In particular --

"The ADD COLUMN syntax is used to add a new column to an existing
table. The new column is always appended to the end of the list of
existing columns. The column-def rule defines the characteristics of
the new column. The new column may take any of the forms permissible
in a CREATE TABLE statement, with the following restrictions:

The column may not have a PRIMARY KEY or UNIQUE constraint.
The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
CURRENT_TIMESTAMP, or an expression in parentheses.
If a NOT NULL constraint is specified, then the column must have a
default value other than NULL.
If foreign key constraints are enabled and a column with a REFERENCES
clause is added, the column must have a default value of NULL.
Note also that when adding a CHECK constraint, the CHECK constraint is
not tested against preexisting rows of the table. This can result in a
table that contains data that is in violation of the CHECK constraint.
Future versions of SQLite might change to validate CHECK constraints
as they are added."

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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using SQLite with mod_perl

[P Kishor]

On Sat, Oct 9, 2010 at 6:24 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 9 Oct 2010, at 7:49am, P Kishor wrote:
>
>> 
>
> My answers to these things are a little weird and I'm not sure I understand 
> at all what you're doing.  But it's a weekend so I'll answer anyway and let 
> you correct me where I've misunderstood it.
>
>>    SELECT col1 FROM table WHERE condition;
>>    if (col1 exists) {
>>        UPDATE table SET col2 = 
>>    }
>>
>> Well, I get the "db is locked" error at the UPDATE statement.
>
> Because you're trying to UPDATE a table while you're in the middle of looking 
> at it, and the UPDATE you make might change the thing you're looking at, 
> which would ruin the SELECT.

I am not. The above was pseudo code.

>There are two cures:
>
> Cure#1: Do the whole SELECT first, and store the answer in an array.  Then 
> work through the array issuing UPDATE commands.  This is not the most 
> efficient way to use SQL but it makes sense to people who are used to 
> programming.
>
> Here is cure #2, which is more likely to be the SQL way of doing it.
>
> I'm not really sure what you mean by your '(col1 exists)' bit, but let's get 
> rid of it for a second and look at the rest of it:
>
>>    SELECT col1 FROM table WHERE condition;
>>        UPDATE table SET col2 = 
>
> The way you do this in SQL is this:
>
> UPDATE table SET col2 =  WHERE condition
>
> So you have one instruction that does all the work for you, including 
> ignoring records it doesn't need to change.  Now, what did you mean by '(col1 
> exists)' ?


Pseudo code.

>
>> So, I want all the perl modules to be loaded when Apache2 starts, and
>> then a $dbh created for each user when the user comes to the web site,
>> but not recreated for the same user on every reload.
>
> I think you may have misunderstood how Apache does its job.  Unless you do 
> things in an unusual manner, Apache treats each page request as a separate 
> 'program',  It spawns a separate 'httpd' process to respond to each request, 
> and the various processes don't talk to one-another.  Variables and handles 
> belong to one particular request for a web page, and disappear as soon as the 
> reply to that request has been sent.
>


Yes, that is how Apache under normal CGI works. But Apache with
mod_perl is a totally different beast. Perl is compiled inside Apache,
and my entire program is compiled and loaded in memory once when
Apache starts up.

Apache still starts separate processes for each request, but all those
processes use the same instance of the once compiled program handled
by the instance of Perl compiled within Apache.

Hence, variables can be shared within processes unless care is taken
to program so they don't.

> So you can't store a database handle between page requests.  Nor should you 
> want to, because you won't be able to tell when the last request comes: any 
> user can close their browser window at any time and you'd never know when to 
> do sqlite3_close().  You close the connection after servicing each web page 
> request.
>
> Treat the servicing of every web page request as a separate running of your 
> program: once it knows it's going to need a SQLite database it opens it using 
> its own handle, and it closes it once it had done all the SQL stuff it's 
> going to need to do.  Of course, if you have many users, each with web 
> browsers open at the same time, then you have many different processes 
> accessing your SQLite database, and you have to handle multi-user situations 
> correctly.


No, to the extent that I understand, mod_perl does not work that way.

My problem is occurring because a shared_lock on the entire db file is
being created for the SELECT, and then that lock is not being
released, so the lock required for the UPDATE is not being given out.

This may be as explained in http://www.sqlite.org/faq.html#q6

I am just trying to solve the above. It may well be that sqlite and
mod_perl may not be good companions (in which case, I am up a 
creek without a db, and will have to look for an alternative storage
solution).

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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] using SQLite with mod_perl

[P Kishor]

This is a perl question really, so apologies to the SQLite community.
However, I am stuck, and I am flailing on various forums
(perlmonks/stackoverflow), hoping to strike lucky. My problem is that
I am running into the "database locked" error under mod_perl with
Apache2. I thought I had surmounted this problem, but it is back with
a vengeance.

Here is what is going on in a single web action --

SELECT col1 FROM table WHERE condition;
if (col1 exists) {
UPDATE table SET col2 = 
}

Well, I get the "db is locked" error at the UPDATE statement. Now, in
my httpd.conf I started using

PerlModule Apache::DBI

and I thought my troubles were over. Except, this lock problem has
started rearing up again. I have tried a few other things, such as
starting every db action with `$dbh->begin_work` and ending with
`$dbh->commit`, but I am already using `AutoCommit => 1` so I am not
sure if that even has any effect.

My application is being loaded in Apache conf file with


SetHandler perl-script
PerlHandler Plack::Handler::Apache2
PerlSetVar psgi_app /path/to/application.pl


So, I want all the perl modules to be loaded when Apache2 starts, and
then a $dbh created for each user when the user comes to the web site,
but not recreated for the same user on every reload. I am assuming
that would be the correct way to work speedily.

Perhaps SQLite is the wrong tool to use with mod_perl (or any
persistent web environment), but I want to establish that for sure
before trying some other db.

Any ideas?

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

Re: [sqlite] Involving in sqlite development

[P Kishor]

why don't you start with...

On Fri, Oct 8, 2010 at 8:28 AM, sjtirtha  wrote:
> Hi,
>
> Here is what I want to do:
> 1. I want to learn more about SQL Database implementation


the above. You will be quite busy doing the above. When you are good
at the above, you can definitely contribute back by helping other
newcomers on the list.


> 2. During the learn I want to give back what I get to the community
>
> I do not have really C programming experience. But I have 8 years
> programming experience in
> several languages: PHP, JavaScript, Java, C++, ABAP
> And I'm willing to learn C.
>
> Concrete tasks what I have in my mind:
> 1. setup an development environment for SQLite (I'm using Ubuntu)
>    Do you have any preference which editor or IDE should I use?
> 2. Start looking for bugs that easy to fix, but nobody has time to fix it
>    Could you give me some hints which bugs are easy to fix for a
> starter like me?
>
> P.S. I just saw, there is developer mailing list for sqlite. Should I
> continue my query there?
>
> Regards,
> Steve
>
>
>
> On Thu, Oct 7, 2010 at 4:26 PM, sjtirtha  wrote:
>> Hi,
>>
>> I'm interested involving in sqlite development.
>> How can I start it?
>
>
> You really need to explain further and more clearly what you want to
> do before anyone will be able to guide you.
>
>
>>
>> Regards
>> Steve
>> ___
>> sqlite-users mailing list
>> sqlite-users at 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Involving in sqlite development

[P Kishor]

On Thu, Oct 7, 2010 at 4:26 PM, sjtirtha  wrote:
> Hi,
>
> I'm interested involving in sqlite development.
> How can I start it?


You really need to explain further and more clearly what you want to
do before anyone will be able to guide you.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up DELETE of a lot of records

[P Kishor]

On Thu, Oct 7, 2010 at 11:05 AM, Michele Pradella
 wrote:
>  Hi all, I have a question about how to speed up a DELETE statement.
> I have a DB of about 3GB: the DB has about 23 millions of records.
> The DB is indexed by a DateTime column (is a 64 bit integer), and
> suppose you want to delete all records before a date.
> Now I'm using a syntax like this (I try all the statement with the
> sqlite shell):
> suppose to use __int64 DateValue=the date limit you want to delete
>
> DELETE FROM table_name WHERE DateTime

What is the speed of

SELECT FROM table WHERE DateTime >= DateValue;

If the above speed is acceptable, then try the following

CREATE TABLE tmp AS SELECT FROM table WHERE DateTime >= DateValue;
DROP TABLE table;
ALTER TABLE tmp RENAME to table;


> the statement is trying to delete about 5 millions records and it takes
> about 4-5minutes.
> Is there a way to try to speed up the DELETE? I already try to put the
> DELETE statement between a BEGIN; COMMIT; statement, but same result.
> After the delete complete I have a -wal file of about 600MB: this file
> is not deleted even if I disconnect from the database.
> Is that the right behavior? I thought that when the last DB connection
> terminate the -wal file is reintegrated in the DB, but it's not.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vertical -> Horizontal transformation

[P Kishor]

On Wed, Sep 29, 2010 at 11:34 AM, Andy Chambers
 wrote:
> Hi,
>
> I've got a nice normalized table and need to produce a de-normalized
> view of this table (i.e. convert it
> from tall skinny, into wide short table).  In order to do this, I was
> planning on just joining the
> table to itself for each "value" that needs to be turned into a
> column.  However, it turns out there's
> 96 values so I run into the "only 64 tables in a join" constraint.  Is
> there another idiom for
> doing this sort of transformation or should I just use two helper
> tables with 33 values in each and
> then join them.
>
> For those who have used other databases, is it quite common to have
> this constraint?  This seems
> like it would be quite a common problem in ETL projects (admittedly
> it's perhaps unusual to be
> using sqlite for an ETL project).
>
> Hope I've made myself clear.


You would make yourself even clearer if you gave the schema for both
the source and the desired target tables.

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



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

Re: [sqlite] Length=10

[P Kishor]

On Wed, Aug 25, 2010 at 10:05 AM, Ted Rolle Jr.  wrote:
> I have a table of UPCs with lengths varying from 6 to 12.  I'd like to
> print those with length=10 to a file for printing.
> SELECT *
>  FROM UPCs
>  WHERE LENGTH(UPC)=10;
> works just fine.  But when I export the table I naturally get the whole
> table.  Would a View help here?  Or create a temporary table.
> I believe there's a simple solution, but it eludes me.  For now.

in the sqlite3 shell, set output mode to file, and then select as
above. All your select output will go to your file.


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the number of rows in a a table?

[P Kishor]

On Tue, Jul 20, 2010 at 11:41 AM, dmsmsm  wrote:
>
> how to get the number of rows in a a table? what is the function to get that?
> Please add a sample code to achive that.


SELECT Count(*) FROM table;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ON conditions vs. WHERE conditions

[P Kishor]

On Tue, Jul 20, 2010 at 9:43 AM, Jean-Christophe Deschamps
 wrote:
> What is the rationale about placing complex conditions in the ON part
> of an inner join rather than in an WHERE clause?

My sense is that it is not so much about "complexity" but more about
the logic of the process. The ON conditions are a part of the
description of the "source table," which is really a virtual table
made up by JOINing several tables using certain conditions, the ON
conditions.

Once the virtual table is described, you tell what to get out of that
(the SELECT conditions), and tell how to restrict/filter that
information (the WHERE conditions).


>
> I understand that the ON clause will limit the temporary table
> processed by a subsequent WHERE clause, while a larger table will be
> filtered by the WHERE part if no selective ON clause is present.
> But with a complex condition (20+ sub-conditions on various part of
> joined tables) requiring full tables scan, aren't both solutions
> essentially equivalent?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to concat column

[P Kishor]

On Fri, Jul 9, 2010 at 2:35 PM, Peng Yu <pengyu...@gmail.com> wrote:
> On Sun, Jul 4, 2010 at 7:15 PM, P Kishor <punk.k...@gmail.com> wrote:
>> On Sun, Jul 4, 2010 at 6:15 PM, Peng Yu <pengyu...@gmail.com> wrote:
>>> Hi,
>>>
>>> I only find row-wise concatenation by not column-wise.
>>>
>>> For example, I have table
>>>
>>> x1 y1
>>> x1 y2
>>> x2 y3
>>> x4 y4
>>>
>>> I want to have the second column concatenated based on the value in
>>> the first column to get the new table
>>> x1 y1y2
>>> x2 y3y4
>>>
>>> Moreover, I want to have a spectator (e.g., ',') in the second column.
>>> x1 y1,y2
>>> x2 y3,y4
>>>
>>> Could you show me if it is possible to do this in sqlite3?
>>
>>
>> sqlite> CREATE TABLE t (a, b);
>> sqlite> INSERT INTO t VALUES ('x1', 'y1');
>> sqlite> INSERT INTO t VALUES ('x1', 'y2');
>> sqlite> INSERT INTO t VALUES ('x2', 'y3');
>> sqlite> INSERT INTO t VALUES ('x4', 'y4');
>> sqlite> SELECT * FROM t;
>> a           b
>> --  --
>> x1          y1
>> x1          y2
>> x2          y3
>> x4          y4
>> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
>> a           Group_concat(b)
>> --  ---
>> x1          y1,y2
>> x2          y3
>> x4          y4
>> sqlite> UPDATE t SET a = 'x2' WHERE a = 'x4';
>> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
>> a           Group_concat(b)
>> --  ---
>> x1          y1,y2
>> x2          y3,y4
>> sqlite>
>
> Is there a way to reverse the operation done by Group_concat.
>
> x1          y1,y2
> x2          y3,y4
>
> Suppose that I start with the above table, how to convert it to the
> following table?
>
> x1          y1
> x1          y2
> x2          y3
> x2          y4
>


Use a programming language.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Numbers as CHARs.

[P Kishor]

On Tue, Jul 6, 2010 at 8:45 PM, Pavel Ivanov  wrote:
>>> The receiving field is defined as CHAR; [snip]
>> SQLite has no such type.  Define the fields as TEXT instead:
>
> Simon, please don't confuse poor users. SQLite will work perfectly and
> indistinguishably well with both CHAR and TEXT. Please read the link
> you gave more carefully (hint: bullet number 2 in the section 2.1).
>
>> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>
> And your propose is nothing better than original one. The problem is
> you both are trying to insert into text field a number. Leading zeros
> in the number can never be significant, so they are trimmed before
> this number is converted to text. The solution is to put single quotes
> around anything that supposed to be treated as text.

Well, in defense of Simon, he is partially correct, in that, setting
the right type would help. However, he errs in that he does not advise
the OP to use single quotes to delimit the string. Consider the
following --

sqlite> CREATE TABLE UPCs (UPC VLADIVOSTOK);
sqlite> INSERT INTO UPCs VALUES ('043000205563');
sqlite> SELECT * FROM UPCs;
UPC
---
43000205563
sqlite> SELECT Typeof(UPC) FROM UPCs;
Typeof(UPC)
---
integer
sqlite> .s
CREATE TABLE UPCs (UPC VLADIVOSTOK);
sqlite> INSERT INTO UPCs VALUES ('foobar');
sqlite> SELECT Typeof(UPC) FROM UPCs;
Typeof(UPC)
---
integer
text
sqlite>


So, because type VLADIVOSTOK is not recognized, sqlite tries to
convert any value entered, even if it is delimited with single quotes,
to something recognizable. I guess it starts with INT, and since it is
able to convert '043000205563' to integer, that is what it does. In
the case of 'foobar', it can't convert it to integer, so it converts
it to a text string.

At least, that is how I understand it.

This whole type and affinity thing seems to be the source of much
confusion for many folks. I am sure I would also be confused by it if
I spent more than a moment on it. Since I do all my checks for data in
and out in my application, I really don't ever bother with this stuff,
but I wonder if there were some way to reduce this confusion in the
minds of others.


>
>
> Pavel
>
> On Tue, Jul 6, 2010 at 6:17 PM, Simon Slavin  wrote:
>>
>> On 6 Jul 2010, at 11:10pm, Ted Rolle, Jr. wrote:
>>
>>> The receiving field is defined as CHAR; [snip]
>>
>> SQLite has no such type.  Define the fields as TEXT instead:
>>
>> 
>>
>> Then try 'UPDATE UPCs SET UPC=043000205563;' and see what you get.
>>
>>> Last question: is this an example of SQLite's "typelessness"?
>>
>> SQLite has types.  It just doesn't require every value in the same column to 
>> be of the same type.
>>
>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Numbers as CHARs.

[P Kishor]

On Tue, Jul 6, 2010 at 5:10 PM, Ted Rolle, Jr.  wrote:
> I, (or more to the point, SQLite) can't seem to retain leading zeros
> on numbers.
>
> The receiving field is defined as CHAR;
> I'm using the SQLite Manager in Firefox.
> I've also tried sqlite3 from the command line.
> Here's a typical (and minimal) statement:
> UPDATE UPCs SET UPC=043000205563;
> UPDATE UPCs SET UPC=CAST(043000205562 AS CHAR) WHERE rowid=93; didn't
> work either.
>
> SELECT TYPEOF(UPC) FROM UPCs WHERE rowid=93; returns 'text'.
>
> I'm reluctant to put quotes (single or double) around the values as
> they are retained in the field.
>

I have no idea why you would say that. It works just fine.

sqlite> CREATE TABLE UPCs (UPC TEXT);
sqlite> INSERT INTO UPCs VALUES ('043000205563');
sqlite> SELECT * FROM UPCs;
UPC

043000205563
sqlite>



> On other text/numeric fields I've added a textual value; that seems to
> "fix" the problem.  But not really.
>
> Last question: is this an example of SQLite's "typelessness"?
>
> Ted
> --
> __
> 3.14159265358979323846264338327950      Let the spirit of pi
> 2884197169399375105820974944592307   spread all over the world!
> 8164062862089986280348253421170679 http://pi314.at  PI VOBISCUM!
> ==
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "use database" in sqlite3?

[P Kishor]

On Tue, Jul 6, 2010 at 11:00 AM, Peng Yu  wrote:
> Hi,
>
> I can attach a database to the current session. But I have to
> explicitly specify the table name to refer to any tables in it (such
> 'create_index' in 'create_index.sqlite_master'). Is there a command
> similar to 'use database' (mysql) in sqlite3 so that I can make a
> particular database as default? (I don't find such command, but please
> let me know in case if I miss anything.)
>
> sqlite> attach database 'create_index.db' as create_index;
> sqlite> select * from create_index.sqlite_master;
>

As far as I know, there is no such command as 'use '. You
can shorten your db prefix to a single letter, and save typing, if
that bothers you

ATTACH DATABASE 'very_long_name_of_my_external_db_file.sqlite' AS v;
SELECT * FROM v.table;

In my personal view, always fully qualifying your source table and
columns is very good practice as it eliminates any ambiguity.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to concat column

[P Kishor]

On Sun, Jul 4, 2010 at 6:15 PM, Peng Yu  wrote:
> Hi,
>
> I only find row-wise concatenation by not column-wise.
>
> For example, I have table
>
> x1 y1
> x1 y2
> x2 y3
> x4 y4
>
> I want to have the second column concatenated based on the value in
> the first column to get the new table
> x1 y1y2
> x2 y3y4
>
> Moreover, I want to have a spectator (e.g., ',') in the second column.
> x1 y1,y2
> x2 y3,y4
>
> Could you show me if it is possible to do this in sqlite3?


sqlite> CREATE TABLE t (a, b);
sqlite> INSERT INTO t VALUES ('x1', 'y1');
sqlite> INSERT INTO t VALUES ('x1', 'y2');
sqlite> INSERT INTO t VALUES ('x2', 'y3');
sqlite> INSERT INTO t VALUES ('x4', 'y4');
sqlite> SELECT * FROM t;
a   b
--  --
x1  y1
x1  y2
x2  y3
x4  y4
sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
a   Group_concat(b)
--  ---
x1  y1,y2
x2  y3
x4  y4
sqlite> UPDATE t SET a = 'x2' WHERE a = 'x4';
sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
a   Group_concat(b)
--  ---
x1  y1,y2
x2  y3,y4
sqlite>


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to supply no values when inserting a record?

[P Kishor]

On Fri, Jul 2, 2010 at 9:28 PM, Peng Yu <pengyu...@gmail.com> wrote:
> On Fri, Jul 2, 2010 at 8:58 PM, P Kishor <punk.k...@gmail.com> wrote:
>> On Fri, Jul 2, 2010 at 8:52 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>>>> I tried the following two commands. Neither of them work. Would
>>>> you please let me know what is the command to insert a record with the
>>>> default value?
>>>
>>> Try this:
>>> insert into test default values;
>>>
>>>
>>
>>
>> Peng, Now that Pavel has shown you the correct syntax, you can also
>> look at the syntax diagram at http://www.sqlite.org/lang_insert.html
>> and familiarize yourself with how to figure out the syntax for common
>> SQL commands.
>>
>> Also, since you noted that you don't have much SQL experience, look
>> for free tutorials on the web. There are many SQL tutorials that you
>> can benefit from.
>
> I'm learning from the book The Definitive Guide to SQLite. It seems to
> me that the sql command are quite different from common programming
> languages, such as C++ and Java, which are use functions and member
> functions. It takes me a while to learn sql's syntax well.


Yes. SQL *is* very different from most programming languages. Many
things are easy to do in SQL, while other in other programming
languages. Like any language, using SQL well also will take time and
patience, and lots of questions and answers.

>
> Thank you for point me these diagrams. And I'm actually aware of them.
> Only if I understand something, I can check it from the syntax
> diagrams. But these diagrams are too concise for me to learn from
> them.

The diagrams actually list the legal syntax for any command as far as
sqlite is concerned. By following the arrows (and the various paths
that they can take), you can figure out what is allowable and what is
not. Other than for those who might be vision-impaired (and thus, may
need spoken text), the diagrams are actually quite fantastic.

> I hope you understand that I have trying to learn as much as I
> can from the book that I have. But occasionally, I may ask some simple
> questions (to experienced users) more frequently in a short period of
> time.

Absolutely not a problem. Hopefully soon you will be able to help
others on the list.

>
> Also, I am wondering what books or resources you used when you first
> learned sql. I have tried to learn sql from the document and tutorial
> mysql (I was trying to use mysql). But in general, I feel these
> tutorials are not complete and not organized well. I feel that the
> book that I have may be the best starting point for a new user.

I personally think that many of the tutorials are quite good. For
example, see http://sqlzoo.net which also allows you to choose
different db programs and see the syntax difference. SQLite is pretty
faithful to the sql standard, so for most of your needs, plain,
vanilla sql should be pretty good. Another great resource is the
documentation for Postgres (probably the best documentation of all...
for every version of Pg released, all you have to do is change the
version number in the URI to get the specific docs).

>
> --
> Regards,
> Peng
>



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

Re: [sqlite] How to supply no values when inserting a record?

[P Kishor]

On Fri, Jul 2, 2010 at 8:52 PM, Pavel Ivanov  wrote:
>> I tried the following two commands. Neither of them work. Would
>> you please let me know what is the command to insert a record with the
>> default value?
>
> Try this:
> insert into test default values;
>
>


Peng, Now that Pavel has shown you the correct syntax, you can also
look at the syntax diagram at http://www.sqlite.org/lang_insert.html
and familiarize yourself with how to figure out the syntax for common
SQL commands.

Also, since you noted that you don't have much SQL experience, look
for free tutorials on the web. There are many SQL tutorials that you
can benefit from.


> Pavel
>
> On Fri, Jul 2, 2010 at 9:40 PM, Peng Yu  wrote:
>> Hi,
>>
>> create table test (value text default 'unknown', value2 text default 
>> 'unknown');
>> insert into test (value) values('xxx');
>>
>> The above code works correctly. But if there is only one column with a
>> default value, I don't see how to insert a record with the default
>> value. I tried the following two commands. Neither of them work. Would
>> you please let me know what is the command to insert a record with the
>> default value?
>>
>> create table test (value text default 'unknown');
>> insert into test () values();
>> insert into test values();
>>
>> --
>> Regards,
>> Peng
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL:Re: How to select an entry that appears <=ntimes and only show n times if it appears more than n times?

[P Kishor]

On Fri, Jul 2, 2010 at 12:06 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> P Kishor <punk.k...@gmail.com> wrote:
>> I think what Peng wants is that given table of type_id
>>
>> 5
>> 5
>> 5
>> 5
>> 5
>> 4
>> 4
>> 4
>> 7
>> 7
>> 8
>> 8
>> 8
>> 8
>>
>> if 'n' is 3, the desired result is
>>
>> 5
>> 5
>> 5
>> 4
>> 4
>> 4
>> 7
>> 7
>> 8
>> 8
>> 8
>>
>> I don't know how to do that with sql.
>
> Well, if you insist:
>
> select type_id from foods f1
> where (
>    select count(*) from foods f2
>    where f2.type_id = f1.type_id
>      and f2.rowid < f1.rowid) < 3;
>

I was going to add "That is an Igor-question" to "I don't know how to
do that with sql." I have no idea how you do this, but if ever I meet
you in person, I will be too awestruck to say anything beyond
"SELECT.."


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXTERNAL:Re: How to select an entry that appears <=n times and only show n times if it appears more than n times?

[P Kishor]

On Fri, Jul 2, 2010 at 11:35 AM, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
> I don't know about anybody else but I can't tell what you want to do.
>
> Have you got some sample data and the results you expect from it?
>
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Peng Yu
> Sent: Fri 7/2/2010 11:29 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] How to select an entry that appears <=n times 
> and only show n times if it appears more than n times?
>
>
>
> On Fri, Jul 2, 2010 at 11:19 AM, P Kishor <punk.k...@gmail.com> wrote:
>> On Fri, Jul 2, 2010 at 11:19 AM, P Kishor <punk.k...@gmail.com> wrote:
>>> On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu <pengyu...@gmail.com> wrote:
>>>> Hi,
>>>>
>>>> SELECT DISTINCT type_id FROM foods;
>>>>
>>>> If I use 'distinct', any entry that shows up greater or equal to one
>>>> time will only appear once. But I want to select an entry that appears
>>>> <=n times and only show n times if it appears more than n times. I
>>>> think that "group by" might help. But I'm not familiar with SQL enough
>>>> yet. Would you please let me know what command to use?
>>>>
>>>
>>> Try
>>>
>>> SELECT , Count(type_id)
>>> FROM foods
>>> GROUP BY 
>>> HAVING Count(type_id) < n
>>
>> make that
>>
>> HAVING Count(type_id) <= n
>
> But this doesn't show anything that count more than n times. I want
> the type_id shows up more than n times in the database only appear n
> times in the result of the query.
>
>
>

I think what Peng wants is that given table of type_id

5
5
5
5
5
4
4
4
7
7
8
8
8
8

if 'n' is 3, the desired result is

5
5
5
4
4
4
7
7
8
8
8

I don't know how to do that with sql. I would solve it using a
programming language.

Something like

SELECT type_id WHERE type_id = 5 LIMIT n
UNION
SELECT type_id WHERE type_id = 8 LIMIT n
UNION

except. LIMIT, afaik, is applied *after* all the UNIONs.



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[P Kishor]

On Fri, Jul 2, 2010 at 11:19 AM, P Kishor <punk.k...@gmail.com> wrote:
> On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu <pengyu...@gmail.com> wrote:
>> Hi,
>>
>> SELECT DISTINCT type_id FROM foods;
>>
>> If I use 'distinct', any entry that shows up greater or equal to one
>> time will only appear once. But I want to select an entry that appears
>> <=n times and only show n times if it appears more than n times. I
>> think that "group by" might help. But I'm not familiar with SQL enough
>> yet. Would you please let me know what command to use?
>>
>
> Try
>
> SELECT , Count(type_id)
> FROM foods
> GROUP BY 
> HAVING Count(type_id) < n

make that

HAVING Count(type_id) <= n


>
>
>> --
>> Regards,
>> Peng
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to select an entry that appears <=n times and only show n times if it appears more than n times?

[P Kishor]

On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu  wrote:
> Hi,
>
> SELECT DISTINCT type_id FROM foods;
>
> If I use 'distinct', any entry that shows up greater or equal to one
> time will only appear once. But I want to select an entry that appears
> <=n times and only show n times if it appears more than n times. I
> think that "group by" might help. But I'm not familiar with SQL enough
> yet. Would you please let me know what command to use?
>

Try

SELECT , Count(type_id)
FROM foods
GROUP BY 
HAVING Count(type_id) < n


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

[P Kishor]

On Tue, Jun 29, 2010 at 10:46 AM, Tim Romano <tim.romano...@gmail.com> wrote:
> Puneet,
> I am not trying to give Adobe any sort of primacy; but I wouldn't call them
> unimportant either.
>
> The core concern, at least as I see it, is the undesirable effects of
> sharing data between implementations that do not handle INT and INTEGER
> primary keys compatibly.
>

There is no incompatibility. CREATE TABLE t AS SELECT ... is not meant
to clone a table. Period. Done.

Don't depend on it, don't expect it, don't promote it.





> I  don't use and won't use the "create table as select ... " syntax, but
> others might, hence the advisory.
>
> Here's a little story:  years ago, back in the days of dumb terminals,
> oncology patients were dying in abnormally high numbers not long after
> receiving their radiation treatment. Turned out that the software that
> controlled the radiation dosage was written to be used with a dumb-terminal
> that did not have cursor-positioning keys. It might have been a VT100. I
> don't recall. You had to hit RETURN to move from field to field. But the
> hospital had installed a  "compatible" terminal that did have these
> cursor-arrow keys.  The hospital technician, ignorant of the consequences,
> were in the habit of using the arrow keys to move the cursor around the
> radiation dosage and timing screen, and then would key in their values and
> execute the program. The numbers they keyed in were actually hitting the
> wrong input fields.  The users' eyes told them they were on the dosage field
> but they were actually keying in the value for the time, or vice-versa, and
> the patients were being given lethal doses of radiation.
>
> I saw this on 60 minutes or 20/20 or some show like that  -- I didn't write
> that program or install the compatible terminal. But since then, whenever I
> see the opportunity for things going FUBAR, I will say something.
>
> Regards
> Tim Romano
> Swarthmore PA
>
>
>
>
>
>
>
>
> On Tue, Jun 29, 2010 at 11:10 AM, P Kishor <punk.k...@gmail.com> wrote:
>
>> On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano <tim.romano...@gmail.com>
>> wrote:
>> > But there may be an argument for making the cloning more precise.
>>
>>
>> The issue is that CREATE TABLE t AS SELECT... is not meant to clone a
>> table. Not too long ago I encountered the same issue (search the mail
>> archives). Igor, as usual, explained it succinctly and effectively --
>>
>> sqlite> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
>> sqlite> INSERT INTO t (b) VALUES ('foo');
>> sqlite> INSERT INTO t (b) VALUES ('bar');
>> sqlite> INSERT INTO t (b) VALUES ('baz');
>> sqlite> SELECT * FROM t;
>> a           b
>> --  --
>> 1           foo
>> 2           bar
>> 3           baz
>> sqlite> CREATE TABLE u AS SELECT a + 0.5 AS a, b FROM t;
>> sqlite> SELECT * FROM u;
>> a           b
>> --  --
>> 1.5         foo
>> 2.5         bar
>> 3.5         baz
>> sqlite> .s
>> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
>> CREATE TABLE u(a,b TEXT);
>> sqlite> SELECT Typeof(a) FROM u;
>> Typeof(a)
>> --
>> real
>> real
>> real
>> sqlite> CREATE TABLE v (a INTEGER PRIMARY KEY, b TEXT);
>> sqlite> INSERT INTO v SELECT * FROM t;
>> sqlite> SELECT * FROM v;
>> a           b
>> --  --
>> 1           foo
>> 2           bar
>> 3           baz
>> sqlite> DELETE FROM v;
>> sqlite> INSERT INTO v SELECT a + 0.5, b FROM t;
>> Error: datatype mismatch
>> sqlite>
>>
>>
>> In other words, don't use CREATE TABLE .. AS SELECT .. to clone.
>> Instead, create the new table the way you want to, and then use INSERT
>> to populate it with data from the old table.
>>
>>
>> > It's a
>> > bit of a mess, or at least it seems so to me because my first ten years
>> of
>> > database work was done with PICK, a database that was developed by PICK
>> > Systems but licensed to many companies and marketed under different
>> brands
>> > with subtle functionality differences, yet applications that adhered to
>> the
>> > core PICK spec were completely portable across all implementations. I
>> think
>> > SQLite implementations should probably adhere to a core spec but I
>> recognize
>> > this as my bias, not dogma.
>> >
>> > Adobe (and possibly Google and some others who are involved in
>> coordinating
>> > their SQLite imple

Re: [sqlite] create table {table-name} as select.... table definition is imperfectly cloned

[P Kishor]

On Tue, Jun 29, 2010 at 9:58 AM, Tim Romano  wrote:
> But there may be an argument for making the cloning more precise.


The issue is that CREATE TABLE t AS SELECT... is not meant to clone a
table. Not too long ago I encountered the same issue (search the mail
archives). Igor, as usual, explained it succinctly and effectively --

sqlite> CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
sqlite> INSERT INTO t (b) VALUES ('foo');
sqlite> INSERT INTO t (b) VALUES ('bar');
sqlite> INSERT INTO t (b) VALUES ('baz');
sqlite> SELECT * FROM t;
a   b
--  --
1   foo
2   bar
3   baz
sqlite> CREATE TABLE u AS SELECT a + 0.5 AS a, b FROM t;
sqlite> SELECT * FROM u;
a   b
--  --
1.5 foo
2.5 bar
3.5 baz
sqlite> .s
CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT);
CREATE TABLE u(a,b TEXT);
sqlite> SELECT Typeof(a) FROM u;
Typeof(a)
--
real
real
real
sqlite> CREATE TABLE v (a INTEGER PRIMARY KEY, b TEXT);
sqlite> INSERT INTO v SELECT * FROM t;
sqlite> SELECT * FROM v;
a   b
--  --
1   foo
2   bar
3   baz
sqlite> DELETE FROM v;
sqlite> INSERT INTO v SELECT a + 0.5, b FROM t;
Error: datatype mismatch
sqlite>


In other words, don't use CREATE TABLE .. AS SELECT .. to clone.
Instead, create the new table the way you want to, and then use INSERT
to populate it with data from the old table.


> It's a
> bit of a mess, or at least it seems so to me because my first ten years of
> database work was done with PICK, a database that was developed by PICK
> Systems but licensed to many companies and marketed under different brands
> with subtle functionality differences, yet applications that adhered to the
> core PICK spec were completely portable across all implementations. I think
> SQLite implementations should probably adhere to a core spec but I recognize
> this as my bias, not dogma.
>
> Adobe (and possibly Google and some others who are involved in coordinating
> their SQLite implementations --I'm not fully "up" on the details of who all
> are involved in that cooperative effort, or the extent to which they have
> reconciled their implementations) treats INT primary keys as aliases for
>  the RowId, whereas SQLite does so only with INTEGER primary keys.
>
> Should one ever share  a SQLite database that happens to contain tables
> defined via the "CREATE TABLE X as SELECT..."  cloning approach with someone
> who is using an Adobe-based tool, the query results returned by the
> Adobe-based tool will not jibe with the results returned by the SQlite
> command-line utility.  On the table with INT primary key, Adobe will be
> fetching the row by row-id.
>
> If a table  has foreign-key value of 10, and is joined to a table with an
> INT primary key, Adobe will bring over the 10th row in the table even though
> the value in the primary key column of that table may or may not contain the
> number 10.
>
> In Adobe:
>
> CREATE TABLE FOO (id INT PRIMARY KEY, name TEXT)
> insert into FOO(1,'Groucho')
> insert into FOO(2,'Chico')
> Insert into FOO(999, 'Harpo')
>
> the row id of Harpo's row is 3 (it's PK value is 999) and Harpo will be
> returned by a join when the foreign key  = 3 not when the foreign key = 999.
>


Right. But, the issue here may be that the sqlite world is much, much
bigger than either the Adobe or the Google worlds. While those two
companies may be a part of the sqlite consortium, sqlite itself runs
on way more platforms and in configurations than Adobe+Google support.

In any case, I am not even sure if the sql standard calls for CREATE
TABLE t AS SELECT... to produce a clone of a table.




>
> Regards
> Tim Romano
> --
> Most people assume one is wearing underwear.  Not so for intelligence.
>
>
> On Tue, Jun 29, 2010 at 9:46 AM, Jay A. Kreibich  wrote:
>
>> On Tue, Jun 29, 2010 at 06:59:18AM -0400, Tim Romano scratched on the wall:
>> > CREATE  TABLE "main"."proto" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT
>>  NOT
>> > NULL , "name" TEXT)
>> > CREATE  TABLE "main"."clone"  as select * from PROTO
>> >
>> > The primary key of table CLONE is defined as "INT" not "INTEGER".
>>
>>   The only information preserved by CREATE TABLE ... AS SELECT is the
>>  column affinity (not "type").  These are equivalent, and both
>>  translate to an INTEGER affinity.
>>
>>
>>
>>  ...which actually surprises me, since I was under the impression
>>  CREATE TABLE ... AS SELECT always produced NONE affinities.  Is this
>>  a semi-recent (last year) change?
>>
>>   -j
>>
>>
>> --
>> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>>
>> "Intelligence is like underwear: it is important that you have it,
>>  but showing it to the wrong people has the tendency to make them
>>  feel uncomfortable." -- Angela Johnson
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> 

Re: [sqlite] Column names in SQL

[P Kishor]

On Mon, Jun 28, 2010 at 10:07 AM, Serdar Genc <serdar.g...@gmail.com> wrote:
> I have already tried it but not working.. :(
>

Works for me.

punk...@lucknow ~$sqlite3
-- Loading resources from /Users/punkish/.sqliterc
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t ("a[b]");
sqlite> INSERT INTO t VALUES ('foo');
sqlite> INSERT INTO t VALUES (3);
sqlite> SELECT * FROM t;
a[b]
--
foo
3
sqlite> SELECT "a[b]" FROM t WHERE "a[b]" = 3;
a[b]
--
3
sqlite>

You are doing something else. You are not describing the entire
problem. How are you accessing your database?


> On Mon, Jun 28, 2010 at 6:01 PM, P Kishor <punk.k...@gmail.com> wrote:
>
>> On Mon, Jun 28, 2010 at 9:58 AM, Serdar Genc <serdar.g...@gmail.com>
>> wrote:
>> > Hi everyone,
>> >
>> > I have a problem related to column names . I have a column name as a[b]
>> in
>> > my table but
>> > this creates a problem when using SELECT statement as
>> > SELECT a[b] from Table. I know [] is a special character but How would I
>> > tell SQlite that field
>> > name is a[b] and I am not using [] with a special purpose..
>>
>> Try SELECT "a[b]"
>>
>>
>> >
>> > Thanks in advance,
>> > Serdar
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
>
> Serdar Genç
> web: http://www.iptakip.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Column names in SQL

[P Kishor]

On Mon, Jun 28, 2010 at 9:58 AM, Serdar Genc  wrote:
> Hi everyone,
>
> I have a problem related to column names . I have a column name as a[b] in
> my table but
> this creates a problem when using SELECT statement as
> SELECT a[b] from Table. I know [] is a special character but How would I
> tell SQlite that field
> name is a[b] and I am not using [] with a special purpose..

Try SELECT "a[b]"


>
> Thanks in advance,
> Serdar
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] composite PK vs. single PK

[P Kishor]

Thanks Cory.

On Fri, Jun 25, 2010 at 9:07 AM, Cory Nelson <phro...@gmail.com> wrote:
> On Fri, Jun 25, 2010 at 6:49 AM, P Kishor <punk.k...@gmail.com> wrote:
>> Is there any gotcha, any disadvantage (query complexity, db size,
>> query speed) to using a composite PK (two columns) vs. a single
>> AUTOINCREMENT INT?
>>
>> Background: I happen to have the two columns in question in my table
>> anyway. Adding an INTEGER PRIMARY KEY would use up space I don't want
>> to use. My db is big enough to worry about space from a single field.
>
> Primary keys that are not a single integer are equivalent in storage
> and complexity to a separate additional index, and composite keys
> don't have any penalty over single-column keys.
>
> Single integer primary keys you get for free.  They take no extra
> storage or complexity because in SQLite every table already has one
> even if you don't use it (called a "rowid").  Specifying one merely
> gives it a new name.
>


My question was from a more generic db perspective although I asked it
here on sqlite list. Specifically, in Pg, there is no concept of
'rowid'. Even though there is an 'oid', it can be turned off, and is
anyway recommended to not be depended upon to act as a PK. Even more
so, it ('oid' in Pg), is an INT4, so it is upper-limit bound. So, let
me rephrase my question a bit --

Are composite PKs made up of two INT columns the same in query speed,
complexity and storage as a single INT PK that is not an alias of a
system row id?

What I glean from your answer --

PKs that are not a single INT, but a composite of, say, two INT
columns, "add the equivalent of an additional index." Is that what you
are saying? An index in addition to what might be created to treat
them as a PK?

And, "composite keys don't have any penalty over single-column keys."
In other words, a composite PK made up of two INT columns is just as
quick and svelte as a single INT column PK. Is that what you are
saying?


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] composite PK vs. single PK

[P Kishor]

Is there any gotcha, any disadvantage (query complexity, db size,
query speed) to using a composite PK (two columns) vs. a single
AUTOINCREMENT INT?

Background: I happen to have the two columns in question in my table
anyway. Adding an INTEGER PRIMARY KEY would use up space I don't want
to use. My db is big enough to worry about space from a single field.

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

Re: [sqlite] understanding the logic of licensing various parts of the sqlite family

[P Kishor]

On Thu, Jun 24, 2010 at 3:19 PM, Richard Hipp <d...@sqlite.org> wrote:
> On Thu, Jun 24, 2010 at 4:15 PM, P Kishor <punk.k...@gmail.com> wrote:
>
>>
>> Right then. That explains #3 above. Are you saying that #5 is
>> available for a fee? And, is there a description of #5 and how it
>> differs from #4? Not that I will understand the differences, nor will
>> I ever need it. I just didn't realize #5 (the "Compleat Tests of
>> SQLite") was available.
>>
>> Thanks for the answer.
>>
>
> http://www.sqlite.org/th3.html
>

Lovely. Thorough, clear and effective. Like sqlite3 itself.


> --
> -
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] understanding the logic of licensing various parts of the sqlite family

[P Kishor]

On Thu, Jun 24, 2010 at 3:04 PM, Richard Hipp <d...@sqlite.org> wrote:
> On Thu, Jun 24, 2010 at 3:58 PM, P Kishor <punk.k...@gmail.com> wrote:
>
>> I am simply curious, and want to expand my knowledge of this --
>>
>> 1. sqlite3 code is in public domain.
>> 2. sqlite mark is trademarked.
>> 3. sqlite3 encryption extension is licensed and for a fee, and comes
>> with a contract to not distribute it further.
>> 4. sqlite code tests are available as long as they are in the
>> non-amalgamated source tree.
>> 5. other "more complete" code tests are proprietary and closed source.
>>
>>
>> I am curious about the reason for #5 being the way it is.
>>
>
> No mystery there:  Sales of licenses for the non-free parts of SQLite is
> (one way) that we make money in order to pay people to work full-time on the
> free parts.
>
>

Right then. That explains #3 above. Are you saying that #5 is
available for a fee? And, is there a description of #5 and how it
differs from #4? Not that I will understand the differences, nor will
I ever need it. I just didn't realize #5 (the "Compleat Tests of
SQLite") was available.

Thanks for the answer.


>
> --
> -
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] understanding the logic of licensing various parts of the sqlite family

[P Kishor]

I am simply curious, and want to expand my knowledge of this --

1. sqlite3 code is in public domain.
2. sqlite mark is trademarked.
3. sqlite3 encryption extension is licensed and for a fee, and comes
with a contract to not distribute it further.
4. sqlite code tests are available as long as they are in the
non-amalgamated source tree.
5. other "more complete" code tests are proprietary and closed source.


I am curious about the reason for #5 being the way it is. Simply
curious, not judgmental.

Fwiw, I never 'make test' sqlite... I just download and install it.
Same with the version that comes with DBD::SQLite other than any tests
that the Perl installation might do.


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how often to open the db

[P Kishor]

On Mon, Jun 21, 2010 at 10:44 PM, Sam Carleton
 wrote:
> I have asked this Q a number of times over the last year and NEVER gotten
> ANYONE to even comment on it.  I am wondering why:
>
> Am I opening the DB too much?

what is too much? I mean, the computer is not going to get tired. But,
are you experiencing speed bottlenecks?

>
> My usage of SQLite is in an Apache module that opens the DB each time it
> needs info from the DB:  For authentication it is open/closed, for the
> initialization of the request the db is open/closed, for the processing of
> the request, the db is opened and closed.

If all of the above is happening within one logical transaction (and,
I don't mean, a db transaction -- since you mentioned Apache, I mean,
a "job," a "click" from the user's point of view), you certainly can
and should open a single db connection, do everything you want to do,
close the db, and then return the result to the user. At least, that
is how I do it.

In fact, once the Apache process ends, the db connection should close
automatically, although it doesn't hurt to close it explicitly.




>
> Is this OK, or should I open it once for the whole request and close it when
> the whole request is finished?
>
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] optimizing use of extension function

[P Kishor]

On Sun, Jun 20, 2010 at 9:21 PM, Sam Carleton
 wrote:
> SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED
>  WHERE case when instertedon > julianday(@time)
>   then findLargeImage(@path, FolderId, ImageId)
>   else 0 end;

I think Igor wants you to add the lIMIT clause at the end of the above like so

SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED
 WHERE case when instertedon > julianday(@time)
  then findLargeImage(@path, FolderId, ImageId)
  else 0 end
LIMIT 1;

-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] optimizing use of extension function

[P Kishor]

On Sun, Jun 20, 2010 at 8:55 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>> I think that is a common assumption, but a wrong one. Think about it
>> -- the sql engine has to get the entire result set back before it can
>> apply the limit clause.
>
> Puneet, are you 100% sure about that or are you just telling your
> opinion?

Pavel, I am not telling my opinion, but I am also not 100% sure. As I
wrote, if I recall correctly, that is how LIMIT works. I remember
reading something to that effect. That said, I could very well be
wrong.


> Just recently there was another thread where "strange"
> behavior was very well explained by the fact that SQLite stopped
> processing query with LIMIT 1 in it after it returned first row. Even
> if I do what you ask ("Think about it -- the sql engine has to get the
> entire result set back before it can apply the limit clause") I don't
> see why is that? I can agree that if you put ORDER BY ... LIMIT 1 and
> you don't have any index satisfying ORDER BY clause then indeed sql
> engine have to take the whole result set, sort it and then return the
> first row. But in all other cases I don't see why it should do that
> and in fact I think SQLite smart enough to not do that.

The above brings up a related issue... At least Pg docs suggest that
LIMIT should always be used with ORDER BY. See
http://developer.postgresql.org/pgdocs/postgres/sql-select.html

"When using LIMIT, it is a good idea to use an ORDER BY clause that
constrains the result rows into a unique order. Otherwise you will get
an unpredictable subset of the query's rows — you might be asking for
the tenth through twentieth rows, but tenth through twentieth in what
ordering? You don't know what ordering unless you specify ORDER BY."

Which implies, the WHERE clause will be applied, then the ORDER BY
will be applied on the entire set (because the returned set might not
be in the desired order), and then, LIMIT will be applied. So, that in
turn implies that by the time LIMIT is applied, the result set is
already out of the bag, it has already been calculated.

In any case, I don't have a definitive citation for my belief, so I am
happy to be corrected.



>
>
> Pavel
>
> On Sun, Jun 20, 2010 at 8:56 PM, P Kishor <punk.k...@gmail.com> wrote:
>> On Sun, Jun 20, 2010 at 7:53 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>>>
>>> On 21 Jun 2010, at 12:41am, P Kishor wrote:
>>>
>>>> iirc, LIMIT 1 is applied *after* the WHERE clause is satisfied. In
>>>> other words, the entire result set is returned, and then it is
>>>> LIMITed. So, the behavior is correct.
>>>
>>> That does agree with what Sam is reporting.  However, I am surprised at 
>>> this fact.  I have been assuming for years that using LIMIT saves the SQL 
>>> engine from having to list all the possible entries.  Having used a number 
>>> of other SQL implementations over the years it didn't even occur to me to 
>>> check to see how LIMIT was implemented in SQLite.  I'm sure I'm not the 
>>> only person who uses LIMIT assuming it will reduce CPU and memory to a 
>>> small limited amount.
>>>
>>
>> I think that is a common assumption, but a wrong one. Think about it
>> -- the sql engine has to get the entire result set back before it can
>> apply the limit clause. But, I could be wrong. Maybe other db engines
>> do it differently.
>>
>> In any case, that is the reason there is the suggested scrolling
>> cursor document on sqlite.org, to help folks with the common use-case
>> of paging through results on, say, a web page.
>>
>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mai

Re: [sqlite] optimizing use of extension function

[P Kishor]

On Sun, Jun 20, 2010 at 7:53 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 21 Jun 2010, at 12:41am, P Kishor wrote:
>
>> iirc, LIMIT 1 is applied *after* the WHERE clause is satisfied. In
>> other words, the entire result set is returned, and then it is
>> LIMITed. So, the behavior is correct.
>
> That does agree with what Sam is reporting.  However, I am surprised at this 
> fact.  I have been assuming for years that using LIMIT saves the SQL engine 
> from having to list all the possible entries.  Having used a number of other 
> SQL implementations over the years it didn't even occur to me to check to see 
> how LIMIT was implemented in SQLite.  I'm sure I'm not the only person who 
> uses LIMIT assuming it will reduce CPU and memory to a small limited amount.
>

I think that is a common assumption, but a wrong one. Think about it
-- the sql engine has to get the entire result set back before it can
apply the limit clause. But, I could be wrong. Maybe other db engines
do it differently.

In any case, that is the reason there is the suggested scrolling
cursor document on sqlite.org, to help folks with the common use-case
of paging through results on, say, a web page.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question About SQLITE and AIR efficiency

[P Kishor]

On Sun, Jun 20, 2010 at 5:18 PM, Simon Slavin  wrote:
>
> On 20 Jun 2010, at 11:11pm, Felipe Aramburu wrote:
>
>> I have a query that I can execute in about 150ms in a sqlite tool like
>> sqlite expert professional that takes 1200ms when I execute the query from
>> AIR
>
> What is AIR ?

I think, some Adobe run-time environment.

You would be better off asking on an Adobe-specific list, or, provide
way more information than you did, and hope to get lucky with someone
else on this list who uses AIR.



>
>> I am using prepared statements, synchronous connection. Does anyone have any
>> idea why a query takes 8 times longer in air?
>
> How long does the query take if you execute it in the sqlite3 command-line 
> tool ?  You can use '.timer ON':
>
> http://www.sqlite.org/sqlite.html
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] optimizing use of extension function

[P Kishor]

On Sun, Jun 20, 2010 at 5:16 PM, Simon Slavin  wrote:
>
> On 20 Jun 2010, at 11:08pm, Sam Carleton wrote:
>
>> Simon,
>>
>> this is a direct CUT and PASTE from my code:
>>
>> #define SQL_GET_NEXT_SLIDE_SHOW_IMAGE \
>>       "SELECT FolderId, ImageId, instertedon " \
>>      "FROM V_FAVORITES_SELECTED " \
>>     "WHERE instertedon > julianday(@time) AND findLargeImage(@path,
>> FolderId, ImageId) = 1 " \
>>     "LIMIT 1"
>>
>> When I set a break point on the findLargeImage() function, it gets
>> called once for every row in the result set from [SELECT FolderId,
>> ImageId, instertedon FROM V_FAVORITES_SELECTED], the WHERE instertedon
>>>
>>   > julianday(@time) and the LIMIT 1 is NOT taken into account.  Thus,
>> the findLargeImage() is *NOT* short-circuiting the query.
>
> Hmm.  That suggests that SQLite is not implementing LIMIT 1 in a good way.  
> Perhaps someone could code up a trivial extension function with a breakpoint 
> and debug it.
>

iirc, LIMIT 1 is applied *after* the WHERE clause is satisfied. In
other words, the entire result set is returned, and then it is
LIMITed. So, the behavior is correct.

Once again, did you try Igor's suggestion? Exactly as he suggested?
Don't know if it will work or not, but this I can say -- if there is
one person in the entire SQL world you should listen to very
carefully, it is Igor. There is never any wasted words in his
suggestion, and his is almost always the correct solution. Try it,
then report back.



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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] optimizing use of extension function

[P Kishor]

Lord, my response was so scrambled... my apologies. Let me try again...

CASE.. WHEN.. THEN.. ELSE.. END is an expression. You can use it in
lieu of simple columns, and you can use it wherever columns can be
used. So, you can use it where you have <> below

SELECT <>
FROM table(s)
WHERE <>

Go back to what Igor showed you --

SELECT folderid, imageid, insertedon
FROM v_favorites_selected
WHERE
  CASE
WHEN
  instertedon > julianday(@time)
THEN
  findImage(@rootPath, FolderId, ImageId)
ELSE
  0
END;

On Sun, Jun 20, 2010 at 3:52 PM, P Kishor <punk.k...@gmail.com> wrote:
> On Sun, Jun 20, 2010 at 3:47 PM, Sam Carleton
> <scarle...@miltonstreet.com> wrote:
>> Erin and Igor,
>>
>> I simply cannot wrap my head around the correct syntax.  BAsed on the
>> documentation, I believe I should be looking to make it fit this
>> pattern:
>>
>> CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
>>
>> Does the WHERE clause get replaced with the WHEN clause like this:
>
> No. WHERE clause is a completely different part of the statement,
> different from WHEN which is a part of the CASE construct. "CASE..
> WHEN .. THEN .. ELSE .. END" is one construct, an expression, and
> applies to the columns, while WHEN is a completely different part.
>
>
>>
>> CASE SELECT FolderId, ImageId, instertedon
>>  FROM V_FAVORITES_SELECTED
>>  WHEN instertedon > julianday(@time)
>>  THEN findImage(@rootPath, FolderId, ImageId) ELSE 0 END;
>>
>>
>> On Sun, Jun 20, 2010 at 12:02 PM, Erin Drummond <erin@gmail.com> wrote:
>>> You missed the "case when" part.
>>> See "The CASE Expression" : http://www.sqlite.org/lang_expr.html
>>>
>>> On Mon, Jun 21, 2010 at 2:28 AM, Sam Carleton
>>> <scarle...@miltonstreet.com> wrote:
>>>> Igor,
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] optimizing use of extension function

[P Kishor]

On Sun, Jun 20, 2010 at 3:47 PM, Sam Carleton
 wrote:
> Erin and Igor,
>
> I simply cannot wrap my head around the correct syntax.  BAsed on the
> documentation, I believe I should be looking to make it fit this
> pattern:
>
> CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
>
> Does the WHERE clause get replaced with the WHEN clause like this:

No. WHERE clause is a completely different part of the statement,
different from WHEN which is a part of the CASE construct. "CASE..
WHEN .. THEN .. ELSE .. END" is one construct, an expression, and
applies to the columns, while WHEN is a completely different part.


>
> CASE SELECT FolderId, ImageId, instertedon
>  FROM V_FAVORITES_SELECTED
>  WHEN instertedon > julianday(@time)
>  THEN findImage(@rootPath, FolderId, ImageId) ELSE 0 END;
>
>
> On Sun, Jun 20, 2010 at 12:02 PM, Erin Drummond  wrote:
>> You missed the "case when" part.
>> See "The CASE Expression" : http://www.sqlite.org/lang_expr.html
>>
>> On Mon, Jun 21, 2010 at 2:28 AM, Sam Carleton
>>  wrote:
>>> Igor,
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB Support

[P Kishor]

On Wed, Jun 16, 2010 at 7:23 AM, Andreas Henningsson
<andreas.hennings...@gmail.com> wrote:
> Do some testing to find out if it suits the application you develop.
> But just in general .. file systems are build to handle files, databases are
> for handle data.
>

Well, at the risk of being pedantic, you say files I say data (sung to
the tune of "you say poe-tay-toe, I say poe-tah-toe"). What is within
the files is data after all. When storing a blob of, say, an image,
you are not storing the file; instead, you are storing those
bits-and-bytes that are reconstructed as an image by your image
viewer. To do so, you open your image file, you read in the contents,
and write them to the db blob column. Your file is gone... it is no
longer relevant. Now what you have is the data in your db.

But yes, my sense is (no firm, scientific tests backing this claim,
mind you), that storing very large binary objects in a db doesn't seem
efficient. Storing them on the file system while storing their
metadata in the db seems a lot more efficient. On the other hand, a
case could be made for storing blobs in the db when you have many,
many small binary objects, as in the case of image thumbnails.
Especially if the blobs are smaller than a page size, the db would
likely be extremely efficient.


> I don't think BLOB in SQlite will increasing the performance compared to
> store the files in
> the file system.
>
> Some SQlite APIs do not support BLOB very good. Also something to
> consider if you will store large files.
>
> /Andreas
>
> On Wed, Jun 16, 2010 at 1:31 PM, P Kishor <punk.k...@gmail.com> wrote:
>
>> for some reason, I remember you asking the same question not too long
>> ago, and getting a bunch of answers. I recall chipping in with an
>> answer myself. DIdn't any of those answers help?
>>
>> On Wed, Jun 16, 2010 at 1:58 AM, Navaneeth Sen B
>> <navanee...@tataelxsi.co.in> wrote:
>> > Hi All,
>> > I would like to know more about this BLOB support in SQLite. Some of my
>> > queries are:
>> >
>> >   1. One of my colleague suggested that using BLOB support for storing
>> >      images in the DB is a good idea, whereas storing AVCHD data(huge
>> >      size) as blobs is not a good idea. I need a bit more clarification
>> >      on this statement.
>>
>> What is the clarification you need? I remember writing that (in my
>> wisdom), it is better to store large binary objects such as big video,
>> audio or image files in the file system, and store the metadata for
>> them in the db. If you have many, many small items, storing them
>> directly as blobs in the sqlite should be very quick and helpful, but
>> other than that, storing them in the file system may be better. Did
>> you experiment with one or the other?
>>
>> >   2. I just want to know how does this BLOB support help in increasing
>> >      the performance?
>>
>> Don't know. Only you can tell, based on your usage scenario and your
>> performance expectations.
>>
>> >   3. What is the difference produced in storing the file inside DB(not
>> >      in blob format) and storing the same file in BLOB format in the DB?
>> >
>> >
>>
>> What do you mean by "What is the difference produced"? Which
>> difference and produced from what? If stored correctly, you will have
>> the same item in the db as would have been in the file system, so
>> which difference are you talking about? If you are talking about the
>> mechanism itself, well, we went through that earlier and above as
>> well... the db does all the homework for you regarding where to store
>> the files, even what to call them, if you implement that, etc. But, of
>> course, you can't access those files directly if they are in the db.
>> You have to get to them via the db only.
>>
>>
>> > --
>> >
>> > *
>> > Thanks & Regards
>> > SEN*
>> > /
>> > /
>> >
>> >





-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB Support

[P Kishor]

for some reason, I remember you asking the same question not too long
ago, and getting a bunch of answers. I recall chipping in with an
answer myself. DIdn't any of those answers help?

On Wed, Jun 16, 2010 at 1:58 AM, Navaneeth Sen B
 wrote:
> Hi All,
> I would like to know more about this BLOB support in SQLite. Some of my
> queries are:
>
>   1. One of my colleague suggested that using BLOB support for storing
>      images in the DB is a good idea, whereas storing AVCHD data(huge
>      size) as blobs is not a good idea. I need a bit more clarification
>      on this statement.

What is the clarification you need? I remember writing that (in my
wisdom), it is better to store large binary objects such as big video,
audio or image files in the file system, and store the metadata for
them in the db. If you have many, many small items, storing them
directly as blobs in the sqlite should be very quick and helpful, but
other than that, storing them in the file system may be better. Did
you experiment with one or the other?

>   2. I just want to know how does this BLOB support help in increasing
>      the performance?

Don't know. Only you can tell, based on your usage scenario and your
performance expectations.

>   3. What is the difference produced in storing the file inside DB(not
>      in blob format) and storing the same file in BLOB format in the DB?
>
>

What do you mean by "What is the difference produced"? Which
difference and produced from what? If stored correctly, you will have
the same item in the db as would have been in the file system, so
which difference are you talking about? If you are talking about the
mechanism itself, well, we went through that earlier and above as
well... the db does all the homework for you regarding where to store
the files, even what to call them, if you implement that, etc. But, of
course, you can't access those files directly if they are in the db.
You have to get to them via the db only.


> --
>
> *
> Thanks & Regards
> SEN*
> /
> /
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such table error.

[P Kishor]

On Mon, Jun 14, 2010 at 5:53 PM, Tom Lynn  wrote:
> I'm fairly new to sqlite and generally use databases with single tables for
> use with my PERL scripts.  I've encountered a problem where I've tried to
> insert data into a table named trunks and get the error "No such table:
> main.atd_data"
>
> atd_data is a table that I use in a different script.  The insert statement
> does not reference it whatsoever (shown below).  This error occurs whether I
> execute the statement from my script or from the sqlite3 command shell.  I
> cannot imagine sqlite "remembering" my other table.  Should I suspect some
> kind of file corruption?
>
> INSERT INTO trunks ( store, trk_date, grp_num, grp_size, grp_type, grp_dir,
> meas_hour, usage, seizures, inc_seizures, grp_overflow, queue_size, queued,
> que_overflow, que_abandoned, out_service, percent_atb, out_block ) VALUES (
> 001, '6/04/2010', 12, 24, 'tie', 'two', 1300, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0,
> 0);
>


Do you have a TRIGGER that is firing on INSERT INTO trunks, a TRIGGER
that is trying to fiddle with main.atd_data?


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

Re: [sqlite] Mail loop?

[P Kishor]

Funny that... I got the "mail loop error" reply to my email below


> This is the mail system at host sqlite.org.
>
> I'm sorry to have to inform you that your message could not
> be delivered to one or more recipients. It's attached below.
>
> For further assistance, please send mail to postmaster.
>
> If you do so, please include this problem report. You can
> delete your own text from the attached returned message.
>
>   The mail system
>
> : mail forwarding loop for sqlite-users@sqlite.org


but it still seems to have gotten through as Michael replied.

On Mon, Jun 14, 2010 at 8:42 AM, Black, Michael (IS)
 wrote:
>
> It's been that way since email existed.  Mail loops used to cause complete 
> havoc with the internet and mail servers.  Especially local loops.  People 
> used to just kill their email servers to stop it.  And out-of-office replies 
> caused havoc with email lists when they first came out.
>
..



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mail loop?

[P Kishor]

On Mon, Jun 14, 2010 at 8:09 AM, Black, Michael (IS)
 wrote:
> The problem is that somebody has a .forward or such which loops back to the 
> list.  It's probably in the alias expansion of sqlite-users which expands to 
> a listfor which a member then expands back to sqlite-users.
>
> The mail logs may show whether it happens locally or if its a remote user.  
> If it's a remote user you should see a log entry complaining about too many 
> loops and it may ID the user name involved.
>


Happened to me a few days ago. It would be a very bad design on the
part of the mail list manager software if one errant user could cause
bad behavior to percolate to everyone on the list.


>
> I'd check sqlite-users for another sqlite-users@ entry...
>
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>
>
> 
>
> From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
> Sent: Mon 6/14/2010 7:46 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Mail loop?
>
>
>
> It is not just you.  But I have no idea what the problem is or how to fix
> it.
>
> On Mon, Jun 14, 2010 at 8:43 AM, Black, Michael (IS) > wrote:
>
>> I've been seing this the last 2-3 weeks whenever I email the list...is it
>> just me???
>>
>>
>> This is the mail system at host sqlite.org.
>>
>> I'm sorry to have to inform you that your message could not
>> be delivered to one or more recipients. It's attached below.
>>
>> For further assistance, please send mail to postmaster.
>>
>> If you do so, please include this problem report. You can
>> delete your own text from the attached returned message.
>>
>>                   The mail system
>>
>> : mail forwarding loop for
>> sqlite-users@sqlite.org
>>
>>
>>
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
> --
> -
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing AVCHD files

[P Kishor]

On Wed, Jun 9, 2010 at 8:49 AM, Jean-Denis Muys  wrote:
>
> On 6/9/10 14:37 , "Simon Slavin"  wrote:
>
>>
>> On 9 Jun 2010, at 12:18pm, Navaneeth Sen B wrote:
>>
>>> I would like to know how i can store an AVCHD file(It has a folder
>>> structure) having size greater than 4GB.
>>
>> It is unlikely that whatever filesystem you're using will allow any file to 
>> be
>> this big.  Therefore you cannot have a database file this big either.  Leave
>> the folder the way it is, and put the filenames into your database.
>>
>
> Modern file systems allow files with sizes weighing in TB. HFS+ for example,
> which we have been using for a number of years already, has a single file
> size limit of 8 Exbibytes.
>


Indeed. See http://support.apple.com/kb/HT2422

"The theoretical maximum file size for a Mac OS Extended file system
is millions of terabytes. In practice, the maximum file size is
equivalent to the maximum volume size, except for a small amount of
disk space reserved for file system information."

Even in version 10.0 of Mac OS X (about 8 or 9 years ago), the max
file size was 2 TB.

Re. OP's question, if the AVCHD "file" is really a folder, you would
probably want to tar-gzip it into a file if you want to store it in a
db. That said, it is probably not a good idea to store it in a db.
Instead, store the metadata for the file in a db while keeping the
file on the file system, and then use the metadata to locate the file.
All of Jean-Denis' earlier reasons apply.

The general rule of thumb -- if you have lots and lots of tiny binary
files, store them in the db. This would be especially efficient if the
size of each file is less than the page size in the db, so the files
don't span pages. And, this would be especially useful if you don't
want to come up with a naming and storing structure for lots and lots
of such files.

An example of the above might be thumbnails of photos (not actual,
full size photos, but just the thumbnails), or 30 second samples of
music, etc. The max page size allowed in sqlite is 32 K, so that is a
good limit to impose.

On the other hand, if you have a few very large files (or a lot of
very large files) then it is better to store the metadata for those
files in the db, but store the files in the file system.




> Now your suggestion is sound, as I argued previously.
>
> Jean-Denis
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] returning a larger snippet in FTS3 match

[P Kishor]

On Tue, Jun 8, 2010 at 9:14 AM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> On Tue, Jun 08, 2010 at 08:49:22AM -0500, P Kishor scratched on the wall:
>> On Tue, Jun 8, 2010 at 8:29 AM, Jay A. Kreibich <j...@kreibi.ch> wrote:
>> > On Tue, Jun 08, 2010 at 06:56:33AM -0500, P Kishor scratched on the wall:
>> >> Hi all, re-asking this in case it missed some of the keener eyes -- I
>> >> am using the Snippet() function to return a snippet of text from my
>> >> FTS3 table showing the MATCH context. I would like to make the
>> >> returned snippet longer. Is that possible?
>> >
>> > ?Yes. ? ? http://sqlite.org/fts3.html#section_4_2
>
>> First, what the heck does '-15' mean?
>
>  Read the two paragraphs that start "Assuming N is a positive
>  value..." and "If N is a negative value...".
>
>> Second, specifying
>> anything more than 4 arguments gives me a SQLite error. For example,
>
>  Are you using the latest build?  It appears to have been updated from
>  an older version that only accepted four arguments.
>


ahhh... that could be it. I am using DBD::SQLite 1.29, which uses sqlite 3.6.22.

It would be nice if each documentation page included the version
number of sqlite for which that specific documentation was valid.
Otherwise, there is no knowing what works and what doesn't work.


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

Re: [sqlite] returning a larger snippet in FTS3 match

[P Kishor]

On Tue, Jun 8, 2010 at 8:29 AM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> On Tue, Jun 08, 2010 at 06:56:33AM -0500, P Kishor scratched on the wall:
>> Hi all, re-asking this in case it missed some of the keener eyes -- I
>> am using the Snippet() function to return a snippet of text from my
>> FTS3 table showing the MATCH context. I would like to make the
>> returned snippet longer. Is that possible?
>
>  Yes.     http://sqlite.org/fts3.html#section_4_2
>
>


Yup, I read that, and I just can't get it. Per the docs

"The snippet function is used to create formatted fragments of
document text for display as part of a full-text query results report.
The snippet function may be passed between one and four arguments, as
follows:

ArgumentDefault Value   Description
0   N/A  The first argument to the snippet function must always be the
special hidden column of the FTS3 table that takes the same name as
the table itself.
1   ""The "start match" text.
2   ""   The "end match" text.
3   "..." The "ellipses" text.
4   -1   The FTS3 table column number to extract the returned fragments
of text from. Columns are numbered from left to right starting with
zero. A negative value indicates that the text may be extracted from
any column.
5   -15  The absolute value of this integer argument is used as the
(approximate) number of tokens to include in the returned text value.
The maximum allowable absolute value is 64. The value of this argument
is refered to as N in the discussion below."

So, first of all, the docs say that Snippet function takes between one
and four arguments, and then it lists 6 possible arguments. From what
I read above, it is the sixth argument, that is, argument 5, where I
can specify the "approximate" number of tokens in the returned text
value. First, what the heck does '-15' mean? Second, specifying
anything more than 4 arguments gives me a SQLite error. For example,

Snippet(fts_pages, '', '', '', -1, 64) AS context

gives the following error

wrong number of arguments to function snippet() at
/Users/punkish/Sites/punkish/_perl/Punkish.pm line 535,  line 36.,
referer: http://punkish.local/
[Tue Jun 08 08:44:54 2010] [error] [client 127.0.0.1] perl(3344)
malloc: *** error for object 0x100b7dc00: pointer being freed was not
allocated, referer: http://punkish.local/
[Tue Jun 08 08:44:54 2010] [error] [client 127.0.0.1] *** set a
breakpoint in malloc_error_break to debug,


What am I missing?



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] returning a larger snippet in FTS3 match

[P Kishor]

Hi all, re-asking this in case it missed some of the keener eyes -- I
am using the Snippet() function to return a snippet of text from my
FTS3 table showing the MATCH context. I would like to make the
returned snippet longer. Is that possible?

On Sun, Jun 6, 2010 at 10:36 AM, P Kishor <punk.k...@gmail.com> wrote:
> I would like to return a much larger snippet in the MATCH results than
> what comes back as default. Is it possible to specify a snippet
> length?
>
> --
> Puneet Kishor
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] returning a larger snippet in FTS3 match

[P Kishor]

I would like to return a much larger snippet in the MATCH results than
what comes back as default. Is it possible to specify a snippet
length?

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

Re: [sqlite] FTS1 or FTS2

[P Kishor]

On Wed, May 26, 2010 at 9:22 PM, Sam Carleton
 wrote:
> Roger,
>
> I did this search:
>
> http://www.google.com/search?q=sqlite+text+search
>
> The top link points here:
>
> http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex
>
> Thus I learned of FTS1 and FTS2, maybe things should be configured so that
> CVS isn't crawled by search engines ;)


The latest version is FTS3 (higher numbers are later versions). FTS1
and FTS2 are deprecated. FTS3 source is included in the sqlite source
download. Check the build instructions for sqlite. You can set a
compile time flag and build FTS3 into sqlite3.


>
> Sam
>
> On Wed, May 26, 2010 at 2:47 PM, Roger Binns  wrote:
>
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 05/26/2010 10:24 AM, Sam Carleton wrote:
>> > If I do opt to use FTS, which one should I be using, FTS1 or FTS2?
>> > According to the web site, it should be FTS1, correct?
>>
>> Just as a matter of interest what part of the web site led you to those
>> conclusions?
>>
>> Roger
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v1.4.10 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>>
>> iEYEARECAAYFAkv9bKcACgkQmOOfHg372QTINwCglr7//BHCgOt3vKApNZ8/EVYI
>> rcIAn1pYT57Rb/7zeyYGuEw+xyYVbxVG
>> =Imqe
>> -END PGP SIGNATURE-
>> ___
>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Novice SQLite user

[P Kishor]

On Wed, May 26, 2010 at 8:15 PM, jdee5  wrote:
>
>
>
> Thanks for your reply.  I have read through the link you suggested, very
> helpful...if I may ask another question concerning this.  Say on my
> application I have 2 users reading some of the database contents at the same
> time and they both log something in my application at the same time.  For
> example say they want to both review different customer accounts and add a
> payment to the different customer accounts.  Would there be a delay with
> both of those when using SQLite, if so would it be significant?
>
> Can I use SQLite this way have my application stored on the server and allow
> users on a LAN/peer to peer have the ability to open my app and write to it
> at the same time?  does this type of multi user access often corrupt the
> database?  If my database does become corrupt how can I repair it.
>
> Sorry for the redudancy in the questions...like I said am new to SQL (used
> MS SQL a little) and really new to the idea of using an embedded database
> engine.


Why do you want to use an "embedded database engine" and want to have
multiple concurrent users changing the db over a network? If you want
to users to be able to change stuff at the same time from different
locations, use a true client/server db such as Postgres or MySQL.

If you have only a few users who will be changing the data, you could
create a SQLite-powered web app, but there could be potential
instances of lockouts. If you want to put your db on a LAN (shared
disk), and have remote users change it, there is likely a potential
for db to be corrupted.



>
> Thanks in advance for information
>
>
>
> Simon Slavin-3 wrote:
>>
>>
>> On 26 May 2010, at 10:04pm, jdee5 wrote:
>>
>>> I am looking for an SQL engine that is easy to install and sqlite seems
>>> like
>>> the right way to go.  I have an application (currently just for network
>>> use
>>> on a LAN) and I want to move to using SQL, my application is fairly small
>>> and simple and I like the idea of embedding SQL and using SQLite so my
>>> end
>>> user doesn't notice any difference from my current application...however,
>>> more than 1 person may want to access the db at a time does SQLite allow
>>> for
>>> this?
>>
>> Yes.  SQLite supports locking for multi-user and multi-process access.  It
>> is unusual in that it locks the entire database file rather than
>> individual records, but the SQLite calls you make handle multi-user access
>> correctly.  You /will/ have to make your application aware of how to
>> handle the errors which arise if one user keeps the database locked so
>> long that the other user can't access it.  I recommend you read this page
>> (though you don't have to memorise it all):
>>
>> 
>>
>>> Can it just sequentially record transactions as they are made even if
>>> made simultaneously... I have search all over and can't come to a clear
>>> conclusion, I am also very new to using SQL.
>>
>> Under most circumstances, even if two programs are entering transactions
>> at the same time, SQLite will handle this without the programs having to
>> be aware of it.  However, make sure you handle the SQLITE_BUSY and
>> SQLITE_LOCKED errors correctly.  I hope someone can recommend a page which
>> breaks down how to do this.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: 
> http://old.nabble.com/Novice-SQLite-user-tp28686380p28688425.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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS1 or FTS2

[P Kishor]

On Wed, May 26, 2010 at 1:47 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 05/26/2010 10:24 AM, Sam Carleton wrote:
>> If I do opt to use FTS, which one should I be using, FTS1 or FTS2?
>> According to the web site, it should be FTS1, correct?
>
> Just as a matter of interest what part of the web site led you to those
> conclusions?


heh, heh... the waybackmachineforsqlite.org perhaps


>
> Roger




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

Re: [sqlite] OperationalError: no such function: group_concat

[P Kishor]

On Tue, May 18, 2010 at 12:00 AM, zeal  wrote:
> my sqlite3 version is 2.3.2
> is it out of date?

yes.

> which version should i install?
>

the latest.


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

Re: [sqlite] prevent sqlite from interpreting string as numbers?

[P Kishor]

On Mon, May 17, 2010 at 1:59 PM, peter360  wrote:
>
> Ah... that is what I missed. Thanks!
>
> On the other hand, why doesn't sqlite give me an error or warning when I
> used "string", if it has no meaning?
>

Because sqlite doesn't care. You could call it "peter360string" and it
would be just fine with sqlite.


>
>
> Igor Tandetnik wrote:
>>
>> peter360 wrote:
>>> I see this in sqlite
>>>
>>> sqlite> create table t1(c1 string);
>>
>> You want t1(c1 text). "string" has no special meaning to SQLite, while
>> "text" does.
>>
>> Igor Tandetnik
>>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

[P Kishor]

On Fri, May 14, 2010 at 11:38 AM, Fabio Spadaro <fabiolinos...@gmail.com> wrote:
> Hi,
>
> 2010/5/14 P Kishor <punk.k...@gmail.com>
>
>> On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro <fabiolinos...@gmail.com>
>> wrote:
>> > Hi,
>> >
>> > 2010/5/14 P Kishor <punk.k...@gmail.com>
>> >
>> >> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro <fabiolinos...@gmail.com
>> >
>> >> wrote:
>> >> > hi,
>> >> >
>> >> > 2010/5/14 P Kishor <punk.k...@gmail.com>
>> >> >
>> >> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro <
>> fabiolinos...@gmail.com
>> >> >
>> >> >> wrote:
>> >> >> > I need to identify data types extracted from a
>> >> >> > join between multiple tables without using cross-checking
>> table_info
>> >> more
>> >> >> > pragmatic.
>> >> >> >
>> >> >>
>> >> >> Could you clarify what you really want to do? Your question is not
>> >> >> clear at all, at least to me. What do you mean by "using
>> >> >> cross-checking table_info more pragmatic"? More pragmatic than what?
>> >> >>
>> >> >> > Is there a faster way to do it?
>> >> >> >
>> >> >>
>> >> >> Faster than what?
>> >> >>
>> >> >> If you want data_types, you can use SELECT Typeof(column_name)
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> > I use python sqlite embedded and in my application I have a panel
>> where
>> >> you
>> >> > can
>> >> > type a query and see the results in a panel's grid
>> >> > Now suppose that I type a query like "select
>> >> > a.field1, b.field2 from table1 a, table2 b " such data
>> >> > will be displayed in grid but I need to know what types of data I
>> >> obtained.
>> >> > How do it?
>> >>
>> >>
>> >> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
>> >> FROM table1 a, table2 b...
>> >>
>> >>
>> >> >
>> >
>> > It does not work, It always return data type text.
>> >
>>
>> That is because your table has probably defined those columns as TEXT.
>> Consider the following --
>>
>> sqlite> CREATE TABLE t (a TEXT, b INTEGER);
>> sqlite> INSERT INTO t VALUES ('foo', 5);
>> sqlite> SELECT a, Typeof(a), b, Typeof(b) FROM t;
>> a           Typeof(a)   b           Typeof(b)
>> --  --  --  --
>> foo         text        5           integer
>> sqlite>
>>
>>
>>
>>
>>
>>
>>
>
> No, i'm sure. Probably typeof in python's sqlite is not supported very mell
> or there is other explanation.
>


Typeof() is a sqlite3 function. Maybe the Python implementation is
overriding it. But, given that you are getting back "text" and not an
error, shows that you are using some Typeof() function. Using the
example db that I showed above, and the following Perl script, I get
the desired and expected output

#!/usr/local/bin/perl
use DBI qw(:sql_types);

$dbh = DBI->connect("dbi:SQLite:dbname=/Users/punkish/Data/punkish/foo","","");
$sth = $dbh->prepare("SELECT a, Typeof(a), b, Typeof(b) FROM t");
$sth->execute;

while (my @row = $sth->fetchrow_array) {
print (join ", ", @row) . "\n";
}

 prints...

foo, text, 5, integer



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

[P Kishor]

On Fri, May 14, 2010 at 10:11 AM, Fabio Spadaro <fabiolinos...@gmail.com> wrote:
> Hi,
>
> 2010/5/14 P Kishor <punk.k...@gmail.com>
>
>> On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro <fabiolinos...@gmail.com>
>> wrote:
>> > hi,
>> >
>> > 2010/5/14 P Kishor <punk.k...@gmail.com>
>> >
>> >> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro <fabiolinos...@gmail.com
>> >
>> >> wrote:
>> >> > I need to identify data types extracted from a
>> >> > join between multiple tables without using cross-checking table_info
>> more
>> >> > pragmatic.
>> >> >
>> >>
>> >> Could you clarify what you really want to do? Your question is not
>> >> clear at all, at least to me. What do you mean by "using
>> >> cross-checking table_info more pragmatic"? More pragmatic than what?
>> >>
>> >> > Is there a faster way to do it?
>> >> >
>> >>
>> >> Faster than what?
>> >>
>> >> If you want data_types, you can use SELECT Typeof(column_name)
>> >>
>> >>
>> >>
>> >>
>> >
>> > I use python sqlite embedded and in my application I have a panel where
>> you
>> > can
>> > type a query and see the results in a panel's grid
>> > Now suppose that I type a query like "select
>> > a.field1, b.field2 from table1 a, table2 b " such data
>> > will be displayed in grid but I need to know what types of data I
>> obtained.
>> > How do it?
>>
>>
>> SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
>> FROM table1 a, table2 b...
>>
>>
>> >
>
> It does not work, It always return data type text.
>

That is because your table has probably defined those columns as TEXT.
Consider the following --

sqlite> CREATE TABLE t (a TEXT, b INTEGER);
sqlite> INSERT INTO t VALUES ('foo', 5);
sqlite> SELECT a, Typeof(a), b, Typeof(b) FROM t;
a   Typeof(a)   b   Typeof(b)
--  --  --  --
foo text5   integer
sqlite>






-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] data type from join multiple tables

[P Kishor]

On Fri, May 14, 2010 at 9:49 AM, Fabio Spadaro <fabiolinos...@gmail.com> wrote:
> hi,
>
> 2010/5/14 P Kishor <punk.k...@gmail.com>
>
>> On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro <fabiolinos...@gmail.com>
>> wrote:
>> > I need to identify data types extracted from a
>> > join between multiple tables without using cross-checking table_info more
>> > pragmatic.
>> >
>>
>> Could you clarify what you really want to do? Your question is not
>> clear at all, at least to me. What do you mean by "using
>> cross-checking table_info more pragmatic"? More pragmatic than what?
>>
>> > Is there a faster way to do it?
>> >
>>
>> Faster than what?
>>
>> If you want data_types, you can use SELECT Typeof(column_name)
>>
>>
>>
>>
>> >
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> I use python sqlite embedded and in my application I have a panel where you
> can
> type a query and see the results in a panel's grid
> Now suppose that I type a query like "select
> a.field1, b.field2 from table1 a, table2 b " such data
> will be displayed in grid but I need to know what types of data I obtained.
> How do it?


SELECT a.field1, Typeof(a.field1), b.field2, Typeof(b.field2)
FROM table1 a, table2 b...


>
> --
> Fabio Spadaro
> www.fabiospadaro.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] data type from join multiple tables

[P Kishor]

On Fri, May 14, 2010 at 9:01 AM, Fabio Spadaro  wrote:
> I need to identify data types extracted from a
> join between multiple tables without using cross-checking table_info more
> pragmatic.
>

Could you clarify what you really want to do? Your question is not
clear at all, at least to me. What do you mean by "using
cross-checking table_info more pragmatic"? More pragmatic than what?

> Is there a faster way to do it?
>

Faster than what?

If you want data_types, you can use SELECT Typeof(column_name)




>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create virtual table if not exists table_id???

[P Kishor]

On Tue, May 11, 2010 at 1:50 PM, Matt Young  wrote:
> sqlite> create virtual table if not exists words using fts3  (f1 );
> Error: near "not": syntax error
> sqlite> create  table if not exists U (w1 );
> sqlite>
>
> Different syntax?

Yes.

> virtual tables don't persist?

How did you reach that conclusion? Did you create a virtual table
successfully, quit sqlite, then reopen the db and found the virtual
table missing?



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite connection?

[P Kishor]

On Tue, May 11, 2010 at 12:45 PM, john cummings  wrote:
> hi all,
>
> i'm new to this forum and sqlite.
>
> is it possible to have an executable (i.e. .exe) with connections to 2
> sqlite databases?

I've never made an executable, but given that I can do so with Perl, I
don't see why not. A connection is just a handle. Just give different
connections different names. Heck, you could make multiple connections
to multiple different databases... one to sqlite, one to Pg, another
one to MySQL, another one to sqlite. Knock yourself out Go crazy.


>
> i've read doc and it doesn't speak to this one way or the other.
>
> thanks,
>






-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert large data question ??

[P Kishor]

On Tue, May 11, 2010 at 12:47 AM, 風箏  wrote:
> Dear
>
> I have about 9 million data insert string need to insert into an table ,each
> row data is unique
>
> this is a sample:
> insert into mydata
> VALUES(38824801,56888,'AABBCC',4.999,157,'2009/9/10
> 19:55:50');
>
> this is my schema:
> table|mydata|mydata|2|CREATE TABLE mydata
> (
>    itno VARCHAR(20),
>    lcno VARCHAR(20),
>    srno VARCHAR(10),
>    ran  VARCHAR(20),
>    pp INTEGER,
>    cdate VARCHAR(20),
>    PRIMARY KEY (itno DESC, lcno ASC)
> )
..
> but i have question about performance,everytime doing the job takes about
> 63000 seconds

Use transactions. But, do you also realize that most of your columns
are defined as VARCHAR, but you are inserting stuff that doesn't look
like TEXT. You will be/should be surprised by the results. From the
example above,

>itno VARCHAR(20),
>lcno VARCHAR(20),
>srno VARCHAR(10),
>ran  VARCHAR(20),
>pp INTEGER,
>cdate VARCHAR(20),

ltno VARCHAR(20): 38824801 <-- if it had leading zeroes, they would vanish
lcno VARCHAR(10): 56888 <-- will become 56888
srno VARCHAR(10): 'AABBCC' <-- inserted correctly
ran  VARCHAR(20): 4.999 <-- are you expecting this to remain a REAL?
pp INTEGER: 157 <-- inserted correctly
cdate VARCHAR: '2009/9/10 19:55:50' <-- inserted correctly

enclose your VARCHARs in single quotes.

-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation typo

[P Kishor]

2010/5/10 "Carlos Andrés Ramírez C." :
>
> Hello guys,
> I was breaking my head trying to figure out how to obtain the last
> inserted row's ID --- using SQLite from Ruby.
>
> I found 'last_insert_rowid()' in your documentation at
> http://www.sqlite.org/lang_corefunc.html   and still did not do it.
>
> After spending a lot of time searching, I found that it was not
> 'last_insert_rowid()' as documented, but instead 'last_insert_row_id()'
>  with an extra underscore character before the 'id'.
>

Nope. last_insert_rowid() is correct. Note that just doing a 'SELECT
row_id FROM table' will croak with an error, while 'SELECT rowid FROM
table' works fine. There is no underscore. I have
$dbh->sqlite_last_insert_rowid() in Perl.

Perhaps your Ruby SQLite package has changed the syntax and introduced
the underscore. Get it corrected there.


> is this a Typo? --- I almost went crazy
> It was like that also in the downloadable documentation, so you wanna
> check that out,
>
> Best wishes,
>
> Carlos Ramirez,
> www.antai-group.com
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption of sqlite DB

[P Kishor]

On Tue, May 4, 2010 at 11:19 AM, Kavita Raghunathan
 wrote:
> Hi,
> What’s the simplest way to encrypt only certain rows in an sqlite DB? If 
> there is no way to do this (for storing passwords etc),

You certainly mean some or all columns in all the rows, don't you?
Well, no matter -- you can encrypt any column in any row using any one
way hashing algorithm. Good enough for routine password storage, etc.

> I would like to know the best way to encrypt the whole sqlite DB. (Prefer 
> only encrypting some rows, but if this introduces complexity, I’m willing to 
> encrypt the whole database)

The sqlite developer sells a proprietary encryption mechanism for a
very reasonable price. While the sqlite source code is in public
domain, the encryption-enabled source code is not in public domain.
So, if you buy it, you are supposed to NOT resell or redistribute it.
I have no experience with it, but from occasional hearsay, it is
supposed to be just as good as sqlite itself, so probably worth every
cent spent on it.

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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite datatypes

[P Kishor]

On Mon, May 3, 2010 at 2:17 PM, Alan Harris-Reid
 wrote:
> Hi there,
>
> When creating a table in SQLite, I often get confused when confronted
> with all the possible datatypes which imply similar contents, so could
> anyone tell me the difference between the following data-types?
>
>    INT, INTEGER, SMALLINT, TINYINT
>    DEC, DECIMAL
>    LONGCHAR, LONGVARCHAR
>    DATETIME, SMALLDATETIME
>
> Is there some documentation somewhere which lists the min./max.
> capacities of the various data-types?  For example, I guess smallint
> holds a larger maximum value than tinyint, but a smaller value than
> integer, but I have no idea of what these capacities are.
>

Read the following carefully http://www.sqlite.org/datatype3.html


> Any help would be appreciated.
>
> Alan Harris-Reid
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] is it possible to return primary key column from given table ?

[P Kishor]

On Mon, May 3, 2010 at 2:14 AM, yogibabu  wrote:
>
> like this: SELECT --idcolumn-- FROM `table`


what is the name of the column? Is it '--idcolumn--'? Are the leading
and trailing '--' part of the name? Remember that leading '--' is used
as SQL comments. If that is indeed the name, try

SELECT "--idcolumn--" FROM table

note --idcolumn-- in double quotes, which enable you to use special
characters and words in your object/entity names.


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

Re: [sqlite] scripting language interpreter

[P Kishor]

On Sat, May 1, 2010 at 9:53 AM, Tim Romano  wrote:
> Simon,
>
> It's not clear to me how this is a result of scripting language support:
>
> "Another problem with it is that sooner or later you need your inner
> language (your SQL engine) to have access to your outer environment, for
> example, to find out if you have lots of filespace free."
>
> Could you please elaborate?
>
> The ability to declare FOREIGN KEYS and TRANSACTIONS are not what I had in
> mind when I used the phrase "scripting language". I am thinking of
> full-blown procedural logic integrated with the RDBMS. The foremost example
> that comes to mind is VBA in MS-Access, though many other examples are
> available, e.g. Revelation, Borland Paradox, or even java in Oracle.
>


You do it the other way around... instead of embedding a scripting
language inside SQLite (which would convert it to
SQLVeryHeavyAndCumbersomeAndLikelyToBlowUp), you embed sqlite inside a
scripting language. See how elegantly this is done with Perl and
DBD::SQLite which embeds its own sqlite engine in the perl module.
Similar products exist for Tcl and Python and other languages.

..  ..


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select %column% from table

[P Kishor]

On Fri, Apr 30, 2010 at 10:17 AM, Gerry Snyder <mesmerizer...@gmail.com> wrote:
> So why not columns keyword and value?
>

because, each row is a conceptual "text file" with many key-value
combos. Putting them in separate rows would mean that each key-val
belongs in a separate text file, whatever that text file represents.
That is not what the OP wants.


> Gerry
>
> On 4/30/10, David Lyon <david_ly...@yahoo.com> wrote:
>> Thanks for everyones efforts let me expand:
>>
>>
>> if I had many many files like this:
>> http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt
>>
>> you see 2 columns keyword and value, the keywords would be the fields  (1st
>> column in the html link above) in the table while the values (second column)
>> are the data I would insert.
>>
>> The fields will be many and include
>>
>> P$nR
>> P$nS
>> P$nB
>>
>> etc.
>>
>> thats why I wanted a quick way to access "select P%R from TABLE";
>>
>>
>> thanks again
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> - Original Message 
>> From: P Kishor <punk.k...@gmail.com>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Sent: Fri, April 30, 2010 10:48:41 AM
>> Subject: Re: [sqlite] select %column% from table
>>
>> crap! I completely misunderstood your question... be confused, and
>> then ignore my reply.
>>
>> On Fri, Apr 30, 2010 at 9:43 AM, P Kishor <punk.k...@gmail.com> wrote:
>>> On Fri, Apr 30, 2010 at 9:28 AM, David Lyon <david_ly...@yahoo.com> wrote:
>>>> If I had a table called TABLE with fields P1N..P50N is there a way to
>>>> select something like:
>>>>
>>>> "select P%N from TABLE"
>>>>
>>>>  to return all the results from columns P1N..P50N or do I have to do
>>>> it manually:
>>>>
>>>> "select P1N, P2N, P3N, P$nN from TABLE"
>>>>
>>>
>>> use GLOB. See below
>>>
>>> Last login: Wed Apr 28 09:42:46 on console
>>> punk...@lucknow ~$sqlite3
>>> -- Loading resources from /Users/punkish/.sqliterc
>>> SQLite version 3.6.23
>>> Enter ".help" for instructions
>>> Enter SQL statements terminated with a ";"
>>> sqlite> CREATE TABLE t(a);
>>> sqlite> INSERT INTO t VALUES ('P1N');
>>> sqlite> INSERT INTO t VALUES ('P3N');
>>> sqlite> INSERT INTO t VALUES ('P30N');
>>> sqlite> INSERT INTO t VALUES ('P303N');
>>> sqlite> INSERT INTO t VALUES ('P303X');
>>> sqlite> INSERT INTO t VALUES ('P30Z');
>>> sqlite> SELECT * FROM t;
>>> a
>>> --
>>> P1N
>>> P3N
>>> P30N
>>> P303N
>>> P303X
>>> P30Z
>>> sqlite> SELECT * FROM t WHERE a GLOB 'P*N';
>>> a
>>> --
>>> P1N
>>> P3N
>>> P30N
>>> P303N
>>> sqlite>
>>>
>>>
>>>> I can obviously do it via scripting but wanted a more elegant way.
>>>>
>>>> Thanks for your help in advance.
>>>>
>>>>
>>>>
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>
>>>
>>>
>>> --
>>> Puneet Kishor http://www.punkish.org
>>> Carbon Model http://carbonmodel.org
>>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>>> ---
>>> Assertions are politics; backing up assertions with evidence is science
>>> ===
>>>
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> --

Re: [sqlite] select %column% from table

[P Kishor]

On Fri, Apr 30, 2010 at 9:57 AM, David Lyon <david_ly...@yahoo.com> wrote:
> Thanks for everyones efforts let me expand:
>
>
> if I had many many files like this:
> http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt
>
> you see 2 columns keyword and value, the keywords would be the fields  (1st 
> column in the html link above) in the table while the values (second column) 
> are the data I would insert.
>
> The fields will be many and include
>
> P$nR
> P$nS
> P$nB
>
> etc.
>

Will every row in your proposed table have the same P?? columns? In
other words, will the keys in each of the files that you receive be
identical? If not, you can't really have a single table with all the
rows.

If you insist on having all this normalized and in a db, you could
have a main table with the common columns, and then have a 'p_table'
with 'p_key' and 'p_val' columns with PRIMARY KEY (p_key, p_val), if
required, and then reference those values in the main table.

If you just need all the values back, instead of getting individual
values, I would store them as a hash (in Perl), and then serialize
that hash and store it in the table.


> thats why I wanted a quick way to access "select P%R from TABLE";
>
>
> thanks again
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Original Message 
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Fri, April 30, 2010 10:48:41 AM
> Subject: Re: [sqlite] select %column% from table
>
> crap! I completely misunderstood your question... be confused, and
> then ignore my reply.
>
> On Fri, Apr 30, 2010 at 9:43 AM, P Kishor <punk.k...@gmail.com> wrote:
>> On Fri, Apr 30, 2010 at 9:28 AM, David Lyon <david_ly...@yahoo.com> wrote:
>>> If I had a table called TABLE with fields P1N..P50N is there a way to 
>>> select something like:
>>>
>>> "select P%N from TABLE"
>>>
>>>  to return all the results from columns P1N..P50N or do I have to do it 
>>> manually:
>>>
>>> "select P1N, P2N, P3N, P$nN from TABLE"
>>>
>>
>> use GLOB. See below
>>
>> Last login: Wed Apr 28 09:42:46 on console
>> punk...@lucknow ~$sqlite3
>> -- Loading resources from /Users/punkish/.sqliterc
>> SQLite version 3.6.23
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> CREATE TABLE t(a);
>> sqlite> INSERT INTO t VALUES ('P1N');
>> sqlite> INSERT INTO t VALUES ('P3N');
>> sqlite> INSERT INTO t VALUES ('P30N');
>> sqlite> INSERT INTO t VALUES ('P303N');
>> sqlite> INSERT INTO t VALUES ('P303X');
>> sqlite> INSERT INTO t VALUES ('P30Z');
>> sqlite> SELECT * FROM t;
>> a
>> --
>> P1N
>> P3N
>> P30N
>> P303N
>> P303X
>> P30Z
>> sqlite> SELECT * FROM t WHERE a GLOB 'P*N';
>> a
>> --
>> P1N
>> P3N
>> P30N
>> P303N
>> sqlite>
>>
>>
>>> I can obviously do it via scripting but wanted a more elegant way.
>>>
>>> Thanks for your help in advance.
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
&g

Re: [sqlite] select %column% from table

[P Kishor]

crap! I completely misunderstood your question... be confused, and
then ignore my reply.

On Fri, Apr 30, 2010 at 9:43 AM, P Kishor <punk.k...@gmail.com> wrote:
> On Fri, Apr 30, 2010 at 9:28 AM, David Lyon <david_ly...@yahoo.com> wrote:
>> If I had a table called TABLE with fields P1N..P50N is there a way to 
>> select something like:
>>
>> "select P%N from TABLE"
>>
>>  to return all the results from columns P1N..P50N or do I have to do it 
>> manually:
>>
>> "select P1N, P2N, P3N, P$nN from TABLE"
>>
>
> use GLOB. See below
>
> Last login: Wed Apr 28 09:42:46 on console
> punk...@lucknow ~$sqlite3
> -- Loading resources from /Users/punkish/.sqliterc
> SQLite version 3.6.23
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE t(a);
> sqlite> INSERT INTO t VALUES ('P1N');
> sqlite> INSERT INTO t VALUES ('P3N');
> sqlite> INSERT INTO t VALUES ('P30N');
> sqlite> INSERT INTO t VALUES ('P303N');
> sqlite> INSERT INTO t VALUES ('P303X');
> sqlite> INSERT INTO t VALUES ('P30Z');
> sqlite> SELECT * FROM t;
> a
> --
> P1N
> P3N
> P30N
> P303N
> P303X
> P30Z
> sqlite> SELECT * FROM t WHERE a GLOB 'P*N';
> a
> --
> P1N
> P3N
> P30N
> P303N
> sqlite>
>
>
>> I can obviously do it via scripting but wanted a more elegant way.
>>
>> Thanks for your help in advance.
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select %column% from table

[P Kishor]

On Fri, Apr 30, 2010 at 9:28 AM, David Lyon  wrote:
> If I had a table called TABLE with fields P1N..P50N is there a way to 
> select something like:
>
> "select P%N from TABLE"
>
>  to return all the results from columns P1N..P50N or do I have to do it 
> manually:
>
> "select P1N, P2N, P3N, P$nN from TABLE"
>

use GLOB. See below

Last login: Wed Apr 28 09:42:46 on console
punk...@lucknow ~$sqlite3
-- Loading resources from /Users/punkish/.sqliterc
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t(a);
sqlite> INSERT INTO t VALUES ('P1N');
sqlite> INSERT INTO t VALUES ('P3N');
sqlite> INSERT INTO t VALUES ('P30N');
sqlite> INSERT INTO t VALUES ('P303N');
sqlite> INSERT INTO t VALUES ('P303X');
sqlite> INSERT INTO t VALUES ('P30Z');
sqlite> SELECT * FROM t;
a
--
P1N
P3N
P30N
P303N
P303X
P30Z
sqlite> SELECT * FROM t WHERE a GLOB 'P*N';
a
--
P1N
P3N
P30N
P303N
sqlite>


> I can obviously do it via scripting but wanted a more elegant way.
>
> Thanks for your help in advance.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 1000 insert statements into empty table taking 20 seconds... very odd!

[P Kishor]

On Wed, Apr 28, 2010 at 4:08 PM, Ian Hardingham  wrote:
> Hey guys - this is my first post here, apologies if I violate any etiquette.
>
> I have a table I create with:
>
> CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY
> AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL,
> record TEXT);
>
> I run a loop from a scripting language which hooks into SQLite, which
> basically calls this INSERT statement 1000 times:
>
> INSERT INTO globalRankingTable (name, ranking, score, record) VALUES
> ('?','?',?,'?')"
>
> This takes a good 23 seconds (and my machine isn't exactly slow).  I am
> doing a lot of SQLite stuff in my application and everything else seems
> to be running fine.

use transactions... start with BEGIN, then INSERT your data, then COMMIT.


>
> Here's some further information:
>
> 1.  I have narrowed it down that the time is being taken in the call to
> sqlite3_exec
> 2.  The length of the strings is not particularly high - like 10 chars
> generally.
>
> I'm pretty noob at SQLite and am completely stumped - any advice at all
> would be much appreciated
>
>
> Ian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column output truncating

[P Kishor]

On Fri, Apr 23, 2010 at 10:42 AM, Derek Martin  wrote:
> On Fri, Apr 23, 2010 at 08:52:37AM -0500, Jay A. Kreibich wrote:
>> On Thu, Apr 22, 2010 at 06:31:07PM -0400, Derek Martin scratched on the wall:
>> > Hi,
>> >
>> > I have a query that produces about 10 columns, some of which are very
>> > wide.  When I run sqlite3 with -column -header, it truncates all
>> > fields to 10 characters.  This makes the query absolutely useless.
>> > How can I turn off this truncation?
>> >
>> > I already tried explicitly setting the width to 0, that did not help.
>>
>>   Pick large enough values, or don't use column mode.
>
> Sadly that was the conclusion I had reached before asking here.
> Needless to say, that was not the answer I was hoping for.
>
> Column mode (without any truncation) is the format desired for these
> reports...  Picking large enough values is bad, because it means that
> I'd need to specify very large column widths for most of the columns,
> which in many cases would needlessly make the report tediously wide,
> to accomodate some less common cases where the reports generate very
> wide columns.  Avoiding this is sort of the point of databases doing
> this for you... ;-)


Well, no... the job of a database is to store your data, and then hand
it back to you as fast as possible without changing the data in
anyway. A perfect and most minimal database will preserve your data
integrity 100%. It will give you back whatever you put in, without any
interpretation. A perfect database is like a perfect audio speaker --
the latter would not add treble or bass to your audio... it will
reproduce the audio exactly as it was meant to be.

On top of the above, sqlite is a very minimalist db library. sqlite3
shell is a c program that allows you to access your data stored in the
sqlite db file via the sqlite db library. Think of the sqlite3 shell
as a client program that the sqlite developer wrote to get to the
sqlite db.

All the formatting that you want to do, you can and should do via your
client program. You can have a choice of many different languages to
program one for yourself. Or you can probably even pay and purchase a
few client programs that others have written.

>
> It would be swell if sqlite3 had a -notruncate option or some such,

What version of sqlite are you using? Funnily, I have a table with
lots of running text, and even though I specify .mode column, I don't
see any truncation at all. Of course, the text wraps around in my
terminal window and makes everything look like nonsense, but that is
another story.


> though adding one now wouldn't really solve my immediate problem,
> since that wouldn't be available on our desktop image.  With its
> current behavior, column mode seems fairly worthless to me.  I guess
> I'll need to convince folks that HTML output is suitable, or that they
> need to install some other database thingy to run this stuff.  Using
> sqlite3 for this seemed perfect, since it's already available to
> everyone.
>d


The "database thingy" you need is a client program. You could try the
one written in XUL, the Firefox add-on, which, while not very
attractive, actually works quite well. It is also free as in both
speech and beer, so you could probably modify it to suit your needs.


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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column output truncating

[P Kishor]

On Fri, Apr 23, 2010 at 8:39 AM, Adam DeVita  wrote:
> Could you include a bit more information about your post? (Version number,
> operating system etc.)
>
> I'm unsure if you have compiled something or are using the command line
> tool.
>
>  There are lots of very knowledgeable and helpful people on the list.
>
> On Thu, Apr 22, 2010 at 6:31 PM, Derek Martin  wrote:
>
>> Hi,
>>
>> I have a query that produces about 10 columns, some of which are very
>> wide.  When I run sqlite3 with -column -header, it truncates all
>> fields to 10 characters.  This makes the query absolutely useless.
>> How can I turn off this truncation?
>>
>> I already tried explicitly setting the width to 0, that did not help.
>>


You have to set the width of every column with .width. I don't know
the exact syntax of .width and how to effectively turn .width
constraint off. You might try .mode line, which will print one value
per line.

Better yet, ditch the command line tool for these kind of things and
use something like Perl, Python or Tcl to work with your db.

If you are good with bash, you can also do a lot of magic by querying
a sqlite db via shell scripts.

>> Thanks.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> VerifEye Technologies Inc.
> 905-948-0015x245
> 7100 Warden Ave, Unit 3
> Markham ON, L3R 8B5
> Canada
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Dimension

[P Kishor]

On Wed, Apr 21, 2010 at 3:36 PM, Adam DeVita <adev...@verifeye.com> wrote:
> Good day,
>
> I've been looking at doing that, but am having problems converting
> backwards.
>
> The idea of a date dimension is to have one row for every possible date in
> the time span of interest
>
> For example, I'm tracking product histories, so I know that there will be no
> activity before January 1, 1990 (a date well before manufacture of the first
> product)
>
> and I won't care about what happens well into the future retire (say around
> year 2030   )
>
> This yeilds (20+20)*365.25 =14,610 maximum potential rows.  If all the
> possibilities are pre-calculated, then if a user wants to express a roll up
> of the facts by any grouping
>
> select count(somthing_interesting) , dd.day_of_week
> from fact_table ft
> inner join date_dimension dd on ft.dateid = dd.dateid
> group by dd,day_of_week
>
> Then
> a) the user doesn't have to worry about converting dates by some group
> because it is all done for them.
> b) we store the date of the event in our fact_table as an int
> c) we don't have to run much of calculation of dates, just a join.
>


Yea, in my view, if you are going to do a lot of math on parts of the
date, you are probably better of storing those parts separately
anyway.

Your db size is trivial though, so you should have no problem either way.


> (I've been reading "The Data Warehoust Toolkit, Second Edition  by Kimball
> and Ross)
>
>
> If I have to generate the date dimension on my own, I'm hoping to use
> something like
> create table date_dimension (
>  [Dateid] integer primary key,
>  [Real_Year] int ,
>  [Month_name] text,
>  [Day] int ,
>  [QuarterNumber] int,
>  [DayofWeek_name] text,
>  [dayofYear] int,
>  [epoch_day] int,
>  [julian_day] int
>  );
>
> /*populate some an auto increment so that all days are covered even if I
> have to write a loop doing*/
>  insert into date_dimension( epoch_day) select count(epoch_day) from
> date_dimension;
>
> /*then */
>  update date_dimension set julian_day = julianday('now') -
> julianday('1990-01-01') + epoch_day;
>
> /* then  uh some query that  updates the table containing the julian
> date of every day from Jan 1, 1990 through 2030, and  fill in the month
> name, year, quarter in nice user friendly strings.*/
>
> Is this approach better than generating a list of date strings for all
> possible dates, throwing away the Feb 29s from non leap years, and then
> parsing the string to get ye year, month, day, day of year, age from epoch,
> month name, etc?
>
>
> regards,
> Adam
>
>
>
>
> On Wed, Apr 21, 2010 at 3:59 PM, P Kishor <punk.k...@gmail.com> wrote:
>
>> On Wed, Apr 21, 2010 at 2:37 PM, Adam DeVita <adev...@verifeye.com> wrote:
>> > Yes.
>> >
>> > A Date dimension  is a table that has all possible dates for your
>> > data, thus making reporting on properties of the date easy.  Something
>> > like this
>> >
>> > CREATE TABLE Date_dimension (
>> >  DateID int NOT NULL , /*an int key to match up to date fields in fact
>> > storage tables*/
>> >  [Date] datetime NOT NULL,
>> >  [Year] int NOT NULL,
>> >  [Month] int NOT NULL,
>> >  [Day] int NOT NULL,
>> >  [QuarterNumber] int NOT NULL,
>> >  [DayofWeek_name] text,
>> >  [Month_name] text,
>> >
>> > )
>> >
>>
>>
>> methinks you can calculate all of the above storing your dates as
>> strings in a single column, and using the date time functions on that
>> column. Check out the functions in the link I sent you.
>>
>> >
>> >
>> > On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov <paiva...@gmail.com>
>> wrote:
>> >
>> >> > What is a "Date Dimension"?
>> >>
>> >> Probably OP meant this:
>> >> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)<http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29>
>> <http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29>
>> >> .
>> >> But I don't have any answer to the question asked.
>> >>
>> >>
>> >> Pavel
>> >>
>> >> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor <punk.k...@gmail.com> wrote:
>> >> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita <adev...@verifeye.com>
>> >> wrote:
>> >> >> Good day,
>> >> >>
>> >> >> Given the context I'm in, sqlite is going to be used for our data
>

Re: [sqlite] Date Dimension

[P Kishor]

On Wed, Apr 21, 2010 at 2:37 PM, Adam DeVita <adev...@verifeye.com> wrote:
> Yes.
>
> A Date dimension  is a table that has all possible dates for your
> data, thus making reporting on properties of the date easy.  Something
> like this
>
> CREATE TABLE Date_dimension (
>  DateID int NOT NULL , /*an int key to match up to date fields in fact
> storage tables*/
>  [Date] datetime NOT NULL,
>  [Year] int NOT NULL,
>  [Month] int NOT NULL,
>  [Day] int NOT NULL,
>  [QuarterNumber] int NOT NULL,
>  [DayofWeek_name] text,
>  [Month_name] text,
>
> )
>


methinks you can calculate all of the above storing your dates as
strings in a single column, and using the date time functions on that
column. Check out the functions in the link I sent you.

>
>
> On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>
>> > What is a "Date Dimension"?
>>
>> Probably OP meant this:
>> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)<http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29>
>> .
>> But I don't have any answer to the question asked.
>>
>>
>> Pavel
>>
>> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor <punk.k...@gmail.com> wrote:
>> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita <adev...@verifeye.com>
>> wrote:
>> >> Good day,
>> >>
>> >> Given the context I'm in, sqlite is going to be used for our data
>> >> warehousing.  (We generate about 2MB of raw data in a month, so we don't
>> >> think we need a heavy DB engine.)
>> >>
>> >> Since most warehouses have one, which are very similar from application
>> to
>> >> application, I'm wondering if  there is somewhere to download a
>> pre-defined
>> >> Date Dimension?
>> >
>> > What is a "Date Dimension"? For SQLite's date time functions, see
>> > http://www.sqlite.org/lang_datefunc.html
>> >
>> >>  I could write my own script, but re-invent and debug the
>> >> wheel?
>> >>
>> >> regards,
>> >> Adam
>> >>
>> >> --
>> >> VerifEye Technologies Inc.
>> >> 905-948-0015x245
>> >> 7100 Warden Ave, Unit 3
>> >> Markham ON, L3R 8B5
>> >> Canada
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > Puneet Kishor http://www.punkish.org
>> > Carbon Model http://carbonmodel.org
>> > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> > Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> > ---
>> > Assertions are politics; backing up assertions with evidence is science
>> > ===
>> > ___
>> > 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
>>
>
>
>
> --
> VerifEye Technologies Inc.
> 905-948-0015x245
> 7100 Warden Ave, Unit 3
> Markham ON, L3R 8B5
> Canada
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Direct access of table data

[P Kishor]

On Wed, Apr 21, 2010 at 1:19 PM, Nathan Biggs  wrote:
> Is there a way to read the values of a table directly without building
> and executing a query.  I have a function that has predefined memory
> (counters) and increments them if the data in the record matches a hard
> coded value.
>
> Since this is hard-coded, I thought it might perform much without all of
> the memory allocations/de-allocations associated with the query engine.
>

Others have already responded, but in questions like this, my standard
response is... measure. If you think it might, it is time to measure,
do a test. Only you can do a test that is meaningful to you. Of
course, you can and should ask for ways to make your queries more
efficient, but in the end, a test on your hardware, in your
environment, is the only thing that matters.

That said, why choose a sql database, and then want to bypass its sql
engine to access the data directly? Even if you could, why would you?
Why not just begin with a more appropriate data store in the first
place? If you don't need sql, just go ahead and choose a key-value
db... Berkeley DB is free for the taking... try Tokyo Cabinet. I am
sure there are others.


> Thanks for your help.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Dimension

[P Kishor]

On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita  wrote:
> Good day,
>
> Given the context I'm in, sqlite is going to be used for our data
> warehousing.  (We generate about 2MB of raw data in a month, so we don't
> think we need a heavy DB engine.)
>
> Since most warehouses have one, which are very similar from application to
> application, I'm wondering if  there is somewhere to download a pre-defined
> Date Dimension?

What is a "Date Dimension"? For SQLite's date time functions, see
http://www.sqlite.org/lang_datefunc.html

>  I could write my own script, but re-invent and debug the
> wheel?
>
> regards,
> Adam
>
> --
> VerifEye Technologies Inc.
> 905-948-0015x245
> 7100 Warden Ave, Unit 3
> Markham ON, L3R 8B5
> Canada
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting from other table

[P Kishor]

On Mon, Apr 19, 2010 at 1:00 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>> In other words, unless it grossly and severely bothers your
>> sensibilities, don't waste your time on this exercise.
>
> AND unless application developer for some weird reason used '*' in
> select statements.
>
>

In other words, don't use SELECT * for production purposes, unless
there is a compelling reason to do so, or you are just incorrigibly
lazy.

However, I should clarify one assertion of mine. I wrote, "for the
most part, the order in which the columns appear in your table schema
is irrelevant." Which begs the question, when could placing columns a
certain way be strategically advantageous? For example, when you have
a blob column. Placing a blob column in between other columns can
reduce speed if you want to get to only the other columns. That is
because a blob may not fit in one page, and may go into overflow
pages, thereby causing other columns beyond it to also go into the
overflow pages. In such a case, it is advantageous to have a blob col
at the very end of your schema. Or, even better, add an additional
table that holds only a linking id col and the blob col, and have a
one-to-one relationship between your master table and the blob table.

I must also note, as a perl enthusiast, laziness *is* a virtue, but
practice safe laziness.

> Pavel
>
> On Mon, Apr 19, 2010 at 1:56 PM, P Kishor <punk.k...@gmail.com> wrote:
>> On Mon, Apr 19, 2010 at 12:47 PM, sabapathy <sabapathy...@rediffmail.com> 
>> wrote:
>>>
>>> The DB had some 15 columns before.
>>> And there are lot of records saved using the s/w tool.
>>> But in the latest version of tool there are some columns added in DB
>>> inbetween of existing columns.
>>> So to use the DB saved using previous version of tool, I need to add some
>>> columns(blank) in between in the old DB.
>>> But I came to know that new columns can be added only at the end.
>>
>>
>> First, and most importantly, you should realize that for the most
>> part, the order in which the columns appear in your table schema is
>> irrelevant. You might, for aesthetic reasons, want to insert some
>> columns in between, but the db doesn't and shouldn't care. You can
>> always reorder the display of your selected output by simply changing
>> the order of the columns in your SELECT statement.
>>
>> In other words, unless it grossly and severely bothers your
>> sensibilities, don't waste your time on this exercise. That said...
>>
>>> So I have to create an empty table in new format and insert the records from
>>> old table & delete the old table(?).
>>> How can I do this efficiently even if there are tens of thousands of
>>> records..?
>>
>> CREATE TABLE newtable (columns in your desired order);
>> INSERT INTO newtable (columns that exist in old table) VALUES SELECT
>> FROM oldtable ;
>>
>>
>> See http://www.sqlite.org/lang_insert.html
>>
>> "The second form of the INSERT statement takes its data from a SELECT
>> statement. The number of columns in the result of the SELECT must
>> exactly match the number of columns in the table if no column list is
>> specified, or it must match the number of columns named in the column
>> list. A new entry is made in the table for every row of the SELECT
>> result. The SELECT may be simple or compound."
>>
>>>
>>> Can I provide mapping of columns to insert from one table to another?
>>> (For eg, if the old table has 2 columns & new one 3 columns, I want to
>>> insert 1st column to 1st column, 2nd to 3rd and leave 2nd column empty in
>>> new table)
>>>
>>> Thanks..
>>> --
>>> View this message in context: 
>>> http://old.nabble.com/Inserting-from-other-table-tp28287723p28287723.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
>>>
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> ___
>> 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] Inserting from other table

[P Kishor]

On Mon, Apr 19, 2010 at 12:47 PM, sabapathy  wrote:
>
> The DB had some 15 columns before.
> And there are lot of records saved using the s/w tool.
> But in the latest version of tool there are some columns added in DB
> inbetween of existing columns.
> So to use the DB saved using previous version of tool, I need to add some
> columns(blank) in between in the old DB.
> But I came to know that new columns can be added only at the end.


First, and most importantly, you should realize that for the most
part, the order in which the columns appear in your table schema is
irrelevant. You might, for aesthetic reasons, want to insert some
columns in between, but the db doesn't and shouldn't care. You can
always reorder the display of your selected output by simply changing
the order of the columns in your SELECT statement.

In other words, unless it grossly and severely bothers your
sensibilities, don't waste your time on this exercise. That said...

> So I have to create an empty table in new format and insert the records from
> old table & delete the old table(?).
> How can I do this efficiently even if there are tens of thousands of
> records..?

CREATE TABLE newtable (columns in your desired order);
INSERT INTO newtable (columns that exist in old table) VALUES SELECT
FROM oldtable ;


See http://www.sqlite.org/lang_insert.html

"The second form of the INSERT statement takes its data from a SELECT
statement. The number of columns in the result of the SELECT must
exactly match the number of columns in the table if no column list is
specified, or it must match the number of columns named in the column
list. A new entry is made in the table for every row of the SELECT
result. The SELECT may be simple or compound."

>
> Can I provide mapping of columns to insert from one table to another?
> (For eg, if the old table has 2 columns & new one 3 columns, I want to
> insert 1st column to 1st column, 2nd to 3rd and leave 2nd column empty in
> new table)
>
> Thanks..
> --
> View this message in context: 
> http://old.nabble.com/Inserting-from-other-table-tp28287723p28287723.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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to fix Syntax - Check exists

[P Kishor]

On Sat, Apr 17, 2010 at 12:10 AM, gretty  wrote:
>
> Hello
>
> I am a programmer using SQLite3 to create a database for an application. I
> have been running into some problems with my SQL queries which I dont think
> are correct. Can you check them & correct them where relevant?
>
> Right now only one table exists, its layout is like so:
>  [quote]
> Table = element
> 2 columns = property TEXT PRIMARY KEY, value TEXT
>
> ie, CREATE TABLE element(property TEXT PRIMARY KEY, value TEXT)[/quote]
>
> Are these queries correct?
>
> Check if property already exists in table:
> [code]
> SELECT property
> FROM element
> WHERE property == 'color';[/code]
>

The correct query would be

SELECT property FROM element WHERE property = 'color';

However, you already know the property = 'color', so why SELECT it?

> If I get NULL back that will mean that this property does not exist in the
> table? Is this correct?
>

Well, no. If you get no rows back then it means that property =
'color' doesn't exist. NULL would mean that the property does exist
but has no value.

> Change the value in a row where property equals color:
> [code]
> DELETE value
> FROM element
> WHERE property == 'color';
>
> INSERT INTO element(value)
> VALUE('orange')
> WHERE property == 'color';
>

What you really want is

  UPDATE element SET value = 'orange' WHERE property = 'color';

I suggest you go through any of the many SQL tutorials you can find via Google.

> commit;[/code]
>
> Also I want to create a new TABLE with the name '#element' or '.element' but
> I always get an error because of the '#' & '.' characters. Is it possible to
> create a table with a '#' or '.' character in its name?

Yes. Quote the names with double quotes.


> --
> View this message in context: 
> http://old.nabble.com/How-to-fix-Syntax---Check-exists-tp28274195p28274195.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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[P Kishor]

On Thu, Apr 15, 2010 at 3:36 AM, Wiktor Adamski
 wrote:
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(a);
> sqlite> insert into t values(1);
> sqlite> insert into t values(2);
> sqlite> select * from (select * from t limit 1)
>   ...> union all
>   ...> select 3;
> 1
> sqlite>


fwiw, the above works correctly in 3.6.23 (see below), so that minor
version number bump might have introduced the issue

punk...@lucknow ~$sqlite3
-- Loading resources from /Users/punkish/.sqliterc
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t (a);
sqlite> INSERT INTO t VALUES (1);
sqlite> INSERT INTO t VALUES (2);
sqlite> SELECT * FROM t;
a
--
1
2
sqlite> SELECT * FROM t LIMIT 1;
a
--
1
sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1);
a
--
1
sqlite> SELECT * FROM (SELECT * FROM t LIMIT 1) UNION SELECT 3;
a
--
1
3
sqlite>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy one row to another

[P Kishor]

On Wed, Apr 7, 2010 at 6:57 PM, Nicolas Williams
 wrote:
> sqlite> CREATE TABLE foo(id INTEGER PRIMARY KEY, a, b, c);
> sqlite> insert into foo values(1, 'a', 'b', 'c');
> sqlite> select * from foo;
> 1|a|b|c
> sqlite> CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 1;
> sqlite> UPDATE tempfoo SET a = 'z';
> sqlite> INSERT OR REPLACE INTO foo SELECT * FROM tempfoo;
> sqlite> SELECT * FROM foo;
> 1|z|b|c
> sqlite>
>
> The trick is CREATE TEMP TABLE ... AS SELECT * FROM ... WHERE ...
> followed by INSERT OR REPLACE INTO ... SELECT * FROM .


Well, my problem is that INSERT OR REPLACE INTO is not working because
of (possibly) FTS3. By the way, REPLACE is an alias for INSERT OR
REPLACE.

This is where I wish I had a way to temporarily disable TRIGGERs, via a pragma.

There has to be some other way of copying the contents of one row into
another... a straight-ahead update, not an insert.





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

Re: [sqlite] copy one row to another

[P Kishor]

On Wed, Apr 7, 2010 at 4:24 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>> I get a "Error: constraint failed". I have no constraint other than
>> INTEGER PRIMARY KEY on id.
>
> You should have something other than integer primary key, otherwise it works:
>
> sqlite> create table t (id integer primary key, foo, bar);
> sqlite> insert into t values (649, 'foo 1', 'bar 1');
> sqlite> insert into t values (651, 'foo 2', 'bar 2');
> sqlite> .h on
> sqlite> select * from t;
> id|foo|bar
> 649|foo 1|bar 1
> 651|foo 2|bar 2
> sqlite> replace into t (id, foo, bar) select 649, foo, bar from t
> where id = 651;
> sqlite> select * from t;
> id|foo|bar
> 649|foo 2|bar 2
> 651|foo 2|bar 2
>
>


The only other stuff going on in my db is an FTS3 virtual table with
triggers that fire on update/insert/delete. Logically, that shouldn't
matter, because the REPLACE should be treated as a normal UPDATE, so
the FTS tables should get updated by the triggers without any problem.
Maybe REPLACE gets treated as an INSERT, which would cause the
constraint error (the insert_in_fts trigger would try to insert
another row with the same id in the FTS tables).


> Pavel
>
> On Wed, Apr 7, 2010 at 5:06 PM, P Kishor <punk.k...@gmail.com> wrote:
>> On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>>> Probably the only way to do that is
>>>
>>> REPLACE INTO t (id, foo, bar, ...)
>>> SELECT 649, foo, bar, ...
>>> WHERE id = 651
>>>
>>
>> I get a "Error: constraint failed". I have no constraint other than
>> INTEGER PRIMARY KEY on id.
>>
>>>
>>> Pavel
>>>
>>> On Wed, Apr 7, 2010 at 4:33 PM, P Kishor <punk.k...@gmail.com> wrote:
>>>> is there a canonical way of copying all the columns (except for the
>>>> PKs, of course) from one row to another in the same table? I want to
>>>> make all columns of row id = 649 in my table to become a duplicate of
>>>> the values in row id = 651... of course, I want the id 649 to remain
>>>> 649.
>>>>
>>>> UPDATE t649
>>>> SET
>>>>  t649.foo = t651.foo
>>>>  t649.bar = t651.bar
>>>>  ..
>>>> (FROM table t649 ... )   <=== this is where I am drawing a blank
>>>>
>>>>
>>>> --
>>>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] copy one row to another

[P Kishor]

On Wed, Apr 7, 2010 at 4:20 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 7 Apr 2010, at 10:06pm, P Kishor wrote:
>
>> On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>>> Probably the only way to do that is
>>>
>>> REPLACE INTO t (id, foo, bar, ...)
>>> SELECT 649, foo, bar, ...
>>> WHERE id = 651
>>>
>>
>> I get a "Error: constraint failed". I have no constraint other than
>> INTEGER PRIMARY KEY on id.
>
> I hope that you don't already have a record with id = 651.
>


Of course I have a row with id = 651. That is where I am copying the
data *from* into row with id = 649.


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users