Re: [sqlite] Autocommit in "with" query: bug or feature?

[big stone]

Hi again,

To answer the question in the title :
- yes, the "autocommit" bug is buggy with "with",
  http://bugs.python.org/issue21718
- it is also buggy with comments at the beginning of the 'select'.

The "autocommit" feature was a false good idea, but as sqlite3 arrived like
that in the standard library, they can't "change" this default.

If I understood well, the "autocommit" idea was to not "hold" the database
file by default, and ease multi-user access to the file database.


I tested your example with Python 2.7.5 and SQLite3.8.4, works nicely.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[Keith Medcalf]

ie, this will work:  isolation_level = None and explicitly "BEGIN" transactions 
...

import sqlite3

def displayDBcontents():
query = "select * from PERSON"
c.execute(query)
print
for row in c: print '%-4s %-10s' % (row[0],row[1])

# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db', isolation_level=None)
c = conn.cursor()
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")

c.execute('BEGIN')
c.execute("insert into PERSON values ('p1','Smith'), ('p2','Dermiez')")
conn.commit()

# We check the contents of table PERSON
displayDBcontents()

# We insert Jones and we check the contents of PERSON

c.execute('BEGIN')
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()

# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]

#  We cancel the last insertion (Jones should disappear)
#  and we check the contents of PERSON
conn.rollback()
displayDBcontents()

# Surprise: Jones still is in the DB

c.close()
conn.close()

>test

p1   Smith
p2   Dermiez

p1   Smith
p2   Dermiez
p3   Jones

1
2

p1   Smith
p2   Dermiez

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Jean-Luc Hainaut
>Sent: Saturday, 5 July, 2014 13:56
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Autocommit in "with" query: bug or feature?
>
>Hi,
>
>Context: Python 2.7.6, Windows XP, SQLite v3.8.5.
>
>The following test program suggest that "with" queries automatically
>execute a commit, as if they were DDL statements. I hope this is a bug,
>otherwise, this side effect considerably reduces the interest of this
>query.
>
>Best regards
>
>Jean-Luc Hainaut
>
>
># -*- coding: UTF8 -*-
>import sqlite3
>
>def displayDBcontents():
>query = "select * from PERSON"
>c.execute(query)
>print
>for row in c: print '%-4s %-10s' % (row[0],row[1])
>
># We create and fill the STAFF database
>conn = sqlite3.connect('STAFF.db')
>c = conn.cursor()
>c.execute("drop table if exists PERSON")
>c.execute("create table PERSON (PID char(4),Name char(10))")
>c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
>conn.commit()
>
># We check the contents of table PERSON
>displayDBcontents()
>
># We insert Jones and we check the contents of PERSON
>c.execute("insert into PERSON values('p3','Jones')")
>displayDBcontents()
>
># We execute a simple "with" query
>c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
>print
>for row in c: print row[0]
>
>#  We cancel the last insertion (Jones should disappear)
>#  and we check the contents of PERSON
>conn.rollback()
>displayDBcontents()
>
># Surprise: Jones still is in the DB
>
>c.close()
>conn.close()
>
>Prof. Jean-Luc Hainaut
>Faculté d'Informatique
>University of Namur
>Rue Grandgagnage, 21
>B-5000 - Namur (Belgium)
>Phone (direct) : +32 (81) 72 49 96
>Phone (secret.): +32 (81) 72 49 64
>Fax: +32 (81) 72 49 67
>E-mail : jlhain...@info.fundp.ac.be
>http://www.info.fundp.ac.be/libd
>
>___
>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] Autocommit in "with" query: bug or feature?

[big stone]

Hi Jean-Luc,


All your problem is that you must use conn.isolation_level = None
With conn.isolation_level = "" , the default of sqlite3, nothing related
with transaction will work.

Just try with :
* this program :
https://github.com/stonebig/sqlite_bro/blob/master/sqlite_bro.py
(or pip install sqlite_bro)

* your example slightly reworked :

-- use conn.isolation_level = None , when you want to manipulate
transactions
drop table if exists PERSON;
create table PERSON (PID char(4),Name char(10));
insert into PERSON values ('p1','Smith'),('p2','Dermiez');

