RE: [sqlite] How To Use ATTACH?

[michael . ruck]

I don't know your Wrapper, but try this:

CppSQLite3DB db; 

db.open("Stocks.db"); 

db.execDML("ATTACH 'Options.db' AS OPT;"); 

sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN "; 
sSQL += "(SELECT Stocks.rowid FROM Stocks, OPT.Options "; 
sSQL += "WHERE Stocks.sStockSymbol = OPT.Options.sStockSymbol); "; 


db.execDML(sSQL.c_str()); 

Note that I prepended the database name assigned in the Attach statement in 
your Update/Select statement.

Mike

>I know how to use ATTACH with sqlite3.exe but I am having problems using 
>it in C++.  I am using a wrapper and what I am trying to do is 
>illustrated by:
>
>CppSQLite3DB db;
>
>db.open("Stocks.db");
>
>db.execDML("ATTACH   'Options.db' AS OPT;");
>
>  sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN ";
>  sSQL += "(SELECT Stocks.rowid FROM Stocks, Options ";
>  sSQL += "WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";
>
>
>   db.execDML(sSQL.c_str());
>
>but this gives me an error saying table Options (in database file 
>Options.db) is not available.
>
>How do I get ATTACH to work in C++?
>
>Thanks,
>  Roger
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to lock sqlite DB when access it

[drh]

"chueng alex1985" <[EMAIL PROTECTED]> wrote:
> I want to lock a DB when I access it, in case of two processes write/read it
> at the same time.
> 

This happens automatically.  You do not have to do anything.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Does SQLite support user-defined data-types ?

[Jerome CORRENOZ]

 I'm implementing the SQLData interface. Is it the right one ?

John Stanton wrote:

You need to revise your Java interface or maybe find another.  What 
are you using?


Jerome CORRENOZ wrote:

Now, I can use user-dfined types by declaring them with SQL create 
tables. Fine !


But when I try to use the following code to get an object, I get a 
ClassCastException:

  Statement stmt = conn.createStatement();
  ResultSet rset = stmt.executeQuery(
  "SELECT * FROM EMP");
  while (rset.next()) {
  Dept dept = (Dept)rset.getObject("DEPT");
  }

Is it normal or is it due to the fact that SQLite doesn't support 
SQL3 commands, mainly the getTypeMap() command that maps a Java class 
with a SQL type ?
Notice that my Java class implements SQLData but it seems having no 
effect.


Thanks for your answer,
Jerome

John Stanton wrote:

Sqlite is flexible in typing.  You can create user defined types 
just by declaring them in your SQL which creates tables.  Thereafter 
the API will return the declared type and the actual type so that 
you can process it appropriately.


Jerome CORRENOZ wrote:


Hi,

I'm starting with SQLite and I would like to know if it is possible 
to create user-defined data-types through the following SQL 
command: create type MyType ... ?


In fact, I need it to map a database with an object-oriented model, 
so that I could manage the objects in a ResultSet like it follows:

   Statement stmt = conn.createStatement();
   ResultSet rset = stmt.executeQuery(
   "SELECT * FROM EMP");
   while (rset.next()) {
   Dept dept = (Dept)rset.getObject("DEPT");
   System.out.println("Lieu du dept : "
   + dept.getLieu());
   }

Is there a way to do it with SQLite ?

Regards,
Jerome




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 









- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 

Re: [sqlite] OR, IN: which is faster?

[drh]

Ion Silvestru <[EMAIL PROTECTED]> wrote:
> If we have a query where we compare a column to a set of values, then
> which is faster: OR or IN?
> Ex: OR: (mycol = "a") OR (mycol = "b") OR (mycol = "c") OR...
> IN: (mycol IN "a", "b", "c" ...)
> 

IN is faster.  However, version 3.2.3 introduced an enhancement
to the SQLite optimizer that automatically converts the OR form
of the expression above into the IN form, thus taking advantage
of the increased speed of IN.  So for SQLite version 3.2.3, there
really is no difference between the two.

See http://www.sqlite.org/optoverview.html#or_opt

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] how to cross compile sqlite 3

[anis chaaba]

Hello everybody
I'm cross compiling sqlite3 for arm and there's errors in the
configure and the makefile generated.
Do anyone of you  cross compiled sqlite3?
thanks in advance

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to cross compile sqlite 3

[C.Peachment]

On Thu, 1 Feb 2007 10:41:25 +0100, anis chaaba wrote:

>I'm cross compiling sqlite3 for arm and there's errors in the
>configure and the makefile generated.
>Do anyone of you  cross compiled sqlite3?
>thanks in advance


Using the preprocessed source code in sqlite-source-3_3_12.zip
lets you avoid the problems of configure checking the host computer
attributes when it should specify the target computer attributes.

I have compiled this source for both linux-arm and windows-pc
without issues.

Chris Peachment




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to cross compile sqlite 3

[anis chaaba]

thanks
i've user sqlite 3.3.11.tar.gz and i made some modifications on configure
and make file and it works



2007/2/1, C.Peachment <[EMAIL PROTECTED]>:


On Thu, 1 Feb 2007 10:41:25 +0100, anis chaaba wrote:

>I'm cross compiling sqlite3 for arm and there's errors in the
>configure and the makefile generated.
>Do anyone of you  cross compiled sqlite3?
>thanks in advance


Using the preprocessed source code in sqlite-source-3_3_12.zip
lets you avoid the problems of configure checking the host computer
attributes when it should specify the target computer attributes.

I have compiled this source for both linux-arm and windows-pc
without issues.

Chris Peachment





-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] How To Use ATTACH?

[Roger Miskowicz]

Hi Mike,
 Thanks, that helped.  Not that I needed to do that but it got me to 
find the real problem...  "Options.db was not in the current folder!"  
which apparently is sufficient reason for its tables not being found.  
Go figure! : )


Roger

[EMAIL PROTECTED] wrote:

I don't know your Wrapper, but try this:

CppSQLite3DB db; 

db.open("Stocks.db"); 

db.execDML("ATTACH 'Options.db' AS OPT;"); 

sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN "; 
sSQL += "(SELECT Stocks.rowid FROM Stocks, OPT.Options "; 
sSQL += "WHERE Stocks.sStockSymbol = OPT.Options.sStockSymbol); "; 



db.execDML(sSQL.c_str()); 


Note that I prepended the database name assigned in the Attach statement in 
your Update/Select statement.

Mike

  
  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How to specify collating sequences in an expression.

[drh]

SQLite has supported collating sequences since version 3.0.0.
A collating sequence is really a specification on how comparison
operators work on strings.

You can have arbitrary collating sequences in SQLite.  But for
the sake of this discussion, lets focus on just two:  BINARY
which is the default collating sequence, and NOCASE which does
case insensitive comparisons (on US-ASCII).

Consider this example table:

   CREATE TABLE t1(
 x TEXT COLLATE BINARY,
 y TEXT COLLATE NOCASE
   );
   INSERT INTO t1 VALUES('hello','Hello');

This query returns one row because comparisons against column y
ignore case:

   SELECT * FROM t1 WHERE y='HELLO';

This query returns no rows because comparisons against column x
take case into account.

   SELECT * FROM t1 WHERE x='HELLO';

When comparing two columns, the column on the left determines which
collating sequence to use.  In the first of the following two queries,
the column on the left uses NOCASE so one row is returned.  But in
the second query, the columns are reversed and the left-most column
uses BINARY.  As a result, no rows are returned from the second
query:

   SELECT * FROM t1 WHERE y=x;
   SELECT * FROM t1 WHERE x=y;

This last point seems a little goofy, but SQLite is documented as
working that way and the situation comes up so very rarely that nobody
has yet complained.

The problem with all of the above is that the collating sequence
on a column is specified when the column is declared and cannot
be changed.  What many people would like to have is some way to 
specify a different collating sequence to override the default
collating sequence for a single comparison.  The question is, what
should the syntax be.  Here are some ideas:

SELECT * FROM t1 WHERE x='HELLO' COLLATE NOCASE;

Here the = operator has some added syntax at the end.  There some
parsing abiguities with this approach, but they can be dealt with
just as they are with the EXCEPT clause on a LIKE operator.  The
comparison operator syntax would be like this:

   [COLLATE ]

Another idea is to use CAST:

 SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';

Or perhaps

 SELECT * FROM t1 WHERE CASE(x COLLATE NOCASE)='HELLO';

A third idea is to invent entirely new syntax, perhaps like this:

 SELECT * FROM t1 WHERE COLLATE NOCASE(x='HELLO')

Please note that while I have used the = operator in all of the
examples above, everything applies equally to !=, <, <=, >, and >=.

Questions for SQLite community members:

  (1)  Is there some standard SQL syntax that I do not know about
   for doing this kind of thing?

  (2)  How do other SQL engines do this kind of thing?

  (3)  Do you have any additional (hopefully better) ideas for
   specifying alternative collating sequences on individual
   comparison operators.

  (4)  What syntax do you prefer?

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite core function question

[Jeff Godfrey]

From: "T" <[EMAIL PROTECTED]>


Hi Jeff,

I've encountered some functions that apparently aren't supported by 
SQLite


So have I, such as replacing occurrences of an inner string.


so I've created my own


I've yet to figure out/try that. Is there a library somewhere of 
prebuilt functions we can add?



Hi Tom,

I'm not aware of any "prebuilt function library".  In my case, I'm
developing my app in Tcl.  There creating and registering new
SQL functions is simple - trivial even.  So, whenever I run into
a non-supported function in the SQL I'm porting, it's quick/easy
to just replace it with one of my own.  The biggest drawback I've
found to the custom functions is the fact that they are (obviously)
not available to any of the 3rd party tools I use to view/browse
my raw database files with.  In that case, the unknown function
calls just generate errors.


[... several suggestions for emulating an INT function...]


Thanks for the suggestions.  You've made some quite inventive
attempts there... ;^)  Since I've already provided my own INT
function, I'm beyond that issue right now.  Ultimately, I should
probably go back and rewrite my queries to use CAST as
DRH mentioned.

Thanks,

Jeff 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

>   (4)  What syntax do you prefer?

This seems the clearest to me, of the given choices:

 SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';

Derrell

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Function question

[Anderson, James H \(IT\)]

>From the CLP (i.e., w/o user-defined functions) is there any way to get
the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
spaces from the right/left end of a column value.)

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

Re: [sqlite] How to specify collating sequences in an expression.

[Ken]

DRH and Sqlite Community,
 
 Provide two sql functions: toupper() and tolower() that can be applied. And 
always use binary comparison.
 
 so:
 select x from where toupper(y) = 'HELLO' ;
 would return 1 row...
 
 But here is the gotcha, more than likely applying that function would negate 
the use of an index that would be on column y. I would think that apply a 
function transformation for use on an indexed column would have pretty bad 
performance. 
 
 On the other hand something like:
 select y from where x= tolower('HELLO' ) ;
 would also return 1 row. And should have no problems utilizing an existing 
indices since the function is only applied once to the sql, not to the stored 
index data values.
 
 Also other systems such as Oracle for instance use things like NLS_COMP, 
NLS_SORT to manipulate sorting characteristics. These can be environment 
variables ora can be set as part of an "alter session" command (Similar to a 
sqlite PRAGMA).
 
 Regards,
 Ken
 
 
 
 
 
 

[EMAIL PROTECTED] wrote: SQLite has supported collating sequences since version 
3.0.0.
A collating sequence is really a specification on how comparison
operators work on strings.

You can have arbitrary collating sequences in SQLite.  But for
the sake of this discussion, lets focus on just two:  BINARY
which is the default collating sequence, and NOCASE which does
case insensitive comparisons (on US-ASCII).

Consider this example table:

   CREATE TABLE t1(
 x TEXT COLLATE BINARY,
 y TEXT COLLATE NOCASE
   );
   INSERT INTO t1 VALUES('hello','Hello');

This query returns one row because comparisons against column y
ignore case:

   SELECT * FROM t1 WHERE y='HELLO';

This query returns no rows because comparisons against column x
take case into account.

   SELECT * FROM t1 WHERE x='HELLO';

When comparing two columns, the column on the left determines which
collating sequence to use.  In the first of the following two queries,
the column on the left uses NOCASE so one row is returned.  But in
the second query, the columns are reversed and the left-most column
uses BINARY.  As a result, no rows are returned from the second
query:

   SELECT * FROM t1 WHERE y=x;
   SELECT * FROM t1 WHERE x=y;

This last point seems a little goofy, but SQLite is documented as
working that way and the situation comes up so very rarely that nobody
has yet complained.

The problem with all of the above is that the collating sequence
on a column is specified when the column is declared and cannot
be changed.  What many people would like to have is some way to 
specify a different collating sequence to override the default
collating sequence for a single comparison.  The question is, what
should the syntax be.  Here are some ideas:

SELECT * FROM t1 WHERE x='HELLO' COLLATE NOCASE;

Here the = operator has some added syntax at the end.  There some
parsing abiguities with this approach, but they can be dealt with
just as they are with the EXCEPT clause on a LIKE operator.  The
comparison operator syntax would be like this:

   [COLLATE ]

Another idea is to use CAST:

 SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';

Or perhaps

 SELECT * FROM t1 WHERE CASE(x COLLATE NOCASE)='HELLO';

A third idea is to invent entirely new syntax, perhaps like this:

 SELECT * FROM t1 WHERE COLLATE NOCASE(x='HELLO')

Please note that while I have used the = operator in all of the
examples above, everything applies equally to !=, <, <=, >, and >=.

Questions for SQLite community members:

  (1)  Is there some standard SQL syntax that I do not know about
   for doing this kind of thing?

  (2)  How do other SQL engines do this kind of thing?

  (3)  Do you have any additional (hopefully better) ideas for
   specifying alternative collating sequences on individual
   comparison operators.

  (4)  What syntax do you prefer?

