Re: [sqlite] SQLITE_ENABLE_ATOMIC_WRITE on windows, good or bad?

[JT Olds]

On this note, how does one figure out if your system does support
atomic writes (or other FS things, like safe appends)?

Is there an easy way to have SQLite tell you what it finds out?

Alternatively (for my current use case), does Ext3 on 2.6 Linux
support atomic writes or safe appends?

On Mon, Sep 13, 2010 at 7:50 PM, Richard Hipp  wrote:
> On Mon, Sep 13, 2010 at 8:59 PM, Sam Carleton 
> wrote:
>
>> When compiling sqlite for Windows desktop OS's (XP, Vista, Win7), should
>> SQLITE_ENABLE_ATOMIC_WRITE be set or not?
>>
>
> Makes no difference really - windows does not support atomic writes.  So the
> atomic write feature will never be used. You might as well leave it turned
> off, to save code space, and a couple of branch instructions in the pager.
>
>
>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] fcntl file locks from different threads? (what constitutes a "troublesome system"?)

[JT Olds]

I have a large deployment of thousands of SQLite databases accessed
from the same multi-threaded process, and up until recently, I didn't
even consider thread safety, because

1) I only ever talk to a SQLite database connection from one thread at
a time, and
2) I am dumb.

I do maintain SQLite database connections from threadpools, and so,
often, I am jumping the connection between threads within the
threadpool, with open transactions, prepared statements, etc, though
never concurrently.

I was just pointed to http://www.sqlite.org/faq.html#q6

We are using SQLite 3.6.22 on Debian Etch (sadness), and I just wrote
some test code around using fcntl to lock and unlock a file from
different threads, and I can't seem to get it to break, nor have we
seen any trouble with our deployment.

So, my questions are:

1) Did we somehow magically avoid this bullet?
2) What situations with fcntl in multiple threads cause sadness?
3) Is Etch a "troublesome system"?
4) What would failures in this scenario look like? Are we risking corruption?

My test code is Python, but Python uses native fcntl and pthreads in
the interpreter to implement its modules, so this should be similar to
what we're doing in our deployment. I run these two python scripts
concurrently on Etch and it works as expected.

#!/usr/bin/python
import fcntl, threading, time
f = file("/tmp/test-fcntl", 'w')
def thread1():
fcntl.lockf(f.fileno(), fcntl.LOCK_EX)
print "locked"
time.sleep(5)
def thread2():
time.sleep(5)
print "unlocking"
fcntl.lockf(f.fileno(), fcntl.LOCK_UN)
print "unlocked"
threads = [threading.Thread(target=t) for t in (thread1, thread2)]
for thread in threads: thread.start()
for thread in threads: thread.join()
# make sure other process locks when we unlock and not when we exit
time.sleep(3)

#!/usr/bin/python
import fcntl, time
time.sleep(1)
f = file("/tmp/test-fcntl", 'w')
print "locking 2"
fcntl.lockf(f.fileno(), fcntl.LOCK_EX)
print "locked 2"

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

Re: [sqlite] sqlite database handle caching and write permissions

[JT Olds]

> Unsafe. Using the authorizer callback instead to figure out if a
> statement may write the database is a better way:
>
>   http://www.sqlite.org/c3ref/c_alter_table.html
>   http://www.sqlite.org/c3ref/set_authorizer.html

Beautiful Dan, thank you. That problem I think has been nailed.

Any ideas on the shared cache issue? I went and reread the
documentation, and it's still unclear to me as to whether or not the
shared cache feature works across two database handles open to the
same database in non-concurrent sequence.

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

Re: [sqlite] sqlite database handle caching and write permissions

[JT Olds]

I really would rather not depend on what is in the SQL itself, as the
concern I have has nothing to do with whether or not the user runs
SELECT, but whether or not this will cause the library to write to
disk. I'd love to decouple those two things.

To that end, from an API perspective, is it safe to cast a sqlite_stmt
struct to the Vdbe struct, and just check the readOnly flag? In other
words, how static is that struct? My Lua interface could require that
all SQL gets prepared, and then I could reject prepared statements
that aren't readOnly myself.

-JT

