Re: [sqlite] Mixing SQlite DLL versions

[Jay A. Kreibich]

On Wed, Feb 18, 2009 at 05:33:50PM -0500, Igor Tandetnik scratched on the wall:
> jose isaias cabrera 
> wrote:
> > Greetings and salutations.
> >
> > Can one SQLite db be UPDATEd and used by folks using different DLL
> > versions?
> 
> All 3.* versions use the same file format, and can happily coexist.

  Not exactly.  The file format changed with 3.3.0.  Files made with
  3.3.0 and later cannot be read by earlier 3.x versions.  Older files
  can be read by newer versions, however.  That was over three years
  ago, however.

  You can also create older style files with newer libraries using
  "PRAGMA legacy_file_format" or the SQLITE_DEFAULT_FILE_FORMAT
  compile-time option.



  In the case of the original poster with 3.5.4 and 3.6.11, this
  shouldn't be an issue.  That's not the whole story, however.  Don't
  forget that the SQL is re-parsed every time the database is open.
  If you use new(er) SQL language features to define a table,
  expression, or query (in a VIEW, for example), that syntax might
  not be understood by older versions.  Even if the file format is
  technically compatible, language incompatibilities can keep the
  older versions from accessing the database.
  
   -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] Database path in widows

[Dennis Cote]

Jibin Scaria wrote:
> I am facing problem with spaces in the database path, able to open database
> but queries are returning "no such table: table name".
>
>
>   
If your table names contain embedded spaces you must quote the name in 
your SQL queries

select * from "table name";

HTH
Dennis Cote

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

[sqlite] Locking design

[Simon]

Hi there,
  I've been thinking about locking a lot lately and I'm having some
trouble...  I'd like to show my 'locking design' based on what I know
of sqlite3 to see if I understand every possible aspects of locking.

For the following, let's use a single database file, shared through
the network (say with NFS or anything similar) and many different
computers/threads connecting to this database file.  (So one file, no
replication, all transaction on the same single file).  The operations
would be read mostly, and rarely writes within the same table.

Readers will always try to get a SHARED lock, but if there is already
a writter with its EXCLUSIVE lock, then the readers will fail with
SQLITE_BUSY.  The process attempting to read should then retry to get
the SHARED lock over and over after a little delay...  correct?
This is done automatically (except for the retries) by doing a simple
query (no transactions)... correct?

Writers will try to get a PENDING lock until it converts automagically
into an EXCLUSIVE lock (once all SHARED locks are released).  Writting
is done and the lock is released allowing readers to get their SHARED
locks.  (I believe if there is already a pending lock, it will get the
SQLITE_BUSY and will have to retry?)
This is done automatically by starting a transaction (+commit)...  correct?

It really seem simple, but somehow, I can't believe it... imagine a
situation where there's 5000 threads reading most of the time and 5%
of them try to write...  there will be a moment where many many
threads will just keep retrying to get their respective locks (some
kind of contention)...   wouldn't it be better to (or is it possible)
to setup a locking queue?  I would like to write: you are 5th in line,
the 4 before you want to read.

Another way would be to use versioning.  I know SQLite3 doesnt support
versionning, but is there a way to emulate it?  Say, making a database
design in which only reads and inserts are possible (upates are
inserts of a new version of the same entry)...  the reader would query
the latest insert ID somehow and query the data below this id...  but
even this would still require locking.

