Re: [sqlite] stored procedures implementation for SQLite

[Andy Gibbs]

On Wednesday, January 26, 2011 5:38 PM, Chris Wolf wrote:

> but if anyone is interested, I checked in my work on GitHub, including 
> pre-compiled
> binaries for MacOS and Linux.
>
> http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended
>

Yes, very interesting!

You approached the problem from a different angle to the way I did it.  I 
did it the other way round: created a procedural grammar that could be used 
like a standard statement, then found a way of storing them and then a way 
to call them.  As a result, I stored the procedures just inside 
sqlite_master, rather than having multiple additional tables.  I simply used 
wrappers around the sqlite3_bind_* functions for handling parameters to the 
stored procedures when they were called, rather than holding the parameter 
names in a table.  But I think your way is just as appropriate, and possibly 
better, in the long run.

Additional to calling a stored procedure through a standalone statement 
("EXEC fn(param)"), I also implemented calling through an SQL function (e.g. 
"SELECT exec(fn, param)"), which meant that the stored procedure could 
return a value that would be fed back into another statement/procedure.  Of 
course, that led to the possibility of recursive calls and other 
nightmares...

Anyway, I'll be interested to see how you progress.

Andy


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

[sqlite] Multi-file Atomic Commit issue.

[Dennis Suehr]

>
> So really it is if there are transactions:  The first transaction by SQLite
> completes exactly as it said it would.  Then the second transaction (the
> "rm" command) deletes the ATTACHed database.
>
> If you want to interrupt a transaction, use the "kill -9" command on the
> SQLite process at one of the xSync calls.  You'll find that the transaction
> will be rolled back.  (We do extensive tests of that scenario before each
> release.)
>
>
> --
> D. Richard Hipp
> drh at 
> sqlite.org
>


Fair point.  I should have considered that.

Thanks for your prompt and concise response.

I'll give the 'kill -9' test a quick go.

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

Re: [sqlite] Multi-file Atomic Commit issue.

[Richard Hipp]

On Thu, Jan 27, 2011 at 10:22 AM, Dennis Suehr  wrote:

>
> BEGIN TRANSACTION;
> REPLACE INTO main.Table values (X,Y,Z);
> REPLACE INTO attachedDbName.Table values (X,Y,Z);
> COMMIT TRANSACTION;
>
> Using GDB, I put a breakpoint on the unixSync() function and then call my C
> library function.
> When the breakpoint gets hit, which it does 7 times until my C library
> function runs to completion, I look for the existence of the Master
> Journal,
> which I find as expected in the same directory as my 'main' database.
>
> I then use 'rm' from a Bash shell to remove the attached DB's database file
> and corresponding rollback journal.  Then, I let my function run to
> completion.  Afterwards, I open up the 'main' database and examine the row
> which I 'updated'.  Unfortunately, it has been updated using the above
> mentioned REPLACE statement.


Deleting a database file out from under an active transaction does not
interrupt that transaction.

The "rm" command of unix does not delete files from the disk.  It merely
decrements their reference count.  The files are not removed until the
reference count reaches zero.

While the transaction is in progress, SQLite has the file open and so the
reference count is still 1 even after you do "rm".  Then when SQLite closes
the file, the reference count drops to zero and the file is removed.

So really it is if there are transactions:  The first transaction by SQLite
completes exactly as it said it would.  Then the second transaction (the
"rm" command) deletes the ATTACHed database.

If you want to interrupt a transaction, use the "kill -9" command on the
SQLite process at one of the xSync calls.  You'll find that the transaction
will be rolled back.  (We do extensive tests of that scenario before each
release.)


-- 
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] Multi-file Atomic Commit issue.

[Dennis Suehr]

Hello,

I have been trying to verify SQLite's Multi-file Atomic commit
functionality, but unfortunately have been unable to do so.

My test scenario is as follows:

SQLite 3.7.4 (amalgamation) built into a C library.
Fedora 12.
Two databases residing on an ext4 filesystem.
One database attached to the other using the ATTACH command.

A function in my C library code which creates and calls an SQL command
similar to the following:

BEGIN TRANSACTION;
REPLACE INTO main.Table values (X,Y,Z);
REPLACE INTO attachedDbName.Table values (X,Y,Z);
COMMIT TRANSACTION;

Using GDB, I put a breakpoint on the unixSync() function and then call my C
library function.
When the breakpoint gets hit, which it does 7 times until my C library
function runs to completion, I look for the existence of the Master Journal,
which I find as expected in the same directory as my 'main' database.

I then use 'rm' from a Bash shell to remove the attached DB's database file
and corresponding rollback journal.  Then, I let my function run to
completion.  Afterwards, I open up the 'main' database and examine the row
which I 'updated'.  Unfortunately, it has been updated using the above
mentioned REPLACE statement.  I have repeated this test by deleting the
attached DB after the first breakpoint was hit and also before the last
breakpoint was hit and found the results to be the same.

This appears to contradict the Atomic Commit document in section 5.0 where
it states: "In other words, either all of the database files are updated or
else none of them are."

