Re: [sqlite] Statements that return a result set?

[Dan Kennedy]

On Jul 30, 2009, at 5:12 PM, Robert Villanoa wrote:

> Hi folk,
>
> I am new to SQLite, and I have a question: In SQLite, is there a  
> statement apart from SELECT statement that can return a result set?  
> Or the SELECT statement is the only statement that can return a  
> result set?


As well as the answers from other posters, there is this:

   http://www.sqlite.org/pragma.html#pragma_count_changes

Setting this pragma causes INSERT/UPDATE/DELETE to return
a result set of 1 row.


>
> Thank you for reading my question!
>
> Robert.
>
>
>
>
> ___
> 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] Multi-master replication with updated Versioning?extension

[Simon Slavin]

On 30 Jul 2009, at 6:19pm, Jay A. Kreibich wrote:

> You're not really supposed to
>  dump and restore a multi-master system.

We're back to talking about synchronising different copies of the  
database again, aren't we ?

Dumping and restoring a multi-master system means restoring the entire  
context of all the copies of the database.  You have to not only  
restore the contents of one copy of the database, but also restore the  
state of all other copies.  And if you're using a journaling method to  
manage your synchrony, restore the state of all the journals too.

The only exception to this is if the dump was done when all sites  
agreed that they all had completely synchronised up-to-date copies of  
the database.  And many multi-master databases are never in that  
state.  So the question is 'what was dumped, and what state was it in' ?

So the next question is how your journaling system was designed.  You  
can design a journaling and synchronising system so that any site(s)  
can restore from a backup at any time, and the next time everyone  
synchronises they'll get their best data.  But often this is not  
considered when the system is designed and the system loses data or  
worse still corrupts everyone else's.

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

Re: [sqlite] Looking for a w_char alternative to the sqlite3_mprintf famili of functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

A.J.Millan wrote:
> Actually I have an application that regularly uses expresions as:
> 
> char *zUserName = "My name";
> char *zSQL = sqlite3_mprintf ("INSERT INTO table VALUES('%q')", zUserName);

Why aren't you using bound parameters?  It is far harder to do what you are
doing above, less efficient and has the potential for SQL injection attacks
(unless you remembered %q everywhere!)

These are the functions you need:

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkpx9jcACgkQmOOfHg372QSlIgCgxyWJHzHtQQHhiceb9eciQysy
Z/IAoIsxN3X20AY3L8YhD+tk3vW36s1N
=LZ6U
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE ststement not working as expected

[John Loveys]

Thanks Shane, but I do not believe that is the issue.  I bind these values
in my INSERT statements and it works fine.  Otherwise I wouldn't have a
DELETE problem, would I?  ;)  Also, the values are acceptable if I try my
DELETE at the SQLite command prompt.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shane Harrelson
Sent: Thursday, July 30, 2009 3:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] DELETE ststement not working as expected

I believe sqlite3_bind_int64() takes a signed int for the value to bind.
You state you are passing it a large unsigned int.   If the unsigned value
is large enough, it will appear to be a negative signed int.

HTH.
-Shane
___
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] DELETE ststement not working as expected

[Shane Harrelson]

I believe sqlite3_bind_int64() takes a signed int for the value to bind.
You state you are passing it a large unsigned int.   If the unsigned value
is large enough, it will appear to be a negative signed int.

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

[sqlite] DELETE ststement not working as expected

[John Loveys]

Hi All

 

I have the following simple table:

CREATE TABLE log

( Channel INT, Start_Time INT, End_Time INT, Data_Value BLOB);

 

And I have this delete statement:

DELETE FROM log WHERE Start_Time <= ?;

 

I prepare this statement when I open the database, along with a bunch of
others.  I keep them in a structure (called log_db in the code below) along
with the database pointer.  I finalize all the statements just before I
close the DB.

 

When I want to do a delete, I reset the statement, then I bind a large
uint64 (number of centi-seconds since the start of UNIX time) to the
parameter and step the statement.

 

void delete_old_logs(log_database_t *log_db, uint64_t cutoff_time)

{

reset_statement(log_db->database, log_db->delete_old_stmt,
"delete_old_stmt");

bind_int64(log_db->database, log_db->delete_old_stmt, 1, cutoff_time,
"delete_old_stmt", "cutoff_time");

step_statement(log_db->database, log_db->delete_old_stmt,
"delete_old_stmt");

reset_statement(log_db->database, log_db->delete_old_stmt,
"delete_old_stmt");

}

 

I use a few simple wrapper functions to avoid all the inline error checking.
During development I exit the application on any error - I do not see any of
my error fprintf's when I try to delete rows.

 

