Re: [sqlite] values containing dash - not evaluated

[jason d]

On Mon, Apr 26, 2010 at 7:59 PM, Black, Michael (IS)
 wrote:
>
> First off confirm it's not a bug with sqlite2:
>
Michael , thank you for this checklist.
 Here is what I have.

>
> sqlite> create table Groups (name varchar(10));
> sqlite> insert into Groups values('bob');
> sqlite> insert into Groups values('jean-baptiste');
> sqlite> select * from Groups where name='jean-baptiste';
> jean-baptiste
>
> If you don't get a results this way tje sqlite2 is the problem (which I 
> doubt).

I get the result perfectly. So this is not sqlite2 issue. I doubt it
could be sqlite issue so I never looked at it this way. But worth a
shot.

>
> Then do an sql .dump of your table.
> sqlite> .dump Groups
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE Groups (name varchar(10));
> INSERT INTO "Groups" VALUES('bob');
> INSERT INTO "Groups" VALUES('jean-baptiste');
> COMMIT;
>
> Then you should be able to see the SQL representation of the string and 
> perhaps see what your problem is.
Ok, i tried this suggestion on both the test table we made above and
my own current DB. I see the dashes.


>
> I don't know if sqlite2 has the .mode command, but if it does it's simpler 
> yet.
>
> sqlite> .mode insert
> sqlite> select * from Groups where name like('%jean%');
> INSERT INTO table VALUES('jean-baptiste');
>
I did not go this far. Weirdly enough all your testing showed me the
real cause. The query egenrated by PHP uses quotes around column names
for select statements. so if I use
sqlite> Select * from Groups where 'name' = 'Jean-baptiste';
// will not work
sqlite> Select * from Groups where name = 'Jean-baptiste';
// works
sqlite> Select * from "Groups" where "name" = 'jean-baptiste';
//works for double quotes around tabel and column names.

This is weird, just the day I posted all the problems I used double
quotes. I even tried with various combos.  The PHP framework I have
been using always generates the last combination of quotes so it
should have worked. I don't know the reason for this happening or
something has changed since then that I cannot find. I am going to run
the the queries again to see if I get results in PHP, if not this is
not an sqlite problem anymore, mor elikely a driver or framework
issue.
Either that or I am incredibly stupid for having wasted everyone's
time. Apologies if that is the case.

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

On Mon, Apr 26, 2010 at 8:23 PM, Simon Slavin  wrote:

>
> On 26 Apr 2010, at 1:04pm, Michal Seliga wrote:
>
> > i had similar problems and it was caused by microsoft office
> > it didn't used ordinary dash but some strange character with different
> > ascii code - so search based on it always failed
> > i had to convert these strange dashes to ordinary ones to make it work
> > try, maybe this is also your case
>
> Good catch.  MS Office, under some circumstances, automatically replaces
> the '-' (minus sign) character with a hyphen ('‐').  Technically the hyphen
> is the right character to use to join two words, but since it doesn't have
> an easy key-combination many people don't type it and like the automatic
> conversion that Office does.  Annoyingly neither of these are actually
> dashes: there are n-dash ('–') and m-dash ('—') characters too.  So there
> are four characters that all look similar but do not have the same hash
> value in normal text processing.
>
> Simon.
>
> PS: Don't get me started on figure-dashes and graphical horizontal lines.
>  Unicode should not include graphical icons.  Bah humbug.
>
>
Michal and Simon, Yes this is what Igor pointed out too.

Igor, I am sorry I havent had the chance to write a test to check Hex values
dump yet.
by the way I used the dash on my keyboard which is next to the number 0 if
that helps what you guys are talking about. I use eclipse or notepad++ on
windows and Nano on Linux to code as I move from place to place. all of them
show me a dash that works everywhere. in fact when I place the values
retrieved from SQlite Select * query, on the telnet request to a server I
get the reply correctly for the values containing dash. for some reason only
SQLite2 is reporting what no else can see. Let me get that Hex dump so
things will get clearer. thanks a lot
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] releasing EXCLUSIVE lock after writing dirty pages from the memory cache into the DB ?

[Richard Hipp]

On Wed, Apr 28, 2010 at 5:22 PM, Toby Ferguson wrote:

> Guillame,
>
> If you have a highly concurrent application then Oracle's latest release of
> BDB might be what you're looking for. It has a SQLite integration (version
> 3.6.23, to be precise) and combines the best of SQLite (API, SQL support
> etc.) with the best of BDB (concurrency, scaling and performance). It's not
> a SQLite code branch; it's a SQLite integration. So if concurrency is an
> issue for you then I'd suggest you take a look:
>
> http://www.oracle.com/technology/products/berkeley-db/index.html
>
> This integration was performed by Dr. Hipp


Actually, neither I nor anybody else on the SQLite core team had anything to
do with the BDB port of the SQLite front-end.  That was a 100% Oracle
undertaking.  We didn't even know about it until it was nearly complete.



