[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 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org/ iQEcBAEBAgAGBQJNS6z9AAoJEGTcPijNG3/AqHcH/AyN5F9s0twi1ffozHdDG3IV YYvO5FW+jiyOB24KnN+c5j7Ffqc2jGFCbD2PMC8hIy8KP/ZZfCS+qsEKL54x82w1 5bse7X4c121cG4Wi2PCrLD0Mg+u/ZzavZXJEpYPdJsVtIhRUvFKLTtvkKKi8Ngkd iN5ErCZDrVjZxr4dU/pijT7GSRNYW7bTSi8d/XzbRzoMV5NF8QzHFmJmQsZCY1yi xyZ39fnOh+Fi2C659fldzmPd1Nzc3GSmE/XTTLX3qqrXbCQPinIBmmJvbImAVz1B I72HxkP49Q+LwcdLoIIASlrBOaSH53w6rQIDoscoSM7czFtDu59VXv8Ptpwqh/E= =ARbF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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: > The trigger is ran once via sqlite3_exec(); Hmm... you mean the trigger is run every single time you perform an >>> insert, no? >>> >>> Yes. I should say the trigger is created once via sqlite3_exec(). >>> > Any insight as to why the trigger is significantly slower? >>> >> >> Perhaps SQLite is having to recompile the trigger SQL on every use, whereas >> your update stmt is prepared. I tried triggers once, and they were slow for >> me too. >> >> Jim >> -- >> HashBackup: easy onsite and offsite Unix backup >> http://www.hashbackup.com > > If that were the case that'd seem like a major design flaw. > > > I created a timing profile using the trigger and without. On the version > without, most of the time is spent all in sqlite, as expected. The one with > triggers had a ton more time spent in the various kernel file system > functions (hfs, I'm on Mac OS 10.6.6). So it seems like the triggers are > creating significant more file access. I have all the inserts surrounded by > BEGIN/END TRANSACTION, so I don't see why this would be doing any more > necessary work. Do you have a test program that we can use to reproduce this phenomenon? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger for incrementing a column is slow
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: >>> The trigger is ran once via sqlite3_exec(); >>> >>> Hmm... you mean the trigger is run every single time you perform an >> insert, no? >> >> Yes. I should say the trigger is created once via sqlite3_exec(). >> Any insight as to why the trigger is significantly slower? >> > > Perhaps SQLite is having to recompile the trigger SQL on every use, whereas > your update stmt is prepared. I tried triggers once, and they were slow for > me too. > > Jim > -- > HashBackup: easy onsite and offsite Unix backup > http://www.hashbackup.com If that were the case that'd seem like a major design flaw. I created a timing profile using the trigger and without. On the version without, most of the time is spent all in sqlite, as expected. The one with triggers had a ton more time spent in the various kernel file system functions (hfs, I'm on Mac OS 10.6.6). So it seems like the triggers are creating significant more file access. I have all the inserts surrounded by BEGIN/END TRANSACTION, so I don't see why this would be doing any more necessary work. Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 time you perform an > insert, no? > > Yes. I should say the trigger is created once via sqlite3_exec(). > > >> Any insight as to why the trigger is significantly slower? > Perhaps SQLite is having to recompile the trigger SQL on every use, whereas your update stmt is prepared. I tried triggers once, and they were slow for me too. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger for incrementing a column is slow
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 time you perform an insert, > no? Yes. I should say the trigger is created once via sqlite3_exec(). >> Any insight as to why the trigger is significantly slower? > > It adds significant overhead for each and every insert. But it seems way more than what's necessary since the manual UPDATE is ~6x faster. > Do you really need to store that value? As oppose to have it computed? After > all, you should always be able to query for it, no? The database is written once, and then read multiple times, so writes don't need to be as efficient as reads. Every time I pull out a node, if the node can contain child nodes I need to also obtain the number of children, so my initial thinking is it'd be faster to index it instead of querying each time. > Alternatively, update it in bulk, e.g: update root set num_children = ( > select count( * ) from root as parent where parent.parent_rowid = root.rowid > ) where rowid in ( select distinct parent_rowid from root ) or something This definitely seems the better way to go, once all the inserts are complete. My SQL isn't very good so thanks for this! On Feb 3, 2011, at 1:26 PM, Simon Slavin wrote: > On 3 Feb 2011, at 7:41pm, Petite Abeille wrote: > >>> Any insight as to why the trigger is significantly slower? >> >> It adds significant overhead for each and every insert. > > But his alternative to doing INSERT with a TRIGGER is to do an INSERT and > then a manual UPDATE. Are triggers really so inefficient that it's that much > faster to do it manually ? > > Simon. Exactly. If anything the trigger should be faster, I would imagine. Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 slower? > > It adds significant overhead for each and every insert. But his alternative to doing INSERT with a TRIGGER is to do an INSERT and then a manual UPDATE. Are triggers really so inefficient that it's that much faster to do it manually ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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. > I hope I'm missing something basic. Yes! Don't use triggers :) Do you really need to store that value? As oppose to have it computed? After all, you should always be able to query for it, no? Alternatively, update it in bulk, e.g: update root set num_children = ( select count( * ) from root as parent where parent.parent_rowid = root.rowid ) where rowid in ( select distinct parent_rowid from root ) or something ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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): http://fossil.wanderinghorse.net/repos/cpdo/ The sqlite3-based driver is the "reference implementation" for the API, and it supports all of the features DB-using apps "normally need" (not including highly driver-dependent operations like scrollable cursors and random access to parts of blobs). We've also got a MySQL driver, since a DB abstraction API supporting only one back-end is pretty senseless. If there are any other C hackers left out there aside from DRH himself, i would love to hear your input/suggestions/comments/etc. (Off-list please - they're not topical here.) Happy Hacking! -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 Stefano is *not* based on the assumption that SQlite handles can't be passed from one thread to another. But certainly passing them around *can* lead to problems. > Although this was true in some earlier versions of SQLite it's > not true in the current version. So if SQLite is compiled with > THREADSAFE=1 (as mentioned in that thread) then you can do with it > whatever you want. Just beware of possible data races and potentially > uncommitted transactions because of some open statement handles. And > if as you say there's no simultaneous access to the database from > different threads then there's no difference in your usage pattern > from single-threaded one. I cite from http://sqlite.org/faq.html#q6: "The restriction on moving database connections across threads was relaxed somewhat in version 3.3.1. With that and subsequent versions, it is safe to move a connection handle across threads as long as the connection is not holding any fcntl() locks. You can safely assume that no locks are being held if no transaction is pending and all statements have been finalized." The problem is that Stefano wants to pass a result set from one thread to another. The result set has an associated SQLite statement handle which is inherently *not* finalized. And that might cause trouble. If access to the database handle is serialized as Stefano claims passing the statement handle between threads shouldn't cause problems, true, but in that case I don't see the advantages of having a separate thread over using a global database access instance. Regards, Ulrich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 much!!! Another question: Why ./configure doesn't set itself this flag when I use --enable-threadsafe in a Solaris system? Regards, Sylvain -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, February 03, 2011 5:15 PM To: sqlite-users@sqlite.org Subject: 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 or directory That error message suggests that errno should be set to ENOENT. And when you used the main thread to do the work it seems like it was, since you didn't get the error then. Do you have to do something special in Solaris to get errno to work in multi-threaded apps? Something like -D_POSIX_C_SOURCE=199506L or -mt perhaps? Is SQLite being compiled with the same thread-related switches as the rest of the app? Dan. > > Sylvain > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: Thursday, February 03, 2011 4:51 PM > To: sqlite-users@sqlite.org > Subject: 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 the code for a function called "unixDelete". In sqlite3.c if > you are using the amalgamation, os_unix.c otherwise. Near the top > of that function is this: > > if( unlink(zPath)==(-1)&& errno!=ENOENT ){ > return SQLITE_IOERR_DELETE; > } > > That's where your error is coming from. If you can put a call to > perror() or print the value of errno just before SQLITE_IOERR_DELETE, > it might show why that call to unlink() is failing. Printing out > "zPath" as well is probably a good idea. > > Dan. > > > > >> >> 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 null size. >> >> Regards, >> >> Sylvain >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy >> Sent: Thursday, February 03, 2011 3:16 PM >> To: sqlite-users@sqlite.org >> Subject: 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 sqlite3_exec(), what value does >> sqlite3_extended_errcode() return? >> >> Dan. >> >> >> ___ >> 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 > ___ 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] 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 EntryID for an entry having Type='Apple' won't appear in the list of entries having Type != 'Apple'. The second condition is always true whenever the first is. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 (rowid INTEGER PRIMARY KEY, parent_rowid INTEGER, name TEXT, num_children INTEGER); and trigger: CREATE TRIGGER update_num_children AFTER INSERT ON root BEGIN UPDATE root SET num_children = num_children + 1 WHERE rowid = NEW.parent_rowid; END; The trigger is ran once via sqlite3_exec(); I am testing with inserting 200,000 entries. With the trigger enabled, it takes about 15.5 seconds. When I disable the trigger, and run the UPDATE via a cached statement, it takes about 2.5 seconds: UPDATE root SET num_children = num_children + 1 WHERE rowid = ?; Any insight as to why the trigger is significantly slower? I hope I'm missing something basic. Thanks, Kevin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 sum(Type != 'Apple')=0; > #2) Query for customers who bought apples *AND* bananas select CustomerID from Customers group by CustomerID having sum(Type = 'Apple')>0 and sum(Type = 'Banana')>0; > #3) Query for customers who bought exactly 2 apples? select CustomerID from Customers group by CustomerID having sum(Type = 'Apple') = 2; -- or select CustomerID from Customers where Type = 'Apple' group by CustomerID having count(*) = 2; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 generate IDs automatically. > Neat. I didn't know that. I would simply *not* insert anything for that column in order to get the automagic PK like INSERT INTO Customers (CustomerID, Type) VALUES (...) >> Your CustomerID seems like it should be unique, yet you have identical rows >> inserted. > > It's not declared unique, why do you think it should be? > Now that I know that NULL actually triggers off an automatic PK, that makes sense. However, while there might be deeper mystery to the OP, having identical rows other than the PK makes no sense to me. For what its worth, it was an "editorial" comment. >> For example, what is the difference between the first and the second row? > > EntryID. > Right. Now I know. >>> #1) Query for customers who *ONLY* bought apples >> SELECT * >> FROM Customers >> WHERE Type = 'Apple'; > > That would also report customers that bought something else besides apples. > 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'); >>> #2) Query for customers who bought apples *AND* bananas >> SELECT * >> FROM Customers >> WHERE Type = 'Apple' OR Type = 'Banana'; > > That would report customers that only bought apples, as well as those > that only bought bananas. I am tired. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 customers c2 where c2.customerid=c1.customerid and Type = 'Banana') ; On 2/3/2011 9:40 AM, 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 generate IDs automatically. > >> Your CustomerID seems like it should be unique, yet you have identical rows >> inserted. > It's not declared unique, why do you think it should be? > >> For example, what is the difference between the first and the second row? > EntryID. > >>> #1) Query for customers who *ONLY* bought apples >> SELECT * >> FROM Customers >> WHERE Type = 'Apple'; > That would also report customers that bought something else besides apples. > >>> #2) Query for customers who bought apples *AND* bananas >> SELECT * >> FROM Customers >> WHERE Type = 'Apple' OR Type = 'Banana'; > That would report customers that only bought apples, as well as those > that only bought bananas. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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. > Your CustomerID seems like it should be unique, yet you have identical rows > inserted. It's not declared unique, why do you think it should be? > For example, what is the difference between the first and the second row? EntryID. >> #1) Query for customers who *ONLY* bought apples > > SELECT * > FROM Customers > WHERE Type = 'Apple'; That would also report customers that bought something else besides apples. >> #2) Query for customers who bought apples *AND* bananas > SELECT * > FROM Customers > WHERE Type = 'Apple' OR Type = 'Banana'; That would report customers that only bought apples, as well as those that only bought bananas. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 apples? > > -- > > DROP TABLE IF EXISTS Customers; > CREATE Table Customers ( > EntryID INTEGER PRIMARY KEY, > CustomerID INT, > Type ENUM > ); > > INSERT INTO Customers VALUES (NULL, 1234, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1234, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1235, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1236, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1237, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1237, 'Banana'); > INSERT INTO Customers VALUES (NULL, 1237, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1238, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1238, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1239, 'Apple'); > INSERT INTO Customers VALUES (NULL, 1239, 'Banana'); Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs. Your CustomerID seems like it should be unique, yet you have identical rows inserted. For example, what is the difference between the first and the second row? That said, > #1) Query for customers who *ONLY* bought apples SELECT * FROM Customers WHERE Type = 'Apple'; > > #2) Query for customers who bought apples *AND* bananas SELECT * FROM Customers WHERE Type = 'Apple' OR Type = 'Banana'; > > #3) Query for customers who bought exactly 2 apples? SELECT CustomerID FROM Customers WHERE Type = 'Apple' GROUP BY CustomerID HAVING Count(CustomerID) = 2; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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? -- DROP TABLE IF EXISTS Customers; CREATE Table Customers ( EntryID INTEGER PRIMARY KEY, CustomerID INT, Type ENUM ); INSERT INTO Customers VALUES (NULL, 1234, 'Banana'); INSERT INTO Customers VALUES (NULL, 1234, 'Banana'); INSERT INTO Customers VALUES (NULL, 1235, 'Apple'); INSERT INTO Customers VALUES (NULL, 1236, 'Banana'); INSERT INTO Customers VALUES (NULL, 1237, 'Banana'); INSERT INTO Customers VALUES (NULL, 1237, 'Banana'); INSERT INTO Customers VALUES (NULL, 1237, 'Apple'); INSERT INTO Customers VALUES (NULL, 1238, 'Apple'); INSERT INTO Customers VALUES (NULL, 1238, 'Apple'); INSERT INTO Customers VALUES (NULL, 1239, 'Apple'); INSERT INTO Customers VALUES (NULL, 1239, 'Banana'); -- Scott Baker - Canby Telcom System Administrator - RHCE - 503.266.8253 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 script/procedure which starts with an > attach command. Somehow, you managed to build the path to the main file. Set the process' working directory to the same path (on Posix systems, see chdir). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 C-programmer Me either, well, just enough to use the SQLite libraries. The rest of my code is in Objective-C. > but: > http://www.delorie.com/gnu/docs/glibc/libc_268.html My question was how can I set the working directory in such a way that the SQL attach command will look for files in that directory, if a path isn't specified. I have just experimented with the C function chdir() and the Objective C method changeCurrentDirectoryPath. Both seem to be observed by the SQLite running of attach commands. So this solves the problem. Thanks Pavel, Luuk, Igor and Simon for steering me in the right direction and for taking the time to reply. I appreciate it. 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] 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 or directory That error message suggests that errno should be set to ENOENT. And when you used the main thread to do the work it seems like it was, since you didn't get the error then. Do you have to do something special in Solaris to get errno to work in multi-threaded apps? Something like -D_POSIX_C_SOURCE=199506L or -mt perhaps? Is SQLite being compiled with the same thread-related switches as the rest of the app? Dan. > > Sylvain > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: Thursday, February 03, 2011 4:51 PM > To: sqlite-users@sqlite.org > Subject: 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 the code for a function called "unixDelete". In sqlite3.c if > you are using the amalgamation, os_unix.c otherwise. Near the top > of that function is this: > > if( unlink(zPath)==(-1)&& errno!=ENOENT ){ > return SQLITE_IOERR_DELETE; > } > > That's where your error is coming from. If you can put a call to > perror() or print the value of errno just before SQLITE_IOERR_DELETE, > it might show why that call to unlink() is failing. Printing out > "zPath" as well is probably a good idea. > > Dan. > > > > >> >> 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 null size. >> >> Regards, >> >> Sylvain >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy >> Sent: Thursday, February 03, 2011 3:16 PM >> To: sqlite-users@sqlite.org >> Subject: 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 sqlite3_exec(), what value does >> sqlite3_extended_errcode() return? >> >> Dan. >> >> >> ___ >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 - If I use ":memory:" it works. - If I run the test() function directly in main() it works. - If I create the try.db with SQL 3.6.22 and if I run the 3.7.5 with the existing file, it works (I have added my query "IF NOT EXISTS" in the table creation query) Sylvain -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Thursday, February 03, 2011 4:34 PM To: General Discussion of SQLite Database Subject: 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, February 03, 2011 4:51 PM To: sqlite-users@sqlite.org Subject: 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 the code for a function called "unixDelete". In sqlite3.c if you are using the amalgamation, os_unix.c otherwise. Near the top of that function is this: if( unlink(zPath)==(-1) && errno!=ENOENT ){ return SQLITE_IOERR_DELETE; } That's where your error is coming from. If you can put a call to perror() or print the value of errno just before SQLITE_IOERR_DELETE, it might show why that call to unlink() is failing. Printing out "zPath" as well is probably a good idea. Dan. > > 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 null size. > > Regards, > > Sylvain > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: Thursday, February 03, 2011 3:16 PM > To: sqlite-users@sqlite.org > Subject: 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 sqlite3_exec(), what value does > sqlite3_extended_errcode() return? > > Dan. > > > ___ > 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] 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 the code for a function called "unixDelete". In sqlite3.c if you are using the amalgamation, os_unix.c otherwise. Near the top of that function is this: if( unlink(zPath)==(-1) && errno!=ENOENT ){ return SQLITE_IOERR_DELETE; } That's where your error is coming from. If you can put a call to perror() or print the value of errno just before SQLITE_IOERR_DELETE, it might show why that call to unlink() is failing. Printing out "zPath" as well is probably a good idea. Dan. > > 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 null size. > > Regards, > > Sylvain > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: Thursday, February 03, 2011 3:16 PM > To: sqlite-users@sqlite.org > Subject: 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 sqlite3_exec(), what value does > sqlite3_extended_errcode() return? > > Dan. > > > ___ > 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] 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 [sqlite-users-boun...@sqlite.org] on behalf of Tiberio, Sylvain [sylvain.tibe...@cassidian.com] Sent: Thursday, February 03, 2011 9:22 AM To: General Discussion of SQLite Database Subject: EXT :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 null size. Regards, Sylvain -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, February 03, 2011 3:16 PM To: sqlite-users@sqlite.org Subject: 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 sqlite3_exec(), what value does sqlite3_extended_errcode() return? Dan. ___ 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] 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 were able to open the original database without specifying a path, > then you can ATTACH to it another database from the same folder without a > path. If you had to specify a path to open the original database, then use > the same path when you open the attached database. It goes something like this: 1. User opens database file. App therefore knows location/path of the chosen file. But this path may be different next time (eg on different device). 2. User runs an SQL script/procedure containing an attach statement. The attach statement specifies a file, but no path, since the path is just the same directory containing the main file. In other words, the relative path doesn't change between runs, but the absolute path will. 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] 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: http://www.delorie.com/gnu/docs/glibc/libc_268.html less than 5 minutes of Google, and some 'general' programming experiance ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 the same > column, so it might need just one. But every table has at least one index. The ROWID of a table doesn't have an "index," so it isn't really fair to say that every table has at least one index. At least, not when using the word "index" to mean a secondary data structure in the database that must be updated and maintained in conjunction with the table data structure. Both tables and indexes are stored within the SQLite file as a tree structure. The raw table data is stored in a tree structure that happens to be sorted by ROWID. For every index, SQLite creates an addition tree structure that is sorted by whatever columns make up the index. The structures are extremely similar, since they serve the same purpose. Both explicit indexes (CREATE INDEX...), and implicit indexes (non-integer PRIMARY KEYs, UNIQUE constraints) create additional tree structures in the database. However, if no PK is defined, or if the PK is defined as an INTEGER PRIMARY KEY, then no external index is created. The only data structure is the table itself. This is also why INTEGER PRIMARY KEYs are so desirable over other PK types. Not only do they reduce database size, by using the inherent structure of the main table data store as their "index," they also tend to be about twice as fast at doing explicit row lookups (vs a traditional index), as only one tree structure needs to be searched to retrieve any column. That speed difference means the break-even point for indexed lookup vs table scan for a set of INTEGER PK values is closer to 15% to 20% of the rows, vs the traditional 5% to 10% rule of thumb that applies to standard indexes. -j > 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
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 null size. Regards, Sylvain -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, February 03, 2011 3:16 PM To: sqlite-users@sqlite.org Subject: 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 sqlite3_exec(), what value does sqlite3_extended_errcode() return? Dan. ___ 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] 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 path, then you can ATTACH to it another database from the same folder without a path. If you had to specify a path to open the original database, then use the same path when you open the attached database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 read it as: change your app so that before executing the script it > sets current directory to the one with the original database. I am programming in Cocoa Touch and Objective C for my app, and using C for the standard SQLite calls. I use the SQLite C function: rc = sqlite3_prepare_v2(_db, cSQL, -1, &sqlStmt, &remainderCSQL); where cSQL is the C string containing the SQL attach statement. 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? 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] 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. I am considering adding a gamesInvolving table which would be simply: TABLE gamesInvolving (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT COLLATE NOCASE, multiturnId INT, won INT) I would then replace my player1= OR player2= queries with some kind of join based on a select from gamesInvolving. My two questions are simply: 1. Would you expect this to be significantly faster? 2. Is there a better way? Thanks, as always. Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 > specified in the script. 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. You can read it as: change your app so that before executing the script it sets current directory to the one with the original database. > For the moment, I've actually had to use a regex to change the SQL script to > insert a path if it's missing, but that is unscrambling the egg and will only > help my app, not if the database files and script are opened in another app. If you want to execute such script with attach from different apps and those apps don't know about your attach command and can't set current directory accordingly that can only mean that you try to hack these apps and perform some illegal operation in them. Don't do that. Pavel On Thu, Feb 3, 2011 at 9:59 AM, BareFeetWare wrote: >>> 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 system, can't > in SQL either tell you the path to the main database (eg via a function) or > let you open a secondary file in the 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 > specified in the script. > > For the moment, I've actually had to use a regex to change the SQL script to > insert a path if it's missing, but that is unscrambling the egg and will only > help my app, not if the database files and script are opened in another app. > > I am replacing occurrences of: > attach\s+'([^/].*?)' > with: > attach '/$1' > > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 system, can't in SQL either tell you the path to the main database (eg via a function) or let you open a secondary file in the 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 specified in the script. For the moment, I've actually had to use a regex to change the SQL script to insert a path if it's missing, but that is unscrambling the egg and will only help my app, not if the database files and script are opened in another app. I am replacing occurrences of: attach\s+'([^/].*?)' with: attach '/$1' 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] 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 thread other than the one created that > handle. Although this was true in some earlier versions of SQLite it's > not true in the current version. So if SQLite is compiled with > THREADSAFE=1 (as mentioned in that thread) then you can do with it > whatever you want. Just beware of possible data races and potentially > uncommitted transactions because of some open statement handles. And > if as you say there's no simultaneous access to the database from > different threads then there's no difference in your usage pattern > from single-threaded one. -- _ The purpose of man is to know his Maker Be known by his Maker And make his Maker known So that others may know his Maker as their Maker(Emeal Zwayne) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 current version. So if SQLite is compiled with THREADSAFE=1 (as mentioned in that thread) then you can do with it whatever you want. Just beware of possible data races and potentially uncommitted transactions because of some open statement handles. And if as you say there's no simultaneous access to the database from different threads then there's no difference in your usage pattern from single-threaded one. Pavel On Thu, Feb 3, 2011 at 7:29 AM, Stefano Mtangoo wrote: > 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 > accesses the database independently in parallel to the thread consuming > the result set this could result in problems. > --- > > So what advice do you give me in such need that are thread safe? Any > other approach? > > > On 02/03/2011 03:09 PM, Pavel Ivanov wrote: >> 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 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 the approach and full posts is here: >>> http://forum.wxwidgets.org/viewtopic.php?t=29991 >>> >>> >>> How can I do that with SQLite3? No access to database at the same time. >>> With thanks, >>> Stefano >>> >>> -- >>> _ >>> The purpose of man is to know his Maker Be known by his Maker And make his >>> Maker known So that others may know his Maker as their Maker(Emeal Zwayne) >>> >>> ___ >>> 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 > > > -- > _ > The purpose of man is to know his Maker Be known by his Maker And make his > Maker known So that others may know his Maker as their Maker(Emeal Zwayne) > > ___ > 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] 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 '/Users/tom/Documents/Work/Databases/Import.sqlitedb'; >> >> Then it works. > On 03/02/2011, at 12:15 PM, Igor Tandetnik wrote: > > Just as with any command line utility, relative paths are resolved relative > to the current working directory. Just start sqlite3 in such a way that the > directory where your database files reside is the current one. Read the fine > manual for your shell of choice. I'm not using the command line utility, though my question would equally apply there. >> How can I attach to a local file in the same directory, without specifying >> the full absolute path? > > How did you manage to open the main file in the first place? Specify the path > to the second file in the same manner. 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 script/procedure which starts with an attach command. I don't want to and usually can't edit the script each time to insert a different path in the attach file name. 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] 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 sqlite3_exec(), what value does sqlite3_extended_errcode() return? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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. SQLite Lib vernumber. 3007005. Open/Create try.db... Exec 'CREATE TABLE test (id INTEGER PRIMARY KEY, name TEST)'... !!! ERROR:Can't exec: disk I/O error(10) Close... In SQlite 3.6.22: No problem SQLite Treadsafe. Yes (1). SQLite Lib version... 3.6.22. SQLite Lib vernumber. 3006022. Open/Create try.db... Exec 'CREATE TABLE test (id INTEGER PRIMARY KEY, name TEST)'... Close... If I open the database in the main function (moving openDatabase from test() to main()), there is no problem with both SQLite version. Any ideas? Regards, Sylvain == C Source Code = #include #include #include #include static sqlite3 *db_p=NULL; static const int openMode=SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE; static char *errmsg_p=NULL; static void openDatabase(const char *filename_p) { printf("Open/Create %s...\n",filename_p); int rc = sqlite3_open_v2(filename_p, &db_p,openMode,NULL); if ( rc ) printf("Can't open database %s: %s(%d)\n", filename_p, sqlite3_errmsg(db_p), rc); } static void closeDatabase(void) { printf("Close...\n"); int rc = sqlite3_close(db_p); if ( rc ) printf("Can't close database: %s(%d)\n", sqlite3_errmsg(db_p), rc); } static void exec(const char* query_p) { printf("Exec '%s'...\n",query_p); int rc = sqlite3_exec(db_p,query_p,NULL,NULL,&errmsg_p); if ( rc ) printf("!!! ERROR:Can't exec: %s(%d)\n", errmsg_p, rc); } static void* test(void *arg_p) { openDatabase((char*)arg_p); exec("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEST)"); closeDatabase(); return NULL; } static void getConfig(void) { printf("SQLite Treadsafe. %s (%d).\n",sqlite3_threadsafe()==0?"No":"Yes",sqlite3_threadsafe()); printf("SQLite Lib version... %s.\n",sqlite3_libversion()); printf("SQLite Lib vernumber. %d.\n",sqlite3_libversion_number()); } int main(int agrc, char *argv[]) { getConfig(); pthread_t id; int rc=pthread_create(&id,NULL,test,(void*)"try.db"); if ( rc ) perror("pthread_create"); pthread_join(id,NULL); } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 accesses the database independently in parallel to the thread consuming the result set this could result in problems. --- So what advice do you give me in such need that are thread safe? Any other approach? On 02/03/2011 03:09 PM, Pavel Ivanov wrote: > 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 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 the approach and full posts is here: >> http://forum.wxwidgets.org/viewtopic.php?t=29991 >> >> >> How can I do that with SQLite3? No access to database at the same time. >> With thanks, >> Stefano >> >> -- >> _ >> The purpose of man is to know his Maker Be known by his Maker And make his >> Maker known So that others may know his Maker as their Maker(Emeal Zwayne) >> >> ___ >> 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 -- _ The purpose of man is to know his Maker Be known by his Maker And make his Maker known So that others may know his Maker as their Maker(Emeal Zwayne) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 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 the approach and full posts is here: > http://forum.wxwidgets.org/viewtopic.php?t=29991 > > > How can I do that with SQLite3? No access to database at the same time. > With thanks, > Stefano > > -- > _ > The purpose of man is to know his Maker Be known by his Maker And make his > Maker known So that others may know his Maker as their Maker(Emeal Zwayne) > > ___ > 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] 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 anything else, correct? >> >> No, it doesn't. Use EXPLAIN QUERY PLAN to see what SQLite3 is doing. > > Hmm SqLite does create persistent indices on UNIQUE - and consequently > PRIMARY - keys, doesn't it? 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 the same column, so it might need just one. But every table has at least one index. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 applications work fine a lot of time. But I can't write tests for reproducing the problem. My hosts are using 32bit and 64bit debian linux (squeeze) with ext3 filesystem. So WAL may be dangerous for some projects now. 2011/2/3 Dustin Sallings > > On Feb 2, 2011, at 8:14, Duquette, William H (318K) wrote: > > > In SQLite 3.7.4/3.7.5, does WAL seem to be stable enough for production > use? > > > I'm using it very, very heavily right now. > > -- > dustin sallings > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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, correct? > > No, it doesn't. Use EXPLAIN QUERY PLAN to see what SQLite3 is doing. Hmm SqLite does create persistent indices on UNIQUE - and consequently PRIMARY - keys, doesn't it? -- Mihai Militaru ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 the approach and full posts is here: http://forum.wxwidgets.org/viewtopic.php?t=29991 How can I do that with SQLite3? No access to database at the same time. With thanks, Stefano -- _ The purpose of man is to know his Maker Be known by his Maker And make his Maker known So that others may know his Maker as their Maker(Emeal Zwayne) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users