Re: [sqlite] Rollback transaction if error

[Max Vlasov]

On Thu, Dec 23, 2010 at 9:52 AM, BareFeetWare wrote:

>
> I want to be able to just send a series of SQL commands in a transaction as
> a one block of text and have SQLite tell me either that it succeeded so
> committed, or that it failed so everything has been rolled back. It is my
> understanding that this is one of the primary purposes of transactions. In
> this way, I can already string together a series of inserts, updates and
> deletes and execute as one block in a transaction, I'd like to be able to do
> the same with other SQL commands (eg drop and create).
>
>

Tom, I think that the idea is to move only first (begin) and last
(commit/rollback) from you queries. In this case your queries can still
contain other queries like drop and create. You just have to check for error
in step command and, analyze sqlite3_get_autocommit() and do rollback
optionally (or do it anyway with a error being not error). Anyway, I think
that getting rid of transaction commands make you queries more portable
since you will be able to concatenate them before wrapping into begin/end

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

Re: [sqlite] Rollback transaction if error

[BareFeetWare]

On 23/12/2010, at 5:38 PM, Drake Wilson wrote:

> Quoth BareFeetWare , on 2010-12-23 15:10:30 +1100:
>> Is there a way to do this in pure SQL, without my application code
>> having to check for errors along the way and then interrogate the
>> SQL to look for a "commit" type line and replace it? This seems
>> pretty error prone and convoluted.
> 
> Why would you introspect the SQL

I want to avoid having to introspect.

> rather than generating the beginning and end of the transaction from a 
> different part of the application code to each statement inside?  Begin 
> transaction, then start executing statements from a list; if any of them 
> fail, stop executing further statements and roll back, otherwise commit at 
> the end.  Don't
> include the begin/end in the list.  Does that not work for you?

It works, but it requires a whole extra level of interaction, whereas I want to 
SQL to take care of it itself, without needing to dissect and branch the SQL in 
application code.

I want to be able to just send a series of SQL commands in a transaction as a 
one block of text and have SQLite tell me either that it succeeded so 
committed, or that it failed so everything has been rolled back. It is my 
understanding that this is one of the primary purposes of transactions. In this 
way, I can already string together a series of inserts, updates and deletes and 
execute as one block in a transaction, I'd like to be able to do the same with 
other SQL commands (eg drop and create).

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Rollback transaction if error

[Drake Wilson]

Quoth BareFeetWare , on 2010-12-23 15:10:30 +1100:
> Is there a way to do this in pure SQL, without my application code
> having to check for errors along the way and then interrogate the
> SQL to look for a "commit" type line and replace it? This seems
> pretty error prone and convoluted.

Why would you introspect the SQL rather than generating the beginning
and end of the transaction from a different part of the application
code to each statement inside?  Begin transaction, then start
executing statements from a list; if any of them fail, stop executing
further statements and roll back, otherwise commit at the end.  Don't
include the begin/end in the list.  Does that not work for you?

> Thanks,
> Tom
> BareFeetWare

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

[sqlite] Rollback transaction if error

[BareFeetWare]

Hi all,

I want to dynamically construct SQL queries that rollback if any part of the 
transaction fails. For instance, my app constructs the SQL needed to replace a 
trigger definition, such as:

begin immediate;
drop trigger if exists "My Trigger";
create trigger "My Trigger"
... new definition
;
commit;

If the new definition fails, I want to rollback so that the old definition 
remains.

I was under the impression that if any error occurred in the transaction that 
SQLite would rollback, but it seems that it only does this in some cases. 
According to:
http://www.sqlite.org/lang_transaction.html

