Re: [sqlite] Create Read-only Database

[Darren Duncan]

Cory Nelson wrote:
> On Sun, Oct 10, 2010 at 8:51 PM, Darren Duncan  
> wrote:
>> I think that it should be possible to configure SQLite to be strictly 
>> read-only
>> in every respect, such that if with such configuration SQLite is told to 
>> open a
>> database that would need updating from a journal or WAL, it would fail with 
>> some
>> appropriate error rather than fixing the database.  This in contrast to the
>> approach of apply the journal or WAL and then don't change anything further; 
>> the
>> latter is also important to support but users should have a choice between 
>> the
>> two options. -- Darren Duncan
> 
> +1

Oh!  Oh!  I just thought of a third option ...

SQLite can be configured to be strictly read-only in every respect but that if 
the database would need updating from a journal or WAL, SQLite would go ahead 
and do this but only in mapped memory whether plain RAM or a shadow file in a 
temp directory.  This option means absolutely no changes to the actual SQLite 
database files but users would then still be able to read from the database.

There should be a number of applications for that scenario, and as far as I 
know 
SQLite already practices some memory mapping so much of the necessary code may 
already exist.

Or a stripped down version of this is that SQLite can apply the journal/WAL to 
a 
copy of the database file it first makes in a temp directory, and then use that 
readonly as usual.

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

Re: [sqlite] Adding a string in front of what is already in a field

[jose isaias cabrera]

"P Kishor" wrote...

> On Sun, Oct 10, 2010 at 11:05 PM, jose isaias cabrera
>  wrote:
>>
>> Greetings.
>>
>> I would like some help with this scenario... DB name OpenJobs.
>>
>> id,pid,spid,notes
>> 100, 24,32,'this is a test'
>> 101, 24,32,'a different note'
>> 102, 24,32,'yet, another different note'
>> ...
>>
>> What I dould like to do is to update each of those notes by adding a 
>> string
>> to the front of data contained in the notes. The final DB data would look
>> like this:
>>
>> id,pid,spid,notes
>> 100, 24,32,'string to add in front\r\nthis is a test'
>> 101, 24,32,'string to add in front\r\na different note'
>> 102, 24,32,'string to add in front\r\nyet, another different note'
>> ...
>>
>> I have come up with this:
>>
>> UPDATE OpenJobs SET notes = 'string to add in front\r\n' ||
>> (SELECT notes from OpenJobs where spid = 32) WHERE spid = 32;
>>
>
> UPDATE OpenJobs
> SET notes = 'string to add in front\r\n' || notes
> WHERE spid = 32;

That easy, huh?  I knew it.

thanks,

josé

>> But I don't think it's going to work.
>>
>> Any help would be greatly appreciated.
>>
>> thanks.
>>
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding a string in front of what is already in a field

[P Kishor]

On Sun, Oct 10, 2010 at 11:05 PM, jose isaias cabrera
 wrote:
>
> Greetings.
>
> I would like some help with this scenario... DB name OpenJobs.
>
> id,pid,spid,notes
> 100, 24,32,'this is a test'
> 101, 24,32,'a different note'
> 102, 24,32,'yet, another different note'
> ...
>
> What I dould like to do is to update each of those notes by adding a string
> to the front of data contained in the notes.  The final DB data would look
> like this:
>
> id,pid,spid,notes
> 100, 24,32,'string to add in front\r\nthis is a test'
> 101, 24,32,'string to add in front\r\na different note'
> 102, 24,32,'string to add in front\r\nyet, another different note'
> ...
>
> I have come up with this:
>
> UPDATE OpenJobs SET notes = 'string to add in front\r\n' ||
>      (SELECT notes from OpenJobs where spid = 32) WHERE spid = 32;
>

UPDATE OpenJobs
SET notes = 'string to add in front\r\n' || notes
WHERE spid = 32;

> But I don't think it's going to work.
>
> Any help would be greatly appreciated.
>
> thanks.
>
> ___
> 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] Adding a string in front of what is already in a field

[jose isaias cabrera]

Greetings.

I would like some help with this scenario... DB name OpenJobs.

