[sqlite] FTS3 can not support double-quote character search.

[Andy]

An unmatched double-quote the right-hand argument to MATCH causes an error
in FTS3.
Example:

CREATE VIRTUAL TABLE t1 USING fts3(x);
INSERT INTO t1 VALUES('abc"def"123');
SELECT * FROM t1 WHERE t1 MATCH '"*';

The SELECT statement above returns: "malformed MATCH expression:  ["*].
Can this feature be supported in the future?
___
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]

FYI, I finally figured out why it is slow with C because I need to turn off 
autocommit, it is now lightning speed :)
Thanks,
Bella
___
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]

> Parameters are numbered from 1, not from 0. Your first sqlite3_bind_int 
> call fails; the second binds a value for function_id column; the 
> parameter for function_name retains its initial value of NULL. The 
> insert then runs afoul of NOT NULL constraint.

Ah, that fixed it.  Thanks much.
Bella
___
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()?

[Igor Tandetnik]

On 1/26/2011 7:48 PM, Bella Chan wrote:
> I got "19 constraint failed" error from this code:
>
> "CREATE TABLE functions ("
> "function_id integer PRIMARY KEY,"
> "function_name integer not null"
> ");\n"
>
>
>  sqlite3_bind_int(statement,0,i);
>  sqlite3_bind_int(statement,1,i+10);

Parameters are numbered from 1, not from 0. Your first sqlite3_bind_int 
call fails; the second binds a value for function_id column; the 
parameter for function_name retains its initial value of NULL. The 
insert then runs afoul of NOT NULL constraint.
-- 
Igor Tandetnik

___
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]

> Incorrect.  You can bind_int or bind_int64 on an integer primary key column
> just like any other column.
> What does sqlite3_errcode(db) and sqlite3_errmsg(db) say after you run
> sqlite3_step(pStmt) on the above?

I got "19 constraint failed" error from this code:

"CREATE TABLE functions ("
"function_id integer PRIMARY KEY,"
"function_name integer not null"
");\n"

...
  const char *sqlcmd;
  string strcmd, entryname;
  sqlite3_stmt *statement;

  strcmd = "INSERT INTO " + tablename + " VALUES(?,?)";
  sqlcmd = strcmd.c_str();
  if (sqlite3_prepare_v2(db, sqlcmd, -1, , 0) != SQLITE_OK) {
cout << "SQL error on " << sqlcmd << ": " << sqlite3_errmsg(db);
sqlite3_close(db);
exit(1);
  }

  for (int i=start_index; i

[sqlite] Tcl API doc bug (was Re: [3.7.4] [BUG] [TCL] busy handler not called on lock escalation)

[Eric Smith]

Igor Tandetnik wrote:

> On 1/26/2011 6:39 PM, Eric Smith wrote:
> > busy handler not called on lock escalation
> 
> This behavior is by design. See
> 
> http://sqlite.org/c3ref/busy_handler.html
> 
> the part that talks about a deadlock.

Understood & agreed.

This is a bug in the Tcl API documentation, which can lead the unwary
Tcl programmer to believing the busy callback will be called in all
cases of contention.  I recommend adding some text to the section 
'The "busy" method'.  This example was largely lifted from the C API
doc:

==
The presence of a busy handler does not guarantee that it will be
invoked when there is lock contention.  If SQLite determines that
invoking the busy handler could result in a deadlock, it will go ahead
and throw an error instead of invoking the busy handler. 

Consider a scenario where one process is holding a read lock that it is 
trying to promote to a reserved lock and a second process is holding a 
reserved lock that it is trying to promote to an exclusive lock. The first
process cannot proceed because it is blocked by the second and the
second process cannot proceed because it is blocked by the first. If
both processes invoke the busy handlers, neither will make any progress.
Therefore, SQLite throws an error for the first process, hoping that
this will induce the first process to release its read lock and allow
the second process to proceed.

See http://www.sqlite.org/lockingv3.html for more details.
==

Eric

--
Eric A. Smith

Slurm, n.:
The slime that accumulates on the underside of a soap bar when
it sits in the dish too long.
-- Rich Hall, "Sniglets"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [3.7.4] [BUG] [TCL] busy handler not called on lock escalation

[Igor Tandetnik]

On 1/26/2011 6:39 PM, Eric Smith wrote:
> busy handler not called on lock escalation

This behavior is by design. See

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

the part that talks about a deadlock.
-- 
Igor Tandetnik

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

Re: [sqlite] how to turn off autocommit in C interface?

[Igor Tandetnik]

On 1/26/2011 6:53 PM, Bella Chan wrote:
>how to turn off autocommit in C interface?

Execute a "BEGIN" or "BEGIN TRANSACTION" statement.
-- 
Igor Tandetnik

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

Re: [sqlite] how to turn off autocommit in C interface?

[Bella Chan]

Please don't worry about this one.  I figured there is no C function to turn it 
off by just execute a "BEGIN" sql command before other commands and a "COMMIT" 
at the end to achieve this.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Bella Chan
Sent: Wednesday, January 26, 2011 3:54 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to turn off autocommit in C interface?


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

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

[Richard Hipp]

On Wed, Jan 26, 2011 at 6:53 PM, Bella Chan  wrote:

> I want to confirm that it is true that I can't do bind_int on integer
> primary key.
>

Incorrect.  You can bind_int or bind_int64 on an integer primary key column
just like any other column.


>
> If I do "insert into functions values(?,?)" and use bind_int on the primary
> key, nothing is added to the table.


What does sqlite3_errcode(db) and sqlite3_errmsg(db) say after you run
sqlite3_step(pStmt) on the above?


-- 
D. Richard Hipp
d...@sqlite.org
___
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] 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

[sqlite] [3.7.4] [BUG] [TCL] busy handler not called on lock escalation

