date:20140210


On 11 Feb 2014, at 4:06am, James K. Lowden  wrote:

> ${HOME}/.sqlite/db would be my choice.  

Since the objective is not to let a naive user unexpectedly lose the data, it 
might seem a bad idea to put the file in a directory which is hidden from naive 
users.

Just another example of why constructing a default 'safe' directory is so 
difficult.

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread James K. Lowden

On Mon, 10 Feb 2014 10:23:57 -0500
Richard Hipp  wrote:

> Proposed Change To Address The Problem:

Thank you for addressing this.  I for one think you're getting a lot of
unhelpful advice.  A database application that loses data?  As a
feature? 

> (1) Detect double-click launch by looking at argc and argv.  

Why make this a special case?  If no database name is provided,
the behavior should be the same regardless of how launched or what OS.
Easier to explain; easier to understand.  

> (3) Announce the name of the "standard" database file in the banner.

Unnecessary IMO, see next.  

> (5) In what folder should the "standard" database file be created?

The folder, er, directory should be the user's home directory, located
by exactly the same logic that locates ~/.sqliterc. 

> (4) What should the name of the "standard" database file be?

${HOME}/.sqlite/db would be my choice.  

Let's consider the ramifications of these choices.  

Every time sqlite3 is launched without an explicit database name,
${HOME}/.sqlite/db is opened.  The naïve user never loses so much as a
row; whenever the application is opened, yesterday's data are still
there, safe and sound.  In keeping with the principle of "data
independence", the location is unimportant.  SQLite Just Works.  

The inquisitive user could RTFM.  Or, say, type ".help", followed by
".databases".  The distance from the command prompt to the required
information is both short and consistent with other uses of sqlite3.  

The default location is the most likely place on the disk to which the
user has write permissions.  By choosing a dotfile, the name is
normally hidden, yet easily discovered.  

It doesn't interfere with current use or pester the user with still
more text and prompts.  I would prefer to see sqlite3 move in the
direction of quietude.  Already 6 lines stand between command and
prompt:

$ sqlite3 | nl
-- Loading resources from /home/jklowden/.sqliterc

 1  SQLite version 3.7.13 2012-06-11 02:05:22
 2  Enter ".help" for instructions
 3  Enter SQL statements terminated with a ";"
 4  sqlite> 

Other thoughts: 

1.  To retain existing behavior, consider making 

attach database :memory: as main;

valid SQL in ~/.sqliterc.  As an interesting bonus, support for 

detach main 

would remove "main" as a special case.  

2.  Consider a -mem option for those situations when ${HOME} is 0400 or
the user knows he want to discard the database on exit.  

Humbly submitted.  

--jkl

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

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread James K. Lowden

On Mon, 10 Feb 2014 16:20:40 -0500
C M  wrote:

> But this must be a fairly commonly sought need.  The solution you
> propose where I occasionally export a copy of the db to Dropbox is
> great *for backup purposes*  but seems to exclude the possibility of
> syncing across multiple computers.  So what would you recommend?

Rethink the problem.  :-)

The problem is not, actually, how to sync a SQLite database across
multiple computers.  The problem is, how to make data in a SQLite
database available at multiple locations, right?  

In fact, the problem may be stated more generally, without reference to
SQLite.  

If you look at it that way, one solution might be to leave the database
in one place, and route queries to that place.  Another might be to use
a DBMS with a "replication" feature that maintains synchronized copies
of the database.  Or build such a feature yourself.  

It's my understanding that Firefox uses SQLite for its bookmarks, and
supports keeping bookmarks in the cloud.  

Hacks borrow and artists steal.  

HTH.  

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

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

On Mon, Feb 10, 2014 at 9:02 PM, Richard Hipp  wrote:

>
> Your work-arounds pending the patch release:
>
> (1) Do not compile with SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4
> (2) Change your query so that it says "+moved_to IS NOT NULL" - add a
> unary "+" operator  before every column name that is subject to an IS NOT
> NULL operator.
>

A third work-around:

(3) After opening each database connection ("db") run the following code:

 if( strcmp(sqlite3_sourceid(),"2013-08-26 23:00")>0
 && strcmp(sqlite3_sourceid(),"2014-02-11")<0 ){
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, 0x800);
}

The call to sqlite3_test_control() will disable the STAT3 and STAT4 logic,
which will suppress the bug.

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

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

Thanks for the bug report.

This was a real problem.  It is now fixed on the SQLite trunk (
http://www.sqlite.org/src/info/c950d6c411).  It will be a few days before
we can get a patch release (3.8.3.1) together.

Your work-arounds pending the patch release:

(1) Do not compile with SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4
(2) Change your query so that it says "+moved_to IS NOT NULL" - add a unary
"+" operator  before every column name that is subject to an IS NOT NULL
operator.

Work-around (2) really only needs to be done for queries that have a WHERE
clause of the form:

   ( ... OR ... OR ... ) AND column IS NOT NULL

and where each sub-term of the (...OR...OR...) part is indexable.  But
adding unary "+" before every column operand of an IS NOT NULL operator is
harmless.

This problem exists in SQLite version 3.8.1, 3.8.2 and 3.8.3 only.



On Mon, Feb 10, 2014 at 2:05 PM, Bert Huijben  wrote:

> [Retrying with the user I subscribed with to avoid the moderation]
>
> Hi,
>
> As part of the Subversion 1.8.6 release we tried introducing some data in
> the 'sqlitstat_stat1' table using the recommended approach for Sqlite
> 3.8.0+
> compatibility to tell sqlite about our 'bad indexes':
> [[
> ANALYZE sqlite_master;
> INSERT INTO "sqlite_stat1" VALUES 
> INSERT INTO "sqlite_stat1" VALUES 
> ...
> ANALYZE sqlite_master;
> ]]
> (this was discussed on this list a few months ago and worked fine in all
> our
> internal testing)
>
> During release testing we found that some distributions decided to enable
> SQLITE_ENABLE_STAT3, and with this flag at least one of our queries doesn't
> work the way we expect it with Sqlite 3.8.3 after that second ANALYZE call.
> (The distribution: OS/X 'Homebrew'
>
> https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
> e826b314 )
>
> Trimmed testcase on
> http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql
>
> Original/full testcase on
> http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql
>
>
> The simplified query
> [[
> SELECT local_relpath, moved_to, op_depth, 1
>  FROM nodes n
> WHERE wc_id = 1
>   AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND
> (local_relpath
> < 'A/B0')))
>   AND moved_to IS NOT NULL
>   AND op_depth >= 0;
> ]]
>
> Returns 1 row in the sqlite versions as we normally compile it, but when
> 3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.
>
> In my opinion the missing heuristics in the statistics table shouldn't make
> the query return invalid data. (It could make it faster, slower, ...).
>
>
> I'm guessing that this is a bug that needs some fix.
>
> But for our usage of Sqlite in Subversion we can probably better block
> usage
> of an sqlite that has STAT2, STAT3 (or higher) enabled.
>
> What is the recommended approach for detecting this scenario?
>
> Bert
>
> --
> The schema of the database and the testcase are part of Subversion and
> Apache 2 licensed, so feel free to use any part for future testing.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] help needed for major SQLite problem

On 10 Feb 2014, at 9:20pm, C M  wrote:

> I purposefully put the SQlite database file in the Dropbox folder because
> it was my intention, with this app, to allow a user to use the app on more
> than one computer and "sync" the database via Dropbox.  E.g., s/he could
> make changes to the db at home and then also from his/her office computer,
> get home, and the database would be synced.  I tried this out on two
> computers at home and it seemed to be sort of working, but I occasionally
> got conflicted copies and yet never pursued the right way to do it.
> 
> But this must be a fairly commonly sought need.  The solution you propose
> where I occasionally export a copy of the db to Dropbox is great *for
> backup purposes*  but seems to exclude the possibility of syncing across
> multiple computers.  So what would you recommend?

Dropbox needs to lock the files it is writing until it has finished writing 
them.  This is a perfectly reasonably request to make of an application which 
plays with other application's data files.

This is not something that can be fixed inside SQLite.  To do so would prevent 
all multi-access activity for SQLite databases.

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

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

Ticket here:  http://www.sqlite.org/src/info/4c86b126f2

The work-around until a fix is available is to avoid compiling with
SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4.


On Mon, Feb 10, 2014 at 4:38 PM, Richard Hipp  wrote:

> STAT3 should never change the answer.  It should only help the answer to
> appear faster.  The fact that the queries gives different answers with and
> without STAT3 clearly indicates a bug.  We are working the problem now.
> Thanks for providing an simplified test case.
>
>
> On Mon, Feb 10, 2014 at 2:05 PM, Bert Huijben  wrote:
>
>> [Retrying with the user I subscribed with to avoid the moderation]
>>
>> Hi,
>>
>> As part of the Subversion 1.8.6 release we tried introducing some data in
>> the 'sqlitstat_stat1' table using the recommended approach for Sqlite
>> 3.8.0+
>> compatibility to tell sqlite about our 'bad indexes':
>> [[
>> ANALYZE sqlite_master;
>> INSERT INTO "sqlite_stat1" VALUES 
>> INSERT INTO "sqlite_stat1" VALUES 
>> ...
>> ANALYZE sqlite_master;
>> ]]
>> (this was discussed on this list a few months ago and worked fine in all
>> our
>> internal testing)
>>
>> During release testing we found that some distributions decided to enable
>> SQLITE_ENABLE_STAT3, and with this flag at least one of our queries
>> doesn't
>> work the way we expect it with Sqlite 3.8.3 after that second ANALYZE
>> call.
>> (The distribution: OS/X 'Homebrew'
>>
>> https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
>> e826b314)
>>
>> Trimmed testcase on
>> http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql
>>
>> Original/full testcase on
>> http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql
>>
>>
>> The simplified query
>> [[
>> SELECT local_relpath, moved_to, op_depth, 1
>>  FROM nodes n
>> WHERE wc_id = 1
>>   AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND
>> (local_relpath
>> < 'A/B0')))
>>   AND moved_to IS NOT NULL
>>   AND op_depth >= 0;
>> ]]
>>
>> Returns 1 row in the sqlite versions as we normally compile it, but when
>> 3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.
>>
>> In my opinion the missing heuristics in the statistics table shouldn't
>> make
>> the query return invalid data. (It could make it faster, slower, ...).
>>
>>
>> I'm guessing that this is a bug that needs some fix.
>>
>> But for our usage of Sqlite in Subversion we can probably better block
>> usage
>> of an sqlite that has STAT2, STAT3 (or higher) enabled.
>>
>> What is the recommended approach for detecting this scenario?
>>
>> Bert
>>
>> --
>> The schema of the database and the testcase are part of Subversion and
>> Apache 2 licensed, so feel free to use any part for future testing.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



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

Re: [sqlite] help needed for major SQLite problem

On 2014/02/10 23:40, Stephen Chrzanowski wrote:

Personally, I don't buy that DropBox is the culprit as I've done this kind
of thing a few times in a few applications of my own, however, I'm the
single user that works on that single account, and any app that uses DB is
usually under development and "closed" on any other geographical site.
However, with the chance that the WAL file makes it down the wire, I could
see that as being a problem if your application spits out small bits of
data periodically and is being run in multiple places. DropBox, for me at
least, realizes that another application has a handle on the database file
and won't touch it, or overwrite anything. But the WAL file might make it
through.

Yes, Dropbox realises when YOU are locking the file, the problem comes when you try to open the file while IT is locking it -
something that will only really happen on an update (upload) or back-sync (download when it was changed elsewhere).

Heh, just try to open the DB on another computer it is sync'ed with while you have it open on your computer, then change stuff in
both systems and close the apps (to release any SQLite locks)... You will quickly realise what a culprit Dropbox can be. Now imagine
it for many users...

Further to this, imagine the WAL file (or any other of the temp files) gets synced without the DB file... or vice-versa, or you get
another user's newer WAL file overwriting your own - a very probable situation.

[Note: This is not drop-box's fault, it has to do what it needs to do to ensure
sharing tactics that suits most syncing needs]

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

Re: [sqlite] help needed for major SQLite problem

On 2014/02/10 23:20, C M wrote:

On Mon, Feb 10, 2014 at 2:54 PM, RSmith wrote:

How to go from the error codes to the diagnosis? I think the logic is as
follows:
[lots of snipping]

Thanks for this insight.

I purposefully put the SQlite database file in the Dropbox folder because
it was my intention, with this app, to allow a user to use the app on more
than one computer and "sync" the database via Dropbox. E.g., s/he could
make changes to the db at home and then also from his/her office computer,
get home, and the database would be synced. I tried this out on two
computers at home and it seemed to be sort of working, but I occasionally
got conflicted copies and yet never pursued the right way to do it.

But this must be a fairly commonly sought need. The solution you propose
where I occasionally export a copy of the db to Dropbox is great *for
backup purposes* but seems to exclude the possibility of syncing across
multiple computers. So what would you recommend?

This is something all the systems we make do all the time, and I am sure is a common thing with most people on this list, so we
definitely understand the need to sync. Problem is Dropbox (or any of the other ones) is a file-syncing solution and not suitable
for direct DB syncs for reasons we covered already.

With most database syncing we just use actual cloud servers and cloud syncs with an actual database, i.e we have several cloud
servers running typically Apache, PHP, MySQL, SQLite, etc. to which all systems connect and either upload or download additions,
changes and the like. Simple versioning within the tables keep track of who updated most recently on a per-record basis, sometimes
per-field for really critical apps, but this is uncommon.

What you essentially trying to do here is allow another system to take care of the syncing for you with the very real benefit that
you don't need to support a cloud server. The downside is that you have to play by the rules of whatever service is used.

I will be honest here, for every suggestion I can come up with, I can find a scenario where it would break in a cloud-sync
environment. We actually have one system that uses cloud syncing through external service (Dropbox, Skydrive, Google drive, whatever
the user uses), but the we use custom data files that play nice with the services, the SQLite (or any other RDBMS) files simply
won't work. This means some extra work. Basically we create a computer-based key (hash), write all changes from the DB to a file
with that key as part of the name in the sync folder (MyDocs/Dropbox/ or whatever the user sets up). Any other system with which it
is shared sees that file appear in its syncing folder, and as soon as it becomes available (checking every few seconds or so), opens
it, adds the updates contained within it if those updates are newer than it's own last changes for the specified records/keys. In
turn, it will write a file containing its own newest changes etc.

SO any system connected to the cloud sync, sees all files created by all other computers, there may be many, but they are typically
very small. Every system incorporates updates from every other system's file if it is newer, but only ever creates its own update
file for its own changes and only really does that on a timely basis when the file is not locked. Every time updates are exported
only the newest updates are included and most important, what actually makes it all work, is that every such file has only 1 writer,
nobody else changes it - so you won't ever get the file trying to sync-back to your own folder or other related problems.

A headache we have is that one of the participant systems might go down for several days, user on holiday, whatever, and then it is
so far out of date that the update files it sees no longer contains some of the stuff it missed, in which case the user is prompted
to get a full DB backup from another user, and they can email it or dropbox it as another file or whatever.

ALL of these shenanigans simply because of the way the cloud syncs work - a rather cumbersome work-around, but hey, the
cloud-syncing is not on us, so a win.

I tried to be scant with details and give basic ideas because different approaches might be better depending on the exact nature of
the data and frequency of changes and importance of updates etc. etc. but feel free to ask off-list if needing more detail lest we
bore the others.

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

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread Stephen Chrzanowski

Personally, I don't buy that DropBox is the culprit as I've done this kind
of thing a few times in a few applications of my own, however, I'm the
single user that works on that single account, and any app that uses DB is
usually under development and "closed" on any other geographical site.
However, with the chance that the WAL file makes it down the wire, I could
see that as being a problem if your application spits out small bits of
data periodically and is being run in multiple places.  DropBox, for me at
least, realizes that another application has a handle on the database file
and won't touch it, or overwrite anything.  But the WAL file might make it
through.

I'd suggest looking into opening the database with an exclusive lock, or
look into using the Backup API that SQLite uses.

Using the SQLite Backup API, when your program starts, do a flat-file
standard file copy from DropBox to a different location (%USERPATH% or
something similar) and wait for the file to finish to copy.  Then open that
backed up file and work on it.  When your user saves, or closes your
application, use the backup API to put the file back to DropBox directory.
This isolates WAL file writes to the local system and not to DB.


On Mon, Feb 10, 2014 at 4:20 PM, C M  wrote:

