[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 4 May 2015, at 8:51pm, Scott Doctor  wrote:

> The issue of locking a file during concurrent access seems to be a major 
> issue, mostly due to the schizophrenic abilities of network file systems. Has 
> the SQLite development team considered embedding its own file locking system 
> logic, such as creating its own lock file to prevent damage from concurrent 
> write access, that does not rely on the network file system?

Read this:



But it's worth pointing out that all these operations depend on the underlying 
network file system working correctly.  It's not possible for SQLite to do 
locking properly if the operating system or storage medium is lying to it.

Simon.

[sqlite] Does vacuum command improves the sqlite3 write/store transactions

[Simon Slavin]

On 4 May 2015, at 9:01pm, Mayank Kumar (mayankum)  wrote:

> I am thinking about measuring the performance of sqlite3 write transactions 
> after lot of delete transactions have been performance but vacuum has not 
> been performed versus when vacuum is performed. Wanted to get some ideas here 
> on what people think theoretically should happen. I know it reclaims free 
> pages, but would this give a dramatic increase in write/update transactions ?

Under some circumstances VACUUM can slow things down instead of speed them up.  
One of the more 'expensive' operations for disk is to extend an existing file 
to take up more space.  The file system has to locate unused sectors, add them 
to the end of the file, and remove them from the free list.  It is far more 
efficient to let SQLite locate unused pages which are already allocated to its 
file.  So VACUUM freeing up 100 sectors followed by lots of INSERTS occupying 
100 new sectors wastes time.

The usual strategy is that you might want to use VACUUM before lots of SELECTs 
but not before lots of operations which change the file.  However, because of 
the numerous different subsystems involved (storage hardware, driver, 
bandwidth, CPU used, OS, other things being done at the same time) when VACUUM 
saves or wastes time for you depends on precise details of your setup.

Generally speaking you shouldn't need to worry about VACUUM unless disk space 
is important to you or you have a need to have your program run as fast as 
possible for some specific operation.  Numerous SQLite databases are in use for 
years without ever once having been VACUUMed.

Simon.

[sqlite] SQLite 3.8.10 enters testing

[Simon Slavin]

On 4 May 2015, at 8:23pm, Richard Hipp  wrote:

> A list of changes (still being revised and updated) is at
> (https://www.sqlite.org/draft/releaselog/3_8_10.html).

"Because of its past success, AFL became a standard part of the testing 
strategy for SQLite beginning with version 3.8.10. There is at least one 
instance of AFL running against SQLite continuously, 24/7/365, trying new 
randomly mutated inputs against SQLite at a rate of a few hundred to a few 
thousand per second. Billions of inputs have been tried, but AFL's 
instrumentation has narrowed them down to less than 20,000 test cases that 
cover all distinct behaviors. Newly discovered test cases are periodically 
captured and added to the TCL test suite."

Heh.  Mister Zalewski can be proud.

Simon.

[sqlite] Does vacuum command improves the sqlite3 write/store transactions

[Mayank Kumar (mayankum)]

Hi All
I am thinking about measuring the performance of sqlite3 write transactions 
after lot of delete transactions have been performance but vacuum has not been 
performed versus when vacuum is performed. Wanted to get some ideas here on 
what people think theoretically should happen. I know it reclaims free pages, 
but would this give a dramatic increase in write/update transactions ?


-mayank

[sqlite] Malformed Database

[Simon Slavin]

On 4 May 2015, at 6:43pm, Brandon Frye  wrote:

> Thanks so much for the response. Can you suggest the specific pragmas -
> although I trust your insight that they may be more harmful than helpful -
> I would like to read more about them (if for nothing else to make sure I
> have not used them trying to fix this!).

The ones which seem relevant are

PRAGMA cache_spill
PRAGMA checkpoint_fullfsync
PRAGMA fullfsync
PRAGMA synchronous
PRAGMA wal_autocheckpoint
PRAGMA wal_checkpoint

By all means experiment with them and see if they help.  I suspect that 
settings which cause more frequent updating of the database will just cause 
your crash to occur sooner,  but since the bug you seem to have discovered in 
iOS is not understood, perhaps one will avoid you triggering it.

Simon.

[sqlite] System.Data.SQLite, Need alternate way to get field length

[Drago, William @ CSG - NARDA-MITEQ]

Thanks for the reply, Clemens. The problem is now solved.


--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Clemens Ladisch
> Sent: Saturday, May 02, 2015 3:11 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] System.Data.SQLite, Need alternate way to get
> field length
>
> William Drago wrote:
> > I am trying to determine the number of bytes in a blob. According to
> the help file under SQLiteDataReader.GetBytes Method in the Remarks
> section:
> >
> > "To determine the number of bytes in the column, pass a null value
> for the buffer. The total length will be returned."
> >
> > I'm working in VEE and VEE doesn't have a null keyword, so I tried
> System.DBNull.Value and I get an error (signatures don't match).
>
> If VEE (whatever that is) does not allow you to specify something that
> ends up as a null reference, the you cannot use this mechanism.
>
> > So, are there any other ways to get the number of bytes in a blob?
>
> SELECT length(MyLittleBlob) FROM ...
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Daniel Sjödin]

Hi,

There is also a VBA wrapper that I've tested. Maybe the usage is a bit
narrow but it shows how broad sqlite is.

https://sqliteforexcel.codeplex.com/

Regards,
Daniel
Hello,
  Thanks for the additions to the awesome-sqlite [1] list.  I added
the ODBC and JDBC drivers to a new middleware section, and the R
driver to a new language binding section, and the R data frame package
to a misc section.

  Note: For now commercial only tools (e.g. SQLite Analyzer) will NOT
get include - sorry. If you want to get it included, buy a sponsored
link or a beer for everyone on the mailing list! Just kidding ;-)

  Cheers.

[1] https://github.com/planetopendata/awesome-sqlite
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Malformed Database