id,pid,spid,notes
100, 24,32,'this is a test'
101, 24,32,'a different note'
102, 24,32,'yet, another different note'
...

What I dould like to do is to update each of those notes by adding a string 
to the front of data contained in the notes.  The final DB data would look 
like this:

id,pid,spid,notes
100, 24,32,'string to add in front\r\nthis is a test'
101, 24,32,'string to add in front\r\na different note'
102, 24,32,'string to add in front\r\nyet, another different note'
...

I have come up with this:

UPDATE OpenJobs SET notes = 'string to add in front\r\n' ||
  (SELECT notes from OpenJobs where spid = 32) WHERE spid = 32;

But I don't think it's going to work.

Any help would be greatly appreciated.

thanks. 

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

Re: [sqlite] Create Read-only Database

[Cory Nelson]

On Sun, Oct 10, 2010 at 8:51 PM, Darren Duncan  wrote:
> I think that it should be possible to configure SQLite to be strictly 
> read-only
> in every respect, such that if with such configuration SQLite is told to open 
> a
> database that would need updating from a journal or WAL, it would fail with 
> some
> appropriate error rather than fixing the database.  This in contrast to the
> approach of apply the journal or WAL and then don't change anything further; 
> the
> latter is also important to support but users should have a choice between the
> two options. -- Darren Duncan

+1

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

Re: [sqlite] Create Read-only Database

[Darren Duncan]

Roger Binns wrote:
> Going back to Joshua's original question, by default a SQLite database is
> not read-only even if you think it is.  The major reason is that even if you
> wanted to use it read-only, the previous program may have had it open for
> writing, and may have crashed in the middle of a transaction.  Consequently
> the reader needs to fix the database using the journal to get it back into a
> correct state which involves writing.  Heck even while you have it open and
> idle, a writer could have started a transaction and crashed requiring 
> recovery.

I think that it should be possible to configure SQLite to be strictly read-only 
in every respect, such that if with such configuration SQLite is told to open a 
database that would need updating from a journal or WAL, it would fail with 
some 
appropriate error rather than fixing the database.  This in contrast to the 
approach of apply the journal or WAL and then don't change anything further; 
the 
latter is also important to support but users should have a choice between the 
two options. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] create_function_v2 destructor bug or doc issue

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

The destructor is documented to be called "when the function is deleted,
either by being overloaded or when the database connection closes".  However
it is also called if the create function call fails which took some valgrind
runs and reading the source to work out.  I guess it is too late to change
the code to avoid the destructor on error conditions so this behaviour
should be documented.

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

iEYEARECAAYFAkyyhpYACgkQmOOfHg372QTZEQCfY8f4B+4XTs7cUZviUNlDwk/v
MtAAnjXC04WWTVcWHz5NU3xxsX1mEHpK
=tJwO
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create Read-only Database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/10/2010 02:55 PM, Bob Keeland wrote:
> but there are cases in which the user will, in the vast majority of cases, 
> not have the knowledge to update the database. 

None of what I wrote has *anything* to do with users.  It was entirely
scenarios under which the SQLite library may need to write to the database
(and directory) in order to ensure consistency and durability by recovering
from a separate program that may have been using the library crashing
(earlier or concurrently).

> A program that I am working on is mostly a 'fill the database' job for me. 
> Writing
> the code will be somewhat minimal. For the user it will be a search the
database
> for an answer that is seemingly unrelated to the database. From the user's
perspective
> they just make selections from listed options and the number of options
(selections)
> is reduced. What the user wants ideally is to be left with only one
selection - that
> will be their answer. They really don't care how they get to that point.
If a person
> updates the database they will probably make the overall program unusable.

You seem to be confusing several things here.

SQLite is a library used by *your* code.  It only does what you tell it to.
 If you make no calls to SQLite to make changes then it won't.  Users are
using your program,- they can only do what your program allows.

If you want to ensure that your program cannot make changes (eg you let
users enter arbitrary SQL) then there is an API that is called on preparing
each chunk of SQL where you can allow or deny operations:

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