> and is offered under a dual-license - an open-source non-commercial
> license, or a commercial and fully supported license.
>
> (In full disclosure, I am a Senior SC supporting Berkeley at Oracle, and I
> get a commission on BDB sales.)
>
> Toby Ferguson
> -Original Message-
> From: Guillaume Duranceau [mailto:guillaume.duranc...@amadeus.com]
> Sent: Wednesday, April 28, 2010 8:20 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] releasing EXCLUSIVE lock after writing dirty pages from
> the memory cache into the DB ?
>
> Hello all,
>
> While running a SQLite transaction writing into the DB (thus holding the
> RESERVED lock), in case the memory cache becomes full, SQLite will try to
> write the content of a dirty page into the DB. To do so, it promotes the
> RESERVED lock to EXCLUSIVE. This can be annoying because afterwards, the
> EXCLUSIVE lock will be released only when the transaction will finally be
> committed. In the meantime, database access to readers will be prohibited.
>
> This behaviour is described at http://www.sqlite.org/lockingv3.html,
> chapter 5.0 "Writing to a database file":
>
> 
> If the reason for writing to the database file is because the memory cache
> was full, then the writer will not commit right away. Instead, the writer
> might continue to make changes to other pages. Before subsequent changes
> are written to the database file, the rollback journal must be flushed to
> disk again. Note also that the EXCLUSIVE lock that the writer obtained in
> order to write to the database initially must be held until all changes
> are committed. That means that no other processes are able to access the
> database from the time the memory cache first spills to disk until the
> transaction commits.
> 
>
> I'm wondering why the EXCLUSIVE lock is not downgraded to a RESERVED lock
> right after writing the dirty page into the DB. This doesn't seem to me as
> an undoable task (the transition EXCLUSIVE->RESERVED would simply need to
> be managed by xUnlock functions), but there might be technical/conceptual
> reasons preventing to do so. What are your views on this ?
>
> Regards  -  Guillaume
> ___
> 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
>



-- 
-
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] releasing EXCLUSIVE lock after writing dirty pages from the memory cache into the DB ?

[Toby Ferguson]

Guillame,

If you have a highly concurrent application then Oracle's latest release of BDB 
might be what you're looking for. It has a SQLite integration (version 3.6.23, 
to be precise) and combines the best of SQLite (API, SQL support etc.) with the 
best of BDB (concurrency, scaling and performance). It's not a SQLite code 
branch; it's a SQLite integration. So if concurrency is an issue for you then 
I'd suggest you take a look: 

http://www.oracle.com/technology/products/berkeley-db/index.html

This integration was performed by Dr. Hipp and is offered under a dual-license 
- an open-source non-commercial license, or a commercial and fully supported 
license.

(In full disclosure, I am a Senior SC supporting Berkeley at Oracle, and I get 
a commission on BDB sales.)

Toby Ferguson
-Original Message-
From: Guillaume Duranceau [mailto:guillaume.duranc...@amadeus.com] 
Sent: Wednesday, April 28, 2010 8:20 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] releasing EXCLUSIVE lock after writing dirty pages from the 
memory cache into the DB ?

Hello all,

While running a SQLite transaction writing into the DB (thus holding the 
RESERVED lock), in case the memory cache becomes full, SQLite will try to 
write the content of a dirty page into the DB. To do so, it promotes the 
RESERVED lock to EXCLUSIVE. This can be annoying because afterwards, the 
EXCLUSIVE lock will be released only when the transaction will finally be 
committed. In the meantime, database access to readers will be prohibited.

This behaviour is described at http://www.sqlite.org/lockingv3.html, 
chapter 5.0 "Writing to a database file":


If the reason for writing to the database file is because the memory cache 
was full, then the writer will not commit right away. Instead, the writer 
might continue to make changes to other pages. Before subsequent changes 
are written to the database file, the rollback journal must be flushed to 
disk again. Note also that the EXCLUSIVE lock that the writer obtained in 
order to write to the database initially must be held until all changes 
are committed. That means that no other processes are able to access the 
database from the time the memory cache first spills to disk until the 
transaction commits. 


I'm wondering why the EXCLUSIVE lock is not downgraded to a RESERVED lock 
right after writing the dirty page into the DB. This doesn't seem to me as 
an undoable task (the transition EXCLUSIVE->RESERVED would simply need to 
be managed by xUnlock functions), but there might be technical/conceptual 
reasons preventing to do so. What are your views on this ?

Regards  -  Guillaume
___
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 Wrapper

[Toby]

Java is written in C (at least, the JVM is). You can connect from Java
to C using the Java Native Interface (JNI) - docs here:
http://java.sun.com/docs/books/jni/

Here at Oracle we use it all the time - it is slower than writing
directly in C, but allows one to extend Java to use the facilities in
a C library in a very useful manner.

Toby

On Apr 14, 4:23 am, Andreas Henningsson
 wrote:
