Re: [sqlite] How to refer to `this` table?

[Abroży Nieprzełoży]

2019-02-23 10:03 GMT+01:00, Rocky Ji:
> I went from
>
> https://sqlite.org/lang_createtable.html
>
> to
>
> https://sqlite.org/syntax/table-constraint.html
>
> to
>
> https://sqlite.org/syntax/expr.html
>
> and figured expr of `check` in table constraint may contain a nested select
> after `not in`.

These diagrams describe the general syntax recognized by the parser
but a parsable statement can still be rejected at a later compilation
step.



>
> On Sat, Feb 23, 2019, 1:24 PM Clemens Ladisch wrote:
>
>> Rocky Ji wrote:
>> > CREATE TABLE Aliases (
>> >   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
>> >   real_name TEXT NOT NULL,
>> >   aka TEXT NOT NULL,
>> >   CONSTRAINT xyz UNIQUE (real_name, aka),
>> >   CONSTRAINT noCircularRef_A CHECK (
>> > real_name NOT IN (SELECT aka FROM Aliases)
>> >   ),
>> >   CONSTRAINT noCircularRef_B CHECK (
>> > aka NOT IN (SELECT real_name FROM Aliases)
>> >   )
>> > );
>> >
>> > Error: no such table: Aliases
>>
>>  says:
>> | The expression of a CHECK constraint may not contain a subquery.
>>
>> You'd have to write triggers to check this:
>>
>> CREATE TRIGGER noCircularRef_insert
>> AFTER INSERT ON Aliases
>> FOR EACH ROW
>> WHEN NEW.real_name IN (SELECT aka FROM Aliases)
>>   OR NEW.aka IN (SELECT real_name FROM Aliases)
>> BEGIN
>>   SELECT RAISE(FAIL, "circular reference");
>> END;
>> -- same for AFTER UPDATE OF real_name, aka
>>
>>
>> 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] Persistent snapshots and rollbacks

[Abroży Nieprzełoży]

The sessions extension: https://www.sqlite.org/sessionintro.html

2018-10-05 17:39 GMT+02:00, Daniel Kraft :
> Hi!
>
> I need the ability to make multiple changes / commits to my SQLite
> database but keep snapshots of previous states and potentially roll back
> to those states later on.  All of that needs to be persistent, i.e.
> survive closing the database and restarting the process.  After some
> time, I can get rid of old snapshots (my process determines by itself
> when and which snapshots can get discarded, it is not based on some
> fixed TTL or something like that).
>
> Is it possible to do all that with SQLite?
>
> From reading the docs, it seems to me that (persistent) WAL mode
> basically does *exactly that* internally:  Changes are recorded in the
> logs so that previous versions are retained.  Rollbacks would be
> possible by "simply" discarding the WAL entries after the desired
> snapshot.  And discarding of very old snapshots corresponds to
> checkpointing.
>
> However, I'm not sure if all of that functionality is (officially)
> exposed to me as a user.  There are in particular two points where I
> think that my requirements differ from the functionality that WAL mode
> exposes:
>
> 1) Handles to snapshots can be obtained and stored, but they are
> read-only.  It seems to be not possible to tell SQLite to restore the
> WAL to a previous version and then continue modifying from that version.
>  (Which basically means truncating the WAL file at a certain point.)
>
> 2) From what I have seen, checkpointing can only be triggered for the
> full WAL (or whatever is possible with existing readers) and not
> selectively up to a desired point.  Of course I could work around that
> by creating a reader at the point I want to keep.  But then I wonder if
> it is a problem if the WAL can never be *fully* checkpointed (as in my
> requirement).  Would that mean that it keeps on growing forever, or is
> checkpointing able to remove parts from the beginning of the WAL?
>
> Is my understanding here correct?  And is there some way in which I
> could achieve my requirements using WAL mode (or somehow else)?
>
> Thank you very much!
>
> Yours,
> Daniel
>
> --
> https://www.domob.eu/
> OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
> Namecoin: id/domob -> https://nameid.org/?name=domob
> --
> 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
> To go: Arc-Cav-Hea-Kni-Mon-Tou
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get data in one query

[Abroży Nieprzełoży]

Julian Day is defined in terms of Universal Time.
https://en.wikipedia.org/wiki/Julian_day


2018-08-29 22:33 GMT+02:00, David Raymond :
> Good to know. Is that actually documented anywhere? All I see is...
>
>
> https://www.sqlite.org/lang_datefunc.html
> "Format 11, the string 'now', is converted into the current date and time as
> obtained from the xCurrentTime method of the sqlite3_vfs object in use."
>
>
> https://www.sqlite.org/c3ref/vfs.html
> "The xCurrentTime() method returns a Julian Day Number for the current date
> and time as a floating point value. The xCurrentTimeInt64() method returns,
> as an integer, the Julian Day Number multiplied by 8640 (the number of
> milliseconds in a 24-hour day). SQLite will use the xCurrentTimeInt64()
> method to get the current date and time if that method is available (if
> iVersion is 2 or greater and the function pointer is not NULL) and will fall
> back to xCurrentTime() if xCurrentTimeInt64() is unavailable."
>
>
> Nothing seems to say "this (is/should be) in (UTC/localtime)" or even "the
> default vfs object gives UTC, but this is an implementation detail and
> should not be relied upon"
>
> Is there any way to know what you're getting? Or to explicitly ask for one?
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Keith Medcalf
> Sent: Wednesday, August 29, 2018 3:45 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Get data in one query
>
>
> ... don't forget that Date('now') returns the UT1 date, not the local (as in
> Wall Clock/Calendar) date ... date('now', 'localtime') gives the local date
> in accordance with the timezone where your computer thinks it is located and
> should always be accurate for 'now' but maybe not a few years in the past on
> Operating Systems that do not contain/use a full set of UT1 -> localtime
> (timezone) conversion rules (such as Windows).
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof
>>Sent: Wednesday, 29 August, 2018 12:46
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Get data in one query
>>
>>2018-08-29 18:58 GMT+02:00 Cecil Westerhof :
>>
>>> 2018-08-29 18:06 GMT+02:00 R Smith :
>>>

 SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
   FROM (SELECT 1 AS Tot,  (time NOT LIKE '%:00') AS Late
   FROM messages
  WHERE date = DATE('now')
)

>>>
>>> Works like a charm. Thanks.
>>>
>>> I made it even more useful:
>>> SELECT Total
>>> ,   Late
>>> ,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>>> FROM(
>>> SELECT SUM(total) AS Total
>>> ,  SUM(late)  AS Late
>>> FROM  (
>>> SELECT 1  AS Total
>>> ,  (time NOT LIKE '%:00') AS Late
>>> FROM   messages
>>> WHERE  date = DATE('now')
>>> )
>>> )
>>>
>>
>>And even more useful:
>>SELECT date
>>,   Total
>>,   Late
>>,   CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
>>FROM(
>>SELECT date
>>,  SUM(total) AS Total
>>,  SUM(late)  AS Late
>>FROM  (
>>SELECT date
>>,   1  AS Total
>>,  (time NOT LIKE '%:00') AS Late
>>FROM   messages
>>WHERE  date >= DATE('now', '-7 days')
>>   AND date  < DATE('now')
>>)
>>GROUP BY date
>>)
>>ORDER BY date DESC
>>
>>--
>>Cecil Westerhof
>>___
>>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] bug: compare number with empty string get different resut

[Abroży Nieprzełoży]

It's documented behaviour.
https://www.sqlite.org/datatype3.html#comparisons


2018-08-19 8:58 GMT+02:00, 麦轲数据管家 :
> create table t1(c int);
> insert into t1 values (1),(2),(3);
> select * from t1 where c>'' ;   --get nothing(empty result)
> select * from t1 where c<'';   --get result set:1,2,3
>
>
> create table t2(c char);
> insert into t2 values (1),(2),(3);
> select * from t2 where c>'';--get correct result set: 1,2,3
>
> the only difference of t1 and t2 is the column data type: int vs char
> ___
> 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] Save text file content in db: lines or whole file?

[Abroży Nieprzełoży]

substr(data, start, len) loads entire value and then substrs it.
sqlite3_blob_read reads the desired parts.

2018-08-06 20:59 GMT+02:00, Abramo Bagnara :
> Il 04/08/2018 07:07, Abramo Bagnara ha scritto:
>> Il 03/08/2018 23:53, Abroży Nieprzełoży ha scritto:
>>> -- One table with whole files
>>> CREATE TABLE content(
>>> id INTEGER PRIMARY KEY,
>>> data BLOB NOT NULL
>>> );
>>> -- And second table with line boundaries
>>> CREATE TABLE lines(
>>> id INTEGER NOT NULL REFERENCES content(id),
>>> line_no INTEGER NOT NULL,
>>> bytes_from INTEGER NOT NULL,
>>> bytes_to INTEGER NOT NULL,
>>> PRIMARY KEY(id, line_num)
>>> ) WITHOUT ROWID;
>>> -- Use Incremental BLOB I/O https://www.sqlite.org/c3ref/blob_open.html
>>
>> Incremental BLOB I/O is faster than using substr(data, start, len) in a
>> SELECT?
>
> Someone familiar with implementation of BLOB I/O and sqlite VM can
> answer to that?
>
> I'd guess that *if* there is a difference it is greater with bigger
> blobs, but it is also possible there is not any sensible difference if
> substr act similarly to sqlite3_blob_read.
>
> But my uninformed guesses are definitely not relevant, until an aware
> Samaritan will come to clarify that ;-)
>
> --
> Abramo Bagnara
>
> BUGSENG srl - http://bugseng.com
> mailto:abramo.bagn...@bugseng.com
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Save text file content in db: lines or whole file?

[Abroży Nieprzełoży]

-- One table with whole files
CREATE TABLE content(
id INTEGER PRIMARY KEY,
data BLOB NOT NULL
);
-- And second table with line boundaries
CREATE TABLE lines(
id INTEGER NOT NULL REFERENCES content(id),
line_no INTEGER NOT NULL,
bytes_from INTEGER NOT NULL,
bytes_to INTEGER NOT NULL,
PRIMARY KEY(id, line_num)
) WITHOUT ROWID;
-- Use Incremental BLOB I/O https://www.sqlite.org/c3ref/blob_open.html

I would also consider keeping the largest files external to the
database. https://www.sqlite.org/intern-v-extern-blob.html

