[sqlite] SQLite version 3.7.6
SQLite version 3.7.6 is not available from the website: http://www.sqlite.org/ Version 3.7.6 is a regularly scheduled bi-monthly maintenance release. Updating from version 3.7.5 is optional. Updating from versions prior to 3.7.5 is recommended. A summary of changes in SQLite version 3.7.6 can be seen at http://www.sqlite.org/releaselog/3_7_6.html Please send email to the sqlite-users@sqlite.org mailing list, or directly to me, if you encounter any problems. Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.6
On Apr 12, 2011, at 8:49 AM, D. Richard Hipp wrote: > SQLite version 3.7.6 is not available from the website: > http://www.sqlite.org/ The "not" in the sentence above should be "now", of course. Sorry for the typo. > > Version 3.7.6 is a regularly scheduled bi-monthly maintenance release. > Updating from version 3.7.5 is optional. Updating from versions prior to > 3.7.5 is recommended. > > A summary of changes in SQLite version 3.7.6 can be seen at > >http://www.sqlite.org/releaselog/3_7_6.html > > Please send email to the sqlite-users@sqlite.org mailing list, or directly to > me, if you encounter any problems. Thanks. > > D. Richard Hipp > d...@hwaci.com > > > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.6.3
SQLite version 3.7.6.3 is now available on the SQLite website http://www.sqlite.org/ http://www.sqlite.org/download.html Version 3.7.6.3 is a patch release that fixes an obscure but nasty bug in WAL-mode. Upgrading is recommended for all users. The bug is present in all prior releases of SQLite that support WAL. In prior releases of SQLite, if you set PRAGMA journal_mode=WAL, and if you set PRAGMA cache_size=N where N is very small (less than 10) and if you do a multi-statement transaction where the last SQLite statement prior to COMMIT is a SELECT statement that requires all of your cache memory to complete, then your COMMIT might be silently converted into a ROLLBACK. The database does not corrupt, but any changes you made to the database during the transaction will be lost. Since cache_size defaults to 2000, applications that never mess with cache_size (which is to say, the vast majority of applications) should never have a problem. But sometimes developers working on low-memory devices try to crank down cache_size in an effort to save memory. If you are one of those developers, you should probably think seriously about upgrading. Additional information: http://www.sqlite.org/src/info/2d1a5c67df http://www.sqlite.org/news.html The patch needed to fix this problem in any 3.7.x release of SQLite can be seen here: http://www.sqlite.org/src/fdiff?v1=b7fe4b8e51d51a06=4b2358556c88660a Please respond to the sqlite-users mailing list (sqlite-users@sqlite.org) or directly to me if you encounter any problems. Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.7 released
SQLite version 3.7.7 is now available on the SQLite website: http://www.sqlite.org/ A list of changes is available at http://www.sqlite.org/releaselog/3_7_7.html Further information about this release can be seen at http://www.sqlite.org/news.html Please post on the SQLite mailing list (sqlite-users@sqlite.org) if you encounter any problems with this release. -- D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.9
SQLite version 3.7.9 is now available on the primary and on the backup websites: http://www.sqlite.org/ http://www2.sqlite.org/ http://www3.sqlite.org/ Version 3.7.9 is a periodic maintenance release. Upgrading from versions 3.7.6.3 and later is optional. Upgrading from prior versions is recommended. Additional information about this release can be found at: http://www.sqlite.org/releaselog/3_7_9.html http://www.sqlite.org/news.html As always, please let me know if you encounter any difficulty with this release. D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] version 3.5.0 - Segv
On Sep 2, 2007, at 10:18 AM, Christian Smith wrote: Is it not worth simply making the library threadsafe by default? There is basically no platform supported today that doesn't have some form of thread abstraction, the overhead of mutex locking is probably negligible, See ticket #2606. http://www.sqlite.org/cvstrac/tktview?tn=2606 In our tests, the overhead of mutexing is not negligible. It slows down the database by about 8%. Nevertheless, we recognize that many people want to run multiple threads (despite my heartfelt pleas to abstain from that dreadful practice) so we probably will make the prebuilt libraries threadsafe on all platforms. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple connections
On Sep 2, 2007, at 11:40 AM, Joe Wilson wrote: In sqlite 3.5 they've changed the design to share a single file descriptor for all connections to the same database. Also, connections to the same database in 3.5+ will share the same database page cache resulting in less overall memory usage. I didn't mean to mislead you, Joe. In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite 3.4.2 and VC++ : lib size too big
On Sep 2, 2007, at 1:13 PM, Miguel Fuentes wrote: I didn't know .lib were much larger =\ I always use .a files, so my bad I just linked it into my exe and got a final 420kb exe. See also http://www.sqlite.org/cvstrac/wiki?p=SizeOfSqlite D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Q: Export Control Classification Number?
On Sep 9, 2007, at 8:07 PM, Coatimundi wrote: My US-based employer is considering using SQLite in a closed, commercial product for which an export market is anticipated to exist. Has the US DoC/BIS made an official classification of any release of the SQLite library as distributed at www.sqlite.org? If so, has the classification been publicly disclosed? The BigNameUsers page [ http://www.sqlite.org/cvstrac/wiki? p=BigNameUsers ] notes that Toshiba requested an ECCN for SQLite. The same note, perhaps coyly, does not provide an answer... GE also has requested an ECCN, not once but twice. So presumably two completely separate organizations within GE are exporting SQLite in some shape or fashion. I forget the "official" wording, but SQLite is an item that is not export controlled. At least as long as you don't purchase the proprietary encryption extension. So it doesn't have an ECCN. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite insertion performance
I have been struggling with the performance of insertion in sqlite. Here we have a very simple case : A table with an integer autoincrement primary key and a text field that is unique. CREATE TABLE my (id PRIMARY KEY, url); CREATE UNIQUE INDEX myurl ON my(url); My application requires inserting up to 10 million records in batches of 20 thousand records. For each group of 2 records, first insert them into a TEMP table. Call the temp table t1. Then transfer the records to the main table as follows: INSERT OR IGNORE INTO my(url) SELECT url FROM t1 ORDER BY url; D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: Yes, I am well aware of this possibility as I've written in my initial mail. It just doesn't fit with the description of sqlite3_last_insert_rowid() in my understanding. I think this is a bug - either in the documentation or in the implementation. sqlite3_last_insert_rowid() should return the correct id, no matter what and it doesn't. Consider this scenario: CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO ex1 VALUES(1,1,1); INSERT INTO ex1 VALUES(2,2,2); INSERT INTO ex1 VALUES(3,3,3); Now you do your INSERT OR IGNORE: INSERT OR IGNORE INTO ex1 VALUES(1,2,3); Three different constraints fail, one for each of three different rows. So if sqlite3_last_insert_rowid() were to operate as you suggest and return the rowid of the failed insert, when rowid would it return? 1, 2, or 3? D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How many virtual table implemenations are there out there?
The current virtual-table implementation does not work when you have shared cache mode enabled. We would like to fix this so that that you can (for example) use FTS and shared cache at the same time. But to do so seems likely to require an incompatible change to the virtual-table interface. The virtual-table interface is currently listed as "experimental" and does not appear in the "official" documentation in capi3ref.html. So we are free to change it if we need to. But I wonder how many people this would inconvenience. If you have or know of a virtual table implementation (other than the ones that are included with SQLite - such as FTS1-3) that will be broken by an API change, please let me know. And please also advice me how much of a hardship a change would be for you. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
On Nov 13, 2007, at 10:37 PM, Richard Klein wrote: [EMAIL PROTECTED] wrote: What? And encourage people to write multitheaded programs? Not likely... I've been meaning to ask ... When you say that multiple threads are evil, do you mean "as opposed to multiple processes"? Or do you feel that multiprogramming in general is evil? Threads are (usually) fine as long as each thread has its own address space that the other threads cannot mess with. In other words, I have no issues with separate processes provided that separate processes really are needed. For example, it is often a good idea to run your GUI in a separate process from your compute engine so that long computations don't free the display. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Request for help with the SQLite Website
On Nov 13, 2007, at 10:55 PM, Joe Wilson wrote: http://home.pacbell.net/ouster/threads.pdf JO and I reach a similar conclusion but by different reasoning, I think. --- Richard Klein <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] wrote: What? And encourage people to write multitheaded programs? Not likely... I've been meaning to ask ... When you say that multiple threads are evil, do you mean "as opposed to multiple processes"? Or do you feel that multiprogramming in general is evil? - Richard Klein __ __ Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite and lemon operator precedence problem/question
On Nov 17, 2007, at 4:58 PM, Joe Wilson wrote: I'm having difficulty with Lemon's operator precedence. Given SQLite's operator precedence table where it's presumably interpreted with lowest precedence tokens at the top to the highest precedence tokens at the bottom: %left OR. %left AND. %right NOT. %left IS MATCH LIKE_KW BETWEEN IN ISNULL NOTNULL NE EQ. %left GT LE LT GE. %right ESCAPE. %left BITAND BITOR LSHIFT RSHIFT. %left PLUS MINUS. %left STAR SLASH REM. %left CONCAT. %left COLLATE. %right UMINUS UPLUS BITNOT. Why doesn't the BITNOT operator '~' have the highest precedence? SQLite version 3.5.2 Enter ".help" for instructions sqlite> select ~1 - ~5; -8 sqlite> select (~1) - (~5); 4 That would be a bug in lemon... D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite and lemon operator precedence problem/question
On Nov 17, 2007, at 5:12 PM, D. Richard Hipp wrote: On Nov 17, 2007, at 4:58 PM, Joe Wilson wrote: I'm having difficulty with Lemon's operator precedence. That would be a bug in lemon... I was wrong. Turns out the bug was in the SQLite grammar file parse.y. It was assigning the same precedence to the ones-complement ~ operator and the NOT operator. But ~ should have higher precedence, it seems. Fixed by check-in [4548]. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite and lemon operator precedence problem/question
On Nov 17, 2007, at 5:30 PM, Joe Wilson wrote: sqlite> select ~1 - ~5; -8 sqlite> select (~1) - (~5); 4 That would be a bug in lemon... I guess adopting the same operator precedence as MySQL or MS SQL Server is out of the question? I believe SQLite uses the same operator precedence as the SQL standard requires. If I am wrong about that, please correct me and I will change it. On the other hand, changing the operator precedence to agree with MySQL or MSSQL is not something we are interested in doing if they are using a non-standard precedence. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory Usage
On Nov 18, 2007, at 8:12 AM, Russell Leighton wrote: On Nov 17, 2007, at 4:56 PM, [EMAIL PROTECTED] wrote: If you compile with -DSQLITE_MEMORY_SIZE= then SQLite will *never* call malloc(). Instead, it uses a static array that is bytes in size for all of its memory needs. You can get by with as little as 100K or so of memory, though the more memory you provide, the faster it will run. 5MB is a good value. Does using this setting (and eliminating malloc/free overhead) result in a significant performance increase? That depends on how good of a malloc you have on your system. On Linux systems that typically use Doug Lea's malloc, there is no measurable performance difference. But I have had some people running embedded systems tell me that using the malloc-free SQLite results in a significant speed boost. Your mileage may vary. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory Usage
On Nov 19, 2007, at 12:36 PM, James Dennett wrote: -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, November 19, 2007 7:36 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Memory Usage Not only applicable to real time systems. If you want a program to run with stability over a long time the first step it to eliminate frees and if malloc is used confine it to the intialization. I have to challenge this, not because it's entirely wrong (it's not), but because it's an over-simplification. I've worked with a lot of software that runs for years, and uses dynamic allocation without problems. One of the keys to writing stable/robust software is avoiding complexity, and *appropriate* use of dynamic allocation can help with that in some situations. There is, of course, a vast range of environments in which software must run for years at a time, and the appropriate implementation techniques vary. A pacemaker doesn't have the same constraints as a telephone switch, or a satellite, or a set-top box or mobile phone, and even within each of those categories there are wide ranges. It's certainly nice that SQLite offers the flexibility to manage its own fixed-size memory pool, but this certainly is not the only way to produce stable, robust, long-running software. Our studies to date indicate that SQLite neither leaks nor fragments memory. Preventing leaks is relatively easy. Preventing memory fragmentation less so. Yet we are not seeing memory fragmentation as a problem for the workloads we have tested. Nevertheless, we cannot *prove* that SQLite, in its current form, will never fragment memory. However, we are working toward a future release where such a proof will be possible, at least for certain well-defined operating parameters. We just are not quite there yet. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Threading (again)
On Nov 25, 2007, at 2:21 PM, Roger Binns wrote: I was under the impression that SQLite 3.5.2 is completely threadsafe, meaning you can make any relevant API call in any thread. Examples of things I thought are safe are: - - Using statements from the same connection in different threads - - Calling step on a statement in one thread and then calling it again in another thread - - Doing some blob i/o in one thread and then doing more in another The FAQ currently says it is somewhat safe. http://www.sqlite.org/faq.html#q6 Is the FAQ out of date? SQLite is completely threadsafe in 3.5.2. But that does not mean that running SQLite will magically fix threading bugs in Linux 2.4 kernels or in GLIBC. The FAQ warns you to beware these problems. By "threadsafe" in 3.5.2, that means you can call SQLite simultaneously from different threads using the same database connection. SQLite contains its own mutexes to serialize access. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] 3.5.x and pthreads design
On Nov 25, 2007, at 5:34 PM, Teufel wrote: Hi everyone, I am using sqlite 3.3 awhile now for some statistic updates in multithreaded enviroment. Now I would like to move on to 3.5.2 use it more for other tasks too. Since the sqlite db is placed here on a fs, which has a broken fcntl, I disabled it by putting "#define fcntl (A,B,C) 0". As I only use it within the same process with couple of threads, I am doing currently the locking with pthread mutexes like: each thread { /* worker stuff */ ... if new stats did arrive: compete on mutex -> when lock is acquired, do open database read something (eg sqlite_exec "select ") if does not exist, do write (sqlite_exec "insert into table...") close database-> release lock } Of course, this is very simple and serializing everything including open/close of the db image. My question is now, what could I improve with 3.5.x now as it has buildin thread safety for the same database (as mentioned in 34to35) but I did not find how this thread safety is made (using mutexes, rwlock? still fcntl?) I cannot rely on fcntl, so for thread-safety, it's all up to pthread mutexes/rwlock For threads within the same process, fcntl is broken by design in POSIX. (You can clearly tell which parts of Unix were invented by Thompson and Richie and which parts were added later by clueless committees. Posix advisory locks belong in the latter category.) SQLite contains a work-around to this problem based on pthreads locks. It should be able to open multiple connections to the same database within the same process and use them independently and locking should work correctly. You should not have to change anything. It should just work. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ORDER BY Performance on 30,000 records
On Dec 2, 2007, at 12:01 PM, Ofir Neuman wrote: Hi All, I have some performance problem when adding ORDER BY to my query, hope you can help me speed things up. This is my table: TABLE1 { ID TEXT ParentID TEXT ModifiedDate INTEGER } ID is the PK of the table and i also have an index on ParentID. Drop the index on ParentID and replace it with this: CREATE INDEX idx2 ON table1(ParentID, ModifiedDate, ID); Then queries of the form SELECT id FROM table1 WHERE parentid=? ORDER BY modifieddate; will be very fast. Current number of records in table: 40,000 My query is very simple: SELECT ID FROM Table1 WHERE ParentID = '{---}' ORDER BY ModifiedDate According to the data in my table this query should return 30,000 records. While using ORDER BY it takes 3-4 SEC to retrieve the query result, without the ORDER BY it take something like 30 ms. Tried to index also 'ModifiedDate' but it didn't help. What am i doing wrong? Thanks, Ofir Neuman. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On Dec 7, 2007, at 11:47 PM, Joe Wilson wrote: I believe it makes compound query behavior more compatible with other popular databases. It is mostly backwards compatible with the previous syntax and only 2 tests performed by "make test" had to be altered. Compatibility with other databases is good. But backwards compatibility with the thousands and thousands of existing SQLite applications is more important. The current behavior of SQLite regarding the column names in compound SELECTs may be boneheaded and broken. But it does at least have the virtue of being backwards compatible with all prior releases of SQLite. So a project can update the version of SQLite they are using to the latest code out of CVS and be reasonable confident that it will still work. If I put in this patch, that confidence is diminished somewhat. Hence I am moving very slowly and cautiously here. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Huge performance drop when using prepared statement
On Dec 9, 2007, at 5:27 AM, Kees Nuyt wrote: Problematic SELECT: c.execute("""SELECT * FROM entry, word, word_entry WHERE entry.id = word_entry.entry_id AND word.id = word_entry.word_id AND word.word GLOB ? """, ('hui*',)) SQLite will optimize a GLOB where the right parameter is a literal string. It will not do so if the right parameter is a parameter. http://www.sqlite.org/optoverview.html#like_opt D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?
On Dec 14, 2007, at 9:24 PM, Lynn Fredricks wrote: That's true. A lot of those kinds of sales presentations are correctly targeted at decision makers that make financial decisions. I don't consider it a bad thing - it's really a necessity to be competitive. My intent is to provide complete detailed technical information about SQLite, including its limitations and faults, and honest comparisons and even recommendations of other products (including, but not limited to DeviceSQL). My intent is to avoid sophistry, misrepresentation, exaggeration, and hype. This intent is sometimes imperfectly executed, but it is my goal. If that means that SQLite is uncompetitive, then so be it. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
On Jan 12, 2008, at 7:55 PM, Shawn Wilsher wrote: Hey all, I was wondering when you plan on releasing the next version of SQLite. Mozilla is currently using 3.5.4, but that does not include some OS/2 fixes that were checked in after the release of 3.5.4. Instead of patching our local copy of sqlite, I'd like to use a release version, but at the same time do not want to delay this fix to our OS/2 users very long. The specific checkins we are looking at are 4646, 4647, and 4648. In case you haven't been watching the timeline (http://www.sqlite.org/cvstrac/timeline) we are in the middle of some major changes. The virtual machine inside of SQLite is being transformed from a stack-based machine into a register-based machine. The whole virtual machine and the code generator is being rewritten. Slowly. Piece by piece. I haven't done an overall line change count yet, but we are looking at some pretty serious code churn. 3.5.4 to 3.5.5 is likely to be the biggest single change in the history of SQLite. So you might not want to release product with 3.5.5 embedded. All the regression tests pass, but still If you like, we can set up a special Mozilla branch off of 3.5.4 that includes the OS/2 fixes. On the other hand, if this is not for a release, but rather for general development work, then please build and test with the latest code from CVS. (This applies to *everybody* not just Mozilla.) Please report any problems. The test suite for SQLite is very thorough, but I have found that users can be very creative in stressing SQLite in ways that I would have never imagined, and have not developed tests for. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
On Jan 13, 2008, at 11:31 AM, Rich Shepard wrote: On Sun, 13 Jan 2008, Darren Duncan wrote: I would think something like that is worthy of a 3.6.0 version number. Not just a minor version increase that would be more suitable for minor changes or bugfixes. I agree with Darren that massive changes to the core of the system should be reflected by a major version increase (to 4.0); at a minimum to a minor version increease (to 3.6). A version number change from 3.5.4 to 3.5.5 tells folks that it's a minor bug fix or simple adjustment, not a wholesale rewrite of the system's core. There are no user-visible changes to the interface. The version numbers in SQLite reflect user-visible changes only. Well, there is one minor user-visible change. The output of EXPLAIN now has 7 columns whereas it used to contain only 5. But the output of EXPLAIN changes from point release to point release all the time anyway, so I do not consider this something worth bumping a version number. I do not expect significant instability with the next release. I want to gain some experience with the new software before I recommend it for millions of deployments. But it should be solid and stable as soon as it is released. For that matter, the current code in CVS (which is well into the conversion to a register machine) has not been giving any problems. There are people on this mailing list (ex: Joe Wilson) who appear to read every line of every change that we make to SQLite, within minutes of making them, and complain if we so much as misspell a word in a comment. And I haven't heard a peep from Joe or anybody else, so I'm thinking the code is still working correctly for everybody despite the massive changes that have already gone in. If you find that the current code in CVS gives problems, or if you see significant problems emerge as we get closer to releasing 3.5.5, then maybe we might consider calling it 3.6.0. But I do not anticipate any serious problems. You should not underestimate the level of detail to which we test SQLite and the thoroughness of the test suite. Not much is likely to slip through the cracks. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
On Jan 13, 2008, at 11:40 AM, Marco Bambini wrote: What will be the main benefits of the new virtual machine? Optimizations such as common subexpression elimination and moving subexpressions outside of inner loops will become much easier. The code generator will, in general, be easier to work on and less error prone. An entire class of errors (stack overflow) such as the recent ticket #2832 (which could cause database corruption) will become impossible since the VM will no longer have a stack to overflow. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Next Version of SQLite
On Jan 13, 2008, at 7:53 PM, Gerry Snyder wrote: Joe Wilson wrote: --- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: There are people on this mailing list (ex: Joe Wilson) who appear to read every line of every change that we make to SQLite, within minutes of making them, and complain if we so much as misspell a word in a comment. And I haven't heard a peep from Joe Wow - what prompted that dig against me? I took it as a kidding compliment. I saw an invisible smiley after his comment. Your contributions to SQLite are appreciated by all of us. Exactly. My remark was meant to indicate that I am amazed at how much attention you pay to the code, not any kind of a dig. Sorry for the confusion. You are an important member of the quality assurance team and I hope that you will continue in that role. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Variable substitution (TCL & SQLite)
On Jan 17, 2008, at 10:23 PM, Zbigniew Baniewski wrote: I'm choosing desired column names dynamically, then store all the names in one variable, something like this... set columns "column1, column2, column3" The names are chosen in much more complicated way, but the above is just a variable contents example. I'm trying then to fetch the data like this: set data [dbcomm eval {SELECT $columns FROM some_table}] ...but it doesn't work. The rules of TCL parsing are that text within {...} gets passed into its command exactly as written with the outermost {...} removed. So the command that is running is: command-name: dbcomm 1st-argument: eval 2nd-argument: SELECT $columns FROM some_table In other words, the $columns was *not* expanded by TCL. It got passed down into SQLite. SQLite sees the $columns and thinks you are dealing with an application variable. Just like a "?" or a ":abc" or "@xyz". Sqlite3_prepare() runs and treats the $columns token as it would any other SQL variable. After the statement is prepared. TCL asks the statement: "What application variables do you have, and what are their names?" The statement tells TCL that it has a variable named "$columns". TCL says "I have a variable by that name", and so then TCL then calls sqlite3_bind_text() to stick the value of the $columns TCL variable into the SQLite variable. TCL then calls sqlite3_step() to run the statement. So, even though $columns looks something like a TCL variable, it is really an SQLite variable. You can change the value of an SQLite variable by binding all you want and it is not going to cause the statement to be reparsed. This is a feature, not a bug - it prevents SQL injection attacks. Notice that the $columns token is an SQLite variable because the {...} prevented TCL from expanding the text within the {...} and thus caused the original $columns text, not the expansion of the value of $columns, to be passed down into SQLite. This is very important. This is the essence of TCL. This is the part of TCL that people who have difficulty with TCL don't understand. The rules of TCL are very, very simple, but they are also different from the rules of Algol-derived languages like C++ or Python and that difference confuses many people. TCL is much closer to Lisp. Make sure you understand this before going on. Now, suppose you use "..." instead of {...} in the original statement: dbcomm eval "SELECT $columns FROM some_table" The rules of TCL are that text within "..." is treated as a single token, but unlike {...} the text within "..." undergoes variable expansion and [...] substatement evaluation before being passed into the command. So the command that gets run is this: command-name: dbcomm 1st-argument: eval 2nd-argument: SELECT column1, column2, column3 FROM some_table The second argument gets passed to sqlite3_prepare(). This causes the statement to be prepared as you want it to be. There are no SQLite variables in this case. The $columns has been interpreted and expanded by TCL before the statement is ever sent into SQLite. You should be very careful using "..." instead of {...} in this context. If a user can control the content of $columns, then the user might be able to do something equivalent to: set columns {null; DELETE FROM critical_table; SELECT null} The result would be a classic SQL injection attach. The use of {...} is preferred for this reason. But sometimes, when you want the text of your SQL statement to be under program control, you want to use "..." instead. Just be very sure you know exactly what you are doing whenever you use "..." D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Foreign Constraint Triggers Across Attached Databases
On Jan 23, 2008, at 10:07 PM, [EMAIL PROTECTED] wrote: I have been trying to implement the paradigm of using Triggers to emulate referential integrity, for example cascading updates and deletes between two database tables. This works when the two database tables are in “main” but when I try to create the triggers between database tables in attached database tables, the create doesn’t work. Tried several iterations and couldn’t come up with the proper SQL syntax to do this. Is there a way to do this, add referential integrity triggers with database tables in attached databases? Even better, if/when is SQLite going to support built-in referential integrity using foreign key constraints in the SQL when creating the tables? Can it support referential integrity with attached database tables? Triggers between two separate databases are not allowed since if you DETACH one of the databases, the triggers obviously will no longer work. If two separate databases are so inseparably bound that they need triggers between them, why not just make them a single database? The same goes for foreign key constraints. There are *severe* implementation difficulties trying to get this to work across separate database. If you have a foreign key in a separate database, that really argues that the two databases ought to be one. Example use case: “main” containing an “AccountTable”, and a daily transactional table “-MM-DD.db3” that will be attached to “main” that contains a TransactionTable that has a “foreign key relation” to the “AccountTable” by having an account primary key as a foreign key in the transaction table. -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Testing the new SQLite mailing list
This is the initial test message for the new SQLite mailing list, using GNU mailman now instead of ezmlm. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes?
On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote: > I've looked high and low and can't find a way to invoke the other 2 > affinity modes. Are they available? I'm on 3.5.4. > > The concept of "strict" affinity mode was briefly discussed years ago, but we never implemented it, having never seen any benefit for such a thing. Can you explain why you think strict affinity mode might be beneficial to you? If somebody can provide a good enough rational to justify strict affinity mode, we might just put it in. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to compile RTREE using eclipse on windows
The following message is forwarded from private email. (I know nothing about eclipse or windows so I can't help.) Please reply to this list with CC to k52...@hanmail.net if you have any suggestions. > Hello > I am a student studying in south Korea about sqlite. > I have sent the e-mail to you before to ask something about rtree. > But I have not solve the problem yet, so I send the e-mail one more > time. > > I downloaded sqlite-3.6.23.1.tar.gz file and then I execute > CONFIGURE and MAKE in linux. > After that, I compiled with config.h, parse.h, parse.c, opcode.h, > opcode.c, sqlite3.h, keywordhash.h file and Sqlite-3.6.3.23.1’s > other source file in windows eclipse environment. > > > > I except a few files like fts1…fts3 which make some error. > In this situation, compile is ok. Works well. > However.. > > > > It doesn’t make error to compile giving option ,– > DSQLITE_ENABLE_RTREE=1, like this one, > But it brings this problem. > > > > Sq.exe’s operation is stopped > you can find the way to solve this problem though on-line. > Close the program after checking solving method though on-line > Close the program > > The program is closed by making table. > When I make Rtree table also I can see the same problem.(the program > is closed) > > I can not use the amalgamation vision to make Sqlite program. > > Please let me know how to compile in window’s eclipse environment > with Sqlite-3.6.23.1.tar.gz file > Thank you for giving many information. Thank you. > D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite turns 10 years old
The first code check-in for SQLite occurred on 2000-05-29 14:26 UTC - ten years ago today. http://www.sqlite.org/src/timeline?c=2000-05-29+14:26 Some of the code in SQLite (such as the Lemon parser generator and the printf implementation) dates back to the late 1980s. But the core of SQLite was not started until 10 years ago. Ten years is not that long ago, though it has been long enough to amass 7114 check-ins - an average of 2.1 check-ins per day. If you are overseeing such a project, 10 years seems like forever. It has hard for me to remember a time when I wasn't working on SQLite. In celebration of SQlite's 10th birthday, we are revamping the look of the SQLite website. You can see a preview of the new look at http://www.sqlite.org/draft/index.html We won't push the new look out to the main website until we do the next release which might not be until July or maybe even August. We had hoped to have SQLite version 3.7.0 ready in time for the 10th birthday celebration, but http://www.sqlite.org/draft/wal.html is taking longer than planned. We want to make sure to get things right so that SQLite lives to see its 20th and 30th birthdays! Thanks, everybody, for helping to make SQLite the most widely deployed SQL database engine in the world. And Happy 10th Birthday to SQLite! D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-announce] HELP : sqlite execute low speed in ARM9+Linux embadded system.
Questions such as this belong on sqlite-users@sqlite.org, not on sqlite-annou...@sqlite.org . Thank you. On Jun 16, 2010, at 11:24 PM, backup wrote: > hi, everyone, > I program in an embaded system: ARM9 soc S3C2410 +Linux 2.4+sqlite > 3.3. > All data store in NAND FLASH , the file system is YAFFS . > My question is the function "sqlite3_exec(.)" take too many > seconds, > the following is my source code, please see the "printf" 's comment, > every "sqlite3_exec(.)" takes 5 seconds, but how to reduce > the time? > > //-- start of code > #define RECORD_NUM 1 > > char QueryData(void) > { > struct timeval tpstart,tpend; > float timeuse; > > char ErrorFlag=1; > unsigned int di,dj,dk; > DataBuffer[0]='\0'; //DataBuffer is goblal variable > sprintf(sqlstr,"select * from db where isSent=0 limit > %d;",RECORD_NUM); > p(semid); > > gettimeofday(,NULL); > rc = sqlite3_exec(db, sqlstr, SQLCallBack, 0, ); > gettimeofday(,NULL); > timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+ > tpend.tv_usec-tpstart.tv_usec; > timeuse/=100; > printf("sqlite3 select * Used Time:%f\n",timeuse); // print value :5 > seconds > > > v(semid); > > sprintf(sqlstr,"update db set isSent=1 where ID in (select ID from > db where isSent=0 limit %d);",RECORD_NUM); > p(semid); > gettimeofday(,NULL); > > > rc = sqlite3_exec(db, sqlstr, 0, 0, ); > > gettimeofday(,NULL); > timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+ > tpend.tv_usec-tpstart.tv_usec; > timeuse/=100; > printf("sqlite3 update Used Time:%f\n",timeuse); //print value : 5s > > > > v(semid); > } > static int SQLCallBack(void *NotUsed, int argc, char **argv, char > **azColName) > { > > struct timeval tpstart,tpend; > float timeuse; > > > int i; > char tstr[500]; > gettimeofday(,NULL); > > // argv[0] is ID ,no use for server > sprintf(tstr,"'%s',",argv[1]); > strcat(DataBuffer,tstr); > for(i=2; i<argc; i++){ > // printf("%s,",argv[i] ? argv[i] : "N"); > sprintf(tstr,"%s,",argv[i]); > strcat(DataBuffer,tstr); > } > strcat(DataBuffer,"\n"); > > gettimeofday(,NULL); > timeuse=100*(tpend.tv_sec-tpstart.tv_sec)+ > tpend.tv_usec-tpstart.tv_usec; > timeuse/=100; > printf("SQLCallBack Used Time:%f\n",timeuse); // print value : > 0.000280s > > return 0; > } > > //-- end of code > > > 网易为中小企业免费提供企业邮箱(自主域名) > ___ > sqlite-announce mailing list > sqlite-annou...@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Oracle joins the SQLite Consortium
The SQLite developers are pleased to announce that Oracle has joined the SQLite Consortium. The SQLite Consortium is a collaboration of major users of SQLite designed to ensure the continuing vitality and independence of SQLite. In exchange for sponsorship, SQLite Consortium Members receive enterprise-level technical support, access to proprietary SQLite add-ons such as the SQLite Encryption Extension and TH3, and guarantees that SQLite will continue to be actively maintained and developed and that it will not fall under the control of a competitor. Oracle uses the parser, code generator, and virtual machine from SQLite in its Berkeley DB product. Additional information about Berkeley DB's SQL API is available at http://www.oracle.com/technology/products/berkeley-db/sql.html D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: CRITICAL bug in sqlite3VdbeExec() code
-- Forwarded message -- From: jur...@ramzes.net To: sqlite-users@sqlite.org Date: Thu, 24 Jun 2010 11:15:20 +0200 Subject: CRITICAL bug in sqlite3VdbeExec() code Dear friends, This is my bug report. sqlite version: 3.6.23.1, Check-in [2e6a462ceb] file: vdbe.c line: 971 function: sqlite3VdbeExec() description: Local variable "pOut is not properly initialized in some cases. For example: ... /* Opcode: Variable P1 P2 * P4 * ** ** Transfer the values of bound parameter P1 into register P2 ** ** If the parameter is named, then its name appears in P4 and P3==1. ** The P4 value is used by sqlite3_bind_parameter_name(). */ case OP_Variable: {/* out2-prerelease */ Mem *pVar; /* Value being transferred */ assert( pOp->p1>0 && pOp->p1<=p->nVar ); pVar = >aVar[pOp->p1 - 1]; if( sqlite3VdbeMemTooBig(pVar) ){ goto too_big; } sqlite3VdbeMemShallowCopy(pOut, pVar, MEM_Static); <<<--- *** HERE*** UPDATE_MAX_BLOBSIZE(pOut); break; } ... Function sqlite3VdbeMemShallowCopy() is called with pOut == NULL, of course with access violation (in Windows version). "case OP_Variable" I have checked under M$ debugger (not coverable errors in my PHP/PDO-Sqlite script with parameters binding), but possible other cases in massive switch statement, I think ... greetings from Poland, JureKL. --- End forwarded message - D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Development snapshots for SQLite 3.7.0 available
On the download page of the draft website: http://www.sqlite.org/draft/download.html you can now find amalgamations (the sqlite3.c source file) for development snapshots. These snapshots are intended for beta-testing only. Interfaces and file formats in these snapshots are subject to change. Nevertheless, these snapshots should be reasonably stable in the sense that they pass our internal tests. It will be a great help to our testing efforts if you will give one of these amalgamations a try in your software and report any problems you encounter to this mailing list. Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please beta-test SQLite 3.7.0
We have scheduled the release of SQLite version 3.7.0 for Thursday, 2010-07-15. That date could yet change, but 2010-07-15 is our target. SQLite version 3.7.0 will feature the addition of a write-ahead log (WAL) capability for transaction control. See http://www.sqlite.org/draft/wal.html for additional information. WAL should increase performance and concurrency in many situations. Version 3.7.0 also has other changes, including a new feature that automatically creates transient indices to improve performance for some complex joins, and other performance enhancements. Version 3.7.0 has been long in the making. We've worked on it for months. We have checked in nearly 600 separate change-sets since 3.6.23.1. About 7.5% of the source code in version 3.7.0 is new. Version 3.7.0 is currently passing all of our extensive internal tests. We have verified it using our 41-point pre-release checklist (which takes several days to work through). And version 3.7.0 is currently in use running the www.sqlite.org website (in WAL mode). We have tested the new release about as much as we can. Additional help from you is needed: Please help us by beta-testing SQLite 3.7.0 in your application! Report any problems found on this mailing list. You can download daily snapshots from http://www.sqlite.org/draft/download.html and view updated documentation at http://www.sqlite.org/draft/index.html Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database corruption on Linux ext3
An appliance manufacturer has discovered a database corruption issue on Linux using ext3. The issue is documented here: http://www.sqlite.org/draft/lockingv3.html#ext3-barrier-problem You are encouraged to submit comments, insights, criticism, and analysis to this mailing list. Thanks. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] New 3.7.0 snapshot - release estimated for July 22
The signature of the VFS has changed slightly - the xShmOpen() method has been removed. Implementations are now expected to automatically create the shared memory on the first call to xShmMap(). And the xShmClose() method has been renamed to xShmUnmap(). A new snapshot with these changes is now available in the usual place: http://www.sqlite.org/draft/download.html The target release date for version 3.7.0 has slipped until 2010-07-22 (a slip of one week). We were going to try to revise WAL so that it worked as a read-only database if write permission to key files was not available. But that was going to turn out to be a big, error- prone mess, and so we decided to back off and simply make it a limitation of WAL that a database could not be read or written in WAL mode if write permissions were lacking. Additional explanation at http://www.sqlite.org/draft/wal.html#readonly The documentation has also been enhanced (in a subsection immediately following the section on read-only databases linked above) to explain our decision to implement shared-memory using a mmapped file in the same directory as the original database. Comments and criticism of this decision are encouraged. Please evaluate the snapshot and provide feedback on this mailing list. The 2010-07-22 release date target assumes no more major problems. But we would rather encounter (and fix) a major problem before the release rather than afterwards. We will slip the release again if necessary. Your beta testing is *very* important. Thanks! D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.0
SQLite version 3.7.0 is now available on the website http://www.sqlite.org/ The most important change in version 3.7.0 is that SQLite now supports write-ahead logs as an optional method for transaction control, for improved performance and concurrency. Additional information can be found here: http://www.sqlite.org/wal.html We are actually already using the write-ahead logging feature on the SQLite website itself, in the Fossil DVCS that tracks all changes to the SQLite source tree. (Yes, the SQLite write-ahead log code is stored in an SQLite write-ahead log database - how's that for recursion!) http://www.sqlite.org/src The added concurrency of the write-ahead log journaling mode allows multiple users to be doing extended read operations, such as checking out historical versions of the SQLite code or looking at extended timelines simultaneously with developers making new checkins, adding or editing tickets, or actually rebuilding the entire 10-year source code database. The write-ahead log code has performed very well so far for us. Version 3.7.0 also marks the official cut-over to our new SQLite logo and a new color scheme for the website. We hope you like the new look. The 114-day time span since the previous release (version 3.6.23.1) is the longest span between consecutive releases in the 10-year history of SQLite. Much of that time was spent testing and stressing the new write-ahead log feature. This is probably the most thoroughly tested release of SQLite that we have every produced. And so even though the write-ahead logging feature is entirely new, we are very hopeful that the 3.7.0 release will prove to be stable and robust and ready for production use. Of course, if you do happen to run into problems, please let me know at once. Thanks! D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.0.1
SQLite version 3.7.0.1 is now available on the SQLite website: http://www.sqlite.org/ http://www.sqlite.org/download.html Version 3.7.0.1 is a patch release that fixes a bug in version 3.7.0 that can lead to database corruption if the same database file is written alternately by version 3.7.0 and version 3.6.23.1 or earlier. Additional information on this problem can be found at: http://www.sqlite.org/src/info/51ae9cad31 In addition, a typo in the OS/2 driver and a performance regression were fixed. The differences between 3.7.0 and 3.7.0.1 are minimal, but because of the possibility of database corruption, upgrading to version 3.7.0.1 is highly recommended. If you encounter any problems, please report them directly to me or to the sqlite-users@sqlite.org mailing list. D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I want to this project but I need help..
On Feb 19, 2008, at 11:46 PM, Cesar D. Rodas wrote: > I want to know if some one had the althttp.c quoted here > http://www.mail-archive.com/sqlite-users@sqlite.org/msg14449.html. > http://www.sqlite.org/docsrc/finfo?name=misc/althttpd.c The link above probably won't work until you login. Userid="anonymous" Password="anonymous". Then click on the link again. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL & tester.tcl question
On Mar 28, 2008, at 4:35 PM, Noah Hart wrote: > A general question for the TCL experts > > There is no problem, I'm just trying to understand how TCL works. > > In tester.tcl,v 1.79, at line 60, there is the following section: > > # Create a test database > # > catch {db close} > file delete -force test.db > file delete -force test.db-journal > sqlite3 db ./test.db > set ::DB [sqlite3_connection_pointer db] > > My question is: Why is the last line not "set ::DB db" > > What does sqlite3_connection_pointer do? > Since this is not a tcl verb, or defined by sqlite3, where does this > get > defined? > There are countless new TCL verbs implemented in C code and found in SQLite source files whose names begin with "test". The sqlite3_connection_pointer verb is but one of many. (BTW, in TCL lingo, one would normally call this a "command" not a "verb". But if you are more comfortable with "verb", that terminology works for me too.) The sqlite3_connection_pointer verb is an anachronism. The various new TCL verbs that interface to SQLite (example: sqlite3_prepare, sqlite3_steo, etc.) often require a database connection as a parameter. Originally, the implementations of these commands required that the parameter be the hexadecimal representation of the actually sqlite3* pointer. But when you use the "sqlite3" command to open a database connection, you get back a TCL object, not a pointer. The sqlite3_connection_pointer verb would translate the TCL database object into the appropriate pointer. The statement: set ::DB [sqlite3_connection_pointer db] translates the TCL database object "db" into a hexadecimal pointer value and stores that value in the global variable "DB". Subsequent commands in the same script would then use the value as "$::DB". This is all an anachronism because at this point, most of the other TCL commands have been upgraded and can accept the TCL database object directly. So instead of saying: sqlite3_prepare $::DB ... we can now say: sqlite3_prepare db ... which is much more convenient. However, the test scripts have been generated incrementally over the past 8 years and most of them have not been upgraded to take advantage of the new syntax. So there are still many calls to [sqlite3_connection_pointer] and uses of $::DB even though they are not needed. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Unsupported File Format" from 3.1.3
On Mar 29, 2008, at 12:34 AM, Jerry Krinock wrote: > Someone sent me an sqlite database file. > > Opening in Mac OS 10.5 with the built-in sqlite 3.4.0, no problems. > > Opening in Mac OS 10.4 with the built-in sqlite 3.1.3, any query > returns sqlite error 1, "unsupported file format". > > Similar files from other users open in either Mac OS/sqlite version. > > I'd thought that sqlite3 databases were generally backward- > compatible. Is there any way to find out what is "unsupported" by > sqlite 3.1.3 in this database? > The databases are backwards compatible. The database you are having trouble with is created by a more recent version of SQLite and uses features that were not added until after 3.1.3. Backwards compatible means that newer versions of SQLite can open any historic database. You are trying to open a newer database with an historic version of SQLite - that would be forwards compatibility. SQLite is generally forwards compatible, except when the newer database uses features which were added later. The added feature is probably a descending index. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] mem5 ("buddy system") usable?
On Mar 31, 2008, at 10:00 PM, Richard Klein wrote: > I just downloaded 3.5.7, and noticed that there is a > new memory allocator mem5.c, which apparently uses the > "buddy system" to allocate power-of-two-sized chunks > of memory from a static pool. This allocator is used > by defining SQLITE_POW2_MEMORY_SIZE. > > Is it okay to use this allocator, or is it only exper- > imental (and therefore liable to disappear in a future > release)? > I'm not making any promises about any of the current five memory allocators. I might decide to replace them all tomorrow. But mem5 is high on the list of memory allocators to keep since it can, under some circumstances, guarantee not to fragment memory, which is a desirable property for embedded systems. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] mem5 ("buddy system") usable?
On Apr 1, 2008, at 2:37 PM, Richard Klein wrote: >> > Fair enough. But can I assume that mem5 *does* currently > work, as far as you know? (I'd really like to use it, as > I'm using SQLite on an embedded system.) > mem5 works as far as I know. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger's actions and callbacks
> > Thanks. But I am experiensing some problems here. When I am using > the same > connection (I store it as third parameter to callback registering > function > > sqlite3_update_hook(db, Callback, db); > > and when trying to access to the db inside callback > > sqlite3_prepare((sqlite3*)data_arg_3, [...]) > > , an error SQLITE_MISUSE is returned to me SQLite is not reentrant through the update hook. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_finalize(pReadStmt); and sqlite3_close(pDB);
On Apr 3, 2008, at 5:09 PM, Joanne Pham wrote: > Hi All, > Should we always call sqlite3_finalize(pReadStmt); and > sqlite3_close(pDB) after we have done with read/write to database. > Or sqlite3_finalize(pReadStmt) is good enough. > Please advice what is the sequence of statement that we should call > after we are done with database activities(read/write). It is sufficient to call only sqlite3_finalize() and not sqlite3_close() in the sense that your data will be written into the database file. But until you call sqlite3_close(), the database will remain open which uses some memory and at least one file descriptor. If you really are never going to use the database again (or at least not soon) it is better to call sqlite3_close() too. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_aggregate_context with C++ classes
On Apr 3, 2008, at 3:39 PM, Steven Fisher wrote: > Can Final be called without Step first being called? Yes. > > > If Step is called, will Final always be called? Yes D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Apr 3, 2008, at 10:46 PM, Zbigniew Baniewski wrote: > I'm sorry to confirm the problem described at http://tinyurl.com/ > 29wc8x > > #v+ > $ tclsh8.5 > % package require sqlite3 > couldn't load file "/usr/lib/sqlite3/libtclsqlite3.so.0": > /usr/lib/sqlite3/libtclsqlite3.so.0: undefined symbol: sqlite3StrICmp > #v- > > Does there exist any cure? > http://www.sqlite.org/cvstrac/chngview?cn=4965 This has never been a problem for the prebuilt binaries on the website. Somebody must have built their own binary that separates the SQLite library from the TCL library and puts them in two separate shared libraries. (Perhaps the configure/make script does this.) The way we build the TCL interface that is on the download page is that the TCL interface code becomes part of the amalgamation and the whole thing is compiled as a single translation unit. I cannot imagine why anyone would want to do it differently. But just yesterday I had a chat conversation with an engineer at Novell/SuSE and he could not understand why anybody would want to do it my way - since that would me there were two complete copies of the SQLite library on disk. Different strokes for different folks, I guess... Anyway, you can fix the problem by either using the precompiled binaries, or downloading the latest from CVS. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] French Translation of SQLite documentation
On Apr 5, 2008, at 10:28 AM, Yves Maingoy wrote: > Hello All and Drh, > > I am a french user of SQLite and I started to translate it's > documentation. > > I use the tclsh scripts on Windows (with WinTcltk) and I produce > html pages from *.in sources files found at http://www.sqlite.org/docsrc > > The documentation is 20% translated at this time and I temporarily > put it at this address : http://fr-sqlite.isuisse.com/ > But, it's not a definitive address : there are advertisings in this > website (I don't like that) and it's not ambitious enough. > > So, I have a proposal from this website : > http://www.developpez.com/hebergement/ > to make an fr-SQLite website, free of charge, to help french > developpers, and where I can put the documentation and maintain it. > Is there any problem to do this ? > > Can I continue to translate the documentation in french ? > Of course you can continue. What can the developers do to help? Ideally, we would like to make documentation available on the main SQLite website in multiple languages. French, German, Chinese, Japanese, Russian, and so forth. The main problem we see is keeping the translations up-to-date. Much of the current documentation is generated automatically from comments in the source code, and it changes, sometimes dramatically from one release to the next. Yves, if you want to put together a French translation of all or even part of the SQLite documentation, or perhaps even write separate French-language documentation about SQLite, we will be willing to post it somewhere on www.sqlite.org. We'll ask that you store your translation in a fossil repository. (See http://www.fossil-scm.org/ for details. All current English-language documentation is in a fossil repostitory hosted at http://www.sqlite.org/docsrc.) We will ask that each translated page contain a timestamp of some kind to show when it was translated and that each page have a pointer back to the canonical English-language page. I'll try to figure out some way to have pointers from the English-language pages over to the translated pages. This same offer applies to anybody else who wants translate SQLite documentation into any other living language. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] French Translation of SQLite documentation
> > Thank you for your answer D. Richard, > > So I continue the translation and I will see fossil for future. > Can you take a ticket for this evolution ? > We can work with you to import your translation whenever you are ready. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
On Apr 6, 2008, at 5:12 PM, John Stanton wrote: > Not a hack, but the traditional way to store dates and times. Sqlite > functions use a magic epoch which facilitates the presentation of the > date and time in the form of the major calendars. > > We do not use the Julian calendar these days. It was supplanted by > the > Gregorian in 1582. Julian refers to defining a date by offset from an > epoch and is the preferred method of storing date and time. > See http://en.wikipedia.org/wiki/Julian_day Note that "Julian" in Julian Day Number and Julian Calendar refer to two different people named Julius. The Julian Day Number Julian is Julius Scaliger, the father of the guy who invented the julian day number in 1583. Julian in Julian Calendar refers to Julius Caesar, the Roman emperor. The date and time routines in SQLite use the Gregorian calendar. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the standard way to store dates and do operations with dates please?
On Apr 7, 2008, at 6:27 PM, Dennis Cote wrote: > D. Richard Hipp wrote: >> >> See http://en.wikipedia.org/wiki/Julian_day >> >> Note that "Julian" in Julian Day Number and Julian Calendar >> refer to two different people named Julius. The Julian Day Number >> Julian is Julius Scaliger, the father of the guy who invented >> the julian day number in 1583. Julian in Julian Calendar refers to >> Julius Caesar, the Roman emperor. >> > > Richard, > > From the wikipedia article you cited: > > Note: although many references say that the Julian in "Julian day" > refers to Scaliger's father, Julius Scaliger, in the introduction to > Book V of his Opus de Emendatione Temporum ("Work on the Emendation of > Time") he states, "Iulianum vocavimus: quia ad annum Iulianum dumtaxat > accomodata est", which translates more or less as "We have called it > Julian merely because it is accommodated to the Julian year." This > Julian refers to Julius Caesar, who introduced the Julian calendar > in 46 BC. > > I can't vouch for the veracity of this note, but he he seems to know > what he is talking about and has given what is purported to be a > reference from the original author that backs his claim (as best I can > tell from the quoted Latin and its translation). As always you have to > take everything on wikipedia with a grain of salt, but this looks > authoritative. > Well, Scaliger's father was apparently named after Julius Caesar (his full name was Julius Caesar Scaliger) so I suppose the roman emperor is the origin of the name either way - it just depends on how many pointers you have to go through to get there D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to add extensions to amalgamation
On Apr 8, 2008, at 8:22 AM, P Kishor wrote: > I want to add an extension or two to the amalgamation so I don't have > to load the extension manually. Can someone kindly explain or point me > to a clear tutorial on how to do so? Be gentle in your explanation as > I am not too C-savvy but can blunder my way around. (am reading a > basic C tutorial right now). > You might be interested in sqlite3_auto_extension http://www.sqlite.org/c3ref/auto_extension.html D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] for function extension in sqlite3
On Apr 8, 2008, at 12:57 PM, dark0s dark0s wrote: > Hi all, I'd like write extension for sqlite3 creating soundex > function like below: > SQLite already contains a soundex function. You just have to recompile using -DSQLITE_SOUNDEX=1 D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access to Btree routines in SQLite
On Apr 9, 2008, at 10:26 AM, Aladdin Lampé wrote: > > Hi Phil and list! > > Thank you for this very instructive post about SQLite's internals > and btrees. I'm just curious about what could be real-life use cases > of having direct access to the btree stuff. As I understand your > example, you store (key,value) pairs inside the btree and then get > them back. Then, what's the purpose of not using a normal SQLite > table to do so? > > On the other hand, since those "direct btree information" are > necessarily stored inside a reguar SQLite file, doesn't this > introduce possible side effects with other functions of the sqlite > library, which would not be aware that some btree roots inside the > file are neither a table nor an index? (vacuum, etc.) > > Last but not least, I am currently developping a virtual table and > I've just realized that I could use SQLite btrees for indexing data > coming from an "external database" (cf recent thread about virtual > tables and access to big external databases). > According to you, would it be a good idea to use this technique in > order to implement an alternative indexing technique (for "external > tables") based on SQLite btrees? > > Thanks a lot for sharing about that, any help would be greatly > appreciated, The use of SQLite's internal BTree routines by external applications is fraught with peril and is strongly discouraged. The BTree interface changes, sometimes in very subtle ways, without notice and with no documentation apart from comments on the code. And the BTree routines are not tested except for the usages modes employed by SQLite itself. If you ignore this advice and decide to use the BTree routines directly, and your code breaks or malfunctions due to some unseen subtly or some future change, then the rule is: "No Tears". D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation for the register based VM?
On Apr 10, 2008, at 11:47 AM, Dennis Cote wrote: > Michael Schlenker wrote: >> >> The old code did some stowing away of values via OP_StoreMem/ >> OP_LoadMem and >> those have been replaced by OP_Copy etc. in the register VM. So how >> many >> registers are available in the VM to push away some intermediate >> results? >> > > From the documentation at http://www.sqlite.org/opcode.html we have: > > The virtual machine contains an arbitrary number of registers > locations > with addresses beginning at one and growing upward. Each memory > location > can hold an arbitrary string. The registers hold all intermediate > results of a calculation. > That is correct, but it is not the whole story. Registers have to be allocated. The canonical way to do this is to increment the Parse.nMem field. Or one can call sqlite3GetTempReg() or sqlite3GetTempRange(). Registers acquired this way need not be released for reuse, though of course they can be. On the other hand, anybody who is modifying the code generator (which is what Michael Schlenker appears to be doing) needs to read through the entire code generator source code base and understand at a deep level what is going on. And, such programmers need to be prepared to throw away their changes and start over when code generator implementation changes, which it does from time to time. 3.5.4->3.5.5 was a big change for the code generator. But you should know that we are not through changing it yet and more big changes could appear in future releases. The virtual machine in SQLite is not an API and we make positively zero effort to maintain backwards compatibility from one point release to the next. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Planner choosing wrong index
On Apr 10, 2008, at 1:30 PM, Steve Krulewitz wrote: > > This problem also affects the table ordering of my joins. Taking the > same schema and adding a "detail" table (see > http://skrul.com/index_detail.sh) , this query chooses idx_a and puts > the foo table first: > > sqlite> explain query plan select * from foo join foo_detail on foo.id > = foo_detail.id where (a is null) and data = 1; > 0|0|TABLE foo WITH INDEX idx_a > 1|1|TABLE foo_detail WITH INDEX idx_foo_detail_data > > And it runs very very _very_ slowly. Forcing sqlite not to use > idx_a, you get: > > sqlite> explain query plan select * from foo join foo_detail on foo.id > = foo_detail.id where (a is null or a is not null) and data = 1; > 0|1|TABLE foo_detail WITH INDEX idx_foo_detail_data > 1|0|TABLE foo USING PRIMARY KEY > > And it runs brilliantly fast as you'd expect. > > For now I've added the "or" hack into my application (thanks, Ken) and > this has sped things up considerably. Is there any better way to fix > this? > Use "+a is null" instead of "a is null". (Add a "+" in front of the column name). The plus sign is a no-op - it generates no code and returns the value of its operand unchanged even if the value is a string. But it also disables the term as a candidate for using indices. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED behavior
On Apr 13, 2008, at 3:01 AM, Tomas Lee wrote: > On 2008 April 12 (Sat) 05:44:53pm PDT, Shawn Wilsher <[EMAIL PROTECTED] > > wrote: >> When using SQLite 3.5.4.1 (a special branch cut for Mozilla, based >> mostly off of 3.5.4 with some OS/2 fixes), I'm getting SQLITE_LOCKED >> returned unexpectedly. The documentation seems to indicate that I >> should only be getting SQLITE_LOCKED if I'm calling sqlite3_exec >> recursively writing to the same table. However, it seems to me that >> I'm having that happen when two different threads are trying to write >> to the same table. I would expect to get SQLITE_BUSY at this point, >> but perhaps I'm misusing the API or have the wrong expectations. >> >> This is happening by using a different sqlite3 database pointers, one >> for each thread. > > Are you using a shared cache? You can get also get SQLITE_LOCKED when > using a shared cache. See section 2.2 of > <http://www.sqlite.org/sharedcache.html>. > > I've not used a shared cache myself. One day I was wondering if I > needed to worry about handling SQLITE_LOCKED errors and I came across > that page. Are these the only times you can get SQLITE_LOCKED errors? If you are in the middle of a SELECT statement and from the same database connection you try to DROP one of the tables that is being read, the DROP statement will return SQLITE_LOCKED. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED behavior
On Apr 14, 2008, at 12:57 PM, Shawn Wilsher wrote: >> Are you using a shared cache? You can get also get SQLITE_LOCKED >> when >> using a shared cache. See section 2.2 of >> <http://www.sqlite.org/sharedcache.html>. >> >> I've not used a shared cache myself. One day I was wondering if I >> needed to worry about handling SQLITE_LOCKED errors and I came across >> that page. Are these the only times you can get SQLITE_LOCKED >> errors? > Ah-ha! We are in fact using the shared cache, which probably explains > this. Any reason why SQLITE_LOCKED is returned instead of > SQLITE_BUSY? With SQLITE_BUSY you can keep retrying until you decide > to give up, or until it works, but you can't do that with > SQLITE_LOCKED. > You can disable much of the SQLITE_LOCKED behavior using PRAGMA read_uncommitted=ON; In that case one thread will be able to read uncommited changes made by a second thread. Turning on uncommitted read will prevent writer and readers from blocking one another. But you still won't be able to have two or more connections writing at the same time. Nor will you be able to DROP a table out from under a reader. I am not aware of any reason why you cannot retry an SQLITE_LOCKED error after a delay, however. Have you actually tried doing that? Is it giving you trouble? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_LOCKED behavior
On Apr 14, 2008, at 2:44 PM, Shawn Wilsher wrote: >> I am not aware of any reason why you cannot retry an SQLITE_LOCKED >> error after a delay, however. Have you actually tried doing that? >> Is it giving you trouble? > Attempting to retry after it being issues results in SQLITE_MISUSE > being returned. > Did you call sqlite3_reset() before each retry? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table modifications and analyze
On Apr 14, 2008, at 2:49 PM, Petite Abeille wrote: > Hello, > > What heuristics do people use to determine the frequency for analyzing > their indices? > I use the simple heuristic of never running ANALYZE. The ANALYZE command is only helpful to the optimizer in certain extreme cases. See, the discussion at http://www.sqlite.org/cvstrac/wiki?p=QueryPlans If you have an application where SQLite chooses a bad query plan by default, you can probably get by with running ANALYZE just once on a typical database, then copying the resulting sqlite_stat1 table into every new database you create with the same schema. Some database engines benefit from running ANALYZE periodically. SQLite does not, in general. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite>=3.5.6 does not open database file
On Apr 14, 2008, at 4:53 PM, D. Richard Hipp wrote: > > On Apr 14, 2008, at 4:47 PM, Steve Topov wrote: >> I can open the file with 3.3.5. I did not try to dump it and recreate >> with 3.5.6. >> > > With version 3.5.5, please do this: > > sqlite3 yourdatabasefile.db .schema >schema.txt > > then email me schema.txt file. > If the database file is small enough, please send me the whole thing. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite>=3.5.6 does not open database file
On Apr 14, 2008, at 4:47 PM, Steve Topov wrote: > I can open the file with 3.3.5. I did not try to dump it and recreate > with 3.5.6. > With version 3.5.5, please do this: sqlite3 yourdatabasefile.db .schema >schema.txt then email me schema.txt file. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite>=3.5.6 does not open database file
On Apr 14, 2008, at 6:58 PM, Steve Topov wrote: > 1.8 Meg > email it to me, please. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite>=3.5.6 does not open database file
On Apr 14, 2008, at 4:30 PM, Steve Topov wrote: > I already reported this bug but did not receive any responds so I am > trying one more time. > The problem: > SQLite version 3.5.6 and up returns an error when opening some > database > files. The error is “SQL logic error or missing database”. > Version prior to version 3.5.6 works fine with these database files. > > I do not know the version of SQLite the database file in question was > created with. And I am talking about SQLite 3 database file – not > SQLite > 2, not Microsoft Access, > not any other file format. > > To make story short: SQLite 3.5.6 can not open SQLite 3 database file. > > I'm working with the file named "dpdshop.sl3" that you sent me, 5444608 bytes in size. MD5 checksum 9fa76c1610a5e7c826d2745191e7a401 I can open and read this file without problems on MacOSX using SQLite version 3.4.0, 3.5.6, 3.5.7 and the latest in CVS. I ran PRAGMA integrity_check using all four versions of SQLite and all return "ok". D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction across threads
On Apr 15, 2008, at 11:31 AM, Shawn Wilsher wrote: >> 1) If shared, then the second threads insert is part of the >> transaction and should succeed. >> 2) No. >> 3) If the connection is shared between threads, there can only be 1 >> txn at a time. The second threads attempt to begin a txn will >> result in an error that indicates a txn is already active. > To be clear, when using a shared cache and more than one sqlite3 > connection object, only one transaction will exist at a time, correct? Correct. > > However, if it is not using the shared cache, you can have a > transaction opened up for each thread? > Well, sort of. Certainly true if each connection has a different database open. But there can only be one write transaction at a time to a single database. If you have multiple connections to the same database file, one can have a write transaction open and one or more others can have a read transaction open, but you cannot have two or more write transactions active at once and all of the read transactions will need to close prior to the write transaction committing (otherwise the writer gets an SQLITE_BUSY.) D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_TRIGGER compilation problems
On Apr 15, 2008, at 10:34 PM, Richard Klein wrote: > I compiled SQLite 3 (version 3.5.7), specifying > SQLITE_OMIT_TRIGGER to reduce the size of the > generated code. > The OMIT macros are not supported in the preprocessed source code or in the amalgamation. To use the OMIT macros you much compile from canonical sources using a unix-like development environment. The reason for this is that SQLite includes several code files that contain automatically generated code. And the generated code depends on which OMIT macros are present. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.5.8
SQLite version 3.5.8 is now available on the SQLite website http://www.sqlite.org/ The primary focus of this release is performance enhancements. But there are also some interface changes: * The sqlite3_randomness() interface has been added. * The sqlite3_context_db_handle() interface has been added. * The sqlite3_limit() interface has been added. * The VACUUM command can now be used to change the database page size. There are also many bug fixes, though none of the bugs fixed appear to be major. As always, please report any problem you may encounter. D. Richard Hipp [EMAIL PROTECTED] P.S.: As I type this, two test failures have emerged in the new release: utf16-bind-6.4 and utf16-bind-7.4. Both failures are benign. The fault is actually in the test script, not in SQLite itself. The test script in these cases assumes a UTF8 database and hence clearly does not work if the database uses UTF16. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.8
On Apr 16, 2008, at 12:25 PM, Ken wrote: > > The download page does not have the almagamation in the .tar.gz > format. > The zip version of the amalgamation does not contain the ./configure > or autoconf as did version 3.5.7. > > The amalgamation (.zip) did not have the shell.c code. > > Was this intended ? > There are a dozen different build products in the latest release. I'm sorry that the one you wanted is not there. Perhaps we can find time to add it later. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite>=3.5.6 does not open database file
On Apr 16, 2008, at 4:41 PM, Steve Topov wrote: > Any clues? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Steve Topov > Sent: Monday, April 14, 2008 5:02 PM > To: 'General Discussion of SQLite Database' > Subject: Re: [sqlite] SQLite>=3.5.6 does not open database file > > I am on Windows XP using sqlite 3.5.6 dll created from the source. Why don't you try the pre-compiled DLLs off of the website. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
On Apr 17, 2008, at 12:31 PM, Dennis Cote wrote: > Eric Minbiole wrote: >> >> However, I wanted to let others take a look, to see if the >> issue was with my query (quite possible), or with the new version. >> > > This is definitely an issue with the new version. It is doing a nested > table scan instead of using the index for the left join. > Likely this has to do with ticket #3015. http://www.sqlite.org/cvstrac/tktview?tn=3015 D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OUTER JOIN and PRIMARY KEY in 3.5.8
On Apr 17, 2008, at 12:04 PM, Eric Minbiole wrote: > I have been using SQLite for about a year now, and have been extremely > pleased. While testing 3.5.8, I noticed that a SELECT with LEFT OUTER > JOIN seemed to stop using an index, resorting to a (slow) full table > scan. A simple (contrived) example follows: > > CREATE TABLE pets ( > pet_id INTEGER PRIMARY KEY, > owner_id INTEGER, > name TEXT ); > > CREATE TABLE owners ( > owner_id INTEGER PRIMARY KEY, > name TEXT ); > > INSERT INTO owners (owner_id, name) VALUES (1, 'Steve'); > INSERT INTO pets (owner_id, name) VALUES (1, 'Fluffy'); > > EXPLAIN QUERY PLAN > SELECT pets.name, owners.name > FROM pets > LEFT OUTER JOIN owners > ON (pets.owner_id = owners.owner_id); Your work-around until I fix this is to say owners.owner_id = pets.owner_id instead if what you have. In other words, put the table on the left side of the join before the equals sign instead of after it. It shouldn't make any difference. SQLite should generate exactly the same code regardless of whether you say A=B or B=A. Clearly something is busted. It will be fixed soon. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote: > Is there a way to select rows that have not been committed yet? > No. SQLite doesn't really commit rows. It commits pages. A single page might hold multiple rows, only some of which might have changed. Or a single row might span multiple pages. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] no VFS error
On Apr 19, 2008, at 7:41 AM, Toby Roworth wrote: > When attempting to open a database, I am getting a "no such vfs" > error. > > What is likely to be causing this - a quick search suggested that vfs > may stand for virtual file system - is this right? > > Any help would be appreciated > What value are you passing as the 4th argument to sqlite3_open_v2()? (I assume you are using sqlite3_open_v2() since that seems the mostly likely way to get this error.) D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXISTS operator doesn't seem to work
On Apr 19, 2008, at 7:06 AM, pinco palletto wrote: > update register > set note = (select ORT.note from old_register ORT > where ORT.person_id = person_id and ORT.document_id = document_id) > where exists ( > select ORT.* from old_register ORT > where ORT.person_id = person_id and ORT.document_id = document_id); update register set note = (select ORT.note from old_register ORT where ORT.person_id = register.person_id and ORT.document_id = register.document_id) where exists (select ORT.* from old_register ORT where ORT.person_id = register.person_id and ORT.document_id = register. document_id); D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] no VFS error
On Apr 19, 2008, at 9:19 AM, Toby Roworth wrote: >> >> What value are you passing as the 4th argument to >> sqlite3_open_v2()? (I assume you are using sqlite3_open_v2() >> since that seems the mostly likely way to get this error.) >> > I'm passing a blank string - this would appear to be the problem! > errorCode = sqlite3_open_v2("data/texts.db", , > SQLITE_OPEN_READONLY, ""); > A null pointer (aka zero). 0. Very different from an empty string. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a suggestion to write tutorial for sqlite
On Apr 19, 2008, at 5:44 PM, Jay A. Kreibich wrote: > > This is a bit off-topic for the mailing list, so please feel free > to send stuff directly to the address below. > It would be good, I think, to have a public record of this conversation. We can create an [EMAIL PROTECTED] mailing list if you think it would help. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a suggestion to write tutorial for sqlite
FWIW, I'll be happy to give write access to the documentation repository (http://www.sqlite.org/docsrc/) and even a prestigious "sqlite.org" email alias to anybody who is willing to step up and make some improvements and updates to the current documentation. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob api
On Apr 21, 2008, at 3:10 PM, Robert Bielik wrote: > Hi all, > > I'm using the sqlite3_blob_* api to write a larger text stream > incrementally. Works a charm, but is there a way to > change the datatype of the blob to text afterwards ? I'd like to see > the text easily in f.i. SQLiteSpy. > Perhaps: SELECT CAST(b AS TEXT) FROM table Really I suppose it depends on what SQLiteSpy is using to determine that the column type is BLOB. If you can subversion that mechanism into thinking the column type is TEXT, then it should just work. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_xxx
On Apr 21, 2008, at 4:25 PM, Richard Klein wrote: > Thanks, Mark! > > I use the individual source files rather than the amalgamation, > for several reasons: To generate individual source files run make target_source > > > (1) Visual Studio has trouble generating line number info for > files that have more than 64K lines. Report this bug to Microsoft. > > > (2) Perforce (our version control software) has trouble diff'ing > two versions of a large file. I had similar problems when I was working on the diff-er in fossil (http://www.fossil-scm.org/) but I eventually overcame them. Suggest you report the problem to Perforce. They are welcomed to use my (GPLed) fossil code :-) > (3) We build SQLite for many different target platforms, using > various C and C++ compilers. We get many (i.e. hundreds) of > warnings, and even some errors. When fixing these problems, > it is simply easier to edit many smaller files rather than one > huge, unwieldy file. As for warnings, see http://www.sqlite.org/faq.html#q17 SQLite is ANSI-C code, not C++ so if you try to compile it with a C++ compiler you might well get errors. I suggest you use a C compiler instead. Surely Visual Studio must include a C compiler. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems with SQLITE_OMIT_xxx
On Apr 21, 2008, at 9:03 PM, Richard Klein wrote: > I regenerated and recompiled the source files > specifying that the following features (among > others) are to be omitted: > > SQLITE_OMIT_REINDEX > SQLITE_OMIT_VIEW > SQLITE_OMIT_VIRTUALTABLE It worked OK when I tried this combination. Are you *sure* you set these options when you ran "make target_source"? Are you certain that you are using the correct set of source files? > > > Nevertheless, I am getting the following link > errors: > > delete.obj : error LNK2019: unresolved external symbol > _sqlite3MaterializeView referenced in function _sqlite3DeleteFrom > update.obj : error LNK2019: unresolved external symbol > _sqlite3MaterializeView referenced in function _sqlite3Update > parse.obj : error LNK2019: unresolved external symbol > _sqlite3VtabArgExtend referenced in function _yy_reduce > parse.obj : error LNK2019: unresolved external symbol > _sqlite3VtabArgInit referenced in function _yy_reduce > parse.obj : error LNK2019: unresolved external symbol > _sqlite3VtabBeginParse referenced in function _yy_reduce > parse.obj : error LNK2019: unresolved external symbol > _sqlite3VtabFinishParse referenced in function _yy_reduce > parse.obj : error LNK2019: unresolved external symbol > _sqlite3Reindex referenced in function _yy_reduce > parse.obj : error LNK2019: unresolved external symbol > _sqlite3CreateView referenced in function _yy_reduce > > Are there any workarounds for these, or will > I have to remove the above OMIT options? > > Thanks, > - Richard > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_PAGER_PRAGMAS
On Apr 22, 2008, at 8:37 PM, Richard Klein wrote: > I've generated and compiled the SQLite sources > with the option SQLITE_OMIT_PAGER_PRAGMAS. > > If I call sqlite_prepare() and sqlite3_step() > on the SQL statement "PRAGMA cache_size = 100;", > I get return codes of SQLITE_OK and SQLITE_DONE, > respectively, but the cache_size doesn't seem to > change. > > Is this the correct behavior? If so, fine -- > it's just that I would have expected sqlite3_ > prepare() to return an error code. > When you compile with SQLITE_OMIT_PAGER_PRAGMAS the cache_size pragma is omitted. A feature of the PRAGMA command is that unrecognized pragmas are silently ignored. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency access to SQLite
e N process has concurrency access to >>>>>>> SQLite database. >>>>>>> In theory in worst case, save_work_result_to_sqlite() should >>>>>>> NOT wait >>>>>>> for access to database longer than N * 1 sec. >>>>>>> But in practice, some process blocks on save_work_to_sqlite() >>>>>>> more >>>>>>> than N*2 sec and dies on my SQLITE_BUSY asserts :/ >>>>>>> >>>>>>> So, I am wondering, is there any ideas how to avoid this? >>>>>>> >>>>>> >>>>>> ___ >>>>>> 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 >>>> >>> >>> >>> >>> -- >>> Alexander Batyrshin aka bash >>> bash = Biomechanica Artificial Sabotage Humanoid >>> ___ >>> >>> 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 >> > > > > -- > Alexander Batyrshin aka bash > bash = Biomechanica Artificial Sabotage Humanoid > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how is sqlitedll-3.5.8.zip built?
On Apr 30, 2008, at 3:29 PM, Dennis Cote wrote: > Richard (or anyone else who knows), > > I am looking at ticket 3084, and I am seeing a difference in behavior > between using the DLL and the amalgamation source with the exact same > test application. The script used to build the DLL found on the website is http://www.sqlite.org/cvstrac/fileview?f=sqlite/mkdll.sh=1.14 It is cross-compiled from a Linux host using a 5-year-old build of mingw. > > > I was under the impression that the DLL available for download is now > built from the amalgamation. Is that correct? If so, what options are > defined when it is being built? If not, how is it built? > > TIA > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Proposed incompatible changes to the SQLite VFS layer
The Virtual File System (VFS) layer for SQLite was introduced in version 3.5.0 on 2007-09-04. The VFS interface has made SQLite much easier to port to unusual systems. However, as we have gained experience with the VFS, we have come to realize that the original VFS design has some warts. In particular, not all of the methods in the VFS interface are able to return the errors that some systems want to return. For example, the xAccess() method is used to determine whether or not a file exists. On Unix and win32, the system calls to determine whether or not a file exists cannot fail as long as the call is well-formed (i.e. you give it a valid file descriptor.) The operating system will always be able to give you a straight yes/no answer about whether or not a file exists. And so we made no provisions in the VFS design for the xAccess() method to return an error code. Since then, we have found that some embedded platforms do not work this way and that it is sometimes necessary for xAccess() to fail and say "I don't know". This can happen, for example, when the system is unable to allocate memory for an IPC buffer. To resolve minor problems like this, we are proposing to make incompatible changes to the SQLite VFS layer for version 3.6.0. The changes will be relatively minor (such as allowing xAccess to return an error code in addition to a result) but they are changes all the same and will require VFS implementations targeting 3.5.x to be revised. If you use SQLite exclusively on workstations (unix, O/S2, and/or win32) then you will not notice any changes at all since the VFS layers for these systems are supplied by default. All you have to do is recompile. However, if you have your own custom VFS layer written for an embedded platform, the change to version 3.6.0 may require you to make adjustments to your application code. Note, however, that the adjustments will not be nearly as complex as the migration from 3.4.2 to 3.5.0. Our plan is to release version 3.5.9 prior to starting work on 3.6.0. If the proposed incompatible changes to the VFS layer might cause you hardship, now is the time to make your concerns known. If you have had trouble using the VFS layer and what to suggest API or functionality changes, now would be a good to to do so, prior to us beginning work on version 3.6.0. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer
On May 2, 2008, at 2:07 PM, Ken wrote: > I use the VFS to disable journal file creation and writing. > > Any possibility to make this part of the sqlite3_open_v2 mode > parameter? > > That way I could drop my custom VFS. > http://www.sqlite.org/pragma.html#pragma_journal_mode Before you rush out and try this on 3.5.8, please note that it is currently only available in CVS HEAD. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] property/config file for SQLite
On May 2, 2008, at 2:50 PM, [EMAIL PROTECTED] wrote: > > Is there a way to configure SQLite using a property/config file? SQLite has no configuration file. This is by design. SQLite is intended to be a "zero-configuration" database. See http://www.sqlite.org/zeroconf.html . The lack of a configuration file is a feature, not a bug. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite & TCL: A "SELECT" from one-column table
On May 3, 2008, at 1:52 PM, Zbigniew Baniewski wrote: > Take a look at following example: > > dbcomm eval {CREATE TABLE something( a_string VARCHAR(20) )} > dbcomm eval {INSERT INTO something VALUES ('Version V8.5')} > > Now, if you want to retrieve the value: > > tclsh8.5 [~/tmp/tcltk]dbcomm eval "SELECT a_string FROM something" > Version V8.5 > > Why so much parentheses? A list, nested in a list, nested in a list? > But why? I get just {Version V8.5} when I try this. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer
On May 3, 2008, at 12:30 AM, Roger Binns wrote: > > I would like a #define of the current version number. http://www.sqlite.org/c3ref/c_version.html D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] splite database under version control (subversion)?
On May 4, 2008, at 4:36 PM, M. Emal Alekozai wrote: > Hi, >> In the BIG db I have worked on there is a table that log every >> insert/update >> on specific and important tables and a log of every sql statement >> execute >> but I haven't ever see a db under version control with svn (or csv >> or git or >> any other). >> > I'm (mis)using subversion more as a synchronization tool than as a > version control system. I have a small software project and I have to > work on this project on different computers (desktop, laptop, ...). To > synchronize the project on all computers I use subversion. Before > starting to work on one computer I make an "svn update" for the > project > and after finishing I do a "svn commit". ... > > But the drawback is that the sqlite database is in a binary format and > putting it into subversion is not optimal ;-). > > A possible solution would be to export the sqlite database as a list > of > sql commands. > > Are there any other solutions /approaches for sqlite available? > http://www.fossil-scm.org/ I wrote fossil for a very specific use - a use which other DSCMs lacked necessary features. But having written it, I am now finding that is is also very useful as a "synchronization tool", such as you describe above, and that is my primary use for fossil at the moment. I use multiple desktop and laptop systems to work on projects and I find that synchronizing using fossil is very convenient. About six months ago, we shifted all of the documentation for SQLite out of CVS and into fossil. The fossil repository for the SQLite documentation is working quite well. See http://www.sqlite.org/docsrc/timeline to see for yourself. We might someday shift the SQLite core from CVS to fossil... Fossil will work fine with binary files. You won't be able to "diff" the files obviously, nor "merge" them if you fork your line of development. But that is just the nature of binary files. I don't think any SCM system is able to diff or merge arbitrary binary files. There may be modules for diffing and merging specific binary files formats (ex: DOC files) but not arbitrary binary files nor likely SQLite database files. So take care not to fork. But otherwise, you should be fine. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] import files with more than 2GB?
On May 5, 2008, at 11:37 AM, Michael Lackhoff wrote: > Hello, > > thanks to the flexibility of sqlite I can use a sybase database dump > as > a source for import into my database. Only problem: the file to import > is over 2GB and I get a "file not found" error. The file is there, it > can be read by split and imported after the split and the resultung > sqlite database is over 2GB as well, so large file support doesn't > seem > to be a general problem. I wonder if there is a possibility to make > this work in one go (without the split). It would speed things up and > would save quite a lot of (temporary) disk space. > > I am using version 3.5.8 on Solaris 9 > > the job file looks like this: > .separator ^A > create table mytable (...); > .import mydata.bcp mytable > Looking at the code, I do not see why this fails, assuming you have large-file support turned on (which you seem to have, and it is the default, after all.) The ".import" code does fopen() then fgets() to read the file. Does fopen() not work for large files on Solaris 9? The code to do an import is not part of the core SQLite, btw. It is part of the CLI. You can find the code by searching for "import" in the shell.c source file. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_mode not implemented?
> > > Since PERSIST is likely to be faster than DELETE on > most platforms, is there ever a reason *not* to use > it? > In PERSIST mode, you have two files associated with your database instead of one. Whenever you move, copy, or rename the database file you *must* also move, copy or rename the journal file to prevent database corruption. The persistent journal file uses disk space that might otherwise have been returned to the operating system and made available to other programs. We have not tested the robustness and power-loss survivability of PERSIST mode nearly as carefully as we have the existing DELETE mode. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting into a microcontroller, minimum requirements
On May 6, 2008, at 8:43 PM, Andrew Cunningham wrote: > Hi, > > I was wondering if anyone has any basic guide lines on embedding > SQLite into > a microcontroller. For example, I am considering using an 8/16 bit > processor > with 1 MB flash, 1 MB SRAM and 2 GB data storage (SD card). Has > anyone ported > this before to an embedded system without an OS? > I have doubts that you will be able to get SQLite to work on anything less than a 32-bit processor. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA journal_mode not implemented?
On May 6, 2008, at 11:08 PM, Scott Hess wrote: > > I suppose in DELETE mode, SQLite will delete the journal for you, but > I don't think it would hurt to do it yourself... No, it's deadly to do it yourself. That is my point. Unless you very carefully analyze the lock state of the database file and the content of the rollback journal, you cannot know whether or not it is safe to delete the journal file. If it is not safe to delete the journal file and you delete it anyway, the database will go corrupt. The code needed to determine whether or not it is safe to delete the journal file is non-trivial. Never, ever, ever delete a journal file on a database that you care able. Let SQLite delete it for you if you want the file to go away. Deleting journal files will eventually result in corrupt databases, regardless of what journaling mode you are using. In PERSIST mode, you can probably get away with deleting the journal file in many cases, but if you do it habitually, you will eventually end up corrupting your data. Hence, an advantage of DELETE journaling mode is this: If a journal file exists you know that file is essential to the correctness of the database. In PERSIST journaling mode, you never know and you delete the file at your peril. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] corrupt database with update?
On May 8, 2008, at 2:54 AM, C M wrote: > How likely (or possible) is it to corrupt or in some way screw up an > SQlite database if one is doing an UPDATE and it fails? (computer goes > out, etc.) Thank you.\ http://www.sqlite.org/atomiccommit.html D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users