Re: [sqlite] SQLite on Windows Phone 8

[Noah Hart]

Hello Peter,

This is a common configuration.

Please visit the C#SQLite project here: 
http://code.google.com/p/csharp-sqlite/

and post questions to the usergroup here: 
https://groups.google.com/forum/?fromgroups#!forum/csharp-sqlite

Please keep in mind the following:

This is not an official version of SQLite
There is completely NO affiliation with SQLite.org
Bugs should not be reported to their ticket tracking system 

Thank you,
Noah Hart





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/SQLite-on-Windows-Phone-8-tp65316p65382.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

Re: [sqlite] Cannot insert/retrieve 8 byte integers without losing digits

[Noah Hart]

May be in your program;  From the 3.7.10 shell

sqlite> select id, start_time from exports;
d006dacf-3134-45b6-828b-0860738e4029|1311178875028
sqlite> select id, cast(start_time as integer) from exports;
d006dacf-3134-45b6-828b-0860738e4029|1311178875028
sqlite>

What version are you running?

Regards, Noah

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of yesnid
Sent: Thursday, February 09, 2012 9:43 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Cannot insert/retrieve 8 byte integers without
losing digits


Hello Simon,

Here is my create:

CREATE TABLE IF NOT EXISTS Exports (id  varchar(50)
PRIMARY
KEY NOT NULL,start_time  integer,end_time   
integer,data_source_id  varchar(50),format 
integer,percent_completeinteger,sizeinteger,comment

varchar(50),user_id varchar(50),state  
integer,friendly_name   varchar(50),download_count 
integer,mark_for_delete integer,udn varchar(50));

and here is my insert:

INSERT INTO Exports
VALUES('d006dacf-3134-45b6-828b-0860738e4029',1311178875028,131117893502
8,'dvd-1',2001,0,0,'/*NoComment*/','matt',1337,'d006dacf-3134-45b6-828b-
0860738e4029',0,0,'406b8555-5ae5-496d-844c-2f839e19eb75');

what winds up in the database for start and end time is: 

1213849748

which is what the number 1311178875028 becomes if you cast it to an int,
I assume that I am just missing a simple thing here but can really use
your help.

Thank you,



Simon Slavin-3 wrote:
> 
> 
> On 9 Feb 2012, at 4:58pm, yesnid wrote:
> 
>> I am having a similar issue, I am trying to store a 13 digit value in

>> my table and it is being converted to a 10 digit value? Is there 
>> something that I need to do, to enable the 8 byte values for integer?
> 
> What type have you defined that column in the table as ?  Can you 
> supply the CREATE command or the relevant part of it ?
> 
> How are you supplying the values to be stored ?  Are you using _exec()

> on a long string, or binding them as an integer, a float, or what ?
> 
> Simon.
> ___
> 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/Cannot-insert-retrieve-8-byte-integers-without-los
ing-digits-tp33243026p33295099.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



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
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

[Noah Hart]

The C#-SQLite port at http://code.google.com/p/csharp-sqlite/ supports both
Silverlight and Windows Phone


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] ANN: C#-SQLite 3.7.7.1

[Noah Hart]

C#-SQLite has been updated to release 3.7.7.1 and is RC1 is now ready for
use.

The 7/5/2011 release features:

 * Updated to SQLite version 3.7.7.1
 * VFS is planned for RC2
 * WAL is planned for RC3
  * FTS is planned for RC4

It now runs 58,187 of the tcl testharness quicktests and 273,163 of the
alltest suite without error.

The project is located at http://code.google.com/p/csharp-sqlite/

Enjoy,

Noah Hart

--
Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system

SQLite® is a registered trademark of Hipp, Wyrick & Company, In
-- 
View this message in context: 
http://old.nabble.com/ANN%3A-C--SQLite-3.7.7.1-tp31999567p31999567.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] ANN: C#-SQLite 3.7.6.3

[Noah Hart]

C#-SQLite has been updated to release 3.7.6.3 and is now ready for use.

The 6/24/2011 release features:

 * Updated to SQLite version 3.7.6.3
 * Silverlight support
 * Windows 7 Phone

It now runs 58,100 of the tcl testharness tests without errors.

The project is located at http://code.google.com/p/csharp-sqlite/

Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system

SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc


Enjoy,

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/ANN%3A-C--SQLite-3.7.6.3-tp31922081p31922081.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] Is additional Check Constraint info available?

[Noah Hart]

Assume that a table has several check restraints, such as a unique index and
a foreign key.

When inserting a row that violates more than one of the constraints, is
there any way to tell which of the constraint would have been violated? 


Thanks,

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/Is-additional-Check-Constraint-info-available--tp31812082p31812082.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] Check restraint

[noah hart]

Assume that a table has several check restraints, such as a unique index and
a foreign key.

When inserting a row that violates more than one of the constraints, is
there any way to tell which of the constraint would have
been violated?

Thanks,

Noah Hart
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running TCL tests on 3.7.5

[Noah Hart]

Some of the 3.7.5 tests use a new TCL construction, {*}, which requires TCL
8.5

You can bypass that particular one in tester.tcl:

proc do_execsql_test {testname sql {result {}}} {
  fix_testname testname
#  uplevel do_test $testname [list "execsql {$sql}"] [list [list
{*}$result]]
  uplevel do_test $testname [list "execsql {$sql}"] [list [concat $result]]
}

however, there are a number of others you will find.  I'd try and update TCL
on Ubuntu 

~Noah Hart


Krokodile wrote:
> 
> Hello
> 
> I've got 3.7.5 sources via ZIP archive from
> http://www.sqlite.org/cgi/src/info/ed759d5a9e - (SQLite version 3.7.5
> release candidate 2)
> Configure and install under Ubuntu 10.10 was ok.
> After that i run "make fulltest" and got following error:
> 
> autoindex1-212... Ok
> autoindex1-300... Ok
> autoindex1-310... Ok
> autoindex1-400... Ok
> autoindex1-401... Ok
> ./testfixture: extra characters after close-brace
> while compiling
> "uplevel do_test $testname [list "execsql {$sql}"] [list [list
> {*}$result]]
> "
> (compiling body of proc "do_execsql_test", line 3)
> invoked from within
> "do_execsql_test autoindex1-500 {
>   CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
>   CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
>   EXPLAIN QUERY PLAN
>   ..."
> (file "./test/autoindex1.test" line 143)
> invoked from within
> "source ./test/autoindex1.test"
> invoked from within
> "interp eval tinterp $script"
> (procedure "slave_test_script" line 24)
> invoked from within
> "slave_test_script [list source $zFile] "
> invoked from within
> "time { slave_test_script [list source $zFile] }"
> (procedure "slave_test_file" line 14)
> invoked from within
> "slave_test_file $file"
> (procedure "run_tests" line 14)
> invoked from within
> "run_tests full -presql {} -files {tkt-5d863f876e.test shared3.test
> bigfile.test tkt3419.test where9.test fts1o.test fts2f.test sync.test
> ioerr4.test e..."
> ("uplevel" body line 1)
> invoked from within
> "uplevel run_tests $name $::testspec($name)"
> (procedure "run_test_suite" line 5)
> invoked from within
> "run_test_suite full"
> (file "./test/all.test" line 17)
> make: *** [fulltest] Error 1
> 
> Why does this problem appears - i made made changes neither in code nor in
> tests. Does it mean version inconsistency?
> 
> Thanks in advance.
> ___
> 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/Running-TCL-tests-on-3.7.5-tp31152794p31153289.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] BUG: mutex_w32.c; diagnostic message needs to be clearer

[Noah Hart]

In the routine winMutexTry at line 284

-printf("enter mutex %p (%d) with nRef=%d\n", p, p->trace, p->nRef);
+printf("try mutex %p (%d) with nRef=%d\n", p, p->trace, p->nRef);

-- 
View this message in context: 
http://old.nabble.com/BUG%3A-mutex_w32.c--diagnostic-message-needs-to-be-clearer-tp31149931p31149931.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] BUG: test script e_vacuum.test needs has_codec

[Noah Hart]

According to vacuum.c

 /* A VACUUM cannot change the pagesize of an encrypted database. */

So the tests in e_vacuum.test which attempt to change page size need to be
bracked with a test for codec

something like the following

# Determine if there is a codec available on this test.
#
if {[catch {sqlite3 -has-codec} r] || $r} {
  set has_codec 1
} else {
  set has_codec 0
}

if {!$has_codec} {
do_test e_vacuum-1.3.2.1 {
...
}

if {!$has_codec} {
do_test e_vacuum-1.3.3.2 {
...
}

~Noah Hart 
-- 
View this message in context: 
http://old.nabble.com/BUG%3A-test-script-e_vacuum.test-needs-has_codec-tp31084122p31084122.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] ANN: C#-SQLite 3.7.5

[Noah Hart]

C#-SQLite has been updated to release 3.7.5 and is now ready for use.

The 2/28 release features:

 * SQL_HAS_CODEC compiler option
 * Silverlight support
 * Windows 7 Phone

Does not support WAL

It now runs 54,618 of the tcl testharness tests without errors.

The project is located at http://code.google.com/p/csharp-sqlite/

Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system

SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc


Enjoy,

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/ANN%3A-C--SQLite-3.7.5-tp31041201p31041201.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] BUG: e_vacuum.test & backcompat.test need ifcapable wal

[Noah Hart]

test files have a couple of lines similar to:
 execsql { PRAGMA journal_mode = wal }

These tests need to be bracketed with ifcapable wal

~ Noah Hart
-- 
View this message in context: 
http://old.nabble.com/BUG%3A-e_vacuum.test---backcompat.test-need-ifcapable-wal-tp31037029p31037029.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] BUG: e_expr.test needs ifcapable utf16

[Noah Hart]

e_expr has a number of tests that need to have ifcapable utf16 bracketing

For example:
db eval { PRAGMA encoding = 'utf-16le' }
do_qexpr_test e_expr-27.4.4 { CAST('ghi' AS blob) } X'670068006900'
do_qexpr_test e_expr-27.4.5 { CAST(456 AS blob) }   X'340035003600'
do_qexpr_test e_expr-27.4.6 { CAST(1.78 AS blob) }  X'31002E0037003800'
...
db eval { PRAGMA encoding = 'utf-16be' }
do_qexpr_test e_expr-27.4.7 { CAST('ghi' AS blob) } X'006700680069'
do_qexpr_test e_expr-27.4.8 { CAST(456 AS blob) }   X'003400350036'
do_qexpr_test e_expr-27.4.9 { CAST(1.78 AS blob) }  X'0031002E00370038'
...
db eval { PRAGMA encoding = 'utf-16le' }
do_expr_test e_expr-28.1.3 { CAST (X'676869' AS text) == 'ghi' } integer 0
do_expr_test e_expr-28.1.4 { CAST (X'670068006900' AS text) } text ghi
...
db eval { PRAGMA encoding = 'utf-16le' }
do_expr_test e_expr-29.1.5 { 
CAST (X'31002E0032003300' AS REAL) } real 1.23
do_expr_test e_expr-29.1.6 { 
CAST (X'3200330030002E003000' AS REAL) } real 230.0
do_expr_test e_expr-29.1.7 { 
CAST (X'2D0039002E0038003700' AS REAL) } real -9.87
do_expr_test e_expr-29.1.8 { 
CAST (X'30002E003000300030003100' AS REAL) } real 0.0001

-- 
View this message in context: 
http://old.nabble.com/BUG%3A-e_expr.test-needs--ifcapable-utf16-tp31035741p31035741.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] BUG: types3.test

[Noah Hart]

this test in types3 says that the type is ""

do_test types3-2.5 {
  set V [db one {SELECT '1234567890123456.0'}]
  tcl_variable_type V
} {}

however when I try it from the command line i get text

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT '1234567890123456.0', typeof('1234567890123456.0');
1234567890123456.0|text

I think that the test is wrong and tcl should be expecting {string}

Regards,

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/BUG%3A-types3.test-tp31016794p31016794.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] BUG: test script exclusive2.test needs do_not_use_codec