void reset_statement(sqlite3 *db, sqlite3_stmt *stmt, const char *stmt_name)

{

int rc = sqlite3_reset(stmt);

if ( rc != SQLITE_OK ) {

LogEvent(ERROR, "Cannot reset statement %s.  %s", stmt_name,
sqlite3_errmsg(db));

fprintf(stderr, "%s %d: Cannot reset statement %s.  %s\n", __FILE__,
__LINE__, stmt_name, sqlite3_errmsg(db));

//exit(rc);

}

}

 

int step_statement(sqlite3 *db, sqlite3_stmt *stmt, const char *stmt_name)

{

int rc = sqlite3_step(stmt);

if ( rc == SQLITE_ERROR ) {

LogEvent(ERROR, "Cannot step statement %s.  %s", stmt_name,
sqlite3_errmsg(db));

fprintf(stderr, "%s %d: Cannot step statement %s.  %s\n", __FILE__,
__LINE__, stmt_name, sqlite3_errmsg(db));

//exit(rc);

}

return rc;

}

 

void bind_int64(sqlite3 *db, sqlite3_stmt *stmt, int index, uint64_t data,

const char *stmt_name, const char *data_name)

{

int rc = sqlite3_bind_int64(stmt, index, data);

if ( rc != SQLITE_OK ) {

LogEvent(ERROR, "Cannot bind %s to %s.  %s", data_name, stmt_name,
sqlite3_errmsg(db));

fprintf(stderr, "%s %d: Cannot bind %s to %s.  %s\n", __FILE__,
__LINE__, data_name, stmt_name, sqlite3_errmsg(db));

//exit(rc);

}

}

 

I have another statement that deletes all rows

 

DELETE FROM log;

 

I use it exactly the same way, except I do not bind a parameter.  Delete all
works fine.  Delete old doesn't do anything!  I have been using SQL for >20
years and these are pretty simple statements.  Does parameter binding just
not work with DELETE?

 

Any insight would be greatly appreciated.

 

Thanks

John

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

Re: [sqlite] search time is non-determinate in multi-thread enviroment

[Lawrence Gold]

On Jul 28, 2009, at 8:09 PM, pierr wrote:

> http://www.sqlite.org/threadsafe.html
> "Multi-thread. In this mode, SQLite can be safely used by multiple  
> threads
> provided that no single database connection is used simulataneously  
> in two
> or more threads."
>
> If I have one(different) connection for each thread , it seems no  
> need to
> synchronize these threads' accessing to the database because they are
> through different connection. Did I read it wrongly?

I believe you are correct.

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

Re: [sqlite] sqlite version 2.8 vs 3.x

[David Bicking]

On Thu, 2009-07-30 at 09:18 -0430, An wrote:
> my question stayed unanswered, so that is why i'm repeating it on the
> mailinglist:
> 
> if sqlite2.8 will be supported of bugs the following years, as the web page
> says, what is another reason for working with version 3.x instead of 2.8 ?
> 
> i'm working with xampp-lite server that comes bundled with 2.8... I guess if
> there's no mayor reason i could stay working on 2.8 ?
> 
> is this recommended ?

> thanx

> An M

If I recall correctly, sqlite 2.8 only stored values as text, so if you
are storing lots of numbers, you will be converting back and forth
between text and numeric a lot. Ditto for blobs.

IIRC, bound parameters was introduced in sqlite 3.x, so you run a
greater risk of sql injection attacks using 2.8.

3.x can better handle schema changes invalidating prepared (though I
think they were called "compiled" in 2.8) statements.

3.x I believe handles multiple threads better than 2.8.

I have written program using 2.8 and since they work, I see no reason to
go back and change them.

Hope that helps.
David



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

Re: [sqlite] Multi-master replication with updated Versioning?extension

[Jay A. Kreibich]

On Thu, Jul 30, 2009 at 08:33:10PM +0400, Alexey Pechnikov scratched on the 
wall:
> Hello!
> 
> On Thursday 30 July 2009 19:47:39 Jay A. Kreibich wrote:
> >   Then again, given that ROWID values are signed 64 bit values, you
> >   could just start each master at some offset (like +0x00FF)
> >   and not worry about it.  It would still be a good idea to force all
> >   the tables into an AUTOINCREMENT mode somehow.
> 

