Re: [sqlite] Simple web query tool

[Lindsay Lawrence]

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 be as complex as it needs
to be.  You can pass in params on the command-line by inserting env values
into a temp table and then using that table as necessary in subsequent sql.

For example:
Configure your httpd for cgi then have a cgi script, say "*report.cgi*":

#!/bin/bash
/path/to/sqlite3 -bail -batch "/path/to/my.s3db" ".read /path/to/report.sql"

and in *"/path/to/report.sql*"

.mode html
.headers on
.print Content-Type: text/html
.print
.print 
select * from from report_view;
.print 

For large datasets, or something you just want to import conveniently into
a spreadsheet, or another db, for further munging you could set csv mode
and/or force a download. As a note, unless you are sorting a very large
dataset the resource usage of all of this is quite low as sqlite just pipes
the dataset out over the http response as it is generated.

/Lindsay


On Wed, Feb 1, 2017 at 8:10 AM, Jay Kreibich  wrote:

> I'm looking for an *extremely* simple web tool that will allow me to
> configure a dozen or so stored queries, which people can then select and
> run on an internal server.  If the system supports a query variable or two,
> that would be fantastic, but I don't even need that.  Any thoughts?  Or do
> I dust off the PHP tutorials and spend an afternoon throwing something
> together?
>
>  -j
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL and consistency

[Rossel, Jonathan]

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 [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: vendredi 3 février 2017 05:05
To: SQLite mailing list
Subject: 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 WAL mode, an additional sync operation of 
> the WAL file happens after each transaction commit. The extra WAL sync 
> following each transaction help ensure that transactions are durable 
> across a power loss, but they do not aid in preserving consistency."

Transactions are fully consistent with our without the extra syncs.
Since transactions are already fully consistent without the extra syncs, adding 
the extra syncs adds no additional consistency.

>
> I don't understand the last bit about consistency preservation and 
> couldn't find more clues on that subject in the docs. Why would consistency 
> be lost ?
> Is SQLite in WAL mode not ACID when synchronous=FULL ? What kind of 
> damage can we expect in case of power loss (assuming the disk performs 
> the syncs as
> supposed) ?
>
> Thanks in advance !
>
> Jonathan
>
>
>
> **
> * This e-mail message is intended only for the addressee(s) 
> and contains information which may be confidential. If you are not the 
> intended recipient please do not read, save, forward, disclose or copy 
> the contents of this e-mail. If this e-mail has been sent to you in 
> error, please delete this e-mail and any copies or links to this 
> e-mail completely and immediately from your system. We also like to 
> inform you that communication via e-mail over the Internet is insecure 
> because third parties may have the possibility to access and 
> manipulate e-mails.
>
> Any views expressed in this message are those of the individual 
> sender, except where the sender specifically states them to be the 
> views of The Swatch Group Ltd.
> **
> * ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
***
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete 
this 
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
***
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jens Alfke]

> 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 between arbitrary 
databases. You’ll have to implement your own solution. (I speak as someone 
who’s specialized in database replication for the past five years; but I work 
on a database engine that was designed for replication.)

In order to do an efficient (incremental) sync, you have to be able to query 
the remote [Postgres] database and determine:
(a) what rows are new since the last time you synced;
(b) what rows have changed since the last time you synced;
(c) what rows have been deleted since the last time you synced

If the table has an auto-incrementing primary key, (a) is pretty easy to do; 
you just query for rows where the primary is greater than the maximum value 
you’ve seen before.

Doing (b) requires that the rows have something like a modification timestamp 
or Lamport clock.

Generally (c) is the hardest, because a deletion usually doesn’t leave any 
trace behind. The best solution would be to attach a trigger to the table that 
adds a ‘tombstone’ to an auxiliary table to record the deletion. (If you can do 
that, you can use a similar approach to log inserts and updates, which makes 
the whole problem a lot easier.) If you can’t do that, the best you can do, I 
think, is to query the primary key of every existing row, and match them all 
against the records in your local database.

A completely different approach is to run a program on the remote machine that 
dumps the entire table to a file in some simple format like CSV or JSON; then 
use rsync or something similar to copy that file to your local machine; then 
read the file and rebuild the local database from it. (This might not be too 
inefficient, because rsync is pretty good about transferring minimal data to 
update the file.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL and consistency

[Richard Hipp]

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 WAL mode, an additional sync operation of the WAL
> file happens after each transaction commit. The extra WAL sync following
> each transaction help ensure that transactions are durable across a power
> loss, but they do not aid in preserving consistency."

Transactions are fully consistent with our without the extra syncs.
Since transactions are already fully consistent without the extra
syncs, adding the extra syncs adds no additional consistency.

>
> I don't understand the last bit about consistency preservation and couldn't
> find more clues on that subject in the docs. Why would consistency be lost ?
> Is SQLite in WAL mode not ACID when synchronous=FULL ? What kind of damage
> can we expect in case of power loss (assuming the disk performs the syncs as
> supposed) ?
>
> Thanks in advance !
>
> Jonathan
>
>
>
> ***
> This e-mail message is intended only for the addressee(s) and contains
> information which may be confidential. If you are not the intended
> recipient please do not read, save, forward, disclose or copy the contents
> of this e-mail. If this e-mail has been sent to you in error, please delete
> this
> e-mail and any copies or links to this e-mail completely and immediately
> from your system. We also like to inform you that communication via e-mail
> over the Internet is insecure because third parties may have the possibility
> to access and manipulate e-mails.
>
> Any views expressed in this message are those of the individual sender,
> except where the sender specifically states them to be the views of
> The Swatch Group Ltd.
> ***
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] WAL and consistency

[Rossel, Jonathan]

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 each transaction commit. The extra WAL sync following each 
transaction help ensure that transactions are durable across a power loss, but 
they do not aid in preserving consistency."

I don't understand the last bit about consistency preservation and couldn't 
find more clues on that subject in the docs. Why would consistency be lost ? Is 
SQLite in WAL mode not ACID when synchronous=FULL ? What kind of damage can we 
expect in case of power loss (assuming the disk performs the syncs as supposed) 
?

Thanks in advance !

Jonathan



***
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete 
this 
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
***
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Michael Nielsen]

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 sync/update every 30 minutes (or
so).

