[sqlite] One line batch file?
I'd like to have a one line batch file:
sqlite3 test 'insert into testable values ('value1','value2')'
but the few variants of this I've tried don't work.
I've seen and understood the batch file that calls another text file
approach but I was wondering if I could avoid this overhead for a
one-liner.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] PATCH: allow DISTINCT queries to use an INDEX, if present
Attached patch allows DISTINCT queries to take advantage of an appropriate
INDEX, if present.
For example, given:
CREATE TABLE foo(a, b);
CREATE INDEX foo_b on foo(b);
explain query plan select distinct b from foo;
sqlite 3.5.2 returns:
0|0|TABLE foo
and sqlite 3.5.2 + patch returns:
0|0|TABLE foo WITH INDEX foo_b ORDER BY
The query "select distinct b from foo" is transformed into
"select b from foo group by b" by the patch.
No regressions in "make test", although a few tests had to be
altered in order to take into account the new ordering of DISTINCT
rows. This is to be expected, as the order in which DISTINCT rows
are returned were never guaranteed without an explicit ORDER BY
clause.
Please send any bug reports or comments to the list.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com Index: src/select.c
===
RCS file: /sqlite/sqlite/src/select.c,v
retrieving revision 1.359
diff -u -3 -p -r1.359 select.c
--- src/select.c31 Aug 2007 17:42:48 - 1.359
+++ src/select.c11 Nov 2007 23:49:01 -
@@ -3067,6 +3067,14 @@ int sqlite3Select(
}
#endif
+ /* Optimizion: convert DISTINCT to GROUP BY to potentially
+ ** take advantage of an INDEX
+ */
+ if (p->isDistinct && !p->isAgg && !p->pGroupBy) {
+pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, p->pEList);
+isDistinct = p->isDistinct = 0;
+ }
+
/* If there is an ORDER BY clause, then this sorting
** index might end up being unused if the data can be
** extracted in pre-sorted order. If that is the case, then the
Index: test/collate5.test
===
RCS file: /sqlite/sqlite/test/collate5.test,v
retrieving revision 1.5
diff -u -3 -p -r1.5 collate5.test
--- test/collate5.test 7 Sep 2005 22:48:16 - 1.5
+++ test/collate5.test 11 Nov 2007 23:49:01 -
@@ -57,17 +57,17 @@ do_test collate5-1.1 {
execsql {
SELECT DISTINCT a FROM collate5t1;
}
-} {a b n}
+} {A B N}
do_test collate5-1.2 {
execsql {
SELECT DISTINCT b FROM collate5t1;
}
-} {apple Apple banana {}}
+} {{} Apple apple banana}
do_test collate5-1.3 {
execsql {
SELECT DISTINCT a, b FROM collate5t1;
}
-} {a apple A Apple b banana n {}}
+} {A Apple a apple B banana N {}}
# The remainder of this file tests compound SELECT statements.
# Omit it if the library is compiled such that they are omitted.
Index: test/insert4.test
===
RCS file: /sqlite/sqlite/test/insert4.test,v
retrieving revision 1.8
diff -u -3 -p -r1.8 insert4.test
--- test/insert4.test 9 Oct 2007 08:29:32 - 1.8
+++ test/insert4.test 11 Nov 2007 23:49:01 -
@@ -112,7 +112,7 @@ do_test insert4-2.4.1 {
INSERT INTO t3 SELECT DISTINCT * FROM t2;
SELECT * FROM t3;
}
-} {9 1 1 9}
+} {1 9 9 1}
xferopt_test insert4-2.4.2 0
do_test insert4-2.4.3 {
catchsql {
Index: test/misc5.test
===
RCS file: /sqlite/sqlite/test/misc5.test,v
retrieving revision 1.17
diff -u -3 -p -r1.17 misc5.test
--- test/misc5.test 12 Sep 2007 17:01:45 - 1.17
+++ test/misc5.test 11 Nov 2007 23:49:01 -
@@ -490,6 +490,7 @@ ifcapable subquery {
LIMIT 10
)
WHERE artist <> ''
+ ORDER BY 1 DESC
)
)
)
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Odd behaviour on UPDATE
Hi Igor, I'm quite sure I did not. By the way, I even tried the following
code (that should BEGIN and COMMIT changes), but result is the same.
char * errors;
sqlite3_exec( waypoint_db, "BEGIN", 0, 0, 0 );
int ret = sqlite3_exec( waypoint_db, sqlstring, 0, 0, &errors );
while( ret != SQLITE_OK )
{
printf("There is some errors while executing SQL statement:\n ");
sqlite3_free( errors );
ret = sqlite3_exec( waypoint_db, sqlstring, 0, 0, &errors );
}
sqlite3_exec( waypoint_db, "COMMIT", 0, 0, 0 );
sqlite3_exec( waypoint_db, "END", 0, 0, 0 );
Igor Tandetnik wrote:
>
> papillon <[EMAIL PROTECTED]> wrote:
>> I update a record with the following code:
>>
>> rc = sqlite3_exec(waypoint_db, sqlstring, callback, 0, &zErrMsg);
>>
>> function return SQLITE_OK.
>>
>> I use a SELECT to see the records and yes, the above records seems to
>> be updated, it shows the new value.
>>
>> I quit the application (correctly closing the db), open it again and
>> ... the record is showing the value prior to the update call.
>> So it seems that even if a SELECT statement show that the value has
>> been changed, these updates are not committed to disk ?
>
> Have you, by any chance, started a transaction (with BEGIN statement)
> but then closed the connection without committing it? This is equivalent
> to rolling it back.
>
> Igor Tandetnik
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/Odd-behaviour-on-UPDATE-tf4787954.html#a13697511
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Odd behaviour on UPDATE
papillon <[EMAIL PROTECTED]> wrote: I update a record with the following code: rc = sqlite3_exec(waypoint_db, sqlstring, callback, 0, &zErrMsg); function return SQLITE_OK. I use a SELECT to see the records and yes, the above records seems to be updated, it shows the new value. I quit the application (correctly closing the db), open it again and ... the record is showing the value prior to the update call. So it seems that even if a SELECT statement show that the value has been changed, these updates are not committed to disk ? Have you, by any chance, started a transaction (with BEGIN statement) but then closed the connection without committing it? This is equivalent to rolling it back. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Odd behaviour on UPDATE
Hello everybody, I'm quite new to sqlite and I have a question about an odd behaviour. I'm using MSVC2003 and latest sqlite version. I update a record with the following code: rc = sqlite3_exec(waypoint_db, sqlstring, callback, 0, &zErrMsg); function return SQLITE_OK. I use a SELECT to see the records and yes, the above records seems to be updated, it shows the new value. I quit the application (correctly closing the db), open it again and ... the record is showing the value prior to the update call. So it seems that even if a SELECT statement show that the value has been changed, these updates are not committed to disk ? I'm quite lost, any suggestion is really welcome. -- View this message in context: http://www.nabble.com/Odd-behaviour-on-UPDATE-tf4787954.html#a13697322 Sent from the SQLite mailing list archive at Nabble.com.
Re: [sqlite] Suggests for improving the SQLite website
Reduce the size of the logo. The proposed combined size of the logo and Navbar takes up too much of the page. I like frames so that the Navbar is always visible. This is a real convenience with long pages. If you don't like frames then another alternative is to have the Navbar at the top and bottom of the page. Robert Wishlaw
Re: [sqlite] SQLite website
On Nov 10, 2007 5:54 AM, <[EMAIL PROTECTED]> wrote:
> When you click on one of the links on the menu bar, afterwards
> the font color is almost identical to the background color
> so you can no longer read the text. Can you suggest a fix
> for this problem?
I uploaded a new version with explicit :hover and :visited colors for all links.
http://www.cs.hmc.edu/~jdarpini/sqlite.html
James
--
main(c,r){for(r=32;r;)printf(++c>31?c=!r--,"\n":c
Re: [sqlite] Re: is safe to use the same database connection at the same time in more than one thread?
Hey everyone, Thanks for your reply, Igor! I've got two more questions, now. Is it also true that if a select statement executes (in any thread) before #50 in my example below, it could return data that aren't durable? My second question is more complicated. How expensive is opening a new connection? My inclination is to keep a pool of connections and write logic so that each transaction has exclusive control of one connection for the lifetime of that transaction. Are there circumstances under which it'd be faster to just always open a new connection? Thanks so much for your help. Cheers, Don On Nov 10, 2007, at 1:24 PM, Igor Tandetnik wrote: Don Lavelle <[EMAIL PROTECTED]> wrote: Second, my question is about SQLite and multi-threading. Basically, the snippet below means that if I have multiple threads executing SQL statements, and I want at least one thread to execute multiple SQL statements as a single unit, I need to make sure only one thread at a time is executing SQL, or the SQL statements that are outside of the thread may get lumped in with the transaction. As in, 10. thread 1: shut off auto commit 20. thread 1: SQL statement 1 succeeds 30. thread 2: independent SQL statement succeeds 40. thread 1: SQL statement 2 fails 50. thread 1: rollback, taking with it thread 2's effect This is correct, assuming both threads are using the same database connection. The behavior would be different if each thread opens its own connection (specifically, the second connection could not successfully commit a change at #30). Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
"Trevor Talbot" <[EMAIL PROTECTED]> writes: > (2) and (3) feel heavy/slow, and pulldown menus are irritating to > navigate. They also do not render correctly with larger font sizes. Agree, pulldown menus are irritating. Jarl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
[EMAIL PROTECTED] writes: > I put up 4 variations. Please, everyone, offer your opinions: > >(1) http://sqlite.hwaci.com/v1/ No CSS of any kind. >(2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners >(3) http://sqlite.hwaci.com/v3/ CSS menus with square corners >(4) http://sqlite.hwaci.com/v4/ CSS font specification only > > (2) and (3) do not work on IE6. (1) has ugly fonts, I am told. > That leaves me with (4). (2) and (3) are rendered imperfect on Konqueror as well, and therefore probably also on Safari (on Mac) since they are based on the same HTML render engine (KHTML). That experience is on Konqueror 3.5.7 that renders the acid test (http://www.webstandards.org/files/acid2/test.html) perfectly!!! I prefer the look of (2), but the implementation may be reconsidered due to the situation regarding Konqueror (and probably Safari as well) Jarl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
"A.J.Millan" <[EMAIL PROTECTED]> writes: >> Regarding the basic "look" of the site, we were considering >> using a style similar to the once found at ActiveState >> >> http://www.activestate.com/ >> > > However the tendency in computers screen is wider than until > now. Today the standard is about 1440 pixels x 900 It's irelevant what's the screen resoulution is, the interesting is the shape of the browser window. I would like to see websites designed for higher-than-wide shaped browser windows approximately like portrait A4 (or Letter) shapes. If all applications (including web-sites) are designed to use full screen. You will never be able get a screen large enough with resolution high enough to feel natural working with more than one application on the screen at the same time, as they are designed to work best when taking up the whole screen area. Jarl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
Gerry Snyder <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: >> >> >> A rough prototype of what a revised website might look like >> can be seen at >> >> http://sqlite.hwaci.com/ > Short, simple, and sweet. I like it. > > My only specific comment was going to be a request to make the page > for datatypes easy to find. Very nice, performing and clean look and feel. I agree. I don't know, but I bet the datatype page is probably one of the most visited pages, anyway web statistics should be able to tell. Now that you are at it, I'd suggest you remove section 6 (other affinity modes) from http://www.sqlite.org/datatype3.html until the feature is implemented. Jarl - To unsubscribe, send email to [EMAIL PROTECTED] -