BEGIN TRANSACTION;
-- We check the contents of table PERSON
select * from PERSON;

-- We insert Jones and we check the contents of PERSON
insert into PERSON values('p3','Jones');
select * from PERSON;

-- We execute a simple "with" query
with CTE(A) as (values (1),(2)) select A from CTE;

-- We cancel the last insertion (Jones should disappear)
-- and we check the contents of PERSON
ROLLBACK;
select * from PERSON;

-- No Surprise: Jones IS NO MORE in the DB
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] think I need better error-handling guidance in the C API

[James K. Lowden]

On Sat, 5 Jul 2014 17:48:41 +0100
Klaas V  wrote:

> James K. Lowden wrote:
> 
> >If the answer to every question, concern, and suggestion is that it
> >already works for millions of programs, there is no point in
> >discussion or further development.  Just use what's there because
> >it's already perfect. 
> 
> Permission to differ, James!

Granted, Klass!  ;-) 

> Since there is no such thing as a perfect application, because no
> living perfect developer exists except probably (the) God(s). SQLite
> however comes pretty close, that's why it has millions of users, fans
> if you like to call them like that. 

I don't understand.  You latched onto "perfect".  Do you maintain your
point if I withdraw it and say only, "Just use what's there because
it's good enough for them"?  

Let me explain why "millions" is not, ipso facto, an argument.  

The set of people who will voice reasonable but rare
documentation complaints on this list might be expressed as

N = U ? K ? D ? S ? V

where
U is the set of SQLite programmers
K is the set knowledgable enough to see a flaw 
  (not just a missing feature or pet peeve)
D is the set who care about documentation
S is the set who subscribe to this list
V is the set who will voice their concern

N might be very small, even if U is measured in millions, if D, S, and
V are small subsets.  We know S, D, and V to be small relative to U. 
We hardly need make assertions about K.  

Relieve any of those constraints, say, K, and you have a larger set.
The number of people who do not see a real flaw grows with the knowledge
necessary to understand it.  The fact that no one has mentioned a
concern previously could be an indication it is not valid.  It
could also just be an indication it's not apparent to D ? S ? V.  

So, you see, perfection is not relevant and millions is not many!  

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

Re: [sqlite] Can't make SQLite work

[mm.w]

as we start low a more simple solution if recent features are needed

http://www.macports.org/

sudo port install sqlite3

best


On Sun, Jul 6, 2014 at 10:27 AM, Richard Hipp  wrote:

> On Sat, Jul 5, 2014 at 3:46 PM, Mike Seabrook Sr 
> wrote:
>
> > Hi there,
> >
> > I am a Masters student at Grand Canyon University. We are to download
> your
> > product. You can download the executable free of charge directly from the
> > SQLite project Web site at http://sqlite.org, which also provides
> > extensive documentation and tutorials on its usage. Yet only MacBook Pro
> I
> > am using the Mountain Lion OS X 10.8.5. I can't make your program
> download
> > to complete this week's homework assignment to turn in by Wednesday
> > morning. Can you please guide me to making this work?
> >
>
> All Macs for the past 8 years come with the "sqlite3" program
> pre-installed.  Just type "sqlite3" from the command-line prompt.
>
> You'd only need to download a new version if you need some of the more
> recent features.
>
>
>
>
> >
> > Mike Seabrook
> > 602-717-7462.
> > ___
> > 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] Sqlite in dead lock state when deleting records from the same table from different threads

[mm.w]

what's the syscall set behind the scene might help, os?


On Sun, Jul 6, 2014 at 6:04 PM, Srikanth Bemineni <
bemineni.srika...@gmail.com> wrote:

