[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

SQLite in GIS
I had known about Spatialite, but I hadn't realized that QGIS was using
SQLite (both indpendently and through Spatialite).

"While PostGIS is generally used on a server to provide spatial database
capabilities to multiple users at the same time, QGIS also supports the use
of a file format called *spatialite* that is a lightweight, portable way to
store an entire spatial database in a single file. ...Using the [Database]
Browser panel, we can create a new *spatialite database* and get it setup
for use in QGIS"
http://docs.qgis.org/2.0/en/docs/training_manual/databases/spatialite.html

A YouTube video
https://www.youtube.com/watch?v=3nG-oY5p9O0

"QGIS has support for approximately 2,700 known CRSs [Coordinate Reference
Systems]. Definitions for each CRS are stored in a *SQLite database* that
is installed with QGIS."
https://docs.qgis.org/2.2/en/docs/user_manual/working_with_projections/working_with_projections.html

For more information on the Spatialite family of projects:
http://www.gaia-gis.it/gaia-sins/

Jim Callahan
Orlando, FL

On Wed, Feb 18, 2015 at 9:34 AM, Richard Hipp  wrote:

> In a feeble effort to do "marketing", I have revised the "Appropriate
> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> Things" and "Edge of the Network" above the break.  See:
>
> https://www.sqlite.org/whentouse.html
>
> Please be my "focus group", and provide feedback, comments,
> suggestions, and/or criticism about the revised document.   Send your
> remarks back to this mailing list, or directly to me at the email in
> the signature.
>
> Thank you for your help.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Simon Slavin]

On 25 Feb 2015, at 8:13pm, Jim Callahan  
wrote:

> I first learned about SQLite in the Bioconductor branch of R. I figured if
> they could handle massive genetic databases in SQLite, SQLite ought to be
> able to handle a million (or even 12 million) voters in a voter file.

I'm guessing that you're not British, but it's funny to a Brit to see someone 
with your name posting about voters:



Simon.

[sqlite] Appropriate Uses For SQLite

[Paul Linehan]

Hi Dr. Hipp, group,


> In a feeble effort to do "marketing",

I am sure that nothing you do is "feeble"!


> I have revised the "Appropriate
> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> Things" and "Edge of the Network" above the break.  See:


Nothing wrong with TLAs (some would call them FTLAs :-) )


> https://www.sqlite.org/whentouse.html

> Please be my "focus group", and provide feedback, comments,
> suggestions, and/or criticism about the revised document.   Send your
> remarks back to this mailing list, or directly to me at the email in
> the signature.


I will preface anything I say by saying that I have done very little with
SQLite - I ran Bugzilla (compiled from source) with it once - great
for passing bugs around and other bits and pieces (College projects
and such).

I have however always been impressed by SQLite's ability to
squeeze a lot into a small package - SQLite punches well
above its weight!

Take a look here (at slide 13)
https://www.usenix.org/legacy/events/lisa11/tech/slides/stonebraker.pdf


I think that using SQLite as the db server (where SQLite is effectively the
VoltDB engine) in a VoltDB scenario, putting database requests into some
sort of queue - each transaction runs to completion, (thus eliminating
latching, locking, buffering, recovery (undo/redo) - which Stonebraker feels
to be useless "work") might be something worth examining.

There was another thread about
"The only thing I'd change about SQLite is the SQL bit.".

Now, I'll readily confess that I didn't grasp everything, but it seems
to me that this is starting to go down the route of separate storage
engines (? la MySQL). I don't think this is a good idea - SQLite
(Doh! look at the name) does SQL - when something better comes along,
then let it do that, but it should IMHO remain true to that.

Respectfully submitted.


Paul...


> D. Richard Hipp

-- 

linehanp at tcd.ie

Mob: 00 353 86 864 5772

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

This might give an impression of the scale of what the BioConductor people
are doing.

"The Gene Expression Omnibus (GEO) at the National Center for Biotechnology
Information (NCBI) is the largest fully public repository [as of 2005] for
high-throughput molecular abundance data, primarily gene expression data."
http://www.ncbi.nlm.nih.gov/pubmed/15608262

"The NCBI Gene Expression Omnibus (GEO) represents the largest public
repository of microarray data. However, finding data in GEO can be
challenging. We have developed GEOmetadb in an attempt to make querying the
GEO metadata both easier and more powerful. All GEO metadata records as
well as the relationships between them are parsed and stored in a local
MySQL database. ... In addition, a Bioconductor package, GEOmetadb that
utilizes a SQLite export of the entire GEOmetadb database is also
available, rendering the entire GEO database accessible with full power of
SQL-based queries from within R."
http://www.ncbi.nlm.nih.gov/pubmed/18842599

Annotation Database Interface

Bioconductor version: Release (3.0)

Provides user interface and database connection code for annotation data
packages using SQLite data storage.

Author: Herve Pages, Marc Carlson, Seth Falcon, Nianhua Li

Maintainer: Bioconductor Package Maintainer 

Citation (from within R, enter citation("AnnotationDbi")):

Pages H, Carlson M, Falcon S and Li N. *AnnotationDbi: Annotation Database
Interface*. R package version 1.28.1.

http://master.bioconductor.org/packages/release/bioc/html/AnnotationDbi.html

To really understand the enormity of what they attempting, you need a
picture like the one "Figure 1: Annotation Packages: the big picture" on
the first page of this document:
http://master.bioconductor.org/packages/release/bioc/vignettes/AnnotationDbi/inst/doc/IntroToAnnotationPackages.pdf

Just to grasp the scale and complexity of what they are doing; one of the
databases mentioned GO.db stores a gigantic directed acyclic graph (DAG).

"GOBPANCESTOR Annotation of GO Identifiers to their Biological Process
Ancestors Description This data set describes associations between GO
Biological Process (BP) terms and their ancestor BP terms, based on the
directed acyclic graph (DAG) defined by the Gene Ontology Consortium. The
format is an R object mapping the GO BP terms to all ancestor terms, where
an ancestor term is a more general GO term that precedes the given GO term
in the DAG (in other words, the parents, and all their parents, etc.)."

I get the idea that they are storing a DAG in a SQLite database for use in
R, explaining "associations between GO Biological Process (BP) terms and
their ancestor BP terms, based on the directed acyclic graph (DAG) defined
by the Gene Ontology Consortium."

DAG, SQLite, R, Biological Processes and Gene Ontology in one paragraph;
oh, my head hurts, I think I'll stick to simpler stuff.

Jim





On Wed, Feb 25, 2015 at 3:13 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> I first learned about SQLite in the Bioconductor branch of R. I figured if
> they could handle massive genetic databases in SQLite, SQLite ought to be
> able to handle a million (or even 12 million) voters in a voter file.
>
> Here is a brief article from 2006, "How to Use SQLite with R" by Seth
> Falcon.
>
> http://master.bioconductor.org/help/course-materials/2006/rforbioinformatics/labs/thurs/SQLite-R-howto.pdf
> Jim
>
> On Thu, Feb 19, 2015 at 2:08 PM, Jim Callahan <
> jim.callahan.orlando at gmail.com> wrote:
>
>> Strongly agree with using the R package Sqldf.
>> I used both RSQLite and Sqldf, both worked extremely well (and I am both
>> a lazy and picky end user). Sqldf had the advantage that it took you all
>> the way to your destination the workhorse R object the data frame (R can
>> define new objects, but the data frame as an in memory table is the
>> default).
>> The SQLITE3 command line interface and the R command line had a nice
>> synergy; SQL was great for getting a subset of rows and columns or building
>> a complex view from multiple tables. Both RSqlite and Sqldf could
>> understand the query/view as a table and all looping in both SQL and R took
>> place behind the scenes in compiled code.
>>
>> Smart phone users say "there is an app for that". R users would say
>> "there is a package for that" and CRAN is the equivalent of the Apple app
>> store or Google Play.
>>
>> R has packages for graphics, classical statistics, Bayesian statistics
>> and machine learning. R also has packages for spacial statistics (including
>> reading ESRI shapefiles), for graph theory and for building decision trees.
>> There is another whole app store for biological applications "bioconductor".
>>
>> The CRAN website has "views" (pages or blogs) showing how packages solve
>> common problems in a variety of academic disciplines or application areas.
>>
>> Jim Callahan
>>  On Feb 19, 2015 11:38 AM, "Gabor Grothendieck" 
>> wrote:
>>
>>> On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp  

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

I first learned about SQLite in the Bioconductor branch of R. I figured if
they could handle massive genetic databases in SQLite, SQLite ought to be
able to handle a million (or even 12 million) voters in a voter file.

Here is a brief article from 2006, "How to Use SQLite with R" by Seth
Falcon.
http://master.bioconductor.org/help/course-materials/2006/rforbioinformatics/labs/thurs/SQLite-R-howto.pdf
Jim

