[sqlite] Getting the current value of busy_timeout

[Donald Shepherd]

Is there a way to get (not set) the current value of busy_timeout when
using an SQLite version older than 3.7.15 and the addition of "PRAGMA
busy_timeout;"?

http://www.sqlite.org/releaselog/3_7_15.html

[sqlite] Getting the current value of busy_timeout

[Richard Hipp]

On 2/19/15, Simon Slavin  wrote:
>
> On 20 Feb 2015, at 12:08am, Richard Hipp  wrote:
>
>> You can add a hack to the code.  But other than that, no.
>
> Given that so many PRAGMAs have a way to find out the current setting, could
> this be added without too much extra code ?

It has already been added.  The OP asked about SQLite before 3.7.15 (3
years ago) which was when the capability was added.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Getting the current value of busy_timeout

[Richard Hipp]

On 2/19/15, Donald Shepherd  wrote:
> Is there a way to get (not set) the current value of busy_timeout when
> using an SQLite version older than 3.7.15 and the addition of "PRAGMA
> busy_timeout;"?
>

You can add a hack to the code.  But other than that, no.
-- 
D. Richard Hipp
drh at sqlite.org

[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] 0 bytes in -wal file indicates successful checkpoint?

[Richard Hipp]

On 2/19/15, Jerry Krinock  wrote:
> Assertion:  If the size of the -wal file is 0 bytes, then this means that
> the associated database has been checkpointed and, if I am sure that no
> process has the database open, I can safely discard the -shm and -wal files,
> and pass only the main database file, to another user say, with no fear of
> data loss.
>
> Am I correct?
>

Don't unlink the -wal file while SQLite is still running.  A process
under unix has no way of knowing that the file has been unlinked and
will keep merrily using it, oblivious to the fact that it is gone.
This can lead to serious troubles.  Note that it is not possible to
delete the -wal file on Windows while it is in use, so this is not an
issue there.

But apart from that, Yes it is safe to send just the database file if
the WAL file is zero bytes in size.

If SQLite is running, the best way to make a copy of the file to move
to another machine is to use the backup API.  The shell will let you
do this from the command-line:

 sqlite3 running.db ".backup safe-to-copy.db"

Or you can invoke the API directly from your application.  Whatever is
convenient.  See https://www.sqlite.org/backup.html for additional
information.

You never need to preserve the -shm file (except of course while
SQLite is running - don't mess with files that SQLite is actively
using on posix!).  The -shm is a performance boosting cache and will
be automatically discarded and reconstructed from scratch the next
time SQLite boots up anyhow.  Only the -wal needs to be preserved
after a crash.

-- 
D. Richard Hipp
drh at sqlite.org

[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] 0 bytes in -wal file indicates successful checkpoint?

[Jerry Krinock]

Assertion:  If the size of the -wal file is 0 bytes, then this means that the 
associated database has been checkpointed and, if I am sure that no process has 
the database open, I can safely discard the -shm and -wal files, and pass only 
the main database file, to another user say, with no fear of data loss.

Am I correct?

Thank you,

Jerry

[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] bug report: SELECT fails with BUSY in WAL mode database with concurrent writer

[Joey Hess]

[ Not subscribed; please CC me. ]

The attached Testcase.hs is a haskell program using sqlite, that
demonstrates what I think may be a bug in WAL mode. Based on the
documentation, readers in WAL mode are supposed to not be blocked by
concurrent writers. However, this test case demonstrates that a SELECT
can fail with busy in a WAL mode database that is getting a large volume
of writes.

To build:

apt-get install haskell-platform
cabal update
cabal install persistent-sqlite persistent-template esqueleto IfElse
ghc --make Testcase

I've been building it on Debian unstable. Note that by default,
persisten-sqlite includes its own embedded copy of sqlite, which is rather
out of date. It can be modified to build with the system library.
I have reproduced the crash when the test case is linked to version 3.8.7.4;
I have not yet tried a newer version.

To run:

rm test.db* (if ran before)
Run one Testcase process, and wait for it to print the Migrating line.
Then immediately run a second Testcase process. One of the two processes
will quickly crash:

