[sqlite] append to a column?

[Yang]

I wonder if I can do this efficiently in sqlite:

for a row, and one column (most likely blob type),  I want to append
some value to the end of the blob, I do this many times. then I may
read up all the appended sections and write out the entire blob with a
new value


without any special support, I can read existing blob, add some new
data to it, and update the column with the new blob value.
but because most incremental updates are small, reading the majority
of the old blob is a waste of time.

ideally this is a sparse table ( each append creates a new "column",
there is no limit on how many columns each row could have, or any
schema for columns, for that matter). But I can't find an
off-the-shelf solution for sparse tables. (can't use Columnar  DB,
since my access pattern is still first based on rows)


Thanks a lot

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

Re: [sqlite] append to a column?

[Kees Nuyt]

On Thu, 18 Nov 2010 01:45:22 -0800, Yang 
wrote:

>I wonder if I can do this efficiently in sqlite:
>
>for a row, and one column (most likely blob type),  I want to append
>some value to the end of the blob, I do this many times. then I may
>read up all the appended sections and write out the entire blob with a
>new value
>
>
>without any special support, I can read existing blob, add some new
>data to it, and update the column with the new blob value.
>but because most incremental updates are small, reading the majority
>of the old blob is a waste of time.
>
>ideally this is a sparse table ( each append creates a new "column",
>there is no limit on how many columns each row could have, or any
>schema for columns, for that matter). But I can't find an
>off-the-shelf solution for sparse tables. (can't use Columnar  DB,
>since my access pattern is still first based on rows)

A variable number of columns is not a good design:

1) It smells like a repeating group or array, which is not correct
for a relational schema

2) Changing a schema on the fly is a bad idea, as it invalidates all
sqlite3_prepared() statements.

3) All rows would have the number of columns needed by the row with
the highest number of updates, most of them would be NULL

Repeating groups and arrays can be prevented by adding a row for
every append action. In order to do so, expand the primary key of
the row with a fragment sequence number (or date/timestamp).

CREATE TABLE Accumulatedblobs (
identity INTEGER,
fragmentseq  INTEGER,
fragment BLOB,
PRIMARY KEY (identity,fragmentseq)
);

If the number of rows becomes a concern you could run a background
optimization process to periodically collect all fragment rows with
the same identity into one.

Interesting to read:
http://www.thethirdmanifesto.com/ 
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to make this calculation in VIEW

[Jeff Archer]

First let me say thank you to all for the very good support that receive here.

I have the these tables and view

// Patterns table
CREATE TABLE IF NOT EXISTS [Patterns] (
PatternID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Name  VARCHAR NOT NULL UNIQUE,
Description   VARCHAR NOT NULL,
Origin_X  REAL NOT NULL DEFAULT 0.0,
Origin_Y  REAL NOT NULL DEFAULT 0.0,
wMicrons  REAL NOT NULL DEFAULT 0.0,
hMicrons  REAL NOT NULL DEFAULT 0.0,
wPixels   INTEGER NOT NULL DEFAULT 0.0,
hPixels   INTEGER NOT NULL DEFAULT 0.0
);

// Tiles table
CREATE TABLE IF NOT EXISTS [Tiles] (\n"
TileID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY 
DEFERRED,\n"
Offset_X  REAL NOT NULL DEFAULT 0.0,\n"
Offset_Y  REAL NOT NULL DEFAULT 0.0\n"
);

// vwPatterns
CREATE VIEW IF NOT EXISTS [vwPatterns] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.Description 
FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

I would like to add these calculated columns to my view but not sure how to 
make 
this work. 

   Patterns.wMicrons * nTilesX AS wTotalMicrons,
   Patterns.hMicrons * nTilesY AS hTotalMicrons,
   Patterns.wPixels * nTilesX AS wTotalPixels,
   Patterns.hPixels * nTilesY AS hTotalPixels,

