Re: [sqlite] SQLite Training

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Rich Shepard wrote:
>That's very true, but it's still the best overall introduction to SQLite.

The book claims to be a definitive guide, not an introduction :-)

>Almost no book on a particular software application remains current. 

This is usually addressed by coming out with new editions, pointing to the
author website for updates or at least some sort of "teach a man to fish"
material such as how to use the website and mailing lists (eg SQLite
developer vs user activities, bug tracker, utility of release notes, what
being open source means and how progress is made, wikis, FAQs etc).

>I actually bought my copy in 2006 and found it answered many questions for
> me. Still does.

Indeed.  I have it open on my desk in front of me :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkthPAoACgkQmOOfHg372QTquQCfTZEQx4TJxHVGDw/EzzQuJusk
YWYAnjKxAspn/LvMZfpCyqRGxtiKEQFo
=2l/a
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency support for multiple process

[nyetngoh wong]

> What made you expect that?
Process A has not entered the exclusive lock and so process B can obtain
shared lock to read.

> SQLite allows multiple readers OR a single writer to access the database
simultaneously.
>From the SQLite doc, as long as no transaction is pending, other process can
read or obtain reserved lock for write.

On Thu, Jan 28, 2010 at 12:51 PM, Igor Tandetnik wrote:

> nyetngoh wong wrote:
> > First, I've a process A that do many inserts to the database and reads
> back
> > from the database to verify. The writes are done in one DEFERRED
> transaction
> > as data are not committed yet. While the first process running, another
> > process B is launched to read from the database for display purposes.
> > Process B will halts until the transaction ends. I expect process B able
> to
> > read the old data and displays appropriately.
>
> What made you expect that?
>
> > But, process B seems to be
> > blocked. Why is this so as I thought SQLite handle concurrency as in this
> > case where there are multiple reads and one single write ?
>
> SQLite allows multiple readers OR a single writer to access the database
> simultaneously.
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] julian qian wants to stay in touch on LinkedIn

[julian qian]

LinkedIn



   
General,

I'd like to add you to my professional network on LinkedIn.

- julian qian

Confirm that you know julian qian
https://www.linkedin.com/e/isd/1028285185/9hLDvOAo/






 
--
(c) 2010, LinkedIn Corporation
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency support for multiple process

[Igor Tandetnik]

nyetngoh wong wrote:
> First, I've a process A that do many inserts to the database and reads back
> from the database to verify. The writes are done in one DEFERRED transaction
> as data are not committed yet. While the first process running, another
> process B is launched to read from the database for display purposes.
> Process B will halts until the transaction ends. I expect process B able to
> read the old data and displays appropriately.

What made you expect that?

> But, process B seems to be
> blocked. Why is this so as I thought SQLite handle concurrency as in this
> case where there are multiple reads and one single write ?

SQLite allows multiple readers OR a single writer to access the database 
simultaneously.

Igor Tandetnik

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

Re: [sqlite] contribution: fts3 porter stemmer enhancements to handle common european accents

[Jean-Christophe Deschamps]

>The porter stemmer, by its very nature, is not intended to work for 
>non-english text

You should read the Snowball site about this: http://snowball.tartarus.org/


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

Re: [sqlite] SQLite Training

[Rich Shepard]

On Wed, 27 Jan 2010, Roger Binns wrote:

> The main drawback is that the book hasn't been revised since 2006 since
> when the virtual machine has changed, foreign key support is present, you
> can use threading freely, there is incremental Blob I/O, virtual tables
> are available, VFS is available, backup API is available etc so the book
> is no longer definitive!

Roger,

   That's very true, but it's still the best overall introduction to SQLite.

   Almost no book on a particular software application remains current. Too
often the book is based on a software version that has been replaced by the
time the book makes it out of the manufacturing process. The fundamentals
are usually still valid, however.

   I actually bought my copy in 2006 and found it answered many questions for
me. Still does.

Rich

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

[sqlite] contribution: fts3 porter stemmer enhancements to handle common european accents