[Simon Slavin]

On 4 May 2015, at 5:48pm, Brandon Frye  wrote:

> I have assumed this is an iOS specific issue. This app has been in
> productions for 3 years now, with the lovely iOS 8.x release - we have
> started to see this problem. We have a couple thousand users, and it is
> only with iOS 8.x release that this problem has started to show itself. We
> filed a bug with Apple, to which they answered 'This is a duplicate of bug
> XXX'. And they will not allow us to view status, or progress, or
> ANYTHING to do with bug XXX (They are just so wonderful to work with).

Yeah, I've had that too.  And it annoyed me too.  You should eventually get 
told when that bug gets fixed, but that doesn't help you now.

> [snip] is there anything I can do on
> the SQLite side that would minimize chances of corruption? I read that WAL
> mode is 'better' and have switched to that. But are there any other things
> I can do to minimize chances of this? Would looking at the db file be
> helpful in any way?

There are some PRAGMAs which will force more frequent writing of the database 
but I suspect that rather than helping your situation they will really only 
give the application more chances of crashing.

Possibly ... just possibly ... keep a known-uncorrupted copy of the database 
file as a backup.  If your corruption can be detected easily from the 
'Malformed' report or by using "PRAGMA quick_check(1)" then dump the new 
version of the database and work on a copy of the backup one.

Simon.

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Gerald Bauer]

Hello,
  Thanks for the additions to the awesome-sqlite [1] list.  I added
the ODBC and JDBC drivers to a new middleware section, and the R
driver to a new language binding section, and the R data frame package
to a misc section.

  Note: For now commercial only tools (e.g. SQLite Analyzer) will NOT
get include - sorry. If you want to get it included, buy a sponsored
link or a beer for everyone on the mailing list! Just kidding ;-)

  Cheers.

[1] https://github.com/planetopendata/awesome-sqlite

[sqlite] What software is deployed more than SQLite?

[Tim Streater]

On 04 May 2015 at 17:31, jungle Boogie  wrote: 

> On 4 May 2015 at 07:58, Warren Young  wrote:
>> On May 3, 2015, at 6:50 PM, jungle Boogie  wrote:
>>>
>>> On 3 May 2015 at 11:18, Richard Hipp  wrote:
 Any input you can provide is appreciated!
>>>
>>> Congratulations to you and your team on SQLite's achievement and I
>>> wish you continued success.
>>>
>>> "Most Widely Deployed And Used Database Engine"
>>>
>>> I don't think the A in and needs capitalization.
>>
>> Both are correct.  The only incorrect thing to do is to mix styles on titles
>> within a single work.
>>  
>> http://www.quickanddirtytips.com/education/grammar/title-capitalization-rules
>
> This says small words don't need the capitalization...
> http://grammar.yourdictionary.com/capitalization/rules-for-capitalization-in-t
> itles.html

In fact none of the words need capitalisation except the first. Otherwise it 
looks like a 1930's newspaper headline.

--
Cheers  --  Tim

[sqlite] Does vacuum command improves the sqlite3 write/store transactions

[Richard Hipp]

On 5/4/15, Richard Hipp  wrote:
> On 5/4/15, Mayank Kumar (mayankum)  wrote:
>> Hi All
>> I am thinking about measuring the performance of sqlite3 write
>> transactions
>> after lot of delete transactions have been performance but vacuum has not
>> been performed versus when vacuum is performed. Wanted to get some ideas
>> here on what people think theoretically should happen. I know it reclaims
>> free pages, but would this give a dramatic increase in write/update
>> transactions ?
>>
>
> Many filesystems operate faster when disk access is sequential.

I failed to mention that the VACUUM command reorganizes the content of
the database so that it is (mostly) sequential in the file.

>
> See the last paragraph at https://www.sqlite.org/draft/dbstat.html for
> information on measuring how sequential your database file actually
> is.
>
> We would very much like to hear from you if you make any actual
> measurements of performance improvements after running VACUUM.  So
> much depends on the OS and the underlying hardware that it is
> difficult to make general statements.  But case studies are still
> useful.
> --
> D. Richard Hipp
> drh at sqlite.org
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Does vacuum command improves the sqlite3 write/store transactions

[Richard Hipp]

On 5/4/15, Mayank Kumar (mayankum)  wrote:
> Hi All
> I am thinking about measuring the performance of sqlite3 write transactions
> after lot of delete transactions have been performance but vacuum has not
> been performed versus when vacuum is performed. Wanted to get some ideas
> here on what people think theoretically should happen. I know it reclaims
> free pages, but would this give a dramatic increase in write/update
> transactions ?
>

Many filesystems operate faster when disk access is sequential.

See the last paragraph at https://www.sqlite.org/draft/dbstat.html for
information on measuring how sequential your database file actually
is.

We would very much like to hear from you if you make any actual
measurements of performance improvements after running VACUUM.  So
much depends on the OS and the underlying hardware that it is
difficult to make general statements.  But case studies are still
useful.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SQLite 3.8.10 enters testing

[Richard Hipp]

On 5/4/15, Simon Slavin  wrote:
>
> Heh.  Mister Zalewski can be proud.
>

See his "trophy case" at http://lcamtuf.coredump.cx/afl/
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Malformed Database

[Simon Slavin]

On 4 May 2015, at 4:09pm, Brandon  wrote:

> We are using SQLite in an iOS app. We are not using the 'built-in' SQLite, we 
> are compiling from source. We have been battling an issue where the database 
> becomes 'malformed'. This occurs when iOS completely crashes (blue screen, 
> flickering, then restart). It's a pretty hard core crash. As a result - the 
> db often becomes malformed. My question is - is there anything I can do to 
> help prevent this? I have enabled 'WAL' mode. At this point, we are at a 
> loss. I am just not sure what our next step is.