i.e.
// vwPatterns
CREATE VIEW IF NOT EXISTS [vwPatterns] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.wMicrons * nTilesX AS wTotalMicrons,
   Patterns.hMicrons * nTilesY AS hTotalMicrons,
   Patterns.wPixels * nTilesX AS wTotalPixels,
   Patterns.hPixels * nTilesY AS hTotalPixels,
   Patterns.Description 
FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

But it get error: "no such column: nTilesX"

It works if I substitute a constant for the nTilesX and nTilesY so I think I do 
not know how to correctly reference these.

Thank you.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using foreign key reference on RowID

[Bernard Ertl]

I'm getting a "foreign key mismatch" error with the following code:

~~~

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE);

CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES 
JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL);

INSERT INTO JobPlans(Name) VALUES ('234234');

# Following line generates the error:
INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ');
~~~

If I explicitly declare an alias for the RowID:

CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name UNIQUE);

I don't get the error.  Is it not possible to reference the SQLite 
internal/default column for the RowID in a foreign key definition?  The online 
docs should be updated to reflect this.

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

Re: [sqlite] Selective update of a column

[Amit Chaudhuri]

Simon - thanks v. much.  Makes complete sense now - and it does precisely
what I wanted. :)
{Appreciate the tip on the index - performance hasn't been an issue yet but
I'm sure I'll end up using it.}

On Wed, Nov 17, 2010 at 12:51 PM, Simon Slavin  wrote:

>
> On 16 Nov 2010, at 3:29pm, Amit Chaudhuri wrote:
>
> > The approach I tried was to perform 2 sequential updates using first the
> > fixed call source table then the mobile. I tried to restrict the update
> to
> > rows in target where the class of call (fixed or mobile) corresponded to
> the
> > content of the source.
> >
> > What seems to happen is that the second update blats the updates
> performed
> > by the first: I can have either fixed results or mobile results but not
> > both.
>
> You /nearly/ got it right.  Your commands were
>
>update target set cost = (select cost from source1 where
> source1.Aend=target.Aend and source1.type=target.type and FM='Fixed');
>
>update target set cost = (select cost from source2 where
> source2.Aend=target.Aend and source2.type=target.type and FM='Mobile');
>
> Your problem is that you have the clause about FM in the wrong part.  The
> FM column is in the table you're updating, not in the source.  So the clause
> about FM should be part of the UPDATE command, not part of the SELECT.  Your
> code should look more like
>
>UPDATE target SET cost = (SELECT cost FROM source1 WHERE
> source1.Aend=target.Aend AND source1.type=target.type) WHERE FM='Fixed';
>
>UPDATE target SET cost = (SELECT cost FROM source2 WHERE
> source2.Aend=target.Aend AND source2.type=target.type) WHERE FM='Mobile';
>
> I haven't tried the above code but I hope it might point you in the right
> direction.
>
> To make things fast, don't forget to index your source* tables on
> (Aend,type) or something like that.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] threads and last_insert_rowid()

[Jay A. Kreibich]

On Tue, Nov 16, 2010 at 04:08:16PM +0300, Ruslan Mullakhmetov scratched on the 
wall:
>   i got following contradictory replies to my question
> 
> > if i execute query like " insert into tbl( filed ) VALUES ( 1 ); Select
> > last_insert_rowid() as li;" would be it atomic? or  it anyway would suffer
> > from threads?
> 
> from borgan:
> > Hi, i think this will probably be "atomic".
> > What i mean is the transaction will aquire EXCLUSIVE lock (because of
> > the insert command) and it will keep this lock until commit, which
> > takes place after the last select and last semicolon.
> >
> > So i believe no other threads should be able to do anything with your
> > db until the whole query finishes.
> and from Kees Nuyt:
> > The last_insert_rowid() function returns the ROWID of the last row
> > insert from the database connection which invoked the function.
> > So, yes, it suffers from threads if the threads use the same
> > database connection.
> 
> whom to trust?

  The docs, which make this fairly clear:

  http://sqlite.org/lang_corefunc.html#last_insert_rowid

