maemo.org - Talk - View Single Post - [Announce] modRana: a flexible GPS navigation system

beermad	2011-08-03 , 17:57
Posts: 21 \| Thanked: 7 times \| Joined on Jul 2010 @ Ipswich, England	#816

Originally Posted by MartinK

Well, I'm not really a database architect - I basically just thought quite a long time about how to make an universal schema and asked a friend who works with databases a bit. Well, like this, the lookup and store databases are independent - so when the lookup one is corrupted, it should be possible to regenerate it just form the stores.

Yeah - say we have x=1 y=2 and z=17 -> 1217, also x=1,y=21,z=7 -> 1217 ...
A hash with separators might work though: 1,2,17 vs 1,21,7 - would something like this be usable ?

Also, would it be possible to maintain backward compatibility by adding this new indexes and still storing the old info ? Converting all the existing database files users might already have would be quite a headache and also some developers might be already working on supporting the format in its current form (IIRC the CloudGPS developer, maybe also some others).

There is a version filed in the schema, so it would be possible to do something like this:
1 = current version

2 = old info + new indexes

3 = just new indexes

I think your proposed versioning makes sense. As long as we add any new columns after the existing columns so that if anybody's already using the schema and doing "select(*)" we won't break their code.

Your idea of a hash-like index using separators seems a good idea. My only other thought was to force each of the x,y,z elements to a fixed length (perhaps by adding 1000000 to each, depending on what the biggest number each one could possibly be). But your method makes it more compact.

Then for version 2, your lookup table could be:

Code:

CREATE TABLE tiles (z INTEGER, x INTEGER, y INTEGER, store_filename STRING, extension varchar(10), unix_epoch_timestamp INTEGER, store_tilenumber INTEGER, lookup STRING, PRIMARY KEY(lookup));
CREATE TABLE version (v integer);
CREATE INDEX oldindex on tiles (z,x,y);

For version 3, drop the z,x,y and extension columns as well as the oldindex index.

Version 2 of the tile store could be:

Code:

CREATE TABLE tiles (z INTEGER, x INTEGER, y INTEGER, tile BLOB, extension VARCHAR(10), unix_epoch_timestamp INTEGER, id INTEGER AUTO INCREMENT, PRIMARY KEY(id));
CREATE TABLE version (v integer);
CREATE INDEX oldindex on tiles (z,x,y);

For version 3, drop the oldindex index. You could keep the x,y,z columns in this table for regenerating the lookup table as you mentioned. You could shrink the lookup table a bit more for version 3 by also dropping the epoch_timestamp from it. That would introduce a slight overhead when checking for updated tiles as it would need to be retrieved from the store rather than the lookup. I'm not sure which way would give the best balance of benefits.

So you'd insert the tile in the store first, then retrieve the value of the id column to populate the store_tilenumber column in the lookup table.

Quote & Reply |