Re: [sqlite] WITHOUT ROWID option
To improve efficiency you could add where 1=2 to avoid returning any
rows. Should just check validity.
On 5/7/2014 8:19 AM, Stephan Beal wrote:
On Wed, May 7, 2014 at 4:57 PM, Simon Slavin slav...@bigfraud.org wrote:
somehow ? Perhaps the ROWID field of a table might have its own
particular indication, and if you don't see any rows marked like that you
could deduce that the table had no ROWID column. I'm sure there are better
ways the
This isn't efficient, but it should work without corner cases: (pseudocode):
function hasRowId(tablename) {
prepare SELECT 1 FROM tablename; // if this fails, tablename likely does
not exist. else...
prepare SELECT rowid FROM tablename; // if this fails, rowid missing
return true only if the second PREPARE succeeds.
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] inner vs. outer join inconsistency
I believe a check constraint with an appropriate typeof comparison has been suggested for this usage. On 3/6/2013 6:29 AM, Ryan Johnson wrote: I would agree that no warning is needed for for columns that don't state any affinity, or for a non-affinity FK that refers to some PK with affinity. I tend to agree with OP that an explicitly text foreign key referring to an explicitly int primary key is probably worth a warning (perhaps from the hypothetical lint mode that surfaces now and then), since it's pretty likely that a user who took the trouble to specify affinities for both PK and FK probably made a mistake if the types are different. Sure, some record might override affinity and store 'abc' as its int primary key, but even if your app relies on that behavior, an int foreign key would be harmless for the same reason. Off topic, I'd love a way to request strong typing for a column (so that attempts to store 'abc' into an int column would fail). You can emulate it with a pair of before/update triggers (select raise(...) where typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect, most of the times I've been bitten by type mismatches were probably either due to this bug or (more likely) due to my not specifying any affinity at all and then being surprised when 1 != '1'. Ryan On 06/03/2013 7:23 AM, Stephen Chrzanowski wrote: SQLite doesn't care what kind of data type you are using, so, no, it shouldn't throw an error. The logic of this database engine is that you will always be comparing apples to apples, regardless if one happens to be orange. On Wed, Mar 6, 2013 at 4:50 AM, Tom Matrix ratomat...@gmail.com wrote: Richard Hipp drh@... writes: On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix ratomatrix@... wrote: I’ve encountered a problem, which is hardly reproducable on arbitrary databases, therefore I attached one. A simple, reproducible test case for (what we think is) your problem can be seen in this ticket: www.sqlite.org/src/tktview/fc7bd6358f59b This bug has been latent in SQLite for almost four years and you are the first to hit it. Probably this is because not many applications contain A=B in the WHERE clause where A is a text expression and B is an integer expression. You can probably work around the problem by changing your schema so that entries.measurementid is an integer rather than text. This does not excuse SQLite: It is still getting the wrong answer and needs to be fixed. We are working on a fix now. But a simple change to your schema will work around the problem and get you going even before that fix is available. Very good example, thank you! It really solved my problem. Nevertheless, I think I don't have to mention that entries.measurementid must have been an integer, and this bug must be fixed in our schema, too. My only remaining concern is, however: Should not SQLite give an error (or at least a warning) in cases where a foreign key constraint refers to a different data type? Thanks again, Tamás ___ 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-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] JDBC Drivers for SQLite?
http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers Check out Google for more. On 12/12/2012 8:54 AM, Tilsley, Jerry M. wrote: All, Might be a silly question, but does anyone know if any JDBC drivers exist for SQLite? Thanks, Jerry Disclaimer This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of St. Claire Regional Medical Center. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of the email is strictly prohibited. If you received this email in error please notify the St. Claire Regional Helpdesk by telephone at 606-783-6565. ___ 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] Ordering of fields in a join
Adding the warning to the explain plan output should work well. ... And yet the coding mistake in the SQL query was very subtle. It makes me wonder if we shouldn't somehow come up with a warning mechanism in SQLite to give developers a heads-up on error-prone constructs, such as using == between two columns with different default collating sequences... Pavel ___ 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] classic update join question
This analysis is a good candidate for inclusion in a FAQ or similar
document.
On 9/5/2012 7:28 PM, Keith Medcalf wrote:
sqlite create table alpha (frequency, term);
sqlite create table beta (term, frequency);
sqlite create index betaterm on beta(term);
sqlite .explain
sqlite explain query plan update alpha set frequency = (select frequency from
beta where beta.term = alpha.term);
sele order from deta
-
0 0 0 SCAN TABLE alpha (~100 rows)
0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 0
0 0 0 SEARCH TABLE beta USING INDEX betaterm (term?)
(~25 rows)
sqlite explain update alpha set frequency = (select frequency from beta where
beta.term = alpha.term);
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Trace 0 0 000
1 Goto 0 38000
2 Null 0 1 200
3 OpenRead 0 2 0 2 00
4 Rewind 0 8 000
5 Rowid 0 2 000
6 RowSetAdd 1 2 000
7 Next 0 5 001
8 Close 0 0 000
9 OpenWrite 0 2 0 2 00
10RowSetRead 1 36200
11NotExists 0 10200
12Null 0 3 400
13Null 0 5 000
14Integer1 6 000
15OpenRead 1 3 0 2 00
16OpenRead 2 4 0 keyinfo(1,BINARY) 00
17Column 0 1 700
18IsNull 7 28000
19SeekGe 2 287 1 00
20Column 2 0 800
21IsNull 8 27000
22IdxRowid 2 8 000
23Seek 1 8 000
24Column 1 1 900
25Move 9 5 100
26IfZero 6 28-1 00
27Next 2 20000
28Close 1 0 000
29Close 2 0 000
30SCopy 5 3 000
31Column 0 1 400
32NotExists 0 33200
33MakeRecord 3 2 8 bb 00
34Insert 0 8 2 alpha 05
35Goto 0 10000
36Close 0 0 000
37Halt 0 0 000
38Transaction0 1 000
39VerifyCookie 0 3 000
40TableLock 0 2 1 alpha 00
41TableLock 0 3 0 beta 00
42Goto 0 2 000
sqlite
sqlite explain query plan select alpha.term, beta.frequency from alpha, beta
where beta.term = alpha.term;
SELECT item[0] = {0:1}
item[1] = {1:1}
FROM {0,*} = alpha
{1,*} = beta
WHERE GE({1:0},{0:1})
END
sele order from deta
-
0 0 0 SCAN TABLE alpha (~100 rows)
0 1 1 SEARCH TABLE beta USING INDEX betaterm (term?)
(~25 rows)
sqlite explain select alpha.term, beta.frequency from alpha, beta where beta.term
= alpha.term;
SELECT item[0] = {0:1}
item[1] = {1:1}
FROM {0,*} = alpha
{1,*} = beta
WHERE GE({1:0},{0:1})
END
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Trace 0 0 000
1 Goto 0 22000
2 OpenRead 0 2 0 2 00
3 OpenRead 1 3 0 2 00
4 OpenRead 2 4 0 keyinfo(1,BINARY) 00
5 Rewind 0 18000
6 Column 0 1 100
7 IsNull 1 17000
8 SeekGe 2 171 1 00
9 Column 2 0 200
10IsNull 2 16000
11IdxRowid 2 2 000
12Seek 1 2 000
13Column 0 1 300
14Column 1 1 4
Re: [sqlite] SQLite4 (don't scream)
Are there significant improvements in speed for existing SQL? How does the compiled size compare with SQLite3? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble importing hex encoded blob
We added blob import on an old version of the shell, 3.5.9. Using a
simple HexToByte function.
To function: static int do_meta_command(char *zLine, struct
callback_data *p){
Added:unsigned char * blobBuffer = NULL;
In the loop // * Bind cached values to prepared statement. *
we added
else if( nColType[i] == SQLITE_BLOB )
{
textLen = strlen(azCol[i]);
// Convert from Hex to Binary.
blobLen = HexToByte( blobBuffer,azCol[i],
textLen );
// Have sqlite make an internal copy since we may
have multiple blobs...
rc = sqlite3_bind_blob(pStmt, i+1, blobBuffer,
blobLen, SQLITE_TRANSIENT);
}
with a free(blobBuffer); done for each row after the reset.
On 5/11/2012 10:22 PM, Joshua Shanks wrote:
I peeked at the source code real quick and it looks like it just
converts the contents of the file into a bunch of SQL that is
essentially opening a transaction and doing an insert for each row
followed by a commit. This suggest I just need to format it
differently so I'll play around with that tomorrow and report back on
if I make an progress. It looks to be using prepared statements
instead of straight inserts which is what i tried to convert out of.
On Fri, May 11, 2012 at 7:22 PM, Richard Hippd...@sqlite.org wrote:
On Fri, May 11, 2012 at 10:13 PM, Joshua Shanksjjsha...@gmail.com wrote:
I set the separator to tab and then in the file it is
X'somevalue'\tX'someothervalue'\n
X'morestuff'\tX'evenmore'\n
but with real hex values
According to the documentation
That document you quote is describing the SQL langauge, not CSV. There is
no way to enter BLOBs using CSV, that I know of.
The .import command operatos on CSV, not SQL.
BLOB literals are string literals containing hexadecimal data and
preceded by a single x or X character. For example:
X'53514C697465'
On Fri, May 11, 2012 at 6:16 PM, Simon Slavinslav...@bigfraud.org
wrote:
On 12 May 2012, at 2:01am, Joshua Shanksjjsha...@gmail.com wrote:
But when I try to use the .import method the values get imported as
the string X' instead of the hex blob value and don't get pulled
out correctly.
.import is for .csv files. What are you putting in the .csv file to
express a value in hex ? I don't think there's a way to do it.
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
--
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trouble importing hex encoded blob
Joshua,
It doesn't exist in the standard version. We added it in-house to aid
development and testing. The code I posted was the changes we made to
the 3.5.9 shell.c in addition to adding an existing hex to byte function
from our libraries.
If you can compile a new shell the existing shell, haven't checked the
shell code recently, is probably still similar enough to use a similar
style, if desired.
The provided code is just a hint as to what might work for you. We
never had time to attempt to push the code back to the standard shell.
The sqlite environment suggested was for Linux and since I'm using
Windows didn't have time to set up an environment.
Jim
On 5/14/2012 10:06 AM, Joshua Shanks wrote:
Hey Jim,
I downloaded the source or 3.7.12 from sqlite.org and can't find that code.
$ ls
shell.c sqlite3.c sqlite3ext.h sqlite3.h
$ head -n3 sqlite3.c
/**
** This file is an amalgamation of many separate C source files from SQLite
** version 3.7.12. By combining all the individual C code files into this
$ grep blobBuffer *
$
On Mon, May 14, 2012 at 8:24 AM, Jim Morrisjmor...@bearriver.com wrote:
We added blob import on an old version of the shell, 3.5.9. Using a simple
HexToByte function.
To function: static int do_meta_command(char *zLine, struct callback_data
*p){
Added:unsigned char * blobBuffer = NULL;
In the loop // * Bind cached values to prepared statement. *
we added
else if( nColType[i] == SQLITE_BLOB )
{
textLen = strlen(azCol[i]);
// Convert from Hex to Binary.
blobLen = HexToByte(blobBuffer,azCol[i], textLen );
// Have sqlite make an internal copy since we may have
multiple blobs...
rc = sqlite3_bind_blob(pStmt, i+1, blobBuffer, blobLen,
SQLITE_TRANSIENT);
}
with a free(blobBuffer); done for each row after the reset.
On 5/11/2012 10:22 PM, Joshua Shanks wrote:
I peeked at the source code real quick and it looks like it just
converts the contents of the file into a bunch of SQL that is
essentially opening a transaction and doing an insert for each row
followed by a commit. This suggest I just need to format it
differently so I'll play around with that tomorrow and report back on
if I make an progress. It looks to be using prepared statements
instead of straight inserts which is what i tried to convert out of.
On Fri, May 11, 2012 at 7:22 PM, Richard Hippd...@sqlite.orgwrote:
On Fri, May 11, 2012 at 10:13 PM, Joshua Shanksjjsha...@gmail.com
wrote:
I set the separator to tab and then in the file it is
X'somevalue'\tX'someothervalue'\n
X'morestuff'\tX'evenmore'\n
but with real hex values
According to the documentation
That document you quote is describing the SQL langauge, not CSV. There
is
no way to enter BLOBs using CSV, that I know of.
The .import command operatos on CSV, not SQL.
BLOB literals are string literals containing hexadecimal data and
preceded by a single x or X character. For example:
X'53514C697465'
On Fri, May 11, 2012 at 6:16 PM, Simon Slavinslav...@bigfraud.org
wrote:
On 12 May 2012, at 2:01am, Joshua Shanksjjsha...@gmail.comwrote:
But when I try to use the .import method the values get imported as
the string X' instead of the hex blob value and don't get pulled
out correctly.
.import is for .csv files. What are you putting in the .csv file to
express a value in hex ? I don't think there's a way to do it.
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
--
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
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Data Import Techniques
If you are not wrapping the inserts in an explicit transaction, try that. On 5/2/2012 9:04 AM, Nigel Verity wrote: Hi I am writing an application which requires approximately 50,000 items to be imported from a text file into a table. Each item is a single string of 8 characters, and the target table has an auto-incrementing PK and one other field, to hold the 8 character string. Using the Import Table Data function in SQLiteman, the data loads very quickly. However in my application, using either an SQL insert command or a resultset, the import is very much slower. Is there another technique I can use to speed things up? Thanks Nige ___ 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] Re Query planner creating a slow plan
It is possible using an alias would force better behavior: selsect theDate from (select transfer_date as theDate from transfer_history where regn_no='039540' and transfer_date= '2012-05-01') order by theDate ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5
It will definitely run. Pelles is new to me. Our application uses SQLite 3.5.9 on WinCE 4.2 through 6.5 using C++. I don't recall that we had to change any source, but we did modify the shell for better input handling, but there were probably some compiler flags we needed to set. Been years since I had to touch it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5
Don't know about those devices but we use the MC55A and related device without issue with C++ and I'm pretty sure they support C#. http://www.motorola.com/Business/US-EN/Business+Product+and+Services/Mobile+Computers/Handheld+Computers/MC55A0 On 2/9/2012 9:24 AM, Richard Hipp wrote: On Thu, Feb 9, 2012 at 12:21 PM, Noah Hartn...@lipmantpa.com wrote: The C#-SQLite port at http://code.google.com/p/csharp-sqlite/ supports both Silverlight and Windows Phone Tim tells me that he needs it to run on a handheld barcode scanner ( http://www.motorola.com/Business/US-EN/Business+Product+and+Services/Bar+Code+Scanning). Do they run C#? I dunno - I'm asking? Hope that help, Noah Hart Tim Leland wrote: Does anyone have any tips/suggestions for getting sqlite3 to run on windows mobile 6.5? Thanks Tim Leland W. Lee Flowers Co. 127 E. W Lee Flowers Rd. Scranton, S.C. 29591 (843)389-2731 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/Compiling-SQLite3-to-run-on-Windows-Mobile-6.5-tp33294689p33294909.html Sent from the SQLite mailing list archive at Nabble.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] Compiling SQLite3 to run on Windows Mobile 6.5
Sorry, no. SQLite is embedded in our application and requires authentication and sync to server before you can get off the login page. On 2/9/2012 9:33 AM, Tim Leland wrote: Is the sqlite part separate or emended in the application? Is it possible to just send me the .exe and test it on the scan gun? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Morris Sent: Thursday, February 09, 2012 12:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5 It will definitely run. Pelles is new to me. Our application uses SQLite 3.5.9 on WinCE 4.2 through 6.5 using C++. I don't recall that we had to change any source, but we did modify the shell for better input handling, but there were probably some compiler flags we needed to set. Been years since I had to touch it. ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General question on sqlite3_prepare, the bind and resets of prepared statements
Yes, works great! On 11/11/2011 4:24 PM, Matt Young wrote: Embedded Sqlite3 questions: I want to load and prepare multiple statements, keep them prepared and when I want to use one of them, I will reset, bind and step. Can pre-prepare multiple independent statements, then run them one at a time at random? Thanks, this may be a newbie question for embeded sqlite3 ___ 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] ensuring uniqueness of tuples spanning across multipletables?
On 10/6/2011 10:43 PM, Ivan Shmakov wrote: Jim Morris writes: The recent thread may relate: [sqlite] Is there an efficient way to insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table? INSERT INTO fts3_table (a,b,c) SELECT 'an A','a B','a C' WHERE NOT EXISTS (SELECT DISTINCT a,b,c FROM fts3_table WHERE a='an A' AND b='a B' AND c='a C'); The above SQL could be adapted to your schema. As mentioned, the performance will be slower than a straight insert. Thanks. It's a solution not quite for the problem I'm having, but I'll probably stick to it (and to the denormalized schema it imples.) However, I wonder, would the following (slightly more concise) query imply any performance loss in comparison to the one above? INSERT INTO fts3_table (a, b, c) SELECT 'an A', 'a B', 'a C' EXCEPT SELECT DISTINCT a, b, c FROM fts3_table; Also, I'm curious if DISTINCT may cause any performance loss in the case that the columns in question are constrained by an UNIQUE index? Like: CREATE UNIQUE INDEX foo-unique ON foo (a, b, c); I don't know for sure. You would need to do some testing to determine performance issues. I wouldn't use distinct if the values are already guaranteed to be unique or in a not exits clause since it may have additional overhead. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ensuring uniqueness of tuples spanning across multipletables?
The recent thread may relate: [sqlite] Is there an efficient way to insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table? INSERT INTO fts3_table (a,b,c) SELECT 'an A','a B','a C' WHERE NOT EXISTS (SELECT DISTINCT a,b,c FROM fts3_table WHERE a='an A' AND b='a B' AND c='a C'); The above SQL could be adapted to your schema. As mentioned, the performance will be slower than a straight insert. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the grand total of count(*) in a select statement
This should do it. select region, count(*) from hosts group by region union all select 'Total, count(*) from hosts ; On 10/3/2011 11:49 AM, James Kang wrote: select region, count(*) from hosts group by region ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the grand total of count(*) in a select statement
That should be select 'Total', count(*) from hosts On 10/3/2011 11:52 AM, Jim Morris wrote: This should do it. select region, count(*) from hosts group by region union all select 'Total, count(*) from hosts ; On 10/3/2011 11:49 AM, James Kang wrote: select region, count(*) from hosts group by region ___ 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] LEFT JOIN optimization
Your where clause WHERE ItemsME.IDR ... is only satisfied if there is an associated ItemsME record so the left outer join is pointless. Just use the inner join. Normally the left outer join would include all of ItemsME_Properties, that probably explains the table scan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PHP, SQLite3 object API, SQLite3::escapeString
I'd guess it was for escaping strings used to build SQL statements by concatenation rather than using prepared statements and binding. On 8/18/2011 7:12 AM, Simon Slavin wrote: ... The SQLite3 object API for PHP includes a function SQLite3::escapeString . The documentation for it doesn't explain what it's of or when to use it. ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] aggregate by break in sequence
You can't replace multiple rows in a single insert/update/delete statement. You might consider copying the duplicates to a temp table, delete them from the old then use a select on the temp table to generate the new rows for the old table. The select portion would be something like select longitude, distance, SUM(IVSUM)/SUM(IVCount), SUM(IVSUM), SUM(IVCount) FROM temp GROUP By longitude, Distance. On 8/16/2011 9:05 AM, Anantha Prasad wrote: Wanted to know if Sqlite can do simple math when there is a break in sequence in the data. For example the foll. table is sorted by Longitude and then Distance: Id Longitude Distance IVmean IVsum IVcount 42 71.0 10 10.5000 221 43 71.0 10 29.4286 28 824 44 71.0 20 9.467762 587 45 71.0 20 11.6667 1441680 46 71.0 30 3.553247 167 47 71.0 40 4.573249 67 I want to compute replace the IVmean for each repeated Distance by IVsum/IVcount - for repeated Distance - for example, Record 42 and 44 should be replaced by 71.0 10 (2+28)/(21+824) Record 44 and 45 should be replaced by 71.0 20 (62+144)/(587+1680) Is this possible to do this in Sqlite. Thanks much. Pras ___ 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] override table?
A union variation that is more amenable to generic selects: select a,b,c,1 as tablename from real where id=123 and not exists (Select 1 from over where over.id = real.id) union select a,b,c,2 as tablename from over where id=123 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite: commands not persisting on db.
On 7/15/2011 1:26 AM, Mattia wrote: - deleting the old database (data.db) and renaming new.db with the correct name (new.db becomes data.db). After this step, as a test, reopen the connection to new.db and ensure that the data is there. Are you sure a commit is done before closing connection? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
The between operator is order dependent. This variation might work: SELECT d from T_d inner join (select min(pos) as xMin, max(pos) as yMax FROM T_x WHERE txt = '1990' OR txt='1991') as xcriteria on xPos between xMin and xMax inner join (select min(pos) as yMin, max(pos) as yMax FROM T_y WHERE txt = 'cogs' OR txt='sga expenses') as ycriteria on yPos between yMin and yMax ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR UPDATE?
Or do an update and if no records are modified then do an insert. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
string literals are enclose in single quotes not double quotes select (select v from t1 where n='a') wrong,* from a1; On 6/28/2011 11:42 AM, thilo wrote: select (select v from t1 where n=a) wrong,* from a1; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query with UNION on large table
Did you try to time a simpler select: SELECT min(n1, n2) as new1, max(n1,n2) as new2 FROM table1 group by new1, new2 having count(*) 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE run slow
You might also consider a trigger to calculate the distance once on insert/update. They you could use an index. On 6/14/2011 4:53 AM, Simon Slavin wrote: On 14 Jun 2011, at 12:11pm, Martin.Engelschalk wrote: make sure you have an index on category and distance, like create index MyIndex on Location (category, distance) Peter is calculating distance inside his SELECT, but I agree that an index on (category) is an excellent idea. The other thing is that this is a bit like RTREEs. So Peter, you might like to read http://www.sqlite.org/rtree.html I don't know if it's worth using rtrees in your particular example but you might find them useful. 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
Re: [sqlite] HELP: SQLException getErrorCode vs gerErrorMessage()
I've only used the xerial driver recently and haven't checked for the error code on exception. It does come with source, so you may be able to walk into the code to see how the value is set/retrieved. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create DB file and then Create Table - Table FAILS.
This line seems erroneous: SQLiteCommand sqlCmd(%conn); Isn't the percent the modulus operator? Shouldn't it be: SQLiteCommand sqlCmd(conn); On 6/1/2011 5:06 PM, Don Ireland wrote: I'm hoping someone can help me with this. Using Visual Studio C++, the following code DOES create the DB file. But the table doesn't get created and I'm stumped as to why it won't create the table. SQLiteConnection conn; conn.ConnectionString = Data Source=D:\Users\Don\Downloads\CashBoxPrefs.dat; conn.Open(); SQLiteCommand sqlCmd(%conn); sqlCmd.CommandText = CREATE TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 TEXT);; But if I run following at the SQLite3.exe from the command line, it DOES create the file and the table. sqlite3 Cashboxprefs.dat sqlite CREATE TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 TEXT); ___ 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] Better way to get records by IDs
If you just need them in descending order, i.e. not an arbitrary order, then order by rec desc will work. On 5/20/2011 7:23 AM, jose isaias cabrera wrote: Martin Engelschalk on Friday, May 20, 2011 10:21 AM wrote... Hi, to order, you have to use order by. In that case, however, it gets complicated. SELECT * FROM Jobs WHERE rec IN (87, 33, 27,2, 1) order by case rec when 87 then 1 when 33 then 2 when 37 then 3 when 2 then 4 when 1 then 5 end; Martin Thanks, this will work. Am 20.05.2011 15:55, schrieb jose isaias cabrera: Oliver Peters on Friday, May 20, 2011 9:47 AM wrote... jose isaias cabreracabrera@... writes: Greetings. I would like to get a bunch of records of IDs that I already know. For example, this table called Jobs, rec,...,data,... 1,...,aaa,... 2,...,zzz,... ... ... 99,...,azz,... [...] What about SELECT * FROM table WHERE id BETWEEN 1 AND 99; greetings Oliver I presented a bad example, Oliver. My apologies. I want specific IDs, so the WHERE rec IN (1,2,27,33,87) works perfectly. However, I have one last question: if I do this call, SELECT * FROM Jobs WHERE rec IN (87, 33, 27,2, 1) the result is 1, 2, 27, 33, 87. How can I get that specific order? thanks, josé ___ 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-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] Query efficiency
You must use the alias if specified: select ar.* from aa ar, ab ab1, ab ab2; rather than select aa.* from aa ar, ab ab1, ab ab2; On 5/19/2011 10:33 AM, Matthew Jones wrote: select aa.* from aa ar, ab ab1, ab ab2; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite3_prepare_v2?
Yes, transaction are designed to work with multiple statements. Begin Statement 1 Statement 2 ... Statement N Commit On 5/17/2011 7:58 AM, StyveA wrote: Hello again, I've got an other question about prepare : Is it possible to make a BEGIN transaction, then prepare many statements (insert, update, delete) and then COMMIT all at once in the right order? Or should I each time make a BEGIN, prepare, COMMIT? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best JDBC driver for SQLite?
I've just started using Xerial also for a small project and have not had any problems so far. I haven't used any other JDBC wrappers so have no comparison info. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3.DLL 3.7.6 memory leaks
Are you sure these leaks aren't yours? Although I don't know the Sqlite
internals some of the data values don't seem to be related to Sqlite, like:
c:/DEV/Platform/
PolicyDataPack.i
You can try to use the allocation number to narrow down the code
location. Don't remember the specifics though.
On 4/18/2011 6:03 AM, Khanh Nguyen wrote:
Hi,
My name is Khanh Nguyen and currently using SQLite3.DLL 3.7.6 in my
application (DLL built with VS2008 C++ with this flag: Multi-threaded
Debug DLL (/MDd).
The DLL has some memory leaks that I have captured here:
The thread 'Win32 Thread' (0x368) has exited with code 0 (0x0).
Detected memory leaks!
Dumping objects -
{8390} normal block at 0x01364C70, 64 bytes long.
Data:c:/DEV/Platform/ 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
2F
{8382} normal block at 0x0138C9C0, 32 bytes long.
Data:7Zl7ji/F9x+bOgbG 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62
47
{8380} normal block at 0x0138B938, 32 bytes long.
Data:PolicyDataPack.i 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
69
{8377} normal block at 0x01391F48, 448 bytes long.
Data: 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
CD
{7174} normal block at 0x01391DC8, 64 bytes long.
Data:c:/DEV/Platform/ 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
2F
{7166} normal block at 0x013621D8, 32 bytes long.
Data:7Zl7ji/F9x+bOgbG 37 5A 6C 37 6A 69 2F 46 39 78 2B 62 4F 67 62
47
{7164} normal block at 0x01386690, 32 bytes long.
Data:PolicyDataPack.i 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
69
{7161} normal block at 0x01390430, 448 bytes long.
Data: 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
CD
{7108} normal block at 0x0138CDC8, 64 bytes long.
Data:c:/DEV/Platform/ 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
2F
{7100} normal block at 0x01386320, 32 bytes long.
Data:ENt5KkYHRqeU/iLR 45 4E 74 35 4B 6B 59 48 52 71 65 55 2F 69 4C
52
{7098} normal block at 0x0138BCD8, 32 bytes long.
Data:PolicyDataPack.b 50 6F 6C 69 63 79 44 61 74 61 50 61 63 6B 2E
62
{7095} normal block at 0x0138D530, 448 bytes long.
Data: 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
CD
{7033} normal block at 0x013796D8, 64 bytes long.
Data:c:/DEV/Platform/ 63 3A 2F 44 45 56 2F 50 6C 61 74 66 6F 72 6D
2F
{7025} normal block at 0x0136DED0, 32 bytes long.
Data:5U/jyx2txHeUQUe/ 35 55 2F 6A 79 78 32 74 78 48 65 55 51 55 65
2F
{7023} normal block at 0x013658D0, 32 bytes long.
Data:NetworkSetting3. 4E 65 74 77 6F 72 6B 53 65 74 74 69 6E 67 33
2E
{7020} normal block at 0x01389BF0, 448 bytes long.
Data: 10 1F F3 02 00 00 00 00 00 00 00 00 CD CD CD
CD
Object dump complete.
The program '[5432] DebugConsole.exe: Native' has exited with code 0
(0x0).
Please help me overcome this memory leak issue.
Thanks and Kind Regards,
Khanh
___
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 Use an Apostrophe in a Text Field?
Did you try doubling the apostrophes? *Goin'' Down the Road Feelin'' Bad* On 4/17/2011 6:16 PM, Simon Slavin wrote: On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote: I'm using SQLite with VBE2008. I've defined a table with a number of text fields in it. If the information I want to write to the database contains an embedded apostrophe, the program throws an error. That is, if I set textfield1 to *Going Down the Road Feeling Bad*, the data gets written correctly and the program continues. But if I set textfield1 to *Goin' Down the Road Feelin' Bad*, I get an error. Is there a way I can use an apostrophe in the data to be written? Your library might do it for you. If you're writing directly to the SQLite library then I believe you can double the apostrophe: Goin'' Down the Road Feelin'' Bad so it might be worth trying that. 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
Re: [sqlite] GROUP BY Problem
Did you want to use correlated sub queries? Something like: SELECT c1,c2, (select sum(t2.c3) FROM t2 WHERE t2.key2=t1.key1) as mySum, (select count(t3.c4) FROM t3 where t3.key3=t1.key1) as myCount FROM t1 ; On 4/7/2011 5:31 PM, Pete wrote: I am trying to use GROUP BY to summarise information from a main table and two sub tables, e.g.: SELECT c1,c2,sum(t2.c3),count(t3.c4) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1 LEFT JOIN t3.key3=t1.key1 GROUP BY t1.key1 The result is that the count column returns the count of (the number of t2 entries * the number of t3 entries), and the sum column returns (the t2 sum value * the count of entries in t3). For example if the sum of t2.c3 is actually 1000 (from 3 rows) and the count of t3.c4 is 5, the sum column returns 5000 and the count column returns 15. If either of t2 or t3 has no qualifying entries, the calculation for the other table is correct. I guess GROUP BY isn't designed to deal with this type of situation. Can anyone suggest a way to do this? Thanks, Pete ___ 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 do this query?
A simple restatement should work: delete from xxx where entry_id in (select t1.entry_id from xxx t1 where not t1.entry_id in(select t2.entry_id from xxx t2 where t1.patient_id = t2.patient_id order by t2.start_date desc limit 1)) On 3/24/2011 12:00 PM, Bart Smissaert wrote: delete from xxx t1 where not t1.entry_id in(select t2.entry_id from xxx t2 where t1.patient_id = t2.patient_id order by t2.start_date desc limit 1) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import FILE TABLE
Make sure your separator is the , http://www.sqlite.org/sqlite.html On 3/10/2011 12: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 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
Re: [sqlite] .import FILE TABLE
.separator , .import myPath/myCSVfile myTable We use a import file with these commands. On 3/10/2011 2:32 PM, jcilibe...@comcast.net wrote: Thanks, but doesn't seem to work: I used command - Original Message - From: Gerry Snydermesmerizer...@gmail.com To: General Discussion of SQLite Databasesqlite-users@sqlite.org Sent: Thursday, March 10, 2011 12:39:33 PM Subject: Re: [sqlite] .import FILE TABLE On 3/10/2011 1:28 PM, jcilibe...@comcast.net wrote: Hello, thanks but adding separator , doesn't seem to work. I tried the following commands: .import separator , myPath/myCSVfile myTable . import separator , myPath/myCSVfile myTable . import myPath/myCSVfile myTable separator , . import myPath/myCSVfile myTable separator , None worked: any ideas? Examination of the myCSVfile.txt shows [1, Jack, Sammamish] 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-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] Help with join
A correlated sub-query might work for you. SELECT [Analyzers].[AnalyzerID] , [Analyzers].[Name] AS [Analyzer] , [Analysis].[AnalysisID] , [Analysis].[ScanID] , [Analysis].[Timestamp] , [Analysis].[EndTime] , (SELECT COUNT(*) AS NumDefects FROM Defects d where d.AnalysisID = Analysis.AnalysisID) as NumDefects, , [Analysis].[Result] FROM [Analysis] JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID] ORDER BY [Analysis].[Timestamp]; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance Problem
On the MC55 and MC70 we use with Sqlite 3.5.9:
PRAGMA temp_store = MEMORY
PRAGMA journal_mode = PERSIST
PRAGMA journal_size_limit = 50
On 2/16/2011 5:24 AM, Black, Michael (IS) wrote:
Try this benchmark program and see what numbers you get. You need to compare
to other machines with the same benchmark to see if it's the machine or your
programming/architecture.
The MC55 is a 520Mhz PXA270 so I would expect to see more than a 6X
difference from my 3Ghz box (memory speed is no doubt slower too).
batch 1 10 0
Sqlite Version: 3.7.5
Inserting 1 rows using a bulk of 10
commits per second: 14217.7
batch 1 10 4
using wal mode
Sqlite Version: 3.7.5
Inserting 1 rows using a bulk of 10
commits per second: 44952.5
batch 1 10 6
using index on t(i)
using wal mode
Sqlite Version: 3.7.5
Inserting 1 rows using a bulk of 10
commits per second: 42383.5
batch 1 10 7
using mode: :memory:
using index on t(i)
using wal mode
Sqlite Version: 3.7.5
Inserting 1 rows using a bulk of 10
commits per second: 219279.0
#includestdlib.h
#includestdio.h
#includestring.h
#ifdef _WIN32
#includewindows.h
#includewinbase.h
#includesys/timeb.h
#else
#includesys/time.h
#endif
#includetime.h
#include sqlite3.h
double elapsed()
{
#ifdef _WIN32X
struct _timeb timebuffer;
_ftime(timebuffer );
return( (double)timebuffer.time + timebuffer.millitm / 1000.0);
#else
double t1;
struct timeval tv;
static long base_seconds;
gettimeofday(tv,NULL);
if (base_seconds==0) base_seconds=tv.tv_sec;
return (tv.tv_sec-base_seconds)+tv.tv_usec/(double)100;
#endif
}
int AddTrigger(sqlite3 *db)
{
char SqlTxt[256];
int rc;
strcpy(SqlTxt,CREATE TRIGGER XX AFTER INSERT ON t FOR EACH ROW BEGIN );
strcat(SqlTxt,UPDATE t2 SET n = NEW.i+1;);
strcat(SqlTxt,END;);
rc = sqlite3_exec(db,SqlTxt,NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
return(0);
}
int main(int argc, char *argv[])
{
sqlite3 *db;
sqlite3_stmt *stmt=NULL;
int rc;
int n=0;
int nrec=0;
int interval=0;
int flags=0;
double t1;
char SqlTxt[256];
if (argc != 4) {
fprintf(stderr,Usage: %s nrecords commit_interval flags\n,argv[0]);
fprintf(stderr,Flag 0 = simple table\n);
fprintf(stderr,Flag 1 = in-memory database\n);
fprintf(stderr,Flag 2 = add index\n);
fprintf(stderr,Flag 4 = WAL mode\n);
fprintf(stderr,Flag 8 = Add an update trigger per insert\n);
fprintf(stderr,Flag 16= Add a manual update per insert\n);
fprintf(stderr,Flag 32 = Synchronous=Off\n);
fprintf(stderr,Add flags to combine features\n);
exit(-1);
}
nrec=atoi(argv[1]);
interval=atoi(argv[2]);
flags=atoi(argv[3]);
if (flags 1)
{
puts(using mode: :memory:);
rc=sqlite3_open(:memory:,db);
}
else {
remove(batch.db);
rc=sqlite3_open(batch.db,db);
}
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
rc=sqlite3_exec(db,create table t (i integer),NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
rc=sqlite3_exec(db,create table t2 (n integer),NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
rc=sqlite3_exec(db,insert into t2 values(0),NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
if (flags 32) {
rc=sqlite3_exec(db,pragma synchronous=OFF,NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
puts(using pragma synchronous=OFF);
}
if (flags 2) {
rc=sqlite3_exec(db,create index tx on t (i),NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
puts(using index on t(i));
}
if (flags 4) {
rc=sqlite3_exec(db,pragma journal_mode=WAL,NULL,NULL,NULL);
if (rc != SQLITE_OK) {
puts(sqlite3_errmsg(db));
}
puts(using wal mode);
}
if (flags 8) {
AddTrigger(db);
puts(using update trigger);
/** if (interval != 1) {
fprintf(stderr,Can't do trigger and begin/commit together\n);
exit(-1);
}
**/
}
if( flags 16 )
puts(using manual update after insert);
printf(Sqlite Version: %s\n, sqlite3_version);
printf(Inserting %d rows using a bulk of %d\n, nrec, interval);
sqlite3_prepare_v2(db,insert into t values(?),-1,stmt,NULL);
t1=elapsed();
if (interval != 1) rc=sqlite3_exec(db,begin,NULL,NULL,NULL);
while(n nrec)
{
++n;
if (interval != 1 (n% interval)==0) {
sqlite3_exec(db,commit,NULL,NULL,NULL);
sqlite3_exec(db,begin,NULL,NULL,NULL);
}
sqlite3_bind_int(stmt,1,n);
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
puts(sqlite3_errmsg(db));
}
sqlite3_reset(stmt);
if( flags 16 )
{
Re: [sqlite] completion of sql words
I tihnk the firefox plug-in Sqlite Manager does. On 2/11/2011 2:30 PM, Simon Slavin wrote: On 11 Feb 2011, at 7:19pm, prad wrote: does the sqlite3 interface have completion of sql key words? in postgresql you can type SEL and press tab to complete. is there such a thing for sqlite3? sqlite3 doesn't have an interface. It is only a programming API. Although you can download a command-line tool for sqlite3 from the sqlite3 web site, it's provided just for convenience and many people don't use it (or even know about it). 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
Re: [sqlite] Bi-directional unique
If you don't care about the order then use and instead of trigger to force the ordering then you will get the collisions you expect. On 2/9/2011 10:12 AM, Black, Michael (IS) wrote: I have a need to create a unique bi-directional relationship. You can think of it as pairings of people who eat dinner together. create table t(i int, j int); insert into t(1,2); insert into t(2,1); should give an error because the pairing of 1-2 already exists. insert into t(3,2); OK insert into t(3,1); OK insert into t(1,3); should be error You can't guarantee that one column is less than the other so there's no win there. Speed is of the utmost concern here so fast is really important (how many ways can I say that???). Is there anything clever here that can be done with indexes or such? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ___ 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] Disk I/O Error
Could a backup or virus scanning software be locking the database? On 2/8/2011 10:12 AM, Nathan Biggs wrote: I haven't tried that, but if we stop the application then restart it, everything works again. That is until we get another Disk I/O error which happens the next day. I'm wondering if the virus scanner is blocking the database write. On 2/8/2011 1:03 PM, Simon Slavin wrote: On 8 Feb 2011, at 5:59pm, Nathan Biggs wrote: Once a day, not at the same time, we are getting a disk I/O error from our application using SQLite. If you try to duplicate the database file do you get any kind of error then ? If not, use the command-line tool to run an integrity check: http://www.sqlite.org/pragma.html#pragma_integrity_check 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I query for a specific count of items?
Only apples SELECT distinct customerid FROM Customers c1 WHERE Type = 'Apple' AND not exists (select 1 from customers c2 where c2.customerid=c1.customerid and not Type = 'Apple') ; Apples and Bananas SELECT distinct customerid FROM Customers c1 WHERE Type = 'Apple' AND exists (select 1 from customers c2 where c2.customerid=c1.customerid and Type = 'Banana') ; On 2/3/2011 9:40 AM, Igor Tandetnik wrote: On 2/3/2011 12:26 PM, Puneet Kishor wrote: On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote: INSERT INTO Customers VALUES (NULL, 1239, 'Banana'); Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs. That's how you tell SQLite to generate IDs automatically. Your CustomerID seems like it should be unique, yet you have identical rows inserted. It's not declared unique, why do you think it should be? For example, what is the difference between the first and the second row? EntryID. #1) Query for customers who *ONLY* bought apples SELECT * FROM Customers WHERE Type = 'Apple'; That would also report customers that bought something else besides apples. #2) Query for customers who bought apples *AND* bananas SELECT * FROM Customers WHERE Type = 'Apple' OR Type = 'Banana'; That would report customers that only bought apples, as well as those that only bought bananas. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about database design
I'd probably move the analyze out of the loop. Since your joining on props.id a better index pind might be create index pind on props (id, pnam) The name of column id in table props would be clearer as obj_id since it is not the id of the property but the id of the record in the obj table. On 2/2/2011 3:23 PM, Jeff Rogers wrote: Andreas Kupries wrote: It seems to me that you are looking for http://en.wikipedia.org/wiki/Database_normalization SQLite seems to do quite poorly performance-wise with fully-normalized attribute tables like this, when you want to query against multiple attributes. My timing comparisons with postgres show sqlite to be as much as 10x-15x slower than pg. My timing code is at http://paste.tclers.tk/2346 This is a synthetic test, but I ran across the issue in a real application. I'm not sure what else I can do do optimize the queries; using a denormalized table is the only thing that seems to help. -J ___ 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] SQL query on sort order
If not already done creating a page of additional collations on the wiki would make sense and minimize work all around. I just mailed you an extension for SQLite offering the collation you need. ___ 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 ?
Did you try a compound index? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How Execute Joint Queries In Sqlite?
A union of a left and right joins should do it. On 12/7/2010 4:50 AM, Simon Slavin wrote: On 7 Dec 2010, at 12:37pm, Pavel Ivanov wrote: I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not Supported. What Should I Do To Do This You should re-think once more: do you really need a full outer join? Maybe you can change your schema so that it was more clear and didn't require full outer join for querying. Just a note that it is probably very easy to do this. You may even be able to phrase your SELECT with an INNER JOIN without changing the schema. Pavel is being helpful, not insulting you. 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
Re: [sqlite] Select fails even though data is in the table.
Why are you looking for a duplicate with col4 instead of the unique key,
col2, col3 that caused the collision?
On 12/1/2010 7:29 AM, Hemant Shah wrote:
Folks,
My C program creates a in-memory database. It creates a table and a unique
index on two columns. If the insert fails due to unique index, it prints old
row and new row. Sometimes it cannot find the old row even though the insert
failed.
Here is the pseudo code:
CreateStmt = “create table mytable (TimeStamp char[50], col2 [char 10], col3
int, col4 char[33]”;
sqlite3_exec(CreateStmt)
IndexStmt = “create unique index myidx (col3 asc, col4 asc)”;
sqlite3_exec(IndexStmt);
InsertStmt = “insert into mytable (TimeStamp, col2, col3, col4) values
(?,?,?,?)”;
sqlite3_prepare_v2(InsertStmt)
sqlite3_bind calls for each column
ReturnCode = sqlite3_step(InsertStmtHandle);
if (ReturnCode != SQLITE_DONE)
{
sqlite3_reset(InsertStmtHandle);
if (sqlite3_errcode(DbHandle) == SQLITE_CONSTRAINT)
{
/* duplicate row */
sqlite3_finalize(InsertStmtHandle);
sqlite3_exec(DbHandle, COMMIT, NULL, NULL, NULL);
SelectStmt = “select TimeStamp, col2, col3, col4 from mytable where
col4 = ?”;
sqlite3_prepare_v2(SelectStmt)
sqlite3_bind calls
ReturnCode = sqlite3_step(SelectStmtHandle);
if (ReturnCode != SQLITE_ROW)
{
sqlite3_reset(SelectStmtHandle);
printf(Row not found. ReturnCode: %d, Error Message:%s, Error
Code: %d\n, ReturnCode, sqlite3_errmsg(DbHandle), sqlite3_errcode(DbHandle));
}
else
{
/* print row */
}
}
else
{
/* other error
}
}
The program prints ReturnCode as 101 which is SQLITE_DONE and error messages
is “No error message”, errcode is 0.
If the insert statement failed because of duplicate row, then why did it not
find the old row?
This only happens for some of the rows.
I am inserting rows at very high rate (about every 50 microseconds) and only
keep one minute worth of data in the table.
How do I debug/fix this problem?
Thanks.
Hemant Shah
E-mail: hj...@yahoo.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] Select fails even though data is in the table.
If you have another thread running that deletes or modifies the table, then move the commit to after the select for duplicate to ensure transactional integrity. On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: The problem is probably in the bind calls that you are not showing. If you care to share them we may be able to help. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Hemant Shah Sent: Wed 12/1/2010 10:08 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Select fails even though data is in the table. The unique key is col3 and col4 (SeqNum and MD5Sum). If the insert fails for this unique key then col4 should be the same. It should find the row for the even if I select for col4 only. Hemant Shah E-mail: hj...@yahoo.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] Select fails even though data is in the table.
I still think you should use the same columns for searching for the duplicate that cause the collision. Using col4 seem problematic. Can you change the code to use col2 and col3? On 12/1/2010 8:24 AM, Hemant Shah wrote: This is a single thread/process. No other thread or process is accessing the data. This is a single process that reads data from message queue and dumps into database to look for duplicate rows. The problem occurs for some rows only (about 3 to 5 an hour). Hemant Shah E-mail: hj...@yahoo.com --- On Wed, 12/1/10, Jim Morrisjmor...@bearriver.com wrote: From: Jim Morrisjmor...@bearriver.com Subject: Re: [sqlite] Select fails even though data is in the table. To: sqlite-users@sqlite.org Date: Wednesday, December 1, 2010, 10:15 AM If you have another thread running that deletes or modifies the table, then move the commit to after the select for duplicate to ensure transactional integrity. On 12/1/2010 8:10 AM, Black, Michael (IS) wrote: The problem is probably in the bind calls that you are not showing. If you care to share them we may be able to help. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Hemant Shah Sent: Wed 12/1/2010 10:08 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Select fails even though data is in the table. The unique key is col3 and col4 (SeqNum and MD5Sum). If the insert fails for this unique key then col4 should be the same. It should find the row for the even if I select for col4 only. Hemant Shah E-mail: hj...@yahoo.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 ___ 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] joining two sequences?
Slightly better version: select distinct f1 .key as foo_key, b1.key as bar_key from bar b1 inner join foo f1 on f1 .value = b1.value where not exists ( -- Values for a particular key in foo select f3.value from foo f3 WHERE f3.key= f1.key union -- Values for a particular key in bar select b3.value from bar b3 WHERE b3.key = b1.key except -- Values common to both foo key and bar key select f2.value from foo f2 inner join bar b2 on b2.value = f2.value WHERE b2.key = b1.key AND f2.key= f1.key ); On 11/19/2010 6:40 PM, Jim Morris wrote: This should return a the equivalent keys in the two maps. The basic idea is to compare the values in each key in foo(left outer join foo) with the values for each key in bar where there are any matching values(left outer join bar) and only select those with a complete match( inner join). Not sure this is the most efficient way. select distinct f1 .key as foo_key, b1.key as bar_key from bar b1 inner join foo f1 on f1 .value = b1.value where not exists ( -- Values for a particular key in foo select f3.value from foo f3 left outer join bar b3 on b3.value= f3.value WHERE f3.key= f1.key union -- Values for a particular key in bar select f3.value from bar b3 left outer join foo f3 on b3.value= f3.value WHERE b3.key = b1.key except -- Values common to both foo key and bar key select f2.value from foo f2 inner join bar b2 on b2.value = f2.value WHERE b2.key = b1.key AND f2.key= f1.key ); On 11/19/2010 1:03 PM, Petite Abeille wrote: Hello, Given two tables describing sequences of key value pairs, what would be a reasonable way to join them? For example, assuming two table foo and bar with identical structure: create temporary table foo ( key integer not null, value text not null, constraint foo_pk primary key( key, value ) ); create temporary table bar ( key integer not null, value text not null, constraint bar_pk primary key( key, value ) ); And a set of sequences in each of the table: insert into foo values( 1, 'a' ); insert into foo values( 2, 'a' ); insert into foo values( 2, 'b' ); insert into foo values( 3, 'a' ); insert into foo values( 3, 'b' ); insert into foo values( 3, 'c' ); insert into bar values( 4, 'a' ); insert into bar values( 4, 'b' ); What would be a good way to join foo( 2, 'a', )( 2, 'b' ) to bar( 4, 'a', )( 4, 'b' )? In other words, join the sequences with the same values? Right now, I'm using group_concat to flatten the sequences: select * from( select key, group_concat( value ) as value fromfoo group bykey ) as foo join( select key, group_concat( value ) as value frombar group bykey ) as bar on bar.value = foo.value Which results in: key|value|key|value 2|a,b|4|a,b All good, if perhaps clunky. But the documentation for group_concat mention that the order of the concatenated elements is arbitrary [1]. Which perhaps would preclude group_concat from being reliably use as a join predicate, no? Could someone think of a nice alternative to group_concat to join such data structure? Thanks in advance. Cheers, PA. [1] http://www.sqlite.org/lang_aggfunc.html ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] joining two sequences?
This should return a the equivalent keys in the two maps. The basic idea is to compare the values in each key in foo(left outer join foo) with the values for each key in bar where there are any matching values(left outer join bar) and only select those with a complete match( inner join). Not sure this is the most efficient way. select distinct f1 .key as foo_key, b1.key as bar_key from bar b1 inner join foo f1 on f1 .value = b1.value where not exists ( -- Values for a particular key in foo select f3.value from foo f3 left outer join bar b3 on b3.value= f3.value WHERE f3.key= f1.key union -- Values for a particular key in bar select f3.value from bar b3 left outer join foo f3 on b3.value= f3.value WHERE b3.key = b1.key except -- Values common to both foo key and bar key select f2.value from foo f2 inner join bar b2 on b2.value = f2.value WHERE b2.key = b1.key AND f2.key= f1.key ); On 11/19/2010 1:03 PM, Petite Abeille wrote: Hello, Given two tables describing sequences of key value pairs, what would be a reasonable way to join them? For example, assuming two table foo and bar with identical structure: create temporary table foo ( key integer not null, value text not null, constraint foo_pk primary key( key, value ) ); create temporary table bar ( key integer not null, value text not null, constraint bar_pk primary key( key, value ) ); And a set of sequences in each of the table: insert into foo values( 1, 'a' ); insert into foo values( 2, 'a' ); insert into foo values( 2, 'b' ); insert into foo values( 3, 'a' ); insert into foo values( 3, 'b' ); insert into foo values( 3, 'c' ); insert into bar values( 4, 'a' ); insert into bar values( 4, 'b' ); What would be a good way to join foo( 2, 'a', )( 2, 'b' ) to bar( 4, 'a', )( 4, 'b' )? In other words, join the sequences with the same values? Right now, I'm using group_concat to flatten the sequences: select * from( select key, group_concat( value ) as value fromfoo group bykey ) as foo join( select key, group_concat( value ) as value frombar group bykey ) as bar on bar.value = foo.value Which results in: key|value|key|value 2|a,b|4|a,b All good, if perhaps clunky. But the documentation for group_concat mention that the order of the concatenated elements is arbitrary [1]. Which perhaps would preclude group_concat from being reliably use as a join predicate, no? Could someone think of a nice alternative to group_concat to join such data structure? Thanks in advance. Cheers, PA. [1] http://www.sqlite.org/lang_aggfunc.html ___ 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] GROUP BY driving me crazy
Can you better explain your intent? Why are you grouping? This is normally for creating sums, averages, counts etc. Do you have a small sample of input vs output desired? On 11/10/2010 11:11 AM, James wrote: I've been fighting with this for a couple days now. I've been searching like mad, and thought I found solutions, but nothing seems to work. I think I may have reached the limit of my understanding :) This is just a simplified example of what I'm going after: SELECT products.id, products.name, images.src FROM products INNER JOIN items ON items.product_id = products.id LEFT JOIN images ON images.item_id = items.id WHERE items.buyable = 1 GROUP BY products.id ORDER BY products.name, images.position DESC I've also tried things like: SELECT products.id, products.name, images.src FROM products INNER JOIN items ON items.product_id = products.id LEFT JOIN (SELECT * FROM images ORDER BY position DESC) images ON images.item_id = items.id WHERE items.buyable = 1 GROUP BY products.id ORDER BY products.name and... SELECT products.id, products.name, (SELECT images.src FROM images WHERE images.item_id = items.id ORDER BY images.position DESC LIMIT 0,1) FROM products INNER JOIN items ON items.product_id = products.id WHERE items.buyable = 1 GROUP BY products.id ORDER BY products.name Without the GROUP BY, the ordering is correct. I found this example to order for GROUP_CONCAT, but I don't understand how I could translate it for my problem: SELECT ID, GROUP_CONCAT(Val) FROM ( SELECT ID, Val FROM YourTable ORDER BY ID, Val ); Is this a fairly simple problem and solution? What would you search for to find solutions to this? I'm having a heck of a time. ___ 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] GROUP BY driving me crazy
There is no logic way to show you intended result. You need some sort of data that can be used as a filter. If you want to filter by color why not add color to the item and use a WHERE clause? Or maybe style? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY driving me crazy
If you would explain why/how the position value is significant that
might help.
I fixed your pseudo SQL to run in SQLite Manager and I don't understand
from the
sample data what your trying to do. There is only one image per item.
Do you have multiple images per item and only want to return the first?
A query like the following seem to yield reasonable results:
SELECT products.name, items.id,images.filename, images.position
FROM products
INNER JOIN items ON items.product_id = products.id
LEFT JOIN images ON images.item_id = items.id
WHERE items.name='White'
ORDER BY products.name ASC, images.position ASC
;
The cleaned up code is:
CREATE TABLE products (id, category_id, name, description);
CREATE TABLE items (id, product_id, part_number, name, price, buyable);
CREATE TABLE images (id, item_id, filename, position);
INSERT INTO products (id, category_id, name ) VALUES (1, 1, 'SQLite
T-Shirt');
INSERT INTO products (id, category_id, name ) VALUES (2, 1, 'SQLite Long
Sleeved Shirt');
INSERT INTO items (id, product_id, name) VALUES ('SQLT-WHT', 1, 'White');
INSERT INTO items (id, product_id, name) VALUES ('SQLT-BLK', 1, 'Black');
INSERT INTO items (id, product_id, name) VALUES ('SQLL-WHT', 2, 'White');
INSERT INTO items (id, product_id, name) VALUES ('SQLL-BLK', 2, 'Black');
INSERT INTO items (id, product_id, name) VALUES ('SQLL-BLU', 2, 'Blue');
INSERT INTO images (item_id, filename, position) VALUES
('SQLT-WHT','sqlt-white.jpg', 2);
INSERT INTO images (item_id, filename, position) VALUES
('SQLT-BLK','sqlt-black.jpg', 1);
INSERT INTO images (item_id, filename, position) VALUES
('SQLL-WHT','sqll-white.jpg', 2);
INSERT INTO images (item_id, filename, position) VALUES
('SQLL-BLK','sqll-black.jpg', 1);
On 11/10/2010 2:47 PM, James wrote:
This will only display products which have items with images. I think
I'm going to sit back and see if there's a simpler way to achieve what
I'm trying to do. Maybe I'm going about this the wrong way, or I need
to make some compromises.
Thanks
On Wed, Nov 10, 2010 at 3:01 PM, Igor Tandetnikitandet...@mvps.org wrote:
select name, filename from products p, images im
where im.item_id = (
select im2.item_id from items left join images im2 on (items.id =
im2.item_id)
where items.product_id = p.id and items.buyable
order by position desc limit 1)
order by name;
--
Igor Tandetnik
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comma-delimited field to rows (Once again)
Not much help but this removes the multiplication: SELECT B1.B + B2.B + B3.B + B4.B FROM (SELECT 0 AS B UNION SELECT 1 AS B) AS B1, (SELECT 0 AS B UNION SELECT 2 AS B) AS B2, (SELECT 0 AS B UNION SELECT 4 AS B) AS B3, (SELECT 0 AS B UNION SELECT 8 AS B) AS B4 On 10/15/2010 3:00 PM, Max Vlasov wrote: Hi, from time to time I try to solve well-known task of making rows from a comma-delimited list and the best I could do was this: - create a user-function returning zero-based Nth item from the list (let's call it GetItemFromSet) - make a complex query like this (this one allows up to 16 elements in the list, can be expanded with similar selects) SELECT Trim(GetItemFromSet(Value, '23, 14, 1, 7, 9')) Item FROM (SELECT B1.B*1 + B2.B*2 + B3.B*4 + B4.B*8 AS VALUE FROM (SELECT 0 AS B UNION SELECT 1 AS B) AS B1, (SELECT 0 AS B UNION SELECT 1 AS B) AS B2, (SELECT 0 AS B UNION SELECT 1 AS B) AS B3, (SELECT 0 AS B UNION SELECT 1 AS B) AS B4, ) WHERE NOT (Item Is Null) Is there a way to implement something more elegant or at least to improve this approach. For example, the query for 16 bit limit will have 16 selects and bigger expression and also will iterate through all cross join output when we actually only have 5 items in the list. Max ___ 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] What is the most efficient way to get the close by numbers?
If there is an index on (name, position) the a where like below might
use it.
A1.name=A2.name and A2.position between( A1.position - 10, A1.position + 10 )
On 8/20/2010 3:54 PM, Peng Yu wrote:
Hi,
I have the following code to search for neighboring positions
(distance=10). But it is slow for large data set. I'm wondering what
is the most efficient query for such a search. Note that I don't
create an index, as I'm not sure what index to create on table A.
$ cat main.sql
#!/usr/bin/env bash
rm -f main.db
sqlite3 main.dbEOF
create table A (name text, position integer);
insert into A values('a', 1);
insert into A values('a', 5);
insert into A values('a', 21);
insert into A values('b', 3);
insert into A values('b', 15);
insert into A values('b', 19);
.mode column
.headers on
.echo on
select * from A as A1, A as A2 where A1.name=A2.name and
abs(A1.position - A2.position)= 10 and A1.position != A2.position;
EOF
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to inner join on named intervals?
Did you try something like(pseudo code): select * from A inner join B on A.name = B.name AND ( B.left between(A.left,A.right) OR B.right between(A.left,A.right) ) On 8/13/2010 8:07 AM, Peng Yu wrote: Hi, Suppose that I have a table A, each row represents a interval. For example, the first row represents an interval [1,10) with a name a. The first and second rows are considered overlapping because the interval [1,10) and interval [5,15) intersect and both rows have the same name a. name left right tag - a 1 10 tag1 a 5 15 tag2 a21 30 tag3 b 3 12 tag4 b15 25 tag5 b19 30 tag6 I want to inner join the above table and the following table B based on the named interval overlapping. name left right attr - a 3 7 attr1 a 8 12 attr2 a16 18 attr3 a25 35 attr4 b31 32 attr5 The result is the following. In each row, the named interval from A overlaps the named interval from B. I don't see there is an easy way to do this in sqlite3. I could use an external program (such as python sqlite package) to enumerate all the named interval from table A and search for overlapping named intervals in table B, but this operation has a complexity of M log (N), where M is the length of table A and N is the length of table B. If some sort of inner join could be used, the complexity should be reduced to log(M+N). I'm wondering if there something that can help do this kind of named interval inner join easily. A.name A.left A.right A.tag B.name B.left B.right B.attr a 1 10 tag1a 3 7attr1 a 1 10 tag1a 8 12attr2 a 5 15 tag2a 3 7attr1 a 5 15 tag2a 8 12attr2 a21 30 tag3a16 18attr3 ___ 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
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 table2 (KEY, X, Y) 0, 0, 0 1, 0, 1 2, 1, 0 3, 1, 1 What I would like to do is, like before, subtract COL1 from COL1 where table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0). But I want to do a vector subtraction instead of a scalar subtraction. So far I have UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE table1.KEY IN (SELECT table2 WHERE X=0) The result I would like to get is table1 0, 0 //(1 - 1) 0, 0 //(2 - 2) 1, 2 //(3 - 1) 1, 2 //(4 - 2) 2, 5 2, 6 3, 7 3, 8 Instead I get 0, 0 //(1 - 1) 0, 1 //(2 - 1) 1, 2 //(3 - 1) 1, 3 //(4 - 1) 2, 5 2, 6 3, 7 3, 8 Is this possible in SQL? peterwinson1 wrote: Hello, I have a some what complex question about UPDATE. I have the following table table1 (KEY, COL1) 0, 1 1, 2 2, 3 3, 4 What I would like to do is to UPDATE COL1 by subtracting the COL1 value where KEY = 0 from the COL1 value of the current row so that the result would be. 0, 0 1, 1 2, 2 3, 3 Can this be done in SQL? It does not have to be one UPDATE/SELECT statement. Thank you pw ___ 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
You must add additional data to the rows so you can refer to them unambiguously. table1 (KEY, COL1, ord) 0, 1,1 0, 2,2 1, 3,1 1, 4,2 2, 5,1 2, 6,2 3, 7,1 3, 8,2 On 7/23/2010 12:16 PM, peterwinson1 wrote: Jim you maybe correct that I don't have enough data to unambiguously identify the rows. But just in case I was not very clear the first time. What I want to do is take the COL1 values of the first 2 rows [1, 2] and subtract them from the COL1 values, two rows at a time. so [1, 2] - [1, 2], then [3, 4] - [1, 2], then [5, 6] - [1, 2], and finally [7, 8] - [1, 2]. The question is can I do this just using SQL? Jim Morris-4 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 table2 (KEY, X, Y) 0, 0, 0 1, 0, 1 2, 1, 0 3, 1, 1 What I would like to do is, like before, subtract COL1 from COL1 where table1.KEY = 0 and WHERE table1.KEY is IN (SELECT table2 WHERE X=0). But I want to do a vector subtraction instead of a scalar subtraction. So far I have UPDATE table1 set COL1 = COL1 - (SELECT COL1 WHERE table1.KEY = 0) WHERE table1.KEY IN (SELECT table2 WHERE X=0) The result I would like to get is table1 0, 0 //(1 - 1) 0, 0 //(2 - 2) 1, 2 //(3 - 1) 1, 2 //(4 - 2) 2, 5 2, 6 3, 7 3, 8 Instead I get 0, 0 //(1 - 1) 0, 1 //(2 - 1) 1, 2 //(3 - 1) 1, 3 //(4 - 1) 2, 5 2, 6 3, 7 3, 8 Is this possible in SQL? peterwinson1 wrote: Hello, I have a some what complex question about UPDATE. I have the following table table1 (KEY, COL1) 0, 1 1, 2 2, 3 3, 4 What I would like to do is to UPDATE COL1 by subtracting the COL1 value where KEY = 0 from the COL1 value of the current row so that the result would be. 0, 0 1, 1 2, 2 3, 3 Can this be done in SQL? It does not have to be one UPDATE/SELECT statement. Thank you pw ___ 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 database handle caching and write permissions
You also need to watch for multiple command separated via ';' On 7/15/2010 11:36 AM, JT Olds wrote: I considered that also, but I wasn't sure about whether or not that guaranteed no disk writes (maybe some sort of function call might be made there). That also restricts things like the usage of in-memory temp tables that might be useful. It appears that sqlite knows whether or not a statement will definitively, actually hit disk, whereas filtering by SELECT seemed unclear to me as to whether it would quite cover or catch everything. If that is truly the best way, then that's fine I guess. -JT On Thu, Jul 15, 2010 at 12:25 PM, Simon Slavinslav...@bigfraud.org wrote: On 15 Jul 2010, at 7:07pm, JT Olds wrote: is there a way to check a prepared statement before allowing its use as to if it will attempt to write to disk? You could perhaps accept only statements that start with 'SELECT'. It depends on how you're passing them to SQLite. 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling as part of MFC C++ project
You need to use the project properties to set the file as a C file and to not use precompiled headers On 7/13/2010 5:47 PM, GHCS Software wrote: What do I need to do to get sqlite3.c to compile in a MFC C++ project (Visual C++)? If I just add it to the project, I end up getting a compile error something like: Unexpected end of file while searching for pre-compiled header directive. Do I need to rename it sqlite3.cpp and put the include ofstdafx.h at the top? Or what?? ___ 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 =n times and only show n times if it appears more than n times?
Maybe this? SELECTwhatever column, min(Count(type_id),n) FROM foods GROUP BYwhatever column ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select an entry that appears =n times and only show n times if it appears more than n times?
Are you thinking of limit? On 7/2/2010 9:58 AM, Simon Slavin wrote: On 2 Jul 2010, at 5:15pm, Peng Yu wrote: I want to select an entry that appears =n times and only show n times if it appears more than n times. I think that group by might help. There's no simple format which will do what you want. Do it in software. 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
Re: [sqlite] loading data from file with the file name as the extra field
Try creating a script file something like(Psuedo code):
.separator ,
CREATE TEMP TABLE dummy (value);
.import file1.csv dummy
INSERT INTO TEST (filename,number) (SELECT 'file1.csv', value FROM dummy;
delete from dummy;
.import file2.csv dummy
INSERT INTO TEST (filename,number) (SELECT 'file2.csv', value FROM dummy;
DROP TABLE dummy;
On 7/2/2010 1:42 PM, Peng Yu wrote:
Hi,
Suppose that I have a number of files, each file has some numbers in
it (by line). I want to load the content of each file and the
associated filename into the following table.
create table test (id integer primary key, filename text, number integer);
For example, if file 'a' has number 1,2,3. Basically, I want to do
insert into test (filename, number) values('a',1);
insert into test (filename, number) values('a',2);
insert into test (filename, number) values('a',3);
I could convert each file with an additional field that has the
filename, and then .import it. But I wondering if there is a syntax in
sql that can allow me to add a field directly. I don't find such a way
and I just want to double check.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow query
You also haven't specified a primary key, i.e. on id On 6/28/2010 11:24 PM, J. Rios wrote: I have the next table table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER ); I have created the next indexes : index1( name ), index2( id2 ), index3( name2 ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] marking transaction boundaries
I don't understand the driver for this but have you considered creating a function that would be called as part of the insert or trigger that would have greater access to application/sqlite internal info that might be used to create a transaction id. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Minor WAL document typo
http://www.sqlite.org/draft/wal.html 'a' should be 'as' in the text located in the same directory or folder a the original database file Should be located in the same directory or folder as the original database file ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE = does not work
Is is possible the character encoding is different?
On 4/30/2010 6:59 AM, Adam DeVita wrote:
Is it possible there is a null, tab, newline or other invisible character?
Try
select timeStamp, '' || resourceType || 'xx' From MyTable where
resourceType like 'PSM' LIMIT 10;
On Fri, Apr 30, 2010 at 9:53 AM, ecforuecforus...@gmail.com wrote:
I don't think it is a case issue. See below from sqlite3 command line.
Also one thing to note - I build the database from c API. I don't know if
that makes a difference.
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType like
'PSM' LIMIT 10;
timeStamp|resourceType
2010-04-28 17:46:45.316|PSM
2010-04-28 17:46:49.854|PSM
2010-04-28 17:46:52.830|PSM
2010-04-28 17:47:04.939|PSM
2010-04-28 17:47:06.776|PSM
2010-04-28 17:47:08.846|PSM
2010-04-28 17:47:12.001|PSM
2010-04-28 17:47:13.845|PSM
2010-04-28 17:47:16.837|PSM
2010-04-28 17:47:18.846|PSM
sqlite
sqlite
sqlite select timeStamp, resourceType From MyTable where resourceType =
'PSM' LIMIT 10;
sqlite
sqlite
sqlite
THANKS
On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS)
michael.bla...@ngc.com
wrote:
You are likely getting the case insensitive result with like.
sqlite create table t(resourceType varchar);
sqlite insert into t values('PSM');
sqlite insert into t values('psm');
sqlite select * from t where resourceType = 'PSM';
PSM
sqlite select * from t where resourceType like 'PSM';
PSM
psm
sqlite select * from t where upper(resourceType) = 'PSM';
PSM
psm
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of ecforu
Sent: Fri 4/30/2010 8:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WHERE = does not work
But the like WHERE clause works the way it is. Its the = that isn't
working. I would rather use = than like. I'm just using like for now
because it works.
Thanks
On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer
tsaw...@mybowlingdiary.com wrote:
With the like clause you have to use the % sign as a wildcard. So
resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite
website
has excellent docs on standard SQL.
-Original Message-
From: ecforuecforus...@gmail.com
Sent: Friday, April 30, 2010 09:22
To: sqlite-users@sqlite.org
Subject: [sqlite] WHERE = does not work
I have an sqlite3 database which I can't query with WHERE =. I have to
use
WHERE like.
Any ideas why this is?
For example I have a resourceType column that has as some of its
entries
(over 50) 'PSM'.
SELECT * FROM MyTable WHERE resourceType = 'PSM' -- returns nothing.
SELECT * FROM MyTable WHERE resourceType like 'PSM' -- returns all PSM
entries.
What's the diff?
Thanks
___
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-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-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] Query plan of MAX (id) versus MAX (id) + 1 in a range
Did you try something like: SELECT id + 1 FROM foo WHERE id= 100 AND id 200 and id = MAX (id); On 02/22/2010 7:02 AM, ArtemGr wrote: Simon Slavinslav...@... writes: Just out of interest, and I know that theoretically this is not an optimal statement, but have you compared this with the results of putting the '+1' in the brackets ? Thanks for the pointer, Simon. Looking back to my analizys, I see that it was wrong. SELECT MAX (id) FROM foo WHERE id= 100 AND id 200; actually jumps to Close directly after AggStep, it would only read a single row to produce the result. No suck luck for SELECT MAX (id) + 1 FROM foo WHERE id= 100 AND id 200; which goes thru the whole range. As for MAX (id) + 1 versus MAX (id + 1), SELECT MAX (id) + 1 FROM foo WHERE id= 100 AND id 200; calculates MAX (id) in a cycle, then it adds 1 outside of the cycle, before submitting ResultRow. SELECT MAX (id + 1) FROM foo WHERE id= 100 AND id 200; increments id in every iteration of the cycle and passes the result to max. MAX (id + 1) is clearly less optimizable than MAX (id) + 1. Obvioulsy, SQLite already have a special case optimization for MAX (id), but optimization breaks with MAX (id) + 1, making it impractical for use in INSERT SELECT. ___ 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
