[sqlite] Test failures on GPFS

[T J]

On Sunday, January 12, 2020, Roman Fleysher 
wrote:

>
> I use SQLite over GPFS , but in DELETE (which I think is the default)
> mode. Not WAL mode. No issues with locking, except performance when
> accessing concurrently from multiple nodes. As others pointed out, this has
> to do with the overhead due to lock requests. GPFS must coordinate with
> many nodes. My observation is that when concurrent access is from a few
> nodes, the performance is OK even though number of nodes is always the
> same. Thus, GPFS coordinates in some smart way only between nodes actively
> involved.
>
> One reason I do not use mySQL with its more efficient network access is
> that sys admin must set it up. With SQLite, I am independent. In addition,
> in my SQL there are authentication issues to be dealt with. I rely on GPFS
> file access permissions (access control list, ACL) to regulate access to
> database.
>
> I heard about BeadrockDB, which internally uses SQLite and provides
> network access with replication. I have not tried it and do not know what
> is involved.
>
>
>
MySQL and similar would indeed be nice to use, but in addition to the
administrative cost, there are also developer costs to get things set up so
that every developer can do work in their own db without affecting the
production db, as well as complexity costs with getting data into those
dbs. Contrast this with just copying the sqlite file(s) as needed (though
integrity concerns still exist).

So I'm mostly weighing options. The data is very much many-reads,
few-writes. Also considering just using an external locking service and
simple flat files, but this has obvious downsides of fewer (if any) data
types, no joins, no transactions, etc.

I may give this a try and see if the perf hit is tolerable.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Test failures on GPFS

[T J]

Hi,

I was interested in using sqlite over GPFS.  I've seen a few useful threads
on this:

   - Network file system that support sqlite3 well

   
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg117085.html

   - disable file locking mechanism over the network

   
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg116846.html

From these, I can see that there are some performance issues, even if I
willing (which I am not) to make all access (read+write) sequential. [I
don't expect to need many, if any, concurrent writers, but I will typically
have concurrent readers.]

To get a better sense of things, I downloaded 3.31.0 and ran the test suite
on GPFS.  Overall, it looks pretty good, but there were some WAL failures.
Could someone comment on the precise implication of those test failures?
I'm interested to know what usage patterns are likely to cause problems,
and which are likely safe.  Also, which other tests can I run (
https://www.sqlite.org/testing.html)? Perhaps more tests around concurrent
read/writes?

!Failures on these tests: e_walauto-1.1.2 e_walauto-1.1.3
e_walauto-1.1.5 e_walauto-1.1.7 e_walauto-1.1.12.3 e_walauto-1.1.12.5
e_walauto-1.2.2 e_walauto-1.2.3 e_walauto-1.2.5 e_walauto-1.2.7
e_walauto-1.2.12.3 e_walauto-1.2.12.5 zipfile-2.4a.2.1
zipfile-2.4a.2.2


Thanks in advance.  The `make test` output log snippet is below.
---

e_walauto-1.1.0... Ok

e_walauto-1.1.1... Ok

e_walauto-1.1.2...

! e_walauto-1.1.2 expected: [1]

! e_walauto-1.1.2 got:  [0]

e_walauto-1.1.3...

! e_walauto-1.1.3 expected: [1]

! e_walauto-1.1.3 got:  [0]

e_walauto-1.1.4... Ok

e_walauto-1.1.5...

! e_walauto-1.1.5 expected: [1]

! e_walauto-1.1.5 got:  [0]

e_walauto-1.1.6... Ok

e_walauto-1.1.7...

! e_walauto-1.1.7 expected: [1]

! e_walauto-1.1.7 got:  [0]

e_walauto-1.1.7... Ok

e_walauto-1.1.8... Ok

e_walauto-1.1.9... Ok

e_walauto-1.1.10.1... Ok

e_walauto-1.1.10.2... Ok

e_walauto-1.1.11.1... Ok

e_walauto-1.1.11.2... Ok

e_walauto-1.1.11.3... Ok

e_walauto-1.1.12.1... Ok

e_walauto-1.1.12.2... Ok

e_walauto-1.1.12.3...

! e_walauto-1.1.12.3 expected: [2]

! e_walauto-1.1.12.3 got:  [0]

e_walauto-1.1.12.4... Ok

e_walauto-1.1.12.5...

! e_walauto-1.1.12.5 expected: [1559]

! e_walauto-1.1.12.5 got:  [0]

e_walauto-1.2.0... Ok

e_walauto-1.2.1... Ok

e_walauto-1.2.2...

! e_walauto-1.2.2 expected: [1]

! e_walauto-1.2.2 got:  [0]

e_walauto-1.2.3...

! e_walauto-1.2.3 expected: [1]

! e_walauto-1.2.3 got:  [0]

e_walauto-1.2.4... Ok

e_walauto-1.2.5...

! e_walauto-1.2.5 expected: [1]

! e_walauto-1.2.5 got:  [0]

e_walauto-1.2.6... Ok

e_walauto-1.2.7...

! e_walauto-1.2.7 expected: [1]

! e_walauto-1.2.7 got:  [0]

e_walauto-1.2.7... Ok

e_walauto-1.2.8... Ok

e_walauto-1.2.9... Ok

e_walauto-1.2.10.1... Ok

e_walauto-1.2.10.2... Ok

e_walauto-1.2.11.1... Ok

e_walauto-1.2.11.2... Ok

e_walauto-1.2.11.3... Ok

e_walauto-1.2.12.1... Ok

e_walauto-1.2.12.2... Ok

e_walauto-1.2.12.3...

! e_walauto-1.2.12.3 expected: [2]

! e_walauto-1.2.12.3 got:  [0]

e_walauto-1.2.12.4... Ok

e_walauto-1.2.12.5...

! e_walauto-1.2.12.5 expected: [1559]

! e_walauto-1.2.12.5 got:  [0]

e_walauto.test-closeallfiles... Ok

e_walauto.test-sharedcachesetting... Ok

Time: e_walauto.test 92703 ms

...

zipfile2.test-closeallfiles... Ok

zipfile2.test-sharedcachesetting... Ok

Time: zipfile2.test 14 ms

Memory used:  now 24  max9283664  max-size   16908288

Allocation count: now  1  max1311131

Page-cache used:  now  0  max 13  max-size  65800

Page-cache overflow:  now  0  max   20640016

SQLite 2020-01-10 01:05:49
0a500da6aa659a8e73206e6d22ddbf2da5e4f1d1d551eeb66433163a3e13109d

14 errors out of 249964 tests on localhost Linux 64-bit little-endian

!Failures on these tests: e_walauto-1.1.2 e_walauto-1.1.3
e_walauto-1.1.5 e_walauto-1.1.7 e_walauto-1.1.12.3 e_walauto-1.1.12.5
e_walauto-1.2.2 e_walauto-1.2.3 e_walauto-1.2.5 e_walauto-1.2.7
e_walauto-1.2.12.3 e_walauto-1.2.12.5 zipfile-2.4a.2.1
zipfile-2.4a.2.2

All memory allocations freed - no leaks

Memory used:  now  0  max9283664  max-size   16908288

Allocation count: now  0  max1311131

Page-cache used:  now  0  max 13  max-size  65800

Page-cache overflow:  now  0  max   20640016

Maximum memory usage: 9283664 bytes

Current memory usage: 0 bytes

Number of malloc()  : -1 calls
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table was deleted on macOS

[t...@qvgps.com]

-- Originalnachricht --
Von: "Simon Slavin" mailto:slav...@bigfraud.org>>
An: "SQLite mailing list" 
mailto:sqlite-users@mailinglists.sqlite.org>>
Gesendet: 15.10.2019 23:24:17
Betreff: Re: [sqlite] Table was deleted on macOS

On 15 Oct 2019, at 10:11pm, t...@qvgps.com<mailto:t...@qvgps.com> wrote:

"no such table: mytable"

The client sent us the database and the table "mytable" was really gone. The 
database also seems to be fine, no errors.

Do you mean by that that you ran integrity_check() ?
is ok



Is everything else there, or did another table, or rows from another table, 
disappear ?

everything else is there, just this one table is gone.


Did the client's computer crashed or otherwise failed at any point ?
He didn't report that, I believe its quiet unlikely with a 2015 MacBookPro.



Is there a chance that the client tried to mess with the table using a database 
editor tool ?
no



Does the client have backups ? Can you look at them and isolate the daterange 
during which the problem occurred ?
no



In our app is no DROP TABLE command, what else can cause a table to be deleted?

ALTER TABLE RENAME
There is no code in the app, which is altering or dropping any table.

Tom


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

[sqlite] Table was deleted on macOS

[t...@qvgps.com]

Hi Group,

here is a very strange and rare fault which one of our users experienced on 
macOS .

He was working in our app for a while creating a lot of new data.
So over several hours many INSERT INTO mytable 
Without any errors.

Then he did a query SELECT COUNT(*) FROM mytable WHERE  (deleted=0 OR deleted 
IS NULL) AND IdCat=2
and an exception occurs:
"no such table: mytable"

The client sent us the database and the table "mytable" was really gone.
The database also seems to be fine, no errors.
How can this happen?
In our app is no DROP TABLE command, what else can cause a table to be deleted?

We have a few hundred copies of our app out, and this is the first time this 
error happens.
Its on macOS and the sqlite-version is 3024000


Thanks
Tom



/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com<mailto:t...@qvgps.com>
**   +264 (0)81 3329923Nam mobile
**   +49  (0)175 7313081   D mobile
**   +49  (0)6182 8492599  D office
***/

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

[sqlite] WITHOUT ROWID

[t...@qvgps.com]

Hi sqlite-group,

I accidentally created tables WITHOUT ROWID.
These tables are filled with some 100MB of data and delivered to customers 
already.

Now I'm looking for way to fix this bug.
Is it possible to change (ALTER TABLE?) these tables to still include rowid?

One option would be to create a new table with rowid, copy all records from the 
old one, delete old one and rename new table.
But maybe there is more simple, quicker solution?
Like just adding a column?

Thanks,
Tom


/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923Nam mobile
**   +49  (0)175 7313081   D mobile
**   +49  (0)6182 8492599  D office
***/

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

Re: [sqlite] Strategies to reduce page-loads?

[t...@qvgps.com]

Right.

Was trying it out now, compiling some osm-dbs with primary key generated 
with this morton encoding from lat,lon and the performance is even 
worse.
Debugging with the sqlite-tool shows, that the page counts for specific 
queries are almost double then before.

Seems like, from the sqlite-side the only options is to have page size 
as big as possible and line-data in the blob-field as much compressed as 
possible.



-- Originalnachricht --
Von: "Simon Slavin" 
An: "SQLite mailing list" 
Gesendet: 31.08.2018 19:07:36
Betreff: Re: [sqlite] Strategies to reduce page-loads?

>On 31 Aug 2018, at 2:46pm, J Decker  wrote:
>
>>There was a voxel engine that was claiming they were going to move to 
>>a
>>morton encoding; and I was working with a different engine, so I built 
>>a
>>simulator to test averge lookup distances; it was far more efficient 
>>to
>>keep sectors of voxels (32x32x32) in flat indexing, which made the 
>>maximum
>>distance 1025
>
>I can confirm that SatNav units do not keep their maps in Morton code 
>order.  It's not a no-brainer go-to solution for mapping.  However, the 
>analysis done to figure out a good storage order is rather complicated 
>and off-topic for this list.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strategies to reduce page-loads?

[t...@qvgps.com]

Ok, then WITHOUT ROWID will most properly fit best in our use case.
Then I can fill the PRIMARY KEY with the z-order and store the osm-id 
just in another column.

But do I still need to fill the table in the correct order according to 
z-order?
I mean, we are talking about 1mio rows or so.
At which point during insert are the pages actually written?


-- Originalnachricht --
Von: "Richard Hipp" 
An: "SQLite mailing list" 
Gesendet: 31.08.2018 15:10:15
Betreff: Re: [sqlite] Strategies to reduce page-loads?

>On 8/31/18, t...@qvgps.com  wrote:
>>
>>So is it just the value of the primary key controlling in which page 
>>the
>>row is stored?
>
>The page on which content is stored is determine (approximately) by
>the value of the ROWID, which is the same as the INTEGER PRIMARY KEY
>as long as you declare the primary key to be of type "INTEGER".  If
>you declare the PRIMARY KEY to be something other than "INTEGER" (for
>example: "INT" or "LONG") then the ROWID and the primary key are
>different and the primary key has no influence over where the content
>is stored.
>
>Or if you create a WITHOUT ROWID table, then the PRIMARY KEY is the
>value that determines (approximately) on which page the content is
>stored.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strategies to reduce page-loads?

[t...@qvgps.com]

>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
>features that are close together geographically to tend to be close
>together within the file.

My primary key is actually the Id of the specific object in the 
OpenStreetMap-database, and we also need this osm-id in the app.
I was trying just to order all rows by the "Z-Order curve"-value first 
before inserting them.

for each (line in lines.ordered.by.zvalue)
{
 insert line  into lines
 insert line  into lines_rtree
}

But this doesn't seem to work.
At least, I don't see any improvements in page-usage.

So is it just the value of the primary key controlling in which page the 
row is stored?





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

Re: [sqlite] Strategies to reduce page-loads?

[t...@qvgps.com]

It would be interesting to "measure" the effect of these ideas during 
the process of optimizing.
I can profile and measure the execution times, but also interesting 
would be to know, how much pages are involved in a specific query.

Is there maybe a way to get the count of pages currently used?



-- Originalnachricht --
Von: "Richard Hipp" 
An: "SQLite mailing list" 
Gesendet: 30.08.2018 13:48:30
Betreff: Re: [sqlite] Strategies to reduce page-loads?

>On 8/30/18, t...@qvgps.com  wrote:
>>
>>Structure is  simple:
>>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
>>BLOB, Flags INT, StyleId INT);
>>And an rtree-index:
>>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
>>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
>
>Three points that might help, either separately or in combination:
>
>(1) Use exactly "INTEGER PRIMARY KEY".  "LONG PRIMARY KEY" and "INT
>PRIMARY KEY" are not the same thing and do not work as well.
>
>(2) In the very latest versions of SQLite, 3.24,0 and the beta for
>3.25.0, you can put the "Lines" information directly in the RTree:
>
>   CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0,
>z1, +Label, +Coordinates, +Flags, +StyleId);
>
>The extra columns in r-tree are prefaced by a "+" character so that
>the r-tree module knows that they are auxiliary columns and not extra
>coordinates.
>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
>features that are close together geographically to tend to be close
>together within the file.  There is are two extension functions in the
>https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the
>SQLite source tree that might help you with this.  Or you can do the
>same using your own functions.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strategies to reduce page-loads?

[t...@qvgps.com]

Thanks guys for quick and competent answers!

After first sight, this "Z-Order curve" looks very promising, will give 
it a try.

The LONG PRIMARY KEY is because  I need a 64-bit integer (osm-id).
I only learned now, that sqlite-int is also 64 bit long.
Will change to INT PRIMARY KEY now.

Tom


-- Originalnachricht --
Von: "Richard Hipp" 
An: "SQLite mailing list" 
Gesendet: 30.08.2018 13:48:30
Betreff: Re: [sqlite] Strategies to reduce page-loads?

>On 8/30/18, t...@qvgps.com  wrote:
>>
>>Structure is  simple:
>>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
>>BLOB, Flags INT, StyleId INT);
>>And an rtree-index:
>>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
>>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
>
>Three points that might help, either separately or in combination:
>
>(1) Use exactly "INTEGER PRIMARY KEY".  "LONG PRIMARY KEY" and "INT
>PRIMARY KEY" are not the same thing and do not work as well.
>
>(2) In the very latest versions of SQLite, 3.24,0 and the beta for
>3.25.0, you can put the "Lines" information directly in the RTree:
>
>   CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0,
>z1, +Label, +Coordinates, +Flags, +StyleId);
>
>The extra columns in r-tree are prefaced by a "+" character so that
>the r-tree module knows that they are auxiliary columns and not extra
>coordinates.
>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
>features that are close together geographically to tend to be close
>together within the file.  There is are two extension functions in the
>https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the
>SQLite source tree that might help you with this.  Or you can do the
>same using your own functions.
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strategies to reduce page-loads?

[t...@qvgps.com]

Hi Guys,

we are using SQlite for storing OpenStreetMap ways (lines).
Once filled its readonly.
Then the lines is queried for specific areas to draw a map.

Structure is  simple:
CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates 
BLOB, Flags INT, StyleId INT);
And an rtree-index:
CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0 
FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);

Queries are always all lines of a specific geographical ares, which is 
very fast because of the rtree.
SELECT Lines.* FROM Lines_bb , Lines WHERE y0 < ? AND y1 > ? AND x0 < ? 
AND x1 > ? AND ? BETWEEN z0 AND z1 AND Lines_bb.Id = Lines.Id ;

Then the rows are fetched and here starts the problems:
var stmt = SQLite3.Prepare2 (handle, select);
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
// fetch row
}

The larger the db, the slower is the fetching!

We compared a small db (50mb) and a big db (500mb), both containing the 
same small area:
Reading the same area of  for example 1000 lines from the small db is 2x 
faster then from the large db.
After doing some profiling, it turned out, that the extra time was spent 
in SQLite3.Step.

My assumption is, that in the big db, these 1000 lines are just spread 
over a much higher count of pages.
So more page-loads resulting in more time.

We changed page_size to the maximum value of 64k and it became much 
better, but still I would lke to improve it.

Thanks,
Tom



/
** Flemming Software Development CC
** Thomas Flemming
** PO Box 81244
** Windhoek, Namibia
** http://www.quovadis-gps.com
** mail t...@qvgps.com
** +264 (0)81 3329923 Nam mobile
** +49 (0)175 7313081 D mobile
** +49 (0)6182 8492599 D office
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When is db size an issue?

[Jason T. Slack-Moehrle]

Hello All,

Off and on for the last few years I have been writing an e-mail client to
scratch a personal itch. I store the mail in SQLite and attachments on the
file system. However, I recently brought in all of my mail for the last 15
years from mbox format. Now, my database size is over 10gb. I'm not seeing
any real performance issues and my queries are executing nice and fast
during search.