On Thu, Feb 19, 2015 at 2:08 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> Strongly agree with using the R package Sqldf.
> I used both RSQLite and Sqldf, both worked extremely well (and I am both a
> lazy and picky end user). Sqldf had the advantage that it took you all the
> way to your destination the workhorse R object the data frame (R can define
> new objects, but the data frame as an in memory table is the default).
> The SQLITE3 command line interface and the R command line had a nice
> synergy; SQL was great for getting a subset of rows and columns or building
> a complex view from multiple tables. Both RSqlite and Sqldf could
> understand the query/view as a table and all looping in both SQL and R took
> place behind the scenes in compiled code.
>
> Smart phone users say "there is an app for that". R users would say "there
> is a package for that" and CRAN is the equivalent of the Apple app store or
> Google Play.
>
> R has packages for graphics, classical statistics, Bayesian statistics and
> machine learning. R also has packages for spacial statistics (including
> reading ESRI shapefiles), for graph theory and for building decision trees.
> There is another whole app store for biological applications "bioconductor".
>
> The CRAN website has "views" (pages or blogs) showing how packages solve
> common problems in a variety of academic disciplines or application areas.
>
> Jim Callahan
>  On Feb 19, 2015 11:38 AM, "Gabor Grothendieck" 
> wrote:
>
>> On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp  wrote:
>> > On 2/18/15, Jim Callahan  wrote:
>> >> I would mention the open source statistical language R in the "data
>> >> analysis" section.
>> >
>> > I've heard of R but never tried to use it myself.  Is an SQLite
>> > interface built into R, sure enough?  Or is that something that has to
>> > be added in separately?
>> >
>>
>> RSQLite is an add-on package to R; however, for data analysis (as
>> opposed to specific database manipulation) I would think most R users
>> would use my sqldf R add-on package (which uses RSQLite by default and
>> also can use driver packages of certain other databases) rather than
>> RSQLite directly if they were going to use SQL for that.
>>
>> In R a data.frame is like an SQL table but in memory and sqldf lets
>> you apply SQL statements to them as if they were all one big SQLite
>> database.  A common misconception is it must be slow but in fact its
>> sufficiently fast that some people use it to get a speed advantage
>> over plain R.  Others use it to learn SQL or to ease the transition to
>> R and others use it allow them to manipulate R data frames without
>> knowing much about R provided they know SQL.
>>
>> If you have not tried R this takes you through installing R and
>> running sqldf in about 5 minutes:
>> https://sqldf.googlecode.com/#For_Those_New_to_R
>>
>> The rest of that page gives many other examples.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>

[sqlite] Appropriate Uses For SQLite

[James K. Lowden]

On Thu, 19 Feb 2015 07:21:17 -0800
Roger Binns  wrote:

> There is a lot that would have to be done with it:
> 
> - - make the IR stable across releases
> - - add more general instructions beyond only what is needed for SQL
> - - expose an API that takes the IR
> - - possibly redesign it to make static verification possible, like
> Java bytecode did.  

As it happens, I'm working on a project exactly like that, and can
attest that it's plenty of work!  

You might be tempted to think -- I was -- that it would be nice to have
a C library of relational algebra operators on a transactional store.
No SQL, just C functions like ra_project, ra_select, and ra_join.  What
could be nicer than, 

t3 = ra_join(db, t1, t2)

?   Well, there's the little matter of "join on what?", so you get 

t3 = ra_join(db, t1, t2, how)

and then there's the question of "what goes in 'how'?".  IOW, how to
specify the join criteria without syntax, using only data?  In fact,
consider a simpler problem, 

t2 = ra_select(db, t1, "where id > 2")

and suddenly you're back in Parserville whether you wanted to be or
not.  You're inventing a language, a very limited, incomplete version
of SQL.  Limited, that is, until you want to say something like "where
exists (select 1 from t5 where t1.id < t5.id)".  Before you know it,
you've reinvented more than half of SQL, except that the ratio of SQL
speakers to your-language speakers approaches infinity.  

Now, you could avoid adding complexity to your language by moving more
of the relational algebra stuff out of it, 

t3 = ra_semijoin( db, t1, 
ra_join(db, t1, t2, ra_natural) )

or something like that.  To Richard's point, the more you do that, the
*less* there is for the optimizer to do, and the more the application
comes to control (for good and ill) the order of operation.  It's very
difficult to maintain the relational abstraction *and* be efficient,
because the C language defines an order of operation, specifically
"greedy evaluation".  That means inner functions are evaluated before
outer ones, inviting production of temporaries that a query optimizer
would just laugh at.  

None of this is news.  Query optimizers have (for decades) been
"lowering the filter" to minimize the processed data.  DBMSs and vector
languages (R, but also APL, NumPy, Matlab, inter alia) are *languages*
precisely so that they can *effect* high-level instructions without
interpreting them literally.  That's not an option in C.  (It's also
what makes things like BLAS such a bear to write *and* use.)  

I'm happy to discuss this off-list in more detail with anyone
interested.  Bringing it back to the topic, I would only say, "be
careful what you wish for".  You want SQL, or something very like it,
to provide abtraction and execution efficiency, because C can't
do that kind of interpretation.  

--jkl

[sqlite] Appropriate Uses For SQLite

[Eduardo Morras]

On Thu, 19 Feb 2015 08:31:13 +0100
Olivier  wrote:

> Hello all,
> 
> https://www.sqlite.org/whentouse.html :
> "The amount of web traffic that SQLite can handle depends on how
> heavily the website uses its database. Generally speaking, any site
> that gets fewer than 100K hits/day should work fine with SQLite. The
> 100K hits/day figure is a conservative estimate, not a hard upper
> bound. SQLite has been demonstrated to work with 10 times that amount
> of traffic.
> 
> The SQLite website (https://www.sqlite.org/) uses SQLite itself, of 
> course, and as of this writing (2015) it handles about 400K to 500K
> HTTP requests per day, about 15-20% of which are dynamic pages
> touching the database. Each dynamic page does roughly 200 SQL
> statements. This setup runs on a single VM that shares a physical
> server with 23 others and yet still keeps the load average of below
> 0.1 most of the time."
> 
> --
> 
> it would be interesting to put *all* sqlite.org pages in the
> database, even if it is useless. This would test with 500K HTTP
> requests per day. It will then be possible to modify this paragraph
> and indicate that Sqlite smoothly manages the 500K HTTP requests per
> day of this website, thus about 100 000K SQL statements per day.
> 
> And why not test with writing on each visit, and even every page
> visit? If Sqlite accept the charge, it would be impressive. it would
> also demonstrate the interest of WAL mode.
> 
> With the evolution of Sqlite and materials evolution (SSD, 
> microprocessors ...), it might be possible.


You can test drupal with sqlite, IIRC it's drupal7. Create a site or use a 
demostration site and use a http benchmark to test it.

There are others cms that can use sqlite as db, seredipity, Joomla, MediaWiki 
you can play and test with.

> 
> Olivier

---   ---
Eduardo Morras 

[sqlite] Appropriate Uses For SQLite

[Hick Gunter]

We are using SQLite as the catch-all data access method (via custom extensions) 
for

- Oracle tables and views
- Faircom CTree files
- Shared memory record stores ("Data Dictionary")
- Log file access
- Blob to record translation (TLV structures)
- Partitioned data stores (CTree and Data Dictionary; content-based 
partitioning)
- Report generation
- User-Defined tables (script output)


-Urspr?ngliche Nachricht-
Von: Richard Hipp [mailto:drh at sqlite.org]
Gesendet: Mittwoch, 18. Februar 2015 15:34
An: General Discussion of SQLite Database
Betreff: [sqlite] Appropriate Uses For SQLite

In a feeble effort to do "marketing", I have revised the "Appropriate Uses For 
SQLite" webpage to move trendy buzzwords like "Internet of Things" and "Edge of 
the Network" above the break.  See:

https://www.sqlite.org/whentouse.html

Please be my "focus group", and provide feedback, comments,
suggestions, and/or criticism about the revised document.   Send your
remarks back to this mailing list, or directly to me at the email in the 
signature.

Thank you for your help.

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

Strongly agree with using the R package Sqldf.
I used both RSQLite and Sqldf, both worked extremely well (and I am both a
lazy and picky end user). Sqldf had the advantage that it took you all the
way to your destination the workhorse R object the data frame (R can define
new objects, but the data frame as an in memory table is the default).
The SQLITE3 command line interface and the R command line had a nice
synergy; SQL was great for getting a subset of rows and columns or building
a complex view from multiple tables. Both RSqlite and Sqldf could
understand the query/view as a table and all looping in both SQL and R took
place behind the scenes in compiled code.

Smart phone users say "there is an app for that". R users would say "there
is a package for that" and CRAN is the equivalent of the Apple app store or
Google Play.

R has packages for graphics, classical statistics, Bayesian statistics and
machine learning. R also has packages for spacial statistics (including
reading ESRI shapefiles), for graph theory and for building decision trees.
There is another whole app store for biological applications "bioconductor".

The CRAN website has "views" (pages or blogs) showing how packages solve
common problems in a variety of academic disciplines or application areas.

Jim Callahan
 On Feb 19, 2015 11:38 AM, "Gabor Grothendieck" 
wrote:

> On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp  wrote:
> > On 2/18/15, Jim Callahan  wrote:
> >> I would mention the open source statistical language R in the "data
> >> analysis" section.
> >
> > I've heard of R but never tried to use it myself.  Is an SQLite
> > interface built into R, sure enough?  Or is that something that has to
> > be added in separately?
> >
>
> RSQLite is an add-on package to R; however, for data analysis (as
> opposed to specific database manipulation) I would think most R users
> would use my sqldf R add-on package (which uses RSQLite by default and
> also can use driver packages of certain other databases) rather than
> RSQLite directly if they were going to use SQL for that.
>
> In R a data.frame is like an SQL table but in memory and sqldf lets
> you apply SQL statements to them as if they were all one big SQLite
> database.  A common misconception is it must be slow but in fact its
> sufficiently fast that some people use it to get a speed advantage
> over plain R.  Others use it to learn SQL or to ease the transition to
> R and others use it allow them to manipulate R data frames without
> knowing much about R provided they know SQL.
>
> If you have not tried R this takes you through installing R and
> running sqldf in about 5 minutes:
> https://sqldf.googlecode.com/#For_Those_New_to_R
>
> The rest of that page gives many other examples.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Eric Grange]

> https://www.sqlite.org/whentouse.html :

I am currently relying on SQLite as main database engine for blockchain
explorers, that's usually between 1 and 2 millions http queries per day,
pretty much all of them hitting more than 60 GB of SQLite databases. There
is a mix of simple table queries, complex joins and extensive
aggregation/statistics queries.

This is kind of a sweet spot for SQLite WAL: the data is queried and
updated all the time, but with only one updater task. Performance is very
good, better than what I see from client/server DB under much lighter
loads. And the latency is just excellent: simple queries run in a matter of
microseconds.