The last_insert_rowid() function returns the ROWID of the last row
insert from the database connection which invoked the function. The
last_insert_rowid() SQL function is a wrapper around the
sqlite3_last_insert_rowid() C/C++ interface function. 

  http://sqlite.org/c3ref/last_insert_rowid.html

[...]
This routine returns the rowid of the most recent successful INSERT
into the database from the database connection in the first
argument. If no successful INSERTs have ever occurred on that
database connection, zero is returned.
[...]

  So Kees Nuyt is correct... the value returned by last_insert_rowid()
  is "per database connection".  It is not a global.  It is not a
  per-database-file value.  It is not a pre-thread value.
  
  If each thread is using its own, private database connection, then
  you're safe.  If multiple threads are sharing a database connection
  and using the "serialized" thread mode, then you have a race
  condition as it is possible for the database connection to be
  handed off between statements.  The value returned by
  last_insert_rowid() may be invalid by the time it is called.

  In either case, these two operations are never "atomic", in the
  fullest sense of that word.  last_insert_rowid() is not bounded by
  transactions in the way statement processing is.

> as for Kees Nuyt reply, did you toke int account that "select 
> last_insert_rowid() "
> and insert query combined in single query and executed via single call 
> of sqlite3_exec()?

  Using sqlite3_exec() changes nothing.  It just runs the normal
  prepare/step interfaces.  It does not provide any kind of atomic
  processing above and beyond autocommit.  It doesn't even wrap
  multiple statements in a transaction.  It is just a syntax
  short-cut and provides no significant functionality beyond
  convenience.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to make this calculation in VIEW

[Simon Davies]

On 17 November 2010 22:13, Jeff Archer  wrote:
> First let me say thank you to all for the very good support that receive here.
>
> I have the these tables and view
>
.
.
.
>
> I would like to add these calculated columns to my view but not sure how to 
> make
> this work.
>
>    Patterns.wMicrons * nTilesX AS wTotalMicrons,
>    Patterns.hMicrons * nTilesY AS hTotalMicrons,
>    Patterns.wPixels * nTilesX AS wTotalPixels,
>    Patterns.hPixels * nTilesY AS hTotalPixels,
>
> i.e.
> // vwPatterns
> CREATE VIEW IF NOT EXISTS [vwPatterns] AS
> SELECT Patterns.Name AS Pattern,
>    Patterns.Origin_X,
>    Patterns.Origin_Y,
>    Patterns.wMicrons,
>    Patterns.hMicrons,
>    COUNT(DISTINCT Offset_X) AS nTilesX,
>    COUNT(DISTINCT Offset_Y) AS nTilesY,
>    Patterns.wPixels,
>    Patterns.hPixels,
>    Patterns.wMicrons * nTilesX AS wTotalMicrons,
>    Patterns.hMicrons * nTilesY AS hTotalMicrons,
>    Patterns.wPixels * nTilesX AS wTotalPixels,
>    Patterns.hPixels * nTilesY AS hTotalPixels,
>    Patterns.Description
> FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID
> GROUP BY Tiles.PatternID;
>
> But it get error: "no such column: nTilesX"

CREATE VIEW IF NOT EXISTS [vwPatterns] AS
SELECT Patterns.Name AS Pattern,
   Patterns.Origin_X,
   Patterns.Origin_Y,
   Patterns.wMicrons,
   Patterns.hMicrons,
   COUNT(DISTINCT Offset_X) AS nTilesX,
   COUNT(DISTINCT Offset_Y) AS nTilesY,
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.wMicrons * COUNT(DISTINCT Offset_X) AS wTotalMicrons,
   Patterns.hMicrons * COUNT(DISTINCT Offset_Y) AS hTotalMicrons,
   Patterns.wPixels * COUNT(DISTINCT Offset_X) AS wTotalPixels,
   Patterns.hPixels * COUNT(DISTINCT Offset_Y) AS hTotalPixels,
   Patterns.Description
FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID
GROUP BY Tiles.PatternID;

>
> It works if I substitute a constant for the nTilesX and nTilesY so I think I 
> do
> not know how to correctly reference these.
>
> Thank you.
>
> Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> <330>819.4615

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

Re: [sqlite] How to make this calculation in VIEW

[O'Neill, Owen]

Hi Jeff, 
I haven't actually tried it, but just by inspection I would guess that a view 
can't refer to another column within itself, so there are 2 options.

Create a second view on top of the first view. (I've not tested this - note how 
the view name is aliased to just 'Patterns' because I'm lazy)

CREATE VIEW IF NOT EXISTS [vwPatterns2] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.wMicrons * nTilesX AS wTotalMicrons,
   Patterns.hMicrons * nTilesY AS hTotalMicrons,
   Patterns.wPixels * nTilesX AS wTotalPixels,
   Patterns.hPixels * nTilesY AS hTotalPixels,
   Patterns.Description 
FROM Tiles INNER JOIN vwPatterns AS Patterns ON Tiles.PatternID = 
vwPatterns.PatternID 
GROUP BY Tiles.PatternID;

Or change the definition of the column within the view to..
   Patterns.wMicrons * COUNT(DISTINCT Offset_X) AS wTotalMicrons,

Hmm will this work ? try it and see !

Cheers
Owen



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jeff Archer
Sent: Wednesday, November 17, 2010 10:14 PM
To: SQLite-user.org
Subject: [sqlite] How to make this calculation in VIEW

First let me say thank you to all for the very good support that receive here.

I have the these tables and view

// Patterns table
CREATE TABLE IF NOT EXISTS [Patterns] (
PatternID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Name  VARCHAR NOT NULL UNIQUE,
Description   VARCHAR NOT NULL,
Origin_X  REAL NOT NULL DEFAULT 0.0,
Origin_Y  REAL NOT NULL DEFAULT 0.0,
wMicrons  REAL NOT NULL DEFAULT 0.0,
hMicrons  REAL NOT NULL DEFAULT 0.0,
wPixels   INTEGER NOT NULL DEFAULT 0.0,
hPixels   INTEGER NOT NULL DEFAULT 0.0
);

// Tiles table
CREATE TABLE IF NOT EXISTS [Tiles] (\n"
TileID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
PatternID INTEGER NOT NULL REFERENCES [Patterns] DEFERRABLE INITIALLY 
DEFERRED,\n"
Offset_X  REAL NOT NULL DEFAULT 0.0,\n"
Offset_Y  REAL NOT NULL DEFAULT 0.0\n"
);

// vwPatterns
CREATE VIEW IF NOT EXISTS [vwPatterns] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.Description 
FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

I would like to add these calculated columns to my view but not sure how to 
make 
this work. 

   Patterns.wMicrons * nTilesX AS wTotalMicrons,
   Patterns.hMicrons * nTilesY AS hTotalMicrons,
   Patterns.wPixels * nTilesX AS wTotalPixels,
   Patterns.hPixels * nTilesY AS hTotalPixels,

i.e.
// vwPatterns
CREATE VIEW IF NOT EXISTS [vwPatterns] AS 
SELECT Patterns.Name AS Pattern, 
   Patterns.Origin_X, 
   Patterns.Origin_Y, 
   Patterns.wMicrons, 
   Patterns.hMicrons, 
   COUNT(DISTINCT Offset_X) AS nTilesX, 
   COUNT(DISTINCT Offset_Y) AS nTilesY, 
   Patterns.wPixels,
   Patterns.hPixels,
   Patterns.wMicrons * nTilesX AS wTotalMicrons,
   Patterns.hMicrons * nTilesY AS hTotalMicrons,
   Patterns.wPixels * nTilesX AS wTotalPixels,
   Patterns.hPixels * nTilesY AS hTotalPixels,
   Patterns.Description 
FROM Tiles INNER JOIN Patterns ON Tiles.PatternID = Patterns.PatternID 
GROUP BY Tiles.PatternID;

But it get error: "no such column: nTilesX"

It works if I substitute a constant for the nTilesX and nTilesY so I think I do 
not know how to correctly reference these.

Thank you.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615 
___
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] append to a column?

[Igor Tandetnik]

Yang  wrote:
> for a row, and one column (most likely blob type),  I want to append
> some value to the end of the blob, I do this many times. then I may
> read up all the appended sections and write out the entire blob with a
> new value

BLOB API:

http://www.sqlite.org/c3ref/blob_open.html

-- 
Igor Tandetnik

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

[sqlite] select where value in

[Joseph Garry]

I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a 
query like
'select tabid, col1, col2 from table where tabid in (?)'
But how do I bind in the parameter here? An example would be welcome, of 
course.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select where value in

[Igor Tandetnik]

Joseph Garry  wrote:
> I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a 
> query like
> 'select tabid, col1, col2 from table where tabid in (?)'
> But how do I bind in the parameter here? An example would be welcome, of 
> course.

You can't do that directly. One way is to create a temp table, like this:

create temp table TabIdList (tabid integer);

Now you can prepare this statement:

select tabid, col1, col2 from myTable where tabid in (select tabid from 
TabIdList);

Populate TabIdList with your list of IDs (you can use a prepared statement for 
this, too, along the lines of "insert into TabIdList(tabid) values (?); ").
-- 
Igor Tandetnik

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

Re: [sqlite] select where value in

[Joseph Garry]

That would work, but the thing I'm after here is speed. And I can't imagine 
what you're suggesting would be very fast. Am I mistaken?
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Thu, 18 Nov 2010 08:08:51 -0500
> Subject: Re: [sqlite] select where value in
> 
> Joseph Garry  wrote:
> > I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a 
> > query like
> > 'select tabid, col1, col2 from table where tabid in (?)'
> > But how do I bind in the parameter here? An example would be welcome, of 
> > course.
> 
> You can't do that directly. One way is to create a temp table, like this:
> 
> create temp table TabIdList (tabid integer);
> 
> Now you can prepare this statement:
> 
> select tabid, col1, col2 from myTable where tabid in (select tabid from 
> TabIdList);
> 
> Populate TabIdList with your list of IDs (you can use a prepared statement 
> for this, too, along the lines of "insert into TabIdList(tabid) values (?); 
> ").
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select where value in

[Simon Slavin]

On 18 Nov 2010, at 1:22pm, Joseph Garry wrote:

>> From: itandet...@mvps.org
>> Joseph Garry  wrote:
>>> I'd like to set up a compiled sqllite (sqlite3_prepare_v2) statement with a 
>>> query like
>>> 'select tabid, col1, col2 from table where tabid in (?)'
>>> But how do I bind in the parameter here? An example would be welcome, of 
>>> course.
>> 
>> You can't do that directly. One way is to create a temp table, like this:
>> 
>> create temp table TabIdList (tabid integer);
>> 
>> Now you can prepare this statement:
>> 
>> select tabid, col1, col2 from myTable where tabid in (select tabid from 
>> TabIdList);
>> 
>> Populate TabIdList with your list of IDs (you can use a prepared statement 
>> for this, too, along the lines of "insert into TabIdList(tabid) values (?); 
>> ").

> That would work, but the thing I'm after here is speed. And I can't imagine 
> what you're suggesting would be very fast. Am I mistaken?

Put an index on the 'tabid' field of myTable and it'll be pretty fast.

(PS: I fixed the text order of your reply.)

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

Re: [sqlite] Using foreign key reference on RowID

[Kees Nuyt]

On Wed, 17 Nov 2010 16:36:12 -0600, "Bernard Ertl"
 wrote:

>I'm getting a "foreign key mismatch" error with the following code:
>
>~~~
>
>PRAGMA foreign_keys = ON;
>
>CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE);
>
>CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES 
>JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL);
>
>INSERT INTO JobPlans(Name) VALUES ('234234');
>
># Following line generates the error:
>INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ');
>~~~
>
> If I explicitly declare an alias for the RowID:
>
>CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name UNIQUE);
>
> I don't get the error.  Is it not possible
> to reference the SQLite internal/default column
> for the RowID in a foreign key definition? 