You've hit a problem with iOS.  iOS is a phone OS.  It's not designed to crash, 
because the rules about phones say that they must always be available for 
calling the emergency number, so they mustn't crash.  Given the no-crash rule 
for real-world performance and released Apps, it doesn't matter what happens if 
your App can crash the OS, because if it can crash the OS Apple will never 
approve it.

So a phone OS often does things that computer OS would never do, like 
aggressive caching which doesn't bother to update main storage until the App 
backgrounds or quits.  And this can lead to the type of corruption you're 
seeing.  And since SQLite is doing everything it can to write to storage 
correctly, your fix cannot be done inside something SQLite is doing.

So your 'treatment' for this problem is not to do with making SQLite more 
trustworthy.  It's to figure out how you're crashing iOS and stop doing it.  
You're going to have to do that sooner or later anyway, because your App will 
never get through Apple's approval process for App distribution if it crashes 
iOS.

If you think one of your SQLite calls is causing the crash we can help you.  Do 
you have any idea what you're calling and under what circumstances it's 
happening ?  If the thing crashing iOS is not to do with SQLite then you might 
be better off asking elsewhere.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Richard Hipp]

On 5/4/15, Scott Doctor  wrote:
>
> The issue of locking a file during concurrent access seems to be
> a major issue, mostly due to the schizophrenic abilities of
> network file systems. Has the SQLite development team considered
> embedding its own file locking system logic, such as creating
> its own lock file to prevent damage from concurrent write
> access, that does not rely on the network file system? Seems
> that if the issue is due to the design of the network file
> system, which we have no control, then embedding some internal
> logic system that handles self-flagging of a lock condition
> seems a reasonable course to pursue.
>

The "unix-dotfile" VFS (which is built into standard SQLite builds on
Unix) does exactly that.

Problem is that if your application crashes, it might leave behind
stale locks that need to be cleaned up manually.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] build failure on trunk

[Richard Hipp]

On 5/4/15, jungle Boogie  wrote:
> Hello All,
>
> I don't think this is severe but just bringing it up as a new release
> is imminent

Try running "make clean; make"  Let us know if that fails to clear the problem.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] The type of the key field 'xxx' is expected to be 'System.Int64', but the value provided is actually of type 'System.Int32'

[Jingfei Hu]

Hi team,

I have a sqlite3 database file. I can read it in Debug mode in VS but not in
Release mode. It gives me following error 



The type of the key field 'xxx' is expected to be 'System.Int64', but the
value provided is actually of type 'System.Int32'



I don't know what's behind it or some misconfigurations exist. Any thoughts?



Thanks,

-J

[sqlite] SQLite 3.8.10 enters testing

[Richard Hipp]

At the request of a notable downstream, we are pushing the release of
SQLite 3.8.10 forward by a month or so.  We will start a
release-testing cycle later today.  A checklist is now online at
https://www.sqlite.org/checklists/3081000/index - when this checklist
goes all green, we will cut the release.  (Previous checklists can be
seen at https://www.sqlite.org/checklists/index.html)

Source code snapshots of the first beta can be found at
(https://www.sqlite.org/download.html).  We will probably be adding
some additional beta products (ex: DLLS) for testing over the next few
hours.

A list of changes (still being revised and updated) is at
(https://www.sqlite.org/draft/releaselog/3_8_10.html).

The 3.8.10 release consists mostly of performance enhancements and
fixes for arcane bugs found by the AFL fuzzer.  The trunk has been
stable for weeks.  We expect this to be a very trouble-free release.
If you have any concerns or questions about the current state of the
code, please bring them up as soon as possible.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Gerald Bauer]

Hello,
  Thanks I've updated the awesome-sqlite list [1] and now includes a
new section on SpatiaLite and more admin tools. Cheers.

[1] https://github.com/planetopendata/awesome-sqlite

[sqlite] sqlite3 and Unicode

[Richard Hipp]

On 5/4/15, Peter Haworth  wrote:
> When using the .dump command with .output to a filename, what encoding does
> sqlite3 for the file? Same as the database encoding?  Is it possible to
> change whatever encoding is used?
>
> Similarly, when using the .import command, does sqlite3 assume the input
> file is the same encoding as the database?
>

I think it assumes UTF8 in both cases.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Drago, William @ CSG - NARDA-MITEQ]

Here's very good JDBC driver for SQLite:

https://bitbucket.org/xerial/sqlite-jdbc

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jim Callahan
> Sent: Monday, May 04, 2015 10:14 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Awesome SQLite List - Collection of SQLite
> Goodies Started - Contributions Welcome
>
> ?Readers on GitHub might also be interested in ODBC and JDBC drivers for
> SQLite:
>
> ODBC
> http://www.ch-werner.de/sqliteodbc/
>
> JDBC
> http://www.ch-werner.de/javasqlite/index.html?
>
> Also, a few weeks ago there was a discussion on this list of how to
> call SQLite from C# and an add-in for Microsoft Visual Studio.
>
> Also there is a (very high quality!)  RSQLite interface from the R
> statistical language to SQLite.
>
> RSQLite (on CRAN)
> http://cran.r-project.org/web/packages/RSQLite/index.html
> as well as
>
> sqldf (on CRAN)
> http://cran.r-project.org/web/packages/sqldf/sqldf.pdf
>
> sqldf (on GitHub)
> https://github.com/ggrothendieck/sqldf
>
>
> As I understand there are interfaces from a variety of other languages
> including Python and PHP.
>
> Jim Callahan
> Orlando, FL
>
>
> On Mon, May 4, 2015 at 8:55 AM, Gerald Bauer 
> wrote:
>
> > Hello,
> >   Thanks I've updated the awesome-sqlite list [1] and now includes a
> > new section on SpatiaLite and more admin tools. Cheers.
> >
> > [1] https://github.com/planetopendata/awesome-sqlite
> > ___
> > 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
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] build failure on trunk

[jungle Boogie]