The reason that I am trying to verify this behaviour is that I plan to store
the attached DB on a RAM disk and therefore need to verify data integrity in
the case of a system crash.

So, am I performing my test incorrectly, or is there something wrong with
SQLite?

Thanks in advance for any and all responses to my query.

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

Re: [sqlite] sqlite3 command line utility

[jeff archer]

From: BareFeetWare list@barefeetware.com
Sent: Thu, January 27, 2011 8:50:15 AM
>
>INSERT INTO "Microscopes" VALUES(null, 'nSpec', 'default');
>
>create temp table "Store" (MicroscopeID);
>
>insert into "Store" select last_insert_rowid();
>
>INSERT INTO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, 
>Description, Magnification, NA, WD) VALUES( 1, (select MicroscopeID from 
>Store), 
>0, '5X'  , 'CFI LU 
>
>Plan Fluor BD 5X'  ,  5.0, 0.15, 18.0);
>
>INSERT INTO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, 
>Description, Magnification, NA, WD) VALUES( 2, (select MicroscopeID from 
>Store), 
>1, '10X' , 'CFI LU 
>
>Plan Fluor BD 10X' ,  10.0, 0.30, 15.0);

Thank you.  This is very convenient.  And seems so obvious now that I have seen 
it.  Not sure why I didn't figure this out on my own.
 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

Re: [sqlite] sqlite3 command line utility

[BareFeetWare]

On 27/01/2011, at 3:30 AM, Jeff Archer wrote:

> INSERT INTO [Microscopes] VALUES(@MicroscopeID, 'nSpec', 'default');
> 
> INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
> Description, Magnification, NA, WD) VALUES( 1, @MicroscopeID, 0, '5X'  , 'CFI 
> LU 
> Plan Fluor BD 5X'  ,   5.0, 0.15, 18.0);

If I understand your objective, I think you can just do it all in SQL, without 
having to re-injecting data an bind from your code:

INSERT INTO "Microscopes" VALUES(null, 'nSpec', 'default');

create temp table "Store" (MicroscopeID);

insert into "Store" select last_insert_rowid();

INSERT INTO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 1, (select MicroscopeID from 
Store), 0, '5X'  , 'CFI LU 
Plan Fluor BD 5X'  ,   5.0, 0.15, 18.0);

INSERT INTO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 2, (select MicroscopeID from 
Store), 1, '10X' , 'CFI LU 
Plan Fluor BD 10X' ,  10.0, 0.30, 15.0);

etc

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] sqlite3 command line utility

[Igor Tandetnik]

Jeff Archer  wrote:
> Is it possible to use parameter binding from the sqlite3 command line utility?

No.

> In my code I have tried making 1 bind to @MicroscopeID and I have tried it 
> with
> appending the digits 1 to 6 and making 6 calls to bind with same result.

You were making these 6 calls to bind on 6 different statement handles, I hope? 
Anyway, you are doing something wrong. Show your code.

You are supposed to insert NULL in Microscopes.MicroscopeID. Since it's INTEGER 
PRIMARY KEY, a unique value will be generated for it. You retrieve this value 
with sqlite3_last_insert_rowid, then bind MicroscopeID parameter to it in all 
the other statements.
-- 
Igor Tandetnik

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

Re: [sqlite] Multiple threads sharing one DB

[Richard Hipp]

On Wed, Jan 26, 2011 at 10:56 AM, Ian Hardingham  wrote:

> Hey guys.
>
> I am under the impression that there is no concurrent access to a single
> SQLite DB.  Ie if thread A is performing a query, and thread B trys to
> query, it will block until thread A is finished, no matter the query.
>
> 1.  Is this correct?
>

It is true if A and B are attempting to share the same database connection.
 Access to the database connection is protected by a mutex.


>
> 2.  Are there any fairly general workarounds of any kind?
>

Use a separate database connection for each thread.  Or better:  Use
processes instead of threads, as threads are evil.


>
> Thanks,
> Ian
> ___
> 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] how to turn off autocommit in C interface?

[Bella Chan]

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

Re: [sqlite] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Bella Chan]

I am already doing the prepare_v2/reset/bind_int/step/finalize set.  Before I 
do more intensive testing to confirm my earlier findings about not much time 
difference between using the separate statement compared with using exec, I 
want to confirm that it is true that I can't do bind_int on integer primary 
key.  

If I do "insert into functions values(?,?)" and use bind_int on the primary 
key, nothing is added to the table.  But if I do "insert into functions 
values(NULL,?)", then the primary key got autoincremented even though I didn't 
declare it as autoincrement and data got added properly:

const char *tablesCreateCmd =
// Create table for functions
"CREATE TABLE functions ("
"function_id integer PRIMARY KEY,"
"function_name varchar2(1000) not null"
");\n"

I am already using the latest sqlite3 source.

Thanks,
Bella

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marian Cascaval
Sent: Wednesday, January 26, 2011 7:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with 
sqlite3_bind_value()?

