[sqlite] \1.8g-@.+o0O8d1n+O0ß..8t."0Śß+e.

ż1 g9O.r1.1 1l^a0/.a^1ßtgJ0L\l 0l. e. .v .:O .ll.z.l0,1.t1.XN. . l.ß. .gT1l1/..8g.9s..J1.9lBf0.0lÁ . ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] beginner question: help required to retrieve "filename" from an open "sqlite3*" handle

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, with "sqlite2_open" the parameter "filename" is used to specify the database location. Q: how I can retrieve this parameter from an open handle? sqlite-version: latest mfg, Andreas Otto -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.15

Re: [sqlite] Trigger for incrementing a column is slow

On 02/04/2011 06:01 AM, Kevin Wojniak wrote: > On Feb 3, 2011, at 2:27 PM, Jim Wilcoxson wrote: > >> On Thu, Feb 3, 2011 at 5:07 PM, Kevin Wojniak wrote: >> >>> >>> On Feb 3, 2011, at 11:41 AM, Petite Abeille wrote: >>> On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:

Re: [sqlite] Trigger for incrementing a column is slow

On Thu, Feb 3, 2011 at 5:07 PM, Kevin Wojniak wrote: > > On Feb 3, 2011, at 11:41 AM, Petite Abeille wrote: > > > On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote: > > > >> The trigger is ran once via sqlite3_exec(); > > > > Hmm... you mean the trigger is run every single

Re: [sqlite] Trigger for incrementing a column is slow

On 3 Feb 2011, at 7:41pm, Petite Abeille wrote: > On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote: > >> The trigger is ran once via sqlite3_exec(); > > Hmm... you mean the trigger is run every single time you perform an insert, > no? > >> Any insight as to why the trigger is significantly

Re: [sqlite] Trigger for incrementing a column is slow

On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote: > The trigger is ran once via sqlite3_exec(); Hmm... you mean the trigger is run every single time you perform an insert, no? > Any insight as to why the trigger is significantly slower? It adds significant overhead for each and every insert.

[sqlite] Announcement: new db access abstraction API w/ sqlite3 support

Hello, all! i've been using sqlite3 since 2004 or 2005, and i can't believe i'm just no subscribing to the list. (sqlite3 is so easy to use, who needs support? ;) i'd like to announce a new C library called cpdo (because it's modeled after PHP's PDO API):

Re: [sqlite] SQLite3 and threading

Am 03.02.2011 15:53, schrieb Pavel Ivanov: > It seems that this explanation as well as all other statements in the > thread you linked are coming from the wrong assumption that SQLite's > handles cannot be used from any thread other than the one created that > handle. The explanation I gave to

Re: [sqlite] Multithreading problem

You're right! I checked the Solaris documentation the correct flag to compile multithread program is -D_REENTRANT. I have reconfigured and remade sqlite libs: ./configure --enable-threadsafe CFLAGS=-D_REENTRANT make I have remade my test file ...and it is working well now! Thanks you very

Re: [sqlite] How do I query for a specific count of items?

On 2/3/2011 12:47 PM, Puneet Kishor wrote: > A... I see now. It is trickier than I thought. How about > > SELECT * > FROM Customers > WHERE Type = 'Apple' AND EntryID NOT IN (SELECT * FROM Customers WHERE > Type != 'Apple'); I assume you meant "NOT IN (SELECT EntryID..." . Naturally, an

[sqlite] Trigger for incrementing a column is slow

I've got a tree structure where whenever I insert a new node, I want its parent entry's number of children to increment. I figured a trigger would be great for this, however it is very slow compared to just a standard UPDATE manually ran after the INSERT. Here is the table: CREATE TABLE root

Re: [sqlite] How do I query for a specific count of items?

On 2/3/2011 12:10 PM, Scott Baker wrote: > CREATE Table Customers ( > EntryID INTEGER PRIMARY KEY, > CustomerID INT, > Type ENUM > ); > > #1) Query for customers who *ONLY* bought apples select CustomerID from Customers group by CustomerID having sum(Type = 'Apple')>0 and

Re: [sqlite] How do I query for a specific count of items?

Igor Tandetnik wrote: > On 2/3/2011 12:26 PM, Puneet Kishor wrote: >> On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote: >>> INSERT INTO Customers VALUES (NULL, 1239, 'Banana'); >> Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs. > > That's how you tell SQLite to

Re: [sqlite] How do I query for a specific count of items?