If you want to ensure code you write does not make changes then don't write
any code that does so.  Pretty much the only way to make changes is via SQL
so you can grep your SQL queries and do things that way.  (And install an
authorizer as a fail-safe.)

If you want to prevent the user modifying the database file in any way (eg
going in separately with a text or hex editor) then you will need to use
operating system access control facilities to protect from that user for the
database file and the directory.  You should also specify read only in the
sqlite3_open_v2 call.

If you want fine low level control over SQLite's interaction with the
underlying operating system then the VFS layer provides that.  You can
implement, augment or override almost all behaviour (very little code if you
inherit from an existing VFS).

> This inability to have a read-only database with SQLite is unfortunate.

Can you restate your problem since it isn't clear who you think is going to
write to the database in the first place?

Another example is some people want to ship SQLite databases on CDROM.
Since the database can't be changed they also want to compress it.  You can
buy an extension written by the SQLite authors themselves that let you do
this.  It also lets you do encryption:

  http://www.hwaci.com/sw/sqlite/cerod.html

(Alternatively if your time has no value you could code something similar
yourself if that is what your needs are.)

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

iEYEARECAAYFAkyyae0ACgkQmOOfHg372QRHwACgkLVkD3Y0dWw0vzLRlKk0yeGJ
HQAAni99j0bjVYRbe0DsbXoPLELcESIv
=crLq
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create Read-only Database

[Bob Keeland]

A second response to this issue orf read-only. For my program the user will 
never directly the database. The user will make selections from the program and 
then the program will query the database. The user will then see how many 
records remain in the database and make selections from other portions of the 
program. The program will then query the database again. In other words, the 
database will be read-only if the user does not really have direct access to 
the database through the program. Does this make sense?
 
BobK

--- On Sun, 10/10/10, Max Vlasov  wrote:


From: Max Vlasov 
Subject: Re: [sqlite] Create Read-only Database
To: "General Discussion of SQLite Database" 
Date: Sunday, October 10, 2010, 1:53 PM


On Sun, Oct 10, 2010 at 9:31 AM, Joshua Grauman  wrote:

> I have a database that I want to be only read-only. I read in the
> optimization FAQ that this will make sqlite not create a journal and so
> run faster. I changed the permissions of my database file in Linux
> (removed the write permission), and sqlite was still able to create a new
> table. Since this file should never be written I also want to get an error
> if it is by some fault in a querry I write. So any ideas about how to make
> this entire database read-only? Is there a way to do this in sqlite
> itself? Thanks!
>
>

Did you try sqlite3_open_v2 with SQLITE_OPEN_READONLY flag?

Also if you're not content with this option, you can always open the file
(just as a general file) prior to sqlite with a "deny write" option. I'm
aware of such feature in Windows/Win32, I'm sure a similar option should
exist in Linux. In this case any attempt to write will lead to OS-level
error that finally will be passes as some sqlite error to your code.

Max Vlasov
maxerist.net
___
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] Create Read-only Database

[Bob Keeland]

This inability to have a read-only database with SQLite is unfortunate. In most 
cases I'll agree that a database should be something that can be updated, but 
there are cases in which the user will, in the vast majority of cases, not have 
the knowledge to update the database. 
 
A program that I am working on is mostly a 'fill the database' job for me. 
Writing the code will be somewhat minimal. For the user it will be a search the 
database for an answer that is seemingly unrelated to the database. From the 
user's perspective they just make selections from listed options and the number 
of options (selections) is reduced. What the user wants ideally is to be left 
with only one selection - that will be their answer. They really don't care how 
they get to that point. If a person updates the database they will probably 
make the overall program unusable.
 
Bob Keeland

--- On Sun, 10/10/10, Roger Binns  wrote:


From: Roger Binns 
Subject: Re: [sqlite] Create Read-only Database
To: "General Discussion of SQLite Database" 
Date: Sunday, October 10, 2010, 2:04 PM


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/10/2010 11:53 AM, Max Vlasov wrote:
> Also if you're not content with this option, you can always open the file
> (just as a general file) prior to sqlite with a "deny write" option. I'm
> aware of such feature in Windows/Win32, I'm sure a similar option should
> exist in Linux. In this case any attempt to write will lead to OS-level
> error that finally will be passes as some sqlite error to your code.