In reboot etc. the replication may start over.

How would be the best approach to such a challenge?

I've actually never worked with SQLite before.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

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 at least one larger than the largest ROWID
that has ever before been inserted in that same table.

would perhaps something like:

The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before been inserted, or currently exists, in that same table.

be more accurate since newrowid = max(1, largestinserted, max(rowid)) + 1 ?

> On 2/2/17, Keith Medcalf  wrote:
> >
> > 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
> > sqlite> drop table x;
> > sqlite> create table x (key integer primary key autoincrement, 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;
> > 2|again
> > sqlite> drop table x;
> > sqlite> create table x (key integer primary key autoincrement, 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> insert into x values (null, 'again');
> > sqlite> select * from x;
> > 1|test
> > 10001|again
> > sqlite>
> >
> > Question is:  when the rowid alias is declared autoincrement, Should the
> > "update" update the hi-level mark for the key field?
> >
> > ---
> > Life should not be a journey to the grave with the intention of arriving
> > safely in a pretty and well preserved body, but rather to skid in
> broadside
> > in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> > proclaiming "Wow! What a Ride!"
> >  -- Hunter S. Thompson
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> 
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

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

[Richard Hipp]

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> 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
> sqlite> drop table x;
> sqlite> create table x (key integer primary key autoincrement, 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;
> 2|again
> sqlite> drop table x;
> sqlite> create table x (key integer primary key autoincrement, 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> insert into x values (null, 'again');
> sqlite> select * from x;
> 1|test
> 10001|again
> sqlite>
>
> Question is:  when the rowid alias is declared autoincrement, Should the
> "update" update the hi-level mark for the key field?
>
> ---
> Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in broadside
> in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

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
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, 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;
2|again
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, 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> insert into x values (null, 'again');
sqlite> select * from x;
1|test
10001|again
sqlite>

Question is:  when the rowid alias is declared autoincrement, Should the 
"update" update the hi-level mark for the key field?

---
Life should not be a journey to the grave with the intention of arriving safely 
in a pretty and well preserved body, but rather to skid in broadside in a cloud 
of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! 
What a Ride!"
 -- Hunter S. Thompson




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

Re: [sqlite] Does SQLite use field definitions?

[Keith Medcalf]

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.

If you insert NULL (or do not specify a value) then the value is one greater 
than the largest rowid currently in use, that is also at least 1.  So newrowid 
= max(1, max(rowid) + 1).

If you add the AUTOINCREMENT keyword, then the semantics changes slightly and 
the max(rowid) ever seen for the table is stored in another table, and that 
value is incremented by 1 to determine the new rowid.  In all cases, the 
minimum value of an automatically generated rowid is 1.

In either case you can explicitly insert (or update) a rowid to contain any 
64-bit signed integer value.  In the case of a definition with AUTOINCREMENT, 
the max rowid ever seen is saved, so if you did something like:

insert into x (key, data) values (NULL, 'test');
update x set key = 1000 where data='test';
delete from x;
insert into x (key, data) values (NULL, 'again');

then if the definition of column key was INTEGER PRIMARY KEY, the end row in x 
would be (1, 'again').  If it was INTEGER PRIMARY KEY AUTOINCREMENT then the 
row should be (1001, 'again').

"INTEGER PRIMARY KEY" is special and can only contain integers -- it is an 
alias for the rowid.  Other primary key (eg TEXT PRIMARY KEY) can contain any 
data type and has the same effect as declaring it TEXT UNIQUE.

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
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, 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;
2|again
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, 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> insert into x values (null, 'again');
sqlite> select * from x;
1|test
10001|again
sqlite>
 
> 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 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
> should
> > to use is TEXT. In SQLite any string value, of any length* can be stored
> in
> > any column, but will be "handled like text" when the column is of the
> TEXT
> > type affinity, and also allow a collation method. Honestly, you can put
> the
> > text of the complete volumes of Don Quixotic in a single TEXT field in a
> > database.
> >
> > * - As long as the string memory doesn't exceed 4GB, the char-size of
> which
> > may differ based on encoding, and if you add the quoted string to the
> query
> > text directly (as opposed to binding it via the API), then the maximum
> size
> > will be governed by the max character length of the query input, which
> can
> > be configured via the API also. You can add the string as memory bytes
> as a
> > BLOB to go bigger, but then you lose collation capability I'll stop
> > there, it's probably more unnecessary info than you wished for already.
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

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

[Kevin O'Gorman]

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 the slow version.

But the big thing is that I took a look at EXPLAIN QUERY PLAN using this
script:
#!/usr/bin/env
python3

"""Output positions that are reachable but unsolved at census 18
See page 76 of Qubic log

Last Modified: Thu Feb  2 07:46:03 PST 2017
"""

import sqlite3  # https://docs.python.org/3.5/library/sqlite3.html

with sqlite3.connect("917.db") as conn:
print("BEOFRE ANALYZE")
for row in conn.execute("""
EXPLAIN
SELECT DISTINCT ppos
FROM move JOIN pos ON mto = pnum
WHERE pcensus = 18 and pmin < pmax
"""):
print(row)
print()
print()
conn.execute("ANALYZE")
print("AFTER ANALYZE")
for row in conn.execute("""
EXPLAIN
SELECT DISTINCT ppos
FROM move JOIN pos ON mto = pnum
WHERE pcensus = 18 and pmin < pmax
"""):
print(row)

and after waiting most of the day for the analyze to finish, I got two
identical query plans,
neither of which I could decipher:
BEFORE
ANALYZE

(0, 'Init', 0, 25, 0, '', '00', None)
(1, 'Null', 1, 7, 0, '', '08', None)
(2, 'OpenRead', 1, 4, 0, '7', '00', None)
(3, 'OpenRead', 3, 6, 0, 'k(1,)', '00', None)
(4, 'OpenRead', 4, 11, 0, 'k(3,,,)', '02', None)
(5, 'Rewind', 3, 21, 1, '0', '00', None)
(6, 'Seek', 3, 0, 1, '', '00', None)
(7, 'Column', 1, 2, 1, '', '00', None)
(8, 'Ne', 2, 20, 1, '(BINARY)', '54', None)
(9, 'Column', 1, 5, 3, '-99', '00', None)
(10, 'Column', 1, 6, 4, '99', '00', None)
(11, 'Ge', 4, 20, 3, '(BINARY)', '53', None)
(12, 'IdxRowid', 3, 5, 0, '', '00', None)
(13, 'SeekGE', 4, 20, 5, '1', '00', None)
(14, 'IdxGT', 4, 20, 5, '1', '00', None)
(15, 'Column', 3, 0, 6, '', '00', None)
(16, 'Eq', 6, 19, 7, '(BINARY)', '80', None)
(17, 'Copy', 6, 7, 0, '', '00', None)
(18, 'ResultRow', 6, 1, 0, '', '00', None)
(19, 'Next', 4, 14, 0, '', '00', None)
(20, 'Next', 3, 6, 0, '', '01', None)
(21, 'Close', 1, 0, 0, '', '00', None)
(22, 'Close', 3, 0, 0, '', '00', None)
(23, 'Close', 4, 0, 0, '', '00', None)
(24, 'Halt', 0, 0, 0, '', '00', None)
(25, 'Transaction', 0, 0, 155, '0', '01', None)
(26, 'TableLock', 0, 4, 0, 'pos', '00', None)
(27, 'TableLock', 0, 7, 0, 'move', '00', None)
(28, 'Integer', 18, 2, 0, '', '00', None)
(29, 'Goto', 0, 1, 0, '', '00', None)


AFTER ANALYZE
(0, 'Init', 0, 25, 0, '', '00', None)
(1, 'Null', 1, 7, 0, '', '08', None)
(2, 'OpenRead', 1, 4, 0, '7', '00', None)
(3, 'OpenRead', 3, 6, 0, 'k(1,)', '00', None)
(4, 'OpenRead', 4, 11, 0, 'k(3,,,)', '02', None)
(5, 'Rewind', 3, 21, 1, '0', '00', None)
(6, 'Seek', 3, 0, 1, '', '00', None)
(7, 'Column', 1, 2, 1, '', '00', None)
(8, 'Ne', 2, 20, 1, '(BINARY)', '54', None)
(9, 'Column', 1, 5, 3, '-99', '00', None)
(10, 'Column', 1, 6, 4, '99', '00', None)
(11, 'Ge', 4, 20, 3, '(BINARY)', '53', None)
(12, 'IdxRowid', 3, 5, 0, '', '00', None)
(13, 'SeekGE', 4, 20, 5, '1', '00', None)
(14, 'IdxGT', 4, 20, 5, '1', '00', None)
(15, 'Column', 3, 0, 6, '', '00', None)
(16, 'Eq', 6, 19, 7, '(BINARY)', '80', None)
(17, 'Copy', 6, 7, 0, '', '00', None)
(18, 'ResultRow', 6, 1, 0, '', '00', None)
(19, 'Next', 4, 14, 0, '', '00', None)
(20, 'Next', 3, 6, 0, '', '01', None)
(21, 'Close', 1, 0, 0, '', '00', None)
(22, 'Close', 3, 0, 0, '', '00', None)
(23, 'Close', 4, 0, 0, '', '00', None)
(24, 'Halt', 0, 0, 0, '', '00', None)
(25, 'Transaction', 0, 0, 155, '0', '01', None)
(26, 'TableLock', 0, 4, 0, 'pos', '00', None)
(27, 'TableLock', 0, 7, 0, 'move', '00', None)
(28, 'Integer', 18, 2, 0, '', '00', None)
(29, 'Goto', 0, 1, 0, '', '00', None)
Maybe somebody can explain them to me, but it doesn't really matter whether
I ever understand them.
Perhaps Mr. Hipp can make use of them.

Absent some flaw in the above script, I think I'm done with this.  I have a
solution that works for me,
and I'd just as soon get back to my real task.  I just wanted to give
feedback in case it would be useful.
That's how i say thanks for a really useful product.

Thanks. Mr. Hipp, and anyone else that has contributed to this product.

++ kevin


On Thu, Feb 2, 2017 at 12:27 AM, 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 inefficient indices, which is more likely if you have not run
> ANALYZE on the fully loaded database.
>
> Using a 3 stage pipe instead you additionally have more CPUs (1 running
> the query, 1 or more sorting the results) working in paralell.
>
> Try EXPLAIN QUERY PLAN to see what the query planner 

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

[Simon Slavin]

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
> machine I'm specifically looking at, and filter results based on that.

Sorry, I forgot that.  So add another column:

name of text file
entry number (line in file ?)
computer identifier (IP address ?)
data

Step 1 can be to get it all into a SQLite database of any kind.  Worry about 
the final form and whether you’ll want to use FTS later.

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

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

[Stephen Chrzanowski]

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 time has passed!).  However, if that type of additional logging does
become a thing for Putty, I might want to put a reservation in the code
that the log file has that information somewhere.

But you're correct.  If the log outputs were able to put a time stamp per
line, that'd help with at least narrowing down the time resolution.  But
that isn't the primary purpose of this particular project.  Its just easy
search, fast updates to the database, and focused results.

On Thu, Feb 2, 2017 at 3:01 PM, Rob Willett 
wrote:

> 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
> timestamp per line for the log file section.
>
> That gives you two features:
>
> 1. You now have a verifiable traceable source as you have downloaded and
> compiled it. I have worked in environments where we need to trace every bit
> of code that comes into the data centre. We need to know where we
> downloaded it from, what the license was, authority from legal to use etc
> etc. Your situation might not warrant it :)
>
> 2. You now have unique lines with a timestamp, a hostname and a TTY
> session (I assume). I think that guarantees uniqueness.
>
> I have no idea if Putty can be modified in this way, but it wouldn't hurt
> to have a look, see if the change is easy, do the change and then send some
> diffs back to the Putty team. If they accept the changes you're sorted. If
> they don't well, Putty doesn't change that much over time so you could
> probably use your version for years to come.
>
> Rob
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Stephen Chrzanowski]

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 looking at around 1000 files and 600meg of raw text, it isn't
easily consumable to a human, so, the only relevant identifiers I need
right now is the start date (Based on file name), end date (Based on file
time stamp), and the server itself (Which can be a mix of servers in a
single log file), which I said, is part of the command line.  The removal
of the duplication of each line in that text file is also important to me
as well as there is no reason to have 500 entries of my initiating the SSH
session to a customer site.

With exactly zero code written and not a database file created, I'm
planning on having log entry table with an auto numbered PK field, text
field, and a hash of the text field (Collisions may occur when [ new.text
<> old.text and new.hash = old.hash ] but I'm not interested in 100%
absolutely perfect results).  I'll then have a table that contains just a
list of server names picked up from reading the log files with a PK field
as well, and then one table that contains an auto numbered PK, reference to
the server PK, and a reference to the PK in the log entry table, and wiggle
in the date mentioned in the filename as well.


On Thu, Feb 2, 2017 at 11:59 AM, Simon Slavin  wrote:

>
>
> Under those circumstances, all you’re really doing by putting this data in
> a SQLite database is consolidating lots of separate files into one.  So
> import everything from those files, without checking for duplicates, using
> as your primary key the combination of logfile name and line number.  To
> avoid having to deal with errors from duplicates use
>
> INSERT OR IGNORE ...
>
> Once you’ve worked out how to get it into a SQLite database you can decide
> whether do searches using LIKE or FTS.  Or duplicate your database and
> experiment with both approaches to find your ideal balance of filesize and
> search speed.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Rob Willett]

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 
timestamp per line for the log file section.


That gives you two features:

1. You now have a verifiable traceable source as you have downloaded and 
compiled it. I have worked in environments where we need to trace every 
bit of code that comes into the data centre. We need to know where we 
downloaded it from, what the license was, authority from legal to use 
etc etc. Your situation might not warrant it :)