I've been reading the docs on SQLite3 on the locking
(http://www.sqlite.org/lockingv3.html) but I would still like to read
more about locking designs... could anyone recommend a link to such a
design discussion?  Not necessarilly based on sqlite, as long as it
can be applied to it...

Thanks in advance for your corrections and suggestions!
  Simon

-- 
When Earth was the only inhabited planet in the Galaxy, it was a
primitive place, militarily speaking.  The only weapon they had ever
invented worth mentioning was a crude and inefficient nuclear-reaction
bomb for which they had not even developed the logical defense. -
Asimov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mixing SQlite DLL versions

[Igor Tandetnik]

jose isaias cabrera 
wrote:
> Greetings and salutations.
>
> Can one SQLite db be UPDATEd and used by folks using different DLL
> versions?

All 3.* versions use the same file format, and can happily coexist.

Igor Tandetnik 



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

[sqlite] Mixing SQlite DLL versions

[jose isaias cabrera]

Greetings and salutations.

Can one SQLite db be UPDATEd and used by folks using different DLL versions?

Long story, but here is the short story...

Our department here uses a tool that I created for PM'ing.  The D SQLite 
wrapper that the program uses works fine with v3.6.11.  The program is being 
used by a few folks that I can not see every day and so, they are using DLL 
version 3.5.4 with live data.  What I would like to do is to update the 
folks that I can see daily with v3.6.11 and send an email to the others to 
update their DLL.

Will this mixing of DLL usage cause any problem with the data (corruption, 
misalignment, loss of data, etc) by having folks connect to the databases 
using different DLL versions?

thanks,

josé 

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

[sqlite] How do I avoid getting “ database is locked ” when using sqlite3 via PDO?

[jepd2v8nnyhoet]

when using sqlite, it waits 0 seconds when another transaction is active.

anyone have a sample code to avoid to get

"database is locked" when PHP + PDO + sqlite3?

The expected database function is this:

(1)transaction A is active. 
(2)transaction B attempts to get lock 
(3)but A is active, so B can not get lock 
(4)B have to wait seconds 
(5)A finish his work 
(6)B can finally get lock

if you use MySQL, it work like above.

But if you use SQLite, it DO NOT work like above.

This cause problem.



--
Power up the Internet with Yahoo! Toolbar.
http://pr.mail.yahoo.co.jp/toolbar/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test1

[jepd2v8nnyhoet]

test1

--
Power up the Internet with Yahoo! Toolbar.
http://pr.mail.yahoo.co.jp/toolbar/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] number of columns in a table

[Martin Engelschalk]

Hi,

see http://www.sqlite.org/capi3ref.html#sqlite3_column_count.

Do this:

sqlite3_stmt *pStmnt;
sqlite3_prepare(YourDbHandle, "select * from YourTable limit 1", -1, 
, 0);
int NumberOfColumns = sqlite3_column_count(pStmnt);
sqlite3_finalize(pStmnt)

Martin

baxy77bax wrote:
> hi,
>
> my question is how to get the number of columns in an existing table.
> example if i can retrieve the number of rows with SELECT count(*) FROM table
> , is there also some simple query to do the same thing for counting the
> number of columns
>
> thnx
>
> r
>
>   

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

Re: [sqlite] number of columns in a table

[Griggs, Donald]

hi,

my question is how to get the number of columns in an existing table.
example if i can retrieve the number of rows with SELECT count(*) FROM
table , is there also some simple query to do the same thing for
counting the number of columns


==

Hello, "r"

You can use sql to query the system table named:  sqlite_master
Also see
   http://www.sqlite.org/pragma.html#schema

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

[sqlite] number of columns in a table

[baxy77bax]

hi,

my question is how to get the number of columns in an existing table.
example if i can retrieve the number of rows with SELECT count(*) FROM table
, is there also some simple query to do the same thing for counting the
number of columns

thnx

r

-- 
View this message in context: 
http://www.nabble.com/number-of-columns-in-a-table-tp22086144p22086144.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite version 3.6.11

[D. Richard Hipp]

On Feb 18, 2009, at 5:04 AM, Marco Bambini wrote:

> What about backup of encrypted databases?

The backup API works with SEE-encrypted databases and with CEROD  
databases.

D. Richard Hipp
d...@hwaci.com



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

[sqlite] MacOSX version 3.6.11 vs 3.6.6.2

[Sylvain Pointeau]

Hi,

Waooo I observed my db running from 11 min to 5 min
that's really great, many thanks,

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

Re: [sqlite] SQLite.Exception: not authorized (to load a shared librarywith SQLite Java Wrapper/JDBC Driver)

[Christian Werner]

Bejay Bamboo wrote:
> 
> Hello,
> i'm trying to load new SQL functions from a shared library with the
> help of the SQLite Java
> Wrapper/JDBC Driver (http://www.ch-werner.de/javasqlite). I'm trying
> 
> java -cp :. -Djava.library.path=/tmp/javasqlite-20090213/.libs
> SQLite.Shell db "select
> load_extension('/tmp/spatialite-2.2-linux-x86-libs/lib/libspatialite.so')"
> 
> I get the following SQL Error:
> 
> SQL Error: SQLite.Exception: not authorized
> 
> Same error in the small test program
> (http://www.ch-werner.de/javasqlite/test.java) i ve added the
> following line:
> 
> db.exec("select
> load_extension('/tmp/spatialite-2.2-linux-x86-libs/lib/libspatialite.so')",new
> test());
> 
> The following statements are fully working  in sqlite:
> sqlite> select 
> load_extension('/tmp/spatialite-2.2-linux-x86-libs/lib/libspatialite.so');
> sqlite> .load /tmp/spatialite-2.2-linux-x86-libs/lib/libspatialite.so
> 
> Did I misunderstood something? Did i trying something which is not
> provided by the SQLite Java
> Wrapper/JDBC Driver?

In order to enable loading an extension the SQLite API
sqlite_enable_load_extension() must be called but there's
currently no method to call it from Java code for 
security reasons.

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

[sqlite] multi-threading multi-db problem

[Christian Barth]

Hello!
I use the latest (3.6.11) version of the SQLite dll in a WinXP 
Application.
The main task of this application is to store values from a 
sensor-hardware in multiple databases. Each DB contains a subset from the 
Values.
I tested serveral design options. One is to read all values from the senor 
hardware and split them to the different DBs.

operational sequence:
--main--  create and init 2 databases in the main-function.
--main--  create 2 Threads and pass the 2 DB-handle parameter (one thread 
per DB!!!)
--thread-- fill the DBs with the values

In the last step occurs an Error. Sometimes the threads dont write in the 
correct DB!!!

I inserted 2 values in one DB. 
DB0.db have to look like this: index | subid | time | 0.0  <---
DB1.db have to look like this: index | subid | time | 1.0  <---

What i got was: 
DB0.db:
sqlite> SELECT * FROM ArchiveLog WHERE Value==1;
11438|37|33:10.897|1.0
14180|19|33:13.647|1.0
19854|29|33:19.241|1.0

DB1.db
sqlite> SELECT * FROM ArchiveLog WHERE Value==0;
18719|16|33:18.101|0.0
The other Values in the DBs are correct.


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

Re: [sqlite] AUTOINCREMENT documentation.

[Marcin Walkowiak - Work (local #2)]

> 
> Not exactly,
> in monotonically increasing sequence next element is always smaller than
> current.
> 

I mean larger :-)

Sorry,
KoD

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

Re: [sqlite] AUTOINCREMENT documentation.

[Marcin Walkowiak - Work (local #2)]

Troeger, Thomas (ext) wrote:
> Hello,
> 
> Sorry if this has been asked before, I couldn't find any reference to it
> in the list archives. I've found a small bug in the documentation, I
> wanted to mention it since I think it should be changed accordingly.
> 
> In http://www.sqlite.org/autoinc.html the documentation says:
> 
> """
> The normal ROWID selection algorithm described above will generate
> monotonically increasing unique ROWIDs ...
> ...
> The AUTOINCREMENT Keyword
> ...
> Note that "monotonically increasing" does not imply that the ROWID
> always increases by exactly one. One is the usual increment. ...
> """
> 
> AFAIK, in a monotonically increasing sequence a value is larger *or
> equal* to the previous value, while in a *strictly* monotonically
> increasing sequence the next value is always larger (see
> http://en.wikipedia.org/wiki/Monotonic_function). This is maybe
> nitpicking, but I think the documentation should be fixed.
> 
> Regards,
> T.
> 
> P.S.: I hope this is the right place to ask such things, I couldn't find
> any documentation improvement link, else I would comment on some
> spelling errors as well ;-)
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

Not exactly,
in monotonically increasing sequence next element is always smaller than
current.

Sequence where consecutive elements can be equal is called monotonically
nondecreasing sequence.

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

[sqlite] SQLite.Exception: not authorized (to load a shared library with SQLite Java Wrapper/JDBC Driver)

[Bejay Bamboo]

Hello,
i'm trying to load new SQL functions from a shared library with the
help of the SQLite Java
Wrapper/JDBC Driver (http://www.ch-werner.de/javasqlite). I'm trying

java -cp :. -Djava.library.path=/tmp/javasqlite-20090213/.libs
SQLite.Shell db "select
load_extension('/tmp/spatialite-2.2-linux-x86-libs/lib/libspatialite.so')"

I get the following SQL Error:

SQL Error: SQLite.Exception: not authorized

Same error in the small test program
(http://www.ch-werner.de/javasqlite/test.java) i ve added the
following line:

db.exec("select
load_extension('/tmp/spatialite-2.2-linux-x86-libs/lib/libspatialite.so')",new
test());


The following statements are fully working  in sqlite:
sqlite> select 
load_extension('/tmp/spatialite-2.2-linux-x86-libs/lib/libspatialite.so');
sqlite> .load /tmp/spatialite-2.2-linux-x86-libs/lib/libspatialite.so

Did I misunderstood something? Did i trying something which is not
provided by the SQLite Java
Wrapper/JDBC Driver?

I would be happy, if anyone can give me some tips. Is there a separate
mailing list for SQLite Java
Wrapper/JDBC Driver? I didn't find any. Should I contact the author directly?

bejay

Background:
---
javasqlite-20090213
Sqlite-LIB version: 3.6.11
Sqlite-DB version: 3.6.11

java version "1.6.0_12"
Java(TM) SE Runtime Environment (build 1.6.0_12-b04)
Java HotSpot(TM) Server VM (build 11.2-b01, mixed mode)

Running on a GNU\Linux Debian Lenny.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] AUTOINCREMENT documentation.

[Troeger, Thomas (ext)]

Hello,

Sorry if this has been asked before, I couldn't find any reference to it
in the list archives. I've found a small bug in the documentation, I
wanted to mention it since I think it should be changed accordingly.

In http://www.sqlite.org/autoinc.html the documentation says:

"""
The normal ROWID selection algorithm described above will generate
monotonically increasing unique ROWIDs ...
...
The AUTOINCREMENT Keyword
...
Note that "monotonically increasing" does not imply that the ROWID
always increases by exactly one. One is the usual increment. ...
"""

AFAIK, in a monotonically increasing sequence a value is larger *or
equal* to the previous value, while in a *strictly* monotonically
increasing sequence the next value is always larger (see
http://en.wikipedia.org/wiki/Monotonic_function). This is maybe
nitpicking, but I think the documentation should be fixed.

Regards,
T.

P.S.: I hope this is the right place to ask such things, I couldn't find
any documentation improvement link, else I would comment on some
spelling errors as well ;-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.11

[Marco Bambini]

What about backup of encrypted databases?
-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





On Feb 18, 2009, at 1:28 AM, D. Richard Hipp wrote:

> SQLite version 3.6.11 is now available from the SQLite website:
>
>  http://www.sqlite.org/
>
> Version 3.6.11 adds support for a new live-backup API which enables
> applications to make backup copies of SQLite databases while the
> database is in use.  There are also improvements to the documentation
> and various obscure bug fixes.
>
> As always, please let us know if you encounter any difficulties.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] Little SQL help please..

[johnny depp (really!)]

Ok, thats good to know!

thx Igor!





Dan Kennedy-4 wrote:
> 
> 
> On Feb 18, 2009, at 1:27 AM, johnny depp (really!) wrote:
> 
>>
>> I didn't run it yet, the idea of using min(col1) = max(col1) was all I
>> needed.
>> I assumed it was incorrect because I thought
>> referring to an ungrouped column in a group by was incorrect,  
>> because the
>> ungrouped col would represent a set, and not a value...
>>
>> I just ran this:
>>
>> select c2 from
>> (select 1 as c1, 1 as c2
>> union
>> select 1 as c1, 2 as c2
>> union
>> select 1 as c1, 3 as c2)
>> group by c1
>>
>> and it returns 3.
>>
>> I think this is incorrect behaviour and should not compile because
>> the result of c2 is clearly {1, 2, 3}...or am I wrong about this?
>> Is this standard SQL languages behaviour?
> 
> You are correct, according to SQL this is invalid. But SQLite and other
> database engines allow it because it is convenient. In such a situation
> SQLite selects a value arbitrarily from the set to return.
> 
> 
> 
> 
> 
>> Igor Tandetnik wrote:
>>>
>>> "johnny depp (really!)"
>>>  wrote in
>>> message news:22057169.p...@talk.nabble.com
 You probably meant:

 select col1, case when min(col2) = max(col2) then min(col2) else  
 'not
 the same' end
 from mytable group by col1;
>>>
>>> It works for me as originally written. Do you get any errors?
>>>
>>> Igor Tandetnik
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> -- 
>> View this message in context:
>> http://www.nabble.com/Little-SQL-help-please..-tp22052925p22063324.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Little-SQL-help-please..-tp22052925p22075596.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Ann: APSW 3.6.11-r1 released

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

APSW 3.6.11-r1 is now available. The home page is at
http://code.google.com/p/apsw/ which includes full documentation, source
and binary distributions for Windows (Python 2.3 onwards including 3.0).

APSW is a wrapper around SQLite that provides all SQLite API
functionality in Python.  It is not DBAPI compliant as it provides
SQLite semantics.  pysqlite provides DBAPI semantics.  You can see the
two approaches contrasted at
http://apsw.googlecode.com/svn/publish/pysqlite.html

Changelist is below and a clickable version at
http://apsw.googlecode.com/svn/publish/changes.html

You can now use the hot backup functionality introduced in SQLite 3.6.11
- - http://www.sqlite.org/backup.html

Updated a VFS test to reflect changes in SQLite underlying error
handling. (Previously SQLite almost always returned FullError on any
write that had an error but now returns SQLError.)

Changed close methods so that Connections can be released earlier.

In prior releases a closed cursor could still be used (reincarnated).
That is no longer the case and you will get CursorClosedError.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkmb2zsACgkQmOOfHg372QStgwCg5M44QX78NK303hGi3ru5tgRM
QRMAoMe2+slnjsaDICYB0LBt5viA1bh5
=Khh7
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users