[Eric Smith]

SQLite version 3.7.4 with TEA.  gcc was run like this:

gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.7.4\" -DPACKAGE_STRING=\"sqlite\ 3.7.4\" 
-DPACKAGE_BUGREPORT=\"\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 
-DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 
-DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 
-DHAVE_LIMITS_H=1 -DHAVE_SYS_PARAM_H=1 -DUSE_THREAD_ALLOC=1 -D_REENTRANT=1 
-D_THREAD_SAFE=1 -DTCL_THREADS=1 -DSQLITE_THREADSAFE=1 -DMODULE_SCOPE=extern\ 
__attribute__\(\(__visibility__\(\"hidden\"\)\)\) -D_LARGEFILE64_SOURCE=1 
-DTCL_WIDE_INT_TYPE=long\ long -DHAVE_STRUCT_STAT64=1 -DHAVE_OPEN64=1 
-DHAVE_LSEEK64=1 -DHAVE_TYPE_OFF64_T=1 -DUSE_TCL_STUBS=1 -DSQLITE_ENABLE_FTS3=1 
-I"./generic" -I"/home/eas/tcl8.5.9/generic"-pipe -O0 -g -Wall -fPIC -c 
`echo ./generic/tclsqlite3.c` -o tclsqlite3.o

(All those parameters were auto-generated by 'configure' except -O0,
which I changed from the default -O2.)

$ uname -a
Linux hudson 2.6.32.26-175.fc12.i686 #1 SMP Wed Dec 1 21:52:04 UTC 2010 i686 
athlon i386 GNU/Linux

I register a Tcl busy handler that fails to be called when a connection 
attempts to escalate its lock status.  This may also indicate a bug 
in the C API as well.

Steps to reproduce:

1. Write the following script into a file named 'foo.tcl':

===
#!/usr/bin/tclsh

load sqlite-tea-3070400/libsqlite3.7.4.so

proc ::busy {ntries} {
puts stderr "[pid] finds the db busy, ntries=$ntries.  try again."
after 1
return 0
}

if {[catch {
  puts stderr "[pid] hello, world"
  sqlite3 db /tmp/foo.db
  db busy ::busy
  db eval begin
  db eval {create table if not exists t(a)}
  db eval commit
  db eval begin
  db eval {select count(*) from t}
  db eval {insert into t values(100)}
  after 1000
  db eval commit
  puts stderr "[pid] exits successfully"
}]} {
  puts stderr "[pid] error in script: $::errorInfo"
} 
===

2. Make sure that the 'load' command above will work by changing the path 
name as appropriate.

3. Make sure /tmp is a normal filesystem and the file 'foo.db' does not
exist there, e.g. as the remnant of some previous debugging exercise.

4. Make the script executable:

   $ chmod +x ./foo.tcl

5. Run the script twice in parallel:

   $ ./foo.tcl & ./foo.tcl

Expected result: 

Both scripts indicate that they have completed successfully by 
printing " exits successfully".  This is because we expect
for the busy handler to be called when the database is locked,
and the busy handler always return 0, indicating that SQLite
should keep retrying until successful.

Actual result:

In almost every test run, neither instance never enters ::busy.  
Instead, I get the following output:

[hudson:~] $ ./foo.tcl & ./foo.tcl
[1] 15308
15309 hello, world
15308 hello, world
15308 error in script: database is locked
while executing
"db eval {insert into t values(100)}"
15309 exits successfully

I say "almost almost every test run" because if I remove that 
'after 1000' line, sometimes the script fails like this and sometimes 
it doesn't.  Adding the 'after' line increases the failure frequency to 
nearly 100%, probably because the "winning" process is holding a certain 
flavor of lock for longer.

Please let me know if you need more information.

Eric

--
Eric A. Smith

Electricity is actually made up of extremely tiny particles 
called electrons, that you cannot see with the naked eye unless 
you have been drinking.
-- Dave Barry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting a table's field labels with Perl, DBI

[Petite Abeille]

On Jan 26, 2011, at 11:04 AM, BareFeetWare wrote:

> Yes, it is very unfortunate that INFORMATION_SCHEMA or similar is not 
> provided by SQLite,

Agree, SQLite is seriously lacking in that domain :/

> a topic that keeps recurring here in one form or another.

Eh!

> Yu currently have to do your own parsing of the schema to get much of the 
> info about columns, triggers, foreign keys etc.

Short of parsing the DDL itself, a concoction of sqlite_master.type, 
sqlite_temp_master , pragma database_list, table_info, index_list, index_info, 
foreign_key_list is what you have to brew to get a partial data dictionary of 
sort:

http://dev.alt.textdrive.com/browser/IMDB/Info.ddl#L68

Oh, well, better than nothing I guess :))



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

Re: [sqlite] EXTERNAL: Bus error on sqlite3_close on Sun Sparc w/SQLite 3.7.4

[Black, Michael (IS)]

My suspicion would be that you have a bug in your code which only shows up on 
Solaris.

Something is getting corrupted on the stack.

Do your compilet support stack checking?  I think you can use efence on Solaris
http://perens.com/FreeSoftware/ElectricFence/


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Joe Mokos [jmo...@opnet.com]
Sent: Wednesday, January 26, 2011 1:31 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Bus error on sqlite3_close on Sun Sparc w/SQLite 
3.7.4

I've written an application that uses SQLite 3.7.4.  The application runs
fine on WinXP, AIX, HPUX and Linux.  When I try to run on Solaris 5.8 SPARC
I get a bus error when I call sqlite3_close.  I get the following stack
trace:



(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) where

current thread: t@1

=>[1] t_delete(0x1b5ed0, 0xfefbc008, 0x40, 0x1b5e90, 0x1a3840, 0x38), at
0xfef427bc

  [2] realfree(0x1b5ec8, 0xfefc2858, 0xfefbc008, 0x1b5e90, 0x3b, 0x1b5e98),
at 0xfef423dc

  [3] _free_unlocked(0xfefc27cc, 0xfefbc008, 0x2a57c8, 0xfefbc008,
0xffbede4a, 0x0), at 0xfef42c1c

  [4] free(0x2a57c8, 0xfefc3a54, 0xfefbfc78, 0xfefbfc78, 0xe9, 0x2a57c8), at
0xfef42b6c

  [5] sqlite3MemFree(0x2a57d0, 0x, 0xc, 0xfef91bf8, 0x1b5d40,
0x2a57c8), at 0x5a8cc

  [6] sqlite3_free(0x2a57d0, 0x2a5788, 0xc, 0x0, 0x1, 0x2cda28), at 0x5cad8

  [7] sqlite3DbFree(0x0, 0x2a57d0, 0xc, 0x1b5d08, 0x1c9008, 0x1967a4), at
0x5cbf0

  [8] sqliteDeleteColumnNames(0x0, 0x1b5c28, 0x25, 0x1b5d08, 0x6, 0x1b5aa8),
at 0xb16f8

  [9] sqlite3DeleteTable(0x0, 0x1b5c28, 0x1833d8, 0x0, 0x0, 0x0), at 0xb1884

  [10] sqlite3SchemaFree(0x187b68, 0xfefc0458, 0xff05c000, 0x2, 0x187b68,
0x2c9430), at 0xb9088

  [11] sqlite3ResetInternalSchema(0x187788, 0x0, 0x1ffc, 0x187938, 0x194258,
0x0), at 0xb1470

  [12] sqlite3_close(0x187788, 0x0, 0x0, 0xfef88194, 0x194288, 0x0), at
0xe9070

  [13] SqliteConsumer::disconnect(0x1833a8, 0xfefc0284, 0xfefc3a54,
0xfefbfc78, 0x15f1f6, 0xffbee634), at 0x51f5c

  [14] XML2SQLite::process(0x1833a8, 0x2e, 0x0, 0x0, 0x1, 0x180710), at
0x54298

  [15] MetricUtility::doSqliteConsumerProcess(0x183380, 0x1833a8, 0x185558,
0x0, 0x1, 0x1c), at 0x4e404

  [16] MetricUtility::process(0x183380, 0xffbc, 0xffbee93c, 0x3,
0xff0ea07c, 0x20), at 0x4e2f8

  [17] main(0x7, 0xffbee93c, 0xffbee95c, 0x17e400, 0x0, 0x0), at 0x4e8f0

(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx)



I know from past experience the bus error usually indicates invalid memory
alignment.  In this case, however, the memory that it is attempting to free
appears to be aligned properly.  The memory is located at 0x002a57c8.  I am
using Sun Workshop 6 to compile both SQLite and my application.  Does anyone
have an idea of what could be causing the bus error?



Joe Mokos



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

Re: [sqlite] SQLite version 3.7.5 - code freeze

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

> A far better solution ... would be to bump
> the VFS version number to 3

That is the only solution that will not break any existing code from anyone.
 ie you have to use VFS 3 to opt in to getting SYNC_OMITTED (probably best
delivered to xSync but xFileControl works too).  I strongly believe this is
the way you should go - new behaviour bumps version numbers while keeping
compatibility for existing code.  Perhaps a little tricky if you want to do
this in the next few days though ...

> Suppose we rig the default VFSes so that they return SQLITE_OK for
> SQLITE_FCNTL_SYNC_OMITTED, instead of SQLITE_ERROR, so that your
> intermediate layer doesn't throw an exception? 

That would keep the vast majority of existing deployed code happy.

> Another solution we talked about was having the xFileControl method in the
> VFS object return SQLITE_NOTFOUND instead of SQLITE_ERROR if it gets an
> unknown opcode.  That too would solve your problem, wouldn't it?  Would it
> break anything else?

That wouldn't help deployed code since everything other than SQLITE_OK turns
into exceptions.  For my next release I will make it adapt to whatever you
decide.

A problem with file control is that you can't tell the difference between
the following:

1 - A bad database name
2 - op not understood
3 - op understood and had an error being executed

Having NOTFOUND is a solution going forward as I can then distinguish
between the last two cases.  The third needs to be turned into an Exception
while NOTFOUND can be appropriately handled.

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

iEYEARECAAYFAk1AeRUACgkQmOOfHg372QRThgCfX1pPMMzNRdjvcxapNGKhtCdx
yTYAn3P0NaZPQz+mHXAZXNQGVn5Gamhv
=UBJn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Insert while select

[Rich Rattanni]

I was way off on the version.  We are using 3.3.17.  Sorry to bother
you about behavior from Apr 25, 2007.  I am simply curious to
understand this behavior.

On Wed, Jan 26, 2011 at 1:33 PM, Richard Hipp  wrote:
> On Wed, Jan 26, 2011 at 1:30 PM, Igor Tandetnik  wrote:
>
>> On 1/26/2011 11:09 AM, Rich Rattanni wrote:
>> > I am helping someone write an application that utilizes SQLite.  The
>> > following code is giving us grief (sqlite lib version 3.5.ish -
>> > Windows XP):
>> >
>> > sqlite3_prepare("select some rows")
>> > while (sqlite3_step() == SQLITE_ROW)
>> > {
>> >      // Do some calc on multiple rows, and occasinally
>> >      sqlite3_exec("Insert calculated data into the same table from
>> > which we are reading")
>> > }
>>
>> If I recall correctly, this is not allowed in SQLite 3.5.*. This became
>> supported in 3.6.x (for some x I don't remember at the moment).
>>
>> > I had a look at http://www.sqlite.org/lockingv3.html.
>>
>> The documentation describes the current behavior. Older versions often
>> behave differently. 3.5 series are 2.5 years old, a lot of progress has
>> been made since then.
>>
>
> 2.5 calendar years equals 17.5 internet years, right?  :-)
>
>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bus error on sqlite3_close on Sun Sparc w/SQLite 3.7.4

[Joe Mokos]

I've written an application that uses SQLite 3.7.4.  The application runs
fine on WinXP, AIX, HPUX and Linux.  When I try to run on Solaris 5.8 SPARC
I get a bus error when I call sqlite3_close.  I get the following stack
trace:

 

(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) where

current thread: t@1

=>[1] t_delete(0x1b5ed0, 0xfefbc008, 0x40, 0x1b5e90, 0x1a3840, 0x38), at
0xfef427bc

  [2] realfree(0x1b5ec8, 0xfefc2858, 0xfefbc008, 0x1b5e90, 0x3b, 0x1b5e98),
at 0xfef423dc

  [3] _free_unlocked(0xfefc27cc, 0xfefbc008, 0x2a57c8, 0xfefbc008,
0xffbede4a, 0x0), at 0xfef42c1c

  [4] free(0x2a57c8, 0xfefc3a54, 0xfefbfc78, 0xfefbfc78, 0xe9, 0x2a57c8), at
0xfef42b6c

  [5] sqlite3MemFree(0x2a57d0, 0x, 0xc, 0xfef91bf8, 0x1b5d40,
0x2a57c8), at 0x5a8cc

  [6] sqlite3_free(0x2a57d0, 0x2a5788, 0xc, 0x0, 0x1, 0x2cda28), at 0x5cad8

  [7] sqlite3DbFree(0x0, 0x2a57d0, 0xc, 0x1b5d08, 0x1c9008, 0x1967a4), at
0x5cbf0

  [8] sqliteDeleteColumnNames(0x0, 0x1b5c28, 0x25, 0x1b5d08, 0x6, 0x1b5aa8),
at 0xb16f8

  [9] sqlite3DeleteTable(0x0, 0x1b5c28, 0x1833d8, 0x0, 0x0, 0x0), at 0xb1884

  [10] sqlite3SchemaFree(0x187b68, 0xfefc0458, 0xff05c000, 0x2, 0x187b68,
0x2c9430), at 0xb9088

  [11] sqlite3ResetInternalSchema(0x187788, 0x0, 0x1ffc, 0x187938, 0x194258,
0x0), at 0xb1470

  [12] sqlite3_close(0x187788, 0x0, 0x0, 0xfef88194, 0x194288, 0x0), at
0xe9070

  [13] SqliteConsumer::disconnect(0x1833a8, 0xfefc0284, 0xfefc3a54,
0xfefbfc78, 0x15f1f6, 0xffbee634), at 0x51f5c

  [14] XML2SQLite::process(0x1833a8, 0x2e, 0x0, 0x0, 0x1, 0x180710), at
0x54298

  [15] MetricUtility::doSqliteConsumerProcess(0x183380, 0x1833a8, 0x185558,
0x0, 0x1, 0x1c), at 0x4e404

  [16] MetricUtility::process(0x183380, 0xffbc, 0xffbee93c, 0x3,
0xff0ea07c, 0x20), at 0x4e2f8

  [17] main(0x7, 0xffbee93c, 0xffbee95c, 0x17e400, 0x0, 0x0), at 0x4e8f0

(/opt/SUNWspro/bin/../WS6U2/bin/sparcv9/dbx) 

 

I know from past experience the bus error usually indicates invalid memory
alignment.  In this case, however, the memory that it is attempting to free
appears to be aligned properly.  The memory is located at 0x002a57c8.  I am
using Sun Workshop 6 to compile both SQLite and my application.  Does anyone
have an idea of what could be causing the bus error?

 

Joe Mokos

 

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

Re: [sqlite] Insert while select

[Richard Hipp]

On Wed, Jan 26, 2011 at 1:30 PM, Igor Tandetnik  wrote:

> On 1/26/2011 11:09 AM, Rich Rattanni wrote:
> > I am helping someone write an application that utilizes SQLite.  The
> > following code is giving us grief (sqlite lib version 3.5.ish -
> > Windows XP):
> >
> > sqlite3_prepare("select some rows")
> > while (sqlite3_step() == SQLITE_ROW)
> > {
> >  // Do some calc on multiple rows, and occasinally
> >  sqlite3_exec("Insert calculated data into the same table from
> > which we are reading")
> > }
>
> If I recall correctly, this is not allowed in SQLite 3.5.*. This became
> supported in 3.6.x (for some x I don't remember at the moment).
>
> > I had a look at http://www.sqlite.org/lockingv3.html.
>
> The documentation describes the current behavior. Older versions often
> behave differently. 3.5 series are 2.5 years old, a lot of progress has
> been made since then.
>

2.5 calendar years equals 17.5 internet years, right?  :-)


> --
> Igor Tandetnik
>
> ___
> 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

Re: [sqlite] Insert while select

[Igor Tandetnik]

On 1/26/2011 11:09 AM, Rich Rattanni wrote:
> I am helping someone write an application that utilizes SQLite.  The
> following code is giving us grief (sqlite lib version 3.5.ish -
> Windows XP):
>
> sqlite3_prepare("select some rows")
> while (sqlite3_step() == SQLITE_ROW)
> {
>  // Do some calc on multiple rows, and occasinally
>  sqlite3_exec("Insert calculated data into the same table from
> which we are reading")
> }

If I recall correctly, this is not allowed in SQLite 3.5.*. This became 
supported in 3.6.x (for some x I don't remember at the moment).

> I had a look at http://www.sqlite.org/lockingv3.html.

The documentation describes the current behavior. Older versions often 
behave differently. 3.5 series are 2.5 years old, a lot of progress has 
been made since then.
-- 
Igor Tandetnik

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

Re: [sqlite] stored procedures implementation for SQLite

[Chris Wolf]

Thanks for bringing that to my attention - that sample was left over from
when I was trying to use APSW rather then sqlite2.  The actual test program,
sqlite-3.7.3/src/createproc_test.c, is correct.  I updated the blog page
to reflect the proper code.

   -Chris

On Jan 26, 2011, at 12:55 PM, Jim Wilcoxson wrote:

> It looks interesting.  Should your except stmt reference apsw?  -Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://www.hashbackup.com
> 
> 
> On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolf  wrote:
> 
>> 
>> 
>> I know this is an old thread, but shortly after I read it, I attempted to
>> implement
>> stored procedures in SQLite!  I only did it to see if I could, not because
>> I
>> necessarily think it's a good idea...  It's very experimental and not fully
>> implemented,
>> 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
>> 
>> Regards,
>> 
>> Chris Wolf
>> 
>> BareFeetWare wrote:
>>> On 13/11/2010, at 11:14 AM, Scott Hess wrote:
>>> 
 On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare <
>> list@barefeetware.com> wrote:
> IMO, if you're implementing database logic (ie constraints and
>> triggers) in application code, then you're reinventing the wheel, making
>> your package unnecessarily complex and grossly inefficient. If you're just
>> using SQLite to store your data but doing all the logic outside of SQLite,
>> then you might as well just be saving your data to a CSV file or XML. See my
>> previous post for reasoning behind this.
 From http://www.sqlite.org/about.html :
> Think of SQLite not as a replacement for Oracle but as a replacement
>> for fopen()
>>> 
>>> The full paragraph from that page reads:
>>> 
> SQLite is an embedded SQL database engine. Unlike most other SQL
>> databases, SQLite does not have a separate server process. SQLite reads and
>> writes directly to ordinary disk files. A complete SQL database with
>> multiple tables, indices, triggers, and views, is contained in a single disk
>> file. The database file format is cross-platform - you can freely copy a
>> database between 32-bit and 64-bit systems or between big-endian and
>> little-endian architectures. These features make SQLite a popular choice as
>> an Application File Format. Think of SQLite not as a replacement for Oracle
>> but as a replacement for fopen()
>>> 
>>> So I think it's referring to how SQLite stores its data in a local file,
>> rather than on a remote server with which it communicates indirectly. ie
>> "SQLite does not have a separate server process". In that way, SQLite is
>> like fopen rather than Oracle. The same paragraphs mentions SQLite
>> "triggers, and views", freely copying a [self contained] SQLite database
>> between architectures, which allude to my point about putting the logic in
>> the database itself so you can move the whole database between
>> architectures.
>>> 
 So, yes, you might as well just be saving your data to a CSV or XML
>> file.  And I'm sure if you had a package to do that, someone would be
>> arguing about whether your XML should allow for embedded transforms.
>>> 
>>> What do you gain by implementing database logic in the application layer,
>> when it could be done far more efficiently and reliably in the SQL schema?
>> The only thing I can think of is avoiding the (shallow) learning curve. Why
>> re-invent and roll your own integrity checking etc when it's already
>> available and in a way much closer to the data than your application code
>> can get?
>>> 
>>> See my previous post for the contrary argument:
>>> 
>> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html
>>> 
>>> Basically, database logic in the database itself is much faster, causes
>> less error, optimises queries, makes the database portable between
>> application environments or GUI apps. What's not to love?
>>> 
>>> Thanks,
>>> Tom
>>> BareFeetWare
>>> 
>>> --
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] SQLite version 3.7.5 - code freeze

[Richard Hipp]

On Wed, Jan 26, 2011 at 12:20 AM, Roger Binns  wrote:

> -
> A far better solution ... would be to bump
> the VFS version number to 3


Suppose we rig the default VFSes so that they return SQLITE_OK for
SQLITE_FCNTL_SYNC_OMITTED, instead of SQLITE_ERROR, so that your
intermediate layer doesn't throw an exception?  Would that solve your
problem, Roger?

Another solution we talked about was having the xFileControl method in the
VFS object return SQLITE_NOTFOUND instead of SQLITE_ERROR if it gets an
unknown opcode.  That too would solve your problem, wouldn't it?  Would it
break anything else?


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

Re: [sqlite] TEMP TRIGGER and SQLITE_OMIT_TEMPDB

[Richard Hipp]

On Wed, Jan 26, 2011 at 1:02 PM, Marco Bambini  wrote:

> I am trying to use TEMP TRIGGER inside my code but every time I use it I
> receive a "SQL logic error or missing database" error.
> I compiled sqlite with the option SQLITE_OMIT_TEMPDB.
>
> [...]


> so sqlite_temp_master is necessary in order to use TEMP TRIGGER and so I
> can use TEMP TRIGGER only if I recompile sqlite without the
> SQLITE_OMIT_TEMPDB macro.
>
> Anyone can confirm my assumption?
>

Confirmed.  You should be getting a better error message, though.  I'll make
a note to work on that.



> Thanks a lot.
> --
> Marco Bambini
> http://www.sqlabs.com
>
>
>
>
>
>
> ___
> 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] TEMP TRIGGER and SQLITE_OMIT_TEMPDB

[Marco Bambini]

I am trying to use TEMP TRIGGER inside my code but every time I use it I 
receive a "SQL logic error or missing database" error.
I compiled sqlite with the option SQLITE_OMIT_TEMPDB.

but analyzing the source code I can see for example:
#ifndef SQLITE_OMIT_TRIGGER
  /* If there are TEMP triggers on this table, modify the sqlite_temp_master
  ** table. Don't do this if the table being ALTERed is itself located in
  ** the temp database.
  */
  if( (zWhere=whereTempTriggers(pParse, pTab))!=0 ){
sqlite3NestedParse(pParse, 
"UPDATE sqlite_temp_master SET "
"sql = sqlite_rename_trigger(sql, %Q), "
"tbl_name = %Q "
"WHERE %s;", zName, zName, zWhere);
sqlite3DbFree(db, zWhere);
  }
#endif

so sqlite_temp_master is necessary in order to use TEMP TRIGGER and so I can 
use TEMP TRIGGER only if I recompile sqlite without the SQLITE_OMIT_TEMPDB 
macro.

Anyone can confirm my assumption?
Thanks a lot.
--
Marco Bambini
http://www.sqlabs.com






___
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

[Navaneeth.K.N]

On Wed, Jan 26, 2011 at 10:24 PM, Ian Hardingham  wrote:
> Many thanks Eric.
>
> Does a write on Table A block a read/write on Table B?

AFAIK, it does. The lock is acquired on the whole file and not on tables.

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

Re: [sqlite] stored procedures implementation for SQLite

[Jim Wilcoxson]

It looks interesting.  Should your except stmt reference apsw?  -Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com


On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolf  wrote:

>
>
> I know this is an old thread, but shortly after I read it, I attempted to
> implement
> stored procedures in SQLite!  I only did it to see if I could, not because
> I
> necessarily think it's a good idea...  It's very experimental and not fully
> implemented,
> 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
>
> Regards,
>
> Chris Wolf
>
> BareFeetWare wrote:
> > On 13/11/2010, at 11:14 AM, Scott Hess wrote:
> >
> >> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare <
> list@barefeetware.com> wrote:
> >>> IMO, if you're implementing database logic (ie constraints and
> triggers) in application code, then you're reinventing the wheel, making
> your package unnecessarily complex and grossly inefficient. If you're just
> using SQLite to store your data but doing all the logic outside of SQLite,
> then you might as well just be saving your data to a CSV file or XML. See my
> previous post for reasoning behind this.
> >> From http://www.sqlite.org/about.html :
> >>> Think of SQLite not as a replacement for Oracle but as a replacement
> for fopen()
> >
> > The full paragraph from that page reads:
> >
> >>> SQLite is an embedded SQL database engine. Unlike most other SQL
> databases, SQLite does not have a separate server process. SQLite reads and
> writes directly to ordinary disk files. A complete SQL database with
> multiple tables, indices, triggers, and views, is contained in a single disk
> file. The database file format is cross-platform - you can freely copy a
> database between 32-bit and 64-bit systems or between big-endian and
> little-endian architectures. These features make SQLite a popular choice as
> an Application File Format. Think of SQLite not as a replacement for Oracle
> but as a replacement for fopen()
> >
> > So I think it's referring to how SQLite stores its data in a local file,
> rather than on a remote server with which it communicates indirectly. ie
> "SQLite does not have a separate server process". In that way, SQLite is
> like fopen rather than Oracle. The same paragraphs mentions SQLite
> "triggers, and views", freely copying a [self contained] SQLite database
> between architectures, which allude to my point about putting the logic in
> the database itself so you can move the whole database between
> architectures.
> >
> >> So, yes, you might as well just be saving your data to a CSV or XML
> file.  And I'm sure if you had a package to do that, someone would be
> arguing about whether your XML should allow for embedded transforms.
> >
> > What do you gain by implementing database logic in the application layer,
> when it could be done far more efficiently and reliably in the SQL schema?
> The only thing I can think of is avoiding the (shallow) learning curve. Why
> re-invent and roll your own integrity checking etc when it's already
> available and in a way much closer to the data than your application code
> can get?
> >
> > See my previous post for the contrary argument:
> >
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html
> >
> > Basically, database logic in the database itself is much faster, causes
> less error, optimises queries, makes the database portable between
> application environments or GUI apps. What's not to love?
> >
> > Thanks,
> > Tom
> > BareFeetWare
> >
> > --
>
> ___
> 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] Multiple threads sharing one DB