> Response To Errors Within A Transaction
> 
> If certain kinds of errors occur within a transaction, the transaction may or 
> may not be rolled back automatically. The errors that cause the behavior 
> include:
> 
>   • SQLITE_FULL: database or disk full
>   • SQLITE_IOERR: disk I/O error
>   • SQLITE_BUSY: database in use by another process
>   • SQLITE_NOMEM: out or memory
>   • SQLITE_INTERRUPT: processing interrupted by application request
> For all of these errors, SQLite attempts to undo just the one statement it 
> was working on and leave changes from prior statements within the same 
> transaction intact and continue with the transaction. However, depending on 
> the statement being evaluated and the point at which the error occurs, it 
> might be necessary for SQLite to rollback and cancel the entire transaction.

So, it seems I have to, in my application code, step through each command in 
the SQL transaction, note any error, then when it gets to a "commit" command, 
replace it instead with a "rollback".

Is there a way to do this in pure SQL, without my application code having to 
check for errors along the way and then interrogate the SQL to look for a 
"commit" type line and replace it? This seems pretty error prone and convoluted.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

[sqlite] fill in blank fileds

[CDN Mark]

Hi Igor,

worked using SQLite Database browser/execute SQL but didn't work using an .sql 
file which is the way I would be doing it.  Tried it again and it did work 
using sql file, thanks for your help


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

Re: [sqlite] fill in blank fileds

[Igor Tandetnik]

On 12/22/2010 7:25 PM, CDN Mark wrote:
> Hi Igor,
>
> not checking for, want to fill in/replace blank or null fields with at least 
> one character

So, does the statement I've shown not do this? Here it is again:

UPDATE Aircraft SET CN = '*' where CN = '' or CN is null;

In what way does it fail to meet your expectations?
-- 
Igor Tandetnik

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

[sqlite] fill in blank fileds

[CDN Mark]

Hi Simon,
ran the first example, came back with lots of lines, second test with the just 
the 10 returned
9 lines as 0:  the second line as 2:3832,  don't understand the purpose/meaning 
of this

Hi Igor,

not checking for, want to fill in/replace blank or null fields with at least 
one character
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fill in blank fileds

[Igor Tandetnik]

On 12/22/2010 6:55 PM, CDN Mark wrote:
> I tried using a statement
> UPDATE Aircraft SET CN = "*" where CN = "";
> as a test which worked, but not for all records.  I don't know if the blank 
> fields are empty or null

I'm not sure I understand the problem. If you are asking how to check 
both for an empty string and null, just do

UPDATE Aircraft SET CN = '*' where CN = '' or CN is null;

-- 
Igor Tandetnik

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

Re: [sqlite] fill in blank fileds

[Simon Slavin]

On 22 Dec 2010, at 11:55pm, CDN Mark wrote:

> I tried using a statement
> UPDATE Aircraft SET CN = "*" where CN = "";
> as a test which worked, but not for all records.  I don't know if the blank 
> fields are empty or null

Do a

SELECT length(CN),hex(CN) FROM Aircraft

if that produces too many lines do

SELECT length(CN),hex(CN) FROM Aircraft WHERE length(CN)<3 LIMIT 10

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

[sqlite] fill in blank fileds

[CDN Mark]

Hi,

looking for help please.  Database is a db3, fields are varchar.  The problem 
is that a necessary updating service for this database looks for 'blank'  
fields, of which there are many, and if there are any, updates ALL the info for 
that record.  The problem is that I want to update with correct info and don't 
want it overwrittten.  I thought if I could at least insert a sinlge character 
it would
stop the auto update.  I tried using a statement
UPDATE Aircraft SET CN = "*" where CN = "";
as a test which worked, but not for all records.  I don't know if the blank 
fields are empty or null

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

Re: [sqlite] SQLite server

[Sylvain Pointeau]

I think you can hide those details,
you can perfectly retrieve all rows (or bunch of rows) and then do not make
any network access, but still simulate the sqlite API.

I would keep the connection open, it doesn't block other clients to connect
as well, just the server has to be multi-process.

the idea behind is to keep the existing code and make your application
working with a database located on a server. If you want to go to a real
database server, why not going directly to PostgreSQL?

Best regards,
Sylvain

On Wed, Dec 22, 2010 at 11:52 PM, Doug  wrote:

> This is where I think the problems will crop up.  Can you imagine making a
> network round trip for each row fetch,
> and then for each column fetch of each row (sqlite3_column_type,
> sqlite3_column_double, etc).
>
> To avoid that, you would need to fetch a lot (buffer) of data and bring it
> back to the client.  And if the response is
> large, will you page, or keep the transaction open on the server thus
> blocking all other clients?
>
> The devil is always in the details :)
>
> Doug
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
> Sent: Wednesday, December 22, 2010 7:51 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite server
>
> Why not doing it with DCOM or Corba or what ever even the sockets?
> but hidden behind the same API of SQLite. The "real" sqlite lib will be on
> the server.
> is it called "remote procedure call"?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64 bit sqlite 3

[Adam DeVita]

At first I was like "awe, I don't wanna do my homework". I'd have to
recompile all my little utilities and distribute them rather than just
distribute a new DLL and it would be nice to keep our local program
maintainers from "helping" instead of keeping to the officially released
code.

Now, after showing that my 64 bit problems go away when I include the
amalgamation source in the project, the whining just stops.

thanks
:)

Adam

On Fri, Dec 17, 2010 at 5:20 PM, Simon Slavin  wrote:

>
> On 17 Dec 2010, at 4:30pm, Adam DeVita wrote:
>
> > Will a 64 bit Windows DLL eventually be posted for download?
>
> SQLite is distributed as source.  Generally speaking you compile the
> amagamation form directly into your application rather than making a
> separate library of it.  Whatever form you want it in, feel free to compile
> it yourself.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Doug]

This is where I think the problems will crop up.  Can you imagine making a
network round trip for each row fetch, 
and then for each column fetch of each row (sqlite3_column_type,
sqlite3_column_double, etc).

To avoid that, you would need to fetch a lot (buffer) of data and bring it
back to the client.  And if the response is
large, will you page, or keep the transaction open on the server thus
blocking all other clients?

The devil is always in the details :)

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
Sent: Wednesday, December 22, 2010 7:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite server

Why not doing it with DCOM or Corba or what ever even the sockets?
but hidden behind the same API of SQLite. The "real" sqlite lib will be on
the server.
is it called "remote procedure call"?
___
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] Can a custom SQL function return NULL?

[Richard Hipp]

On Wed, Dec 22, 2010 at 1:41 PM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> On 12/22/10 10:35 AM, "Richard Hipp"  wrote:
>
> On Wed, Dec 22, 2010 at 1:16 PM, Duquette, William H (318K) <
> william.h.duque...@jpl.nasa.gov> wrote:
>
> > If I define a custom SQL function in Tcl using the SQLite "$db function"
> > command, is there any way to make the function return NULL?  I'm guessing
> > not.
> >
>
> There is no way to get a Tcl function to return NULL, since TCL has no
> concept of NULL.  So, no, sadly, you cannot get an SQLite function
> implemented in Tcl to return NULL.
>
> I imagine one could write an SQL function in C that returns NULL,
> though, right?


Yes.  In fact, that is the default return value for SQL functions
implemented in C.  You have to take special actions to prevent it.



> What if the API included a function null(x) that
> returns NULL if x is the "nullvalue" string specified with
> [$db nullvalue], and x otherwise?
>

You mean the "nullif()" function?
http://www.sqlite.org/lang_corefunc.html#nullif



>
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> 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] Can a custom SQL function return NULL?

[Duquette, William H (318K)]

On 12/22/10 10:52 AM, "Simon Slavin"  wrote:

>> There is no way to get a Tcl function to return NULL, since TCL has no
>> concept of NULL.  So, no, sadly, you cannot get an SQLite function
>> implemented in Tcl to return NULL.
> 
> ... but you might find reading this useful:
> 
> 
> 

Thanks for the pointer.  I'm familiar with that page,
actually.  :-)

