back

new SQL functions introduced (or changed) since version 4.4.0

TOC - Table Of Contents

1. ImportSHP(), ExportSHP(), ImportDBF() and ExportDBF()
2. SQL functions manipulating Sequences

---

1 - ImportSHP(), ExportSHP(), ImportDBF() and ExportDBF()

The default behavior implemented by the C API supporting import or export operations involving an external DBF file has changed since version 4.4.0 (August 2016).

• all Column names for any DB Table created in order to import data from an external Shapefile or DBF file will now be automatically converted by default to full lowercase.
• the above rule doesn't apply to DBF files being exported; in this case the default rule is to leave all Column names unchanged as they are.

A short rationale: practical experience shows that too many users (and/or may be several software tools) tend to adopt lowercase or uppercase names more or less at random.
Usually this is not necessarily a big issue from a pure SQL perspective, but it could easily become very disturbing when you regularly update the same dataset by importing subsequent versions.
More specifically, a sudden switch from lower to uppercase (or the opposite) can mysteriously stop many OWS-based WEB services simply because OWS names are case-sensitive.

The signatures of ImportSHP(), ExportSHP(), ImportDBF() and ExportDBF() have consequently been extended by introducing an additional argument, so as to enable users to finely control this specific facet as they wish.
Here are the new extended signatures:

ImportSHP ( filename Text , table Text , charset Text , srid Integer , geom_column Text , pk_column Text , geometry_type Text , coerce2D Integer ,         compressed Integer , spatial_index Integer , text_dates Integer , colname_case Text , update_statistics Integer , verbose Integer ) : Integer

ExportSHP ( table Text , geom_column Text , filename Text , charset Text , geom_type Text , colname_case Text ) : Integer

ImportDBF ( filename Text , table Text , charset Text , pk_column Text , text_date Integer , colname_case Text ) : Integer

ExportDBF ( table Text , filename Text , charset Text , colname_case Text ) : Integer

The new colname_case argument accepts the following values:

• 'LOWER' or 'LOWERCASE'
  all column names will be converted to full lowercase (default option).
• 'UPPER' or 'UPPERCASE'
  all column names will be converted to full uppercase.
• 'SAME' or 'SAMECASE'
  all column names will be left as they are.

Note: ImportSHP() can now directly update Layer Statistics, depending on the update_statistics argument value (always set to 1 aka TRUE by default).

1a - spatialite CLI support

The same options are now also supported by the corresponding dot macros available on the spatialite CLI frontend, as shown by the following examples:

.loadshp ./myshp my_table CP1252 geom pk_uid MULTIPOLYGON 2d no yes yes UPPERCASE

.dumpshp my_table ./myshp CP1252 POLYGON UPPER

.loaddbf ./my.dbf my_table CP1252 pk_uid yes SAMECASE

.dumpdbf mytable ./my.dbf CP1252 SAME

1b - spatialite_gui support

Please read the appropriate documentation

---

2 - SQL functions manipulating Sequences

Intro: the PostgreSQL DBMS supports Sequences, an interesting feature often useful in many SQL-driven processes. Basically, a Sequence is a number generator producing integer values increasing by 1 for each subsequent call.
SQLite lacks an equivalent capability, so SpatiaLite (starting since version 4.4.0 - August 2016) now supports its own Sequence implementation closely modeled on PostgreSQL, except for a very important aspect that should not be overlooked:

• on PostgreSQL a Sequence is a persistent DB object; a Sequence must be explicitly created and can be eventually altered or dropped.
  The lifecycle of a Sequence spans from its creation to its deletion.
• on SpatiaLite a Sequence is a transient memory object living inside a specific DB Connection, and its lifecycle ends when the hosting Connection terminates.
  A Sequence is automatically created on-the-fly when it's referenced for the first time, and can never be explicitly destroyed other than by terminating the active Connection.

SQL function	Description
sequence_nextval ( seq_name Text ) : Integer	Advances the Sequence identified by seq_name to its next value and then returns the value. If the Sequence is referenced for the first time it will be created on-the-fly. NULL will be returned on any error.
sequence_currval ( seq_name Text ) : Integer	Returns the value most recently obtained by sequence_nextval() for the Sequence identified by seq_name. NULL will be returned on any error or if the Sequence doesn't exist.
sequence_lastval ( void ) : Integer	Returns the value most recently obtained by sequence_nextval() (any Sequence). NULL will be returned on any error or when no Sequence has yet been used.
sequence_setval ( seq_name Text , value Integer ) : Integer	Sets the current value for the Sequence identified by seq_name. If the Sequence doesn't exist it will be created and initialized on-the-fly. A negative value will always be treated as a positive (absolute) value. Will return value on success or NULL on failure.

Unnamed Sequence and Named Sequences

You are absolutely free to define as many Sequences as you wish, each one of them being identified by its own name (Sequence names are always considered to be case-insensitive).
A special case exists: when the argument seq_name is NULL, or when it's of the INTEGER, DOUBLE or BLOB type (non TEXT), the Unnamed Sequence will always be intended.

Examples #1

SELECT sequence_lastval(), sequence_nextval(NULL), sequence_currval('SeqA');
-------------
NULL, 1, NULL

• sequence_lastval() is returning NULL because there wasn't any previous call to sequence_nextval()
• sequence_nextval(NULL) is returning 1 because this is the first call inserting to the unnamed sequence (that has been created on-the-fly).
• sequence_currval('SeqA') is returning NULL because a sequence named 'SeqA' was never used before, so it's still undefined.