> Hi,
>
> Is it possible for any  SQLLite developer to explain the locking mechanism
> in case of the shared connections, specifically table level locking, how I
> can debug this and find out who is holding the lock. ?
>
> Srikanth Bemineni
>
>
>
>
> On Thu, Jul 3, 2014 at 12:47 PM, Srikanth Bemineni <
> bemineni.srika...@gmail.com> wrote:
>
> > Hi,
> >
> > But in shared cache mode. I assume this is going to be a table level
> lock,
> > instead of a lock on the whole database. This will really block other
> > threads which are dealing with other tables.
> >
> >
> > http://www.sqlite.org/sharedcache.html
> >
> > 2.1 Transaction Level Locking
> >
> > SQLite connections can open two kinds of transactions, read and write
> > transactions. This is not done explicitly, a transaction is implicitly a
> > read-transaction until it first writes to a database table, at which
> point
> > it becomes a write-transaction.
> >
> > At most one connection to a single shared cache may open a write
> > transaction at any one time. This may co-exist with any number of read
> > transactions.
> > 2.2 Table Level Locking
> >
> > When two or more connections use a shared-cache, locks are used to
> > serialize concurrent access attempts on a per-table basis. Tables support
> > two types of locks, "read-locks" and "write-locks". Locks are granted to
> > connections - at any one time, each database connection has either a
> > read-lock, write-lock or no lock on each database table.
> >
> > At any one time, a single table may have any number of active read-locks
> > or a single active write lock. To read data a table, a connection must
> > first obtain a read-lock. To write to a table, a connection must obtain a
> > write-lock on that table. If a required table lock cannot be obtained,
> the
> > query fails and SQLITE_LOCKED is returned to the caller.
> >
> > Once a connection obtains a table lock, it is not released until the
> > current transaction (read or write) is concluded.
> >
> >
> > As per the above documentation
> > "Once a connection obtains a table lock, it is not released until the
> > current transaction (read or write) is concluded."
> >
> > This means once the statement is finalized or the whole transaction
> > is committed. Currently I am getting an error on table level locks
> >
> > Thread 1 SQLITE_LOCKED(6) Error  is locked
> > Thread 2 SQLITE_LOCKED(6) Error database table is locked
> >
> > Srikanth Bemineni
> >
> >
> > On Thu, Jul 3, 2014 at 12:35 PM, Simon Slavin 
> > wrote:
> >
> >>
> >> On 3 Jul 2014, at 6:11pm, Srikanth Bemineni <
> bemineni.srika...@gmail.com>
> >> wrote:
> >> >
> >> > As per Igor
> >> > BEGIN IMMEDIATE should get a write lock on the table 1 when first
> select
> >> > call is initiated
> >> >
> >> > 10:00.234 Thread 1 BEGIN
> >> > 10:00.235 Thread 1 select * from 
> >> > 10:00.234 Thread 1 select * from 
> >> > 10:00.456 Thread 1 delete from 
> >> > 10:00.500 Thread 1 COMMIT
> >> >
> >> > Igor
> >> >
> >> > 1. If there is no second thread , then the above transaction works
> fine.
> >> > Here also I am doing the select operation first . So the same thread
> can
> >> > update a read lock to write lock ?
> >> >
> >> > 2. Will BEGIN IMMEDIATE  get a write lock on the table for the first
> >> select
> >> > statement as per the  thread sequence above.
> >>
> >> You're referring to 'read lock' and 'write lock' but it's easier to
> think
> >> of there just being a lock.
> >>
> >> BEGIN IMMEDIATE gets a lock right there at the BEGIN IMMEDIATE command.
> >>  It doesn't have to wait for anything later.  Now nothing else can
> happen
> >> to the database until the COMMIT/ROLLBACK.
> >>
> >> 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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Srikanth Bemineni]

Hi,

Is it possible for any  SQLLite developer to explain the locking mechanism
in case of the shared connections, specifically table level locking, how I
can debug this and find out who is holding the lock. ?

Srikanth Bemineni




On Thu, Jul 3, 2014 at 12:47 PM, Srikanth Bemineni <
bemineni.srika...@gmail.com> wrote:

> Hi,
>
> But in shared cache mode. I assume this is going to be a table level lock,
> instead of a lock on the whole database. This will really block other
> threads which are dealing with other tables.
>
>
> http://www.sqlite.org/sharedcache.html
>
> 2.1 Transaction Level Locking
>
> SQLite connections can open two kinds of transactions, read and write
> transactions. This is not done explicitly, a transaction is implicitly a
> read-transaction until it first writes to a database table, at which point
> it becomes a write-transaction.
>
> At most one connection to a single shared cache may open a write
> transaction at any one time. This may co-exist with any number of read
> transactions.
> 2.2 Table Level Locking
>
> When two or more connections use a shared-cache, locks are used to
> serialize concurrent access attempts on a per-table basis. Tables support
> two types of locks, "read-locks" and "write-locks". Locks are granted to
> connections - at any one time, each database connection has either a
> read-lock, write-lock or no lock on each database table.
>
> At any one time, a single table may have any number of active read-locks
> or a single active write lock. To read data a table, a connection must
> first obtain a read-lock. To write to a table, a connection must obtain a
> write-lock on that table. If a required table lock cannot be obtained, the
> query fails and SQLITE_LOCKED is returned to the caller.
>
> Once a connection obtains a table lock, it is not released until the
> current transaction (read or write) is concluded.
>
>
> As per the above documentation
> "Once a connection obtains a table lock, it is not released until the
> current transaction (read or write) is concluded."
>
> This means once the statement is finalized or the whole transaction
> is committed. Currently I am getting an error on table level locks
>
> Thread 1 SQLITE_LOCKED(6) Error  is locked
> Thread 2 SQLITE_LOCKED(6) Error database table is locked
>
> Srikanth Bemineni
>
>
> On Thu, Jul 3, 2014 at 12:35 PM, Simon Slavin 
> wrote:
>
>>
>> On 3 Jul 2014, at 6:11pm, Srikanth Bemineni 
>> wrote:
>> >
>> > As per Igor
>> > BEGIN IMMEDIATE should get a write lock on the table 1 when first select
>> > call is initiated
>> >
>> > 10:00.234 Thread 1 BEGIN
>> > 10:00.235 Thread 1 select * from 
>> > 10:00.234 Thread 1 select * from 
>> > 10:00.456 Thread 1 delete from 
>> > 10:00.500 Thread 1 COMMIT
>> >
>> > Igor
>> >
>> > 1. If there is no second thread , then the above transaction works fine.
>> > Here also I am doing the select operation first . So the same thread can
>> > update a read lock to write lock ?
>> >
>> > 2. Will BEGIN IMMEDIATE  get a write lock on the table for the first
>> select
>> > statement as per the  thread sequence above.
>>
>> You're referring to 'read lock' and 'write lock' but it's easier to think
>> of there just being a lock.
>>
>> BEGIN IMMEDIATE gets a lock right there at the BEGIN IMMEDIATE command.
>>  It doesn't have to wait for anything later.  Now nothing else can happen
>> to the database until the COMMIT/ROLLBACK.
>>
>> 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-3.8.5: is auto index created multiple times for a single statement?

[Nissl Reinhard]

Hi,

while preparing this statement


create table gpBestellvorschlagInfo as
select GanttPlanID
, BestellterminRaw
, case when not (
 select max(HinweisCodiert)
 from Bestellvorschläge
 where ArtikelOID = o.ArtikelOID
 and HinweisCodiert <> 1
) is null then
(
 select group_concat(Hinweis, x'0d0a')
 from (
 select distinct Hinweis
 from Bestellvorschläge
 where ArtikelOID = o.ArtikelOID
 order by HinweisCodiert desc
 )
)
else
 null
end as Hinweis
from BestellVorschläge o
where not GanttPlanID is null


I get this log message twice:

automatic index on Bestellvorschläge(ArtikelOID)

I hope that the index is created only once when executing the prepared 
statement. But does the query optimizer consider the costs for creating the 
index twice?

Bye.
--
Reinhard Nißl, TB3, -198

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

Re: [sqlite] think I need better error-handling guidance in the C API

[Eric Rubin-Smith]

Klaas V wrote: 

> BTW (someone else wrote this): to call a program 'crappy' sounds a wee 
> bit megalomanic...  