I'm no expert either in C or in SQLite but what Igor is pointing out is 
extremely important, because I have noticed myself the benefit of using 
sqlite3_prepare_v2() and sqlite3_reset() instead of just using sqlite3_exec() 
within iterations. And this is what SQLite documentation is talking about.

The speed optimization in my C++ program was incredible and the code structure 
was like this (just like Igor's recommendations):

sqlite3_exec(... "BEGIN TRANSACTION" ...);
sqlite3_prepare_v2();
for (int i=0; i<100; i++){
sqlite3_reset();
sqlite3_bind_int();
sqlite3_step();
}
sqlite3_exec(... "COMMIT TRANSACTION" ...);


Sorry if this is too basic and you already knew it, but I felt like sharing my 
basic knowledge :P




Marian Cascaval






From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Wed, January 26, 2011 2:51:38 PM
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with 
sqlite3_bind_value()?

Bella Chan  wrote:
> I am surprised to see that C is slower than Perl when inserting lots of data 
>into DB sequentially as I have 100 columns in a row
> so I have been making 100 bind_int calls while Perl I only need to do execute 
>once.

You are doing something wrong. Are you re-preparing the statement for each row, 
by any chance? Are you grouping your inserts within a transaction? Show some 
code.

> Trying to see if I can use bind_value()
> instead but no clue ho to create the sqlite3_value structure.

sqlite3_bind_value is only useful inside custom functions. In any case, your 
problem lies elsewhere.
-- 
Igor Tandetnik

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



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

[sqlite] sqlite3 command line utility

[Jeff Archer]

Is it possible to use parameter binding from the sqlite3 command line utility?
I would like to make a small illustration of my problem but it only seems to 
have an issue with I am doing an insert and using a bound parameter for a 
column 
which is a foreign key.
I have foreign key on by: PRAGMA foreign_keys = ON;

BEGIN IMMEDIATE;

CREATE TABLE IF NOT EXISTS [Microscopes] 
(MicroscopeID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,Name VARCHAR NOT NULL
,Description  VARCHAR
);

CREATE TABLE IF NOT EXISTS [Objectives] 
(ObjectiveID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,MicroscopeID INTEGER NOT NULL REFERENCES [Microscopes] DEFERRABLE 
INITIALLY 
DEFERRED
,TurretPos    INTEGER
,Name VARCHAR NOT NULL
,Description  VARCHAR NOT NULL
,Magnification    REAL NOT NULL
,NA   REAL NOT NULL
,WD   REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS [Calibrations] 
(CalibrationID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,ObjectiveID   INTEGER NOT NULL REFERENCES [Objectives] DEFERRABLE INITIALLY 
DEFERRED
,MicroscopeID  INTEGER NOT NULL REFERENCES [Microscopes] DEFERRABLE INITIALLY 
DEFERRED
,Timestamp DATETIME NOT NULL UNIQUE
,Description   VARCHAR
);

INSERT INTO [Microscopes] VALUES(@MicroscopeID, 'nSpec', 'default');

INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 1, @MicroscopeID, 0, '5X'  , 'CFI 
LU 
Plan Fluor BD 5X'  ,   5.0, 0.15, 18.0);
INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 2, @MicroscopeID, 1, '10X' , 'CFI 
LU 
Plan Fluor BD 10X' ,  10.0, 0.30, 15.0);
INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 3, @MicroscopeID, 2, '20X' , 'CFI 
LU 
Plan Fluor BD 20X' ,  20.0, 0.45,  4.5);
INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 4, @MicroscopeID, 3, '50X' , 'CFI 
LU 
Plan Fluor BD 50X' ,  50.0, 0.80,  1.0);
INSERT INTO [Objectives](ObjectiveID, MicroscopeID, TurretPos, Name, 
Description, Magnification, NA, WD) VALUES( 5, @MicroscopeID, 4, '100X', 'CFI 
LU 
Plan Fluor BD 100X', 100.0, 0.90,  1.0);

COMMIT;

The issue is with the @MicroscopeID in the 5 inserts into the Objectives 
table.  
If I remove the foreign key constraint on MicroscopeID in the Objectives 
table I 
get MicroscopeID of
1
NULL
NULL
NULL
NULL

My version is 3.7.3.

I have SQLite Expert and have tried same SQL and it appears to work correctly 
after prompting me for a value of @MicroscopeID 6 times.

In my code I have tried making 1 bind to @MicroscopeID and I have tried it with 
appending the digits 1 to 6 and making 6 calls to bind with same result.

My app is 64 bit and SQLite Expert is 32 bit.

Any thought or suggestion will be appreciated as I am not sure how to proceed.  


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] Multiple threads sharing one DB

[Ian Hardingham]

Hey guys.

I am under the impression that there is no concurrent access to a single 
SQLite DB.  Ie if thread A is performing a query, and thread B trys to 
query, it will block until thread A is finished, no matter the query.

1.  Is this correct?

2.  Are there any fairly general workarounds of any kind?

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

Re: [sqlite] forcing flush of journal

[Paul Sanderson]

Thats what I thought, thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users