2. You now have unique lines with a timestamp, a hostname and a TTY 
session (I assume). I think that guarantees uniqueness.


I have no idea if Putty can be modified in this way, but it wouldn't 
hurt to have a look, see if the change is easy, do the change and then 
send some diffs back to the Putty team. If they accept the changes 
you're sorted. If they don't well, Putty doesn't change that much over 
time so you could probably use your version for years to come.


Rob

On 2 Feb 2017, at 19:53, Stephen Chrzanowski wrote:

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 that I feed 
it a
date range, a server I'm interested in, and optionally provide text 
that
further filters the information I'm looking for.  Once I have the 
filtered
data, I'd have a list of days that I'd been on that exact server, 
and/or

entries that mention my subject server, and I can see the text only
pertaining to that machine and date range.  I'd be able to read the 
full
set of activities on that machine for that day, and not have to hop 
around

to multiple log files..  This would get rid of the concept of many log
files as well, since all files are now one.  Kind of Borg-ish?


On Thu, Feb 2, 2017 at 11:54 AM, Donald Griggs  
wrote:



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 them to 
an

archive?

That way, you'd never have to read through a huge log file to find 
what

should be imported.


==From the page linked above:


I am using some putty parameters which will make every session 
unique, in

this case “”, which means:

   -  = hostname for the session
   -  = year
   -  = month
   -  = day
   -  = time

 ==