[Noah Hart]

exclusive2.test reads directly from the database using binary read

It needs the following changes:

-source $testdir/tester.tcl
+source $testdir/tester.tcl
+
+# Do not use a codec for tests in this file, as the database file is  
+# manipulated directly using tcl scripts (using binary read)
+#  
+do_not_use_codec  


~Noah Hart

-- 
View this message in context: 
http://old.nabble.com/BUG%3A-test-script-exclusive2.test-needs-do_not_use_codec-tp30927245p30927245.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] BUG: test script vacuum2.test needs do_not_use_codec

[Noah Hart]

vacuum2.test reads directly from the database using hexio_read

It need the following changes:

-source $testdir/tester.tcl
+source $testdir/tester.tcl
+
+# Do not use a codec for tests in this file, as the database file is  
+# manipulated directly using tcl scripts (using the [hexio_read] command).  
+#  
+do_not_use_codec  

Also, this file contains 2 tests named vacuum2-2.1

~Noah Hart

-- 
View this message in context: 
http://old.nabble.com/BUG%3A-test-script-vacuum2.test-needs-do_not_use_codec-tp30927226p30927226.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] BUG: test scripts corrupt*.test need do_not_use_codec

[Noah Hart]

corruptA.test & corruptD.test

need the following changes:

-source $testdir/tester.tcl
+source $testdir/tester.tcl
+
+# Do not use a codec for tests in this file, as the database file is  
+# manipulated directly using tcl scripts (using the [hexio_write] command).  
+#  
+do_not_use_codec  


Corrupt3.test current skips the tests when compiled with codec, but will run
with the following changes
-if {[sqlite3 -has-codec]} { finish_test ; return }
+# Do not use a codec for tests in this file, as the database file is  
+# manipulated directly using tcl scripts (using the [hexio_write] command).  
+#  
+do_not_use_codec  


~Noah Hart

-- 
View this message in context: 
http://old.nabble.com/BUG%3A-test-scripts-corrupt*.test-need-do_not_use_codec-tp30927026p30927026.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] BUG: capi3e.test when compiled with SQLITE_OMIT_UTF16

[Noah Hart]

capi3e.test needs 
  ifcapable utf16 logic before capi3e-2.1.$i 
to properly pass tests when compiled with SQLITE_OMIT_UTF16

~ Noah Hart


-- 
View this message in context: 
http://old.nabble.com/BUG%3A-capi3e.test-when-compiled-with-SQLITE_OMIT_UTF16-tp30905474p30905474.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] BUG REPORT: 3.7.5 fails to compile with SQLITE_OMIT_WAL

[Noah Hart]

The routine pager_write_changecounter in pager.c at line 2933 is eliminated
by the #ifndef SQLITE_OMIT_WAL at line 2854

This causes a compile error in the pager_write_pagelist routine at line 4014
error LNK2001: unresolved external symbol _pager_write_changecounter@4
pager.obj   sqlite3

Moving the routing in pager.c to after the #endif at line 3025 will solve
the problem.


Are you running your test suite against comditional compilation with
SQLITE_OMIT_WAL?

Regards,

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/BUG-REPORT%3A-3.7.5-fails-to-compile-with-SQLITE_OMIT_WAL-tp30818271p30818271.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

Re: [sqlite] how to use group_concat uniquely

[Noah Hart]

Igor Tandetnik wrote:
> 
> On 1/19/2011 12:51 PM, Noah Hart wrote:
>>> select E, replace(group_concat(distinct T), ',', ';'),
>>> replace(group_concat(distinct P), ',', ';') from MyTable;
>>
>>Ahhh
>> the DISTINCT keyword was what I'm missing. However, this may be a bug,
>> when I use group_concat(DISTINCT T, ';') I get the error message
>> DISTINCT aggregates must have exactly one argument
> 
> That's why I had to muck around with replace(). The syntax only allows 
> DISTINCT keyword in aggregate functions taking exactly one parameter. 
> group_concat defaults to comma as a separator when called with one 
> parameter.
> -- 
> Igor Tandetnik
> 

Well, I went and read the page on the Aggregate Functions, and it is very
clear in the documentation.

Thanks again,

Noah

-- 
View this message in context: 
http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30716460.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

Re: [sqlite] how to use group_concat uniquely

[Noah Hart]

On 1/19/2011 12:19 PM, Noah Hart wrote:
> I am having difficulty trying to return data in a very compact form.  Here
> is
> a simple example that will explain the problem:
>
> I have a table with column E, T and P containing data such as
> A, 1, R
> A, 1, S
> A, 2, R
> A, 2, S
>
> Trying the query
> select E, group_concat(T,';'), group_concat(P,';') give me the expected
> results
> A 1;1;2;2 R;S;R;S
>
> However, I would like to create a query will return the results
>
> A 1;2 R;S

select E, replace(group_concat(distinct T), ',', ';'), 
replace(group_concat(distinct P), ',', ';') from MyTable;

Wouldn't work if values in T or P contain commas.

Here's a more verbose query that doesn't have this limitation (but is 
likely much slower):

select E,
   (select group_concat(T, ';') from (select distinct T from MyTable 
where E=AllE.E)),
   (select group_concat(P, ';') from (select distinct P from MyTable 
where E=AllE.E))
from (select distinct E from MyTable) AllE;

-- 
Igor Tandetnik

___
Ahhh the DISTINCT keyword was what I'm missing.

However, this may be a bug, when I use group_concat(DISTINCT T, ';') I get
the error message
 DISTINCT aggregates must have exactly one argument

Noah
-- 
View this message in context: 
http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712310.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] how to use group_concat uniquely

[Noah Hart]

I am having difficulty trying to return data in a very compact form.  Here is
a simple example that will explain the problem:

I have a table with column E, T and P containing data such as
A, 1, R
A, 1, S
A, 2, R
A, 2, S

Trying the query
select E, group_concat(T,';'), group_concat(P,';') give me the expected
results
A 1;1;2;2 R;S;R;S

However, I would like to create a query will return the results

A 1;2 R;S

Any ideas?

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712025.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] vacuum2 & 3 test does not work if compiled with codes

[Noah Hart]

if SQLITE is compiled with SQLITE_HAS_CODEC then the test 2.2 will fail, as
well as a number of the vacuum3 tests

recommend adding the follwing lines to the beginning of the file

# Do not use a codec for tests in this file, as the database file is
# manipulated directly using tcl scripts. See proc [set_file_format].
#
do_not_use_codec


Also the second test # vacuum2-2.1 should be vacuum2-2.2

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/vacuum2---3-test-does-not-work-if-compiled-with-codes-tp29711540p29711540.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] dbstatus test needs ifcapable !lookaside logic

[Noah Hart]

if SQLITE is compiled with SQLITE_OMIT_LOOKASIDE then the dbstatus test 2 and
3 series will fail.

recommend adding the lines
ifcapable !lookaside { 
finish_test
return
 }

right before the lines