--
D. Richard Hipp  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Memory database to file

[David Champagne]

I suppose since no one replied to this, that it's not possible to do it.  Just 
wanted to confirm.  Thank you...

- Original Message 
From: David Champagne <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 29, 2007 2:32:00 PM
Subject: [sqlite] Memory database to file


Hi,

I've got an application that creates a database with a large number of records 
(millions), and the indexation of the tables is taking a long time.  Once the 
database is initially created, it is never modified.  No records are added or 
deleted.  So, in the case where a user has sufficient memory, I want to offer 
the option to create the entire database in memory, including the indexes, and 
then serialize that to disk.  I am not sure if this is really going to work or 
if it will be more effcient.  I am using the "ATTACH DATABASE" command to 
create a copy of my ":memory:" database.  I can copy copy the tables (see 
below), and I can get a list of the indxes (see further below), but I don't 
know how to copy the indexes.  Also, does anyone know if this method is really 
going to be faster (memory db -> disk db) than doing everything with a disk db? 
 Would the indexes really be copied or just re-created?

//get the list of tables
sql_cmd = "SELECT name FROM SQLITE_MASTER WHERE type = 'table'";
rc = sqlite3_prepare( m_db, sql_cmd.c_str(),(int)sql_cmd.length(), ,  
);

std::vector tables;
rc = sqlite3_step(stmt);
while ( rc != SQLITE_DONE && rc != SQLITE_ERROR )
{
  tables.push_back((const char*) sqlite3_column_text ( stmt,0 ));
  rc = sqlite3_step(stmt);
}
sqlite3_finalize(stmt); 

//get the list of indexes
sql_cmd = "SELECT name, tbl_name FROM SQLITE_MASTER WHERE type = 'index'";
rc = sqlite3_prepare( m_db, sql_cmd.c_str(),(int)sql_cmd.length(), ,  
);

std::vector> indexes;
rc = sqlite3_step(stmt);
while ( rc != SQLITE_DONE && rc != SQLITE_ERROR )
{
  indexes.push_back(std::pair((const char*) sqlite3_column_text 
( stmt,0 ), (const char*) sqlite3_column_text ( stmt,1 )));
  rc = sqlite3_step(stmt);
}
sqlite3_finalize(stmt);

rc = sqlite3_exec(m_db,"begin;", callback, 0, );

//copy the tables
for (vector::const_iterator it = tables.begin(); it != tables.end(); 
it++)
{
  string sTable = *it;
  sql_cmd = "CREATE TABLE dbdisk." + sTable + " AS SELECT * FROM " + sTable + 
";";
  rc = sqlite3_exec(m_db,sql_cmd.c_str(), callback, 0, );
}

//copy the indexes
for (vector>::const_iterator it = indexes.begin(); it 
!= indexes.end(); it++)
{
  string sIndex = (*it).first;
  string sTable = (*it).second;
  //to be filled in
}

rc = sqlite3_exec(m_db,"commit;", callback, 0, );




Don't get soaked.  Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


 

Expecting? Get great news right away with email Auto-Check. 
Try the Yahoo! Mail Beta.
http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[drh]

Ken <[EMAIL PROTECTED]> wrote:
> DRH and Sqlite Community,
>  
> Provide two sql functions: toupper() and tolower() that can be 
> applied. And always use binary comparison.
>  
>  so:
>  select x from where toupper(y) = 'HELLO' ;
>  would return 1 row...
>  
>  But here is the gotcha, more than likely applying that function 
> would negate the use of an index that would be on column y.

It would indeed.  But more importantly, this technique only
works for case comparisons.  I'm looking for a general solution 
that will work on any collating sequence, such as comparison 
functions on Chinese text.  Toupper() is not help there.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Ken]

 If you are dealing with say a chinese char set then wouldn't you want to 
handle this at a "global" level by modifying the database characteristics, then 
maybe a Pragma command would be the way to go.
 
 

[EMAIL PROTECTED] wrote: Ken  wrote:
> DRH and Sqlite Community,
>  
> Provide two sql functions: toupper() and tolower() that can be 
> applied. And always use binary comparison.
>  
>  so:
>  select x from where toupper(y) = 'HELLO' ;
>  would return 1 row...
>  
>  But here is the gotcha, more than likely applying that function 
> would negate the use of an index that would be on column y.

It would indeed.  But more importantly, this technique only
works for case comparisons.  I'm looking for a general solution 
that will work on any collating sequence, such as comparison 
functions on Chinese text.  Toupper() is not help there.
--
D. Richard Hipp  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How To Use ATTACH?

[Dennis Cote]

Roger Miskowicz wrote:


CppSQLite3DB db;

db.open("Stocks.db");

db.execDML("ATTACH   'Options.db' AS OPT;");

 sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN ";
 sSQL += "(SELECT Stocks.rowid FROM Stocks, Options ";
 sSQL += "WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";


  db.execDML(sSQL.c_str());


Roger,

A very minor efficiency note.

You can eliminate the runtime concatenation of your SQL strings by 
letting the compiler do it at compile time. The compiler will 
concatenate char[] constants that are separated by whitespace into a 
single char[] constant. Replacing


sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN ";
sSQL += "(SELECT Stocks.rowid FROM Stocks, Options ";
sSQL += "WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";

with

sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN "
"(SELECT Stocks.rowid FROM Stocks, Options "
"WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";

eliminates two unnecessary calls to the string operator+= function. I 
also think it looks better. :-)


HTH
Dennis Cote




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Ken]

After re-reading this:
 (2) Oracle does the following:
Binary and Linguistic sorting:
Binary is the default.
 
Linguistic sorting is configured by setting NLS_COMP=LINGUISTIC and Setting 
NLS_SORT to a language specific sort rule.
(these can be set at the session level or DB etc, via an alter session 
command, alter system command or by environment variables)
  
 As such  there is no mechanism to change the sorting element for a single  
column.
 
 Case comparisons are handled by toupper,  tolower  and other sql functions are 
plentiful. It also utilizes a "Blank-padded/ nonpadded" comparis when dealing 
with VARCHAR vs CHAR datatypes. But that is probably out of scope since sqlite 
is loosely typed.
 
 (4).  I like the following syntax:
SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';
   As it  pretty clearly modifies the "default"  attribute.
 
 Ken
 

Re: [sqlite] Memory database to file

[Nemanja Corlija]

On 1/29/07, David Champagne <[EMAIL PROTECTED]> wrote:

Hi,

I've got an application that creates a database with a large number of records 
(millions),
and the indexation of the tables is taking a long time.  Once the database is 
initially
created, it is never modified.  No records are added or deleted.  So, in the 
case where a
user has sufficient memory, I want to offer the option to create the entire 
database in
memory, including the indexes, and then serialize that to disk.  I am not sure 
if this is
really going to work or if it will be more effcient.


I had a similar problem, and going this route was very much worth it
for me. Though you might find that its not worth it for you. Only way
to know for sure is to tray both methods and compare the results.



I am using the "ATTACH DATABASE"
command to create a copy of my ":memory:" database.  I can copy copy the tables 
(see
below), and I can get a list of the indxes (see further below), but I don't 
know how to copy
the indexes.


This will return a list of sql statements used to create all indexes
in your db. You just need to execute each row returned.

SELECT sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL

IS NOT NULL check at the end is used to filter out autoindex-es
created implicitly by sqlite in case of a primary key and UNIQUE
constraint.


Also, does anyone know if this method is really going to be faster (memory
db -> disk db) than doing everything with a disk db?  Would the indexes really 
be copied
or just re-created?


They'll be recreated. But in order to achieve faster index creation
times for disk db, you need to populate your disk db with presorted
data. Something like this:

CREATE TABLE dbdisk.table1 AS SELECT * FROM table1 ORDER BY index_col

index_col being the column on which you want to create an index in
disk db. You might find  that its not even worth it to create an index
in memory db, just execute the above statement. In my limited
experience regarding this, it takes about the same amount of time, but
again your mileage will vary.
In my case this was all easy to do since I control the schema and I
only have one index in my table. If you don't control the schema, I
don't think this is worth the hassle, if even possible.

Trick, if I can call it that, here is to create index in disk db when
data is already sorted. That way you improve index creation speed and
locality of reference which should translate into somewhat better
query speed if your db is big. In order to get most of that, you'd
want to avoid any implicit index-es except for INTEGER PRIMARY KEY.
For example, if you have a schema like this:

CREATE TABLE t1(name TEXT PRIMARY KEY, age INT);

You'd want to transform that into something more like this:

CREATE TABLE t1(name TEXT, age INT);
CREATE INDEX t1_idx1 ON t1(name);

This whole thing will probably provide no significant gain if your
resulting disk db is less then 50-100 MB.

--
Nemanja Corlija <[EMAIL PROTECTED]>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQLite in the wild

[Robert Simpson]

Just found out on my forums that the National Library of Medicine is using
SQLite.  They provide a free software program for HAZMAT first-responders to
use on their PDA's to get information on hazardous materials.

>From their website:
Mobile support, providing First Responders with critical information in the
palm of their hand. 
Comprehensive decision support, including assistance in identification of an
unknown substance and, once the substance is identified, providing guidance
on immediate actions necessary to save lives and protect the environment. 
Access to 400+ substances from NLM's Hazardous Substances Data Bank (HSDB)
which contains detailed information on over 4,700 critical hazardous
substances 
Rapid access to the most important information about a hazardous substance
by an intelligent synopsis engine and display called "Key Info" 
Intuitive, simple, and logical user interface developed by working with
experienced first responders

More info can be found here:
http://wiser.nlm.nih.gov/


Robert Simpson
Programmer at Large
http://sqlite.phxsoftware.com




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Michael Schlenker]

[EMAIL PROTECTED] schrieb:

SQLite has supported collating sequences since version 3.0.0.
A collating sequence is really a specification on how comparison
operators work on strings.

You can have arbitrary collating sequences in SQLite.  But for
the sake of this discussion, lets focus on just two:  BINARY
which is the default collating sequence, and NOCASE which does
case insensitive comparisons (on US-ASCII).

Consider this example table:

   CREATE TABLE t1(
 x TEXT COLLATE BINARY,
 y TEXT COLLATE NOCASE
   );
   INSERT INTO t1 VALUES('hello','Hello');

This query returns one row because comparisons against column y
ignore case:

   SELECT * FROM t1 WHERE y='HELLO';

This query returns no rows because comparisons against column x
take case into account.

   SELECT * FROM t1 WHERE x='HELLO';

When comparing two columns, the column on the left determines which
collating sequence to use.  In the first of the following two queries,
the column on the left uses NOCASE so one row is returned.  But in
the second query, the columns are reversed and the left-most column
uses BINARY.  As a result, no rows are returned from the second
query:

   SELECT * FROM t1 WHERE y=x;
   SELECT * FROM t1 WHERE x=y;

This last point seems a little goofy, but SQLite is documented as
working that way and the situation comes up so very rarely that nobody
has yet complained.

The problem with all of the above is that the collating sequence
on a column is specified when the column is declared and cannot
be changed.  What many people would like to have is some way to 
specify a different collating sequence to override the default

collating sequence for a single comparison.  The question is, what
should the syntax be.  Here are some ideas:

SELECT * FROM t1 WHERE x='HELLO' COLLATE NOCASE;

Here the = operator has some added syntax at the end.  There some
parsing abiguities with this approach, but they can be dealt with
just as they are with the EXCEPT clause on a LIKE operator.  The
comparison operator syntax would be like this:

   [COLLATE ]

Another idea is to use CAST:

 SELECT * FROM t1 WHERE CAST(x AS TEXT COLLATE NOCASE)='HELLO';

Or perhaps

 SELECT * FROM t1 WHERE CASE(x COLLATE NOCASE)='HELLO';

A third idea is to invent entirely new syntax, perhaps like this:

 SELECT * FROM t1 WHERE COLLATE NOCASE(x='HELLO')

Please note that while I have used the = operator in all of the
examples above, everything applies equally to !=, <, <=, >, and >=.

Questions for SQLite community members:

  (1)  Is there some standard SQL syntax that I do not know about
   for doing this kind of thing?

  (2)  How do other SQL engines do this kind of thing?


Microsoft SQL Server does this either on the database level, by 
specifying the collation to use for the whole database, or at the column 
level with an alter table statement or during table creation.


See:
http://msdn2.microsoft.com/en-us/library/aa258237(SQL.80).aspx

Michael

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Dennis Cote]

[EMAIL PROTECTED] wrote:

A collating sequence is really a specification on how comparison
operators work on strings.
  
This is a bit of a simplification, at least as far as the SQL:1999 
standard goes.



You can have arbitrary collating sequences in SQLite.  But for
the sake of this discussion, lets focus on just two:  BINARY
which is the default collating sequence, and NOCASE which does
case insensitive comparisons (on US-ASCII).

Consider this example table:

   CREATE TABLE t1(
 x TEXT COLLATE BINARY,
 y TEXT COLLATE NOCASE
   );
   INSERT INTO t1 VALUES('hello','Hello');

The problem with all of the above is that the collating sequence
on a column is specified when the column is declared and cannot
be changed.  What many people would like to have is some way to 
specify a different collating sequence to override the default

collating sequence for a single comparison.  The question is, what
should the syntax be.  Here are some ideas:

SELECT * FROM t1 WHERE x='HELLO' COLLATE NOCASE;
  


This is the syntax that the SQL:1999 standard specifies. It would also 
allow this


SELECT * FROM t1 WHERE x COLLATE NOCASE = 'HELLO';

or even this

SELECT * FROM t1 WHERE x COLLATE NOCASE = 'HELLO' COLLATE NOCASE