​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

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

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

[Stephen Chrzanowski]

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 that I feed it a
date range, a server I'm interested in, and optionally provide text that
further filters the information I'm looking for.  Once I have the filtered
data, I'd have a list of days that I'd been on that exact server, and/or
entries that mention my subject server, and I can see the text only
pertaining to that machine and date range.  I'd be able to read the full
set of activities on that machine for that day, and not have to hop around
to multiple log files..  This would get rid of the concept of many log
files as well, since all files are now one.  Kind of Borg-ish?


On Thu, Feb 2, 2017 at 11:54 AM, Donald Griggs  wrote:

> 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 them to an
> archive?
>
> That way, you'd never have to read through a huge log file to find what
> should be imported.
>
>
> ==From the page linked above:
>
>
> I am using some putty parameters which will make every session unique, in
> this case “”, which means:
>
>-  = hostname for the session
>-  = year
>-  = month
>-  = day
>-  = time
>
>  ==
> ​
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with custom collation

[x]

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 mailing list
Subject: 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 you’re less likely to 
run into multi-word character sequences in normal use [if you’re non-Asian], 
it’s easier to miss bugs in your multi-word character handling. (Tip: put some 
emoji in your test strings!)

(A UTF-16 collator is also probably going to be slower than a UTF-8 one, since 
SQLite will need to copy/expand the UTF-8 strings from the database when it 
calls it. And yes, collators can easily become performance bottlenecks; I’ve 
seen it.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Daniel Kamil Kozar]

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 at
all being passed, thus forcing the virtual table to use the most
"generic" index for accessing it. This seems like an omission to me,
since virtual tables could very well provide their own handling for
this special kind of constraint, without having to revert to a full
scan of the table just so that SQLite can look for rows that satisfy
this constraint.
Interestingly, specifying a = NULL constraint in a query results in
the constraint being passed to xBestIndex. This is curious, since - at
least to my understanding - no two NULLs are supposed to compare
equal, and SQLite behaves just like that when using its normal table
implementations : running a query with = NULL gives an empty result,
while IS NULL gives the expected result.
I modified src/test_intarray.c in order for it to be self-contained
and make some pre-defined queries which show what I'm talking about.
The source is available here :
https://gist.githubusercontent.com/xavery/976981b84d3bba90ef6bb480adc7a16d/raw/61ed629ef3dc4af0735e50ad40d33c16c7d11a13/sqlite-vtab-isnull.c
I could only find one thread which also deals with this issue :
http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg62850.html
- however, it does not offer an explanation why the implementation
behaves this way.

Kind regards,
Daniel
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with custom collation

[Jens Alfke]

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 you’re less likely to 
run into multi-word character sequences in normal use [if you’re non-Asian], 
it’s easier to miss bugs in your multi-word character handling. (Tip: put some 
emoji in your test strings!)

