Autoincrementing fields¶
For QGIS 0.7 we have a minimum version of sqlite3.0 required. However the implementation of autoincrementing fields differs between 3.1 and above and versions below 3.0. This particularly affect you if you wish to have your autoincrementing primary key begin from a number other than 1. In the case of the users qgis.db tbl_srs I wanted to start from record #10000 so that I could ensure that there would be no overlab with the srs.db qgis system level database. After a bit of googling I found:
http://comments.gmane.org/gmane.comp.db.sqlite.general/10777
>> I used this SQL: >> >> CREATE TABLE NewEmployees(EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT, >> LastName TEXT, FirstName TEXT); >> >> and using SQLite3.exe did this >> >> SQLite3 employees.db >> .read create.sql >>.exit >> >> which created my database but no tools nor the SQLite.NET provider can >> read it, I get the error "malformed database schema near AUTOINCREMENT?
>That really sounds as if the tools and the provider are using a pre-3.1 >version of SQLite, since AUTOINCREMENT wasn't introduced until 3.1.0.
Now in Ubuntu default sqlite install is:
ii libsqlite0 2.8.15-3 SQLite shared library ii libsqlite3-0 3.0.8-3 SQLite 3 shared library ii libsqlite3-dev 3.0.8-3 SQLite 3 development files ii python-sqlite 1.0.1-1ubuntu1 Python interface to SQLite ii sqlite3 3.0.8-3 A command line interface for SQLite 3 ii sqlite3-doc 3.0.8-3 SQLite 3 documentation
The strange thing is if you look at the following session, I have an auto incrementeing field in the original srs table I made using the autonum approach I read in the docs somewhere - and it works fine....
SQLite version 3.0.8 Enter ".help" for instructions sqlite> .s tbl_srs CREATE TABLE tbl_srs ( srs_id INTEGER PRIMARY KEY, description text NOT NULL, projection_acronym text NOT NULL, ellipsoid_acronym NOT NULL, parameters text NOT NULL, srid integer NOT NULL, epsg integer NOT NULL, is_geo integer NOT NULL); CREATE UNIQUE INDEX idx_srsepsg on tbl_srs(epsg); CREATE UNIQUE INDEX idx_srssrid on tbl_srs(srid);
sqlite> select * from tbl_srs order by srs_id desc limit 1; 2672|NAD27 / Alaska Albers|aea|clrk66|+proj=aea +lat_1=55 +lat_2=65 +lat_0=50 +lon_0=-154 +x_0=0 +y_0=0 +ellps=clrk66 +datum=NAD27 +to_meter=0.3048006096012192 +no_defs|2964|2964|0
sqlite> insert into tbl_srs (description,projection_acronym,ellipsoid_acronym,parameters,srid,epsg,is_geo) values ('test','test','test','test',1111,1111,1);
sqlite> select * from tbl_srs order by srs_id desc limit 1;
2673|test|test|test|test|1111|1111|1
See how it assigns 2673 to the next rec?
Now if I change the to using an autoinc field (Im pulling the data in from a sql dump in the example below) and simply remove the autoinc clause, the primary key on qgs.db tbl_srs increments whenever I add a new rec (this is as described in the sqlite docs somewhere):
aps02ts@ubuntu:/tmp$ sqlite3 qgis.db
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> .read qgis.db.dump
DELETE FROM sqlite_sequence;
SQL error: no such table: sqlite_sequence
INSERT INTO "sqlite_sequence" VALUES('tbl_srs', 99999);
SQL error: no such table: sqlite_sequence
sqlite> select * from tbl_srs order by srs_id desc limit 1;
sqlite> insert into tbl_srs (description,projection_acronym,ellipsoid_acronym,parameters,srid,epsg,is_geo) values ('test','test','test','test',1111,1111,1);
sqlite> select * from tbl_srs order by srs_id desc limit 1;
1|test|test|test|test|1111|1111|1
sqlite> insert into tbl_srs (description,projection_acronym,ellipsoid_acronym,parameters,srid,epsg,is_geo) values ('test','test','test','test',1111,1111,1);
sqlite> select * from tbl_srs order by srs_id desc limit 1;
2|test|test|test|test|1111|1111|1
sqlite>
Notice how this line failed though:
INSERT INTO "sqlite_sequence" VALUES;
SQL error: no such table: sqlite_sequence
I suspect in sqlite <3.1 this system table does not exist. I tried redoing the above line after I had inserted a couple of dummy recs into the srs tbl but the same error message occurs. Finally I tried this:
sqlite> insert into tbl_srs (srs_id,description,projection_acronym,ellipsoid_acronym,parameters,srid,epsg,is_geo) values (9999,'test','test','test','test',1111,1111,1); sqlite> select * from tbl_srs order by srs_id desc limit 1; 9999|test|test|test|test|1111|1111|1
i.e forcing the srs_id for a record. Then on the next insert I allowed the srs_id to default again and voila!:
sqlite> insert into tbl_srs (description,projection_acronym,ellipsoid_acronym,parameters,srid,epsg,is_geo) values ('test','test','test','test',1111,1111,1);
sqlite> select * from tbl_srs order by srs_id desc limit 1;
10000|test|test|test|test|1111|1111|1
However there is a small problem with this approach - if you delete all the records, the sqlite < 3.1 incmenetor will drop back to 1 next time a record is added.
Field aliases in views¶
Another important difference I found is that for sqlite < 3.1 views with unaliased fields work fine, but for >= 3.1 they need aliasing. This means that the example below:
CREATE VIEW vw_srs as
select a.description,
a.srs_id,
a.is_geo,
b.name,
a.parameters
from tbl_srs a
inner join tbl_projection b
on a.projection_acronym=b.acronym
order by
b.name, a.description;
Wont work in newer versions of sqlite 3, but changing it to look like this:
CREATE VIEW vw_srs as
select a.description as description,
a.srs_id as srs_id,
a.is_geo as is_geo,
b.name as name,
a.parameters as parameters
from tbl_srs a
inner join tbl_projection b
on a.projection_acronym=b.acronym
order by
b.name, a.description;
works fine in all versions of sqlite 3.x