2018-08-03 21:04 GMT+02:00, Abramo Bagnara :
>
> I need to save text files (let say between 1 KB to 20 MB) in a SQLite DB.
>
> I see two possibilities:
>
> 1) save all the content in a single column:
>
> create table content(id integer not null primary key,
>  text blob not null);
>
> 2) split the content in lines:
>
> create table line(content integer not null,
> line integer not null,
>   text blob not null,
>   primary key(content, line));
>
> Some queries will need to extract the whole file, while other queries
> will need to extract the text for a range of lines.
>
> According to your experience it is better/faster the first option, the
> second option or a smarter option I've not considered?
>
> My partial considerations are:
>
> - 1 is simpler
> - 1 leads to faster load
> - 1 is slower to retrieve a range of lines (not 100% sure)
>
> --
> Abramo Bagnara
> ___
> 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] Sqlite Sharding HOWTO

[Abroży Nieprzełoży]

> Ideally, I would like to have a way of "seeing" the whole dataset with a
> single query spanning all  available databases.

I think swarmvtab may be helpful.
https://www.sqlite.org/swarmvtab.html


2018-07-29 10:34 GMT+02:00, Gerlando Falauto :
> Hi,
>
> I'm totally new to sqlite and I'd like to use it for some logging
> application on an embedded
> linux-based device.  Data comes from multiple (~10), similar sources at a
> steady rate.
> The rolling data set would be in the size of 20 GB. Such an amount of
> storage would suffice to retain data from the previous week or so.
>
> Reading the documentation https://www.sqlite.org/whentouse.html somehow
> suggests the usage of sharding:
>
>>Concurrency is also improved by "database sharding": using separate
> database files for
>> different subdomains. For example, the server might have a separate
> SQLite database for each
>> user, so that the server can handle hundreds or thousands of simultaneous
> connections, but
>> each SQLite database is only used by one connection.
>
> In my case I would be doing sharding on the data source and/or the day of
> the timestamp, so to have individual files in the size of a few hundreds MB.
> This way, deleting the oldest data would be as easy as deleting the
> corresponding file.
>
> However, I did not find any reference whatsoever on sharding being
> available _within_ sqlite.
> Ideally, I would like to have a way of "seeing" the whole dataset with a
> single query spanning all  available databases.
>
> Would that be at all feasible? I saw the "attach database" statement which
> seems closely related but whose use-case I honestly don't get.
> If not, is there any state-of-the-art adapter layer that would be
> performing (and hide) the underlying sharding? I don't really care about
> query performance (e.g. if such a global query spanning 20 different
> databases is indeed performed serially, thereby take 20 times longer), I
> just need a way of hiding this detail.
>
> I saw some reference to SPHiveDB
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg43575.html
> but the project looks stale (9 years since the last commit).
>
> I also looked into AtomDB but it looks overly complicated for my use-case
> (single, embedded server), plus it somehow requires the underlying sharding
> to be totally exposed.
>
> Any ideas?
> Gerlando
> ___
> 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] GROUP BY [Re: unique values from a subset of data based on two fields]

[Abroży Nieprzełoży]

>>> SELECT
>>>a,
>>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>> FROM t1 t
>>> GROUP BY a
>> Why not
>> select a, min(b) as b, min(c) as c from t1 group by a;
>> ?
>
> It still does not quarantee that the valuse show for b and c are comming
> from the same row...

select a, b, min(c) as c from (select a, min(b) as b from t1 group by
a) join t1 using(a, b) group by a, b;

?

2018-06-30 15:45 GMT+02:00, Luuk :
>
>
> On 30-6-2018 15:39, Abroży Nieprzełoży wrote:
>>> SELECT
>>>a,
>>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>> FROM t1 t
>>> GROUP BY a
>> Why not
>> select a, min(b) as b, min(c) as c from t1 group by a;
>> ?
>
> It still does not quarantee that the valuse show for b and c are comming
> from the same row...
>
>
>>
>> 2018-06-30 15:12 GMT+02:00, Luuk :
>>> On 30-6-2018 14:55, Keith Medcalf wrote:
>>>> Note that this is SQLite3 specific (and specific to Sybase of the era
>>>> where Microsoft SQL Server was actually just a rebranded Sybase, and
>>>> Microsoft re-writes of SQL Server up to about 2000).  Technically you
>>>> cannot do a query of the form:
>>>>
>>>> SELECT c1, c2
>>>>   FROM t1
>>>> GROUP BY c2;
>>>>
>>>> because each column in the select list must be either an aggregate or
>>>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
>>>> however and the value returned is taken from "some random row" of the
>>>> group.  If there are multiple such columns, they all come from the same
>>>> row in the group.  Although documented as a "random" row of the group,
>>>> it
>>>> is the first (or last) row visited in the group while solving the query
>>>> (and this is of course subject to change but within the same version of
>>>> SQLite3 will deterministically be the row either first or last in the
>>>> visitation order -- the actual row may of course change depending on use
>>>> of indexes, etc).  You can re-write this part so it will work in other
>>>> SQL
>>>> dialects that strictly enforce the requirement for c1 to be either an
>>>> aggregate or listed in the group by clause.
>>>>
>>>> ---
>>>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>>>> says
>>>> a lot about anticipated traffic volume.
>>>>
>>> Ok ,my highway to hell start here (regargind the use of SQL)
>>>
>>> In SQLite3 you are allowed to do this:
>>> SELECT a,b,c
>>> FROM t1
>>> GROUP BY a
>>>
>>> The values of 'b' and 'c' will be taken from a 'random' row...
>>>
>>> But if we rewrite this in SQL, i am getting something like this:
>>> SELECT
>>>a,
>>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>> FROM t1 t
>>> GROUP BY a
>>>
>>> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
>>> i mean how can one be use that both values are from the same row?
>>> This is not a problem to SQLite, because in SQLite the values of b and c
>>> seems to be originating from the same row, but what about *SQL* (if that
>>> exists...?)
>>>
>>> --
>>> some test results:
>>> sqlite> insert into t1 values (1,1,2);
>>> sqlite> insert into t1 values (1,2,1);
>>> sqlite> insert into t1 values (2,2,1);
>>> sqlite> insert into t1 values (2,1,2);
>>> sqlite> select a,b,c from t1 group by a;
>>> 1|2|1
>>> 2|1|2
>>> sqlite> SELECT
>>>...>a,
>>>...>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>>...>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>>...> FROM t1 t
>>>...> GROUP BY a;
>>> 1|1|1
>>> 2|1|1
>>> sqlite>
>>>
>>> ___
>>> 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] GROUP BY [Re: unique values from a subset of data based on two fields]

[Abroży Nieprzełoży]

> SELECT
>a,
>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a

Why not
select a, min(b) as b, min(c) as c from t1 group by a;
?


2018-06-30 15:12 GMT+02:00, Luuk :
>
> On 30-6-2018 14:55, Keith Medcalf wrote:
>> Note that this is SQLite3 specific (and specific to Sybase of the era
>> where Microsoft SQL Server was actually just a rebranded Sybase, and
>> Microsoft re-writes of SQL Server up to about 2000).  Technically you
>> cannot do a query of the form:
>>
>> SELECT c1, c2
>>   FROM t1
>> GROUP BY c2;
>>
>> because each column in the select list must be either an aggregate or
>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
>> however and the value returned is taken from "some random row" of the
>> group.  If there are multiple such columns, they all come from the same
>> row in the group.  Although documented as a "random" row of the group, it
>> is the first (or last) row visited in the group while solving the query
>> (and this is of course subject to change but within the same version of
>> SQLite3 will deterministically be the row either first or last in the
>> visitation order -- the actual row may of course change depending on use
>> of indexes, etc).  You can re-write this part so it will work in other SQL
>> dialects that strictly enforce the requirement for c1 to be either an
>> aggregate or listed in the group by clause.
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven says
>> a lot about anticipated traffic volume.
>>
> Ok ,my highway to hell start here (regargind the use of SQL)
>
> In SQLite3 you are allowed to do this:
> SELECT a,b,c
> FROM t1
> GROUP BY a
>
> The values of 'b' and 'c' will be taken from a 'random' row...
>
> But if we rewrite this in SQL, i am getting something like this:
> SELECT
>a,
>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a
>
> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> i mean how can one be use that both values are from the same row?
> This is not a problem to SQLite, because in SQLite the values of b and c
> seems to be originating from the same row, but what about *SQL* (if that
> exists...?)
>
> --
> some test results:
> sqlite> insert into t1 values (1,1,2);
> sqlite> insert into t1 values (1,2,1);
> sqlite> insert into t1 values (2,2,1);
> sqlite> insert into t1 values (2,1,2);
> sqlite> select a,b,c from t1 group by a;
> 1|2|1
> 2|1|2
> sqlite> SELECT
>...>a,
>...>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>...>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>...> FROM t1 t
>...> GROUP BY a;
> 1|1|1
> 2|1|1
> sqlite>
>
> ___
> 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] column types and constraints

[Abroży Nieprzełoży]

SQLite returns what has been saved in the database, the application
should control what it writes to the database.

If the database can be edited by an untrusted entity, the application
should treat the content of the database as untrusted.

Even if SQLite enforced data types, someone who can edit the database
could also change the schema accordingly to allow to store malicious
data.


2018-06-29 21:00 GMT+02:00, Warren Young :
> On Jun 29, 2018, at 11:46 AM, Richard Hipp  wrote:
>>
>> On 6/29/18, Bob Friesenhahn  wrote:
>>>
>>> Without adding all the necessary safe-guards to ensure that only valid
>>> data goes into the database, sqlite puts the using application at risk
>>> (security and stability) with its wishy-washy ways.
>>
>> Can you provide an example of a security of stability problem caused
>> by flexible typing?
>
> The following is an answer to your challenge, not a feature request.  I’d
> enable strong typing support in SQLite if it were available, but I started
> using SQLite knowing its stance on typing, so I can’t honorably demand it
> now.
>
> Nevertheless:
>
> 1. In defining a table schema, declare a column as UNSIGNED INTEGER; SQLite
> ignores the “UNSIGNED” and uses INTEGER affinity.
>
> 2. Store “-1FRED” in that column, for which SQLite uses TEXT affinity, so as
> to not lose any data.
>
> 3. Retrieve the value with sqlite3_column_int(), which forces a CAST to INT,
> resulting in -1, that being the longest prefix that is INT-like.
>
> 4. The application uses the value as an index into an array.  If the
> application passes int* to sqlite3_column_int() to avoid compiler
> complaints, they’ll get a negative index.  If they pass unsigned* instead,
> casting it to int* to placate the compiler, they get UINT_MAX on a 2’s
> complement machine, which will certainly crash the program when used as an
> array index.  Either way, a security exploit is probably available.
>
> If your reaction is that the application shouldn’t have allowed input of
> “-1FRED” for an integer value, that’s true, but it would be nice if SQLite
> would backstop the application’s restrictions.  The application tried to
> tell SQLite it wanted help enforcing its limits when giving the UNSIGNED
> attribute in declaring the table schema.
>
> If you say that the application shouldn’t have trusted the value it got from
> SQLite, why not?  From the application programmer’s perspective, it’s now
> validated data.
>
> It is of course possible to work around all of this.  The application
> programmer “just” has to write checks in layers closer to the end user,
> checks which are not strictly necessary with other DBMSes.  People coming
> from those other DBMSes reasonably expect the data to be implicitly
> trustworthy once it is finally at rest.
> ___
> 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] Check if the new table has been created