No it isn't. In general you can't refer to anything that is not part
of your schema.

> The online docs should be updated to reflect this.

Disputable, as this is not specific for SQLite, it's part of SQL.
The fact that ROWID is something hidden is documented well enough.


The definition:
CREATE TABLE JobPlans (
id INTEGER PRIMARY KEY NOT NULL,
Name UNIQUE
);
is physically the same as 
CREATE TABLE JobPlans (
Name UNIQUE
);
anyway, so what's the problem defining the alias?
Using the alias is much more portable.

Note that the rowid alias doesn't have to be called RowID at all.

CREATE TABLE IF NOT EXISTS Tasks (
JobPlan_ID INTEGER NOT NULL 
REFERENCES JobPlans(id)
ON DELETE CASCADE,
UID UNIQUE NOT NULL
);
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using foreign key reference on RowID

[Jay A. Kreibich]

On Wed, Nov 17, 2010 at 04:36:12PM -0600, Bernard Ertl scratched on the wall:

> Is it not possible to reference the SQLite
> internal/default column for the RowID in a foreign key definition?

  Even if you could, you don't want to do this.  
  
  Unless you define an ROWID alias (i.e. an INTEGER PRIMARY KEY column)
  ROWID values are not preserved across vacuums or dumps.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using foreign key reference on RowID

