Dropping columns in sqlite/spatialite

SQLite doesn’t have a nice ALTER TABLE DROP COLUMN command and neither does spatialite. Instead, you get to run a long sequence of commands like this. Here I wanted to drop all the extra columns from my huc12 layer for the country.

So, starting with a table that looks like this

CREATE TABLE "huc12" (  
 PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,  
 "OBJECTID" INTEGER,  
 "HUC_8" TEXT,  
 "HUC_10" TEXT,  
 "HUC_12" TEXT,  
 "ACRES" DOUBLE,  
 "NCONTRB_A" DOUBLE,  
 "HU_10_GNIS" TEXT,  
 "HU_12_GNIS" TEXT,  
 "HU_10_DS" TEXT,  
 "HU_10_NAME" TEXT,  
 "HU_10_MOD" TEXT,  
 "HU_10_TYPE" TEXT,  
 "HU_12_DS" TEXT,  
 "HU_12_NAME" TEXT,  
 "HU_12_MOD" TEXT,  
 "HU_12_TYPE" TEXT,  
 "META_ID" TEXT,  
 "STATES" TEXT,  
 "GlobalID" TEXT,  
 "SHAPE_Leng" DOUBLE,  
 "SHAPE_Area" DOUBLE, "Geometry" MULTIPOLYGON)

and ending with

CREATE TABLE "huc12" (
 PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
 "HUC_12" TEXT,
 "ACRES" DOUBLE,
 "HU_12_GNIS" TEXT,
 "HU_12_NAME" TEXT,
 "HU_12_MOD" TEXT,
 "HU_12_TYPE" TEXT,
 "GlobalID" TEXT,
 "Geometry" MULTIPOLYGON)

I ran this stuff in the middle.

BEGIN TRANSACTION;
 CREATE temporary TABLE "huc12sm" (
 PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
 "HUC_12" TEXT,
 "ACRES" DOUBLE,
 "HU_12_GNIS" TEXT,
 "HU_12_NAME" TEXT,
 "HU_12_MOD" TEXT,
 "HU_12_TYPE" TEXT,
 "GlobalID" TEXT,
 "Geometry" MULTIPOLYGON);
 INSERT INTO huc12sm SELECT PK_UID, HUC_12, ACRES, HU_12_GNIS, HU_12_NAME, HU_12_MOD, HU_12_TYPE, GlobalID, Geometry FROM huc12;
 DROP TABLE huc12;
 CREATE TABLE "huc12" (
 PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
 "HUC_12" TEXT,
 "ACRES" DOUBLE,
 "HU_12_GNIS" TEXT,
 "HU_12_NAME" TEXT,
 "HU_12_MOD" TEXT,
 "HU_12_TYPE" TEXT,
 "GlobalID" TEXT,
 "Geometry" MULTIPOLYGON);
 INSERT INTO huc12 SELECT PK_UID, HUC_12, ACRES, HU_12_GNIS, HU_12_NAME, HU_12_MOD, HU_12_TYPE, GlobalID, Geometry FROM huc12sm;
 DROP TABLE huc12sm;
 COMMIT;
 VACUUM;
 HUC_12, ACRES, HU_12_GNIS, HU_12
 SELECT Count(*), GeometryType("Geometry"), Srid("Geometry"), CoordDimension("Geometry")
 FROM "huc12"
 GROUP BY 2, 3, 4;

SELECT RebuildGeometryTriggers('huc12', 'Geometry')

I wish someone would package this stuff into a nice function… hint hint.