[Abroży Nieprzełoży]

But that trigger would be executed in a context of a process modifying
the database.

2018-06-20 8:24 GMT+02:00, Peter Johnson :
> Is it possible to create a trigger on sqlite_master which calls a
> user-defined function AFTER INSERT?
>
> That would avoid having to poll, but it'd still allow the application to be
> notified when the schema changed.
>
> On 19 June 2018 at 20:56, Richard Hipp  wrote:
>
>> On 6/19/18, Igor Korot  wrote:
>> > Hi, Wout,
>> >
>> > On Tue, Jun 19, 2018 at 1:31 PM, Wout Mertens 
>> > wrote:
>> >> you can query the table with
>> >> https://www.sqlite.org/pragma.html#pragma_table_info
>> >
>> > Let me give you a scenario:
>> >
>> > 1. My application connects to the database and performs some
>> > operations (using C API).
>> > 2. During the application run, someone started sqlite3, connects to
>> > the database and creates a
>> > brand new table.
>> > 3. My application will need to pick up the newly created table and
>> continue.
>> >
>> > Is it easily possible?
>> >
>> > There is a sqlite3_*_hook() family of functions, but it looks like
>> > they won't help with sqlite_master.
>> >
>> > Is there a different way?
>>
>> Poll the PRAGMA schema_version value and watch for changes.
>> --
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

[Abroży Nieprzełoży]

afaik there is no such api.
You need to periodically check if something changed.

Run
pragma schema_version;
to get current schema version.
If it changes then run
select name from sqlite_master where type='table' and name not
like 'sqlite_%';
to get the list of table names and compare this list with one you've
got previously.


2018-06-19 20:42 GMT+02:00, Igor Korot :
> Hi, Wout,
>
> On Tue, Jun 19, 2018 at 1:31 PM, Wout Mertens 
> wrote:
>> you can query the table with
>> https://www.sqlite.org/pragma.html#pragma_table_info
>
> Let me give you a scenario:
>
> 1. My application connects to the database and performs some
> operations (using C API).
> 2. During the application run, someone started sqlite3, connects to
> the database and creates a
> brand new table.
> 3. My application will need to pick up the newly created table and continue.
>
> Is it easily possible?
>
> There is a sqlite3_*_hook() family of functions, but it looks like
> they won't help with sqlite_master.
>
> Is there a different way?
>
> Thank you.
>
>>
>> On Tue, Jun 19, 2018, 8:26 PM Igor Korot  wrote:
>>
>>>  Hi,
>>> Is there a C API which checks if the new table has been created?
>>>
>>> Thank you.
>>> ___
>>> 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] Possible Input Parser Issue Inf How to convert SQL file into database when a column value is Inf?

[Abroży Nieprzełoży]

or .dump should produce output with 1e999 / -1e999 instead of Inf / -Inf

2018-06-13 1:11 GMT+02:00, Chris Brody :
> On Tue, Jun 12, 2018 at 6:40 PM Richard Hipp  wrote:
>> [...]
>> Maybe use 1e999 and -1e999 instead?
>
> I can confirm on SQLite versions 3.19.2 & 3.24.0:
>
> sqlite> select 1e999;
> Inf
> sqlite> select -1e999;
> -Inf
>
> I wouldn't mind it if SQLite would be a little more symmetrical, i.e.
> output of .dump with Inf or -Inf values would be valid input.
>
> Chris
> ___
> 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] Selecting with distinct on across two columns

[Abroży Nieprzełoży]

SQLite allows you to simply do:
select colA, colB, colC from tab group by colA, colB;
You can also do:
select colA, colB, min(colC) from tab group by colA, colB;
or:
select colA, colB, max(colC) from tab group by colA, colB;

2018-06-10 1:41 GMT+02:00, Joseph L. Casale :
> I have a table where I need to select all distinct records from two columns
> and
> include a third that varies. It is possible that for multiple records where
> the first
> and second columns are equivalent, the third varies so it cannot be used in
> the
> distinct clause. In this case, I want to select the third column in the
> first record
> and ignore the remaining to append to the final result.
>
> For example:
>
> colA | colB | colC
> ---
> aaa | bbb | lorem ipsum
> aaa | bbb | lorem ipsum dolar
> aaa | ccc | foo bar
>
> This should only return the first and third row.
>
> How do you do this in SQLite?
>
> Thanks,
> jlc
> ___
> 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] A fix for the exp-window-functions branch to build by nmake.

[Abroży Nieprzełoży]

Index: Makefile.msc
===
--- Makefile.msc
+++ Makefile.msc
@@ -1305,11 +1305,12 @@
   $(TOP)\src\vtab.c \
   $(TOP)\src\wal.c \
   $(TOP)\src\walker.c \
   $(TOP)\src\where.c \
   $(TOP)\src\wherecode.c \
-  $(TOP)\src\whereexpr.c
+  $(TOP)\src\whereexpr.c \
+  $(TOP)\src\window.c

 # Core miscellaneous files.
 #
 SRC03 = \
   $(TOP)\src\parse.y
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie help

[Abroży Nieprzełoży]

You can use this quick piece of software listed below to
redirect std i/o of a program to a tcp connection.

Assuming the executable file is named tcpcmd.exe. Following command line
   tcpcmd 12345 "sqlite3 -batch"
will run "sqlite3 -batch" and listen on tcp port 12345 (for security
reasons it listens only on loopback interface (127.0.0.1)).

(You may need to link with ws2_32.lib)
-
#include 
#include 

#define BUFFER_SIZE 1024
#define OV_ENTRIES 2

enum TransferState
{
TS_NULL,
TS_READING,
TS_READY,
TS_WRITING
};

WSADATA g_WsaData;
PROCESS_INFORMATION g_Process;
SOCKET g_sockLst, g_sockClient;
HANDLE g_hInWr, g_hInRd, g_hOutWr, g_hOutRd;
HANDLE g_hIOCP;
wchar_t* g_zCommand;
int g_nPort;
int g_bConnected = 0;
enum TransferState g_eInState = TS_NULL, g_eOutState = TS_NULL;
OVERLAPPED g_ovIn, g_ovOut;
WSABUF g_wbIn, g_wbOut;
DWORD g_flgWsaIn;
DWORD g_nIn, g_nOut;
char g_bufIn[BUFFER_SIZE], g_bufOut[BUFFER_SIZE];

void printError(const wchar_t* zFunc, DWORD nErr);
int parseArgs(int argc, wchar_t* argv[]);
int openPort();
int acceptClient();
void closeClient();
int runCommandProcess();
int main_loop();

int wmain(int argc, wchar_t* argv[])
{
int rc = parseArgs(argc, argv);
if (rc)
return rc;
rc = WSAStartup(MAKEWORD(2, 2), _WsaData);
if (rc)
{
printError(L"WSAStartup", rc);
return rc;
}
rc = openPort();
if (!rc)
{
rc = runCommandProcess();
if (!rc)
{
while (main_loop() == 0);
TerminateProcess(g_Process.hProcess, 0);
}
closeClient();
closesocket(g_sockLst);
}
WSACleanup();
return rc;
}