In my usage, the empty string makes a fine substitute for
NULL in my tcl code. The problem is not inserting "" into
tables when I really want NULL.  Historically, I'd pretty
much abandoned the use of NULL; every column had to have
an explicit value, and I'd use markers like "" and "NONE"
to indicate empty values. But now I'm starting to make
use of foreign key constraints, and that solution will no
longer fly.  Time to learn to do it right. :-)

Will

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."


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

[sqlite] [ANN] Free Valentina Studio Pro - Please Give Us Feedback on SQLite Support

[Lynn Fredricks]

Paradigma Software's Valentina Studio Pro is a powerful creation, analysis
and reporting tool for database developers. With Valentina Studio 4.8, we
added support for SQLite databases. This means the built in features like
Visual SQL Builder, Diagrams, Report printing, and more are available for
use with SQLite databases. It is available on Windows, Mac OS X and in a
preview version on Linux.

For the foreseeable future, you'll be able to use Valentina Studio Pro for
free, though it is possible to purchase (it makes some watermarks on the
reports go away).

We'd really appreciate feedback from developers who regularly work with
SQLite databases so we can make it better. There is a mailing list available
and bug reporting system:

Mailing Lists: http://www.valentina-db.com/en/support/mailing-lists

Bug Reports: http://www.valentina-db.com/bt/

Thanks for any help you can give us and best wishes this holiday season :-)

Best regards,

Lynn Fredricks
Paradigma Software
http://www.paradigmasoft.com

Valentina SQL Server: The Ultra-fast, Royalty Free Database Server 

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

Re: [sqlite] Can a custom SQL function return NULL?

[Simon Slavin]

On 22 Dec 2010, at 6:35pm, Richard Hipp wrote:

> On Wed, Dec 22, 2010 at 1:16 PM, Duquette, William H (318K) <
> william.h.duque...@jpl.nasa.gov> wrote:
> 
>> If I define a custom SQL function in Tcl using the SQLite "$db function"
>> command, is there any way to make the function return NULL?  I'm guessing
>> not.
>> 
> 
> There is no way to get a Tcl function to return NULL, since TCL has no
> concept of NULL.  So, no, sadly, you cannot get an SQLite function
> implemented in Tcl to return NULL.

... but you might find reading this useful:



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

Re: [sqlite] Can a custom SQL function return NULL?

[Duquette, William H (318K)]

On 12/22/10 10:35 AM, "Richard Hipp"  wrote:

On Wed, Dec 22, 2010 at 1:16 PM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> If I define a custom SQL function in Tcl using the SQLite "$db function"
> command, is there any way to make the function return NULL?  I'm guessing
> not.
>

There is no way to get a Tcl function to return NULL, since TCL has no
concept of NULL.  So, no, sadly, you cannot get an SQLite function
implemented in Tcl to return NULL.

I imagine one could write an SQL function in C that returns NULL,
though, right?  What if the API included a function null(x) that
returns NULL if x is the "nullvalue" string specified with
[$db nullvalue], and x otherwise?


--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] Can a custom SQL function return NULL?

[Richard Hipp]

On Wed, Dec 22, 2010 at 1:16 PM, Duquette, William H (318K) <
william.h.duque...@jpl.nasa.gov> wrote:

> If I define a custom SQL function in Tcl using the SQLite "$db function"
> command, is there any way to make the function return NULL?  I'm guessing
> not.
>

There is no way to get a Tcl function to return NULL, since TCL has no
concept of NULL.  So, no, sadly, you cannot get an SQLite function
implemented in Tcl to return NULL.



>
> Thanks!
>
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> 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] Can a custom SQL function return NULL?

[Duquette, William H (318K)]

If I define a custom SQL function in Tcl using the SQLite "$db function" 
command, is there any way to make the function return NULL?  I'm guessing not.

Thanks!


--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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

Re: [sqlite] SQLite server

[Sylvain Pointeau]

Why not doing it with DCOM or Corba or what ever even the sockets?
but hidden behind the same API of SQLite. The "real" sqlite lib will be on
the server.
is it called "remote procedure call"?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Simon Slavin]