And while it may not be fashionable to say so, I really like the SQL in
SQLite :)
There is no query that cannot be performed efficiently and with little
code. Worst that happens is you need a new index or a few temporary tables
to breakup a complex query in several simpler steps.

I would say SQLite is perfectly suited for "front line" web servers that
serve, present and generally make accessible live data coming from other
systems.

Eric

[sqlite] Appropriate Uses For SQLite

[Jonathan Moules]

Hi Richard,
How about mentioning extensions as a whole? I can't seem to find a list of 
SQLite extensions on sqlite.org, but it seems like it'd be useful information, 
and not just for those deciding on whether the language is right for them.
(When I use the word "extensions", I'm referring to things like Spatialite).

I appreciate the extensions are separate projects, but a list would probably be 
useful (PostGres has one, but it seems short - 
http://www.postgresql.org/download/products/6-postgresql-extensions/ ). It 
might be worth creating such a page for SQLite too.


Another thought - the rich ecosystem of administrative GUI's (Both open source 
and commercial). Given most folks on this list appear to be Guru's who breathe 
SQL, I can see why it was missed, but they're important to us lay-users.

Cheers,
Jonathan



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Wednesday, February 18, 2015 2:34 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Appropriate Uses For SQLite

In a feeble effort to do "marketing", I have revised the "Appropriate Uses For 
SQLite" webpage to move trendy buzzwords like "Internet of Things" and "Edge of 
the Network" above the break.  See:

https://www.sqlite.org/whentouse.html

Please be my "focus group", and provide feedback, comments,
suggestions, and/or criticism about the revised document.   Send your
remarks back to this mailing list, or directly to me at the email in the 
signature.

Thank you for your help.

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/YnvGRjO1h!fGX2PQPOmvUkWM85sEKD4+AxKwKR2OO3rUCZRh4ynTU2SGHzny8KZl+wI!vZXG5UPCP4z!bwErJQ==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Appropriate Uses For SQLite

[Gabor Grothendieck]

On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp  wrote:
> On 2/18/15, Jim Callahan  wrote:
>> I would mention the open source statistical language R in the "data
>> analysis" section.
>
> I've heard of R but never tried to use it myself.  Is an SQLite
> interface built into R, sure enough?  Or is that something that has to
> be added in separately?
>

RSQLite is an add-on package to R; however, for data analysis (as
opposed to specific database manipulation) I would think most R users
would use my sqldf R add-on package (which uses RSQLite by default and
also can use driver packages of certain other databases) rather than
RSQLite directly if they were going to use SQL for that.

In R a data.frame is like an SQL table but in memory and sqldf lets
you apply SQL statements to them as if they were all one big SQLite
database.  A common misconception is it must be slow but in fact its
sufficiently fast that some people use it to get a speed advantage
over plain R.  Others use it to learn SQL or to ease the transition to
R and others use it allow them to manipulate R data frames without
knowing much about R provided they know SQL.

If you have not tried R this takes you through installing R and
running sqldf in about 5 minutes:
https://sqldf.googlecode.com/#For_Those_New_to_R

The rest of that page gives many other examples.

[sqlite] Appropriate Uses For SQLite

[Richard Hipp]

On 2/19/15, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 02/18/2015 01:28 PM, Jay Kreibich wrote:
>> SQLite kind of already does this, if you consider VDBE instructions
>> to be an IR.
>
> There is a lot that would have to be done with it:
>
> - - make the IR stable across releases
>
> - - add more general instructions beyond only what is needed for SQL
>
> - - expose an API that takes the IR
>
> - - possibly redesign it to make static verification possible, like Java
> bytecode did.  Currently VDBE can assume there is no hostile intent,
> but general provided IR would need to be checked.  For example a
> static check that it doesn't goto outside the bounds of the IR.
>
> - - lots of documentation and provision for testing
>
> All that adds up to a lot of work, and possibly quite a lot of redesign!
>

And, there would likely be a performance hit.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Appropriate Uses For SQLite

[Stephen Chrzanowski]

Dr Hipp;

If you're doing a PR stunt, you should take a lot of these emails and put
them on a "What users say" page and link them to the email thread (If it is
accessible by the public -- I get this list in my email).  That way you'd
get real comments about the stability of SQLite against real world
applications.

Myself, I'm a lowly desktop developer who writes stuff for my own personal
use, and have never distributed anything with SQLite to the public.  I have
distributed a program within my place of employment that handles an
infinite number of alarm clocks though

[sqlite] Appropriate Uses For SQLite

[Olivier]

> Simon Slavin 
> 19 f?vrier 2015 08:54
>
> It may be a note in whentouse.html should distinguish between 
> situations where the database is frequently written-to and situations 
> where you have data which is rarely changed. The lack of writes means 
> that a lot of advantages of client/server systems give little 
> advantage. The SQLite web site is a good example.

yes, that's why it would be interesting that there is a writing in the 
database at each visit of page, to see if SQLite can handle it. With WAL 
mode and good material, this may be possible.

This can be done by step, so as not to bring down the site. For example, 
at first, a write every 100 page visits, then if there is no problem, 
80, etc.

Sqlite is known and famous now. But his multi-user capabilities, WAL 
mode, are *much* less known. On the side of marketing, it is its weak point.

I think it is important to raise awareness of the possibilities of WAL 
mode. And what better way than to show a real website with thousands of 
writing every day at the same time that hundreds of thousands of reading?

Many developers (there are idlers everywhere!) need to show to their 
hierarchy of actual usage examples. Otherwise, the hierarchy will take a 
decision on the only reputation of the product: very good single-user 
database. They will be very reluctant to any other  type of use.

> I have an example where I have a completely static 40 Gigabyte (sic.) 
> SQLite web-facing database which is accessed using PHP. It doesn't 
> have as many reads as the SQLite web site but it's a great test of a 
> 40 Gig database file and shows no bad affects related to its size. And 
> SQLite searches billions (sic.) of rows so quickly that one frequent 
> user suspected it wasn't working properly and I had to explain 
> tree-based indexes to him.
Interestingly, thank you Simon!

Maybe on the SQLite website, a page might list these cases of actual 
use, to show that SQLite can handle a variety of situations.

Olivier

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> Olivier 
> 19 f?vrier 2015 08:31
> Hello all,
>
> https://www.sqlite.org/whentouse.html :
> "The amount of web traffic that SQLite can handle depends on how 
> heavily the website uses its database. Generally speaking, any site 
> that gets fewer than 100K hits/day should work fine with SQLite. The 
> 100K hits/day figure is a conservative estimate, not a hard upper 
> bound. SQLite has been demonstrated to work with 10 times that amount 
> of traffic.
>
> The SQLite website (https://www.sqlite.org/) uses SQLite itself, of 
> course, and as of this writing (2015) it handles about 400K to 500K 
> HTTP requests per day, about 15-20% of which are dynamic pages 
> touching the database. Each dynamic page does roughly 200 SQL 
> statements. This setup runs on a single VM that shares a physical 
> server with 23 others and yet still keeps the load average of below 
> 0.1 most of the time."
>
> --
>
> it would be interesting to put *all* sqlite.org pages in the database, 
> even if it is useless. This would test with 500K HTTP requests per 
> day. It will then be possible to modify this paragraph and indicate 
> that Sqlite smoothly manages the 500K HTTP requests per day of this 
> website, thus about 100 000K SQL statements per day.
>
> And why not test with writing on each visit, and even every page 
> visit? If Sqlite accept the charge, it would be impressive. it would 
> also demonstrate the interest of WAL mode.
>
> With the evolution of Sqlite and materials evolution (SSD, 
> microprocessors ...), it might be possible.
>
> Olivier
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Appropriate Uses For SQLite

[Olivier]

Hello all,

https://www.sqlite.org/whentouse.html :
"The amount of web traffic that SQLite can handle depends on how heavily 
the website uses its database. Generally speaking, any site that gets 
fewer than 100K hits/day should work fine with SQLite. The 100K hits/day 
figure is a conservative estimate, not a hard upper bound. SQLite has 
been demonstrated to work with 10 times that amount of traffic.

The SQLite website (https://www.sqlite.org/) uses SQLite itself, of 
course, and as of this writing (2015) it handles about 400K to 500K HTTP 
requests per day, about 15-20% of which are dynamic pages touching the 
database. Each dynamic page does roughly 200 SQL statements. This setup 
runs on a single VM that shares a physical server with 23 others and yet 
still keeps the load average of below 0.1 most of the time."

--

it would be interesting to put *all* sqlite.org pages in the database, 
even if it is useless. This would test with 500K HTTP requests per day. 
It will then be possible to modify this paragraph and indicate that 
Sqlite smoothly manages the 500K HTTP requests per day of this website, 
thus about 100 000K SQL statements per day.

And why not test with writing on each visit, and even every page visit? 
If Sqlite accept the charge, it would be impressive. it would also 
demonstrate the interest of WAL mode.

With the evolution of Sqlite and materials evolution (SSD, 
microprocessors ...), it might be possible.

Olivier

> Richard Hipp <mailto:drh at sqlite.org>
> 18 f?vrier 2015 15:34
> In a feeble effort to do "marketing", I have revised the "Appropriate
> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> Things" and "Edge of the Network" above the break. See:
>
> https://www.sqlite.org/whentouse.html
>
> Please be my "focus group", and provide feedback, comments,
> suggestions, and/or criticism about the revised document. Send your
> remarks back to this mailing list, or directly to me at the email in
> the signature.
>
> Thank you for your help.
>

[sqlite] Appropriate Uses For SQLite

[Simon Slavin]

On 19 Feb 2015, at 7:31am, Olivier  quoted:

> it would be interesting to put *all* sqlite.org pages in the database, even 
> if it is useless. This would test with 500K HTTP requests per day. It will 
> then be possible to modify this paragraph and indicate that Sqlite smoothly 
> manages the 500K HTTP requests per day of this website, thus about 100 000K 
> SQL statements per day.

It may be a note in whentouse.html should distinguish between situations where 
the database is frequently written-to and situations where you have data which 
is rarely changed.  The lack of writes means that a lot of advantages of 
client/server systems give little advantage.  The SQLite web site is a good 
example.

I have an example where I have a completely static 40 Gigabyte (sic.) SQLite 
web-facing database which is accessed using PHP.  It doesn't have as many reads 
as the SQLite web site but it's a great test of a 40 Gig database file and 
shows no bad affects related to its size.  And SQLite searches billions (sic.) 
of rows so quickly that one frequent user suspected it wasn't working properly 
and I had to explain tree-based indexes to him.

Simon.

[sqlite] Appropriate Uses For SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/18/2015 01:28 PM, Jay Kreibich wrote:
> SQLite kind of already does this, if you consider VDBE instructions
> to be an IR.

There is a lot that would have to be done with it:

- - make the IR stable across releases

- - add more general instructions beyond only what is needed for SQL

- - expose an API that takes the IR

- - possibly redesign it to make static verification possible, like Java
bytecode did.  Currently VDBE can assume there is no hostile intent,
but general provided IR would need to be checked.  For example a
static check that it doesn't goto outside the bounds of the IR.

- - lots of documentation and provision for testing

All that adds up to a lot of work, and possibly quite a lot of redesign!

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTl/20ACgkQmOOfHg372QRx0ACgzY6vhU+84LMLMcpeZCAiLtwl
RE8AoJFv/1LQTMhsFvHxgODh/zxnKKQF
=Egzn
-END PGP SIGNATURE-

[sqlite] Appropriate Uses For SQLite

[Lindsay Lawrence]

I have found SQLite is uniquely suited to server-based applications of all
kinds. Wrapped in a simple custom server it is an outstandingly performant
ultra-lightweight engine that can easily service multiple clients. The
ability to spin up multiple instances of the library against the same file
is invaluable. It is the high performance core of a distributed system we
use. Zero configuration is a beautiful thing.

We have found it to be suitable for quite large data; recently loaded the
full Census 2010 and ACS survey datasets, over 0.5 billion rows, into a
single file totalling over 100GB. Properly indexed it is a instantly
queryable allowing some fascinating data exploration and visualization. We
have also used it in scenarios where the ability to 'attach' distinct
database files allows the system to load only what it needs to service
particular queries and makes it simple to clone core tables and distribute
them over multiple systems for combining with related data.

Finally, it has replaced many traditional text-based data exploration tools
I once used... sed, awk, grep etc. Server logs, data exports from
'enterprise' systems, all manner of regularly structured data, are easily
ingested into a sqlite database file and with the addition of simple
dynamically loaded plugins + SQL can be quickly explored, data summarized,
etc. Creating views off of the raw data, I can deliver a single compact
database file to a report writer who using the ODBC driver can make eye
candy reports off of the summarized data with Excel, Access or other UI
based tools.

I will say, the recent addition of the 'WITH' clause to the language as
brought consider expressive power to what I can write directly in SQL. I
love the fact that I can do so much with the SQL language itself now
without writing a line of low-level code and the SQLite API makes it easy
to extend the functional api as needed.

In short, the zero configuration, high performance and lightweight
characteristics of SQLite have made it uniquely suited for the distributed
'cloud' environment we run in where relational data can be fragmented and
distributed over many VMs and still be accessible with a common interface
and query language.

Many thanks.

Best Regards
Lindsay


On Wed, Feb 18, 2015 at 11:11 AM, Darko Volaric  wrote:

> I second this notion. I think SQLite is uniquely suited to server based
> applications of all kinds. Its light footprint and the fact that it's a
> library rather than a full system gives it a flexibility and raw
> performance that other systems cannot. We use it at the core of each node
> in a distributed and parallel system.
>
> When using SQLite the architecture of your database system is not
> preordained by designers who could not foresee novel designs and
> approaches. SQLite is like a systems programing language: It's lean and
> mean and a powerful tool that gives full control to the systems designer
> and programmer.
>
> The only thing I'd change about SQLite is the SQL bit. To me it's an
> anachronism and a mess and needs to be factored further out of the SQLite
> core, with a more rigorous formalism providing an interface (with an
> exposed and supported API) to the database engine, but at a higher level
> than say the virtual machine.
>
> On Wed, Feb 18, 2015 at 9:12 AM, Marcus Grimm 
> wrote:
>
> > We use sqlite as the db engine inside a server application
> > with a number of clients that connect to the server.
> > Sqlite works just beatiful here and I wish these statements
> > "sqlite shall not be used for client/server things" would be
> > worded less generally. In fact when we mention sqlite as our
> > db engine customer point to this restriction and we run into
> > an excuse sort of arguments.
> > On the bottom line: Sqlite CAN very well serve as the DB
> > engine for client/server applications, it just depend how
> > the api is used.
> >
> > Marcus
> >
> > Am 2015-02-18 15:34, schrieb Richard Hipp:
> >
> >> In a feeble effort to do "marketing", I have revised the "Appropriate
> >> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> >> Things" and "Edge of the Network" above the break.  See:
> >>
> >> https://www.sqlite.org/whentouse.html
> >>
> >> Please be my "focus group", and provide feedback, comments,
> >> suggestions, and/or criticism about the revised document.   Send your
> >> remarks back to this mailing list, or directly to me at the email in
> >> the signature.
> >>
> >> Thank you for your help.
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Marcus Grimm]

We use sqlite as the db engine inside a server application
with a number of clients that connect to the server.
Sqlite works just beatiful here and I wish these statements
"sqlite shall not be used for client/server things" would be
worded less generally. In fact when we mention sqlite as our
db engine customer point to this restriction and we run into
an excuse sort of arguments.
On the bottom line: Sqlite CAN very well serve as the DB
engine for client/server applications, it just depend how
the api is used.

Marcus

Am 2015-02-18 15:34, schrieb Richard Hipp:
> In a feeble effort to do "marketing", I have revised the "Appropriate
> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> Things" and "Edge of the Network" above the break.  See:
> 
> https://www.sqlite.org/whentouse.html
> 
> Please be my "focus group", and provide feedback, comments,
> suggestions, and/or criticism about the revised document.   Send your
> remarks back to this mailing list, or directly to me at the email in
> the signature.
> 
> Thank you for your help.

[sqlite] Appropriate Uses For SQLite

[Adam Kennedy]

But the great thing about SQLite is you don't have to go logical form in
light weight apps.

http://search.cpan.org/dist/ORLite/lib/ORLite.pm

ORLite does a half-and-half approach that generates the easy parts of the
SQL with very little code, but avoids the code weight needed to generate
all of it.

my @users = DB::User->select( 'where name = ? order by name', $name );

It's great to have the flexibility to go all logical form, partly, or none,
as best suits the nature of the database you are embedding in your larger
application.

Adam

On 18 February 2015 at 15:44, Darko Volaric  wrote:

> Right now, roughly speaking, I'm doing:  logical form -> SQL -> execution
> of logical form, and SQL seems to me to just be an arbitrary hoop that I
> have to jump through, complicating things along the way.
>

[sqlite] Appropriate Uses For SQLite

[Darko Volaric]

I think that IR would be something like first order predicate logic, to
which SQL and the relational calculus is closely related. Now that we have
WITH and recursive queries, you've basically got a bottom-up evaluation of
the declarative subset of Prolog (if you ignore issues relating to logic
variables).

On Wed, Feb 18, 2015 at 1:13 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 02/18/2015 11:43 AM, Richard Hipp wrote:
> > but I think the truth is we are probably stuck with SQL for a while
> > yet.
>
> In theory there could be an intermediate representation form (like
> compilers do) that is publicly available, with the (now optional) SQL
> part producing IR, as well as any other query language
> implementations.  The LLVM project is an example of doing a design
> like this.
>
> In practise the result wouldn't be very Lite, and would constrain
> future development.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlTlAGsACgkQmOOfHg372QRP+ACgqoP3Ss8ZgvO95M8IVHhLRDbo
> itEAoMhJKWIKiiYjsAqNUGl/cpv/e+fp
> =Z+np
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Darko Volaric]

I agree with you, and am not suggesting getting rid of it, but rather
making it "pluggable" like many parts of the back end.

Right now, roughly speaking, I'm doing:  logical form -> SQL -> execution
of logical form, and SQL seems to me to just be an arbitrary hoop that I
have to jump through, complicating things along the way.

Obviously this is not terribly mainstream, but I think would greatly
improve the usefulness and structure of SQLite.

Just my 2 cents on the subject...

On Wed, Feb 18, 2015 at 11:43 AM, Richard Hipp  wrote:

> On 2/18/15, Darko Volaric  wrote:
> > The only thing I'd change about SQLite is the SQL bit.
>
> Most people agree that the SQL language is a bit of a mess.  But so is
> the Qwerty keyboard layout.  The problem is that the improvement you
> get by moving to something else is less than the pain of making the
> move.  Everybody agrees that Dvorak is a better keyboard layout, and
> yet nobody uses Dvorak even though it has been widely available on
> computer keyboards since the Apple-II,  Not intending to rain on your
> parade, but I think the truth is we are probably stuck with SQL for a
> while yet.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Jay Kreibich]

