Re: [sqlite] Whish List for 2015
The only thing on my SQLite wish list is for the development team to have a meaningful holiday season and a happy, healthy, and productive new year. The details of the productive part I leave in their capable hands. What has been added each year has far surpassed my expectations, and I have no worries about that trend continuing. Gerry Snyder ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table qualification not supported in some cases (was: quasi-bug related to locking, and attached databases)
Some further comments on VIEWs: A regular (non TEMPORARY) VIEW can reference only tables in the file where it is stored. SQLite allows qualified names, but the practice should be avoided because the VIEW won't work if the file is attached under a different name. A TEMPORARY VIEW can reference tables in any attached file, and the use of qualified names is allowed and encouraged (at least by me). Gerry - On 10/28/2014 12:24 AM, Hick Gunter wrote: Indices, foreign keys, ... all work only within a single DB file, so allowing a qualifier would suggest functionality that is not present and probably quite hard to provide. How would one keep an index residing in one DB file consistent with a table in a different file if only one of the files is attached? In these cases, the qualification is implied. NB: IIRC this also applies to views, but seems not to be prominently documented. Eg. CREATE INDEX db.index_name ON [=db.]table_name ... CREATE TABLE db.table_name (...) FOREIGN KEY (...) REFERENCES [=db.]referenced_table CREATE TRIGGER db.trigger_name ... ON [=db.]table_name ... CREATE VIEW db.view_name AS SELECT ... FROM [=db.]table_name ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unicode case insensitive
In a vaguely similar situation I wrote a custom collation that converted
accented letters to their non-accented cousins. Since the conversion is
on a case-by-case basis I also had to do a pre-screening that would show
any non-ascii characters that I wasn't converting, so that I could add
them to my collation.
This is not quite what you want, since (I think) you want O and Ö and Ó
to be distinct, but the same sort of technique should work for you.
Convert everything to upper (or lower) case brute force. Perhaps tedious
to set up, but straightforward.
Gerry
On 10/24/2014 9:54 AM, dd wrote:
Hi,
ö and Ö same character but case different. I dont want to allow to insert
two entries for same data with different case. It works well with ascii
set. How to handle this? any inputs welcome.
$./sqlite3 '/home//sqlite/test/a.db'
SQLite version 3.8.7 2014-10-17 11:24:17
Enter .help for usage hints.
sqlite .fullschema
CREATE TABLE test(id integer primary key autoincrement, t text collate
nocase, unique(t));
/* No STAT tables available */
sqlite insert into test(t) values('a');
sqlite insert into test(t) values('A');
Error: UNIQUE constraint failed: test.t
sqlite .headers on
sqlite select * from test;
id|t
1|a
sqlite insert into test(t) values('ö');
sqlite insert into test(t) values('Ö');//issue: allowed to insert. Expects
constraint failed err. But, not.
sqlite select * from test;
id|t
1|a
2|ö
3|Ö
sqlite .q
Thanks,
dd
___
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] unicode case insensitive
By brute force I just meant specifying each conversion (such as Ö to ö) individually. In my Tcl code, it is done with a single [string map ...] statement containing all of the conversions. The down side being, as I mentioned earlier, that each time I run it on a new set of data I have to check that a new accented character has not been added. Gerry On 10/24/2014 10:44 AM, dd wrote: Hi, Any sample/open source avail to custom collation. Will it work for like queries. Any performance degradation? Convert everything to upper (or lower) case brute force. Sorry. I am not clear. Can you please elaborate this. Thanks. On Fri, Oct 24, 2014 at 9:16 PM, Gerry Snyder mesmerizer...@gmail.com wrote: In a vaguely similar situation I wrote a custom collation that converted accented letters to their non-accented cousins. Since the conversion is on a case-by-case basis I also had to do a pre-screening that would show any non-ascii characters that I wasn't converting, so that I could add them to my collation. This is not quite what you want, since (I think) you want O and Ö and Ó to be distinct, but the same sort of technique should work for you. Convert everything to upper (or lower) case brute force. Perhaps tedious to set up, but straightforward. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any tips on reducing memory requirements for small MCU?
One possibility might be to use the long-obsolete SQLite2, which was around when PC's had much smaller memories. I know it is heresy to suggest it, and you would have a lot of recoding to do, but it seems that it might be workable. Gerry Snyder --- On 10/20/2014 2:21 PM, Dennis Field wrote: I have SQLite compiled for a Cortex M4 with 256 KB of RAM. Currently, as other things on the system are taking up a grand total of about 190 KB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite max arguments assistance
I feel sure the transaction amounts are strings, not numbers. Here is a
quick example:
create temp table gigo(a real)
insert into gigo values ('$5.00')
select a, typeof(a) from gigo
gives:
$5.00 text
If you can remove the dollar signs in the CSV file you should do better.
Hope this helps,
Gerry
On 9/22/2014 12:12 PM, Jungle Boogie wrote:
Hello All,
select * from august where transaction_amount = (select
max(transaction_amount) from august)
This statement should show be the merchant account with the top most expensive
transaction from my table called august.
Result:
$999.63
(I trimmed out other items that I can't show).
Same results with this: select max(transaction_amount) from august
$999.63
But this is NOT the most expensive amount, but it is for a three digit dollar
amount.
For example, this record is much higher in terms of transaction_amount:
$16695.36
This is a csv file that I've imported and I'm using SQLiteSpy with sqlite
3.8.6 as well as
FreeBSD lyander-fbsd 10.0-RELEASE-p9 FreeBSD 10.0-RELEASE-p9 #0: Mon Sep 15
14:32:29 UTC 2014
r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC i386
also with sqlite3.8.6
How am i misunderstanding max?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling Timezones
Have you read http://sqlite.org/lang_datefunc.html ? On 7/29/2014 6:41 AM, Will Fong wrote: Hi, How are timezones best handled? Since dates are stored in GMT, when I go to display them, I need to add/subtract the timezone. That's not too hard when I can just store the timezone as -5 for EST. When I'm providing a date to query on, I would have to apply the reverse of the timezone, +5, to normalize it to GMT. That kinda sucks :( I come from PostgreSQL, so I normally set at the connection level the timezone and PG handles all the conversions. Does SQLite have a similar feature? Is there a standard way to handle this? Thanks, -will ___ 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] Hexadecimal integer literals
I can think of situations where I would want the result to be truncated to 64 bits. I can think of situations where I would want SQLite to raise an error. I cannot imagine wanting a floating point result. Gerry Snyder - On 7/23/2014 4:07 AM, Richard Hipp wrote: We are looking into adding hexadecimal integer literals to SQLite. In other words, we are looking to enhance SQLite to understand 0x1234 as another way of writing 4660. Hex literals are useful in conjunction with the bit-wise AND and OR operators ( and |) and in applications that make use of bit fields. The question is what to do with hex literals that are larger than 64 bits. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOBs and NULLs
On 4/23/2014 10:21 AM, Drago, William @ MWG - NARDAEAST wrote: If I was sure I wouldn't be merging data I might use timer ticks as my ID, but I'm not sure and I can't take the chance. -Bill Would it be possible to use INTEGER PRIMARY KEY AUTOINCREMENT for the ID, and manually start each test station at an ID value a billion larger than the previous one? Or whatever delta makes sense? Then collision could never happen. And, as a possible bonus, the ID would indicate which station the row came from. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite dump makes wrong CREATE VIEW order
On 4/17/2014 12:43 AM, Tyumentsev Alexander wrote: sqlite in some cases dumps views in wrong order. Interesting situation. My take on it would be that dump is a simple little tool designed to help move a database file from one place to another. In tricky situations, some editing of its output may be needed. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite sorting/searching algorithm
Look at COLLATE in the ORDER BY clause. Gerry On Mar 25, 2014 11:32 AM, michal.pilszak michal.pils...@o2.pl wrote: you cannot tell SQLite to use a particular algorithm to do those things that's exactly what I wanted to know. So, I won't waste my time on looking for impossible on Internet. Thank you for this answear. And thank you for suggestion with ANALYZE command. This maybe be useful. I'll try to get familiar with it and use it when/if I'll have some time. Dnia 25 marca 2014 18:55 Simon Slavin lt;slav...@bigfraud.orggt; napisał(a): On 25 Mar 2014, at 5:48pm, michal.pilszak lt;michal.pils...@o2.plgt; wrote: gt; Is there any parameter I can set to select another algorithm (e.g. another algorithm of ORDER BY) and check its efficiency? You can tell SQLite to search for different rows or order them in a different order. But you cannot tell SQLite to use a particular algorithm to do those things. SQLite decides how to obey your instructions itself. The best thing you can do is to CREATE an INDEX ideally suited to your SELECT command. If your database is large and you are concerned about how long your operations may take you might like to execute the ANALYZE command after you have INSERTed your data in the database and CREATEd your INDEX(es): lt;http://www.sqlite.org/lang_analyze.htmlgt; Hope this helps. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Pozdrawiam, MP. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation suggestion
There is no mention in the write-up of PRAGMA table_info that it works for a VIEW as well as for a TABLE. It takes only a few seconds to verify this, but saving others the trouble of doing so seems like a good idea to me. Thank you, Gerry Snyder ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Free Page Data usage
Instead of delete and then insert, can you somehow just keep track of which rows are to be deleted, and when new rows come in replace if you can and otherwise insert? A little more bookkeeping, but it might save the space you need. Gerry On Feb 7, 2014 10:57 PM, Raheel Gupta raheel...@gmail.com wrote: Hi, Sir, the 32 TB size is not always going to be reached. The Database is going to be used to store blocks of a Block Device like /dev/sda1 The size can reach 3-4 TB easily and would start from atleast 20-100 GB. 32 TB of data though impractical as of today will be possible in 2-3 years. The issue happens when I delete the rows and new rows are inserted at the end of the database the size of the database exceeds that of the actual block device size even though many pages are having free space. Hence I am simply trying to optimize the utilization of the free space available. I would have loved to use the page size of 2KB which would give me a practical size of 4TB. But that would have this hard limit of 4TB. So I have two possible options which I am trying to help me solve this issue : 1) Either make the page size to 2KB and increase the maximum page count to 2^64 which will be more than sufficient. 2) Improve the free space utilization of each page when the page size is 64KB. I hope this makes sense. On Sat, Feb 8, 2014 at 12:54 AM, RSmith rsm...@rsweb.co.za wrote: A database that is geared for 32TB size and you are concerned about rather insignificant space wasted by the page size that is needed to reach the 32TB max size... does not make any sense unless you are simply paranoid about space. Removing the gaps in the table space when deleting a row (or rows) will render a delete query several magnitudes slower. If it IS that big of a concern, then maybe use standard files rather than SQLite to save data in? If the SQL functionality is a must, you can use vacuum as often as is needed to clear unused space - but beware, 1 - Vacuum takes some processing to re-pack a DB, especially a near 32TB one... in the order of minutes on a computer I would guess, and much much more on anything else. 2 - a 32TB DB will need up to 64TB total free disk space to be sure to vacuum correctly - so having issues with it taking up maybe 40TB for 32TB of data is in itself an irrelevant concern. Even large queries, temporary tables etc will all need additional interim space for the sorts of queries that might be requested of a 32TB data-set. The real point being: if you do not have at least 64TB free on whatever that 32TB DB will sit, you are doing it wrong, and if you do have that much free, you can ignore the 25% wasted deletion space problem. If the problem is simply your own pedanticism (at least I can sympathise with that!) then it's simply a case of Welcome to efficient databasing, but if it is a real space deficit, then I'm afraid you will have to re-plan or reconsider either the max allowable DB, or the physical layer's space availability - sorry. On 2014/02/07 20:35, Raheel Gupta wrote: Hi, I use a page size of 64 KB. But my row consists of 2 columns that is : i - Auto Increment Integer, b - 4096 Bytes of BLOB data Now for the sake of calculation, lets say 16 rows fit in a page and my table has 1 rows when I start. Now, lets say I delete some data which is not in sequence i.e. it can be deleted as per data which is not in use. To create such a hypothetical situation for explaining this to you, here is a simple query : DELETE from TABLE where i%4 = 0; As you may see that there is now 25% data deleted in each page. Now even if I do insert another 2500 rows (25% of original size) my database size reaches 125% of the original size when I inserted the 1 rows initially. Hence there is significant space wastage. Anyway i can improve that ? It would be nice if the database size would be close to the original size after deleting 25% and adding some new 25% data. I know you would recommend to use smaller page sizes. Ideally 2KP page size is good but then, the number of pages is restricted to a max of 2^32 which will restrict the total database size to 4TB only. I need the max size to be capable of atleast 32TB. On Fri, Feb 7, 2014 at 11:14 PM, Donald Griggs dfgri...@gmail.com wrote: Can you write more about how this is causing you a problem? Most users don't experience this as a problem On Feb 7, 2014 10:30 AM, Raheel Gupta raheel...@gmail.com wrote: SQLite's tables are B-trees, sorted by the rowid. Your new data will probably get an autoincremented rowid, which will be appended at the end of the table. A page gets reorganized only when about 2/3 is free space. Anyway to make this ratio to lets say 1/3 ? ___ sqlite-users mailing list sqlite-users@sqlite.org
Re: [sqlite] Boolean and DataReader
On 1/29/2014 1:08 PM, Johnny wrote: Sorry, I have again a question about reading a sqlite db from c#. Suppose you have a simple Boolean column in a table. I want to use a .net DataReader (connected layer). My question is: Why I get a cast exception when calling the getBoolean method (solution A)? Casting the DataReader to Boolean (solution B) works perfectly. A) Boolean my_bool = DR.getBoolean(... B) Boolean my_bool = (Boolean)DR[... Obviously I can choose B but I would prefer the A programming style. SQLite does not have a Boolean data type, so it stores the values as an integer or a string. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performing Multiple SQL Statements Within A Transactions Using TCL
On 10/10/2013 2:14 PM, Tilsley, Jerry M. wrote:
All,
Does anybody have any examples of wrapping multiple SQL insert/update
statements in a transaction using the TCL API?
Sure:
db transaction {
db eval {create table if not exists s2011.tclcode(procname text,
version text, tcl text, comments text, unique(procname, version))}
db eval {delete from s2011.tclcode}
foreach table $tablelist {
set shorttablename [lindex [split $table .] 1]
set tc [db eval select tcl, comments from $table limit 1]
set t [lindex $tc 0]
set c [lindex $tc 1]
db eval insert into s2011.tclcode(procname, version, tcl,
comments) values(:shorttablename,:::GEB::defaultversion,:t,:c)
}
}
HTH,
Gerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple connection to the same DB
My track record of giving helpful advice here is not great, but this does not sound like an NFS problem to me. Your example made it look like everyone is logging into the server, and running the SQLite executable located there, rather than running an executable located on their own machine and all accessing the db file on the server. These are very different things. If I am right, then something else is happening, such as somehow using different files, or something else deleting or otherwise modifying the file between accesses. HTH, Gerry On 9/22/2013 8:34 AM, olivier Ménard wrote: The os in use is Linux Ubuntu, so i suppose the protocol NFS too. If i have well understood : in theory, multiple access should work with sqlite, but in practice, it doesn't because of the os : the mechanism of locking doesn't work ? I supose it won't change anything but Is it possible to improve the mechanism If everyone writes an explicit BEGIN IMMEDIATE (or EXCLUSIVE ?) INSERT ... END Thanks for all the answers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite clusters?
On 9/17/2013 6:24 AM, Simon Slavin wrote: Just a quick couple of things you didn't mention that might help. You probably already know about them but you mentioned ATTACH and didn't mention them so I thought I might niggle you. First, look into VIEWs. You can save any SELECT as a VIEW, then consult it like you would a table. So if you have split your data up in separate tables, and even separate databases using ATTACH, you can reunite it by defining a VIEW that includes one or more JOINs. Hmmm, I don't think this is correct. I was under the impression that a view is limited to the tables in the db file where it resides, and have received error messages whenever I tried to access another file's tables. Gerry Snyder ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite clusters?
On 9/17/2013 8:51 AM, Tony Papadimitriou wrote: A temp view, however, can access table from different DBs. - Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Literature on the information theory behind SQL(lite)?
O'Reilly just started a 50%-off sale on all ebooks (60% on orders of $100 or more), good through Sept. 10. A good chance to pick up these or anything else, SQL-oriented or otherwise. The discount code* *is B2S3 (but is also shown on the website). Gerry * * On Thu, Jul 18, 2013 at 5:31 AM, Richard Hipp d...@sqlite.org wrote: On Tue, Jul 9, 2013 at 9:27 PM, Jay A. Kreibich j...@kreibi.ch wrote: If you want to learn more about the theory and concepts behind SQL, I would strongly recommend these two books: SQL and Relational Theory (2nd Ed) by C.J. Date http://shop.oreilly.com/product/0636920022879.do Relational Theory for Computer Professionals by C.J. Date http://shop.oreilly.com/product/0636920029649.do O'Reilly is running a half-price sale on the latter book, today only. ($16 instead of the usual $32.) Use the discount code DEAL to claim the reduced price. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off
On 7/15/2013 1:18 PM, Bernd wrote: I'm reading that text out of an Oracle-DB into a SQLite table which has the affected column defined as 'String' - which maps to TEXT in native SQLite No. Look at section 2.1 of http://sqlite.org/datatype3.html Only CHAR, CLOB, or TEXT cause the column to have TEXT affinity. HTH, Gerry Snyder ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite database on Dropbox, Google Drive, MS SkyDrive, Ubuntu One or SAMBA share
On 6/27/2013 12:38 PM, joe.fis...@tanguaylab.com wrote: Anyone, Does anyone have good or bad experiences using a SQLite database in a shared folder? The 'Dropbox / Drive / SkyDrive / One' I use Dropbox for SQLite files a lot. If I am not careful to be making changes to a file on only one PC at a time, I can get a Conflicted copy on one of the machines, but by and large it has worked very well. I can not think of any way Dropbox could be handling things better. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] configure file is missing from sqlite-autoconf-3071600/tea directory
Probably why the file is a bit smaller than other recent ones. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is the easiest way of changing the field type?
On 12/2/2012 1:52 PM, Igor Korot wrote: So, does this mean that I need to drop the DB in the text file, edit it and then re-create it from this file? How to make alterations to a table that can not be done with the ALTER TABLE command is outlined in topic 11 of the FAQ: http://sqlite.org/faq.html#q11 You just have to make a change to a column definition rather than add or drop columns, but the idea is the same. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Status analyze with Sqlite
On 11/22/2012 4:47 PM, Steffen Mangold wrote: HI sqlite community, I have a problem I get stucked, maybe someone can help me. :( My issue: For instance if we have 10 rows with following data ID | TimeStamp | Status 0 | 2012-07-24 22:23:00 | status1 1 | 2012-07-24 22:23:05 | status1 2 | 2012-07-24 22:23:10 | status2 3 | 2012-07-24 22:23:16 | status2 4 | 2012-07-24 22:23:21 | status2 5 | 2012-07-24 22:23:26 | status2 6 | 2012-07-24 22:23:32 | status2 7 | 2012-07-24 22:23:37 | status3 8 | 2012-07-24 22:23:42 | status3 9 | 2012-07-24 22:23:47 | status3 What I want as result is ID | Begin | End | Status --- 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 Hmmm, the ID in the result bears virtually no relation to the ID in the data. Is that intentional? Anyhow, some of what you want could come from select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data group by Status order by Status HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE Query Assistance Please
On 9/24/2012 9:25 AM, Don Goyette wrote: So, I still need to know how to convert the Excel format timestamp (Days since 1900-01-01) into a Unix Epoch format timestamp (Seconds since 1970-01-01). I agree with Bart's reply, but to convert epochs, subtract the Excel format timestamp of 1970-01-01 (easily found in excel), and to convert units multiply the result by seconds per day (86400). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
On 9/16/2012 9:17 AM, John Clegg wrote: I have a table Members with 896 rows and a text field Year2012. It contains Paid 156 times, Comp 13 times and the rest are null (confirmed in sqlitebrowser as empty) Back in the olden days when this table was in Access, select count(Year2013) from Members used to return 169. In LibreOfiice with the data stored in embedded HSQL it returns 169. In LibreOffice connecting to sqlite3 it returns 896. Any ideas please? Read the documentation? http://sqlite.org/lang_aggfunc.html The count(X) function returns a count of the number of times that /X/ is not NULL in a group. The count(*) function (with no arguments) returns the total number of rows in the group. HTH, Gerry PS Note that it is count(X) and not count(X) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tcl binaries for SQLite 3.7.14, Win Linux
Hello, all, I like to keep up with SQLite deliveries, and have started compiling the Tcl bindings for Win 32 and Linux 32. If anyone would like them, they are available at: https://www.dropbox.com/sh/1ropl1g9xsif1ci/Vc2l-zUEgp Both binaries were compiled with ./configure, make, and strip. The Linux file was renamed to match the Win one (except .so instead of .dll) so that my cross-platform tclkit can use it. I have run both minimally, so they work for me, but YMMV. No guarantee, warrantee, or promises. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is SQLite the right tool to analyze a 44GB file
The file name does not necessarily have anything to do with the table name. On May 7, 2012 2:25 PM, peter korinis kori...@earthlink.net wrote: Simon I searched the entire disk for the table name and no matches. pk ___ 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] Concurrency
On 4/30/2012 5:10 PM, Sean Cui wrote: Here is a simple scenario to explain what the OP meant: Under WAL mode, In connection A, we issue SQL UPDATE Employee SET Salary=0 to SQLite. While the command is executing, from another connection, we issue SELECT Salary FROM Employee. In this case, even under WAL, the SELECT command will still be blocked by the UPDATE command, right? The document referred to several emails ago in this thread states: The WAL approach inverts this. The original content is preserved in the database file and the changes are appended into a separate WAL file. A COMMIT http://www.sqlite.org/lang_transaction.html occurs when a special record indicating a commit is appended to the WAL. Thus a COMMIT can happen without ever writing to the original database, which _allows readers to continue operating from the original unaltered database while changes are simultaneously being committed into the WAL_. Multiple transactions can be appended to the end of a single WAL file. (Emphasis mine) Otherwise it would not be very concurrent, would it? Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are two select statements 2000 times faster than one?
At worst you could use another table to keep track of the maximum and minimum, and update it with triggers when something is added to or deleted from the virtual table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem compiling Tcl bindings on Win
I thought it might be nice to be able to keep my Tcl bindings for SQLite up to date, so I downloaded the autoconf tarball on both my linux and Win (Vista) machines. Not surprisingly, the compile went fine on linux. On Windows I also downloaded and installed MSYS / MINGW. I got into the tea directory and ran configure, which ran to completion with only one warning WARNING: 'Makefile.in' seems to ignore the --datarootdir setting In running make, there were a few warnings and then: gcc -shared -o sqlite3711.dll tclsqlite3.o /c/Tcl/lib/tclstub85.lib tclsqlite3.o:tclsqlite3.c:(.text+0x4883): undefined reference to `tclStubsPtr' tclsqlite3.o:tclsqlite3.c:(.text+0x48a0): undefined reference to `tclStubsPtr' tclsqlite3.o:tclsqlite3.c:(.text+0x48b9): undefined reference to `tclStubsPtr' tclsqlite3.o:tclsqlite3.c:(.text+0x48d2): undefined reference to `tclStubsPtr' tclsqlite3.o:tclsqlite3.c:(.text+0x48e0): undefined reference to `tclStubsPtr' tclsqlite3.o:tclsqlite3.c:(.text+0x48f1): more undefined references to `tclStubs Ptr' follow tclsqlite3.o:tclsqlite3.c:(.text+0x6da54): undefined reference to `Tcl_InitStubs ... collect2: ld returned 1 exit status make: *** [sqlite3711.dll] Error 1 The /c/Tcl/lib/tclstub85.lib points to the Active State 8.5.11 Tcl I use. Is my problem due to omitted steps, wrong steps, or configuration? TIA, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replacing several nested queries and UNION ALLs with one query
a2 != '' seems redundant when a1 = a2 and a1 != '' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE
On 2/6/2012 8:36 AM, Bill McCormick wrote: Is there no way to force columns added to a table with alter table to be added at certain column positions? Alternatively, if there is some way to save the data in an existing table; drop the table; re-create the table with the desired schema; and then reload the data, this would be useful as well. However, I cannot see how to do this simply. A very quick search at the SQLite website (hint, hint) found: http://www.sqlite.org/faq.html#q11 HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ALTER TABLE
On 2/6/2012 9:22 AM, Bill McCormick wrote: Sorry, I should have mentioned that I did see that, but it doesn't quite fit my application. I need a script that doesn't care what the existing table looks like. In my situation, I may have dozens of databases among different locations, perhaps not all at the same revision level. The script I need would be able to bring each up to the current revision. So, if I had a fist step: CREATE TEMPORARY TABLE t1_backup AS SELECT * FROM t1; and then DROP TABLE t1; and then add the table with it's latest schema revision CREATE TABLE t1( ... ); It seems difficult to get the saved data back in ... INSERT INTO t1 SELECT * FROM t1_backup; ... without know what the previous schema looks like. It complains like this: Error: table prod has 27 columns but 25 values were supplied Yes, the INSERT statement has to specify all of the original column names in the proper order. I wrote a general ALTER TABLE code in Tcl, and it is one of the largest functions in the system. It includes moving columns within a table, since I agree some times a simple spreadsheet-like display is useful. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error on selecting non-grouped column
Would be pragma to reverse unordered selects show a different result? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database schema has changed?
On 9/13/2011 8:38 AM, Tim Streater wrote: I don't see an easy way of solving conflicting absids. The hard way of solving this is to select all the fields of the messages table explicitly (except absid), so I can then insert them into a new row in the destination table. But I'm trying to avoid this as a maintenance headache (I may wish to change the schema for messages from time to time). The explicit column selection sure seems like the right way of solving it to me. What you are doing to avoid it is (obviously from the discussion) difficult and error-prone. Yes, you will have to change the statement when the schema changes. I think of that as a task, not a headache. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ISNULL in sqlite
On 6/25/2011 12:33 PM, logan...@gmail.com wrote: Hello, How do I check for a null or empty string in SQLite. In addition to the other replies you have received, you need to be made aware that an empty string and a NULL are very different, and (perhaps) both have to be checked for, depending on how the data gor into the table. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite as a Logger: How to mimic rotation of logs?
If this has already been suggested, I apologize. Add an integer column with a UNIQUE ON CONFLICT REPLACE constraint.Then after you figure out how many entries are enough (maxcount), insert each row, specifying that column as mod((lastinsertrowid()+1),maxcount) or however you specify a modulus or remainder. That column will just wrap around when it hits maxcount; and you extract based on rowid, not that column to keep things in proper order. And don't worry about maxing out on rowid. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ADV: Using SQLite ebook, 50% off today
I learned a lot from it, too. Gerry Snyder On 5/3/11, Nico Williams n...@cryptonector.com wrote: On Tue, May 3, 2011 at 11:20 AM, Simon Slavin slav...@bigfraud.org wrote: On 3 May 2011, at 5:09pm, Jay A. Kreibich wrote: Using SQLite is today's Ebook Deal of the Day over at O'Reilly Media. Today only (Tuesday, May 3rd) the ebook is 50% off, at $15.99. Well I don't know, Jay. Have you read it ? Is it any good ? For those who don't know, Jay A. Kreibich is in fact the author of said book. I can't tell you if it's any good, because I'm not the target audience, but you can read a /lot/ of the content from the link he provided and try before you buy. I use it as a reference, along with the docs at sqlite.org. It's quite good. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Lack of decimal support
Do money values really get multiplied together? What is the meaning of square cents as a unit? Gerry On 3/26/11, Patrick Earl pate...@patearl.net wrote: That is true, but then when you are formulating generic queries within a place such as an ORM like NHibernate, you would need to figure out when to translate the user's 100 into 1. As well, if you multiplied numbers, you'd need to re-scale the result. For example, (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :( If one wanted to get excessively complicated, they could implement a series of user functions that perform decimal operations using strings and then reformulate queries to replace + with decimal_add(x,y). That said, it'd be so much nicer if there was just native support for base-10 numbers. :) Patrick Earl On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare list@barefeetware.com wrote: On 27/03/2011, at 12:39 PM, Patrick Earl wrote: Base-10 numbers are frequently used in financial calculations because of their exact nature. SQLite forces us to store decimal numbers as text to ensure precision is not lost. Unfortunately, this prevents even simple operations such as retrieving all rows where an employee's salary is greater than '100' (coded as a string since decimal types are stored as strings). Can you store all money amounts as integers, as the cents value? That is exact, searchable etc. Thanks, Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import FILE TABLE
On 3/10/2011 1:28 PM, jcilibe...@comcast.net wrote: Hello, Unbelievably active user group! I have been unable to import a CSV text file from MS Access to sqlite: 1. Created a small table (3 fields and 1 record) in Access and exported it to a CSV text file named myCSVfile.txt 2. Transferred from PC to Mac. Opened file myCSVfile.txt ...looks OK eg: [1, Jack, Sammamish] 3. Created a new DB (myDB) and table (myTable) in SQLite Database Browser eg: [ID:primaryKey Name:text City:text] 4. Opened the DB in terminal with sqlite myPath/myDB Does adding the line: .separator , help? 5. Entered command .import myPath/myCSVfile myTable Always get back message: line 1: expected 3 columns of data but found 1 Help! I've read many archived posts...so I know this should work. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prescott Iris Soc
-- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected cascading delete
On 1/12/2011 2:54 PM, Duquette, William H (318K) wrote: I've just discovered that a REPLACE can trigger a cascading delete. Is this expected behavior? I have an undo scheme where I grab entire rows from the database before they are changed; then, on undo I simply put the rows back using INSERT OR REPLACE. My assumption was that doing a REPLACE was equivalent to doing an UPDATE on the non-key values given the key values. Apparently not. From the ON CONFLICT section of the docs: When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. This seems to make it expected behavior. Gerry (JPL retiree) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
On 12/13/2010 6:54 AM, steve mtangoo wrote: I have a script that is supposed to query the Bible scriptures between two intervals. My table is named Bible and have columns: ID (int), Book (int), Chapter(int), Verse (int) and Scripture(text). Is your ID column a sequential numbering of the verses? If so, using it could simplify the query. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to write Query to do this?
On 12/13/2010 7:55 AM, steve mtangoo wrote: Yes, ID is autoincrement and hence sequential. The problem with using it is, I have to know the ID of the beginning (for eg Book 1 Chapter 1 Verse 2) and the Id of the end (eg Book 4 Chapter 10 Verse 3). Then Simple BETWEEN will resolve it. Thanks for replying! select verse from Bible where ID between (select ID from Bible where book = 1 and Chapter = 1 and Verse = 1) and (select ID from Bible where book = 2 and Chapter = 3 and Verse = 1) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to optimize this simple select query ?
On 12/8/2010 9:56 AM, Vander Clock Stephane wrote: Hello, on the table : CREATE TABLE HASH( ID INTEGER PRIMARY KEY ASC, x1_y1 INTEGER, x1_y2 INTEGER, ... x5_y5 INTEGER ); CREATE INDEX HASH_X1_Y1_IDX ON HASH (X1_Y1); CREATE INDEX HASH_X2_Y2_IDX ON HASH (X2_Y2); CREATE INDEX HASH_X3_Y3_IDX ON HASH (X3_Y3); CREATE INDEX HASH_X4_Y4_IDX ON HASH (X4_Y4); CREATE INDEX HASH_X5_Y5_IDX ON HASH (X5_Y5); with millions of rows, how to optimize such query : Select ID from HASH where x1_y1=#randomnumber1 and x1_y1=#randomnumber1+ 20 and ... x5_y5=#randomnumber73 and x5_y5=#randomnumber73 + 20; because they takes very very lot of time (hourS) to return :( on other SGBD (like Firebird) with same amount of data they return immediatly ... I usually seem to be wrong when I try to help here, but I keep trying. My guess is that SQLite uses only one index per query (or per table per query or something like that), and so has to do a whole bunch of full table scans (or at least full scans of the remaining rows). Maybe you need a query like: select id from (select id from hash where x1_y1 BETWEEN #randomnumber1 AND (#randomnumber1 + 20)) where x1_y2 BETWEEN #randomnumber4 AND (#randomnumber4 + 20) nested many more levels deep. This might allow using all the indices. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug: wrong query result when using an index
On Sun, Dec 5, 2010 at 7:25 AM, Gavrie Philipson gav...@gmail.com wrote: Hi, The query is as follows: SELECT entry_type AS entry_type_int, entry_types.name as entry_type_name, entry_id FROM timeline JOIN entry_types ON entry_type_int = entry_types.id WHERE (entry_type_name = 'cli_command' AND entry_id IN (SELECT command_id FROM object_changes WHERE obj_context = 'exported_pools')) OR (entry_type_name = 'object_change' AND entry_id IN (SELECT change_id FROM object_changes WHERE obj_context = 'exported_pools')) I also got no rows with the above query, but got: 300 object_change 2048 after changing the query to: SELECT entry_type AS entry_type_int, entry_types.name as entry_type_name, entry_id FROM timeline JOIN entry_types ON entry_type = entry_types.id WHERE (entry_types.name = 'cli_command' AND entry_type_name IN (SELECT command_id FROM object_changes WHERE obj_context = 'exported_pools')) OR (entry_type_name = 'object_change' AND entry_id IN (SELECT change_id FROM object_changes WHERE obj_context = 'exported_pools')) The changes were to use the real column names and not the aliases from the SELECT clause. I believe the problem arises (and the book Using SQLite explains it a lot better than I can) because the FROM and WHERE clauses are executed before the SELECT clause. I can not explain why the absence or presence if an INDEX changes the result. It is a known feature of SQLite that it does not contain huge amounts of error checking, and the results when you do things you shouldn't can be surprising. Hope this helps, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [ADV] 60% off Using SQLite and other O'Reilly ebooks
I bought this book on opening day and now can highly recommend it. It is a very useful supplement to the online docs. The info is pretty up-to-date, but SQLite keeps advancing so the most recent enhancements perforce are missing. For example, wal mode is not covered, but there is a good description of foreign keys. In the general SQL sections, I found The SELECT Pipeline among the most illuminating, giving good explanations of what happens in the processing of each clause. The book is one of my better recent purchases. Gerry On 11/29/10, Jay A. Kreibich j...@kreibi.ch wrote: O'Reilly Media is running a one-day sale, Monday, 29 Nov, only. *All* ebooks and videos are 60% off, including Using SQLite and over 2000 other titles. Just use the discount code DDF2H when placing your order. With this discount, the ebook version of Using SQLite is only $12.80 USD. Purchasing this title provides lifetime access to DRM-free PDF, ePub, Mobi, and APK files. http://oreilly.com/store/index.html Sale info http://oreilly.com/catalog/9780596521196Using SQLite -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_sequence table
On 11/16/2010 2:14 PM, Duquette, William H (316H) wrote: Howdy! According to the docs on sqlite.org, a table with INTEGER PRIMARY KEY AUTOINCREMENT gets an entry in the sqlite_sequence table. I've got some code that contains such a table; but if I query the sqlite_sequence table I don't see it being updated; it's always empty. Anyone have any idea what's going on? Have you put anything in the table that is INTEGER PRIMARY KEY AUTOINCREMENT so there is a sequence number to store in sqlite_sequence? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite database sync
Keep track of changes, keep track of backups. On 10/6/10, David Haymond haymondsoftw...@gmail.com wrote: If I copy, I don't want to transfer EVERY record to the server each time I sync, because that would be a waste of bandwidth. What is the best way to copy only those records that have changed to the server? David -Original Message- From: Simon Slavin Sent: Wednesday, October 06, 2010 7:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite database sync On 6 Oct 2010, at 1:29pm, David Haymond wrote: I am completely new to SQLite (and SQL in general), and I am currently working on an iPhone app that uses the embedded SQLite engine to cache offline data. What is the best way to implement synchronization capabilities (change tracking, state tracking, etc.) in a separate table (such as meta), so that the app can sync to the server? My database contains two tables: locations and trips. trips is the child of locations. Does it actually have to synchronise ? In other words, are changes made to both copies, or only to the copy on the iPhone ? If changes are made to only one copy, you can just copy that copy (if you see what I mean). If changes are made to both copies, you're in for a world of hurt because you really need to separate out intentional changes from the changes actually made. One way to do it is to keep a log of the operations executed since the last synch. For example you could make another table 'changelog': timestamp operation 2398479 INSERT INTO locations ... 2402372 UPDATE trips ... To synchronise you just execute the log, in timestamp order, on all the other copies. This does not work properly under all circumstances because UPDATE commands can interfere with one-another, but in a simple database it should work fine. To do things properly you need to maintain a separate unchanged model, synch it with all the satellites, then copy it to all the satellites. It's complicated and requires lots of bytes moved about. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query suggestion?
On 9/9/2010 11:32 AM, Doug wrote: Thank you Igor. You've helped me before with what also turned out to be a similar select referencing the same table twice. I guess it's a concept that I don't fully get. If there is a name for this technique I'll go Google and study up on it. You should be able to find some good info by googling :correlated subquery Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why the deadlock?
Er, did you not see Dan Kennedy's comments a fed days ago?? On 8/24/10, Nikolaus Rath nikol...@rath.org wrote: Nikolaus Rath nikolaus-bth8mxji...@public.gmane.org writes: Still no one able to clarify the issues raised in this thread? Let me try to summarize what I still don't understand: - Will SQLite acquire and release an EXCLUSIVE lock while keeping a SHARED lock if one executes a UPDATE query with one cursor while a different cursor is in the middle of a SELECT query, -or- will the EXCLUSIVE lock be held until the SELECT query finishes? - Is there a way to prevent SQLite from keeping the SHARED lock while waiting for an EXCLUSIVE lock if doing so would result in a deadlock (because another connection holding a SHARED lock needs to get an EXCLUSIVE lock before it can release the SHARED lock)? Hmm. Still no answer. But thanks to Simon I know at least that some people are reading this thread :-). So different question: does anyone know how to get this thread to the attention of an SQLite developer who might be able to help? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using SQLite - O'Reilly Deal of the Day
On 8/24/2010 8:09 AM, Jay A. Kreibich wrote: Using SQLite has gone to press! To celebrate, Using SQLite is is today's O'Reilly Ebook Deal of the Day. And quite a deal it is! Thank you for posting the announcement, Jay. I had just been wondering what book to start reading on my BlackBerry, and now I know. Thanks again, Gerry PS Quick review: The index looks pretty decent. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using variable is queries
On 7/30/2010 5:40 AM, Igor Tandetnik wrote: chris23879chris23...@blueyonder.co.uk wrote: I'm tring to create a paging function in sqlite. Is it possible to declare and use a variable in sqlite. No. But since SQLite is embedded in your application, you can use variables in your host programming language. And the other answer is that every row of every table is filled with nothing but variables. Accessing them is a little more verbose, admittedly. If you don't want to keep the variables, use a temporary table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems with running test suite
package require Tcl 8.5 is all it takes Gerry On 7/29/10, Richard Hipp d...@sqlite.org wrote: On Thu, Jul 29, 2010 at 4:44 PM, Paweł Hajdan, Jr. phajdan...@chromium.orgwrote: I'm planning to contribute some patches (upstreaming patches Chromium project applies to its local copy of sqlite). I've checked out the fossil repository, configured and compiled sqlite, and tried running make test (I didn't make any changes). I'm pasting below the test result and some fossil info. Please let me know if you need more. This is on Ubuntu Lucid. Do you have any ideas what makes these tests fail? Should I be doing it some other way? wal2-10.2.1... Ok wal2-10.2.2... Ok wal2-10.2.3... Ok wal2-11.0... Ok wal2-11.1.1... Ok ./testfixture: bad field specifier t while executing The test suite now requires Tcl 8.5. You appear to be running Tcl 8.4. It would be relatively easy for use to enhance the test suite so that it automatically detects a too-old version of Tcl and prints a warning or error. We'll try to make that change for you soon. -- - D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with complex UPDATE question
On 7/23/2010 10:09 AM, Jim Morris wrote: What you are trying to do is unclear to me. It seems that table1 doesn't have enough data to unambiguously identify the rows. On 7/23/2010 8:03 AM, peterwinson1 wrote: Thanks Eric and Alan for your help. I tried to apply your code to my problem and it works to a limited extent because the problem is more complicated than the example I gave in the post. I tries to simplify my exact problem but that didn't work out. So here is the problem that I trying to solve. table1 (KEY, COL1) 0, 1 0, 2 1, 3 1, 4 2, 5 2, 6 3, 7 3, 8 It seems to me that you should really have: table1 (KEY, COL1, COL2) 0, 1, 2 1, 3, 4 2, 5, 6 3, 7, 8 based on the rest of your question. Is there a good reason you can not use a schema like this? Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there busy handling in SQLite jdbc drivers?
I could not find any information on either the zentus or xerial websites on any built-in provisions for busy handling. Did I miss it, or is the functionality there but not documented, or do I need to check error codes and retry in my code? TIA, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to determine when to VACUUM?
On 7/6/2010 2:58 PM, Kristoffer Danielsson wrote: What you are saying makes sense. Thanks for your advice! However, I do believe there are times when vacuuming would be beneficial. For instance, if a database for software X is detected to have 90% unused space for a couple of weeks, then why bloat the harddrive? If there is lots of free space on the drive, why work at giving it a little bit more? In any event, knowing the database and hard disk usage patterns will lead to much, much, much better criteria for vacuuming than any general rule of thumb. Optimizing plentiful resources is non-optimal. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXTERNAL:Re: How to select an entry that appears =ntimes and only show n times if it appears more than n times?
On 7/2/2010 10:09 AM, P Kishor wrote: I was going to add That is an Igor-question to I don't know how to do that with sql. I have no idea how you do this, but if ever I meet you in person, I will be too awestruck to say anything beyond SELECT.. From me it would more likely be an irate Who are you and how dare you be so much smarter than I am? But +1 on the awestruck for sure. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unexpected large journal file
On 6/18/2010 7:01 PM, Scott Hess wrote: The old-school solution to this problem is an external sort (*). The basic idea is that you process the incoming data in memory-sized chunks (ie, fast), then write out sorted subfiles. Then you process the sorted files in parallel, merging to the output. The other old-school solution involving preprocessing is block sorting or binning. You go through the data once and create a bunch of files of unsorted but grouped data. Then insert the data from the files in order. If the files are transaction-sized, each one will be adding to a fairly small range of values. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select %column% from table
So why not columns keyword and value?
Gerry
On 4/30/10, David Lyon david_ly...@yahoo.com wrote:
Thanks for everyones efforts let me expand:
if I had many many files like this:
http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt
you see 2 columns keyword and value, the keywords would be the fields (1st
column in the html link above) in the table while the values (second column)
are the data I would insert.
The fields will be many and include
P$nR
P$nS
P$nB
etc.
thats why I wanted a quick way to access select P%R from TABLE;
thanks again
- Original Message
From: P Kishor punk.k...@gmail.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Fri, April 30, 2010 10:48:41 AM
Subject: Re: [sqlite] select %column% from table
crap! I completely misunderstood your question... be confused, and
then ignore my reply.
On Fri, Apr 30, 2010 at 9:43 AM, P Kishor punk.k...@gmail.com wrote:
On Fri, Apr 30, 2010 at 9:28 AM, David Lyon david_ly...@yahoo.com wrote:
If I had a table called TABLE with fields P1N..P50N is there a way to
select something like:
select P%N from TABLE
to return all the results from columns P1N..P50N or do I have to do
it manually:
select P1N, P2N, P3N, P$nN from TABLE
use GLOB. See below
Last login: Wed Apr 28 09:42:46 on console
punk...@lucknow ~$sqlite3
-- Loading resources from /Users/punkish/.sqliterc
SQLite version 3.6.23
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite CREATE TABLE t(a);
sqlite INSERT INTO t VALUES ('P1N');
sqlite INSERT INTO t VALUES ('P3N');
sqlite INSERT INTO t VALUES ('P30N');
sqlite INSERT INTO t VALUES ('P303N');
sqlite INSERT INTO t VALUES ('P303X');
sqlite INSERT INTO t VALUES ('P30Z');
sqlite SELECT * FROM t;
a
--
P1N
P3N
P30N
P303N
P303X
P30Z
sqlite SELECT * FROM t WHERE a GLOB 'P*N';
a
--
P1N
P3N
P30N
P303N
sqlite
I can obviously do it via scripting but wanted a more elegant way.
Thanks for your help in advance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
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
--
Sent from my mobile device
___
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
How long would it take to test using an index with 18 fields? Might not be crazy. Gerry On 4/23/10, Nathan Biggs nbi...@mycfs.com wrote: Max, thanks for the information. That will be very useful for other table queries, but not for this one. For my table in questions there are 18 fields. I think an index with 18 fields would be a little crazy. On 4/23/2010 3:06 AM, Max Vlasov wrote: ...As I add more restrictions on the where-clause it tends to slow down. I realize that this is due to my indexes, but can't add a lot of indexes because it slows down the insert speed which is more important than the query speed. Nathan, maybe you already knew but just in case... if your select relies on an index and also queries fields not presented in that index, consider appending these extra fields to the index. It doesn't make sense in terms of search speed, (moreover it will increas the db size), but this will save time since no extra lookup will be taking place. So if you have table CREATE TABLE a, b, c, and index CREATE INDEX ON a, b and use query similar to SELECT a, b, c ... WHERE a= and b = sqlite will do extra lookup to get c from the table, but if you change the index to CREATE INDEX ON a, b, c the same query will get all the data from the index itself saving time and the amount of data flow. I did a quick test and it showed not only a noticable difference in time, but also a significant difference in amount of the data read. Max, maxerist.net ___ 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 -- Sent from my mobile device ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 23, Issue 26
Peter Haworth wrote: I have this question too. I'm not programming in C so don;t have access to the sqlite_last_insert_rowid Yes you do. You just didn't realize it. Using the cmd line tool: F:\sqlitesqlite3 SQLite version 3.5.9 Enter .help for instructions sqlite create table a(b); sqlite insert into a values(17); sqlite select last_insert_rowid(); 1 sqlite insert into a values(18); sqlite insert into a values(19); sqlite select last_insert_rowid(); 3 sqlite HTH, and happy turkey day!! Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacing a table
Pavel Ivanov wrote: To update column in all rows of the table you need to issue the following statement: UPDATE table_name SET column_name = value And note that the value above does not have to be a constant. It can, for instance, depend on other values in the row being updated. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dump in-memory db to file in tcl
Ned Fleming wrote: Is it possible to dump an in-memory sqlite database (or table?) to a file from within Tcl? I create it like so: sqlite3 dbFireData :memory: and insert a bunch of records, and then commit. Is there reason not to attach a file (old or new), and either create table realfile.newtablecopy as select * from main.originaltable or else create the new table with ~ the same create table statement used for the original table, and then insert into realfile.newtablecopy select * from main.originaltable ? The first method copies all the data, but will drop any special thingies from the column definitions. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integer Storage class
Beau Wilkinson wrote: i chose Integer for Performance isues... i'm develop. an POS Software and our articles table has lot's of records. i need to optimize search. an Integer (1,2,4,6,8 bytes) is faster that 13-bytes-ean text for comparison. That's reasonable, but I think Sqlite stores everything as textual data anyway. I think the implication is that this data will take at least one-byte-per-digit. Not true in SQLite 3. See: http://www.sqlite.org/datatype3.html Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problems encountered on upgrade from SQLite2 to -3
Rod Dav4is wrote: Thanks for reminding me: A thing's value is generally proportional to its cost. And the attitude of its support team figures in there, too. -R. There is only one person with attitude I see here, and it is not Dr. Hipp and it is not P. Kishor. I have never seen a program, free or commercial, with better support. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Undefined Symbol: Tcl_CreateObjCommand
Carlos Tasada wrote: Hi guys, I'm trying to use sqlite 3.6.17 from a Tcl script in Linux, but as soon as I do load libsqlite3.so I get an error: undefined symbol: Tcl_CreateObjCommand Testing the script in Windows works fine. Anyone knows how to solve it? Thanks. Looks like you are using the library without the Tcl bindings. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Protect against SQL injection inside of the database?
On Thu, Jul 16, 2009 at 4:20 AM, Michael Schlenker m...@contact.de wrote:
This is perfectly safe:
set result [db1 eval {select * from X where label = $myStringValue and id
$compId}]
But you MUST use {} to quote your query and not , so sqlite gets to do
the
substitution (or better said convert things to prepared statements and bind
values correctly) and not Tcl.
No reason to avoid Tcl. You can also avoid the possibility of Tcl
substitution by using :myStringValue instead of $myStringValue.
Gerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and MinGWSys
ArbolOne wrote: I would like to compile SQLite in a WinXP machine, how can I do this using MinGW? Have you looked at the Notes on Compiling SQLite section of http://www.sqlite.org/cvstrac/wiki ? A lot of info there. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shared binary plus static lib
Mark Constable wrote: Yikes, that will hurt when I go to build on a TCL-less system. There are stand-alone Tcl binaries for many platforms, should that ever be a problem for you and you don't want to install a full package. Both etcl and freewrap are good products. They come with other files, but the binary is all that is needed. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it ok now to create a table with the same name as a table in an attached file?
In the SQLite web page http://sqlite.org/lang_attach.html there is the statement: You cannot create a new table with the same name as a table in an attached database, but you can attach a database which contains tables whose names are duplicates of tables in the main database. Is the the statement inoperative? Or does it just mean that if an attached file has table xxx and I want to create an xxx table in the main file I have to use the name main.xxx ? I can do the latter, and it seems to work, but I want to be sure there is not some subtle thing I am missing. In the next paragraph there are the statemnts: When a database is attached, all of its tables which don't have duplicate names become the default table of that name. Any tables of that name attached afterwards require the database prefix. Assuming my interpretation is correct, I think the second sentence should read Any tables of that name attached /or created/ afterwards require the database prefix. Thank you in advance, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 tables hidden from program to program
rajyalakshmi bommaraju wrote: Hi, I started using sqlite3 recently on Ubuntu. I came across an issue with the database that, I was able to create database and table from commandline successfully, I also inserted couple of rows, no problem. When I tried to open database from C program, It is fine but I cant access the table. It says that the table doesnt exist ,I get error when I try to query from the table. I had to recreate the table from the C Program then I can insert or read from the table. It looks like the tables are not global and are hidden from one program to other. This should not be the case. What happens if you look at the file with the CLI after creating the table with CLI, and recreating it with C? What does the command select SQL from sqlite_master; show? Issuing that command in the C program before recreating the table could be informative, too. You could be looking at different files, or the table names may be subtly different (such as one of them including a space or linefeed or unprintable character. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minus sign in table name
bitzzz wrote: I am converting an existing database to sqlite format, but I got the following problem: When I create a table name with a minus sign inside then the following column definitions are ignored. So: CREATE TABLE 'TEST1-TEST' (TST TEXT); refuses to create the column TST in the table TEST1-TEST. The table is created without TST column without any error. CREATE TABLE 'TEST1TEST' (TST TEXT); works ok. So my question is what is wrong in my SQL statement ? I also tried with alter table, but got the same result. Did you try: CREATE TABLE TEST1-TEST (TST TEXT); (note the use of double quotes) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit to database and/or blob size on Mac/Windows?
Vinnie wrote: . Is there a limit to the database size on Windows or Macintosh? Does the following help? http://sqlite.org/limits.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite 2
Chuvke wrote: Hello All, I need a sqlite 2 windows binary but can't find it on the sqlite site anywhere. Anyone know where I can download sqlite.exe ? Any v2 will be ok. I am sure there better ways, but if nothing else comes up, I can email 2.8.17 to you. the zip file is only 133K. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vacuum syntax
Richard Dauben wrote: Hopefully a quick Vacuum question I want to vacuum my sqlite3 database from an Objective C program but have not been able to find the necessary routine or statement. What is the proper syntax? Gimme a V Gimme an A Gimme a C Gimme a U Gimme a U Gimme an M It's just an SQL command. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it using an index?
Scott Baker wrote: I didn't realize INTEGER PRIMARY KEY was case sensitive. Thanks Are you sure what you used before did not have a typo, or the words in a different order? Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Commit frequency and performance
pri...@gmail.com wrote: Yes, there are 3 indexes being created I'll post again after figuring out which of the changes improved the performance. Thanks for the clues! Since you are able to do some experimenting, try entering the data without the indices, and then create them. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieving NANs
Sander Jansen wrote: I need to store NaNs in my database and able to retrieve them as well. Since sqlite will give me back 0.0 when I call sqlite3_column_double and a result contains a NAN, I was wondering what the best way is to retrieve a NAN from the database. Here's what I currently do in peudo code: if (sqlite3_column_type(column)==SQLITE_FLOAT) value = sqlite3_column_double(column); else value = NAN; Now, the doc says that The value returned by sqlite3_column_type() is only meaningful if no type conversions have occurred as described below. Am I correctly assuming the no type conversions have occurred means no type conversions on that (row,column) of the result set? I mean next time I call sqlite3_column_type() on the same column but on the next row of the result set, it will still give the correct answer? Thanks, Sander That sounds correct, yes. One suggestion--if the column will always have either a float or NAN, why not just not insert anything if you have NAN, and then test for NULL? HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree insert performance
Oyvind Idland wrote: Is there any trick to speed up the inserts here ? Are you doing the inserts inside a transaction? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Terrible performance for one of our tables
Jens Miltner wrote: Any ideas why there would be such a _huge_ performance hit after deleting and re-inserting records for a while? Without deletes/inserts the reads are sequential, and the OS and/or the drive/controller are reading ahead for you, hiding much of the disk read and seek times. After fragmentation reads are (to some extent) random, and fancy read-aheads do not help. Instead of deleting, could you just somehow mark obsoleted rows as deleted (possibly with a new column, but probably some existing column could just have a unique value for that)? The file would grow a lot larger, and you would be doing a lot of unneeded reads, but the fact that they are sequential might more than make up for that. Just a thought. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Network concurrency question
Darrell Lee wrote: Here is my situation: the maximum number of clients that would be reading the SQLite database is 6, of that 6 the maximum number of clients that might be trying to write to the SQLite db is 3. In you guys experience, on a scale of 1-10 , 10 being the most likely to happen, what are the chances of the database becoming corrupt if it is on a network share? Thanks, If your data are critical isn't a 1 unacceptable? And if not, isn't 10 okay? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prices
Arvind M wrote: dear sir i am interested in purchase of licence of sqlite server SQLite does not have a server-client architecture now, any more than it did five months ago. It is all-in-one. You can get the source code and write a server-client pair yourself that uses the same file format as SQLite, if you desire. SQLite is free. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speeding up the simplest queries
Zbigniew Baniewski wrote: On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: count(*) is an odd one... In most database systems it is extremely fast, but in SQLite it tends to be rather slow. I forgot the important thing: usually I was using count(*) just to detect the presence of any record meeting given condition. Is it possible to make it fast _not_ using count(*)? I need just a boolean result of 1/0 (yes, there is at least one / there aren't any). Add limit 1 to the query, so that it stops after finding the first match. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get declared Datatype of SQLite Virtual Table
paul schindler wrote: Thanks for your answer! But I expected that when 'amount' has the declared datatype INTEGER then sum(amount) is also INTEGER... Not if one of the entries in the column is 1.7 Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
D. Richard Hipp wrote: On Sep 6, 2008, at 2:50 AM, Scott Hess wrote: On Fri, Sep 5, 2008 at 11:31 PM, jonwood [EMAIL PROTECTED] wrote: But the documentation states the ROWID can change at any time so I'm not sure what I can do safely. Do this: CREATE TABLE t ( id INTEGER PRIMARY KEY, a TEXT NOT NULL, b INTEGER NOT NULL, UNIQUE (a, b) ); (a,b) will be just as unique as in the first case, but now you can use id as a stable alias for rowid I promise that INTEGER PRIMARY KEY will always be an alias for the rowid in SQLite. This will not change. I am not sure what all this means when taken together. It sounds as if defining id as shown above is unnecessary, since it is just an alias for ROWID, and if one is stable the other has to be. Correct? So now I can't interpret ROWID can change as meaning anything other than the ROWID supplied by SQLite while doing an insertion, if none is supplied by the user, can change at any time. Is this correct? My little Tcl/Tk routines for displaying and managing SQLite files have always assumed ROWID is a safe stable way of determining a row. Have I missed something? (again?) Thanks, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comments on db design
steffen scheer wrote: Hey all I want to use sqlite as a storage engine for DNA sequence data. It is part of a workflow engine for DNA processing. I was wondering whether i chose the right db design. The db holds information about DNA sequences. Arranged in three tables. For every dna sequence one entry in all 3 tables will be made (1:1 Relation). Data will be inserted once, never deleted, but some attributes get updated. To me, KISS (keep it simple, stupid) rules until its performance has been shown to be unacceptable. Surely you can create a dummy db file with nonsense data of typical size all in a single table and see whether it meets your needs. Keeping 2 or 3 tables in synch is not a difficult task, but it is a potential source for error. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating rank field by points in stats table.
Igor Tandetnik wrote: Pejayuk [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a player stats table with a points field. I want to update the rank in this using a single query based on the points in the row. I am not that good at complex sql but I have managed to get the result I want from the following select query. SELECT points,(SELECT COUNT(*) FROM stats b WHERE b.points = a.points ORDER BY points ASC) AS rank FROM stats a This gives me a list of all points and their rank correctly. What I actualy need is an update query along the same lines update stats set rank = (select count(*) from stats b where b.points = stats.points); Igor Tandetnik That does not quite seem to work when there are ties. How about: update stats set rank = (select count(*)+1 from stats b where b.points stats.points); Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help with Dates please
Harold Wood wrote: I have several tables that i need to datestamp as transactions occur and then retrive with a select where between X and Y. nbsp; What is the best way to do this in SQLite? Look at http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions and ask again if you have further questions. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select problem with equal compare
Alexey Pechnikov wrote: 1. I try to select: sqlite select save_date from photo_tags where save_date2454612.21079943 limit 1 offset 3073; save_date = 2454612.21079943 But 2454612.210799432454612.21079943 is wrong result. 2 Please read the recent thread What is quicker which has a long discussion on the limitations of floating point. A short summary might be, Unless you assume that any value stored in floating point format is only an approximation to the input value, you will run into trouble sooner or later. If you need exact results (and want to avoid a _lot_ of analysis), use string values or integers (possibly scaled). Also, when starting a new thread, please do not just reply to an ongoing one and change the subject. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] deleting 100,000 entries
BareFeet wrote: Hi Carlo, I want to clear the whole thing out(list)! Do you mean that you want to delete all rows from the list table? If so, do this: delete from list; Or you could drop the table and then create it again. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Finding Missing Table Rows
Darren Duncan wrote: Clue stick coming up. There's a much simpler solution. You should be using relational difference instead, the MINUS keyword, whose syntax is the same as UNION but for the keyword. I think maybe you mean EXCEPT, not MINUS. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended pragmas for new SQLite users to focus on
[EMAIL PROTECTED] wrote: Hi Gerry, Much care and thought have gone into setting up the default behaviors in SQLite. My advice would be not to use any pragmas initially. That may be the safe solution, but my impression was that SQLite defaults to conservative settings that may not apply to many of today's high RAM workstations. Since I asked my question, I came across the following article that suggests 4 common pragma opportunities for optimization. The article is from 2006 so I don't know if its advice still applies to the current version of SQLite. http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#pragmas The statement below from that article makes it clear that the information in the article was not up-to-date even in 2006: /(This PRAGMA is not fully implemented, as of 2.8.6.) / Versions 2.8.15 and 3.0.3 date back to July 2004, and 2.8.6 is from August 2003. There is no doubt that pragmas can improve performance for lots of different circumstances. Limited RAM, lots of RAM, among many possibilities. Knowing about the kinds of things that can be done is a Good Thing. But my advice stands. Set things up and see how things work. If you want/need better performance, start tweaking. But there is a good chance that the performance out of the box will be fine. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a suggestion to write tutorial for sqlite
P Kishor wrote: For starters, a database of every single email from Igor and Dennis Cote should be mandatory reading for anyone wanting to do anything with SQLite. And drh and Dan Kennedy and Scott Hess. You probably assumed that the first of these went without saying. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select by order Speed
Igor Tandetnik wrote: select id, track from music order by track, id limit 2; select id, track from music where track'aaa' or (track='aaa' and id4) order by track, id limit 2; Igor, Thank you! I had been looking at a more general case, and a small amount of thought made me conclude that what I wanted was not possible. You just showed that it is not only possible, but even fairly straightforward. In my situation the first two fields cannot be relied on to be unique, so rowid will have to be added to the where and order by clauses, but now it is clear what needs to be done. Thanks again, for this and many other educational postings. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to disable journaling rollback?
Phil Sherrod wrote: I have an application that requires creating a database with about 50 million records. Is there any way to turn off journaling and rollback during the creation? I am willing to sacrifice reliability for speed in this situation. Do you know that the performance without doing anything special is unacceptable? Can you do trial runs with increasing fractions of the data to see? You might be pleasantly surprised. Just be sure to have large numbers of insertions in each transaction. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the minutes number only
Joanne Pham wrote: I would like to return only the number of minutes as below The strftime function is your friend: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Odd problem with select on large table
[EMAIL PROTECTED] wrote: What's driving me mad is that when I do a select from the command line like so: sqlite3 test.db `select name from PerfTest1 where name = key5000' 0 rows are returned. However if I do a simple: sqlite3 test.db 'select name from PerfTest1' and just let it go it prints all 1 rows!! Could it be that the data in the name column have a blank before or after the key5000? SQLite does not automatically trim its data. HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] tclsqlite
dick128 wrote: I have a tcl app that I want to use tclsqlite with but when I load the dll tcl exits without any error messages. did catch, etc. still exits. tries 3.5.4 and 3.5.6 - same result. What version of Tcl? I have used all of the recent tclsqlite3.dll files with almost all of the recent Active State releases of Tcl (both 8.4 and 8.5) with no problems. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