> On Mon, Feb 10, 2014 at 2:54 PM, RSmith  wrote:
>
> > How to go from the error codes to the diagnosis? I think the logic is as
> > follows:
> >
> > We can see an error occurs when trying to access the file, or more
> > specifically, trying to obtain a shared lock on it. This means it is
> locked
> > by another application (as opposed to another SQLite thread). Now the
> > question remains which other application? We would usually simply suggest
> > to look in your system, but you already provided a log of the error, and
> it
> > is clear from the error that a file you are trying to access is in
> > "Documents\My Dropbox\myapp\" which, as everyone knows, is a dropbox
> > folder, which means likely you have dropbox installed. Secondly, Dropbox
> is
> > a known culprit in this regard, because it syncs files with the cloud (it
> > is not the only one, Skydrive, Google drive etc all do this), which means
> > it will have to lock a file while either uploading or download-syncing it
> > for consistency and concurrency reasons.  Put these three pieces of
> > evidence together, and the answer is inevitable - you probably have
> dropbox
> > problems.
> >
> > The remedy is not easy - same as when dealing with Excel exports or some
> > other system that will lock files of it's own volition if it is opened
> with
> > that system - simply making a byte-copy of the file, changing it and
> > replacing it afterwards, with a possible replace-queue facility which
> will
> > wait till a lock is released. Problem is, what if the other app made
> > changes that you actually mean to keep?
> >
> > To put this into your perspective, what if the file was dropboxed,
> altered
> > on another machine of the user's, or by another user (through a dropbox
> > share), and is now updated in the cloud and due to sync back?  Whatever
> > solution, versioning-control or other system you come up with to handle
> > this, it has to be full of user-informative messages and you can never
> keep
> > an editable file where locking might be a problem inside a dropbox (or
> > other locking+syncing) folder.
> >
> > It is better to have a DB file (meaning a file that gets small
> incremental
> > changes over time as opposed to a load-once, save-once methodology) in a
> > place that is not affected by locks, and sometimes exporting (using the
> > SQLIte backup facility maybe) to the dropbox or shared folder so it gets
> > propagated to the cloud...  Using it within that folder is just not
> > feasible.
> >
>
> Thanks for this insight.
>
> I purposefully put the SQlite database file in the Dropbox folder because
> it was my intention, with this app, to allow a user to use the app on more
> than one computer and "sync" the database via Dropbox.  E.g., s/he could
> make changes to the db at home and then also from his/her office computer,
> get home, and the database would be synced.  I tried this out on two
> computers at home and it seemed to be sort of working, but I occasionally
> got conflicted copies and yet never pursued the right way to do it.
>
> But this must be a fairly commonly sought need.  The solution you propose
> where I occasionally export a copy of the db to Dropbox is great *for
> backup purposes*  but seems to exclude the possibility of syncing across
> multiple computers.  So what would you recommend?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

STAT3 should never change the answer.  It should only help the answer to
appear faster.  The fact that the queries gives different answers with and
without STAT3 clearly indicates a bug.  We are working the problem now.
Thanks for providing an simplified test case.


On Mon, Feb 10, 2014 at 2:05 PM, Bert Huijben  wrote:

> [Retrying with the user I subscribed with to avoid the moderation]
>
> Hi,
>
> As part of the Subversion 1.8.6 release we tried introducing some data in
> the 'sqlitstat_stat1' table using the recommended approach for Sqlite
> 3.8.0+
> compatibility to tell sqlite about our 'bad indexes':
> [[
> ANALYZE sqlite_master;
> INSERT INTO "sqlite_stat1" VALUES 
> INSERT INTO "sqlite_stat1" VALUES 
> ...
> ANALYZE sqlite_master;
> ]]
> (this was discussed on this list a few months ago and worked fine in all
> our
> internal testing)
>
> During release testing we found that some distributions decided to enable
> SQLITE_ENABLE_STAT3, and with this flag at least one of our queries doesn't
> work the way we expect it with Sqlite 3.8.3 after that second ANALYZE call.
> (The distribution: OS/X 'Homebrew'
>
> https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
> e826b314 )
>
> Trimmed testcase on
> http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql
>
> Original/full testcase on
> http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql
>
>
> The simplified query
> [[
> SELECT local_relpath, moved_to, op_depth, 1
>  FROM nodes n
> WHERE wc_id = 1
>   AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND
> (local_relpath
> < 'A/B0')))
>   AND moved_to IS NOT NULL
>   AND op_depth >= 0;
> ]]
>
> Returns 1 row in the sqlite versions as we normally compile it, but when
> 3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.
>
> In my opinion the missing heuristics in the statistics table shouldn't make
> the query return invalid data. (It could make it faster, slower, ...).
>
>
> I'm guessing that this is a bug that needs some fix.
>
> But for our usage of Sqlite in Subversion we can probably better block
> usage
> of an sqlite that has STAT2, STAT3 (or higher) enabled.
>
> What is the recommended approach for detecting this scenario?
>
> Bert
>
> --
> The schema of the database and the testcase are part of Subversion and
> Apache 2 licensed, so feel free to use any part for future testing.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread C M

On Mon, Feb 10, 2014 at 2:54 PM, RSmith  wrote:

> How to go from the error codes to the diagnosis? I think the logic is as
> follows:
>
> We can see an error occurs when trying to access the file, or more
> specifically, trying to obtain a shared lock on it. This means it is locked
> by another application (as opposed to another SQLite thread). Now the
> question remains which other application? We would usually simply suggest
> to look in your system, but you already provided a log of the error, and it
> is clear from the error that a file you are trying to access is in
> "Documents\My Dropbox\myapp\" which, as everyone knows, is a dropbox
> folder, which means likely you have dropbox installed. Secondly, Dropbox is
> a known culprit in this regard, because it syncs files with the cloud (it
> is not the only one, Skydrive, Google drive etc all do this), which means
> it will have to lock a file while either uploading or download-syncing it
> for consistency and concurrency reasons.  Put these three pieces of
> evidence together, and the answer is inevitable - you probably have dropbox
> problems.
>
> The remedy is not easy - same as when dealing with Excel exports or some
> other system that will lock files of it's own volition if it is opened with
> that system - simply making a byte-copy of the file, changing it and
> replacing it afterwards, with a possible replace-queue facility which will
> wait till a lock is released. Problem is, what if the other app made
> changes that you actually mean to keep?
>
> To put this into your perspective, what if the file was dropboxed, altered
> on another machine of the user's, or by another user (through a dropbox
> share), and is now updated in the cloud and due to sync back?  Whatever
> solution, versioning-control or other system you come up with to handle
> this, it has to be full of user-informative messages and you can never keep
> an editable file where locking might be a problem inside a dropbox (or
> other locking+syncing) folder.
>
> It is better to have a DB file (meaning a file that gets small incremental
> changes over time as opposed to a load-once, save-once methodology) in a
> place that is not affected by locks, and sometimes exporting (using the
> SQLIte backup facility maybe) to the dropbox or shared folder so it gets
> propagated to the cloud...  Using it within that folder is just not
> feasible.
>

Thanks for this insight.

I purposefully put the SQlite database file in the Dropbox folder because
it was my intention, with this app, to allow a user to use the app on more
than one computer and "sync" the database via Dropbox.  E.g., s/he could
make changes to the db at home and then also from his/her office computer,
get home, and the database would be synced.  I tried this out on two
computers at home and it seemed to be sort of working, but I occasionally
got conflicted copies and yet never pursued the right way to do it.

But this must be a fairly commonly sought need.  The solution you propose
where I occasionally export a copy of the db to Dropbox is great *for
backup purposes*  but seems to exclude the possibility of syncing across
multiple computers.  So what would you recommend?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

On Mon, Feb 10, 2014 at 2:38 PM, Tim Streater  wrote:

> I wouldn't worry about whether the user clicks a red button in one corner
> or another. If they do that, presumably they risk corrupting their database
> anyway.
>

No.  SQLite is hardened against hard-kills like this. The database is
unharmed by clicking the red button or sending a "kill -9" or doing a
"force quit" or pulling the power plug.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

The latest patch for this is:


http://www.sqlite.org/src/vdiff?from=0dfa7ee9157ea6b1=fe284afe739c497e=1

Changes:

(1) Reword the banner to make it more terse and to try to avoid "banner
fatigue".

(2) If opened with no command-line arguments (and hence on an in-memory
database) output a warning in the banner.

   (a)  On unix the warning is bold.
   (b)  On windows the warning is bold and red.
   (c)  The warning suggests the use of ".open FILENAME"

(3) Added the ".save" command as an alias for ".backup".  But there is no
mention of this in the banner.  (Trying to keep the banner succinct.)

There is no warning prior to close because (in my experience) most users
will do a hard kill of some kind (Ctrl-D or closing the console window) in
which case no warning is possible.


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

Re: [sqlite] help needed for major SQLite problem

On 2014/02/10 21:18, C M wrote:

Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338)
SQLITE_IOERR

SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM
WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O
error (3338) SQLITE_IOERR

Looks like GetFileAttributesEx() might be throwing an ERROR_ACCESS_DENIED
exception. Maybe a virus scanner or some other background process had
temporarily locked the database file.

Dan.

I agree, and I think Dropbox is the culprit here.

May I ask either Dan or Kees, or anyone here, how to go from the error
codes to that diagnosis?

Kees, why do you think Dropbox is the culprit?

I may want to deploy this app to users who would also backup their database
by having it in the Dropbox folder. What would people suggest I do about
this?

How to go from the error codes to the diagnosis? I think the logic is as
follows:

We can see an error occurs when trying to access the file, or more specifically, trying to obtain a shared lock on it. This means it
is locked by another application (as opposed to another SQLite thread). Now the question remains which other application? We would
usually simply suggest to look in your system, but you already provided a log of the error, and it is clear from the error that a
file you are trying to access is in "Documents\My Dropbox\myapp\" which, as everyone knows, is a dropbox folder, which means likely
you have dropbox installed. Secondly, Dropbox is a known culprit in this regard, because it syncs files with the cloud (it is not
the only one, Skydrive, Google drive etc all do this), which means it will have to lock a file while either uploading or
download-syncing it for consistency and concurrency reasons. Put these three pieces of evidence together, and the answer is
inevitable - you probably have dropbox problems.

The remedy is not easy - same as when dealing with Excel exports or some other system that will lock files of it's own volition if
it is opened with that system - simply making a byte-copy of the file, changing it and replacing it afterwards, with a possible
replace-queue facility which will wait till a lock is released. Problem is, what if the other app made changes that you actually
mean to keep?

To put this into your perspective, what if the file was dropboxed, altered on another machine of the user's, or by another user
(through a dropbox share), and is now updated in the cloud and due to sync back? Whatever solution, versioning-control or other
system you come up with to handle this, it has to be full of user-informative messages and you can never keep an editable file where
locking might be a problem inside a dropbox (or other locking+syncing) folder.

It is better to have a DB file (meaning a file that gets small incremental changes over time as opposed to a load-once, save-once
methodology) in a place that is not affected by locks, and sometimes exporting (using the SQLIte backup facility maybe) to the
dropbox or shared folder so it gets propagated to the cloud... Using it within that folder is just not feasible.

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

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread Kees Nuyt

On Mon, 10 Feb 2014 14:18:18 -0500, C M 
wrote:

>On Sat, Feb 8, 2014 at 4:28 AM, Kees Nuyt  wrote:
>>
>> On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy
>>  wrote:
>
>
>
>> >> SQLITE_LOG: delayed 1375ms for lock/sharing conflict (10) SQLITE_IOERR
>> >>
>> >> SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents and
>Settings\user\My
>> >> Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338)
>> >> SQLITE_IOERR
>> >>
>> >> SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM
>Durations
>> >> WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O
>> >> error (3338) SQLITE_IOERR
>
>
>> >Looks like GetFileAttributesEx() might be throwing an ERROR_ACCESS_DENIED
>> >exception. Maybe a virus scanner or some other background process had
>> >temporarily locked the database file.
>> >
>> >Dan.
>>
>> I agree, and I think Dropbox is the culprit here.
>
>May I ask either Dan or Kees, or anyone here, how to go from the error
>codes to that diagnosis?
>
>Kees, why do you think Dropbox is the culprit?

Because the log indicates that the database and its journal are
located in a dropbox directory:

"SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents and
Settings\user\My Documents\My Dropbox\myapp\gorp.db-journal) -
Access is denied. (3338) SQLITE_IOERR"

Given that name, I just assumed you are using dropbox. Dropbox
will try to synchronise files in that directory with its copy in
the cloud whenever its contents have changed.
In an effort to make a consistent copy, dropbox will probably
lock the file during the copy operation.

Both the database and its journal will change frequently when
you are using it, and dropbox will try yo keep up.

You may want to temporarily disable dropbox to avoid this
unwanted cpncurrent access, or perhaps restrict dropbox to time
slots when you don't use the database.

>I may want to deploy this app to users who would also backup their database
>by having it in the Dropbox folder.  What would people suggest I do about
>this?

Perhaps you could put the database somewhere else and
periodically use the sqlite3_backup() interface to save a copy
to the dropbox folder.

>Thanks again to all.  (btw, I now view my original subject line as a bit
>much; I was just frustrated by it happening so randomly and without the
>ability to fix it)

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread Tim Streater

On 10 Feb 2014 at 19:25, Simon Slavin  wrote:

> I don't like the idea anyway.  There should be no difference between
> double-clicking on an app and starting it by typing its name.

I would go for:

1) Warning at startup if an in-memory db is being used

2) Add .save /path/to/filename so the work can be saved

3) Add option to enable the warning in (4) (e.g. to put in .sqliterc)

4) Add warning if you ctrl-d or .quit (previously enabled, see (3))

Is all that backwards compatible? I wouldn't worry about whether the user 
clicks a red button in one corner or another. If they do that, presumably they 
risk corrupting their database anyway. I've never double-clicked on sqlite3 and 
wouldn't expect to.

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

Re: [sqlite] Solving the Sudoku with SQlite 3.8.3 trunk

2014-02-10 Thread big stone

Don't say postgresql is slower :
- I remember now I did then implement the "norvig postgresql",
- and it manages a 20% win over your solution with today postgresql 9.3.2.3.

Anyway, it's indeed a "less inefficient" run for now between SQL motors.

It remains to be seen if improving SQLite in sudoku solving will improve it
for other existing (or not yet imagined) workloads.

** the "norvig" PostgresSQL code **

-- implementing several "logical" algorithms in SQL to solve a sudoku
--
--   Norvig algorithm from Peter Norvig, http://norvig.com/sudoku.html
--
--   Brut Force algorithm from Anton Scheffer,
http://technology.amis.nl/2009/10/13/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring/