..Testcase: user error (SQLite3 returned ErrorBusy while attempting to perfor

While running, it outputs '.' every time it successfully changes the
database. It's expected that some write attempts fail, as there are multiple
concurrent writers; if a write fails, it prints '!' and ignores the failure.

The crash comes when a *read* fails. WAL documentation indicates that
writers should not block reads, but this test case seems to demonstrate
otherwise!

Also attached is a TestcaseReader.hs. This only does reads, no writes.
TestcaseReader can be run while Testcase is running, and will also
demonstrate the problem:

user error (SQLite3 returned ErrorBusy while attempting to perform prepare 
"SELECT \"fscked\".\"key\"\nFROM \"fscked\"\nWHERE \"fscked\".\"key\" = ?\n": 
database is locked)

finally succeeded after 1 retries
all 1..10 followup selects succeeded

The interest thing about this is that it shows that the failing
SELECT is always the first one made on a new database connection.
I've seen it need to retry 60+ times to get that first SELECT to
succeed, but once a SELECT does succeed, it seems it's past
the danger zone and the database can be used without problems.

-- 
see shy jo
-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 811 bytes
Desc: Digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150219/8a937849/attachment.pgp>

[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] Complex insert query to normalised database

[gunnar]

(And if you go for the one to many in between table, then you shouldn't 
add the recipe_category_id field to recipe-data and also not the foreign 
key).

An alternative is when you say that one recipe_data can belong to at 
most, say, 5 categories.
Then you can do without the extra table and add recipe_category_id1, 
recipe_category_id2, ... recipe_category_id5 fields to recipe_data. And 
5 foreign keys.



On 02/19/2015 10:08 AM, gunnar wrote:
> You should make the relation the other way around.
> Remove the foreign key  from category and add to recipe_data a field 
> recipe_category_id and add also to the recipe_data table a foreign key
>
> FOREIGN KEY(recipe_category_id) REFERENCES category(category_id)
>
>
> Then you have defined a 1 to many relationship (each recipe_data 
> belongs to at most one category and each category can belong to many 
> recipe_data's).
>
>
> If you want a many to many relationship (which you want from what I 
> read) then you need one extra table in between with two fields
>
> recipe_data_category
>   recipe_id
>   category_id
>   FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id)
>   FOREIGN KEY(category_id) REFERENCES category(category_id)
>
>
> something like that.
>
>
> Gunnar
>
>
>
>
>
>
>
> On 02/19/2015 09:26 AM, Flakheart wrote:
>> Here is something I don't understand. From my reading of foreign 
>> keys, it
>> points to the unique id of the table record that it references right?
>>
>> If I insert a recipe with a specific category and then a different 
>> recipe
>> that uses the same category, how then does this foreign key work without
>> storing duplicate categories in the category table?
>>
>> Then later on, I need a recipe to be a member of multiple categories. 
>> I have
>> no idea how this would work.
>>
>> I'm at sea here.
>>
>> CREATE TABLE recipe_data(
>>recipe_id  INTEGER PRIMARY KEY AUTOINCREMENT,
>>recipe_nameTEXT,
>>recipe_version TEXT,
>>recipe_lastupdate  TEXT,
>>recipe_favouriteINTEGER,
>>recipe_deleted  INTEGER,
>>recipe_descriptionTEXT,
>>recipe_notes   TEXT,
>>recipe_servingsINTEGER,
>>recipe_peparationtime   TEXT,
>>recipe_cookingtime   TEXT,
>>recipe_totaltime TEXT
>> );
>>
>> CREATE TABLE category(
>>category_id INTEGER PRIMARY KEY AUTOINCREMENT,
>>category_name TEXT,
>>recipe_id INTEGER,
>>FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id)
>> );
>>
>>
>>
>> -- 
>> View this message in context: 
>> http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590p80623.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>

[sqlite] Complex insert query to normalised database

[gunnar]

You should make the relation the other way around.
Remove the foreign key  from category and add to recipe_data a field 
recipe_category_id and add also to the recipe_data table a foreign key

FOREIGN KEY(recipe_category_id) REFERENCES category(category_id)


Then you have defined a 1 to many relationship (each recipe_data belongs 
to at most one category and each category can belong to many recipe_data's).


If you want a many to many relationship (which you want from what I 
read) then you need one extra table in between with two fields

recipe_data_category
   recipe_id
   category_id
   FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id)
   FOREIGN KEY(category_id) REFERENCES category(category_id)


something like that.


Gunnar







On 02/19/2015 09:26 AM, Flakheart wrote:
> Here is something I don't understand. From my reading of foreign keys, it
> points to the unique id of the table record that it references right?
>
> If I insert a recipe with a specific category and then a different recipe
> that uses the same category, how then does this foreign key work without
> storing duplicate categories in the category table?
>
> Then later on, I need a recipe to be a member of multiple categories. I have
> no idea how this would work.
>
> I'm at sea here.
>
> CREATE TABLE recipe_data(
>recipe_id  INTEGER PRIMARY KEY AUTOINCREMENT,
>recipe_name TEXT,
>recipe_version  TEXT,
>recipe_lastupdate  TEXT,
>recipe_favourite INTEGER,
>recipe_deleted   INTEGER,
>recipe_description  TEXT,
>recipe_notesTEXT,
>recipe_servings INTEGER,
>recipe_peparationtime   TEXT,
>recipe_cookingtime   TEXT,
>recipe_totaltimeTEXT
> );
>
> CREATE TABLE category(
>category_id INTEGER PRIMARY KEY AUTOINCREMENT,
>category_name   TEXT,
>recipe_id   INTEGER,
>FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id)
> );
>
>
>
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590p80623.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> 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]