[Simon Slavin]

On 26 Jan 2011, at 4:54pm, Ian Hardingham wrote:

> Does a write on Table A block a read/write on Table B?

SQLite only has one locking thing: it locks the entire database.  So under 
conditions where table A would be locked, all tables would be locked in the 
same way.

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

Re: [sqlite] Insert while select

[Simon Slavin]

On 26 Jan 2011, at 4:09pm, Rich Rattanni wrote:

> sqlite3_prepare("select some rows")
> while (sqlite3_step() == SQLITE_ROW)
> {
>// Do some calc on multiple rows, and occasinally
>sqlite3_exec("Insert calculated data into the same table from
> which we are reading")
> }
> sqlite3_finalize()

Is there a chance that the INSERTs that he's doing would interfere in any way 
with the SELECT ?  In other words, if he executed an indentical SELECT after 
doing all the INSERTs, would be definitely get the same results as he did 
before ?

Simon.
___
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

[Ian Hardingham]

Many thanks Eric.
  
Does a write on Table A block a read/write on Table B?


On 26/01/2011 16:18, Eric Smith wrote:
>
>
> On Wed, Jan 26, 2011 at 11:02 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?
>
>
> Certainly not!  By default, an arbitrary number of readers can execute 
> queries concurrently.  But writers block readers and other writers.
>
> Under the "WAL" journaling mode you can have readers executing 
> concurrently with a writer.  See 
> http://www.sqlite.org/wal.html#concurrency for details on that.
>
> SQLite isn't the king of concurrency by any stretch, but it does much 
> better than at-most-one-querier-at-a-time.
>
> Eric
>
> --
> Eric A. Smith
>
> Dad said, "To fish you must be patient."  Since Dad is not patient,
> and since he was catching no fish, I figured he must be right.
> -- Paige Smith, age 14
>

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

Re: [sqlite] stored procedures implementation for SQLite

[Chris Wolf]

I know this is an old thread, but shortly after I read it, I attempted to 
implement
stored procedures in SQLite!  I only did it to see if I could, not because I 
necessarily think it's a good idea...  It's very experimental and not fully 
implemented,
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

Regards,

Chris Wolf

BareFeetWare wrote:
> On 13/11/2010, at 11:14 AM, Scott Hess wrote:
> 
>> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare  
>> wrote:
>>> IMO, if you're implementing database logic (ie constraints and triggers) in 
>>> application code, then you're reinventing the wheel, making your package 
>>> unnecessarily complex and grossly inefficient. If you're just using SQLite 
>>> to store your data but doing all the logic outside of SQLite, then you 
>>> might as well just be saving your data to a CSV file or XML. See my 
>>> previous post for reasoning behind this.
>> From http://www.sqlite.org/about.html :
>>> Think of SQLite not as a replacement for Oracle but as a replacement for 
>>> fopen()
> 
> The full paragraph from that page reads:
> 
>>> SQLite is an embedded SQL database engine. Unlike most other SQL databases, 
>>> SQLite does not have a separate server process. SQLite reads and writes 
>>> directly to ordinary disk files. A complete SQL database with multiple 
>>> tables, indices, triggers, and views, is contained in a single disk file. 
>>> The database file format is cross-platform - you can freely copy a database 
>>> between 32-bit and 64-bit systems or between big-endian and little-endian 
>>> architectures. These features make SQLite a popular choice as an 
>>> Application File Format. Think of SQLite not as a replacement for Oracle 
>>> but as a replacement for fopen()
> 
> So I think it's referring to how SQLite stores its data in a local file, 
> rather than on a remote server with which it communicates indirectly. ie 
> "SQLite does not have a separate server process". In that way, SQLite is like 
> fopen rather than Oracle. The same paragraphs mentions SQLite "triggers, and 
> views", freely copying a [self contained] SQLite database between 
> architectures, which allude to my point about putting the logic in the 
> database itself so you can move the whole database between architectures.
> 
>> So, yes, you might as well just be saving your data to a CSV or XML file.  
>> And I'm sure if you had a package to do that, someone would be arguing about 
>> whether your XML should allow for embedded transforms.
> 
> What do you gain by implementing database logic in the application layer, 
> when it could be done far more efficiently and reliably in the SQL schema? 
> The only thing I can think of is avoiding the (shallow) learning curve. Why 
> re-invent and roll your own integrity checking etc when it's already 
> available and in a way much closer to the data than your application code can 
> get?
> 
> See my previous post for the contrary argument:
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html
> 
> Basically, database logic in the database itself is much faster, causes less 
> error, optimises queries, makes the database portable between application 
> environments or GUI apps. What's not to love?
> 
> Thanks,
> Tom
> BareFeetWare
> 
> --

___
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

[Eric Smith]

On Wed, Jan 26, 2011 at 11:02 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?
>

Certainly not!  By default, an arbitrary number of readers can execute
queries concurrently.  But writers block readers and other writers.

Under the "WAL" journaling mode you can have readers executing concurrently
with a writer.  See http://www.sqlite.org/wal.html#concurrency for details
on that.

SQLite isn't the king of concurrency by any stretch, but it does much better
than at-most-one-querier-at-a-time.

Eric

--
Eric A. Smith

Dad said, "To fish you must be patient."  Since Dad is not patient,
and since he was catching no fish, I figured he must be right.
-- Paige Smith, age 14
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert while select

[Rich Rattanni]

I am helping someone write an application that utilizes SQLite.  The
following code is giving us grief (sqlite lib version 3.5.ish -
Windows XP):

sqlite3_prepare("select some rows")
while (sqlite3_step() == SQLITE_ROW)
{
// Do some calc on multiple rows, and occasinally
sqlite3_exec("Insert calculated data into the same table from
which we are reading")
}
sqlite3_finalize()

I had a look at http://www.sqlite.org/lockingv3.html.  After a quick
read, I would think that the prepare or step put a SHARED lock on the
database.
Now it is sqlite3_exec's turn to run, so he may acquire at most a
PENDING lock?  I would come to this conclusion since the select is
still active
so the SHARED lock exists.  Then the sqlite3_finalize() allows the
SHARED lock to be released, which then allows the PENDING to promote
to
EXCLUSIVE and the data to finally commit?

I am kind of throwing out my analysis and looking for a yes or no.  My
final statement for which I request validation... So unless I wrap my
data
in explicit begin/commits the data is not, in fact guaranteed to be
safely on disk?  If my program were to crash before the
sqlite3_finalize then
is the data from the selects lost?

One last question:  The locking documentation says that at some time
some cache may fill up which then causes the process to wish to commit
data.
If the above while loop generated enough insert statements to fill up
this cache, would the this loop deadlock?  A better way to ask this:
Is nesting a
insert statement in a select loop dangerous?

Thanks for taking the time to read this.
___
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] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Marian Cascaval]

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