Actually Linux/Unix does not have such deny mode options.  Additionally
locking is cooperative not mandatory.

There are two reasons for this.  Historically Unix has always been
multi-user and having the ability to deny other users/programs access to a
file that they have permissions for would cause far more harm than good.

The second is that system data has traditionally been stored in plain text
files - things like /etc/hosts for name to IP address mappings and
/etc/passwd for the user password database.  If any program could deny
access to those for any other program/user then it would be a trivial denial
of service attack.  See the Unix Hater's Guide (free PDF online) for more
details of Unix locking.

Going back to Joshua's original question, by default a SQLite database is
not read-only even if you think it is.  The major reason is that even if you
wanted to use it read-only, the previous program may have had it open for
writing, and may have crashed in the middle of a transaction.  Consequently
the reader needs to fix the database using the journal to get it back into a
correct state which involves writing.  Heck even while you have it open and
idle, a writer could have started a transaction and crashed requiring recovery.

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

iEYEARECAAYFAkyyDkQACgkQmOOfHg372QQEGQCcDEK20d0jgCe1YfGLMxTT7erc
4tAAn0HBEZhM1rFpot6K+ORNTMquyZyo
=iHyP
-END PGP SIGNATURE-
___
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] Create Read-only Database

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 10/10/2010 11:53 AM, Max Vlasov wrote:
> Also if you're not content with this option, you can always open the file
> (just as a general file) prior to sqlite with a "deny write" option. I'm
> aware of such feature in Windows/Win32, I'm sure a similar option should
> exist in Linux. In this case any attempt to write will lead to OS-level
> error that finally will be passes as some sqlite error to your code.

Actually Linux/Unix does not have such deny mode options.  Additionally
locking is cooperative not mandatory.

There are two reasons for this.  Historically Unix has always been
multi-user and having the ability to deny other users/programs access to a
file that they have permissions for would cause far more harm than good.

The second is that system data has traditionally been stored in plain text
files - things like /etc/hosts for name to IP address mappings and
/etc/passwd for the user password database.  If any program could deny
access to those for any other program/user then it would be a trivial denial
of service attack.  See the Unix Hater's Guide (free PDF online) for more
details of Unix locking.

Going back to Joshua's original question, by default a SQLite database is
not read-only even if you think it is.  The major reason is that even if you
wanted to use it read-only, the previous program may have had it open for
writing, and may have crashed in the middle of a transaction.  Consequently
the reader needs to fix the database using the journal to get it back into a
correct state which involves writing.  Heck even while you have it open and
idle, a writer could have started a transaction and crashed requiring recovery.

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

iEYEARECAAYFAkyyDkQACgkQmOOfHg372QQEGQCcDEK20d0jgCe1YfGLMxTT7erc
4tAAn0HBEZhM1rFpot6K+ORNTMquyZyo
=iHyP
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create Read-only Database

[Max Vlasov]

On Sun, Oct 10, 2010 at 9:31 AM, Joshua Grauman  wrote:

> I have a database that I want to be only read-only. I read in the
> optimization FAQ that this will make sqlite not create a journal and so
> run faster. I changed the permissions of my database file in Linux
> (removed the write permission), and sqlite was still able to create a new
> table. Since this file should never be written I also want to get an error
> if it is by some fault in a querry I write. So any ideas about how to make
> this entire database read-only? Is there a way to do this in sqlite
> itself? Thanks!
>
>

Did you try sqlite3_open_v2 with SQLITE_OPEN_READONLY flag?

Also if you're not content with this option, you can always open the file
(just as a general file) prior to sqlite with a "deny write" option. I'm
aware of such feature in Windows/Win32, I'm sure a similar option should
exist in Linux. In this case any attempt to write will lead to OS-level
error that finally will be passes as some sqlite error to your code.

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

[sqlite] Building sqlite 3.7.3 with Tcl binding

[Paweł Salawa]

Hi,