On 4 May 2015 at 13:12, Richard Hipp  wrote:
> On 5/4/15, jungle Boogie  wrote:
>> Hello All,
>>
>> I don't think this is severe but just bringing it up as a new release
>> is imminent
>
> Try running "make clean; make"  Let us know if that fails to clear the 
> problem.

It does fail to clear it but running a subsequent make progresses through.

> --
> D. Richard Hipp
> drh at sqlite.org

-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

The issue of locking a file during concurrent access seems to be 
a major issue, mostly due to the schizophrenic abilities of 
network file systems. Has the SQLite development team considered 
embedding its own file locking system logic, such as creating 
its own lock file to prevent damage from concurrent write 
access, that does not rely on the network file system? Seems 
that if the issue is due to the design of the network file 
system, which we have no control, then embedding some internal 
logic system that handles self-flagging of a lock condition 
seems a reasonable course to pursue.



Scott Doctor
scott at scottdoctor.com
--

[sqlite] build failure on trunk

[jungle Boogie]

Hello All,

I don't think this is severe but just bringing it up as a new release
is imminent
...
http://hastebin.com/dajofuzetu.md



-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] What software is deployed more than SQLite?

[Simon Slavin]

On 4 May 2015, at 8:26am, Clemens Ladisch  wrote:

> Yahoo! Mail wrote:
>> How about curl? [snip]
> 
> I would estimate that there are less networked devices without SQLite
> than there are devices using SQLite but without network access.

Also, anything sophisticated enough to need curl is probably going to want to 
cache the results somewhere, or store a history of URLs.  And immediately we're 
back to needing a DBMS of some kind.

Simon.

[sqlite] sqlite3 and Unicode

[Peter Haworth]

When using the .dump command with .output to a filename, what encoding does
sqlite3 for the file? Same as the database encoding?  Is it possible to
change whatever encoding is used?

Similarly, when using the .import command, does sqlite3 assume the input
file is the same encoding as the database?

Thanks,

Pete
lcSQL Software 
Home of lcStackBrowser  and
SQLiteAdmin 

[sqlite] Malformed Database

[Richard Hipp]

On 5/4/15, Brandon  wrote:
> Hi There,
>
> I have never posted to the mailing list - so if I am doing something
> 'wrong' - my sincere apologies.
>
> We are using SQLite in an iOS app. We are not using the 'built-in'
> SQLite, we are compiling from source. We have been battling an issue
> where the database becomes 'malformed'. This occurs when iOS completely
> crashes (blue screen, flickering, then restart). It's a pretty hard core
> crash. As a result - the db often becomes malformed. My question is - is
> there anything I can do to help prevent this? I have enabled 'WAL' mode.
> At this point, we are at a loss. I am just not sure what our next step is.
>
> I can send a db and makefile for review if helpful (or a link). Any
> feedback would be greatly appreciated.
>

Can we assume that you have read https://www.sqlite.org/howtocorrupt.html
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] What software is deployed more than SQLite?

[Scott Doctor]

You could just use all caps and scream the headline, then you do not 
need to worry what should be capitalized.

-
Scott Doctor
scott at scottdoctor.com
-

On 5/4/2015 10:01 AM, Tim Streater wrote:
> On 04 May 2015 at 17:31, jungle Boogie  wrote:
>
>> On 4 May 2015 at 07:58, Warren Young  wrote:
>>> On May 3, 2015, at 6:50 PM, jungle Boogie  
>>> wrote:
 On 3 May 2015 at 11:18, Richard Hipp  wrote:
> Any input you can provide is appreciated!
 Congratulations to you and your team on SQLite's achievement and I
 wish you continued success.

 "Most Widely Deployed And Used Database Engine"

 I don't think the A in and needs capitalization.
>>> Both are correct.  The only incorrect thing to do is to mix styles on titles
>>> within a single work.
>>>   
>>> http://www.quickanddirtytips.com/education/grammar/title-capitalization-rules
>> This says small words don't need the capitalization...
>> http://grammar.yourdictionary.com/capitalization/rules-for-capitalization-in-t
>> itles.html
> In fact none of the words need capitalisation except the first. Otherwise it 
> looks like a 1930's newspaper headline.
>
> --
> Cheers  --  Tim
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Scott Doctor]

Nothing wrong with a commercial tool. I use, and paid for, many of them. 
After trying everything I could find for a particular problem, open 
source, freeware, trial versions, I chose a commercial program as it was 
more detailed and did want I needed it to do. Their program solved an 
issue for me that the open source, free stuff did not. The day I can 
open source my rent, groceries, car repairs, is when everything else can 
be free.

-
Scott Doctor
scott at scottdoctor.com
-

On 5/4/2015 9:15 AM, Gerald Bauer wrote:
> Hello,
>Thanks for the additions to the awesome-sqlite [1] list.  I added
> the ODBC and JDBC drivers to a new middleware section, and the R
> driver to a new language binding section, and the R data frame package
> to a misc section.
>
>Note: For now commercial only tools (e.g. SQLite Analyzer) will NOT
> get include - sorry. If you want to get it included, buy a sponsored
> link or a beer for everyone on the mailing list! Just kidding ;-)
>
>Cheers.
>
> [1] https://github.com/planetopendata/awesome-sqlite
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] Malformed Database

[Brandon Frye]

Thanks so much for the response. Can you suggest the specific pragmas -
although I trust your insight that they may be more harmful than helpful -
I would like to read more about them (if for nothing else to make sure I
have not used them trying to fix this!).

Our current 'patch' for this was to build SQLite recovery in to our app.
The user can go through a recovery step that actually dumps out all the
changed/inserted records from their local copy of the db. Those changes are
dumped as a SQL script and emailed to us. Although this is not ideal - this
has been very helpful in recovering changes the user has made but have not
been able to be synced. From that point, the user downloads a fresh copy of
their database from our server (often 1-3+ GB).

Thanks again for the feedback

On Mon, May 4, 2015 at 10:21 AM, Simon Slavin  wrote:

>
> On 4 May 2015, at 5:48pm, Brandon Frye  wrote:
>
> > I have assumed this is an iOS specific issue. This app has been in
> > productions for 3 years now, with the lovely iOS 8.x release - we have
> > started to see this problem. We have a couple thousand users, and it is
> > only with iOS 8.x release that this problem has started to show itself.
> We
> > filed a bug with Apple, to which they answered 'This is a duplicate of
> bug
> > XXX'. And they will not allow us to view status, or progress, or
> > ANYTHING to do with bug XXX (They are just so wonderful to work
> with).
>
> Yeah, I've had that too.  And it annoyed me too.  You should eventually
> get told when that bug gets fixed, but that doesn't help you now.
>
> > [snip] is there anything I can do on
> > the SQLite side that would minimize chances of corruption? I read that
> WAL
> > mode is 'better' and have switched to that. But are there any other
> things
> > I can do to minimize chances of this? Would looking at the db file be
> > helpful in any way?
>
> There are some PRAGMAs which will force more frequent writing of the
> database but I suspect that rather than helping your situation they will
> really only give the application more chances of crashing.
>
> Possibly ... just possibly ... keep a known-uncorrupted copy of the
> database file as a backup.  If your corruption can be detected easily from
> the 'Malformed' report or by using "PRAGMA quick_check(1)" then dump the
> new version of the database and work on a copy of the backup one.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

-- 


*LEGAL DISCLAIMER:* The information in this email is confidential and may 
be legally privileged. It is intended solely for the addressee. Access to 
this email by anyone else is unauthorized. If you are not the intended 
recipient, any disclosure, copying, distribution or any action taken or 
omitted to be taken in reliance on it, is prohibited and may be unlawful.

[sqlite] What software is deployed more than SQLite?

[jungle Boogie]

On 4 May 2015 at 10:01, Tim Streater  wrote:
> On 04 May 2015 at 17:31, jungle Boogie  wrote:
>
>> On 4 May 2015 at 07:58, Warren Young  wrote:
>>> On May 3, 2015, at 6:50 PM, jungle Boogie  
>>> wrote:

 On 3 May 2015 at 11:18, Richard Hipp  wrote:
> Any input you can provide is appreciated!

 Congratulations to you and your team on SQLite's achievement and I
 wish you continued success.

 "Most Widely Deployed And Used Database Engine"

 I don't think the A in and needs capitalization.
>>>
>>> Both are correct.  The only incorrect thing to do is to mix styles on titles
>>> within a single work.
>>>
>>> http://www.quickanddirtytips.com/education/grammar/title-capitalization-rules
>>
>> This says small words don't need the capitalization...
>> http://grammar.yourdictionary.com/capitalization/rules-for-capitalization-in-t
>> itles.html
>
> In fact none of the words need capitalisation except the first. Otherwise it 
> looks like a 1930's newspaper headline.
>

I don't know if I'd go that far.

http://www.nytimes.com/

The articles there have the headlines capitalized.

but anyway, maybe we should get back to sqlite! ;)

> --
> Cheers  --  Tim
>


-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Jim Callahan]

?Readers on GitHub might also be interested in ODBC and JDBC drivers for
SQLite:

ODBC
http://www.ch-werner.de/sqliteodbc/

JDBC
http://www.ch-werner.de/javasqlite/index.html?

Also, a few weeks ago there was a discussion on this list of how to call
SQLite from C# and an add-in for Microsoft Visual Studio.

Also there is a (very high quality!)  RSQLite interface from the R
statistical language to SQLite.

RSQLite (on CRAN)
http://cran.r-project.org/web/packages/RSQLite/index.html
as well as

sqldf (on CRAN)
http://cran.r-project.org/web/packages/sqldf/sqldf.pdf

sqldf (on GitHub)
https://github.com/ggrothendieck/sqldf


As I understand there are interfaces from a variety of other languages
including Python and PHP.

Jim Callahan
Orlando, FL


On Mon, May 4, 2015 at 8:55 AM, Gerald Bauer  wrote:

> Hello,
>   Thanks I've updated the awesome-sqlite list [1] and now includes a
> new section on SpatiaLite and more admin tools. Cheers.
>
> [1] https://github.com/planetopendata/awesome-sqlite
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Malformed Database

[Brandon Frye]

I have assumed this is an iOS specific issue. This app has been in
productions for 3 years now, with the lovely iOS 8.x release - we have
started to see this problem. We have a couple thousand users, and it is
only with iOS 8.x release that this problem has started to show itself. We
filed a bug with Apple, to which they answered 'This is a duplicate of bug
XXX'. And they will not allow us to view status, or progress, or
ANYTHING to do with bug XXX (They are just so wonderful to work with).
So we know we are not the only people seeing this.

There is no one particular work flow through our software that exhibits
this problem. The only common thing is that when it does occur, it is
always during a write operation. Our users use our software all day long
and probably perform operations that write to the db thousands of times a
day. So it is probably safe to say that 99.999% of the time, we are not
seeing this. We are extremely impressed with SQLite and I am pretty much
100% convinced that this is an iOS specific bug. So my question here is -
given that this is (most likely) an iOS bug (and getting Apple to do
ANYTHING to help us is basically impossible), is there anything I can do on
the SQLite side that would minimize chances of corruption? I read that WAL
mode is 'better' and have switched to that. But are there any other things
I can do to minimize chances of this? Would looking at the db file be
helpful in any way?
Thanks in advance

On Mon, May 4, 2015 at 8:23 AM, Simon Slavin  wrote:

>
> On 4 May 2015, at 4:09pm, Brandon  wrote:
>
> > We are using SQLite in an iOS app. We are not using the 'built-in'
> SQLite, we are compiling from source. We have been battling an issue where
> the database becomes 'malformed'. This occurs when iOS completely crashes
> (blue screen, flickering, then restart). It's a pretty hard core crash. As
> a result - the db often becomes malformed. My question is - is there
> anything I can do to help prevent this? I have enabled 'WAL' mode. At this
> point, we are at a loss. I am just not sure what our next step is.
>
> You've hit a problem with iOS.  iOS is a phone OS.  It's not designed to
> crash, because the rules about phones say that they must always be
> available for calling the emergency number, so they mustn't crash.  Given
> the no-crash rule for real-world performance and released Apps, it doesn't
> matter what happens if your App can crash the OS, because if it can crash
> the OS Apple will never approve it.
>
> So a phone OS often does things that computer OS would never do, like
> aggressive caching which doesn't bother to update main storage until the
> App backgrounds or quits.  And this can lead to the type of corruption
> you're seeing.  And since SQLite is doing everything it can to write to
> storage correctly, your fix cannot be done inside something SQLite is doing.
>
> So your 'treatment' for this problem is not to do with making SQLite more
> trustworthy.  It's to figure out how you're crashing iOS and stop doing
> it.  You're going to have to do that sooner or later anyway, because your
> App will never get through Apple's approval process for App distribution if
> it crashes iOS.
>
> If you think one of your SQLite calls is causing the crash we can help
> you.  Do you have any idea what you're calling and under what circumstances
> it's happening ?  If the thing crashing iOS is not to do with SQLite then
> you might be better off asking elsewhere.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

-- 


*LEGAL DISCLAIMER:* The information in this email is confidential and may 
be legally privileged. It is intended solely for the addressee. Access to 
this email by anyone else is unauthorized. If you are not the intended 
recipient, any disclosure, copying, distribution or any action taken or 
omitted to be taken in reliance on it, is prohibited and may be unlawful.

[sqlite] Malformed Database

[Brandon Frye]

Yes sir - we have read that. It lead us down the path of changing to WAL
mode.

On Mon, May 4, 2015 at 8:14 AM, Richard Hipp  wrote:

> On 5/4/15, Brandon  wrote:
> > Hi There,
> >
> > I have never posted to the mailing list - so if I am doing something
> > 'wrong' - my sincere apologies.
> >
> > We are using SQLite in an iOS app. We are not using the 'built-in'
> > SQLite, we are compiling from source. We have been battling an issue
> > where the database becomes 'malformed'. This occurs when iOS completely
> > crashes (blue screen, flickering, then restart). It's a pretty hard core
> > crash. As a result - the db often becomes malformed. My question is - is
> > there anything I can do to help prevent this? I have enabled 'WAL' mode.
> > At this point, we are at a loss. I am just not sure what our next step
> is.
> >
> > I can send a db and makefile for review if helpful (or a link). Any
> > feedback would be greatly appreciated.
> >
>
> Can we assume that you have read https://www.sqlite.org/howtocorrupt.html
> --
> 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
>

-- 


*LEGAL DISCLAIMER:* The information in this email is confidential and may 
be legally privileged. It is intended solely for the addressee. Access to 
this email by anyone else is unauthorized. If you are not the intended 
recipient, any disclosure, copying, distribution or any action taken or 
omitted to be taken in reliance on it, is prohibited and may be unlawful.

[sqlite] What software is deployed more than SQLite?

[jungle Boogie]

On 4 May 2015 at 07:58, Warren Young  wrote:
> On May 3, 2015, at 6:50 PM, jungle Boogie  wrote:
>>
>> On 3 May 2015 at 11:18, Richard Hipp  wrote:
>>> Any input you can provide is appreciated!
>>
>> Congratulations to you and your team on SQLite's achievement and I
>> wish you continued success.
>>
>> "Most Widely Deployed And Used Database Engine"
>>
>> I don't think the A in and needs capitalization.
>
> Both are correct.  The only incorrect thing to do is to mix styles on titles 
> within a single work.
>
>   
> http://www.quickanddirtytips.com/education/grammar/title-capitalization-rules
>

This says small words don't need the capitalization...
http://grammar.yourdictionary.com/capitalization/rules-for-capitalization-in-titles.html

I'm just happy Richard isn't including a period at the end of the
title, like some people I know. ;)


> It?s kind of like C brace style, in that respect.

-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Steven M. McNeese]

Another admin tool is SQLite Analyzer by Kraslabs.com

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jim Callahan
Sent: Monday, May 4, 2015 9:14 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Awesome SQLite List - Collection of SQLite Goodies 
Started - Contributions Welcome

?Readers on GitHub might also be interested in ODBC and JDBC drivers for
SQLite:

ODBC
http://www.ch-werner.de/sqliteodbc/

JDBC
http://www.ch-werner.de/javasqlite/index.html?

Also, a few weeks ago there was a discussion on this list of how to call SQLite 
from C# and an add-in for Microsoft Visual Studio.

Also there is a (very high quality!)  RSQLite interface from the R statistical 
language to SQLite.

RSQLite (on CRAN)
http://cran.r-project.org/web/packages/RSQLite/index.html
as well as

sqldf (on CRAN)
http://cran.r-project.org/web/packages/sqldf/sqldf.pdf

sqldf (on GitHub)
https://github.com/ggrothendieck/sqldf


As I understand there are interfaces from a variety of other languages 
including Python and PHP.

Jim Callahan
Orlando, FL


On Mon, May 4, 2015 at 8:55 AM, Gerald Bauer  wrote:

> Hello,
>   Thanks I've updated the awesome-sqlite list [1] and now includes a 
> new section on SpatiaLite and more admin tools. Cheers.
>
> [1] https://github.com/planetopendata/awesome-sqlite
> ___
> 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


---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[sqlite] What software is deployed more than SQLite?

[Clemens Ladisch]

Yahoo! Mail wrote:
> How about curl? This is taken directly from the official website:
>
> " curl is used in command lines or scripts to transfer data. It is also
> used in cars, television sets, routers, printers, audio equipment, mobile
> phones, tablets, settop boxes, media players and is the internet transfer
> backbone for thousands of software applications totally affecting more
> than/one billion users/."