On Feb 18, 2015, at 3:13 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 02/18/2015 11:43 AM, Richard Hipp wrote:
>> but I think the truth is we are probably stuck with SQL for a while
>> yet.
> 
> In theory there could be an intermediate representation form (like
> compilers do) that is publicly available, with the (now optional) SQL
> part producing IR, as well as any other query language
> implementations.  The LLVM project is an example of doing a design
> like this.

SQLite kind of already does this, if you consider VDBE instructions to be an IR.

I?ve often wondering how difficult it would be to put a new front-end on SQLite 
to parse Tutorial D (or some other ?true relational? language) and generate 
VDBE instructions.

 -j

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

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] Appropriate Uses For SQLite

[Richard Hipp]

On 2/18/15, Darko Volaric  wrote:
> The only thing I'd change about SQLite is the SQL bit.

Most people agree that the SQL language is a bit of a mess.  But so is
the Qwerty keyboard layout.  The problem is that the improvement you
get by moving to something else is less than the pain of making the
move.  Everybody agrees that Dvorak is a better keyboard layout, and
yet nobody uses Dvorak even though it has been widely available on
computer keyboards since the Apple-II,  Not intending to rain on your
parade, but I think the truth is we are probably stuck with SQL for a
while yet.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Appropriate Uses For SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/18/2015 11:43 AM, Richard Hipp wrote:
> but I think the truth is we are probably stuck with SQL for a while
> yet.