#---
# Run the dbstatus-2 and dbstatus-3 tests with several of different
# lookaside buffer sizes.
#
foreach ::lookaside_buffer_size {0 64 120} {
-- 
View this message in context: 
http://old.nabble.com/dbstatus-test-needs-ifcapable-%21lookaside-logic-tp29711400p29711400.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] tkt-9d68c88.test needs ifcapable vtab test

[Noah Hart]

This test uses sqlite3 db test.db -vfs devsym
 which is not available when compiled with SQLITE_OMIT_VIRTUALTABLE

needs the following block

ifcapable !vtab {
  finish_test
  return
}

Regards, Noah

-- 
View this message in context: 
http://old.nabble.com/tkt-9d68c88.test-needs-ifcapable-vtab-test-tp29681786p29681786.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] journal2.test needs ifcapable !vtab block

[Noah Hart]

This test uses the testvfs which is not available when compiled with
SQLITE_OMIT_VIRTUALTABLE

ifcapable !vtab {
  finish_test
  return
}

Regards, Noah
-- 
View this message in context: 
http://old.nabble.com/journal2.test-needs-ifcapable-%21vtab--block-tp29681580p29681580.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

Re: [sqlite] rollback to savepoint regression / bug with journal_mode=off and locking_mode=exclusive

[Noah Hart]

http://www.sqlite.org/pragma.html#pragma_journal_mode states

The OFF journaling mode disables the atomic commit and rollback capabilities
of SQLite. The ROLLBACK command no longer works; it behaves in an undefined
way. Applications must avoid using the ROLLBACK command when the journal
mode is OFF


I don't think this is a bug, just different undefined results.

Regards,

Noah


SQLite 3.7.2 has a regression with journal_mode=off and
locking_mode=exclusive. Here is the SQL reproduce:


drop table if exists t1;

PRAGMA locking_mode=exclusive;
pragma locking_mode;

CREATE TABLE t1(a PRIMARY KEY, b);

PRAGMA journal_mode = off;

BEGIN;
  INSERT INTO t1 VALUES(13, 14);
  SAVEPOINT s1;
INSERT INTO t1 VALUES(15, 16);
  ROLLBACK TO s1;
ROLLBACK;

SELECT * FROM t1;
-

SQLite3 3.7.2 rolls back the savepoint insert and yields:

exclusive
exclusive
off
13|14

SQLite3 3.6.23.1 commits the savepoint insert and yields:

exclusive
exclusive
off
13|14
15|16

The SQL was shortened from savepoint.test. It works well in
locking_mode=normal so I dare say this is most likely a
locking_mode=exclusive bug.

Ralf

-- 
View this message in context: 
http://old.nabble.com/rollback-to-savepoint-regression---bug-with-journal_mode%3Doff-and-locking_mode%3Dexclusive-tp29554275p29554673.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] Comment Correction in vdbemem.c

[Noah Hart]

line 1019
/* op can only be TK_REGISTER is we have compiled with SQLITE_ENABLE_STAT2.

s/b

/* op can only be TK_REGISTER if we have compiled with SQLITE_ENABLE_STAT2.


Noah Hart
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Commenting correction

[Noah Hart]

updates.c line 11

**May you do good and not evil.
**May you find forgiveness for yourself and forgive others.
**May you share freely, never taking more than you give.
**
sqlite*
** This file contains C code routines that are called by the parser

Noah Hart
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.7.0 delayed. Was: no longer able to use read-only databases?

[Noah Hart]

Richard, 

In thinking about this, consider that if you embed the WAL within the SQLite
database itself, past the high-water mark, it would also take case of this
problem.

Noah Hart


On Fri, Jul 9, 2010 at 3:21 PM, Matthew L. Creech <mlcre...@gmail.com>wrote:

> In testing the latest SQLite snapshot with WAL enabled, it seems that
> there's no way to use a database in a read-only location.
>

The release of SQLite version 3.7.0 will likely be delayed while we try to
figure out how to deal with this issue.



-- 
-
D. Richard Hipp
d...@sqlite.org

-- 
View this message in context: 
http://old.nabble.com/Version-3.7.0-delayed.-Was%3A-no-longer-able-to-use-read-only-databases--tp29142917p29143134.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] ANN: C#-SQLite 3.6.23 with aes 256 encryption

[Noah Hart]

C#-SQLite has been updated to release 3.6.23.1 and is now ready for review. 

The 6/21 release features:

 * SQL_HAS_CODEC compiler option
 * a AES256 crypto module 
 * Silverlight support 

It now runs 35,028 of the tcl testharness tests without errors.

The project is located at http://code.google.com/p/csharp-sqlite/

Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system

SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc


Enjoy,

Noah Hart

-- 
View this message in context: 
http://old.nabble.com/ANN%3A-C--SQLite-3.6.23-with-aes-256-encryption-tp28966276p28966276.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] Error in date.test with encryption codec

[Noah Hart]

The date14 test uses hexio_write, which is invalid when opened with an
encryption codec


Regards,

Noah Hart

-- 
View this message in context: 
http://old.nabble.com/Error-in-date.test-with-encryption-codec-tp28953028p28953028.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] Error in pagesize.test with encryption codec

[Noah Hart]

pagesize returns incorrect results when using a codec with an encryption
routine, 
since the Vacuum is not allowed to change the page size of an encrypted
database

Suggested changes, change the test to open the databases without encryption
by adding

-key {}

to all the 

sqlite3 db test.db 

statements


Regards,

Noah Hart

-- 
View this message in context: 
http://old.nabble.com/Error-in-pagesize.test-with-encryption-codec-tp28952580p28952580.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] Error in exclusive2.test with encryption codec

[Noah Hart]

exclusive2returns incorrect results when using a codec with an encryption
routine, 
since the pagerChangeCounter reads directly from the file

Suggested changes, run the test without encryption

Line 23
+# Disable encryption on the database for this test.
+sqlite3 db test.db -key {}


Regards,

Noah Hart

-- 
View this message in context: 
http://old.nabble.com/Error-in-exclusive2.test-with-encryption-codec-tp28952460p28952460.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] Error in nan.test with encryption codec

[Noah Hart]

nan returns incorrect results when using a codec with an encryption routine, 
since test nan-3.1 reads directly from the file via   hexio_read test.db
2040 8

Suggested changes, run the test without encryption, or skip test 14 when
running with a codec

line 27:
+# Disable encryption on the database for this test.
+sqlite3 db test.db -key {}

167:
-  sqlite3 db test.db
+  sqlite3 db test.db -key {}

173:
-  sqlite3 db test.db
+  sqlite3 db test.db -key {}

179:
-  sqlite3 db test.db
+  sqlite3 db test.db -key {}

185:
-  sqlite3 db test.db
+  sqlite3 db test.db -key {}

Regards,

Noah Hart

-- 
View this message in context: 
http://old.nabble.com/Error-in-nan.test-with-encryption-codec-tp28952408p28952408.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] Error in pcache.test with encryption codec

[Noah Hart]

pcachereturns incorrect results when using a codec with an encryption
routine, 
since test pcache-1.14 writes directly to offset 24 in the header

Suggested changes, run the test without encryption, or skip test 14 when
running with a codec

line 31:
- sqlite3 db test.db
+sqlite3 db test.db -key {}



Regards,

Noah Hart

-- 
View this message in context: 
http://old.nabble.com/Error-in-pcache.test-with-encryption-codec-tp28952275p28952275.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] Error in vacuum2.test with encryption codec

[Noah Hart]

vacuum2 returns incorrect results when using a codec with an encryption
routine

Suggested changes: 

line 31:
+# Disable encryption on the database for this test.
sqlite3 db test.db -key {} 
  
Change line 70
- sqlite3 db2 test.db
+sqlite3 db2 test.db -key {}

Noah Hart

-- 
View this message in context: 
http://old.nabble.com/Error-in-vacuum2.test-with-encryption-codec-tp28952227p28952227.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] Error in filefmt.test with encryption codec

[Noah Hart]

filefmt return incorrect results when using a codec with an encryption
routine

filefmt-1.1...
Expected: [53514C69746520666F726D6174203300]
 Got: [19C10CA15F9AB24A612E680FE7816B25]

This happens because the sqlite db command in TCL opens the database with
the option -key {xyzzy}
when compiled with a codec

Suggested fix

do_test filefmt-1.1 {
  sqlite3 db test.db 
  db eval {CREATE TABLE t1(x)}
  db close
  hexio_read test.db 0 16
} {53514C69746520666F726D6174203300}

change all the   "sqlite3 db test.db" type lines to

  sqlite3 db test.db -key {}

This allows the test to run correctly.

If there is a better method for running these tests with an encryption
codec, please let me know


Noah Hart

-- 
View this message in context: 
http://old.nabble.com/Error-in-filefmt.test-with-encryption-codec-tp28952009p28952009.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] Error in jrnlmode2.test with encryption codec

[Noah Hart]

jrnlmode2-2.4 & 2-2.6, return incorrect results when using a codec with an
encryption routine
Expected: [0 {1 2 3 4 5 6}]
 Got: [1 {file is encrypted or is not a database}]

This happens because the sqlite db command in TCL opens the database with
the option -key {xyzzy}
# Use the pager codec if it is available
#
if {[sqlite3 -has-codec] && [info command sqlite_orig]==""} {
  rename sqlite3 sqlite_orig
  proc sqlite3 {args} {
if {[llength $args]==2 && [string index [lindex $args 0] 0]!="-"} {
  lappend args -key {xyzzy}
}
uplevel 1 sqlite_orig $args
  }
}


However, when additional db commands are defined, there is no key added when
a codec exists

Suggested correction for 2-2.4 & 2-2.6

do_test jrnlmode2-2.4 {
  if {[sqlite3 -has-codec]} { 
sqlite3 db2 test.db -readonly 1 -key {xyzzy} 
  } else {
sqlite3 db2 test.db -readonly 1
  }
  catchsql { SELECT * FROM t1 } db2
} {0 {1 2 3 4 5 6}}



do_test jrnlmode2-2.6 {
  if {[sqlite3 -has-codec]} { 
sqlite3 db2 test.db -readonly 1 -key {xyzzy} 
  } else {
sqlite3 db2 test.db -readonly 1
  }
  catchsql { SELECT * FROM t1 } db2
} {0 {1 2 3 4 5 6}}


Thanks,

Noah

-- 
View this message in context: 
http://old.nabble.com/Error-in-jrnlmode2.test-with-encryption-codec-tp28951848p28951848.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] Error in minmax3.test with SQLITE_HAS_CODEC

[Noah Hart]

minmax3.test has a routine at line 23

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
# 
proc set_file_format {newval} {
  hexio_write test.db 44 [hexio_render_int32 $newval]
  set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
  incr schemacookie
  hexio_write test.db 40 [hexio_render_int32 $schemacookie]
  return {}
}

Clearly if the database is encrypted, then plugging data into bytes 40-44
will corrupt page 1.

Question:

Should this test be commented out with a test like :
if {![sqlite3 -has-codec]}  ...

or, should a codec implementation leave the schema bytes on page 1 alone?


Thanks,

Noah

-- 
View this message in context: 
http://old.nabble.com/Error-in-minmax3.test-with-SQLITE_HAS_CODEC-tp28951238p28951238.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] BUG REPORT: alter2 test does not run with SQLITE_HAS_CODEC

[Noah Hart]

Line 25 of alter2.test has the following comment:

# These tests do not work if there is a codec. 
#
#if {[catch {sqlite3 -has_codec} r] || $r} return

Either the comment on line 25 is incorrect, 
or should line 27 should be uncommented

Please advise,

Noah 



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking under various Windows versions

[Noah Hart]

Robert, 

Makes sense.  Some background ... I'm again looking into how to handle
locking under Silverlight, and was looking into the WINCE method for
ideas.

The isolatedstoragefilestream under NET.4 claims to support the Lock
method, (see
http://msdn.microsoft.com/en-us/library/system.io.isolatedstorage.isolat
edstoragefilestream_methods%28v=VS.100%29.aspx) but doesn't actually
seem to be there under VS2010 and Silverlight 4

Might need to go the mutex route

Noah


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Robert Simpson
Sent: Friday, April 16, 2010 3:57 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Locking under various Windows versions

WinCE has no lockfile support, so it's fudged on the device by means of
a
shared memory block to handle the locking.  If you open a SQLite
database on
a network share from a CE device, then it will not be able to use the
network share's locking mechanisms.  In short, don't do it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Noah Hart
Sent: Friday, April 16, 2010 3:48 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Locking under various Windows versions

I am trying to determine if SQLite holds an exclusive lock on a 
database opened under Windows-CE, will that lock be honored 
by a connection opened under another version of Windows?

What about the opposite case?  When  the database is first 
opened under Windows 7, an exclusive lock is acquired, then
a connection to the database is made under WindowsCE.

I've read the os_win.c code and am not clear how it is handled in this
type of mixed mode


Regards

Noah Hart




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If
you
are not the addressee or authorized to receive this for the addressee,
you
must not use, copy, disclose, or take any action based on this message
or
any information herein. If you have received this message in error,
please
advise the sender immediately by reply e-mail and delete this message.
Thank
you for your cooperation.


___
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] Locking under various Windows versions

[Noah Hart]

I am trying to determine if SQLite holds an exclusive lock on a 
database opened under Windows-CE, will that lock be honored 
by a connection opened under another version of Windows?

What about the opposite case?  When  the database is first 
opened under Windows 7, an exclusive lock is acquired, then
a connection to the database is made under WindowsCE.

I've read the os_win.c code and am not clear how it is handled in this
type of mixed mode


Regards

Noah Hart




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REQUEST: Implement APDB like PRAGMA for CHS access

[Noah Hart]

Maybe SQLite could implement a PRAGMA to use the 

physical memory locator (CHS# - Cylinder, Head, Sector) as data-access
keys as well

 

Could even be faster than the INTEGER PRIMARY KEY now used.

 

What do you think?

 

http://thedailywtf.com/Articles/Announcing-APDB-The-Worlds-Fastest-Datab
ase.aspx

 

Regards,

 

Noah

 




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG 3.6.23 CorruptE.test needs ifcapable oversize_cell_check

[Noah Hart]

(Resent to sqlite-users)

 

CorruptE.test gives different results with and without
SQLITE_ENABLE_OVERSIZE_CELL_CHECK 

 

Coding similar to the following from corrupt7.test needs to be added

 

# Deliberately corrupt some of the cell offsets in the btree page

# on page 2 of the database.

#

# The error message is different depending on whether or not the

# SQLITE_ENABLE_OVERSIZE_CELL_CHECK compile-time option is engaged.

#

ifcapable oversize_cell_check {

 

Regards,

 

==

Noah Hart

Sr. Systems Analyst

Lipman Ins Admin, Inc.

'510-796-4676 x266

==

 




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: shell.c ATTACH parses filename poorly

[Noah Hart]

There may be a documentation bug

According to http://sqlite.org/lang_attach.html
the format of the attach command is 
ATTACH [DATABASE]  AS 

Jay reports below that the filename can be an expression.

Looks like the documentation needs to be clarified.

REQUEST:  Add explanation to Syntax Diagrams For SQLite showing 
what the legal values are for the non bolded bubbles

Regards,

Noah

-Original Message-

> 
> I know that the work-a-round is to enter the filename in quotes.  
> 
> My question is why is the attach statement parsing for a column at
all.

  The format of the ATTACH command is:
 
ATTACH [DATABASE]  AS 

  It happens that the expression needs to be a text value for the
  command to work properly, but the parser doesn't know that.

  A bare character-string given as an expression is considered a
  column reference (consider SELECT or WHERE clauses).  However,
  the ATTACH command has no table/column context, so the column
  name is unresolved, i.e. no such column.



-j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ANN: C#-SQLite 3.6.22

[Noah Hart]

C#-SQLite has been updated to release 3.6.22 and is now ready for review.  

It currently runs 30428 tests with 0 errors.  There are currently issues
with recursive triggers so the project is compiled with SQLITE_OMIT_TRIGGER

The project is located at http://code.google.com/p/csharp-sqlite/

Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system

SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc


Enjoy,

Noah Hart 
-- 
View this message in context: 
http://old.nabble.com/ANN%3A-C--SQLite-3.6.22-tp27133897p27133897.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] BUG REPORT: 3.6.21;

[Noah Hart]

Using the command line tools from the website
3.6.18 reports the error correctly;

SQLite version 3.6.18
sqlite> PRAGMA recursive_triggers = on;
sqlite> CREATE TABLE t5 (a primary key, b, c);
sqlite> INSERT INTO t5 values (1, 2, 3);
sqlite> CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN UPDATE OR IGNORE
t5 SET a = new.a, c = 10;   END;
sqlite> UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 ;
SQL error: too many levels of trigger recursion


3.6.21 does not handle it properly

SQLite version 3.6.21
sqlite> PRAGMA recursive_triggers = on;
sqlite> CREATE TABLE t5 (a primary key, b, c);
sqlite> INSERT INTO t5 values (1, 2, 3);
sqlite> CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN UPDATE OR IGNORE
t5 SET a = new.a, c = 10;   END;
sqlite> UPDATE OR REPLACE t5 SET a = 4 WHERE a = 1 ;
Error: SQL logic error or missing database



BACKGROUND:
I was trying to get the test triggerC-1.11 in triggerC.test to work
correctly; 
The assert in btree.c at line 3699 in the routine sqlite3BtreeRollback
was 
failing returning a 2000, rather than 0

assert( countWriteCursors(pBt)==0 );


Regards,

Noah Hart



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG Report -- schema.test does not check for authorization in build

[Noah Hart]

Test schema-13.1 fails with 
Error: {authorization not available in this build} 

Test needs to be bracket with
ifcapable auth {

do_test schema-13.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM sqlite_master" -1 dummy]
  db function hello hello
  db function hello {}
  db auth auth


db auth fails because tclsqlite.c has

#ifdef SQLITE_OMIT_AUTHORIZATION
Tcl_AppendResult(interp, "authorization not available in this
build", 0);
return TCL_ERROR;
#else



Regards,

Noah Hart



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG Report -- check.test has inconsistent test numbering

[Noah Hart]

Documentation bug -- annoyance level

Check.test has test # 4.3 listed twice

do_test check-4.3 {
  execsql {
UPDATE t4 SET x=4, y=3;
SELECT * FROM t4
  }
} {4 3}
do_test check-4.3 {
  execsql {
UPDATE t4 SET x=12, y=2;
SELECT * FROM t4
  }
} {12 2}

Regards,

Noah Hart



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in test file fuzz3.test

[Noah Hart]

This test file contains the tests

 

do_test fuzz2-3.0 {

  fuzzcatch {CREATE TRIGGER ...

do_test fuzz2-3.1 {

  fuzzcatch {CREATE TRIGGER ...

do_test fuzz2-3.2 {

  fuzzcatch {CREATE TEMP TRIGGER ...

 

TRIGGERS are unavailable when compiled with SQLITE_OMIT_TRIGGER

 

Test needs to be bracketed with 

 

ifcapable {trigger} {  # Only do the following tests if triggers are
enable

 

Regards,

 

Noah Hart

 




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in test file tkt-3fe897352e.test

[Noah Hart]

This test file has a call to hex_to_utf16be 

which is unavailable when compiled with SQLITE_OMIT_UTF16

 

Test needs to be bracketed with ifcapable {utf16} 

 

Regards,

 

Noah Hart

 




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Testfixture test file may be misnamed

[Noah Hart]

Was it deliberate that the following file was created with a .txt rather than
.test extension?

test/tkt-d82e3f3721.txt

Regards,

Noah Hart
-- 
View this message in context: 
http://old.nabble.com/Testfixture-test-file-may-be-misnamed-tp26752815p26752815.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

Re: [sqlite] ynVar

[Noah Hart]

The source from 12/7 do show
#if SQLITE_MAX_VARIABLE_NUMBER<=32767
typedef i16 ynVar;
#else
typedef int ynVar;
#endif

Regards, Noah 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Lauren Foutz
Sent: Friday, December 11, 2009 11:40 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] ynVar

In src/sqliteInt.h there is the following code fragment:
/*
** The datatype ynVar is a signed integer, either 16-bit or 32-bit.
** Usually it is 16-bits.  But if SQLITE_MAX_VARIABLE_NUMBER is greater
** than 32767 we have to make it 32-bit.  16-bit is preferred because
** it uses less memory in the Expr object, which is a big memory user
** in systems with lots of prepared statements.  And few applications
** need more than about 10 or 20 variables.  But some extreme users want
** to have prepared statements with over 32767 variables, and for them
** the option is available (at compile-time).
*/
#if SQLITE_MAX_VARIABLE_NUMBER<=32767
typedef i64 ynVar;
#else
typedef int ynVar;
#endif


The code and the comments seem to imply that ynVar should be i16 instead

of i64.  Is this a mistake?  (I ask because having ynVar be i64 causes a

lot of warnings in Visual Studios).

Lauren
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation correction

[Noah Hart]

vdbe.c line 3217
/* The input value in P3 might be of any type: integer, real, string,
** blob, or NULL.  But it needs to be an integer before we can do
** the seek, so covert it. */
 ==


s/b convert

Regards,

Noah Hart
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most wanted features of SQLite ?

[Noah Hart]

Stored Procedures

Noah 


Simon Slavin-2 wrote:
> 
> So if you had a team of programmers to write something like SQLite  
> which didn't have the drawbacks SQLite has, which drawbacks would you  
> identify ?  I'm asking not about minor faults with specific SQLite  
> library calls, but about the sort of things which require rewriting  
> from the ground up.  The ones that seem to come up most often here are
> 
> * Some sort of synchronisation support
> * Support for multiple concurrent clients/processes
> * Unicode support from the ground up
> 
> Please note: I am not suggesting that any of these problems are easy  
> to solve.  I'm just interested in what problems people want solved.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Most-wanted-features-of-SQLite---tp25514570p25515213.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] ANN: C#-SQLite 3.6.17

[Noah Hart]

C#-SQLite has been updated to release 3.6.17 and is now ready for review.  

It currently runs 30992 tests with 0 errors, but still has issues with 4
tests.

The project is located at http://code.google.com/p/csharp-sqlite/

Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system

SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc


Enjoy,

Noah Hart 
-- 
View this message in context: 
http://www.nabble.com/ANN%3A-C--SQLite-3.6.17-tp25070237p25070237.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

Re: [sqlite] ANN: C#-SQLite 3.6.16

[Noah Hart]

Kosenko Max wrote:
> 
> 
> Noah Hart wrote:
>> C#-SQLite is now ready for review.  The project is located at
>> http://code.google.com/p/csharp-sqlite
> I think this name much better than sql-sharp. 
> I've posted a question on SQLite ADO.NET forum
> http://sqlite.phxsoftware.com/forums/p/1879/7971.aspx about supporting
> your implementation in ADO.NET Provider. In Silverlight that require some
> adjustments to your code (not much) and ADO Provider will need to have
> parts of System.Data included as a stubs.
> 
> From your point of view - which parts you haven't yet ported?
> 
The big things are:  FTS, VIRTUAL TABLES, INCRBLOB

And there are still 9 tests to review:  

I need to determine if the test errors are errors in the port, errors in the
TCL, or the test is not appropriate for C#


>From http://code.google.com/p/csharp-sqlite/wiki/CompilerOptions

I am currently compiling with the following OMIT options:

* SQLITE_MUTEX_OMIT
* SQLITE_OMIT_AUTHORIZATION
* SQLITE_OMIT_GET_TABLE
* SQLITE_OMIT_INCRBLOB
* SQLITE_OMIT_LOOKASIDE SQLITE
* OMIT_SHARED_CACHE
* SQLITE_OMIT_UTF16
* SQLITE_OMIT_VIRTUALTABLE 

I have not ported support for

* SQLITE_ENABLE_FTS3
* SQLITE_ENABLE_ICU
* SQLITE_ENABLE_MEMORY_MANAGEMENT
* SQLITE_ENABLE_RTREE 

Noah
-- 
View this message in context: 
http://www.nabble.com/ANN%3A-C--SQLite-3.6.16-tp24839242p24858586.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] ANN: C#-SQLite 3.6.16

[Noah Hart]

C#-SQLite is now ready for review.  The project is located at
http://code.google.com/p/csharp-sqlite/

This is SQLite ver 3.6.16 ported into managed code, written in C#

Please keep in mind the following:

* C#-SQLite is an independent reimplementation of the SQLite software
library
* This is not an official version of SQLite
* Bugs should not be reported to the SQLite.org ticket tracking system 

SQLite® is a registered trademark of Hipp, Wyrick & Company, Inc


Enjoy,

Noah Hart

-- 
View this message in context: 
http://www.nabble.com/ANN%3A-C--SQLite-3.6.16-tp24839242p24839242.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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

And in the Window's there are ...

For example:

System.Data.SQLite
An open source ADO.NET provider for the SQLite database engine
http://sqlite.phxsoftware.com/

http://code.google.com/p/sqlite-ng/
sqlite-ng A fork of SQLite with more community involvement

SQLite-ng is a fork of SQLite with the goal of providing 100% API and ABI
compatibility.


sqlite3pp
SQLite3++ - C++ wrapper of SQLite3 API
It makes SQLite3 API more friendly to C++ users. It supports almost all of
SQLite3 features using C++ classes such as database, command, query, and
transaction. The query class supports iterator concept for fetching records.


sqlitevb
SQLiteVB: public functions modified to attend VB standards of compatibility


Noah


On Sun, Aug 2, 2009 at 7:57 AM, Noah Hart<n...@lipmantpa.com> wrote:
>
> Richard sent me a gentle reminder that read in part:
>
> 
> Please also note that the SQLite source code is in the public domain, but
> the "SQLite" name is not.  SQLite is a registered trade mark.  If I don't
> defend the trademark, then I could lose it.  So, I really do need to
> insist
> that you not use the name "SQLite" for your product.
> 
>
>
> This is an excellent reminder, and until this is done, I've removed access
> to the source code and will terminate this google code project.  I'll post
> an announcement in the future when the new project is ready.


A very valid point from DRH re. protecting the sanctity of SQLite, the
(tm), but the horse may have already left the stable. A quick search
on macupdate.com reveals the following products with ‘SQLite’ in their
name (with the indicated capitalization) --

-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24782171.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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

The license is the same as SQLite, I'm waiting on google to change the
project to PD since that is not one of the canned choices.

Noah

It's a pity news. I hoped Dr. can think about even somehow supporting your
project.

I don't know why he insists on that (he actually can answer for himself
here) while there are a lot of SQLite based projects with that name usage.
May be that's because of your license?

