Re: [sqlite] SQLite destroys civilization.

[C M]

On Sun, Mar 2, 2014 at 12:34 PM, Richard Hipp  wrote:

> Reports on twitter say that the "nanobots" in the TV drama "Revolution"
> have source code in the season two finale that looks like this:
>
> https://pbs.twimg.com/media/BhvIsgBCYAAQdvP.png:large
>
> Compare to the SQLite source code here:
>
> http://www.sqlite.org/src/artifact/69761e167?ln=1264-1281
> --
> D. Richard Hipp
> d...@sqlite.org
>

Best subject line here ever!   Now I feel a little guilty for using
SQLite.  :D

I'm curious how this came to your attention...?
___
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

[C M]

On Mon, Feb 10, 2014 at 4:40 PM, 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.
>

That is the same situation with me; I only develop and use this
application, and thus this database, on one computer only.  I haven't tried
the Dropbox syncing in at least two years, though it used to work.

But I'm getting the sense from others here responding to this post that
Dropbox does put a lock on the database file in order to back it up, and it
has to do that whenever the data is changed, and so one might wind up
having Dropbox lock the file when you need to write to it.  Oddly, I make
changes to the database hundreds of times a day and only get this lock
conflict--if that is indeed what is happening--every now and then, like
every few weeks or months, particularly when the RAM is filled up with a
memory leaky Firefox and other open applications...possibly because the
lock is held longer during those times?


> I'd suggest looking into opening the database with an exclusive lock,


Can you say more about that?  I'm not familiar with that option.



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

That sounds similar to what James Lowden is suggesting in this thread.
I'll look into that, and will probably have questions for the list later.

Thanks,
Che
___
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

[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] help needed for major SQLite problem

[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] help needed for major SQLite problem

[C M]

This is a follow-up to a question I asked on this list on Sep 1st, 2013,
about an error that I was randomly getting with disk-based SQLite database
in a Python desktop application.  I now have more info to provide about the
error...such as what was asked for at that time:

On Sun, Sep 1, 2013 at 6:12 PM, Richard Hipp  wrote:

> Does Python have an interface to the error and warning log mechanism of
> SQLite?  (http://www.sqlite.org/errlog.html)  Can you turn that on?  It
> will probably give more details about what it happening.
>

I wasn't able to do this at first, but thanks to switching from the
standard sqlite3 module in Python (otherwise known as pysqslite) to Roger
Binns's APSW module, and then also using an experimental module,
apswdbapi2, from Edzard Pasma (thank you both), I was able to set things up
to return the warning log...I think.  Today, after not seeing the error in
a very long time, I hit the error, and this was printed to sys.stdout:

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

Does that give anyone a better idea of what could be happening and how I
can fix this problem?

Thanks to all again,
Che M
___
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

[C M]

On Mon, Sep 2, 2013 at 3:28 PM, E.Pasma <pasm...@concepts.nl> wrote:

> Op 2 sep 2013, om 17:58 heeft C M het volgende geschreven:
>
>> ...
>>
>> Is setting up APSW and making the transition from pysqlite2's wrapper
>> fairly straightforward, or would I have to re-do all the database queries?
>> (Even so, I do them all in one utilities library, so perhaps it would be
>> not too much work).
>> ...
>>
> The transition from Pythons builtin SQLite wrapper to APSW is not too
> hard. But you must know that APSW assumes isolation_level = None (pysqlite
> connect parameter).
>
> I wrote an experimental module, apswdbapi2, that lets you try APSW in a
> pysqlite compatible mode including the isolation_level.
>
> http://pythonhosted.org/**sqmediumlite/src/apswdbapi2.**py.html<http://pythonhosted.org/sqmediumlite/src/apswdbapi2.py.html>


Thanks, that looks quite interesting.  I will be in touch with Roger
regarding APSW and if and once I get set up with that, I will give this a
try as well.  I may have a few questions for you if I try it.

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

[C M]

On Sun, Sep 1, 2013 at 6:12 PM, Richard Hipp  wrote:

> Does Python have an interface to the error and warning log mechanism of
> SQLite?  (http://www.sqlite.org/errlog.html)  Can you turn that on?  It
> will probably give more details about what it happening.
>

Looks like, no, though maybe Roger Binn's AWSW does allow that; I'm looking
into it.  Thanks for your help!
___
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

[C M]

On Sun, Sep 1, 2013 at 7:51 PM, Roger Binns  wrote:

>
> He needs to give the exception traceback which will show what is happening
> at the time.
>
> A common mistake with newish Python programmers is to catch all
> exceptions, and then keep going which also hides the exception tracebacks.
>

I have taken out the try/except catch and will hope this error occurs soon
and report back with the offending line.  I don't think it will tell me
much, though, other than the "SQlite logic error or missing database" and
the SELECT or INSERT INTO query that triggered that.  Keep in mind, these
exact same queries *do work* if I just wait a few seconds and try again, so
there is nothing wrong with them per se.



>
> On 01/09/13 15:12, Richard Hipp wrote:
> > Does Python have an interface to the error and warning log mechanism
> > of SQLite?  (http://www.sqlite.org/errlog.html)  Can you turn that on?
> > It will probably give more details about what it happening.
>
> pysqlite is the standard Python sqlite3 module and doesn't expose that
> functionality.  (It also hasn't had a new release in over two years.)  It
> tries to make SQLite look like the standard Python DBAPI and maps the
> SQLite errors into those fewer DBAPI exceptions.
>
> APSW (disclosure: I am the author) does provide the SQLite errors in a 1:1
> mapping to exceptions.  The error log could be used in theory, but is
> impractical because it has to be setup before any other SQLite call.
> There doesn't appear to be any reason why it can't be changed after the
> library is initialised.
>
> I'll see if I can add something more useful to the next APSW release.
>

If you think APSW would provide more details about just what went wrong
other than "SQLite logic error or missing database" (which, I feel, doesn't
tell me much at all), then I could potentially try it.

Is setting up APSW and making the transition from pysqlite2's wrapper
fairly straightforward, or would I have to re-do all the database queries?
(Even so, I do them all in one utilities library, so perhaps it would be
not too much work).

That's an impressive project, btw!  I'm surprised this is the first I've
heard of it.
___
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

[C M]

On Mon, Sep 2, 2013 at 2:33 AM, Markus Schaber <m.scha...@codesys.com>wrote:

> Hi, C M,
>
>
> Just to be safe: Can you roule out any antivirus or other security
> software locking the files temporarily?
>

Good idea, but I don't think that is what is happening in my case.  I
actually have a way to catch the "Database is locked" exception and
instruct the user appropriately (it is easy to lock a database by making a
change with SQLite Database Browser but not saving it).  Also, I don't use
security software on this PC other than web browsing security.

That is something to keep in mind for other users, though.  Thanks.
___
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

[C M]

On Sun, Sep 1, 2013 at 6:53 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 1 Sep 2013, at 10:34pm, C M <cmpyt...@gmail.com> wrote:
>
> > Do you know how I can do that with Python?  For example, I tried this:
> >
> > status = cursor.execute("some SQL statement here")
> > print "The status is: ", status
> >
> > But it prints the cursor object:
> >
> >> The status is 
>
> Does the cursor object have properties ?  Can you either look through them
> in a debugger or find documentation somewhere that tells you if one of them
> is something like 'last error' ?
>
> There should be a way to do it: the ability to read the code returned by
> API calls is essential to using SQLite properly.
>

I don't know.  I am looking into this, and may consider trying APSW as the
wrapper instead of pysqlite2 (see Roger Binns's post) to see if I can get
that information.

With regard to your other posts about this problem, my guess is that you
> have some underlying error causing this that isn't part of SQLite.  The
> expected cause of this error is if someone deletes your database file (or a
> journal) while you have the database open.  There are other errors which
> can make the file handle invalid.  But I suspect that just as you wrote
> about your RAM, you actually have a hardware or OS problem which is the
> real culprit.
>

That very well may be, but I want to be able to respond appropriately in
the application if this happens to a user, who may also have a similar
hardware problem (in that PCs that run slow or are bogged down with RAM use
from Firefox, e.g., are incredibly common).

One "hypothesis"  (this is totally naive, so may be really off base) I have
is that if the CPU use / RAM use is high, then the various processes are
not getting their chance to write to the hard drive, and so if SQLite tries
to write it is put in a queue and is getting "timed out".  Then, if I try
again in a few seconds, now the other processes have had a chance to write
and SQLite gets write access right away.  If that is the case, that seems
like something that can be worked around...I'm just not sure how to
determine if it is the case and how to work around it if it is.
___
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

[C M]

On Sun, Sep 1, 2013 at 4:28 PM, jose isaias cabrera
<cabr...@wrc.xerox.com>wrote:

>
> "C M" wrote...
>
>
>  Keeping it simple:
>>
>> I have a Python application that uses SQLite, and I randomly get this
>> error:
>>
>> "SQL logic error or missing database"
>>
>>  Is the database in network drive or not in the same machine that is
> running the app?
>

The database is a file on the same hard drive that the app is on.  It's all
on a one laptop.
___
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

[C M]

On Sun, Sep 1, 2013 at 2:23 PM, Simon Slavin  wrote:

>
> On 1 Sep 2013, at 6:38pm, Richard Hipp  wrote:
>
> > A good starting place might be to tell us what the program is doing when
> > the error comes back.
>
> As well as telling us the call that trieggers the error, please put logic
> into your program so that it not only checks the result code of the call
> that generates the error but also checks to see that all earlier SQLite
> calls return SQLITE_OK when you expect them to.  Often the call that
> returns the error is after the one that caused the problem.
>

Do you know how I can do that with Python?  For example, I tried this:

status = cursor.execute("some SQL statement here")
print "The status is: ", status

But it prints the cursor object:

> The status is 
___
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

[C M]

On Sun, Sep 1, 2013 at 1:38 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Sun, Sep 1, 2013 at 1:12 PM, C M <cmpyt...@gmail.com> wrote:
>
> >
> > I have a Python application that uses SQLite, and I randomly get this
> > error:
> >
> > "SQL logic error or missing database"
> >
> > I have no idea how to figure out what's wrong,
> >
>
> A good starting place might be to tell us what the program is doing when
> the error comes back.
>

I'm not 100% sure, because there are several SQLite calls that it could be
tripping up on at that part of the program's use.  But all of them are
basic SQL, and at the point in the code where I see the error, it is either
SELECT or INSERT INTO statements.  (If it becomes necessary, I can try to
put in a way to know right where it happens, though I'm not sure how to do
that...see my response to Simon Slavin).

Importantly, the exact same part of the code will work on one occasion, and
on another occasion with the same "state" of the application, will not
work.  That is, I can run my application, try something basic from a fresh
start, this error will occur, close the application, try the same exact
thing again, and that time it will work fine.  It occurs rather
infrequently, but the point is I want to make sure it never occurs.

The only thing I've noticed is that *maybe* it is more likely to happen
when my laptop's RAM is pretty filled up with other processes (such as when
Firefox runs high RAM and lots of other applications are open).  Could that
put us on the trail of what could be going wrong?

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

[sqlite] help needed for major SQLite problem

[C M]

Keeping it simple:

I have a Python application that uses SQLite, and I randomly get this error:

"SQL logic error or missing database"

I have no idea how to figure out what's wrong, and if I can't figure it
out, it leaves a huge deal-breaking bug in my application, such that I'd
have to abandon SQlite and use another RDMS.  I'd hate that, as I love
SQLite and it is integrated deeply with my application.  But I can't have a
database application that occasionally just doesn't work, and I've put
years of work into this project and really need it to be robust.

Any help on this could potentially make a huge positive difference in my
efforts.  Thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL logic error or missing database with Python sqlite

[C M]

This appears to have been asked many times online, but none of the
threads I've seen help me fix my issue.

I am using SQLite through Python 2.5 or 2.7, which is the sqlite3
module.  In a desktop application, every now and then, and in a fairly
irreproducible way, when committing to the database I get this error:

sqlite3.OperationalError: SQL logic error or missing database

I thought this was a PySqlite generated error, but now I see the same
error is seen with Ruby, PHP, C++ and other languages, so now it seems
it is generated by SQLite itself...but I really don't know.

If I try additional times in that same instance of my app being open,
it gives me the same error every time.  If I close the app and re-open
it, it probably will not give me this error, with the same or very
similar data being written in the same routines.  So I "know" that the
code as written is correct (a significant--greater than 90%?--of the
time I don't see this error).

In terms of what is causing this, I don't know, but I've noticed that
on the occasions that this has happened my computer's RAM was pretty
bogged down.  I am testing all this on a 2004 laptop with a very
fragmented HD with 1 GB of RAM, and I had the following processes
running with this much RAM consumed:

- Firefox.exe: 297,000 K (and climbing...This is FF 17)
- plugin-container.exe:  51,260K.
- Dropbox.exe: 30,212 K.
- explorer.exe:  22,836 K
- pythonw.exe: 18,432 K
- svchost.exe: 16,992 K
- notepad.exe: 5,532 K
- and others...

When there is this much RAM being used, particularly when FF is
hogging it like that, my computer gets quite slow to respond to any
action, such as saving a file (sometimes taking more than 10 seconds
to return responsiveness).

Is it possible that the "SQL logic error or missing database" error is
due to the issue of low available RAM?  Or the fragmented HD?  Or
something like that?  (I get the impression from reading online that
this is a very general error that could be due to just about anything,
and as such gives just about no information).  The problem is, I am
currently sitting with FF over 400,000 K and I just successfully used
my app, so it truly is intermittent.

This error might go away if I used a newer/cleaner/more RAM computer,
but I want to "stress test" my application for those who may be using
similarly clunky computers--I want to try to avoid it even for older
model computers.

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

Re: [sqlite] MIN() for a timedelta?

[C M]

Thanks everyone for the various replies and help.  Very useful and I
will look into the differences and if I have questions about how these
work will let you know.

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

Re: [sqlite] MIN() for a timedelta?

[C M]

On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams <n...@cryptonector.com> wrote:
> On Thu, Jul 26, 2012 at 4:32 PM, C M <cmpyt...@gmail.com> wrote:
>> I could zero pad these strings myself, so that '9:00:00.00'
>> becomes '09:00:00.00', but that would break other uses of these
>> values in my code and was wondering if there were a way in SQlite to
>> "see" these values as timedeltas.  I tried this:
>>
>> SELECT MIN(TIME(duration) FROM Durations
>>
>> but that returns nothing.
>
> Just use CASE to add the missing zero as necessary, something like this:
>
> SELECT strftime('%s', (SELECT CASE WHEN '9:12:32' LIKE '0%' THEN
> '9:12:32' ELSE '0' || '9:12:32' END));
>
> Replace '9:12:32' there with whatever expression, probably a column name.

Thanks but I can't figure out how to use that to get the MIN()
timedelta.  For example, if I try this (the table is called
Durations...the column is duration):

SELECT  MIN(CASE WHEN duration LIKE '0%' THEN
duration ELSE '0' || duration END) FROM Durations WHERE duration != ''

it returns:
01:00:00:00

which is definitely not the min timedelta in the table.

If I try:

SELECT  (SELECT strftime('%s', (SELECT CASE WHEN duration LIKE '0%' THEN
duration ELSE '0' || duration END))) FROM Durations

It returns a large number of results, including negative values like -413362495.

I'm really lost on how to combine the above into a sensible query to
get the minimum timedelta.

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

[sqlite] MIN() for a timedelta?

[C M]

I have string representations of a Python timedelta stored in an
SQLite database of the form H:MM:SS:ss (the last is microseconds).
 Here are a possible examples of such timedeltas:

'0:00:06.229000'
'9:00:00.00'
'10:01:23:041000'

I want to select the shortest duration (the smallest timedelta) using
the SQLite MIN(), like so:

SELECT MIN(duration) FROM Durations

The problem is, in Python, the string representation of the timedelta
is not left zero padded, so '9:00:00.00' (nine hours) is selected
by MIN() as greater than '10:01:23:041000' (ten hours and change).
This is not right in terms of time, as 9 hours is smaller than 10
hours.

I could zero pad these strings myself, so that '9:00:00.00'
becomes '09:00:00.00', but that would break other uses of these
values in my code and was wondering if there were a way in SQlite to
"see" these values as timedeltas.  I tried this:

SELECT MIN(TIME(duration) FROM Durations

but that returns nothing.

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

Re: [sqlite] catching sqlite errors...what to know

[C M]

Thanks for the help, just getting back now.

> Your message indicates you are using Python.  You should be using
> transactions and context managers.  This ensures that things either
> complete or don't, not some half way state.

I thought SQlite never did anything in a half way state anyway?  I
thought that if something failed to be written it was rolled back?
(Pardon if I'm mangling the terminology or misunderstanding it badly).

> Finally you should have a test suite for your code.  The good news is that
> it is fairly easy to cause some of the errors.  For example if you would
> like a database to be locked then open it in a new connection and execute
> "BEGIN EXCLUSIVE".

Any idea how to produce the other errors?  Like the Disk I/O error
given by Python's sqlite3 module?

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

Re: [sqlite] catching sqlite errors...what to know

[C M]

On Sun, Jul 1, 2012 at 12:12 PM, Simon Slavin  wrote:

Thanks for the help.  Sorry to not get back sooner.

>> 1) locked database (due to using SQLite Database Browser to change a
>> database but not Saving it)
>> 2) sqlite3.OperationalError: SQL logic error or missing database
>> (happens very rarely.  No idea why)
>> 3) sqlite3.OperationalError: disk I/O error (also happens very rarely.
>> No idea why)
>
> You already know the cause of number (1).  I bet the same thing is causing 
> (2) and (3).

I don't think a locked database (1) is causing (3), because I have
observed the disk I/O error when the database is clearly not locked.
It is also intermittent.  I suspect it might be a real problem with my
hard disk, which is old and nearly filled.  (Which is actually useful,
since I'd love to have a solution to this issue for users with
similarly decrepit hard drives!).

> One of the problems with reporting them here is that they are not SQLite 
> errors (i.e.
> not one of the error codes listed here

> 

> ) but seem to be errors generated by a framework or API you're using.

Yes, these are the errors generated by the sqlite3 module in Python
(formerly, I think, called pysqlite).  There is probably a mapping out
there somewhere between SQlite errors and these, and so I'll try to
find it.

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

[sqlite] catching sqlite errors...what to know

[C M]

I'm working on a database program and have found three different kinds
of SQLite errors now and then, only 1 of which is known as to what is
causing it:

1) locked database (due to using SQLite Database Browser to change a
database but not Saving it)
2) sqlite3.OperationalError: SQL logic error or missing database
(happens very rarely.  No idea why)
3) sqlite3.OperationalError: disk I/O error (also happens very rarely.
 No idea why)

So, because these errors, if not handled, lock up my application, I
have to go through the entire program and find every read or write to
it and make contingencies for these--or other--errors.  To assist
that, I've put all database access in a wrapper function so that all
accesses go through that function and I can catch exceptions.
However, I still have to take a lot of time to go through all the code
and make sure the code can handle whatever that wrapper returns when
there is an error.  This will take a lot of time.

Therefore, to make sure I'm approaching this task right, what I'd like
to know from this list are answers to a few relevant questions...

- What might be (likely) causing errors (2) and (3)--and how could
they be prevented?

- What other common errors should I be alert for (though these are the
only ones I've ever seen)?

- I'd like to be able to potentially save a great deal of work by
testing that there is an unlocked connection to a working database as
a first step in each module *before* a lot of code runs, and if it
fails, then just stop before I have to back out of a lot of GUI code,
etc.  How "risky" is that shortcut? (that is, how much chance is there
of a database error happening anyway once I've done that initial
check?)

- Any pointers to design principles for database applications (future
ones, for me) that make managing possible errors most efficient?

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

Re: [sqlite] SELECT average timestamp to get average time of day? (C M)

[C M]

On Wed, Feb 29, 2012 at 8:41 AM, Mark Belshaw
wrote:

> This is the first time I've posted a response to any mailing list, so I
> hope
> I'm doing it right and it appears where it should!
>

It sure did.  Thanks for participating.


> Not SQLite, but a technique we use in our Time & Attendance system to help
> with this sort of thing, where employees work nights / days and rotating
> shift patterns, is to include a Boolean "After Midnight" with each time. We
> are not looking for averages, so I won't get into the SQL you would use for
> that, but are trying to keep transactions for the same shift together,
> where
> they may be from different, consecutive, calendar days.
>
> So, an early morning transaction for an early morning shift would have, say
> False | 04:35, whereas an early morning Out from a night shift would be
> True
> | 05:02.
>

That's an interesting angle.  I may actually already be doing something
like that inadvertently, because I am processing different times of day and
likely events during them separately.  In other words, I might only handle
the 8pm--4am block in one action, which would be equivalent to your True
("After Midnight") case.

The arithmetic then becomes something like ([Out After Midnight * 24] + Out
> Time) - ([In After Midnight] * 24] + In Time). I guess it's really just a
> variation on the 'add 12 hours, do your stuff, then take it off again'
> method but, if you can determine the required state of After Midnight at
> the
> point of data collection, it gives you a method to persist this state to
> the
> database, rather than having to unpick each one on the fly.
>

I'll think about how to incorporate that in what I'm doing.

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

Re: [sqlite] RE SELECT average timestamp to get average time of day?

[C M]

On Sat, Feb 25, 2012 at 8:44 AM, Black, Michael (IS)  wrote:

> I subtract 12 hours...so any time from 24:00:00 to 12:00:00 will work.
>
> Time from noon to noon becomes midnight to midnight.  Then you just add
> the 12 hours back in.
>
>
>
> CREATE TABLE tijd(t  int(11));
> INSERT INTO "tijd" VALUES('2012-02-25 22:00:00');
> INSERT INTO "tijd" VALUES('2012-02-27 01:00:00');
> INSERT INTO "tijd" VALUES('2012-02-27 23:00:00');
>
>
>
> sqlite> select time(avg(time(t,'-12 hour')+12)*3600,'unixepoch')  from
> tijd;
> 23:20:00
>
>
>
> You may pick an offset other than 12 depending on your data.
>

Thank you, this should work well for me, and it is good to see how one
should write it as an SQLite query.

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

Re: [sqlite] SELECT average timestamp to get average time of day?

[C M]

On Fri, Feb 24, 2012 at 4:53 PM, Marc L. Allen
wrote:

> You're trying to calculate it for individual people?  Can you count on
> night-time people to stay night-time, or do you need to worry about someone
> shifting by 12 hours?
>

It's for individuals, and it is possible for individuals to shift or drift
by any amount.

>
> If not, your best bet is, for the night-time people, add, say 6 hours to
> all of their times, do your average, then subtract the 6 hours back out.
>

Yes, this is a good idea, the same as was given in another response.
Thanks.

I found that this type of measure is referred to as the "mean of circular
quantities", and there is even a Wikipedia page about that...I had just
never thought about it before.  I also found the Mitsuta Method for dealing
with this type of issue.  But in any approach, things break down if data is
strewn all over a 24 hour period.


> There are cases where this will fail, but you might be able to detect data
> sets that will cause this issue and ignore them.
>

I will have to just come up with a reasonable check of the data's variance
and if I find it is all over the clockface, let the user know that the mean
bedtime can't really be computed due to the erratic data.  Maybe if only a
few outliers are found I could filter them out.   I may post a follow-up
question regarding that.

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

Re: [sqlite] SELECT average timestamp to get average time of day?

[C M]

On Fri, Feb 24, 2012 at 4:06 PM, Marc L. Allen
wrote:

> Actually this is quite an interesting question.
>
> Given two fixed times of midnight and noon, having the third time one
> minute before or after midnight drastically changes what I think you want
> the answer to be.
>
> Midnight, Noon, 12:01 AM -> Average around 2AM.
> Midnight, Noon, 11:59 AM -> Average around 10PM
>
> Can you provide more information on what you're trying to do?
>

Yes, I'm trying to come up with average bedtimes.  (Or other
once-a-day-and-usually-in-the-same-general-time-window type human
activities).

So, in my case, I might go to bed at 23:00 one night and then 01:00 the
next, so the "naive average" there would be 23 + 1 = 24/2 = 12:00, or
noon.  But of course, to say I was going to bed around noon would not
represent the situation at all.  Instead, just as you said, I want in this
case 01:00 to be treated as 25:00, so 23 + 25 = 48/2 = 24:00, or midnight.

The thing is, other people might have bedtimes at mid-day, if they work
nights, for example, so it's hard to know how to approach this.

(One visual metaphor I have is to think about the times on an analog clock
with hands.  For 11pm and 1am, the "average angle" of the big hand is
12:00am.)

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

[sqlite] SELECT average timestamp to get average time of day?

[C M]

I'd like to have a SELECT query to get the average time of a person's day
(not necessarily a strict 24 hour day) given timestamps of the form:
'-MM-DD HH:MM:SS.mm'.  The data will have gaps of days in which
there is no timestamp for that day.

The problem is, simply averaging times of day gets into a problem if times
cross midnight and days are non-consecutive.  For example, the average I'd
want from these three timestamps:

'2012-02-18 22:00:00.00'
'2012-02-19 23:00:00.00'
'2012-02-28 01:00:00.00'

Should be 11:20pm, as they are all within a few hours of each other at
night.  I have not been able to find a query that produces this.

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

Re: [sqlite] corrupt database with update?

[C M]

On Thu, May 8, 2008 at 7:08 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> On May 8, 2008, at 2:54 AM, C M wrote:
>
>> How likely (or possible) is it to corrupt or in some way screw up an
>> SQlite database if one is doing an UPDATE and it fails? (computer goes
>> out, etc.)  Thank you.\
>
> http://www.sqlite.org/atomiccommit.html
>
> D. Richard Hipp
> [EMAIL PROTECTED]

Thank you--that's a lot of useful information.
Che
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] corrupt database with update?