> Looked at the source code. It is some parts in C also. I did not know that
> is
> was possible to mix java and C.
>
> You have using a very rare platform. Why do you use mips?
>
> My guess is that you have to build it yourself.
>
> /Andreas
>
> On Wed, Apr 14, 2010 at 11:16 AM, Andreas Henningsson <
>
>
>
>
>
> andreas.hennings...@gmail.com> wrote:
> > The homepage say this
>
> > "Jar file containing binaries for: Windows, Linux/x86/amd64, and Mac OS
> > X/ppc/x86/amd64"
>
> > I don't know how it works. If a SQLite binary is includen in the jar file
> > and it's this binary they talk about.
> > I mean, if it is written in java it should work as long you have a Java
> > runtime of the same version. Right?
>
> > You have have to investigate it.
>
> > Public license yes.
>
> > Performance was ok for me but I did not put it under heavy load.
>
> > /Andreas
>
> > On Wed, Apr 14, 2010 at 10:51 AM, Navaneeth Sen B <
> > navanee...@tataelxsi.co.in> wrote:
>
> >> Hi Andreas,
>
> >> I would like to know a couple of things about the wrapper you used :
>
> >>   1. Is that available for mips-linux?
> >>   2. Is it available under public license?
> >>   3. Does it have any performance issues?
>
> >> Thanks & Regards,
> >> Sen
>
> >> On 4/14/2010 1:52 PM, Andreas Henningsson wrote:
> >> > I used this one
>
> >> >http://www.zentus.com/sqlitejdbc/
>
> >> > Works perfect.
>
> >> > /Andreas
>
> >> > On Wed, Apr 14, 2010 at 7:17 AM, Navaneeth Sen B<
> >> navanee...@tataelxsi.co.in
>
> >> >> wrote:
>
> >> >> Hi all,
>
> >> >> I would like to know if a java wrapper is available for SQlite3 APIs.
>
> >> >> Regards,
> >> >> Sen
> >> >> *
> >> >> *
>
> >> >> ___
> >> >> sqlite-users mailing list
> >> >> sqlite-us...@sqlite.org
> >> >>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-us...@sqlite.org
> >>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> > --
> > Andreas Henningsson
>
> > "Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire
>
> --
> Andreas Henningsson
>
> "Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users-
>  Hide quoted text -
>
> - Show quoted text -
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Wrapper

[Toby]

Navaneeth - Java wrappers for SQLite3 are available from Christian
Werner's site, including a JDBC wrapper.

http://www.ch-werner.de/javasqlite/

This is the wrapper that is being shipped with Berkeley DB 11GR2.

Toby

On Apr 13, 10:17 pm, Navaneeth Sen B 
wrote:
> Hi all,
>
> I would like to know if a java wrapper is available for SQlite3 APIs.
>
> Regards,
> Sen
> *
> *
>
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://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] 1000 insert statements into empty table taking 20 seconds... very odd!

[P Kishor]

On Wed, Apr 28, 2010 at 4:08 PM, Ian Hardingham  wrote:
> Hey guys - this is my first post here, apologies if I violate any etiquette.
>
> I have a table I create with:
>
> CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY
> AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL,
> record TEXT);
>
> I run a loop from a scripting language which hooks into SQLite, which
> basically calls this INSERT statement 1000 times:
>
> INSERT INTO globalRankingTable (name, ranking, score, record) VALUES
> ('?','?',?,'?')"
>
> This takes a good 23 seconds (and my machine isn't exactly slow).  I am
> doing a lot of SQLite stuff in my application and everything else seems
> to be running fine.

use transactions... start with BEGIN, then INSERT your data, then COMMIT.


>
> Here's some further information:
>
> 1.  I have narrowed it down that the time is being taken in the call to
> sqlite3_exec
> 2.  The length of the strings is not particularly high - like 10 chars
> generally.
>
> I'm pretty noob at SQLite and am completely stumped - any advice at all
> would be much appreciated
>
>
> Ian
> ___
> 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
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 1000 insert statements into empty table taking 20 seconds... very odd!

[Ian Hardingham]

Hey guys - this is my first post here, apologies if I violate any etiquette.

I have a table I create with:

CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY 
AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, 
record TEXT);

I run a loop from a scripting language which hooks into SQLite, which 
basically calls this INSERT statement 1000 times:

INSERT INTO globalRankingTable (name, ranking, score, record) VALUES 
('?','?',?,'?')"

This takes a good 23 seconds (and my machine isn't exactly slow).  I am 
doing a lot of SQLite stuff in my application and everything else seems 
to be running fine.

Here's some further information:

1.  I have narrowed it down that the time is being taken in the call to 
sqlite3_exec
2.  The length of the strings is not particularly high - like 10 chars 
generally.

I'm pretty noob at SQLite and am completely stumped - any advice at all 
would be much appreciated


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

Re: [sqlite] Searching with like for a specific start letter

[Tim Romano]

A practical situation? Lexicographical applications and full-text
applications against text corpora require indexed substring searches,
including ends-with searches. (The FTS extension is not always a good fit.)
 I am glad that only the LIKE operator has been overridden in Adobe's
version and in the version that ships with the System.Data.SQLite (.NET)
adapter;  I'd be up the creek if both LIKE and GLOB had been overridden. I
like your renaming suggestion but unfortunately that's not an option if the
implementors want to make their implementation widely available and support
standard syntax. Hence, Adobe and Google et al don't have a LIKEU().

Tim Romano


On Wed, Apr 28, 2010 at 10:09 AM, Jean-Christophe Deschamps
wrote:

> Tim,
>
>
> I agree it is possible to overload LIKE and GLOB independantly but I
> don't see a practical situation where overloading only one of them
> would be desirable.
>
> For instance, if some extension overloads LIKE to support ICU, it would
> be logical and consistent to overload GLOB with the same
> function.  Given that the two entries differ only by a parameter,
> enjoying Unicode support in LIKE and not in GLOB (or vice-versa) would
> be a bit strange.
>
> Should one have a need to keep the native functions untouched, there is
> the easy possibility to call the new versions with new names (e.g.
> LIKEU, GLOBU) even if that makes the SQL less standard.
>
> In short: possible yes, likely not much.
>
> ___
> 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] releasing EXCLUSIVE lock after writing dirty pages from the memory cache into the DB ?