[James Berry]

I'd like to contribute for potential inclusion, or to help out others in the 
community, a small set of enhancements I've made to the porter tokenizer. This 
implementation shares most of its code with the current porter tokenizer, as 
the changes are really just in the tokenizer prior to the stemming operation. 
This small patch implements an additional tokenizer, which I am calling 
"porterPlus", for lack of further inspiration.

The code is based on several observations made while attempting to use the 
current porter tokenizer on a common english/utf-8 dataset:

 - There are a limited number of accented characters common in english text.

 - If the accents simply weren't there, the words would be stemmed 
appropriately, but the porter stemmer gives up on a word when it sees any utf-8 
characters, leading to perceived failures in the search queries.

 - The porter stemmer, by its very nature, is not intended to work for 
non-english text, so we can write off the major part of the the utf-8 character 
set, while concentrating on major improvements to those characters involved in 
common european languages, particularly those that have been adopted into 
english usage.

 - Additionally, there are a number of punctuation characters commonly rendered 
in utf-8 that are missed by the regular porter tokenizer  (hyphen and 
typographic quotes are good examples).

This small patch does the following:

- Defines a new tokenizer "porterPlus" which shares most of its code 
with the regular porter tokenizer

- Identifies a small subset of utf-8 characters for special handling. 
In the case of common accented varieties of regular ascii characters, the 
accents are dropped, leaving the unaccented character only. For instance, sauté 
is converted to saute. The resultant word is passed as usual into the porter 
stemmer.

- Also identifies a small subset of utf-8 characters to treat as 
delimiters, as they would otherwise be treated as part of another token, 
leading to search failures. (hyphen, typographic quotes, etc).

In our use so far, these small changes have meant that we now normalize away 
all of the important utf-8 characters in our input text, which gives us 100% 
searchability of significant input tokens.

The patch (to the 3.6.22 amalgamation) is attached.

James

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

[sqlite] Documentation errors

[Colin Shaw]

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

[sqlite] Question about locking

[yava]