In theory there could be an intermediate representation form (like
compilers do) that is publicly available, with the (now optional) SQL
part producing IR, as well as any other query language
implementations.  The LLVM project is an example of doing a design
like this.

In practise the result wouldn't be very Lite, and would constrain
future development.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlTlAGsACgkQmOOfHg372QRP+ACgqoP3Ss8ZgvO95M8IVHhLRDbo
itEAoMhJKWIKiiYjsAqNUGl/cpv/e+fp
=Z+np
-END PGP SIGNATURE-

[sqlite] Appropriate Uses For SQLite

[Darko Volaric]

I second this notion. I think SQLite is uniquely suited to server based
applications of all kinds. Its light footprint and the fact that it's a
library rather than a full system gives it a flexibility and raw
performance that other systems cannot. We use it at the core of each node
in a distributed and parallel system.

When using SQLite the architecture of your database system is not
preordained by designers who could not foresee novel designs and
approaches. SQLite is like a systems programing language: It's lean and
mean and a powerful tool that gives full control to the systems designer
and programmer.

The only thing I'd change about SQLite is the SQL bit. To me it's an
anachronism and a mess and needs to be factored further out of the SQLite
core, with a more rigorous formalism providing an interface (with an
exposed and supported API) to the database engine, but at a higher level
than say the virtual machine.

On Wed, Feb 18, 2015 at 9:12 AM, Marcus Grimm 
wrote:

> We use sqlite as the db engine inside a server application
> with a number of clients that connect to the server.
> Sqlite works just beatiful here and I wish these statements
> "sqlite shall not be used for client/server things" would be
> worded less generally. In fact when we mention sqlite as our
> db engine customer point to this restriction and we run into
> an excuse sort of arguments.
> On the bottom line: Sqlite CAN very well serve as the DB
> engine for client/server applications, it just depend how
> the api is used.
>
> Marcus
>
> Am 2015-02-18 15:34, schrieb Richard Hipp:
>
>> In a feeble effort to do "marketing", I have revised the "Appropriate
>> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
>> Things" and "Edge of the Network" above the break.  See:
>>
>> https://www.sqlite.org/whentouse.html
>>
>> Please be my "focus group", and provide feedback, comments,
>> suggestions, and/or criticism about the revised document.   Send your
>> remarks back to this mailing list, or directly to me at the email in
>> the signature.
>>
>> Thank you for your help.
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[Richard Hipp]

On 2/18/15, Jim Callahan  wrote:
> I would mention the open source statistical language R in the "data
> analysis" section.

I've heard of R but never tried to use it myself.  Is an SQLite
interface built into R, sure enough?  Or is that something that has to
be added in separately?


> The interface in the RSqlite package is much better and
> faster than any of the Python interfaces in that the interface fully
> understands queries as tables and that the looping for the return of rows
> is done in compiled code rather than at an interpreted command line.
> On Feb 18, 2015 9:34 AM, "Richard Hipp"  wrote:
>
>> In a feeble effort to do "marketing", I have revised the "Appropriate
>> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
>> Things" and "Edge of the Network" above the break.  See:
>>
>> https://www.sqlite.org/whentouse.html
>>
>> Please be my "focus group", and provide feedback, comments,
>> suggestions, and/or criticism about the revised document.   Send your
>> remarks back to this mailing list, or directly to me at the email in
>> the signature.
>>
>> Thank you for your help.
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

I would mention the open source statistical language R in the "data
analysis" section. The interface in the RSqlite package is much better and
faster than any of the Python interfaces in that the interface fully
understands queries as tables and that the looping for the return of rows
is done in compiled code rather than at an interpreted command line.
On Feb 18, 2015 9:34 AM, "Richard Hipp"  wrote:

> In a feeble effort to do "marketing", I have revised the "Appropriate
> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> Things" and "Edge of the Network" above the break.  See:
>
> https://www.sqlite.org/whentouse.html
>
> Please be my "focus group", and provide feedback, comments,
> suggestions, and/or criticism about the revised document.   Send your
> remarks back to this mailing list, or directly to me at the email in
> the signature.
>
> Thank you for your help.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Appropriate Uses For SQLite

[John McKown]

On Wed, Feb 18, 2015 at 8:53 AM, Richard Hipp  wrote:

> On 2/18/15, Jim Callahan  wrote:
> > I would mention the open source statistical language R in the "data
> > analysis" section.
>
> I've heard of R but never tried to use it myself.  Is an SQLite
> interface built into R, sure enough?  Or is that something that has to
> be added in separately?
>
>
?It is not a part of the base language, no. It is an add-on module on CRAN
by Hadley Wickham, who is very expert in R. CRAN is to R what CPAN is to
Perl and CTAN is to TeX.
http://cran.revolutionanalytics.com/
http://cran.revolutionanalytics.com/web/packages/RSQLite/index.html
?


-- 
He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Appropriate Uses For SQLite

[Richard Hipp]

In a feeble effort to do "marketing", I have revised the "Appropriate
Uses For SQLite" webpage to move trendy buzzwords like "Internet of
Things" and "Edge of the Network" above the break.  See:

https://www.sqlite.org/whentouse.html

Please be my "focus group", and provide feedback, comments,
suggestions, and/or criticism about the revised document.   Send your
remarks back to this mailing list, or directly to me at the email in
the signature.

Thank you for your help.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Appropriate Uses For SQLite

[Rich Shepard]

On Wed, 18 Feb 2015, Richard Hipp wrote:

> Please be my "focus group", and provide feedback, comments, suggestions,
> and/or criticism about the revised document. Send your remarks back to
> this mailing list, or directly to me at the email in the signature.

Richard,

   It is clear and well organized. The one difference between SQLite and
a client-server rdbms that could be explicit is single-user versus
multi-user applications and situations. That difference is implied in the
descriptions but likely not noticed by your target market audience.

Rich

Re: [sqlite] appropriate Uses For SQLite

[Visnik]

Thanks for the info. it has helped clarify some things for me. I think I will
stick with MySQL with this application as it is going to be a networked app. 
If I do one that does not need to be on a network i will dive into SQLlite.

thanks again.



Pavel Ivanov-2 wrote:
> 
> There's no contradiction in those citations. First talks about website
> with some 100K hits/day. Website means application running on some
> dedicated server, clients send HTTP requests to your application and
> application processes it working with locally stored database. Second
> citation is talking about some file-server where you store your
> database, and clients run your application locally which then access
> database via some file sharing network protocol.
> 
>> I have no experience with SQLite but some MySQL experience. Is SQLite a
>> good
>> fit for this type of application/situation?
> 
> Generally speaking - no, because as citation says using SQLite
> database over the network is a bad idea. But depending on your usage
> patterns it might work, although without strong guarantees that
> database will never be corrupted.
> 
> 
> Pavel
> 
> On Thu, Sep 30, 2010 at 4:17 PM, Visnik <visnik+oldnab...@gmail.com>
> wrote:
>>
>> I am looking into the best method for implementing a database for a small
>> application that I will most likely be  building it in Adobe Air.  I
>> looked
>> at "Appropriate Uses For SQLite" on the the SQLite.org website and I got
>> some conflicting info.
>>
>> for Example I saw the following:
>> "SQLite usually will work great as the database engine for low to medium
>> traffic websites (which is to say, 99.9% of all websites). The amount of
>> web
>> traffic that SQLite can handle depends, of course, on how heavily the
>> website uses its database. Generally speaking, any site that gets fewer
>> than
>> 100K hits/day should work fine with SQLite."
>>
>> this seemed to conflict a little with this:
>> "If you have many client programs accessing a common database over a
>> network, you should consider using a client/server database engine
>> instead
>> of SQLite."
>>
>> My application will be on a local network and only accessed by 10- 20
>> people
>> at most. I would like to have a non-client/server based solution if
>> possible.  I was looking at MySQL originally, but the "embedded into
>> application part of SQLite got my attention as I would not have to have a
>> Apache, PHP an MySQL solution ready at all times.
>>
>> I have no experience with SQLite but some MySQL experience. Is SQLite a
>> good
>> fit for this type of application/situation?
>>
>> thanks
>> --
>> View this message in context:
>> http://old.nabble.com/appropriate-Uses-For-SQLite-tp29852017p29852017.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/appropriate-Uses-For-SQLite-tp29852017p29852925.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] appropriate Uses For SQLite

[Pavel Ivanov]

There's no contradiction in those citations. First talks about website
with some 100K hits/day. Website means application running on some
dedicated server, clients send HTTP requests to your application and
application processes it working with locally stored database. Second
citation is talking about some file-server where you store your
database, and clients run your application locally which then access
database via some file sharing network protocol.

> I have no experience with SQLite but some MySQL experience. Is SQLite a good
> fit for this type of application/situation?

Generally speaking - no, because as citation says using SQLite
database over the network is a bad idea. But depending on your usage
patterns it might work, although without strong guarantees that
database will never be corrupted.


Pavel

