Re: [sqlite] Simple web query tool

Running the sqlite3 command-line shell via cgi works way better than you may expect. The command-line shell has a small footprint and works well with stdio in batch mode. You can run a shell script that runs an instance of the cli shell and reads and runs a .sql file. The sql file and bash can

Re: [sqlite] WAL and consistency

Dear Mr Hipp, That's quite a privilege to get this answer from SQLite's creator himself ! Thanks for the clarification, I guess I didn't switch my brain to formal logic when I read this part of the docs... Have a nice day, Jonathan -Original Message- From: sqlite-users

Re: [sqlite] Replicate SQLite and keep sync (every half hour) from PostgreSQL table

> On Feb 2, 2017, at 1:15 AM, Michael Nielsen wrote: > > I would like to replicate the PostgreSQL table (including a WHERE clause) > to a in-memory SQLite database, which will sync/update every 30 minutes (or > so). There isn’t any built-in or standard way to do this

Re: [sqlite] WAL and consistency

On 2/2/17, Rossel, Jonathan wrote: > Hi, > > Let me first congratulate you for the awesome product that SQLite is! I just > need a clarification with respect to a comment given in the docs > (http://sqlite.org/pragma.html#pragma_synchronous): > > "With synchronous=FULL in

[sqlite] WAL and consistency

Hi, Let me first congratulate you for the awesome product that SQLite is! I just need a clarification with respect to a comment given in the docs (http://sqlite.org/pragma.html#pragma_synchronous): "With synchronous=FULL in WAL mode, an additional sync operation of the WAL file happens after

[sqlite] Replicate SQLite and keep sync (every half hour) from PostgreSQL table

I'm able to access a remote PostgreSQL table from my server. However, the PostgreSQL table contains around 50 mio. records, and I have a certain column ID which I only need. I would like to replicate the PostgreSQL table (including a WHERE clause) to a in-memory SQLite database, which will

Re: [sqlite] AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

On Thursday, 2 February, 2017 18:56, Richard Hipp wrote: > The behavior is correct. > I have adjusted the documentation to try to avoid ambiguity. See > https://www.sqlite.org/docsrc/info/f6e2eab4e71644b1 for the > documentation update. The ROWID chosen for the new row is

Re: [sqlite] AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

The behavior is correct. I have adjusted the documentation to try to avoid ambiguity. See https://www.sqlite.org/docsrc/info/f6e2eab4e71644b1 for the documentation update. On 2/2/17, Keith Medcalf wrote: > > sqlite> create table x (key integer primary key, value text); >

[sqlite] AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

sqlite> create table x (key integer primary key, value text); sqlite> insert into x values (null, 'test'); sqlite> update x set key=1 where value='test'; sqlite> select * from x; 1|test sqlite> delete from x; sqlite> insert into x values (null, 'again'); sqlite> select * from x; 1|again

Re: [sqlite] Does SQLite use field definitions?

On Thursday, 2 February, 2017 09:12, Clyde Eisenbeis inquired: > What about the INTEGER PRIMARY KEY (defined in my first email post)? > Does this field start at 1, and then auto increment for each new line? INTEGER PRIMARY KEY declares a field to be an alias for the rowid.

Re: [sqlite] "DISTINCT" makes a query take 37 times as long

When I read this, it seemed like it made sense. The thing is, it does not match up with reality. First, the analysis of what happens when I pipe the results to 'sort' misses the fact that the sort process executes within the 31 minutes of that version. It would not make a dent in the time of

Re: [sqlite] New tool for PUTTY logging [Windows]

On 2 Feb 2017, at 8:05pm, Stephen Chrzanowski wrote: > There's a little bit more involved than just consolidating the files into > one that I need. Specifically, since the command line on all customer > linux machines are formatted a certain way, I can easily identify what

Re: [sqlite] New tool for PUTTY logging [Windows]

Definitely radical and possible, but something I don't think I'd like to take on, simply because I'm a Delphi dev'r, not C/C++, although, I did do 10 other peoples final C++ projects back in my college days, but that was two decades ago. (I don't mind saying that, but man do I hate realizing that

Re: [sqlite] New tool for PUTTY logging [Windows]

There's a little bit more involved than just consolidating the files into one that I need. Specifically, since the command line on all customer linux machines are formatted a certain way, I can easily identify what machine I'm specifically looking at, and filter results based on that. Because I'm

Re: [sqlite] New tool for PUTTY logging [Windows]

I've been following this thread with interest. I have used Putty for years as its the de-facto standard for decent ssh terminals on Windows boxes. A slightly more radical suggestion for the log files. Since Putty is open source, have a look at the code and see if you can easily add in a

Re: [sqlite] New tool for PUTTY logging [Windows]

I can only get to our customer machines by jumping into a server that has access to both sides of the network. Our side, and the customer side. I can't get to a customers machine directly. The is out, but I'm already doing the rest. The image in my head of what my program is going to do is

Re: [sqlite] Help with custom collation

Thanks for the advice Jens. My problem is the software I use (Embarcadero c++ builder) is more utf16 orientated. It does support utf8 but many of the built in functions return System.UnicodeString which is utf16. From: Jens Alfke Sent: 02 February 2017 17:32 To:

[sqlite] IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

Hi. I'd like to ask why using a IS NULL or IS NOT NULL constraint in a query made to a virtual table does not result in these constraints being present in the sqlite3_index_info structure passed to the virtual table's xBestIndex. Currently, using one of these constraints results in no constraints

Re: [sqlite] Help with custom collation

Perhaps off-topic, but: UTF-16 is generally not recommended, unless you need to work with legacy APIs that require it. It has the same difficulties as UTF-8 (having to handle characters broken into multi-element sequences) but uses more RAM and isn’t downward compatible with ASCII. Also, since

Re: [sqlite] Does SQLite use field definitions?

An integer primary key is the only field that absolutely must be the type declared. It basically says to use the internal rowid as a visible field. Currently it does start at 1 and any newly inserted record where the id is not explicitly specified will get an id of 1 more than whatever's the

Re: [sqlite] New tool for PUTTY logging [Windows]

On 2 Feb 2017, at 4:48pm, Stephen Chrzanowski wrote: > Unfortunately no, there is no time stamp at the command lines, and I can't > add that ability (Maybe if I setup my own new account on our jump-point > server, but then I've got another kettle to deal with). The only

Re: [sqlite] New tool for PUTTY logging [Windows]

Unfortunately no, there is no time stamp at the command lines, and I can't add that ability (Maybe if I setup my own new account on our jump-point server, but then I've got another kettle to deal with). The only reference to a time is based on the filename that Putty creates the file, and the

Re: [sqlite] New tool for PUTTY logging [Windows]

Maybe another method to consider: This guy shows that Putty appears to support creating separate log files for each session including a timestamp in the file name. https://www.viktorious.nl/2013/01/14/putty-log-all-session-output/ Could your script import any new log files it sees, then move

Re: [sqlite] New tool for PUTTY logging [Windows]

On 2 Feb 2017, at 4:22pm, Stephen Chrzanowski wrote: > But, in my preplanning, scenario development and brain storming, the above > paragraph is going to destroy my machine doing a [ select * from CmdLine > where upper(CmdEntered) =upper('SomeText') ] every time I read a

Re: [sqlite] New tool for PUTTY logging [Windows]

Interesting idea. Does LastInsertID return the row that was a dupe? I suppose I can test that.. On Thu, Feb 2, 2017 at 11:34 AM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > You could make the CmdEntered field unique, or create a hash on the > uppercase content of the command

Re: [sqlite] New tool for PUTTY logging [Windows]

You could make the CmdEntered field unique, or create a hash on the uppercase content of the command and make that a unique key. Then use INSERT OR IGNORE... Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786

[sqlite] New tool for PUTTY logging [Windows]

By a new requirement of my manager, we're asked to log all our SSH sessions to our customer machines. The current Windows search is a PITA, grepping for text is burdensome considering the number of sessions I open per day, and being a pack rat, I love reading about stuff I did years ago. :] (Not

Re: [sqlite] Documentation clarification request... (re: Triggers)

On 2 Feb 2017, at 2:38pm, Michael Tiernan wrote: > So that makes sense (I think) and isn't causing a problem for me but then, a > little further on it says: > > If a WHEN clause is supplied, the SQL statements specified are only > executed for rows for which the

Re: [sqlite] Can stmt SQL be changed?

Thanks for the replies. I’ve amended the wrapper to finalize the stmt before any change to sql. Only required a one word change. From: Richard Hipp Sent: 02 February 2017 12:27 To: SQLite mailing list Subject: Re: [sqlite]

[sqlite] Documentation clarification request... (re: Triggers)

In the online documentation: https://sqlite.org/lang_createtrigger.html It says: At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional. FOR EACH ROW implies that the SQL statements specified in

Re: [sqlite] Does SQLite use field definitions?

What about the INTEGER PRIMARY KEY (defined in my first email post)? Does this field start at 1, and then auto increment for each new line? On Thu, Feb 2, 2017 at 5:02 AM, R Smith wrote: > > > On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote: >> >> I don't see MEMO listed. I

Re: [sqlite] Can stmt SQL be changed?

On 2/2/2017 8:11 AM, Simon Slavin wrote: So would it be possible to detect someone using _prepare() on an existing statement and return SQLITE_MISUSE ? Well, one could be passing an address of uninitialized variable to sqlite_prepare. You probably don't want to error out if the random

Re: [sqlite] Can stmt SQL be changed?

So would it be possible to detect someone using _prepare() on an existing statement and return SQLITE_MISUSE ? It would slow things down, of course, so there’s a little trade-off between detecting the error and making _prepare() a tiny bit slower. But it should be a very very tiny bit.

Re: [sqlite] Can stmt SQL be changed?

On 2/2/2017 4:28 AM, x wrote: For a while I got away with this sqlite3_stmt *stmt; if (sqlite3_prepare16_v2(DB, L”sql statement”, -1, , NULL) != SQLITE_OK) throw exception(“.”); // use stmt sqlite3_reset(stmt); if

Re: [sqlite] Can stmt SQL be changed?

On 2/2/17, x wrote: > For a while I got away with this > > sqlite3_stmt *stmt; > > if (sqlite3_prepare16_v2(DB, L”sql statement”, -1, , NULL) != > SQLITE_OK) > throw exception(“.”); > > // use stmt > > sqlite3_reset(stmt);

Re: [sqlite] "DISTINCT" makes a query take 37 times as long

On 2/2/17, Hick Gunter wrote: > DISTINCT forces the query optimizer to create an intermediate table to hold > the results and compare each row of the non-distinct result set with an > automatically created index. It may also affect the query plan in a way that > chooses

Re: [sqlite] Help with confirming a couple of error traces

On 2/2/17, Shaobo He wrote: > May I ask that do you see null pointer deferences > during development regularly? Sometimes, but not too often. We get assertion faults more. Or just incorrect answers. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Can stmt SQL be changed?

On 2 Feb 2017, at 11:08am, x wrote: > Thanks for the reply Simon but the wrapper throws an exception if either > reset or finalize fails to return SQLITE_OK so not that. Okay. So your question boils down to whether the second _prepare() in this is legal: _open()

Re: [sqlite] Can stmt SQL be changed?

Thanks for the reply Simon but the wrapper throws an exception if either reset or finalize fails to return SQLITE_OK so not that. Sent from Mail for Windows 10 From: Simon Slavin Sent: 02 February 2017 10:41 To:

Re: [sqlite] Does SQLite use field definitions?

On 2017/02/01 5:41 PM, Clyde Eisenbeis wrote: I don't see MEMO listed. I needed to use MEMO in Microsoft Access to handle char strings longer than 256. However, I don't see any complaints by SQLite when I use MEMO. SQLite will never complain about anything you use as a type. What you

Re: [sqlite] Can stmt SQL be changed?

On 2 Feb 2017, at 9:28am, x wrote: > The reused stmt was the only possible culprit so I’m wondering if it’s down > to the changed sql? You can find out. Both _reset() and _finalize() return a result code just like _prepare(). Do the same kind of exception checking

[sqlite] Can stmt SQL be changed?

For a while I got away with this sqlite3_stmt *stmt; if (sqlite3_prepare16_v2(DB, L”sql statement”, -1, , NULL) != SQLITE_OK) throw exception(“.”); // use stmt sqlite3_reset(stmt); if (sqlite3_prepare16_v2(DB, L”different sql

Re: [sqlite] Sqlite with Docker and mounted volumes

That's a good point. Thank you Keith! On Thu, Feb 2, 2017 at 12:12 AM, Keith Medcalf wrote: > If it is a remote filesystem (vs a local filesystem) then you must also > have only one connection (ever) to the database file at any given time. > Otherwise you may have issues.

Re: [sqlite] Help with custom collation

Thanks Dan. Replacing “Compare” with L”Compare” and casting to wchar_t* inside the function solved the problem. Thanks for all the replies. From: Dan Kennedy Sent: 02 February 2017 08:42 To:

Re: [sqlite] Help with custom collation

On 02/02/2017 02:08 AM, x wrote: Thanks Clemens. You’re right about changing the UTF8String* to char* as it now works but when trying it with a column containing Unicode characters it didn’t. I’d have liked to have tried it with windows wchar_t* type but If I try using if

Re: [sqlite] Help with custom collation

Sorry misread that you are attempting to write a custom collation. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hick Gunter Gesendet: Donnerstag, 02. Februar 2017 09:06 An: 'SQLite mailing list'

Re: [sqlite] "DISTINCT" makes a query take 37 times as long

DISTINCT forces the query optimizer to create an intermediate table to hold the results and compare each row of the non-distinct result set with an automatically created index. It may also affect the query plan in a way that chooses inefficient indices, which is more likely if you have not run

Re: [sqlite] Help with custom collation

The interface your (simple) function must support is: void xFunc(sqlite3_context*,int,sqlite3_value**) with the first parameter being the sqlite3_context, the second parameter being the number of arguments passed in, and the third parameter being an array of pointers to unprotected sqlite3_value