Re: [sqlite] forcing flush of journal

[Simon Slavin]

On 26 Jan 2011, at 2:08pm, Paul Sanderson wrote:

> I have an sqlite database and an assocaiated .journal file that I need
> tolook at. I have estalished that there is data in the journal that
> seems relevant and want flush the journal (if that is the correct
> term) and to then view the tables with the updated data. Is there a
> way to do this?

Use any program that uses the standard SQLite routines to open the data file -- 
for example the sqlite3 command-line tool.  The application will automatically 
recover anything that can be recovered from the journal file.

If you need to distinguish between stuff in the database and stuff in the 
journal, take copies before doing anything, and open a copy of the database 
from a folder where you have /not/ copied the journal file.

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

[sqlite] forcing flush of journal

[Paul Sanderson]

I have an sqlite database and an assocaiated .journal file that I need
tolook at. I have estalished that there is data in the journal that
seems relevant and want flush the journal (if that is the correct
term) and to then view the tables with the updated data. Is there a
way to do this?

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

[sqlite] ANN: SQLite Data Wizard 11.1 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite Data Wizard 11.1, a 
powerful Windows GUI solution for SQLite data management.

The new version is immediately available at
http://www.sqlmaestro.com/products/sqlite/datawizard/

SQLite Data Wizard provides you with a number of easy-to-use wizards to 
convert any ADO-compatible database to the SQLite database, import data into 
SQLite tables, export data from tables, views and queries to most popular 
file formats as well as generate data-driven ASP.NET pages for your SQLite 
database.