Here the = operator has some added syntax at the end.  There some
parsing abiguities with this approach, but they can be dealt with
just as they are with the EXCEPT clause on a LIKE operator.  The
comparison operator syntax would be like this:

   [COLLATE ]

  

To accept the standard syntax this would need to be something like:

[COLLATE ]   [COLLATE ]


Questions for SQLite community members:

  (1)  Is there some standard SQL syntax that I do not know about
   for doing this kind of thing?

  (2)  How do other SQL engines do this kind of thing?

  (3)  Do you have any additional (hopefully better) ideas for
   specifying alternative collating sequences on individual
   comparison operators.

  (4)  What syntax do you prefer?

  
I would *strongly* recommend adopting the SQL:1999 standard syntax for 
compatibilty with other database systems, now and in the future.


In the standard character strings have a couple of attributes, a 
character set and a collation. SQLite does not support multiple 
character sets, so we can ignore that attribute. This leaves each string 
with a collation attribute. This attribute can be specified explicitly 
in data type clause of a column definition, or in the data type clause 
of a cast expression, or directly with an explicit COLLATE clause after 
a string expression, even on a string literal.


   create table t (column1 text COLLATE )
   CAST(  AS text COLLATE )
   column1 COLLATE 
   'a string' COLLATE 

Then the standard has set of rules that specifies the collation of the 
character string resulting from operations, like substring and 
concatenation based on the collation of the operand strings. This is 
determined by another attribute of a string, its coercibility, which 
indicates if a string has a rigid collation, is using a collation from 
another source, or if it can be coerced to use a collation from another 
source. These rules are specified for functions with one or two string 
arguments like substring, or concatenation, and for comparisons. The 
rules used to assign a string's coercibility are fairly simple:


   If you provide an explicit collate clause the coercibility is 
Explicit and the collation is the one specified by the collation clause.
   If the string is a column reference the coercibility is Implicit and 
the collation is the one specified by the column definition.
   If the string is a value other than a column reference (a literal or 
parameter value) the coercibility is Coercible and the collation is the 
default collation.


The standard then defines three table, one for the result of a monadic 
function, one for the result of a dyadic function, and one for the 
collation used for a comparison. These tables are basically common sense.


For monadic functions the output has the same coercibility and collation 
as the input.


For dyadic function the result are much as expected; Implicit overrides 
Coercible and produces Implicit, Explicit overrides Coercible and 
Implicit and produces Explicit. There are two corner cases though. The 
first is a function with two Implicit arguments that have different 
collation, which is defined to produce a value with no collation.  The 
second is a function with two arguments with different Explicit 
collations, which is defined as invalid syntax.


For comparisons the collation that is used is the default if both 
operands are Coercible. An Explicit collation is used if either argument 
is Explicit, unless they are both Explicit but specify different 
collations, this is defined as invalid syntax. An Implicit collation 
will be used unless both arguments are Implicit with different collation 
values, this is also defined as invalid syntax.


The rules 

RE: [sqlite] Function question

[Anderson, James H \(IT\)]

Does the fact that I have received no reply mean that there's no way to
get this functionality within the CLP? 

-Original Message-
From: Anderson, James H (IT) 
Sent: Thursday, February 01, 2007 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Function question

>From the CLP (i.e., w/o user-defined functions) is there any way to get
the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
spaces from the right/left end of a column value.)

jim


NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: How to specify collating sequences in an expression.

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

  (2)  How do other SQL engines do this kind of thing?


MS SQL Server supports

a=b collate CollationName

syntax. There are a few examples at

http://msdn2.microsoft.com/en-us/library/ms179886.aspx

This article also specifies a rather complicated set of rules for
determining which collation should be used for a given comparison.

MS SQL also supports defining multiple indexes on the same table and
field(s), differing only in collation (and the optimizer is smart
enough, most of the time, to use these indexes appropriately). I haven't
tried it with SQLite, maybe it's also supported.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Function question

[Nemanja Corlija]

On 2/1/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

Does the fact that I have received no reply mean that there's no way to
get this functionality within the CLP?

-Original Message-
From: Anderson, James H (IT)
Sent: Thursday, February 01, 2007 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Function question

From the CLP (i.e., w/o user-defined functions) is there any way to get
the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
spaces from the right/left end of a column value.)


SQLite doesn't have any trim functions by default. Here's a list of
available functions:

http://www.sqlite.org/lang_expr.html#corefunctions

If you decide to write one your self, you can compile it as a loadable
extension and load it at runtime from SQL using load_extension()
function.

--
Nemanja Corlija <[EMAIL PROTECTED]>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] glob function

[P Kishor]

"glob(X,Y) This function is used to implement the "X GLOB Y" syntax of
SQLite. The sqlite3_create_function() interface can be used to
override this function and thereby change the operation of the GLOB
operator."

Excuse my ignorance, but what exactly does glob(x, y) do? Or, what is
the "X GLOB Y" syntax of SQLite?

On a related note, most functions are self-explanatory, but some are
not so. Would it be possible to add example syntax/usage to
 for each of the functions?


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Function question

[Anderson, James H \(IT\)]

OK, thanks. 

-Original Message-
From: Nemanja Corlija [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 01, 2007 2:33 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Function question

On 2/1/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> Does the fact that I have received no reply mean that there's no way
to
> get this functionality within the CLP?
>
> -Original Message-
> From: Anderson, James H (IT)
> Sent: Thursday, February 01, 2007 9:09 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Function question
>
> From the CLP (i.e., w/o user-defined functions) is there any way to
get
> the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
> spaces from the right/left end of a column value.)
>
SQLite doesn't have any trim functions by default. Here's a list of
available functions:

http://www.sqlite.org/lang_expr.html#corefunctions

If you decide to write one your self, you can compile it as a loadable
extension and load it at runtime from SQL using load_extension()
function.

-- 
Nemanja Corlija <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[drh]

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> 
> MS SQL also supports defining multiple indexes on the same table and
> field(s), differing only in collation (and the optimizer is smart
> enough, most of the time, to use these indexes appropriately). I haven't
> tried it with SQLite, maybe it's also supported.
> 

SQLite also supports multiple indices differing only
in collation, and the optimizer will use the appropriate
index to satisfy the ORDER BY clause.  But because the
SQLite parser does not currently provide a means to change
the collating sequence assigned to an expression, there
is no way for the optimizer to use different indices
for optimizing access since an expression in the WHERE
clause can only have a single collation.  That's what
I'm trying to fix

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Function question

[Kees Nuyt]

Hi Jim,

On Thu, 1 Feb 2007 09:08:44 -0500, you wrote:

>From the CLP (i.e., w/o user-defined functions) is there any way to get
>the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
>spaces from the right/left end of a column value.)

As far as I can tell load_extension(X,Y) can be used in the
command line program. 

I usually postprocess output by piping it through awk to solve
these kinds of problems.

sqlite3 databasefile outfile

>jim

HTH
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Function question

[P Kishor]

On 2/1/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:





On 2/1/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:
> Does the fact that I have received no reply mean that there's no way to
> get this functionality within the CLP?
>
> -Original Message-
> From: Anderson, James H (IT)
> Sent: Thursday, February 01, 2007 9:09 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Function question
>
> From the CLP (i.e., w/o user-defined functions) is there any way to get
> the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
> spaces from the right/left end of a column value.)
>


I don't know what a "CLP" is, but all the core functions are
documented at , and
no,
ltrim/rtrim are not in the list, nor is anything that can easily help
cook up ltrim/rtrim functionality afaict. On the other hand, they are
trivial capabilities to add via custom functions and your programming
interface of choice.

CLP = Command Line Processor.



This comes up frequently -- sqlite3, the shell program (the one being
called CLP here), is mistaken for sqlite3, the C library that provides
the db magic. Think of sqlite3, the program as an example program that
demonstrates what sqlite3, the library is capable of. Use your
favorite programming environment to construct your own program to
interact with sqlite3, the library.

Perhaps changing the name of sqlite3, the program to something like
sqlite_shell might help alleviate this confusion.


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Function question

[P Kishor]

On 2/1/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

Does the fact that I have received no reply mean that there's no way to
get this functionality within the CLP?

-Original Message-
From: Anderson, James H (IT)
Sent: Thursday, February 01, 2007 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Function question

From the CLP (i.e., w/o user-defined functions) is there any way to get
the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
spaces from the right/left end of a column value.)




I don't know what a "CLP" is, but all the core functions are
documented at , and no,
ltrim/rtrim are not in the list, nor is anything that can easily help
cook up ltrim/rtrim functionality afaict. On the other hand, they are
trivial capabilities to add via custom functions and your programming
interface of choice.




--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How To Use ATTACH?

[Roger]

A very minor efficiency note.

You can eliminate the runtime concatenation of your SQL strings by 
letting the compiler do it at compile time. The compiler will 
concatenate char[] constants that are separated by whitespace into a 
single char[] constant. Replacing


sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN ";
sSQL += "(SELECT Stocks.rowid FROM Stocks, Options ";
sSQL += "WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";

with

sSQL = "UPDATE Stocks SET bOption=1 WHERE rowid IN "
"(SELECT Stocks.rowid FROM Stocks, Options "
"WHERE Stocks.sStockSymbol = Options.sStockSymbol); ";

eliminates two unnecessary calls to the string operator+= function. I 
also think it looks better. :-)


HTH
Dennis Cote


Didn't know that, much appreciated. Thanks Dennis

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: How to specify collating sequences in an expression.

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

  (2)  How do other SQL engines do this kind of thing?


MS SQL Server supports

a=b collate CollationName

syntax. There are a few examples at

http://msdn2.microsoft.com/en-us/library/ms179886.aspx

This article also specifies a rather complicated set of rules for
determining which collation should be used for a given comparison.

MS SQL also supports defining multiple indexes on the same table and
field(s), differing only in collation (and the optimizer is smart
enough, most of the time, to use these indexes appropriately). I haven't
tried it with SQLite, maybe it's also supported.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Ken]

Based on the example, I was under the impression you were trying to fix a 
comparison operator.
 
 Oracle used hints( ie comments embedded in the sql) to tell the optimizer 
which index to select. 
 
 Cant you assign a collating sequence in the ordre by? Why not use that to 
determine if there is an index to use? It seems that then if one were to do the 
following
 
 Is this ambiguous ???
 
 select  x
 from t1 , t 2where cast(t1.x as text collate nocase)  = t2.y
order by X collate binary  ;
 
 Seems to me this is simply a filtering or comparison mechanism not for 
ordering, as ordering is already handled by the order by clause.
 
 In the case of Oracle, any time a function ie (toupper () ) is applied to a 
column that is part of the index in a join clause, then the index is no longer 
considered for the join operation and a Full table scan will occur. Since in 
reallity the two are not really joinable, so for oracle a join operation must 
be binary  at least thats with simple indexes. There are 
 
 Function based indexes that will allow the user to define any function they 
choose to apply to the indexed columns. Then the optimizer will select that 
index when the function is applied to the join attributes that match...
 
 IMHO  the cast is the way to go to assign a collating sequence. 
 
 Maybe you need an additional index type ? One where the index is specified 
with a function.
 
 Ken
 
 
 
[EMAIL PROTECTED] wrote: "Igor Tandetnik"  wrote:
> 
> MS SQL also supports defining multiple indexes on the same table and
> field(s), differing only in collation (and the optimizer is smart
> enough, most of the time, to use these indexes appropriately). I haven't
> tried it with SQLite, maybe it's also supported.
> 

SQLite also supports multiple indices differing only
in collation, and the optimizer will use the appropriate
index to satisfy the ORDER BY clause.  But because the
SQLite parser does not currently provide a means to change
the collating sequence assigned to an expression, there
is no way for the optimizer to use different indices
for optimizing access since an expression in the WHERE
clause can only have a single collation.  That's what
I'm trying to fix

--
D. Richard Hipp  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Function question

[Anderson, James H \(IT\)]

That's an interesting idea, thanks, Kees. 

-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 01, 2007 2:57 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Function question


Hi Jim,

On Thu, 1 Feb 2007 09:08:44 -0500, you wrote:

>From the CLP (i.e., w/o user-defined functions) is there any way to get
>the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim
>spaces from the right/left end of a column value.)

As far as I can tell load_extension(X,Y) can be used in the
command line program. 

I usually postprocess output by piping it through awk to solve
these kinds of problems.

sqlite3 databasefile outfile

>jim

HTH
-- 
  (  Kees Nuyt
  )
c[_]


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> 
> In the standard character strings have a couple of attributes, a 
> character set and a collation. SQLite does not support multiple 
> character sets, so we can ignore that attribute. This leaves each string 
> with a collation attribute. This attribute can be specified explicitly 
> in data type clause of a column definition, or in the data type clause 
> of a cast expression, or directly with an explicit COLLATE clause after 
> a string expression, even on a string literal.
> 
> create table t (column1 text COLLATE )
> CAST(  AS text COLLATE )
> column1 COLLATE 
> 'a string' COLLATE 

What are the precedences.  If I say:

x COLLATE seq1 || y COLLATE seq2

Does that mean:

(x COLLATE seq1) || (y COLLATE seq2)

Or does it mean

((x COLLATE seq1) || y) COLLATE seq2

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Dennis Cote]

[EMAIL PROTECTED] wrote:


What are the precedences.  If I say:

x COLLATE seq1 || y COLLATE seq2

Does that mean:

(x COLLATE seq1) || (y COLLATE seq2)
  
Yes, I believe this is the meaning the standard would take. But notice 
that you have a dyadic function (concatenation) with two arguments that 
have explicit collations that are not equal (seq1 != seq2), so this 
should in fact generate a syntax error.



Or does it mean

((x COLLATE seq1) || y) COLLATE seq2


  

