[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

[sqlite] R*Tree / In Memory Database / GUI Object Hit Testing

[Robert M. Münch]

Hi, I’m wondering if the R*Tree index of Sqlite could be used to implement GUI 
object hit testing?

Scenario: We have a 2D scene-graph for our GUI, which does an automatic layout 
of objects following the CSS grid idea. So, we build up the graph, run layout 
and get back the coordinates where to render things.

We could populate a r*tree table with (runtime-object-memory-pointer, x0, y0, 
x1, y0) pretty easy. Now the user clicks the mouse somewhere, and we would like 
to get back all rectangles that were hit. Since we don’t have any overlapping 
we could even sort the rectangles by size and get the whole tree-path to the 
leaf rectangle back.

Since we would implement a R*Tree anyway for hit testing and use Sqlite in our 
app, I can imagine that using it might really be nice here. IMO if everything 
is running in memory, this should be very efficient. We are talking maybe about 
5000 entries.

What do you think? Or maybe any experiences with such a setup?

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is VACUUM ANALYZE removed?

[Kenichi Ishigaki]

2018-05-19 22:21 GMT+09:00 Richard Hipp :
> On 5/19/18, Kenichi Ishigaki  wrote:
>> Hi.
>>
>> VACUUM ANALYZE without a semicolon in-between used to work, but it
>> seems not since 3.15.0. I couldn't find an entry for this in
>> releaselogs. Is it an intentional change, or a regression?
>
> If by "used to work" you mean that it didn't raise an error, then you
> are correct.  But it never did work in the sense that it never did
> both VACUUM and ANALYZE (I'm guessing that's what you thought it was
> doing).  The ANALYZE keyword was silently and mistakenly ignored.  The
> documentation showed that anything following the VACUUM was a syntax
> error, but the engine did not enforce that restriction.
>
> That bug was fixed in 3.15.0.
>
> Beginning with 3.15.0, if anything comes after the VACUUM verb, it
> must be the name one of the ATTACH-ed databases that is to be
> vacuumed.

Thank you for clarification.

Regards,
Kenichi

>
> --
> 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] Is VACUUM ANALYZE removed?

[Richard Hipp]

On 5/19/18, Kenichi Ishigaki  wrote:
> Hi.
>
> VACUUM ANALYZE without a semicolon in-between used to work, but it
> seems not since 3.15.0. I couldn't find an entry for this in
> releaselogs. Is it an intentional change, or a regression?

If by "used to work" you mean that it didn't raise an error, then you
are correct.  But it never did work in the sense that it never did
both VACUUM and ANALYZE (I'm guessing that's what you thought it was
doing).  The ANALYZE keyword was silently and mistakenly ignored.  The
documentation showed that anything following the VACUUM was a syntax
error, but the engine did not enforce that restriction.

That bug was fixed in 3.15.0.

Beginning with 3.15.0, if anything comes after the VACUUM verb, it
must be the name one of the ATTACH-ed databases that is to be
vacuumed.

-- 
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] Is VACUUM ANALYZE removed?

[Kenichi Ishigaki]

Hi.

VACUUM ANALYZE without a semicolon in-between used to work, but it
seems not since 3.15.0. I couldn't find an entry for this in
releaselogs. Is it an intentional change, or a regression?

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

[sqlite] Potential issue for date function when given Julian days in the range 999BC to 1BC

[Inderjit Gill]

Hi,
When the date function is given a date between 999BC and 1BC in Julian
days it formats the year with only 3 digits rather than 4:

select julianday('-0050-01-01');
> 1702797.5
select date(1702797.5);
> -050-01-01

Shouldn't the returned value actually be '-0050-01-01' (As mentioned in
the ISO 8601 standard's wiki page:
https://en.wikipedia.org/wiki/ISO_8601#Years)?

Dates earlier than 999BC return 4 digits prefixed with a sign character:

select date(1270350.5);
> -1234-01-01

and dates from the first century onward are padded with leading 0s:

select date(1721790.5);
> 0002-01-01

If the dates from 999BC to 1BC were returned in the format --MM-DD
they'd have the nice property of being valid input for the julianday
function (this is currently true for all dates apart from this problematic
range).

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