> Before start replication databases may be copied from prototype 
> database as files and so they are binary equal.

  That still works (outside of adjustments to the sqlite_sequences
  table).
  
  Any replication function is going to manually insert a whole record,
  including the ROWID.  The AUTOINCREMENT values would only be used if
  a master has to insert a brand-new row that otherwise doesn't exist
  anywhere in the cluster of masters.  In that case it has to be
  assigned a globally unique (i.e. UUID) ROWID value.  Off-setting the
  pool of ROWID values each master uses effectively does this.

> UUID is really unique key for multi-master replication.

  And that's more or less what this does.  Any brand new record that is
  created will be assigned a globally unique ROWID value, basically
  making that ROWID a UUID.

> This is bad because ROWID without explicit field is not persistant 
> and may change after dump/restore. Please see illustration of the problem:

  Yes, this is true of almost every database system out there,
  including ones that generate UUIDs.  You're not really supposed to
  dump and restore a multi-master system.  If you trash a database you
  "restore" it by creating an empty master an syncing from a working
  master (or, in the case of SQLite, just copy of the file).  If all
  your masters are trashed and you're building a new cluster, the
  alignment of ROWID values doesn't matter; even if they're freshly
  generated they're still unique across the new one-master cluster.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Looking for a w_char alternative to the sqlite3_mprintf famili of functions

[A.J.Millan]

Hi list:

Actually I have an application that regularly uses expresions as:

char *zUserName = "My name";
char *zSQL = sqlite3_mprintf ("INSERT INTO table VALUES('%q')", zUserName);

Because of internationalization I need use Unicode instead of ASCII chars:

w_char *zwUserName = L"My Chinese Name";

w_char *zwSQL = sqlite3_x ("INSERT INTO table VALUES('%q')", zwUserName);

Some suggestion?

Thanks in advance

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

Re: [sqlite] Statements that return a result set?

[Simon Slavin]

On 30 Jul 2009, at 11:12am, Robert Villanoa wrote:

> I am new to SQLite, and I have a question: In SQLite, is there a  
> statement apart from SELECT statement that can return a result set?  
> Or the SELECT statement is the only statement that can return a  
> result set

PRAGMA will, but only for some arguments.  For instance,

PRAGMA table_info(myTable)

will.  I think it's safe to treat all SELECT and PRAGMA statements as  
returning a result set, and no others.  But there are probably some  
I've forgotten.  And, of course, a badly formed SELECT statement, or  
one that refers to a column that doesn't exist, won't.

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

Re: [sqlite] Multi-master replication with updated Ver sioning extension

[Alexey Pechnikov]

Hello!

On Thursday 30 July 2009 19:47:39 Jay A. Kreibich wrote:
>   Then again, given that ROWID values are signed 64 bit values, you
>   could just start each master at some offset (like +0x00FF)
>   and not worry about it.  It would still be a good idea to force all
>   the tables into an AUTOINCREMENT mode somehow.

I'm not agree. I did use this but it is "manual replication". Before start 
replication
databases may be copied from prototype database as files and so they are binary 
equal.
And for replication between different versions of the database you way doesn't 
work.

UUID is really unique key for multi-master replication.
SQLite extension is available by link http://mobigroup.ru/files/sqlite-ext/uuid/
Hash of record may be used as pseudo-UUID for some tables.

>   PRAGMA request: http://www.sqlite.org/cvstrac/tktview?tn=4002

This is bad because ROWID without explicit field is not persistant and may 
change 
after dump/restore. Please see illustration of the problem:

sqlite> create table test(a);
sqlite> insert into test values(1);
sqlite> insert into test values(2);
sqlite> insert into test values(3);
sqlite> select rowid,* from test;
1|1
2|2
3|3
sqlite> delete from test where rowid=2;
sqlite> .dump test
BEGIN TRANSACTION;
CREATE TABLE test(a);
INSERT INTO "test" VALUES(1);
INSERT INTO "test" VALUES(3);
COMMIT;
sqlite> drop table test;
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE test(a);
sqlite> INSERT INTO "test" VALUES(1);
sqlite> INSERT INTO "test" VALUES(3);
sqlite> COMMIT;
sqlite> select rowid,* from test;
1|1
2|3

As you can see now ROWID=2 is correspond to field value 3 and it's wrong.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning extension

[Jay A. Kreibich]