(A UTF-16 collator is also probably going to be slower than a UTF-8 one, since 
SQLite will need to copy/expand the UTF-8 strings from the database when it 
calls it. And yes, collators can easily become performance bottlenecks; I’ve 
seen it.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite use field definitions?

[David Raymond]

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 current 
highest at the time. Though I believe that's not a standard so much as a 
"here's how we decided to implement it for now" thing, and thus shouldn't be 
relied upon.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clyde Eisenbeis
Sent: Thursday, February 02, 2017 9:12 AM
To: SQLite mailing list
Subject: 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 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 should
> to use is TEXT. In SQLite any string value, of any length* can be stored in
> any column, but will be "handled like text" when the column is of the TEXT
> type affinity, and also allow a collation method. Honestly, you can put the
> text of the complete volumes of Don Quixotic in a single TEXT field in a
> database.
>
> * - As long as the string memory doesn't exceed 4GB, the char-size of which
> may differ based on encoding, and if you add the quoted string to the query
> text directly (as opposed to binding it via the API), then the maximum size
> will be governed by the max character length of the query input, which can
> be configured via the API also. You can add the string as memory bytes as a
> BLOB to go bigger, but then you lose collation capability I'll stop
> there, it's probably more unnecessary info than you wished for already.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 reference
> to a time is based on the filename that Putty creates the file, and the
> last modified time stamp on the file at the file system level.  Then I have
> that as a range of days, which should be good enough to start pin pointing
> what I may need to find.

Under those circumstances, all you’re really doing by putting this data in a 
SQLite database is consolidating lots of separate files into one.  So import 
everything from those files, without checking for duplicates, using as your 
primary key the combination of logfile name and line number.  To avoid having 
to deal with errors from duplicates use

INSERT OR IGNORE ...

Once you’ve worked out how to get it into a SQLite database you can decide 
whether do searches using LIKE or FTS.  Or duplicate your database and 
experiment with both approaches to find your ideal balance of filesize and 
search speed.

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

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

[Stephen Chrzanowski]

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
last modified time stamp on the file at the file system level.  Then I have
that as a range of days, which should be good enough to start pin pointing
what I may need to find.

For past entries, that would be the best time resolution I would have.
However, going forward, if this software were to run as a type of service
(Or a hidden app that runs in the background -- Its my machine, its
allowed), I could just watch when a file is changed and put in whatever is
new and the resolution would become time between checks.

The other duplicate lines that can show up isn't of what I type, but, what
is returned.  There are a lot of times I use WATCH to keep tabs on the
status of the age of files in a particular directory, or, spam LS to do
something similar, etc.  There is a lot of duplication (I can't even
imagine to what level as I just don't know) I'm just not aware of or
noticed, but, its something I know exists and something I'd like to trim
down.

(Not to mention, I really wanna get rid of 900 files off my drive and
consolidate into one)

On Thu, Feb 2, 2017 at 11:36 AM, Simon Slavin  wrote:

>
> 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 new line
> > from a new log file to verify if the entry has been made.
>
> Wait wait wait.  I type duplicate commands all the time.
>
> When you create your text log does it not have a timestamp on every line
> ?  If not, can you make it do so ?  Then all you have to do is check
> whether the timestamp matches a column of the table.  Should be a
> fixed-length or delimited field, easy to isolate.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Donald Griggs]

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 them to an
archive?

That way, you'd never have to read through a huge log file to find what
should be imported.


==From the page linked above:


I am using some putty parameters which will make every session unique, in
this case “”, which means:

   -  = hostname for the session
   -  = year
   -  = month
   -  = day
   -  = time

 ==
​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 new line
> from a new log file to verify if the entry has been made.

Wait wait wait.  I type duplicate commands all the time.

When you create your text log does it not have a timestamp on every line ?  If 
not, can you make it do so ?  Then all you have to do is check whether the 
timestamp matches a column of the table.  Should be a fixed-length or delimited 
field, easy to isolate.

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

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

[Stephen Chrzanowski]

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 and make that a unique key.
>
> Then use INSERT OR IGNORE...
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
>
> On 2 February 2017 at 16:22, Stephen Chrzanowski 
> wrote:
> > 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
> > to mention the CYA thing is primary reason for this move -- I'm not
> > complaining)
> >
> > So I'm thinking about writing a tool that'll take the output of the PUTTY
> > logs, read them line by line, and insert the data into a SQLite database
> > with some referential integrity that will allow me to search against what
> > server I'm connecting to, a range of dates the logs, particular text,
> etc.
> > (Granted there is a huge range of error that could happen with this that
> > I'm not anticipating, but, meh.  I need something)
> >
> > Getting the data from the logs into a database is a true no-brainer.
> Same
> > with parsing and deciding how I want to check for text and properly
> catalog
> > what I'm doing per machine.  Some putty sessions I jump between several
> > machines, so during the line reading, I'll be looking for keywords
> > (Hostnames) based on the command prompt since how our prompts are
> globally
> > the same across all machines.
> >
> > During the reading process, what I want to do is read the line in, check
> > the database to see what I've read in already exists and react
> accordingly
> > by adding the new entry and setting up the relationships in other tables.
> > Childs play, IMO.
> >
> > 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 new line
> > from a new log file to verify if the entry has been made.  So my thought
> > leans towards FTS, but, I've never written anything dealing with that.
> >
> > Is there any kind of special preparation I need to do to the database to
> > get it working effectively?  Is there a particular way I have to make my
> > queries to see if previous text exists?  Is there a primer, not on the
> > theory of how it works in the back end, but, how to generate the SQL call
> > and deal with what comes out?  Are there any caveats I need to be aware
> > of?  Do I skip FTS and just roll my own word analyzer?
> >
> > Since Oct 2016, my logs are sitting at just shy of 700meg of text.
> Looking
> > for what I did on a particular machine even last month would be a pain at
> > this point.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 2 February 2017 at 16:22, Stephen Chrzanowski  wrote:
> 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
> to mention the CYA thing is primary reason for this move -- I'm not
> complaining)
>
> So I'm thinking about writing a tool that'll take the output of the PUTTY
> logs, read them line by line, and insert the data into a SQLite database
> with some referential integrity that will allow me to search against what
> server I'm connecting to, a range of dates the logs, particular text, etc.
> (Granted there is a huge range of error that could happen with this that
> I'm not anticipating, but, meh.  I need something)
>
> Getting the data from the logs into a database is a true no-brainer.  Same
> with parsing and deciding how I want to check for text and properly catalog
> what I'm doing per machine.  Some putty sessions I jump between several
> machines, so during the line reading, I'll be looking for keywords
> (Hostnames) based on the command prompt since how our prompts are globally
> the same across all machines.
>
> During the reading process, what I want to do is read the line in, check
> the database to see what I've read in already exists and react accordingly
> by adding the new entry and setting up the relationships in other tables.
> Childs play, IMO.
>
> 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 new line
> from a new log file to verify if the entry has been made.  So my thought
> leans towards FTS, but, I've never written anything dealing with that.
>
> Is there any kind of special preparation I need to do to the database to
> get it working effectively?  Is there a particular way I have to make my
> queries to see if previous text exists?  Is there a primer, not on the
> theory of how it works in the back end, but, how to generate the SQL call
> and deal with what comes out?  Are there any caveats I need to be aware
> of?  Do I skip FTS and just roll my own word analyzer?
>
> Since Oct 2016, my logs are sitting at just shy of 700meg of text.  Looking
> for what I did on a particular machine even last month would be a pain at
> this point.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] New tool for PUTTY logging [Windows]