However, does anyone have any thoughts about the size? Should I be
concerned? Is there a theoretical limit I should keep in the back of my
mind?

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

[sqlite] When is db size an issue?

[Jason T. Slack-Moehrle]

Hello All,

Off and on for the last few years I have been writing an e-mail client to
scratch a personal itch. I store the mail in SQLite and attachments on the
file system. However, I recently brought in all of my mail for the last 15
years from mbox format. Now, my database size is over 10gb. I'm not seeing
any real performance issues and my queries are executing nice and fast
during search.

However, does anyone have any thoughts about the size? Should I be
concerned? Is there a theoretical limit I should keep in the back of my
mind?

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

[sqlite] Possible thread-safety bug in lemon parser with ParseTrace()

[Fletcher T. Penney]

1)  I hope I am reporting this to the right place.  If not, my apologies.

2) I have been using lemon parsers for a year or more, but am by no 
means an export on the lemon source itself.  I did not see this issue 
referenced elsewhere, my apologies if I missed it.



I *think* there is a thread-safety issue in the ParseTrace() function:

void ParseTrace(FILE *TraceFILE, char *zTracePrompt){
  yyTraceFILE = TraceFILE;
  yyTracePrompt = zTracePrompt;
  if( yyTraceFILE==0 ) yyTracePrompt = 0;
  else if( yyTracePrompt==0 ) yyTraceFILE = 0;
}

It appears that `yyTraceFILE` and `yyTracePrompt` are global variables 
that can conceivably be written to simultaneously on two separate 
threads.  I suspect the negative effects of this would be low 
(overwriting of one prompt with another, and they would likely be 
identical strings anyway).



It is detected by Xcode's Thread Sanitizer, and I wanted to report it in 
case there was a more untoward effect that I was missing.  If nothing 
needs to be done about it, that's fine too.



Thanks!

Fletcher




--
Fletcher T. Penney
fletc...@fletcherpenney.net
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] confused getting started

[NTrewartha T-Online]

I too am new to DB's and Sqllite.
I have downloaded the binaries for win 10 and there is a dll and def file.
I clicked on the def file hoping this would integrate the dll into VS 
2015 community, but all what
happened was that the def file get listed. and I cannot seem to find an 
import def  tool.

Have to google that but *I would be grateful for any pointers*.

There is a GUI tool - which I think is better for learners which ought 
to be mentioned.

http://sqlitebrowser.org/ The "DBBrowser for sqllite".

Any examples of a C,C++,C# or Python usage for sqllite.?

I would like sqllite on my raspberry pi 3 after I have gained experience 
under Windows 10.


Judging what the replies to questions, the very new beginners are left a 
bit out in the cold.

Perhaps the documentation ought to cover the need of pure beginners.

Regard to you all,

NT


On 05.03.2017 10:54, a...@zator.com wrote:

Besides the need to include RDBMS engine inside your application, and manage it 
from the code. You have a standalone application (sqlite3.exe) who let manage 
your databese from the CLI (command line interpreter) of your system, and play 
whit the majority options who SQLite offer.

HTH.

--
Adolfo


 Mensaje original 
De: John Albertini <john.albert...@gmail.com>
Para:  sqlite-users@mailinglists.sqlite.org
Fecha:  Sat, 4 Mar 2017 19:10:26 -0500
Asunto:  [sqlite] confused getting started

I'm not a computer novice, but also not a nerd/geek.

Been using PCs since the mid 1980s and have used dBase III+ and Approach
previously.

I can't seem to grasp what I need to download / install to use SQLite?

Can someone guide me through the process?  Looking to use it with
RootsMagic.

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


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



--

Nigel Trewartha
Sonnenweg 3
33397 Rietberg
Germany
Tel: 05244/3631 Fax: 05244/9063266
ntrewar...@t-online.de

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

[sqlite] Best way to temporarily store data before processing

[Joseph T.]

Mr. Moules, why not skip the raw tables entirely? Or failing that a separate in 
memory db would probably be the best and quickest option.



Sent from my Samsung Epic? 4G TouchJonathan Moules  wrote:Hi List,
I'm wondering if anyone can offer me a "best practice" way of doing this.

I'm doing some log analysis using Python/SQLite. Python parses a log file and 
splits the raw data from each line in the log into one of about 40 tables in an 
SQLite database (I'll call them Raw Tables).

Once a log file has been processed like this, I run some SQL which takes the 
data from the Raw Tables and aggregates/processes it into about 10 different 
"Prepared Tables" which are read with a bunch of Views. The 
aggregation/processing doesn't take long, and the SQL for it is simple.

I'd like to update the Prepared Tables after each log file is read because 
there are thousands of files and I don't want to have to rely on having GB of 
disk space sitting around for temporary Raw Tables.

Once the Prepared Tables have been created, there's no real need to keep the 
data in the Raw Tables.

The Prepared Tables don't have to be in the same database as the Raw Tables. 
I'm happy to use ATTACH.

So my question:
What's the best way to do this with the minimum overhead?

Options that have come to mind (probably missed a lot):
??? - Some sort of empty template database for the Raw Tables which is 
copied/cloned/overwritten for each file processed.
??? - And/Or use "DELETE FROM Raw_Tables" to truncate it after each 
file (there are no indexes).
??? - And/Or place it into :memory:.
??? - And/Or just CREATE the Raw Tables for each file?
??? - And/Or do it within the Prepared Tables database and use "DELETE 
FROM Raw_Tables". (That file you wouldn't want in :memory: of course).


Thoughts welcome, thanks for your time,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099


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

[sqlite] How to insert a pointer data to sqlite?

[Joseph T.]

Unless, I'm wrong. What you want to do is use two tables. One to store the node 
values and another that references them for whatever object using them. Say, 
points for a pair of triangles, a,b,c,d,e. If table triangle is a table 
pointing at the table point (id,object,point) you could have a triangle sharing 
points and then when the shared point is changed the triangles would change to 
if reloaded.




Sent from my Samsung Epic? 4G TouchYAN HONG YE  wrote:I 
have a data:
Id  pid namemark
1   0   f1   sample
2   1   f2   sample
3   1   f3   sample
4   2   f4   sample
5   2   *id(2).name *id(2).mark

These means that under id(2) and id(5) have same node, if change one of the 
node, the other update auto,
How to realize this function?
Thank you!

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[J T]

I just thought of what may be a simpler solution.

I'm assuming that there is a certain limit to the length of the books 
(positions can be safely assumed to never exceed say, 100,000)

So what can be done is

update page set position=position + 10 where position>='3';
insert into page(book,position) values('1','3');
update page set position=position - 9 where position>10;

This will work around the unique contraint and seems simpler than dropping it 
everytime you want to insert a page.

 
 

 

 

-Original Message-
From: Gwendal Roué 
To: General Discussion of SQLite Database 
Sent: Mon, Dec 8, 2014 12:07 pm
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail



> Le 8 déc. 2014 à 17:21, Simon Slavin  a écrit :
> 
>> Why not an opt-in way to ask for deferred constraint checking. The key here 
is only to allow perfectly legit requests to run. With all the due respect to 
sqlite implementors and the wonderful design of sqlite.
> 
> SQL-99 includes a syntax for deferred checking.  We don't need to invent our 
own syntax with a PRAGMA. However, it is done when the constraint is defined 
rather than being something one can turn on or off.  So you would need to think 
out whether you wanted row- or transaction-based checking when you define each 
constraint in the first place.

Hi Simon,

This topic is fascinating. Googling for SQL-99 deferred checking, I stumbled 
upon this page which shows how deferred index maintenance affects Oracle query 
plan, and performance : 
https://alexanderanokhin.wordpress.com/deferred-index-maintenance/.

I now understand that the strategy for checking index constraints is tied to 
their maintenance.

The `UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 
1` query we are talking about has indeed to perform both. Such an 
innocuous-looking request, and it sends us right into the very guts of 
relational constraints :-)

Gwendal

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


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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[J T]

I just thought of what may be a simpler solution.

I'm assuming that there is a certain limit to the length of the books 
(positions can be safely assumed to never exceed say, 100,000)

So what can be done is

update page set position=position + 10 where position>='3';
insert into page(book,position) values('1','3');
update page set position=position - 9 where position>10;

This will work around the unique contraint and seems simpler than dropping it 
everytime you want ot insert a page.

 

 

 

-Original Message-
From: James K. Lowden 
To: sqlite-users 
Sent: Tue, Dec 9, 2014 10:38 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail


On Mon, 08 Dec 2014 22:01:15 +0700
Dan Kennedy  wrote:

> On 12/08/2014 09:55 PM, Nico Williams wrote:
> > Ideally there would be something like DEFERRED foreign key checking
> > for uniqueness constraints...
> 
> You could hack SQLite to do enforce unique constraints the same way
> as FKs. When adding an entry to a UNIQUE index b-tree, you check for
> a duplicate. If one exists, increment a counter. Do the opposite when 
> removing entries - decrement the counter if there are two or more 
> duplicates of the entry you are removing. If your counter is greater 
> than zero at commit time, a UNIQUE constraint has failed.

It's not *deferred* constraint checking.  It's constraint checking.
Best to honor the transaction first.  

Rather than adding to the syntax, perhaps a pragma could cause updates
to happen in a transaction: 

1.  Create a temporary table to hold the after-image of the updated
rows. 
2.  begin transaction
3 . Delete the rows from the target table. 
3.  Insert the updated rows from the temporary table.  
4.  commit
5.  drop temporary table. 

Of course there are more efficient answers available deeper in the
update logic, affecting only the partcular columns at the time the
constraint is enforced.  I guess they all involve deleting the
prior set from the index and inserting the new one.  

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

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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[J T]

 

 Cancel that, apparently that only updates the last record...

 

-Original Message-
From: John McKown 
To: General Discussion of SQLite Database 
Sent: Mon, Dec 8, 2014 9:18 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail


On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen 
wrote:

> I am like you, Gwendal, in that I don't like that behavior in SQLite;
> however, not liking it doesn't make it a bug.
>

​On another of my forums, this is called a BAD - Broken, As Designed.​ As
opposed to the normal WAD - Working As Designed.

-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[J T]

Might have another work around.

update page set position=position + 1 where designation=(select designation 
from page where book='1' order by position desc)

and then insert your page.

Please see if that'll work. I tested it, but your results may differ.



 

 

 

-Original Message-
From: RSmith 
To: General Discussion of SQLite Database 
Sent: Mon, Dec 8, 2014 9:15 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail



On 2014/12/08 15:58, Gwendal Roué wrote:
> I'm new to this mailing list, and I won't try to push my opinion, which is : 
yes this is a bug, and this bug could be fixed 
> without introducing any regression (since fixing it would cause failing code 
to suddenly run, and this has never been a 
> compatibility issue). Thank you all for your support and explanations. The 
root cause has been found, and lies in the constraint 
> checking algorithm of sqlite. I have been able to find a work around that is 
good enough for me. Now the subject deserves a rest, 
> until, maybe, someday, one sqlite maintainer who his not attached to the 
constraint-checking algorithm fixes it. Have a nice day, 
> Gwendal Roué 

Your new-ness is irrelevant, if you have a worthy argument it deserves being 
heard. To that end, let me just clarify that nobody was 
saying the idea of deferring the constraint checking is invalid or ludicrous 
(at 
least I had no such intention) and you make a valid 
point, especially since most other DB engines do work as you suggest - and this 
will be fixed in SQLite4 I believe, where 
backward-compatibility is not an issue.

The reason I (and others) will say it isn't a bug is because it isn't working 
different than is intended, or more specifically, than 
is documented. It works exactly like described - whether you or I agree with 
that paradigm or not is up to discussion but does not 
make it a "bug" as long as it works as described.

I hope the work-around you found works great!



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


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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[J T]

Sorry, wasn't focused on what I was looking at. Though, you said you already 
tried the order by without success which would have been my next suggestion or 
clarification of my first. As, you should be able to update the rows from the 
end down to the page that would be after your insertion (update pages set 
position=position + 1 where book=0 order by position desc.) and then inserting 
the new page at the desired position. But if that's not working, I have to 
agree with your opinion of it being a bug.

 

 

-Original Message-
From: Gwendal Roué <g...@pierlis.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, Dec 8, 2014 8:40 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail


J T,

I did provide a sequence of queries that reliably reproduce the issue (see 
below, from the first CREATE to the last UPDATE). There is no trigger involved, 
as far as I know. Forgive me but I don't see how I could use your advice.

My work around has been to destroy the unique index, and then re-create it 
after 
the update. This solution is good enough as my table is not that big, and the 
"pure" code path remains intact, with only two inserted statements that are 
easily described and commented.

Gwendal Roué

> Le 8 déc. 2014 à 14:24, J T <drenho...@aol.com> a écrit :
> 
> Try having your cascade occur before the row is created, updated or deleted.
> 
> http://www.sqlite.org/lang_createtrigger.html
> 
> 
> 
> 
> 
> 
> 
> -Original Message-
> From: Richard Hipp <d...@sqlite.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Mon, Dec 8, 2014 8:14 am
> Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail
> 
> 
> On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué <g...@pierlis.com> wrote:
> 
>> Hi,
>> 
>> Unique indexes make some valid update queries fail.
>> 
>> Please find below the SQL queries that lead to the unexpected error:
>> 
>> -- The `books` and `pages` tables implement a book with several pages.
>> -- Page ordering is implemented via the `position` column in the pages
>> table.
>> -- A unique index makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position
>> are not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
>> 1;
>> 
>> The query should run without any error, since it does not break the unique
>> index.
>> 
> 
> Uniqueness is checked for each row change, not just at the end of the
> transaction.  Hence, uniqueness might fail, depending on the order in which
> the individual rows are updated.
> 
> 
>> 
>> Thank you for considering this issue.
>> 
>> Cheers,
>> Gwendal Roué
>> 
>> ___
>> 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

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


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

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[J T]

Try having your cascade occur before the row is created, updated or deleted.

http://www.sqlite.org/lang_createtrigger.html

 

 

 

-Original Message-
From: Richard Hipp 
To: General Discussion of SQLite Database 
Sent: Mon, Dec 8, 2014 8:14 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail


On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué  wrote:

> Hi,
>
> Unique indexes make some valid update queries fail.
>
> Please find below the SQL queries that lead to the unexpected error:
>
> -- The `books` and `pages` tables implement a book with several pages.
> -- Page ordering is implemented via the `position` column in the pages
> table.
> -- A unique index makes sure two pages do not share the same position.
> CREATE TABLE books (
> id INT PRIMARY KEY
> )
> CREATE TABLE pages (
> book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
> UPDATE CASCADE,
> position INT
> )
> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>
> -- Let's populate the tables with a single book and three pages:
> INSERT INTO books VALUES (0);
> INSERT INTO pages VALUES (0,0);
> INSERT INTO pages VALUES (0,1);
> INSERT INTO pages VALUES (0,2);
>
> -- We want to insert a page between the pages at positions 0 and 1. So we
> have
> -- to increment the positions of all pages after page 1.
> -- Unfortunately, this query yields an error: "columns book_id, position
> are not unique"/
>
> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
> 1;
>
> The query should run without any error, since it does not break the unique
> index.
>

Uniqueness is checked for each row change, not just at the end of the
transaction.  Hence, uniqueness might fail, depending on the order in which
the individual rows are updated.


>
> Thank you for considering this issue.
>
> Cheers,
> Gwendal Roué
>
> ___
> 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

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[J T]

The questions you have to ask is

Are the rows I'm returning identifiable by a unique id -- typically the row id, 
but also unique identifiers, like ISBN for books, Employee ID for employees, 
etc. If you find duplicates of what should be a unique id in a table then its 
probably a sign the data is bad. (Two books with the same ISBN, two employees 
with the same Employee ID.) Of course, the other possibility is that the 
database wasn't normalized and the standard operations (Create, Read, Update, 
Delete) weren't used in a logical fashion (for instance leaving out a way to 
update or delete employees and thus making it impossible to rename an employee 
who's changed their name.)

When I search for this author are the books returned normalized against the 
author's table? That is, is there an identifier shared between the tables that 
allows one table to be searched in relation to the other? (The relational part 
of databases.)

The other thing is finding external sources to verify against, or performing 
tests as mentioned by other members of this list.

Create a test author.
create test books by the test author.
do you get only the books you entered for that author?
If not, why not?
If so, then can you repeat the results?

Another thing to look at are your queries.

Select [fields] from [table] where [condition]
Insert into [table] ([columns]) values ([value for each column])
Delete from [table] where [condition]
Update [table] set [field]=[value], [field2]=[value2] ... where [condition]
Select [fields] from [table] where [condition] limit [rows to skip],[rows to 
return]


If you have doubts about the accuracy of the tool you're using there are free 
SQLite Managers out there.

And then there's always the last option which is reading the file manually. 
This last requires a bit more understanding of the database engine itself as 
you need to be able to identify what type is supposed to be where by the bytes 
of the file, and would probably have to write a program to do this 
programmatically instead of trying to do it manually.



 

 

 

-Original Message-
From: Dwight Harvey 
To: sqlite-users 
Sent: Sun, Dec 7, 2014 9:24 pm
Subject: [sqlite] How to Verify (know that they are correct) Queries in SQLite 
GUI Manager Firefox add-on?


I am a student with no Tech or IT background what so ever.

I am new to Databases and IT in general.

I am taking an accelerated class in database basics and within the last
three weeks I just learned what databases were.

I know very little and Databases are complex and intimidating.

I figured out how to run queries but I don't know if they are
correct/accurate, as in what I requested from the 'RUN' results?

How do you 'VERIFY' your query results?


My instructor wants me to explain how do I KNOW that the records are
accurate. Here is an example of what is expected in the assignment...

 *VERIFICATION:  *What is verification?  Each time you retrieve data, you
should ask yourself, "How do I know I selected the correct data?".   For
example, if you were asked to pull all records written by an author named
Fred Smith, your query might be based on last name equal to Smith.
However, if you might get records for someone with the first name of Fred,
Mary and Ginger.   What would you do to insure you are pulling only Fred?
The person who has requested the data will always want assurance from you
that you are 100% positive you pulled the correct records.  Look at the
records returned and always as yourself, did I pull the correct records?
How would I verify it?

"Capture each query, number of records returned and *explain your
validation of the query.*" Example:

/** First query 1. List all employees **/
SELECT dbo.Firstname, dbo.Lastname
FROM dbo.employees
--records returned: 24
*--Validation:  I did a quick listing of top 200 records and 4 were
returned*.
___
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] Search for text in all tables

[J T]

Dominique,

Why not get a column count (datareader.fieldcount (C#) or 
cursor.getColumnCount() (Java/Android))?

>From there you should be able to simply do a 
try { 
str = getString(columnIndex); 
checkValue(str); 
} 
catch(Exception e) {  
// wasn't a string or the check (and replace) failed
// you may want to catch different exceptions
}





 

 

 

-Original Message-
From: Dominique Devienne 
To: General Discussion of SQLite Database 
Sent: Thu, Dec 4, 2014 6:57 am
Subject: Re: [sqlite] Search for text in all tables


On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein 
wrote:

> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
> > If you are a coder then it is a relatively straight forward process
> > Loop through each table
> >Loop through each column
>
> This is the part I am having trouble with. I can loop through tables using
> sqlite3_master, but how do I loop through columns? Parse the schema?


http://www.sqlite.org/pragma.html#pragma_table_info  --DD
___
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] General R*Tree query

[Brian T. Carcich]

On Wed, Dec 18, 2013 at 1:53 AM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Perhaps this is a weird way for me to get deeper knowledge of R trees, and
> because I vaguely remember that Tyco refers to a specific epoch in which
> coordinates are defined, but would it be possible to search R tree using a
> cone, i.e. stars within a cone of certain degree around given star? This
> would require a trigonometric calculation before comparison can be made but
> can be done in a single comparison.
>
> Or, since RA and DEC coordinates are not area preserving (nor distance) --
> i.e. angle between stars at DEC =0 is bigger than angle between stars at
> DEC=80 when they are the same delta RA apart -- then maybe instead of
> defining rectangular FOV in RA and DEC one should be defining rectangular
> FOV in DEC, sin(RA)? Then one would not need two searches.
>
> The goal is to find neighbors to a given star defined roughly by some
> metric? Since there's nothing magical in RA , DEC coordinates the metric
> could use some other coordinates?



Every [RA,DEC] pair resolves to a unit vector in Cartesian coordinate space
i.e. an [X,Y,Z] triplet on the surface of a unit sphere; that would be a
continuous metric without the RA=0=360 issue.  I don't see why the R*Tree
could not be set up with X, Y, and Z, plus magnitude limits; the set of
nodes is hollow in a 3D sense so the first-level non-leaf nodes would have
a lot of empty space, but I don't think that matters; I've been thinking
about doing it this way for some time.  For my app I already store XYZs in
the outer, non-R*Tree table because all final comparisons have to be in
Cartesian space anyway.

But in general the search region is so small that the cosine[DEC]
dependence of distance per degree of RA is effectively constant for any one
search, and  an [RA,DEC,Mag] tree should be "good enough" because it pares
down the search space quickly from 2.5M stars in Tycho=2 to a few hundred
very quickly, with the caveat that there is a special case near RA=0=360.

In any event the R*Tree is not going to do the final geometric comparison,
rather it reduces the number of stars that need that comparison, and that's
the goal.

Btw, if you want to see something cool, look at chapter 4 of Dustin Lang's
thesis (www.astro.princeton.edu/~dstn/lang-thesis.pdf); the whole paper is
cool, but I really enjoy that chapter.

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

Re: [sqlite] General R*Tree query

[Brian T. Carcich]

On Tue, Dec 17, 2013 at 3:57 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

>
> Since coordinate system is spherical, how do you tell that RA=23:59 and
> RA=00:01 are next to each other using usual comparisons?


I don't; usual comparisons won't work so I do two comparisons:

I am usually looking for stars within a convex field of view (FOV),
typically a frustum with a rectangular footprint, so I determine if and
where RA=0=360 crosses that footprint, and break the FOV into two pieces,
from 0<=RA<=loRA and hiRA<=RA<=360, so loRA becomes hira in one search and
hiRA become lora in the other.

There are only three cases:  zero, one or two crossings.  Zero crossings
means I can do everything in one SELECT; one crossing means either one of
the poles is in the FOV and I search RA=0 to 360; DEC=someDEC to +/-90, or
the FOV touches RA=0(=360) from one side or the other, which reduces to the
zero case; two crossings means the poles are not in the FOV and I can do
two searches as mentioned above, from 0 up to someLowRA and from 360 down
to someHighRA.

There are some edge cases but that is basically it.

I actually handle "two or more crossings" cases the same as two cases, even
though I don't think "more" can happen with a convex FOV footprint.  For
any edge (segment of the great circle between two vertices) of the FOV that
crosses RA=0, which is easily determined since I have the vertices in XYZ
coordinates, I insert a vertex in the edge at the crossing, and then
recurse with subsets of vertices split across RA=0.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] General R*Tree query

[Brian T. Carcich]

On Tue, Dec 17, 2013 at 3:51 PM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 12/18/2013 12:49 AM, Brian T. Carcich wrote:
>
>> [...]
>
> Points are fine. [...]
> Is it working now? How many more stars do you have data for?


Excellent, thanks for the info!

I forgot to mention that we do perform searches using magnitude.

Yes it is working now; I do the normal SQLite3 R*Tree INNER JOIN to get to
the index table (tyc2index) from the indexrtree table (tyc2indexrtree)
regions overlapping the user-supplied RA,DEC limits (hira = High RA limit;
lodec = Low DEC limit; etc), and then do another INNER JOIN ON the index
table start and end offsets with the offsets in the main catalog table
(tyc2catalog_uvs), so it all happens in one call.  The beauty is that all
the work is done up front when I load the data from the star catalog, and
then the SELECT does the rest.  Also, the approach should work for any
catalog that has RA,DEC and Magnitude, which almost all catalogs do.

I think the SELECT is in the Githup repo ... yeah, here it is:


SELECT tyc2catalog_uvs.offset ,tyc2catalog_uvs.x ,tyc2catalog_uvs.y
> ,tyc2catalog_uvs.z ,tyc2catalog_uvs.mag


> FROM tyc2indexrtree



INNER JOIN tyc2index
> ON tyc2indexrtree.offset=tyc2index.offset



INNER JOIN tyc2catalog_uvs
> ON tyc2index.catalogstart<=tyc2catalog_uvs.offset
>AND tyc2index.catalogend>tyc2catalog_uvs.offset
>AND tyc2catalog_uvs.mag   AND tyc2indexrtree.hira>?
>   AND tyc2indexrtree.lora   AND tyc2indexrtree.hidec>?
>   AND tyc2indexrtree.lodec ORDER BY tyc2catalog_uvs.mag asc;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] General R*Tree query

[Brian T. Carcich]

I'm working on an SQLite solution to get at star catalogs; they are usually
searched via Right Ascension (RA), Declination (DEC), and magnitude (mag).
 RA,DEC is a spherical coordinate system to specify a star position on-sky;
magnitude is related to star brightness.

What I have so far is here:

   https://github.com/drbitboy/Tycho2_SQLite_RTree


I started with the Tycho-2 star catalog.  It comprises 2.5 million stars in
a flat ASCII, fixed-width catalog file (actually two files but ignore that
for now), and an index file (map) of ~10k small RA-DEC regions, with an
average of ~250 stars in each region.  The regions do not overlap, and all
the stars in any one region are in contiguous lines in the catalog file.

The index file does not implement any grouping or sorting by magnitude.
 Each index region refers to

A) a contiguous region on-sky with defined by a min-max RA pair and a
min-max DEC pair.

B)  a contiguous range of the lines (stars) in the flat file that are
within that region.

So the data in the index file are a reasonable starting point for an R*Tree
in SQLite3.  I put the index file data into the virtual table using the RA
and DEC limits for each region as the two min-max pairs of columns in the
table, and the index table, referenced by the primary key of the virtual
table, contains the starting and ending+1 indices (offsets actually) of the
stars in the flat catalog file for each region.

So I use the R*Tree module to get a fast lookup into the index table,
returning index regions that overlap an input RA and DEC min-max pair, then
step through the catalog lines for each of those regions.

Here's my question:  is there any advantage to skipping the index step and
putting the star catalog data into the virtual table itself?  One advantage
is that I could include the magnitude in the rtree table.

The reason I ask is that rtree table uses min-max pairs, but each star is a
point so the min and max are equal for each star.  Would that break any
implicit R*Tree rules or negate any efficiencies?

Thanks,

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

Re: [sqlite] SQLite Provenance

[Gavin T Watt]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

I totally agree with you about about the server location being of little
interest, really, and that the having access to the source is much more
important. However, my customer wanted me to check this anyway,a nd you've
answered the mail.

BTW, do you know if the SQLite team runs any kind of static code analysis
on the source?


Gavin Watt, CISSP
Sr. Prin. Sys. Engr.
Information Assurance
Network Centric Systems (NCS)

714-446-3104 (office)
714-234-8869 (BB)
714-446-4136 (fax)
gw...@raytheon.com

120 S. St. College Blvd..
Mail Station: FU/679/H118
Brea, Ca.  92821, USA



From: Roger Binns <rog...@rogerbinns.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date: 07/06/2012 03:05 PM
Subject: Re: [sqlite] SQLite Provenance
Sent by: sqlite-users-boun...@sqlite.org



* PGP Signed by an unknown key

On 06/07/12 14:58, Gavin T Watt wrote:
> ... the provenance of SQLite for security reasons.

Where the server is is of little interest.  It would be good if the team
actually signed the release in some way then at least you would know it
was what they released.

What is of more importance is that you have access to the code (which
everyone does) and where the code came from.  Especially note the first
and third paragraphs here:

  http://www.sqlite.org/copyright.html

Roger

* Unknown Key
* 0x0DFBD904(L)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-BEGIN PGP SIGNATURE-
Version: PGP Desktop 10.2.0 (Build 2068)
Charset: utf-8

wsBVAwUBT/dlmo8W3Wkt3UsBAQiR/wgAgcCQ0mcJMWRP9G5aCUp1b5/cWAaoyHbI
2aNHKRdtwST9ugtt6lk2AnEjtIqjM4C9jMudCWsxh2qB+gVguJQhbPegLiVOGHI+
1axfwIiGOMqdgba2BW+uQVbWYHMfsm4u3wThS91S2BwZR4TWGRdbkjg5IxV72JGH
KTvuNLC5Dv6p3f1sOK7qSV7HVsQmFYjXGsWWb1U2MHuJH0rZ0KsVCa0mu6zFdRur
jddMFu8wWrNEOMSozsI+mWWn2k68mBj2CblbkIho9lScHJiGKlK5o75anmqw8xQj
4fsYmUsEjTqanaeRrU3CNKMuphseyW8cYnVrt1+BY67chMipKTm54w==
=P9aB
-END PGP SIGNATURE-___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Provenance

[Gavin T Watt]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Thanks for the information.

Not a Jingoistic quest, but we're using SQLite in a system for which there
is an interest in the provenance of SQLite for security reasons. With the
server ins Dalls, we golden!


Gavin Watt, CISSP
Sr. Prin. Sys. Engr.
Information Assurance
Network Centric Systems (NCS)

714-446-3104 (office)
714-234-8869 (BB)
714-446-4136 (fax)
gw...@raytheon.com

120 S. St. College Blvd..
Mail Station: FU/679/H118
Brea, Ca.  92821, USA



From: Roger Binns <rog...@rogerbinns.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Date: 07/06/2012 11:01 AM
Subject: Re: [sqlite] SQLite Provenance
Sent by: sqlite-users-boun...@sqlite.org



* PGP Signed by an unknown key

On 06/07/12 08:29, Gavin T Watt wrote:
> Can anyone tell me where (what country) the SQLite server(s) are
> located?

Go to http://centralops.net and enter the site of interest into domain
dossier.

You will see that the sqlite.org domain name is openly registered (not
hidden by a privacy/anonymity registrar) and that the IP address it is
hosted on belongs to Linode who obtained it as a block from ThePlanet
internet services.

If you tick traceroute at the top then you can deduce from the traceroute
(at the bottom) that particular IP address is in Dallas.

If this is some sort of jingoistic quest then all companies involved are
American.

Roger

* Unknown Key
* 0x0DFBD904(L)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-BEGIN PGP SIGNATURE-
Version: PGP Desktop 10.2.0 (Build 2068)
Charset: utf-8

wsBVAwUBT/dflI8W3Wkt3UsBAQimkQf+M4Fylk+jIEdYmU9qoz8pexgmeMFVgDBo
E22D6T91QrzfBp+8zTtuMCIVYe31Sv+H2E3rdfWuP+xLM82OqldSylv/eaG0uRXl
fRCISD63mvnJpBY63LS9r2tWbw5+1JCsDtLVzcwOTLrRoreBvXT5RXbZKe5g+j84
JZVtZZrjAMyiaw2XC3uyoq8I71HC5wCyYjYuWd+QwaqLGwndC9Nvtua1opXHzTC5
hviMgkhfbq2+q7ZUu1IiBCDPk255aG8tx83sxn2RiKyI6voBMFVJE1E1sv1vvykE
uL7wj4ybw2UDKkgcsBFmAaQDb228tGghbSXqB28npuVpTtugLPRkFg==
=G2iZ
-END PGP SIGNATURE-___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Provenance

[Gavin T Watt]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Can anyone tell me where (what country) the SQLite server(s) are located?


Gavin Watt, CISSP
Sr. Prin. Sys. Engr.
Information Assurance
Network Centric Systems (NCS)

714-446-3104 (office)
714-234-8869 (BB)
714-446-4136 (fax)
gw...@raytheon.com

120 S. St. College Blvd..
Mail Station: FU/679/H118
Brea, Ca.  92821, USA
-BEGIN PGP SIGNATURE-
Version: PGP Desktop 10.2.0 (Build 2068)
Charset: utf-8

wsBVAwUBT/cEaY8W3Wkt3UsBAQhbYAf/VNDpJBz42Q/1H5rUVQbsoRVJKPiP2qJV
cjqnCNj07g1/PgKkg/+8KXpq2ekuVcZmRa1Nbxvjqvqa3D9FeDK7/GJVhFZvE2Bn
b0FGJrgrg6qzRNYQL8+mjPw75eny/Ugl4LLncQU7UoVdnMhWD4BadCGks4+8kVBf
8eFO3nF2I+59hkCuP7uqDzB33jUz1bOlXTrNlV/CWbuiJ8/veYSToXR4flgW6npr
4ftIoV9VV94/W8tlJODbfvZ8aSCIbpLb9LITz3ketBfnM5R3WLBJrjAyBn1v55Pu
xb4jnJntm9P3inAZffB2qkfdgHL4NR5797IguF7KO70SsJ+AqBvSWg==
=R+P3
-END PGP SIGNATURE-___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple connections to in-memory database

[T Ü]

Thank you so much Pavel. I will try with the new version.




 From: Pavel Ivanov <paiva...@gmail.com>
To: T Ü <shocking_blue2...@yahoo.com> 
Cc: General Discussion of SQLite Database <sqlite-users@sqlite.org> 
Sent: Thursday, July 5, 2012 4:03 PM
Subject: Re: [sqlite] Multiple connections to in-memory database
 
So this feature shouldn't work for you. From my first message:

> But this possibility was
> introduced in SQLite 3.7.13. So your asp.net provider should be
> compiled with the latest version of SQLite, otherwise it won't work.


Pavel