:-) Are you denying that crappy programs exist in the world, or are you 
saying that they do exist but that stating something true about them 
is megalomaniacal?  Keeping in mind that I include most of the code 
I write among that which is crappy.  And that the very reason for my 
post was to avoid the very real danger that I am about to undertake the 
creation of more such crappy code (the result of which will no doubt be 
that this unfortunate forum will be subjected to more of my ridiculous 
questions).  

Also, I was trying to be funny.  Which I thought I was making pretty 
clear.  Apparently I need to add some of my jokes, and my writing 
ability in general, to the list of crappy things in the world.  

Even if I *were* a megalomaniac, and even if all those zillion programs 
*were* of perfect quality, that would *still* not be a refutation of my 
very narrow point, which is that the docs are lacking in the specific 
ways that I enumerated.  

If they are not lacking in those ways because I misread the docs or 
missed one of the pages outright, then I'm asking with apologies to be 
corrected and directed to what I missed or misread.  

But people familiar with the library mostly seem to agree with my 
material point.  

So!  Just looking for a ticket, so the relevant material can be added at 
the pleasure and convenience of the docs maintainers.  

-- 
Eric A. Rubin-Smith

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

Re: [sqlite] Autocommit in "with" query: bug or feature?

[Keith Medcalf]

Python 2.7.8 cannot run your program using the shipped version of SQLite.  It 
does not support either multiple valued inserts nor the WITH statement.  Did 
you just "drop in" a new version of the sqlite3.dll?

While this (dropping a new version of sqlite3.dll) will work fine mostly, the 
dbapi2 compatible layer diddles around with transactions in order to support 
its version of autocommit.  

In order to do so, the _sqlite3.pyd module must detect the "statement type".  
Contained within cursor.c is the following code:

if (!strcmp(buf, "select")) {
return STATEMENT_SELECT;
} else if (!strcmp(buf, "insert")) {
return STATEMENT_INSERT;
} else if (!strcmp(buf, "update")) {
return STATEMENT_UPDATE;
} else if (!strcmp(buf, "delete")) {
return STATEMENT_DELETE;
} else if (!strcmp(buf, "replace")) {
return STATEMENT_REPLACE;
} else {
return STATEMENT_OTHER;
}

You will note that this code classifies statements that begin with the word 
"WITH" as belonging to class "OTHER".  "OTHER" effectively means a DDL 
statement and an autocommit operation is done after DDL statements execute.  
You need to change this to something like the following:

if (!strcmp(buf, "select")) {
return STATEMENT_SELECT;
} else if (!strcmp(buf, "with")) {
return STATEMENT_SELECT;
} else if (!strcmp(buf, "insert")) {
return STATEMENT_INSERT;
} else if (!strcmp(buf, "update")) {
return STATEMENT_UPDATE;
} else if (!strcmp(buf, "delete")) {
return STATEMENT_DELETE;
} else if (!strcmp(buf, "replace")) {
return STATEMENT_REPLACE;
} else {
return STATEMENT_OTHER;
}

Once this change is made (and Python and the extension compiled and installed), 
your code executes as expected.

You can either download pysqlite from the PPI here 
https://pypi.python.org/pypi/pysqlite and build your own extension with the 
above modification; ask the python developers if they can fix this in their 
distribution when they include the new version of SQLite supporting the WITH 
statement; or extract and fix the _sqlite3 extension in Python yourself.

When I tested this it was with the current head of trunk for SQLite3 and the 
currently available pysqlite from above.  I made a few changes to the 
distribution of pysqlite to include the above change, and also so that you can 
simply

import pysqlite2 as sqlite3

instead of

import pysqlite2.dbapi2 as sqlite3

by modifying the __init__.py in addition to the cursor.c change.  Importing 
pysqlite2.dbapi2 as sqlite3 will still work, of course.

I do not know how this version of pysqlite (2.6.3) compares to the one included 
with python (version 2.6.0).  However, you can download the bits here:

http://www.dessus.com/files/pysqlite-2.6.3.win32-py2.7.exe
http://www.dessus.com/files/pysqlite-2.6.3.zip