On Thu, Jul 30, 2009 at 06:11:42PM +0400, Alexey Pechnikov scratched on the 
wall:
> Hello!
> 
> On Thursday 30 July 2009 17:25:15 P Kishor wrote:
> > > I haven't looked at your work in depth, but I am interested in this. I
> > > have implemented a very simple versioning system with TRIGGERs whereby
> > > every change (INSERT, UPDATE, DELETE) in a column in a table is stored
> > > in a versions table along with its primary key, allowing me to go back
> > > and examine any version and roll back to it, if desired.
> 
> Yes, the primary key field is good enough for master-slave replication
> but not for multi-master because each master has self sequence counter.

  You're saying primary key when I think you more specifically mean
  ROWID.  Assuming you could override and force AUTOINCREMENT behavior
  on all tables (that might make an interesting PRAGMA), this could
  help with that:

  http://www.sqlite.org/cvstrac/tktview?tn=3563

  As long as the "BY" is greater than the number of masters and each
  master has a "FROM" that is sequenced, they'll leap-frog over each
  other.

  Then again, given that ROWID values are signed 64 bit values, you
  could just start each master at some offset (like +0x00FF)
  and not worry about it.  It would still be a good idea to force all
  the tables into an AUTOINCREMENT mode somehow.

  PRAGMA request: http://www.sqlite.org/cvstrac/tktview?tn=4002

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Ver sioning extension

[Alexey Pechnikov]

Hello!

On Thursday 30 July 2009 18:17:45 Jim Showalter wrote:
> MD5 hashes can still collide. How does this implementation deal with 
> hash collisions?

You may use any other hash (sha256 as example). But I think md5 collisions is 
not 
the problem for common applications. 

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning extension

[Jim Showalter]

MD5 hashes can still collide. How does this implementation deal with 
hash collisions?

- Original Message - 
From: "Alexey Pechnikov" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, July 30, 2009 7:11 AM
Subject: Re: [sqlite] Multi-master replication with updated Versioning 
extension


> Hello!
>
> On Thursday 30 July 2009 17:25:15 P Kishor wrote:
>> > I haven't looked at your work in depth, but I am interested in 
>> > this. I
>> > have implemented a very simple versioning system with TRIGGERs 
>> > whereby
>> > every change (INSERT, UPDATE, DELETE) in a column in a table is 
>> > stored
>> > in a versions table along with its primary key, allowing me to go 
>> > back
>> > and examine any version and roll back to it, if desired.
>
> Yes, the primary key field is good enough for master-slave 
> replication but not for
> multi-master because each master has self sequence counter. And full 
> record
> hash may be used for master-slave replication on tables without 
> primary keys.
>
> As table-independant way extension use ROWID field and md5 hash of 
> all fields.
> So multi-master is possible with some restrictions such as 
> non-unique records
> is denied. But may be ROWID + hash of record can help for this 
> situation too.
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Multi-master replication with updated Versioning extension

[Alexey Pechnikov]

Hello!

On Thursday 30 July 2009 17:25:15 P Kishor wrote:
> > I haven't looked at your work in depth, but I am interested in this. I
> > have implemented a very simple versioning system with TRIGGERs whereby
> > every change (INSERT, UPDATE, DELETE) in a column in a table is stored
> > in a versions table along with its primary key, allowing me to go back
> > and examine any version and roll back to it, if desired.

Yes, the primary key field is good enough for master-slave replication but not 
for 
multi-master because each master has self sequence counter. And full record 
hash may be used for master-slave replication on tables without primary keys.

As table-independant way extension use ROWID field and md5 hash of all fields.
So multi-master is possible with some restrictions such as non-unique records
is denied. But may be ROWID + hash of record can help for this situation too.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite version 2.8 vs 3.x

[An]

my question stayed unanswered, so that is why i'm repeating it on the
mailinglist:

if sqlite2.8 will be supported of bugs the following years, as the web page
says, what is another reason for working with version 3.x instead of 2.8 ?

i'm working with xampp-lite server that comes bundled with 2.8... I guess if
there's no mayor reason i could stay working on 2.8 ?

is this recommended ?


thanx


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

Re: [sqlite] sqlite3_get_table and probable memory leak

[Maciej Miszczak]

Thanks, Pavel, for your all-embracing answer. I'll dig in this topic.

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

Re: [sqlite] Multi-master replication with updated Versioning extension

[P Kishor]

On Thu, Jul 30, 2009 at 7:42 AM, P Kishor wrote:
> Alexey,
>
>
> On Thu, Jul 30, 2009 at 4:42 AM, Alexey Pechnikov 
> wrote:
>> Hello!
>>
>> Please see
>> http://mobigroup.ru/files/sqlite-ext/versioning/
>
> I haven't looked at your work in depth, but I am interested in this. I
> have implemented a very simple versioning system with TRIGGERs whereby
> every change (INSERT, UPDATE, DELETE) in a column in a table is stored
> in a versions table along with its primary key, allowing me to go back
> and examine any version and roll back to it, if desired.
>
> Again, without looking at your code first, what is it that you are
> doing with the C libs that can't be done with just TRIGGERs and a
> versions table?