> 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] Complex insert query to normalised database

[Simon Slavin]

Sorry, I accidentally included 'INTEGER' before 'REFERENCES'.  Should have been

CREATE TABLE category(
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
category_name   TEXT
);

CREATE TABLE recipes_in_categories(
r_i_c_idINTEGER PRIMARY KEY AUTOINCREMENT,
recipe_id   REFERENCES recipe_data(recipe_id),
category_id REFERENCES category(category_id)
);

Simon.

[sqlite] Complex insert query to normalised database

[Simon Slavin]

> On 19 Feb 2015, at 8:26am, Flakheart  wrote:
> 
> If I insert a recipe with a specific category and then a different recipe
> that uses the same category, how then does this foreign key work without
> storing duplicate categories in the category table?
> 
> Then later on, I need a recipe to be a member of multiple categories. I have
> no idea how this would work.
> 
> I'm at sea here.
> 
> CREATE TABLE recipe_data(
>  recipe_id  INTEGER PRIMARY KEY AUTOINCREMENT,
>  recipe_name   TEXT,
>  recipe_versionTEXT,
>  recipe_lastupdate  TEXT,
>  recipe_favourite   INTEGER,
>  recipe_deleted INTEGER,
>  recipe_descriptionTEXT,
>  recipe_notes  TEXT,
>  recipe_servings   INTEGER,
>  recipe_peparationtime   TEXT,
>  recipe_cookingtime   TEXT,
>  recipe_totaltime  TEXT
> );
> 
> CREATE TABLE category(
>  category_id   INTEGER PRIMARY KEY AUTOINCREMENT,
>  category_name TEXT,
>  recipe_id INTEGER,
>  FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id)
> );

I'll give you another structure which will allow each recipe to be filed under 
many categories, and uses FOREIGN KEYs to enforce correct data-handling.  I'm 
going to try to guess the conventions and style you're using but please excuse 
me if I get it wrong.

CREATE TABLE recipe_data exactly as above

CREATE TABLE category(
 category_id INTEGER PRIMARY KEY AUTOINCREMENT,
 category_name   TEXT
);

CREATE TABLE recipes_in_categories(
 r_i_c_idINTEGER PRIMARY KEY AUTOINCREMENT,
 recipe_id   INTEGER REFERENCES recipe_data(recipe_id),
 category_idINTEGER REFERENCES category(category_id)
);

Normally for the foreign keys to work you'd have to create indexes on the 
referenced fields.  But since an INTEGER PRIMARY KEY AUTOINCREMENT field is 
automatically indexed this isn't necessary.

Simon.

Simon.

[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 
> 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] Complex insert query to normalised database

[Flakheart]

"I'm going to try to guess the conventions and style you're using but please
excuse me if I get it wrong."

I would not dare to criticise what I do not understand. I am incredibly
grateful!

Lots of study to do. Not that I sleep much any more:):)



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590p80630.html
Sent from the SQLite mailing list archive at Nabble.com.

[sqlite] Complex insert query to normalised database

[Flakheart]

"FOREIGN KEY(recipe_category_id) REFERENCES category(category_id)"

Thank you gunnar. I don't understand it yet but will work hard at it. Once I
make up some dummy data to play with, it might get me a better idea of how
all this works.

One thing I have no lack of is recipes!

My ambition is to one day have a database of several million.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590p80629.html
Sent from the SQLite mailing list archive at Nabble.com.

[sqlite] Complex insert query to normalised database

[Flakheart]

Here is something I don't understand. From my reading of foreign keys, it
points to the unique id of the table record that it references right? 

If I insert a recipe with a specific category and then a different recipe
that uses the same category, how then does this foreign key work without
storing duplicate categories in the category table?

Then later on, I need a recipe to be a member of multiple categories. I have
no idea how this would work.

I'm at sea here.

CREATE TABLE recipe_data(
  recipe_id  INTEGER PRIMARY KEY AUTOINCREMENT,
  recipe_nameTEXT,
  recipe_version TEXT,
  recipe_lastupdate  TEXT,
  recipe_favouriteINTEGER,
  recipe_deleted  INTEGER,
  recipe_description TEXT,
  recipe_notes   TEXT,
  recipe_servingsINTEGER,
  recipe_peparationtime   TEXT,
  recipe_cookingtime   TEXT,
  recipe_totaltime   TEXT
);

CREATE TABLE category(
  category_idINTEGER PRIMARY KEY AUTOINCREMENT,
  category_name  TEXT,
  recipe_id  INTEGER,
  FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id)
);



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590p80623.html
Sent from the SQLite mailing list archive at Nabble.com.