On 22 Dec 2010, at 2:41pm, Philip Graham Willoughby wrote:

> You would need to reintroduce practically everything you think you can get 
> rid of if you wish to ensure that there is only one process and you are 
> intending to provide a facility for two transactions to be active 
> concurrently.

I agree with your statement but I don't think it's worth doing those things.  
SQLite can still be light, and it's okay for one connection to lock all the 
others out until it is finished.  As long as you support the various types of 
transaction locking (i.e. IMMEDIATE, etc.) and return the appropriate result 
codes, you're okay.

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

Re: [sqlite] SQLite server

[Philip Graham Willoughby]

On 22 Dec 2010, at 13:12, Simon Slavin wrote:

> 
> You do mention something worthwhile: if you had a server/client version of 
> SQLite you could get rid of all the code to do with file sharing and locking. 
>  That's quite a lot of code, if you include all the PRAGMAs and related 
> programming that could be ignored.
> 
> You will need some way to keep multiple connections straight -- each one has 
> its own transaction and rollback status.  But you have the advantage that one 
> process knows everything that needs to be known: it can all be handled by one 
> process, that process knows everything it has to know, and it can keep 
> everything it needs in memory.

You would need to reintroduce practically everything you think you can get rid 
of if you wish to ensure that there is only one process and you are intending 
to provide a facility for two transactions to be active concurrently.

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] how to realize rollback or commit using sqlite?

[Igor Tandetnik]

zhaoyinghuan  wrote:
> I think sqlite can automatically do commit or rollback when sql 
> statments(insert,update,or delete) are finished.
> Is it correct? Or it must use  "Begin transation"?
> Anybody can give me some examples?

You can start an explicit transaction by running BEGIN [TRANSACTION] statement. 
If you do, you would then need to explicitly commit it (with COMMIT or END 
statement) or roll it back (with ROLLBACK statement). All statements that run 
between BEGIN and COMMIT/ROLLBACK belong to this transaction.

If you don't start an explicit transaction, then every statement you run 
automatically starts one at the beginning, and commits at the end.

Igor Tandetnik

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

Re: [sqlite] SQLite server

[jeff archer]

From: Simon Slavin  Date: Tue, 21 Dec 2010 14:19:25 +
>This was my first thought when I considered implementing an SQLite-over-IP 
>protocol: that there was no user model and therefore no need for passwords.  
>Mounting a >database on the server would mean that anyone who knew your IP 
>address, port number and database name could access the data.
>
>So my conclusion was that you would need to implement, in the first case, a 
>simple user privilege model, nominating which users (or which IP addresses) 
>could access >which databases.

In SQLiteServer code the user validation could be handled by a callback into 
the 
application code that is hosting the SQLiteServer amalgamation code.  The 
application code could leave default of NULL and have no user validation or 
supply function and provide appropriate user validation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Simon Slavin]

On 22 Dec 2010, at 12:45am, Jeff Archer wrote:

> A SQLite Server would be userful.  I have considered creating this myself.  I 
> have thought that it would need to be kept zero config and it should be 
> provided 
> in amalgamation form to keep simple to embed in applications.  Wouldn't need 
> incredible throughput as it is a ServerLite.  Would very conveniently avoid 
> the 
> issues of sharing database file across network.

You do mention something worthwhile: if you had a server/client version of 
SQLite you could get rid of all the code to do with file sharing and locking.  
That's quite a lot of code, if you include all the PRAGMAs and related 
programming that could be ignored.

You will need some way to keep multiple connections straight -- each one has 
its own transaction and rollback status.  But you have the advantage that one 
process knows everything that needs to be known: it can all be handled by one 
process, that process knows everything it has to know, and it can keep 
everything it needs in memory.

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

[sqlite] how to realize rollback or commit using sqlite?

[zhaoyinghuan]

Hi, erveryone
 
I'm a  beginner.
 