int main_loop()
{
int rc;
DWORD dwProcExit;
ULONG i;
ULONG nOvEntries = 0;
OVERLAPPED_ENTRY ovEntries[OV_ENTRIES];
rc = GetExitCodeProcess(g_Process.hProcess, );
if (!rc)
{
printError(L"GetExitCodeProcess", GetLastError());
return 1;
}
if (dwProcExit != STILL_ACTIVE)
return 1;
switch (g_eInState)
{
case TS_NULL:
rc = acceptClient();
if (rc)
break;
memset(_ovIn, 0, sizeof(g_ovIn));
g_wbIn.buf = g_bufIn;
g_wbIn.len = BUFFER_SIZE;
g_nIn = 0;
g_flgWsaIn = 0;
rc = WSARecv(g_sockClient, _wbIn, 1, NULL, _flgWsaIn, 
_ovIn, NULL);
if (rc)
{
DWORD err = WSAGetLastError();
if (err != ERROR_IO_PENDING)
{
printError(L"WSARecv", err);
closeClient();
return 0;
}
}
g_eInState = TS_READING;
break;
case TS_READY:
memset(_ovIn, 0, sizeof(g_ovIn));
rc = WriteFile(g_hInWr, g_bufIn, g_nIn, NULL, _ovIn);
if (!rc)
{
DWORD err = GetLastError();
if (err != ERROR_IO_PENDING)
{
printError(L"WriteFile", err);
return 1;
}
}
g_eInState = TS_WRITING;
break;
default:
break;
}
switch (g_eOutState)
{
case TS_NULL:
memset(_ovOut, 0, sizeof(g_ovOut));
rc = ReadFile(g_hOutRd, g_bufOut, BUFFER_SIZE, NULL, _ovOut);
if (!rc)
{
DWORD err = GetLastError();
if (err != ERROR_IO_PENDING)
{
printError(L"ReadFile", err);
return 1;
}
}
g_eOutState = TS_READING;
break;
case TS_READY:
rc = acceptClient();
if (rc)
break;
memset(_ovOut, 0, sizeof(g_ovOut));
g_wbOut.buf = g_bufOut;
g_wbOut.len = g_nOut;
rc = WSASend(g_sockClient, _wbOut, 1, NULL, 0, _ovOut, 
NULL);
if (rc)
{
DWORD err = WSAGetLastError();
if (err != ERROR_IO_PENDING)
{
printError(L"WSASend", err);
closeClient();
return 0;

Re: [sqlite] Subject: Re: SQL Date Import

[Abroży Nieprzełoży]

> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?

I don't think so. The SQLite team really cares about backward compatibility.

You can store timestamp as a unixepoch integer or as a text in format
supported by date/time functions.
http://www.sqlite.org/draft/lang_datefunc.html


2018-06-02 21:55 GMT+02:00, Thomas Kurz :
> Are there any plans to implement a DATETIME and/or TIMESTAMP field types?
>
> - Original Message -
> From: Simon Slavin 
> To: SQLite mailing list 
> Sent: Saturday, June 2, 2018, 21:04:10
> Subject: [sqlite] Subject: Re:  SQL Date Import
>
> On 2 Jun 2018, at 7:32pm, dmp  wrote:
>
>> By the way, most databases give exactly that INSERT when dumping data
>> for DATE, TIME, TIMESTAMP, etc., text. I'm not advocating a preferred
>> type for storage here.
>
> I think your proposed programme of experimentation is the right way to
> pursue this.  But I wanted to save you some time.
>
> SQLite doesn't have a DATE type.  You can store dates in a SQLite database
> as text, or integers or floating point numbers (e.g. "20180602", a number of
> days, a number of seconds).  But when you ask for a value, that's what
> you'll get back.  Any interpretation of that value as a date is up to you or
> your software.
>
> 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

Re: [sqlite] random rows

[Abroży Nieprzełoży]

hmm... i think:

begin transaction;
query 'select count(*) from tab' to get total number of records, name it N
randomly select 200 integers from the range 0 to N-1
for each number query 'select * from tab limit 1 offset ?' with '?'
bound to selected number
end transaction;



2018-06-01 2:12 GMT+02:00, Torsten Curdt :
> I need to get some random rows from a large(ish) table.
>
> The following seems to be the most straight forward - but not the fastest.
>
>   SELECT * FROM table ORDER BY random() limit 200
>
> Is there a faster/better approach?
>
> cheers,
> Torsten
> ___
> 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] How does _exec() do a transaction ?

[Abroży Nieprzełoży]

sqlite3_exec doesn't open transaction by itself.

Each statement is prepared and executed separately.


2018-05-30 15:33 GMT+02:00, Simon Slavin :
> On 30 May 2018, at 2:30pm, Simon Slavin  wrote:
>
>> Does SQLite perform the whole _exec() in one transaction or each command
>> in a separate transaction ?
>
> Subsidiary question:
>
> Does SQLite parse the entire string of commands for a syntax error first,
> triggering an error if anything is wrong before executing the first command
> ?  Or does it execute the first command, then parse the next part of the
> string for a command ?
>
> 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] [EXTERNAL] Re: database locked on select

[Abroży Nieprzełoży]

Do the select and updates run inside a explicit transaction or they
run in individual implicit transactions?

pseudocode:
-
exec(BEGIN);// <- Do you have this?

st_sel = prepare(SELECT ...);
st_upd = prepare(UPDATE tab SET c1=?1, c2=?2, WHERE id=?3);

while(step(st_sel) == SQLITE_ROW)
{
do_something(...);
bind(st_upd, ...);
step(st_upd);
reset(st_upd);
}

finalize(st_upd);
finalize(st_sel);

exec(COMMIT);// <- and this?
-



>> One technique I have used in a similar situation was to write all my
>> UPDATE commands to a long text buffer.  This was in an unusual situation
>> where I needed to get the SELECT done as quickly as possible to avoid
>> locking up lab equipment.  So the software figured out all the UPDATE
>> commands and concatenated them in a text variable:
>>
>> UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;...
Each update is prepared and executed separately.
If you really want a single query you could write something like:

WITH data(id, c1, c2 /*, ... */) AS (VALUES
(123, 'abc', 'xyz' /*, ... */),
(456, 'xxx', 'yyy' /*, ... */),
(789, 'xyz', 'xyz' /*, ... */)
/*  ...  */
) UPDATE tab
SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id = tab.id)
WHERE id IN (SELECT id FROM data);


2018-05-28 21:32 GMT+02:00, Torsten Curdt :
> Yes, manually buffering the resultset or buffering the updates is of course
> a possible workaround.
> But I would like to avoid that as much as possible.
>
> Another approach is to use limit/offset and then page through the resultset
> to control the amount of buffering needed.
> But this just feels like a lot of complexity for such simple task.
>
> Anyway - since WAL mode seems to work I will stick with that for now.
>
> cheers,
> Torsten
>
> On Mon, May 28, 2018 at 9:10 PM Simon Slavin  wrote:
>
>> On 28 May 2018, at 7:56pm, Torsten Curdt  wrote:
>>
>> > Just to clarify: I have a single thread - so intermixing the stepping
>> > through a resultset and doing an update requires WAL mode but should be
>> > fine. Correct?
>>
>> Yes, this should work fine.  Obviously, one thread is not going to be
>> trying to do two database accesses at the same time, especially since your
>> software design uses the result from one SELECT row in order to figure out
>> what UPDATE to issue.
>>
>> You could, course, build up an array of pairs in memory while doing the
>> SELECT, then consult the array to create all the UPDATE commands once you
>> have finalized the SELECT.  If you do do this don't forget to surround the
>> UPDATE commands with BEGIN;...COMMIT; .
>>
>> One technique I have used in a similar situation was to write all my
>> UPDATE commands to a long text buffer.  This was in an unusual situation
>> where I needed to get the SELECT done as quickly as possible to avoid
>> locking up lab equipment.  So the software figured out all the UPDATE
>> commands and concatenated them in a text variable:
>>
>> UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;...
>>
>> The buffer could get as big as half a megabyte or so.  Then I
>> sqlite3_reset() the SELECT command.  Then I submit the entire piece of
>> text
>> as one long parameter to sqlite3_exec().  Worked perfectly, very quickly,
>> and didn't take up much more memory than storing the parameters in an
>> array.
>>
>> 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

Re: [sqlite] database locked on select

[Abroży Nieprzełoży]

BTW why not to update all rows by single update query?

2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> I am doing a select, then iterate through the resultset and on each row
> call update on that row.
> I am using the golang driver and ran into the issue that on the update the
> database is still locked from the select.
>
>   https://github.com/mattn/go-sqlite3/issues/569
>
> I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked and IIUC
> these types of updates should be possible since version 3.3.7 though - and
> I am using 3.19.3.
>
> Any suggestion on how to track down why the updates fail?
>
> cheers,
> Torsten
> ___
> 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] This is driving me nuts

[Abroży Nieprzełoży]

Alternatively you can alloc physical memory using AWE mechanism.
AWE requires SeLockMemoryPrivilege, so you may have to run the
application on an administator account (or other account with
sufficient rigths).
When using AWE there is no need to increase working set size because
AWE pages are not included in the working set.

---
int enableLockMemoryPrivilege()
{
int rc;
DWORD err;
HANDLE hToken;
TOKEN_PRIVILEGES privilege;
memset(, 0, sizeof(privilege));
privilege.PrivilegeCount = 1;
privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
rc = LookupPrivilegeValue(NULL, SE_LOCK_MEMORY_NAME,
[0].Luid);
if (!rc)
return -1;
rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, 
);
if (!rc)
return -2;
rc = AdjustTokenPrivileges(hToken, 0, , sizeof(privilege),
NULL, NULL);
err = GetLastError();
CloseHandle(hToken);
if (!rc || err)
return -3;
return 0;
}

struct PhysMem
{
void* pMem;
SIZE_T sMem;
ULONG_PTR nPages;
ULONG_PTR* aPages;
};

void unmapAndFreePhysicalPages(struct PhysMem* mem)
{
if(mem->pMem)
VirtualFree(mem->pMem, 0, MEM_RELEASE);
if (mem->aPages)
{
FreeUserPhysicalPages(GetCurrentProcess(), >nPages, 
mem->aPages);
free(mem->aPages);
}
memset(mem, 0, sizeof(*mem));
}

int allocAndMapPhysicalPages(struct PhysMem* mem, SIZE_T size)
{
int rc;
SYSTEM_INFO sysInfo;
memset(mem, 0, sizeof(*mem));
GetNativeSystemInfo();
mem->nPages = size / sysInfo.dwPageSize;
if (size % sysInfo.dwPageSize > 0)
mem->nPages++;
size = mem->nPages * sysInfo.dwPageSize;
mem->aPages = (ULONG_PTR*)calloc(mem->nPages, sizeof(ULONG_PTR));
if (!mem->aPages)
return -1;
rc = AllocateUserPhysicalPages(GetCurrentProcess(), >nPages, 
mem->aPages);
if (!rc)
{
free(mem->aPages);
memset(mem, 0, sizeof(*mem));
return -2;
}
mem->sMem = size = mem->nPages * sysInfo.dwPageSize;
mem->pMem = VirtualAlloc(NULL, mem->sMem, MEM_RESERVE | MEM_PHYSICAL,
PAGE_READWRITE);
if (!mem->pMem)
{
unmapAndFreePhysicalPages(mem);
return -3;
}
rc = MapUserPhysicalPages(mem->pMem, mem->nPages, mem->aPages);
if (!rc)
{
unmapAndFreePhysicalPages(mem);
return -4;
}
return 0;
}

int configSqlitePhysMemory()
{
int rc;
struct PhysMem mem;
rc = allocAndMapPhysicalPages(, 64 * 1024 * 1024);
if (rc < 0)
return -1;
rc = sqlite3_config(SQLITE_CONFIG_HEAP, mem.pMem, (int)mem.sMem, 64);
if (rc != SQLITE_OK)
{
unmapAndFreePhysicalPages();
return -2;
}
return 0;
}

int enableLockMemPrivilegeAndConfigSqliteMemory()
{
int rc;
rc = enableLockMemoryPrivilege();
if (rc < 0)
return -1;
rc = configSqlitePhysMemory();
if (rc < 0)
return -2;
return 0;
}
-------



2018-05-28 0:23 GMT+02:00, Abroży Nieprzełoży
<abrozynieprzelozy314...@gmail.com>:
> Firstly you have to enable SeIncreaseWorkingSetPrivilege - to do this
> you may use the function enableIncreaseWorkingSetPrivilege listed
> below, it should return 0 on success - then you can use
> SetProcessWorkingSetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
> Working set sizes must be multiple of page size (typically 4096 bytes).
> https://msdn.microsoft.com/en-us/library/ms686234
>
> -
> int enableIncreaseWorkingSetPrivilege()
> {
>   int rc;
>   DWORD err;
>   HANDLE hToken;
>   TOKEN_PRIVILEGES privilege;
>   memset(, 0, sizeof(privilege));
>   privilege.PrivilegeCount = 1;
>   privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
>   rc = LookupPrivilegeValue(NULL, SE_INC_WORKING_SET_NAME,
> [0].Luid);
>   if (!rc)
>   return -1;
>   rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES,
> );
>   if (!rc)
>   return -2;
>   rc = AdjustTokenPrivileges(hToken, 0, , sizeof(privilege),
> NULL, NULL);
>   err = GetLastError();
>   CloseHandle(hToken);
>   if (!rc || err)
>   return -3;
>   return 0;
> }
> --

Re: [sqlite] This is driving me nuts

[Abroży Nieprzełoży]

Firstly you have to enable SeIncreaseWorkingSetPrivilege - to do this
you may use the function enableIncreaseWorkingSetPrivilege listed
below, it should return 0 on success - then you can use
SetProcessWorkingSetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
Working set sizes must be multiple of page size (typically 4096 bytes).
https://msdn.microsoft.com/en-us/library/ms686234