[Stephen Chrzanowski]

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
to mention the CYA thing is primary reason for this move -- I'm not
complaining)

So I'm thinking about writing a tool that'll take the output of the PUTTY
logs, read them line by line, and insert the data into a SQLite database
with some referential integrity that will allow me to search against what
server I'm connecting to, a range of dates the logs, particular text, etc.
(Granted there is a huge range of error that could happen with this that
I'm not anticipating, but, meh.  I need something)

Getting the data from the logs into a database is a true no-brainer.  Same
with parsing and deciding how I want to check for text and properly catalog
what I'm doing per machine.  Some putty sessions I jump between several
machines, so during the line reading, I'll be looking for keywords
(Hostnames) based on the command prompt since how our prompts are globally
the same across all machines.

During the reading process, what I want to do is read the line in, check
the database to see what I've read in already exists and react accordingly
by adding the new entry and setting up the relationships in other tables.
Childs play, IMO.

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 new line
from a new log file to verify if the entry has been made.  So my thought
leans towards FTS, but, I've never written anything dealing with that.

Is there any kind of special preparation I need to do to the database to
get it working effectively?  Is there a particular way I have to make my
queries to see if previous text exists?  Is there a primer, not on the
theory of how it works in the back end, but, how to generate the SQL call
and deal with what comes out?  Are there any caveats I need to be aware
of?  Do I skip FTS and just roll my own word analyzer?

Since Oct 2016, my logs are sitting at just shy of 700meg of text.  Looking
for what I did on a particular machine even last month would be a pain at
this point.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 WHEN clause is true. If no WHEN
>   clause is supplied, the SQL statements are executed for all rows.
> 
> My quandary comes from the last part of that statement "executed for all 
> rows". Does that mean "for all rows in the table" or does it mean "for all 
> rows /modified/ in the table" or "all rows previously mentioned as being 
> inserted, updated, or deleted by the statement causing the trigger to fire"?

(Slight handwaving, because this probably isn’t the way things work internally.)

The check is done for each row modified.
The trigger is triggered each time the WHERE clause is satisfied.
When a trigger is triggered it is applied to one row of the table.

Each time you modify any row of any table, SQLite checks the 'WHERE' clause  
each TRIGGER on that table to see whether it needs to bring that TRIGGER into 
play.  If that modification of that row doesn’t satisfy the WHERE clause, 
nothing else about the TRIGGER is processed.  If that modification does satisfy 
the WHERE clause then the trigger is performed on that modification.

If you issued an UPDATE command which modified six rows of the table, you 
should imagine the check being done six times, but the trigger would be 
performed any number from 0 to 6 times.

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

Re: [sqlite] Can stmt SQL be changed?

[x]

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] 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);
>
>
> if (sqlite3_prepare16_v2(DB, L”different sql statement”, -1, ,
> NULL) != SQLITE_OK)
> throw exception(“.”);

This second prepare overwrites the "stmt" pointer with a pointer to a
new object.  The old statement was never finalized.  This results in a
statement object leak, which SQLite detects when you try to close the
database connection.


>
>
> // use stmt again
>
> sqlite3_finalize(stmt);
>
> (I’m using a c++ SQLite wrapper I wrote myself so hopefully I’ve written the
> above out correctly).
>
> Anyway, on the latest attempt at using the above everything worked fine as
> before. No error messages, stmt executed fine before and after SQL change
> and was finalized without getting an errmsg. This time though I got an error
> message when I tried to close the DB (something along the lines of “unable
> to close db ... unfinalized stmts or ...”.
>
> The reused stmt was the only possible culprit so I’m wondering if it’s down
> to the changed sql?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Michael Tiernan]

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
   the trigger may be executed (depending on the WHEN clause) for each
   database row being inserted, updated or deleted by the statement
   causing the trigger to fire.

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 WHEN clause is true. If no WHEN
   clause is supplied, the SQL statements are executed for all rows.

My quandary comes from the last part of that statement "executed for all 
rows". Does that mean "for all rows in the table" or does it mean "for 
all rows /modified/ in the table" or "all rows previously mentioned as 
being inserted, updated, or deleted by the statement causing the trigger 
to fire"?


I know I can "ass-u-me" what the correct answer should be but I really 
don't have the bandwidth to go and clean up the mess I might make if I'm 
wrong.


Thanks for everyone's time!

--
  << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
  Non Impediti Ratione Cogatationis
  Women and cats will do as they please, and men and dogs
   should relax and get used to the idea. -Robert A. Heinlein

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

Re: [sqlite] Does SQLite use field definitions?

[Clyde Eisenbeis]

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 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 should
> to use is TEXT. In SQLite any string value, of any length* can be stored in
> any column, but will be "handled like text" when the column is of the TEXT
> type affinity, and also allow a collation method. Honestly, you can put the
> text of the complete volumes of Don Quixotic in a single TEXT field in a
> database.
>
> * - As long as the string memory doesn't exceed 4GB, the char-size of which
> may differ based on encoding, and if you add the quoted string to the query
> text directly (as opposed to binding it via the API), then the maximum size
> will be governed by the max character length of the query input, which can
> be configured via the API also. You can add the string as memory bytes as a
> BLOB to go bigger, but then you lose collation capability I'll stop
> there, it's probably more unnecessary info than you wished for already.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can stmt SQL be changed?

[Igor Tandetnik]

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 
garbage it contains just accidentally happens to match a valid statement 
handle. That kind of heisenbug would be a doozy to debug.