Sqlite uses file locking for concurent access to databases. Is it possible
to implement locking using data synchronization library liburcu
(http://lttng.org/?q=node/18) on top of mmaped file? I'm sure this would
be not sqlite but some other light database.

RCU achieves scalability improvements by allowing reads to occur
concurrently with updates (http://lwn.net/Articles/262464/)



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

Re: [sqlite] SQLite Training

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> Excellent.  I must check out a copy.

You may find my review useful:

  http://www.rogerbinns.com/sqlitereview.html

The main drawback is that the book hasn't been revised since 2006 since when
the virtual machine has changed, foreign key support is present, you can use
threading freely, there is incremental Blob I/O, virtual tables are
available, VFS is available, backup API is available etc so the book is no
longer definitive!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAktg88cACgkQmOOfHg372QROHQCdGF8b0gFPyr3IqLr5DZE7ZW+q
ZiIAoMYRqDaOfDrw0WHOSbR1s/cwAbqa
=P77k
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Concurrency support for multiple process

[nyetngoh wong]

Hi,

First, I've a process A that do many inserts to the database and reads back
from the database to verify. The writes are done in one DEFERRED transaction
as data are not committed yet. While the first process running, another
process B is launched to read from the database for display purposes.
Process B will halts until the transaction ends. I expect process B able to
read the old data and displays appropriately. But, process B seems to be
blocked. Why is this so as I thought SQLite handle concurrency as in this
case where there are multiple reads and one single write ? Please help.
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Training

[Simon Slavin]

On 28 Jan 2010, at 2:04am, Roger Binns wrote:

> Simon Slavin wrote:
>> ... but that requires that you understand at least a bit of relational 
>> databases theory.  
> 
> Mike Owen's book does that well.  There are 26 pages covering the relational
> model before SQL is introduced.

Excellent.  I must check out a copy.

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

Re: [sqlite] SQLite Training

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> ... but that requires that you understand at least a bit of relational 
> databases theory.  

Mike Owen's book does that well.  There are 26 pages covering the relational
model before SQL is introduced.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAktg8LEACgkQmOOfHg372QQKWgCfYBJYhuncut+UWTxscFIcj2dT
+D8Ani6IiBy576jqEKtU8yD/QuRP3nLB
=/2Hd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Training

[Rich Shepard]

On Thu, 28 Jan 2010, Simon Slavin wrote:

> ... but that requires that you understand at least a bit of relational
> databases theory.  We've recently had questions from people who don't
> understand an INDEX, or what you would want one, or how to make one which
> is useful for a particular query.  So I guess the lowest rung on learning
> SQLite is something like "What is a relational database ?".

> Sorry, a little hobby-horse of mine.

Simon,

   You'll find no disagreement here. I've seen too many "databases" that are
flat-field because the creators have no idea on how to build a relational 
database
by normalizing data. However, that's not specific to SQLite.

   There are many Web sites that can provide a good introduction. Google will
turn them up.

   I will make the same argument about statistical software, particularly GIS
for spatial analyses. Just because you teach someone how to use a word
processor does not make her a writer. Holds true for any technical tool: if
you don't know how to properly use the tool you can hurt yourself.

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

Re: [sqlite] SQLite Training

[Simon Slavin]

On 28 Jan 2010, at 1:03am, Rich Shepard wrote:

> All you need is to know SQL well

... but that requires that you understand at least a bit of relational 
databases theory.  We've recently had questions from people who don't 
understand an INDEX, or what you would want one, or how to make one which is 
useful for a particular query.  So I guess the lowest rung on learning SQLite 
is something like "What is a relational database ?".

Sorry, a little hobby-horse of mine.

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

Re: [sqlite] SQLite Training

[Rich Shepard]

On Thu, 28 Jan 2010, nyetngoh wong wrote:

> I'm currently working on a project that uses SQLite and would like to know
> if there is any SQLite certification available in Singapore. Do you
> provide any forms of technical training or courses on using SQLite
> efficiently ?

   All you need is to know SQL well. I'll recommend two books.

   1) Michael Owens. 2006. "The Definitive Guide to SQLite." Apress.
An excellent reference to the specifics of SQLite.

   2) Rick F. van der Lans. 2009. "The SQL Guide to SQLite." Lulu.
An excellent tutorial and reference on SQL as implemented in SQLite.

   In addition, feel free to post specific questions here.

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

[sqlite] SQLite Training

[nyetngoh wong]

Hi,

I'm currently working on a project that uses SQLite and would like to know
if there is any SQLite certification available in Singapore. Do you provide
any forms of technical training or courses on using  SQLite efficiently ?

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

[sqlite] Problem using sqlite in a C++/CLI .Net application

[Edward Diener]

I am able to compile a C++/CLI .Net application using sqlite. But when
it links it complains with some linker warnings:

xxx.obj : warning LNK4248: unresolved typeref token (011D) for
'sqlite3'; image may not run
xxx.obj : warning LNK4248: unresolved typeref token (011F) for
'sqlite3_stmt'; image may not run

This is because the 'sqlite3' and 'sqlite3_stmt' structures are
completely opaque in the sqlite3.h header files, and the MSIL is 
evidently trying to create code for it.

Is there a workaround for this possible problem ?

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

Re: [sqlite] Conditional insertion syntaxe in SQLite

[Tiberio, Sylvain]

Thanks for the suggestion of a temp table for storing intermediaire data,
Igor.

Now DELETE TRANSACTION works fine with only SQL code!

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Tuesday, January 26, 2010 6:23 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Conditional insertion syntaxe in SQLite

Tiberio, Sylvain  wrote:
> Is it possible to write something like that in SQL (for SQLite of
> course!): 
> 
> If [toDeleteMin,toDeleteMax] is include in an existing range Store the 
> existing range value into min and max variables Delete [Min,Max] 
> Create [Min,toDeleteMin-1] Create [toDeleteMax+1,max] endif

There are no variables, but you can use a temp table. Something like this:

create temp table Vars(rangeid, existingMin, existingMax);

insert into Vars
select rowid, roomIdMin, roomIdMax from range where roomIdMin < toDeleteMin
and roomIdMax > toDeleteMax;

delete from range where rowid = (select rangeid from Vars); insert into
range select 1, existingMin, toDeleteMin from Vars; insert into range select
1, toDeleteMax, existingMax from Vars;

drop table Vars;

(Edge conditions are left as an exercise for the reader, I'm pretty sure I
got them wrong).
--
Igor Tandetnik

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

Re: [sqlite] what are the limitations for IN() lists?

[Simon Slavin]

On 27 Jan 2010, at 1:28pm, Tim Romano wrote:

> The question in my mind is whether the following is any more 
> performance-efficient than the approach above (note 2a-2e and 5a-5b):
> 2. Webservice:
> 1) receives the request
> 2) instantiates a database connection
> 2a) creates an in-memory database
> 2b) attaches in-memory database

Hmm.  A couple of things worth considering: first that JavaScript under HTML5 
has its own access to SQL commands.  If this system is for use only inside an 
organisation, and you can say everyone must use a modern browser, then you can 
use the HTML5 tools which automatically ensure keep the databases local (in 
fact, on the client's hard disk, not the server !).  By the way, all the 
browsers I've seen that support this actually use sqlite3 internally.

However, you might be planning to do this on the server using PHP.  And PHP has 
more than one SQLite library and you should be sure you're using one that uses 
sqlite3, not the original sqlite library.

So part of your design decision is whether some of the presentation work can be 
done in JavaScript on the client.

> 2c) issues query to disk-database to fetch random hex value to 
> ensure temp table is named uniquely

You don't need this.  If you're using a TEMP table, or keeping the table in 
:memory:, then you can call it whatever you want: only the single connection 
you're using right now can see it, and it will vanish as soon as Apache (or 
whatever) has finished serving that particular web page.

[later: I see Jay explained this bit better, so read his response.]

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

Re: [sqlite] what are the limitations for IN() lists?

[Jay A. Kreibich]

On Wed, Jan 27, 2010 at 08:28:15AM -0500, Tim Romano scratched on the wall:

> The question in my mind is whether the following is any more 
> performance-efficient than the approach above (note 2a-2e and 5a-5b):
> 2. Webservice:
>  1) receives the request
>  2) instantiates a database connection
>  2a) creates an in-memory database
>  2b) attaches in-memory database

  You create the database by attaching it, so these are one step.

>  2c) issues query to disk-database to fetch random hex value to 
> ensure temp table is named uniquely
>  2d) creates temporary table in the in-memory database
>  2e) populates temporary table with values that would otherwise be 
> placed in the IN-list

  You're making this much too complex.

  Without attaching a new ":memory:" or "" database or doing anything
  else different from what you're already doing, you can simply give
  the command:

CREATE TEMP TABLE in_args (val);

  And then insert your values into it.  The "TEMP" will make SQLite
  automatically create a temp database (that is, an internal equivalent
  to "ATTACH  AS 'temp'"), but because it is known to be a temp
  database, several performance-related configurations are made,
  such as setting the database to exclusive locking mode.  Temp
  databases are also exclusive to the database connection, so there is
  no need for unique names or nonsense like that.  You couldn't share a
  temp database if you wanted to.  This kind of thing is an OLD problem
  in databases, and it was solved a long time ago.

  The temp database will either be an in-memory database, or it can be
  a "file-backed" database.  Which depends on the value of PRAGMA
  temp_store and some compile-time settings.  "File-backed" is not
  exactly the same as "file-based."  IIRC, a file-backed database will
  only actually hit disk if it over-flows the cache (which defaults to
  500 pages); most of the time even a "file" temp database won't
  actually hit disk, so the performance is very good.

>  3) creates a command with SQL statement (now joining disk-tables to 
> in-memory table)

  No need to re-write the query with a JOIN.  The IN expression supports
  table names.  You can simply say "...IN temp.in_args" (note: no
  column name; it must be a one-column table).