New features
=

1. Data Import: import from ODBC data sources has been implemented. This 
allows you to import data from any database accessible via an ODBC driver or 
OLE DB provider to SQLite.

2. Data Import: the Insert-or-Update mode has been added.

3. Data Import: now it is possible to specify SQL scripts to be executed 
before and/or after data import to each table.

4. Data Pump: starting with this version it is possible to define naming 
conventions for transferring metadata objects.

5. Data Pump: new version allows you to restrict number of pumped records, 
view source table data as well as count number of records in the source 
tables.

6. Data Export: when exporting to MS Access data from all the exported 
tables/views/queries are stored to a single target database.

7. Common: this release has been successfully tested with 64-bit operating 
systems including Windows 7 x64.

In addition to this, several bugs have been fixed and some other minor 
improvements and corrections have been made. Full press-release is available 
at:
http://www.sqlmaestro.com/news/company/data_wizards_are_updated_to_version_11_1/

Background information:
---
SQL Maestro Group offers complete database admin, development and management 
tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, 
Firebird and MaxDB providing the highest performance, scalability and 
reliability to meet the requirements of today's database applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com

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

Re: [sqlite] Memory bloats when attaching database with WAL enabled

[Simon Slavin]

On 25 Jan 2011, at 5:08pm, Akbar Syed wrote:

> I stop my application and restart it again. During restart, since the
> databases are already existing, I do open all the databases (DB1, DB2 and
> DB3). And Attach DB2 and DB3.

Hold on.  If you're doing the attaching with an 'ATTACH' command there's no 
need to separately open the databases.

Simon.
___
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()?

[Igor Tandetnik]

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] how to create sqlite3_value structure to be used with sqlite3_bind_value()?

[Bella Chan]

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.  Trying to see if I 
can use bind_value() instead but no clue ho to create the sqlite3_value 
structure.
Thanks,
Bella
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Memory bloats when attaching database with WAL enabled

[Akbar Syed]

I have switched to WAL in my application for I am quite impressed with its
write performances when compared to JOURNALING. After a bit struggle with
checkpointing, I could successfully adopt it, yet I encountered a strange
problem.
Here is the scenario of my application
I have more than one database in my application. (Let's say DB1, DB2 and
DB3)
When I start my application, I create all the databases (not memory based)
and keep the connections open. I attach DB2 and DB3 to DB1. All the
connections are open.
I perform database write operations. The memory usages are under the control
(approx 5MB)
I stop my application and restart it again. During restart, since the
databases are already existing, I do open all the databases (DB1, DB2 and
DB3). And Attach DB2 and DB3.
And during this attach i.e, with the very first attach of DB2 to DB1, the
memory usage has increased a lot (upto 64MB).