Only apples SELECT distinct customerid FROM Customers c1 WHERE Type = 'Apple' AND not exists (select 1 from customers c2 where c2.customerid=c1.customerid and not Type = 'Apple') ; Apples and Bananas SELECT distinct customerid FROM Customers c1 WHERE Type = 'Apple' AND exists (select 1 from

Re: [sqlite] How do I query for a specific count of items?

On 2/3/2011 12:26 PM, Puneet Kishor wrote: > On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote: >> INSERT INTO Customers VALUES (NULL, 1239, 'Banana'); > > Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs. That's how you tell SQLite to generate IDs automatically. >

Re: [sqlite] How do I query for a specific count of items?

On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote: > If I have the following (highly simplified) customer table how do I: > > #1) Query for customers who *ONLY* bought apples > #2) Query for customers who bought apples *AND* bananas > #3) Query for customers who bought exactly 2

[sqlite] How do I query for a specific count of items?

If I have the following (highly simplified) customer table how do I: #1) Query for customers who *ONLY* bought apples #2) Query for customers who bought apples *AND* bananas #3) Query for customers who bought exactly 2 apples?

Re: [sqlite] Attach to file in same directory

On 2/3/2011 9:48 AM, BareFeetWare wrote: > I could be using any SQLite utility, whether the command line, SQLite > Manager, Froq etc. I happen to be using my own app, developed for the > iPad/iPhone. In any of these environments, I open my main SQLite data > file then want to run an SQL

Re: [sqlite] Attach to file in same directory

>> On 03-02-11 16:18, BareFeetWare wrote: >> >> What SQLite or C library call could I put before that to set the current >> directory, that the sqlite3_prepare_v2 function would observe when >> processing the attach statement? > On 04/02/2011, at 2:25 AM, Luuk wrote: > > i'm not a

Re: [sqlite] Multithreading problem

On 02/03/2011 11:00 PM, Tiberio, Sylvain wrote: > Here the modification in sqlite3.c: > > if( unlink(zPath)==(-1)&& errno!=ENOENT ){ > perror(zPath); > return SQLITE_IOERR_DELETE; > } > > And here is the result: > > /home/tiberio/perso/source/sql/bug/try.db-wal: No such file

Re: [sqlite] Multithreading problem

Michael, The database try.db is created in the directory where I test this issue. In this directory I edit the source file, compile it and execute the test. - If I use SQL 3.6.22 it runs well. - I have the save issue if I put the datafile in: * Ram disk (/tmp/) * local disk * my NFS home disk -

Re: [sqlite] Multithreading problem

Here the modification in sqlite3.c: if( unlink(zPath)==(-1) && errno!=ENOENT ){ perror(zPath); return SQLITE_IOERR_DELETE; } And here is the result: /home/tiberio/perso/source/sql/bug/try.db-wal: No such file or directory Sylvain -Original Message- From:

Re: [sqlite] Multithreading problem

On 02/03/2011 10:22 PM, Tiberio, Sylvain wrote: > > Dan, > > Thanks for your attention. > > sqlite3_extended_errcode() return 0xA0A that means SQLITE_IOERR_DELETE. Earlier versions of SQLite ignored the return code of unlink(). That is probably why you're not seeing a problem with 3.6.22. Search

Re: [sqlite] Multithreading problem

Can you "su" as the owner you are are expecting and see if you can delete it? Maybe the directory permissions are messed up? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Attach to file in same directory

>> On 3 Feb 2011, at 2:59pm, BareFeetWare wrote: >> >> But if a I have an arbitrary SQL script/procedure to perform, that starts >> with an attach statement, I don't have creation control over the path >> specified in the script. > On 04/02/2011, at 2:18 AM, Simon Slavin wrote: > > If you

Re: [sqlite] Attach to file in same directory

On 03-02-11 16:18, BareFeetWare wrote: > What SQLite or C library call could I put before that to set the current > directory, that the sqlite3_prepare_v2 function would observe when processing > the attach statement? i'm not a C-programmer but:

Re: [sqlite] Question about database design

On Feb 3, 2011, at 3:38 AM, Simon Slavin wrote: > SQLite creates some indexes the programmer doesn't specifically ask for: on > the rowid, on the primary key, and on any column declared as UNIQUE. Of > course, in a particular table all three of these might actually be

Re: [sqlite] Multithreading problem

Dan, Thanks for your attention. sqlite3_extended_errcode() return 0xA0A that means SQLITE_IOERR_DELETE. Here are others information: - My problem occurs in Sparc/Solaris 10 system. - After my program error, the file try.db exists and has the correct right -rw-r--r--, correct owner/group and a

Re: [sqlite] Attach to file in same directory

On 3 Feb 2011, at 2:59pm, BareFeetWare wrote: > But if a I have an arbitrary SQL script/procedure to perform, that starts > with an attach statement, I don't have creation control over the path > specified in the script. If you were able to open the original database without specifying a

Re: [sqlite] Attach to file in same directory

> On 04/02/2011, at 2:08 AM, Pavel Ivanov wrote: > > What's wrong with the following suggestion to you? > >>> Just start sqlite3 in such a way that the directory where your database >>> files reside is the current one. Because I am not using "sqlite3", ie the command line utility. > You can

[sqlite] Yet another question - this time about using two tables

I have a table called multiturnTable which records games between two players, so has two fields "player1" and "player2". Currently when, for instance, trying to find all games involving a specific player, I search based on player1=x OR player2=x. I'm fairly sure this is anti-good db design.

Re: [sqlite] Attach to file in same directory

>> Instead use operating system commands to retrieve the full path to the first >> file, then construct a full path to the second file. > > But if a I have an arbitrary SQL script/procedure to perform, that starts > with an attach statement, I don't have creation control over the path >

Re: [sqlite] Attach to file in same directory

>> On 3 Feb 2011, at 1:03am, BareFeetWare wrote: >> >> How can I attach to a local file in the same directory, without specifying >> the full absolute path? > On 03/02/2011, at 12:17 PM, Simon Slavin wrote: > > No easy way. Argh. That's kind of mental, that SQLite, a file based database

Re: [sqlite] SQLite3 and threading

Thanks Pavel, I consider my case closed though any thought is welcomed! On 02/03/2011 05:53 PM, Pavel Ivanov wrote: > It seems that this explanation as well as all other statements in the > thread you linked are coming from the wrong assumption that SQLite's > handles cannot be used from any

Re: [sqlite] SQLite3 and threading

It seems that this explanation as well as all other statements in the thread you linked are coming from the wrong assumption that SQLite's handles cannot be used from any thread other than the one created that handle. Although this was true in some earlier versions of SQLite it's not true in the

Re: [sqlite] Attach to file in same directory

>> On 2/2/2011 8:03 PM, BareFeetWare wrote: >> I use the attach command to attach another SQLite database file that resides >> in the same directory as my main file. I tried: >> >> attach 'Import.sqlitedb'; >> >> But it fails to find the file. If I specify the full path: >> >> attach

Re: [sqlite] Multithreading problem

On 02/02/2011 09:31 PM, Tiberio, Sylvain wrote: > Hi! > > > > I have a problem when I try to create a new database in a thread and try > to add a table on it. > > > > The following C code (see in the end of this e-mail) produces: The program is working Ok with 3.7.5 here. After the IO error in

[sqlite] Multithreading problem

Hi! I have a problem when I try to create a new database in a thread and try to add a table on it. The following C code (see in the end of this e-mail) produces: in SQLite 3.7.5: Disk I/O error (same problem with 3.7.4) SQLite Treadsafe. Yes (1). SQLite Lib version... 3.7.5.

Re: [sqlite] SQLite3 and threading

From Urlich's explanation (I respect him as he is in the 'game of programming ' many years ahead me) is this, I quote: -- This decreases the chance of failure but doesn't eliminate it, since still SQLite handles are passed around. As soon as the database thread

Re: [sqlite] SQLite3 and threading

What problems did you meet when you tried to do what you want? Pavel On Thu, Feb 3, 2011 at 4:39 AM, Stefano Mtangoo wrote: > Hi, > I use SQLite3 with wxSQLite3 wrapper and all is fine until I wanted to > shift the DB thing into the secondary thread. > What I want to do

Re: [sqlite] Question about database design

On 3 Feb 2011, at 10:43am, Mihai Militaru wrote: > Nicolas Williams wrote: > >>> Any idea why pg does ok on these queries without the extra index - >>> Maybe they're created by default? SQLIte doesn't create any indexes >>> automatically on primary key fields or

Re: [sqlite] WAL for production use

I did see some persistent database locks in WAL mode in SQLite 3.7.4 and previous versions (I don't test 3.7.5 yet ) and all queries are failed with message about database locked by write query. So we need vacuum database, try change to "delete" mode and vacuum again... In "delete" mode these

Re: [sqlite] Question about database design

On Wed, 2 Feb 2011 18:59:48 -0600 Nicolas Williams wrote: > > Any idea why pg does ok on these queries without the extra index - > > Maybe they're created by default? SQLIte doesn't create any indexes > > automatically on primary key fields or anything else,

[sqlite] SQLite3 and threading

Hi, I use SQLite3 with wxSQLite3 wrapper and all is fine until I wanted to shift the DB thing into the secondary thread. What I want to do is send string containing query to secondary thread and the secondary thread is supposed to query db and post back the resultset. Urlich had doubts about