Max.

-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24782158.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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

Richard sent me a gentle reminder that read in part:


Please also note that the SQLite source code is in the public domain, but
the "SQLite" name is not.  SQLite is a registered trade mark.  If I don't
defend the trademark, then I could lose it.  So, I really do need to insist
that you not use the name "SQLite" for your product.



This is an excellent reminder, and until this is done, I've removed access 
to the source code and will terminate this google code project.  I'll post
an announcement in the future when the new project is ready.

Also, if anyone has an ideal about what to call it ...
Regards,

Noah Hart   



Noah Hart wrote:
> 
> I am pleased to announce that the C# port is done to the point where
> others can look at it.
> 
> The project is located at http://code.google.com/p/sqlitecs
> 
> Enjoy,
> 
> Noah Hart
> 

-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24777619.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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

This is not a driver, dll, or wrapper.  This is a port of the underlying
SQLite software.

Noah

Yves Goergen wrote:
> 
> On 01.08.2009 02:14 CE(S)T, Noah Hart wrote:
>> I am pleased to announce that the C# port is done to the point where
>> others
>> can look at it.
>> 
>> The project is located at http://code.google.com/p/sqlitecs
> 
> Excuse me, but what's the difference of this to SQLite ADO.NET at
> http://sqlite.phxsoftware.com/ ?
> 
> -- 
> Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de>
> Visit my web laboratory at http://beta.unclassified.de
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24769515.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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