[Bernard Ertl]

As someone who just started using SQLite without any previous background in
SQL, it was confusing to me.  I did a search on nabble through this mailing
list and see now that I'm not the first person to ask about this issue. 
IMO, it would be helpful to people new to SQLite to mention this in the docs
on the foreign key support page:

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

It's not clear from the CREATE TABLE page in the docs:

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

if there are any performance issues or other considerations in defining an
alias to the rowid.  The text there doesn't really offer any reason to
someone new to the system to use an alias.  Seems like a duplication of work
for no benefit (because the benefits aren't clearly explained).  Maybe this
text should be updated so people are encouraged to use an alias instead of
the 'hidden' column.



Kees Nuyt wrote:
> 
> On Wed, 17 Nov 2010 16:36:12 -0600, "Bernard Ertl"
>  wrote:
> 
>>I'm getting a "foreign key mismatch" error with the following code:
>>
>>~~~
>>
>>PRAGMA foreign_keys = ON;
>>
>>CREATE TABLE IF NOT EXISTS JobPlans (Name UNIQUE);
>>
>>CREATE TABLE IF NOT EXISTS Tasks (JobPlan_ID INTEGER NOT NULL REFERENCES
JobPlans(RowID) ON DELETE CASCADE, UID UNIQUE NOT NULL);
>>
>>INSERT INTO JobPlans(Name) VALUES ('234234');
>>
>># Following line generates the error:
>>INSERT INTO Tasks(JobPlan_ID,UID) VALUES (1,'ZZZ');
>>~~~
>>
>> If I explicitly declare an alias for the RowID:
>>
>>CREATE TABLE IF NOT EXISTS JobPlans (RowID INTEGER PRIMARY KEY, Name
UNIQUE);
>>
>> I don't get the error.  Is it not possible
>> to reference the SQLite internal/default column
>> for the RowID in a foreign key definition? 
> 
> No it isn't. In general you can't refer to anything that is not part
> of your schema.
> 
>> The online docs should be updated to reflect this.
> 
> Disputable, as this is not specific for SQLite, it's part of SQL.
> The fact that ROWID is something hidden is documented well enough.
> 
> 
> The definition:
> CREATE TABLE JobPlans (
>   id INTEGER PRIMARY KEY NOT NULL,
>   Name UNIQUE
> );
> is physically the same as 
> CREATE TABLE JobPlans (
>   Name UNIQUE
> );
> anyway, so what's the problem defining the alias?
> Using the alias is much more portable.
> 
> Note that the rowid alias doesn't have to be called RowID at all.
> 
> CREATE TABLE IF NOT EXISTS Tasks (
>   JobPlan_ID INTEGER NOT NULL 
>   REFERENCES JobPlans(id)
>   ON DELETE CASCADE,
>   UID UNIQUE NOT NULL
> );
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Using-foreign-key-reference-on-RowID-tp30246958p30248826.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] Using foreign key reference on RowID

