Whatever format you choose to store it in, I highly recommend storing the UTC
time. It might be a little more work, but:
1. your program can display the correct local time, even if the
database/app/user is in/changes to another timezone
2. you won't have to deal with seeing two 1:30am on the
In the documents, using PRAGMA synchronous 1 (NORMAL) is supposed to be safe
unless there is an OS crash or power failure.
I've had a few customers (about one a month, out of thousands) that is getting
the "database disk image is malformed" ExtErr=11 error with the NORMAL pragma,
and they
ilto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, March 2, 2015 3:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA Synchronous safety
On 2 Mar 2015, at 4:45pm, Doug Nebeker wrote:
> 1. Is setting synchronous to FULL necessary to prevent the
The documentation says that sqlite3_mutex_try will always return SQLITE_BUSY
for "some systems (for example, Windows 95)".
That's not quite accurate from what I see in the latest implementation of
winMutexTry. It will ALWAYS return SQLITE_BUSY for any Windows usage, making
the existence of
I'm hoping this might be of help to contribute to SQLite's robustness.
We've got thousands of SQLite installations and they almost always work
flawlessly. Every once in a while we get a corruption error and I finally have
a log that catches it.
SQLite has been in use in the project since
> For obvious security reasons all allocations from the Operating System are
> pre-initialized to 0x00.
Time to bash Windows, but according to the docs for HeapAlloc, memory is not
automatically initialized to 0
https://msdn.microsoft.com/en-us/library/windows/desktop/aa366597(v=vs.85).aspx
> > Yes I did the same experiment with a lock that made thread A wait
> > until B was finished. So actually only one thread can be active at
the time.
> > I don't see how the outcome of this experiment can be of any
> > interest, as there is no time reduction any longer. But your guess
is
>
I must be missing something obvious and I'm hoping someone can help me
out.
I have an existing table and add a new column:
ALTER TABLE xyz ADD COLUMN newcol TEXT;
Next I want to set some default values to the new column. Because this
code could potentially get executed later, I'm trying to
I'm wondering if someone can help me understand why a table scan is
being done for the SQL below even though I've created what I thought
would be appropriate indices.
When I do a EXPLAIN QUERY PLAN on the following SQL:
SELECT S.ScanID S.Date, S.RootDirID, S.OwningMonitorID, D.Path
FROM Check
I have a generic SQL question for the less-newbie-than-me out there.
Imagine I have the following data:
TimeStamp | Person | Data1
---
1 | Doug | X
2 | Doug | Y
2 | Fred | A
3 | Doug | Z
4 | Fred | B
How do I set all
]
Sent: Monday, October 30, 2006 5:08 PM
To: SQLite
Subject: [sqlite] Re: SQL that is stumping me
Doug Nebeker <[EMAIL PROTECTED]>
wrote:
> Imagine I have the following data:
>
> TimeStamp | Person | Data1
> ---
> 1 | Doug | X
> 2
URL didn't work for me either, but you'll see what you want at the root
(at least today):
http://www.cesarodas.com/
-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED]
Sent: Monday, March 05, 2007 10:44 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Lemon example
The
Be aware that the Windows GetTickCount call has a resolution of 10 to 15
ms on most machines, so that could throw throw your timings off if
you're timing each individual test case as it appears below. To get
better timer resolution, use QueryPerformanceCounter.
Now, would that make SQLite3 looks
I have a simple C++ HTTP server that I wrote (all in a single .cpp and
.h file). Multi-threaded, has a session concept (via cookies), can
parse POST and GET variables. It is a C++ class that you can derive
from and then override the LoadFile method (which is where I typically
put my
-users@sqlite.org
Subject: Re: [sqlite] uSQLiteServer Source code available
Doug Nebeker wrote:
>I have a simple C++ HTTP server that I wrote (all in a single .cpp and
>.h file). Multi-threaded, has a session concept (via cookies), can
>parse POST and GET variables. It is a C++ class tha
private
account (admin AT poweradmin DOT com) to keep from bothering the list.
-- Doug
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 15, 2005 10:48 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] uSQLiteServer Source code available
Doug
I've written a C++ wrapper for SQLite which has been working great.
Lately though I've started getting what might be deadlocks when running
two threads against the same database. One thread is a reader and the
other is a writer (at the moment, I'm getting 'database locked' errors,
but that's
eeds. One needs to release the locks it holds. Rolling
back is the way to do that.
--Ned.
http://nedbatchelder.com
-Original Message-----
From: Doug Nebeker [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 30 November, 2005 11:04 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrency handlin
Also be sure to #define THREADSAFE in your project settings if you'll be using
multiple threads.
From: Mike Marshall [mailto:[EMAIL PROTECTED]
Sent: Tue 12/13/2005 2:17 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Trying to compile under VC.NET
Just
I used to have the same issue. I finally did two things:
1. The background worker thread is at least normal priority. If you own
the DB, you need to get in and get out. I put sleeps in to make sure I
wasn't hitting the DB too often from this thread
2. All connections to the database happen
Two comments:
* This should only be used for Windows CE as-is. On Windows
XP/2000/2003(?)/Terminal Services you should probably add "Global\" to
the front of the mutex name so the lock is truly system wide.
Unforunately, you'd have to query Windows to see whether that prefix
could/should be
This group is a wonderful resource. Some day I'll have enough
experience to contribute in a meaningful way...
I have a small number of read and writer threads. A few months ago I
had some deadlock issues and one simple solution was to run all
transactions as exclusive (ie BEGIN EXCLUSIVE).
When you don't wrap everything in a transaction, each statement becomes
it's own transaction. And the database file is opened, updated, and
closed on each transaction. So your first case had roughly 50 times the
amount of file I/O and transaction startup/commit overhead as the second
case.
>From http://www.hwaci.com/sw/sqlite/whentouse.html
"Situations Where Another RDBMS May Work Better"
* High Concurrency
SQLite uses reader/writer locks on the entire database file. That means
if any process is reading from any part of the database, all other
processes are prevented from writing
What does the code look like that is calling sqlite3_prepare? The crash
point is just dereferening a pointer that you passed in, and it looks
like the pointer value must be bad.
> int sqlite3_prepare(
> sqlite3 *db, /* Database handle. */
> const char *zSql, /* UTF-8
Pre-compiled Windows DLL: http://www.sqlite.org/sqlitedll-3_3_5.zip
Source for Windows: http://www.sqlite.org/sqlite-source-3_3_5.zip
You can compile the source (I happen to do it with Microsoft Visual
Studio) and get the DLL as well.
The links above are available on the following page:
I appologize for asking what might be a dumb question, but here goes:
I have a wrapper class that I use for SQLite that I'm quite happy with.
I've decided that if it is compiled for DEBUG usage, I want it to do an
EXPLAIN on every SQL statement and then check the output to see if any
table scans
Thanks Dennis.
So does that mean if I get "TABLE xyz" in the 'detail' column back and
it does NOT mention an index that a full table scan is taking place?
I found some info about EXPLAIN QUERY PLAN
(http://www.sqlite.org/cvstrac/wiki?p=QueryPlans) but haven't found out
exactly what the response
I can't comment on Anne's situation, but I too think there is something funny
going on with charsets because of one small data point:
I open and use a database using the '16' APIs (ie sqlite3_open16,
sqlite3_prepare16, etc). When I tried to run an EXPLAIN QUERY PLAN statement
(like "EXPLAIN
The problem with that solution is that it assumes all database access
happens from within a single process. As far as I understand it, SQLite
allows database access from multiple processes (and even from remote
processes I assume) and thus the locking has to happen outside of the
process. In
You might be surprised at the speed increase you see in compile time if
you've got large projects. The time isn't lost to CPU as much, but disk I/O
time adds up when hitting many hundreds of small (header) files (even with
an SSD).
Doug
-Original Message-
From:
This is a tough one to answer because there is so much context to consider.
SQLite, or any database, could easily solve the problem you mention (storing
values that can change without needing to recompile your program).
Whether it's more efficient is another question. If you think you'll
I was reading about the new query planner and came across a few references to
that idea that the left most columns in the index definition should be the most
unique (as far as values in the column are concerned).
Is that correct? In my case, many tables have a timestamp column, and I've
been
...@sqlite.org]
On Behalf Of Simon Slavin
Sent: Tuesday, August 27, 2013 7:29 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CREATE INDEX and column order
On 27 Aug 2013, at 1:07pm, Doug Nebeker <ad...@poweradmin.com> wrote:
> I was reading about the new quer
Igor is naturally correct. One additional thing to keep in mind - the commit
phase of a transaction is where a lot of work gets done (meaning slow disk
access). So if you have a lot of INSERTs or DELETEs to do, doing many within a
transaction will give you better performance.
Doug
> UTC is "the right time." If you're doing anything with dates and
> times I would STRONGLY recommend that all recorded times are in UTC.
Jay is right. I've been bitten by storing local times before. Even if your
users
are in the same time zone, that time zone shifts with day light savings.
Been using SQLite for a long time and a huge fan. We occasionally see database
corruption on a local NTFS Windows drive and I've been trying to figure it out.
I finally have some logs from the SQLITE_CONFIG_LOG callback that may be of
help:
(11) database corruption at line 78267 of
Our experience might not apply completely, but we just went through moving to
VS2015, but because of all of the vcredist issues with the new version
(involving the UCRT), we had to go back to VS2012. Note that installing
vcredist:
“To install this update, you must have April 2014 update
This isn't exactly the same, but you can find out what other applications have
a file open (SQLite database file in this case) using the following Windows
APIs:
RmRegisterResources
RmGetList
That's what I use to help diagnose locking issues.
Doug
-Original Message-
From: sqlite-users
Is it just a matter of using sqlite3_create_function to register a function
that guarantees it will concatenate in the order rows are received? Would that
guarantee that your example works, or is order no longer guaranteed once they
leave the inner select?
SELECT group_concat(LineText, '\n')
Imagine a table that holds individual lines of text documents:
CREATE TABLE DocLines
(
DocID INTEGER,
LineIndex INTEGER,
LineText TEXT
);
INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (1, 1, 'Mary had a
little lamb');
INSERT INTO DocLines (DocID, LineIndex,
41 matches
Mail list logo
