Re: [sqlite] Statistics on integer primary key - Cardinality
Hi Peter, Regarding: Selectivity is known -- since it's a primary key, which is unique -- it will be 1. Cardinality can vary. I wonder if you're referring to the definition of *cardinality* as used in mathematics http://en.wikipedia.org/wiki/Cardinality In mathematics, the cardinality of a set is a measure of the number of elements of the set. as opposed to the definition common in SQL: http://en.wikipedia.org/wiki/Cardinality_%28SQL_statements%29 In SQL (Structured Query Language), the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. Regards, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very Slow DB Access After Reboot on Windows
Regarding: Also...try doing a copy my.db nul: to get it cached once before you use it. Am I right in thinking he may want to include the /b (binary) option so that the copy doesn't stop at the first nul byte? copy /b my.db nul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very Slow DB Access After Reboot on Windows
Regarding win/dos COPY command and /b option: Thanks for correcting me, Michael. I somehow thought that NUL being the target would introduce some sort of text affinity but it's good to know the truth instead. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to invoke successive SQLite script files
Regarding: My question is, what command do I use in script-A to invoke script-C, script-B, etc? Is it the same .read command? I believe it does work that way. What did you get when you tried it? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite Insert Speed Optimization
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Drescher Sent: Tuesday, July 13, 2010 12:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite Insert Speed Optimization I also wrap my statements (about 500 inserts at a time) with a begin/end transaction. After these 500 i take a few seconds to read more data so sqlite should have time to do any housekeeping it might need. Wrap more into a transaction. 500 is too small of a percentage of a million. John John, I was wondering if that's really so. Wouldn't the marginal speed improvement be quite small? Is the percentage of the final rowcount really a criterion? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ticks to unixepoch date
Hi Roberto, Re: Please find a test database in the attachment. I don't think attachments are permitted on the mailing list messages. If it's only ten rows, perhaps just using the command line utility to perform a .dump Command and pasting the text into a new message would do the trick. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite Insert Speed Optimization
Griggs, Donald wrote: Is the percentage of the final rowcount really a criterion? The answer to that, according to my brief exploration, is somewhere between yes and very much yes, depending on various factors. Thanks, Eric. I guess I was wondering if the fastest records-per-transaction value would depend on the page cache and be more or less independent of the total records to be imported. (Indicies omitted.) So, the records-per-transaction for import to a 20 million row table should be twenty times the size for a 1 million row table? I confess I've got a lot to learn. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] network access problem
I just tried -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Monday, May 24, 2010 2:37 PM From: sqlite-users-boun...@sqlite.org on behalf of Art Sent: Mon 5/24/2010 1:28 PM To: sqlite-users@sqlite.org Subject: [sqlite] network access problem Running in sqlite application in Virtual Box, attempt to open a database with sqlite from a shared network folder \\Vboxsvr\testdata however the open16 and openv2 (with read only) both fail --- rc = 14. File opens file if moved locally to hard drive. using latest version of sqlite3 3.6.23.1 this seems to be a bug in the open code of sqlite when accessing a file across a network. Thank you, Art Zerger azer...@yahoo.com === Hi Art, I just succeeded in opening a db with a UNC path, under Windows XP Pro, using the same version (3.6.23.1) with the command-line utility -- but I do NOT use virtual box. Does the command-line utility fail for you under virtual box? Might you attempt it without virtualbox? Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very Odd... where field1='' or field2='' is slow
Regarding: select * from table1 where field1='x' or field2='y' Hi, Ray, I believe you sent two messages -- the first with OR and the second with AND. Since your subject consistently says OR and your last email says AND I'll assume you meant AND. Sqlite using a maximum of one index per table per select. In order to perform your OR select, it must scan every the table. You may want to create a compound index of both fields. (Or maybe a UNION ALL would be fast? You can use EXPLAIN QUERY PLAN to analyze.) Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Ecforu, Re: What's the diff? In sqlite, LIKE without a % (percent-sign ) would be a case-insensitive search, whereas == would be case-sensitive. sqlite select 'cat' like 'CAT'; 1 sqlite select 'cat' == 'CAT'; 0 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Would this query help determine if any extraneous characters present? SELECT * FROM MyTable WHERE LENGTH(resourceType) 3 AND resourceType LIKE 'PSM' ; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Direct access of table data
Hello Biggs, this is Griggs, Re: Is there a way to read the values of a table directly without building and executing a query. I have a function that has predefined memory... No easy way. The sophistication of database abstraction is the reason you presumably chose to use sqlite. Re: I thought it might perform much [better] without all ... If you've measured this and the query truly takes too long, you might try (in order): 1) trying to optimize the query (do you have ideal indexing, for instance?) 2) restructure your data/program 3) use a simple file rather than a database Regards, Donald Griggs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW: Direct access of table data
From: Nathan Biggs [mailto:nbi...@mycfs.com] Sent: Wednesday, April 21, 2010 2:43 PM To: Griggs, Donald Subject: Re: [sqlite] Direct access of table data Griggs, Thanks for the quick reply. I guess I'm just getting greedy since sqlite is so much faster than our standard databases (progress). When you run a query for select * from table, does it literally copy the contents of the table, or does it just build a structure that points to the data in the table? On 4/21/2010 2:29 PM, Griggs, Donald wrote: Hello Biggs, this is Griggs, Re: Is there a way to read the values of a table directly without building and executing a query. I have a function that has predefined memory... No easy way. The sophistication of database abstraction is the reason you presumably chose to use sqlite. Re: I thought it might perform much [better] without all .. If you've measured this and the query truly takes too long, you might try (in order): 1) trying to optimize the query (do you have ideal indexing, for instance?) 2) restructure your data/program 3) use a simple file rather than a database Regards, Donald Griggs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: Direct access of table data
Briggs, Re: I guess I'm just getting greedy since sqlite is so much faster than our standard databases (progress). That's a distinct possibility.;-) Re: When you run a query for select * from table, does it literally copy the contents of the table, or does it just build a structure that points to the data in the table? When you wrote points to the data in the table -- by table do you mean the data on your disk drive? If so, then yes, sqlite must read the data into RAM, and at least one ram-to-ram copy is implied. (The data for a row might span multiple db pages.) If you're using an sqlite wrapper, that may possibly perform a copy as well. If you're using multi-megabyte BLOB's then these can be an exception, as you may read them incrementally: http://www.sqlite.org/c3ref/blob_read.html If, however, you're NOT using large blobs, then considerations such as your synchronization level, http://www.sqlite.org/pragma.html#pragma_synchronous indexing, page size, etc. are usually much more important to performance than a ram-to-ram copy. Again, have you measured the time to perform the query in question? Is it worrisome? If not, don't worry. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Northwind example database
On 27 Mar 2010, at 10:46am, GeoffW wrote: Just for educational purposes I have been experimenting a little with the Northwind Sqlite database contained on the sqlite official site. Download link: http://download.vive.net/Northwind.zip. Am I misunderstanding here or are the dates in the wrong format for sqlite within this converted database ? Assuming it is wrong and not my understsanding, are there any easy ways to get the dates reversed and corrected to sqlite order and written back out to the database ? Perhaps this is better performed in the calling language, but the following sql should reformat these dates. Of course, you'd need to substitute and repeat for the other fields. -- Reformat date from, e.g., '1/5/2010 12:00:00 AM' to '2010-01-05' update employees set birthdate = replace (birthdate, ' 12:00:00 AM', ''); update employees set birthdate = '0' || birthdate where substr(birthdate, 2,1) == '/'; update employees set birthdate = substr(birthdate, 1, 3) || '0' || substr(birthdate, 4,99) where substr(birthdate, 5,1) == '/'; -- Date should now be formatted as dd/mm/ -- Now change to -mm-dd update employees set birthdate = substr(birthdate, 7,4) || '-' || substr(birthdate, 1,2) || '-' || substr(birthdate, 4,2); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 error: database or disk is full when commit transaction
Tim, Regarding: I guess when do the transaction, the database was duplicated, and it caused no free disk space Unless you do something such as vacuum the database should not actually be duplicated. A rollback journal file, though, **is** created. (details at http://www.sqlite.org/atomiccommit.html and elsewhere) Others on this list can likely give better advice, but I wondered if: 1) Perhaps deleting only part of the table in multiple transactions would help. 2) If your operating system allows it, you might experiment with applying transparent compression to the database directory and perhaps more. 3) If #2 is not feasible, would applying compression yourself to certain data fields be worthwhile? I assume you're working with an embedded device of some sort which would explain such severe memory constraints? Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Sqlite User
Hello, Alexis, Regarding: how many user at the same time ken use sqlite I'm afraid the answer is, it can vary widely depending upon your application statistics, your programming, your CPU speed and ram, etc. I'm writing to you from the sqlite users mailing list. Perhaps you could write back and say something about your level of familiarity with databases (e.g. are you a programmer?, what databases have you worked with?, etc.) and a bit about your intended application. A lot of the simplicity of sqlite comes from the fact that no there is no server/client and sqlite locks the entire database during transaction writes. This simplicity comes at the cost of some amount of concurrency. You may want to refer to item 5 at: http://www.sqlite.org/faq.html As well as http://www.sqlite.org/whentouse.html http://www.sqlite.org/lockingv3.html Sometimes, the answer can only be determined through your own testing, but some applications are at one extreme or another, and the list members can give you general advice. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] suitable UI -- RE: What's the problem with my INSERT clause?
Regarding: BTW, can you recommend me suitable UI to work with SQLite? Maybe you've already tried out some of the GUI tools listed at: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools I'm not sure what your requirements and preferences are, or what operating system you're using, or whether you require an open-source solution, but I personally use Sqlite3Explorer, among other tools. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Sqlite files in temp folder
Mark, Can you offer any advice on how to stop the files being written, and remove the programme(s) that are writing these files. Hello, I'm (simply) a member of the sqlite mailing list. As you probably know, it is not sqlite itself that is creating these files, but some program on the system which incorporates sqlite -- and we have no *direct* way of determining what program that is. I googled briefly and found the following two links which may well be relevant regarding the BBC's iPlayer and the service Kservice.exe: http://hintofsarcasm.com/2006/01/12/sky-by-broadband http://www.techsupportforum.com/microsoft-support/windows-xp-support/451685-massive-sqlite-temp-files-filling-hard-drive-help.html If the symptom described in the above links does not fit your situation, perhaps I could suggest: 1) Downloading one of the many sqlite GUI front-end programs, and see if either the column/table names, or the data values themselves give you some hints as to what program may be at fault. 2) If #1 is beyond your capabilities, you might take one of the smaller sqlite files, compress it, and email it to me directly, and I will make a quick look-see. Perhaps others on this mailing list have better suggestions. Hope this helps, Donald Griggs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: no such table on .import
sqlite .import C:\HEAD.txt head; Error: no such table: head; Any idea why I'm getting no such table? Hi Phil, The dot commands -- such as .import -- don't require a semicolon terminator, and the utility is interpreting your trailing semicolon as part of the table name. You may also want to surround 'c:\head.txt' with single quotes, but I think if you omit the semicolon you'd finally get ahead with your work. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index creation in a memory database
Regarding: Maybe the question for this issue is why it's not ok this syntax: create index memoria.Dispositivos_TipoDispositivo on memoria.Dispositivos From my reading of the CREATE INDEX syntax at: http://www.sqlite.org/lang_createindex.html the parentheses are required, not optional. Are they optional under the sql99 standard? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique column constrained to 0 .. 2^32?
Regarding: ...is to use an MD5/sha1 or similar checksum of the record and use the last 32 bits of that checksum. It is extremely unlikely for there to be a collision ... Except that the OP wrote: ...I don't think it works very well for 2^32 possible values (when there may well be only a couple of hundred unused ones) I think that, with 500 unused values, you'll have consumed over 99.8 percent of your values -- and you appear to be describing this situation as ordinary. (Collisions, in this situation, of course, would be in the majority.) Is your application such that, being just a hair's breadth away, it will almost certainly then exhaust the rest of your 2**32 values, resulting in bad things happening? If there may well be only a couple of hundred unused values, then unless your rows and indices are tiny, wouldn't your database be so large that a 2GB table of integers will be small in comparison?Anyway, Scott has beat me to a suggestion that you could just store the ID of the *deleted* rows while monitoring your maximum ID to be sure it remains under 2**32 (i.e., 4294967296). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] selective result columns
I'm not clear. Were you able to use Simon's syntax link: http://sqlite.awardspace.us/syntax/sqlitepg09.htm To correct your syntax? SQL works with sets, and will always return the same number of columns, but you *can* use sql to force a NULL, an empty string, or a space to be returned for a column value. Does that not meet your need? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim'ming a column
Regarding: I know that trim(vEmail) will do it, but what would be the command to run to trim all of the existing records? UPDATE LSOpenJobs SET vEmail = TRIM(vEmail); Given the state of the world economy, I hope that LSOpenJobs is an ever-expanding table. ;-) Regarding: Also, how to I trim specific characters? If you don't need to keep such characters at ALL, you can use REPLACE with hex literals to change unwanted, non-whitespace characters to spaces, then TRIM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim'ming a column
UPDATE LSOpenJobs SET vEmail = TRIM(vEmail); Given the state of the world economy, I hope that LSOpenJobs is an ever-expanding table. ;-) Why do you say this? Will the table grow because of this statement? I have fixed the client data entry to take care of this in the future, but there are a lot of records that I didnot clean previously, so I need to do this, at least once. = Jose, I beg your pardon. My comment ending with ;-) was simply a poor joke, presuming that LSopenJobs was a list of available jobs -- something I hoped would increase during a time of large unemployment in the world. The sql below should not increase the size of your database. UPDATE LSOpenJobs SET vEmail = TRIM(vEmail); Wishing you peace and prosperity, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted SQLITE database recovery
Regarding: I have a small corrupted sqlite 3 database. ... Are there any tools for a more detailed analisys? I'm not aware of any, Marko. You may want to try .dump mytable1 .dump mytable2 etc On individual tables to see if some are salvageable. For failing tables, if you *can* read a certain number of records, you might try a manual binary search for a ROWID that is readable beyond the failure area, then .output mySalvage.txt select * from myBadTable where ROWID = myROWIDgood; .output stdout I realize that neither of these methods is guaranteed to work, and that you may already be aware of them. Your backup may turn out to be your best option. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New sqlite bug report: Problem with /* */ comment followed by Non-SQL (sqlite-specific) command
Regarding: /* Here comes the comment. Source is reduced to show core of the problem. */ .mode column == FWIW, I reproduced error on 3.16.19 windows. Of course, I presume this is not a problem with sqlite itself, but with the sqlite3 utility program. Interesting in that I can make the error vanish by either: - Including any sql command or pragma ending in semicolon before the dot command - Enclosing EACH line of initial comments with /* */ (instead of surrounding multiple lines of comment with a single /* */ pair. - even including an unnecessary semicolon on the end of a comment, like -- puppies; will prevent the error. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New sqlite bug report: Problem with /* */ commentfollowed by Non-SQL (sqlite-specific) command
comment lines in sql begin with -- Regards, Simon === But both types are supported, right? http://www.sqlite.org/lang_comment.html C comments can span any number of lines. C-style comments begin with /* and extend up to and including the next */ character pair or until the end of input, whichever comes first. C-style comments can span multiple lines. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] index optimal?
Hi Sylvain, Regarding: can you describe an output of a explain? == A very short answer could be that, in the output of EXPLAIN QUERY PLAN If you see the name of an index, then that index is used. Regarding: is there something to tell us the best index to have for a specified query? == Your question might be interpreted as: a) Given the indexes defined in my schema, which ones are ideal to use for a specific query? Or b) How do I create ideal indexes? For each of these questions, the answer is Both skill and craft are sometimes involved, therefore there will often not be an absolute rule. For question a (which to use), sqlite itself tries to determine this, and often does a very good job. In some cases, the ANALYZE command helps sqlite make these decisions. For question b (how to create ideal indices) I am definitely not an expert, but I think some general guidance might be: -- For a given SELECT, sqlite will use, at most, one index per table. -- An index on a large table is usually more useful than an index on a tiny one. -- An index on a column with many repeated values (low specificity) may be less useful. -- Compound indexes are sometimes used to good effect, but remember that they are used from left to right -- Your own testing in your particular database will give the most authoritative answers. -- This list is for sqlite, but I suspect you'll want to seek out general SQL books and other resources. Maybe this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to skip the first field on .import
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ted Rolle Sent: Friday, October 30, 2009 10:36 AM To: sqlite-users Subject: [sqlite] How to skip the first field on .import The first field in my table is ID primary integer autoincrement. = Do you instead mean INTEGER PRIMARY KEY AUTOINCREMENT ? I read that if it is set to NULL it defaults to the maximum value possible. Not a Good Thing(tm). = Can you post the url where you read this? From page http://www.sqlite.org/autoinc.html it says something quite different: If no ROWID is specified on the insert, an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking candidate ROWIDs at random until it finds one that is not previously used. How do I let this start out at the default value and auto increment? My column separator is '|'. = I'm guessing you're using the command-line utility program. Others may have better answers, but: -- If you're restoring a table you've dumped previously, then you probably want the primary key values to be set explicitly to their previous values -- i.e., you don't want them to take on new values. -- If this is an initial data import, one way to do this is to .import to a temporary table withOUT the ID key, then use an sql command to transfer the data to the permanent table (which DOES use INTEGER PRIMARY KEY AUTOINCREMENT), e.g. INSERT INTO myPerm SELECT NULL, * FROM myTemp Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to decide which table is the outer table and whichtable is the inner table?
Re: How to decide which table is the outer table and whichtable is the inner table? Possibly relevant: http://www.sqlite.org/lang_analyze.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to input a double num?
Regarding: How to input the num 212345678901234567890123456.988290112? Purely curious, if it's not confidential -- how are you acquiring numbers of such precision? If these are measurements, I'm just awed by the precision the device is achieving. If instead these numbers represent something other than a measurement, you might want to consider storing them in a different form. Donald Griggs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grammar of X is Y
Depends on what your definition of is is. (Sorry, non-English speakers. This is a tiny joke based in American political history) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite server for Windows/Linux?
Gilles, I know almost nothing about them, but the following may possibly be of interest: http://sqlrelay.sourceforge.net/ http://www.it77.de/sqlite/sqlite.htm http://www.sqlite.org/cvstrac/wiki?p=ClientServer Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?
Regarding: ...copy the output of a SELECT into the clipboard so I can paste it elsewhere. Sorry for piling on another me too post, but: I'm grateful to Mike Cariotoglou for Sqlite3Explorer http://www.singular.gr/sqlite/ You can copy/paste the results of a select instantly into a program that accepts tab separators (such as Excel). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gilles Ganault Sent: Friday, October 23, 2009 11:12 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe? On Fri, 23 Oct 2009 10:19:17 -0400, Griggs, Donald donald.gri...@allscripts.com wrote: I'm grateful to Mike Cariotoglou for Sqlite3Explorer http://www.singular.gr/sqlite/ Unless I overlooked it, it won't let me copy all the rows into the clipboard (tried CTRL-A, also tried selecting the first and the last row followed by CTRL-C, to no avail). Also... V3.01 29/11/2007 Access violation at address 006C304F in module 'sqlite3Explorer.exe'. Read of address 0039. Too bad :-/ Thanks anyway. = Regarding Sqlite3Explorer: ..it won't let me copy all the rows into the clipboard ... I should probably have mentioned that RIGHT-CLICK within the output grid provides an option to COPY RESULT SET TO CLIPBOARD. I just tried it with 9150 rows, and they appeared to paste correctly into a blank Micro$oft Excel spreadsheet. For truly *huge* outputs, you may prefer a script to export from sqlite3.exe and import elsewhere. Regarding: Access violation I cant' say. I think the author may be on this list, or you can search out his email address. Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite DB Structure
Regarding: I NEED HELP! I have SQLite3 DB (contacts from iPhone), it contains 1 record, but when I open it with NOTEPAD.EXE, I see more than 1 record. I need to repair all records from it С уважением, I received your db (via private email) and the good news is that the PRAGMA INTEGRITY_CHECK reports that it is a healthy database. The *bad* news is that I suspect the data you are looking for was deleted From the database point of view, at bottom are the tables and the number of rows in each table. I can provide you a full dump of these records and schema, but I suspect that's not what you are seeking. I know of no utility to help you seek out data from deleted records whose data may nonetheless be still present in the database file. Using something like NOTEPAD may actually be your best bet -- see what cyrilic strings you can find. Also, John's suggestion to contact the application's support dept (Apple?) may help. There's some great-looking documention on the sqlite file structure here: http://www.sqlite.org/fileformat.html but it's not for the faint-of-heart (i.e., not so easily digested). I'm reminded of my old databank wristwatch. When it's battery died, I joked that I had to go get a whole new set of friends. :-( 0 ABGroup 0 ABGroupChanges 0 ABGroupMembers 3 ABMultiValue 0 ABMultiValueEntry 8 ABMultiValueEntryKey 7 ABMultiValueLabel 3 ABPerson 0 ABPersonChanges 2 ABPersonMultiValueDeletes 3 ABPersonSearchKey 0 ABPhoneLastFour 0 ABRecent 3 ABStore 26 FirstSortSectionCount 29 LastSortSectionCount 16 _SqliteDatabaseProperties 2 sqlite_sequence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Like do not use index as previous version
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ge...@iol.it Sent: Wednesday, October 21, 2009 2:03 PM To: sqlite-users@sqlite.org Subject: [sqlite] Like do not use index as previous version Hi all, it seems that in last versions on sqlite3 LIKE clause stopped to use indexes; I created a new empty database with SQLIte 3.6.13 and I run these statements : CREATE TABLE TEST (TEXT_1 text PRIMARY KEY, TEXT_2 text, TEXT_3 text COLLATE NOCASE); CREATE INDEX TEST_IDX_2 ON TEST (TEXT_2); CREATE INDEX TEST_IDX_3 ON TEST (TEXT_3); Running explain query plan on select * from test where text_3 like 'x';, I have the following result: 0|0|TABLE test WITH INDEX TEST_IDX_3 And it's what I expected. If I execute the some statements in SQLite 3.6.16 and 3.6.19, I have this result: 0|0|TABLE test So It's not using the index as in 3.6.13 version. The some if I try to use the operator GLOB with field text_2: in SQLite 3.6.13 it uses the TEST_IDX_2 index but it's not used in SQLite 3.6.19. Any suggestions? == Regarding suggestions: Since like and glob are intended for use with wildcards and you're not using wildcards, why not use where text_3 == 'x'; instead? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use of .TIMER within sqlite3 commandline utility: was RE: sqlite-users Digest, Vol 21, Issue 122
I believe one must enable the .TIMER option when compiling sqlite3. I think the pre-compiled versions have this disabled by default (at least for the Windows binaries). Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite reading all column data on selects.
Hello, Adam, Regarding: ... are there plans to stop sqlite3 from reading in all column data on specific select queries? i.e I would like sqlite to ONLY read column data for columns which are specified in the select and where clauses. I'm no sqlite expert, and moreover I'm not sure I understand your question properly. Are you wanting to prevent sqlite from transferring data columns from disk when those columns are not needed? If so, I don't think that's generally feasible. You might want to look at sqlite's (and other database system's) architechture. Sqlite will instruct the operating system to read *pages* that it needs to complete your requests. That being said, you *can* sometimes improve performance by locating less-used columns later in your table definition (i.e., to the right of the more-used columns). Even better, you can place large blob-ish fields into separate tables, along with an indexing identifier, in the hope that these pages will be read only when required. What problem are you attempting to solve? Regards, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index usage
Matthew, Regarding: There's no way to optimize your query to be fast in both situations. I do *not* know if this would be of any help, but the newest 3.1.18 sqlite release which includes the SQLITE_ENABLE_STAT2 feature may possibly be of interest: (and excuse me if you've mentioned this already) http://sqlite.org/compile.html#enable_stat2 SQLITE_ENABLE_STAT2 This option adds additional logic to the ANALYZE command and to the query planner that can help SQLite to chose a better query plan under certain situations. The ANALYZE command is enhanced to collect a 10-sample histogram of the data in each index and store that histogram in the sqlite_stat2 table. The query planner will then use the histogram data to help it estimate how many rows will be selected by a range constraint in a WHERE clause. Regards, Donald Griggs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replace extra carriage returns?
Hi Matt, Regarding: Is there a more comprehensive function list other than http://www.sqlite.org/lang_corefunc.html; Is there an sqlite-supported function that's not listed there, or are you saying you want more functions? If the latter, sqlite struggles to keep the lite on, but you can define your own functions as desired. Regarding: I just want to remove multiple carriage returns from a text typed field. I think you'll want to use SELECT replace(field1, x'0d0a0d0a', x'0d0a'); Note that I'm assuming you already *have* the multiple cr/lf's in your text field. If you're importing data with the commandline utility, it allows very flexible definition of *field* separators, but I don't think you can redefine the *record* separator. So to eliminate empty lines on imported data, you could still do something like: -- Create a table with only one long field. -- Use something very unlikely as field separator CREATE TABLE raw(line); .separator '#$%' .import 'myRawFile.txt' raw -- delete any truly empty lines and output my clean data for later re-import. DELETE FROM RAW WHERE STRLEN(line) 1; .output 'myCleanFile.txt' SELECT * FROM raw; .output stdout OR -- you could use a tiny program in awk, sed, perl, etc. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create a view and add a column
Regarding: ...I need to generate a view and add a ID column to that view. Is this possible? I may well not be understanding your question, Tim. If so, you may want to provide more information, such as what software you're using to speak to the database, do you work with SQL, etc. If you *are* working with sql, a VIEW is pretty much shorthand for a SELECT query. You create a view by simply prepending CREATE VIEW myViewName AS in front of your desired SELECT query. http://www.sqlite.org/lang_createview.html E.g. CREATE VIEW myViewName AS SELECT (Name, Address) FROM PeopleTable; To add a column to a view, the column must be in one of the tables in the SELECT, or otherwise calculated by the select. You drop the existing VIEW and create the new one. E.g. DROP VIEW myViewName; CREATE VIEW myViewName AS SELECT (Name, Address, ID) from PeopleTable; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How long should Insert take?
Hi Rod, Regarding: *Insert rate*: 7/sec. You'll want to wrap your INSERT loop in a transaction, i.e., BEGIN / COMMIT. Without a transaction, each INSERT has to wait for multiple disk revolutions. http://www.sqlite.org/lang_transaction.html If you have a huge number of inserts, you may want to get fancy and start a new transaction every several thousand. I think you'll find a marked difference. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Attached database
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Filipe Madureira Is there a way to get a list of attached databases? Either by SQL or by function call? == Greetings, Filipe, PRAGMA database_list; http://sqlite.org/pragma.html#schema Or, if you're using the commandline utility: .databases You may want to read the entire section on PRAGMAs as there are many useful ones. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reverse string comparison for searches
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Shaun Seckman (Firaxis) Sent: Friday, August 21, 2009 2:33 PM To: General Discussion of SQLite Database Subject: [sqlite] Reverse string comparison for searches Hello everyone, Currently, in my database I'm storing thousands of strings that are formatted in such a way where they share similar prefixes (i.e. TXT_KEY_FOO, TXT_KEY_BAR). Sadly, this format cannot be changed so I was wondering if it would be possible and perhaps faster to have SQLite perform reverse string comparisons for looking up specific strings. Has anyone done this? Is it practical or worth doing? These lookups are performed very frequently so any amount of time that can be shaved off will be noticed. -Shaun = Hi Shaun, If you only need to check for exact matches, would adding a column for an integer hash of the strings be useful? Of course you'd want to index it. You'd want to filter out potential hash collisions with something like: WHERE hash_search = hash_db AND string_search = string_db ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A problem with versions of SQLite
Regarding: 1. Provide a pre-compiled 2,1 (for Windows), or ... Hi Rod, IN A SEPARATE PRIVATE EMAIL TO YOU I've attached a windows executable command line utility for version 2.13. Conversion is easy. Open the database using the version 2 executable and use commands like .output ascii.sql followed by .dump and .quit Then fire up a current version 3 executable and .read ascii.sql Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite: Porting to another Operating system.
Hi, CityDev, Regarding: All you have to do is copy That's handy - I didn't realise that. However I suggest it's good practice to dump and reload in these kinds of situations. I don't yet know how SQLite works but I suspect a reload will get the physical data into a better shape and clear out deleted items etc. Do you know where's there's documentation on this? -- Dr. Hipp is the originator of sqlite, so it's hard to find advice more authoritative than his. For removing deleted items (which would be reused as needed anyway), you may want to look at the VACUUM command -- it also rebuilds the db structures. If you still need an external ascii dump, there is a command-line-interface utility documented at: http://www.sqlite.org/sqlite.html The .dump command gives you sql which can rebuild a database (via the .read command). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite: Porting to another Operating system.
Re: I can't see how you get to that page. I found the link from page: http://www.sqlite.org/features.html And perhaps it's linked from others as well. Re: I would expect to reorganise the physical database on a regular basis... Once again, I'd look at the VACUUM command, though you may find you don't really need to run it very often. http://www.sqlite.org/lang_vacuum.html You may also be interested in the ANALYZE command, which can provide hints to sqlite's query optimizer. http://www.sqlite.org/lang_analyze.html Once you're up and running with your database app, running analyze just once may be enough. You might want to do some experiments to see if running either of these frequently has a measurable benefit to your application. Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Db design question (so. like a tree)
Regarding: I could start the id initially with 10 to allocate That WOULD allow for a bunch of bull.;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite performance on multi process env
Subject: Re: [sqlite] SQlite performance on multi process env Hello, Zhrahman, Regarding: ... kindly suggest how to properly have the database shared in memory among n number of processes. So they can execute select operatins(read only no update on teh database) effeciently. If the large number of processes are unavoidable, you might want to consider: 1) Writing a server layer that would be the only process to open the DB file and service the requests for the other processes. 2) Modifying such a layer already written by others. 3) Using a database designed for lots of simultaneous clients, such as Postgres or MySql. (I believe the succinct advice has been something like, Think of sqlite as a replacement for fwrite, not as a replacement for Oracle.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: database disk image is malformed
Hi Joanne, It's possible you'll have to resort to your most recent backup. But before doing that, I wasn't sure from your reply that you saw the other points I listed. -- Hold tight to my backups of my data. -- Run PRAGMA integrity_check; as soon as possible. -- See if the problem can be reproduced using the command-line utility. -- Is there an index defined on peerid? Does Explain query plan show that it is used by the first query. If so, perhaps the peerid index contains some corruption? -- Does adding peerid to your second select run without error? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check the healthy of database and the indexes ofthe tables
Hello Joanne, Regarding: Is there any command to check if the index or database in good condition. That's why I listed the pragma below in my email of last night and repeat email earlier. You're in luck if only the index is corrupted of course. SAVE a copy of your current database (very important). Try dropping the index and rebuilding it. Even if ok at that point, you might want to then run a VACUUM. == Joanne, I couldn't say, but if I were you I'd probably -- Hold tight to my backups of my data. -- Run PRAGMA integrity_check; as soon as possible. -- See if the problem can be reproduced using the command-line utility. -- Is there an index defined on peerid? Does Explain query plan show that it is used by the first query. If so, perhaps the peerid index contains some corruption? -- Does adding peerid to your second select run without error? Regards, Donald -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joanne Pham Sent: Tuesday, May 05, 2009 4:02 PM To: General Discussion of SQLite Database Subject: [sqlite] How to check the healthy of database and the indexes ofthe tables Hi All, I had the database and one of the index is not good condition. Every time I use the index by select ... group by .. the result only return few rows and the message print out that database disk image is malformed. Is there any command to check if the index or database in good condition. Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check the healthy of database and the indexesofthe tables
Joanne, I don't want to imply that you *have* to take my suggested course of action (and probably MOST of the folks on this list know better sqlite than I), but, nonetheless, I'm still wondering about the results of my suggestions. Maybe if you could either give your results for each, or a short reason why you don't think it's a good thing to try -- or even just say you *choose* not to try it --- by providing the list with this information it might greatly shorten your resolution time and the number of emails. As it stands, I don't even know if you've read them all. Specifically, 1-- See if the problem can be reproduced using the command-line utility. 2-- Does adding peerid to your second select run without error? 3-- Do the suggestions for removing the index and rebuilding it (AFTER saving your current database) sound reasonable? Might a VACUUM be good as well? A new one: 4-- If above does not resolve the difficulty, I would try a .dump command on each of my tables. Hope this is some help, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to exit from sqlite shell?
I find it easy to confuse myself if I have either an unclosed quote, an unclosed /* comment, or an unterminated command (semicolon).Below is an example of two simultaneously. SQLite version 3.6.10 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select 'runaway quoted string ... .quit ... ' ... .quit ... ; SQL error: no such column: runaway quoted string .quit .quit sqlite .quit [it exits] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: database disk image is malformed
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joanne Pham Sent: Monday, May 04, 2009 2:51 PM To: General Discussion of SQLite Database Subject: [sqlite] SQL error: database disk image is malformed Hi All, I ran the following sql statement: select blobid, fbid from sig group by peerid; return about 10 rows 22 ... 33 return about 10 rows and I got the error message: SQL error: database disk image is malformed but when I ran the following sql statement: select blobid, fbid from sig; I didn't see any error message. So why the first sql statement has problem but not the second. Thanks JP == == Joanne, I couldn't say, but if I were you I'd probably -- Hold tight to my backups of my data. -- Run PRAGMA integrity_check; as soon as possible. -- See if the problem can be reproduced using the command-line utility. -- Is there an index defined on peerid? Does Explain query plan show that it is used by the first query. If so, perhaps the peerid index contains some corruption? -- Does adding peerid to your second select run without error? Regards, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a table from a text file via c/c++ interface?
Hi, Feng, I don't think that load data from is part of the sql standard -- I may be wrong. At any rate, the source code to the command line utility is freely available. When you wrote that Using ' insert into values' ... is very time comsuming did you mean time consuming to write the program, or time-consuming to run? If it is taking a very long time to RUN compare to the sqlite3 .import command, then be sure you're surrounding the import (or at least every 1000 lines or so) in a TRANSACTION. Expect a large increase in speed. http://www.sqlite.org/lang_transaction.html Perhaps this helps, Donald Griggs -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of knightfeng Sent: Sunday, May 03, 2009 10:41 PM To: sqlite-users Subject: [sqlite] How to create a table from a text file via c/c++ interface? Hi, '.import' can be used to create a table from text file in the command line version , but how can I do that using C/C++ interface? Using ' insert into values' by reading a text file line by line is very time comsuming and it seems that there is no such SQL command LOAD DATA FROM supported by MySql in sqlite. Many thanks. Zhixing Feng 2009-05-04 knightfeng ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference between all the joins
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andrey Fedorov Sent: Tuesday, April 21, 2009 1:27 PM To: sqlite-users@sqlite.org Subject: [sqlite] Difference between all the joins Hi all, According to the join-op syntax [1], SQLite has 13 distinct join statements: , JOIN LEFT JOIN OUTER JOIN LEFT OUTER JOIN INNER JOIN CROSS JOIN NATURAL JOIN NATURAL LEFT JOIN NATURAL OUTER JOIN NATURAL LEFT OUTER JOIN NATURAL INNER JOIN NATURAL CROSS JOIN Are they all unique? Which are equivalent? - Andrey 1. http://sqlite.org/syntaxdiagrams.html#join-op ___ ___ ___ Hello, Andrey, No, many are synonyms included (I think) as part of the SQL standards or to ease conversion from various other SQL dialects. E.g. an unadorned JOIN is identical to an INNER JOIN. Inner joins have no left or right so a LEFT JOIN can be used as a synonym for a LEFT OUTER JOIN. Furthermore, LEFT is a default for OUTER JOINS, so OUTER JOIN == LEFT JOIN == LEFT OUTER JOIN. This article looks interesting: http://en.wikipedia.org/wiki/Join_(SQL) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Appending Text to a Column
Text concatenation is done with || Two vertical bars (virgules). update tblEntry set Tags = Tags || ' new text' where RowID = 13 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange sqlite_busy deadlock behavior
Dave, Regarding: I understand what a deadlock is, and I know it's not technically a deadlock. This is why I stated the title as deadlock behavior. My apologies, Dave. I thought there might actually have been a difference in definition that was adding to your problem. After your reply, though, I see that I had *not* actually seen your original post as I thought I had. Worse, I don't have the knowledge to help you much with your actual problem (interpret stack trace, etc.) other than to say that others have had success with multi-threaded apps though I understand they can be vexing. Am I right that you've determined for certain that you finalized and committed on one thread yet still receive the BUSY for some time when polling from the other thread? Hoping you and those more knowledgeable on this list than I will work out a fix shortly, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug
Hi, Arthur, This mailing list, like many, will automatically strip attachments. Posting onto your own webspace is a great way, as you have discovered, to reference files. I'm writing just to say that I *did* reproduce a problem. I'm using Windows XP SP3. I narrowed the problem to the sql below. The fault occurs on the insert command. Using the windows binary for 3.6.11, I have no problem, but 3.6.12 results in a fault. Changing the DEFAULT (datetime( 'now', 'localtime' ) ) to a simple constant such as 'puppies' avoids the fault. However, doing a simple: SELECT datetime( 'now', 'localtime' ); does *not* cause a fault. Regards, Donald === .echo on -- registrierten Nicks mit zugehörigkeit zu Accounts CREATE TABLE IF NOT EXISTS nickList ( nickID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ownerID INTEGER NOT NULL, nickName VARCHAR NOT NULL UNIQUE, registeredAt VARCHAR NOT NULL DEFAULT (datetime( 'now', 'localtime' ) ) ); INSERT INTO nickList ( nickID, ownerID, nickName ) VALUES ( -1, -1, 'Unknown2'); select * from nickList; .quit == ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange sqlite_busy deadlock behavior
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Brown Sent: Wednesday, April 08, 2009 1:16 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Strange sqlite_busy deadlock behavior I tried the BEGIN EXCLUSIVE method, but now the problem is that thread-A is in the middle of a query doing sqlite3_step() to get results, and thread-B tries a begin exclusive and gets back SQLITE_BUSY in the deadlock situation :) I guess I am forced to use your 2nd method?? Hi Dave, A *deadlock* would mean that neither process will ever proceed. http://en.wikipedia.org/wiki/Deadlock In your situation, Thread-B would simply wait until thread-A finishes --- either via some sort of inter-thread communication, or via polling on the part of thread-B. Thread-B experiences a delay, but no deadlock occurs. Presumably, thread-A is designed to step lively and not dally unnecessarily. Of course, the 2nd method is great if you have no concerns about isolation. (PRAGMA read_uncommitted=ON) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] export table to csv
Hi Richard, The sqlite3 utility allows a *single* dot-command on the command line itself. You can have an unlimited number of commands in a separate text file, though. http://www.sqlite.org/sqlite.html So, using Windows syntax, you can have something like: === Set MyTempFile=%temp%\Mytemp.tmp echo .separator ,%myTempFile% Echo .output thisisit.csv %myTempFile% Echo select * from iotemplate;%myTempFile% Echo .output stdout %myTempFile% Sqlite3.exe ioimport.db3 .read %myTempFile% Del %mytempfile% nul If you're using linux/unix, you can redirect input to the your shell script and avoid the temporary file. Hope this helps, Donald -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Nero Sent: Monday, March 30, 2009 11:12 AM To: sqlite-users@sqlite.org Subject: [sqlite] export table to csv All, I can successfully import a databese via command line with: sqlite3.exe -separator , ioimport.db3 .import Temp.csv iofromexcel Now i am trying to export a table in the db with: sqlite3.exe ioimport.db3 .mode csv .separator , .output thisisit.csv select * from iotemplate; .output stdout This does not work and I have tried all solutions possible. Can someone lead me in the right direction on how to do this via command line Thanks in advance! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating unique data takes many hours, help
Hi Michael, Two thoughts -- and I hope others will chime in if I'm off-base here: 1) Build just one index after the import: From page: http://www.sqlite.org/lang_createtable.html The UNIQUE constraint causes an unique index to be created on the specified columns. I think that if you then create your own index, it will be redundant, and, with millions of records, time-consuming. However, you may well want to KEEP your own index (and add the UNIQUE constrait to it), and *remove* the UNIQUE constraint on the table column definition. I hear that creating the index after the data has been imported is faster (and less fragmented) than creating an implicit one on-the-fly during import. 2) Hashing If Vinnie [thev...@yahoo.com] was correct in guessing your goal (to determine if a given string has been examined before), and if performance remains a problem, I wondered if the design might benefit from using hashing to pre-qualify a string and/or substitute for a direct match. A short hash (perhaps a 64-bit integer?) could be stored in a separate table or even a separate database from the strings themselves. If the hash of a new string does not match any hash in the database, then you know there is no need to search for the string itself. If the entire hash index can be kept in RAM, you might get a big benefit. You could decide to take this a step further by using a longer hash as a proxy for the string itself. The hash would need to be long enough to make collisions extremely unlikely, yet not so long as to negate the value of using a proxy for the original strings. In practice, you'd probably want to compute a single long hash for each string, then use the first X bytes as the pre-qualifying hash, and the remainder as the confirming hash. If your average string length is short, then using *two* hashes may not be much of a gain. (Of course, since this is not a cryptographic application, you don't need to worry about whether your chosen hash algorithm is secure or not.) Regards, Donald -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Vinnie Sent: Sunday, March 29, 2009 6:14 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] creating unique data takes many hours, help Michael: While my answer isn't strictly limited to sqlite, the performance nut in me can't help myself. You have a a table with only one column, a string. I could be wrong here but it seems like you just want to keep a list of values that you have already tried. After you insert a bunch of strings into the table you want to be able to quickly look up a string to see if it exists, so that you can tell if you already probed that sequence (taking a guess here). If my guess is right, and the only thing you are doing is looking up sorted single-column elements, you probably can get away with your own quick disk-based binary tree implementation and avoid sqlite for this particular circumstance altogether. The result would be several orders of magnitude faster, even after you have followed the suggestions others have given. Hi, I am new with sqlite, and I create a program that reads several mllion records and puts them into a sqlite db using. The table has one column ONLY indexed and unique, but it takes many hours. Is it necessary to pre-allocate the space, or is the anything that I can do to reduce the time it takes. this is how I create the db, table and index. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacement for COPY
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BOB_GOREN Sent: Tuesday, March 24, 2009 10:49 AM To: sqlite-users@sqlite.org Subject: [sqlite] Replacement for COPY Sqlite 2.8.13 supported statements like: COPY ar FROM xxx.odb USING DELIMITERS | What is the equivalent in sqlite 3.6.11? COPY seems to no longer be supported - the same statement that works on 2.8 now generates a syntax error and COPY and DELIMITERS are not in the list of keywords for 3.6. == Hi Bob, I found this page: http://www.hwaci.com/sw/sqlite/lang_copy.html which directs developers to the .import command within the sqlite3 command-line utility. If you must have this capability in your code (versus the standalone utility) then you might want to look at the source for .import Alternatively, you might want to seek out some good routines in your language of choice for handling delimited text. The easy trick to quick imports, as you're probably already aware, will be to combine many or all of your sql INSERTS within a transaction. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation on SQLite indirectBLOB?
Hi Mark, There's a good reason for your difficulty -- Sqlite uses dynamic typing: http://www.sqlite.org/datatype3.html As far as I know, the term IndirectBlob is not defined within sqlite and is likely a term from the BitPim developers. Sqlite allows one to use most anything as a type -- BlobThatAteNewYork would work quite nicely. From sqlite's point-of-view, if a BLOB contains structure, it's structure known only to the calling application. Hope this helps, Donald -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of MBR Sent: Wednesday, March 25, 2009 6:09 PM To: sqlite-users@sqlite.org Subject: [sqlite] Documentation on SQLite indirectBLOB? The Open Source project BitPim uses SQLite. I'm trying to examine the database BitPim creates. At http://www.sqlite.org/download.html I found sqlite-3_6_11.zip, which is a pre-compiled Windows command-line SQL interface. I ran: sqlite3 bitpim.db then typed: .dump and saved the output to see what's in the database. The result is a set of SQL queries that can be used to recreate the database. In the output I found numerous references to columns of type indirectBLOB. When I look at the data that gets inserted into those columns, it's pretty clear that the value in those columns is of the form: /tableName/,/recordId/,/recordId/,... But I've been unable to find documentation on the keyword 'indirect' or 'indirectBLOB' anywhere on the web, including http://www.sqlite.org/docs.html. Can anyone here tell me where to find that documentation? Mark Rosenthal m...@arlsoft.com mailto:m...@arlsoft.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems with shared cache?
-Original Message- On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald donald.gri...@allscripts.com wrote: However, when I ask the user to send me their deck, I find that: sqlite pragma integrity_check; integrity_check --- ok sqlite select id, count(id) from cards group by id having count(id) 1; sqlite Any ideas? Obviously, that user is not playing with a full deck. ;-) = From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Damien Elmes Sent: Tuesday, March 24, 2009 2:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] problems with shared cache? Sorry, my application's files are called decks, and I unwittingly used the wrong terminology. Any ideas about the problem? Damien, I, if anyone, should apologize -- was just an attempt at humor using an English language idiom metaphor for card games. I'm afraid I don't know enough to help with your shared cache problem (posted 19 March), sorry. It's fortunate, I suppose, that your particular application didn't really need it. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Export the results from a select to excel
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of d...@dommel.be Sent: Monday, March 23, 2009 11:28 AM To: General Discussion of SQLite Database Subject: [sqlite] Export the results from a select to excel Hello, what is the fastest way to do this with the C-api? tx, Danny Belgium === Hello, Danny from Belgium, Do you mean fastest to program or fastest to run? Most any reasonably-sized spreadsheet shouldn't take all that long to be exported, regardless of method. You can simply output your SELECT with tab or comma separators, then Excel can read up the file. If you need the spreadsheet to be fancy, with nice formatting and headers pre-set, then I'll leave it to someone more knowledgeable to respond. The fastest method to *develop* would likely be to use the SQLITE3 utility program with a tiny script something like: Sqlite3 MyDatabase.db MyLittleScript.sql Where MyLittleScript.sql is: .separator '\t' .output MyExport.tab SELECT [my select statement here]; .output stdout .quit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems with shared cache?
However, when I ask the user to send me their deck, I find that: sqlite pragma integrity_check; integrity_check --- ok sqlite select id, count(id) from cards group by id having count(id) 1; sqlite Any ideas? Obviously, that user is not playing with a full deck. ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed of DROP INDEX
Re: [sqlite] Speed of DROP INDEX This may not be useful to your situation, but my (not terribly informed) *guess* is that the reason it takes so long is that the index pages are spread throughout your 8-millon row database. If by chance it's feasible to either: -- Not create the index until the table is populated, or -- Run vacuum at some non busy time between the table creation and the need for index deletion Then perhaps the delete would be quicker. A side benefit might be faster lookups. Again, even if I'm corrent, this may not be feasible for your application. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how many tables can I have in one database
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mrobi...@cs.fiu.edu Sent: Wednesday, March 18, 2009 7:26 PM To: sqlite-users@sqlite.org Subject: [sqlite] how many tables can I have in one database Dear colleagues, I understand that Sqlite can handle databases with 2 teragytes of data each. I would like to know how many tables can a dabase have. Thank you Michael = Hi, Michael, I *think* the answer will be, As many as you need. On the other hand, if you're contemplating a design involving hundreds of tables, then many would say that the design would likely benefit from some serious consideration as to normalization. Approximately how many tables do you envision using? There *is* a limit of 64 tables in one JOIN statement, by the way. http://www.sqlite.org/limits.html Regards, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] httpd server ???
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ken Sent: Tuesday, March 17, 2009 7:31 PM To: sqlite Subject: [sqlite] httpd server ??? === Hi, Ken, Mr. Hipp's Tiny Web Server is found at: http://www.hwaci.com/sw/tws/ Quoting from the page: Embeddable The core HTTP engine used by TWS is modular and implemented in just two source files. It is very easy to embed this HTTP engine inside another application to give that application the ability to report its status or to be configured dynamically using a web browser. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE : documentation and search topics
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of REPKA_Maxime_NeufBox Sent: Monday, March 16, 2009 3:11 PM To: General Discussion of SQLite Database Subject: [sqlite] SQLITE : documentation and search topics Hello, A main remark to SQLite developers : - Does it exist a Help file .chm for SQLite ? I am quite new user with Database and SQLite and find very hard to get information and documentation. My opinion is that it is totaly NOT intuitive on the web to find information. For comparison I am quite new on Autohotkey software (autohokey.com) and I have no problem to find information and answer on problem (most of the time I find the answer without posting a question). - Did I miss something on SQLite web ? or does other users think like - me that some improvment should be done ? MaxMax14 == Bonjour, Maxime, I'm *not* an sqlite developer, but maybe I can provide a portion of an answer. -- CHM -- There's no .chm file for a couple of reasons. Mainly, .chm is a Microsoft application help format for Windows, and Windows is just one of the many operating systems on which sqlite is run. Secondly, sqlite is not really an application (not like, say, Microsoft Access) -- it is instead an embeddable SQL engine intended for programmers. That being said, there *is* also provided a command line tool called sqlite3 intended mainly for testing, but which is useful in it's own right. If you run it, it will let you know that a consise help screen is available using the .help command. The full source code (very well commented) is available for both the sqlite engine and the command line utility. There is not to say that other folks have not written end-user applications using Sqlite, of course, and there are a number of applications that are GUI database administration tools that may or may not have good online help included -- you'd want to approach those developers in that regard. -- Your current problem -- I take it you likely have something in particular you would like to know about sqlite? Since you haven't found it on the website, you may want to post your particular problem here. -- Documentation in general -- On the one hand, each person would organize a website differently, and all will never be pleased. But on the other hand, sometimes someone unacquainted with the product can provide some of the most useful feedback, so you may want to post some specific suggestions you may have. I find that it's very helpful to visit the wiki -- and of course you may submit wiki improvements quite directly. Hope this may help in some way, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE : Constraint question
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wolfgang Enzinger Sent: Sunday, March 15, 2009 1:39 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLITE : Constraint question BTW, is there a document that explains in more detail what operations the CHECK constraint is capable of? Hello, Wolfgang, Were you looking for something beyond the documentation under CREATE TABLE? Column constraints: http://www.sqlite.org/syntaxdiagrams.html#column-constraint Table constraints: http://www.sqlite.org/syntaxdiagrams.html#table-constraint Conflict clause: http://www.sqlite.org/syntaxdiagrams.html#conflict-clause Foreign key clause (enforceable with preprocessing to create triggers): http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause Regards, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow performance with Sum function
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Trainor, Chris Sent: Wednesday, March 11, 2009 5:31 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow performance with Sum function Do not be tempted by the incremental vacuum feature. Incremental vacuum will reduce the database size as content is deleted, but it will not reduce fragmentation. In fact, incremental vacuum will likely increase fragmentation. Incremental vacuum is just a variation on auto_vacuum. It is designed for flash memory with zero seek latency. D. Richard Hipp d...@hwaci.com Thanks for the reply, but I am confused again. Is incremental vacuum different from the vacuum command? It seems like vacuum would defragment the database according to the description here: http://www.sqlite.org/lang_vacuum.html = No, the auto_vacuum command differs from the regular vacuum command. The auto_vacuum command does not reduce (and may increase fragmentation). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transactions and attached databases
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tito Ciuro Sent: Wednesday, February 25, 2009 6:36 PM To: sqlite-users@sqlite.org Subject: [sqlite] Transactions and attached databases Hello, If I attach one or more databases and wrap a series of operations which affect some/all of them, would ROLLBACK or COMMIT treat these operations atomically? For example: Open database 'foo'; Attach database 'bar' as a1; BEGIN TRANSACTION; INSERT INTO main.some_table ... ; DELETE FROM main.some_table WHERE ... ; INSERT INTO a1.another_table ... ; COMMIT/ROLLBACK TRANSACTION; Would the insertions and deletions be either committed or rolled back atomically? Is this supported? Thanks, -- Tito === === Greetings, Tito, Did you see page: http://www.sqlite.org/lang_attach.html Transactions involving multiple attached databases are atomic, assuming that the main database is not :memory:. It then goes on to say: If the main database is :memory: then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite caching
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of manohar s Sent: Monday, February 23, 2009 4:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite caching Thanks for your quick replies, although restarting my machine, disc cache is cleared, I am trying to find an utility which could do the job without requiring a restart. Regards, Manohar.S Greetings, Manohar, You can do something like close your sqlite application, copy a large unrelated file to the nul device, then restart your app -- that should clear the cache of the sqlite data (if the o.s. uses a least recently used cache instead of least frequently used cache -- someone more knowledgeable may be able to say for a particular O.S.) However, most any well-performing database is going to depend on a good RAM cache -- either from the operating system or within it's own code -- or both. And it's already been mentioned that what you're seeing is not caching of your RESULTS but instead caching of portions of the database itself. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] number of columns in a table
hi, my question is how to get the number of columns in an existing table. example if i can retrieve the number of rows with SELECT count(*) FROM table , is there also some simple query to do the same thing for counting the number of columns == Hello, r You can use sql to query the system table named: sqlite_master Also see http://www.sqlite.org/pragma.html#schema Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hundreds of thousands of INSERTs
p...@ferret:~/log_analyzer date ; ./log_parser_ms_sqlite.pl 2 vmta2-2009-02-16_1400-success.log.bz2.done.ec.ed ; date Tue Feb 17 15:07:40 CET 2009 Tue Feb 17 15:13:22 CET 2009 it's a 75% shave of application time, amazing!!! -- If you really have to have even more performance, you could cache inserts in RAM and wrap multiple INSERTS with a single transaction when you have a certain number cached (or if a max delay is reached). I noted that you were willing to consider PRAGMA synchronous = OFF so I assume that you could tolerate a little data loss in case of a crash. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hundreds of thousands of INSERTs
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati Sent: Tuesday, February 17, 2009 4:55 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Hundreds of thousands of INSERTs Griggs, Donald wrote: If you really have to have even more performance, you could cache inserts in RAM and wrap multiple INSERTS with a single transaction when you have a certain number cached (or if a max delay is reached). shouldn't varying the transaction size have the same effect? moving from 4096 to 8192 didn't show any improvement. = Paolo, My apologies -- you wrote in your original post: one INSERT per row. Which I mis-remembered as One INSERT per TRANSACTION. If you're already inserting 4096 rows per transaction -- then, yes, you're right -- I wouldn't be surprized that even larger groupings don't improve performance. If you think you may be disk-bound rather than CPU-bound, I don't know if going to a 15,000 RPM disk would be a possibility -- assuming you're on something slower now -- and that the cost could be justified. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
On linux you can do dd if=database.db of=/dev/null bs=1M and after perform select ... = From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nathan Biggs Subject: Re: [sqlite] Full Table Read We are actually using Windows XP for an OS. (I know, lots of limitations, but that is what we have to use). = I think the windows equivalent would be: copy /b database.db nul In either case, we're assuming your database fits into available ram-cached virtual space. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Full Table Read
Griggs, Donald wrote: On linux you can do dd if=database.db of=/dev/null bs=1M and after perform select ... = From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nathan Biggs Subject: Re: [sqlite] Full Table Read We are actually using Windows XP for an OS. (I know, lots of limitations, but that is what we have to use). = I think the windows equivalent would be: copy /b database.db nul In either case, we're assuming your database fits into available ram-cached virtual space. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users = Biggs wrote: Is that just creating an in-memory database then? I'm not familiar with a copy to a null location. = Nothing quite that fancy. It's just attempting to get most or all of your database into the read-cache of the operating system. (I probably was not accurate earlier when I wrote virtual space since I don't know how the read-cache is implemented.) Your performance, after a successful loading into cache, should be about that of a ram-based database -- without the data integrity and synchronization problems a ramdisk-based database can cause. You didn't mention any order by clause on your select *. If you *do* require a an ordering of the output then an index, of course, could possibly make a large difference. Instead of the copy to a nul device, you could also try running a vacuum command -- since it can defragment a table who's portions are spread throughout the file. All this assumes that the time to perform the copy or vacuum is tolerable in your application. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limits in SQLite
Greetings, Ha Le, When you asked What are the factors which determine the different limits, I suspect I'm not understanding just what you're asking. You referenced http://www.sqlite.org/limits.html And one of your two examples was Maximum Depth Of An Expression Tree The webpage reports: == Maximum Depth Of An Expression Tree ... The depth of expression trees is therefore limited in order to avoid using too much stack space. The SQLITE_MAX_EXPR_DEPTH parameter determines the maximum expression tree depth. If the value is 0, then no limit is enforced. The current implementation has a default value of 1000. = All software running on real machines has limits. Most of the limits in sqlite are so high that if one is approaching them, there's a fairly good chance one's design is not going to work well with sqlite anyway. The tree depth explanation from the webpage describes why the depth is limited, plus it lets you increase or decrease the default maximum as your needs dictate. The default of 1000 is quite high. What further information were you looking for? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fast and simple database merge
Regarding: ...Really cool would be something like: cat db1.dat db2.dat db3.dat final.dat I don't think there are any really cool cat's available. ;-) The format of the database is fairly fancy, though it is documented if you think you'd be interested in writing a utility against it. You can use the command line utility with its .dump command to output the schema and data for the databases to be appended, then run these against the original database to add in the new tables and data. It will be slower, since the data must be dumped as well as inserted, but if you don't have any conflicting names in your schemas, maybe this would save you writing code. (If you can delay adding indicies until after adding the data you may see some speed improvements.) Don't know if this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index keywords
Hi Vance, Could you maybe give one or two examples on exactly what you need that you're not finding on the website? As far as syntax, it's pretty much all linked from: http://www.sqlite.org/lang.html E.g. for automatically incrementing primary key, see: http://www.sqlite.org/lang_createtable.html#rowid (if you must have an auto-incrementing key other than an integer primary key, you will need to implement it yourself via a trigger) As far as typing, sqlite differs from many other databases: http://www.sqlite.org/datatype3.html As far as a manual on SQL itself, yes, you would need to look elsewhere. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction behavior with large numbers of inserts
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns Sent: Wednesday, January 28, 2009 1:12 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Transaction behavior with large numbers of inserts -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ian Frosst wrote: My question is; am I assessing the situation correctly, and if so, is there anything I can do to avoid this rather nasty situation? I would rather not take the periodic-commit approach, and increasing the page cache size would potentially cause the use of more memory than I can spare. Disk usage, however, is not a problem; I can use as much as is necessary. You can open a second connection and make another database that you do all your writing to, using as small a page cache as you want. When done add the data in your first connection. Basically do this (main is the name of the first database): attach second.db as second insert into main.table (select * from second.table) detach second Roger === Roger, I am probably missing something obvious here, but I was confused. The INSERT into main.table (with its associated SELECT) would still be an implicit transaction and of course would require writing to the database of the million records. During the rather long period of time for the INSERT to complete, wouldn't Ian's original problem still exist (and perhaps be worse because of the added delays and possible disk arm contention)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help : sqlite database restore
Venkat, If you have a .db file it almost certainly *is* an sqlite database. A great attribute of sqlite databases is that they are contained in a single file. If you're looking for a GUI frontend, they are legion, including good free ones such as sqlite3explorer and a plug-in sqliteManager for firefox webbrowser. See http://www.sqlite.org/cvstrac/wiki?p=ManagementTools Donald -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of venkat karri Sent: Tuesday, January 27, 2009 3:25 PM To: punk...@eidesis.org; General Discussion of SQLite Database Subject: Re: [sqlite] help : sqlite database restore This morning I had a meeting with the team and found that we also have a db file, could you please send me the complete syntax to import or restore this .db file in sqlite. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help : sqlite database restore
On 1/27/09, Griggs, Donald donald.gri...@allscripts.com wrote: Venkat, If you have a .db file it almost certainly *is* an sqlite database. A great attribute of sqlite databases is that they are contained in a single file. If you're looking for a GUI frontend, they are legion, including good free ones such as sqlite3explorer and a plug-in sqliteManager for firefox webbrowser. See http://www.sqlite.org/cvstrac/wiki?p=ManagementTools Donald = Hi Donald, Thanks for the link thats helpful, How do i access the .db file and read the data... = Venkat, There may be a bit of a language barrier here. Each of the GUI sqlite utilities can be used to access and read the data in an sqlite database. Each has it's own instructions. Often, one chooses to open the database file from within the GUI. One then supplies the desired SQL instructions and asks that they be executed by some means. There are even ODBC wrappers available so that you may even be able to use some tool with which you are already familiar. All of the data, of course, is also available by using the Command Line Utility provided as part of the official release. Have you downloaded the utility and/or a GUI front-end? Have you had trouble with the directions provided by them? If you're comfortable with the command line utility, for instance, have you had trouble after reading page, http://www.sqlite.org/quickstart.html ? If so, you may need to elaborate on access and read the data since I'm not as sure as I was earlier that I understand the difficulty you're having. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help : sqlite database restore
Venkat, Sorry. I really intended to quote page: http://www.sqlite.org/sqlite.html Though http://www.sqlite.org/quickstart.html May be helpful as well. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J. R. Westmoreland Is there a way to get an autoincrement field without being a primary key? Yes, you can create a trigger to do this. (This also allows you great flexibility, since you can, for instance, use an increment other than 1, include a check digit, etc., but it's not built-in as the one for primary key can be.) Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recover deleted records
Hi Alex I can't answer your specific questions, but I presume you've already studied the file format document (most recently mentioned by Roger Binn's recent post): http://www.sqlite.org/fileformat.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW: A list as an SQL paramater
Regarding: This would definitely work but does incur the overhead of sqlite3_prepare each time I want to run the query. Maybe you would be preparing hundreds or thousands of different queries per minute -- but if by chance you're not -- could anyone be expected to notice the time for the prepare's? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Upgrade sqlite 3.3.4 to sqlite 3.6.7
Subject: [sqlite] Upgrade sqlite 3.3.4 to sqlite 3.6.7 Hi, I consider upgrade sqlite 3.3.4 to sqlite 3.6.7. So, I wonder there is any change (or problem) of file format. === Upgrading from version 2 to version 3 (understandably) required a dump and restore, but not upgrading from one version 3 to a newer one. Following is from page: http://sqlite.org/different.html Stable Cross-Platform Database File The SQLite file format is cross-platform. A database file written on one machine can be copied to and used on a different machine with a different architecture. Big-endian or little-endian, 32-bit or 64-bit does not matter. All machines use the same file format. Furthermore, the developers have pledged to keep the file format stable and backwards compatible, so newer versions of SQLite can read and write older database files. Most other SQL database engines require you to dump and restore the database when moving from one platform to another and often when upgrading to a newer version of the software. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Edward J. Yoon Sent: Tuesday, January 13, 2009 2:45 AM To: General Discussion of SQLite Database Subject: [sqlite] Upgrade sqlite 3.3.4 to sqlite 3.6.7 Hi, I consider upgrade sqlite 3.3.4 to sqlite 3.6.7. So, I wonder there is any change (or problem) of file format. -- Best Regards, Edward J. Yoon @ NHN, corp. edwardy...@apache.org http://blog.udanax.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bool Values
A design constraint of SQLITE was to avoid adding a bunch of bool present in other DB's. (Bad joke, couldn't resist) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Emil Obermayr Sent: Friday, January 09, 2009 2:58 PM To: General Discussion of SQLite Database Subject: [sqlite] Bool Values Is there a way to use bool-values to make migration from other DB easier? e.g. select * from address where local = true like defining true as a contant that represents a numeric 1? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] meta command via string via shell?
Regarding: ...but this doesn't work because the .mode tabs isn't separated from the regular SQL. Could someone point out how to accomplish this? If I understand correctly, all you need to do is write the desired commands out to a text file, then either direct stdin to the text file, or use the '.read command. With Windows, FWIW, I have more luck with .read , e.g. Sqlite3 NLSY.db .read MyFileOfCommands.sql ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MAX_VARIABLE_NUMBER and .import for very widefile
Regarding: I am sure there is a better way to deal with 12K rows by 2500 columns, but I can't figure it out I wonder if you might want to use *sed* or *awk* or *perl* to preprocess the data before import. A master table could contain the unique person id, plus the fields that you intend to index and that you are likely to filter upon most often. Other tables could exist for the remaining data, and could be joined on the person id as needed. This might: -- let you avoid a customized version of sqlite -- allow your most-used queries to run faster ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confusing with how to to this in sqlite
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rachmat Febfauza Sent: Sunday, December 28, 2008 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] confusing with how to to this in sqlite thanks simon for the explanation. after holiday, i works on how to optimize my query. actually awal1 table consist 12000 rows and akhir1 too. how to improve performance? i added index on table awal1 and akhir1 with following syntax : create index awal1i1 on awal1(Code,Category,Product,Location,Begin); create index akhir1i1 on akhir1(Code,Category,Product,Location,End); is this create index syntax right? or i must specify each column with individual index?? like create index awal1i1 on awal1(Code); create index awal1i2 on awal1(Product); etc and i want to know to to improve performance of my query? some hint? i have one question again, is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file. thanks again = = Regarding syntax: If you don't get an error, the syntax is acceptible.;-) Sqlite *does* support compound indicies. However: -- You may want to use EXPLAIN QUERY PLAN as a prefix to your SELECT (just running as a test) to ensure than your index is used. -- You can quickly experiment with using a simple index on BEGIN or PRODUCT instead and measure times. -- As you measure times, be aware of possible caching effects -- i.e. the first run may be slower than subsequent runs of the a query on the same tables. -- Make sure you see the link on performance, below. -- Make sure you include many INCLUDES within a single TRANSACTION (if appropriate to your application). This can make a dramatic difference. -- You want to be familiar with the PRAGMA's that can affect performance. http://www.sqlite.org/pragma.html (but note that some of these can be used to trade data safety for performance -- make sure you're making an informed choice) Regarding: is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file. Have you read http://www.sqlite.org/whentouse.html And http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations ? If not, you'll want to. Many folks successfully run sqlite on multi-gigabyte databases, BUT -- in those cases, the simplicity and small footprint of sqlite may be less compelling, -- Are there any features in http://www.sqlite.org/omitted.html that you will grieve in their absence? You might go over the detailed feature lists for postgres, Mysql, etc. with the same question in mind. -- How much concurant access do you anticipate? -- Will you control the queries (so as to optimize them and the indicies) or will the database be subjected frequently to ad hoc queries (which *might* benefit from a sophisticated query optimizer)? Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IN expression performance
Regarding: Is there a way to use a prepared statement and bind a (variable) array of integers? Like in: SELECT FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15 ... ) Becomes: SELECT FROM table WHERE someinteger IN ( ? ) I don't know that you can bind these, but do I remember correctly that these integers are selected by human users? If so, I guess the savings in time would be unmeasurably small, right? Or do your users type the string directly were you aiming to reduce sql injections? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] REGEXP
Regarding: ...No regexp() user function is defined by default... The reason, if I recall correctly, is that the regexp routines that were available were GPL'd and would mess with the licensing of sqlite. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data dump in sqlite
I'm not certain I understand your question, but here goes: Sqlite3.exe is a command-line interface, so you don't really browse at all. However, you can specify the path and name of a database on the command line when you invoke sqlite3, e.g. (In windows): Sqlite3 c:\NiceDirectory_Without_spaces\MyDatabase.db The quotes are usually optional, but not when your path contains spaces ALSO, you may want to see the ATTACH command in the documentation. Once inside sqlite3, the .databases command should produce SOME output, even if you haven't opened an existing database. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Manisha De Silva Sent: Tuesday, December 09, 2008 10:36 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] data dump in sqlite I downloaded sqlite3.exe I typed in .databases and it doesn't show any database. Actually I have sqlite db in C:\Program Files\Jabber Inc\Jabber XCP\var\spool and I want this to dump the data to a text file. I typed in sqlite .databases How can I browse to the specific folder name which contains the jsm.db sqlite db file This message (and any associated files) = ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users