--
Igor Tandetnik

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

Re: [sqlite] Can stmt SQL be changed?

[Simon Slavin]

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.

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

Re: [sqlite] Can stmt SQL be changed?

[Igor Tandetnik]

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 (sqlite3_prepare16_v2(DB, L”different sql statement”, -1, , 
NULL) != SQLITE_OK)
throw exception(“.”);


// use stmt again

sqlite3_finalize(stmt);


You are simply leaking a statement. You prepare one, never finalize it, 
prepare another, and finalize the latter. The fact that you use the same 
variable to store both handles is irrelevant (apart from the fact that 
it makes it easier to leak). It's no different than, say,


char* p;
p = new char[42];
p = new char[84];  // previous allocation leaked.
delete[] p;  // second allocation freed

Just finalize the first statement before reusing the variable for the 
second one.

--
Igor Tandetnik

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

Re: [sqlite] Can stmt SQL be changed?

[Richard Hipp]

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);
>
>
> if (sqlite3_prepare16_v2(DB, L”different sql statement”, -1, ,
> NULL) != SQLITE_OK)
> throw exception(“.”);

This second prepare overwrites the "stmt" pointer with a pointer to a
new object.  The old statement was never finalized.  This results in a
statement object leak, which SQLite detects when you try to close the
database connection.


>
>
> // use stmt again
>
> sqlite3_finalize(stmt);
>
> (I’m using a c++ SQLite wrapper I wrote myself so hopefully I’ve written the
> above out correctly).
>
> Anyway, on the latest attempt at using the above everything worked fine as
> before. No error messages, stmt executed fine before and after SQL change
> and was finalized without getting an errmsg. This time though I got an error
> message when I tried to close the DB (something along the lines of “unable
> to close db ... unfinalized stmts or ...”.
>
> The reused stmt was the only possible culprit so I’m wondering if it’s down
> to the changed sql?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 inefficient indices, which is more likely if you have not run
> ANALYZE on the fully loaded database.

Creating an intermediate table is one way in which DISTINCT might be
implemented.  The query planner might also try to force the output
into sorted order, so that it can eliminate duplicates simply by
comparing against the previous output.  The second mechanism can cause
the use of inefficient indexes, if they are present and un-ANALYZED.
The query planner computes an estimated run-time for each of various
techniques it considers, and picks the one it thinks will run the
fastest.  Running ANALYZE helps the query planner to generate better
(more accurate) cost estimates.

I have not yet analyzed this situation sufficiently to tell what is going on.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can stmt SQL be changed?

[Simon Slavin]

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()
  _prepare()
_step()
  _reset()
  _prepare()
_step()
  _reset()
_close()

and you’re getting an error returned on _close() rather than on the _prepare() 
itself.

The documentation doesn’t seem to say whether it’s legal or not, but I feel 
that if it isn’t, you should be getting SQLITE_MISUSE returned from the second 
_prepare().

You appear to be doing appropriate error-trapping.  I’m out of ideas but I hope 
with that restatement of your problem someone else can help you.

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

Re: [sqlite] Can stmt SQL be changed?

[x]

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: SQLite mailing list
Subject: 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 for them, just in case 
they’re return an error.  It won’t tell you definitely what’s wrong but it 
might help.

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

Re: [sqlite] Does SQLite use field definitions?

[R Smith]

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 
should to use is TEXT. In SQLite any string value, of any length* can be 
stored in any column, but will be "handled like text" when the column is 
of the TEXT type affinity, and also allow a collation method. Honestly, 
you can put the text of the complete volumes of Don Quixotic in a single 
TEXT field in a database.


* - As long as the string memory doesn't exceed 4GB, the char-size of 
which may differ based on encoding, and if you add the quoted string to 
the query text directly (as opposed to binding it via the API), then the 
maximum size will be governed by the max character length of the query 
input, which can be configured via the API also. You can add the string 
as memory bytes as a BLOB to go bigger, but then you lose collation 
capability I'll stop there, it's probably more unnecessary info than 
you wished for already.


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

Re: [sqlite] Can stmt SQL be changed?

[Simon Slavin]

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 for them, just in case 
they’re return an error.  It won’t tell you definitely what’s wrong but it 
might help.

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

[sqlite] Can stmt SQL be changed?

[x]

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 statement”, -1, , 
NULL) != SQLITE_OK)
throw exception(“.”);


// use stmt again

sqlite3_finalize(stmt);

(I’m using a c++ SQLite wrapper I wrote myself so hopefully I’ve written the 
above out correctly).

Anyway, on the latest attempt at using the above everything worked fine as 
before. No error messages, stmt executed fine before and after SQL change and 
was finalized without getting an errmsg. This time though I got an error 
message when I tried to close the DB (something along the lines of “unable to 
close db ... unfinalized stmts or ...”.

The reused stmt was the only possible culprit so I’m wondering if it’s down to 
the changed sql?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite with Docker and mounted volumes

[Sebastián Guevara]

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.  (Note "Single User" does not mean "Single
> Connection")
>
> > Hi Simon.  Thank you for your answer. It's single user and without
> > concurrency.
> >
> >
> > On Feb 1, 2017 18:32, "Simon Slavin"  wrote:
> >
> >
> > On 1 Feb 2017, at 5:08pm, Sebastián Guevara  >
> > wrote:
> >
> > > Hello to all. We are contemplating using SQLite from within a Docker
> > > container accessing a DB on a mounted volume.
> >
> > Does your setup involve two or more users accessing the data at the same
> > time ?  It’s possible that locking won't work properly.
> >
> > Sorry I can’t answer your question, but the above occurred to me when I
> > read it.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with custom collation

[x]

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: 
sqlite-users@mailinglists.sqlite.org
Subject: 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 (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, 
> NULL, ) != SQLITE_OK)
>
>   throw Exception("Collation creation error");
>
>
>
> I get the message “no such collation sequence: Compare” when running the 
> query.

The second argument passed to sqlite3_create_collation16() should point
to a buffer containing a utf-16 string. Not utf-8.

Dan.



>
>
>
>
>
>
>
> From: Clemens Ladisch
> Sent: 01 February 2017 17:32
> To: 
> sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Help with custom collation
>
>
>
> x wrote:
>> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
>> {
>>  const UTF8String *S1 = static_cast(s1),
>>  *S2 = static_cast(s2);
>>  return 0;
>> }
>>
>>  if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, 
>> NULL, ) != SQLITE_OK)
>>  throw Exception("Collation creation error");
>>
>> S1 and S2 appear to point to NULL values.
> What is "UTF8String"?  If it is anything different from "char", the code is 
> wrong.
>
>> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ 
>> the Compare function is never entered.
>> Is this because ID is an integer column?
> No, it is because the values in that column are integer values.
>
>> Is there no way to implement a custom collation on an integer column?
> There is no way to implement a custom collation for integer values.
> Collations are used only for string values.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Help with custom collation