How do I compile sqlite 3.7.3 with Tcl bindings? I don't see any tcl-related 
options in configure script (the amalgamation distribution) and default 
compilation doesn't probide Tcl bindings.

Thanks for help!
Regards,
-- 
Paweł Salawa
pawelsal...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT DISTINCT and multi-column UNIQUE constrains

[Kees Nuyt]

On Sat, 9 Oct 2010 12:20:26 +0200, Joerg Sonnenberger
 wrote:

>Hi all,
>I'm seeing high disk IO and associated processing overhead in the
>following situation, which shouldn't be as expensive as it currently is.
>
>Schema:
>CREATE TABLE symbol (file integer NOT NULL REFERENCES file (id), symbol
>varchar NOT NULL, revision varchar NOT NULL, branch boolean NOT NULL,
>UNIQUE (file, symbol));
>
>Query:
>SELECT DISTINCT symbol FROM symbol;
>
>Query plan:
[snipped]

>What I expect to see is an index scan on the index of the UNIQUE constrain
>and picking the value without ever touch the table. 

I would at most expect that if it had been defined as
UNIQUE (symbol, file)  -- (1)
instead of
UNIQUE (file, symbol)  -- (2)

Semantically both forms represent the same constraint.
 
For the optimizer there could be a difference though: The
first form (1) has the required column first, so it might
not have to descend to the bottom of the index B-Tree. the
optimizer might decide to use the index in this case.

The second form (2) would force a full index scan, which is
not much better than a table scan.

The optimizer might decide differently after running
ANALYZE; on a fully populated database.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT DISTINCT and multi-column UNIQUE constrains

[Drake Wilson]

Quoth Joerg Sonnenberger , on 2010-10-09 12:20:26 +0200:
> Schema:
> CREATE TABLE symbol (file integer NOT NULL REFERENCES file (id), symbol
> varchar NOT NULL, revision varchar NOT NULL, branch boolean NOT NULL,
> UNIQUE (file, symbol));
> 
> Query:
> SELECT DISTINCT symbol FROM symbol;
[...]
> What I expect to see is an index scan on the index of the UNIQUE constrain
> and picking the value without ever touch the table. 

How would this happen?  The index uses the columns in order, as far as
I know, so you'll have for instance ('file A', 'symbol 1'), then later
('file B', 'symbol 1') and so on, and you can't trivially get the
DISTINCT out of that without sorting in temporary storage.

Using UNIQUE (symbol, file) instead would seem the obvious solution.
Is there a reason you can't do that?

> Joerg

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

Re: [sqlite] using SQLite with mod_perl

[laurent dami]

  >-Message d'origine-
  >De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
  >boun...@sqlite.org] De la part de P Kishor
  >Envoyé : samedi, 9. octobre 2010 17:10
  >I am just trying to solve the above. It may well be that sqlite and
  >mod_perl may not be good companions (in which case, I am up a 
  >creek without a db, and will have to look for an alternative storage
  >solution).
  >

