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') F
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, Lin
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
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 rollup
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 [3d862f207e
> 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
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 versi
e.org
[mailto: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 p
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 claim
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 da
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 th
I've got a very simple example table and index:
CREATE TABLE DevProps
(
CompID INTEGER NOT NULL,
PropID INTEGER NOT NULL
);
CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, PropID);
When I check the query plan for this statement:
SELECT CompID FROM DevProps WHERE PropID=33
it
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
-Origi
...@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 wrote:
> I was reading about the new query planner and came across a
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 u
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 hav
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: sqlite-users-boun...@sqlit
> 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.
Thank you Igor and Tomash.
-Original Message-
From: Tomash Brechko [mailto:[EMAIL PROTECTED]
Sent: Friday, May 18, 2007 2:14 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is the column value after ALTER TABLE ADD
COLUMN?
On Fri, May 18, 2007 at 14:00:21 -0500, Doug Nebeker
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 be
> > 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
> >
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
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 U
ECTED]
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
&g
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 D
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 use a wrapper that accepts a string of one or more SQL statements. I
scan
through the string looking for "INSERT", "UPDATE" or "DELETE". If I see
any
of those values, I start the whole thing with a "BEGIN IMMEDIATE;". If
none
are found I assume it is a SELECT (read only) and start with a "BEG
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 proc
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 QUE
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 co
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
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:
http://
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 enc
>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 an
It sounds to me that he isn't saying it is leaking--it simply isn't
releasing memory after a SELECT statement finishes (is finalized). This
might be by design. I would expect the data to be released if the
database connection is closed, but not necessarily after each SELECT.
It would be nice to h
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.
-
I'm currently seeing something that has never happened--about 90 master
journal files (the -mjX type) that are popping up in my
database directory. I'm using 3.2.7 or 3.2.8 (have to double check) on
Windows.
The scenario:
I have two threads that are reading and writing to the database pretty
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). Th
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 added
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 ins
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 sw
he other needs. 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] Concurren
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 after
ter to send it to my 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
To: sqlite-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++
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 command/trans
I was reading through the wiki yesterday and came across the following:
* Make sure you're compiling SQLite with -DTHREADSAFE=1
I didn't know about that #define! I know about the defines that are
related
to the pragmas, but are there others (well, dumb question, of course
there
are). Is there a
I'm trying to figure out why my deletes are taking so long. It _might_
have started with 3.2.5, but not sure.
I'm using 3.2.5 on Windows XP SP2 on a very fast machine. The XInfo
table (shown below) has quite a few indices because it is selected on in
every imaginable way:
CREATE TABLE XInfo
(
48 matches
Mail list logo