On Thu, Jul 15, 2010 at 12:41 PM, Jim Morris <jmor...@bearriver.com> wrote:
> You also need to watch for multiple command separated via ';'
>
> On 7/15/2010 11:36 AM, JT Olds wrote:
>> I considered that also, but I wasn't sure about whether or not that
>> guaranteed no disk writes (maybe some sort of function call might be
>> made there). That also restricts things like the usage of in-memory
>> temp tables that might be useful. It appears that sqlite knows whether
>> or not a statement will definitively, actually hit disk, whereas
>> filtering by SELECT seemed unclear to me as to whether it would quite
>> cover or catch everything.
>>
>> If that is truly the best way, then that's fine I guess.
>>
>> -JT
>>
>> On Thu, Jul 15, 2010 at 12:25 PM, Simon Slavin<slav...@bigfraud.org>  wrote:
>>
>>> On 15 Jul 2010, at 7:07pm, JT Olds wrote:
>>>
>>>
>>>> is there a way to check a prepared statement
>>>> before allowing its use as to if it will attempt to write to disk?
>>>>
>>> You could perhaps accept only statements that start with 'SELECT'.  It 
>>> depends on how you're passing them to SQLite.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite database handle caching and write permissions

[JT Olds]

I considered that also, but I wasn't sure about whether or not that
guaranteed no disk writes (maybe some sort of function call might be
made there). That also restricts things like the usage of in-memory
temp tables that might be useful. It appears that sqlite knows whether
or not a statement will definitively, actually hit disk, whereas
filtering by SELECT seemed unclear to me as to whether it would quite
cover or catch everything.

If that is truly the best way, then that's fine I guess.

-JT

On Thu, Jul 15, 2010 at 12:25 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 15 Jul 2010, at 7:07pm, JT Olds wrote:
>
>> is there a way to check a prepared statement
>> before allowing its use as to if it will attempt to write to disk?
>
> You could perhaps accept only statements that start with 'SELECT'.  It 
> depends on how you're passing them to SQLite.
>
> Simon.
> ___
> 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] sqlite database handle caching and write permissions

[JT Olds]

Hello all,
 I have two slightly related questions regarding sqlite database handles.

First, I'm developing a system that opens thousands of different
sqlite databases at a variety of times, some database handles more
often than others, but never the same one multiple times concurrently,
though sometimes the same database handle will get opened and closed
multiple times in a row in quick succession. My understanding is that
this isn't as performant as it could be, since it's a shame I'm not
just reusing sqlite pages in memory instead of swapping to and from
disk. My question regards the shared cache feature
(http://www.sqlite.org/c3ref/enable_shared_cache.html). Is this an
optimization for the same sqlite db handle being opened concurrently,
or does this actually assist with keeping sqlite pages in memory so
they don't have to be read from disk again the second time the same
database handle is opened after the first one has been closed? If so,
how do I configure the amount of memory sqlite uses for caching said
pages, and if not, is there anything better I can do besides keeping
an LRU cache of DB handles open?

Second, in the same system, on these db handles, I am considering
allowing users of my system to provide Lua-scripts with built in SQL
queries and run them, but I don't want them to be able to modify the
database. Of course, I can run the Lua script with appropriate
bindings to my existing db handles inside a transaction, but I'd
rather have SQL queries that may potentially write to disk fail
completely, instead of wasting resources just to get rolled back. Is
there a way to temporarily mark a read/write sqlite disk handle
read-only, or perhaps is there a way to check a prepared statement
before allowing its use as to if it will attempt to write to disk?
Digging through the code it looks like I could potentially set
readOnly flags in various structs (pager, vdbe, etc), but there
doesn't seem to be a library interface to do so. I suppose if the
shared cache works as I would like, there is little performance harm
in closing the database and then reopening in read-only mode.

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

Re: [sqlite] Mathematical "power" operator?

[jt]

Hi,

Look at the new "loadable extension" feature:
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions
It also describe an example that could be really easy to convert to
your sqrt problem.

--
Julien

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] how to create an autoincremented rowid in a view

[jt]

Hello,

Is there a way to have the rowid not null in a view?


.nullvalue NULL

create table t(c);
insert into t values(1);
insert into t values(2);

create view v as select * from t, t as t2;
select rowid, * from v;