-
int enableIncreaseWorkingSetPrivilege()
{
int rc;
DWORD err;
HANDLE hToken;
TOKEN_PRIVILEGES privilege;
memset(, 0, sizeof(privilege));
privilege.PrivilegeCount = 1;
privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
rc = LookupPrivilegeValue(NULL, SE_INC_WORKING_SET_NAME,
[0].Luid);
if (!rc)
return -1;
rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, 
);
if (!rc)
return -2;
rc = AdjustTokenPrivileges(hToken, 0, , sizeof(privilege),
NULL, NULL);
err = GetLastError();
CloseHandle(hToken);
if (!rc || err)
return -3;
return 0;
}
-




You can also compile SQLite with -DSQLITE_ZERO_MALLOC=1 and
-DSQLITE_ENABLE_MEMSYS5=1 and configure it to use buffer of locked
memory.
Locked memory won't get swapped so SQLite won't encounter page faults.

Function configSqliteMemory listed below allocs few megabytes of
memory, locks it and configures SQLite. It also increases working set
size because maximum quantity of memory that process can lock is equal
to minimum working set size (locked memory is included in the working
set).

-
int configSqliteMemory()
{
int rc;
void* memPtr;
SIZE_T memSize = 64 * 1024 * 1024;
SIZE_T wsMinSize, wsMaxSize;
rc = GetProcessWorkingSetSize(GetCurrentProcess(), , 
);
if (!rc)
return -1;
wsMinSize += memSize;
wsMaxSize += memSize;
rc = SetProcessWorkingSetSize(GetCurrentProcess(), wsMinSize, 
wsMaxSize);
if (!rc)
return -2;
memPtr = VirtualAlloc(NULL, memSize, MEM_RESERVE | MEM_COMMIT, 
PAGE_READWRITE);
if (!memPtr)
return -3;
rc = VirtualLock(memPtr, memSize);
if (!rc)
{
VirtualFree(memPtr, 0, MEM_RELEASE);
return -4;
}
rc = sqlite3_config(SQLITE_CONFIG_HEAP, memPtr, (int)memSize, 64);
if (rc != SQLITE_OK)
{
VirtualFree(memPtr, 0, MEM_RELEASE);
return -5;
}
return 0;
}
-




2018-05-27 20:03 GMT+02:00, x <tam118...@hotmail.com>:
> Starting to mess about with windows handles and pages I’ve never heard of is
> beyond my pain threshold Abrozy. Thanks anyway.
>
>
>
> 
> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf
> of Abroży Nieprzełoży <abrozynieprzelozy314...@gmail.com>
> Sent: Sunday, May 27, 2018 5:23:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] This is driving me nuts
>
> I think you can experiment with changing the the working set size
> limits and see what the effect will be.
> https://msdn.microsoft.com/en-us/library/cc441804
>
> 2018-05-27 17:09 GMT+02:00, curmudgeon <tam118...@hotmail.com>:
>> It seems the array was being optimised away. I had to initialise every
>> value
>> to get the OS to claim the RAM. Once I did that the timings for the array
>> were on a par with the vector with the second pass being slower than the
>> first.
>>
>> While that clears up that part of the mystery I'm no closer to a solution.
>> Going back to the latest set of results why is the assignments in the
>> second
>> pass taking so much longer when there's still 5+ GB of memory free?
>>
>>
>>
>> --
>> Sent from: http://sqlite.1065341.n5.nabble.com/
>> ___
>> 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] This is driving me nuts

[Abroży Nieprzełoży]

I think you can experiment with changing the the working set size
limits and see what the effect will be.
https://msdn.microsoft.com/en-us/library/cc441804

2018-05-27 17:09 GMT+02:00, curmudgeon :
> It seems the array was being optimised away. I had to initialise every value
> to get the OS to claim the RAM. Once I did that the timings for the array
> were on a par with the vector with the second pass being slower than the
> first.
>
> While that clears up that part of the mystery I'm no closer to a solution.
> Going back to the latest set of results why is the assignments in the second
> pass taking so much longer when there's still 5+ GB of memory free?
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] This is driving me nuts

[Abroży Nieprzełoży]

Collect some memory statistics before and after memory allocation and
before and after query execution.


#include 
#include 
#include 

void memoryStats()
{
  PROCESS_MEMORY_COUNTERS_EX pmc;
  PERFORMANCE_INFORMATION pi;
  MEMORYSTATUSEX ms;
  ZeroMemory(, sizeof(pmc));
  ZeroMemory(, sizeof(pi));
  ZeroMemory(, sizeof(ms));
  pmc.cb = sizeof(pmc);
  pi.cb = sizeof(pi);
  ms.dwLength = sizeof(ms);
  GetProcessMemoryInfo(GetCurrentProcess(),
(PROCESS_MEMORY_COUNTERS*), sizeof(pmc));
  GetPerformanceInfo(, sizeof(pi));
  GlobalMemoryStatusEx();
  printf("PMC.PageFaultCount = %20u\n", pmc.PageFaultCount);
  printf("PMC.PeakWorkingSetSize = %20zu\n", pmc.PeakWorkingSetSize);
  printf("PMC.WorkingSetSize = %20zu\n", pmc.WorkingSetSize);
  printf("PMC.QuotaPeakPagedPoolUsage= %20zu\n",
pmc.QuotaPeakPagedPoolUsage);
  printf("PMC.QuotaPagedPoolUsage= %20zu\n", pmc.QuotaPagedPoolUsage);
  printf("PMC.QuotaPeakNonPagedPoolUsage = %20zu\n",
pmc.QuotaPeakNonPagedPoolUsage);
  printf("PMC.QuotaNonPagedPoolUsage = %20zu\n",
pmc.QuotaNonPagedPoolUsage);
  printf("PMC.PagefileUsage  = %20zu\n", pmc.PagefileUsage);
  printf("PMC.PeakPagefileUsage  = %20zu\n", pmc.PeakPagefileUsage);
  printf("PMC.PrivateUsage   = %20zu\n", pmc.PrivateUsage);
  printf("PI.CommitTotal = %20zu\n", pi.CommitTotal);
  printf("PI.CommitLimit = %20zu\n", pi.CommitLimit);
  printf("PI.CommitPeak  = %20zu\n", pi.CommitPeak);
  printf("PI.PhysicalTotal   = %20zu\n", pi.PhysicalTotal);
  printf("PI.PhysicalAvailable   = %20zu\n", pi.PhysicalAvailable);
  printf("PI.SystemCache = %20zu\n", pi.SystemCache);
  printf("PI.KernelTotal = %20zu\n", pi.KernelTotal);
  printf("PI.KernelPaged = %20zu\n", pi.KernelPaged);
  printf("PI.KernelNonpaged  = %20zu\n", pi.KernelNonpaged);
  printf("PI.PageSize= %20zu\n", pi.PageSize);
  printf("PI.HandleCount = %20u\n", pi.HandleCount);
  printf("PI.ProcessCount= %20u\n", pi.ProcessCount);
  printf("PI.ThreadCount = %20u\n", pi.ThreadCount);
  printf("MS.dwMemoryLoad= %20u\n", ms.dwMemoryLoad);
  printf("MS.ullTotalPhys= %20llu\n", ms.ullTotalPhys);
  printf("MS.ullAvailPhys= %20llu\n", ms.ullAvailPhys);
  printf("MS.ullTotalPageFile= %20llu\n", ms.ullTotalPageFile);
  printf("MS.ullAvailPageFile= %20llu\n", ms.ullAvailPageFile);
  printf("MS.ullTotalVirtual = %20llu\n", ms.ullTotalVirtual);
  printf("MS.ullAvailVirtual = %20llu\n", ms.ullAvailVirtual);
  printf("MS.ullAvailExtendedVirtual = %20llu\n",
ms.ullAvailExtendedVirtual);
}




2018-05-26 19:01 GMT+02:00, x <tam118...@hotmail.com>:
> Hi Abrozy.
>
>
>
> I inserted the line
>
> int64_t Mem=sqlite3_memory_highwater(0);
>
> immediately after the while loop and added Mem to the cout. The values
> returned were
>
> 2234704 (I==0)
>
> 2234768 (I==1).
>
>
>
> Tom
>
>
>
> 
> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf
> of Abroży Nieprzełoży <abrozynieprzelozy314...@gmail.com>
> Sent: Saturday, May 26, 2018 5:39:03 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] This is driving me nuts
>
>> I swapped Size[0] with Size[1] so that the resize was going from large to
>> small. The timings on each pass were then similar but were still around
>> the
>> 24.xxx mark associated with the i==1 pass.
> Note the difference between size and capacity of the vector.
> Reducing the size does not necessarily reduce the capacity,
> so the vector may not free memory when reducing size.
> You should call shrink_to_fit to free additional memory.
> http://en.cppreference.com/w/cpp/container/vector/shrink_to_fit
>
>
> What's the value returned by sqlite3_memory_highwater after executing the
> query?
> http://www.sqlite.org/c3ref/memory_highwater.html
>
>
> 2018-05-26 17:43 GMT+02:00, x :
>> I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was
>> working on code that stored RowIDs in a vector. I was sizing the vector
>> beforehand and then timing how long it took to store the RowIDs returned
>> by
>> an sqlite query. By accident

Re: [sqlite] This is driving me nuts

[Abroży Nieprzełoży]

> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
Note the difference between size and capacity of the vector.
Reducing the size does not necessarily reduce the capacity,
so the vector may not free memory when reducing size.
You should call shrink_to_fit to free additional memory.
http://en.cppreference.com/w/cpp/container/vector/shrink_to_fit


What's the value returned by sqlite3_memory_highwater after executing the query?
http://www.sqlite.org/c3ref/memory_highwater.html