[Bernard Ertl]

Wow.  Thanks.  I see now that this is mentioned in the docs on the page for
the VACUUM statement.  It really should be mentioned on the CREATE TABLE
page also where the rowid is explained.  This is important information for
people who are learning SQLite and trying to figure out how to design their
database!


Jay A. Kreibich-2 wrote:
> 
> On Wed, Nov 17, 2010 at 04:36:12PM -0600, Bernard Ertl scratched on the
> wall:
> 
>> Is it not possible to reference the SQLite
>> internal/default column for the RowID in a foreign key definition?
> 
>   Even if you could, you don't want to do this.  
>   
>   Unless you define an ROWID alias (i.e. an INTEGER PRIMARY KEY column)
>   ROWID values are not preserved across vacuums or dumps.
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Using-foreign-key-reference-on-RowID-tp30246958p30248863.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] Read database from multiple processes

[Prakash Reddy Bande]

Hi,

I have a database placed on a shared drive.
Two processes (from different hosts) do a bunch of select commands.

I have a busy handler that sleeps of 1 second in each attempt and bails out 
after 10 attempts.

The observation is that, if only one process is running (on any host) the 
results are returned pretty fast. However, if both processes are doing the 
selects concurrently the performance is dead slow.
Note that each process is doing somewhere around 5000 selects. My understanding 
is that when only selects are happening there is no locking involved.

I am using 3.7.2.

Regards,

Prakash Bande
Altair Engg. Inc.
Troy MI
Ph: 248-614-2400 ext 489
Cell: 248-404-0292

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

Re: [sqlite] Read database from multiple processes

[Teg]

Hello Prakash,

What kind of drive is it? My experience is that SATA drives hate being
banged on from multiple threads. They just can't handle multiple
simultaneous work loads without slowing down drastically. They're fine
with heavy duty single threaded IO. In my application, I serialize
most heavy duty disk IO for this reason. If the drive is hanging off a
USB interface, it's much worse.

In your case, I might consider a "server" application that answers
requests from the clients so, only one process is banging on the disk
at a time.

C

Thursday, November 18, 2010, 4:21:04 PM, you wrote:

PRB> Hi,

PRB> I have a database placed on a shared drive.
PRB> Two processes (from different hosts) do a bunch of select commands.

PRB> I have a busy handler that sleeps of 1 second in each attempt
PRB> and bails out after 10 attempts.

PRB> The observation is that, if only one process is running (on any
PRB> host) the results are returned pretty fast. However, if both
PRB> processes are doing the selects concurrently the performance is dead slow.
PRB> Note that each process is doing somewhere around 5000 selects.
PRB> My understanding is that when only selects are happening there is no 
locking involved.

PRB> I am using 3.7.2.

PRB> Regards,

PRB> Prakash Bande
PRB> Altair Engg. Inc.
PRB> Troy MI
PRB> Ph: 248-614-2400 ext 489
PRB> Cell: 248-404-0292

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



-- 
Best regards,
 Tegmailto:t...@djii.com

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

[sqlite] Custom collating sequences and performance

[Duquette, William H (316H)]

Suppose I define a custom collating sequence FOO, and use it on an indexed 
column:

   CREATE TABLE mytable (mykey TEXT PRIMARY KEY COLLATE FOO, ...)