[Dan Kennedy]

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 (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, 
) != SQLITE_OK)

  throw Exception("Collation creation error");



I get the message “no such collation sequence: Compare” when running the query.


The second argument passed to sqlite3_create_collation16() should point 
to a buffer containing a utf-16 string. Not utf-8.


Dan.











From: Clemens Ladisch
Sent: 01 February 2017 17:32
To: 
sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Help with custom collation



x wrote:

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
{
 const UTF8String *S1 = static_cast(s1),
 *S2 = static_cast(s2);
 return 0;
}

 if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
) != SQLITE_OK)
 throw Exception("Collation creation error");

S1 and S2 appear to point to NULL values.

What is "UTF8String"?  If it is anything different from "char", the code is 
wrong.


If I change the select to ‘select ID from IDTbl order by ID collate Compare’ 
the Compare function is never entered.
Is this because ID is an integer column?

No, it is because the values in that column are integer values.


Is there no way to implement a custom collation on an integer column?

There is no way to implement a custom collation for integer values.
Collations are used only for string values.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Help with custom collation

[Hick Gunter]

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' 
Betreff: 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 objects

To access the arguments, you must call one of the sqlite3_value() functions.

When implementign an aggregate function, you must provide callbacks for 
aggregation (xStep) and returning the value (xFinal)

See http://sqlite.org/c3ref/create_function.html

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Mittwoch, 01. Februar 2017 17:45
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Help with custom collation

The collation function used was as follows

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2) {
const UTF8String *S1 = static_cast(s1),
*S2 = static_cast(s2);
return 0;
}

Which was registered with the following code

if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");

The above is merely a tester where I’ve set a breakpoint at the ‘return 0’ 
line. I tried it with the following stmt

‘select ID from IDTbl order by Name collate Compare’.

I’m having the following problems


  1.  When it stops at the breakpoint the debugger shows the Len1 and Len2 
params with correct looking values but the S1 and S2 appear to point to NULL 
values. I just keep getting ‘???’ values. Am I casting wrongly or something.


  1.  If I change the select to ‘select ID from IDTbl order by ID collate 
Compare’ the Compare function is never entered. Is this because ID is an 
integer column? Is there no way to implement a custom collation on an integer 
column?



  1.  If I change the registration to
if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");
it seems to register OK but running the select yields a “no such 
collation sequence: Compare” error.

Can any kind soul cast any light on this for me.

PS I’m using Embarcadero C++ builder 10.1 Berlin on windows 10 with the 
sqlite3.c amalgamation included in my project.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

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

[Hick Gunter]

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 ANALYZE 
on the fully loaded database.

Using a 3 stage pipe instead you additionally have more CPUs (1 running the 
query, 1 or more sorting the results) working in paralell.

Try EXPLAIN QUERY PLAN to see what the query planner is doing.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Kevin O'Gorman
Gesendet: Donnerstag, 02. Februar 2017 03:28
An: sqlite-users 
Betreff: [sqlite] "DISTINCT" makes a query take 37 times as long

I have a database of positions and moves in a strategic game, and I'm searching 
for unsolved positions that have been connected to an immediate ancestor.  I'm 
using Python 3.5.2, and the code looks like

#!/usr/bin/env python3
"""Output positions that are reachable but unsolved at census 18 or greater See 
page 76 of Qubic log

Last Modified: Tue Jan 31 12:13:07 PST 2017 """

import sqlite3  # https://docs.python.org/3.5/library/sqlite3.html

with sqlite3.connect("917.db") as conn:
for row in conn.execute("""
SELECT DISTINCT ppos
FROM move JOIN pos ON mto = pnum
WHERE pcensus = 18 and pmin < pmax
"""):
print(row[0])

As written here, this query runs for 1193 minutes (just short of 20 hours).  If 
I remove the "DISTINCT" and instead pipe the result into the sort program that 
comes with Linux "sort --unique" the query and sort takes only 31 minutes.  The 
results are the same, and consist of 4.2 million rows.

This seems extreme.

--
word of the year: *kakistocracy*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Help with custom collation

[Hick Gunter]

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 
objects

To access the arguments, you must call one of the sqlite3_value() functions.

When implementign an aggregate function, you must provide callbacks for 
aggregation (xStep) and returning the value (xFinal)

See http://sqlite.org/c3ref/create_function.html

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Mittwoch, 01. Februar 2017 17:45
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Help with custom collation

The collation function used was as follows

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2) {
const UTF8String *S1 = static_cast(s1),
*S2 = static_cast(s2);
return 0;
}

Which was registered with the following code

if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");

The above is merely a tester where I’ve set a breakpoint at the ‘return 0’ 
line. I tried it with the following stmt

‘select ID from IDTbl order by Name collate Compare’.

I’m having the following problems


  1.  When it stops at the breakpoint the debugger shows the Len1 and Len2 
params with correct looking values but the S1 and S2 appear to point to NULL 
values. I just keep getting ‘???’ values. Am I casting wrongly or something.


  1.  If I change the select to ‘select ID from IDTbl order by ID collate 
Compare’ the Compare function is never entered. Is this because ID is an 
integer column? Is there no way to implement a custom collation on an integer 
column?



  1.  If I change the registration to
if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, 
) != SQLITE_OK)
throw Exception("Collation creation error");
it seems to register OK but running the select yields a “no such 
collation sequence: Compare” error.

Can any kind soul cast any light on this for me.

PS I’m using Embarcadero C++ builder 10.1 Berlin on windows 10 with the 
sqlite3.c amalgamation included in my project.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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