[Jay A. Kreibich]

On Wed, Apr 28, 2010 at 07:00:39PM +0100, Simon Slavin scratched on the wall:
> 
> On 28 Apr 2010, at 6:53pm, Jay A. Kreibich wrote:
> 
> >  Once (some) pages are written to disk, you have uncommitted changes
> >  in the database file.  Allowing readers could "leak" those
> >  uncommitted changes to other database connections, which is not
> >  allowed under the ACID transaction model.
> > 
> >  Further, because only some pages get written out, the database file
> >  might very well be inconsistent and unusable.  You need the full
> >  picture of file pages plus cached pages to have a consistent database
> >  image.  At that point in time, the file alone cannot provide that.
> > 
> >  Everything is still safe, thanks to the journal file, but no readers
> >  can touch the file until the transaction is either fully committed or
> >  fully rolled back.
> 
> Could you add a mode that meant every time /some/ pages were written
> out, /all/ pages were written out, and the lock could be relaxed back
> down to RESERVED mode ?

  No.
  
  That might keep the file structure consistent, but the data can
  (keys, constraints, etc.) still be inconsistent.   Plus, it would 
  still leak uncommitted changes to other connections, which is a
  Very Bad Idea.  Leaking changes violates the "I" (Isolated) in ACID.

  http://en.wikipedia.org/wiki/ACID

   -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] releasing EXCLUSIVE lock after writing dirty pages from the memory cache into the DB ?

[Simon Slavin]

On 28 Apr 2010, at 6:53pm, Jay A. Kreibich wrote:

>  Once (some) pages are written to disk, you have uncommitted changes
>  in the database file.  Allowing readers could "leak" those
>  uncommitted changes to other database connections, which is not
>  allowed under the ACID transaction model.
> 
>  Further, because only some pages get written out, the database file
>  might very well be inconsistent and unusable.  You need the full
>  picture of file pages plus cached pages to have a consistent database
>  image.  At that point in time, the file alone cannot provide that.
> 
>  Everything is still safe, thanks to the journal file, but no readers
>  can touch the file until the transaction is either fully committed or
>  fully rolled back.

Could you add a mode that meant every time /some/ pages were written out, /all/ 
pages were written out, and the lock could be relaxed back down to RESERVED 
mode ?  Or would that create problems where a lot of transactions had to be 
committed all at once to keep the database consistent ?

Of course, this would slow down situations where one process was going to keep 
refilling the memory cache, but it would reduces times when one process hogs 
the EXCLUSIVE lock.

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

Re: [sqlite] releasing EXCLUSIVE lock after writing dirty pages from the memory cache into the DB ?

[Jay A. Kreibich]

On Wed, Apr 28, 2010 at 05:20:26PM +0200, Guillaume Duranceau scratched on the 
wall:
> Hello all,
> 
> While running a SQLite transaction writing into the DB (thus holding the 
> RESERVED lock), in case the memory cache becomes full, SQLite will try to 
> write the content of a dirty page into the DB. To do so, it promotes the 
> RESERVED lock to EXCLUSIVE. 

> I'm wondering why the EXCLUSIVE lock is not downgraded to a RESERVED lock 
> right after writing the dirty page into the DB. This doesn't seem to me as 
> an undoable task (the transition EXCLUSIVE->RESERVED would simply need to 
> be managed by xUnlock functions), but there might be technical/conceptual 
> reasons preventing to do so. What are your views on this ?

  Once (some) pages are written to disk, you have uncommitted changes
  in the database file.  Allowing readers could "leak" those
  uncommitted changes to other database connections, which is not
  allowed under the ACID transaction model.

  Further, because only some pages get written out, the database file
  might very well be inconsistent and unusable.  You need the full
  picture of file pages plus cached pages to have a consistent database
  image.  At that point in time, the file alone cannot provide that.

  Everything is still safe, thanks to the journal file, but no readers
  can touch the file until the transaction is either fully committed or
  fully rolled back.

   -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] releasing EXCLUSIVE lock after writing dirty pages from the memory cache into the DB ?

[Dan Kennedy]

On Apr 28, 2010, at 10:20 PM, Guillaume Duranceau wrote:

> Hello all,
>
> While running a SQLite transaction writing into the DB (thus holding  
> the
> RESERVED lock), in case the memory cache becomes full, SQLite will  
> try to
> write the content of a dirty page into the DB. To do so, it promotes  
> the
> RESERVED lock to EXCLUSIVE. This can be annoying because afterwards,  
> the
> EXCLUSIVE lock will be released only when the transaction will  
> finally be
> committed. In the meantime, database access to readers will be  
> prohibited.
>
> This behaviour is described at http://www.sqlite.org/lockingv3.html,
> chapter 5.0 "Writing to a database file":
>
> 
> If the reason for writing to the database file is because the memory  
> cache
> was full, then the writer will not commit right away. Instead, the  
> writer
> might continue to make changes to other pages. Before subsequent  
> changes
> are written to the database file, the rollback journal must be  
> flushed to
> disk again. Note also that the EXCLUSIVE lock that the writer  
> obtained in
> order to write to the database initially must be held until all  
> changes
> are committed. That means that no other processes are able to access  
> the
> database from the time the memory cache first spills to disk until the
> transaction commits.
> 
>
> I'm wondering why the EXCLUSIVE lock is not downgraded to a RESERVED  
> lock
> right after writing the dirty page into the DB. This doesn't seem to  
> me as
> an undoable task (the transition EXCLUSIVE->RESERVED would simply  
> need to
> be managed by xUnlock functions), but there might be technical/ 
> conceptual
> reasons preventing to do so. What are your views on this ?