I would estimate that there are less networked devices without SQLite
than there are devices using SQLite but without network access.


Regards,
Clemens

[sqlite] What software is deployed more than SQLite?

[Warren Young]

On May 3, 2015, at 6:50 PM, jungle Boogie  wrote:
> 
> On 3 May 2015 at 11:18, Richard Hipp  wrote:
>> Any input you can provide is appreciated!
> 
> Congratulations to you and your team on SQLite's achievement and I
> wish you continued success.
> 
> "Most Widely Deployed And Used Database Engine"
> 
> I don't think the A in and needs capitalization.

Both are correct.  The only incorrect thing to do is to mix styles on titles 
within a single work.

  http://www.quickanddirtytips.com/education/grammar/title-capitalization-rules

It?s kind of like C brace style, in that respect.

[sqlite] Malformed Database

[Brandon]

Hi There,

I have never posted to the mailing list - so if I am doing something 
'wrong' - my sincere apologies.

We are using SQLite in an iOS app. We are not using the 'built-in' 
SQLite, we are compiling from source. We have been battling an issue 
where the database becomes 'malformed'. This occurs when iOS completely 
crashes (blue screen, flickering, then restart). It's a pretty hard core 
crash. As a result - the db often becomes malformed. My question is - is 
there anything I can do to help prevent this? I have enabled 'WAL' mode. 
At this point, we are at a loss. I am just not sure what our next step is.

I can send a db and makefile for review if helpful (or a link). Any 
feedback would be greatly appreciated.

Thanks in advance,
Brandon
SQLite enthusiast

-- 


*LEGAL DISCLAIMER:* The information in this email is confidential and may 
be legally privileged. It is intended solely for the addressee. Access to 
this email by anyone else is unauthorized. If you are not the intended 
recipient, any disclosure, copying, distribution or any action taken or 
omitted to be taken in reliance on it, is prohibited and may be unlawful.

[sqlite] Possible bug with locking/retying

[Hick Gunter]

The obvious solution would be to statically link beets to a properly compiled 
SQLite library instead of relying on everybody else to get it "right"

-Urspr?ngliche Nachricht-
Von: Simon Slavin [mailto:slavins at bigfraud.org]
Gesendet: Sonntag, 03. Mai 2015 02:18
An: General Discussion of SQLite Database
Betreff: [sqlite] Possible bug with locking/retying

In searching for something else I came across this:



I don't like the fact that it's undated.  For all I know this is about a three 
year old bug which has long since been patched.

I understand the description, but not what SQLite does internally, and I don't 
know whether it was ever reported to the SQLite dev team.  Would someone like 
to take a look ?

Simon.
___
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] Does column order matter for performance?

[Hick Gunter]

Actually it does. Sort of.

 The beginning of the record is the "manifest" (actual type of data list) area. 
This contains tokens representing the actual type/contents of the fields. 
Sticking with the example this will be 99 bytes containing the tokens "null", 
"zero", "one" and "integer" followed by one variable sized token "blob".

 The length of the manifest is stored in it's first field, giving the offset of 
the "payload" (data item list) which contains the 0-99 variable sized integers 
that are not NULL, 0 or 1, followed by the actual blob data. So the amount of 
work required to locate the blob data varies considerably.

-Urspr?ngliche Nachricht-
Von: Simon Slavin [mailto:slavins at bigfraud.org]
Gesendet: Freitag, 01. Mai 2015 11:32
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Does column order matter for performance?


On 1 May 2015, at 6:49am, Pol-Online  wrote:

> I?m curious: how much work are we talking about here? If I have 99 INTEGER 
> fields followed by a BLOB one, to access the BLOB field, does SQLite need to 
> compute the size of each previous INTEGER field first?

Given the way that SQLite uses different sized fields for different integers, 
yes.  Also, given that SQLite columns have affinity and not type, it has to 
calculate these sizes separately for each row retrieved.  It can't use the 
table definition and calculate one set of positions for all rows.

Fortunately, the lengths of all columns in a row can be calculated by looking 
at the serial types of all the columns, which are stored in a block at the 
beginning of the row data.  So the format is not

Type of column 1, data 1, type of column 2, data 2, type of column 3, data 3 ...

it is

Type of column 1, type of column 2, type of column 3 ..., data 1, data 2, data 
3 ...

so SQLite doesn't have to read through variable-sized data to work out where 
later columns are.

More info in places around section 2.1 of



Simon.
___
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] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 4 May 2015, at 1:30am, James K. Lowden  wrote:

> That is the way most remote filesystems are designed and implemented
> and documented.  Cf. http://www.ietf.org/rfc/rfc1813.txt:
> 
>   4.11 Caching policies
> 
>   The NFS version 3 protocol does not define a policy for
>   caching on the client or server. In particular, there is no
>   support for strict cache consistency between a client and
>   server, nor between different clients. See [Kazar] for a
>   discussion of the issues of cache synchronization and
>   mechanisms in several distributed file systems.
> 
> If you can find documentation for cache semantics for CIFS, I'd be
> interested to see it.  

In contrast to NFS both SMB and AFP are designed to support networks properly, 
with caches only on the computer which hosts the file and locking correctly 
implemented.  And SQLite, whether it's depending on the locking built into them 
or its own mutex, should work correctly.

The problem is that the network file system implementations don't perform 
according to the design.  I don't work at this level but as I understand it 
there are two main reasons: bugs and speed.  I'm going to ignore bugs here.

The speed problem is that properly supporting locking slows down operations a 
lot.  Instead of one operation you do lock-read-unlock which takes almost three 
times as long.  And that's assuming that the lock succeeds.  So a thing you 
don't do in a client demo is show them the system running single-user, because 
when they get their own multi-user copy of your system they're going to start 
screaming about how slow it is.