It seems to me that it shouldn't be necessary for SQLite to evaluate FOO's 
comparison function when doing queries on mytable; the collation order should 
be implicit in the mykey column's index.  Is this in fact the case?

Thanks!

Will


--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] Custom collating sequences and performance

[Drake Wilson]

Quoth "Duquette, William H (316H)" , on 
2010-11-18 14:08:10 -0800:
> It seems to me that it shouldn't be necessary for SQLite to evaluate
> FOO's comparison function when doing queries on mytable; the
> collation order should be implicit in the mykey column's index.  Is
> this in fact the case?

When doing which queries?

How do you propose to look up a key value in the index without using
the collation function?

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

Re: [sqlite] Custom collating sequences and performance

[Duquette, William H (316H)]

On 11/18/10 2:16 PM, "Drake Wilson"  wrote:

Quoth "Duquette, William H (316H)" , on 
2010-11-18 14:08:10 -0800:
> It seems to me that it shouldn't be necessary for SQLite to evaluate
> FOO's comparison function when doing queries on mytable; the
> collation order should be implicit in the mykey column's index.  Is
> this in fact the case?

When doing which queries?

How do you propose to look up a key value in the index without using
the collation function?

I was thinking of queries like

SELECT mykey FROM mytab ORDER BY mykey

But yes, looking up a particular record would have
to use the collation function function, wouldn't it.

Will

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

[sqlite] UPDATE during SELECT

[Nikolaus Rath]

Hello,

I understand that running INSERT or DELETE during an active SELECT query
can get me into trouble. But is it safe to run (in pseudocode):

for value in "SELECT main_column IN mytable":

   UPDATE mytable SET other_column='foobar' WHERE main_column=value

?


Thanks,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE during SELECT

[Igor Tandetnik]

Nikolaus Rath  wrote:
> I understand that running INSERT or DELETE during an active SELECT query
> can get me into trouble. But is it safe to run (in pseudocode):
> 
> for value in "SELECT main_column IN mytable":
> 
>   UPDATE mytable SET other_column='foobar' WHERE main_column=value

It should be safe. Though I don't see how this would be different than just 
updating all rows in a single query:

UPDATE mytable SET other_column='foobar';

-- 
Igor Tandetnik


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

Re: [sqlite] Custom collating sequences and performance

[Dan Kennedy]

On 11/19/2010 05:22 AM, Duquette, William H (316H) wrote:
> On 11/18/10 2:16 PM, "Drake Wilson"  wrote:
>
> Quoth "Duquette, William H (316H)", on 
> 2010-11-18 14:08:10 -0800:
>> It seems to me that it shouldn't be necessary for SQLite to evaluate
>> FOO's comparison function when doing queries on mytable; the
>> collation order should be implicit in the mykey column's index.  Is
>> this in fact the case?
>
> When doing which queries?
>
> How do you propose to look up a key value in the index without using
> the collation function?
>
> I was thinking of queries like
>
>  SELECT mykey FROM mytab ORDER BY mykey

The collation sequence callback will not be invoked in that
case. SQLite will just iterate from start to finish of the
index b-tree. You do need to have the collation sequence
registered when compiling the query though.

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

[sqlite] creating a sqlite db on raw disk ?

[Yang]

when I create a db on a file system, I guess a query process has to go
through 2 levels of seeks ?
first sqlite finds the B-tree node that stores the index to the file
offset of my desired record, then sqlite uses that offset to make
syscall seek(offset),
then Kernel consults the FS implementation to find from its  OWN
B-tree (for example in ext3 fs )  the block location of that offset.


innodb allows creating a db on a raw disk partition, can we do the
same on  sqlite?

I tried directly creating a db on ramdisk, failed:


javasqlite-20100727# sqlite3 /dev/ram0
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table blah ( x int );
Error: disk I/O error


note that I was able to create an e2fs on the same ramdisk

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