rowid is always null but I want it to be different for every row of the
view. Actually, rowid should only identify a row in the view during a query,
not between two queries on the view (and I suspect this behaviour to be
easier than enforcing serial number to a view's rows).

Oracle has the ROWNUM pseudo column that records the SELECT iterarion
number. I think it could be used to have row serial number in a view.
Is there a work around for sqlite?

I can create my view with a SELECT random(), * ... since random() is now 64
bits, but I definetly prefer small, localized numbers.

--
jt

Re: [sqlite] Inserting python data structues into database

[jt]

From pysqlite http://initd.org/tracker/pysqlite.


On 6/2/06, John Stanton <[EMAIL PROTECTED]> wrote:

Python newsgroup wrote:
> Hi,
>
> What is the most efficient way to enter python binary data such as lists or
> dictionaries in to sqlite? Has anyone had any experiences with this? We
> will
> be inserting a list of lists of integers into our database.
> For example:
> [[1,2,3],[1,4,6],[1,1,1],[2,4,6],[12,32,4],...,[1,3,4]]
>
> Any suggestions will be appreciated
>
> cheers,
> Bijan
>
How do you want to access it?




--
Julien

Re: [sqlite] Inserting python data structues into database

[jt]

Try the pickle module, it dumps nearly any datastruct (except file
object) in a string.
You can load it back afterwards.

On 6/1/06, Python newsgroup <[EMAIL PROTECTED]> wrote:

Hi,

What is the most efficient way to enter python binary data such as lists or
dictionaries in to sqlite? Has anyone had any experiences with this? We will
be inserting a list of lists of integers into our database.
For example:
[[1,2,3],[1,4,6],[1,1,1],[2,4,6],[12,32,4],...,[1,3,4]]

Any suggestions will be appreciated

cheers,
Bijan





--
Julien

[sqlite] SQLITE3.exe from Windows CMD

[Josef Hlawatschek \(JT\)]

Hi,

I want to use SQLITE3.EXE to run automated reports in Windows. I use
an init file to load parameters and a SQL script file. All goes well
until the SQL script has been executed, then the SQLITE3.EXE command
prompt appears. The command prompt only closes once you manually type
in ".exit" or ".quit". I have tried to add the ".exit" to the init and
SQL script file and it still does not close automatically.

I do not need to programmatically connect to the SQLITE DB, so I would
like to keep it as simple as possible.

Any assistance would be greatly appreciated

Kind Regards

Josef Hlawatschek (Pr.Eng)
Telkom SA Limited
Tel: 012.680.8290

NB: This e-mail and its contents are subject to the Telkom SA Limited
e-mail legal notice which can be viewed at:

http://www.telkom.co.za/TelkomEMailLegalNotice.PDF

Should you be unable to access the link provided, a copy of the legal
notice will be e-mailed to you on sending a blank e-mail to
[EMAIL PROTECTED]



~~
This e-mail and its contents are subject to the Telkom SA Limited
e-mail legal notice available at
http://www.telkom.co.za/TelkomEMailLegalNotice.PDF
~~

Re: [sqlite] quote() and constraints

[jt]

On 3/29/06, Pam Greene <[EMAIL PROTECTED]> wrote:
> On 3/29/06, jt <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> >
> > I'm implementing a log procedure with triggers on my tables (following
> > some ideas found in http://www.sqlite.org/cvstrac/wiki?p=UndoRedo).
> > As I have more than one table, I'm generating the relevant SQL to do the
> > job.
> > I use the quote() function to quote each value of each row.
> >
> > The problem is that " SELECT quote('foo')!='foo' ": when I tried to
> > load the log table in another database, I get "SQL error; constraint
> > failed" on every row that has a CHECK constraint.
> > The quick solution is to add quote() around each value in the CHECK
> > statement.
> >
> > Is there another way to do it?
> > Why is " SELECT quote('foo')!='foo' "?
>
>
> quote('foo') returns 'foo', including the ' '.  The 'foo' on your right-hand
> side doesn't include the quotes.
>
> - Pam
>
>

thanks.
I was using it in the wrong way. I should have rtfm.

--
jt

[sqlite] quote() and constraints

[jt]

Hi,

I'm implementing a log procedure with triggers on my tables (following
some ideas found in http://www.sqlite.org/cvstrac/wiki?p=UndoRedo).
As I have more than one table, I'm generating the relevant SQL to do the job.
I use the quote() function to quote each value of each row.

The problem is that " SELECT quote('foo')!='foo' ": when I tried to
load the log table in another database, I get "SQL error; constraint
failed" on every row that has a CHECK constraint.
The quick solution is to add quote() around each value in the CHECK statement.

Is there another way to do it?
Why is " SELECT quote('foo')!='foo' "?

config: sqlite 3.3.4

--
jt

[sqlite] forcing primary key range

[jt]

Hello,

Is there a way to force the range value taken by a primary key?

Suppose I have a table that will never contain more than 2**20 rows.
On some occasion, I want new inserted rows to have a pkey in [0,
2**32), on another occasion they would take their value in [2**32,
2*33), etc. In my problem, I can ensure that a range will not be
overflowed.

So I need to be able to set the pkey range, back and forth depending
on the "occasion".
I tried to fool the sqlite_sequence table but it didn't work.

Is there a way to do this natively?

--
jt