D 2019-12-24T11:09:17.686
L Drop-RenameTable\sand\sRenameColumn
P fc7558abb5a0deb61df19bb46d3fbb3f59b3feff
U sandro
W 12524
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a><hr><br>
<h1>Introduction</h1>
All historical versions of <b>SQLite3</b> have always presented several limitations affecting the implementation of the standard SQL statement <b>ALTER TABLE</b>:
<ul>
<li><b>ALTER TABLE <i>old_name</i> RENAME TO <i>new_name</i></b> was effectively supported, but the name change simply applied to the target Table itself.<br>
It didn't propagate to all depending Foreign Keys, Triggers and Views, thus easily leading to an inconsistent and buggy DB layout.</li>
<li><b>ALTER TABLE <i>tbl_name</i> RENAME COLUMN <i>old_col</i> TO <i>new_col</i></b> wasn't supported at all.</li>
</ul>
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.<br><br>
The situation has radically changed since the released version <b>3.25.0</b> of <b><i>2018-09-15</i></b>.<br>
SQLite3 now fully supports both of the above forms of <b>ALTER TABLE</b> and this, longly awaited, good news means that SQLite3 now behaves in the same way as any other DBMS on the market.<br><br>
Seen from the SpatiaLite perspective, problems arise that need to be addressed.<ul>
<li>As a general rule: always assume that <b>SQLite3</b> does not know (<i>nor care</i>) about Administration TABLE used by any <b>extensions</b> (such as <b>SpatiaLite</b> or <b>GeoPackage</b>).</li>
</ul>
Therefore any use of <b>ALTER TABLE</b> on any Spatial Table or Column will have <b><i>no effect</i></b> on any Administration TABLE, causing the creation of an invalid DB layout
<ul>
<li>caused by <b>broken links</b> to all used Metadata Tables entries and Spatial Indices</li>
</ul>
<br><br>
To resolve this issue, a complete set of corresponding SQL functions have been introduced in version <b>5.0.0</b> to insure that any Administration tasks needed are done correctly.
<ul>
<li><b>DropTable()</b>: can safely drop a Spatial Table, Spatial View or Spatial VirtualTable.<br>
Any dependent Triggers, Spatial Views, Spatial Indexes, Metadata and Statistics will be properly removed fully preserving the DB layout consistency.<br>
This will also work on any ordinary (non Spatial) Table, RasterLite2 Raster Coverage, VirtualTable, GeoPackage (Vector / Raster Table) and OGR/FDO Table.<br>
<i><u>Note</u></i>: this function is not directly related to the recent changes introduced by SQLite3
<ul>
<li>but being closely related to the administration tasks for Spatial Tables/Views has been adapted, replacing the <i>deprecated</i> <b>DropGeoTable()</b> function in full.</li>
</ul></li>
<li><b>RenameTable()</b>: can safely rename a Spatial (or non-Spatial) Table, but <b>cannot be used</b> for any kind of View or VirtualTable.<br>
Any dependent Triggers, Views, Spatial Indexes, Metadata and Statistics will be properly updated fully preserving the DB layout consistency.
<ul>
<li><b>VIEWs</b>: only the name of the <b>underlaying</b> TABLE used by the VIEW will be renamed.</li>
</ul>
This will also work on any ordinary (non Spatial) Table, RasterLite2 Raster Coverage, GeoPackage and OGR/FDO Table.</li>
<li><b>RenameColumn()</b>: can safely rename a Spatial (or non-Spatial), but <b>cannot be used</b> for any kind of View or VirtualTable.<br>
Any depending Trigger, View, Spatial Index, Metadata and Statistics will be properly updated fully preserving the DB layout consistency.
<ul>
<li><b>VIEWs</b>: only the Column-name of the <b>underlaying</b> TABLE used by the VIEW will be renamed.</li>
</ul>
Will also work on ordinary (non Spatial) Columns, GeoPackage and OGR/FDO Geometries.</li>
<li><b>Special Note</b>: SQLite <b>CREATE VIEW</b> syntax explicitly declaring columns:
<ul>
<li>The <b>explicit</b> declaration of Columns in the <b>CREATE VIEW</b> is (officially) optional.</li>
<li>Unofficially, any Column not <b>explicitly</b> defined is considered <i>undefined</i> and SQLite will attempt to resolve the <b>old</b> name by looking inside the underlaying <b>TABLE</b></li>
<li>More information and practical samples on how to <b>explicitly</b> define columns can be found here: <a href="https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.adminstration.html#topic_Adminstration_CREATE:VIEW_COLUMNS"><b>SQLite</b>: <b>CREATE VIEW</b> syntax <b>explicitly</b> declaring columns</a></li>
</ul></li>
</ul>
<br>
Let's now see each single SQL function in full details.<br><br>
<hr>
<table cellspacing="8" cellpadding="8" bgcolor="#ffff90"><tr><td>
<h3>Warning</h3>
Both <b>RenameTable()</b> and <b>RenameColumn()</b> strictly require SQLite3 version <b>3.25.0</b> (or greater).<br>
If SpatiaLite is running against an earlier version both functions will always raise an <b>Exception</b> complaining about the mismatching library version.
</td></tr></table>
<hr>
<h2>DropTable()</h2>
<verbatim>
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
</verbatim>
Supported arguments with their interpretation:
<ul>
<li><b>db-prefix</b>: this first mandatory argument, of the <b>TEXT</b> type, must contain the prefix identifying the <i>attached DB</i> where the Table or View to be dropped is expected to be.<br>
When <b>NULL</b> is used: <b>'MAIN'</b> DB will be implicitly assumed.</li>
<li><b>name</b>: this second mandatory argument, of the <b>TEXT</b> type, must contain the name of the Table (or View) to be dropped.</li>
<li><b>permissive</b>: this third optional argument, of the <b>BOOLEAN</b> type, can be appropriately set in order to determine the expected behavior in the case of failure:
<ul>
<li>if <b>permissive=FALSE</b> any error (both invalid arguments or failure due e.g. to specifying a non existing Table) will raise an <b>SQL Exception</b>, thus immediately stopping the execution of any following SQL statement.</li>
<li>if <b>permissive=TRUE</b> only the errors caused by invalid arguments will raise an Exception.<br>
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.<br>
Simply stated, <b><i>permissive-mode</i></b> is more or less the same then specifying a standard SQL <b>IF EXISTS</b> clause.</li>
<li><i><u>Note</u></i>: the default setting is <b>permissive=FALSE</b>. (i.e. the TABLE <b>must</b> exist).</li>
<li><i><u>Hint</u></i>: opting for the <b><i>non-permissive</i></b> 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.<br>
On the other hand when executing some more or less complex SQL Script <b><i>permissive</i></b> 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.
</ul></li>
<li>the <b>return value</b> is of the <b>BOOLEAN</b> type:
<ul>
<li><b>TRUE</b> in the case of success.</li>
<li><b>FALSE</b> (or an <b>Exception</b>) in the case of invalid arguments or failure.</li>
</ul></li>
<li><b>Note</b>: 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.</li>
</ul>
<br>
<table cellspacing="8" cellpadding="8" bgcolor="#ffff90"><tr><td>
<h3>Caveat</h3>
A similar function named <b>DropGeoTable()</b> was supported by previous versions of SpatiaLite.<br><br>
<b>DropGeoTable()</b> is now <b>DEPRECATED</b>, and will remain to avoid breaking existing applications and scripts.<br>
It is strongly recommended use <b>DropTable()</b> for any new development as full and more reliable replacement.
</td></tr></table>
<br>
<hr>
<h2>RenameTable()</h2>
<verbatim>
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
</verbatim>
Supported arguments with their interpretation:
<ul>
<li><b>db-prefix</b>: this first mandatory argument, of the <b>TEXT</b> type, must contain the prefix identifying the <i>attached DB</i> where the Table to be renamed is expected to be.<br>
When <b>NULL</b> is used: <b>'MAIN'</b> DB will be implicitly assumed.</li>
<li><b>old_name</b>: this second mandatory argument, of the <b>TEXT</b> type, must contain the name of the Table to be renamed.</li>
<li><b>new_name</b>: this third mandatory argument, of the <b>TEXT</b> type, must contain the new name to be assigned to the Table.</li>
<li><b>permissive</b>: this fourth optional argument, of the <b>BOOLEAN</b> type, can be appropriately set in order to determine the expected behavior in the case of failure.<br>
The interpretation is exactly the same as in <b>DropTable()</b>.</li>
<li>the <b>return value</b> is of the <b>BOOLEAN</b> type:
<ul>
<li><b>TRUE</b> in the case of success.</li>
<li><b>FALSE</b> (or an <b>Exception</b>) in the case of invalid arguments or failure.</li>
</ul></li>
<li><b>Note</b>: 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.</li>
</ul>
<br>
<table cellspacing="8" cellpadding="8" bgcolor="#ffff90"><tr><td>
<h3>Be aware</h3>
SQLite3 can never rename a <b>View</b> or <b>VirtualTable</b>.<br>
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.
</td></tr></table>
<br>
<hr>
<h2>RenameColumn()</h2>
<verbatim>
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
</verbatim>
Supported arguments with their interpretation:
<ul>
<li><b>db-prefix</b>: this first mandatory argument, of the <b>TEXT</b> type, must contain the prefix identifying the <i>attached DB</i> where the Table containing th Column to be renamed is expected to be.<br>
When <b>NULL</b> is used: <b>'MAIN'</b> DB will be implicitly assumed.</li>
<li><b>table_name</b>: this second mandatory argument, of the <b>TEXT</b> type, must contain the name of the Table to which belongs to Column to be renamed.</li>
<li><b>old_name</b>: this third mandatory argument, of the <b>TEXT</b> type, must contain the name of the Column to be renamed.</li>
<li><b>new_name</b>: this fourth mandatory argument, of the <b>TEXT</b> type, must contain the new name to be assigned to the Column.</li>
<li><b>permissive</b>: this fifth optional argument, of the <b>BOOLEAN</b> type, can be appropriately set in order to determine the expected behavior in the case of failure.<br>
The interpretation is exactly the same as in <b>DropTable()</b>.</li>
<li>the <b>return value</b> is of the <b>BOOLEAN</b> type:
<ul>
<li><b>TRUE</b> in the case of success.</li>
<li><b>FALSE</b> (or an <b>Exception</b>) in the case of invalid arguments or failure.</li>
</ul></li>
<li><b>Note</b>: 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.</li>
</ul>
<br>
<table cellspacing="8" cellpadding="8" bgcolor="#ffff90"><tr><td>
<h3>Be aware</h3>
SQLite3 can never rename Columns belonging to a <b>View</b> or <b>VirtualTable</b>.<br>
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.
</td></tr></table>
<br>
<hr><br>
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a>
Z 12a3399bc6e5594bcc89659b42300507