>  4) executes the command
>  5) grabs the results
>  5a) drops the temporary table in the IN-memory database
>  5b) detaches the memory-database

  If you close the database connection all TEMP stuff is automatically
  cleaned up.  Again, this is an old problem built very deeply into the
  core of any modern RDBMS.  They're designed to be used this way.

>  6) closes the database connection
>  7) sends the results to the browser-agent
> 
> At what point does step #3) in the top IN-list approach become more 
> expensive than steps 2a-2e and 5a-5b in the bottom in-memory approach?

  Using an actual temp table, I wouldn't be too concerned about this.

  Further, doing it this way avoids the need to build any SQL
  statements with string manipulations-- always a very dangerous thing
  that can lead to problems.  Using a temp table, you should be able to
  do everything with static SQL statements and binds.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the type of a parameter

[Jay A. Kreibich]

On Wed, Jan 27, 2010 at 01:43:36PM +, Simon Davies scratched on the wall:
> 2010/1/27 gujx :
> > Hi, I had a question puzzled me. How can I get the type of a parameter in a
> > prepared SQL.
> >
> > For example, the code is:
> .
> .
> .
> >
> > ? ? ? "INSERT INTO [test_for_cpp] ([id], [age], [name]) VALUES (?, ?,
> > 'xiaowang') ; ",
> > ? ? ? -1,
> > ? ? ? ,
> > ? ? ? 
> > ? ?);
> >
> > How can I get the type of the specific column that a ??? referenced to?
> > For example, I want to get the type (here is integer) of the first column
> > that the first ??? referenced to.

  Columns don't have types in SQLite, they have affinities.  They're
  not the same thing: http://www.sqlite.org/datatype3.html

> pragma table_info, see http://www.sqlite.org/pragma.html#pragma_table_info

  Yes, but that will return the actual defined type, not the column
  affinity. For example, if a column is defined "name varchar", then
  table_info will return "varchar", not "text."

  As far as I know, there is no way to extract the affinity of a
  column, other than trying to use the same algorithm SQLite uses.

  In short, the person that writes the query needs to know the expected
  type, just like every other database engine.  The main difference is
  that SQLite will happily accept any type you want, which can be good
  or bad, depending on what you're trying to do.  Adding a CHECK typeof()
  constraint is easy enough for those situations where you need a
  specific type.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get the type of a parameter

[Simon Davies]

2010/1/27 gujx :
> Hi, I had a question puzzled me. How can I get the type of a parameter in a
> prepared SQL.
>
> For example, the code is:
.
.
.
>
>       "INSERT INTO [test_for_cpp] ([id], [age], [name]) VALUES (?, ?,
> 'xiaowang') ; ",
>       -1,
>       ,
>       
>    );
>
> How can I get the type of the specific column that a “?” referenced to?
> For example, I want to get the type (here is integer) of the first column
> that the first “?” referenced to.

pragma table_info, see http://www.sqlite.org/pragma.html#pragma_table_info

>
> Gu Jinxiang
>

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

Re: [sqlite] what are the limitations for IN() lists?

[Tim Romano]

Thanks for the suggestion of a memory-database, Jean-Christophe. It is 
not something I've used so far with SQLite but I have some preliminary 
questions in the abstract.

The typical scenario with a webservice goes like this (database 
connections are ephemeral, not persistent):

1. User visits URL, passing parameters to the webservice in query-string 
and/or in the form-fields.
2. Webservice:
 1) receives the request
 2) instantiates a database connection
 3) creates a command with SQL statement (in my case, using IN-list)
 4) executes the command
 5) grabs the results
 6) closes the database connection
 7) sends the results to the browser-agent

The question in my mind is whether the following is any more 
performance-efficient than the approach above (note 2a-2e and 5a-5b):
2. Webservice:
 1) receives the request
 2) instantiates a database connection
 2a) creates an in-memory database
 2b) attaches in-memory database
 2c) issues query to disk-database to fetch random hex value to 