No, I fairly sure that wouldn't be interpreted this way.

I will check the standards syntax specifications and let you know how 
they say this should be parsed.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Ken]

   select x, y, z 
 from t1 
where collate binary x = y ;

 collating_expr ::=   [collate ] expr ;
 
 The collating expression would apply to both x and y.
 
[EMAIL PROTECTED] wrote: Dennis Cote  wrote:
> 
> In the standard character strings have a couple of attributes, a 
> character set and a collation. SQLite does not support multiple 
> character sets, so we can ignore that attribute. This leaves each string 
> with a collation attribute. This attribute can be specified explicitly 
> in data type clause of a column definition, or in the data type clause 
> of a cast expression, or directly with an explicit COLLATE clause after 
> a string expression, even on a string literal.
> 
> create table t (column1 text COLLATE )
> CAST(  AS text COLLATE )
> column1 COLLATE 
> 'a string' COLLATE 

What are the precedences.  If I say:

x COLLATE seq1 || y COLLATE seq2

Does that mean:

(x COLLATE seq1) || (y COLLATE seq2)

Or does it mean

((x COLLATE seq1) || y) COLLATE seq2

--
D. Richard Hipp  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Passing parameters Python and CGI

[Paul Issott]

Hi, I'm a newbie using sqlite 3 via Python 2.5, I've created a db using

CREATE TABLE messages (
id   INTEGER PRIMARY KEY,
subjectTEXT
)

Populated it with a few lines and one of the row of rows is (1, u'News') 
after:-

rows = curs.fetchall()

How do I pass these parameters in a cgi script, for instance I've tried 
using:-


print 'row[1]'

which only prints _row[1]_  instead of  _News_
and probably doesn't set the id either.

Thanks for any help.

Re: [sqlite] How to specify collating sequences in an expression.

[Dennis Cote]

Richard,

Some of the standard syntax definitions for the collate clauses are:

Table column definition

 ::=

{  |  }
[  ]
[  ]
[ ... ]
[  ]

A string expression

 ::=

| 
| 

 ::=

| 

 ::=
  

 ::=
 [  ]

 ::=

| 

The collate clause:

 ::= COLLATE 



The details about the use of collations is in section 4.2.3 "Rules 
determining collating sequence usage". I have copied that section from a 
pdf below (note the tables have been copied as graphics to preserve the 
layout, I hope they make it through):



4.2.3 Rules determining collating sequence usage

The rules determining collating sequence usage for character strings are 
based on the following:


— Expressions where no columns are involved (e.g., literals, host 
variables) are by default compared

using the default collating sequence for their character repertoire.
NOTE 3 – The default collating sequence for a character repertoire is 
defined in Subclause 10.6,
‘‘’’, and Subclause 11.30, ‘‘definition>’’.


— When one or more columns are involved (e.g., comparing two columns, or 
comparing a column to
a literal), then provided that all columns involved have the same 
default collating sequence and
there is no explicit specification of a collating sequence, that default 
collating sequence is used.


— When columns are involved having different default collating 
sequences, explicit specification of
the collating sequence in the expression is required via the clause>.


— Any explicit specification of collating sequence in an expression 
overrides any default collating

sequence.

To formalize this, s effectively have a 
coercibility characteristic. This
characteristic has the values Coercible, Implicit, No collating 
sequence, and Explicit. value expression>s with the Coercible, Implicit, or Explicit 
characteristics have a collating sequence.


A  consisting of a column reference has the 
coercibility characteristic
Implicit, with collating sequence as defined when the column was 
created. A expression> consisting of a value other than a column (e.g., a host 
variable or a literal) has the
coercibility characteristic Coercible, with the default collation for 
its character repertoire. A value expression> simply containing a  has the 
coercibility characteristic

Explicit, with the collating sequence specified in the .






For n-adic operations (e.g., ) with operands X1, X2, . 
. . , Xn, the collating sequence
is effectively determined by considering X1 and X2, then combining this 
result with X3, and so on.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> >
> > What are the precedences.  If I say:
> >
> > x COLLATE seq1 || y COLLATE seq2
> >
> > Does that mean:
> >
> > (x COLLATE seq1) || (y COLLATE seq2)
> >   
> Yes, I believe this is the meaning the standard would take. But notice 
> that you have a dyadic function (concatenation) with two arguments that 
> have explicit collations that are not equal (seq1 != seq2), so this 
> should in fact generate a syntax error.
> 

I guessed that COLLATE would bind more tightly than ||
(or any other operator).

Note that SQLite has historically resolved an ambiguous collation
by choosing the collation of the left operand.  I'll need to preserve
that behavior in order to maintain backwards compatibility.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Passing parameters Python and CGI

[P Kishor]

On 2/1/07, Paul Issott <[EMAIL PROTECTED]> wrote:

Hi, I'm a newbie using sqlite 3 via Python 2.5, I've created a db using

CREATE TABLE messages (
id   INTEGER PRIMARY KEY,
subjectTEXT
)

Populated it with a few lines and one of the row of rows is (1, u'News')
after:-
rows = curs.fetchall()

How do I pass these parameters in a cgi script, for instance I've tried
using:-

print 'row[1]'

which only prints _row[1]_  instead of  _News_
and probably doesn't set the id either.




your question actually has nothing to do with SQLite, but instead
relates to Python and CGI. I know nothing about Python, but in my
world (Perl/CGI), I would do

Given --

CREATE TABLE messages (
 id  INTEGER PRIMARY KEY,
 subject TEXT
)

In my code --

$sth = $dbh->prepare(qq{
 SELECT id, subject
 FROM messages
});

$sth->execute;

And then, in my html (I would never do it this way as I would use a
template, but anyhow) --

while (my $row = $sth->fetchrow_arrayref) {
 print "$row[1]";
}

$sth->finish;


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Does SQLite support user-defined data-types ?

[John Stanton]

If you are implementing JDBC to Sqlite then you just need to write a 
class.  You could use an existing JDBC class as a template.


Jerome CORRENOZ wrote:

 I'm implementing the SQLData interface. Is it the right one ?

John Stanton wrote:

You need to revise your Java interface or maybe find another.  What 
are you using?


Jerome CORRENOZ wrote:

Now, I can use user-dfined types by declaring them with SQL create 
tables. Fine !


But when I try to use the following code to get an object, I get a 
ClassCastException:

  Statement stmt = conn.createStatement();
  ResultSet rset = stmt.executeQuery(
  "SELECT * FROM EMP");
  while (rset.next()) {
  Dept dept = (Dept)rset.getObject("DEPT");
  }

Is it normal or is it due to the fact that SQLite doesn't support 
SQL3 commands, mainly the getTypeMap() command that maps a Java class 
with a SQL type ?
Notice that my Java class implements SQLData but it seems having no 
effect.


Thanks for your answer,
Jerome

John Stanton wrote:

Sqlite is flexible in typing.  You can create user defined types 
just by declaring them in your SQL which creates tables.  Thereafter 
the API will return the declared type and the actual type so that 
you can process it appropriately.


Jerome CORRENOZ wrote:


Hi,

I'm starting with SQLite and I would like to know if it is possible 
to create user-defined data-types through the following SQL 
command: create type MyType ... ?


In fact, I need it to map a database with an object-oriented model, 
so that I could manage the objects in a ResultSet like it follows:

   Statement stmt = conn.createStatement();
   ResultSet rset = stmt.executeQuery(
   "SELECT * FROM EMP");
   while (rset.next()) {
   Dept dept = (Dept)rset.getObject("DEPT");
   System.out.println("Lieu du dept : "
   + dept.getLieu());
   }

Is there a way to do it with SQLite ?

Regards,
Jerome




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 









- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 









-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
>> >
> > (x COLLATE seq1) || (y COLLATE seq2)
> >   
> Yes, I believe this is the meaning the standard would take. 

The words I am putting into the SQLite language spec are
shown below.  Please tell me if you thing my understanding
is correct:

  The COLLATE operator can be throught of as a unary
  postfix operator.  The COLLATE operator has the highest
  precedence.  It always binds more tightly than any
  unary prefix operator or any binary operator.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] UNIQUE constraint on column

[Shane Harrelson]

"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
>
> I have two tables, an "Objects" table with a foreign key into a second
> "Strings" table which is composed of unique values.  It is a many to
> one relationship, that is, several Objects may reference the same
> String.   When an Object is added, its associated String is added to
> the Strings table.   If the String already exists in the Strings
> table, I'd like the new Object to reference the existing copy.
>
> Currently, I've implemented it as so (leaving out error handling, etc.):
>
> begin transaction
> insert into Strings (value) VALUES ( 'foo')
> if string insert result is SQLITE_OK
>get rowid of last insert (sqlite3_last_insert_rowid)
> else if result is SQLITE_CONSTRAINT
>select rowid from Strings where value = 'foo'
> end if
> if rowid
>insert into Objects (string_id) VALUES (rowid)
> end if
> if no error
>commit transaction
> else
>rollback transaction
> end if
>
> With my dataset, there is about a 10% chance of the string being a
> duplicate -- that is about 1 in 10 string inserts hit the UNIQUE
> constraint violation.
>
> I've tested "viloating" the internals of the VBDE and pulling the
> rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT
> result is returned and it is measurably (5-10%) faster then doing the
> subsequent SELECT.
>

What you are doing is the most efficient way that I can think of
right off hand.  If "violating" the internals is something you want
to do that's fine - just remember that it is likely to break in
irrepaiable ways in some future point release.  No tears.

Notice that an sqlite3_last_constraint_rowid() function doesn't really
work because an insert might fail due to multiple constraint violations
all on different rows.  In your schema, perhaps, there can be no more
than one constraint violated at a time, but it is easy enough to
construct a schema where multiple rows can violate a constraint, so the
sqlite3_last_constraint_rowid() idea does not generalize well.

--
D. Richard Hipp  <[EMAIL PROTECTED]>



Thanks all for the feedback.   I was able to get comparable speed-ups
to my "vbde hack" on the string insertion by using a small "cache" of
rowids for previously inserted strings.  This reduced the duplicate
inserts to about 1 in 100.

I still think someway of getting the rowid of the record that forced
the query to end with a constraint violation (even if it isn't the
only one) would be useful.  Perhaps it could be added to the current
error string which already includes the column name(s).

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How do I know sqlite_get_table is finished

[RB Smissaert]

Using the VB wrapper dll SQLite3VB.dll from Todd Tanner's site:
http://www.tannertech.net/sqlite3vb/index.htm

In one particular procedure I had a serious problem when doing a call to
sqlite_get_table, causing Excel to crash. It took me a long time to pinpoint
the trouble as VBA debugging methods didn't help here. Eventually it
appeared that the trouble was calling sqlite3_close too soon after
sqlite_get_table.
Not sure if this makes sense, but after moving sqlite3_close some lines down
in that procedure the problem seems to be solved, so I take it the
connection was closed while SQLite was still fetching data, causing the
error and the Excel crash.

So what is the best way to determine if sqlite_get_table is finished?
Running number_of_rows_from_last_call in a loop might be something, but
maybe there is something better.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to specify collating sequences in an expression.

[Dennis Cote]

[EMAIL PROTECTED] wrote:


Note that SQLite has historically resolved an ambiguous collation
by choosing the collation of the left operand.  I'll need to preserve
that behavior in order to maintain backwards compatibility.

  

Richard,

Perhaps you could add (yet another | a) pragma that would allow a user 
to specify they want the database to use the SQL standard rules for 
selecting the collation to use. It would default to off for backwards 
compatibility, but could be turned on for standards conformance. This 
should probably be a sticky pragma (like page_size) that stays with the 
database so that the standard collations rules can be used in triggers etc.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How do I know sqlite_get_table is finished

[epankoke]

sqlite_get_table does not terminate unless there is an error or it has 
retrieved all the records you asked for.  Something else must have been causing 
the error, or the wrapper you are using is not implementing the function call 
correctly.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "RB Smissaert" <[EMAIL PROTECTED]>
> Using the VB wrapper dll SQLite3VB.dll from Todd Tanner's site:
> http://www.tannertech.net/sqlite3vb/index.htm
> 
> In one particular procedure I had a serious problem when doing a call to
> sqlite_get_table, causing Excel to crash. It took me a long time to pinpoint
> the trouble as VBA debugging methods didn't help here. Eventually it
> appeared that the trouble was calling sqlite3_close too soon after
> sqlite_get_table.
> Not sure if this makes sense, but after moving sqlite3_close some lines down
> in that procedure the problem seems to be solved, so I take it the
> connection was closed while SQLite was still fetching data, causing the
> error and the Excel crash.
> 
> So what is the best way to determine if sqlite_get_table is finished?
> Running number_of_rows_from_last_call in a loop might be something, but
> maybe there is something better.
> 
> RBS
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How do I know sqlite_get_table is finished

[RB Smissaert]

Thanks, yes, I somehow didn't think my explanation made sense and in fact I
just had another Excel crash, caused by the same call to sqlite_get_table.

I just can't understand why this is happening.
There is a valid connection, there is a valid SQL, the DB file is there and
working otherwise fine, etc.

The wrapper works otherwise 100% perfect and I only have the problem in this
particular procedure. I guess there must be a VBA bug then, but I just can't
find it.

RBS


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 01 February 2007 22:56
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How do I know sqlite_get_table is finished