[C M]

How likely (or possible) is it to corrupt or in some way screw up an
SQlite database if one is doing an UPDATE and it fails? (computer goes
out, etc.)  Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re: select date using variables in Python

[C M]

On Dec 12, 2007 1:51 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:

> C M wrote:
> >
> > Thanks to you both--using the || did the trick, and I can try the other
> > approaches mentioned as well.  In Python Igor's suggestion was just:
> >
> > amount = "+1"
> > cur.execute('SELECT string, d FROM test WHERE d >= date("now", ? || ? ||
> "
> > days")',amount)
> >
> >
> You will might be better off building the entire argument string in
> Python and then binding that string to a single argument to the date
> function in the SQL. You can then use the same query to do offsets in
> hours, days, months, or years for example. It will probably be slightly
> faster as well since SQLite will be parsing an executing a simpler SQL
> statement.
>
> You should also invert your string quoting in Python. SQL uses single
> quotes for literal values, and double quotes for identifiers (such as
> table and column names). SQLite accepts double quotes around literals as
> an extension, but it can get you into trouble if you have a column with
> the same name as your literal value. In that case it will be interpreted
> as the column name. You might even want to use Python's triple quotes to
> allow both single and double quotes to be used in the SQL string itself.
>
> Note, the plus character in '+1 days' is not needed. It is simply a sign
> character, it does not signify addition. You only need the sign if you
> want a negative offset.
>
> amount = 1
> unit = "days"
> offset = str(amount) + " " + unit
> cur.execute("SELECT string, d FROM test WHERE d >= date('now', ?)",
> offset)
>
>
> HTH
> Dennis Cote
>

Thank you--very useful to know.

Re: [sqlite] Re: select date using variables in Python

[C M]

On Dec 12, 2007 8:20 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> C M <[EMAIL PROTECTED]> wrote:
> > cur.execute('SELECT string FROM test WHERE d >= date("now","+1 day")')
> >
> > However, I'd like to make it flexible, so that a user can put in an
> > amount of days forward or backward and the query will use
> > that--basically I want the user to be able to select the date range
> > over the data in the table.  I tried something like:
> >
> > amount = "1"  #just to try it, later this will refer to a user-chosen
> > variable
> > cur.execute ('SELECT string FROM test WHERE d >= date("now", "+",?,"
> > day")',amount)
> >
> > But of course that's not right and it doesn't work.  What is the right
> > syntax in this case to use the ? to stand for the 1 in the original
> > "+1 day" portion?
>
> I don't know Python well enough, but the SQL statement you want would
> look like this:
>
> SELECT string FROM test WHERE d >= date('now', ? || ' days');
>
> || is the string concatenation operator in SQL.
>
> Igor Tandetnik
>

Thanks to you both--using the || did the trick, and I can try the other
approaches mentioned as well.  In Python Igor's suggestion was just:

amount = "+1"
cur.execute('SELECT string, d FROM test WHERE d >= date("now", ? || ? || "
days")',amount)

[sqlite] select date using variables in Python

[C M]

I'm new to SQLite and can't figure out the right way to write this. I want
to select a range of dates, let's say anything beyond tomorrow So in my
table called test I want to select the column called string based on the
date being tomorrow or later...

This statement (from the sql wiki about dates) in my Python code works:

cur.execute('SELECT string FROM test WHERE d >= date("now","+1 day")')

However, I'd like to make it flexible, so that a user can put in an amount
of days forward or backward and the query will use that--basically I want
the user to be able to select the date range over the data in the table.  I
tried something like:

amount = "1"  #just to try it, later this will refer to a user-chosen
variable
cur.execute ('SELECT string FROM test WHERE d >= date("now", "+",?,"
day")',amount)

But of course that's not right and it doesn't work.  What is the right
syntax in this case to use the ? to stand for the 1 in the original "+1 day"
portion?

Or am I barking up the wrong tree with this approach?  Ultimately I want to
make it totally generalizable, so that users can select whatever range of
dates they want, and so I thought I needed a way to sub in the variable of
#of days--just not sure how.

Any help is appreciated.

Re: [sqlite] Request for help with the SQLite Website

[C M]

I agree; less is more.  Way too many words on the front page now.

First, why have nav bars at top AND at the right side?  (Plus vertical nav
bars are best put on the left side. )

I'd recommend just (something like) this text on the main page:

SQLite is a free, public domain, compact, embedded SQL database engine that
works from a single disk file.  It's reliability, ease of use, and small
size has led it to be adopted in more applications than we can count,
including several high-profile projects. 
More technically, SQLite is a in-process library that implements a
self-contained ,
serverless,
zero-configuration ,
transactionalSQL database
engine, and transactions are
ACID  even if interrupted by system
crashes or power failures.  Click here to learn more.

If there are to be more graphics on that page, some small logos of the
projects
using SQLite might be both brighten things up and enhance the attractiveness
of the product.

Re: [sqlite] best way to match a date but not a time?

[C M]

Thanks, Simon, Trey, and Dan, this is really helpful and has got me
back on track.  -CM

On 9/2/07, Trey Mack <[EMAIL PROTECTED]> wrote:
>
> > I want to have queries that will match dates but not care about times.
> > The date might be today, anything within the last week, month, year,
> > or a range of dates.  I'm using Python's datetime function, so the
> > dates enter the database in this format 2007-09-01 12:00:02.
> >
> > So far, < or > queries seem to work, like:
> >
> > SELECT duration FROM specactivities WHERE date < "2006"
> >
> > but what I can't do is use =, since it seems like it is trying to match
> > both the date and the exact time.
>
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
>
> You're storing dates as TEXT, not DOUBLE, correct?
>
> WHERE date(colName) = '2007-09-01' should work to match a particular date.
> Be aware though, this approach disables the use of indices. So, if you
> have
> an index that will be used with < or > queries you mentioned before, the
> specific date-match with date(colName) will be slower because it has to do
> a
> full table scan.
>
> Perhaps this would be better:
>
> SELECT duration FROM specactivities WHERE date >= '2007-09-01' AND date <
> '2007-09-02'
>
> To the experts: will an index be used for both comparisons in the WHERE
> clause? Or just the first? I think I remember reading somewhere that an
> index can be used for any number of exact matches, but only 1 less-than or
> greater-than comparison, and that would be the last usable column of the
> index. Or maybe it could be used for >= AND < on the same column at the
> same
> time, but that would be the last usable column of the index?
>
> HTH,
> Trey
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

[sqlite] best way to match a date but not a time?

[C M]

Hi, I'm very new to SQLite, and I'm using it with Python.

I want to have queries that will match dates but not care about times.
The date might be today, anything within the last week, month, year,
or a range of dates.  I'm using Python's datetime function, so the
dates enter the database in this format 2007-09-01 12:00:02.

So far, < or > queries seem to work, like:

SELECT duration FROM specactivities WHERE date < "2006"

but what I can't do is use =, since it seems like it is trying to match
both the date and the exact time.

Any help is appreciated.