2018-05-26 17:43 GMT+02:00, x :
> I'm using c++ builder 10.2 tokyo on windows 10 and sqlite 3.23.1. I was
> working on code that stored RowIDs in a vector. I was sizing the vector
> beforehand and then timing how long it took to store the RowIDs returned by
> an sqlite query. By accident I sized the vector too big (by a factor of 10)
> and found the timings doubled (sometimes tripled) despite the fact it was
> still only stepping through the same query. We're talking going from 20 secs
> to 40-60 secs.
>
> At first I thought 'memory problem' BUT as I'm using a ms surface pro 4 with
> 16 GB ram and 512 GB SSD and running a 64 bit release version I thought how
> can that be? I mean the mistakenly sized vector only used 8 GB?
>
> I've managed to whittle the problem down to the following console
> application. The 'Big' table in the following code has just under 112
> million records and contains 2 integer columns and 4 text columns
> (full_name_1, full_name_2,sort_name_1,sort_name_2). It has one (unique)
> index on the 2 integer columns. I did a vacuum on the database beforehand.
> Apart from background tasks the console app was the only one running.
>
> #include 
> #include 
> #pragma hdrstop
> #pragma argsused
> #include 
> #include 
> #include 
> #include 
> #include 
> #include "sqlite.h"
>
> std::vector v;
> const int Size[]={11200,10}; // 112 million, 1 billion
> int _tmain(int argc, _TCHAR* argv[])
> {
> sqlite3 *DB;
> sqlite3_open("c:/SQLiteData/Formbook.db",);
> sqlite3_stmt *stmt;
> sqlite3_prepare_v2(DB,"select RowID from Big order by
> RowID",-1,,NULL);
>
> for (int i=0; i<2; i++)
> {
> v.resize(Size[i]);
>
> // NB the exact same code is executed whether i is 0 or 1.
> The only thing that
> // changes is the size() of v and v isn't even used in the
> timed code below.
>
> clock_t Start=clock();
>
> while (sqlite3_step(stmt)==SQLITE_ROW) {}
>
> // Above just steps through stmt (111,724,900 steps to be
> exact).
>
> std::cout << 1.0*(clock()-Start)/CLOCKS_PER_SEC <<
> std::endl;
>
> sqlite3_reset(stmt);
> }
> sqlite3_finalize(stmt);
> sqlite3_close(DB);
> getch();
> return 0;
> }
>
> 5 sets of timings (secs) were as follows
>
> i==0 i==1
> 17.61024.172
> 20.34424.594
> 19.95324.375
> 19.89123.594
> 19.93825.516
>
> I can't understand why the second pass (exact same code executed) takes an
> average of 4.8 secs longer.
>
> To add to the puzzle
>
> I tried making v an int64_t*, replaced the resize with v=new
> int64_t[Size[i]] and added delete [] v to the end of the inner block. Pretty
> much the same thing as far as memory goes yet the average timings for i==0
> and i==1 were almost identical at around 17.4.
>
> I tried replacing the sqlite related code with non-sqlite code e.g.
> populating v up to v.begin+11200 using rand(). Again the timing anomaly
> disappeared.
>
> I swapped Size[0] with Size[1] so that the resize was going from large to
> small. The timings on each pass were then similar but were still around the
> 24.xxx mark associated with the i==1 pass.
>
> I copied the Big table from its current db to a new db where it was the only
> table. I then redid the timings using the new db but there was no real
> change.
>
> i==0 i==1
> 17.59425.672
> 20.56324.406
> 21.21923.843
> 20.48425.343
> 20.56225.172
>
> I lastly tried gradually lowering Size[1] from 1 billion to 200 million step
> 200 million. There was little difference to the results for
> Size[1]=800,000,000 but at Size[1]=600,000,000 the difference was down to
> under a second. At Size[1]=400,000,000 timings for i==0 and i==1 were pretty
> much the same.
>
>
> While that last test suggests it must be a memory issue it begs the question
> why was there no difference in timings for i==0 & i==1 when an array was
> used rather than a vector? Surely the memory requirements are very similar?
>
> The timing anomaly was still apparent when Size[1]=800 million so why, when
> you've got 16 GB ram, does a 6.4 GB vector cause any problems?
>
> What's it got to do with sqlite, if anything? Why was sqlite_step slowed
> down?
>
> Any suggestions appreciated.
> 

Re: [sqlite] Database file with a nonstandard sqlite_seuence schema causes crash.

[Abroży Nieprzełoży]

I'm not working on anything specific, just experimenting, so you don't
have to rush :)


2018-05-20 21:53 GMT+02:00, Richard Hipp:
> Thank you for the bug report.
>
> I agree that this is a problem that needs to be fixed, and it will be
> fixed before the next release.  However, the problem arises in a part
> of the code where we must move cautiously to avoid a performance
> regression. Further, your specific problem suggests an entire new
> class of problems that need to be investigated and carefully tested.
> So fixing this will take some time.
>
> The bug has been in the code since version 3.8.7 (2014-10-17).  I
> think you can wait a few days for the fix.
>
>
>
> On 5/19/18, Abroży Nieprzełoży <abrozynieprzelozy314...@gmail.com> wrote:
>> C:\test>sqlite3 test.db
>> SQLite version 3.24.0 2018-05-18 17:58:33
>> Enter ".help" for usage hints.
>> sqlite> .version
>> SQLite 3.24.0 2018-05-18 17:58:33
>> c6071ac99cfa4b6272ac4d739fc61a85acb544f6c1c2ae67b31e92aadcc995bd
>> zlib version 1.2.11
>> msvc-1912
>> sqlite> CREATE TABLE xqlite_sequence(name TEXT PRIMARY KEY, seq
>> INTEGER) WITHOUT ROWID;
>> sqlite> INSERT INTO xqlite_sequence VALUES('ttt', 1);
>> sqlite> PRAGMA writable_schema=1;
>> sqlite> UPDATE sqlite_master SET name='sqlite_sequence',
>> tbl_name='sqlite_sequence', sql='CREATE TABLE sqlite_sequence(name
>> TEXT PRIMARY KEY, seq INTEGER) WITHOUT ROWID' WHERE
>> name='xqlite_sequence';
>> sqlite> .exit
>>
>> C:\test>sqlite3 test.db
>> SQLite version 3.24.0 2018-05-18 17:58:33
>> Enter ".help" for usage hints.
>> sqlite> CREATE TABLE ttt(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
>> sqlite> .exit
>>
>> C:\test>sqlite3 test.db
>> SQLite version 3.24.0 2018-05-18 17:58:33
>> Enter ".help" for usage hints.
>> sqlite> INSERT INTO ttt(data) VALUES('xx');
>>
>
>
> --
> 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] Database file with a nonstandard sqlite_seuence schema causes crash.

[Abroży Nieprzełoży]

btw 'PRAGMA integrity_check;' returns 'ok'


2018-05-20 18:27 GMT+02:00, Abroży Nieprzełoży :
>> The documentation says:
>> **"Warning: misuse of this pragma can easily result in a corrupt database
>> file."**
>
> But it would be nice if corrupted database didn't crash the application ;)
>
>
>
> 2018-05-20 16:05 GMT+02:00, Clemens Ladisch :
>> Abroży Nieprzełoży wrote:
>>> sqlite> PRAGMA writable_schema=1;
>>
>> The documentation says:
>> **"Warning: misuse of this pragma can easily result in a corrupt database
>> file."**
>>
>>> sqlite> CREATE TABLE xqlite_sequence(name TEXT PRIMARY KEY, seq INTEGER)
>>> WITHOUT ROWID;
>>
>> SQLite does not use SQL but raw VDBE commands update the row in the
>> sqlite_sequence
>> table (see autoIncBegin() and autoIncrementENd() in insert.c).  Without
>> the
>> rowid,
>> that code no longer works.
>>
>>
>> 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

Re: [sqlite] Database file with a nonstandard sqlite_seuence schema causes crash.

[Abroży Nieprzełoży]

> The documentation says:
> **"Warning: misuse of this pragma can easily result in a corrupt database
> file."**

But it would be nice if corrupted database didn't crash the application ;)



2018-05-20 16:05 GMT+02:00, Clemens Ladisch :
> Abroży Nieprzełoży wrote:
>> sqlite> PRAGMA writable_schema=1;
>
> The documentation says:
> **"Warning: misuse of this pragma can easily result in a corrupt database
> file."**
>
>> sqlite> CREATE TABLE xqlite_sequence(name TEXT PRIMARY KEY, seq INTEGER)
>> WITHOUT ROWID;
>
> SQLite does not use SQL but raw VDBE commands update the row in the
> sqlite_sequence
> table (see autoIncBegin() and autoIncrementENd() in insert.c).  Without the
> rowid,
> that code no longer works.
>
>
> 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] Database file with a nonstandard sqlite_seuence schema causes crash.

[Abroży Nieprzełoży]

C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> .version
SQLite 3.24.0 2018-05-18 17:58:33
c6071ac99cfa4b6272ac4d739fc61a85acb544f6c1c2ae67b31e92aadcc995bd
zlib version 1.2.11
msvc-1912
sqlite> CREATE TABLE xqlite_sequence(name TEXT PRIMARY KEY, seq
INTEGER) WITHOUT ROWID;
sqlite> INSERT INTO xqlite_sequence VALUES('ttt', 1);
sqlite> PRAGMA writable_schema=1;
sqlite> UPDATE sqlite_master SET name='sqlite_sequence',
tbl_name='sqlite_sequence', sql='CREATE TABLE sqlite_sequence(name
TEXT PRIMARY KEY, seq INTEGER) WITHOUT ROWID' WHERE
name='xqlite_sequence';
sqlite> .exit

C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> CREATE TABLE ttt(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
sqlite> .exit

C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> INSERT INTO ttt(data) VALUES('xx');
C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> .version
SQLite 3.24.0 2018-05-18 17:58:33 
c6071ac99cfa4b6272ac4d739fc61a85acb544f6c1c2ae67b31e92aadcc995bd
zlib version 1.2.11
msvc-1912
sqlite> CREATE TABLE xqlite_sequence(name TEXT PRIMARY KEY, seq INTEGER) 
WITHOUT ROWID;
sqlite> INSERT INTO xqlite_sequence VALUES('ttt', 1);
sqlite> PRAGMA writable_schema=1;
sqlite> UPDATE sqlite_master SET name='sqlite_sequence', 
tbl_name='sqlite_sequence', sql='CREATE TABLE sqlite_sequence(name TEXT PRIMARY 
KEY, seq INTEGER) WITHOUT ROWID' WHERE name='xqlite_sequence';
sqlite> .exit