Note that the prebuilt extension may not work for you, but it works for me.  
You may have to download the extension source (zip), update the sqlite3.lib and 
sqlite3.h with the files corresponding to the version of sqlite3 you want to 
support, and build the extension yourself.

Alternatively, you may want to look into APSW by Roger Binns, who is also on 
this list.  APSW is a Pythonic wrapper for SQLite3.  It is not an dbapi2 
complianct replacement since it exposes all the capabilities of SQLite3 and 
does not limit it to the lowest common denominator of functionality.  APSW can 
be found here:

https://github.com/rogerbinns/apsw

Make sure you read the documentation -- it is not the same as the dbapi2 
interface even though it may appear to be.  Roger generally updates APSW with 
each release of SQLite3 to include new features found in SQLite.

I may extract the code from the Python distribution and fork a pysqlite that 
mimics the _sqlite3.pyd extension in the Python distribution with the above 
StatementKind fix.  I'll have to see how doable that is.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Jean-Luc Hainaut
>Sent: Saturday, 5 July, 2014 13:56
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Autocommit in "with" query: bug or feature?
>
>Hi,
>
>Context: Python 2.7.6, Windows XP, SQLite v3.8.5.
>
>The following test program suggest that "with" queries automatically
>execute a commit, as if they were DDL statements. I hope this is a bug,
>otherwise, this side effect considerably reduces the interest of this
>query.
>
>Best regards
>
>Jean-Luc Hainaut
>
>
># -*- coding: UTF8 -*-
>import sqlite3
>
>def displayDBcontents():
>query = "select * from PERSON"
>c.execute(query)
>print
>for row in c: print '%-4s %-10s' % (row[0],row[1])
>
># We create and fill the STAFF database
>conn = sqlite3.connect('STAFF.db')
>c = conn.cursor()
>c.execute("drop table if exists PERSON")
>c.execute("create table PERSON (PID char(4),Name char(10))")
>c.execute("insert into PERSON values 

Re: [sqlite] Autocommit in "with" query: bug or feature?

[RSmith]

On 2014/07/05 21:55, Jean-Luc Hainaut wrote:

Hi,

Context: Python 2.7.6, Windows XP, SQLite v3.8.5.

The following test program suggest that "with" queries automatically execute a 
commit, as if they were DDL statements. I hope this is a bug, otherwise, this side effect 
considerably reduces the interest of this query.


Bonjour Jean-Luc,

The assumption is not true I think, there is nothing in a "with" query that 
causes committal - but let's see the example.


# -*- coding: UTF8 -*-
import sqlite3

def displayDBcontents():
 query = "select * from PERSON"
 c.execute(query)
 print
 for row in c: print '%-4s %-10s' % (row[0],row[1])

# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db')
c = conn.cursor()
Right about here should be the start of a transaction somewhere... but I do not see any, where did  you want to roll back to? If you 
are using pysqlite it will probably have an auto-open transaction, but that should be avoided, I'm not sure exactly how since I 
don't use it. Either way, you should try do it manually.

c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
conn.commit()
This Commit is a no-op... no explicit transaction was opened, so every one of the above "c.execute()" commands started AND ended an 
implicit transaction.
And so we continue without an open transaction... or... maybe a transaction was opened in the background, but now you committed it, 
and so it is not open anymore.. or maybe a new one is opened automatically - you need to find out how the wrapper works exactly.

# We check the contents of table PERSON
displayDBcontents()

# We insert Jones and we check the contents of PERSON
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()

# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]

#  We cancel the last insertion (Jones should disappear)
#  and we check the contents of PERSON
conn.rollback()

Roll back to where? there is no open transaction still... every single execute 
was a transaction by itself.

displayDBcontents()

# Surprise: Jones still is in the DB

Yep, but not a surprise at all, very much expected.


c.close()
conn.close()


May I suggest a slightly altered script:

import sqlite3

def displayDBcontents():
query = "select * from PERSON"
c.execute(query)
print
for row in c: print '%-4s %-10s' % (row[0],row[1])

# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db')
c = conn.cursor()
c.execute("BEGIN TRANSACTION;")   # This might wee have a defined automatic 
implemetnation in the object, such as c.beginTransaction() or such.
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
c.execute("COMMIT;")# btw - Why did you refer back to the "Conn" here in 
your version? c.commit should work just fine.

# Now the last open transaction was committed, there is no longer an open 
transaction, so we need to start a new one.
c.execute("BEGIN TRANSACTION")   # Or c.beginTransaction() etc.

# We check the contents of table PERSON
displayDBcontents()

# We insert Jones and we check the contents of PERSON
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()

# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]

#  We cancel the last insertion (Jones should disappear)
#  and we check the contents of PERSON
c.execute("ROLLBACK;")   # or c.rollback() or such...
displayDBcontents()

# NOW if jones is still here, it would be indeed a huge Surprise - but he won't 
be, because SQL cannot lie. :)