If a reader tried to read the db file after a writer has
started writing out dirty pages but before it has committed
its entire transaction, it would see an inconsistent (and
possibly corrupt) database file.

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

Re: [sqlite] releasing EXCLUSIVE lock after writing dirty pages from the memory cache into the DB ?

[Richard Hipp]

On Wed, Apr 28, 2010 at 11:20 AM, Guillaume Duranceau <
guillaume.duranc...@amadeus.com> wrote:

> Hello all,
>
> I'm wondering why the EXCLUSIVE lock is not downgraded to a RESERVED lock
> right after writing the dirty page into the DB. This doesn't seem to me as
> an undoable task (the transition EXCLUSIVE->RESERVED would simply need to
> be managed by xUnlock functions), but there might be technical/conceptual
> reasons preventing to do so. What are your views on this ?
>


Doing so (downgrading the EXCLUSIVE lock) would allow other processes to see
a partially committed transaction that could easily contain and inconsistent
file structure, and would at the very least contain uncommitted data.


>
> Regards  -  Guillaume
> ___
> 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] releasing EXCLUSIVE lock after writing dirty pages from the memory cache into the DB ?

[Guillaume Duranceau]

Hello all,

While running a SQLite transaction writing into the DB (thus holding the 
RESERVED lock), in case the memory cache becomes full, SQLite will try to 
write the content of a dirty page into the DB. To do so, it promotes the 
RESERVED lock to EXCLUSIVE. This can be annoying because afterwards, the 
EXCLUSIVE lock will be released only when the transaction will finally be 
committed. In the meantime, database access to readers will be prohibited.

This behaviour is described at http://www.sqlite.org/lockingv3.html, 
chapter 5.0 "Writing to a database file":


If the reason for writing to the database file is because the memory cache 
was full, then the writer will not commit right away. Instead, the writer 
might continue to make changes to other pages. Before subsequent changes 
are written to the database file, the rollback journal must be flushed to 
disk again. Note also that the EXCLUSIVE lock that the writer obtained in 
order to write to the database initially must be held until all changes 
are committed. That means that no other processes are able to access the 
database from the time the memory cache first spills to disk until the 
transaction commits. 


I'm wondering why the EXCLUSIVE lock is not downgraded to a RESERVED lock 
right after writing the dirty page into the DB. This doesn't seem to me as 
an undoable task (the transition EXCLUSIVE->RESERVED would simply need to 
be managed by xUnlock functions), but there might be technical/conceptual 
reasons preventing to do so. What are your views on this ?

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

Re: [sqlite] Searching with like for a specific start letter

[Jean-Christophe Deschamps]

Tim,

>But did I say that  GLOB uses an index if it has been overloaded?  No.  I
>wrote that if LIKE has been overloaded, queries that contain LIKE 
>won't use
>the index.  Typically, GLOB won't have been overridden too just 
>because LIKE
>has been overridden: the rationale for overriding the LIKE operator 
>does not
>apply equally to GLOB, and it would make little sense to override GLOB 
>in a
>manner that vitiates its raison d'être. You are conflating these two
>functions ("... if LIKE/GLOB has been overridden... overloads LIKE/GLOB")
>but in important respects they are dissimilar.

I agree it is possible to overload LIKE and GLOB independantly but I 
don't see a practical situation where overloading only one of them 
would be desirable.

For instance, if some extension overloads LIKE to support ICU, it would 
be logical and consistent to overload GLOB with the same 
function.  Given that the two entries differ only by a parameter, 
enjoying Unicode support in LIKE and not in GLOB (or vice-versa) would 
be a bit strange.

Should one have a need to keep the native functions untouched, there is 
the easy possibility to call the new versions with new names (e.g. 
LIKEU, GLOBU) even if that makes the SQL less standard.

In short: possible yes, likely not much.

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

Re: [sqlite] Searching with like for a specific start letter

[Tim Romano]

Jean-Christophe,
But did I say that  GLOB uses an index if it has been overloaded?  No.  I
wrote that if LIKE has been overloaded, queries that contain LIKE won't use
the index.  Typically, GLOB won't have been overridden too just because LIKE
has been overridden: the rationale for overriding the LIKE operator does not
apply equally to GLOB, and it would make little sense to override GLOB in a
manner that vitiates its raison d'être. You are conflating these two
functions ("... if LIKE/GLOB has been overridden... overloads LIKE/GLOB")
but in important respects they are dissimilar.

Regards
Tim Romano

On Mon, Apr 26, 2010 at 8:27 PM, Jean-Christophe Deschamps 
wrote:

> Tim,
>
> >Queries using GLOB do use the index on the column in question (i.e.
> >optimization is attempted)
> >Queries using LIKE do not use that index if the LIKE operator has been
> >overridden.
>
> Sorry but GLOB doesn't use an index either if LIKE/GLOB has been
> overloaded.  This is consistent with the docs and the output of Explain
> query plan for both variants when an extension is active and overloads
> LIKE/GLOB.
>
> Things can be different with a custom built of SQLite, where native
> LIKE/GLOB itself has been modified.  With custom code, all bets are off.
>
> ___
> 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] near "?": syntax error

