Re: [sqlite] Table was deleted on macOS
-- Originalnachricht -- Von: "Simon Slavin" mailto:slav...@bigfraud.org>> An: "SQLite mailing list" mailto:sqlite-users@mailinglists.sqlite.org>> Gesendet: 15.10.2019 23:24:17 Betreff: Re: [sqlite] Table was deleted on macOS On 15 Oct 2019, at 10:11pm, t...@qvgps.com<mailto:t...@qvgps.com> wrote: "no such table: mytable" The client sent us the database and the table "mytable" was really gone. The database also seems to be fine, no errors. Do you mean by that that you ran integrity_check() ? is ok Is everything else there, or did another table, or rows from another table, disappear ? everything else is there, just this one table is gone. Did the client's computer crashed or otherwise failed at any point ? He didn't report that, I believe its quiet unlikely with a 2015 MacBookPro. Is there a chance that the client tried to mess with the table using a database editor tool ? no Does the client have backups ? Can you look at them and isolate the daterange during which the problem occurred ? no In our app is no DROP TABLE command, what else can cause a table to be deleted? ALTER TABLE RENAME There is no code in the app, which is altering or dropping any table. Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org<mailto: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] Table was deleted on macOS
Hi Group, here is a very strange and rare fault which one of our users experienced on macOS . He was working in our app for a while creating a lot of new data. So over several hours many INSERT INTO mytable Without any errors. Then he did a query SELECT COUNT(*) FROM mytable WHERE (deleted=0 OR deleted IS NULL) AND IdCat=2 and an exception occurs: "no such table: mytable" The client sent us the database and the table "mytable" was really gone. The database also seems to be fine, no errors. How can this happen? In our app is no DROP TABLE command, what else can cause a table to be deleted? We have a few hundred copies of our app out, and this is the first time this error happens. Its on macOS and the sqlite-version is 3024000 Thanks Tom / ** Flemming Software Development CC ** Thomas Flemming ** PO Box 81244 ** Windhoek, Namibia ** http://www.quovadis-gps.com ** mail t...@qvgps.com<mailto:t...@qvgps.com> ** +264 (0)81 3329923Nam mobile ** +49 (0)175 7313081 D mobile ** +49 (0)6182 8492599 D office ***/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WITHOUT ROWID
Hi sqlite-group, I accidentally created tables WITHOUT ROWID. These tables are filled with some 100MB of data and delivered to customers already. Now I'm looking for way to fix this bug. Is it possible to change (ALTER TABLE?) these tables to still include rowid? One option would be to create a new table with rowid, copy all records from the old one, delete old one and rename new table. But maybe there is more simple, quicker solution? Like just adding a column? Thanks, Tom / ** Flemming Software Development CC ** Thomas Flemming ** PO Box 81244 ** Windhoek, Namibia ** http://www.quovadis-gps.com ** mail t...@qvgps.com ** +264 (0)81 3329923Nam mobile ** +49 (0)175 7313081 D mobile ** +49 (0)6182 8492599 D office ***/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies to reduce page-loads?
Right. Was trying it out now, compiling some osm-dbs with primary key generated with this morton encoding from lat,lon and the performance is even worse. Debugging with the sqlite-tool shows, that the page counts for specific queries are almost double then before. Seems like, from the sqlite-side the only options is to have page size as big as possible and line-data in the blob-field as much compressed as possible. -- Originalnachricht -- Von: "Simon Slavin" An: "SQLite mailing list" Gesendet: 31.08.2018 19:07:36 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 31 Aug 2018, at 2:46pm, J Decker wrote: > >>There was a voxel engine that was claiming they were going to move to >>a >>morton encoding; and I was working with a different engine, so I built >>a >>simulator to test averge lookup distances; it was far more efficient >>to >>keep sectors of voxels (32x32x32) in flat indexing, which made the >>maximum >>distance 1025 > >I can confirm that SatNav units do not keep their maps in Morton code >order. It's not a no-brainer go-to solution for mapping. However, the >analysis done to figure out a good storage order is rather complicated >and off-topic for this list. > >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] Strategies to reduce page-loads?
Ok, then WITHOUT ROWID will most properly fit best in our use case. Then I can fill the PRIMARY KEY with the z-order and store the osm-id just in another column. But do I still need to fill the table in the correct order according to z-order? I mean, we are talking about 1mio rows or so. At which point during insert are the pages actually written? -- Originalnachricht -- Von: "Richard Hipp" An: "SQLite mailing list" Gesendet: 31.08.2018 15:10:15 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/31/18, t...@qvgps.com wrote: >> >>So is it just the value of the primary key controlling in which page >>the >>row is stored? > >The page on which content is stored is determine (approximately) by >the value of the ROWID, which is the same as the INTEGER PRIMARY KEY >as long as you declare the primary key to be of type "INTEGER". If >you declare the PRIMARY KEY to be something other than "INTEGER" (for >example: "INT" or "LONG") then the ROWID and the primary key are >different and the primary key has no influence over where the content >is stored. > >Or if you create a WITHOUT ROWID table, then the PRIMARY KEY is the >value that determines (approximately) on which page the content is >stored. >-- >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] Strategies to reduce page-loads?
>
>(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
>a "Morton code" or "Z-Order curve" of the coordinates.
>(https://en.wikipedia.org/wiki/Z-order_curve) That will cause
>features that are close together geographically to tend to be close
>together within the file.
My primary key is actually the Id of the specific object in the
OpenStreetMap-database, and we also need this osm-id in the app.
I was trying just to order all rows by the "Z-Order curve"-value first
before inserting them.
for each (line in lines.ordered.by.zvalue)
{
insert line into lines
insert line into lines_rtree
}
But this doesn't seem to work.
At least, I don't see any improvements in page-usage.
So is it just the value of the primary key controlling in which page the
row is stored?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies to reduce page-loads?
It would be interesting to "measure" the effect of these ideas during the process of optimizing. I can profile and measure the execution times, but also interesting would be to know, how much pages are involved in a specific query. Is there maybe a way to get the count of pages currently used? -- Originalnachricht -- Von: "Richard Hipp" An: "SQLite mailing list" Gesendet: 30.08.2018 13:48:30 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/30/18, t...@qvgps.com wrote: >> >>Structure is simple: >>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates >>BLOB, Flags INT, StyleId INT); >>And an rtree-index: >>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0 >>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT); > >Three points that might help, either separately or in combination: > >(1) Use exactly "INTEGER PRIMARY KEY". "LONG PRIMARY KEY" and "INT >PRIMARY KEY" are not the same thing and do not work as well. > >(2) In the very latest versions of SQLite, 3.24,0 and the beta for >3.25.0, you can put the "Lines" information directly in the RTree: > > CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0, >z1, +Label, +Coordinates, +Flags, +StyleId); > >The extra columns in r-tree are prefaced by a "+" character so that >the r-tree module knows that they are auxiliary columns and not extra >coordinates. > >(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on >a "Morton code" or "Z-Order curve" of the coordinates. >(https://en.wikipedia.org/wiki/Z-order_curve) That will cause >features that are close together geographically to tend to be close >together within the file. There is are two extension functions in the >https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the >SQLite source tree that might help you with this. Or you can do the >same using your own functions. >-- >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] Strategies to reduce page-loads?
Thanks guys for quick and competent answers! After first sight, this "Z-Order curve" looks very promising, will give it a try. The LONG PRIMARY KEY is because I need a 64-bit integer (osm-id). I only learned now, that sqlite-int is also 64 bit long. Will change to INT PRIMARY KEY now. Tom -- Originalnachricht -- Von: "Richard Hipp" An: "SQLite mailing list" Gesendet: 30.08.2018 13:48:30 Betreff: Re: [sqlite] Strategies to reduce page-loads? >On 8/30/18, t...@qvgps.com wrote: >> >>Structure is simple: >>CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates >>BLOB, Flags INT, StyleId INT); >>And an rtree-index: >>CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0 >>FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT); > >Three points that might help, either separately or in combination: > >(1) Use exactly "INTEGER PRIMARY KEY". "LONG PRIMARY KEY" and "INT >PRIMARY KEY" are not the same thing and do not work as well. > >(2) In the very latest versions of SQLite, 3.24,0 and the beta for >3.25.0, you can put the "Lines" information directly in the RTree: > > CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0, >z1, +Label, +Coordinates, +Flags, +StyleId); > >The extra columns in r-tree are prefaced by a "+" character so that >the r-tree module knows that they are auxiliary columns and not extra >coordinates. > >(3) Consider deliberating choosing INTEGER PRIMARY KEY values based on >a "Morton code" or "Z-Order curve" of the coordinates. >(https://en.wikipedia.org/wiki/Z-order_curve) That will cause >features that are close together geographically to tend to be close >together within the file. There is are two extension functions in the >https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the >SQLite source tree that might help you with this. Or you can do the >same using your own functions. >-- >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] Strategies to reduce page-loads?
Hi Guys,
we are using SQlite for storing OpenStreetMap ways (lines).
Once filled its readonly.
Then the lines is queried for specific areas to draw a map.
Structure is simple:
CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
BLOB, Flags INT, StyleId INT);
And an rtree-index:
CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
Queries are always all lines of a specific geographical ares, which is
very fast because of the rtree.
SELECT Lines.* FROM Lines_bb , Lines WHERE y0 < ? AND y1 > ? AND x0 < ?
AND x1 > ? AND ? BETWEEN z0 AND z1 AND Lines_bb.Id = Lines.Id ;
Then the rows are fetched and here starts the problems:
var stmt = SQLite3.Prepare2 (handle, select);
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
// fetch row
}
The larger the db, the slower is the fetching!
We compared a small db (50mb) and a big db (500mb), both containing the
same small area:
Reading the same area of for example 1000 lines from the small db is 2x
faster then from the large db.
After doing some profiling, it turned out, that the extra time was spent
in SQLite3.Step.
My assumption is, that in the big db, these 1000 lines are just spread
over a much higher count of pages.
So more page-loads resulting in more time.
We changed page_size to the maximum value of 64k and it became much
better, but still I would lke to improve it.
Thanks,
Tom
/
** Flemming Software Development CC
** Thomas Flemming
** PO Box 81244
** Windhoek, Namibia
** http://www.quovadis-gps.com
** mail t...@qvgps.com
** +264 (0)81 3329923 Nam mobile
** +49 (0)175 7313081 D mobile
** +49 (0)6182 8492599 D office
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