Max, I missed posting the remaining errors

Current results ... 
9 errors out of 30054 tests

Still skipping about 9 additional tests

Noah

Kosenko Max wrote:
> 
> Wow, that's impressive.
> 
> And very interesting that you've gained 3x-5x performance gain.
> Don't make this project educational only. I'm sure you'll find additional
> contributors. Just recently Miguel de Icaza was asking for line by line
> port of SQLite to C#.
> 
> Great achievement that all tests are passing now.
> 
> Max.
> 
> 
> Noah Hart wrote:
>> 
>> I am pleased to announce that the C# port is done to the point where
>> others can look at it.
>> 
>> The project is located at http://code.google.com/p/sqlitecs
>> 
>> Enjoy,
>> 
>> Noah Hart
>> 
> 

-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24769505.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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

The compiled version of the SQLite3.exe is 528KB vs 506KB for the official
release

I haven't checked the footprint while it is running.  That would be depend
on what it is doing ...

Noah

Fred Williams wrote:
> 
> 
> Hummm... Guess there is a reason there are no implementations of C#
> external
> to the Mickeysoft world :-)
> 
> Guess if I had a lot of time to kill I could port it to Delphi...
> 
> BTW, what's the memory footprint?
> 
> Fred
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Kosenko Max
> Sent: Saturday, August 01, 2009 6:22 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: SQLite 3.6.16.C#
> 
> 
> 
> Seems like I've misunderstood your performance results. And they are
> 3-5times
> slower than original...
> 
> -
> Best Regards.
> Max Kosenko.
> --
> View this message in context:
> http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24768252.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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24769476.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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

Yes, but still decent speed

All Results are in Rows per Second

TestSQLite3C#   SQLite3
Inserts 300K1300K
Selects 1500K   8450K
Updates 60K 300K
Deletes 250K700K 

Noah

Kosenko Max wrote:
> 
> Seems like I've misunderstood your performance results. And they are
> 3-5times slower than original... 
> 

-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24769455.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

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Noah Hart]

Cory, There was no attempt at optimization in this initial port.  SQLite does
a lot of char/byte/string manipulation as well as and passing parameters by
address in the middle of an array.  But I ended up having to do a lot of
byte buffer copying.  MY guess is that is where most of the time is spent.

Regards,

Noah

On Sat, Aug 1, 2009 at 4:21 AM, Kosenko Max wrote:
>
> Seems like I've misunderstood your performance results. And they are
> 3-5times
> slower than original...
>

This could be for a number of reasons.  For one, it uses p/invoke for
a number of things, which can be pretty slow and is not portable.
Another, it is basically a direct port of the C code -- it is using
goto all over the place, which probably hampers optimization as
opposed to exceptions.

-- 
Cory Nelson
http://int64.org

-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24769442.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] ANN: SQLite 3.6.16.C#

[Noah Hart]

I am pleased to announce that the C# port is done to the point where others
can look at it.

The project is located at http://code.google.com/p/sqlitecs

Enjoy,

Noah Hart
-- 
View this message in context: 
http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24764742.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] Port to C#; Progress report

[Noah Hart]

A progress report because people keep asking ...
 
Yes, it is Version 3.6.16 

I am currently compiling with the following OMIT options:

DEBUG_CLASS_NONE
TRACE
SQLITE_ASCII
SQLITE_DEBUG
SQLITE_ENABLE_COLUMN_METADATAS
SQLITE_ENABLE_OVERSIZE_CELL_CHECK
SQLITE_SYSTEM_MALLOC
SQLITE_TEST
VDBE_PROFILE_OFF


I am currently compiling with the following OMIT options:
SQLITE_MUTEX_OMIT
SQLITE_OMIT_AUTHORIZATION 
SQLITE_OMIT_GET_TABLE 
SQLITE_OMIT_INCRBLOB 
SQLITE_OMIT_LOOKASIDE 
SQLITE_OMIT_SHARED_CACHE 
SQLITE_OMIT_UTF16 
SQLITE_OMIT_VIRTUALTABLE 

I have not ported support for
SQLITE_ENABLE_FTS3
SQLITE_ENABLE_ICU
SQLITE_ENABLE_MEMORY_MANAGEMENT
SQLITE_ENABLE_MEMSYS3
SQLITE_ENABLE_MEMSYS5
SQLITE_ENABLE_RTREE

I now get 0 errors out of 28079 tests in the test harness;
Only 35 more tasks before initial release;

Initial project page is now setup at http://code.google.com/p/sqlitecs/

Full MSVC C# Solution will be released with 2 projects; the shell and the
testharness;
The C# TCL port has also been included as part of the testharness project

I am currently waiting for google to approve changing licensing terms 
from " Artistic License/GPL" to "Public Domain" 


I would anticipating posting the full C# code in the next few weeks

Regards,

Noah Hart

-- 
View this message in context: 
http://www.nabble.com/Pros-and-cons-of-various-online-code-sharing-sites-tp24413069p24655194.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

Re: [sqlite] Pros and cons of various online code sharing sites

[Noah Hart]

>How do you plan to statically link your new library into multiple
projects? 
>Are you planning to make a copy of the code files for each C# project
that uses Sqlite? 

I use the "Add as Link". 

Again my goal was not to create a reusable library, but rather as a
programming challenge to learn C#


>I don't think you'll be able to do this for assemblies written in any
other language, 
>e.g. VB.NET. In fact, people using these languages are basically locked
out of your library, 
>at least without a wrapper to make it a DLL (I think).

Correct, those who want to use it in other languages, can use existing
dlls, which work just fine.  
For example, the  SQLite ADO.NET Provider from sqlite.phxsoftware.com is
an excellent solution

>Also, this approach (making copies of the code) introduces parallel
maintenance issues, esp. 
True

>There is also the "Add as Link" feature, which is similar to #include
in C++. 
I had to recreate TCL for C# as well in order to run the test harness;
So I use the Add as Link for both the test harness as well as the shell
application


>I don't mean to defy your disclaimer; these are topics of some
practical importance to me. 
>I deal with C++ / Sqlite apps daily that may eventually need to be
(rapidly) transitioned to C#. 
>My growing sentiment is that .NET does not really lend itself to
this... I guess I want "#include" from C++.

You can call SQLite from C# by using existing wrappers.  Again, my goal
was to learn OOP using something I could use.

>Another area I miss "#include" is in declaring simple constants; 

YUP -- this was a real challenge in the porting.

Regards,

Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pros and cons of various online code sharing sites

[Noah Hart]

BACKGROUND:
In order to learn C#, I have spent the last year converting the source code
of SQLite3 from C to C#. As of version 3.6.16, it is now ready to release in
the wild. I don't want to self-host CVS or some other repository, so I am
trying to decide where to post the code.

My goals for this are simple. I just want to allow people to download the
source code, submit feedback, bug reports, etc.

I have looked at sourceforge, googlecode, codeproject and a few others.
However, while they all look fine, I not sure what works well in the real
world.

Any suggestions from your personal experience would be appreciated.

DISCLAIMER:  
This port was done simply to learn C#, and then embed SQLite3 into a C#
application without the need for a dll.  Please no criticism for doing this,
or a discussion of if it was advisable to port SQLite3 to C# in the first
place.

Also, I know this is off-topic, but I am really interested in your
suggestions.  If you want to flame me, please reply directly, and not to the
list.

Regards,

Noah Hart

-- 
View this message in context: 
http://www.nabble.com/Pros-and-cons-of-various-online-code-sharing-sites-tp24413069p24413069.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

Re: [sqlite] error in documentation of SELECT?

[Noah Hart]

Just because the syntax allows it, doesn't mean that it will be useful

SELECT '1', sqlite_version() as A where A < 'a' group by 1 order by 1

Noah

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Monday, May 18, 2009 11:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] error in documentation of SELECT?

I didn't notice it earlier and now I'm a bit surprised. Can I ask a
more elaborate example which will include WHERE and/or GROUP BY but
not include FROM?

Pavel




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Changes to closed tickets.

[Noah Hart]

General question:  

If I append a report to a closed ticket, will developers look at it, or
should I open a new ticket?

For example:

Ticket #3802 was closed with "Unable to reproduce this problem"

I've added new information to allow the developers to reproduce.  Will
it be looked at, or should I resubmit the ticket.


Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

[Noah Hart]

Nico wrote:

>> Number 1, the database is no longer portable. The only solution to
>> this is to include the functionality in the core.

>Yes but, there is no single Unicode collation.  Collation is
>language-specific, even when using Unicode.  Thus you're asking that
>SQLite3 have a plethora of built-in Unicode collations.

I do understand the issues, but I think you misunderstand my intent.
I'm not asking that SQLite3 have any built-in Unicode collations.
Rather my point is that it would be of benefit if SQLite would have 
some built in mechanism for a rule-based collation.

>> more general solution would be to design it around a sqlite_collation
>> master table in the database. An application developer (not the
SQLite
>> team) would be responsible to define and populate their "user
defined"
>> collation.

>It's more complex than you think.  You need to keep Unicode
>normalization forms in mind and you need to deal with decomposed
>characters no matter what (since not all future additions to Unicode
>will include pre-composed forms, and NFC is closed to new pre-composed
>forms anyways), which means multi-codepoint sequences need to be
>accounted for in the collation.  You'd very quickly realize that it'd
be
>even simpler for you if SQLite3 just had built-in collations for all
the
>relevant languages.  And once more SQLite3 would no longer be light.
>Perhaps when built with ICU SQLite3 could make it trivial to load any
of
>those collations.

I'm not suggesting that it is not a complex problem.  

SQLite does a very nice job of taking SQL statements and decomposing 
them into a form that a byte engine can process.  I feel that the 
collations problem could also be addressed in this way with a rules 
based table along with some underlying data that a application developer

(not the SQLite development team) could populate.  

If the "user defined" collation wouldn't work for a particular 
application, then it would not need to be used at all.

If Richard feels that this is something that has no value, I'm more 
than willing to drop the whole idea.  

Regards,

Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

[Noah Hart]

Igor Tandetnik writes:

>This is not quite true. You say custom functions are supported: then you can 
>do ORDER BY sortkey(textField), >with a suitably defined sortkey() function 
>(see strxfrm, LCMapString). You can't however build indexes using >such a 
>function, something you can do with a collation.

True, that is how I current accomplish it, but it is very inefficient, since 
indexes cannot be used, and a javascript function end up being called for the 
row by row comparison.

>Sorting in many locales is not as simple as suitably ordering individual 
>Unicode characters. E.g. in German >phonebook order, letter ö (small o with 
>umlaut aka diaresis) sorts as if it were two letters oe, that is od < ö >< of. 
>In French, strings are compared ignoring diacritics first, then ties are 
>broken by considering diacritics >right-to-left. In Spanish traditional sort, 
>a pair ch sorts as if it were a single letter between c and d. Even >in 
>English, you would often want to sort co-op and coop, or cant and can't, in 
>such a way that they are kept >together.

>Consider also things like combinig diacritics.

Again I agree.  In fact, with my application, I am not only dealing with 
UTF-16, but also there is the issue of combined letter forms.

This is not a trivial topic, and I don't have a proposed solution.  However, I 
feel this thread is worth pursuing.  There are a lot of smart people on this 
list and who use SQLite.

If the SQLite developers would be willing to open a discussion about embedding 
this functionality, maybe some brainstorming could come up with an acceptable 
solution.


Regards, Noah 






CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

[Noah Hart]

I've been reading and thinking about this topic for a while, and would like to 
add my thoughts.

I realize that we don't "vote" on features, but I feel that this type of idea 
has merit.

It is true, that SQLite has user defined collations, and a extension could be 
registered, but the problem with that is twofold:

Number 1, the database is no longer portable. The only solution to this is to 
include the functionality in the core.