Ok, I get it. Your code actually creates the versioning tables and
triggers automatically. Great. Will give you feedback once I try it
out.

By the way, DRH has create fossilscm, which is obviously using some
kind of versioning. It might be worthwhile studying that and
incorporating that, if relevant.


>
>>
>> Master-slave may use ROWIDs and multy-master may use md5 hash of full record.
>>
>> This is test version and I'm glad to get any comments and ideas.
>>
>> P.S. md5 extension sources is here
>> http://mobigroup.ru/files/sqlite-ext/md5/
>>
>> Best regards, Alexey Pechnikov.
>> http://pechnikov.tel/
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
>



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

[sqlite] [OpenSQL Camp] OpenSQL Camp, November 2009 in Portland, OR (fwd)

[Rich Shepard]

   For those who are interested.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863

-- Forwarded message --
Hello!

We're planning to have OpenSQL Camp in Portland. The event will be free, and
we're hoping to have 120 people there.  We've started a wiki page for adding
yourself to the attendee list, and for helping with planning!

http://opensqlcamp.org/Events/Portland2009/

We'll keep you posted as details evolve!  Feel free to pass this on to
groups you think might be interested.

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

Re: [sqlite] Insert multiple entries in a single INSERT statement

[CityDev]

Kees Nuyt wrote:
> 
> 
> Insert one row at a time.
> 
> 
> 
 Presumably you can do this kind of thing:

INSERT INTO Table2 ( [FieldX] )
SELECT FieldY
FROM Table1;
-- 
View this message in context: 
http://www.nabble.com/Insert-multiple-entries-in-a-single-INSERT-statement-tp24705205p24737715.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] Multi-master replication with updated Versioning extension

[P Kishor]

Alexey,


On Thu, Jul 30, 2009 at 4:42 AM, Alexey Pechnikov wrote:
> Hello!
>
> Please see
> http://mobigroup.ru/files/sqlite-ext/versioning/

I haven't looked at your work in depth, but I am interested in this. I
have implemented a very simple versioning system with TRIGGERs whereby
every change (INSERT, UPDATE, DELETE) in a column in a table is stored
in a versions table along with its primary key, allowing me to go back
and examine any version and roll back to it, if desired.

Again, without looking at your code first, what is it that you are
doing with the C libs that can't be done with just TRIGGERs and a
versions table?

>
> Master-slave may use ROWIDs and multy-master may use md5 hash of full record.
>
> This is test version and I'm glad to get any comments and ideas.
>
> P.S. md5 extension sources is here
> http://mobigroup.ru/files/sqlite-ext/md5/
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] sqlite3_get_table and probable memory leak

[Pavel Ivanov]

> Is lowering the PRAGMA cache_size a good way to fix this?

Yes, it's the only way to fix this. Though IIRC you cannot lower it
below 10, so 10 pages will be always stored in memory. OTOH you can
implement your own version of page cache that will not leave pages in
memory at all (for more information see
http://www.sqlite.org/c3ref/pcache_methods.html).

> I see now I have probably missed some basic rules
> of using SQLite. I just created a Database class with constructor
> opening a database connection and storing a sqlite3* pointer and
> destructor closing this connection.

I think you've implemented it correctly. I doubt you want to open and
close connection for executing of each statement because it's some
serious overhead.

Pavel

On Thu, Jul 30, 2009 at 8:25 AM, Maciej
Miszczak wrote:
> Pavel, you're great! There's nothing better than to just ask the right
> people. Calling sqlite3_close after the mentioned function solved the
> problem. By the way I got something like this:
>> - 0x0005e688 Free 28 was never alloc'd 0x400f3ec4
>> - 0x0005e750 Free 29 was never alloc'd 0x400f3ec4
>> - 0x0005e768 Free 30 was never alloc'd 0x400f3ec4
>> - 0x0005e788 Free 31 was never alloc'd 0x400f3ec4
> and so on, but I think there's nothing to worry about. My database has
> the page_size of 1024. I see now I have probably missed some basic rules
> of using SQLite. I just created a Database class with constructor
> opening a database connection and storing a sqlite3* pointer and
> destructor closing this connection. In this situation every
> Database::execute call produced, as you said, not freed cache. It seems
> to be the cause of all my problems with "leaks". Is lowering the PRAGMA
> cache_size a good way to fix this? My database works on embedded
> platform with 64MB of RAM.
>
> Thanks again, cheers,
> Maciej
> ___
> 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] Closure