C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> CREATE TABLE ttt(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
sqlite> .exit

C:\test>sqlite3 test.db
SQLite version 3.24.0 2018-05-18 17:58:33
Enter ".help" for usage hints.
sqlite> INSERT INTO ttt(data) VALUES('xx');
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thoughts about the sqlite3IntFloatCompare function from the vdbeaux.c file.

[Abroży Nieprzełoży]

Some test: https://ideone.com/zkHHty


2018-05-18 13:36 GMT+02:00, Abroży Nieprzełoży
<abrozynieprzelozy314...@gmail.com>:
> 
>  1: static int sqlite3IntFloatCompare(i64 i, double r){
>  2:   if( sizeof(LONGDOUBLE_TYPE)>8 ){
>  3: LONGDOUBLE_TYPE x = (LONGDOUBLE_TYPE)i;
>  4: if( x  5: if( x>r ) return +1;
>  6: return 0;
>  7:   }else{
>  8: i64 y;
>  9: double s;
> 10: if( r<-9223372036854775808.0 ) return +1;
> 11: if( r>9223372036854775807.0 ) return -1;
> 12: y = (i64)r;
> 13: if( i 14: if( i>y ){
> 15:   if( y==SMALLEST_INT64 && r>0.0 ) return -1;
> 16:   return +1;
> 17: }
> 18: s = (double)i;
> 19: if( s 20: if( s>r ) return +1;
> 21: return 0;
> 22:   }
> 23: }
> 
>
> Line 11: the value 9223372036854775807.0 is unrepresentable as a double.
> The compiler uses the approximation 9223372036854775808.0,
> so the condition is in fact 'if( r>9223372036854775808.0 )'.
> Line 12: when r=9223372036854775808.0 then y=SMALLEST_INT64
> and this special case is handled by the condition on line 15.
>
> But if the condition in line 11 were 'if( r>=9223372036854775808.0 )'
> then for r=9223372036854775808.0 the function would return -1
> before assigning r to y, so the condition on line 15 would be unnecessary.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Thoughts about the sqlite3IntFloatCompare function from the vdbeaux.c file.

[Abroży Nieprzełoży]

 1: static int sqlite3IntFloatCompare(i64 i, double r){
 2:   if( sizeof(LONGDOUBLE_TYPE)>8 ){
 3: LONGDOUBLE_TYPE x = (LONGDOUBLE_TYPE)i;
 4: if( xr ) return +1;
 6: return 0;
 7:   }else{
 8: i64 y;
 9: double s;
10: if( r<-9223372036854775808.0 ) return +1;
11: if( r>9223372036854775807.0 ) return -1;
12: y = (i64)r;
13: if( iy ){
15:   if( y==SMALLEST_INT64 && r>0.0 ) return -1;
16:   return +1;
17: }
18: s = (double)i;
19: if( sr ) return +1;
21: return 0;
22:   }
23: }


Line 11: the value 9223372036854775807.0 is unrepresentable as a double.
The compiler uses the approximation 9223372036854775808.0,
so the condition is in fact 'if( r>9223372036854775808.0 )'.
Line 12: when r=9223372036854775808.0 then y=SMALLEST_INT64
and this special case is handled by the condition on line 15.

But if the condition in line 11 were 'if( r>=9223372036854775808.0 )'
then for r=9223372036854775808.0 the function would return -1
before assigning r to y, so the condition on line 15 would be unnecessary.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Function to use to convert a text float to a float for use in ORDER BY

[Abroży Nieprzełoży]

> select col1, col2 from mytable order by cast(body_size as float);

And if you care about speed of select, you could create appropriate index:

create index mytable_bodysize_index on mytable(cast(body_size as float));


2018-05-18 0:13 GMT+02:00, Keith Medcalf :
>
> Why not encode (speak / say) what you want to do directly, rather than
> prayerfully relying on implementation details --
>
> select col1, col2 from mytable order by cast(body_size as float);
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Tim Streater
>>Sent: Thursday, 17 May, 2018 15:42
>>To: SQLite mailing list
>>Subject: [sqlite] Function to use to convert a text float to a float
>>for use in ORDER BY
>>
>>My db has a table with a column defined thus:
>>
>>  body_size text default '0.0'
>>
>>whose purpose is to hold the size of the item that the row
>>represents. All rows contain a value but as a string to one decimal
>>place, not a number. So the column contains strings such as '0.0',
>>'3.7', '22.9', etc. All are positive.
>>
>>Now I want to use the body_size in an ORDER BY so I'll need SQLite to
>>convert the values to a float. What will be the best function to use?
>>I'd thought of this:
>>
>>  select col1, col2 from mytable order by round(body_size,1)
>>
>>however the doc doesn't specify whether round accepts a non-numeric
>>argument. Perhaps:
>>
>>  select col1, col2 from mytable order by abs(body_size)
>>
>>would be better, but I wonder which is fastest (although speed may
>>not matter too much, the typical select shouldn't return more than
>>few thousand rows).
>>
>>
>>--
>>Cheers  --  Tim
>>___
>>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] Is this really the best way to do this?

[Abroży Nieprzełoży]

I would make something like this:

CREATE TABLE blocked(
mail TEXT PRIMARY KEY,
action TEXT NOT NULL DEFAULT 'DISCARD'
) WITHOUT ROWID;

INSERT INTO blocked(mail) VALUES('badm...@example.com');

SELECT coalesce(action, 'OK') AS action
FROM (SELECT 'goodm...@example.com' AS mail)
LEFT JOIN blocked USING(mail);

SELECT coalesce(action, 'OK') AS action
FROM (SELECT 'badm...@example.com' AS mail)
LEFT JOIN blocked USING(mail);


2018-05-16 10:22 GMT+02:00, Rob Willett :
> Hi,
>
> I'm experimenting with an email server, Mail In a Box. It's a free
> product and gives you a mail server in a box with SSL certificates,
> multiple domains and seems to work well.
>
> One thing it doesn't do is allow people to specify emails to block.
>
> It uses SQLite to store the underlying data necessary to power Postfix.
> Dr Richard Hipp, add another new application to your list :)
>
> I've worked out how Postfix calls SQLite (pretty easy), worked out what
> I want to do, and am trying to write a single line of SQL that does it.
>
> The need is that the SQL must generate a single string answer depending
> on the email address that is fed in through the query. There are no
> options here, it must return a value even if there is nothing in the
> table. It is not practical to add a table with every email address that
> returns OK.
>
> For this example the only strings it can return are 'OK' and 'DISCARD',
> though the RFC allows other strings. If a query is done on an email and
> it is blocked then it must return DISCARD (or an action in the action
> column. If no email is in the table matching then it must return 'OK'.
>
> As a piece of pseudo code this would ne
>
> function GetMailStatus(emailAddress)
> {
>   IF emailAddress is present in blocked THEN
>   return action associated with emailAddress -- Action is normally
> DISCARD
>
>   return 'OK'
> }
>
> I've created the table
>
> CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
>  email TEXT NOT NULL UNIQUE ,
>action TEXT NOT NULL DEFAULT 'DISCARD')
>
> I can insert values
>
> insert into blocked (email) values('rwillett.dr...@example.com')
> insert into blocked (email) values('rwillett+dr...@example.com')
>
> And this is the code that I have written that works but it looks poor to
> my untutored eyes
>
> select CASE
>  WHEN EXISTS (select 1 from blocked where email =
> 'rwillett.dr...@example.com')
>  THEN (select action from blocked where email =
> 'rwillett.dr...@example.com')
>  ELSE 'OK'
>  END
>
>
> In the Postfix query I'll replace
>
> email = 'rwillett.dr...@example.com'
>
> with email = '%s'
>
> so that the email address is passed in.
>
> My worry is that the query has two selects and 'feels' bad. It works but
> feels dirty...
>
> Is there a better way under SQLite?
>
> Any suggestions welcomed.
>
> Rob
> ___
> 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] Multi threaded readers on memory sqlite cannot scale

[Abroży Nieprzełoży]

AFAIK the only way to access single memory database from multiple connections
is through shared cache.

2018-05-15 0:27 GMT+02:00, Keith Medcalf :
>
>>2018-05-13 12:50 GMT+02:00, Techno Magos :
>
>>> Hello
>
>>> I do not have clear examples to post  on this but would like to
>>> report findings around multi threaded read access (single process) in a
>>> large system that uses sqlite.
>
>>> This may be a known issue/restriction of memory sqlite behaviour,
>>> but wanted to check with the list first:
>
>>> 1. Running 2, 3, ... 6 multi threaded readers of a single *memory
>>> *sqlite database (via shared cache mode) on an 8 core cpu shows no
>>> throughput gain at all compared to single threaded throughput.
>>> In fact, it shows a throughput drop: i.e. if a single thread can
>>> do N simple queries/sec, 2 threads .. up to 6 threads do a little
>>> less (10% drop) in total.  This suggests that access to memory
>>> sqlite can only be serialized?
>
> No one has asked the $10 million dollar question yet ... why are you doing
> this?  It certainly does not sound like you have a highly constrained
> itty-bitty wee boxen for which the "shared cache" was designed (ie, you seem
> to have more that 4 cycles per second of CPU and 10 bytes of memory).
>
> So what was the configuration which you WERE PREVIOUSLY USING which was
> deficient and for which you are trying this optimization?
>
> Everyone knows that when you use "shared cache mode" you are trading off
> memory and CPU cycles against performance.  Shared Cache Mode is designed so
> that if you are *really* so inclined you can run SQLite on your Tandy Pocket
> Computer too (https://en.wikipedia.org/wiki/Tandy_Pocket_Computer)
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] Stored Procedures

[Abroży Nieprzełoży]

> That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very
> cheap" either - it doubles writes (once to the rollback journal, once to
> the DB), forces syncs, and likely results in a more seek heavy i/o pattern
> (this depends a bit on schema design and whether the app requires/fully
> takes advantage of relational operations).

Use explicit transaction maybe?



2018-05-14 6:15 GMT+02:00, Rowan Worth :
> On 14 May 2018 at 01:08, Richard Damon  wrote:
>
>> On 5/13/18 12:55 PM, Rowan Worth wrote:
>> > On 9 May 2018 at 08:56, Richard Hipp  wrote:
>> >
>> >>   But with
>> >> SQLite, there is no round-trip latency.  A "round-trip" to and
>> >> database is just a function call, and is very very cheap.
>> >>
>> > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes
>> the
>> > latency of _communication_ between the app and database in this
>> statement,
>> > and excludes any processing time required by the database.
>> >
>> > If you were to interpret "round-trip" from an app-centric perspective
>> > (as
>> > in "the time taken to retrieve/commit data") then the statement becomes
>> > misleading because handling the data involves i/o, possibly even
>> > synchronous i/o, which is not "very very cheap" by any standard I'm
>> > aware
>> > of :)
>> >
>> > -Rowan
>>
> Yes, if the request requires I/O, then that costs time, but then the
>> operation would likely use similar I/O in whatever way the application
>> needed to get that information, so that I/O shouldn't really be charged
>> to the use of a database, but to the information requested. One thing to
>> remember is SQLite is often compared as a better way to get information
>> then using simple disk i/o, so the 'cost' of using SQLite (compared to
>> the alternative) shouldn't include the base time to read the file, but
>> only any extra i/o above that.
>>
>
> That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very
> cheap" either - it doubles writes (once to the rollback journal, once to
> the DB), forces syncs, and likely results in a more seek heavy i/o pattern
> (this depends a bit on schema design and whether the app requires/fully
> takes advantage of relational operations).
>
> To be clear, this is not a criticism of sqlite. These costs are paid for a
> reason (eg. durability) and I think sqlite does its job very efficiently.
> You're also right that an app implementing similar features without sqlite
> will have to pay similar costs.
>
> My point is simply that it's unwise to think of any DB query as having "no
> latency" even when dealing with an sqlite DB.
> -Rowan
> ___
> 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] Multi threaded readers on memory sqlite cannot scale