ensure temp table is named uniquely
 2d) creates temporary table in the in-memory database
 2e) populates temporary table with values that would otherwise be 
placed in the IN-list
 3) creates a command with SQL statement (now joining disk-tables to 
in-memory table)
 4) executes the command
 5) grabs the results
 5a) drops the temporary table in the IN-memory database
 5b) detaches the memory-database
 6) closes the database connection
 7) sends the results to the browser-agent

At what point does step #3) in the top IN-list approach become more 
expensive than steps 2a-2e and 5a-5b in the bottom in-memory approach?

Regards
Tim Romano

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

[sqlite] how to get the type of a parameter

[gujx]

Hi, I had a question puzzled me. How can I get the type of a parameter in a
prepared SQL.

For example, the code is:

 

sqlite3 *conn;

if (SQLITE_OK != sqlite3_open("testBind.db", ))

{

   printf("can't open the database.");

   return ;

}

 

if(SQLITE_OK != sqlite3_exec(conn, "create table  test_for_cpp(id
integer,name text,age integer)", 0, 0, 0))

{

   return ;

}

 

   sqlite3_stmt *ppStmt;

const char *pzTail; 

int nVal =sqlite3_prepare_v2(

   conn,   

   "INSERT INTO [test_for_cpp] ([id], [age], [name]) VALUES (?, ?,
'xiaowang') ; ",

   -1,

   ,  

   

);

 

 

How can I get the type of the specific column that a “?” referenced to?
For example, I want to get the type (here is integer) of the first column
that the first “?” referenced to.

 

 

Gu Jinxiang

 

以上、よろしくお��いします。

 

 

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

[sqlite] how to get the type of a parameter

[gujx]

Hi, I had a question puzzled me. How can I get the type of a parameter in a
prepared SQL.

For example, the code is:

 

sqlite3 *conn;

if (SQLITE_OK != sqlite3_open("testBind.db", ))

{

   printf("can't open the database.");

   return ;

}

 

if(SQLITE_OK != sqlite3_exec(conn, "create table  test_for_cpp(id
integer,name text,age integer)", 0, 0, 0))

{

   return ;

}

 

   sqlite3_stmt *ppStmt;

const char *pzTail; 

int nVal =sqlite3_prepare_v2(

   conn,   

   "INSERT INTO [test_for_cpp] ([id], [age], [name]) VALUES (?, ?,
'xiaowang') ; ",

   -1,

   ,  

   

);

 

 

How can I get the type of the specific column that a “?” referenced to?
For example, I want to get the type (here is integer) of the first column
that the first “?” referenced to.

 

Gu Jinxiang

 

以上、よろしくお��いします。

 

 

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

Re: [sqlite] Sqlite3 Optimization Question

[Jens Miltner]

Am 22.01.2010 um 15:01 schrieb Michael Thomason:

> The only problem is that the table is very large.  If I list out the
> contents of the table in a text file, it is one-third the size of the
> database.  So, if the text file is 12 MB, the database is 32 MB.

You may also want to check the encoding of your database: UTF-8  
storage may be more efficient, especially when you're expecting to  
have mostly English words (or words in a language using roman  
characters).
If your database uses UTF-16 encoding, this essentially doubles the  
storage space needed for those languages.

You can use "pragma encoding" to query and set the string encoding in  
your database (see )



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

Re: [sqlite] Java and sqlite

[Chris Herssens]

See http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC for een Java interface
of SQLite

Regards

On Tue, Jan 26, 2010 at 8:51 PM, Miloud B.  wrote:

> Hi guys,
>
> I want to use SQLite in Java applications, what do you advice ? Which
> interface do you use ?
> I found two:
> http://www.ch-werner.de/javasqlite/ : Which I like a lot but seems no
> longer maintained
> and
> http://www.zentus.com/sqlitejdbc/
>
> Which on do you think is  better to use ? Have you heard of any other
> interface ?
>
> Thanks a lot
>
> Miloud B.
> Cheers
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users