--   translated per Marcin Mank,
http://archives.postgresql.org/pgsql-general/2009-11/msg00150.php
--
-- Norvig Algorithm =
--   . from given Sudoku problem, generate an array with each cell of the
sudoku being a string with all remaining possibilites
--   . define as "neighbors" of a cell, all cells which must not have the
same piece as this cells, (there are 20 neighbors to each cell)
--   . then analyse the cell (the string) have the less remaining
solutions, but more than 1,
--.. for a given sudoku position, "play" only on the first cell you
found with the less possibilities, and try each of them,
--   ... replace in this cell the string by the possible piece you
want to try,
--   ... remove the piece you played from the neighbors cells own
possibilities (as it's no more a possibility for them)
--... when removing a possibility to a neighbor, if it leave
him only on possibility, "play" it immediately,
--   ... if you remove all the solutions from a neighbor, than
you're not on the sudoku solution path, stop this track.
--
-- Brut force Algorithm =
--. try all possible piece in all empty position of the sudoku problem,
from top left to bottom right of the sudoku board cells,
--. stop a track each time you can't fill the next position
-- 
-- Speed result :
--. brute force loose on the easy and complex sudo, wins on the medium
--. "Brut" and  "Norvig" algorithms seem to stay mono-thread, giving-up
the competitive advantage of SQL
-- ==

-- Array usage in SQL (
http://www.postgresql.org/docs/9.2/static/functions-array.html)
-- array length (on a dimension) :array_length(anyarray, dimension_integer)
-- array to rowset : select unnest(ARRAY[1,2])
-- rowset to array : ARRAY(SELECT * FROM test)
-- array to string : select array_to_string(ARRAY[1, 2, 3, NULL, 5], ',',
'*')
-- string to array : select string_to_array('xx~^~yy~^~zz', '~^~', 'yy')



CREATE OR REPLACE function  format_string_sudoku(board  text [])
-- displays a sudoku "board" in 9x9 form with delimiters (so 13 line
records)
RETURNS setof text
LANGUAGE SQL
AS $$
with recursive  formatted_string(ss , ind )

as (select '' , 81 FROM generate_series(1,1) lp
union all
   select (case when length($1[ind])=1 then $1[ind] else '_' end) || (case
  when ind % 81=0 then '|='
  when ind % 27=0 then '||---||'
  when ind % 9=0 then '||'
  when ind % 3=0 then '|' else '' end)
  || ss , ind-1 from formatted_string where ind>0 )

select unnest(string_to_array( '=|' || ss, ''))
from formatted_string where ind=0
$$;

CREATE OR REPLACE function neighbors(cell integer)
-- gives back the rowset of neighbors of the given "cell" of any sudoku
board
RETURNS SETOF integer
LANGUAGE SQL
AS $$
with list_of(neighbors) as (select   unnest(ARRAY[
  mod( $1 - 1, 9 ) - 8 + lp * 9 ,
  ( ($1 - 1 ) / 9 ) * 9 + lp  ,
   mod( ( ( $1 - 1 ) / 3 ), 3 ) * 3 + ( ( $1 - 1 ) / 27 ) * 27 + lp + (
( lp - 1 ) / 3 ) * 6]) FROM generate_series(1,9) lp
  )
select  distinct * from list_of where
neighbors<>$1
$$;


CREATE OR REPLACE function truly_possibles(board text, cell integer)
-- gives the table of possible pieces to put in given "cell"  of the
current  sudoku "board"
-- including the one already in place, if it exists
RETURNS SETOF text
LANGUAGE SQL
AS $$

select z from   (SELECT gs::text AS z FROM generate_series(1,9) gs) z
  WHERE  substr( $1,  $2  ,1)=' '  and  NOT EXISTS ( SELECT NULL
   FROM neighbors($2) lp
   WHERE   z = substr( $1,   lp  , 1 )
 )
  union select  substr( $1,  $2 ,1 ) z where  substr( $1,  $2 ,1 )<>'
'
$$;

create or replace function create_sudoku_board(initial   text) returns
setof text[]
-- create a sudoku board from the given "s" (string form) sudoku problem
language sqlas
$$

with recursive

-- handle several string presentations of the sudoku board problem to solve
cleaned_starter(starter) as (select
regexp_replace(regexp_replace(initial,'\n|\=|\-|\|', '' ,'g' ),'\.','
','g'  )),

-- recursively generate the board from the bottom right cell to the top
left of the sudoku board
board_generator( t  , board , next_cell

Re: [sqlite] help needed for major SQLite problem

On 10 Feb 2014, at 7:18pm, C M  wrote:

> I may want to deploy this app to users who would also backup their database
> by having it in the Dropbox folder.  What would people suggest I do about
> this?

Don't run the app while Dropbox is messing with its datafile.

The problem is with Dropbox, which can copy stuff between folder at any time.

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

On 10 Feb 2014, at 5:57pm, Richard Hipp  wrote:

> On Mon, Feb 10, 2014 at 12:51 PM,  wrote:
> 
>> I second the idea of a kind of "WARNING: All your work will be lost, are
>> you sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the
>> application was started by (double-)clicking on it, otherwise the warning
>> will be a nuisance when running test scripts.
> 
> I think I know how to detect a double-click launch versus a command-line
> launch on windows.  But I don't know how to do this, or even if it is
> possible to do, on Mac or Linux.  Anybody have any ideas?

I don't think it's possible on a Mac for a command-line application.  The 
changes you make for double-click command line are stored in something 
(info.plist) which isn't included when your product is the single executable 
file.

I don't like the idea anyway.  There should be no difference between 
double-clicking on an app and starting it by typing its name.

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Gabor Grothendieck

On Mon, Feb 10, 2014 at 1:56 PM, Petite Abeille
 wrote:
>
> On Feb 10, 2014, at 7:39 PM, Gabor Grothendieck  
> wrote:
>
>> That should have read right join.
>
> My personal opinion? Anyone even considering using a right outer join should 
> be cursed into repeating their first day at high school. For ever. Groundhog 
> Day, The High School Years.
>

Right joins are important since they generalize subscripting.  For
example, if X and Y are data tables (created using the R data.table
package) then X[Y] is a right join of X and Y.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help needed for major SQLite problem

2014-02-10 Thread C M

On Sat, Feb 8, 2014 at 4:28 AM, Kees Nuyt  wrote:
>
> On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy
>  wrote:

> >> SQLITE_LOG: delayed 1375ms for lock/sharing conflict (10) SQLITE_IOERR
> >>
> >> SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents and
Settings\user\My
> >> Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338)
> >> SQLITE_IOERR
> >>
> >> SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM
Durations
> >> WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O
> >> error (3338) SQLITE_IOERR

> >Looks like GetFileAttributesEx() might be throwing an ERROR_ACCESS_DENIED
> >exception. Maybe a virus scanner or some other background process had
> >temporarily locked the database file.
> >
> >Dan.
>
> I agree, and I think Dropbox is the culprit here.

May I ask either Dan or Kees, or anyone here, how to go from the error
codes to that diagnosis?

Kees, why do you think Dropbox is the culprit?

I may want to deploy this app to users who would also backup their database
by having it in the Dropbox folder.  What would people suggest I do about
this?

Thanks again to all.  (btw, I now view my original subject line as a bit
much; I was just frustrated by it happening so randomly and without the
ability to fix it)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3


On Feb 10, 2014, at 8:05 PM, Bert Huijben  wrote:

> As part of the Subversion 1.8.6 release we tried introducing some data in
> the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+
> compatibility to tell sqlite about our 'bad indexes’:

( Not directly related to your question, but… why, oh why is svn log 
--use-merge-history so excruciatingly slow? )

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

[sqlite] Simple SELECT misbehaving in Sqlite 3.8.3 with ENABLE_STAT3

2014-02-10 Thread Bert Huijben

[Retrying with the user I subscribed with to avoid the moderation]

Hi,

As part of the Subversion 1.8.6 release we tried introducing some data in
the 'sqlitstat_stat1' table using the recommended approach for Sqlite 3.8.0+
compatibility to tell sqlite about our 'bad indexes':
[[
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES 
INSERT INTO "sqlite_stat1" VALUES 
...
ANALYZE sqlite_master;
]]
(this was discussed on this list a few months ago and worked fine in all our
internal testing)

During release testing we found that some distributions decided to enable
SQLITE_ENABLE_STAT3, and with this flag at least one of our queries doesn't
work the way we expect it with Sqlite 3.8.3 after that second ANALYZE call.
(The distribution: OS/X 'Homebrew'
https://github.com/Homebrew/homebrew/commit/c9eca803d676961ead136b07ab145cc1
e826b314 )

Trimmed testcase on
http://b.qqn.nl/f/201402-sqlite-stat3-no-row.sql

Original/full testcase on
http://b.qqn.nl/f/201402-sqlite-stat3-no-row-FULL.sql


The simplified query
[[
SELECT local_relpath, moved_to, op_depth, 1
 FROM nodes n
WHERE wc_id = 1
  AND (local_relpath = 'A/B' OR ((local_relpath > 'A/B/') AND (local_relpath
< 'A/B0')))
  AND moved_to IS NOT NULL
  AND op_depth >= 0;
]]

Returns 1 row in the sqlite versions as we normally compile it, but when
3.8.3 has SQLITE_ENABLE_STAT3 enabled it doesn't return any rows.

In my opinion the missing heuristics in the statistics table shouldn't make
the query return invalid data. (It could make it faster, slower, ...).


I'm guessing that this is a bug that needs some fix.

But for our usage of Sqlite in Subversion we can probably better block usage
of an sqlite that has STAT2, STAT3 (or higher) enabled.

What is the recommended approach for detecting this scenario?

Bert

--
The schema of the database and the testcase are part of Subversion and
Apache 2 licensed, so feel free to use any part for future testing.

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell


On Feb 10, 2014, at 7:39 PM, Gabor Grothendieck  wrote:

> That should have read right join.  

My personal opinion? Anyone even considering using a right outer join should be 
cursed into repeating their first day at high school. For ever. Groundhog Day, 
The High School Years.

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell



On 2014/02/10 20:31, Petite Abeille wrote:

On Feb 10, 2014, at 4:23 PM, Richard Hipp  wrote:


Proposed Change To Address The Problem:

What’s the problem exactly? CS101 students distress? That’s way beyond SQLite 
reach.

My 2¢: don’t create a default persistent database. This is not helpful to 
anyone.



I agree 100% on both points, except to say that if a simple change can be found that do not alter the function but can make it work 
better for everyone, then it would be a crime not to implement.


I quite like another poster's idea, Change nothing, but warn when starting sqlite3.exe, not when quitting. An easy function to 
commit an in-memory DB to disk will be great too, though I believe .backup can do this already (though another suggestion to alias 
it to .save have merit). None of this will break current implementations or scripts.



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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Gabor Grothendieck

That should have read right join.  Its a nuisance when you are trying
to show someone SQL and trying to keep things simple that you have to
add the complexity of switching the arguments around.

I am still on 3.7.17 which is the version that currently ships with
the software I am using but its nice to know that I will have rfc4180
when its upgraded.

On Mon, Feb 10, 2014 at 1:26 PM, Petite Abeille
 wrote:
>
> On Feb 10, 2014, at 5:19 PM, Gabor Grothendieck  
> wrote:
>
>> The other features that would make teaching a bit easier would be to support
>> left join explicitly and support the rfc4180 standard for csv files.
>
> Hmmm?
>
> Left join:
> http://www.sqlite.org/syntaxdiagrams.html#join-operator
>
> RFC-4180 compliant .import:
> http://sqlite.org/releaselog/3_8_0.html
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Konrad Hambrick


All --

My $0.02 is to not change anything except maybe, perhaps printing a warning 
message.

If they want to create a persistent DB, .quit ; and start over with a dbname on 
the command line ...

I am afraid any such change might break existing bash and bat scripts I've got 
'out there in the wild'.

Thanks for letting me pipe-in ...

-- kjh


> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf
> Of Richard Hipp
> Sent: Monday, February 10, 2014 12:14 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line 
> shell
> 
> On Mon, Feb 10, 2014 at 1:11 PM, Mike King  wrote:
> 
> > Why not show the warning on exit only if an in memory database is in use.
> >
> >
> 
> Because on windows, the likely "exit" will be when the user clicks the big
> red X in the upper right-hand corner, closing the terminal window down, so
> there is no opportunity to display a warning nor ask for confirmation.
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell



On 2014/02/10 18:22, John McKown wrote:

Being a UNIX (Linux) partisan, and somewhat tacky towards Windows users,
why not go the normal Windows route of having a "pop up" dialog box (or at
least a message) similar to what normal Windows applications say about
possible loss of data. Something along the lines of "You are exiting
sqlite3, but there is data in one or more memory resident tables which will
be lost. Proceed (Y or N)?"


Being a Windows partisan and somewhat untacky to downright loving of all other systems and uses, allow me to explain the way Windows 
works quickly - it has a kernel which runs services and programs, just like unix/linux/etc, and then it has a very standardized very 
elaborate graphical user interface system which attaches graphical areas (commonly known as "Forms") to underlying processes - much 
like OSX etc.


Running a process as a command-line or shell process means it can be really lightweight and devoid of any of the mentioned graphical 
user-interfacy stuff, with the added benefit that, a few specific adaptations aside, you can use much the same C codebase to make it 
run on linux or whatever else, much like the discussed slite3 tool. As such, attaching a pop-up anything to the process requires it 
to have evolved into a GUI-supporting system so it has parent windows to have the popped-up handles attached to - a change which is 
simple, but would see the exe auto-double in size.  So to answer your suggestion - no, that's not a good idea.


To further this point, there must be a quadrillion free full graphical-interface SQLite tools on every OS out there... why on earth 
do students not simply use any of those?


FWIW, my vote goes with the current mainstream opinion, leave as is, warn when quitting, possibly provide a .save command. Forcing 
the shell-window closed (Alt-F4, Click close, Alt-SPACE->C, Task manager, whatever) will steal any opportunity for ever having a 
warning of any kind - but then, this is true for any other shell tool, why would it be a special requirement for sqlite3.exe? I 
don't think anybody would expect to have their DB saved if they forcibly shutdown ANY app, only when exiting normally, one should be 
at least warned of a potential loss.


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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Adam Devita

Good day,

I'd rather the warning be in the text when you open the sqlite tool with an
implied in memory database.  Put an extra \n if you want the warning to
stand out.

Adam



On Mon, Feb 10, 2014 at 1:26 PM, Petite Abeille wrote:

>
> On Feb 10, 2014, at 5:19 PM, Gabor Grothendieck 
> wrote:
>
> > The other features that would make teaching a bit easier would be to
> support
> > left join explicitly and support the rfc4180 standard for csv files.
>
> Hmmm?
>
> Left join:
> http://www.sqlite.org/syntaxdiagrams.html#join-operator
>
> RFC-4180 compliant .import:
> http://sqlite.org/releaselog/3_8_0.html
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell


On Feb 10, 2014, at 4:23 PM, Richard Hipp  wrote:

> Proposed Change To Address The Problem:

What’s the problem exactly? CS101 students distress? That’s way beyond SQLite 
reach.

My 2¢: don’t create a default persistent database. This is not helpful to 
anyone.

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell


On Feb 10, 2014, at 5:19 PM, Gabor Grothendieck  wrote:

> The other features that would make teaching a bit easier would be to support
> left join explicitly and support the rfc4180 standard for csv files.

Hmmm? 

Left join:
http://www.sqlite.org/syntaxdiagrams.html#join-operator

RFC-4180 compliant .import:
http://sqlite.org/releaselog/3_8_0.html


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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread Kevin Martin

On 10 Feb 2014, at 17:57, Richard Hipp  wrote:

> I think I know how to detect a double-click launch versus a command-line
> launch on windows.  But I don't know how to do this, or even if it is
> possible to do, on Mac or Linux.  Anybody have any ideas?

For me, It's not so much how it is launched that matters, but whether it is 
running interactively. I would only want the behaviour altered if stdin is a 
terminal. What about something as simple as

isatty(STDIN_FILENO);

Thanks,
Kevin

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

On Mon, Feb 10, 2014 at 1:11 PM, Mike King  wrote:

> Why not show the warning on exit only if an in memory database is in use.
>
>

Because on windows, the likely "exit" will be when the user clicks the big
red X in the upper right-hand corner, closing the terminal window down, so
there is no opportunity to display a warning nor ask for confirmation.

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Mike King

Why not show the warning on exit only if an in memory database is in use.
Likewise by default open in memory unless a path is specified.

On Monday, 10 February 2014, Richard Hipp  wrote:

> On Mon, Feb 10, 2014 at 12:51 PM, > wrote:
>
> > I second the idea of a kind of "WARNING: All your work will be lost, are
> > you sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the
> > application was started by (double-)clicking on it, otherwise the warning
> > will be a nuisance when running test scripts.
> >
>
> I think I know how to detect a double-click launch versus a command-line
> launch on windows.  But I don't know how to do this, or even if it is
> possible to do, on Mac or Linux.  Anybody have any ideas?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

On Mon, Feb 10, 2014 at 12:51 PM,  wrote:

> I second the idea of a kind of "WARNING: All your work will be lost, are
> you sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the
> application was started by (double-)clicking on it, otherwise the warning
> will be a nuisance when running test scripts.
>

I think I know how to detect a double-click launch versus a command-line
launch on windows.  But I don't know how to do this, or even if it is
possible to do, on Mac or Linux.  Anybody have any ideas?


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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-lineshell

2014-02-10 Thread tonyp

I second the idea of a kind of "WARNING: All your work will be lost, are you 
sure you want to quit? (y/N)" on trying to exit, but *ONLY* if the 
application was started by (double-)clicking on it, otherwise the warning 
will be a nuisance when running test scripts.


-Original Message- 
From: Richard Hipp

Sent: Monday, February 10, 2014 6:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Proposed enhancement to the sqlite3.exe 
command-lineshell


What if, instead of opening a standard database, the sqlite3.exe
command-line shell just issued a warning message reminding the user that
they are working on a transient in-memory database and suggesting the use
of the ".open" command to connect to a persistent on-disk database.  Like
in this patch:

http://www.sqlite.org/src/info/90e9deae4a


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


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

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

2014-02-10 Thread Nico Williams

On Sat, Feb 8, 2014 at 7:26 AM, Richard Hipp  wrote:
> OpenBSD lacks a coherent filesystem cache.  That is to say, changes to a
> file made using write() are not necessarily reflected in mmap-ed memory
> right away.  And change to a mmap-ed segment are not necessarily reflected
> in subsequent read() operations.

Availability of unified caches are not entirely a function of the OS;
the OS may support it but the filesystem might not.

The right thing to do is to msync() with MS_INVALIDATE in this case.
It should be a no-op when the OS+filesystem implement a unified cache.
 It will be expensive when they don't.  I don't recall if there's a
portable way to find out if the OS+filesystem provide a unified cache.

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

Re: [sqlite] Memory-mapped I/O [was: SQLite 3.7.17 preview - 2x faster?]

2014-02-10 Thread Nico Williams

On Sun, Feb 9, 2014 at 5:03 PM, Richard Hipp  wrote:
> On Sun, Feb 9, 2014 at 5:49 PM, James K. Lowden 
> wrote:
> I suspect that adding msync() calls would wipe out any speed advantage for
> using memory-mapped I/O.  And since speed is the only advantage to memory
> mapped I/O and because there are many disadvantages, I don't see a use-case
> for trying to make mmap work on OpenBSD.

msync() with MS_SYNC will definitely destroy performance, so don't do that.

msync() with MS_ASYNC and/or MS_INVALIDATE are generally no-ops when
the OS and *filesystem* implement a shared cache.  The extra cost
here, then, is the cost of a system call (plus whatever work has to be
done to determine that the call is a no-op).

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Jay Kreibich


On Feb 10, 2014, at 10:20 AM, Jay Kreibich  wrote:

> On Feb 10, 2014, at 10:15 AM, Richard Hipp  wrote:
> 
>> What if, instead of opening a standard database, the sqlite3.exe
>> command-line shell just issued a warning message reminding the user that
>> they are working on a transient in-memory database and suggesting the use
>> of the ".open" command to connect to a persistent on-disk database.  Like
>> in this patch:
>> 
>> http://www.sqlite.org/src/info/90e9deae4a
> 
> 
> I think a warning is the best solution.  In addition to the .open reminder,  
> I would add a ".save" shell command that dumps the current main database to a 
> file.  This might be a simple alias to the .backup command.
> 

Actually... let me amend this.  I like the suggestion of having an .quit 
warning when "main" is an in-memory database.

In addition, I'd suggest a .save shell command that dumps the current main 
database to disk, and then opens the file as the new main.  This would provide 
a "document" model that many desktop users are already used to dealing with.


>   -j
> 
> --  
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it, but 
> showing it to the wrong people has the tendency to make them feel 
> uncomfortable." -- Angela Johnson
> 
> 
> 
> 

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

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




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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Stephen Chrzanowski

I was just going to suggest that John.  Short of hitting CTRL-C to break
out of the program, the user may have to "double-quit" if no file path has
been given to be saved to, just for confirmation.

> .q
!!Warning - In-Memory Database not saved.  Quit again to exit without saving
> .q
C:\Users\Default User> _

Or, use ".qq" to force quit and ignore the warning.

On Mon, Feb 10, 2014 at 11:22 AM, John McKown
wrote:

> Being a UNIX (Linux) partisan, and somewhat tacky towards Windows users,
> why not go the normal Windows route of having a "pop up" dialog box (or at
> least a message) similar to what normal Windows applications say about
> possible loss of data. Something along the lines of "You are exiting
> sqlite3, but there is data in one or more memory resident tables which will
> be lost. Proceed (Y or N)?"
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread John McKown

Being a UNIX (Linux) partisan, and somewhat tacky towards Windows users,
why not go the normal Windows route of having a "pop up" dialog box (or at
least a message) similar to what normal Windows applications say about
possible loss of data. Something along the lines of "You are exiting
sqlite3, but there is data in one or more memory resident tables which will
be lost. Proceed (Y or N)?"

On Mon, Feb 10, 2014 at 10:15 AM, Richard Hipp  wrote:

> What if, instead of opening a standard database, the sqlite3.exe
> command-line shell just issued a warning message reminding the user that
> they are working on a transient in-memory database and suggesting the use
> of the ".open" command to connect to a persistent on-disk database.  Like
> in this patch:
>
>  http://www.sqlite.org/src/info/90e9deae4a
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Wasn't there something about a PASCAL programmer knowing the value of
everything and the Wirth of nothing?

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Jay Kreibich


On Feb 10, 2014, at 10:15 AM, Richard Hipp  wrote:

> What if, instead of opening a standard database, the sqlite3.exe
> command-line shell just issued a warning message reminding the user that
> they are working on a transient in-memory database and suggesting the use
> of the ".open" command to connect to a persistent on-disk database.  Like
> in this patch:
> 
> http://www.sqlite.org/src/info/90e9deae4a


I think a warning is the best solution.  In addition to the .open reminder,  I 
would add a ".save" shell command that dumps the current main database to a 
file.  This might be a simple alias to the .backup command.

  -j

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

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




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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread David Bicking

The first time I saw sqlite demonstrated at a linux user group, the presenter 
didn't realize he was using a memory database. I had to explain why all his 
work was lost, then proceeded to continue the demo since I knew more about the 
product. (This was years ago, I think we were still at sqlite 2 at the time.)

Personally, I don't like the idea of a default db. I would rather see a warning 
message sent to the console if no filename was specified saying that data is 
being saved in memory and will be lost on exit. And perhaps suggesting how to 
open a file if that is how they want to proceed.

I really don't like a bizarre solution that has sqlite3.exe or (sqlite3 on 
linux/mac) behaving differently between a double click or from the command 
line. That just seems wrong to me.

David

On Mon, 2/10/14, Stephan Beal  wrote:

 Subject: Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line 
shell
 To: "General Discussion of SQLite Database" 
 Date: Monday, February 10, 2014, 10:46 AM

 On Mon, Feb 10, 2014 at
 4:41 PM, Simon Slavin 
 wrote:

 > Why ?  I
 suspect some Mac user would find it just as useful.  And
 then why
 > leave Unix users out ?
 >

 Speaking
 as a member of the Unix-only crowd: please don't! While
 i admit
 that the current behaviour has
 caused minor amounts of annoyance on my part
 (my own fault, as i've been using it long
 enough to know better), i also
 feel that the
 current behaviour is "correct." i think it's
 an interesting
 idea for Windows, though.
 Don't have an opinion on Apple installations.

 If it could be configured via
 environment variable, i'd be happy to see it
 in Unix, too. e.g. SQLITE3_DEFAULT_DB, if set
 to a non-empty string, would
 be the db which
 gets automatically opened at startup IFF 1==argc (or some
 similar heuristic - maybe always load it if no
 filename args are provided).

 -- 
 -
 stephan beal
 http://wanderinghorse.net/home/stephan/
 http://gplus.to/sgbeal
 "Freedom is sloppy. But since
 tyranny's the only guaranteed byproduct of
 those who insist on a perfect world, freedom
 will have to do." -- Bigby Wolf
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Dominique Devienne

On Mon, Feb 10, 2014 at 5:15 PM, Richard Hipp  wrote:

> What if, instead of opening a standard database, the sqlite3.exe
> command-line shell just issued a warning message reminding the user that
> they are working on a transient in-memory database and suggesting the use
> of the ".open" command to connect to a persistent on-disk database.  Like
> in this patch:
>
>  http://www.sqlite.org/src/info/90e9deae4a

Sounds better to me. Although I'd still prefer a terser 1-liner is
possible. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

What if, instead of opening a standard database, the sqlite3.exe
command-line shell just issued a warning message reminding the user that
they are working on a transient in-memory database and suggesting the use
of the ".open" command to connect to a persistent on-disk database.  Like
in this patch:

 http://www.sqlite.org/src/info/90e9deae4a


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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Gabor Grothendieck

On Mon, Feb 10, 2014 at 10:23 AM, Richard Hipp  wrote:
> The Problem:
>
> Many new users (especially university students taking a "database 101"

The other features that would make teaching a bit easier would be to support
left join explicitly and support the rfc4180 standard for csv files.

> class) download the "sqlite3.exe" file from the SQLite website,
> double-click on the "sqlite3" icon to get a command-line shell, then start
> typing SQL statements.  But when they exit the shell, they are distressed
> to discover that their database has disappeared.
>
> Proposed Change To Address The Problem:
>
> When launching sqlite3.exe with a double-click, have it open a standard
> database in a standard place instead of an in-memory database as you would
> get when launching sqlite3.exe with no arguments.  Possibly also give
> additional hints, such as references to the ".open" command, when launching
> by double-click.
>
> (1) Detect double-click launch by looking at argc and argv.  On a
> double-click launch, argc==1 and argv[0] contains the full pathname of the
> executable.  On a command-line launch, argv[0] contains whatever the user
> typed, which is usually not the full pathname
>

I assume that means that if you do not keep sqlite3 on your path then you must
use:

   /path/to/sqlite3 :memory:

to call sqlite3 with an in-memory database. I am not so enthusiastic about this.

How about as an alternative that it works as it does now but when you
exit it asks you
if you want to save the database.  That seems more consistent with how
other programs
(editors, word processors, spreadsheets, etc.) work.

> (2) This change would be for Windows only.  The code to implement it would
> be enclosed in #ifdef _WIN32 ... #endif
>
> (3) Announce the name of the "standard" database file in the banner.
>
> Questions:
>
> (4) What should the name of the "standard" database file be?
>
> (5) In what folder should the "standard" database file be created?
>

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

On 10 Feb 2014, at 4:15pm, Richard Hipp  wrote:

> What if, instead of opening a standard database, the sqlite3.exe
> command-line shell just issued a warning message reminding the user that
> they are working on a transient in-memory database and suggesting the use
> of the ".open" command to connect to a persistent on-disk database.

I find that equally acceptable, given the difficulty of squeezing in the 'Guess 
the acceptable path' routine at this stage.

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

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Dominique Devienne

On Mon, Feb 10, 2014 at 4:23 PM, Richard Hipp  wrote:

> Proposed Change To Address The Problem:
>
> When launching sqlite3.exe with a double-click, have it open a standard
> database in a standard place instead of an in-memory database as you would
> get when launching sqlite3.exe with no arguments.  Possibly also give
> additional hints, such as references to the ".open" command, when launching
> by double-click.
>

Personally, I don't care much for this change... I noticed you didn't ask
for +1 / -1 on this, but most REPLs behave like sqlite3 does now, i.e. pure
in-memory changes, and you need to explicitly do something to persist your
experimentations. My $0.02. --DD

Questions:
>
> (4) What should the name of the "standard" database file be?
>

In keeping with sqlite_master, I'd name it sqlite_default.db

> (5) In what folder should the "standard" database file be created?
>

It depends on the Windows version I'm afraid. On Win7,
%HOMEDRIVE%\%HOMEPATH% seems like a sensible default, or perhaps
%USERPROFILE% too. Didn't remember the previous Windows / DOS equivalents
for Vista, XP, and earlier. --DD

C:\Users\DDevienne>set USER
USERDNSDOMAIN=...
USERDOMAIN=...
USERNAME=DDevienne
USERPROFILE=C:\Users\DDevienne

C:\Users\DDevienne>set HOME
HOMEDRIVE=C:
HOMEPATH=\Users\DDevienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

2014-02-10 Thread Stephan Beal

On Mon, Feb 10, 2014 at 4:41 PM, Simon Slavin  wrote:

> Why ?  I suspect some Mac user would find it just as useful.  And then why
> leave Unix users out ?
>

Speaking as a member of the Unix-only crowd: please don't! While i admit
that the current behaviour has caused minor amounts of annoyance on my part
(my own fault, as i've been using it long enough to know better), i also
feel that the current behaviour is "correct." i think it's an interesting
idea for Windows, though. Don't have an opinion on Apple installations.

If it could be configured via environment variable, i'd be happy to see it
in Unix, too. e.g. SQLITE3_DEFAULT_DB, if set to a non-empty string, would
be the db which gets automatically opened at startup IFF 1==argc (or some
similar heuristic - maybe always load it if no filename args are provided).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed enhancement to the sqlite3.exe command-line shell

On 10 Feb 2014, at 3:23pm, Richard Hipp  wrote:

> When launching sqlite3.exe with a double-click, have it open a standard
> database in a standard place instead of an in-memory database as you would
> get when launching sqlite3.exe with no arguments.  Possibly also give
> additional hints, such as references to the ".open" command, when launching
> by double-click.

If the shell does have to make up its own filepath it should tell the user what 
it did, where to find the path, and perhaps also how to specify their own path 
in future.

> [snip] (2) This change would be for Windows only.

Why ?  I suspect some Mac user would find it just as useful.  And then why 
leave Unix users out ?

> (5) In what folder should the "standard" database file be created?

I suspect the problem is knowing where a 'safe' location would be to create a 
file.  Could this be added as a variable inside the VFS ?  The VFS for HFS+ 
would presumably be able to assume you are on a Mac and produce a 
Mac-appropriate default pathname.  The VFS used inside an Android phone would, 
again, be a good place to do it.

Is that how the path for temporary files (not journal files, but /really/ 
temporary files) is decided ?

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

[sqlite] Proposed enhancement to the sqlite3.exe command-line shell

The Problem:

Many new users (especially university students taking a "database 101"
class) download the "sqlite3.exe" file from the SQLite website,
double-click on the "sqlite3" icon to get a command-line shell, then start
typing SQL statements.  But when they exit the shell, they are distressed
to discover that their database has disappeared.

Proposed Change To Address The Problem:

When launching sqlite3.exe with a double-click, have it open a standard
database in a standard place instead of an in-memory database as you would
get when launching sqlite3.exe with no arguments.  Possibly also give
additional hints, such as references to the ".open" command, when launching
by double-click.

(1) Detect double-click launch by looking at argc and argv.  On a
double-click launch, argc==1 and argv[0] contains the full pathname of the
executable.  On a command-line launch, argv[0] contains whatever the user
typed, which is usually not the full pathname

(2) This change would be for Windows only.  The code to implement it would
be enclosed in #ifdef _WIN32 ... #endif

(3) Announce the name of the "standard" database file in the banner.

Questions:

(4) What should the name of the "standard" database file be?

(5) In what folder should the "standard" database file be created?

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

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

On Mon, Feb 10, 2014 at 9:07 AM, Clemens Eisserer wrote:

> Hi Richard,
>
> > In WAL mode, with synchronous=NORMAL (the default), fsync() only happens
> on
> > a checkpoint operation.  Whether or not checkpoint is "very seldom"
> depends
> > on a number of factors, but seems likely to be the case in your scenario.
>
> Thanks a lot for your answer.
> Just to make sure, with auto-checkpointing turned off, which other
> factors can cause a checkpoint to be created (if not manually)?
>

When the last open connection to the database file closes, it automatically
tries to checkpoint.  You cannot turn that off.

Checkpoints are good.  You do not want your WAL file to grow too large, and
the only way to reduce the size of the WAL file is to run a checkpoint.

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

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-10 Thread Clemens Eisserer

Hi Richard,

> In WAL mode, with synchronous=NORMAL (the default), fsync() only happens on
> a checkpoint operation.  Whether or not checkpoint is "very seldom" depends
> on a number of factors, but seems likely to be the case in your scenario.

Thanks a lot for your answer.
Just to make sure, with auto-checkpointing turned off, which other
factors can cause a checkpoint to be created (if not manually)?

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

Re: [sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

On Mon, Feb 10, 2014 at 8:40 AM, Clemens Eisserer wrote:

> Hi,
>
> I would like to use sqlite for storing temperature data acquired every
> 10s running on my raspberry pi.
> As my first SD card died within a week with this workload, I am
> looking for opportunities to reduce write operations triggered by
> fsyncs to flash.
> For me loosing 1h of data at a power failure isn't an issue, however
> the DB shouldn't be corrupt afterwards.
>
> I found the pragma "synchronous", which when set to "NORMAL" does seem
> to do exactly what I am looking for - when sqlite is used in WAL mode.
> Am I right that with this configuration, fsync is only executed very
> seldomly?
>

In WAL mode, with synchronous=NORMAL (the default), fsync() only happens on
a checkpoint operation.  Whether or not checkpoint is "very seldom" depends
on a number of factors, but seems likely to be the case in your scenario.


> > In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized
> before each checkpoint
> > and the database file is synchronized after each completed checkpoint
> and the WAL file header is synchronized
> > when a WAL file begins to be reused after a checkpoint, but no sync
> operations occur during most transactions.
>
> Thank you in advance, Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] How to minimize fsync'ed writes to flash for storing periodically measured data?

2014-02-10 Thread Clemens Eisserer

Hi,

I would like to use sqlite for storing temperature data acquired every
10s running on my raspberry pi.
As my first SD card died within a week with this workload, I am
looking for opportunities to reduce write operations triggered by
fsyncs to flash.
For me loosing 1h of data at a power failure isn't an issue, however
the DB shouldn't be corrupt afterwards.

I found the pragma "synchronous", which when set to "NORMAL" does seem
to do exactly what I am looking for - when sqlite is used in WAL mode.
Am I right that with this configuration, fsync is only executed very seldomly?

> In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized 
> before each checkpoint
> and the database file is synchronized after each completed checkpoint and the 
> WAL file header is synchronized
> when a WAL file begins to be reused after a checkpoint, but no sync 
> operations occur during most transactions.

Thank you in advance, Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

2014-02-10 Thread Constantine Yannakopoulos

On Sun, Feb 9, 2014 at 8:27 PM, Simon Slavin  wrote:

> I know it's a hack.  But it's an elegant efficient hack that takes
> advantage of the things SQLite does well.  As long as that's the only way
> you were using LIKE.
>

Don't get me wrong, the solution is good. But apart from the specific
problem I also started the thread in order to prove that the implementation
of the LIKE optimization in SqLite is not all it could be. Apart from mixed
languages there are other, less extreme scenarios where a
collation-sensitive like optimization will come in handy. For instance, for
languages with accents it would be nice to be able to create a
case-insensitive accent-insensitive (CI_AI) collation and be able to use
LIKE on it, even if it doesn't use an index. And overloading the LIKE
operator globally is not a good idea because it will affect all LIKE
operations in a database, even in columns that are not CI_AI.

I was hoping to elicit a response from D. R. Hipp but he has chosen not to
respond.

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

Re: [sqlite] Free Page Data usage

2014-02-10 Thread Eduardo Morrás

>El lun, 10/2/14, Simon Slavin  escribió:
>
> Asunto: Re: [sqlite] Free Page Data usage
> Para: "General Discussion of SQLite Database" 
> Fecha: lunes, 10 de febrero, 2014 12:34
> 
> 
> On 10 Feb 2014, at 11:29am, Raheel Gupta 
> wrote:
> 
> >>> 64-bit page numbers would not be backwards
> compatible.
> > 
> > It might be possible to implement it in backwards
> compatible mode. I guess
> > SQlite has some free flags in its superblock. Maybe we
> can use a single
> > byte to mark that this is a 64 bit page number ?
> 
> The problem is that people would create a file in the new
> version of SQLite which could handle 64 bits, and expect to
> be able to open it in old versions of SQLite which would not
> understand it.
> 
> It would seem that the move from SQLite3 to SQLite4 would be
> a good time to make all file pointers 64 bits.  But I
> don't know enough about the internal workings of SQLite4 to
> know for sure.
> 
> Simon.

 Sqlite4 is a completly different beast. It uses a hashmap & reduce. I don't 
see any improvement moving sqlite3 to 64 bits page number neither. The problem 
about inner fragmentation, using 64 KB page size is solved if, from time to 
time, make a backup, close db and open backup as main db file.

If db is very huge, backup and switch will cost time, and a DBMS where 
indexes/indexs, db statistics, tables, etc... are split in several files, I 
mean, Postgresql, SQLServer, etc... may work better ( inner fragmentation point 
of view ) and should be used.

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

Re: [sqlite] Free Page Data usage