Re: [sqlite] Unlucky number for the ROUND function
2.8.15 gives 9.9 --Keith On 8/30/05, Bob Dankert <[EMAIL PROTECTED]> wrote: > > Using the downloaded command-line tool for 3.2.5, I get the same :.0 > result. Using an older version which I compiled, I get 9.9, though it > seems it should round up to 10.0? > > Bob > > Envision Information Technologies > Associate > [EMAIL PROTECTED] > v. 608.256.5680 > f. 608.256.3780 > > > -Original Message- > From: Eric Bohlman [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 30, 2005 11:05 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Unlucky number for the ROUND function > > Edzard Pasma wrote: > > I found a number where the ROUND () function goes wrong: > > > > SQLite version 3.2.5 > > Enter ".help" for instructions > > sqlite> select round (9.95, 1); > > :.0 > > I get 9.9 (running on Win98, compiled with MingW). >
Re: [sqlite] C++ API: Retrieve multiple rows into struct, using callback
It's not too hard. Instantiante a static class method that matches the sqlite callback signature. When you register the callback with sqlite, you'll want to pass in the this pointer to the instantiated class. Within the static class method, cast the void pointer back to the instantiated class, and reenter the class through an internal (non-static) method. Push all the results you find into the class member variables, and voila, you have your container holding all results. Be careful not to muck with the instatiated class object from other threads much, though. --Keith On 8/26/05, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > On 8/26/05, Cam Crews <[EMAIL PROTECTED]> wrote: > > Hi. I'm new to sqlite and would like to use the sqlite C++ API to > > return multiple rows from a SELECT statement. I'm able to load a > > single row's result into a struct & pass it back by reference through > > the callback, but haven't been able to find any resources recommending > > a method for returning *multiple* results (rows). Anyone know if > > there are sample code/suggestions for getting this to work? I've thus > > far seen only rudementry examples of printing results to stdout from > > the callback. > > > > If these resources aren't already out there, I'll consider writing a > > tutorial on performing this using a vector of structs... thanks, > > If you're using c++ you probably don't want to use the callback > for retrieving results. It's difficult to reference members of an > instantiated class from a callback. use sqlite_step(). > I used stl vectors to store my results. > > > --- > The Castles of Dereth Calendar: a tour of the art and architecture of > Asheron's Call > http://www.lulu.com/content/77264 >
[sqlite] Re: OT: Prechecking SQL
Sigh. Amazing what the lack of an 'out' will do to a sentence: >I know sqlite will parse an SQL statement with running it, reporting > any errors. That should be: I know sqlite will parse an SQL statement without running it, reporting any errors. On 5/5/05, Keith Herold <[EMAIL PROTECTED]> wrote: > I know sqlite will parse an SQL statement with running it, reporting > any errors. Is there an ODBC way of doing this? I anticipate some > users of my app replacing the SQLite back end with a different (ODBC) > database system, and I would like to check unknown SQL out before I > release it to the database(s). > > -- Keith > ** > - Ever notice how 'big' isn't, compared to 'small'? > > - I'm not a professional; I just get paid to do this. > > - Rules for programming: >1. Get it working, right? >2. Get it working right. > > - Things I've learned about multithreaded programming: > > 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt > rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt > iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb > .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il > m^Ne from two or more threads > ** > -- ** - Ever notice how 'big' isn't, compared to 'small'? - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] OT: Prechecking SQL
I know sqlite will parse an SQL statement with running it, reporting any errors. Is there an ODBC way of doing this? I anticipate some users of my app replacing the SQLite back end with a different (ODBC) database system, and I would like to check unknown SQL out before I release it to the database(s). -- Keith ** - Ever notice how 'big' isn't, compared to 'small'? - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] COMMIT versus END
Right; I was thinking of checkpoint stuff, in a sense. Smaller transactions make things waaay slower :) . --Keith On Fri, 11 Mar 2005 14:03:45 -0500 (EST), Clay Dowling <[EMAIL PROTECTED]> wrote: > > Keith Herold said: > > I have a batch process that commits sets of 25 pre-built sql scripts > > on a timed basis. I have been wrapping the execution of all 25 sets > > in a single begin...end transaction set. The problem is that if any > > of those scripts fails to execute properly, than all 25 scripts are > > rolled back. It would be nice to get a bit better granuality, with > > partial commits or something. > > That's pretty much the definition of a transaction. Everything succeeds > or everything fails. Smaller transactions are what you want. The things > that should succeed or fail together go in the same transaction. > > Clay Dowling > -- > Lazarus Notes from Lazarus Internet Development > http://www.lazarusid.com/notes/ > Articles, Reviews and Commentary on web development > -- ** - Ever notice how 'big' isn't, compared to 'small'? - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] COMMIT versus END
Is there a difference between COMMIT and END TRANSACTION ? I have a batch process that commits sets of 25 pre-built sql scripts on a timed basis. I have been wrapping the execution of all 25 sets in a single begin...end transaction set. The problem is that if any of those scripts fails to execute properly, than all 25 scripts are rolled back. It would be nice to get a bit better granuality, with partial commits or something. Enter COMMIT. Does it allow you to commit portions of the sql scripts? I.e., could I start a BEGIN TRANSACTION; then at the end of each script execute a COMMIT to successfully commit that portion, and then proceed to the end, and END TRANSACTION. That way, if somewhere down the line, I lose the 15th file, at least the first 14 are in the database. Or is COMMIT a synonym for END TRANSACTION? -- Keith ** - Ever notice how 'big' isn't, compared to 'small'? - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] A question about performance
Hi all; I know that nested queries in the FROM and WHERE clauses are compiled and run just once. Is that true for the nested queries in the SELECT clause as well? For example: SELECT firstname AS First, (SELECT name as Last FROM tbl_of_last_names WHERE name = 'Roger') FROM tbl_of_first_names, tbl_of_last_names, tbl_of_unique_people WHERE (tbl_of_first_names.PersonID = tbl_of_unique_people.ID) AND (tbl_of_last_names.PersonID = tbl_of_unique_people.ID) ; Is the SELECT name as Last compiled only once, or once for every row found in the outer query? --Keith I am running 2.8.15 on WinXP/2000.
Re: [sqlite] sqlite performance variationin linux and windows
http://www.adtmag.com/article.asp?id=7421 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vcconMixedDLLLoadingProblem.asp It's been awhile since Iooked at this, but I distinctly remember reading from one of the product managers at MS that this wouldn't be fixed until 2005. The basic details were, I think, that the 'native' multithreaded C++ applications, under heavy load, would kill deadlock and halt. Since I work for a speech recognition company, the cpu is *always* under heavy load, and the problem has kept us from moving to .NET . Otherwise, I think we would probably do switch, since there are some frustrating bugs in VC6 that have been fixed. I also don't know if this has been fixed already, or not. --Keith On Fri, 25 Feb 2005 17:31:14 -0500, Andrew Piskorski <[EMAIL PROTECTED]> wrote: > On Fri, Feb 25, 2005 at 02:25:06PM -0800, Keith Herold wrote: > > Well, MS has said that VC++ .NET has a serious multithreading issue; > > a lot of companies are staying on VC6 for that reason. > > Keith, I wasn't aware of that. Could you point us to more info on the > problem, please? > > -- > Andrew Piskorski <[EMAIL PROTECTED]> > http://www.piskorski.com/ > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] sqlite performance variationin linux and windows
Well, MS has said that VC++ .NET has a serious multithreading issue; a lot of companies are staying on VC6 for that reason. --Keith On Fri, 25 Feb 2005 23:47:32 +0200, Ionut Filip <[EMAIL PROTECTED]> wrote: > > Hi Appadurai, > > From my experience the stock sqlite.dll is not very fast. I guess it's > build with VC++ 6. Using VC++ 7.1 I was able to build a version up to > 10-15% faster, not a bad optimization for 0 lines of code :) . It was a > little bigger, about +25%, but for desktop this is not a problem. I only > did this for sqlite 2.8.*, but it should also work for 3.* . > > Is there any technical reason why the "stock" dll is build with VC++ 6 > while better compilers are available ? > > Ionut Filip > > > -Original Message- > From: Neelamegam Appadurai [mailto:[EMAIL PROTECTED] > Sent: Friday, February 25, 2005 2:05 PM > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > Subject: Re: [sqlite] Re: sqlite performance variationin linux and windows > > Hi Chris, > Thanks for the detailed explanation, > I will dig deeper into my code to see if there is anything wrong in the OS > specific code in my appln. Anyway thanks for the support. appadurai > >
Re: [sqlite] Memory DB to disk DB
I am/was doing this in application, with 2.8.15 . I simply attached the on-disk database to the memory, and then wrote a bunch of dump queries to drop the memory data to disk (from the memory db connection): ATTACH 'C:\my_database_on_disk.sqlitedb' AS diskdb ; Unfortunately, I don't think you can execute transactions on the attached database in 2.8.15, and I couldn't think of a way to attach to the memory database from the disk database. I think you can execute transactions on the attached db in 3.x, though. It works ok, although since I was using the in-memory database as a preliminary db (multithreaded app), my dump queries got ugly, and quite slow on large sets, because I had to do the checks to ensure that the in-memory db wasn't dumping data that already existed in the database (I know, triggers, etc., but I hadn't tried those yet, and INSERT OR IGNORE scares me). I eventually substituted writing text sql scripts to disk, and then executing those directly into the disk database, which saved me 25-30% of the insert time. --Keith On Fri, 18 Feb 2005 04:01:32 +0100, chorlya <[EMAIL PROTECTED]> wrote: > I gues you could attach in-memory db to a newly created disk db and > then do something like > > CREATE TABLE newDiskTbl AS SELECT * FROM memoryTbl > > Take a look at http://www.sqlite.org/lang_createtable.html for more details > > Regards, > chorlya > > On Fri, 18 Feb 2005 11:13:40 +1100, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: > > > > I have a situation where I start with an in-memory DB, then need to save the > > entire thing to a new disk DB. Does anyone know the best way to do this? > > Would I attach the memory DB to a newly created disk DB? Is this even > > possible? I notice the COPY command is not supported in 3.x according to > > the > > documentation on the website, so even if I could attach it I would still > > need a > > way to copy the tables. Any suggestions would be greatly appreciated. > > > > TIA > > -brett > > > > > > This message was sent using IMP, the Internet Messaging Program. > > > > > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] Beginner Problem...
On Thu, 17 Feb 2005 16:34:07 +0100, Peter Berkenbosch <[EMAIL PROTECTED]> wrote: > Yes :) > > -Oorspronkelijk bericht- > Van: Claudio Bezerra Leopoldino [mailto:[EMAIL PROTECTED] > Verzonden: donderdag 17 februari 2005 16:25 > Aan: SQLiter Mailing List > Onderwerp: [sqlite] Beginner Problem... > > > I've used SQLite utility to learn about the library, > but have a doubt... > I need to submit sql text scripts to the utility > instead of command line sql code. It´s possible? > > Cláudio Leopoldino > > ___ > Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. > http://br.acesso.yahoo.com/ - Internet rápida e grátis > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-2-2005 > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-2-2005 > > somewhat more helpfully :), .read replace with the name of your script. I have had some trouble with this, when reading in files which have binary-encoded data (using sqlite_binary_encode), but for everything else, it works fine. I would also use .output when reading scripts; if there are any errors, the information scrolls by a little two quickly, and if the script is long, the scroll buffer will lose the info: .output myoutputfile.txt To redirect to stdout, use: .output stdout For help: .help --Keith -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] Reset values if insert fails
The output is an SQL script, formatted for later insert, so I don't have access to the return values, and can't use the API. The update names table (a temporary table) is used to maintain the current ID's so that the dependent inserts don't require all the information leading up to the current insert. The sql scripts are inserted, one at a time, in a transaction; each of the scripts has lots and lots of sql in it (think of a log file). --Keith On Fri, 11 Feb 2005 08:15:49 -0500 (EST), Clay Dowling <[EMAIL PROTECTED]> wrote: > > Keith Herold said: > > I have a question about how to reset values if an insert fails. Using > > the following tables, is there any relatively straightforward way to > > set LastNameIDInserted to -1 if the UNIQUE constraint on tblNames > > fails? > > It looks to me like you're trying to put a burden on the database that > properly belongs in your code. You'll be able to tell if the insert > failed by the response code of the sqlite_exec or sqlite_step function > call. You'll be able to get the most recent value with > sqlite_last_insertid, if there was no error condition on your return. > > If you ever need to know what the highest value is for the insertion id > you can do SELECT MAX(ROWID) FROM myTable; You shouldn't be trying to > maintain these keys yourself though. Let the database do it for you. > > Clay > -- > Lazarus Notes from Lazarus Internet Development > http://www.lazarusid.com/notes/ > Articles, Reviews and Commentary on web development > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] Reset values if insert fails
I have a question about how to reset values if an insert fails. Using
the following tables, is there any relatively straightforward way to
set LastNameIDInserted to -1 if the UNIQUE constraint on tblNames
fails?
CREATE TABLE tblNames
(
NameID INTEGER PRIMARY KEY,
First CHAR(10),
Last CHAR(10),
CONSTRAINT uniquepairs UNIQUE
(
First,
Last
)
);
CREATE TABLE tblNamesLastInserted
(
LastNameIDInserted INTEGER
) ;
CREATE TRIGGER updateLastInsertedID AFTER INSERT ON tblNames
BEGIN
UPDATE tblNamesLastInserted
SET LastNameIDInserted = new.NameID ;
END ;
INSERT INTO tblNames (First, Last) VALUES ('Keith', 'Herold') ;
-- LastNameIDInserted = 1
INSERT INTO tblNames (First, Last) VALUES('Keith', 'Herold');
-- LastNameIDInserted = -1
I know about last_insert_rowid, but that doesn't tell you whether an
insert succeeded or not, just what the last rowid was. I tried
modifying the trigger to use a case statement that set
LastNameIDInserted = -1 if change_count() was 0, but that didn't seem
to do anything (and I suppose it's because the trigger never fires,
because the insert fails, and there is no 'AFTER' in this case?).
I suppose a 'BEFORE' trigger could do this for me, but I was
wondering if there was some other way?
I am trying to move some work currently done in C++ into the database,
so that I can rely on the database to do the heavy-lifting.
-- Keith
**
- Ever notice how 'big' isn't, compared to 'small'?
- I'm not a professional; I just get paid to do this.
- Rules for programming:
1. Get it working, right?
2. Get it working right.
- Things I've learned about multithreaded programming:
123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt
rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt
iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb
.ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il
m^Ne from two or more threads
**
Re: [sqlite] Temporary tables versus nested queries
Okay, I can see that. Here's the theory question: if SQLite does not allow inner queries to reference outer queries, than the inner query ought to need to be run just once. Why rerun for each row in the outer case? I'll have to look at writing these as a join; it may be possible. --Keith On Mon, 17 Jan 2005 09:30:40 -0800 (PST), Jay <[EMAIL PROTECTED]> wrote: > > I believe it reevaluates the subquery for each row of the outer > query (i.e. Yuk!). If you can write your query as a join, or temp > tables, it's much better. The commercial sql engines behave this way > too. > > --- Keith Herold <[EMAIL PROTECTED]> wrote: > > > Hi all; I was playing about with some (probably stupid) queries the > > other day, and I noticed that the performance on queries with nested > > subqueries was *much* slower, than the same queries with the nested > > subqueries represented by temporary tables (win32, 2.8.15). Has > > anyone else noticed this? Why would that be true? I sorta expected > > that a nested query would be in an intermediate (hidden) temp table > > anyway, so that they would behave at the same speed, but such is not > > the case. > > > > It doens't matter, in the sense that I will happily create and drop > > the temp tables involved, but it did make me wonder. > > > > Involved is an outer query which contains an inner query, which also > > contains another inner query. > > > > -- Keith > > ** > > - Ever notice how 'big' isn't, compared to 'small'? > > > > - Sounds like a Wookie; acts like mad cow. > > > > - I'm not a professional; I just get paid to do this. > > > > - Rules for programming: > >1. Get it working, right? > >2. Get it working right. > > > > - Things I've learned about multithreaded programming: > > > > 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt > > rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt > > iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb > > .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il > > m^Ne from two or more threads > > ** > > > > > = > > - > > "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of > the ancient tomb of the petrified pharaoh, he vowed there would be no curse > on him like on that other Lord, unless you count his marriage to Lady > Tarlington who, when the lost treasure was found, will be dumped faster than > that basket in the bulrushes." > Melissa Rhodes > - > > The Castles of Dereth Calendar: a tour of the art and architecture of > Asheron's Call > http://www.lulu.com/content/77264 > > > __ > Do you Yahoo!? > Yahoo! Mail - Find what you need with new enhanced search. > http://info.mail.yahoo.com/mail_250 > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] Temporary tables versus nested queries
Hi all; I was playing about with some (probably stupid) queries the other day, and I noticed that the performance on queries with nested subqueries was *much* slower, than the same queries with the nested subqueries represented by temporary tables (win32, 2.8.15). Has anyone else noticed this? Why would that be true? I sorta expected that a nested query would be in an intermediate (hidden) temp table anyway, so that they would behave at the same speed, but such is not the case. It doens't matter, in the sense that I will happily create and drop the temp tables involved, but it did make me wonder. Involved is an outer query which contains an inner query, which also contains another inner query. -- Keith ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] OT: Compiling under Windows
In VS 6, we generally find that you need to compile everything with Multithreaded-dll, even the libraries, or you get very strange error messages as the compiler tries to link with conflicting run-time libraries. --Keith On Thu, 13 Jan 2005 20:54:13 +0100, Rolf Schaeuble <[EMAIL PROTECTED]> wrote: > What do you mean with "Multithreaded" or "Mulithreaded-DLL" project? Can > you explain exactly where you can select between these two options, and > whether you are using Visual Studio 6 or 2002/2003? > > Rolf Schäuble > > Michael Knigge wrote: > > > Hello, > > > > this question is slightly OT here but. > > > > I want to compile SQLite as a library (not as a DLL) and add this > > library to my DLL-Project. Why? Because the C-Compiler from MS > > produces so many warnings and I want to compile all my own projects > > without even a single warning > > > > So I wonder what is correct: To compile/link the resulting sqlite.lib > > as a "Multithreaded" project or as a "Multithreaded-DLL" project? > > Remember, I want to add this sqlite.lib to my DLL > > > > > > Bye & Thanks, > > Michael > > > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] Good db XML datastructure?
Hi all; I have been trying to find a good XML schema for describing databases, so that I can, among other things, produce documentation for my database format. Is there a good (free) one already out there, that is genarally accepted/used? I'm not looking to export data, just the description of the database (i.e., schema, comments, etc.) -- Keith ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
Re: [sqlite] mailing list problems?
I also; I sent in a couple of messages on Monday and I haven't seen them post. --Keith On Wed, 12 Jan 2005 09:20:13 -0700, Dennis Cote <[EMAIL PROTECTED]> wrote: > Hi, > > Yesterday I posted three messages to the mailing list. Today when I look at > the list only the last of those three messages has been distributed back to > my email account. > > Has anyone else noticed the mailing list failing to distribute messages? > > Dennis Cote > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] Question about EXCEPT
Since EXCEPT returns the left side of a query, once duplicates are deleted from the select on the right, how might one get the !Left AND !Right rows, i.e., the rows which are not in *both* the left and right selects. I had thought EXCEPT did this, but it doesn't, and I was wondering if SQlite had a way to do this that did not involve doing except on both, switching the 'left' and the 'right'. --Keith
Re: [sqlite] Detecting changes/last insert rowid question
Well, yeah, I was hoping to get it out of a succession of queries. SQLite's API is hidden behind a wrapper, and most of it is masked out, so getting access to the API is problematic. Unfortunately, the semantics of the return code on that API call mean that the execute took place, not necessarily that the execute statement succeeded (I have no idea why), so I can't look at the return to determine if a change occurred. I have found change_count in func.c; when testing that using sqlite.exe, if I do an Insert, and then write a 'changes' select, I get 0 changes. However, when I do the insert and select as a single statement (that is, I hit enter only once, not twice), I get back changes = 1, which is what I need for now. I assume that for SQLite3, instead of change_count, I should use 'changes'? --Keith On Thu, 6 Jan 2005 14:30:06 -0500 (EST), Clay Dowling <[EMAIL PROTECTED]> wrote: > > Keith Herold said: > > I know I can recover the last_insert_rowid() in SQLite in the SQL > > query itself; is there an equivalant SQL function to sqlite_changes or > > sqlite_last_statement_changes (from 2.8.15)? If, for example, an > > insert doesn't take place, I get the last id of the insert *before* > > this one. What I would really like is to be able to determine whether > > the last insert actually inserted something or not, but from within > > the SQL query. > > You can check the result code of your API function call to see if the > query succeeded or not. A failed call means that it didn't do the insert. > SQLITE_DONE or SQLITE_OK means that your insert happened and the > last_insert_id() result is your new id. > > Clay > > -- > Lazarus Notes from Lazarus Internet Development > http://www.lazarusid.com/notes/ > Articles, Reviews and Commentary on web development > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] Detecting changes/last insert rowid question
I know I can recover the last_insert_rowid() in SQLite in the SQL query itself; is there an equivalant SQL function to sqlite_changes or sqlite_last_statement_changes (from 2.8.15)? If, for example, an insert doesn't take place, I get the last id of the insert *before* this one. What I would really like is to be able to determine whether the last insert actually inserted something or not, but from within the SQL query. If not, is there any good way, without, for example, querying the number of rows in the database, saving the count in C, executing the change, then requerying the number and comparing it to the original count? --Keith -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] String Concatenation
I have a semi-quick question about how to do successive string concatenation. I have a table: CREATE TABLE mystrings ( OwnerID INTEGER NOT NULL, AString VARCHAR(900), Sequence INTEGER DEFAULT 0 ); Each AString is associated with an Owner; a single owner can have multiple strings assigned to it, with the order of the strings held by sequence: OwnerID, AString, Sequence 1, 'concatenate', 0 1, 'some', 1 1, 'strings', 2 1, 'together', 3 What I need to do is create a single string out of the AString, Sequence pairs, for a given owner. Obviously I could do this through some C++ code, but I would prefer to do it within SQL code, but can't think of a way. The sequences are not always the same lenght, i.e., a particular owner may have 1, 10, or 1972 word long sequences. With cursors, I don't think this would be difficult, but I can't figure out how to do this within SQLite. Any help? --Keith
RE: [sqlite] Is there any way to enable recursive triggers?
Out of curiosity, why not allow recursion, but start returning warnings from sqlite3_step indicating that the query is recursive (perhaps once it has crossed some predefined recursion count)? As one post mentioned, every programming language allows you to shoot yourself in the foot with recursion, and for precisely the reasons that make a hard limit seem somewhat unpalatable. Although, if hard limits are imposed, I would prefer a runtime defined limit, so that I can change the limit based on the needs of the application, without having to recompile the sqlite libraries. --Keith ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: Jeremy Hinegardner [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 8:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there any way to enable recursive triggers? On Tue, Jan 04, 2005 at 05:54:04AM -0500, D. Richard Hipp wrote: > One stubling block with recursive triggers is that a recursive trigger > can result in an infinite loop. I sent out a query a month or so ago > requesting ideas on how to detect and deal with infinite loops in > recursive triggers. I got a few helpful responses. More input would > be appreciated. Graeme Birchall has an excellent book (The DB2 UDB Cookbook) available for download. It has an entire chapter dedicated to DB2's Recursive SQL and one section of it is "Halting Recursive Processing". Maybe it will provide some insight. http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED]
[sqlite] Pilgrim seeking wisdom on schemas
I am looking for some inspiration on schemas in SQLite. I am trying to decide whether the following is a good idea, because I can't think of a way to do this in SQLite without writing some C++ code. I have a table that consists of warning and error messages that come from two separate (but related) sources of operations and sets. In other words, any paricular operation can generate warnings and errors, and any particular set can generate warnings or errors. CREATE TABLE tblWarningsAndErrors ( SourceID INTEGER NOT NULL, --links to source table id; for sets, this is tblSets.SetID; -- for operations this tblOps.OpID MessageType INTEGER, -- one of: 1: SetWarning, 2: SetError, 3: OpWarning, 4: OpError -- 1,2 go to tblSets; 3,4 go to tblOps Message VARCHAR(900) ); The Set table: CREATE TABLE tblSets ( SetID INTEGER PRIMARY KEY, --... ); CREATE TABLE tblOps ( OpId INTEGER PRIMARY KEY, --... ); My midgit-brained idea was to keep all the warnings and errors in a single table, and then use the MessageType to decide which of the two tables to access in order to link up the tables. However, I can't think of a way to make the decision in SQLite entirely in SQL (in something like transact-sql, I could execute a stored procedure). Is this even possible, without writing some C/C++/Java, whatever, code in SQLite? Is this generally just a bad design, or a normal thing? Most of me thinks that the design principle involved probably necessitates a separate table for set warnings and errors, and another one for operation warnings and errors. --Keith ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
RE: [sqlite] What's the difference of "select * from tb" and "select "ID" from tb"?
Doesn't this mean select the literal value "ID"? ** - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 21, 2004 11:55 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] What's the difference of "select * from tb" and "select "ID" from tb"? red forks wrote: > A table: > create table tb (ID varchar); > When execute select * from tb, I can refer the column row["ID"], while > using "select "ID" from tb", I must using row["\"ID\"']. > > Why Sqlite return column name as "ID"? > I do not understand the question. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] Implementing aggregate user functions
I think one example is calculating things like standard deviation, where you need each data item separately, first to calculate the mean, then to calculate the variance. Thresholding (finding 'optimal' values) is another one. On very large datasets, with, say, a high parameter space, it might take quite a while to do the calculations. Of course, avg() is already in the spec, so this isn't the 'perfect' instance, but I can imagine problem spaces where a non-linear space is approximated into a linear one in some way not obvious. --Keith ** - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: Roger Binns [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 7:26 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Implementing aggregate user functions > new API function. But I am loath to add new API functions without > good reason and I do not consider performance optimization of an > exception condition a good reason. So you are going to have to argue > very hard to get such a thing implemented. My motivation isn't based on performance, but on correctness. If the function already did sqlite3_result_error in step, it is going to be somewhat upset at having to give a value anyway in final. It may also be the case that some functions take a lot of time to calculate their final results and can't do so step by step, although I can't think of any examples :-) I am doing an enhanced Python wrapper and want to make user functions written in Python seem as natural as possible. Performance will matter somewhat less :-) Roger
[sqlite] Storing precompiled queries/vm?
3.0 supports precompiled queries natively; is it possible to store the vm in a resource dll or something, load it up when the application starts, and then attach an open sqlite db to the stored vm? My impression was no, based on the API and the lack of information on wiki and the mailing list about it. --Keith ** - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
RE: [sqlite] PHP5 && SQLite3
Ummm... Sure, but not really my question, either. Not questioning the wisdom, just the technical issues. Perhaps someone can point me to the relevant archives so I can look? --Keith -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:14 AM To: [EMAIL PROTECTED] Subject: RE: [sqlite] PHP5 && SQLite3 On Tue, 19 Oct 2004, Keith Herold wrote: >Out of curiosity, how hard would it be to build a translation layer? I >suppose it's easier to keep both versions around, and upgrade when >necessary, but as a technical problem, it's at least interesting? Only >downwardly, of course, so there would be no expectation that 2.X could >use 3.X, just the reverse. It had sounded as if only the file format >was the problem; is it more than that? > >I'm not pushing, in any sense, just wondering if anyone has looked at >this issue with an eye to resolving it. There's nothing to resolve. In the discussion of SQLite3 requirements earlier in the year, one of the issues that came up was database migration, and the concensus on the list at the time was to forgo automatic migration to keep the library simple. Migration can be done very simply using the sqlite/sqlite3 shells and the .dump command. This was a conscious deliberate decision. > >--Keith > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] PHP5 && SQLite3
I wasn't saying SQLite *should* do it, I was wondering how technically difficult such a layer might be. For example, while the API's are different, if the underlying file format is the real problem, it would still be nice to leverage SQLite3's (S3) better concurrency. Clearly, this is a bit of problem, since it would required hacking out the disk read components from S3 and replacing them with a generic interface. Having not looked at the source, I can't imagine this is trivial, since there is no doubt DHP has heavily optimized the format and the reading/writing of it but still... This is one of those "Can it be done, and at what cost", not a "It needs to be done, and damn the cost". --Keith -Original Message- From: Stober, Mel [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 9:01 AM To: '[EMAIL PROTECTED]' Subject: RE: [sqlite] PHP5 && SQLite3 using PHP doesn't help in pure C++ desktop applications that do not run in web-server entironments. Mel Stober -Original Message- From: Craig Morrison [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 10:57 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] PHP5 && SQLite3 Keith Herold wrote: > Out of curiosity, how hard would it be to build a translation layer? > I suppose it's easier to keep both versions around, and upgrade when > necessary, but as a technical problem, it's at least interesting? > Only downwardly, of course, so there would be no expectation that 2.X > could use 3.X, just the reverse. It had sounded as if only the file > format was the problem; is it more than that? > > I'm not pushing, in any sense, just wondering if anyone has looked at > this issue with an eye to resolving it. The PHP library IS the translation layer. The crux of the issue is that the formats are NOT compatible, nor are the API's. What Dr. Hipp suggested is the best possible course. Link with both API's which _DO_ have separate naming conventions making it possible to support both formats concurrently. All new databases follow Sqlite3, legacy follow Sqlite2. There are signatures in the database files to distinguish between the two. Use the tools that are available to add the layer of abstraction necessary. Don't force that on SQlite itself. Its all summed up in one sentence, which is the first one that appears at sqlite.org: "SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine." The focus is there, anything beyond that is up to the implementation. -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://www.mtsprofessional.com/ A Win32 email server that works for You.
RE: [sqlite] PHP5 && SQLite3
Out of curiosity, how hard would it be to build a translation layer? I suppose it's easier to keep both versions around, and upgrade when necessary, but as a technical problem, it's at least interesting? Only downwardly, of course, so there would be no expectation that 2.X could use 3.X, just the reverse. It had sounded as if only the file format was the problem; is it more than that? I'm not pushing, in any sense, just wondering if anyone has looked at this issue with an eye to resolving it. --Keith ** - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 6:40 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] PHP5 && SQLite3 julien wrote: > I put this question because compatibility between SQLite2 and SQLite3 > seems poor. > Poor? More like non-existent. SQLite2 and SQLite3 have a very similar design but both the interface and the underlying file format are quite different and imcompatible. Derrell Lipman wrote: > > The plan _is_ to integrate SQLite3. There are two of us who may > integrate it, depending on availability and need. There's no schedule > for when it will be completed, but it is reasonably high on some of > our priority lists. > SQLite3 is designed so that it can be statically linked into the same executable as SQLite2. One plan for PHP might be to link in both libraries. When opening an existing database, check the header and use either the SQLite2 or the SQLite3 library depending on what kind of database it is. Create new databases as SQLite3. The scripting interface would be the same and the user would never know the difference. In this way, you preserve backwards compatibility with existing SQLite2 database files while at the same time taking advantage of new SQLite3 features. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] Quick question about sqlite_trace
For every statement; great! --Keith -Original Message- From: Keith Herold [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 11:03 AM To: [EMAIL PROTECTED] Subject: [sqlite] Quick question about sqlite_trace Does sqlite_trace fire for every sql statement in a sql_exec, or just for the whole sql_exec/sql_compile? I.e., suppose the call to sql_exec is: BEGIN TRANSACTION; INSERT INTO tblMyDestinationTable (Dogs, Cats, FoodCosts) SELECT * FROM tblMyTable tmt WHERE tmt.Foo = 'bar'; SELECT last_insert_rowid(); END TRANSACTION; So, does sqlite_trace fire for each of BEGIN, INSERT INTO, SELECT, END , or just once for the whole sql_exec? I do lots of these kinds of things, and it would be nice if I could output the sequence to as they are actually executed rather than as a big chunk. --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
[sqlite] Quick question about sqlite_trace
Does sqlite_trace fire for every sql statement in a sql_exec, or just for the whole sql_exec/sql_compile? I.e., suppose the call to sql_exec is: BEGIN TRANSACTION; INSERT INTO tblMyDestinationTable (Dogs, Cats, FoodCosts) SELECT * FROM tblMyTable tmt WHERE tmt.Foo = 'bar'; SELECT last_insert_rowid(); END TRANSACTION; So, does sqlite_trace fire for each of BEGIN, INSERT INTO, SELECT, END , or just once for the whole sql_exec? I do lots of these kinds of things, and it would be nice if I could output the sequence to as they are actually executed rather than as a big chunk. --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
RE: [sqlite] Questions about sqlite's join translation
This response would be good to put in swiki. --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 5:59 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Questions about sqlite's join translation Keith Herold wrote: > The swiki says that making JOINs into a where clause is more > efficient, since sqlite translates the join condition into a where > clause. When SQLite sees this: SELECT * FROM a JOIN b ON a.x=b.y; It translate it into the following before compiling it: SELECT * FROM a, b WHERE a.x=b.y; Neither form is more efficient that the other. Both will generate identical code. (There are subtle differences on an LEFT OUTER JOIN, but those details can be ignored when you are looking at things at a high level, as we are.) > It also > says that you make queries more effiecient by minimizing the number of > rows returned in the FROM clause as far to the left as possible in the > join. Does the latter matter if you are translating everything into a > where clause anyway? > SQLite implements joins using nested loops with the outer loop formed by the first table in the join and the inner loop formed by the last table in the join. So for the example above you would have: For each row in a: For each row in b such that b.y=a.x: Return the row If you reverse the order of the tables in the FROM clause like this: SELECT * FROM b, a WHERE a.x=b.y; You should get an equivalent result on output, but SQLite will implement the query differently. Specifically it does this: For each row in b: For each row in a such that a.x=b.y: Return the row The trick is that you want to arrange the order of tables so that the "such that" clause on the inner loop is able to use an index to jump right to the appropriate row instead of having to do a full table scan. Suppose, for example, that you have an index on a(x) but not on b(y). Then if you do this: SELECT * FROM a, b WHERE a.x=b.y; For each row in a: For each row in b such that b.y=a.x: Return the row For each row in a, you have to do a full scan of table b. So the time complexity will be O(N^2). But if you reverse the order of the tables in the FROM clause, like this: SELECT * FROM b, a WHERE b.y=a.x; For each row in b: For each row in a such that a.x=b.y Return the row No the inner loop is able to use an index to jump directly to the rows in a that it needs and does not need to do a full scan of the table. The time complexity drops to O(NlogN). So the rule should be: For every table other than the first, make sure there is a term in the WHERE clause (or the ON or USING clause if that is your preference) that lets the search jump directly to the relavant rows in that table based on the results from tables to the left. Other database engines with more complex query optimizers will typically attempt to reorder the tables in the FROM clause in order to give you the best result. SQLite is more simple-minded - it codes whatever you tell it to code. Before you ask, I'll point out that it makes no different whether you say "a.x=b.y" or "b.y=a.x". They are equivalent. All of the following generate the same code: ON a.x=b.y ON b.y=a.x WHERE a.x=b.y WHERE b.y=a.x -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
RE: [sqlite] still having problems with DBD::SQLite2
Well, you can always synchronize access and share the same pointer, right (in windows; using fork in unix is bad, presumably because fork() just copies all the data into the child)? It sucks if you are using sql_step, or, I imagine, precompiled queries, though. --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Friday, October 08, 2004 4:23 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] still having problems with DBD::SQLite2 At 4:28 PM -0500 10/8/04, Freeman, Michael wrote: >I am pretty sure I know whats going on now. I am using POE (Perl Object >environment, I highly recommend it poe.perl.org) and what is happening >is my program is basically trying to do inserts into the database at >the same time, which I think is creating a deadlock. It can handle >doing one insert at one time, but when I fire a lot of events at it >that are kind of happening asynchronously on the server, it fails. It >would be nice if the debugging and logging output made some sort of >damn sense or would tell you these things.. I think I have had my head >up my ass all day cuz of this. I am going to try do some stuff in my >program that will "pause" all the other helper "threads" when I'm doing >a sql insert. Make sure that each thread has its own database connection via its own DBI->connect(), assuming that DBI isn't pooling and reusing the connections behind your back. This is analagous to C programs having a separate sqlite open() in each thread, which is necessary. -- Darren Duncan
[sqlite] Questions about sqlite's join translation
The swiki says that making JOINs into a where clause is more efficient, since sqlite translates the join condition into a where clause. It also says that you make queries more effiecient by minimizing the number of rows returned in the FROM clause as far to the left as possible in the join. Does the latter matter if you are translating everything into a where clause anyway? --Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
RE: [sqlite] Locking in 3.0.5
Also, I seem to recall on swiki that even reads benefit from transaction wrapping. Like Christian, I have wrapped any series of reads where the data need to be consistent (at that moment, as opposed to database consistency). I would welcome a BEGIN (READONLY) statement. ---Keith ** - I'm not a professional; I just get paid to do this. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads ** -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 7:00 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Locking in 3.0.5 On Wed, 1 Sep 2004, Matt Wilson wrote: >On Wed, Sep 01, 2004 at 02:46:39PM +0100, Christian Smith wrote: >> >> Add a new "BEGIN [TRANSACTION] FOR READONLY" statement, which begins >> the transaction with a read lock only and doesn't allow the >> transaction to even try to promote to a write lock. > >Why do you need a transaction at all if you're not going to commit? > >In my code, readers never use BEGIN, only writers do. A transaction gives you a snapshot in time of the database. You may need to do more than one query, and require a consistent snapshot for the duration of the multiple queries. > >Cheers, > >Matt > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] .db file size is not changing
I think you have to do some VACUUM'ing in order to shrink the database. --Keith -Original Message- From: Paolo Vernazza [mailto:[EMAIL PROTECTED] Sent: Thursday, August 26, 2004 9:56 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] .db file size is not changing Unnikrishnan Nair wrote: >Hi all, > >I have a sqlite database. I had thousands of rows in >it. I did a test with the database and everything was >good. So before I ship it to production, I went to the database and I >did drop all the index and 'deleted' all the rows from the table. I >thought the size of the database will come down so that I can ship the >model database for production. But the size didn't change. >How can I reduce the size or is it possible at all? > >Thanks. >Unni > > I think that it is better let your program create the DB structure from a sql schema script. Usually it is smaller than the corresponding DB.. sqlite data.db .schema > schema.sql Paolo
RE: [sqlite] Rowid in VIEWs?
Oo, pretty! I am working my way through "The Complete Reference SQL Second Edition" and the documentation, albeit slowly. I don't see a reference to LIMIT, which implies that is a SQLite, thing, yah? I finally went the really lazy route (I have a Friday deadline, and didn't want to try experimenting with precompiled queries, right now) and created a temporary table for each new set of results. That temp table has an INTEGER PRIMARY KEY, and I jam the results into it. It has several advantages; one is that it doesn't reconduct the query the way LIMIT probably would; two, it keeps the results around for secondary processing (if I need it); three, it allows me to do TOP/LIMIT-like functionality using the rowid. Once Friday is passed, I am going to go back through and do some timing to figure out whether this is faster then doing a precompiled query, or whatever, later. I expect to see a win using precompiled queries (same number of function calls, I think) but I may still need to research inside the results, so we will see. Any advice/experience on doing it this way? --Keith -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: Monday, August 23, 2004 6:41 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Rowid in VIEWs? On Tue, 17 Aug 2004, Keith Herold wrote: >The short question: do (temporary) VIEW's have rowid's in SQLITE? > >A small description of the problem: > > ... On occasion, the result is quite large, and my standard way of >returning results is very slow on large result sets, so I try to break >up how many results I return. Since TOP does not exist in sqlite, I >thought I would create a temporary view using the (Key,Value) pairs in >a SELECT statement. BTW, TOP is not a standard SQL construct. The standard way to do TOP like functionality is to use LIMIT in your SELECT: http://www.sqlite.org/lang.html#select Notice the LIMIT keyword, just after the ORDER BY clause. > >--Keith > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Temporary tables
I am using a temporary table for some results, and I wanted to have the
rowids reset every time the table is used, so I drop the table if it is
already created, and then recreate it and fill it with results .
I thought I could use a SELECT along the lines of
SELECT 1 FROM sqlite_master WHERE (type = 'table') AND ('name =
tblTest') ;
But this doesn't seem to work. Is there another place to check for a
temporary table's existence (sqlite 2.8.13)?
--Keith
**
- I'm not a professional; I just get paid to do this.
- Good writing lets the reader think about 'what'
was written, not 'how' it was written.
**
RE: [sqlite] Rowid in VIEWs?
Heh. Didn't think about precompiled queries at all. Have to look into that. The description you give is what I am already writing to handle it from the C++ side, so it looks like I'm half-way there . Thanks for the help. --Keith -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 11:29 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Rowid in VIEWs? Keith Herold wrote: > The short question: do (temporary) VIEW's have rowid's in SQLITE? The short answer: no. You don't say how you are accessing the database, but if you are using C, then you can simply use the precompiled query interface to perform your lookup and return your result in small blocks. You will need a getdata function that takes a query and returns up to N results. The first time the function is called it can compile the query and save a pointer to the vm produced. Then it steps the vm until it has retreived N results or the vm is done. If the vm is done it can finalize the vm and clear the vm pointer. In either case it returns the results it retrieved. This will let you get results in small blocks but only perform your complex query once. I hope this helps.
[sqlite] Rowid in VIEWs?
The short question: do (temporary) VIEW's have rowid's in SQLITE?
A small description of the problem:
I have a small database of values, where I would like users to be able
to execute simple filters (as (Key,Value) pairs) to return a subset of
the data. On occasion, the result is quite large, and my standard way
of returning results is very slow on large result sets, so I try to
break up how many results I return. Since TOP does not exist in sqlite,
I thought I would create a temporary view using the (Key,Value) pairs in
a SELECT statement. Then I could request results from the view, and
limit the result set size by view.rowid > some_number. From a perusal
of the documentation, SWIKI, and some of my own experiments, it doesn't
seem like this will work, because VIEWs don't seem to have rowids. If
there are not, in fact, rowid's in a VIEW (I can imagine there aren't
since it seems like a VIEW is just a link back to rowid's in the source
tables), is there a decent way to do what I need?
So, a quick source table:
CREATE TABLE KeyValue AS
{
KVID INTEGER PRIMARY KEY,
KeyID INTEGER NOT NULL,
ValueID INTEGER NOT NULL
} ;
And an example of the view:
CREATE TEMPORARY VIEW vwKVFilter AS
SELECT KeyID, ValueID
FROM KeyValue
WHERE KeyID = '1' AND ValueID = '2' ;
And what I would like to be able to do to look at the results in
vwKVFilter, repeatedly, by changing the BETWEEN conditions:
SELECT KeyID, ValueID
FROM vwKVFilter
WHERE vwKVFilter.rowid BETWEEN 10 AND 20
Actually, what I really want to do is return the 'next' 10 items
starting at some id, without having to reconduct the original SELECT
that populated the VIEW (that query is, in the real system, a fairly
large set of inner joins) , but I don't see anything in the structure of
SQLite that allows this kind of behavior. Short of the rowid's, which
already increment, I don't see any way to provide a rowid-like item in
the view, that increments as rows are inserted.
Any help is much appreciated.
Incidentally, the documentation on the web site for CREATE VIEW looks a
bit jacked-up. The sentence beginning 'If a is specified...' and other
parts of that section are missing words, I think. I assume the missing
word is 'database-name' .
--Keith
[sqlite] unsubscribing
Hi there one of you list admin people. I have tried for days to unsubscribe to the mailing list at this address, but it has not worked (I work at home now, and it is more convenient to use my home email for sqlite). I don't know if our spam filter at work is catching the confirmation emails and dumping them (and our network guy is out for a week or two) or not, but I can't even get the help messages. Can someone help me out (by unsubscribing)? --Keith
RE: [sqlite] Storing large files
I had the impression additionally that BLOBS aren't usually journalled (for many rdbms), so storing them in the database is nothing more than a convenience, and just makes the database too darned big. With DRH's advice, remember to store locations you will *always* be able to get to (like not "C:\WIN32", or "/home/keith/private"). Use UNC or web addresses or something you can remap easily, that doesn't break if you just copy the database and forget to copy the files. I know that sounds like an obvious thing, but it will bite you in areas most prefer unbitten. Ask me how I learned that lesson (painfully, I might add). --Keith > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 15, 2004 7:54 AM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Storing large files > > > Paul Malcher wrote: > > Dennis Volodomanov wrote: > > > >> Hello all, > >> > >> Can someone please tell me if there's any limit on the > size of data > >> stored in one field (BLOB) in SQLite3? > >> > >> > > Hi, I spent all day yesterday picking through SQLite3 > docs, trying to > > solved a problem that turned out was my own fault. I never saw any > > mention of 16 MB Blob limit. Either way I intend to find > out , I'm gonna > > lay some serious abuse on SQLite today and see what can and cannot > > handle. I'll check the docs again maybe I missed it either > way I'll let > > you know what I find out. > > The 16MB limit was in SQLite 2.8 file format. In most > installations, a separate limitation of 1MB is imposed by the > schema layer. To relax that limit, you have to change > MAX_BYTES_PER_ROW in the sqliteInt.h file and recompile. > > In SQLite version 3.0, there is no theoretical limit on the > size of BLOBs. (The limit is really about 4.6e+18 bytes, but > file size limits will come into play first so you will never > get to that size.) However, the same MAX_BYTES_PER_ROW > constraint is still in place. So with a default build, the > maximum BLOB size is still about 1MB. You can, I suppose, > increase MAX_BYTES_PER_ROW to whatever value you want and > recompile. But, if you make MAX_BYTES_PER_ROW really big, I > think you will find that performance gets very bad for very > large rows. > > For large BLOBs, your best bet is to store the BLOB data in a > separate file and store the name of the separate file in the > SQLite database. > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > >
RE: [sqlite] FW: 2.8.14 performance, Indexed Table INSERTS
> On Jun 20, 2004, at 9:07 PM, Darren Duncan wrote: > down the result set would make things faster..? Wouldn't the select > here: > >CREATE TABLE tmp ( flag boolean, name text ); > >SELECT name FROM tmp WHERE flag = 1 AND name LIKE '%foo%'; > > run faster with an index on the flag column since it can scan > just the > flag = 1 rows instead of the full table? I think this is one of those big-O things, right? It might be faster, but only a bit faster, and not enough to justify the hassle of creating and maintaining the index. --Keith - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Perl code to 'clean up' the source
The current cleaned-up source download is not 2.8.14, but I would like to use 2.8.14 in an upcoming product. Does anyone have the perl code handy (and willing to give it to me :) ) that I could use to clean up the source code (it's for windows; I can do it myself, but it will take me a while, so I thought I would ask around)? I have already entered a ticket about the download (with the lowest priority and severity). Thanks, Keith ++ [EMAIL PROTECTED] Lead Speech Recognition Engineer Toll-Free: (877) 977 - 0707 Phone: (858) 707 - 0707 x238 www.LumenVox.com Fax: (858) 707 - 7072 LumenVox, LLC 3615 Kearny Villa Rd #202 San Diego, CA 92123 ++ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQLite version 3 design question: '500'=500?
In MS SQL 2000, through the query analyzer SELECT '500' = 500 returns 500 . SELECT 500 = '500' returns Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '='. Beyond these, I have no preference on whether they are true or false; I am less worried about what the programming languages say as far as PERL, etc. . I would rather the return look like the majority vote on what the 'other' SQL engines/manufacturers do. --Keith > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 12, 2004 5:20 PM > To: [EMAIL PROTECTED] > Subject: [sqlite] SQLite version 3 design question: '500'=500? > > > The development team is making progress on SQLite version > 3.0. But we've run across an interesting puzzle. What should > be returned by this: > > SELECT '500'=500; > > Is the result "0" or "1"? In other words, what happens when > you compare a number to a string that looks like that number. > Are they equal or not? > > Other languages return a mixture of results here. Strings > and number compare equal in AWK, Perl, PHP, Tcl, and SQLite > version 2. String and numbers are not equal in Python and Ruby. > > Based on my experience, I would choose to make strings and > numbers equal. But there are complications to that approach > in SQLite 3.0. SQLite 3 supports manifest typing with 4 > basic types: NULL, NUMERIC, TEXT, and BLOB. Objects sort in > that order: NULLs first, followed by NUMERICs in numerical > order, then TEXT in a user-defined collating sequence and > finally BLOBs in memcmp() order. So '500' occurs at a > completely different place in the sort order from 500. If > comparison operators are to be consistent with sort order, > the following must be true: > > 500 < 600 > 600 < '500' > > But if that is the case, then clearly, '500' != 500. So > unless somebody can come up with a better idea, SQLite > version 3.0 will return "0" for the following: > > SELECT '500'=500; > > On the other hand, the following two statements will return > "1" (or true): > > SELECT '500'+0=500; > SELECT '500'=(500||''); > > Note that in other SQL engines, it is an error to compare > a string to an integer (I think - somebody please correct > me if I am wrong) so we cannot get any guidance there. > > Your thoughts? > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] execution order of Update
As a side note, I thought that the was one of the nice things about SQL in general, that there was no real strong ordering requirement with respect to statements. That was why T-SQL and some of the others introduced more procedural constructs. Is this wrong? --Keith ++ [EMAIL PROTECTED] Lead Speech Recognition Engineer Toll-Free: (877) 977 - 0707 Phone: (858) 707 - 0707 x238 www.LumenVox.com Fax: (858) 707 - 7072 LumenVox, LLC 3615 Kearny Villa Rd #202 San Diego, CA 92123 ++ > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 12, 2004 5:19 AM > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] execution order of Update > > > Rubens Jr. wrote: > > > > Is the update command executed in same order that was writen in the > > sql command ? example : > > UPDATE t1 SET f1 = f2, f2 = '' WHERE > > Is garanted that with this command f1 will have the value > of f2 BEFORE f2 > > receive value xxx ? > > I need to save the value of f2 than update f2, but > > I'm not shure that this may be done with only one sql command ... > > > > The right-hand side of every term is evaluated first, before > any changes are made. So you can say this: > > UPDATE t1 SET f1=f2, f2=f1; > > And it will swap the values of F1 and F2. > > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQL Syntax checker?
Doesn't sqlite have a function for doing this? It only checks SQLite-92 stuff, I think. You could always roll one from the spec using (f)lex and yacc. Or bison, or . A quick google showed different parsers in Java, for Delphi and FoxPro, etc. --Keith ++ [EMAIL PROTECTED] Lead Speech Recognition Engineer Toll-Free: (877) 977 - 0707 Phone: (858) 707 - 0707 x238 www.LumenVox.com Fax: (858) 707 - 7072 LumenVox, LLC 3615 Kearny Villa Rd #202 San Diego, CA 92123 ++ > -Original Message- > From: Dan Keeley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 05, 2004 2:36 AM > To: [EMAIL PROTECTED] > Subject: [sqlite] SQL Syntax checker? > > > Hi, > > Not directly sqlite related, but does anyone know of a SQL > syntax checker > which will check syntax without actually connecting to a db > and running the > query? > > Thanks! > Dan > > _ > Express yourself with cool new emoticons > http://www.msn.co.uk/specials/myemo > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Setting Autoincrement
Hi folks; is there a way to set an INTEGER PRIMARY KEY to a predetermined value, so that all subsequent inserts have the correct autoincrement value? I have an in-memory db that periodically writes to a disk db (there are good reasons for this). Later, I may need a connection to that disk db via another memory db, but any inserts from that connection need to take place at the appropriate autoincrement value. So, suppose I last wrote to the disk db and ended at autoincrement = 514 . Later, I reconnect, but need the new in-memory db to start incrementing inserts at 515, 516, etc., so that the eventual write to the disk db is synchronized correctly (I will not be hitting the upper limit on rows, so I am not worrying about reusing autoincrements). I can, of course, just do the autoincrement myself, but I would prefer to let SQLite continue to handle that. Any ideas? --Keith - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Question about in memory db's
Well that's kinda nifty! Does the ATTACH lock the external file for as long as it is ATTACHed or only when transactions are active? If it is only locked during updates, this is probably the way I will do it, since I can sacrifice a little bit of time for periodic writes to the DB file. --Keith > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Friday, March 12, 2004 4:37 PM > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] Question about in memory db's > > > Keith Herold wrote: > > Is there a fast way to dump the in-memory db to disk, > *and* periodically > reload the file into an in-memory db? > > > The ATTACH command can be used for this. > > Suppose you do an sqlite_open() on the in-memory database > and you want to transfer the complete contents of an > in-memory table XYZ to an on-disk database named "abc.db" > You can do something like this: > > ATTACH 'abc.db' AS external; > BEGIN; > DELETE FROM external.xyz; > INSERT INTO external.xyz SELECT * FROM xyz; > COMMIT; > DETACH external; > > The same technique works in reverse to load an in-memory > table from disk: > > ATTACH 'abc.db' AS external; > BEGIN; > DELETE FROM xyz; > INSERT INTO xyz SELECT * FROM external.xyz; > COMMIT; > DETACH external; > > Use WHERE clauses creatively if you only want to transfer > part of the data. > > Note that it is not necessary to ATTACH and DETACH every > time you want to do this. If you are always using the > same external database file, you can just ATTACH it once > when you open the in-memory database and it will always > be there for you. > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Question about in memory db's
I am considering using SQLite as the 'file-based' version to the back-end of one of our products. I have been doing some testing, and the only way to allow read and write access at acceptable speeds seems to be to lock the db file for all the updates, allowing no reads through. Unfortunately, the application is GUI-based, and I have to service the read requests in a reasonable amount of time. I was experimenting with SQLite's in-memory database, which is much faster on updating than the db file, but still suffers from lag on reads. One approach is to use the in-memory database for updates, and periodically dump the in-memory db to a file, so that reads can use the current file version (there will be a difference between in-memory and file, but for the application it's ok). Is there a fast way to dump the in-memory db to disk, *and* periodically reload the file into an in-memory db? --Keith - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