sqlite_get_table does not terminate unless there is an error or it has
retrieved all the records you asked for.  Something else must have been
causing the error, or the wrapper you are using is not implementing the
function call correctly.

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "RB Smissaert" <[EMAIL PROTECTED]>
> Using the VB wrapper dll SQLite3VB.dll from Todd Tanner's site:
> http://www.tannertech.net/sqlite3vb/index.htm
> 
> In one particular procedure I had a serious problem when doing a call to
> sqlite_get_table, causing Excel to crash. It took me a long time to
pinpoint
> the trouble as VBA debugging methods didn't help here. Eventually it
> appeared that the trouble was calling sqlite3_close too soon after
> sqlite_get_table.
> Not sure if this makes sense, but after moving sqlite3_close some lines
down
> in that procedure the problem seems to be solved, so I take it the
> connection was closed while SQLite was still fetching data, causing the
> error and the Excel crash.
> 
> So what is the best way to determine if sqlite_get_table is finished?
> Running number_of_rows_from_last_call in a loop might be something, but
> maybe there is something better.
> 
> RBS
> 
> 
> 
>

-
> To unsubscribe, send email to [EMAIL PROTECTED]
>

-
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Passing parameters Python and CGI

[Paul Issott]

Think I managed to solve it using:-

print '%s' % (row[0], row[1])

Paul

Paul Issott wrote:

Hi, I'm a newbie using sqlite 3 via Python 2.5, I've created a db using

CREATE TABLE messages (
id   INTEGER PRIMARY KEY,
subjectTEXT
)

Populated it with a few lines and one of the row of rows is (1, 
u'News') after:-

rows = curs.fetchall()

How do I pass these parameters in a cgi script, for instance I've 
tried using:-


print 'row[1]'

which only prints _row[1]_  instead of  _News_
and probably doesn't set the id either.

Thanks for any help.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Sqlite used in open source phone system (pbx)

[Griggs, Donald]

Maybe this is old news, but I noticed that Sqlite is used by "OpenPBX,"
an open source PBX.
I would think it's yet another tribute to Sqlite's reliability that it
was chosen to run in a phone switch.

-

OpenPBX is a fork of the Asterisk PBX.

On the home page of the OpenPBX wiki:
   http://wiki.openpbx.org/tiki-index.php

Under the topic:

*Most important differences between OpenPBX and Asterisk*

they include:
 "OpenPBX.org uses Sqlite instead of Berkeley DB as its internal
database "


[opinions are mine, and not necessarily those of my company]

Re: [sqlite] How to specify collating sequences in an expression.

[Dennis Cote]

[EMAIL PROTECTED] wrote:



The words I am putting into the SQLite language spec are
shown below.  Please tell me if you thing my understanding
is correct:

  The COLLATE operator can be throught of as a unary
  postfix operator.  The COLLATE operator has the highest
  precedence.  It always binds more tightly than any
  unary prefix operator or any binary operator.


That sounds right to me, and seems to match the grammar I posted earlier 
for a string value expression.


You might need to be careful about the binding of the unary plus 
operator which you use to disable indexes. In an expression like this:


   +col1 COLLATE NOCASE

you may want The + to bind to the col1 first and then bind the collation 
to that expression. i.e.


   (+col1) COLLATE NOCASE

rather than

   +(col1 COLLATE NOCASE)


I'm wading through the standard grammar to see if there are other areas 
that may be of concern. So far the collate clause can optionally be 
applied to any , which can be a string valued 
function, a parenthesized expression, or one of many primaries> which I think will reduce to a column reference, subquery, 
case statement, or a cast in SQLite.


 ::=
   
   | 
   | 

 ::=
   
   | 

 ::=
 factor>


 ::=
[  ]

 ::=
   
   | 

 ::=
   
   | 

 ::=
 

 ::=
   
   | 
   | 
   | 
   | 
   | 
   | 
   | 
   | 
   | 
   | 
   | 
   | 
   | 
   | 
   | 

Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How do I know sqlite_get_table is finished

[Guy Hachlili]

Well...

At 23:17 2/1/2007 +, you wrote:

Thanks, yes, I somehow didn't think my explanation made sense and in fact I
just had another Excel crash, caused by the same call to sqlite_get_table.

I just can't understand why this is happening.
There is a valid connection, there is a valid SQL, the DB file is there and
working otherwise fine, etc.

The wrapper works otherwise 100% perfect and I only have the problem in this
particular procedure. I guess there must be a VBA bug then, but I just can't
find it.


Is it possible that you are using the array returned by the function after 
you close the database, and that the VB wrapper frees the data when the 
database is closed?
If that is the case, you will probably get a crash as you are accessing a 
memory that was already freed.


Guy



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Appropriate uses for SQLite

[Anil Gulati -X \(agulati - Michael Page at Cisco\)]

Hi SQLite users

Thank you for your attention - I am just hoping for some clarification
of usability of SQLite.
Referring to: http://www.sqlite.org/whentouse.html
- SQLite works well in websites
- Other RDBMS may work better for Client/Server applications
- SQLite will work over a network file system, but because of the
latency associated with most network file systems, performance will not
be great

I am trying to decide whether I can use SQLite for a website that runs
on 4 load-balanced servers using networked file storage mounted by all
servers for common data access. SQLite will have to load its database
from the network file space.

These servers run multiple web-sites, hence the additional servers, but
my pages are not high hit-rate. The sites I am planning anticipate a
maximum of 200 users altogether. Current raw, uncompressed data (mostly
text) is about 2MB growing to around 4MB. The current starter database
of 1.6MB raw compresses to 963KB.

My concerns are:

1. Network file system
How bad is the latency introduced from using a network file system?

2. Concurrent access
I can't understand how SQLite is recommended for 99.9% of websites but
only *high* concurrency is not recommended? I currently use a flat-file
system which uses a single file per record. If users happen to write to
the same record simultaneously one of the updates will be lost but
corruption is highly unlikely, if not impossible. It seems that for
SQLite the risk for concurrent access is always data corruption, which
would be unacceptable for me.

The issue is that there may be short periods where multiple users will
be updating around the same time and I want to make sure that the
possibility of corruption is extremely low. I am asking for more
detailed information on the above issues to clarify my decision.

All feedback gratefully received.
Thanks.
Anil.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How do I know sqlite_get_table is finished

[RB Smissaert]

Good thought, but
1. I set the connection
2. get the array
3. dump it in the sheet
4. and then close the connection.

The crash happens at number 2.
I am sure I must be overlooking something simple here. The trouble is that
this error is not consistent.

RBS


-Original Message-
From: Guy Hachlili [mailto:[EMAIL PROTECTED] 
Sent: 01 February 2007 23:50
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] How do I know sqlite_get_table is finished

Well...

At 23:17 2/1/2007 +, you wrote:
>Thanks, yes, I somehow didn't think my explanation made sense and in fact I
>just had another Excel crash, caused by the same call to sqlite_get_table.
>
>I just can't understand why this is happening.
>There is a valid connection, there is a valid SQL, the DB file is there and
>working otherwise fine, etc.
>
>The wrapper works otherwise 100% perfect and I only have the problem in
this
>particular procedure. I guess there must be a VBA bug then, but I just
can't
>find it.

Is it possible that you are using the array returned by the function after 
you close the database, and that the VB wrapper frees the data when the 
database is closed?
If that is the case, you will probably get a crash as you are accessing a 
memory that was already freed.

Guy




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Appropriate uses for SQLite

[Philip Butler]

I am not an expert on SQLite - but if you are running separate  
websites from your multiple servers, then why not use 4 instances of  
SQLite ??  That is unless the websites need to share the same  
database/tables.


If they do need to share the same database/tables, then PostgreSQL or  
MySQL may be the more appropriate choice.  They are designed to be  
distributed (hence their increased overhead) while SQLite is designed  
to be lean-and-mean.


Just my 2 cents worth...

Phil

On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page  
at Cisco)) wrote:



Hi SQLite users

Thank you for your attention - I am just hoping for some clarification
of usability of SQLite.
Referring to: http://www.sqlite.org/whentouse.html
- SQLite works well in websites
- Other RDBMS may work better for Client/Server applications
- SQLite will work over a network file system, but because of the
latency associated with most network file systems, performance will  
not

be great

I am trying to decide whether I can use SQLite for a website that runs
on 4 load-balanced servers using networked file storage mounted by all
servers for common data access. SQLite will have to load its database
from the network file space.

These servers run multiple web-sites, hence the additional servers,  
but

my pages are not high hit-rate. The sites I am planning anticipate a
maximum of 200 users altogether. Current raw, uncompressed data  
(mostly

text) is about 2MB growing to around 4MB. The current starter database
of 1.6MB raw compresses to 963KB.

My concerns are:

1. Network file system
How bad is the latency introduced from using a network file system?

2. Concurrent access
I can't understand how SQLite is recommended for 99.9% of websites but
only *high* concurrency is not recommended? I currently use a flat- 
file
system which uses a single file per record. If users happen to  
write to

the same record simultaneously one of the updates will be lost but
corruption is highly unlikely, if not impossible. It seems that for
SQLite the risk for concurrent access is always data corruption, which
would be unacceptable for me.

The issue is that there may be short periods where multiple users will
be updating around the same time and I want to make sure that the
possibility of corruption is extremely low. I am asking for more
detailed information on the above issues to clarify my decision.

All feedback gratefully received.
Thanks.
Anil.

-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Appropriate uses for SQLite

[Anil Gulati -X \(agulati - Michael Page at Cisco\)]

Thanks for replying Phil...

Actually I am not running separate websites - but I have to deploy to
multiple webservers which will all serve the same pages. Each webserver
will have their own copy of the SQLite code, but they need to load the
data from a network file server to share the same data.

I guess this is why I am asking for feedback: it seems that this case is
a marginal case for SQLite and I am just trying to assess performance
and corruption possibilities in more detail than is presented on the
SQLite web pages.

The main point that encourages me to try SQLite is that it is
recommended for 99.9% of websites. I believe my traffic is very low and
SQLite should be recommended from that point of view.

However, although the likelihood of two users simultaneously updating a
particular record is going to be very low I believe it is going to
happen that two users will try to update the database simultaneously.

I know that SQLite has some file locking features that have even been
improved in v 3.0. So:
- will simultaneous *database* access result in corruption?
- will simultaneous *record* access result in corruption?
- if not, when *can* corruption occur?

I don't mind making the users wait in the unlikely event of a record
collision, or even drop data once in a blue moon, but corruption is not
acceptable.

Thanks again.
Anil.

-Original Message-
From: Philip Butler [mailto:[EMAIL PROTECTED] 
Sent: Friday, 2 February 2007 11:39 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

I am not an expert on SQLite - but if you are running separate websites
from your multiple servers, then why not use 4 instances of SQLite ??
That is unless the websites need to share the same database/tables.

If they do need to share the same database/tables, then PostgreSQL or
MySQL may be the more appropriate choice.  They are designed to be
distributed (hence their increased overhead) while SQLite is designed to
be lean-and-mean.

Just my 2 cents worth...

Phil

On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page at
Cisco)) wrote:

> Hi SQLite users
>
> Thank you for your attention - I am just hoping for some clarification

> of usability of SQLite.
> Referring to: http://www.sqlite.org/whentouse.html
> - SQLite works well in websites
> - Other RDBMS may work better for Client/Server applications
> - SQLite will work over a network file system, but because of the 
> latency associated with most network file systems, performance will 
> not be great
>
> I am trying to decide whether I can use SQLite for a website that runs

> on 4 load-balanced servers using networked file storage mounted by all

> servers for common data access. SQLite will have to load its database 
> from the network file space.
>
> These servers run multiple web-sites, hence the additional servers, 
> but my pages are not high hit-rate. The sites I am planning anticipate

> a maximum of 200 users altogether. Current raw, uncompressed data 
> (mostly
> text) is about 2MB growing to around 4MB. The current starter database

> of 1.6MB raw compresses to 963KB.
>
> My concerns are:
>
> 1. Network file system
> How bad is the latency introduced from using a network file system?
>
> 2. Concurrent access
> I can't understand how SQLite is recommended for 99.9% of websites but

> only *high* concurrency is not recommended? I currently use a flat- 
> file system which uses a single file per record. If users happen to 
> write to the same record simultaneously one of the updates will be 
> lost but corruption is highly unlikely, if not impossible. It seems 
> that for SQLite the risk for concurrent access is always data 
> corruption, which would be unacceptable for me.
>
> The issue is that there may be short periods where multiple users will

> be updating around the same time and I want to make sure that the 
> possibility of corruption is extremely low. I am asking for more 
> detailed information on the above issues to clarify my decision.
>
> All feedback gratefully received.
> Thanks.
> Anil.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Appropriate uses for SQLite

[Philip Butler]

And this is when I'll step back and listen to the experts...

Since it is a low-load situation, file/record locking on SQLite seems  
like it would be acceptable to me.


As for data corruption - that's bad -- very bad.  However, with  
automated backups some degree of comfort may be realized.  With the  
db systems that I have designed, I have an automatic process that  
dumps the db to a text file every 4 hours or so.  These are kept for  
a couple of days.  I sleep easy at night knowing this...


Phil

On Feb 1, 2007, at 7:59 PM, Anil Gulati -X ((agulati - Michael Page  
at Cisco)) wrote:



Thanks for replying Phil...

Actually I am not running separate websites - but I have to deploy to
multiple webservers which will all serve the same pages. Each  
webserver

will have their own copy of the SQLite code, but they need to load the
data from a network file server to share the same data.

I guess this is why I am asking for feedback: it seems that this  
case is

a marginal case for SQLite and I am just trying to assess performance
and corruption possibilities in more detail than is presented on the
SQLite web pages.

The main point that encourages me to try SQLite is that it is
recommended for 99.9% of websites. I believe my traffic is very low  
and

SQLite should be recommended from that point of view.

However, although the likelihood of two users simultaneously  
updating a

particular record is going to be very low I believe it is going to
happen that two users will try to update the database simultaneously.

I know that SQLite has some file locking features that have even been
improved in v 3.0. So:
- will simultaneous *database* access result in corruption?
- will simultaneous *record* access result in corruption?
- if not, when *can* corruption occur?

