Re: [sqlite] Slow Transaction Speed?
> my developemnt system is a Win XP, with of course NTFS, I > don't know which drive it has, I guess a standard 7200 rpm. > What file extension (i.e. the letters after the dot in the filename) do you give the database? I faintly recall there is a windows peculiarity with system restore or something similar that archives certain file extensions in the background. That may contribute to your slow down. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Techniques to delay writes to SQLite
I would like some advice on how best to implement delays in writing to a SQLite file (in my case using PHP) to maximise concurrent access. The web application I'm developing mainly issues quick reads and writes on a local SQLite file using PHP 5. This works satisfactorily. However in the future there may occasionally be a relative long running SELECT statement while performing some reporting analysis. I would like to reduce the potential contention with the long read blocking and therefore timing out a write operation. I'm not concerned about the small delay in the database being updated. I'm aware of one technique to create/use temporary tables using a select statement but I would like something more robust since the complexity of the long running SELECT statement is indeterminate since it is modifiable by an authorised user. One idea I had was to use a shared flag across the PHP processes. A potentially long running SELECT statement would set this flag to true. All write operations would check for this flag and on its value being true would open a new SQLite file and write the raw SQL strings to act as queue. Something similar to the Undo example comes to mind (http://www.sqlite.org/cvstrac/wiki?p=UndoRedo). On completion the long running SELECT statement would open the new SQLite file and "play" the SQL strings in order back into the original SQLite file. How would I write binary safe INSERT/UPDATES SQL statement like that in PHP? I'm open to other techniques particularly if they would be simpler to implement and manage! Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exporting database to CSV file
. > > Is there a way to do this entirely through php? I would like > to make a query on a table and write the results to a csv > file so that the user can have the option of downloading it. > Has anyone ever done something similar to this? > > Thanks > I believe there is a function like 'fputcsv' which may work for you. However I would test the multi-line output as mentioned in the earlier email from Sylvain. I recall trying to use the function and that it suffers from the same problem. You may find it easier just to code it directly yourself. This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Significance of Sqlite version?
I note recently that the SQLite version has gone from a 3 point number (i.e. 3.6.2) to a 4 point number (i.e. 3.6.6.2). Should I read any significance into this change? Is there going to be two strands to development/release of SQLite or will the current practice of the 'latest is the best' still remain true? Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
> > Any advice will be greatly appreciated. If there is any FM I > should R kindly point me to it :) > FTS information is difficult to find. Try http://www.sqlite.org/cvstrac/wiki?p=FtsOne I can't remember how I came across this link because I can never find it on the SQLite website. Ignore references to FTS1 as the SQL syntax is the same for FTS3 (I believe it is only the internals that have changed). One tip is to read the document a number of times. There are a number of important but subtle concepts that you need to grasp to effectively use FTS. In particular understand how the tokeniser works. Words with hyphens or UTF8 may not work as you might expect. This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorporating FTS into existing database schema
This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with Sqlite
> I'm using the install of Firefox that comes with the Wubi > install of Linux. I like sqlite, but have a little problem. > Perhaps someone can help. > > When I add a new record to a database, an entry screen comes > up with my fields and the ability to enter the new record. > But the information I type into the input field seems placed > in the field a bit too low -- about half of each letter is > cut off at the bottom and I can't really read what I'm typing. > > Is there a way to fix this? Has anyone else had this experience? I suspect you might be using the SQLite Manager add-on to Firefox. You can check the add-ons used in Firefox by going to the menu "Tools->Add-ons". When the window pops up select the "Extensions" tab at the top. More information about SQLite Manager can be found at: http://code.google.com/p/sqlite-manager/ Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connection to in-memory database
>
> I would like to use transactions from separate threads, each
> thread having one connection to a single in-memory db.
>
If your production environment is a modern linux distribution you may
find the temporary directory ("/tmp") is already a memory drive using
the tmpfs filesystem. If not it is very easy to create one. Search the
internet for more information.
You can then access the database by multiple processes/threads by
referring to the file path.
Nick
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
> > I would like to generate Snippets from MATCHes in two > columns, however, I get the following error: "unable to use > function MATCH in the requested context" with the following query -- > > SELECT poem_id, context > FROM poems a JOIN ( > SELECT > rowid, > Snippet(fts_poems, '', > '', '') AS context > FROM fts_poems > WHERE poem MATCH ? OR history MATCH ? > ) b ON a.poem_id = b.rowid > The query above does not use the syntax of MATCH when using FTS. See the extract below from http://www.sqlite.org/cvstrac/wiki?p=FtsOne: Any term in a query string may be preceded by the name of a particular column to use for matching that term: sqlite> select name, ingredients from recipe where recipe match 'name:pie ingredients:onions'; broccoli pie|broccoli cheese onions flour sqlite> The following are entirely equivalent: sqlite> select name from recipe where ingredients match 'sugar'; sqlite> select name from recipe where recipe match 'ingredients:sugar'; When a specific column name appears to the left of the MATCH operator, that column is used for matching any term without an explicit column qualifier. Thus, the following are equivalent: sqlite> select name from recipe where recipe match 'name:pie ingredients:onions'; sqlite> select name from recipe where name match 'pie ingredients:onions'; This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Finding similar duplicates
> > You probably want > > x.first_name like substr(y.first_name, 1,2) || '%' > > or > > substr(x.first_name, 1, 2) = substr(y.first_name, 1, 2) > > Igor Tandetnik > Igor, Peter, Thanks very much for your help. This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Finding similar duplicates
This should be simple but my brains not functioning. So I would appreciate some help from the SQL masters... I have a table with first_name and a last_name column. I would like to find similar duplicates by finding the same last_name and matching the first two characters of the first name. Therefore if the table has the following rows: Ind_id last_name first_name 100 Smithdavid 101 Smithdave 102 Smithirene I would like it to pick out the top two rows. I'm using the following but while the syntax is correct the last where expression below is wrong: select * from current as x, current as y where x.last_name = y.last_name and x.ind_id != y.ind_id and x.first_name like substr(y.first_name, 0,2) In my english I was trying to write "match where the first name of x begins with the first two characters of y first name". Using a literal example for above the SQL would end: where x.first_name like 'da%' (Note: the ind_id comparison avoids matching itself) Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] restricting access to sqlite database
> > Thanks for your reply! However, apache has to be able to > access /a/ totally/different/path/to/db, so this means that > any user on the same server can access it via e.g. a PHP web > page, if they know that path, is that correct? > Yes, but > > >> In MySQL for example, this is not a problem because of > the different > >> users/privileges, but what is the common way around this in SQLite? > > MySQL would actually suffer from a similar problem but in a different way. Imagine the scenario that your forum accesses a MySQL database using username & password strings stored in a PHP script. This script would need to be readable by apache for the forum to work. If someone else know the name of that script, they could craft a rogue PHP to display the above PHP script so that they could copy the username/password. They could use username/password to access your MySQL database and corrupt/delete it. There are alternative solutions. One I know of (but never used before) is to use the 'cgi' version of PHP which can run under different user names. Best place to ask would be a PHP list. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PHP] Compiling with latest SQLite?
> >Hello > > > >I noticed that the PDO::SQLite driver that comes with PHP 5.2.5 is > >3.3.17, while the non-OOP version is 2.8.17. > > > >Does someone know how to recompile PHP with the latest SQLite source? > > You don't have to, the alternative is: > > extension=php_pdo_sqlite_external.dll > plus the current sqlite3.dll > That works for me. > > php_pdo_sqlite_external.dll can be in the php/ext directory, > as usual. > You may have to copy sqlite3.dll to the apache/bin directory. > Kees, That's fine for windows, unfortunately the same facility is not available in the unix world. I compiled a PDO module using 3.5.4 (I think) using the source code from SQLite.org. Took a little fiddling but eventually got it to work. It past the same PHP tests as the latest PDO version (3.3.17) so presume it works okay. It was compiled using Ubuntu 7.04 x86. If anyone wants this shared library, let me know and I can send a copy. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Shared Cache for Processes
Hi all, Could the 'Shared Cache' option in SQLite theoretically improve the performance of the db if used by multiple processes? The application in particular is Apache using pre-fork processes accessing the same db. The info at http://www.sqlite.org/sharedcache.html seems to indicate it could benefit threads only. I believe it would not but would like confirmation from someone else. Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Advice on adding page cache statistics to Sqlite
Hi, I'm looking into adding cache statistics (at the pager level) to SQLite to try to better understand how often SQLite is 'hitting' the disk for a particular application. Two ways I've considered doing this are: 1. Add a static array to SQLite and populate from function 'pagerAcquire'. - Pros: Simple - Cons: Not particularly accessible, resides in memory, harder to dynamically adjust 2. Using some sort of special table (similar to sqlite_master) and populate using SQL. - Pros: Data accessible from application. Cons: Not sure where to start At this stage I'm only looking to record primitive information. If, for example, we went down route 2, below could be a suitable table definition. "CREATE TABLE sqlite_cache_stats(\n" " total_page_reads_from_cache integer,\n" " total_page_reads_from_disk integer,\n" " session_page_reads_from_cache integer,\n" " session_page_reads_from_disk integer\n" ")" Where 'total_*' fields are kept for the life of the database file and "session_*" are kept for the life of the connection, resetting on opening the database. I would appreciate some suggestions on how to progress this. Perhaps something similar may already exist, so any pointers would be beneficial Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Disk caching impacts performance.
> > I just tried (hadn't noticed that option before) to go from > 2000 to 4000 and 8000, without noticing any difference. I > might try next week to raise the page size to 50k and see if > it makes a difference? > On the presumption the Sqlite allocates new pages on the page boundary I would suggest you use multiples of the file system page size. I believe (but I could be wrong) both Linux and Windows NTFS defaults to 4096 bytes. If you chose 4,500 bytes in this scenario, for every call to retrieve one Sqlite page, it would require two calls to the OS. For whats it worth, when I did some performance testing a few years ago I recall there was no significant differenence using larger page sizes on a standard desktop machine but your mileage may vary. On a related note I do remember that when I was testing large reads (1M, 10M , 100M) using a single 'fread' call compiled in MSVC 2005 on Windows XP SP2 it had an interesting side effect in the fact that it performed the function by calling a lower level API multiple times with a size of 65,355 bytes, regardless of the original size requested in 'fread'. I believe this can be shown using the file system tools from SysInternals. Therefore I suspect there will be little-to-no benefit of page sizes greater than 64kiB on Windows XP. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Suggests for improving the SQLite website
> > Please continue to provide feedback. > Assuming the build process is fairly automated and not too onerous to implement I would like to see 'nightlys/weeklys' source and precompiled binaries of SQLite. I would imagine like me, many of us are behind company firewalls with no facility for using cvs externally. The thought of downloading every file using http://www.sqlite.org/cvstrac/dir?d=sqlite is not particularly pleasing ;) Admittedly not a presentation comment but rather an improvement to what the website offers. This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] CURRENT_TIMESTAMP value in single transaction
When enclosed in a single transaction, would inserting many rows into a table using the special default keyword 'CURRENT_TIMESTAMP' result in all of the rows guaranteeing the same timestamp value? If not, is there a recommended way to assign a unique value to a collection of inserts in a single transaction generated from a trigger? Thanks in advance Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How can I get my query to run as fast as SQLiteSpy?
> I have a simple table with five columns and 450,000 rows. In > SQLiteSpy, I can run "SELECT * FROM trend_data" and get all > 450,000 rows in 4.5 seconds. But in my program, if I use > sqlite3_prepare() and > sqlite3_step() until I run out of data, it takes 55 seconds > to get through all rows. A test with hard-coded junk data > showed that my program is account for only 2 seconds of that. > If I use sqlite3_get_table(), I can cut my time in half, > which is nice, but I'm still taking 25 seconds to get the > same data SQLiteSpy is getting in 4. > How is SQLiteSpy doing it, and can I use the same trick? I suspect that SqLiteSpy is not extracting all the rows since you can't see 450,000 rows on the computer screen at any one time. It probably uses some form of double buffering method which extracts data as you scroll through the rows to give the allusion that it has extracted all the rows. You can limit the number of rows and what position you start using the terms LIMIT and OFFSET within your SQL statement. See http://www.sqlite.org/lang_select.html Rgds This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance analysis of SQLite statements
> > You used to be able to compile with -DVDBE_PROFILE=1 to enable some > > special assembly-language instructions that would use > hi-res timers on > > ix586 chips to provide the cycle counts needed to execute each > > instruction in a VDBE program. But I haven't used that feature in > > years so I don't know if it still works or not. > > If you are talking of the "rdtsc" instruction, then to work > with current dual core CPUs the test must be "pinned" to a > single CPU, or you could be reading TSC values from different CPU's. > > Other than that, as long as the code works on a "single-core" > CPU, it should work on newer ones. > > Off course, there is that word: "should" ;-) > I would add that if using windows I recommend using QueryPerformanceCounter() instead of "rdtsc" as this function/call already handles the above mentioned issue which affects multiprocessor/multicore systems. In a previous job I did some performance metrics since I presumed it would be slow but remember being pleasantly surprised that it was as fast (circa 4 clock cycles to retrieve and store in integer) as using assembler code. This was using VS C++ 2003 compiler. I'm not aware of a similar call in Unix Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] what's the fastest way to get the record count of a table?
> > Hi, > > I want to check the record count of a table every 5 seconds. > > It seems there's only one way to get a table's record count: > > select coun(*) from ATable > > > > Create a separate table that has a single row and single > column for storing the record count: > > CREATE TABLE reccount(cnt INTEGER); > > Then create triggers that fire on every insert or delete and > update the record count table. > > CREATE TRIGGER rc1 AFTER INSERT ON tableA BEGIN > UPDATE reccount SET cnt=cnt+1; > END; > CREATE TRIGGER rc2 AFTER DELETE ON tableA BEGIN > UPDATE reccount SET cnt=cnt-1; > END; > > Then to get the record count do: > >SELECT cnt FROM reccount; > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > I've noticed this question has been raised a few times in the past and the workaround proposed is to create a "count" table to eliminate the need to do a table scan. I was wondering whether it is worth adding this feature to Sqlite so that a call to "select count(*)... " (which is not restricted with a WHERE clause) is retrieved from an internal "count" table? i.e. this performance workaround is part of the core sqlite code. Just an idea. Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Backing up a SQlite database
>Derrell, >Just to clarify, you don't need to use an exclusive transaction. That will acquire a write lock and unnecessarily block >all other readers as well. You only need to hold a read lock to prevent any other process from acquiring a write lock. >Dennis Cote I asked a similar question last year... http://article.gmane.org/gmane.comp.db.sqlite.general/17946 This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Retrieving id after insert
> how would I go about retriving the id that was assigned to it? I can't just execute another query such as select lab_id from lab_table where lab_name = > x because if there are two or more similar lab names, I have a problem. Thanks. http://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] From Windows file format to MacOSX
>I did the test with two versions of SQLite on Windows: with the 3.3 it doesn't work (which seems more or less >normal according to the documentation). >However, with version 2.8, the mac can't read the file. The error is: >Error: file is encrypted or is not a database How have you transferred the file from Windows to Mac? (FTP, Appletalk?) This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Performance of two queries, why such big difference ?
>Yes, but it completes the first query in 1ms somehow, so where is the >difference ? >From your original email... >WHERE channel = ? AND time >= ? >ORDER BY time ASC " >WHERE channel IN (-2,?) AND time >= ? >ORDER BY time ASC " .. the WHERE clause is different, that is why you are getting the different processing times. I think you are actually trying to find out why 1 query is 200x faster than the other. There could be a few reasons why they are different - One possible reason is that you have a large number of records where the channel value is set to "-2". Dan has also given a suggestion to try a different approach to get the same answer to the second query. Without giving more information, there is a limit to how much help members of this list can give. You may wish to post the results of the following queries which may help identify your problem. SELECT count(*) FROM files WHERE channel = ? AND time >= ? ORDER BY time ASC " LIMIT ? SELECT count(*) FROM files WHERE channel IN (-2,?) AND time >= ? ORDER BY time ASC " LIMIT ? Also tell us the values used for the "?" As I mentioned earlier, try using the EXPLAIN keyword which describes how the internals of SQLite are working. If you have difficulty in understanding the output of EXPLAIN, post the results to this mailing list and I'm sure someone (with greater knowledge of this command than myself) will respond. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Performance of two queries, why such big difference ?
>It takes <1ms to return 16 rows using the first query, but over 200ms when using the second one. What is wrong ? Is there a way to speed up the second >query ? If you are using a newer version of SQLite, I suggest you run the two queries again prefixing the SQL statement with EXPLAIN QUERY PLAN Things to read: http://www.sqlite.org/cvstrac/wiki?p=QueryPlans http://www.sqlite.org/lang_explain.html The time it takes to query is related to how many records SQLite will return for your WHERE statement (in your case you have to ignore LIMIT because it has to pull *all* the records initially to do the ORDER BY statement). Personally, searching and sorting ~1 million records in a fifth of a second sounds quite quick to me. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Sqlite crashes when i imort huge list
>The use of sequential numbers as the trailing part of the 'word' >results in a continual rebalancing of the b-tree with each insertion. Is that right considering it looks like you have not created an index on the word column before inserting the data? Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] disabling large file support
>gcc -g -O2 -DOS_BEOS=1 -DSQLITE_DISABLE_LFS -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR > -DHAVE_READLINE=0 -o sqlite3 ./src/shell.c ./.libs/libsqlite3.a -lroot -lbe -lreadline $ sqlite3 test.db SQLite version 3.3.5 Enter ".help" for Have you tried '-DSQLITE_DISABLE_LFS=1'? This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] OffTopic: Locking on Windows XP
>// Try and lock the file for writing
>ULARGE_INTEGER bytesToLock.QuadPart = 2147483648; // 2^31 if
(LockFile(h, 0, 0, bytesToLock.LowPart,
>bytesToLock.HighPart) == 0) { ...
>}
Sorry, finger trouble :)
Should read:
// Try and lock the file for writing
ULARGE_INTEGER bytesToLock.QuadPart = 2147483648; // 2^31
if (LockFile(h, 0, 0, bytesToLock.LowPart, bytesToLock.HighPart) == 0)
{
...
}
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
[sqlite] OffTopic: Locking on Windows XP
Hi,
This is more of a heads up than anything else. I'm developing an app
that writes to files using the Windows API. I actually used some code
from the SQLite file library hence this message.
The code snippet is:
// Try to open existing file
h = CreateFileA(path,
GENERIC_WRITE,
0,
NULL,
CREATE_NEW,
(FILE_ATTRIBUTE_NORMAL | FILE_FLAG_SEQUENTIAL_SCAN),
NULL);
if(h == INVALID_HANDLE_VALUE )
{
...
}
// Try and lock the file for writing
ULARGE_INTEGER bytesToLock.QuadPart = 2147483648; // 2^31
if (LockFile(h, 0, 0, bytesToLock.LowPart, bytesToLock.HighPart) == 0)
{
...
}
The app keeps the file locked until completion. The app takes a few mins
to run so I thought I test the locking. I opened Explorer and then
opened the file in Wordpad. Add a few garbage words and hit 'Save'. To
my surprise it had saved. I could confirm this by closing and reopening
the file in Wordpad seeing the change.
I checked my code and also tried commenting out the LockFile call (just
using the CreateFile call with the SharedMode parameter set to 0). Still
the same result.
I then changed the directory the app was writing to the local hard
drive. This time I got the usual "This file is opened by another
process..." popup when trying to open in Wordpad. So the difference was
the output directory.
It so happened that the output directory was on a network drive that had
been "Made available offline" (not sure its proper term) and was offline
at the time the app was running.
I then changed the output directory of the app to another online network
drive. Again, this time I got the usual "This file is opened by another
process..." popup when trying to open in Wordpad.
So unless someone could point out either a code/human error, I believe
locking capabilities are nonexistent for network drives that are have
file synchronisation enabled and are in offline mode.
Obviously DRH needs to verify but I'd imagine this could be a problem if
you use a multi-thread/multi-process application accessing a SQLite db
on a particular (though unlikely) network setup described above.
Regards
Nick
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
RE: [sqlite] Purging the mailing list roles. Was: Please RestoreYour Account Access
>+1 for moderated posting status after days, -10 for all of the >other suggestions that will cause me to jump through hoops to stay subscribed. I would imagine 95% of the content of this list is done by 20 or so posters. I'm not one of them. For similar reasons for staying subscribed, I don't want to jump through hoops for occasionally posting. If I feel I got something to contribute I just want to reply and that be the end of it. So +1 for moderated posting status *only* if its done without additional effort of the poster (human moderator?). This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access
> > I wonder if I need to implement some kind of mechanism that requires > you to either send a message to the mailing list or else renew your > subscription every 3 months. Does anybody have any experience with > other mailing lists that require such measures? > As most people, they are either email replies or a link to a website. Obviously it depends on your desired goal. If it is keeping automated scripts away then I would suggest a link to a website to confirm signup. On that website ask the user to enter a random set of digits displayed on the page. (That will keep the scripts that open links in confirmation emails away). If the goal is a general cleansing, then your suggestion to regularly "opt-in" sounds fine. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Serialising memory DB
Is there a way to serialise a memory DB so that it can be sent over a network socket and re-opened on another computer as a memory DB? I was thinking of using a SQLite table as a configuration system (like a .ini file) that can be sent over the network. Obviously you could use a file based DB instead and pass that around but I would like to be able to use memory DB for computers that either don't have hard drives or an OS that doesn't handle POSIX file operations (fopen et al). Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Hot Backups
For simple databases (say 10's of MB), I assume the safest way of backing up the database is to copy the file. For a modern desktop/server, this should take less than 20 seconds or so. While the database won't be under heavy utilisation, I would like to ensure that the database file is not corrupted during the copy, therefore I plan to do the following: 1. Connect to database and issue a BEGIN IMMEDIATE 2. Copy the database file to a new location 3. Issue a rollback and disconnect from the database. Is this sufficient to ensure a smooth database backup? Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] large table performance
>Given the schema below, feeding a million INSERTs into the database by >sqlite3_exec() takes about 30 minutes (this includes transactions, indices >and "PRAGMA synchronous=off" as well as a fully optimized build of sqlite). >Is there any chance to speed this up? Production datasets could easily bring a >billion genotypes ... I assumed from your description that you populate many rows in one shot. If that is the case I recommend that you just create tables without indices and populate the dB with the data. Then create the indices afterwards to improve reading performance. This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Problems with character '
Hi Niels, You've just found the first problem with using TEXT columns. What you need to do is put another ' in front of it i.e. ''. Depending on the language you are using, you may find it includes a function that "escapes" special characters like '. For example, PHP supply "sqlite_escape_string". The alternative is to store the data as binary. To do this, I believe you need to do the prepare/bind/exec statements. Hope that some help. Regards Nick -Original Message- From: Niels Boldt [mailto:[EMAIL PROTECTED] Sent: 28 February 2006 10:26 To: sqlite-users@sqlite.org Subject: [sqlite] Problems with character ' *** WARNING *** This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message. Hi Guys I'm having some problems dealing with the character '. Sqlite thinks fair enough that it should terminate the string, which I would like it to avoid. Are there any solutions to my problems. Thanks, Niels This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] New benchmark comparisons
>It did pretty well against Sql Server Mobile as well. I just posted those >benchmarks here: >http://sqlite.phxsoftware.com/forums/623/ShowPost.aspx Just had a quick look and noticed a peculiarity. SQLite seems to do pretty well across the board except in the graph for "INSERT 1 Row Test (implicit transaction)". It seemly does well except when using the SD card. Then I noticed your labelling is not consistent across all the pictures. Is that a mistake or is it what you had expected? For me it would be easier for my brain to compare if you had them in the same label order across the pictures (i.e. SQLite (M), SQLMobile (M), SQLite (C) ...) Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Thread handling in Windows
>Question: How do you do the same thing on windows? >How do you get a thread to clean up its thread-local-storage >obtained using TlsAlloc() and TlsSetValue() when the >thread terminates? You may find this example useful: http://www.codeproject.com/threads/tls.asp Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Regarding String Comparision
Rob/Ritesh >Also keep in mind that such a search is CASE SENSITIVE. >There are two solutions to that, either makes the collation >case insensitive or do a: I don't have access to SQLite immediately but I seem to remember in one of my applications that the use of select * from test where filename like '%file%'; would return string that are case INsensitive. (ie 'file', 'FILE', 'File' ...) Ritesh I would suggest that you confirm this before relying on it. Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] optimizing out function calls
Have a look at archive here http://thread.gmane.org/gmane.comp.db.sqlite.general/13781 At the time I was using the random number generator function and was confused about its usage. It may help some of you. Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] getting only count of records
>Is this the right way to do or is there a better way ? In addition to the comments others have made, I would suggest you use: 'select count(*) from tx where VehicleID = ' + QuotedStr(VehicleID) 'count(*)' is a special case for the count function that returns "count of records". In terms of performance, this will have no measurable benefit with SQLite. However other database's can often return results substantially quicker (particularly when there is no 'where' clause) than using 'count()'. Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Getting CVS source without CVS
Hi, It there a way to download the latest source from CVS without using the CVS tool? I only have access to the internet through HTTP/FTP and via a company proxy. Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Simple benchmark results on COUNT, MIN, MAX
I'm sharing a simple benchmark done to work out the most efficient way to count the number of rows in a table for my app. The first thing I should point out, this will only work when you adhere to the following: * You want the total number of rows in a table (i.e. no WHERE clause) * Any deletions from the table are done ONLY from the beginning of the table Create 100,000 rows in the 'event' table like: INSERT INTO event VALUES (NULL, 1, 11, 'sometext 1', '01-01-05 12:00'); INSERT INTO event VALUES (NULL, 1, 11, 'sometext 2', '01-01-05 12:00'); INSERT INTO event VALUES (NULL, 1, 11, 'sometext 3', '01-01-05 12:00'); ... INSERT INTO event VALUES (NULL, 1, 11, 'sometext 9', '01-01-05 12:00'); INSERT INTO event VALUES (NULL, 1, 11, 'sometext 10', '01-01-05 12:00'); Results SQL, Attempt #1 (ms), #2 (ms), #3 (ms) SELECT count(*) FROM event,83.49, 83.31, 83.48 SELECT count(eventID) FROM event, 109.09, 106.92, 107.98 SELECT min(eventID) FROM event, 0.64,0.61,0.59 SELECT max(eventID) FROM event, 0.68,0.57,0.57 So (for me at least), it is quicker to run two separate min/max select statements and compute the number of rows than to use the count function. An interesting fact for those that are still reading. The times for the following SQL statement: SELECT min(eventID), max(eventID) FROM event took 155.18ms, 152.08ms, 154.70ms Regards Nick Supporting Info: This was using SQLite 3.2.3 in SQLiteSpy 1.2 The table was created with: CREATE TABLE event ( eventID INTEGER PRIMARY KEY AUTOINCREMENT, elementID INTEGER NOT NULL, typeID INTEGER NOT NULL, value TEXT NOT NULL, datetime TEXT NOT NULL ); Email if you have any questions This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Temporary tables and locking
Hi I don't think I fully understanding the locking strategy with temporary tables. The documentation at http://www.sqlite.org/cvstrac/wiki?p=MultiThreading suggests that Temporary tables are not locked like the normal database. If you can read and write to temporary tables and their is no locking mechanism, then I assume the scope of a temporary table is restricted. Is the scope of temporary tables within a thread or a process? Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Network-based DB performance for Mozilla
>If you can't tolerate the delays accessing the database over the >network, can you make a copy of the database in a temp directory on the >local machine on startup. If you copy the file after you lock the >profile it should be safe to copy down to local storage. Then use the >local database while the application runs, and finally copy the database >back to the server, if it has been modified, when the application quits. Just to add, this is similar to how Roaming Profiles (corporate environment) work on Windows Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] need to write during a long read
>take longer than 5 seconds. These reads are being done by PHP using the
>PDO driver for sqlite3. My understanding of the problem is that the PHP
>reader is holding a SHARED lock for longer than 5 seconds, so while the
>C program can acquire a PENDING lock, it can not get the EXCLUSIVE lock
>inside of 5 seconds and thus times out.
I've also used the PDO driver in the past and it took me a while to realise
that the SHARED lock was not being released by PDO/SQlite under after it
tried to retrieve the next row and failed. So in the following example loop:
(Run SQL select statement)
while (fetchArray())
{
/* Long time spent processing here */
}
The SHARED lock is held until the end of the while loop instead of releasing
before the loop (which I was expecting). Obviously if you have a significant
amount of processing in the loop, its not difficult to get writer
starvation.
A question to those who know - when (in terms of which C API call) does
SQLite release the SHARED lock after a read?
And a general survey to everyone... in your applications, what is the
'standard' practice to handle a SELECT statement that may return more than a
few rows? Can temporary tables be used without still holding the
database-level lock?
Sorry Mark, I've noticed I've diverged from your question a tad.
Thanks
Nick
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
RE: [sqlite] Can you use random(*) to retrieve a pseudo random r owfrom 3.2.3?
>Nick, > >I just thought I should clarify my SQL. The select clause should be > >select * from MyTbale join... > >since the random number is already included in each row of the joined >table. >Dennis Cote Thanks Dennis for the tip. Did you give it try? I've tried it and its not selecting any rows after a couple hundred attempts. I appreciate its "random" but to test I subsequently put in a row with the min/max random values and that row is still not selected. CREATE TABLE MyTable(start_col int,end_col int); INSERT INTO "MyTable" VALUES(-2, 2); INSERT INTO "MyTable" VALUES(-21, 21); INSERT INTO "MyTable" VALUES(-21, 21); INSERT INTO "MyTable" VALUES(-2147483648, 2147483647); sqlite> select * from MyTable join (select random(*) as number) as rand where st art_col >= rand.number and end_col < rand.number; sqlite> Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Can you use random(*) to retrieve a pseudo random row from 3.2.3?
Hi, I trying to see if I can use the random(*) function to select a pseudo-random row and not sure if it will work as I expect. I want to use it to select a row based on two column values, however I suspect that each time random(*) is called in a single SQL line, it retrieves a different number. An example of how I would like to use it would be: select *,random(*) from myTable where start_col >= random(*) AND end_col < random(*); However, the following SQL generates different random numbers as shown: sqlite> select random(*), random(*); 733511021|-1742103221 Does that mean that my original SQL would not use a single random number but multiple? Another test below using a single column seems to confirm that it uses different random number for selecting and displaying the results. sqlite> select random(*) where random(*) between -2 AND 2; 759396015 sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; sqlite> select random(*) where random(*) between -2 AND 2; -1605422300 sqlite> select random(*) where random(*) between -2 AND 2; Am I trying to do the impossible here? I've enclosed an "explain" for those that are willing to interpret it for me sqlite> explain select *,random(*) from myTable where start_col >= random(*) AND end_col < random(*); 0|Goto|0|18| 1|Integer|0|0| 2|OpenRead|0|2| 3|SetNumColumns|0|2| 4|Rewind|0|16| 5|Column|0|0| 6|Function|0|0|random(-1) 7|Lt|361|15|collseq(BINARY) 8|Column|0|1| 9|Function|0|0|random(-1) 10|Ge|361|15|collseq(BINARY) 11|Column|0|0| 12|Column|0|1| 13|Function|0|0|random(-1) 14|Callback|3|0| 15|Next|0|5| 16|Close|0|0| 17|Halt|0|0| 18|Transaction|0|0| 19|VerifyCookie|0|1| 20|Goto|0|1| 21|Noop|0|0| Many Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Possible bug regarding endiannes and realstorageclas s (sqlite3)
Richard,
As of interest, I've modified your code below and ran on two systems:
a) Sun Ultra-Enterprise SPARC (gcc)
b) Windows XP AMD (VS .NET 2003)
Code snippet:
union utest {
double r;
long long i;
unsigned char z[8];
};
float test_2;
int main(int argc, char **argv){
union utest x;
x.r = 1.0;
printf("%02x%02x%02x%02x%02x%02x%02x%02x\n",
x.z[0], x.z[1], x.z[2], x.z[3], x.z[4], x.z[5], x.z[6], x.z[7]);
x.i = 1;
printf("%02x%02x%02x%02x%02x%02x%02x%02x\n",
x.z[0], x.z[1], x.z[2], x.z[3], x.z[4], x.z[5], x.z[6], x.z[7]);
test_2 = 1.0;
printf("%08x\n", *((unsigned int *) _2) );
return 0;
}
Results:
a)
3ff0
0001
3f80
wsi012 (16) file test2
test2: ELF 32-bit MSB executable SPARC Version 1, dynamically
linked, not stripped
b)
f03f
0100
3f80
Regards
Nick
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.
RE: [sqlite] Possible bug regarding endiannes and real storagecla ss (sqlite3)
>SQLite tries to store everything on disk as big-endian. That >means it always byte swaps on little-endian machines (basically, >ix86) and omits byte swapping for big-endian machines (which is >to say, everything other than ix86.) The byte swapping happens >for integers *and* floating-point numbers. When I was doing research [1] a while ago into how little endian architectures store IEEE-754 floating point numbers, I understood that they store them exactly like big endian platforms unlike integer formats. I never found information to suggest they were different though I've never done any experimentation in this area. [1] Both AMD & Intel websites publish (with a little delving) technical documentation on their respective processor architecture. This may give more information on the matter: http://babbage.cs.qc.edu/courses/cs341/IEEE-754.html Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] database table is locked
I think I might be getting confused on this subject as well. Does this mean that SQLite 3.x can NOT process multiple inserts/updates within one transaction if it is working on the same table? ie Below would return "database table is locked"? BEGIN TRANSACTION SELECT * from table1 WHERE col > x UPDATE table1 SET col = ... INSERT INTO table1 COMMIT TRANSACTION Nick -Original Message- From: Thomas Briggs [mailto:[EMAIL PROTECTED] Sent: 12 May 2005 16:11 To: sqlite-users@sqlite.org Subject: RE: [sqlite] database table is locked *** WARNING *** This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message. Aha! Now I understand what's going on. I have been completely missing the fact that everyone is trying to update the same table they're reading from. I know that's obvious to you guys, but I completely missed that subtle fact. Everything makes sense now. Thanks for your patience. :) -Tom > -Original Message- > From: Martin Engelschalk [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 12, 2005 9:53 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] database table is locked > > @Thomas Briggs, Jay Sprenckle > > I use the C Api described at http://www.sqlite.org/capi3ref.html. > > My code seemed to work with sqlite 3.0.7, but I cannot be too sure > because my project is in development. It would take some time > to check > with 3.0.7 > > My code worked roughly like this: > I created an update - Statement using compile(). > I created a query (simple select from one table) using compile() and > fetched the data using sqlite3_step() > When i found a record i needed to update, i bound 2 Variables using > sqlite3_bind_text() to the update statement, one of which was the > integer primary key. > The sqlite3_exec() failed with "database table is locked". > > This is a different thing to "database locked", i think. > > Martin > > Thomas Briggs schrieb: > > > This question seems to come up often, and I'm still confused as to > >what problems people are having. What APIs are you using to perform > >these steps? In particular, when you want to update a row, are you > >using a prepared query that is executed multiple times, or are you > >creating an SQL statement and executing that with > sqlite3_exec? Are you > >using 2.8 or 3.x? Maybe the confusion on my part is due to different > >database versions. > > > > I'm confused as to why executing a query would lock the database. > >The only thing I can think of is that the query required a > temp table, > >and the creation of that temp table led to the database being locked. > >The entire many readers/single writer concept makes no sense if > >executing any query locks the whole database. > > > > -Tom > > > > > > > >>-Original Message- > >>From: Thomas Fjellstrom [mailto:[EMAIL PROTECTED] > >>Sent: Thursday, May 12, 2005 7:10 AM > >>To: sqlite-users@sqlite.org > >>Subject: Re: [sqlite] database table is locked > >> > >>On May 12, 2005 04:59 am, Martin Engelschalk wrote: > >> > >> > >>>Hello, > >>> > >>>i open cursor on a table and retrieve rows from it. > >>>For every row i decide whether to update it. However, when > >>> > >>> > >>executing > >> > >> > >>>the update I get the error "database table is locked". > >>>My application is the only one working on the table. > >>>Is it illegal to update a table while selecting from it or > >>> > >>> > >>am i doing > >> > >> > >>>somethin wrong? > >>> > >>>Thanks, > >>>Martin > >>> > >>> > >>Yup. you'll have to scan for updates to make, then after the > >>scan, make the > >>updates. > >> > >>-- > >>Thomas Fjellstrom > >>[EMAIL PROTECTED] > >> > >> > >> > This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
RE: [sqlite] Does sqlite really support transaction?
This may not be the solution for you but I use the "on conflict" (http://www.sqlite.org/lang_conflict.html) algorithms when creating tables to automatically rollback when trying to insert duplicate contents. Nick -Original Message- From: John Buck [mailto:[EMAIL PROTECTED] Sent: 12 May 2005 17:12 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Does sqlite really support transaction? *** WARNING *** This mail has originated outside your organization, either from an external partner or the Global Internet. Keep this in mind if you answer this message. I can think of no way to implement complex atomic transactions with rollbacks in a flat SQL script. You need some sort of higher level language to make decisions about results .. IE C++ etc.. -- JB -Original Message- From: Vladimir Zelinski [mailto:[EMAIL PROTECTED] Sent: Thursday, May 12, 2005 11:26 AM To: sqlite-users@sqlite.org; Jay Sprenkle Subject: Re: [sqlite] Does sqlite really support transaction? Here is the problem. My external program builds sql script and places into file my_script.sql Then I execute statement "sqlite3 my_db < my_script.sql" as a system call from my external program. I can check result of execution this statement and it always returns 0 (success) regardless of actual SQL result. I have absolutely no knowledge of my SQL execution status. Vladimir --- Jay Sprenkle <[EMAIL PROTECTED]> wrote: > On 5/12/05, Vladimir Zelinski <[EMAIL PROTECTED]> > wrote: > > I understand that. Question is HOW I can check if > > statement failed or successful? What variable > contains > > status of the result of execution. > > The return code from the call that executes it. > Are you running SQL via code or from the command > line interpreter? > This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] OT: Suitable table schema for object relationship
Hi, I trying to design a table schema that will allow link relationships between objects with the following features: * Show a direct link relationship between two objects * Be able to remove relationships * Find indirect links between objects (This is were it gets tricky) I have persisted with trying a number of table schemas over the last few weeks to what initially seems a simple problem. However, I having difficulty 'nailing it on the head'. Assuming each object is represented by a letter. How would you design a schema for the above features? A --> B --> C \ \--> D --> E In this example, A and B have a direct relationship whereas A has an indirect relationship with C, D, E. D has a direct relationship with B and E whereas it is indirectly related to A and C. I can think of two ways to implement the indirect relationships. The first way was storing only direct relationships in the database and used the application to recursively search for indirect links. (ie A is linked to B. B is linked to C and D. C is linked to ... and so on). This would be slow and a question is raised on how many times you would do a recursive search. The second way is to store the indirect links as well (ie A to C, A to D, A to E). This will grow the number of rows in the db at an increasing rate - I've modelled it to be 0.5 * N * (N + 1) where N is the number of links (4 in the above). I don't expect the size to be an issue but I can not figure a good way to remove indirect links from the db. For example, How would you delete the indirect relationship A to E if the direct link B to D was removed? What if (not shown) E also had a direct relationship with A. The removal of the B --> D link would still leave indirect links with E and C. I appreciate this is not related to SQLite (though it is the db I'm using), but I welcome any ideas to help me solve this 'simple' problem. Many Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Question regarding Memory Tables
Hello, I was thinking about using memory tables for short term data and was wondering whether SQLite does anything to stop the OS paging the memory to disk? I know there is a POSIX function "mlock" that stop memory being paged to disk but I believe the program has to run as root/admin since this technique can be used to exhaust all the memory on a computer. Any thoughts? Many Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