Number 2, your platform may not support the sqlite3_create_collation interface. 
For example, Firefox now includes SQLite. Unfortunately, while Firefox supports 
user defined functions, their implementation does not support user defined 
collations.  

Someone commented that the US lives in a 7-bit world.  
This means that the other 6 billion people on the planet do not.

This creates a real problem for me.  I am writing a foreign language Firefox 
extension, and the issue of sorting is critical, since Firefox uses Unicode 
sorting, which does not "sort" (based on my rules) correctly.   This means I 
have no way to correct the sorting, except in the display routines.

That being said, I would not limit this feature to 8bit locales.  A more 
general solution would be to design it around a sqlite_collation master table 
in the database. An application developer (not the SQLite team) would be 
responsible to define and populate their "user defined" collation.

True, if I call my "user defined" collation sequence "COLLATE_PN", someone else 
might use the same identifier. Their lookup table even be different than the 
"COLLATE_PN" I am using.  However, I feel that is a deployment problem, not a 
development issue.  

It is unlikely that in a single SQLite database, we would have 2 user tables, 
with the same "user defined" collation identifier, but differing collation 
lookups.



I would encourage the developers to at least consider such a feature, after 
all, they did recently add the built-in RTRIM collation.


Regards,

Noah Hart


-Original Message-
- Please, let us try to bring down the discussion to the intended solution - a 
simple way to define and use a "user defined" collating for 8 bit ASCII 
characters! 
As said before, the proposal doesn't rely on locales. If a user needs a german 
collating sequence with sort order for phone-book, dictionary or german upper 
case, it's up to the user to supply a simple 256 byte string with the 
wanted/needed sort order for that index. It could be beneficial to all users 
with the need for special sorting requirements and almost no impact to cpu 
cycles, even on small systems. 

There sould be many users with 8bit ASCII locales requirements that would love 
such an extension.
 



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this expected behavoir, or should I open a ticket?

[Noah Hart]

Never mind, brain glitch.

Sorry for the waste of bandwidth.  Just too late on a Friday afternoon.

Noah

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Friday, February 06, 2009 4:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is this expected behavoir, or should I open a
ticket?

Noah Hart <n...@lipmantpa.com> wrote:
> SQL Version: 3.6.10
>
> CREATE TABLE T1(a, b);
> INSERT INTO T1 VALUES(C1_A, randomblob(1));
>
> SQL error:no such column: C1_A
>
> I know that I can quote the value C1_A, but why is that necessary?

Why do you believe it shouldn't be necessary? C1_A is an identifier, not

a string literal. What is this identifier supposed to name?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this expected behavoir, or should I open a ticket?

[Noah Hart]

SQL Version: 3.6.10

CREATE TABLE T1(a, b); 
INSERT INTO T1 VALUES(C1_A, randomblob(1));

SQL error:no such column: C1_A

I know that I can quote the value C1_A, but why is that necessary?

Thanks,

Noah







CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT issue with SQLite 3.6.10

[Noah Hart]

Please supply the SQL TEXT for the TABLE and the VIEW;

Noah

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of KurDtE
Sent: Tuesday, January 27, 2009 8:41 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SELECT issue with SQLite 3.6.10


First, thank you both for your help !

I'm getting pretty confused :
When I execute the query SELECT "GENERAL.ID" FROM VIEW_GENERAL; on
SQLite
Administrator (which uses an older version of SQLite than 3.6.10),
everything works fine, but when I try the same query with SQLite 3.6.10
on
command line, I get :

"GENERAL.ID"
"GENERAL.ID"
"GENERAL.ID"
"GENERAL.ID"
"GENERAL.ID"

meaning that it processes "GENERAL.ID" as text and not as a column name
...

Weird isn't it ?


D. Richard Hipp wrote:
> 
> 
> On Jan 27, 2009, at 10:08 AM, Fred Williams wrote:
> 
>>
>> Should not the GENERAL.ID be enclosed in double quotes?  Or did I  
>> misread
>> the SQL Standard?
> 
> You can use double-quotes to conform to the SQL standard.  But SQLite

> also allows some non-standard quoting mechanisms for compatibility  
> with other database engines.   [...] is used by MS SQL Server.   `...`

> is used by MySQL.
> 
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context:
http://www.nabble.com/SELECT-issue-with-SQLite-3.6.10-tp21682817p2168908
2.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



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.6.8+ breaks YUM

[Noah Hart]

Fixed here ---

11:04
Check-in [6186] : Allow recently added keywords 'savepoint' and
'release' to be used as database object names. Just as they could be
prior to 3.6.8. Ticket #3590. 



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tuan Hoang
Sent: Monday, January 19, 2009 11:59 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite 3.6.8+ breaks YUM

Tuan Hoang wrote:
> Hi,
> 
> I've been back-porting SQLite 3.x to CentOS 4.7 for some development 
> work.  I've been taking the SRPMS from koji.fedoraproject.org and 
> rebuilding them.
> 
> All has been fine through v3.6.7 but when I tried to recently upgrade 
> to 3.6.10 (by just updating the SPEC file and rebuilding), the YUM 
> updater no longer works.  In particular the python-sqlite package 
> exits with an error when it tries to read it's cache file (I assume 
> that it's a SQLite DB).  I checked the in-between builds and one of 
> the changes in v3.6.8 has triggered this error.
> 
> Is there anyone else with a similar problem?  FWIW, I've also done 
> this under CentOS 5.2 and it also breaks its YUM too.
> 
> Thanks,
> Tuan
> 
> P.S.  Please reply all since I'm not subscribed to the mailing list.
> 

I did a little more debugging with the yum and it's use of
python-sqlite.  It appears that the database is not corrupt, but rather
that the database can't be created at all.

The attached CREATE TABLE statements work fine with v3.6.7 and before
(at least the ones that I've tried).  As of v3.6.8 up through v3.6.10,
YUM can no longer create these tables.

Did the string "release" suddenly become a keyword?  If so, why?

Tuan



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Assigning REGEX from javascript

[Noah Hart]

Turns out there is a fairly simple solution,   (thanks to Mirnal Kant)

In javascript:

//functions to be created for the db
var smDbFunctions = {
  //for use as where col regexp string_for_re
  // col goes as the second argument
  regexp: {
onFunctionCall: function(val) {
  var re = new RegExp(val.getString(0));
  if (val.getString(1).match(re))
return 1;
  else
return 0;
}
  }
};

after instantiating a SQLite instance:

Database.createFunction("REGEXP", 2, smDbFunctions.regexp);

This does work, see Mirnal's SQLite Manager version 0.4.3 for proof of
concept.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Noah Hart
Sent: Tuesday, January 13, 2009 9:29 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Assigning REGEX from javascript

BACKGROUND:  

Firefox includes SQLite version 3.5.9, it also allows extensions, which
are written in javascript and can call the embedded SQLite engine.

As expected, executing the following SQL statement 'SELECT "TEXT" REGEX
"T*";' gives an error, since there is no REGEX function included.

javascript includes a native regex function.

SQLite allows loadable extensions via SELECT load_extension('filename');

QUESTION:
Is it possible to load a javascript extension which could be registered
to do REGEX?


Regards,

Noah





CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] reading beyond end of file

[Noah Hart]

Just a random thought ... This is new code in pager.c, 
and if Pager->journalOff  is at the end of the file, 
then perhaps it could cause his problem.

**
** To work around this, if the journal file does appear to
contain
** a valid header following Pager.journalOff, then write a 0x00
** byte to the start of it to prevent it from being recognized.
*/
rc = sqlite3OsRead(pPager.jfd, zMagic, 8, jrnlOff);


--- Noah



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Friday, January 16, 2009 3:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] reading beyond end of file
Importance: High


On Jan 16, 2009, at 5:38 PM, Dave Toll wrote:

> Hello list
>
>
>
> I recently upgraded from SQLite 3.6.7 to 3.6.10 and I'm now noticing
> some apparently undesirable behaviour. I'm running on an embedded  
> system
> with my own VFS implementation, and I see in my tests that SQLite is  
> now
> trying to read journal files at an offset beyond the end of the  
> file. Is
> anyone aware of any recent changes that could cause this scenario?
> Should this case be handled within the VFS implementation?
>


For testing this, I added an assert() to the unix VFS which will fire  
if it ever tries to read past the end of a journal file.  Then I ran  
our test suite.  The assert() never fired.  So in our test suite, at  
least, SQLite never reads past the end of a a journal file.

I'm curious to know what you are doing to provoke it to read past the  
end of a journal file.

D. Richard Hipp
d...@hwaci.com



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Assigning REGEX from javascript

[Noah Hart]

BACKGROUND:  

Firefox includes SQLite version 3.5.9, it also allows extensions, which
are written in javascript and can call the embedded SQLite engine.

As expected, executing the following SQL statement 'SELECT "TEXT" REGEX
"T*";' gives an error, since there is no REGEX function included.

javascript includes a native regex function.

SQLite allows loadable extensions via SELECT load_extension('filename');

QUESTION:
Is it possible to load a javascript extension which could be registered
to do REGEX?


Regards,

Noah




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite syntax diagrams

[Noah Hart]

Page http://www.sqlite.org/draft/lang_droptable.html

add "and triggers" after "All indices". 

Add a "caution note"

=> Note: It is possible to drop a table while there are views that
reference that table.


Regards, Noah




-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Friday, October 03, 2008 7:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite syntax diagrams


On Oct 3, 2008, at 10:48 AM, D. Richard Hipp wrote:

> http://www.sqlite.org/draft/syntaxdiagrams.html
> http://www.sqlite.org/draft/syntax.html




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem using random() in queries

[Noah Hart]

I agree with you here.  It is a temporary table that should "FIX" the
values.

Interestingly

select name, RNDValue
from (
select 'name', random() as RNDValue 
)
where RNDValue > 0;

only calls random once and works as expected.

0|Trace|0|0|0|explain select name, RNDValuefrom (select 'name',
random() as RNDValue)where RNDValue > 0;|00|
1|OpenEphemeral|0|2|0||00|
2|Goto|0|17|0||00|
3|String8|0|1|0|name|00|
4|Function|0|0|2|random(-1)|00|
5|MakeRecord|1|2|3||00|
6|NewRowid|0|4|0||00|
7|Insert|0|3|4||08|
8|Integer|0|5|0||00|
9|Rewind|0|16|0||00|
10|Column|0|1|3||00|
11|Le|5|15|3|collseq(BINARY)|6a|
12|Column|0|0|6||00|
13|Column|0|1|7||00|
14|ResultRow|6|2|0||00|
15|Next|0|10|0||00|
16|Halt|0|0|0||00|
17|Goto|0|3|0||00|


Noah

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik
Sent: Friday, August 29, 2008 9:39 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] problem using random() in queries

Noah Hart <[EMAIL PROTECTED]> wrote:
> I would expect that multiple calls to random always return different
> values, even if in the same line.
>
> The fact that we are aliasing random by a column name makes no
> difference to me.

What about this:

select name, RNDValue
from (
select name, random() as RNDValue from names
)
where RNDValue > 0;

Conceptually, the inner select produces a temporary table, and the outer

works on that table. Personally, I found it surprising that random() was

still called twice per row in this case.

Igor Tandetnik 



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem using random() in queries

[Noah Hart]

Richard, Before you "fix" it, I'm not convinced it is broken.

>From MS SQL server

create table _names (N  varchar(5));
insert into _names values('a');
insert into _names values('b');
insert into _names values('c');
insert into _names values('d');
insert into _names values('e');

select N, RAND() as RNDValue from _names 
a   0.301745013642105
b   0.301745013642105
c   0.301745013642105
d   0.301745013642105
e   0.301745013642105


select N, RAND() as RNDValue from _names where RAND() >= 0.5;
a   0.0427909435260437
b   0.0427909435260437
c   0.0427909435260437
d   0.0427909435260437
e   0.0427909435260437