On Thu, Jul 5, 2012 at 8:56 AM, T Ü <shocking_blue2...@yahoo.com> wrote:
> It returns 3.6.23.1
>
> 
> From: Pavel Ivanov <paiva...@gmail.com>
> To: T Ü <shocking_blue2...@yahoo.com>
> Cc: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Thursday, July 5, 2012 3:40 PM
>
> Subject: Re: [sqlite] Multiple connections to in-memory database
>
> On Thu, Jul 5, 2012 at 8:37 AM, T Ü <shocking_blue2...@yahoo.com> wrote:
>> By trying I found out that SQLiteConnection("Data
>> Source=:memory:;cache=shared"); worked.
>> In a single aspx.page at cs code, first I open an in-memory database
>> connection
>>        SQLiteConnection conn = new SQLiteConnection ( "Data
>> Source=:memory:;cache=shared" );
>>        conn.Open();
>> than create table and insert some data
>> then without closing that connection open another connection in the
>> sameway,
>> but when I try to select the rows of the table that I created in the
>> previous table, I get no such table error.
>> What am I doing wrong???
>
> Please execute "SELECT sqlite_version()" in your cs code and tell us
> the result of it.
>
> Pavel
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple connections to in-memory database

[T Ü]

It returns 3.6.23.1




 From: Pavel Ivanov <paiva...@gmail.com>
To: T Ü <shocking_blue2...@yahoo.com> 
Cc: General Discussion of SQLite Database <sqlite-users@sqlite.org> 
Sent: Thursday, July 5, 2012 3:40 PM
Subject: Re: [sqlite] Multiple connections to in-memory database
 
On Thu, Jul 5, 2012 at 8:37 AM, T Ü <shocking_blue2...@yahoo.com> wrote:
> By trying I found out that SQLiteConnection("Data
> Source=:memory:;cache=shared"); worked.
> In a single aspx.page at cs code, first I open an in-memory database
> connection
>         SQLiteConnection conn = new SQLiteConnection ( "Data
> Source=:memory:;cache=shared" );
>         conn.Open();
> than create table and insert some data
> then without closing that connection open another connection in the sameway,
> but when I try to select the rows of the table that I created in the
> previous table, I get no such table error.
> What am I doing wrong???

Please execute "SELECT sqlite_version()" in your cs code and tell us
the result of it.

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

Re: [sqlite] Multiple connections to in-memory database

[T Ü]

By trying I found out that SQLiteConnection("Data 
Source=:memory:;cache=shared"); worked.
In a single aspx.page at cs code, first I open an in-memory database connection 

        SQLiteConnection conn = new SQLiteConnection ( "Data 
Source=:memory:;cache=shared" );
    conn.Open();

than create table and insert some data
then without closing that connection open another connection in the sameway, 
but when I try to select the rows of the table that I created in the previous 
table, I get no such table error.
What am I doing wrong???

 

 From: Pavel Ivanov <paiva...@gmail.com>
To: T Ü <shocking_blue2...@yahoo.com>; General Discussion of SQLite Database 
<sqlite-users@sqlite.org> 
Sent: Thursday, July 5, 2012 3:21 PM
Subject: Re: [sqlite] Multiple connections to in-memory database
 
On Thu, Jul 5, 2012 at 7:46 AM, T Ü <shocking_blue2...@yahoo.com> wrote:
> I have an asp.net application.
> I open a sqlite in-memory connection with SQLiteConnection conn = new 
> SQLiteConnection ( "Data Source=:memory:" ); command.
> I read that by using cache=shared parameter, I can make that in-memory 
> database reachable from other connections.
>
> 1.What is the way of applying cache=shared parameter in this type of 
> connection? SQLiteConnection conn = new SQLiteConnection ( "Data 
> Source=:memory:?cache=shared" ); is not working?

I think you should write SQLiteConnection("Data
Source=file::memory:?cache=shared"). But this possibility was
introduced in SQLite 3.7.13. So your asp.net provider should be
compiled with the latest version of SQLite, otherwise it won't work.

> 2.What is the way of creating a new connection for accessing the previously 
> opened in-memory database?

You should create new connection the same way as previously opened
one, i.e. SQLiteConnection("Data Source=file::memory:?cache=shared").


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

[sqlite] Multiple connections to in-memory database

[T Ü]

I have an asp.net application.
I open a sqlite in-memory connection with SQLiteConnection conn = new 
SQLiteConnection ( "Data Source=:memory:" ); command.
I read that by using cache=shared parameter, I can make that in-memory database 
reachable from other connections.

1.What is the way of applying cache=shared parameter in this type of 
connection? SQLiteConnection conn = new SQLiteConnection ( "Data 
Source=:memory:?cache=shared" ); is not working?

2.What is the way of creating a new connection for accessing the previously 
opened in-memory database?

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

[sqlite] How Execute Joint Queries In Sqlite?

[Arunkumar T]

I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
Supported. What Should I Do To Do This

Can You Help Me?

Regards

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

Re: [sqlite] ODBC Driver

[christian.wer...@t-online.de]

> I do not seem to be able to parse a multiple insert statement through 
> the odbc drive using SQLExecDirect(...) I have tried with BEGIN, COMMIT 
> TRANSACTION does anyone have any ideas?

The SQLite ODBC driver allows only one SQL statement per SQLExecDirect()
and SQLPrepare(). When using transactions the right sequence of
calls is SQLSetConnectAttr(...SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF),
SQLExecDirect() and finally SQLEndTran() to commit or rollback.
There's no need to send BEGIN/COMMIT statements through SQLExecDirect()

Hope that helps,
Christian



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

Re: [sqlite] Deadlock with two local instances

[Chris T]

Thanks!  I ran the example code and it seems like every UPDATE fails
with errors like the following:

SqlStep Timeout on handle: 8 (rc = 6)
SqlStep tries on handle 8: 200
BeginTrans Timeout/Error on handle:  8, Errorcode = 6
Write Thread: DB is busy! tries = 142 handle = 8

Looking at the database contents it looks like none of the updates
were successful at all (though I didn't look extremely carefully).

Are these errors normal?

On Tue, Oct 27, 2009 at 12:55 AM, Marcus Grimm <mgr...@medcom-online.de> wrote:
>> Another odd thing is that when I call sqlite3_reset on the prepared
>> statement, it also returns SQLITE_BUSY.  Should I only reset the
>> statement when it has been executed successfully?
>
> one possible approach when getting SQLITE_BUSY is to
> retry the sqlite3_step call until it finally gets thru.
>
> note that sqlite3_reset just returns the same error
> code as the previous sqlite3_step call.
>
> attachments don't work on the list, you will need
> find another way to provide your example code.
>
> you may also take a look at http://www.sqlite.org/cvstrac/wiki?p=SampleCode
> for the busy handling.
>
> hth
>
> Marcus Grimm
>
>>
>> On Mon, Oct 26, 2009 at 2:40 PM, Chris T <citrus...@gmail.com> wrote:
>>> I'm new to sqlite (and sql in general, actually) and came across
>>> something puzzling.
>>>
>>> I wrote a test program statically linked with the amalgamated sqlite
>>> code.  When I run a single instance, everything is fine.  When I start
>>> a second instance in the same directory they both deadlock.  Every
>>> call to sqlite3_step returns SQLITE_BUSY.
>>>
>>> The source code to my test program is attached.  It was written in
>>> Visual Studio, so feel free to remove the reference to windows.h and
>>> change the calls to Sleep( ) if you don't use Windows.
>>>
>> ___
>> 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] Deadlock with two local instances

[Chris T]

Another odd thing is that when I call sqlite3_reset on the prepared
statement, it also returns SQLITE_BUSY.  Should I only reset the
statement when it has been executed successfully?

On Mon, Oct 26, 2009 at 2:40 PM, Chris T <citrus...@gmail.com> wrote:
> I'm new to sqlite (and sql in general, actually) and came across
> something puzzling.
>
> I wrote a test program statically linked with the amalgamated sqlite
> code.  When I run a single instance, everything is fine.  When I start
> a second instance in the same directory they both deadlock.  Every
> call to sqlite3_step returns SQLITE_BUSY.
>
> The source code to my test program is attached.  It was written in
> Visual Studio, so feel free to remove the reference to windows.h and
> change the calls to Sleep( ) if you don't use Windows.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Deadlock with two local instances

[Chris T]

I'm new to sqlite (and sql in general, actually) and came across
something puzzling.

I wrote a test program statically linked with the amalgamated sqlite
code.  When I run a single instance, everything is fine.  When I start
a second instance in the same directory they both deadlock.  Every
call to sqlite3_step returns SQLITE_BUSY.

The source code to my test program is attached.  It was written in
Visual Studio, so feel free to remove the reference to windows.h and
change the calls to Sleep( ) if you don't use Windows.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime comparison with custom format

[t-master]

Igor Tandetnik wrote:
> 
> t-master <t-zimmerm...@onlinehome.de> wrote:
>> I have string in a table representing a DateTime.
>> The format is 21.08.2009 00:25:00
> 
> I recommend you change the format. Yours is custom-designed to make your 
> life miserable.
> 
>> And I would like to compare it to "now"
> 
> select case when
> substr(T, 7, 4)||'-'||substr(T, 4, 2)||'-'||substr(T,1,2)||substr(T, 
> 11) > datetime('now')
> then 'future' else 'past' end
> from (select '21.08.2009 00:25:00' as T);
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

Hi

the problem is, this db is created by another program and I don't have the
access to change the format (btw it's the standard datetime format in
germany, not custom-designed :-P)

-- 
View this message in context: 
http://www.nabble.com/DateTime-comparison-with-custom-format-tp25085040p25088287.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] DateTime comparison with custom format

[t-master]

Hi
I have string in a table representing a DateTime.
The format is 21.08.2009 00:25:00
And I would like to compare it to "now"
How can I do this?
-- 
View this message in context: 
http://www.nabble.com/DateTime-comparison-with-custom-format-tp25085040p25085040.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

Re: [sqlite] SQLite Import Tool

[Keith T]

has any one expressed an interest to help out? it does not seem to be all that 
difficult, notwithstanding your mention of time-out issues.

please let me know a bit ore detail about the db you are exporting, and we can 
see if it is a project we can do quickly for you.
thanks-
Keith T
the SLK Groups


On Wednesday 12 November 2008 11:30:48 Baskaran Selvaraj wrote:
> Thanks.  SQLite ODBC driver takes hours for data transfer and most of the
> time it is not success.
>
> > Date: Wed, 12 Nov 2008 12:56:19 -0200> From: [EMAIL PROTECTED]>
> > To: sqlite-users@sqlite.org> Subject: Re: [sqlite] SQLite Import Tool> >
> > Hi> > You can use SQLite ODBC Driver and SQL Management Studio Data
> > Transfer> Wizard to perform this action.> > On Wed, Nov 12, 2008 at 12:45
> > PM, Baskaran Selvaraj <> [EMAIL PROTECTED]> wrote:> > >> >> > Hi
> > All,> > This is Baskaran and I am looking for a vendor tool to automate
> > the import> > process.> > We have an application which is written to use
> > SQLite database. Right now,> > I import the data> > into SQLite database
> > from SQL Server 2005 manually. Looking for a vendor> > software, which
> > can used> > to automate the process of importing the data from SQL Server
> > 2005 to> > SQLite database> > on a daily basis> > .> > Thanks> > Baskaran
> > Selvaraj, DBA> >> >
> > _> > See
> > how Windows(R) connects the people, information, and fun that are part
> > of> > your life> > http://clk.atdmt.com/MRT/go/119463819/direct/01/> >
> > ___> > 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
>
> _
> See how Windows® connects the people, information, and fun that are part of
> your life http://clk.atdmt.com/MRT/go/119463819/direct/01/
> ___
> 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] Value between changes

[Bjørn T . Nøstdahl]

> Can you give an example of the output?  For example, do you want to
> know the difference between A0001 and A0002 or between to records with
> the same code?

20080314100030 A0001 (9min 30sec to next status change)
20080314101000 A0002 (7min to next status change)
20080314101700 A (3min to next status change)
20080314102000 A0002 (1min to next status change)
20080314102100 A

Expected output: (The total time within that status)
A 300 (3min)
A0001 930 (9min 30sec)
A0002 800 (7min + 1min)

One problem is sadly that the date/time is stored as text, and this will 
make the calculations even more difficult. 

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

[sqlite] flexible tables

[r t]

Dear SQLite list,

Is there a way to automatically create tables based on
imported text files of unknown field count and unknown
data types?  While I suspect this can be managed with
a shell script interacting with SQLite, perhaps there
is a more direct method?

Zotlud


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 

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

Re: [sqlite] Re: Error in SQLite's CSV output

[T]

I think I've discovered yet another bug in the CSV output using the  
sqlite3 command line tool.


Here's a sample of the new bug:

.mode csv
select 'a=1,234', 'b=5';

gives:
a=1,234,b=5

but should give:
"a=1,234",b=5


Since no replies, I'll assume this is a bug. I've reported it as:
http://www.sqlite.org/cvstrac/tktview?tn=2850

CSV seems to have quite a history of bugs in SQLite :-/

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Casting bug

[T]

Hi Mike,


well, first of all you must have a typo, since the sql you show will
return 14170, not 0.69 or anything like it.


Yes, typo, sorry. The short version returns 14169 but should give  
14170, as per my correction a minute ago.



however, one thing springs out:

Total * 100 + 100 is wrong IMHO, unless you are looking for CEILING  
functionallity.
"round" would need : total * 100 +50 (which rounds to nearest  
integer at

two decimal points, not to the nearest LARGER integer,
which is what your sample does)


I shouldn't have complicated my question by using the word "round".  
It's not the rounding that I'm trying to do. The input values are  
already rounded to two decimal places (cents of the dollar). I am  
trying to output fixed decimal places. So 141.7 should output as  
141.70, and 23 should output as 23.00 etc.


I think I actually found a fix to the apparent bug in cast, by  
rounding the product to zero decimal places, even though the result  
should be the same:



select cast( 141.70 * 100 as integer)


gives the incorrect 14169

but this workaround:

select cast( round(141.70 * 100, 0) as integer)

gives the correct: 14170

Now the question is, does this fix the problem, or only for some  
numbers?


Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Casting bug

[T]

oops, sorry, slight correction:


When I try:

select cast( 141.70 * 100 as integer)

I get 14169, but should get 14170


Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Casting bug

[T]

When I try:

select cast( 141.70 * 100 as integer)

I get 0.69, but should get 0.70

What's the problem? Seems like a bug.

I tried some other numbers in place of 141.70, and they worked OK,  
though I imagine there are others that have the bug that I just  
haven't tried.


The above is the isolated buggy part of a formula I use to round of  
real amounts to two fixed decimal places:


select
substr(
'  $' || cast( Total as integer ) || '.'
|| substr( cast( Total * 100 + 100 as integer ), -2, 2 )
, -10, 10
)
from ( select 141.70 as Total )
;

which gives $141.69 but should give $141.70

Is there a more reliable way, using only SQLite?

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to check if the table has some specific values

[T]

Hi Joanne,

I have been working on MSSQL server and SQLite is new to me. I  
usually did the following in MSQL server to check of the specific  
row is existed in the table and have different action depending on  
the result of the check. For example:

Create table versionTable (dbVersion varchar(20)
  insert into versionTable values('6, 0, 0, 1');
Now the table is created and it has one row(6, 0, 0, 1).
I usually do the following to check the content of the table

If NOT EXISTS ( select 1 from versionTable where dbVersion = '6, 0,  
0, 1')

 insert into versionTable values('6, 0, 0, 2');
ELSE
update versionTable set dbVersion = '6, 0, 0, 2';

I really don't know how to convert these syntax from MSSQL server to  
SQLite.


I suggest that you avoid thinking about procedural steps in an SQL  
database. SQL is based in the concept of sets so you apply a single  
action to a whole set or subset. Rather than check if something exists  
and then choose what to do about it, I think a better approach is to  
specify the subset that you want to affect, and run the action on that  
subset. If the subset is empty, then nothing will happen. This also  
has the advantage of fewer connections to the database, so potentially  
twice as fast.


In your particular example, you seem to just want one row always in  
the table, and insert if it doesn't exist, and update if it does  
exist. SQLite has a built in variant of insert that will handle this,  
called "insert or replace", which will replace if it would violate a  
constraint such as a primary key. So, a rewrite would be something  
like this:


-- Set up:
create table versionTable (ID integer primary key, dbVersion text);
insert into versionTable values(1, '6, 0, 0, 1');

-- Insert or replace:
insert or replace into versionTable values(1, '6, 0, 0, 2' );

-- Check:
select * from versionTable;

which gives:

ID  dbVersion
--  --
1   6, 0, 0, 2

Does this suit your purpose?

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Date functions

[T]

Hi Andreas,

I like to save a date for each row in my database. Later I would  
select the rows with a query:


SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'

Is there a date data type in sqlite? I've not found it in the docs.



Store dates in this format: -MM-DD such as 2007-12-02

Use dates like this:

select *
from Store_Information
where Date between '1999-01-06' and '1999-01-06'

Some documentation is here:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Converting date from d/m/yy format

[T]

Hi Gerry,

I would suggest either [scan] or [regexp] , with the former probably  
being easier.


I'd love to use RegExp, but SQLite doesn't include it in its standard  
functions (though I wish it did for so many reasons). I'm not familiar  
with scan.


Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Converting date from d/m/yy format

[T]

Hi John,

How can I convert dates from the format d/m/yy to SQL style YYY-MM- 
DD?





The data is from a bank, so I have no control over its production.



I couldn't find any suitable built in SQLite functions, which all  
seem  to operate in the other direction.




If you transform the date into the internal Sqlite format on data  
aquisition.


See above re "no control over its production". I have to convert the  
dates. I can and want to do it in SQLite, since I won't have to  
introduce another architecture.


Then you can use the internal Sqlite date functions to present the  
date in the format of your choice.




See above re "other direction", which is not what I need.

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Converting date from d/m/yy format

[T]

Hi all,

How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD?

I have some imported data that includes a date column in the format d/ 
m/yy, where:


d  = day as 1 or two digits
m  = month as 1 or two digits
yy = year as two digits

eg:

2/11/07 = today
2/8/68  = 2nd of August, 1968

How can I convert this in SQLite to -MM-DD?

The data is from a bank, so I have no control over its production.

I couldn't find any suitable built in SQLite functions, which all seem  
to operate in the other direction.


The best I've come up with so far is:

create table Raw( Date );
insert into Raw( Date ) values ( '2/11/07' );
insert into Raw( Date ) values ( '2/8/68' );
select
case /* Prefix for year = 20 if year starts 0, 1 or 2, else 19 
*/
when cast( substr( Date, -2, 1 ) as integer ) < 3
then '20'
else '19'
end
|| substr( Date, -2, 2 ) /* Year = last two characters */
|| '-' ||
case /* Prefix month with 0 if short */
when substr( Date, -5, 1 ) = '/'
then '0'
else ''
end
||
case /* Month = from after / to 4th last character */
when substr( Date, 2, 1) = '/'
then substr( Date, 3, length( Date ) - 5 )
else substr( Date, 4, length( Date ) - 6 )
end
|| '-' ||
case /* Day = from 1st to character before first / */
when substr( Date, 2, 1 ) = '/'
then '0' || substr( Date, 1, 1 ) /* Prefix with 0 if short */
else substr( Date, 1, 2 )
end
as Date
from Raw
;

which correctly gives:

2007-11-02
1968-08-02

But is there a more robust, built in method?

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite 3.5.* source code location?

[T]

Hi Bharath,

Could anybody tell where can I get the latest sqlite3 source code  
for Mac OS 10.4?


I'm no source code expert, but source code is generally by nature  
platform agnostic. So there is generally just one source code, which  
you can compile to suit whatever platform.


So, you just need the first source code link at:
http://www.sqlite.org/download.html
after the heading "Source Code".

Or, directly, this link:
http://www.sqlite.org/sqlite-3.5.1.tar.gz

I have only compiled up to version 3.4.2 on Mac OS X. And I've had to  
alter the config to allow opening database files on AppleShare volumes  
(which for some reason is still not enabled by default).


Tom
T


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Formatting numbers

[T]

Hi All,

What's the best way to format a number with a fixed number of decimal  
places?


For instance, if I have an Amount that I want to format as dollars  
and cents, I'm currently using:


begin immediate
;
create temporary table Invoice( Amount real )
;
insert into Invoice values( 123.4 )
;
select '$' || cast( Amount as integer ) || '.'
|| substr( cast( Amount * 100 + 100 as integer ), -2, 2 )
from Invoice
;
rollback
;

which gives:

$123.40

Is there a better way? I can't see any number formatting function in  
SQLite's repertoire.


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sqlite insertion performance

[Kefah T. Issa]

Dear Kees and Richard,

Much appreciated.


I tried the ordered-urls-insert the results were better, but it is still 
taking progressively longer time as the number of records increases.

A fundamental question to be asked here :

Shouldn't the time complexity (Big-O) of the insert operation be constant?

I even did a third test where the integer primary key is not auto increment; 
the same problem is observed.

Time complexity is clearly constant when there are no indexes at all (except 
for the implicit auto-increment integer primary key)

But otherwise, time taken increases incrementally (if not exponentially) with 
the number of existing records.

Acknowledging my ignorance on the subject; I really don't see a reason why 
this is happening except being a potential bug or performance improvement 
opportunity.

Regards,
- Kefah.



On Saturday 15 September 2007 12:51:02 Kees Nuyt wrote:
> Hi Kefah,
>
> On Sat, 15 Sep 2007 04:43:46 +0300, you wrote:
> >Thanks Kees,
> >
> >In fact using integer instead of string gives very similar results.
> >
> >Dropping the unique index constraint and then creating it again when
> > needed sounds interesting, as insertion without the unique index is
> > satisfactory and constact over time.
>
> Thanks for the feedback.
>
> >I tried this, but got a trivial question :
> >When creating the unique index, sqlite gives me :
> >SQL error: indexed columns are not unique
> >
> >What should be done here?
>
> Apparently the data in the text column is not unique.
> That is the disadvantage of building the index after the
> insertions: the database can't exercise the contraints on your
> data, so you would have to do that yourself, for example by a
> sort --unique step. My second suggestion for speeding things was
> sorting the input data, so now you have two reasons for a sort.
>
> Such a data cleaning step will take considerable time, so time
> gained in inserting may be lost again in preprocessing.
> It might be better to use the database constraints, and live
> with the slow insertions. Your benchmarks will tell you what's
> best.
>
> >Thanks again,
> >- Kefah.
>
> Good luck.
>
> >On Saturday 15 September 2007 00:25:03 Kees Nuyt wrote:
> >> On Fri, 14 Sep 2007 23:20:53 +0300, you wrote:
> >> >Dear All,
> >> >
> >> >I have been struggling with the performance of insertion in sqlite.
> >> >
> >> >Here we have a very simple case :
> >> >
> >> >A table with an integer autoincrement primary key and a text field that
> >> > is unique.
> >> >
> >> >CREATE TABLE my (id PRIMARY KEY, url);
> >> >
> >> >CREATE UNIQUE INDEX myurl ON my(url);
> >> >
> >> >
> >> >My application requires inserting up to 10 million records in batches
> >> > of 20 thousand records.
> >> >
> >> >I use :
> >> >A. PRAGMA SYNCHRONOUS = OFF
> >> >B. Prepared Statement
> >> >C. setAutoCommit(false), then to true at the end of the batch.
> >> >
> >> >Using the above, the insertion starts off at a good speed, then
> >> > drastically slows down as more records are inserted.
> >> >
> >> >It goes like this :
> >> >
> >> >The first four inserstions (first 4*20K -> 60K records)
> >> >0took786
> >> >1took944
> >> >2took1001
> >> >3took1303
> >> >
> >> >After the first 1 Million records
> >> >50took2560
> >> >51took1921
> >> >55took1900
> >> >53took3990
> >> >
> >> >After the 2nd million records
> >> >2took22393
> >> >3took16789
> >> >4took29419
> >> >5took13993
> >> >
> >> >By the end of the the 3rd million records the insertion time goes up to
> >> > 30 seconds per 20K records.
> >> >
> >> >My app is running from a java code and I am using SqliteJDBC 0.37 (the
> >> > latest) on Sun JDK 1.6.0_02 on CentOS 5.0 4GB Ram, two duo core cpu
> >> > server with raid10 sata-II harddisk.
> >> >
> >> >
> >> >I know I might be stretching sqlite far beyond its limits, I just want
> >> > to verify that there is nothing more that can be done to make a case
> >> > for sqlite in this scenario. I am not sure whats the best next thing
> >> > to do either.
> >> >
> >> >Your feedback and input will be highly appreciated,
> >> >
> >> >- Kefah.
> >>
> >> Most probably the UNIQUE INDEX on the TEXT column is the
> >> culprit.
> >>
> >> My first try would be to create and fill the table first, and
> >> create the UNIQUE INDEX on the TEXT column afterwards.
> >>
> >> The second suggestion would be to INSERT the rows in sorted
> >> order, the sort key being the TEXT column.



-- 
Kefah T. Issa
Manager

>/. freesoft technologies llc
freesoft technologies, LLC.
Cell : +962 777 80 90 50
Office : +962 6 55 23 967
Fax : +962 6 55 61 967
Jabber IM (XMPP) : [EMAIL PROTECTED] 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sqlite insertion performance

[Kefah T. Issa]

Thanks Kees,

In fact using integer instead of string gives very similar results.

Dropping the unique index constraint and then creating it again when needed 
sounds interesting, as insertion without the unique index is satisfactory and 
constact over time.

I tried this, but got a trivial question : 
When creating the unique index, sqlite gives me : 
SQL error: indexed columns are not unique

What should be done here?

Thanks again,
- Kefah.


On Saturday 15 September 2007 00:25:03 Kees Nuyt wrote:
> On Fri, 14 Sep 2007 23:20:53 +0300, you wrote:
> >Dear All,
> >
> >I have been struggling with the performance of insertion in sqlite.
> >
> >Here we have a very simple case :
> >
> >A table with an integer autoincrement primary key and a text field that is
> >unique.
> >
> >CREATE TABLE my (id PRIMARY KEY, url);
> >
> >CREATE UNIQUE INDEX myurl ON my(url);
> >
> >
> >My application requires inserting up to 10 million records in batches of
> > 20 thousand records.
> >
> >I use :
> >A. PRAGMA SYNCHRONOUS = OFF
> >B. Prepared Statement
> >C. setAutoCommit(false), then to true at the end of the batch.
> >
> >Using the above, the insertion starts off at a good speed, then
> > drastically slows down as more records are inserted.
> >
> >It goes like this :
> >
> >The first four inserstions (first 4*20K -> 60K records)
> >0took786
> >1took944
> >2took1001
> >3took1303
> >
> >After the first 1 Million records
> >50took2560
> >51took1921
> >55took1900
> >53took3990
> >
> >After the 2nd million records
> >2took22393
> >3took16789
> >4took29419
> >5took13993
> >
> >By the end of the the 3rd million records the insertion time goes up to 30
> >seconds per 20K records.
> >
> >My app is running from a java code and I am using SqliteJDBC 0.37 (the
> > latest) on Sun JDK 1.6.0_02 on CentOS 5.0 4GB Ram, two duo core cpu
> > server with raid10 sata-II harddisk.
> >
> >
> >I know I might be stretching sqlite far beyond its limits, I just want to
> >verify that there is nothing more that can be done to make a case for
> > sqlite in this scenario. I am not sure whats the best next thing to do
> > either.
> >
> >Your feedback and input will be highly appreciated,
> >
> >- Kefah.
>
> Most probably the UNIQUE INDEX on the TEXT column is the
> culprit.
>
> My first try would be to create and fill the table first, and
> create the UNIQUE INDEX on the TEXT column afterwards.
>
> The second suggestion would be to INSERT the rows in sorted
> order, the sort key being the TEXT column.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Sqlite insertion performance

[Kefah T. Issa]

Dear All,

I have been struggling with the performance of insertion in sqlite.

Here we have a very simple case : 

A table with an integer autoincrement primary key and a text field that is 
unique.

CREATE TABLE my (id PRIMARY KEY, url);

CREATE UNIQUE INDEX myurl ON my(url);


My application requires inserting up to 10 million records in batches of 20 
thousand records.

I use : 
A. PRAGMA SYNCHRONOUS = OFF
B. Prepared Statement
C. setAutoCommit(false), then to true at the end of the batch.

Using the above, the insertion starts off at a good speed, then drastically 
slows down as more records are inserted.

It goes like this : 

The first four inserstions (first 4*20K -> 60K records)
0took786
1took944
2took1001
3took1303

After the first 1 Million records 
50took2560
51took1921
55took1900
53took3990

After the 2nd million records
2took22393
3took16789
4took29419
5took13993

By the end of the the 3rd million records the insertion time goes up to 30 
seconds per 20K records.

My app is running from a java code and I am using SqliteJDBC 0.37 (the latest) 
on Sun JDK 1.6.0_02 on CentOS 5.0 4GB Ram, two duo core cpu server with 
raid10 sata-II harddisk.


I know I might be stretching sqlite far beyond its limits, I just want to 
verify that there is nothing more that can be done to make a case for sqlite 
in this scenario. I am not sure whats the best next thing to do either.

Your feedback and input will be highly appreciated,

- Kefah.





P.S. here is a snippit of the java code :
Class.forName("org.sqlite.JDBC");
Connection connection = 
DriverManager.getConnection("jdbc:sqlite:./my.db");

long total_time=0;
int iterations = 50;
int records_per_iteration = 2;

for(int i=0; i<iterations; i++) {

long time = System.currentTimeMillis();
connection.createStatement().execute("PRAGMA synchronous = 
OFF;");
connection.setAutoCommit(false);
PreparedStatement insert = connection.prepareStatement("insert 
or ignore into my(url) values(?);");
for(int j=0; j<records_per_iteration; j++) {
Random random = new Random((long)
(i*j*System.currentTimeMillis()));
String value = Double.toHexString(random.nextDouble()) 
+ Long.toHexString(time);
insert.setString(1, value);
insert.executeUpdate();
}

connection.commit();
insert.close();
connection.setAutoCommit(true);
connection.createStatement().execute("PRAGMA synchronous = 
FULL;");
long iteration_time = (System.currentTimeMillis()-time);
System.out.println(i+ "\t took \t " + iteration_time);
total_time+=iteration_time;

}

System.out.println("Hello : Average : " + 
(total_time*1.0/iterations));








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] why a VIEW doesn't contain a ROWID

[T]

This I know, but the thing is, I want the ROWID in VIEW to be
sequential even after a SELECT with some condition has been executed,
ie., from 1 to n. Just like in normal table. In your case it is not
like that.


Ah, OK, I asked a similar question a week or two ago, and had to come  
up with my own solution (two actually), in the absence of others.  
Look for the thread "Enumerating rows in a view" in this mail list  
(or archive).


By the way, what I mean to say is, why don't we have default ROWID  
in VIEW like as in normal TABLE.


In a few situations (such as my previous thread) I'd like to have an  
enumerated sequence in a view, but I would not want this to replace  
the rowid that we carry over from a table. We need the rowid from a  
table so we can match rows (eg when the user changes a value in view  
data which we need to redirect back to the corresponding table data).


Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Enumerating rows in a view

[T]

Hi Darren,

If that works, then try using a subquery in the view definition  
instead.


 create view Enumerated
 as
 select rowid as Sequence, Name from (
   select Name from Planets order by Name
 )

Sort of like that.


Thanks for the suggestion, but, unless I'm missing something, it  
doesn't work. Since rowid doesn't exist in the inner query, the outer  
query assigns Sequence with a null value, so the whole result is:


SequenceName
--  --
.   Earth
.   Jupiter
.   Mars
.   Mercury
.   Venus

(. = null)

If we instead change it to include rowid in the subquery:

create view Enumerated
as
select rowid as Sequence, Name from (
   select rowid, Name from Planets order by Name
)
;

we get the original rowids (but we instead want a numerical sequence  
1, 2, 3, 4, 5):


SequenceName
--  --
3   Earth
5   Jupiter
4   Mars
1   Mercury
2   Venus

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sparse matrix

[T]

Hi All,

Simon answered:

Here's a less gruesome version - no cases. I've given no thought to  
performance comparisons.


Thanks for the two great solutions you posted.


Upon further investigation, those solutions assume that we want all  
like occurrences together, effectively sorting records in way that  
overrides the original view or table.


The best solutions I've come up with so far require first enumerating  
the rows in the view (or table). In my separate email thread  
"Enumerating rows in a view", I mention two methods:


1. Deconstructing the "order by", replacing it with a series of  
inequality operators. Then counting how many other records are less  
than each current record.


or:

2. Creating a temporary table, filling with the rows from the view,  
using the automatic rowid as the enumeration.


So, for the example "Timetable" in this thread, where I said:


and a view that sorts the data is:

create view "Timetable Sorted"
as
select rowid, Day, Subject, Room, Teacher, Period
from Timetable
order by Day, Period
;


enumerating the rows via each method would be:

1. Deconstructing order into inequalities:

create view Enumerated
as
select count(*) as Sequence, *
from Timetable as Current
left join Timetable as Others
where Current.Day > Others.Day or ( Current.Day = Others.Day and  
Current.Period >= Others.Period )

group by Current.Day, Current.Period
;

or:

2. Creating a temporary table:

create temporary table Enumerated1
as
select * from "Timetable Sorted"
;
create temporary view Enumerated
as
select rowid as Sequence, * from Enumerated1
;

Note, I've also used a temporary view just to provide a Sequence  
column containing the enumeration. This gives the same column name as  
method 1, so the actual Sparsing (below) can use the same syntax (ie  
Sequence instead of rowid).


Each method gives the sorted view/table Enumerated as:

SequenceDay Period  Teacher RoomSubject
--  --  --  --  --  --
1   Monday  1   Ng  A1  English
2   Monday  2   Peters  A2  Maths
3   Monday  3   Peters  A2  Computing
4   Monday  4   KentH1  Sport
5   Tuesday 1   Peters  A2  Maths
6   Tuesday 2   Ng  A1  History
7   Tuesday 3   Ng  A1  English
8   Tuesday 4   Ng  A1  History
9   Wednesday   1   Peters  A2  Maths
10  Wednesday   2   KentH1  Sport
11  Wednesday   3   Who S2  Science
12  Wednesday   4   Smith   S2  Science

Now, on to replacing repeated values with null (ie showing a "sparse  
matrix") and counting the repetitions. Now that I have enumerated  
sorted rows, I can use the following method. I compare each Current  
value with the Previous value (ie the value that is enumerated as one  
less than the Current value). If it's the same as the previous, then  
it's a repetition which I replace with null. If it's different to the  
previous, then I show the value and the count of the same values from  
the current to the next change, or the bottom of the table.


For the Day column, for example, I calculate the Day (Current.Day or  
null) and DayCount (count until the next change, or null) like this:


select
  case
  when Previous.Day is null or Current.Day != Previous.Day
  then Current.Day
  else null
  end
   as Day,
  case
  when Previous.Day is null or Current.Day != Previous.Day
  then
 coalesce(
(
   select Sequence from Enumerated as Others
   where Current.Day != Others.Day and Others.Sequence >=  
Current.Sequence limit 1

),
Bottom
 ) - Current.Sequence
  else null
  end
   as DayCount
from Enumerated as Current
	left join Enumerated as Previous on Current.Sequence - 1 =  
Previous.Sequence

left join ( select max( Sequence ) + 1 as Bottom from Enumerated )
;

which gives the desired:

Day DayCount
--  --
Monday  4
.   .
.   .
.   .
Tuesday 4
.   .
.   .
.   .
Wednesday   4
.   .
.   .
.   .

(I've used . to show nulls)

To build the complete matrix, ie for all columns, I duplicated the  
expressions above for Day and DayCount to make Room and RoomCount  
(replacing Day with Room), Subject and SubjectCount etc. The result  
looks like the desired:


Day DC   Room  RC   Subject SC   Teacher TC   Period
--  ---    ---  --  ---  --  ---  --
Monday  4A11English 1Ng  11
.   .A22Maths   1

Re: [sqlite] Enumerating rows in a view

[T]

Hi All,

Again following up:


When I create a table, SQLite enumerates the rows in the rowid column.

When I create a view, is there any way to enumerate the output rows?


Another method would I've developed/discovered is to create a  
temporary table, fill it with the data from the view, then use the  
automatically created rowid column as the enumeration. This works, but  
is not ideal since the creation of a temporary table can't be included  
in a view itself.


So, to solve the previous example:

create view "Planets Sorted" as select Name from Planets order by  
Name;


we could do something like:

begin immediate
;
create temporary table Enumerated
as select * from "Planets Sorted"
;
select rowid as Sequence, * from Enumerated
;
commit
;

which gives the desired:


1 Earth
2 Jupiter
3 Mars
4 Mercury
5 Venus


This "create temporary table" method is the same syntax for any view,  
unlike the "deconstruct the order into comparisons" method that I  
posted before which requires adding custom comparison operators. But  
the temporary table can't be included in a view, whereas the  
deconstruct method can. So neither is ideal but the best I've got so  
far.


Any better alternatives welcome :-)

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Enumerating rows in a view

[T]

When I create a table, SQLite enumerates the rows in the rowid column.

When I create a view, is there any way to enumerate the output rows?

For example, say I have a table:

create table Planets( Name text collate nocase );
insert into Planets values( 'Mercury' );
insert into Planets values( 'Venus' );
insert into Planets values( 'Earth' );
insert into Planets values( 'Mars' );
insert into Planets values( 'Jupiter' );



How could I give those rows with enumeration:

1 Earth
2 Jupiter
3 Mars
4 Mercury
5 Venus


In the absence of any other replies, the best I've come up with so far  
is:


create view Enumerated
as
select count(*) as Sequence, Current.Name as Name
from Planets as Current
left join Planets as Others
where Current.Name >= Others.Name
group by Current.Name
;

It works by basically counting how many Other records are less than or  
equal to the Current record.


If there was some view, say "Filtered", that needed to be enumerated,  
I could replace "planets" in the above view with "Filtered" but I'd  
still have to re-write the "order by" clause as a comparison, which  
ignores the work already done by the "order by".


Is there a better way?

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Enumerating rows in a view

[T]

Hi all,

When I create a table, SQLite enumerates the rows in the rowid column.

When I create a view, is there any way to enumerate the output rows?

For example, say I have a table:

create table Planets( Name text collate nocase );
insert into Planets values( 'Mercury' );
insert into Planets values( 'Venus' );
insert into Planets values( 'Earth' );
insert into Planets values( 'Mars' );
insert into Planets values( 'Jupiter' );

and I have a view that sorts them alphabetically:

create view as select Name from Planets order by Name;

which gives:

Earth
Jupiter
Mars
Mercury
Venus

How could I give those rows with enumeration:

1 Earth
2 Jupiter
3 Mars
4 Mercury
5 Venus

Note that the sort order may be more complex than this, or there may  
be a "where" filtering. But the result needs to simply number the  
rows from 1 to n.


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sparse matrix

[T]

Hi Darren,


It seems to me that you have a flawed design.


Displaying sparse like that should be a function of your  
application display code, not the database


I had to chuckle that when I asked "How do I use this to do that",  
your solution was "you shouldn't have that and you should do it with  
something other than this" ;-) Not really helpful, but good for a  
chuckle.


Fortunately Simon's replies helped me out.

Hopefully my followup posting gives a better example.

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sparse matrix

[T]

Hi Simon,

Here's a less gruesome version - no cases. I've given no thought to  
performance comparisons.


Thanks for the two great solutions you posted. They certainly achieve  
the desired result with the simplified sample I gave. However, in the  
broader reality, it doesn't quite satisfy my situation since the  
rowids aren't necessarily in the order that they appear in the final  
view.


Here's a broader example.

Say I have a timetable like this:

rowid   Day   Period   TeacherRoomSubject
1   Tuesday   1Peters  A2 Maths
2   Monday2Peters  A2 Maths
3   Monday1Ng  A1 English
4   Monday3Peters  A2 Computing
5   Tuesday   3Ng  A1 English
6   Tuesday   2Ng  A1 History
7   Wednesday 4Smith   S2 Science
8   Wednesday 3Who S2 Science
9   Monday4KentH1 Sport
10  Wednesday 2KentH1 Sport
11  Tuesday   4Ng  A1 History
12  Wednesday 1Peters  A2 Maths

And I want to output something like this:

Day   Room SubjectTeacherPeriod
MondayA1   EnglishNg 1
  A2   Maths  Peters 2
   Computing 3
  H1   Sport  Kent   4
Tuesday   A2   Maths  Peters 1
  A1   HistoryNg 2
   English   3
   History   4
Wednesday A2   Maths  Peters 1
  H1   Sport  Kent   2
  S1   ScienceWho3
  Smith  4

But with counts of repetitions, like this:

Day   Room Subject  TeacherPeriod
MondayA1 1 English   1  Ng 1 1
  A2 2 Maths 1  Peters 2 2
   Computing 1   3
  H1 1 Sport 1  Kent   1 4
Tuesday   A2 1 Maths 1  Peters 1 1
  A1 3 History   1  Ng 3 2
   English   1   3
   History   1   4
Wednesday A2 1 Maths 1  Peters 1 1
  H1 1 Sport 1  Kent   1 2
  S1 2 Science   2  Who1 3
Smith  1 4

The "1" counts could even appear as null/blank, that would be fine.

The SQL for my sample Timetable above is:

create table Timetable
(
Day text,
Period integer,
Teacher text,
Room text,
Subject text
)
;
insert into table values('Tuesday',1,'Peters','A2','Maths');
insert into table values('Monday',2,'Peters','A2','Maths');
insert into table values('Monday',1,'Ng','A1','English');
insert into table values('Monday',3,'Peters','A2','Computing');
insert into table values('Tuesday',3,'Ng','A1','English');
insert into table values('Tuesday',2,'Ng','A1','History');
insert into table values('Wednesday',4,'Smith','S2','Science');
insert into table values('Wednesday',3,'Who','S2','Science');
insert into table values('Monday',4,'Kent','H1','Sport');
insert into table values('Wednesday',2,'Kent','H1','Sport');
insert into table values('Tuesday',4,'Ng','A1','History');
insert into table values('Wednesday',1,'Peters','A2','Maths');

and a view that sorts the data is:

create view "Timetable Sorted"
as
select rowid, Day, Subject, Room, Teacher, Period
from Timetable
order by Day, Period
;

Thanks for any further insight you may have,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Sparse matrix

[T]

Hi all,

OK, this one has me stumped. I've been staring at it too long.

I have a table with a column like this:

Code
a
a
a
a
b
b
c
c
c

I want to output two columns, one showing the Code, another showing  
the count of each code, but with a null wherever it was a repeat,  
like this:


Code   Count
a  4
.
.
.
b  2
.
c  3
.
.

(Note I've used . to show a null)

Any ideas?

I can simply do this to show the count of each, but it doesn't show  
the blank rows:


select Code, count(*) as Count from List group by Code;

Here's the test table:

create table List( Code text );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'b' );
insert into List values( 'b' );
insert into List values( 'c' );
insert into List values( 'c' );
insert into List values( 'c' );

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Altering views, keeping triggers

[T]

Hi Joe,


You've got the right idea. Just make use of sqlite_master.tbl_name.

  select sql || ';' from sqlite_master
where type = 'trigger' and tbl_name = 'MyView';

sqlite_master.name is the name of the table/view/index/trigger itself,
and sqlite_master.tbl_name is what it acts on.


I think that's mostly right. tbl_name "is what it acts on" for an  
index and trigger, but not for a view. Unfortunately, for a view,  
tbl_name is just the name of the view, not the table (or view) that  
it acts on. That's partly understandable since a view could act on  
more than one table.


But I had overlooked the fact that tbl_name will tell me what a  
trigger acts on. So thanks a lot for your thoughtful posting. That  
will help :-)


Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PRAGMA writable_schema=ON;

[T]

Hi Joe,

Even if you got the sqlite_master table entries right, the in- 
memory sqlite schema data structures would not be in sync.


Yes, but my point/question was, would that not only apply to tables  
and indexes? I expect that views and triggers have no data structures  
(eg rootpage = 0) so there's nothing to get out of sync.


You'd be better off using the normal DROP/CREATE SQL statements and  
published sqlite APIs to do this sort of thing.


Ideally, yes. But I've come across a few situations (such as the one  
I gave as an example) where being able to write to the sqlite_master  
tables would permit a solution that could be done totally in SQL, or  
which would save parsing schema.


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PRAGMA writable_schema=ON;

[T]

Now that is interesting. I didn't realize we could change
sqlite_master directly, but have often thought it could be very  
handy.


Warning:  If you mess up, your database becomes unreadable and  
unrepairable.  This is a very dangerous feature.  If you use it and  
you lose data:  no tears.


Let me clarify. What I'm asking is whether editing the view and  
trigger entries in sqlite_master is safer than editing table and  
index entries (see below).


I'd greatly appreciate any educated insight, not necessarily a  
guarantee. :-)


Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 15 August 2007 11:50:53 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] PRAGMA writable_schema=ON;

[EMAIL PROTECTED] wrote:


It appears that you can set

  PRAGMA writable_schema=ON;

Then do a manual UPDATE of the sqlite_master table to insert


I tried it and it seems to work.  But it is dangerous.  If you mess  
up, you corrupt the database file.


Now that is interesting. I didn't realize we could change  
sqlite_master directly, but have often thought it could be very  
handy. I've often read from it (ie select from sqlite_master), but  
not written (ie update or insert).


I imagine that writing to a table or index entry would be disastrous,  
eg:


update sqlite_master
set sql = 'create table MyTable( Col1, Col2 )
where name = 'MyTable' and type = 'table'
;

since I think SQLite wouldn't set up the required table data. Correct?

But what about triggers and views? Since (AFAIK, since rootpage = 0)  
there's no data structure created by them in the file, can we safely  
manipulate them directly in sqlite_master?


For an example, I'll use the predicament from my earlier message  
"Altering views, keeping triggers". As a possible solution to keeping  
triggers when a view is changed, would it be safe to either:


1. Update the view in sqlite_master directly, thereby avoiding  
SQLite's deletion of associated triggers:


update sqlite_master
set sql = 'create view MyView as select Col1, Col2 from MyTable'
where name = 'MyView' and type = 'view'
;

or:

2. Cache the triggers before changing the view, then insert them  
directly into sqlite_master:


begin immediate
;
create temporary table Cache as
select * from sqlite_master where type = 'trigger'
;
drop view 'MyView'
;
insert into sqlite_master
select * from Cache
where name not in ( select name from sqlite_master where type =  
'trigger' )

;
commit;

I guess "insert or ignore" could be used instead of testing for  
existence, if sqlite_master enforces a unique( type, name), but I  
don't know if this is safe to assume.


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] PRAGMA writable_schema=ON;

[T]

[EMAIL PROTECTED] wrote:


It appears that you can set

  PRAGMA writable_schema=ON;

Then do a manual UPDATE of the sqlite_master table to insert


I tried it and it seems to work.  But it is dangerous.  If you mess  
up, you corrupt the database file.


Now that is interesting. I didn't realize we could change  
sqlite_master directly, but have often thought it could be very handy.  
I've often read from it (ie select from sqlite_master), but not  
written (ie update or insert).


I imagine that writing to a table or index entry would be disastrous,  
eg:


update sqlite_master
set sql = 'create table MyTable( Col1, Col2 )
where name = 'MyTable' and type = 'table'
;

since I think SQLite wouldn't set up the required table data. Correct?

But what about triggers and views? Since (AFAIK, since rootpage = 0)  
there's no data structure created by them in the file, can we safely  
manipulate them directly in sqlite_master?


For an example, I'll use the predicament from my earlier message  
"Altering views, keeping triggers". As a possible solution to keeping  
triggers when a view is changed, would it be safe to either:


1. Update the view in sqlite_master directly, thereby avoiding  
SQLite's deletion of associated triggers:


update sqlite_master
set sql = 'create view MyView as select Col1, Col2 from MyTable'
where name = 'MyView' and type = 'view'
;

or:

2. Cache the triggers before changing the view, then insert them  
directly into sqlite_master:


begin immediate
;
create temporary table Cache as
select * from sqlite_master where type = 'trigger'
;
drop view 'MyView'
;
insert into sqlite_master
select * from Cache
where name not in ( select name from sqlite_master where type =  
'trigger' )

;
commit;

I guess "insert or ignore" could be used instead of testing for  
existence, if sqlite_master enforces a unique( type, name), but I  
don't know if this is safe to assume.


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Altering views, keeping triggers

[T]

Oops, sorry, that first paragraph should have read as below (I said  
"trigger" once where I meant "view"):


Short version of question:

I frequently want to alter view definitions, such as renaming them or  
changing the column definitions. The only way to do this is to drop  
the old view and create a new one (eg with the same name). The problem  
is that when I do this, any associated triggers are deleted by SQLite.  
What's the best way to keep the associated triggers?


Long version of question:

I can rename or add a column to a table (using SQLite's "alter table"  
command"). But to rename or add a column to a view I have to drop the  
view and recreate it. And if I want to to alter existing columns in a  
table, I have to drop the table and recreate it. That's fine, I can  
cope with that.


My problem is that, to my mind, SQLite seems inconsistent in how it  
deals with triggers compared to views, when a precedent table or view  
is deleted. Here are the actions I take and the effects in SQLite:


Action Effect on associated views Effect on  
associated triggers
-- --  
-
Rename table   None   Rename table  
references

Drop table None   Delete trigger
Drop view  None   Delete trigger

I would rather than SQLite consistently did not change any dependent  
triggers when I changed a precedent table or view (ie "none" in all  
the grid cells above). That would be consistent with how it treats  
dependent views. ie SQLite seems happy to keep views that no longer  
link to anything, but not triggers. And it's easier for me to  
explicitly delete triggers, if I want to, than try to find out what  
triggers will be affected, cache them somehow, perform my action, and  
reconstitute the triggers.


As a specific case, when I want to change existing columns in a table  
or view, the only way to do that in SQLite is to cache the old  
definition, drop the table or view, edit the cached definition  
(changing the columns) and recreate it. That's fine. But now all the  
associated triggers have disappeared. Or, in psuedo code:


set viewDefinition to SQLExec( "select sql from sqlite_master where  
name = 'MyView';" )

SQLExec( "drop view 'MyView';" )
set viewDefinition to ChangeColumnDefinitions( viewDefinition )
SQLExec( viewDefinition )

As mentioned, all the associated triggers are deleted. IS there a way  
to systematically locate and keep them? Such as:


set viewDefinition to SQLExec( "select sql || ';' from sqlite_master  
where type = 'view' and name = 'MyView';" )
set triggerDefinitions to SQLExec( "select sql || ';' from  
sqlite_master where type = 'trigger' and sql like '%on MyView%';" )

SQLExec( "drop view 'MyView';" )
set viewDefinition to ChangeColumnDefinitions( viewDefinition )
SQLExec( viewDefinition )
SQLExec( triggerDefinitions )

However, that would fail because the test "like '%on MyView%'" is not  
robust or accurate enough.


Any other ideas or comments? How do you approach the issue, or don't  
you have the need to alter views with associated triggers?


Thanks,
Tom

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Altering views, keeping triggers

[T]

Hi all,

Short version of question:

I frequently want to alter view definitions, such as renaming them or  
changing the column definitions. The only way to do this is to drop  
the old trigger and create a new one (eg with the same name). The  
problem is that when I do this, any associated triggers are deleted by  
SQLite. What's the best way to keep the associated triggers?


Long version of question:

I can rename or add a column to a table (using SQLite's "alter table"  
command"). But to rename or add a column to a view I have to drop the  
view and recreate it. And if I want to to alter existing columns in a  
table, I have to drop the table and recreate it. That's fine, I can  
cope with that.


My problem is that, to my mind, SQLite seems inconsistent in how it  
deals with triggers compared to views, when a precedent table or view  
is deleted. Here are the actions I take and the effects in SQLite:


Action Effect on associated views Effect on  
associated triggers
-- --  
-
Rename table   None   Rename table  
references

Drop table None   Delete trigger
Drop view  None   Delete trigger

I would rather than SQLite consistently did not change any dependent  
triggers when I changed a precedent table or view (ie "none" in all  
the grid cells above). That would be consistent with how it treats  
dependent views. ie SQLite seems happy to keep views that no longer  
link to anything, but not triggers. And it's easier for me to  
explicitly delete triggers, if I want to, than try to find out what  
triggers will be affected, cache them somehow, perform my action, and  
reconstitute the triggers.


As a specific case, when I want to change existing columns in a table  
or view, the only way to do that in SQLite is to cache the old  
definition, drop the table or view, edit the cached definition  
(changing the columns) and recreate it. That's fine. But now all the  
associated triggers have disappeared. Or, in psuedo code:


set viewDefinition to SQLExec( "select sql from sqlite_master where  
name = 'MyView';" )

SQLExec( "drop view 'MyView';" )
set viewDefinition to ChangeColumnDefinitions( viewDefinition )
SQLExec( viewDefinition )

As mentioned, all the associated triggers are deleted. IS there a way  
to systematically locate and keep them? Such as:


set viewDefinition to SQLExec( "select sql || ';' from sqlite_master  
where type = 'view' and name = 'MyView';" )
set triggerDefinitions to SQLExec( "select sql || ';' from  
sqlite_master where type = 'trigger' and sql like '%on MyView%';" )

SQLExec( "drop view 'MyView';" )
set viewDefinition to ChangeColumnDefinitions( viewDefinition )
SQLExec( viewDefinition )
SQLExec( triggerDefinitions )

However, that would fail because the test "like '%on MyView%'" is not  
robust or accurate enough.


Any other ideas or comments? How do you approach the issue, or don't  
you have the need to alter views with associated triggers?


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNION?

[T]

Hi Lee,


This query does not work in SQLite but works in MS SQL Server:

sqlite> SELECT items_idx, [name], active FROM Items
   ...> WHERE active = 'T' AND Items.items_idx IN
   ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777)
   ...> UNION
   ...> (SELECT item FROM RelatedItems WHERE related_item = 1777))
   ...> ORDER BY Items.name ASC;
SQL error: near "UNION": syntax error


Of course the problem is the brackets you have around each SELECT  
statement, which separates them from the UNION operator.


So, it works fine like this:

SELECT items_idx, [name], active FROM Items
WHERE active = 'T' AND Items.items_idx IN
(
SELECT related_item FROM RelatedItems WHERE item = 1777
UNION
SELECT item FROM RelatedItems WHERE related_item = 1777
)
ORDER BY Items.name ASC;

Also, you may want to consider avoiding performing an IN on a UNION.  
As far as I know, SQLite doesn't optimize that, so will build the  
entire union before performing the IN. If you instead do the  
following, it should be a lot faster (if you have lots of data). But  
I may be wrong.


SELECT items_idx, [name], active
FROM Items
WHERE active = 'T'
AND
(
items_idx IN
( SELECT [related_item] FROM RelatedItems WHERE item  
= 1777 )

OR items_idx IN
( SELECT item FROM RelatedItems WHERE related_item =  
1777 )

)
ORDER BY [name] ASC
;


or by using a JOIN:

SELECT items_idx, [name], active
FROM Items
JOIN RelatedItems AS RI
WHERE active = 'T' AND
(
RI.related_item = Items.items_idx AND RI.item = 1777
OR
RI.item = Items.items_idx AND RI.related_item = 1777
)
ORDER BY Items.name ASC
;

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] "database is locked" SQLITE_BUSY when db is on network drive...

[T]

Hi Chase,

it connects fine.  no errors.  but then i try to create a temp  
table (which, like i said, works if the db is local) it fails  
immediately with SQLITE_BUSY "database is locked".


It's some Mac vs SQLite bug. You can recompile SQLite to fix it. See  
the old post below. And search the archives for "locked" in the subject.


Tom

 
From: T <[EMAIL PROTECTED]>
Date: 11 May 2007 10:54:02 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database is locked error

Is there a timetable for including the fix for opening SQLite files  
on a shared volume? Since it's fairly trivial, is there a reason why  
it hasn't been included yet?


Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 29 April 2007 3:35:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database is locked error

Following up an old thread:

The SQLite sources include an (Apple-supplied) patch to work around  
the problem. Recompile

with

   -DSQLITE_ENABLE_LOCKING_STYLE=1

We are working toward turning on this patch by default, but we are  
not quite there yet.


I compiled and ran SQLite 3.3.17 and got the old error again when  
accessing a database file on a server volume, with SQLite saying it  
is locked.


Does this mean that we are still "not quite there yet" with a default  
fix? Any time frame?


Thanks,
Tom

--
Best value broadband in Australia.
$3 per GB excess instead of $150 from BigPond
Free uploads, free morning downloads.
http://www.tandb.com.au/broadband/?sig


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] strategy adding indexes

[T]

Hi Richard,

It's great to see clarity on this "from the horse's mouth" (I hope  
that translates across the equator). Thank you.


- indexes that include all possible combinations of fields that  
may appear in a WHERE clause.


As an aside, note that, AFAIK, indexes are only used:

1. To get the first match of a query. If you ask for more than one  
matching record, the second, third etc matches are found by  
searching, not through the index.


No.

If an index is used to retrieve the first row in the result, then it  
continues to be used for every row.


That's great! :-) That's much faster and more versatile.

My source seems incorrect, or perhaps my understanding of it. Where is  
it, ah yes, this book here, flipping pages... In the book "The  
Definitive Guide to SQLite", chapter 4, page 155, under the title  
"Indexes", it reads:


If you have a table of 10,000 records, a sequential scan will read  
all 10,000 rows to find all matches, while an index scan will read  
4 rows (log(10,000)) to find the first match (and from that point  
on it would be linear time to find all subsequent matches).


Is that last part wrong (about linear time for subsequent matches), or  
am I misinterpreting it?


2. From left to right in the same order as your index... If you  
miss a column in the
sequence or place one out of order, the index won't be used from  
that point in the test onwards.


The order of terms in a WHERE clause make no difference.


Again, that's good to know. Again I've located the source of my info  
on the next page of the same book, which says:



Index Utilization
...
Say you have a table defined as follows:
CREATE TABLE foo (a,b,c,d);
Furthermore, you create a multicolumn index as follows:
CREATE INDEX foo_idx on foo (a,b,c,d);
The columns of foo_idx can only be used sequentially from left to  
right. That is, in the query SELECT * FROM foo WHERE a=1 AND b=2  
AND d=3 only the first and second conditions will use the index.  
The reason the third condition was excluded is because there was  
no condition that used c to bridge the gap to d. Basically, when  
SQLite uses a multicolumn index, it works from left to right  
column-wise. It starts with the left column and looks for a  
condition using that column. It moves to the second column, and  
so on. It continues until either it fails to find a valid  
condition in the WHERE clause that uses it or there are no more  
columns in the index to use.


Is that wrong, or am I just not getting it?

3. ... The last test (only) may be one or two inequality tests,  
such as ">" or "<".


Each index can use at most one inequality operator and it must be on  
the right-most column of the index that gets used.


"The book" seems to indicate that we can use at most two (not one)  
inequality operators, where it says:


SQLite will use a multicolumn index only if all of the conditions  
use either the equality (=) or IN operator for all index columns  
except for the right-most index column. For that column, you can  
specify up to two inequalities to define its upper and lower  
bounds.


Or am I misunderstanding?

Thanks for your patience with any obvious errors I may be making.

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] strategy adding indexes

[T]

Hi RBS,

- indexes that include all possible combinations of fields that may  
appear

in a WHERE clause.


As an aside, note that, AFAIK, indexes are only used:

1. To get the first match of a query. If you ask for more than one  
matching record, the second, third etc matches are found by searching,  
not through the index.


2. From left to right in the same order as your index. So if you  
create index MyIndex on MyTable ( Column1, Column2, Column3 ), then  
you must test them in the same order, eg: where Column1 = Value1 and  
Column2 = Value2 or Column3 = Value3. If you miss a column in the  
sequence or place one out of order, the index won't be used from that  
point in the test onwards.


3. In equality tests, eg "=" (equals) and "in". If you use "like" for  
comparison, the index isn't used. The last test (only) may be one or  
two inequality tests, such as ">" or "<". And that last test must be  
in sequence (ie rule 2).


I hope this helps a bit. Some more learned SQLiters out there may care  
to correct or clarify.


Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] last_row_id() after insert via trigger

[T]

Hi all,

Short question:

When I explicitly insert a row into a table, I am able to use the  
last_insert_rowid() function to get the rowid of that inserted row.


But how do I get the rowid of a row inserted by a trigger? It seems  
that last_insert_rowid() doesn't change.


Longer example:

For example, I have a table and a view, with a trigger that will  
insert a row into the table, if the user attempts to insert a row into  
the corresponding view. That works fine, except that I can't get the  
rowid of the inserted row.


See (and test) sql below:

begin transaction;

-- Create the Test Table, View and Trigger:
drop table if exists "Test Table";

create table "Test Table" ( "Dummy" );

create view if not exists "Test View" as select rowid, * from "Test  
Table";


create trigger if not exists "Insert In View"
instead of insert
on "Test View"
begin
insert into "Test Table" ( rowid ) values ( null );
end;

-- Test inserting into the table:
insert into "Test Table" (rowid) values (null);
select rowid || ' from table after insert' from "Test Table" where  
rowid=last_insert_rowid();

insert into "Test Table" (rowid) values (null);
select rowid || ' from table after insert' from "Test Table" where  
rowid=last_insert_rowid();


-- Test inserting into the view, which will trigger insert into the  
table:

insert into "Test View" (rowid) values (null);
select rowid || ' from view after insert' from "Test View" where  
rowid=last_insert_rowid();

insert into "Test View" (rowid) values (null);
select rowid || ' from view after insert' from "Test View" where  
rowid=last_insert_rowid();
select rowid || ' from table' from "Test Table" where  
rowid=last_insert_rowid();


rollback;

which gives output:

1 from table after insert
2 from table after insert
2 from view after insert
2 from view after insert
2 from table

but should increment after each insert, ie give 1, 2, 3, 4, 4

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Enclosing quotes

[T]

Hi All,

When I rename a table, SQLite seems to override the quote characters I  
use, instead using single quote marks. Is this a bug?


I prefer to use double quotes or square brackets for entity and column  
names (aka "user-defined objects"), obeying the SQLite documentation,  
but SQLite overrides me and the documentation.


Here's the SQLite documentation at http://www.sqlite.org/lang_keywords.html

SQLite adds new keywords from time to time when it take on new  
features. So to prevent your code from being broken by future  
enhancements, you should normally quote any indentifier that is an  
English language word, even if you do not have to.


If you want to use a keyword as a name, you need to quote it. There  
are three ways of quoting keywords in SQLite:


'keyword'
A keyword in single quotes is interpreted as a literal string if it  
occurs in a context where a string literal is allowed, otherwise it  
is understood as an identifier.



"keyword"
A keyword in double-quotes is interpreted as an identifier if it  
matches a known identifier. Otherwise it is interpreted as a string  
literal.



[keyword]
A keyword enclosed in square brackets is always understood as an  
identifier. This is not standard SQL. This quoting mechanism is used  
by MS Access and SQL Server and is included in SQLite for  
compatibility.


Here's an example of the problem:

create table [Old Table Square] ( Test );
create table 'Old Table Single' ( Test );
create table "Old Table Double" ( Test );
create trigger "Old Trigger Double"
after delete on "Old Table Double"
begin
delete from "Other Table";
end;

alter table [Old Table Square] rename to [New Table Square];
alter table 'Old Table Single' rename to 'New Table Single';
alter table "Old Table Double" rename to "New Table Double";
.dump

gives:

CREATE TABLE 'New Table Square' ( Test );
CREATE TABLE 'New Table Single' ( Test );
CREATE TABLE 'New Table Double' ( Test );
CREATE TRIGGER "Old Trigger Double"
after delete on 'New Table Double'
begin
delete from "Other Table";
end;

Notice that the quotes of the table names have all changed to single  
quotes, in the create table and create trigger statements, after  
renaming.


To me, this seems bad, because single quote marks are also interpreted  
as enclosing a literal string.


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Updatable views

[T]

Hi All,

I'm using triggers to make my views updatable (ie the user can edit  
the values view because SQLite will instead change the related source  
table value). Which method is best to facilitate this?:


1. Use the "instead of update on ViewName" syntax. Trigger on the  
update of the view as a whole (ie any column triggers the one update).


or:

2. Use the "instead of update of ColumnName on ViewName" syntax in a  
trigger for each column. Trigger on the update of each column/field  
individually.


It seems to me that triggering on the view as a whole would  
unnecessarily update multiple values/columns when only one is changed.  
On the other hand, adding a trigger for each column seems overkill.  
Which is the best way?


Below is the complete SQL of a simplified example, where I have an  
Orders table and a Products table, (which lists the Products in each  
order). I have an "Orders Calc" view which shows fields from the  
Orders table, along with a sum() calculation of the Products in that  
Order.


The result of the SQL by both methods is the same:

Testing method 1: Create a trigger for the view as a whole:
10001|Apple|22.0|1020.0|Cheque|2007-07-21|R1234|2007-07-21
10001|Apple|22.0|1020.0|Cheque|2007-07-22|R1234|2007-07-21
SQL error near line 85: You cannot change the Total, since it is  
calculated.


Testing method 2: Create a trigger for the view per column:
10001|Apple|22.0|1020.0|Cheque|2007-07-21|R1234|2007-07-21
10001|Apple|22.0|1020.0|Cheque|2007-07-23|R1234|2007-07-21
SQL error near line 154: You cannot change the Total, since it is  
calculated.


Any personal approaches or revelations welcome :-)

Thanks,
Tom

/*
Create the test tables and view.
*/

create table Orders
(
"Order ID" integer primary key autoincrement,
Supplier text,  -- name of supplier
Delivery real,  -- delivery cost in dollars
	"Paid Method" text,	-- method of payment, such as deposit, credit  
card, cash

"Paid Date" date, -- date that payment was sent
Receipt text,   -- payment receipt
Ordered date-- date that the order was sent
);

create table if not exists Products
(
"Order ID" integer,   -- Orders foreign key
Code text,  -- Product Code
Description text,   -- Product Description
Buy real,   -- Buy price I pay when ordering this item
Quantity integer-- Quantity of this product in this order
);

create view "Orders Calc"
as
select
Orders."Order ID" as "Order ID",
Supplier,
Delivery,
sum( Quantity * Buy ) + Delivery
as Total,
"Paid Method",
"Paid Date",
Receipt,
Ordered
from Orders
left join Products
on Orders."Order ID" = Products."Order ID"
group by Orders."Order ID";

/*
Insert Test data
*/

begin;
insert into Orders values ( 10001, 'Apple', 22.0, 'Cheque',  
'2007-07-21', 'R1234', '2007-07-21');

insert into Products values ( 10001, 'IPH8GB', 'iPhone 8GB', 499.0, 2 );
commit;

/*
Method 1: Create a trigger for the view as a whole
*/

create trigger "Update Orders Calc"
instead of update on "Orders Calc"
for each row
begin
update Orders
set
Supplier = new.Supplier,
Delivery = new.Delivery,
"Paid Method" = new."Paid Method",
"Paid Date" = new."Paid Date",
Receipt = new.Receipt,
Ordered = new.Ordered
where "Order ID" = new."Order ID";
select
case
when old.Total != new.Total
			then raise( abort, 'You cannot change the Total, since it is  
calculated.')

end;
end;

/*
Test method 1
*/

begin;
select 'Testing method 1: Create a trigger for the view as a whole:';
select * from "Orders Calc";
update "Orders Calc"
set "Paid Date" = '2007-07-22' where "Order ID" = 10001;
select * from "Orders Calc";
update "Orders Calc"
set Total = 300.0 where "Order ID" = 10001;
rollback;

/*
Method 2: Create a trigger for the view per column
*/

drop trigger if exists "Update Orders Calc";

create trigger "Update Orders Calc Supplier"
instead of update of Supplier on "Orders Calc"
for each row
begin
	update Orders set Supplier = new.Supplier where "Order ID" =  
new."Order ID";

end;

create trigger "Update Orders Calc Delivery"
instead of update of Delivery on "Orders Calc"
for each row
begin
	update Orders set Delivery = new.Delivery where "Order ID" =  
new."Order ID";

end;

create trigger "Update Orders Calc Paid Method"
instead of update of "Paid Method" on "Orders Calc"
for each row
begin
	update Orders set "Paid Method" = new."Paid Method" where "Order ID" 

Re: [sqlite] SQLite on Mac

[T]

Hi Ahmed,


Does SQLite work on Mac, and if yes, is there any Mac enabled version
that I could download?


As others have mentioned, yes, SQLite not only runs on a Mac, but it's  
already installed as of Mac OS X 10.4 "Tiger" and after. Apple uses it  
for indexing email in the Mail application, Core Data in XCode  
development, and media management in high end apps like Aperture.


If you have an earlier Mac OS X version, or want the very latest  
SQLite version, you can download it from the first link under the  
"Source Code" heading at:

http://www.sqlite.org/download.html
You'll need the Apple Developer Tools installed on your computer,  
which comes free with your computer or Mac OS X install discs, to  
compile and install it in about four steps.


To try it out, launch the Terminal program (already in your / 
Applications/Utilities folder) and type:


sqlite3 MyTestDatabase

then in the sqlite3 shell, type any sqlite commands, such as:

.help
.quit
create table MyTestTable( Name text, Age integer);

and so on.

There is also a range of GUI apps for the Mac for editing SQLite  
databases.


Reply here if you need more info.

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Oddball query required

[Lucas (a.k.a T-Bird or bsdfan3)]

Thank you very much Igor.  This will do exactly what I need.

Igor Tandetnik wrote:


Lucas (a.k.a T-Bird or bsdfan3)
<[EMAIL PROTECTED]> wrote:


I am using SQLite in an application within a game where I have a table
of player nicknames and cumulative player scores (among other things).
How do I figure out at what (numeric) one of these records would be at
if the table were sorted, say, by the cumulative scores column?



select count(*) from tableName
where scrore > (select score from tableName where playerId = :player);

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Oddball query required

[Lucas (a.k.a T-Bird or bsdfan3)]

I am using SQLite in an application within a game where I have a table 
of player nicknames and cumulative player scores (among other things).  
How do I figure out at what (numeric) one of these records would be at 
if the table were sorted, say, by the cumulative scores column?  Or 
should I just store that position in the database itself?




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Capturing data at a point in time

[T]

Subject was: [sqlite] Trigger update of multiple columns

I've change the subject since this thread is discussing a tangent to  
my original query.


Hi Fred,

Therefore, the data items contained in each row of this table  
should be a permanent reflection of
the master tables' data content at an exact point in time and must  
not remain linked to the related dynamic master tables.


Exactly what I'm after here :-) Thanks for cutting through my haze ;-)

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Update of multiple columns

[T]

In case the original question has been clouded by discussions of  
running a general store and the arson habits of the Hand Crafted  
Guild, allow me to crystalize my purely SQLite question:


I know I can update via:

  update Table1
set
  c1 = (select d1 from Table2 where Table2.id = desired_id)
, c2 = (select d2 from Table2 where Table2.id = desired_id)
, c3 = (select d3 from Table2 where Table2.id = desired_id)
, cn = (select dn from Table2 where Table2.id = desired_id)
  where
rowid = desired_rowid

But that executes the same where clause n times, so scans through  
Table2 for a to find the same matching row n times.


Is it possible to construct an update that executes a where clause  
once to locate all of the desired columns?


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trigger update of multiple columns

[T]

Hi Ed,


I tried to update a list of columns:
UPDATE t SET (c1, c2, c3) = (SELECT c1, c2, c3) FROM t2 WHERE ..
but this syntax is not accepted as you probably already know.


Thanks for your very thoughtful reply. That is exactly the type of  
syntax I'm after, with only one executed WHERE clause for multiple  
columns. But, as you say, it doesn't work in SQLite.


I may promote [INSERT OR] REPLACE then. It is syntactically  
described in the SQLite documentation but for the semantics you may  
see the original MySQL doc.

 http://dev.mysql.com/doc/refman/5.0/en/replace.html
It is the only way that I see to do the update with only a single  
scan of the product table.


Yes, a single scan is the objective.

Hmmm, yes, I can see how REPLACE might be useful. Perhaps something  
like:
REPLACE INTO t ( id, c1, c2, c3 ) SELECT id c1, c2, c3 FROM t2 WHERE  
id = new.id


REPLACE, as I understand it, does rely on the id field being created  
as a PRIMARY KEY, but that should be fine (and most likely already  
the case).


But may be REPLACE causes troubles in combination with triggers.  
Because indirectly it performs a DELETE and a new INSERT.


I guess in a trigger it would look something like:

CREATE TRIGGER trigger
AFTER UPDATE OF id
ON t
BEGIN
  UPDATE t
REPLACE INTO t ( id, c1, c2, c3 ) SELECT id, c1, c2, c3 FROM t2  
WHERE id = new.id

  WHERE
rowid=new.rowid
  ;
END

I'll have to check if that's allowed.


Other suggestions should be welcome.


Yes, other suggestions would be very welcome.

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trigger update of multiple columns

[T]

Hi Gerry,

keep each version of each product's description in the products  
table, along with the date that description became valid. Then the  
product ID and date in each transaction would reference the  
appropriate product table data.


In certain circumstances, I can see how that would be useful.  
However, it's a problem here because:


1. The products table has many more rows and columns than I need to  
copy into the occasional sale. So maintaining the products table  
beyond this need is cumbersome.


2. When a product changes (such as price increase), I don't want to  
add a whole new product_id and mostly duplicate information, into the  
already huge products database.


3. 99% of the items in the Products database get deleted without any  
sale being made against them. So keeping them all around for  
historical reasons would multiply the size of the database many fold.  
It also raises issues of tracking what products can be deleted and  
what needs to be kept since a sale used it.


4. I only need to "track" a change in a product if a sale is made  
against it. So it makes sense to capture the details of that product  
in the sale when the sale is made.


So, what I need is, that when (and only when) a product is added to a  
sale, then that product's buy price, sell price and description are  
stored against that sale.


I hope that clarifies the situation. Thanks for your thoughts and time.

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trigger update of multiple columns

[T]

Hi John,

You have a reference data set which is accessed to get the current  
value of reference elements and store transactions to record  
events. The transaction trails provide event history.


Yes, agreed.

A price is in the reference data, its value transferred to a  
transaction is no longer a price, it is a sale which represents the  
value of the price at the time the event occurred.


Yes.

How about reading your price data etc and just inserting a  
transaction into your sales table?


Yes, that's what I'm doing. I just want to make it more efficient.

Technically it's the sale_products table (since each sale has many  
products etc), but yes, I want to insert the transactions data  
(product_id, buy, sell, desc) into the sale_products table. But I  
want a mechanism whereby if I enter the product_id, then the buy,  
sell, desc columns are auto entered (copied) from their corresponding  
row in the products table.


Tom



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trigger update of multiple columns

[T]

Hi John,

A general rule of database design is to seperate reference and  
transactional data.  Then you can have a normalized database in a  
dynamic environment.


Yes, I think that's what I am designing.

The reference data is the products table (and potentially customer  
table etc)


The transactional data is the sale_products table which lists what  
products went with each sale.


Or do you mean something else?

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trigger update of multiple columns

[T]

Hi Puneet,


ok, your problem makes a bit more sense


Great :-)


but still, much in it doesn't make sense.


OK, I'll see if I can clarify further.

even if the products table is changing, the buy, sell, and  
description of a given item shouldn't change.


The buy and sell price of products always change, due to inflation,  
sales, competition and so on. Pick up just about any grocery or  
electronic goods catalog today and next month and compare prices on  
the same products.


Further, even if buy and sell change, then at least the description  
will be not be different.


True, the description changes less often than prices. However, it  
does change. It may be as small as a spelling correction, or have  
some promotion attached eg "Now 20% bigger", or just a refinement of  
the description without a change to the actual product. In all cases,  
it is important that a particular sale shows the price and  
description (and any other details) that were listed for the product  
at the moment is was bought/sold.


If none of that apply as you imply above, then it is no longer the  
same product... it is a different product altogether. In other  
words, you can still identify each product with a unique id


What you say is true if it is a completely different or even updated  
model/product. You would expect that the product_id would also change  
for such a major change. However, this type of change is not what I'm  
catering for. And, in any case, the product_id of each product is set  
by the suppliers, so I have no control over it. And yes, I could  
introduce my own product_id to override theirs but that's getting way  
of track and doesn't really occur here anyway.


and one of the basic rules of normalization is that all related  
things should be together.


Yes, I understand that. But I also hope I'm getting the point through  
that the price is not static for a given product, and to a lesser  
degree the description may change slightly, and I need to keep a  
record of the product price and features as it was when purchased.


So, all information about a product should be in the products  
table, not in the sale_products table.


The sales_products table is a many to many table, linking many sales  
with many products. One sale may contain many products. And one  
product may appear in many sales. To my knowledge the only way to set  
up a many to many relationship is to create an intermediate table in  
this way, even without consideration of the changing price etc.


Take a bar of soap -- bought it for $2, sold it for $2.40,  
description is "woodsy, honey dew cataloupe smelling hand crafted  
soap." A product_id of 243 identifies that entity uniquely. If you  
change its attributes, say now it is, "viscous, tar-based paste  
guaranteed to get motor grease off" then it is a completely  
different product, and should have a different product_id.


Good example, I'll use it. I sell that soap today to Bill for the  
price shown in today's catalog, at $2.40. But next week we're  
overstocked, so I sell it to Ted for $2.10. And next week I revise my  
catalog after having realized that people in my state don't know what  
cantaloupe is, but instead call it "water melon" (true story). The  
following week the "Hand Crafted" guild tells me that the name is  
trademarked, so I change it's description again before selling it to  
Neo. And a month later, the product is completely removed from my  
catalog since I can no longer source it (Hand Crafted Guild under  
suspicion of arson).


So, in each case, I need to capture the current product details for  
the invoice/sale. I need an exact record of what I invoiced each  
customer for. I need to know that I sold it to Bill for $2.40 when it  
was called "cantaloupe", even though the last product catalog shows  
it at $2.10 and called it "water melon", and especially since it no  
longer appears in my current catalog in a month's time.


So, my sale_products table looks like this, in part:

sale_idproduct_id   buy   sell   desc
1001   243  $2$2.40  woodsy, honey dew cataloupe  
smelling hand crafted soap
1013   243  $2$2.10  woodsy, honey dew cataloupe  
smelling hand crafted soap

1042   145  $5$6.00  white rabbit tattoo remover
1042   243  $2$2.10  woodsy, honey dew water melon  
scented hand crafted soap

1042   176  $4$5.10  red pill
1058   243  $2$2.10  woodsy, honey dew water melon  
scented home made soap


And sales contains (simplified):

sales:

sale_id  customer   date
1001 Bill   2007-06-18
1013 Ted2007-06-24
1042 Neo2007-06-30
1058 Morpheus   2007-07-04

Even if the product table is updated to capture new items from the  
different catalogs, it will forever store the attributes of each  
product, creating a unique history right there.


No. The product catalog/table is orders of magnitude larger than the  

Re: [sqlite] Trigger update of multiple columns

[T]

Hi Puneet and John,

You each respectively said:

Why are you repeating the Code, Buy, Sell, and Description columns  
in the Sale_Products table when they already exists in the Products  
table?


A traditional goal in database design is to place data in "Third  
Normal Form" which means in essence that each data element is only  
stored in one place


Good question/point. Sorry I thought from my introverted world that  
this would be obvious, so didn't elaborate. So let me clarify:


The Products table is changing all the time. What might be listed  
today when a Sale is made, might no longer be listed in Products in a  
couple of weeks (but needs to retain the details in the Sale). And  
even if it is still listed in Products, it's very likely that the  
price and probably description will have changed.


So the Sale_Products table needs to capture the values of the Buy  
Price, Sell Price and Description (and others) when the sale is made,  
and cannot simply be dynamically related to their occurrence in the  
Products table (since it will change, but the Sale_Products extract  
for the current sale must remain unchanged).


assume that the "buy" column is the price I paid for the item, and  
"sell" column is the price I get for it) --


Yes, that's right.


CREATE TABLE products (
 product_id INTEGER PRIMARY KEY,
 buy REAL,
 sell REAL,
 desc TEXT
);

CREATE TABLE customers (
 customer_id INTEGER PRIMARY KEY,
 .. other customer info columns ..
);

CREATE TABLE sales (
 sale_id INTEGER PRIMARY KEY,
 product_id INTEGER,   -- FK to products table
 customer_id INTEGER   -- FK to customes table
);


One sale may involve several products, so it's more like this:

CREATE TABLE sales (
 sale_id INTEGER PRIMARY KEY,
 product_id INTEGER,   -- FK to products table
 customer_id INTEGER   -- FK to customes table
);

CREATE TABLE sale_products (
 sale_id INTEGER,  -- FK to sales table
 product_id INTEGER,   -- FK to products table
 buy REAL,
 sell REAL,
 desc TEXT
);

Now, when a new product is added to a Sale, I do this:

INSERT INTO sale_products(sale_id, product_id) VALUES( ? , ? )

And what I need is some kind of trigger that will automatically fill  
in the buy, sell and desc columns for me.


Something like:

CERATE TRIGGER update_sale_products
AFTER UPDATE OF product_id
on sale_products
BEGIN
  UPDATE sale_products
SET
  buy  = (SELECT buy  FROM products WHERE products.product_id =  
NEW.product_id)
, sell = (SELECT sell FROM products WHERE products.product_id =  
NEW.product_id)
, desc = (SELECT desc FROM products WHERE products.product_id =  
NEW.product_id)

  WHERE
ROWID=NEW.ROWID
  ;
END

which is basically just a rewrite of my original post, but using your  
capitalization and entity names.


But I want something without the multiple lookups on the products  
table of the same thing, ie the:

WHERE products.product_id = NEW.product_id

Any ideas?

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Trigger update of multiple columns

[T]

Hi John,

Thanks for your reply.

You would have a better database if you normalize it and not do  
what you propose.


Meaning what, exactly?

Obviously, I've only given rough indications as to my data source,  
such as:


the huge Products table (which is actually a UNION ALL of various  
supplier catalogs)


without detailing where all the data in there comes from, since that  
would distract too much from my question. But suffice to say that  
"normalizing" beyond the current structure is not straight forward.


In any case, at least in my ignorance of what you propose, it's  
beside the point.


In essence, I'm asking: is it possible to update multiple columns in  
a row, where all those values come from a single related row, without  
SQLite having to find (eg via SELECT) that related row multiple times?


Or, put another way, I want to get SQLite to:

1. Locate the related row.

2. Grab the desired columns from that row, putting each in the  
related row.


I hope that clarifies.

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Trigger update of multiple columns

[T]

Hi All,

I have a pretty standard sales tracking database consisting of tables:

Products  - Each row is a product available for sale.
Includes fields: Code, Buy, Sell, Description

Sales - Each row is a sale made to a customer.
Includes fields: Ref, Customer

Sale_Products - Each row is an product (many) included in a sale (one).
Includes fields: Sale_Ref, Code, Buy, Sell, Description

Now, when I add a new Sale_Products row and assign a product Code to  
it, I want to trigger it to auto enter the Buy and Sell prices, and  
the description, by looking up the related Product (ie where  
Sale_Products.Code = Products.Code)


How can I do this?

I have something like this:

create trigger Update_Sale_Products_Code
after update of Code
on Sale_Products
begin
  update Sale_Products
set
  Buy = (select Buy from Products where Products.Code = new.Code)
, Sell = (select Sell from Products where Products.Code = new.Code)
, Description = (select Description from Products where  
Products.Code = new.Code)

  where
rowid=new.rowid
  ;
end

It works, but it's unnecessarily slow, since it takes a while to look  
up the huge Products table (which is actually a UNION ALL of various  
supplier catalogs), and it's looking it up for each updating field  
(and I have more fields to lookup than shown in this example). It  
would be more efficient to look it up once to find the corresponding  
product (according to Products.Code = new.Code), but I'm stumped as  
to how to do that.


I tried:

create trigger Update_Sale_Products_Code
after update of Code
on Sale_Products
begin
  update Sale_Products
set
  Buy = (select Buy from Products)
, Sell = (select Sell from Products)
, Description = (select Description from Products)
  where
rowid=new.rowid
and Products.Code = new.Code
  ;
end

But that fails, and seems a bit ambiguous anyway. It seems to need  
some kind of JOIN, but I can't see provision for it in the UPDATE  
syntax.


There must be a much simpler way that I'm overlooking. Please  
enlighten me.


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Why do you use SQLite? Comments for an article needed

[Lucas (a.k.a T-Bird or bsdfan3)]

I personally like it for embedded (in other applications) jobs because 
of its small code footprint compared to a RDBMS.


Tim Anderson wrote:


Many thanks to those who have commented (more are welcome of course;
though I won't be able to use all of them).

I'll post a link to the piece when it appears.

Thanks again

Tim

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Regexp

[T]

Hi,

I noticed in recent versions of SQLite the addition of some functions  
such as Trim(). So I am hopeful that at least one more function,  
Regexp(), can be included since it is such a central and common need.  
Time and time again I come up with needing a Regexp function, and I  
notice that the SQLite expression syntax even caters for it, but  
doesn't actually implement it.


I need full PCRE (ie Perl Compatible Regular Expression) support, for  
things such as grouping, replacing, "g i s" options.


For instance, Trim() could have been accomplished via Regexp, such as:

Trim = '   text between spaces   ' Regexp '/[:space:]*(.*?)[:space:]*/s'

I'm not sure how the SQLite syntax should cater for extracting a  
particular group from the match (ie the text match between the  
brackets above).


I know there are hooks for adding a Regexp function/operator  
ourselves, but I need to know it's available on other machines with  
standard install. It's the same reasoning, I guess, as why Trim() was  
added, but Regexp seems to serve a wider need.


Please and thankyou,
Tom

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error

[T]

Is there a timetable for including the fix for opening SQLite files  
on a shared volume? Since it's fairly trivial, is there a reason why  
it hasn't been included yet?


Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 29 April 2007 3:35:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database is locked error

Following up an old thread:

The SQLite sources include an (Apple-supplied) patch to work around  
the problem. Recompile

with

   -DSQLITE_ENABLE_LOCKING_STYLE=1

We are working toward turning on this patch by default, but we are  
not quite there yet.


I compiled and ran SQLite 3.3.17 and got the old error again when  
accessing a database file on a server volume, with SQLite saying it  
is locked.


Does this mean that we are still "not quite there yet" with a default  
fix? Any time frame?


Thanks,
Tom




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[T]

Hi Brandon,


Is there a way to upgrade the module to SQLite 3 on PHP4?


I think so. I recall reading something about it, but since I was able  
to just install PHP5 I went via that route. Try Google:

http://www.google.com.au/search?q=SQLite3+PHP4

Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Longest "real" SQL statement

[T]

Hi Richard,

I tend to use a series of CREATE VIEW AS SELECT type statements to  
effectively nest my SELECT statements, making them more humanly  
editable and manageable. Consequently, the size of the maximum sized  
single SQL statement is reduced, though the total SQL statement  
effectively executed may be reasonably large. So I don't know if such  
a series would be of use to look at, for your purposes.


In any case, below is a series of nested CREATE VIEW ... AS SELECT  
statements, which if combined into the one that effectively runs when  
the first "Aligned_All_Accept" is run, is quite large (larger than  
the sum of its parts). The longest single SQL statement is probably  
the view called "Structured_Amicroe".


For the curious, this set of views modifies the varied source price  
list data from five supplier price lists into one homogeneous view,  
selectively adding calculated fields (such as image URLs) and  
removing records with dodgy data.


Tom

CREATE VIEW Aligned_All_Accept AS
  SELECT * FROM Aligned_All EXCEPT SELECT * FROM Aligned_All_Reject

CREATE VIEW Aligned_All_Reject AS
  SELECT * FROM Aligned_All WHERE Buy = 0 OR Buy * 1.1 > RRP AND RRP  
IS NOT NULL AND RRP > 0


CREATE VIEW Aligned_All AS
SELECT * FROM Aligned_Amicroe
  UNION ALL
SELECT * FROM Aligned_Apple
  UNION ALL
SELECT * FROM Aligned_BlueChipIT
  UNION ALL
SELECT * FROM Aligned_MacSense
  UNION ALL
SELECT * FROM Aligned_PowerMove
  UNION ALL SELECT * FROM Aligned_Simms

CREATE VIEW Aligned_Amicroe AS
  SELECT rowid, Supplier, Category, Category2, Category3, Code,  
Description, NULL AS Detail, NULL AS Thumbnail, NULL AS Image, NULL  
AS Manufacturer, NULL AS URL, NULL AS Warranty, Buy, NULL AS RRP,  
Sell, NULL AS Stock, NULL AS Ordered, NULL AS Due, Modified FROM  
Calculated_Amicroe


CREATE VIEW Aligned_Apple AS
  SELECT rowid, Supplier, Category, Category2, Category3, Code,  
Description, Detail, NULL AS Thumbnail, Image, Manufacturer, NULL AS  
URL, Warranty, Buy, RRP, Sell, Stock, NULL AS Ordered, NULL AS Due,  
Modified FROM Calculated_Apple


CREATE VIEW Aligned_BlueChipIT AS
  SELECT rowid, Supplier, Category, Category2, NULL AS Category3,  
Code, Description, Detail, Thumbnail, Image, Manufacturer, NULL AS  
URL, NULL AS Warranty, Buy, NULL AS RRP, Sell, Stock, Ordered, Due,  
Modified FROM Calculated_BlueChipIT


CREATE VIEW Aligned_MacSense AS
  SELECT rowid, Supplier, Category, Category2, Category3, Code,  
Description, Detail, NULL AS Thumbnail, Image, NULL AS Manufacturer,  
URL, NULL AS Warranty, Buy, RRP, Sell, NULL AS Stock, NULL AS  
Ordered, NULL AS Due, Modified FROM Calculated_MacSense ORDER BY lower 
(Category), lower(Category2), lower(Category3)


CREATE VIEW Aligned_PowerMove AS
  SELECT rowid, Supplier, Category, Category2, NULL AS Category3,  
Code, Description, Detail, NULL AS Thumbnail, Image, NULL AS  
Manufacturer, NULL AS URL, NULL AS Warranty, Buy, RRP, Sell, Stock,  
NULL AS Ordered, NULL AS Due, Modified FROM Calculated_PowerMove


CREATE VIEW Aligned_Simms AS
  SELECT rowid, Supplier, Category, Category2, Category3, Code,  
Description, Detail, NULL AS Thumbnail, Image, Manufacturer, NULL AS  
URL, Warranty, Buy, RRP, Sell, Stock, NULL AS Ordered, NULL AS Due,  
Modified FROM Calculated_Simms


CREATE VIEW Calculated_Amicroe AS
  SELECT rowid, Category, Category2, Category3, Code,  
Products.Description AS Description, Buy, Products.Modified AS  
Modified, 'Amicroe' AS Supplier, Round(Buy * 1.1 * 1.13 + 0.5) AS  
Sell FROM Structured_Amicroe AS Products JOIN Suppliers ON  
Suppliers.Name='Amicroe' WHERE Buy IS NOT NULL AND Buy > 0.0


CREATE VIEW Calculated_Apple AS
  SELECT rowid, Category, Category2, Category3, Code,  
Products.Description AS Description, Detail, CASE WHEN Image LIKE '% 
CPP00869.JPG' OR Image LIKE '%CPP00011.JPG' OR Image LIKE '% 
CPP00014.JPG' OR Image LIKE '%simmsnoimage.jpg' THEN NULL ELSE  
'http://www.simms.com.au/direct/' || Image END AS Image,  
Manufacturer, Warranty, Buy, RRP, Stock, Products.Modified AS  
Modified, 'Apple' AS Supplier, CASE WHEN RRP NOTNULL AND RRP > 0 AND  
Round(Buy * 1.1 * (1 + Markup) + 0.5) > RRP THEN Round((Buy * 1.1 * 2  
+ RRP * 8) / 10) ELSE Round(Buy * 1.1 * (1 + Markup) + 0.5) END AS  
Sell FROM Structured_Apple AS Products JOIN Suppliers ON  
Suppliers.Name='Apple'


CREATE VIEW Calculated_BlueChipIT AS
  SELECT rowid, Category, Category2, Code, Products.Description AS  
Description, Detail, Manufacturer, Buy, Stock, Ordered, Due, 'http:// 
www.bluechipit.com.au/content/_images/' || Code || '_sm.jpg' AS  
Thumbnail, 'http://www.bluechipit.com.au/content/_images/' || Code ||  
'_lg.jpg' AS Image, Products.Modified AS Modified, 'BlueChipIT' AS  
Supplier, Round(Buy * 1.1 * (1 + Markup) + 0.5) AS Sell FROM  
Structured_BlueChipIT AS Products JOIN Suppliers ON  
Suppliers.Name='BlueChipIT' WHERE Category != 'Services'


CREATE VIEW Calculated_MacSense AS
  SELECT rowid, Category, Category2, Category3,