On Thu, Sep 30, 2010 at 4:17 PM, Visnik <visnik+oldnab...@gmail.com> wrote:
>
> I am looking into the best method for implementing a database for a small
> application that I will most likely be  building it in Adobe Air.  I looked
> at "Appropriate Uses For SQLite" on the the SQLite.org website and I got
> some conflicting info.
>
> for Example I saw the following:
> "SQLite usually will work great as the database engine for low to medium
> traffic websites (which is to say, 99.9% of all websites). The amount of web
> traffic that SQLite can handle depends, of course, on how heavily the
> website uses its database. Generally speaking, any site that gets fewer than
> 100K hits/day should work fine with SQLite."
>
> this seemed to conflict a little with this:
> "If you have many client programs accessing a common database over a
> network, you should consider using a client/server database engine instead
> of SQLite."
>
> My application will be on a local network and only accessed by 10- 20 people
> at most. I would like to have a non-client/server based solution if
> possible.  I was looking at MySQL originally, but the "embedded into
> application part of SQLite got my attention as I would not have to have a
> Apache, PHP an MySQL solution ready at all times.
>
> I have no experience with SQLite but some MySQL experience. Is SQLite a good
> fit for this type of application/situation?
>
> thanks
> --
> View this message in context: 
> http://old.nabble.com/appropriate-Uses-For-SQLite-tp29852017p29852017.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] appropriate Uses For SQLite

[Visnik]

I am looking into the best method for implementing a database for a small
application that I will most likely be  building it in Adobe Air.  I looked
at "Appropriate Uses For SQLite" on the the SQLite.org website and I got
some conflicting info.

for Example I saw the following:
"SQLite usually will work great as the database engine for low to medium
traffic websites (which is to say, 99.9% of all websites). The amount of web
traffic that SQLite can handle depends, of course, on how heavily the
website uses its database. Generally speaking, any site that gets fewer than
100K hits/day should work fine with SQLite."

this seemed to conflict a little with this:
"If you have many client programs accessing a common database over a
network, you should consider using a client/server database engine instead
of SQLite."

My application will be on a local network and only accessed by 10- 20 people
at most. I would like to have a non-client/server based solution if
possible.  I was looking at MySQL originally, but the "embedded into
application part of SQLite got my attention as I would not have to have a
Apache, PHP an MySQL solution ready at all times. 

I have no experience with SQLite but some MySQL experience. Is SQLite a good
fit for this type of application/situation?

thanks
-- 
View this message in context: 
http://old.nabble.com/appropriate-Uses-For-SQLite-tp29852017p29852017.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Appropriate uses for SQLite

[David M. X. Green]

Thank you for your thorough replies to my supplementary remarks on this topic.
It seems to me clear now that it is best not to take a careful disclaimer etc (on 
site networking support) as typically/generally indicating a limitation on 
usefulness of sqlite. Rather it is a matter of taking care & testing the system 
carefully when multiple writes may be an issue due to bugs in the file system 
locking. Moreover, like sqlite, other databases are known to work well relying on 
the system file locking.

David
---
David M X Green


|||"Mike Owens" (2007-02-12 15:13) wrote: |||>>>

Hey, sorry I'm a little late on this one (as usual).








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

Re: [sqlite] Appropriate uses for SQLite

[Mike Owens]

Hey, sorry I'm a little late on this one (as usual).

On 2/3/07, David M X Green <[EMAIL PROTECTED]> wrote:

I am new to this but are these issues those of trying to get it to do what sqlite 
it > is not designed for. I quote the book

The Definitive Guide to SQLite - Chapter 1 --- Networking
"  Again, most of these limitations are intentional—they are a result of 
SQLite's
design. Supporting high write concurrency, for example, brings with it great
deal of complexity and this runs counter to SQLite's simplicity in design.
Similarly, being an embedded database, SQLite intentionally does
__not__support__networking__ [my emphasis].  This should come as no surprise.
In short, what SQLite can't do is a direct result of what it can. It was
designed to operate as a modular, simple, compact, and easy-to-use embedded
relational database whose code base is within the reach of the programmers
using it. And in many respects it can do what many other databases cannot, such
as run in embedded environments where actual power consumption is a limiting
factor. "


Dennis Cote and Scott Hess have hit the nail on the head, in my
opinion. As you quoted, chapter 1 sites networking support as a big
disclaimer, for the simple reason that I want to make it clear to the
reader what SQLite does not do, so that ideally he/she can make a
decision right up front whether or not if might be useful for their
purposes.

Having said that, p 196 in chapter 5 includes a sidebar on network
file systems and this whole issue. The issue is in theory quite
simple: SQLite supports the requisite locking semantics such that if
the underlying network file system supports locking in its network
files system in the same way it does its local file system (which it
should), then then SQLite will work correctly. Period. That is to say,
SQLite does locking the same way over network file systems as it does
local file systems. Since this locking code works correctly on local
file systems, and network file systems use exactly the same locking
semantics, then one may logically conclude that SQLite works correctly
over network file systems. End theory.

In practice, however, it's a whole other question: How do you know
that a particular network file system implements every part of this
locking protocol accurately under all conditions? Plus, you are
introducing many other variables -- namely the network, both logical
(TCP stack bugs, etc.) and physical (bad NICs, switches, cables,
punchdowns, etc.). Everything becomes much more complex, even if the
NFS implementation is flawless.

But let's consider just the network file system. With respect to it,
the short answer is, no one can tell you definitively that it's not
without bugs. And a single bug is all it takes to cause proper locking
to fail, no fault to SQLite. It comes down to the logical conundrum
that you can't prove a negative. Even the people who implemented a
particular network file system cannot be 100% sure it is not without a
bug somewhere that may adversely affect locking in some particular
scenario (client crash, or network problem).

So the official answer from SQLite, wisely, is that they cannot
certify that a particular network file system is without bugs, and
therefore whether or not SQLite works over a particular NFS
implementation for your particular application. There are simply too
many variables to account for, but more importantly, *all* of these
variables are external to SQLite. SQLite uses standard system calls
for locking. If the NFS implements those locking calls correctly, then
SQLite will work correctly.

So that's the disclaimer. It's not that SQLite lacks special code to
make it work over network file systems. Much to the contrary, it has
everything required for it to work. The disclaimer simply states that
you cannot expect SQLite's developers to speak for code or systems
they did not write. They can tell you for sure that SQLite follows
POSIX locking semantics on UNIX and the equivalent on Windows. The
locking semantics used on local file systems are the same used for
network file systems (that is, they should be transparent --- no
additional coding is required to use them). What SQLite's developers
cannot tell you that Samba is without bugs, or that Linux NFS is
without bugs, which may be connecting to a OS X NFS server, which is
also without bugs, etc. They didn't write the code. Theoretically,
SQLite should work perfectly in all cases, but one small bug in a
single NFS implementation on either client or server, and either a
lock may exist too long, leaving other clients locked out, or may not
happen at all, letting multiple clients write to the same database at
the same time, resulting in almost assured corruption.

So, what can you do? Well, things may still work out fine for you, you
just have to determine empirically whether or not your particular
system's network file system has any serious locking bugs that come
into play for your particular application.


Is it 

Re: [sqlite] Appropriate uses for SQLite

[Chris Hanson]

On Feb 2, 2007, at 8:59 AM, Clark Christensen wrote:

FWIW, I'm not convinced Samba has locking working correctly.  Using  
a very recent Samba version, I managed to corrupt a SQLite database  
last fall by (I think) doing simultaneous writes from the Linux host  
box, and my WinXP client box (via a SMB drive map).  I'm guessing  
the XP writes started first.  It seems unlikely it would have  
happened had the Linux host started first.


This is a situation where you really need all file locking managed by  
the operating system, or you need to access the file only via a single  
mechanism (e.g. only via SMB, even on the Linux host box from which  
the SMB mount is exported).


If you have clients accessing a file via different remote mechanisms,  
these mechanisms *may not* share an underlying locking infrastructure,  
which can easily lead to corruption.  Only if they are *guaranteed to*  
share an underlying locking infrastructure is it safe to access a file  
that requires any sort of locking via multiple different remote  
mechanisms.  This is a general issue with any shared-access remote  
filesystem and any operating system, not something specific to SQLite,  
NFS, SMB, Windows, or Linux.


  -- Chris


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

RE: [sqlite] Appropriate uses for SQLite

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

Yes, John, thanks very much.

It seems absolutely clear by now that sharing data on a network mount by
multiple web servers is not suitable for SQLite.

Funny, now that I say that I wonder how I couldn't see that from the
beginning this was a worst case scenario.

I guess I didn't know much about SQLite when I first posted and got
excited about the design methodology. At least I understand SQLite a lot
better now.

Thanks again to everyone who posted in reply to my question.

Take care guys and have fun.
God bless.
Anil

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Monday, 5 February 2007 3:08 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

We used Sqlite in a web server successfully.  It is embedded in a custom
written web server which provides multithreaded access and application
language support (an application server).  It handles many databases,
with each user capable of having their own database.  Synchronization is
performed within the server.

In our application Sqlite is an ideal solution and outperforms the
alternative, using an IPC or network connection to PostgreSQL.  The
embedded nature of Sqlite gives us an advantage by minimizing network
and disk traffic and avoiding repeated process creation and destruction.

In your application my reaction would be to run PostgreSQL or similar
(maybe the new free version of DB/2) on one of your servers and connect
from the others.

Anil Gulati -X (agulati - Michael Page at Cisco) wrote:
> I have certainly got no desire to change the design goals of SQLite.
> 
> The only reason I posted originally is because there was a strong 
> recommendation in the documentation to use it for web sites which of 
> course risk concurrent writes... The trouble introduced there in my 
> case is that I am using multiple web servers which therefore have to 
> share data over the network.
> 
> FWIW I don't interpret any posts on this thread as an attempt to 
> change SQLite, either. But there seems to be some who see value in 
> more clearly defining *when* SQLite *does* work. I guess that there is