>From the SQL manual:
" When you use an algorithm based on GETDATE to generate seed values,
RAND can still generate duplicate values if the calls to RAND are made
within the interval of the smallest datepart used in the algorithm. This
is especially likely when the calls to RAND are included in a single
batch. Multiple calls to RAND in a single batch can be executed within
the same millisecond. This is the smallest increment of DATEPART. In
this case, incorporate a value based on something other than time to
generate the seed values."


So, it comes down to definition:
I would expect that multiple calls to random always return different
values, even if in the same line.

So the following should give different results for each call to random()

Select random(), random(), random()

And the following as well:

Select random() where random() >0

The fact that we are aliasing random by a column name makes no
difference to me.

Therefore: If the "random" function in sqlite is defined as having a
different value every time it is called, and we explain that this is
true, even if aliased, then the current implementation works correctly
and no work, other than documentation is needed.


Regards,

Noah


-Original Message-


OK.  Even though this kind of thing is probably an abuse of SQL, I'm  
working on ticket #3343.  Just for the record, I'd like everybody to  
know that the following is really, really hard to do correctly and is  
going to require a lot of extra code in SQLite - code that nobody will  
ever use in practice:

   SELECT random()%5 AS x, count(*) FROM tab GROUP BY x;

D. Richard Hipp
[EMAIL PROTECTED]




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generic speed testing

[Noah Hart]

Greg, I intended that sqlite3 be launched without a filename, so this
will give a memory based database and disk I/O would not need to be
considered.

Regards -- Noah

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald
Sent: Friday, August 15, 2008 2:52 PM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: Re: [sqlite] Generic speed testing

Regarding:  "On my AMD system the tests seem to be CPU bound."

On that note, I believe the test creates a 625 megabyte database before
deleting most of it and vacuuming down to a tiny size.   So I guess
included in the test is not just one's disk speed, but how fast one's
operating system can allocate the space and how fragmented the result
is. 



This email and any attachments have been scanned for known viruses using
multiple scanners. We believe that this email and any attachments are
virus free, however the recipient must take full responsibility for
virus checking. 
This email message is intended for the named recipient only. It may be
privileged and/or confidential. If you are not the named recipient of
this email please notify us immediately and do not copy it or use it for
any purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generic speed testing

[Noah Hart]

Ken, 
I'm not really sure what I want to test, or rather what would be a meaningful 
test, so I wanted to start a public discussion about relative performances.

All systems will have limits in some way based on CPU, memory and disk.  But an 
interesting question to me is sqlite whether sqlite is more efficient (whatever 
that means) on one platform vs. another.

This first script was intended to focus on CPU which (with enough data) should 
give a nice baseline for further testing on various platforms.

I should also note that it is intended that sqlite3 be launched without a 
filename, since this will give a memory based database, ignoring all I/O


Regards -- Noah

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ken
Sent: Friday, August 15, 2008 2:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Generic speed testing


Noah, really nice job with this tool. I find it quire useful just to get a 
relative performance comparison between my hardware systems. 

On my AMD system the tests seem to be CPU bound. But this is a DB, My concern 
is that this is really more of a CPU stress test than an I/O  DB test.

That begs the question, What do you really want to test?
CPU    prepare/parse with calculations ? Or select insert/update/delete that Is 
more I/O bound  (what about indexing ?)

Maybe think of creating another test suite that stresses i/o subsystem and does 
not run complex calculations.

HTH
Ken




--- On Fri, 8/15/08, Noah Hart <[EMAIL PROTECTED]> wrote:
From: Noah Hart <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Generic speed testing
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Friday, August 15, 2008, 4:34 PM

Good Point Ken,

Here is version 1.2 with the missing CREATE TABLE statement and some new
PRAGMA settings.

Any suggestions for the CACHE_SIZE setting? 

Also -- I can see how to modify an existing Wiki page, but does anyone
know how to create a new Wiki page, so I can put this script there
rather than repeating it in email?

Noah
SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
5000rpmDisk"

0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
1|Trivial Inserts|16.31|10077696|618.0K Rows/Second
2|Trivial Selects|0.85|10077696|11898.0K Rows/Second
3|Trivial Updates|82.41|10077696|122.0K Rows/Second
4|Trivial Deletes|23.32|10077696|432.0K Rows/Second
5|Insert with calculations|29.02|10077696|347.0K Rows/Second
6|Updates with calculations and longer rows|54.52|10077696|185.0K
Rows/Second

--
-- The author disclaims copyright to this source code.  In place of
-- a legal notice, here is a blessing:
--
--May you do good and not evil.
--May you find forgiveness for yourself and forgive others.
--May you share freely, never taking more than you give.
--
--**
*
-- This file contains code used to implement the performance scripts
--
-- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $


--
-- LEVEL THE PLAYING FIELD WITH PRAGMAs
--

 PRAGMA auto_vacuum = NONE;
 PRAGMA cache_size = 2;
 PRAGMA count_changes = 1;
 PRAGMA encoding = "UTF-8";
 PRAGMA fullfsync = 0;
 PRAGMA journal_mode = DELETE;
 PRAGMA locking_mode = EXCLUSIVE;
 PRAGMA page_size = 1024;
 PRAGMA synchronous = OFF; 
 PRAGMA temp_store = MEMORY;
--
-- A LITTLE SETUP BEFORE WE BEGIN
--

 CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
 INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0);
 CREATE TABLE TEST1 (I INTEGER, T TEXT);
 CREATE TABLE N_1(i INTEGER, t TEXT);
 INSERT INTO N_1 VALUES(1, 't1_');
 INSERT INTO N_1 VALUES(2, 't_22_');
 INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
 INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
 INSERT INTO N_1 VALUES(5, 'text_555_');
 INSERT INTO N_1 VALUES(6, ' ');
 CREATE TABLE N_2(i INTEGER, t TEXT);
 INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
JOIN N_1 N2 CROSS JOIN N_1 N3;


-- TEST 1 
-- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL

 BEGIN;   
 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
2440587.5)*86400 FROM TIMER;
 INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
JOIN N_2 N3;
 COMMIT;

UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes() 
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);

-

Re: [sqlite] Generic speed testing

[Noah Hart]

Good Point Ken,

Here is version 1.2 with the missing CREATE TABLE statement and some new
PRAGMA settings.

Any suggestions for the CACHE_SIZE setting? 

Also -- I can see how to modify an existing Wiki page, but does anyone
know how to create a new Wiki page, so I can put this script there
rather than repeating it in email?

Noah
SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
5000rpmDisk"

0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
1|Trivial Inserts|16.31|10077696|618.0K Rows/Second
2|Trivial Selects|0.85|10077696|11898.0K Rows/Second
3|Trivial Updates|82.41|10077696|122.0K Rows/Second
4|Trivial Deletes|23.32|10077696|432.0K Rows/Second
5|Insert with calculations|29.02|10077696|347.0K Rows/Second
6|Updates with calculations and longer rows|54.52|10077696|185.0K
Rows/Second

--
-- The author disclaims copyright to this source code.  In place of
-- a legal notice, here is a blessing:
--
--May you do good and not evil.
--May you find forgiveness for yourself and forgive others.
--May you share freely, never taking more than you give.
--
--**
*
-- This file contains code used to implement the performance scripts
--
-- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $


--
-- LEVEL THE PLAYING FIELD WITH PRAGMAs
--

 PRAGMA auto_vacuum = NONE;
 PRAGMA cache_size = 2;
 PRAGMA count_changes = 1;
 PRAGMA encoding = "UTF-8";
 PRAGMA fullfsync = 0;
 PRAGMA journal_mode = DELETE;
 PRAGMA locking_mode = EXCLUSIVE;
 PRAGMA page_size = 1024;
 PRAGMA synchronous = OFF; 
 PRAGMA temp_store = MEMORY;
--
-- A LITTLE SETUP BEFORE WE BEGIN
--

 CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
 INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0);
 CREATE TABLE TEST1 (I INTEGER, T TEXT);
 CREATE TABLE N_1(i INTEGER, t TEXT);
 INSERT INTO N_1 VALUES(1, 't1_');
 INSERT INTO N_1 VALUES(2, 't_22_');
 INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
 INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
 INSERT INTO N_1 VALUES(5, 'text_555_');
 INSERT INTO N_1 VALUES(6, ' ');
 CREATE TABLE N_2(i INTEGER, t TEXT);
 INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
JOIN N_1 N2 CROSS JOIN N_1 N3;


-- TEST 1 
-- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL

 BEGIN;   
 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
2440587.5)*86400 FROM TIMER;
 INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
JOIN N_2 N3;
 COMMIT;

UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes() 
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);


-- TEST 2
-- TRIVIAL SELECTS 

 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid
> 0)
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);


-- TEST 3
-- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL

 BEGIN;   
 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') -
2440587.5)*86400 FROM TIMER;
 UPDATE TEST1 SET I=I;
 COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);


-- TEST 4
-- TRIVIAL DELETES

 BEGIN;   
 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Deletes', (julianday('now') -
2440587.5)*86400 FROM TIMER;
 DELETE FROM TEST1 WHERE I >0;
 COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);

--
-- A LITTLE CLEANUP BEFORE WE CONTINUE
--

 DROP TABLE TEST1;
 CREATE TABLE TEST1 (I INTEGER, T 

[sqlite] Generic speed testing

[Noah Hart]

After looking at the code for speed test #1-4, I've decided that a
command line version that does not use TCL would be better.  Wanting to
keep things simple, I'm looking only at inserts, selections and deletes.

I'm not sure if this will even be a valid comparison, so your feedback
and initial numbers are appreciated.

Please reply with your data as follows

SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
5000rpmDisk"

0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second
1|Trivial Inserts|16.12|10077696|625.0K Rows/Second
2|Trivial Selects|0.87|10077696|11650.0K Rows/Second
3|Trivial Updates|81.69|10077696|123.0K Rows/Second
4|Trivial Deletes|22.17|10077696|455.0K Rows/Second
5|Insert with calculations|29.02|10077696|347.0K Rows/Second
6|Updates with calculations and longer rows|54.52|10077696|185.0K
Rows/Second


Regards, Noah

Performance script version 1 follows:

--
-- The author disclaims copyright to this source code.  In place of
-- a legal notice, here is a blessing:
--
--May you do good and not evil.
--May you find forgiveness for yourself and forgive others.
--May you share freely, never taking more than you give.
--
--**
*
-- This file contains code used to implement the performance scripts
--
-- $Id: performance.txt,v 1.0 2008/08/14 12:50:00 nbh Exp $


 PRAGMA SYNCHRONIZATION = FULL;
 PRAGMA locking_mode = EXCLUSIVE;
 PRAGMA synchronous = OFF; 
--
-- A LITTLE SETUP BEFORE WE BEGIN
--

 CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
 INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.0', 0, 1, 0);
 CREATE TABLE TEST1 (I INTEGER, T TEXT);
 CREATE TABLE N_1(i INTEGER, t TEXT);
 INSERT INTO N_1 VALUES(1, 't1_');
 INSERT INTO N_1 VALUES(2, 't_22_');
 INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
 INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
 INSERT INTO N_1 VALUES(5, 'text_555_');
 INSERT INTO N_1 VALUES(6, ' ');
 CREATE TABLE N_2(i INTEGER, t TEXT);
 INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
JOIN N_1 N2 CROSS JOIN N_1 N3;


-- TEST 1 
-- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL

 BEGIN;   
 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
2440587.5)*86400 FROM TIMER;
 INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
JOIN N_2 N3;
 COMMIT;

UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes() 
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);


-- TEST 2
-- TRIVIAL SELECTS 

 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid
> 0)
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);


-- TEST 3
-- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL

 BEGIN;   
 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') -