I don't mind making the users wait in the unlikely event of a record
collision, or even drop data once in a blue moon, but corruption is  
not

acceptable.

Thanks again.
Anil.

-Original Message-
From: Philip Butler [mailto:[EMAIL PROTECTED]
Sent: Friday, 2 February 2007 11:39 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

I am not an expert on SQLite - but if you are running separate  
websites

from your multiple servers, then why not use 4 instances of SQLite ??
That is unless the websites need to share the same database/tables.

If they do need to share the same database/tables, then PostgreSQL or
MySQL may be the more appropriate choice.  They are designed to be
distributed (hence their increased overhead) while SQLite is  
designed to

be lean-and-mean.

Just my 2 cents worth...

Phil

On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page at
Cisco)) wrote:


Hi SQLite users

Thank you for your attention - I am just hoping for some  
clarification



of usability of SQLite.
Referring to: http://www.sqlite.org/whentouse.html
- SQLite works well in websites
- Other RDBMS may work better for Client/Server applications
- SQLite will work over a network file system, but because of the
latency associated with most network file systems, performance will
not be great

I am trying to decide whether I can use SQLite for a website that  
runs


on 4 load-balanced servers using networked file storage mounted by  
all



servers for common data access. SQLite will have to load its database
from the network file space.

These servers run multiple web-sites, hence the additional servers,
but my pages are not high hit-rate. The sites I am planning  
anticipate



a maximum of 200 users altogether. Current raw, uncompressed data
(mostly
text) is about 2MB growing to around 4MB. The current starter  
database



of 1.6MB raw compresses to 963KB.

My concerns are:

1. Network file system
How bad is the latency introduced from using a network file system?

2. Concurrent access
I can't understand how SQLite is recommended for 99.9% of websites  
but



only *high* concurrency is not recommended? I currently use a flat-
file system which uses a single file per record. If users happen to
write to the same record simultaneously one of the updates will be
lost but corruption is highly unlikely, if not impossible. It seems
that for SQLite the risk for concurrent access is always data
corruption, which would be unacceptable for me.

The issue is that there may be short periods where multiple users  
will



be updating around the same time and I want to make sure that the
possibility of corruption is extremely low. I am asking for more
detailed information on the above issues to clarify my decision.

All feedback gratefully received.
Thanks.
Anil.


-- 
---

To unsubscribe, send email to [EMAIL PROTECTED]
-- 
---



-
To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] Appropriate uses for SQLite

[Scott Hess]

I'd be _extremely_ leery of doing this on a network store.  In theory,
it should work just fine, but bridging theory and practice may very
well cost you many sleepless nights.  sqlite is in many ways easier
than mysql, but mysql isn't _that_ much harder to use, and it just
won't have this class of issue.

-scott

[Ironically, one of the many inputs to my position on storing
databases on a network filesystem was an experience using NFS for
mysql storage :-).]


On 2/1/07, Philip Butler <[EMAIL PROTECTED]> wrote:

And this is when I'll step back and listen to the experts...

Since it is a low-load situation, file/record locking on SQLite seems
like it would be acceptable to me.

As for data corruption - that's bad -- very bad.  However, with
automated backups some degree of comfort may be realized.  With the
db systems that I have designed, I have an automatic process that
dumps the db to a text file every 4 hours or so.  These are kept for
a couple of days.  I sleep easy at night knowing this...

Phil

On Feb 1, 2007, at 7:59 PM, Anil Gulati -X ((agulati - Michael Page
at Cisco)) wrote:

> Thanks for replying Phil...
>
> Actually I am not running separate websites - but I have to deploy to
> multiple webservers which will all serve the same pages. Each
> webserver
> will have their own copy of the SQLite code, but they need to load the
> data from a network file server to share the same data.
>
> I guess this is why I am asking for feedback: it seems that this
> case is
> a marginal case for SQLite and I am just trying to assess performance
> and corruption possibilities in more detail than is presented on the
> SQLite web pages.
>
> The main point that encourages me to try SQLite is that it is
> recommended for 99.9% of websites. I believe my traffic is very low
> and
> SQLite should be recommended from that point of view.
>
> However, although the likelihood of two users simultaneously
> updating a
> particular record is going to be very low I believe it is going to
> happen that two users will try to update the database simultaneously.
>
> I know that SQLite has some file locking features that have even been
> improved in v 3.0. So:
> - will simultaneous *database* access result in corruption?
> - will simultaneous *record* access result in corruption?
> - if not, when *can* corruption occur?
>
> I don't mind making the users wait in the unlikely event of a record
> collision, or even drop data once in a blue moon, but corruption is
> not
> acceptable.
>
> Thanks again.
> Anil.
>
> -Original Message-
> From: Philip Butler [mailto:[EMAIL PROTECTED]
> Sent: Friday, 2 February 2007 11:39 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Appropriate uses for SQLite
>
> I am not an expert on SQLite - but if you are running separate
> websites
> from your multiple servers, then why not use 4 instances of SQLite ??
> That is unless the websites need to share the same database/tables.
>
> If they do need to share the same database/tables, then PostgreSQL or
> MySQL may be the more appropriate choice.  They are designed to be
> distributed (hence their increased overhead) while SQLite is
> designed to
> be lean-and-mean.
>
> Just my 2 cents worth...
>
> Phil
>
> On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page at
> Cisco)) wrote:
>
>> Hi SQLite users
>>
>> Thank you for your attention - I am just hoping for some
>> clarification
>
>> of usability of SQLite.
>> Referring to: http://www.sqlite.org/whentouse.html
>> - SQLite works well in websites
>> - Other RDBMS may work better for Client/Server applications
>> - SQLite will work over a network file system, but because of the
>> latency associated with most network file systems, performance will
>> not be great
>>
>> I am trying to decide whether I can use SQLite for a website that
>> runs
>
>> on 4 load-balanced servers using networked file storage mounted by
>> all
>
>> servers for common data access. SQLite will have to load its database
>> from the network file space.
>>
>> These servers run multiple web-sites, hence the additional servers,
>> but my pages are not high hit-rate. The sites I am planning
>> anticipate
>
>> a maximum of 200 users altogether. Current raw, uncompressed data
>> (mostly
>> text) is about 2MB growing to around 4MB. The current starter
>> database
>
>> of 1.6MB raw compresses to 963KB.
>>
>> My concerns are:
>>
>> 1. Network file system
>> How bad is the latency introduced from using a network file system?
>>
>> 2. Concurrent access
>> I can't understand how SQLite is recommended for 99.9% of websites
>> but
>
>> only *high* concurrency is not recommended? I currently use a flat-
>> file system which uses a single file per record. If users happen to
>> write to the same record simultaneously one of the updates will be
>> lost but corruption is highly unlikely, if not impossible. It seems
>> that for SQLite the risk for concurrent access is always data
>> corruption, which would be unacceptable for 

RE: [sqlite] Appropriate uses for SQLite

[Anil Gulati -X \(agulati - Michael Page at Cisco\)]

That's what I'm talking about! It's good to get the perspective from
your setup, Phil. I'm beginning to get the picture.

I am starting to think that I should stick to an enhancement of my
current system. It's *very* basic, even more basic than SQLite, but
corruption seems almost impossible when data is in separate files with
no compression, etc.

Thanks for taking the time.
Anil.

-Original Message-
From: Philip Butler [mailto:[EMAIL PROTECTED] 
Sent: Friday, 2 February 2007 12:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

And this is when I'll step back and listen to the experts...

Since it is a low-load situation, file/record locking on SQLite seems
like it would be acceptable to me.

As for data corruption - that's bad -- very bad.  However, with
automated backups some degree of comfort may be realized.  With the db
systems that I have designed, I have an automatic process that dumps the
db to a text file every 4 hours or so.  These are kept for a couple of
days.  I sleep easy at night knowing this...

Phil

On Feb 1, 2007, at 7:59 PM, Anil Gulati -X ((agulati - Michael Page at
Cisco)) wrote:

> Thanks for replying Phil...
>
> Actually I am not running separate websites - but I have to deploy to 
> multiple webservers which will all serve the same pages. Each 
> webserver will have their own copy of the SQLite code, but they need 
> to load the data from a network file server to share the same data.
>
> I guess this is why I am asking for feedback: it seems that this case 
> is a marginal case for SQLite and I am just trying to assess 
> performance and corruption possibilities in more detail than is 
> presented on the SQLite web pages.
>
> The main point that encourages me to try SQLite is that it is 
> recommended for 99.9% of websites. I believe my traffic is very low 
> and SQLite should be recommended from that point of view.
>
> However, although the likelihood of two users simultaneously updating 
> a particular record is going to be very low I believe it is going to 
> happen that two users will try to update the database simultaneously.
>
> I know that SQLite has some file locking features that have even been 
> improved in v 3.0. So:
> - will simultaneous *database* access result in corruption?
> - will simultaneous *record* access result in corruption?
> - if not, when *can* corruption occur?
>
> I don't mind making the users wait in the unlikely event of a record 
> collision, or even drop data once in a blue moon, but corruption is 
> not acceptable.
>
> Thanks again.
> Anil.
>
> -Original Message-
> From: Philip Butler [mailto:[EMAIL PROTECTED]
> Sent: Friday, 2 February 2007 11:39 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Appropriate uses for SQLite
>
> I am not an expert on SQLite - but if you are running separate 
> websites from your multiple servers, then why not use 4 instances of 
> SQLite ??
> That is unless the websites need to share the same database/tables.
>
> If they do need to share the same database/tables, then PostgreSQL or 
> MySQL may be the more appropriate choice.  They are designed to be 
> distributed (hence their increased overhead) while SQLite is designed 
> to be lean-and-mean.
>
> Just my 2 cents worth...
>
> Phil
>
> On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page at
> Cisco)) wrote:
>
>> Hi SQLite users
>>
>> Thank you for your attention - I am just hoping for some 
>> clarification
>
>> of usability of SQLite.
>> Referring to: http://www.sqlite.org/whentouse.html
>> - SQLite works well in websites
>> - Other RDBMS may work better for Client/Server applications
>> - SQLite will work over a network file system, but because of the 
>> latency associated with most network file systems, performance will 
>> not be great
>>
>> I am trying to decide whether I can use SQLite for a website that 
>> runs
>
>> on 4 load-balanced servers using networked file storage mounted by 
>> all
>
>> servers for common data access. SQLite will have to load its database

>> from the network file space.
>>
>> These servers run multiple web-sites, hence the additional servers, 
>> but my pages are not high hit-rate. The sites I am planning 
>> anticipate
>
>> a maximum of 200 users altogether. Current raw, uncompressed data 
>> (mostly
>> text) is about 2MB growing to around 4MB. The current starter 
>> database
>
>> of 1.6MB raw compresses to 963KB.
>>
>> My concerns are:
>>
>> 1. Network file system
>> How bad is the latency introduced from using a network file system?
>>
>> 2. Concurrent access
>> I can't understand how SQLite is recommended for 99.9% of websites 
>> but
>
>> only *high* concurrency is not recommended? I currently use a flat- 
>> file system which uses a single file per record. If users happen to 
>> write to the same record simultaneously one of the updates will be 
>> lost but corruption is highly unlikely, if not impossible. It seems 
>> that for SQLite the risk for 

RE: [sqlite] Appropriate uses for SQLite

[Anil Gulati -X \(agulati - Michael Page at Cisco\)]

Hmmm.. Seems to confirm my previous feeling, Scott. You even used the
word 'extremely', plus 'sleepless nights'. With the current workload I
need all the sleep I can get.

Trouble is, I don't have admin access to the server. All my solutions
need to be user deployed. Already have access to Oracle anyway, but that
seems to incur a large latency for the connection (3 seconds or so) and
has other contra-indications. I am looking for something very
lightweight and self-contained.

Thanks for your post.
Anil.

-Original Message-
From: Scott Hess [mailto:[EMAIL PROTECTED] 
Sent: Friday, 2 February 2007 12:24 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

I'd be _extremely_ leery of doing this on a network store.  In theory,
it should work just fine, but bridging theory and practice may very well
cost you many sleepless nights.  sqlite is in many ways easier than
mysql, but mysql isn't _that_ much harder to use, and it just won't have
this class of issue.

-scott

[Ironically, one of the many inputs to my position on storing databases
on a network filesystem was an experience using NFS for mysql storage
:-).]

On 2/1/07, Philip Butler <[EMAIL PROTECTED]> wrote:
> And this is when I'll step back and listen to the experts...
>
> Since it is a low-load situation, file/record locking on SQLite seems 
> like it would be acceptable to me.
>
> As for data corruption - that's bad -- very bad.  However, with 
> automated backups some degree of comfort may be realized.  With the db

> systems that I have designed, I have an automatic process that dumps 
> the db to a text file every 4 hours or so.  These are kept for a 
> couple of days.  I sleep easy at night knowing this...
>
> Phil
>
> On Feb 1, 2007, at 7:59 PM, Anil Gulati -X ((agulati - Michael Page at

> Cisco)) wrote:
>
> > Thanks for replying Phil...
> >
> > Actually I am not running separate websites - but I have to deploy 
> > to multiple webservers which will all serve the same pages. Each 
> > webserver will have their own copy of the SQLite code, but they need

> > to load the data from a network file server to share the same data.
> >
> > I guess this is why I am asking for feedback: it seems that this 
> > case is a marginal case for SQLite and I am just trying to assess 
> > performance and corruption possibilities in more detail than is 
> > presented on the SQLite web pages.
> >
> > The main point that encourages me to try SQLite is that it is 
> > recommended for 99.9% of websites. I believe my traffic is very low 
> > and SQLite should be recommended from that point of view.
> >
> > However, although the likelihood of two users simultaneously 
> > updating a particular record is going to be very low I believe it is