[Manoj M]

Thanks for all help.
Yes, its an application issue. We fixed it.

Regards,
Manoj Marathayil



On Wed, Apr 28, 2010 at 5:16 PM, Richard Hipp  wrote:
> On Wed, Apr 28, 2010 at 7:30 AM, Alexey Pechnikov 
> wrote:
>
>> 2010/4/28 Manoj M :
>> > I am getting error message "near "?": syntax error" randomly while
>> > executing the query "SELECT [record] FROM [ac_contacts_cache] LIMIT 0,
>> > 3".
>>
>> The SQL "LIMIT 0, 3" is incorrect. Use "LIMIT 3 OFFSET 0" instead.
>>
>
> SQLite accepts both variations on the LIMIT syntax.  They do exactly the
> same thing.
>
> Manoj has an application problem of some kind.  He is sending something to
> sqlite3_prepare() that is different from what he things he is sending.  Or,
> perhaps he has multiple threads running with SQLite mutexes disabled.  Or
> prehaps he is send a string into sqlite3_prepare() and then freeing and/or
> overwriting that string before sqlite3_prepare() returns.  In any event, it
> is not SQLite that is causing Manoj's problem, and without additional
> information, we can't really determine the source of the problem.
>
>
>
>>
>> --
>> 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
>>
>
>
>
> --
> -
> 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

Re: [sqlite] near "?": syntax error

[Richard Hipp]

On Wed, Apr 28, 2010 at 7:30 AM, Alexey Pechnikov wrote:

> 2010/4/28 Manoj M :
> > I am getting error message "near "?": syntax error" randomly while
> > executing the query "SELECT [record] FROM [ac_contacts_cache] LIMIT 0,
> > 3".
>
> The SQL "LIMIT 0, 3" is incorrect. Use "LIMIT 3 OFFSET 0" instead.
>

SQLite accepts both variations on the LIMIT syntax.  They do exactly the
same thing.

Manoj has an application problem of some kind.  He is sending something to
sqlite3_prepare() that is different from what he things he is sending.  Or,
perhaps he has multiple threads running with SQLite mutexes disabled.  Or
prehaps he is send a string into sqlite3_prepare() and then freeing and/or
overwriting that string before sqlite3_prepare() returns.  In any event, it
is not SQLite that is causing Manoj's problem, and without additional
information, we can't really determine the source of the problem.



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



-- 
-
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] Hypothetical memory consumption question

[Radcon Entec]

I tried this, using plain ordinary Windows applications instead of services.  
One ran through ten insertions, and the other performed one insertion per 
second overnight.  This morning, the 10-insertion application was taking 4.9 
megabytes of memory, while the all-night version took 5.6 megabytes.  If I can 
get my service down to that rate of growth, I'll be happy.

RobR






From: Radcon Entec 
To: General Discussion of SQLite Database 
Sent: Tue, April 27, 2010 2:25:56 PM
Subject: [sqlite] Hypothetical memory consumption question

Greetings!

I have a hypothetical question.  Assume I have the simplest possible SQLite 
database on a disk file: a single table with a single column. Now assume that I 
have a Windows service  that opens the database when it starts, and leaves it 
open forever.  The service has a loop that is executed once a second.  The loop 
increments a counter that starts at 0.  If the counter is less than 10, then a 
query of the form "INSERT INTO table_name (column_name) VALUES (counter)" is 
built, and sqlite_exec() is called to execute it.  After 10 seconds, the 
service continues to run, but the insertion is never perfored again.  Now 
assume I have a second Windows service identical to the first, except that the 
maximum value of the counter is 10,000.  Now assume I start both services at 
the same time.  Then, three hours later (>10,000 seconds), I use TaskManager to 
see how much memory the two services are using.  Will they be consuming the 
same amount of memory?

RobR



  
___
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] near "?": syntax error

[Igor Tandetnik]

Manoj M wrote:
> I am getting error message "near "?": syntax error" randomly while
> executing the query "SELECT [record] FROM [ac_contacts_cache] LIMIT 0,
> 3".

I don't see how this error may arise from this query, seeing as it doesn't 
contain '?' anywhere.

How exactly do you "execute" the query? You must be passing a string to 
sqlite3_prepare* different from one you think you are passing.
-- 
Igor Tandetnik

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

Re: [sqlite] Exclusive transactions over network

[Max Vlasov]

Jean-Christophe,
from my experience it depends. We have several clients accessing a database
shared on a 2003 server and no corruption took place so far, but sometimes
freezing of a client was possible. Also when I did some artificial tests,
when several clients tried to write on a constant basis there were cases
when one of them could also freeze. Consider doing some die hard tests with
your configuration. This should not be the same scheme as yours, the only
thing you should additionally do from time to time is PRAGMA
integrity_check. After a whole night test and thousands of successful writes
from several computers you will at least have probability arguments on your
side )

Max Vlasov,
maxerist.net

On Wed, Apr 28, 2010 at 9:43 AM, Jean-Christophe Deschamps 
wrote:

> Hi gurus,
>
> I'm aware of the limitations that generally preclude using SQLite over
> a network.
> Anyway do you think that doing so with every read or write operation
> wrapped inside an explicit exclusive transaction can be a safe way to
> run a DB for a group of 10 people under low load (typically 2Kb read or
> 100b writes per user per minute)?
> Schema will be very simple and queries / inserts as well.  Speed is not
> a real concern.
>
> So do you believe DB corruption can still occur in this context,
> knowing that the use will be for a very limited time (2-3 weeks) and
> low volume (~50K rows)?
>
> Using one of the available client/server wrappers is not a suitable option.
> This is targeted at Windows, XP or later.
>
> Do you have a better idea to make the thing more robust, even at
> additional cost in concurrency and/or speed.
>
> ___
> 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] CHECK constraints and type affinity

[Igor Tandetnik]

Black, Michael (IS) wrote:
> Hmmm...when I get rid of the "+'"
> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
> the constraint works
> 
> Seems to me that "+N" is the same as "abs(N)".  I'm not even sure of what the 
> intent of "+N" would be???

A unary plus in SQLite is a no-op, but it suppresses type coercion, and also 
may inhibit the use of an index (sometimes SQLite optimizer picks a suboptimal 
index for the query, and suppressing that index allows the optimizer to pick a 
different one).

ColumnName is an expression with the affinity associated with the column; 
+ColumnName is an expression with the same value but no affinity. The 
difference is important in some cases.
-- 
Igor Tandetnik

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

Re: [sqlite] CHECK constraints and type affinity

[Black, Michael (IS)]

Hmmm...when I get rid of the "+'"
CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
the constraint works
 
Seems to me that "+N" is the same as "abs(N)".  I'm not even sure of what the 
intent of "+N" would be???
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik
Sent: Wed 4/28/2010 7:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] CHECK constraints and type affinity



Dan Bishop wrote:
> If I write
>
> sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
>
> the constraint is applied AFTER converting N to an integer.
>
> sqlite> INSERT INTO T1 VALUES('42');
> sqlite> INSERT INTO T1 VALUES('-5');
> SQL error: constraint failed

A curious thing seems to happen. Inside CHECK constraint, a unique situation is 
created that probably doesn't exist anywhere else (well, maybe within a 
trigger; I'm too lazy to try and repro) - expression N has a value of type TEXT 
(convertible to integer) but an INTEGER affinity. The comparison then appears 
to coerce both operans to numbers first.

This example allows a negative value to slip past the check:

CREATE TABLE T1 (N INTEGER CHECK(+N >= 0));
INSERT INTO T1 VALUES('-5');
select N, typeof(N) from T1
-5 | integer

The unary plus suppresses type coercion, so '-5' is compared with 0, and any 
string is considered greater than any number so the check succeeds. Then the 
value is coerced to integer before storage. This proves that CHECK expression 
is evaluated before converting the value for storage in all cases; it just so 
happens that, in your example, the same conversion is performed when evaluating 
the expression itself.

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] near "?": syntax error

[Igor Tandetnik]

Alexey Pechnikov wrote:
> 2010/4/28 Manoj M :
>> I am getting error message "near "?": syntax error" randomly while
>> executing the query "SELECT [record] FROM [ac_contacts_cache] LIMIT 0,
>> 3".
> 
> The SQL "LIMIT 0, 3" is incorrect. Use "LIMIT 3 OFFSET 0" instead.

Both are valid in SQLite
-- 
Igor Tandetnik

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

Re: [sqlite] CHECK constraints and type affinity

[Igor Tandetnik]

Dan Bishop wrote:
> If I write
> 
> sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
> 
> the constraint is applied AFTER converting N to an integer.
> 
> sqlite> INSERT INTO T1 VALUES('42');
> sqlite> INSERT INTO T1 VALUES('-5');
> SQL error: constraint failed

A curious thing seems to happen. Inside CHECK constraint, a unique situation is 
created that probably doesn't exist anywhere else (well, maybe within a 
trigger; I'm too lazy to try and repro) - expression N has a value of type TEXT 
(convertible to integer) but an INTEGER affinity. The comparison then appears 
to coerce both operans to numbers first.

This example allows a negative value to slip past the check:

CREATE TABLE T1 (N INTEGER CHECK(+N >= 0));
INSERT INTO T1 VALUES('-5');
select N, typeof(N) from T1
-5 | integer

The unary plus suppresses type coercion, so '-5' is compared with 0, and any 
string is considered greater than any number so the check succeeds. Then the 
value is coerced to integer before storage. This proves that CHECK expression 
is evaluated before converting the value for storage in all cases; it just so 
happens that, in your example, the same conversion is performed when evaluating 
the expression itself.

Igor Tandetnik

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

Re: [sqlite] CHECK constraints and type affinity

[Igor Tandetnik]

Dan Bishop wrote:
> sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
> 
> the constraint is applied AFTER converting N to an integer.
> 
> sqlite> INSERT INTO T1 VALUES('42');
> sqlite> INSERT INTO T1 VALUES('-5');
> SQL error: constraint failed

How do you know? Both expressions below are true:

-5 < 0
'-5' < '0'

-- 
Igor Tandetnik

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

Re: [sqlite] near "?": syntax error

[Alexey Pechnikov]

2010/4/28 Manoj M :
> I am getting error message "near "?": syntax error" randomly while
> executing the query "SELECT [record] FROM [ac_contacts_cache] LIMIT 0,
> 3".

The SQL "LIMIT 0, 3" is incorrect. Use "LIMIT 3 OFFSET 0" instead.