I have not done any memory optimizations yet, but this is quite strange
issue. I have not observed this problem when I was using Journaling.

I appreciate if someone provides me some information what might be going
wrong when WAL coupled with ATTACH?
I shall be thankful for your feedback and suggestions.

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

[sqlite] Auto Reply: sqlite-users Digest, Vol 37, Issue 26

[alexander . gorrod]

This is an auto-replied message. I am currently unavailable. I will be back at 
work on Monday 31st January. For urgent questions please contact Michael Brey 
(michael.b...@oracle.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting a table's field labels with Perl, DBI

[BareFeetWare]

On 26/01/2011, at 8:43 PM, John Delacour wrote:

> I did try a few things along those lines and seemed to come across 
> the problem mentioned on that page : "...Unfortunately, 
> INFORMATION_SCHEMA views are seldom supported by the DBMS..." 

Yes, it is very unfortunate that INFORMATION_SCHEMA or similar is not provided 
by SQLite, a topic that keeps recurring here in one form or another. Yu 
currently have to do your own parsing of the schema to get much of the info 
about columns, triggers, foreign keys 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] Getting a table's field labels with Perl, DBI

[John Delacour]

At 22:41 -0800 25/01/2011, Darren Duncan wrote:

>How long have you been using Perl?
>
>Anyway, to start with I would replace the last couple lines with:
>
>my $catalog_rowset = $dbh->selectall_arrayref("PRAGMA 
>table_info(contacts)") ;
>my @col_names = map { $_->[1] } @{$catalog_rowset};
>print join ', ', @col_names;

Yes, that's neat, and an encouragement to me to get 'map' into my 
head.  I've been using Perl long enough to know better but it's only 
when I need to advance my knowledge for a real task that I push my 
learning to meet the occasion.


>Another thing you can try is use DBI's special methods for basic 
>system catalog information, rather than using a SQL query to get 
>that information as you did, not that the way you did it is wrong 
>per se, but just an alternate means to the end.
>
>I refer to http://search.cpan.org/dist/DBI/DBI.pm#Catalog_Methods :
>
>column_info ...


I did try a few things along those lines and seemed to come across 
the problem mentioned on that page : "...Unfortunately, 
INFORMATION_SCHEMA views are seldom supported by the DBMS..." 
Nevertheless I obviously need to read DBI.pm more thoroughly.

Thank you.

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