Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID:	7c048675f80cd9e1a69f7a9f02ded1dcb19c2c85
Page Name:	Drop-RenameTable and RenameColumn
Date:	2019-12-24 11:09:17
Original User:	sandro
Parent:	fc7558abb5a0deb61df19bb46d3fbb3f59b3feff (diff)

Content

Back to 5.0.0-doc main page

Introduction

All historical versions of SQLite3 have always presented several limitations affecting the implementation of the standard SQL statement ALTER TABLE:

ALTER TABLE old_name RENAME TO new_name was effectively supported, but the name change simply applied to the target Table itself.
It didn't propagate to all depending Foreign Keys, Triggers and Views, thus easily leading to an inconsistent and buggy DB layout.
ALTER TABLE tbl_name RENAME COLUMN old_col TO new_col wasn't supported at all.

All this was inconsistent from the usual behavior of other SQL DBMSes, and was easily perceived by many developers and users as a nasty and unpleasant limitation.

The situation has radically changed since the released version 3.25.0 of 2018-09-15.
SQLite3 now fully supports both of the above forms of ALTER TABLE and this, longly awaited, good news means that SQLite3 now behaves in the same way as any other DBMS on the market.

Seen from the SpatiaLite perspective, problems arise that need to be addressed.

As a general rule: always assume that SQLite3 does not know (nor care) about Administration TABLE used by any extensions (such as SpatiaLite or GeoPackage).

Therefore any use of ALTER TABLE on any Spatial Table or Column will have no effect on any Administration TABLE, causing the creation of an invalid DB layout

caused by broken links to all used Metadata Tables entries and Spatial Indices

To resolve this issue, a complete set of corresponding SQL functions have been introduced in version 5.0.0 to insure that any Administration tasks needed are done correctly.

DropTable(): can safely drop a Spatial Table, Spatial View or Spatial VirtualTable.
Any dependent Triggers, Spatial Views, Spatial Indexes, Metadata and Statistics will be properly removed fully preserving the DB layout consistency.
This will also work on any ordinary (non Spatial) Table, RasterLite2 Raster Coverage, VirtualTable, GeoPackage (Vector / Raster Table) and OGR/FDO Table.
Note: this function is not directly related to the recent changes introduced by SQLite3
- but being closely related to the administration tasks for Spatial Tables/Views has been adapted, replacing the deprecated DropGeoTable() function in full.
RenameTable(): can safely rename a Spatial (or non-Spatial) Table, but cannot be used for any kind of View or VirtualTable.
Any dependent Triggers, Views, Spatial Indexes, Metadata and Statistics will be properly updated fully preserving the DB layout consistency.
- VIEWs: only the name of the underlaying TABLE used by the VIEW will be renamed.
This will also work on any ordinary (non Spatial) Table, RasterLite2 Raster Coverage, GeoPackage and OGR/FDO Table.
RenameColumn(): can safely rename a Spatial (or non-Spatial), but cannot be used for any kind of View or VirtualTable.
Any depending Trigger, View, Spatial Index, Metadata and Statistics will be properly updated fully preserving the DB layout consistency.
- VIEWs: only the Column-name of the underlaying TABLE used by the VIEW will be renamed.
Will also work on ordinary (non Spatial) Columns, GeoPackage and OGR/FDO Geometries.
Special Note: SQLite CREATE VIEW syntax explicitly declaring columns:
- The explicit declaration of Columns in the CREATE VIEW is (officially) optional.
- Unofficially, any Column not explicitly defined is considered undefined and SQLite will attempt to resolve the old name by looking inside the underlaying TABLE
- More information and practical samples on how to explicitly define columns can be found here: SQLite: CREATE VIEW syntax explicitly declaring columns

Let's now see each single SQL function in full details.

Warning

Both RenameTable() and RenameColumn() strictly require SQLite3 version 3.25.0 (or greater).
If SpatiaLite is running against an earlier version both functions will always raise an Exception complaining about the mismatching library version.

DropTable()

SELECT DropTable('some_db', 'some_table');
----------------------
1

SELECT DropTable(NULL, 'inexistent_table');
----------------------
DropTable exception - not existing table [main.inexistent_table]

SELECT DropTable(NULL, 'inexistent_table', 1);
----------------------
0

Supported arguments with their interpretation:

db-prefix: this first mandatory argument, of the TEXT type, must contain the prefix identifying the attached DB where the Table or View to be dropped is expected to be.
When NULL is used: 'MAIN' DB will be implicitly assumed.
name: this second mandatory argument, of the TEXT type, must contain the name of the Table (or View) to be dropped.
permissive: this third optional argument, of the BOOLEAN type, can be appropriately set in order to determine the expected behavior in the case of failure:
- if permissive=FALSE any error (both invalid arguments or failure due e.g. to specifying a non existing Table) will raise an SQL Exception, thus immediately stopping the execution of any following SQL statement.
- if permissive=TRUE only the errors caused by invalid arguments will raise an Exception.
  Failures due e.g. to specifying a non existing Table will just return an error code, but the execution of following SQL statements will continue unaffected.
  Simply stated, permissive-mode is more or less the same then specifying a standard SQL IF EXISTS clause.
- Note: the default setting is permissive=FALSE. (i.e. the TABLE must exist).
- Hint: opting for the non-permissive mode is best fit when interactively typing SQL statements on the keyboard, because this way any possible error will be immediately reported in full details.
  On the other hand when executing some more or less complex SQL Script permissive mode is often more useful, because trivial and forgivable errors (such as attempting to drop a non existing table) will not block the execution of the script.
the return value is of the BOOLEAN type:
- TRUE in the case of success.
- FALSE (or an Exception) in the case of invalid arguments or failure.
Note: this function carefully checks to insure that the dropping of any System or Metadata Tables internally required by either SQLite3, SpatiaLite, RasterLite2, GeoPackage or FDO is prevented.

Caveat

A similar function named DropGeoTable() was supported by previous versions of SpatiaLite.

DropGeoTable() is now DEPRECATED, and will remain to avoid breaking existing applications and scripts.
It is strongly recommended use DropTable() for any new development as full and more reliable replacement.

RenameTable()

SELECT RenameTable('some_db', 'old_table', 'new_table');
----------------------
1

SELECT RenameTable(NULL, 'old_table', 'existing_new_table');
----------------------
RenameTable exception - already existing table [main.existing_new_table]

SELECT RenameTable(NULL, 'old_table', 'existing_new_table', 1);
----------------------
0

Supported arguments with their interpretation:

db-prefix: this first mandatory argument, of the TEXT type, must contain the prefix identifying the attached DB where the Table to be renamed is expected to be.
When NULL is used: 'MAIN' DB will be implicitly assumed.
old_name: this second mandatory argument, of the TEXT type, must contain the name of the Table to be renamed.
new_name: this third mandatory argument, of the TEXT type, must contain the new name to be assigned to the Table.
permissive: this fourth optional argument, of the BOOLEAN type, can be appropriately set in order to determine the expected behavior in the case of failure.
The interpretation is exactly the same as in DropTable().
the return value is of the BOOLEAN type:
- TRUE in the case of success.
- FALSE (or an Exception) in the case of invalid arguments or failure.
Note: this function carefully checks to insure that the renaming of any System or Metadata Tables internally required by either SQLite3, SpatiaLite, RasterLite2, GeoPackage or FDO is prevented.

Be aware

SQLite3 can never rename a View or VirtualTable.
If you absolutely need to change the name of any View or VirtualTable after it's creation, you must first drop it, and then recreate it with the new name.

RenameColumn()

SELECT RenameColumn('some_db', 'table_name', 'old_column', 'new_column');
----------------------
1

SELECT RenameColumn(NULL, 'table_name', 'old_column', 'existing_column');
----------------------
RenameColumn exception - column already defined [main.table_name] existing_column

SELECT RenameColumn(NULL, 'table_name', 'old_column', 'existing_column', 1);
----------------------
0

Supported arguments with their interpretation:

db-prefix: this first mandatory argument, of the TEXT type, must contain the prefix identifying the attached DB where the Table containing th Column to be renamed is expected to be.
When NULL is used: 'MAIN' DB will be implicitly assumed.
table_name: this second mandatory argument, of the TEXT type, must contain the name of the Table to which belongs to Column to be renamed.
old_name: this third mandatory argument, of the TEXT type, must contain the name of the Column to be renamed.
new_name: this fourth mandatory argument, of the TEXT type, must contain the new name to be assigned to the Column.
permissive: this fifth optional argument, of the BOOLEAN type, can be appropriately set in order to determine the expected behavior in the case of failure.
The interpretation is exactly the same as in DropTable().
the return value is of the BOOLEAN type:
- TRUE in the case of success.
- FALSE (or an Exception) in the case of invalid arguments or failure.
Note: this function carefully checks to insure that the renaming of any Columns belonging to System or Metadata Tables internally required by either SQLite3, SpatiaLite, RasterLite2, GeoPackage or FDO is prevented.

Be aware

SQLite3 can never rename Columns belonging to a View or VirtualTable.
If you absolutely need to change the name of any Columns in a View or VirtualTable after it's creation, you must first drop it, and then recreate it with the new Column name.

Back to 5.0.0-doc main page