[CityDev]

As an aside, a principle of the relational model is that operations on
relations should produce a relation. This caused a bit of a problem early on
as if you perform a Project operation ie cut down the number of fields, you
can finish up with 'duplicate' rows ie rows that can't be distinguished.
RDBMs in practice all use tables instead of relations. Even base tables can
contain 'duplicate' records although that would be bad practice. 


 
-- 
View this message in context: 
http://www.nabble.com/Statements-that-return-a-result-set--tp24735659p24737576.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] sqlite3_get_table and probable memory leak

[Maciej Miszczak]

Pavel, you're great! There's nothing better than to just ask the right 
people. Calling sqlite3_close after the mentioned function solved the 
problem. By the way I got something like this:
> - 0x0005e688 Free 28 was never alloc'd 0x400f3ec4
> - 0x0005e750 Free 29 was never alloc'd 0x400f3ec4
> - 0x0005e768 Free 30 was never alloc'd 0x400f3ec4
> - 0x0005e788 Free 31 was never alloc'd 0x400f3ec4
and so on, but I think there's nothing to worry about. My database has 
the page_size of 1024. I see now I have probably missed some basic rules 
of using SQLite. I just created a Database class with constructor 
opening a database connection and storing a sqlite3* pointer and 
destructor closing this connection. In this situation every 
Database::execute call produced, as you said, not freed cache. It seems 
to be the cause of all my problems with "leaks". Is lowering the PRAGMA 
cache_size a good way to fix this? My database works on embedded 
platform with 64MB of RAM.

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

Re: [sqlite] Use of Transaction with in memory db

[Igor Tandetnik]

Sharma, Gaurav wrote:
> 1- Is there any significance of using transaction (whether auto
> commit or manual) with in memory database.

Yes. Transactions work on in-memory databases the same way they do for 
regular databases.

> 2- If the answer of first question is "yes" then second question
> would be if I wish to share an in memory db connection amongst thread
> with mutex protection. Is there required any special treatment while
> using with Transactions besides protecting them with mutex.

Again, the same considerations as for regular databases. Transaction is 
per connection: if one thread issues a BEGIN statement, all subsequent 
operations by all threads go into the same transaction.

Igor Tandetnik 



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

Re: [sqlite] Statements that return a result set?

[Igor Tandetnik]

Robert Villanoa wrote:
> I am new to SQLite, and I have a question: In SQLite, is there a
> statement apart from SELECT statement that can return a result set?

PRAGMA, EXPLAIN

Igor Tandetnik 



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

Re: [sqlite] Use of in memory db

[Igor Tandetnik]

Sharma, Gaurav wrote:
> Is there any way through which without using the mutex lock mechanism
> multiple threads can perform INSERT in bulk on same memory db. What I
> assume is if I share an in memory db connection handle across threads
> then it will not be sqlite's responsibility but the user's
> responsibility to protect multiple insert/update statements. On the
> other hand if the connection to physical db is shared amongst threads
> then sqlite takes care of synchronization between multiple
> insert/update from multiple threads.

I don't believe this is the case. The locking behavior should be the 
same for in-memory and on-disk databases. The exact behavior is 
described here: http://www.sqlite.org/threadsafe.html

> Basically a broader question is that, Is there any way to avoid
> sharing of connection handle and even then be able to work on the
> same memory db from multiple threads.

No.

> I really wish to avoid the
> burden of using mutex for every (insert or update) in multi threaded
> scenario.

You don't have to, SQLite will do that for you.

> That actually supposed to put lot of overhead on my
> application.

If you mean performance overhead, then I don't see how you can avoid 
that. You are working on a shared data structure - someone somewhere 
must synchronize access to avoid races, whether your code or SQLite 
itself.

Igor Tandetnik 



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

Re: [sqlite] Statements that return a result set?

[P Kishor]

On Thu, Jul 30, 2009 at 5:12 AM, Robert
Villanoa wrote:
> Hi folk,
>
> I am new to SQLite, and I have a question: In SQLite, is there a statement 
> apart from SELECT statement that can return a result set? Or the SELECT 
> statement is the only statement that can return a result set?
>

Nothing personal about SQLite here... SELECT is a SQL standard clause,
and as the name implies, does the job of returning a result set. DDL
and DML portions of SQL do other things such as ALTER the table or
UPDATE/DELETE/INSERT, etc. However, if you want something back, then
yes, SELECT is your friend.


> Thank you for reading my question!
>
> Robert.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Use of in memory db

[Pavel Ivanov]