Sqlite and mod_perl are definitely good companions, as long as one
doesn't mess up with transactions.
Upon startup, Apache starts a collection of servers, which may be either
processes or threads, depending on the MPM (multi-process module, see
http://httpd.apache.org/docs/2.2/mpm.html ); but this doesn't matter much.

Under mod_perl, each of those servers has an embedded perl interpreter,
so indeed they work concurrently. Each server has a loop, listening for
requests, and then producing the answer ... but the server may hold
data that is persistent between requests, and within such data
it is often a very good idea to keep a persistent connection to
the database, avoiding the cost of opening/closing the connection
at each request. Therefore we may have a collection of concurrent
Apache/mod_perl servers, where each server has an open connection
to the database, and that is not a problem.

The important point, however, is to properly open and close a transaction
whenever updating the database, using the begin_work(), commit() and
rollback() methods (see L). This sequence should
ALWAYS happen within a single http request, i.e. do not
start the transaction in one request, expecting the next request to
close that transaction : this will lock your database, because the next
request might never arrive, or it might be served by a different process
or thread than the one that served the initial request.

Good luck,

Laurent Dami


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

[sqlite] SELECT DISTINCT and multi-column UNIQUE constrains

[Joerg Sonnenberger]

Hi all,
I'm seeing high disk IO and associated processing overhead in the
following situation, which shouldn't be as expensive as it currently is.

Schema:
CREATE TABLE symbol (file integer NOT NULL REFERENCES file (id), symbol
varchar NOT NULL, revision varchar NOT NULL, branch boolean NOT NULL,
UNIQUE (file, symbol));

Query:
SELECT DISTINCT symbol FROM symbol;

Query plan:

0|Trace|0|0|0||00|
1|OpenEphemeral|1|2|0|keyinfo(1,BINARY)|00|
2|Integer|0|3|0||00|
3|Integer|0|2|0||00|
4|Gosub|5|34|0||00|
5|Goto|0|37|0||00|
6|OpenRead|0|5|0|2|00|
7|Rewind|0|13|0||00|
8|Column|0|1|8||00|
9|Sequence|1|9|0||00|
10|MakeRecord|8|2|10||00|
11|IdxInsert|1|10|0||00|
12|Next|0|8|0||01|
13|Close|0|0|0||00|
14|Sort|1|36|0||00|
15|Column|1|0|7||00|
16|Compare|6|7|1|keyinfo(1,BINARY)|00|
17|Jump|18|22|18||00|
18|Move|7|6|1||00|
19|Gosub|4|29|0||00|
20|IfPos|3|36|0||00|
21|Gosub|5|34|0||00|
22|Column|1|0|1||00|
23|Integer|1|2|0||00|
24|Next|1|15|0||00|
25|Gosub|4|29|0||00|
26|Goto|0|36|0||00|
27|Integer|1|3|0||00|
28|Return|4|0|0||00|
29|IfPos|2|31|0||00|
30|Return|4|0|0||00|
31|SCopy|1|11|0||00|
32|ResultRow|11|1|0||00|
33|Return|4|0|0||00|
34|Null|0|1|0||00|
35|Return|5|0|0||00|
36|Halt|0|0|0||00|
37|Transaction|0|0|0||00|
38|VerifyCookie|0|5|0||00|
39|TableLock|0|5|0|symbol|00|
40|Goto|0|6|0||00|

What I expect to see is an index scan on the index of the UNIQUE constrain
and picking the value without ever touch the table. 

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

[sqlite] TEA tarball 3.7.3

[Bob Chapman]

I was looking for the TEA tarball for 3.7.3.  The download page has two files:

   (1) sqlite-3.7.3.tar.gz (3.25 MiB)
   (2) sqlite-3_7_3.tar.gz (1.19 MiB)

with identical descriptions that identify each as "A tarball of the
complete source tree for SQLite version 3.7.3 as extracted from the
version control system."

Based on the contents, the second tarball (sqlite-3_7_3.tar.gz)
appears to be the TEA tarball for 3.7.3.  Should the tarball name be
changed to sqlite-3_7_3-tea.tar.gz and the Download Page description
corrected?

(Thanks to all who are responsible for making sqlite available!)

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

[sqlite] sqlite3_create_function_v2 not available for extensions

[Ioannis Epaminonda]

sqlite3_create_function_v2 function is not defined in sqlite3ext.h hence not
available for use in extensions.
-- 
View this message in context: 
http://old.nabble.com/sqlite3_create_function_v2-not-available-for-extensions-tp29926215p29926215.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] ADD UNIQUE CONSTRAINT

[P Kishor]

On Sun, Oct 10, 2010 at 3:08 AM, Fadhel Al-Hashim  wrote:
> Thank you,
>
> I was wondering about adding a new constraint to an existing column that is
> currently holding data.

As you can see from the syntax diagram, you can't just add a
constraint. You have to add a column. You can always update the new
constraint-full column with the value from the old constraint-less
column.

Or, recreate the table and copy data from the old table.


>
> On Sun, Oct 10, 2010 at 10:54 AM, P Kishor  wrote:
>
>> On Sun, Oct 10, 2010 at 2:45 AM, Fadhel Al-Hashim 
>> wrote:
>> > Good day,
>> >
>> > is it possible to Alter a table and add a unique constraint on one or
>> more
>> > columns?
>> >
>>
>> See http://www.sqlite.org/lang_altertable.html
>>
>> In particular --
>>
>> "The ADD COLUMN syntax is used to add a new column to an existing
>> table. The new column is always appended to the end of the list of
>> existing columns. The column-def rule defines the characteristics of
>> the new column. The new column may take any of the forms permissible
>> in a CREATE TABLE statement, with the following restrictions:
>>
>> The column may not have a PRIMARY KEY or UNIQUE constraint.
>> The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
>> CURRENT_TIMESTAMP, or an expression in parentheses.
>> If a NOT NULL constraint is specified, then the column must have a
>> default value other than NULL.
>> If foreign key constraints are enabled and a column with a REFERENCES
>> clause is added, the column must have a default value of NULL.
>> Note also that when adding a CHECK constraint, the CHECK constraint is
>> not tested against preexisting rows of the table. This can result in a
>> table that contains data that is in violation of the CHECK constraint.
>> Future versions of SQLite might change to validate CHECK constraints
>> as they are added."
>>
>> > thanks,
>> >
>> > fadhel
>> > ___
>> > 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-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

Re: [sqlite] ADD UNIQUE CONSTRAINT

[Fadhel Al-Hashim]

Thank you,

I was wondering about adding a new constraint to an existing column that is
currently holding data.

On Sun, Oct 10, 2010 at 10:54 AM, P Kishor  wrote:

> On Sun, Oct 10, 2010 at 2:45 AM, Fadhel Al-Hashim 
> wrote:
> > Good day,
> >
> > is it possible to Alter a table and add a unique constraint on one or
> more
> > columns?
> >
>
> See http://www.sqlite.org/lang_altertable.html
>
> In particular --
>
> "The ADD COLUMN syntax is used to add a new column to an existing
> table. The new column is always appended to the end of the list of
> existing columns. The column-def rule defines the characteristics of
> the new column. The new column may take any of the forms permissible
> in a CREATE TABLE statement, with the following restrictions:
>
> The column may not have a PRIMARY KEY or UNIQUE constraint.
> The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
> CURRENT_TIMESTAMP, or an expression in parentheses.
> If a NOT NULL constraint is specified, then the column must have a
> default value other than NULL.
> If foreign key constraints are enabled and a column with a REFERENCES
> clause is added, the column must have a default value of NULL.
> Note also that when adding a CHECK constraint, the CHECK constraint is
> not tested against preexisting rows of the table. This can result in a
> table that contains data that is in violation of the CHECK constraint.
> Future versions of SQLite might change to validate CHECK constraints
> as they are added."
>
> > thanks,
> >
> > fadhel
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADD UNIQUE CONSTRAINT

[P Kishor]

On Sun, Oct 10, 2010 at 2:45 AM, Fadhel Al-Hashim  wrote:
> Good day,
>
> is it possible to Alter a table and add a unique constraint on one or more
> columns?
>

See http://www.sqlite.org/lang_altertable.html

In particular --

"The ADD COLUMN syntax is used to add a new column to an existing
table. The new column is always appended to the end of the list of
existing columns. The column-def rule defines the characteristics of
the new column. The new column may take any of the forms permissible
in a CREATE TABLE statement, with the following restrictions:

The column may not have a PRIMARY KEY or UNIQUE constraint.
The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
CURRENT_TIMESTAMP, or an expression in parentheses.
If a NOT NULL constraint is specified, then the column must have a
default value other than NULL.
If foreign key constraints are enabled and a column with a REFERENCES
clause is added, the column must have a default value of NULL.
Note also that when adding a CHECK constraint, the CHECK constraint is
not tested against preexisting rows of the table. This can result in a
table that contains data that is in violation of the CHECK constraint.
Future versions of SQLite might change to validate CHECK constraints
as they are added."

> thanks,
>
> fadhel
> ___
> 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] ADD UNIQUE CONSTRAINT

[Fadhel Al-Hashim]

Good day,

is it possible to Alter a table and add a unique constraint on one or more
columns?

thanks,

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