I still do not know if the transactions can be used explicit like this in the wrapper, but that is not a SQLite question really. The 
above in a normal SQLite (or any other SQL engine) will work as advertised and no happy random committing could be taking place.




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

Re: [sqlite] Autocommit in "with" query: bug or feature?

[Clemens Ladisch]

Jean-Luc Hainaut wrote:
> Context: Python
>
> The following test program suggest that "with" queries automatically
> execute a commit, as if they were DDL statements.

SQLite itself does not differentiate between DDL and DML statements;
when there are no explicit transactions, every statement gets wrapped
into an automatic transaction.

Python tries to be clever and automatically starts transactions that are
not committed as long as you keep executing DML statements.  However,
it is not possible to detect DML reliably (as you have seen).

> I hope this is a bug, otherwise, this side effect considerably reduces
> the interest of this query.

I consider pysqlite's default transaction handling to be a bug.

To disable it (and handle transactions manually), use something like
this:

  connection.isolation_level = None

or use a different SQLite driver, such as apsw.


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

Re: [sqlite] Can't make SQLite work

[Richard Hipp]

On Sat, Jul 5, 2014 at 3:46 PM, Mike Seabrook Sr 
wrote:

> Hi there,
>
> I am a Masters student at Grand Canyon University. We are to download your
> product. You can download the executable free of charge directly from the
> SQLite project Web site at http://sqlite.org, which also provides
> extensive documentation and tutorials on its usage. Yet only MacBook Pro I
> am using the Mountain Lion OS X 10.8.5. I can't make your program download
> to complete this week's homework assignment to turn in by Wednesday
> morning. Can you please guide me to making this work?
>

All Macs for the past 8 years come with the "sqlite3" program
pre-installed.  Just type "sqlite3" from the command-line prompt.

You'd only need to download a new version if you need some of the more
recent features.




>
> Mike Seabrook
> 602-717-7462.
> ___
> 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] missing doc for FTS feature "^"

[Laurent Dami]

Hi,

I accidentally found the following interesting feature in the Change 
history :


2011-11-01 (3.7.9)

If a search token (on the right-hand side of the MATCH operator) in 
FTS4 begins with "^" then that token must be the first in its field of 
the document. ** Potentially Incompatible Change **



But this is not mentioned in the fts3.html document, so most users 
probably never heard about this feature.




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

[sqlite] Autocommit in "with" query: bug or feature?

[Jean-Luc Hainaut]

Hi,

Context: Python 2.7.6, Windows XP, SQLite v3.8.5.

The following test program suggest that "with" queries automatically execute a 
commit, as if they were DDL statements. I hope this is a bug, otherwise, this 
side effect considerably reduces the interest of this query.

Best regards

Jean-Luc Hainaut


# -*- coding: UTF8 -*-
import sqlite3

def displayDBcontents():
query = "select * from PERSON"
c.execute(query)
print
for row in c: print '%-4s %-10s' % (row[0],row[1])

# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db')
c = conn.cursor()
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
conn.commit()

# We check the contents of table PERSON
displayDBcontents()

# We insert Jones and we check the contents of PERSON
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()

# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]