I think sqlite can automatically do commit or rollback when sql 
statments(insert,update,or delete) are finished.
Is it correct? Or it must use  "Begin transation"?
Anybody can give me some examples?
 
Thanks.
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Jeff Archer]

Just my two cents...

A SQLite Server would be userful.  I have considered creating this myself.  I 
have thought that it would need to be kept zero config and it should be 
provided 
in amalgamation form to keep simple to embed in applications.  Wouldn't need 
incredible throughput as it is a ServerLite.  Would very conveniently avoid the 
issues of sharing database file across network.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Sylvain Pointeau]

and what about using a DCOM like technology to open a distant database?
the sqlite API will stay the same but behind the scene, it will access your
server using a DCOM like technology?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Neville Franks]

Wednesday, December 22, 2010, 1:19:25 AM, you wrote:

SS> On 21 Dec 2010, at 1:44pm, Philip Graham Willoughby wrote:

>> Implementing an SQLite-based server does not obviously enable this in and of 
>> itself. If you could open a database on a remote machine using its filename 
>> as the OP was trying to do it would enable this, but we got into this 
>> discussion when it was discovered that doing so was a bad idea.

SS> This was my first thought when I considered implementing an
SS> SQLite-over-IP protocol: that there was no user model and
SS> therefore no need for passwords.  Mounting a database on the
SS> server would mean that anyone who knew your IP address, port
SS> number and database name could access the data.

SS> So my conclusion was that you would need to implement, in the
SS> first case, a simple user privilege model, nominating which users
SS> (or which IP addresses) could access which databases.

SS> Just to throw into the pot:

SS> It's not that hard to do an extremely simple version of this
SS> using HTTP and either XML or JSON.  Set up a web server with some
SS> PHP or Python pages which take the following parameters:

SS> databasename
SS> command

SS> You call up the right web page with the right values for the
SS> parameters, either as a GET or a PUT.  One web page just executes
SS> the command and returns a result code, result message, etc., in
SS> either XML or JASON, depending on what the programmer likes best. 
SS> A different web page is for executing SELECTs (or some PRAGMAs)
SS> and returns the above plus a table of results.

If you are considering heading down this track I'd suggest seriously
considering using a REST API. I've been doing an evaluation of various
NoSQL Databases recently, mainly with CouchDB and it uses REST quite
effectively. And stick with JSON.

Emulating the WebSQL API may be another option. This is used by
WebKit/Chrome etc. to access SQLite.

And another option could be to use the MySQL tcp/ip interface which I
assume is well documented. I mention this because I am doing some
work with Node.js which has a MySQL interface and am using
XMLHTTPRequest (Ajax) in Javascript in the Browser to access the MySQL
DB on the Web Server via. Node.js.

---
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

[sqlite] FTS3 + SQLITE_OMIT_PAGER_PRAGMAS causes division by zero exception

[Ralf Junker]

Recent changes to FTS3 apparently require that SQLite must be compiled
with pager pragmas, otherwise FTS3 will cause a division by zero
exception as I have experienced right now.

This means that the FTS3 extension can crash an application if the core
SQLite library is compiled with SQLITE_OMIT_PAGER_PRAGMAS.

The following SQL triggers the exception, based on the table data in
fts4aa.test:

SELECT docid, mit(matchinfo(t1)) FROM t1
 WHERE t1 MATCH 'spake hebrew'
 ORDER BY docid;

Debugging is not available to me, but as far as I read the code, FTS3's
dependency on pager pragmas was introduced by check-in 5ae0ba447a on
2010-10-19. With SQLITE_OMIT_PAGER_PRAGMAS, the "PRAGMA %Q.page_size"
command in fts3.c, fts3DatabasePageSize() silently returns 0 instead of
reporting an appropriate error message, which can then lead to a
division by zero exception, most likely in fts3_write.c,
sqlite3Fts3SegReaderCost().

Given that the FTS3 extension can potentially be linked against a SQLite
core compiled with SQLITE_OMIT_PAGER_PRAGMAS and cause unexpected
failure, this might be worth a workaround or error message.

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