So manufacturers at all the drivers in the chain -- OS, network file system, 
disk file system -- tend to implement locking badly to make themselves look 
fast.  Failing to properly handle unlikely cases here and there to save the 
time taken to figure out whether they apply.  And SQLite is incredibly 
intensive on locking and file operations, so it quickly routes out such 
problems.

Simon.

[sqlite] Multiple instances of the same program accessing the same db file

[Stephen Chrzanowski]

TL;DR

Get SQLite away from networks entirely when you start throwing multiple
machines at the problem.  Your last paragraph is probably the best bet if
you're not willing to get into a real client/server setup, and yes, you're
going to run into aggravated users, but, then, which is more important to
deal with?  Aggravated users or functional data?  Training will help with
the aggravated users, as will making your application aware of the
situations is going to be used in and properly deal with the issues that
could come up.  Single user over a wire (Other than IDE, SATA, or SCSI
cables) is "OK", but I'd still not trust any NFS type of communication.  If
you start throwing multiple users with ANY sort of write possibilities,
you're begging for trouble, as you've read in your research.  Read access,
from what I'm told and have read in these very forums is "Alright to do",
but again, I'm still not a fan.

The long story;

At the end of the road you're traveling, you're going to reach the final
conclusion that if you want concurrent users to have access to the data,
AND you want to make sure the data stays valid and not corrupted because of
network file sharing issues, the easiest detour to your final destination
is that you're going to have to get into a real client/server setup and get
it out of your codes mind that a client application should act as a server
as well.

Whether it is with SQLite and you build (IE: Write) a 'server' to handle
the requests and your database stays on the local storage system where the
server is run, or, switch to another program entirely like MySQL.  The
other option is, if you're hooked on SQLite, is to normalize the data to be
worked with on each client into individual files, then when the client is
done with the work, use the SQLite backup routines to push the data back to
the server.  Then, if required, use your merge maintenance to collect all
the resources and drop it into one main data store.  I don't write many
client/server applications for the Win32 environment, but anything I've
written that is web accessible has ALWAYS had a database engine with the
physical files sitting on some drive that is DIRECTLY controlled by the OS
running the database engine.

For desktop apps using SQLite that might want data shared between machines,
I put it in my programs head that any data to be EXCHANGED is done via the
SQLite backup routines.  Modifications are ALWAYS done locally.  If needed,
have the app write a lock file that matches the database file you're
working on, that way any other clients coming to pick the data up look for
that lock file and can behave accordingly, as in, pull the data in as read
only, or, deny access entirely.  First come, first serve policy.  I got
fancy with one of my apps in that I opened up a TCP port in the high 32k
range.  The lock file contained the local IP address of the machine.  If
another client came along to look for the database in question and
encountered the lock file, it'd open up a port to the client and just see
if it still was awake.  Then MS started throwing firewalls around and that
became a fun job to get around.

SQLite is awesome in a server architecture, being used as the back end for
PHP, small to medium web servers, and all the such.  Even with hundreds to
thousands of users hitting the SQLite database files, the file is being
accessed by X number of applications on the local store, or one governing
OS, is handling the file manipulation.  The fact that it is one OS handling
the file manipulation, you'll find that on ANY DBMS (MS-SQL, MySQL,
Postgres, etc) typically are run from a local store.  There are reasons for
this, and you're running into that reason.  I've actually never heard of a
database engine running over any kind of network device, SAN included.

One method I started to develop years ago was a web service that would take
in SQL calls from a client and spit back a formatted table of results.
Problems obviously arose with raw byte data in text results, so, changed it
up so that the server would just generate a SQLite3 DB on the fly and give
that to the client to work with.  The perk on that was that I could get
table normalization with some work on additional parsing what the query
was.  The issue (Which would have been resolved with further development)
was record locking.  The challenge was to decide what records (For example,
consider Insurance Policies, Doctor Records, Personnel files all as
individual record types) were to be locked on the server while the client
dealt with the data, and how long to keep the data locked.

On Sun, May 3, 2015 at 11:20 PM, Scott Doctor  wrote:

>
> I googled file locking issues. Sheesh, The horror stories of programs like
> Quickbooks corrupting data files. Files getting locked when nobody is using
> them (A windows 7 bug that supposedly was fixed) and endless information
> about what not to do, or what does not work. Very little about what does
> work.
>
> My application is 

[sqlite] What software is deployed more than SQLite?

[Simon Slavin]

On 3 May 2015, at 7:18pm, Richard Hipp  wrote:

> https://www.sqlite.org/draft/mostdeployed.html
> 
> Any input you can provide is appreciated!

>   ? Every iPhone and iOS device

iPhone is an iOS device.  Better would be "Every iOS device" or "Every iPhone 
and iPad".

>   ? Every Mac

You might want "Every Mac and Apple TV" though maybe Apple TVs're too obscure 
to be worth mentioning.

>   ? Most PHP and Python installations

You might want to add in Apache and Ruby on Rails.  Or you might not.

I like the new softer version far better.

Simon.

[sqlite] What software is deployed more than SQLite?

[Yahoo! Mail]

How about curl? This is taken directly from the official website:

" curl is used in command lines or scripts to transfer data. It is also 
used in cars, television sets, routers, printers, audio equipment, 
mobile phones, tablets, settop boxes, media players and is the internet 
transfer backbone for thousands of software applications totally 
affecting more than/one billion users/."

On 05/03/2015 11:32 PM, Richard Hipp wrote:
> On 5/3/15, Richard Hipp  wrote:
>> I'm trying to update the "Most Deployed Database" page
>> (https://www.sqlite.org/mostdeployed.html) in the SQLite documentation
>> (which has not been touched in close to a decade)...
> I invite merciless criticism of the draft revision at
> (https://www.sqlite.org/draft/mostdeployed.html).  This is part of the
> SQLite "sales pitch", so I want SQLite to look good, but I don't want
> to overstate the case.