2440587.5)*86400 FROM TIMER;
 UPDATE TEST1 SET I=I;
 COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);


-- TEST 4
-- TRIVIAL DELETES

 BEGIN;   
 INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Deletes', (julianday('now') -
2440587.5)*86400 FROM TIMER;
 DELETE FROM TEST1 WHERE I >0;
 COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);

--
-- A LITTLE CLEANUP BEFORE WE CONTINUE
--

 DROP TABLE TEST1;
 PRAGMA page_count;
 VACUUM;
 PRAGMA page_count;


-- TEST 5
-- INSERTS WITH CALCULATIONS -- SHOULD BE SLOWER THAN 1

 BEGIN;   
 INSERT INTO 

Re: [sqlite] Looking for generic speed test results

[Noah Hart]

Will do.

Noah
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Tuesday, August 12, 2008 11:55 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Looking for generic speed test results
Importance: High


On Aug 12, 2008, at 2:46 PM, Noah Hart wrote:

>>
>> If you want to do a meaningful speed comparison, then speed1.test
>> should probably be modified to set PRAGMA synchronous=OFF.
>
> Would this be true for all the speed tests?  I'll open a ticket to  
> make
> that request.

The speed1.test script was actually designed to measure changes in  
performance from one release to the next on my linux workstation.  It  
was not designed to measure the relative performance of different  
machines.

If you want to measure the relative performance of SQLite on different  
machines, please at least audit the speed1.test script first to see if  
it is appropriate for the job.


D. Richard Hipp
[EMAIL PROTECTED]



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for generic speed test results

[Noah Hart]

>> Such as
>>
>> SQLite 3.6.1 running under "Vista 32 bit, 2.4GHz, 3G ram,
5000rpmDisk"
>>
>> speed1-insert1...326279489 uS 10.8195909732183 row/s
>
>Right off the bat, I notice that I'm getting 101871.8 rows/s under  
>Linux.  Almost 10,000 times faster.

These were just dummy numbers I pasted here for the email.

>If you want to do a meaningful speed comparison, then speed1.test  
>should probably be modified to set PRAGMA synchronous=OFF.

Would this be true for all the speed tests?  I'll open a ticket to make
that request.

Regards, Noah




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Looking for generic speed test results

[Noah Hart]

Thanks Peter,

I think that to start with, I am only interested in "generic"
amalgamation compilations.
These would run the standard TCL test scripts speed1, speed2, speed3,
speed4.

That would be a good place to start.

Noah

-Original Message-
From: On Behalf Of Peter Holmes

I'd be glad to post results for Ubuntu 7.10 on a Dell Inspiron 530 (Dual

Core 1.6Ghz) if you're interested.  Just supply source code, compile 
options you want me to use, etc.

Noah Hart wrote:
> I understand that memory, disk and processor all play a part in the
> speed test results.





CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Looking for generic speed test results

[Noah Hart]

I understand that memory, disk and processor all play a part in the
speed test results.

However, I am looking for some benchmarks that would give ballpark
figures for the results.

Are people willing to post their speed test results, along with OS, CPU
speed and Memory characteristics?"

Such as 

SQLite 3.6.1 running under "Vista 32 bit, 2.4GHz, 3G ram, 5000rpmDisk"

speed1-insert1...   326279489 uS 10.8195909732183 row/s
..
speed2-insert1...   326279489 uS 10.8195909732183 row/s
...
speed3-1.incrvacuum...  326279489 uS 10.8195909732183 row/s
...
speed4-join1... 326279489 uS 10.8195909732183 row/s

etc


My goal is to create tables, similar to the following:

Numbers in Rows per second

(Assuming > 1GB Ram, and reasonable disk)

Unix 32bit
 CPU1GHz2Ghz3Ghz
Delete  8   9   10
Insert  10k 20k 25k
Join
Sub-selects
Triggers
Views

Windows Vista 32bit
 CPU1GHz2Ghz3Ghz
Delete  8   9   10
Insert  10k 20k 25k
Join
Sub-selects
Triggers
Views

Windows Vista 64bit
 CPU1GHz2Ghz3Ghz
Delete  8   9   10
Insert  10k 20k 25k
Join
Sub-selects
Triggers
Views 

Windows XP 32bit
 CPU1GHz2Ghz3Ghz
Delete  8   9   10
Insert  10k 20k 25k
Join
Sub-selects
Triggers
Views 



Regards, Noah




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Noah Hart]

+3 on removal of #3

Noah 

--- On Thu, 8/7/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
From: D. Richard Hipp <[EMAIL PROTECTED]>
Subject: [sqlite] Proposed removal of (mis-)feature
To: "General Discussion of SQLite Database" 
Date: Thursday, August 7, 2008, 12:26 PM

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem with simple select

[Noah Hart]

My guess is that there is a trailing space in the record.
Try the following:
sqlite> select save_id ||'<' from ae_objects where save_id like 165;
165<

And see where the "sean" save_id field ends.

Regards, Noah

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Sean Riley
Sent: Friday, July 11, 2008 5:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] problem with simple select

Thanks for the quick response. My application is using 3.4.1, but I
grabbed the 3.5.9 executable and got the same thing.

SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select * from ae_objects;
20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2
sqlite> select * from ae_objects where save_id=165;
sqlite> select * from ae_objects where save_id like 165;
20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2

So I tried what your code from below and it worked for me in a new
database. Strange thing though, if I do the insert from your code into
my existing database, then that new record shows up when I do:

sqlite> select * from ae_objects where save_id=165;

But the existing record (the "sean" one) does not!




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem with simple select

[Noah Hart]

Sean, what version of sqlite are you using?


With the command line version it appears to work under 3.5.9

SQLite version 3.5.9
Enter ".help" for instructions
sqlite> DROP TABLE if exists ae_objects ;
sqlite> CREATE TABLE ae_objects (
   ...>   oid INTEGER PRIMARY KEY,
   ...>   nameVARCHAR(64),
   ...>   template_id INTEGER,
   ...>   template_module_id  INTEGER,
   ...>   pos_x   FLOAT,
   ...>   pos_y   FLOAT,
   ...>   pos_z   FLOAT,
   ...>   facing  FLOAT,
   ...>   sprite_id   INTEGER,
   ...>   sprite_module_idINTEGER,
   ...>   save_id VARCHAR(16),
   ...>   save_type   INTEGER );
sqlite>
sqlite> insert into ae_objects values
(40007,1000,1,0,3.43301269412041,12.4330126941204,0.0,0.0,11,0,165,2);
sqlite> select * from ae_objects;
40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2
sqlite> select * from ae_objects where save_id = 165;
40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2
sqlite> select * from ae_objects where save_id = '165';
40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2
sqlite> select * from ae_objects where save_id like '165';
40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2
sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164;
40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2
sqlite>



Regards, Noah


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Sean Riley
Sent: Friday, July 11, 2008 2:08 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] problem with simple select

Hello, 

I have a strange issue with a seemingly simple query.

The table schema:

CREATE TABLE ae_objects (
  oid INTEGER PRIMARY KEY,
  nameVARCHAR(64),
  template_id INTEGER,
  template_module_id  INTEGER,
  pos_x   FLOAT,
  pos_y   FLOAT,
  pos_z   FLOAT,
  facing  FLOAT,
  sprite_id   INTEGER,
  sprite_module_idINTEGER,
  save_id VARCHAR(16),
  save_type   INTEGER );

Sequence of SQL statements executed:
sqlite> select * from ae_objects;
40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2
sqlite> select * from ae_objects where save_id = 165;
sqlite> select * from ae_objects where save_id = '165';
sqlite> select * from ae_objects where save_id like '165';
40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2
sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164;
40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2

Why does the "save_id = 165" fail to get any rows? I get zero rows when
I attempt to find the row by exact match of the "save_id" column, but
with a "like" or a "greater than 164 and less than 166" I get the one
row I am looking for.

This only seems happens when that particular column is varchar(16). I
have gotten this behavior on windows and linux. Can provide a db file if
that helps.


---
Sean Riley
Lead Programmer, Areae Inc.
"All problems in computer science can be solved by another level of
indirection", Butler Lampson, 1972

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index and ORDER BY

[Noah Hart]

Richard, 

Just a suggestion.

Would it make sense to ask one of your document maintainers to add
something similar to your explanation and add it to the "SELECT"
documentation page as a note to using the WHERE clause and the "INDEX"
documentation page



=
Note the "+" operator in front of the "type" field in the WHERE clause.
This + size makes that term of the WHERE clause an expression, rather
than a constraint on a column, and this disqualifies it from use by an
index.  That forces SQLite to use another query strategy.

=

Regards, Noah



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index and ORDER BY

[Noah Hart]

Jeff, try this select instead 

sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE
eid<=32619760 and +type=22  ORDER BY eid DESC LIMIT 1;
0|0|TABLE events USING PRIMARY KEY ORDER BY


Regards-- Noah

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, July 01, 2008 9:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Index and ORDER BY

I agree.  If I drop indices that use "type", I get my performance back  
for this query:

sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22  
ORDER BY eid DESC LIMIT 1;
16643833|27906245|5972704|0|22|9|4
CPU Time: user 0.001000 sys 0.001000

sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE  
eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events USING PRIMARY KEY ORDER BY

The problem is that indexing the type column gives me a huge  
performance benefit for other queries in my application.  Is there any  
way I can force sqlite to not use an index for a particular query?
Thanks for your help!
Jeff



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Count(1)

[Noah Hart]

Questions to the SQLite maintainers...

The docs tell us that ...
** The page headers looks like this:
**
**   OFFSET   SIZE DESCRIPTION
**  0   1  Flags. 1: intkey, 2: zerodata, 4: leafdata,
8: leaf
**  1   2  byte offset to the first freeblock
**  3   2  number of cells on this page

Since the count of cells in use stored in for each btree page?
Wouldn't it be pretty easy to optimize count(*) by


count = 0
Btree_MOVE_TO_FIRST_ENTRY
while not Btree_END_OF_TREE
 count += NUMBER_OF_ENTRIES_ON_THIS_CHILD_PAGE
 Btree_MOVE_TO_NEXT_CHILD_PAGE
return count;


With large rows contents lengths, the savings would be minimal
However even with rows contents lengths around 100, the savings would be
10x

Regards -- Noah


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Scott Hess
Sent: Friday, April 04, 2008 9:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Count(1)

What I meant when I said "full table scan" is that it has to read at
least something for every single row in the table.  So the following
are going to be the same:

  SELECT COUNT(*) FROM t;
  SELECT COUNT(rowid) FROM t;

It won't have to scan any overflow pages, but it will have to hit all
the leaf nodes.

You could certainly do a full scan on an index other than the rowid.
It might involve much less reading if the indexed items are small
relative to the overall row.  Not sure if SQLite does this
optimization for you or not (I don't think it much matters - it's
still going to bel O(N), just with a lower constant).

-scott


On Fri, Apr 4, 2008 at 8:19 AM, Samuel Neff <[EMAIL PROTECTED]>
wrote:
> Scott,
>
>  Is it really a full table scan or just an index scan (at least in the
case
>  where no data is needed from the table as in the original sample that
had no
>  join or where clause).
>
>  Thanks,
>
>  Sam
>
>
>
>  On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote:
>
>  > A little bit more info:  SELECT COUNT(*) is implemented as a full
>  > table scan, so SQLite is visiting every row in the table, which
will
>  > get slower and slower as the table gets bigger and the database
>  > fragments.  This differs from many database engines (which
implement
>  > an optimization for this)  Doing the trigger thing means that it
only
>  > visits the specific row that contains the count.
>  >
>  > -scott
>  >
>  >
>  --
>  -
>  We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264)
developer.
>  Position is in the Washington D.C. metro area. Contact
>  [EMAIL PROTECTED]
>
>
> ___
>  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



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users