> > going to happen that two users will try to update the database 
> > simultaneously.
> >
> > I know that SQLite has some file locking features that have even 
> > been improved in v 3.0. So:
> > - will simultaneous *database* access result in corruption?
> > - will simultaneous *record* access result in corruption?
> > - if not, when *can* corruption occur?
> >
> > I don't mind making the users wait in the unlikely event of a record

> > collision, or even drop data once in a blue moon, but corruption is 
> > not acceptable.
> >
> > Thanks again.
> > Anil.
> >
> > -Original Message-
> > From: Philip Butler [mailto:[EMAIL PROTECTED]
> > Sent: Friday, 2 February 2007 11:39 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Appropriate uses for SQLite
> >
> > I am not an expert on SQLite - but if you are running separate 
> > websites from your multiple servers, then why not use 4 instances of

> > SQLite ??
> > That is unless the websites need to share the same database/tables.
> >
> > If they do need to share the same database/tables, then PostgreSQL 
> > or MySQL may be the more appropriate choice.  They are designed to 
> > be distributed (hence their increased overhead) while SQLite is 
> > designed to be lean-and-mean.
> >
> > Just my 2 cents worth...
> >
> > Phil
> >
> > On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page 
> > at
> > Cisco)) wrote:
> >
> >> Hi SQLite users
> >>
> >> Thank you for your attention - I am just hoping for some 
> >> clarification
> >
> >> of usability of SQLite.
> >> Referring to: http://www.sqlite.org/whentouse.html
> >> - SQLite works well in websites
> >> - Other RDBMS may work better for Client/Server applications
> >> - SQLite will work over a network file system, but because of the 
> >> latency associated with most network file systems, performance will

> >> not be great
> >>
> >> I am trying to decide whether I can use SQLite for a website that 
> >> runs
> >
> >> on 4 load-balanced servers using networked file storage mounted by 
> >> all
> >
> >> servers for common data access. SQLite will have to load its 
> >> database from the network file space.
> >>
> >> These servers run multiple web-sites, hence the additional 

RE: [sqlite] How do I know sqlite_get_table is finished

[Eric Pankoke]

Is it possible for you to post the "offending" block of VBA code?  Even
seeing your list of steps, it might be easier to help if we can view the
actual syntax.

Eric Pankoke
Founder
Point Of Light Software
http://www.polsoftware.com/
 
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 01, 2007 7:06 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] How do I know sqlite_get_table is finished

Good thought, but
1. I set the connection
2. get the array
3. dump it in the sheet
4. and then close the connection.

The crash happens at number 2.
I am sure I must be overlooking something simple here. The trouble is
that
this error is not consistent.

RBS


-Original Message-
From: Guy Hachlili [mailto:[EMAIL PROTECTED] 
Sent: 01 February 2007 23:50
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] How do I know sqlite_get_table is finished

Well...

At 23:17 2/1/2007 +, you wrote:
>Thanks, yes, I somehow didn't think my explanation made sense and in
fact I
>just had another Excel crash, caused by the same call to
sqlite_get_table.
>
>I just can't understand why this is happening.
>There is a valid connection, there is a valid SQL, the DB file is there
and
>working otherwise fine, etc.
>
>The wrapper works otherwise 100% perfect and I only have the problem in
this
>particular procedure. I guess there must be a VBA bug then, but I just
can't
>find it.

Is it possible that you are using the array returned by the function
after 
you close the database, and that the VB wrapper frees the data when the 
database is closed?
If that is the case, you will probably get a crash as you are accessing
a 
memory that was already freed.

Guy





-
To unsubscribe, send email to [EMAIL PROTECTED]


-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Appropriate uses for SQLite

[Eric Pankoke]

By separate files, do you mean that each user has their own data store?
If so, why not do the same thing with SQLite?  If each user has a unique
name or ID, append that to a generic file name and you have a separate
instance for each user.  I don't believe there's that much overhead to
the system tables that SQLite uses, and you solve your concurrency
issues.

Eric Pankoke
Founder
Point Of Light Software
http://www.polsoftware.com/
 