#  We cancel the last insertion (Jones should disappear)
#  and we check the contents of PERSON
conn.rollback()
displayDBcontents()

# Surprise: Jones still is in the DB

c.close()
conn.close()

Prof. Jean-Luc Hainaut
Faculté d'Informatique
University of Namur
Rue Grandgagnage, 21
B-5000 - Namur (Belgium)   
Phone (direct) : +32 (81) 72 49 96
Phone (secret.): +32 (81) 72 49 64
Fax: +32 (81) 72 49 67
E-mail : jlhain...@info.fundp.ac.be
http://www.info.fundp.ac.be/libd

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

[sqlite] Can't make SQLite work

[Mike Seabrook Sr]

Hi there,

I am a Masters student at Grand Canyon University. We are to download your 
product. You can download the executable free of charge directly from the 
SQLite project Web site at http://sqlite.org, which also provides extensive 
documentation and tutorials on its usage. Yet only MacBook Pro I am using the 
Mountain Lion OS X 10.8.5. I can't make your program download to complete this 
week's homework assignment to turn in by Wednesday morning. Can you please 
guide me to making this work?

Mike Seabrook
602-717-7462.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE query not working

[Linh Duong]

Hi All,

I encounter problem with the below SQL (it does not turn data from my database):
SELECT l.link_id, l.cat, l.bi_direction, l.frm_coord_id, l.to_coord_id, r.name, 
l.start_jtn_elev, l.end_jtn_elev, r.id, r.code, r.prefix FROM Links l, 
Links_Index x, Roads r WHERE l.link_id = x.link_id AND x.link_id MATCH 
convexquad(?1,?2,?3,?4,?5,?6,?7,?8) AND cat <= 8 AND l.road_id = r.id

However, if I remove condition cat <= 8 the query returns data. Below is the 
query:
SELECT l.link_id, l.cat, l.bi_direction, l.frm_coord_id, l.to_coord_id, r.name, 
l.start_jtn_elev, l.end_jtn_elev, r.id, r.code, r.prefix FROM Links l, 
Links_Index x, Roads r WHERE l.link_id = x.link_id AND x.link_id MATCH 
convexquad(?1,?2,?3,?4,?5,?6,?7,?8) AND l.road_id = r.id

In these 2 queries, I implemented the custom R-Tree with function callback 
convexquad. I am using SQLITE 3.8.5

Please help me explain this problem if possible.

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

Re: [sqlite] Multiple reads and writes to a single DB connection from multiple threads

[Dan Kennedy]

On 06/02/2014 08:36 PM, Hick Gunter wrote:

If you compile with SQLITE_THREADSAFE=1 then multiple calls from different 
threads will be serialized by SQLite.

"Serialized" means that only one thread at a time will be allowed to run within SQLite; 
API calls from other threads will block until the currently running thread returns. If your 
application uses virtual tables implemented with native tables, then there may be an issue with 
deadlocks (appl -> sqlite3_step -> virtual table code -> sqlite3_step would cause the 
same thread to recursively enter SQLite).


Both FTS4 and Rtree do this, and both work with SQLite connections in 
serialized mode. SQLite is re-entrant.


Dan.


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

Re: [sqlite] Basic SQLite/EF6 question

[William Drago]

On 7/5/2014 12:38 PM, Joe Mistachkin wrote:

Do you see the System.Data.SQLite option in the Data Connections
dialog within Server Explorer in Visual Studio?


Yes, and I can see my tables, etc. too.


   Which version
and edition of Visual Studio are you using?

Microsoft Visual Studio Professional 2013
Version 12.0.30501.00 Update 2

Microsoft .NET Framework
Version 4.5.50938

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

Re: [sqlite] Multiple reads and writes to a single DB connection from multiple threads

[prashantbkdhas]

Thanks, Keith.

I did not realize even read calls will be serialized with
SQLITE_THREADSAFE=1. Your explanation makes it clear all calls (read/write)
will be serialized with this SQLITE_THREADSAFE=1.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Multiple-reads-and-writes-to-a-single-DB-connection-from-multiple-threads-tp75972p76467.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