> What I assume is if I share an in memory db connection handle across threads 
> then it will not be sqlite's responsibility but the user's responsibility to 
> protect multiple insert/update statements. On the other hand if the 
> connection to physical db is shared amongst threads then sqlite takes care of 
> synchronization between multiple insert/update from multiple threads.

Your assumption is wrong. When you share the same connection among
several threads SQLite works with the same scenario no matter if you
connection to in-memory database or to physical file. And this
scenario depends on compilation options and flags given to
sqlite3_open. With default compilation options if you gave flag
SQLITE_OPEN_NOMUTEX then it's your responsibility to protect with
mutexes, if you gave flag SQLITE_OPEN_FULLMUTEX then SQLite makes this
work for you. What you meant to say maybe is that if you have
different connections in each thread to the same physical file then
SQLite can make more fine-grained synchronization between threads.
This scenario is not applicable to in-memory database because there's
no way you can open several connections to the same in-memory
database.

> Basically a broader question is that, Is there any way to avoid sharing of 
> connection handle and even then be able to work on the same memory db from 
> multiple threads.

As I've already said: no.

> Or any one can suggest how best the mutex can be used if I share the 
> connection and then perform transactions (insert, update).

If you have resource shared between threads there's only one way to
use it - protect each access to it with mutex. There's no other ways
to do it and no tricks can be done in here. The only trick that you
can do is to avoid sharing of the resource and develop some memory
structure that will allow you concurrent access from different threads
with the pattern your application needs. But SQLite is not your fellow
here - it doesn't offer this kind of memory structure.

Pavel

On Thu, Jul 30, 2009 at 7:16 AM, Sharma,
Gaurav wrote:
> Hi All,
>
> Is there any way through which without using the mutex lock mechanism 
> multiple threads can perform INSERT in bulk on same memory db. What I assume 
> is if I share an in memory db connection handle across threads then it will 
> not be sqlite's responsibility but the user's responsibility to protect 
> multiple insert/update statements. On the other hand if the connection to 
> physical db is shared amongst threads then sqlite takes care of 
> synchronization between multiple insert/update from multiple threads.
>
> Basically a broader question is that, Is there any way to avoid sharing of 
> connection handle and even then be able to work on the same memory db from 
> multiple threads. I really wish to avoid the burden of using mutex for every 
> (insert or update) in multi threaded scenario. That actually supposed to put 
> lot of overhead on my application.
>
> Or any one can suggest how best the mutex can be used if I share the 
> connection and then perform transactions (insert, update).
>
> With Best Regards
> Gaurav Sharma
>
>
> The information contained in this electronic mail transmission
> may be privileged and confidential, and therefore, protected
> from disclosure. If you have received this communication in
> error, please notify us immediately by replying to this
> message and deleting it from your computer without copying
> or disclosing it.
>
>
> ___
> 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_get_table and probable memory leak

[Maciej Miszczak]

Hi,

I have a simple function (used to get only one value from a database), 
as shown below, and I've struggled with it for a long time, without 
success. I use libsqlite3.6.13.
> int Database::execute(std::string query, std::string* result, 
> std::string* errMsg)
> {
> int rc, tmpRowsCount, tmpColsCount;
> char* zErrMsg;
> char** tmpRes;
> ostringstream tmp;
>
> OS.logger->log(string("Executing query: ") + query, LOG);
>
> rc = sqlite3_get_table(db, query.c_str(), , , 
> , );
> if( rc!=SQLITE_OK )
> {
> tmp << "SQL error: " << zErrMsg;
> if (errMsg != NULL)
> {
> errMsg->assign(zErrMsg);
> sqlite3_free(zErrMsg);
> }
> OS.logger->log(tmp.str(), ERR);
> tmp.str("");
> sqlite3_free_table(tmpRes);
> return -1;
> }
>
> if ((tmpRowsCount != 1) || (tmpColsCount != 1)) // TODO: check row 
> count
> {
> tmp << "Invalid number of rows (" << tmpRowsCount << ") or 
> columns (" << tmpColsCount << ")";
> OS.logger->log(tmp.str(), ERR);
> tmp.str("");
> sqlite3_free_table(tmpRes);
> return -2; // TODO: define error
> }
>
> if (result != NULL)
> result->assign(tmpRes[1]);
>
> sqlite3_free_table(tmpRes);
>
> return 0;
> }
The problem is that after calling above function i get from mtrace:
> Memory not freed:
> -
>Address Size Caller
> 0x0005f9d0  0xf  at 0x401e8ef8
> 0x000628280x490  at 0x4012c920
Not freed 0xf at 0x0005f9d0 is OK, because it's caused by 
result->assign(tmpRes[1]). What I'm confused with is the 0x490 of not 
freed memory.