-Original Message-
From: Anil Gulati -X (agulati - Michael Page at Cisco)
[mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 01, 2007 8:27 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Appropriate uses for SQLite

That's what I'm talking about! It's good to get the perspective from
your setup, Phil. I'm beginning to get the picture.

I am starting to think that I should stick to an enhancement of my
current system. It's *very* basic, even more basic than SQLite, but
corruption seems almost impossible when data is in separate files with
no compression, etc.

Thanks for taking the time.
Anil.

-Original Message-
From: Philip Butler [mailto:[EMAIL PROTECTED] 
Sent: Friday, 2 February 2007 12:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

And this is when I'll step back and listen to the experts...

Since it is a low-load situation, file/record locking on SQLite seems
like it would be acceptable to me.

As for data corruption - that's bad -- very bad.  However, with
automated backups some degree of comfort may be realized.  With the db
systems that I have designed, I have an automatic process that dumps the
db to a text file every 4 hours or so.  These are kept for a couple of
days.  I sleep easy at night knowing this...

Phil

On Feb 1, 2007, at 7:59 PM, Anil Gulati -X ((agulati - Michael Page at
Cisco)) wrote:

> Thanks for replying Phil...
>
> Actually I am not running separate websites - but I have to deploy to 
> multiple webservers which will all serve the same pages. Each 
> webserver will have their own copy of the SQLite code, but they need 
> to load the data from a network file server to share the same data.
>
> I guess this is why I am asking for feedback: it seems that this case 
> is a marginal case for SQLite and I am just trying to assess 
> performance and corruption possibilities in more detail than is 
> presented on the SQLite web pages.
>
> The main point that encourages me to try SQLite is that it is 
> recommended for 99.9% of websites. I believe my traffic is very low 
> and SQLite should be recommended from that point of view.
>
> However, although the likelihood of two users simultaneously updating 
> a particular record is going to be very low I believe it is going to 
> happen that two users will try to update the database simultaneously.
>
> I know that SQLite has some file locking features that have even been 
> improved in v 3.0. So:
> - will simultaneous *database* access result in corruption?
> - will simultaneous *record* access result in corruption?
> - if not, when *can* corruption occur?
>
> I don't mind making the users wait in the unlikely event of a record 
> collision, or even drop data once in a blue moon, but corruption is 
> not acceptable.
>
> Thanks again.
> Anil.
>
> -Original Message-
> From: Philip Butler [mailto:[EMAIL PROTECTED]
> Sent: Friday, 2 February 2007 11:39 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Appropriate uses for SQLite
>
> I am not an expert on SQLite - but if you are running separate 
> websites from your multiple servers, then why not use 4 instances of 
> SQLite ??
> That is unless the websites need to share the same database/tables.
>
> If they do need to share the same database/tables, then PostgreSQL or 
> MySQL may be the more appropriate choice.  They are designed to be 
> distributed (hence their increased overhead) while SQLite is designed 
> to be lean-and-mean.
>
> Just my 2 cents worth...
>
> Phil
>
> On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page at
> Cisco)) wrote:
>
>> Hi SQLite users
>>
>> Thank you for your attention - I am just hoping for some 
>> clarification
>
>> of usability of SQLite.
>> Referring to: http://www.sqlite.org/whentouse.html
>> - SQLite works well in websites
>> - Other RDBMS may work better for Client/Server applications
>> - SQLite will work over a network file system, but because of the 
>> latency associated with most network file systems, performance will 
>> not be great
>>
>> I am trying to decide whether I can use SQLite for a website that 
>> runs
>
>> on 4 load-balanced servers using networked file storage mounted by 
>> all
>
>> servers for common data access. SQLite will have to load its database

>> from the network file space.
>>
>> These servers run multiple web-sites, hence the additional servers, 
>> but my pages are not high hit-rate. The sites I am planning 
>> anticipate
>
>> a maximum of 200 users altogether. Current raw, uncompressed data 
>> (mostly

RE: [sqlite] Appropriate uses for SQLite

[WB Stow]

I think that he said that he is running one website on four different
servers using loadbalancing, in which case they do need to all share the
same database.

I have not tested this with SQLite, but if all four servers are connected
via a gigabit ethernet backbone then you shouldn't have to worry about
network latency much.

I do not know anything about the warning about using SQLite with *high
concurrency* websites. I thought that I read something about locking issues,
but maybe someone else on the list can clarify that warning a little.

Wayne

-Original Message-
From: Philip Butler [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 01, 2007 7:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

I am not an expert on SQLite - but if you are running separate  
websites from your multiple servers, then why not use 4 instances of  
SQLite ??  That is unless the websites need to share the same  
database/tables.

If they do need to share the same database/tables, then PostgreSQL or  
MySQL may be the more appropriate choice.  They are designed to be  
distributed (hence their increased overhead) while SQLite is designed  
to be lean-and-mean.

Just my 2 cents worth...

Phil

On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page  
at Cisco)) wrote:

> Hi SQLite users
>
> Thank you for your attention - I am just hoping for some clarification
> of usability of SQLite.
> Referring to: http://www.sqlite.org/whentouse.html
> - SQLite works well in websites
> - Other RDBMS may work better for Client/Server applications
> - SQLite will work over a network file system, but because of the
> latency associated with most network file systems, performance will  
> not
> be great
>
> I am trying to decide whether I can use SQLite for a website that runs
> on 4 load-balanced servers using networked file storage mounted by all
> servers for common data access. SQLite will have to load its database
> from the network file space.
>
> These servers run multiple web-sites, hence the additional servers,  
> but
> my pages are not high hit-rate. The sites I am planning anticipate a
> maximum of 200 users altogether. Current raw, uncompressed data  
> (mostly
> text) is about 2MB growing to around 4MB. The current starter database
> of 1.6MB raw compresses to 963KB.
>
> My concerns are:
>
> 1. Network file system
> How bad is the latency introduced from using a network file system?
>
> 2. Concurrent access
> I can't understand how SQLite is recommended for 99.9% of websites but
> only *high* concurrency is not recommended? I currently use a flat- 
> file
> system which uses a single file per record. If users happen to  
> write to
> the same record simultaneously one of the updates will be lost but
> corruption is highly unlikely, if not impossible. It seems that for
> SQLite the risk for concurrent access is always data corruption, which
> would be unacceptable for me.
>
> The issue is that there may be short periods where multiple users will
> be updating around the same time and I want to make sure that the
> possibility of corruption is extremely low. I am asking for more
> detailed information on the above issues to clarify my decision.
>
> All feedback gratefully received.
> Thanks.
> Anil.
>
> -- 
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> -- 
> ---



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Appropriate uses for SQLite

[Anil Gulati -X \(agulati - Michael Page at Cisco\)]

That's the principle I am currently using, but that functionality is
extended and packaged in a Perl module. There is not one store per user,
data has to be shared between users. Each file is named by a multi-part
index with other non-indexed data also inside the file. Files in
multiple directories are used. For instance, in one example one
directory has 743 files, another 715 and another only 1 file.

The file separation is half the effort for storing the data in this
solution. I don't think it's worth extending that into SQLite. I am
looking at SQLite as a solution to replace that effort.

Thanks Eric.
Anil.

-Original Message-
From: Eric Pankoke [mailto:[EMAIL PROTECTED] 
Sent: Friday, 2 February 2007 1:08 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Appropriate uses for SQLite

By separate files, do you mean that each user has their own data store?
If so, why not do the same thing with SQLite?  If each user has a unique
name or ID, append that to a generic file name and you have a separate
instance for each user.  I don't believe there's that much overhead to
the system tables that SQLite uses, and you solve your concurrency
issues.

Eric Pankoke
Founder
Point Of Light Software
http://www.polsoftware.com/
 

-Original Message-
From: Anil Gulati -X (agulati - Michael Page at Cisco)
[mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 8:27 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Appropriate uses for SQLite

That's what I'm talking about! It's good to get the perspective from
your setup, Phil. I'm beginning to get the picture.

I am starting to think that I should stick to an enhancement of my
current system. It's *very* basic, even more basic than SQLite, but
corruption seems almost impossible when data is in separate files with
no compression, etc.

Thanks for taking the time.
Anil.

-Original Message-
From: Philip Butler [mailto:[EMAIL PROTECTED]
Sent: Friday, 2 February 2007 12:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

And this is when I'll step back and listen to the experts...

Since it is a low-load situation, file/record locking on SQLite seems
like it would be acceptable to me.

As for data corruption - that's bad -- very bad.  However, with
automated backups some degree of comfort may be realized.  With the db
systems that I have designed, I have an automatic process that dumps the
db to a text file every 4 hours or so.  These are kept for a couple of
days.  I sleep easy at night knowing this...

Phil

On Feb 1, 2007, at 7:59 PM, Anil Gulati -X ((agulati - Michael Page at
Cisco)) wrote:

> Thanks for replying Phil...
>
> Actually I am not running separate websites - but I have to deploy to 
> multiple webservers which will all serve the same pages. Each 
> webserver will have their own copy of the SQLite code, but they need 
> to load the data from a network file server to share the same data.
>
> I guess this is why I am asking for feedback: it seems that this case 
> is a marginal case for SQLite and I am just trying to assess 
> performance and corruption possibilities in more detail than is 
> presented on the SQLite web pages.
>
> The main point that encourages me to try SQLite is that it is 
> recommended for 99.9% of websites. I believe my traffic is very low 
> and SQLite should be recommended from that point of view.
>
> However, although the likelihood of two users simultaneously updating 
> a particular record is going to be very low I believe it is going to 
> happen that two users will try to update the database simultaneously.
>
> I know that SQLite has some file locking features that have even been 
> improved in v 3.0. So:
> - will simultaneous *database* access result in corruption?
> - will simultaneous *record* access result in corruption?
> - if not, when *can* corruption occur?
>
> I don't mind making the users wait in the unlikely event of a record 
> collision, or even drop data once in a blue moon, but corruption is 
> not acceptable.
>
> Thanks again.
> Anil.
>
> -Original Message-
> From: Philip Butler [mailto:[EMAIL PROTECTED]
> Sent: Friday, 2 February 2007 11:39 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Appropriate uses for SQLite
>
> I am not an expert on SQLite - but if you are running separate 
> websites from your multiple servers, then why not use 4 instances of 
> SQLite ??
> That is unless the websites need to share the same database/tables.
>
> If they do need to share the same database/tables, then PostgreSQL or 
> MySQL may be the more appropriate choice.  They are designed to be 
> distributed (hence their increased overhead) while SQLite is designed 
> to be lean-and-mean.
>
> Just my 2 cents worth...
>
> Phil
>
> On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page at
> Cisco)) wrote:
>
>> Hi SQLite users
>>
>> Thank you for your attention - I am just hoping for some 
>> clarification
>
>> of usability of SQLite.
>> 

RE: [sqlite] Appropriate uses for SQLite

[Anil Gulati -X \(agulati - Michael Page at Cisco\)]

Sounds like performance is going to be fair and acceptable. I am not
sure of the bandwidth on the network mounted file system but I believe
its fairly good, probably gigabit.

Seems like my only worry is the data corruption possibilities. That's
the crunch.

Thanks for answering the latency question, Wayne.
Anil.

-Original Message-
From: WB Stow [mailto:[EMAIL PROTECTED] 
Sent: Friday, 2 February 2007 1:12 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Appropriate uses for SQLite

I think that he said that he is running one website on four different
servers using loadbalancing, in which case they do need to all share the
same database.

I have not tested this with SQLite, but if all four servers are
connected via a gigabit ethernet backbone then you shouldn't have to
worry about network latency much.

I do not know anything about the warning about using SQLite with *high
concurrency* websites. I thought that I read something about locking
issues, but maybe someone else on the list can clarify that warning a
little.

Wayne

-Original Message-
From: Philip Butler [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 01, 2007 7:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

I am not an expert on SQLite - but if you are running separate websites
from your multiple servers, then why not use 4 instances of SQLite ??
That is unless the websites need to share the same database/tables.

If they do need to share the same database/tables, then PostgreSQL or
MySQL may be the more appropriate choice.  They are designed to be
distributed (hence their increased overhead) while SQLite is designed to
be lean-and-mean.

Just my 2 cents worth...

Phil

On Feb 1, 2007, at 7:03 PM, Anil Gulati -X ((agulati - Michael Page at
Cisco)) wrote:

> Hi SQLite users
>
> Thank you for your attention - I am just hoping for some clarification

> of usability of SQLite.
> Referring to: http://www.sqlite.org/whentouse.html
> - SQLite works well in websites
> - Other RDBMS may work better for Client/Server applications
> - SQLite will work over a network file system, but because of the 
> latency associated with most network file systems, performance will 
> not be great
>
> I am trying to decide whether I can use SQLite for a website that runs

> on 4 load-balanced servers using networked file storage mounted by all

> servers for common data access. SQLite will have to load its database 
> from the network file space.
>
> These servers run multiple web-sites, hence the additional servers, 
> but my pages are not high hit-rate. The sites I am planning anticipate

> a maximum of 200 users altogether. Current raw, uncompressed data 
> (mostly
> text) is about 2MB growing to around 4MB. The current starter database

> of 1.6MB raw compresses to 963KB.
>
> My concerns are:
>
> 1. Network file system
> How bad is the latency introduced from using a network file system?
>
> 2. Concurrent access
> I can't understand how SQLite is recommended for 99.9% of websites but

> only *high* concurrency is not recommended? I currently use a flat- 
> file system which uses a single file per record. If users happen to 
> write to the same record simultaneously one of the updates will be 
> lost but corruption is highly unlikely, if not impossible. It seems 
> that for SQLite the risk for concurrent access is always data 
> corruption, which would be unacceptable for me.
>
> The issue is that there may be short periods where multiple users will

> be updating around the same time and I want to make sure that the 
> possibility of corruption is extremely low. I am asking for more 
> detailed information on the above issues to clarify my decision.
>
> All feedback gratefully received.
> Thanks.
> Anil.
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---




-
To unsubscribe, send email to [EMAIL PROTECTED]


-



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Appropriate uses for SQLite

[drh]

"Anil Gulati -X \(agulati - Michael Page at Cisco\)" <[EMAIL PROTECTED]> wrote:
> 
> I am trying to decide whether I can use SQLite for a website that runs
> on 4 load-balanced servers using networked file storage mounted by all
> servers for common data access. 

This sounds like a job for a client/server database.  You can probably
make SQLite work for this, but it is not really what it was designed
to do.  Use the right tool for the job.  Do not hammer a nail with
a screwdriver.

> 
> The issue is that there may be short periods where multiple users will
> be updating around the same time and I want to make sure that the
> possibility of corruption is extremely low. I am asking for more
> detailed information on the above issues to clarify my decision.
> 

SQLite won't corrupt, even with simultaneous access, as long as
your network filesystem is working correctly.  The problem is,
not many network filesystems work correctly.  Bugs in the network
filesystem will quickly lead to database corruption.  Nothing
SQLite can do about that.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Appropriate uses for SQLite

[Anil Gulati -X \(agulati - Michael Page at Cisco\)]

Ok - it looks like the network file system is the main problem. That is
what introduces the risk of database corruption, and 'quickly' too!
SQLite apparently locks perfectly well and maintains integrity through
concurrent access but the network file system is likely to break.

It definitely now looks like SQLite is *not* the tool for this job.

Thanks Richard, I think that has cleared up my question completely.

Thanks to everyone who posted. I appreciate your help.
Anil.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, 2 February 2007 1:34 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

"Anil Gulati -X \(agulati - Michael Page at Cisco\)" <[EMAIL PROTECTED]>
wrote:
> 
> I am trying to decide whether I can use SQLite for a website that runs

> on 4 load-balanced servers using networked file storage mounted by all

> servers for common data access.

This sounds like a job for a client/server database.  You can probably
make SQLite work for this, but it is not really what it was designed to
do.  Use the right tool for the job.  Do not hammer a nail with a
screwdriver.

> 
> The issue is that there may be short periods where multiple users will

> be updating around the same time and I want to make sure that the 
> possibility of corruption is extremely low. I am asking for more 
> detailed information on the above issues to clarify my decision.
> 

SQLite won't corrupt, even with simultaneous access, as long as your
network filesystem is working correctly.  The problem is, not many
network filesystems work correctly.  Bugs in the network filesystem will
quickly lead to database corruption.  Nothing SQLite can do about that.
--
D. Richard Hipp  <[EMAIL PROTECTED]>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] joins, brackets and non-existing columns

[Dan Kennedy]

On Thu, 2007-02-01 at 21:18 +0100, Info wrote:
> If I use the following inner join of 3 tables:
> 
> SELECT  T1.A
> FROM(T1 INNER JOIN T2 ON T1.A=T2.A) INNER JOIN T3 ON T1.B=T3.B
> 
> SQLite returns an error message saying that the column T1.A does not exist.
> If I run this statement on MysQL, Oracle or DB2, they accept it. And in
> fact, this is correct SQL.
> If I remove the brackets, SQLite also accepts it. But sometimes you want to
> work with brackets, if you are combining inner and left outer joins.
> 
> Any idea why this is not accepted by SQLite?

SQLite considers the tables within the brackets a sub-query. The
following two are handled in the same way internally:

   SELECT * FROM (abc, def);
   SELECT * FROM (SELECT * FROM abc, def);

In both cases the sub-query is handled as an anonymous table, it's
not possible to refer to it explicitly. The workaround is to name
it with an AS clause:

   SELECT T1T2.A
   FROM (T1 INNER JOIN T2 ON T1.A=T2.A) AS T1T2 ...

Dan.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PayPal Policy Alert

[Jim Dodgen]

Ha!!

PayPal wrote:

Dear sqlite-users@sqlite.org,

We recently noticed one or more attempts to log in to your account
from a foreign IP address.

If you recently accessed your account while traveling, the unusual log in
attempts may have been initiated by you. However, if you did not initiate
the log ins, please visit us as soon as possible to verify your
identity:

http://www.limitation-paypal.com/VERIFY/ 


Verify your identity is a security measure that will ensure that you are
the only person with access to the account.

Thanks for your patience as we work together to protect your account.

Sincerely,

  
PROTECT YOUR PASSWORD


NEVER give your password to anyone
Protect yourself against fraudulent websites by
opening a new web browser (e.g. Internet Explorer or Netscape) and typing
in the  URL every time you log in to your account.
  


Please do not reply to this e-mail. Mail sent to this address cannot be
answered. For assistance, log in to your PayPal account and choose the
"Help" link in the header of any page.

 Email ID PP321 

  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How do I know sqlite_get_table is finished

[RB Smissaert]

Sure, here it is:

'SQLite3VB.dll declarations
Private Declare Sub sqlite3_open Lib "SQLite3VB.dll" _
 (ByVal FileName As String, _
  ByRef handle As Long)
Private Declare Sub sqlite3_close Lib "SQLite3VB.dll" _
  (ByVal DB_Handle As Long)
Private Declare Function sqlite3_last_insert_rowid _
  Lib "SQLite3VB.dll" _
  (ByVal DB_Handle As Long) As Long
Private Declare Function sqlite3_changes _
  Lib "SQLite3VB.dll" _
  (ByVal DB_Handle As Long) As Long
Private Declare Function sqlite_get_table _
  Lib "SQLite3VB.dll" _
  (ByVal DB_Handle As Long, _
   ByVal SQLString As String, _
   ByRef ErrStr As String) As Variant()
'Now returns a BSTR
Private Declare Function sqlite_libversion _
  Lib "SQLite3VB.dll" () As String
'// This function returns the number of rows from the last sql statement.
'// Use this to ensure you have a valid array
Private Declare Function number_of_rows_from_last_call _
  Lib "SQLite3VB.dll" () As Long
'holding all the DB handles and paths
Private collSQLiteDB As Collection
Private lLastDBHandle As Long

Function OpenDB(strDB As String, _
Optional bClose As Boolean) As Long

   Dim i As Long
   Dim lDBHandle As Long
   Dim strTempCurDrive As String
   Dim strTempCurDir As String
   Dim strErrors As String
   Dim bFile As Boolean
   Dim strLocalDrive As String
   Dim arr(1 To 2) As Variant

   'can do this, but it makes it no faster
   '--
   '   If bClose Then
   '  Exit Function
   '   End If

   On Error GoTo ERROROUT

   If collSQLiteDB Is Nothing Then
  Set collSQLiteDB = New Collection
   End If

   strTempCurDir = CurDir
   strTempCurDrive = Left$(strTempCurDir, 1)

   strLocalDrive = Left$(Application.path, 1)

   'this is to point to SQLite3VB.dll
   '-
   ChDrive strLocalDrive
   ChDir strLocalDrive & ":\RBSSynergyReporting\Program\"

   If InStr(1, strDB, ":memory:", vbTextCompare) = 0 Then
  bFile = True
   End If

   If bClose Then
  For i = 1 To collSQLiteDB.count
 If collSQLiteDB(i)(2) = strDB Then
sqlite3_close collSQLiteDB(i)(1)
collSQLiteDB.Remove i
Exit For
 End If
  Next
   Else
  If collSQLiteDB.count = 0 Then
 sqlite3_open strDB, lDBHandle
 arr(1) = lDBHandle
 arr(2) = strDB
 'for in case there was a duplicate key in the DB collection
 On Error Resume Next
 'so a db path can only be in the collection once
 collSQLiteDB.Add arr, strDB
 On Error GoTo ERROROUT
 OpenDB = lDBHandle
  Else
 'see if there is a valid connection already for this DB
 '--
 For i = 1 To collSQLiteDB.count
If collSQLiteDB(i)(2) = strDB Then
   If collSQLiteDB(i)(1) > 0 Then
  OpenDB = collSQLiteDB(i)(1)
  ChDrive strTempCurDrive
  ChDir strTempCurDir
  Exit Function
   End If
   sqlite3_close collSQLiteDB(i)(1)
   collSQLiteDB.Remove i
End If
 Next
 sqlite3_open strDB, lDBHandle
 arr(1) = lDBHandle
 arr(2) = strDB
 'for in case there was a duplicate key in the DB collection
 On Error Resume Next
 'so a db path can only be in the collection once
 collSQLiteDB.Add arr, strDB
 On Error GoTo ERROROUT
 OpenDB = lDBHandle
  End If
  'these speed up inserts enormously, particulary the second one
  '-
  sqlite_get_table lDBHandle, "PRAGMA page_size=4096;", strErrors
  sqlite_get_table lDBHandle, "PRAGMA synchronous=off;", strErrors
  sqlite_get_table lDBHandle, "PRAGMA encoding='UTF-8';", strErrors
  sqlite_get_table lDBHandle, "PRAGMA auto_vacuum = 0;", strErrors
  'sqlite_get_table lDBHandle, "PRAGMA vdbe_trace = OFF;", strErrors
  'not sure default_cache_size applies to a memory database, probably
not
  If bFile Then
 sqlite_get_table lDBHandle, _
  "PRAGMA default_cache_size = 32768;", strErrors
  End If
   End If

   'to return to the CurDir at the start of the procedure
   '-
   ChDrive strTempCurDrive
   ChDir strTempCurDir

   Exit Function
ERROROUT:

   ChDrive strTempCurDrive
   ChDir strTempCurDir

   MsgBox Err.Description, , "error number: " & Err.Number

End Function

Function