Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-04 Thread Doug Nebeker
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')

[sqlite] Ordering a GROUP BY, or other concatenating tricks?

2017-10-03 Thread Doug Nebeker
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,

Re: [sqlite] SQLite - how to get number of active connection?

2016-09-10 Thread Doug Nebeker
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

Re: [sqlite] Failed to install Microsoft Visual C++ Runtime

2016-08-02 Thread Doug Nebeker
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

[sqlite] Database corruption question

2016-07-20 Thread Doug Nebeker
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

[sqlite] Article about pointer abuse in SQLite

2016-03-23 Thread Doug Nebeker
> 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

[sqlite] Rare database corruption - does this help?

2015-09-10 Thread Doug Nebeker
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

[sqlite] PRAGMA Synchronous safety

2015-03-02 Thread Doug Nebeker] 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

[sqlite] PRAGMA Synchronous safety

2015-03-02 Thread Doug Nebeker
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

Re: [sqlite] Best Practice: Storing Dates

2015-01-14 Thread Doug Nebeker
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

[sqlite] Documentation error: sqlite3_mutex_try

2014-02-15 Thread Doug Nebeker
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

Re: [sqlite] To BEGIN or not to BEGIN. That is the question...

2013-08-30 Thread Doug Nebeker
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

Re: [sqlite] CREATE INDEX and column order

2013-08-28 Thread Doug Nebeker] 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 quer

[sqlite] CREATE INDEX and column order

2013-08-27 Thread Doug Nebeker
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

Re: [sqlite] Beginning database question

2013-04-17 Thread Doug Nebeker
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

Re: [sqlite] VC++ and SQLite

2012-11-12 Thread Doug Nebeker
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:

Re: [sqlite] sqlite time is 2 hours to late

2012-06-27 Thread Doug Nebeker
> 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.

[sqlite] What is the column value after ALTER TABLE ADD COLUMN?

2007-05-18 Thread 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

RE: [sqlite] One more SQLite threading question

2007-05-18 Thread Doug Nebeker
> > 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 >

RE: [sqlite] SQLite v/s SQLite3 Performance Assay

2007-03-13 Thread Doug Nebeker
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

RE: [sqlite] Lemon example

2007-03-05 Thread Doug Nebeker
URL didn't work for me either, but you'll see what you want at the root (at least today): -Original Message- From: Cesar Rodas [mailto:[EMAIL PROTECTED] Sent: Monday, March 05, 2007 10:44 AM To: Subject: Re: [sqlite] Lemon example The

RE: [sqlite] Re: SQL that is stumping me

2006-10-31 Thread Doug Nebeker
] 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

[sqlite] SQL that is stumping me

2006-10-30 Thread Doug Nebeker
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

[sqlite] Can't figure out index problem

2006-08-24 Thread Doug Nebeker
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

RE: [sqlite] concurent writes and locks

2006-08-11 Thread Doug Nebeker
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

RE: [sqlite] Multithreading. Again.

2006-06-05 Thread Doug Nebeker
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

RE: [sqlite] Charset-Confusions

2006-05-15 Thread Doug Nebeker
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

Re: [sqlite] Detecting table scan

2006-05-04 Thread Doug Nebeker
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 ( but haven't found out exactly what the response

[sqlite] Detecting table scan

2006-05-03 Thread Doug Nebeker
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

RE: [sqlite] Complile and connecting to SQLite

2006-05-02 Thread Doug Nebeker
Pre-compiled Windows DLL: Source for Windows: 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:

RE: [sqlite] Intermittent crash in sqlite3_prepare()

2006-04-24 Thread Doug Nebeker
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

RE: [sqlite] how to fix problem of lock

2006-04-04 Thread Doug Nebeker
>From "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

RE: [sqlite] question about performance

2006-02-08 Thread Doug Nebeker
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.

[sqlite] Best possible concurrency

2006-01-13 Thread Doug Nebeker
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).

RE: [sqlite] CE locking -- review the code

2006-01-09 Thread Doug Nebeker
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

RE: [sqlite] Threads and locking

2005-12-16 Thread Doug Nebeker
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

RE: [sqlite] Trying to compile under VC.NET

2005-12-14 Thread Doug Nebeker
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: Subject: RE: [sqlite] Trying to compile under VC.NET Just

RE: [sqlite] Concurrency handling question

2005-11-30 Thread Doug Nebeker
eeds. One needs to release the locks it holds. Rolling back is the way to do that. --Ned. -Original Message----- From: Doug Nebeker [mailto:[EMAIL PROTECTED] Sent: Wednesday, 30 November, 2005 11:04 AM To: Subject: [sqlite] Concurrency handlin

[sqlite] Concurrency handling question

2005-11-30 Thread Doug Nebeker
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

RE: [sqlite] uSQLiteServer Source code available

2005-11-16 Thread Doug Nebeker
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: Subject: Re: [sqlite] uSQLiteServer Source code available Doug

RE: [sqlite] uSQLiteServer Source code available

2005-11-15 Thread Doug Nebeker 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

RE: [sqlite] uSQLiteServer Source code available

2005-11-15 Thread Doug Nebeker
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