Re: [sqlite] SQLite server

[Philip Graham Willoughby]

On 21 Dec 2010, at 14:19, Simon Slavin wrote:

> 
> On 21 Dec 2010, at 1:44pm, Philip Graham Willoughby wrote:
> 
>> Implementing an SQLite-based server does not obviously enable this in and of 
>> itself. If you could open a database on a remote machine using its filename 
>> as the OP was trying to do it would enable this, but we got into this 
>> discussion when it was discovered that doing so was a bad idea.
> 
> This was my first thought when I considered implementing an SQLite-over-IP 
> protocol: that there was no user model and therefore no need for passwords.  
> Mounting a database on the server would mean that anyone who knew your IP 
> address, port number and database name could access the data.

Worse than that: SQLite will attempt to open any filename you give it as if it 
were a database. I do not know what damage could be done by opening 
non-database files, but I suggest that one could rapidly find out if one 
deployed this.

> So my conclusion was that you would need to implement, in the first case, a 
> simple user privilege model, nominating which users (or which IP addresses) 
> could access which databases.

I recommend you get the web server to authenticate people and use HTTPS, rather 
than trying to handle authentication yourself. You'll still need to handle 
authorisation, but that's the easy bit. Never rely solely on IP security; it is 
very easy to spoof or usurp an IP address.

> Just to throw into the pot:
> 
> It's not that hard to do an extremely simple version of this using HTTP and 
> either XML or JSON.  Set up a web server with some PHP or Python pages which 
> take the following parameters:
> 
> databasename
> command

Like this short script does for MySQL, in fact: 
http://www.bin-co.com/php/scripts/sql2json/ It's probably 20 minutes work at 
most to adapt that to use an SQLite module instead of a MySQL module.

For me, this still does not result in anything near an SQLite server, for the 
same reasons that the above script does not constitute a MySQL server.

To be worth having requires a client embedded in the main SQLite library so 
that I can sqlite3_open remote databases and use ATTACH DATABASE to use any 
combination of local and remote databases as one. Short-lived scripts on a web 
server cannot allow all the API functions to work with a remote database using 
this mechanism: ATTACH_DATABASE, SAVEPOINT, BEGIN TRANSACTION, CREATE TEMPORARY 
*, and most of the C API routines cannot work.

To do it properly you would cut SQLite in two between the SQL Command Processor 
and the Virtual Machine. A long-running program on the remote machine would own 
all the database connections made to that machine, and would appropriately 
authenticate/authorise remote users. ATTACH/sqlite3_open of a remote DB would 
need to perform an sqlite3_open on the remote system and that opened database 
would need to persist until the client used DETACH/sqlite3_close or terminated 
(and for reference, detecting termination is extremely hard). The client would 
need to be sent the remote database's schema so that after that point 
communications from the client are in the form of virtual-machine instructions: 
the client handles the parsing/generation of VDBE programs and the server runs 
them. The user-function-execution engine would need extensions to allow 
user-functions on the client machine to be executed. A globally-installed 
user-function mechanism for the server would also be good. Some kind of 2-phase 
commit protocol would need to be used to co-ordinate transactions across 
multiple machines.

It could be done. But it seems pointless to go to all that effort when 
MySQL/Postgres/DB2/Oracle/MSSQL have the advantages for this problem domain 
that they are designed to do this, have been well tested in this architecture, 
are as simple as they can be, and are fully supported.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

[sqlite] Nicolas Bonte is out of the office.

[Nicolas Bonte]

I will be out of the office starting  21/12/2010 and will not return until
05/01/2011.

For urgent matter you can directly contact me on following number :
+32 476962721
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Windows 95

[joel . guittet-ext]

Hi,

Thanks to you to help me with this problem.
As I do not require special features (only insert/select/order by/delete), 
I think it is maybe easier to use an old version of SQLite. I will try 
with 3.6.10 as said by Max. If it does not work I will try an older one.

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