> a lot of enthusiasm for SQLite's ability and performance and it's nice

> to be able to prove that SQLite does actually compete in areas it was 
> not even designed for. Perhaps an argument for less complex design as 
> a generic software design strategy.
> 
> -Original Message-
> From: David M X Green [mailto:[EMAIL PROTECTED]
> Sent: Sunday, 4 February 2007 1:17 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Appropriate uses for SQLite
> 
> I am new to this but are these issues those of trying to get it to do 
> what sqlite it is not designed for. I quote the book
> 
> The Definitive Guide to SQLite - Chapter 1 --- Networking "  
> Again, most of these limitations are intentional-they are a result of 
> SQLite's design. Supporting high write concurrency, for example, 
> brings with it great deal of complexity and this runs counter to 
> SQLite's simplicity in design.
> Similarly, being an embedded database, SQLite intentionally does 
> __not__support__networking__ [my emphasis].  This should come as no 
> surprise.
> In short, what SQLite can't do is a direct result of what it can. It 
> was designed to operate as a modular, simple, compact, and easy-to-use

> embedded relational database whose code base is within the reach of 
> the programmers using it. And in many respects it can do what many 
> other databases cannot, such as run in embedded environments where 
> actual power consumption is a limiting factor. "
> --
> Is it really a good idea to network a data base that relies on the OS 
> file systems like this? Is it ever going to be safe enough?
> 
> David M X Green
> 
> 
>>>>|||"Alex Roston" (2007-02-02 20:05) wrote: |||>>>
>>
>>Scott Hess wrote:
>>
>>>On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
>>>
>>>>[EMAIL PROTECTED] wrote:
>>>>
>>>>>The problem is, not many network filesystems work correctly.
>>>>
>>>>I'm sure someone knows which versions of NFS have working file 
>>>>locking, at least under Linux.
>>>
>>>I doubt it is this easy.  You need to line up a bunch of things in 
>>>the right order, with the right versions of nfs, locking services, 
>>>perhaps the right kernel versions, the right config, etc, etc.
>>>
>>>IMO the _real_ solution would be a package which you could use to try
> 
> 
>>>to verify whether the system you have is actually delivering working 
>>>file locking.  Something like diskchecker (see 
>>>http://brad.livejournal.com/2116715.html).  The basic i

Re: [sqlite] Appropriate uses for SQLite

[John Stanton]

We used Sqlite in a web server successfully.  It is embedded in a custom 
written web server which provides multithreaded access and application 
language support (an application server).  It handles many databases, 
with each user capable of having their own database.  Synchronization is 
performed within the server.


In our application Sqlite is an ideal solution and outperforms the 
alternative, using an IPC or network connection to PostgreSQL.  The 
embedded nature of Sqlite gives us an advantage by minimizing network 
and disk traffic and avoiding repeated process creation and destruction.


In your application my reaction would be to run PostgreSQL or similar 
(maybe the new free version of DB/2) on one of your servers and connect 
from the others.


Anil Gulati -X (agulati - Michael Page at Cisco) wrote:

I have certainly got no desire to change the design goals of SQLite.

The only reason I posted originally is because there was a strong
recommendation in the documentation to use it for web sites which of
course risk concurrent writes... The trouble introduced there in my case
is that I am using multiple web servers which therefore have to share
data over the network.

FWIW I don't interpret any posts on this thread as an attempt to change
SQLite, either. But there seems to be some who see value in more clearly
defining *when* SQLite *does* work. I guess that there is a lot of
enthusiasm for SQLite's ability and performance and it's nice to be able
to prove that SQLite does actually compete in areas it was not even
designed for. Perhaps an argument for less complex design as a generic
software design strategy.

-Original Message-
From: David M X Green [mailto:[EMAIL PROTECTED] 
Sent: Sunday, 4 February 2007 1:17 AM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

I am new to this but are these issues those of trying to get it to do
what sqlite it is not designed for. I quote the book

The Definitive Guide to SQLite - Chapter 1 --- Networking "  Again,
most of these limitations are intentional-they are a result of SQLite's
design. Supporting high write concurrency, for example, brings with it
great deal of complexity and this runs counter to SQLite's simplicity in
design.
Similarly, being an embedded database, SQLite intentionally does
__not__support__networking__ [my emphasis].  This should come as no
surprise.
In short, what SQLite can't do is a direct result of what it can. It was
designed to operate as a modular, simple, compact, and easy-to-use
embedded relational database whose code base is within the reach of the
programmers using it. And in many respects it can do what many other
databases cannot, such as run in embedded environments where actual
power consumption is a limiting factor. "
--
Is it really a good idea to network a data base that relies on the OS
file systems like this? Is it ever going to be safe enough?

David M X Green



|||"Alex Roston" (2007-02-02 20:05) wrote: |||>>>


Scott Hess wrote:


On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:


[EMAIL PROTECTED] wrote:


The problem is, not many network filesystems work correctly.


I'm sure someone knows which versions of NFS have working file 
locking, at least under Linux.


I doubt it is this easy.  You need to line up a bunch of things in 
the right order, with the right versions of nfs, locking services, 
perhaps the right kernel versions, the right config, etc, etc.


IMO the _real_ solution would be a package which you could use to try



to verify whether the system you have is actually delivering working 
file locking.  Something like diskchecker (see 
http://brad.livejournal.com/2116715.html).  The basic idea would be 
to have a set of networked processes exercising the APIs and looking 
for discrepencies.  Admittedly, passing such a test only gets you a 
statistical assurance (maybe if you'd run the test for ten more 
minutes, or with another gig of data, it would have failed!), but 
failing such a test is a sure sign of a problem.


-scott


That's a really useful idea, not only for itself, but also because it 
might lead to debugging some of the network issues, and allowing the 
developers to build a database of stuff that works: "Use Samba version




foo, with patch bar, and avoid the Fooberry 6 network cards." Or


whatever.

My suspicion, in the earlier case with Windows and Linux clients is 
that Windows didn't handle the locking correctly, and that would be 
worth proving/disproving too.


An alternate approach is to use something a little more like a 
standard client-server model, where there's a "server" program which 
intervenes between (possibly multiple) workstations and the database 
itself. The "server" would queue requests to the database, make sure 
that no more than one write request at a time went to the database, 
and certify that writes have been properly mad

RE: [sqlite] Appropriate uses for SQLite

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

I have certainly got no desire to change the design goals of SQLite.

The only reason I posted originally is because there was a strong
recommendation in the documentation to use it for web sites which of
course risk concurrent writes... The trouble introduced there in my case
is that I am using multiple web servers which therefore have to share
data over the network.

FWIW I don't interpret any posts on this thread as an attempt to change
SQLite, either. But there seems to be some who see value in more clearly
defining *when* SQLite *does* work. I guess that there is a lot of
enthusiasm for SQLite's ability and performance and it's nice to be able
to prove that SQLite does actually compete in areas it was not even
designed for. Perhaps an argument for less complex design as a generic
software design strategy.

-Original Message-
From: David M X Green [mailto:[EMAIL PROTECTED] 
Sent: Sunday, 4 February 2007 1:17 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Appropriate uses for SQLite

I am new to this but are these issues those of trying to get it to do
what sqlite it is not designed for. I quote the book

The Definitive Guide to SQLite - Chapter 1 --- Networking "  Again,
most of these limitations are intentional-they are a result of SQLite's
design. Supporting high write concurrency, for example, brings with it
great deal of complexity and this runs counter to SQLite's simplicity in
design.
Similarly, being an embedded database, SQLite intentionally does
__not__support__networking__ [my emphasis].  This should come as no
surprise.
In short, what SQLite can't do is a direct result of what it can. It was
designed to operate as a modular, simple, compact, and easy-to-use
embedded relational database whose code base is within the reach of the
programmers using it. And in many respects it can do what many other
databases cannot, such as run in embedded environments where actual
power consumption is a limiting factor. "
--
Is it really a good idea to network a data base that relies on the OS
file systems like this? Is it ever going to be safe enough?

David M X Green

>>>|||"Alex Roston" (2007-02-02 20:05) wrote: |||>>>
> Scott Hess wrote:
>> On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
>>> [EMAIL PROTECTED] wrote:
>>> > The problem is, not many network filesystems work correctly.
>>>
>>> I'm sure someone knows which versions of NFS have working file 
>>> locking, at least under Linux.
>>
>> I doubt it is this easy.  You need to line up a bunch of things in 
>> the right order, with the right versions of nfs, locking services, 
>> perhaps the right kernel versions, the right config, etc, etc.
>>
>> IMO the _real_ solution would be a package which you could use to try

>> to verify whether the system you have is actually delivering working 
>> file locking.  Something like diskchecker (see 
>> http://brad.livejournal.com/2116715.html).  The basic idea would be 
>> to have a set of networked processes exercising the APIs and looking 
>> for discrepencies.  Admittedly, passing such a test only gets you a 
>> statistical assurance (maybe if you'd run the test for ten more 
>> minutes, or with another gig of data, it would have failed!), but 
>> failing such a test is a sure sign of a problem.
>>
>> -scott
> That's a really useful idea, not only for itself, but also because it 
> might lead to debugging some of the network issues, and allowing the 
> developers to build a database of stuff that works: "Use Samba version

> foo, with patch bar, and avoid the Fooberry 6 network cards." Or
whatever.
> 
> My suspicion, in the earlier case with Windows and Linux clients is 
> that Windows didn't handle the locking correctly, and that would be 
> worth proving/disproving too.
> 
> An alternate approach is to use something a little more like a 
> standard client-server model, where there's a "server" program which 
> intervenes between (possibly multiple) workstations and the database 
> itself. The "server" would queue requests to the database, make sure 
> that no more than one write request at a time went to the database, 
> and certify that writes have been properly made.
> 
> The problem with this approach is that it eats quite heavily into 
> SQLite's speed advantage, but if you've already put thousands of hours

> into developing your system, it might be a worthwhile hack.
> 
> Alex
> 
> --
> ---
> 
> 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

[David M X Green]

I am new to this but are these issues those of trying to get it to do what 
sqlite it is not designed for. I quote the book

The Definitive Guide to SQLite - Chapter 1 --- Networking
"  Again, most of these limitations are intentional—they are a result of 
SQLite’s
design. Supporting high write concurrency, for example, brings with it great
deal of complexity and this runs counter to SQLite’s simplicity in design.
Similarly, being an embedded database, SQLite intentionally does
__not__support__networking__ [my emphasis].  This should come as no surprise.
In short, what SQLite can’t do is a direct result of what it can. It was
designed to operate as a modular, simple, compact, and easy-to-use embedded
relational database whose code base is within the reach of the programmers
using it. And in many respects it can do what many other databases cannot, such
as run in embedded environments where actual power consumption is a limiting
factor. "
--
Is it really a good idea to network a data base that relies on the OS file 
systems like this? Is it ever going to be safe enough?

David M X Green


|||"Alex Roston" (2007-02-02 20:05) wrote: |||>>>

Scott Hess wrote:

On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:

[EMAIL PROTECTED] wrote:
> The problem is, not many network filesystems work correctly.

I'm sure someone knows which versions of NFS have working file locking,
at least under Linux.


I doubt it is this easy.  You need to line up a bunch of things in the
right order, with the right versions of nfs, locking services, perhaps
the right kernel versions, the right config, etc, etc.

IMO the _real_ solution would be a package which you could use to try
to verify whether the system you have is actually delivering working
file locking.  Something like diskchecker (see
http://brad.livejournal.com/2116715.html).  The basic idea would be to
have a set of networked processes exercising the APIs and looking for
discrepencies.  Admittedly, passing such a test only gets you a
statistical assurance (maybe if you'd run the test for ten more
minutes, or with another gig of data, it would have failed!), but
failing such a test is a sure sign of a problem.

-scott
That's a really useful idea, not only for itself, but also because it 
might lead to debugging some of the network issues, and allowing the 
developers to build a database of stuff that works: "Use Samba version 
foo, with patch bar, and avoid the Fooberry 6 network cards." Or whatever.


My suspicion, in the earlier case with Windows and Linux clients is that 
Windows didn't handle the locking correctly, and that would be worth 
proving/disproving too.


An alternate approach is to use something a little more like a standard 
client-server model, where there's a "server" program which intervenes 
between (possibly multiple) workstations and the database itself. The 
"server" would queue requests to the database, make sure that no more 
than one write request at a time went to the database, and certify that 
writes have been properly made.


The problem with this approach is that it eats quite heavily into 
SQLite's speed advantage, but if you've already put thousands of hours 
into developing your system, it might be a worthwhile hack.


Alex

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 








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

Re: [sqlite] Appropriate uses for SQLite

[Alex Roston]

Scott Hess wrote:

On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:

[EMAIL PROTECTED] wrote:
> The problem is, not many network filesystems work correctly.

I'm sure someone knows which versions of NFS have working file locking,
at least under Linux.


I doubt it is this easy.  You need to line up a bunch of things in the
right order, with the right versions of nfs, locking services, perhaps
the right kernel versions, the right config, etc, etc.

IMO the _real_ solution would be a package which you could use to try
to verify whether the system you have is actually delivering working
file locking.  Something like diskchecker (see
http://brad.livejournal.com/2116715.html).  The basic idea would be to
have a set of networked processes exercising the APIs and looking for
discrepencies.  Admittedly, passing such a test only gets you a
statistical assurance (maybe if you'd run the test for ten more
minutes, or with another gig of data, it would have failed!), but
failing such a test is a sure sign of a problem.

-scott
That's a really useful idea, not only for itself, but also because it 
might lead to debugging some of the network issues, and allowing the 
developers to build a database of stuff that works: "Use Samba version 
foo, with patch bar, and avoid the Fooberry 6 network cards." Or whatever.


My suspicion, in the earlier case with Windows and Linux clients is that 
Windows didn't handle the locking correctly, and that would be worth 
proving/disproving too.


An alternate approach is to use something a little more like a standard 
client-server model, where there's a "server" program which intervenes 
between (possibly multiple) workstations and the database itself. The 
"server" would queue requests to the database, make sure that no more 
than one write request at a time went to the database, and certify that 
writes have been properly made.


The problem with this approach is that it eats quite heavily into 
SQLite's speed advantage, but if you've already put thousands of hours 
into developing your system, it might be a worthwhile hack.


Alex

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

Re: [sqlite] Appropriate uses for SQLite

[Scott Hess]

On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:

[EMAIL PROTECTED] wrote:
> The problem is, not many network filesystems work correctly.

I'm sure someone knows which versions of NFS have working file locking,
at least under Linux.


I doubt it is this easy.  You need to line up a bunch of things in the
right order, with the right versions of nfs, locking services, perhaps
the right kernel versions, the right config, etc, etc.

IMO the _real_ solution would be a package which you could use to try
to verify whether the system you have is actually delivering working
file locking.  Something like diskchecker (see
http://brad.livejournal.com/2116715.html).  The basic idea would be to
have a set of networked processes exercising the APIs and looking for
discrepencies.  Admittedly, passing such a test only gets you a
statistical assurance (maybe if you'd run the test for ten more
minutes, or with another gig of data, it would have failed!), but
failing such a test is a sure sign of a problem.

-scott

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

Re: [sqlite] Appropriate uses for SQLite

[Clark Christensen]

FWIW, I'm not convinced Samba has locking working correctly.  Using a very 
recent Samba version, I managed to corrupt a SQLite database last fall by (I 
think) doing simultaneous writes from the Linux host box, and my WinXP client 
box (via a SMB drive map).  I'm guessing the XP writes started first.  It seems 
unlikely it would have happened had the Linux host started first.

Maybe it was something else, but given the explanations I've seen of what's 
known to corrupt a SQLite DB, it seems pretty likely it was a locking issue 
with a network file system.

Fortunately, it was early in the dev process on that project, and there wasn't 
much data in the DB, and I was able to recover it with a text editor.

That I didn't have a copy of the data outside the DB points to a weakness in my 
development habits.  Gotta work on that.

 -Clark

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, February 2, 2007 7:49:55 AM
Subject: Re: [sqlite] Appropriate uses for SQLite

[EMAIL PROTECTED] wrote:
> The problem is, not many network filesystems work correctly.  
Hi All,

If my understanding of this is correct, SQLite only requires that the 
network file system has; reliable data transport and working file 
locking. This has primarily been an issue on older implementations of 
NFS where the file locking does not work correctly. I believe that SMB 
(windows shares and Samba) do work correctly. I also seem to remember 
someone saying that current versions of NFS work correctly. It seems 
like overkill to say SQLite won't work an all network file systems, when 
in fact it will work on some, perhaps even most.

I appreciate that there are still network latency issues that will 
impact performance, but that is a separate issue. Many users could 
accept the lower performance as long as they don't have to worry about 
corruption.

I'm sure someone knows which versions of NFS have working file locking, 
at least under Linux. Perhaps we can collect this information about 
network file systems that are known to work and those that are known to 
have problems and post them on the wiki. Right now this  issue seems to 
be handled more as folklore than science.

So, what works and what doesn't? Let me know.

Dennis Cote


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





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

Re: [sqlite] Appropriate uses for SQLite

[Alex Roston]

Dennis Cote wrote:

[EMAIL PROTECTED] wrote:
The problem is, not many network filesystems work correctly.  

Hi All,

If my understanding of this is correct, SQLite only requires that the 
network file system has; reliable data transport and working file 
locking. This has primarily been an issue on older implementations of 
NFS where the file locking does not work correctly. I believe that SMB 
(windows shares and Samba) do work correctly. I also seem to remember 
someone saying that current versions of NFS work correctly. It seems 
like overkill to say SQLite won't work an all network file systems, 
when in fact it will work on some, perhaps even most.


I appreciate that there are still network latency issues that will 
impact performance, but that is a separate issue. Many users could 
accept the lower performance as long as they don't have to worry about 
corruption.


I'm sure someone knows which versions of NFS have working file 
locking, at least under Linux. Perhaps we can collect this information 
about network file systems that are known to work and those that are 
known to have problems and post them on the wiki. Right now this  
issue seems to be handled more as folklore than science.


So, what works and what doesn't? Let me know.

Dennis Cote
I don't have much more to say on the subject than Dennis does, but I do 
want to second his idea of getting accurate data and posting it 
someplace.  It's a very good idea.


Alex

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

Re: [sqlite] Appropriate uses for SQLite

[Dennis Cote]

[EMAIL PROTECTED] wrote:
The problem is, not many network filesystems work correctly.  

Hi All,

If my understanding of this is correct, SQLite only requires that the 
network file system has; reliable data transport and working file 
locking. This has primarily been an issue on older implementations of 
NFS where the file locking does not work correctly. I believe that SMB 
(windows shares and Samba) do work correctly. I also seem to remember 
someone saying that current versions of NFS work correctly. It seems 
like overkill to say SQLite won't work an all network file systems, when 
in fact it will work on some, perhaps even most.


I appreciate that there are still network latency issues that will 
impact performance, but that is a separate issue. Many users could 
accept the lower performance as long as they don't have to worry about 
corruption.


I'm sure someone knows which versions of NFS have working file locking, 
at least under Linux. Perhaps we can collect this information about 
network file systems that are known to work and those that are known to 
have problems and post them on the wiki. Right now this  issue seems to 
be handled more as folklore than science.


So, what works and what doesn't? Let me know.

Dennis Cote


-
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] 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\)]

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

[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 - 

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

[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 it

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

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
>>

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% o

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

[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]

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]
-

[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]
-