SELECT sequence_nextval('SeqA'), sequence_nextval(1), sequence_setval('SeqB', 1000);
-------------
1, 2, 1000

• sequence_nextval('SeqA') is returning 1 because this is the first call inserting to the named sequence 'SeqA' (that has been created on-the-fly)
• sequence_nextval(1) is returning 1 because this is the second call inserting to the unnamed sequence.
• sequence_setval('SeqB', 1000) is returning 1000, thus confirming that a new sequence 'SeqB' has just been created and initialized to that value.

SELECT sequence_nextval('SEQB'), sequence_currval('seqa'), sequence_nextval('SEQA'), sequence_lastval();
------------
1001, 1, 2, 2

• sequence_nextval('SEQB') is returning 1001 because this is the first call inserting to the named sequence 'SeqB' after its initial creation with an explicitly set value.
• sequence_currval('seqa') is returning 1 because this was the value returned by the previous call to sequence_nextval('SeqA').
• sequence_nextval('SEQA') is returning 2 because this is the second call inserting to this sequence since its initial creation.
• sequence_lastval() is returning 2 because this was the value returned by the previous call to sequence_nextval() (any sequence).

---

Example #2

The Problem

1. We'll start with a dataset representing Italian Regions, Provinces and Municipalities (2015).
   you can download the required Shapefiles from here.
2. the goal is to create a new Municipalities Table. For each Municipality the following Columns must be supplied:
   • an unique code (Primary Key, of the TEXT type), formed by concatenating the Province short name and a progressive number (e.g. FI001, FI002, FI003, ...., PA001, PA002, PA003 and so on).
     Codes must be numbered accordingly to lexicographic order of the Municipality name within each Province.
   • Region name, Province short name and Municipality name
   • area (in Sq.Km)
   • relative ranking by area (in descending order); three different ranking levels must be supplied:
     • nation-wide
     • region-wide
     • province-wide

You would imagine that resolving this problem would only require a trivial CREATE TABLE ... AS SELECT ... ORDER BY ... SQL query, thus conveniently ordering the resultset while calling sequence_nextval() during the insertion to the output table. Unhappily SQLite doesn't work this way; you'll soon discover that sequence_nextval() is being called during the fetching of each row and not after the ordering of the resultset. Such a simplistic approach will unfortunately fail. A more sophisticated (and more complex) approach is required; we have to use several temporary tables in order to store some intermediate resultsets before being able to correctly populate the output table. The best strategy is to deploy a short SQL script, as the following one (commented step by step for your convenience).

--
-- starting a Transaction
--
BEGIN;

--
-- creating the output table
--
CREATE TABLE rank_comuni (
    code TEXT PRIMARY KEY,
    nat_rank INTEGER NOT NULL,
    reg_rank INTEGER NOT NULL,    
    prov_rank INTEGER NOT NULL,
    name TEXT NOT NULL,
    reg TEXT NOT NULL,
    prov TEXT NOT NULL,
    sup_km2 DOUBLE);

--
-- first temporary table:
--    * pre-computing areas
--    * ordering by national rank 
--
CREATE TEMPORARY TABLE tmp_comuni1 AS    
SELECT c.comune AS name, r.regione AS reg, p.sigla AS prov, ST_Area(c.geometry) / 1000000.0 AS sup_km2
FROM com2015 AS c
JOIN reg2015 AS r ON (c.cod_reg = r.cod_reg)
JOIN prov2015 AS p ON (c.cod_pro = p.cod_pro)
ORDER BY sup_km2 DESC;

--
-- second temporary table:
--    * setting national rank values
--    * ordering by regional rank
--
CREATE TEMPORARY TABLE tmp_comuni2 AS    
SELECT sequence_nextval('italy') AS nat_rank, name, reg, prov, sup_km2
FROM tmp_comuni1
ORDER BY reg, sup_km2 DESC;

--
-- third temporary table:
--    * setting regional rank values
--    * ordering by provincial rank
--
CREATE TEMPORARY TABLE tmp_comuni3 AS    
SELECT nat_rank, sequence_nextval(reg) AS reg_rank, name, reg, prov, sup_km2
FROM tmp_comuni2
ORDER BY prov, sup_km2 DESC;

--
-- fourth temporary table:
--    * setting national rank values
--    * ordering by region, province and name
--
CREATE TEMPORARY TABLE tmp_comuni4 AS    
SELECT nat_rank, reg_rank, sequence_nextval(prov) AS prov_rank, name, reg, prov, sup_km2
FROM tmp_comuni3
ORDER BY reg, prov, name;

--
-- resetting the ?prov? sequence
--
SELECT sequence_setval(prov, 0)
FROM (SELECT DISTINCT prov FROM tmp_comuni3);

--
-- populating the output table
--
INSERT INTO rank_comuni    
SELECT prov || CastToText(sequence_nextval(prov), 3),
       nat_rank, reg_rank, prov_rank, name, reg, prov, sup_km2
FROM tmp_comuni4;

--
-- dropping all temporary tables
--
DROP TABLE tmp_comuni1;
DROP TABLE tmp_comuni2;
DROP TABLE tmp_comuni3;
DROP TABLE tmp_comuni4;

--
-- committing the pending Transaction
--
COMMIT;

---

back