[Abroży Nieprzełoży]

Try to open N separate database connections (without shared cache) and
load content using sqlite3_deserialize with
SQLITE_DESERIALIZE_READONLY flag.
http://www.sqlite.org/c3ref/deserialize.html
SQLite won't copy data but use provided buffer so you won't have N
copies of databse.

2018-05-13 12:50 GMT+02:00, Techno Magos :
> Hello
>
> I do not have clear examples to post  on this but would like to report
> findings around multi threaded read access (single process) in a large
> system that uses sqlite.
>
> This may be a known issue/restriction of memory sqlite behaviour, but
> wanted to check with the list first:
>
> 1. Running 2, 3, ... 6 multi threaded readers of a single *memory *sqlite
> database (via shared cache mode) on an 8 core cpu shows no throughput gain
> at all compared to single threaded throughput. In fact, it shows a
> throughput drop: i.e. if a single thread can do N simple queries/sec, 2
> threads .. up to 6 threads do a little less (10% drop) in total. This
> suggests that access to memory sqlite can only be serialized?
>
> 2. Running the same example on sqlite *file *(multi threaded mode; WAL
> journal) scales almost linearly;  so 6 threads provide nearly 6xN
> throughput. Single threaded throughput is a bit slower (around 15-20%)
> than single threaded in-memory access (expected).
>
> So, memory sqlite is not really usable with multiple threads (readers).
> While one might expect  that multiple readers of *memory *content could
> scale even better than with file content.
>
> Can this restriction be lifted?
> Is there some special mode possible to achieve scaling up throughput with
> multiple threads for memory sqlite content?
>
>
> Thanks
> ___
> 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] Multi threaded readers on memory sqlite cannot scale

[Abroży Nieprzełoży]

I have a few ideas you could try with a file db.

1. Use VFS with no locks - named "win32-none" or "unix-none" depending
on your system (4th argument to sqlite_open_v2).

2. Run "pragma locking_mode=exclusive;" on each connection or compile
SQLite with -DSQLITE_DEFAULT_LOCKING_MODE=1

3. Compile SQLite with -DSQLITE_MAX_MMAP_SIZE= and -DSQLITE_DEFAULT_MMAP_SIZE=


2018-05-13 12:50 GMT+02:00, Techno Magos :
> Hello
>
> I do not have clear examples to post  on this but would like to report
> findings around multi threaded read access (single process) in a large
> system that uses sqlite.
>
> This may be a known issue/restriction of memory sqlite behaviour, but
> wanted to check with the list first:
>
> 1. Running 2, 3, ... 6 multi threaded readers of a single *memory *sqlite
> database (via shared cache mode) on an 8 core cpu shows no throughput gain
> at all compared to single threaded throughput. In fact, it shows a
> throughput drop: i.e. if a single thread can do N simple queries/sec, 2
> threads .. up to 6 threads do a little less (10% drop) in total. This
> suggests that access to memory sqlite can only be serialized?
>
> 2. Running the same example on sqlite *file *(multi threaded mode; WAL
> journal) scales almost linearly;  so 6 threads provide nearly 6xN
> throughput. Single threaded throughput is a bit slower (around 15-20%)
> than single threaded in-memory access (expected).
>
> So, memory sqlite is not really usable with multiple threads (readers).
> While one might expect  that multiple readers of *memory *content could
> scale even better than with file content.
>
> Can this restriction be lifted?
> Is there some special mode possible to achieve scaling up throughput with
> multiple threads for memory sqlite content?
>
>
> Thanks
> ___
> 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] '.open' without args crashes shell

[Abroży Nieprzełoży]

SQLite version 3.24.0 2018-05-09 16:32:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .version
SQLite 3.24.0 2018-05-09 16:32:00
9f7a6ae878cd17ff4de7c55e654406773e0ea2b9fe1c4e2a9fc2b0da84d059a4
zlib version 1.2.11
msvc-1912
sqlite> .open
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

[Abroży Nieprzełoży]

I think Barry mean that you can represent the (x,y) pair as a single
number like (max(X)-min(X))*(Y-min(Y))+X-min(X) or so, but I don't see
how it would be helpful.

2018-05-02 0:20 GMT+02:00, Roman Fleysher:
> Dear Barry,
>
> The statement about the square is not obvious to me. The requirements on
> counts in x and y are different.
>
> I also imagine answer could be two or several non-overlapping  "rectangles".
> "Rectangles" will not be densely filled with dots, they might have empty
> spots either because the points were never on the list or were eliminated.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 6:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Well those constraints simplify your problem.
>
> In the resultant dataset, the largest X and Y values will be equal, and the
> largest X will have and entry for every coordinate from (X, 1) to (X, X).
> Likewise the largest Y will have an entry for every coordinate from (1, Y)
> to (Y, Y). Basically you'll have two lines from the axes, drawing a square.
> All points outside that square will be culled, all points on and inside the
> square will be kept.
>
> Since you know that, you now have a one dimensional problem to solve. It
> still seems a little recursive to me, but it should be easier because you
> only need to find a single number (which you can then plug into a delete
> statement).
>
> If my statement about the square is not obvious to prove in your head I can
> try write a proof for that but I'm not much good at proofs.
>
>> On 2 May 2018, at 7:27 am, Roman Fleysher
>> wrote:
>>
>> Pairs (x,y) do not repeat.
>>
>> Actual x and y are positive integers, but I do not see how being positive
>> can be relevant. Integer is important for sorting/comparison.
>>
>>
>> Roman
>>
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on
>> behalf of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 5:23 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>>
>> Is there a uniqueness constraint on your initial data? Can the same
>> coordinate be listed multiple times?
>>
>> Is there a requirement that X > 0 and Y > 0?
>>
 On 2 May 2018, at 3:35 am, Simon Slavin  wrote:

 On 1 May 2018, at 6:28pm, Simon Slavin  wrote:

 I just realised that
>>>
>>> That was intended to be personal email.  Apologies, everyone.
>>>
>>> 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
> ___
> 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] Broken DB?

[Abroży Nieprzełoży]

http://www.sqlite.org/howtocorrupt.html

2018-04-27 23:58 GMT+02:00, Kevin O'Gorman:
> I've got a working site, but I made a copy of the database in order to do
> some development work.
> I've hit a snag that looks like a problem in the data.
>
> Ive written a management command to show the problem:
>
> from django.core.management.base import BaseCommand, CommandError
>
> # Stuff for the library
> from oil.models import Packet, Signature, Log, Voter
>
> class Command(BaseCommand):
> help = 'Shows a quick count of validations'
> BaseCommand.requires_migrations_checks = True
>
>
> def handle(self, *args, **options):
> voters = Log.objects.all()
> self.stdout.write(repr(voters[0]))
>
> I'm suspecting a problem has crept into my Log table, because it works fine
> if I change Log on the
> second line of handle() to any of the other tables.  If it runs as shown
> here however, I get
>
> kevin@camelot-x:/build/comprosloco$ manage oiltest
> Traceback (most recent call last):
>   File "./manage", line 22, in 
> execute_from_command_line(sys.argv)
>   File "/build/django/django/core/management/__init__.py", line 364, in
> execute_from_command_line
> utility.execute()
>   File "/build/django/django/core/management/__init__.py", line 356, in
> execute
> self.fetch_command(subcommand).run_from_argv(self.argv)
>   File "/build/django/django/core/management/base.py", line 283, in
> run_from_argv
> self.execute(*args, **cmd_options)
>   File "/build/django/django/core/management/base.py", line 330, in execute
> output = self.handle(*args, **options)
>   File "/raid3/build/comprosloco/oil/management/commands/oiltest.py", line
> 15, in handle
> self.stdout.write(repr(voters[0]))
>   File "/build/django/django/db/models/base.py", line 590, in __repr__
> u = six.text_type(self)
>   File "/raid3/build/comprosloco/oil/models.py", line 172, in __str__
> self.accepted
> TypeError: sequence item 0: expected str instance, datetime.datetime found
> kevin@camelot-x:/build/comprosloco$
>
> And I have no idea how to debug it further.  The schema of Log is
> sqlite> .schema oil_log
> CREATE TABLE "oil_log" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> "packet" integer NOT NULL, "signature" integer NOT NULL, "action"
> varchar(20) NOT NULL, "criteria" varchar(150) NOT NULL, "candidates"
> varchar(100) NOT NULL, "accepted" varchar(10) NOT NULL, "user_id" integer
> NOT NULL REFERENCES "auth_user" ("id"), "timestamp" datetime NOT NULL);
> CREATE INDEX "oil_log_packet_ecd59bc4" ON "oil_log" ("packet");
> CREATE INDEX "oil_log_user_id_7f26e501" ON "oil_log" ("user_id");
> sqlite>
>
>
> Help???
>
>
> --
> Dictionary.com's word of the year: *complicit*
> ___
> 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] Query regarding CVE-2018-8740

[Abroży Nieprzełoży]

I think sqlite-autoconf-3230100 should be OK.
http://www.sqlite.org/2018/sqlite-autoconf-3230100.tar.gz

Always check download page for the newest version
http://www.sqlite.org/download.html


2018-04-27 1:11 GMT+02:00, salil GK:
> Hello
>
>We are using sqlite-autoconf-322 in our product. Recently there
> was a CVE released for sqlite - CVE-2018-8740 - for which patch is
> available in
> https://www.sqlite.org/cgi/src/vdiff?from=1774f1c3baf0bc3d=d75e67654aa9620b.
> But this patch is for sqlite code I suppose. The patch mentioned above
> is not applicable for sqlite-autoconf.
>Is there any new version of sqlite-autoconf available which is
> compliant to CVE-2018-8740
>
> Thanks in advance
> ~S
> ___
> 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] 'replace ... do update...' crash

[Abroży Nieprzełoży]

sqlite> .version
SQLite 3.24.0 2018-04-20 18:01:31
279c48f6061f766f5437edd6964c0dd1e10399314eb17b6e5ee34df925a7alt1
zlib version 1.2.11
msvc-1912


CREATE TABLE abc(
a TEXT UNIQUE,
b TEXT UNIQUE
);
CREATE TABLE tt(
id INTEGER PRIMARY KEY AUTOINCREMENT,
x TEXT
);
CREATE TRIGGER bef_ins BEFORE INSERT ON abc BEGIN INSERT INTO tt(x)
VALUES('bi'); END;
INSERT INTO abc(a, b) VALUES('X', 'Y'),('1', '2');

REPLACE INTO abc(a, b) VALUES('X', 'Z'),('9', '2') ON CONFLICT(b) DO
UPDATE SET b='8'; --crash
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users