Here is raw mtrace log:
> = Start
> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61310 0x56
> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61370 0x1e
> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x61398 0x67
> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61398
> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61370
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5fbe8 0x58
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62828 0x658
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61370 0xe0
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62e88 0x408
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61458 0x230
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61458
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x62828
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x61458 0x160
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x62828 0x490
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5fe00 0x20
> @ /usr/lib/libsqlite3.so.0:[0x4012c920] + 0x5f9b8 0x10
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61458
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x62e88
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x61370
> @ /usr/lib/libsqlite3.so.0:[0x4012c8ec] < 0x5fbe8
> @ /usr/lib/libsqlite3.so.0:[0x4012c8ec] > 0x5fbe8 0x18
> @ /usr/lib/libstdc++.so.6:(_Znwj+0x6c)[0x401e8ef8] + 0x5f9d0 0xf
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5fe00
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5f9b8
> @ /usr/lib/libsqlite3.so.0:(sqlite3_free+0x74)[0x400f3ec4] - 0x5fbe8
> @ /usr/lib/libstdc++.so.6:(_ZNSsD1Ev+0x1c)[0x401d14e0] - 0x61310
> = End
sqlite3_free_table is called for sure, because if I comment it, I get:
> Memory not freed:
> -
>Address Size Caller
> 0x0005f9b8 0x10  at 0x4012c920
> 0x0005f9d0  0xf  at 0x401e8ef8
> 0x000601a8 0x18  at 0x4012c8ec
> 0x00060208 0x20  at 0x4012c920
> 0x000628280x490  at 0x4012c920
Thanks in advance for any hints.

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

[sqlite] Statements that return a result set?

[Robert Villanoa]

Hi folk,

I am new to SQLite, and I have a question: In SQLite, is there a statement 
apart from SELECT statement that can return a result set? Or the SELECT 
statement is the only statement that can return a result set?

Thank you for reading my question!

Robert.



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

[sqlite] Multi-master replication with updated Versioning extension

[Alexey Pechnikov]

Hello!

Please see
http://mobigroup.ru/files/sqlite-ext/versioning/

Master-slave may use ROWIDs and multy-master may use md5 hash of full record.

This is test version and I'm glad to get any comments and ideas.

P.S. md5 extension sources is here
http://mobigroup.ru/files/sqlite-ext/md5/

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reasons for SQLITE_BUSY

[Edzard Pasma]

--- nikol...@rath.org wrote:
> "Igor Tandetnik"  writes:
>> Nikolaus Rath  wrote:
>>> I am accessing the same database from different threads. Each thread
>>> has its own connection. I have set the busy timeout for each
>>> connection to 5000 milliseconds.
>>>
>>> However, in some testcases I still get SQLITE_BUSY errors from
>>> sqlite3_step. Moreover, the whole testcases run in much less than 5
>>> seconds, to apparently sqlite does not even try to wait for the lock
>>> to disappear.
>>
>> You are getting a deadlock. The scenario goes like this: thread A runs a 
>> transaction that starts as a reader (with a SELECT statement) but later 
>> becomes a writer (by executing INSERT, UPDATE or DELETE). Thread B also 
>> runs a transaction like this, or a simple writer transaction. Then the 
>> following sequence of events occurs:
>>
>> 1. Thread A starts as reader and takes a shared lock
>> 2. Thread B starts as writer, takes a pending lock and waits for readers 
>> to clear.
>> 3. Thread A tries to become a writer and promote its lock to reserved, 
>> but can't because there's already a writer on the database.
>>
>> The two threads deadlock. No amount of waiting by either thread would 
>> get them out of the impasse: the only way out is for one of the threads 
>> to roll back its transaction and start from scratch. When SQLite detects 
>> this situation, it returns SQLITE_BUSY immediately, without calling the 
>> busy handler (because, again, waiting won't help any).
>>
>> To avoid the possibility of a deadlock, start your reader-turning-writer 
>> transactions with BEGIN IMMEDIATE (this essentially makes the 
>> transaction a writer right away).
>
> Ah, I see. I expected that a deadlock would actually result in both
> threads hanging forever, rather than SQLite detecting it and abandoning
> immediately. The later is of course even better once you know about it.
> Thanks for the explanations! I should be able to fix my problem now..

Hi,

Just in case it appears difficult to fix, I like to suggest to try using shared 
cache mode. The shared cache locking model does not have this particular 
deadlock situation. I'm assuming that the database is accessed from within a 
single process only. 

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