-- 
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] for LINQ-to-sql from C# which interface/drivers are the most solid to use?

[Greg Hauptmann]

Hi,

Re using LINQ-to-sql from C# to Sqlite, which interface/drivers are the most
solid to use?  I'm come up with the following list so far however would
appreciate some feedback re which are the most solid.


http://www.devart.com/linqconnect/

http://code.google.com/p/dblinq2007/

http://codefornothing.wordpress.com/2007/07/19/sqlite-data-provider-for-subsonic-part-2/


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

[sqlite] Problems building on Tru64

[Daniel Richard G.]

Reporting issues against 3.6.23.1 after encountering one building
Subversion 1.6.11 on Tru64 V4.0G:

* -D_POSIX_C_SOURCE=199506L was needed in order to enable the necessary
  pthread functionality on this system. The Unix configure script should
  at least check that this is available.

* RTLD_GLOBAL does not exist on this system. (See attached patch for a
  quick fix, against the amalgamated source.)

* I still see errors when linking the shared library:

/usr/local/bin/bash ./libtool --tag=CC --mode=link cc -DSQLITE_THREADSAFE=1  
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -std1 -warnprotos -readonly_strings 
-fast -O4-o libsqlite3.la -rpath /opt/sqlite/lib -no-undefined 
-version-info 8:6:8 sqlite3.lo  
/usr/bin/ld -shared  .libs/sqlite3.o  -lc  -msym -soname libsqlite3.so.0 `test 
-n "8.8.6:0.0:1.0:2.0:3.0:4.0:5.0:6.0:7.0:8.0" && echo -set_version 
8.8.6:0.0:1.0:2.0:3.0:4.0:5.0:6.0:7.0:8.0` -update_registry .libs/so_locations 
-o .libs/libsqlite3.so.8.8.6
/usr/bin/ld:
Warning: Unresolved:
__pthread_self
pthread_mutexattr_init
pthread_mutexattr_destroy
__pthread_mutex_init
__pthread_mutex_destroy
__pthread_mutex_lock
__pthread_mutex_trylock
__pthread_mutex_unlock
pthread_mutexattr_settype
(cd .libs && rm -f libsqlite3.so.0 && ln -s libsqlite3.so.8.8.6 libsqlite3.so.0)
(cd .libs && rm -f libsqlite3.so && ln -s libsqlite3.so.8.8.6 libsqlite3.so)
ar cru .libs/libsqlite3.a  sqlite3.o
ranlib .libs/libsqlite3.a
creating libsqlite3.la
(cd .libs && rm -f libsqlite3.la && ln -s ../libsqlite3.la libsqlite3.la)

  These errors were non-fatal in the build, but I suspect the shared
  library isn't going to be of much use like this. There needs to be
  some -lpthread option or the like passed to the linker (another thing
  that the configure script needs to determine, as the proper
  incantation varies per system).


--Daniel

(Please Cc: any replies to me, as I am not subscribed to this list.)


-- 
NAME = Daniel Richard G. _\|/_Remember, skunks
MAIL = sk...@iskunk.org (/o|o\) _- don't smell bad---
MAIL+= sk...@alum.mit.edu   < (^),> it's the people who
WWW  = (not there yet!)  /   \  annoy us that do!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] near "?": syntax error

[Manoj M]

I am getting error message "near "?": syntax error" randomly while
executing the query "SELECT [record] FROM [ac_contacts_cache] LIMIT 0,
3".

Table schema:

CREATE TABLE IF NOT EXISTS
[ac_contacts_cache] (
[record] TEXT NOT NULL
)

Any help here is appreciated.

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

Re: [sqlite] SQLITE .import

[Jens Miltner]

Am 19.04.2010 um 20:11 schrieb P Kishor:

>> 3) From a linux box whats the easiest way of backing up the db  in perl or 
>> shell?
>> 
>> Can I lock the $sqlitedb first (how?) and then cp $sqlitedb 
>> /usr/local/db/backup
>> 
> 
> There is no concept of locking a db in sqlite. A sqlite db is just a
> file, just like any other file in your computer... a file that you can
> see, touch, move, rename. Just rename the db to db.backup, and create
> a new db. If you want to do all these shenanigans while you have other
> users connected to your db then --
> 
> you should either use sqlite's new backup facility (which I have never
> bothered learning, yet)
> 
> or, use a real client/server db such as Pg which will allow you to
> shut down/lock the db.


Well, besides using the new backup APIs, you could also start an immediate 
transaction with "BEGIN IMMEDIATE", which has the effect of locking the 
database for write access (since SQLite uses file-based locking). It should 
then be safe to copy the database file. When done, rollback the transaction.

Please correct me if this is not safe...



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

[sqlite] CHECK constraints and type affinity

[Dan Bishop]

If I write

sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));

the constraint is applied AFTER converting N to an integer.

sqlite> INSERT INTO T1 VALUES('42');
sqlite> INSERT INTO T1 VALUES('-5');
SQL error: constraint failed

But if I write

sqlite> CREATE TABLE T2 (N INTEGER CHECK(TYPEOF(N) = 'integer'));

the constraint is applied BEFORE converting N to an integer.

sqlite> INSERT INTO T2 VALUES(17);
sqlite> INSERT INTO T2 VALUES('18');
SQL error: constraint failed

Why the inconsistency?

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