I have a huge Geopackage where the included tables have foreign names. I would like to rename these tables to something more intuitive so that the data gets easier for me to work with. There is a external text file with translations that I would like to use.
I guess there are two ways to do this: Either run a workspace and save a new geopackage with the "new" table names, or rename when opening the file. Both methods are fine by me.
How do I rename the tables?
I think the quickest option would be to use a SQLExecutor to fire a couple of ALTER queries to the Geopackage. If you read in that text file with the translations and it has both the original and new names that could be easily done. It'll save you having to read and write all the data.
Thanks! I am unfortunately a total SQL noob, but I got this far by googling:
ALTER TABLE @Value(old_file)
RENAME TO @Value(new_file);
Problem is that RENAME TO returns a unknown error. Is the command not supported or did I mess up the syntax?
@aron In theory, you should be able to rename the table using ALTER TABLE <mytable> RENAME TO <yourtable>, provided you use the Geopackage format in the SQLCreator. FME will then use the GDAL driver which will build the appropriate SQL to also alter all the metadata tables and indices. I couldn't get it to work - I'm getting a readonly database error. QGIS has a similar function I believe.
The full SQL that the GDAL driver builds looks something like this - so lot's of room for error...
UPDATE gpkg_geometry_columns SET table_name = 'road_cl'
WHERE lower(table_name )= lower('road_centrelines');
UPDATE gpkg_contents SET identifier = 'road_cl'
WHERE lower(table_name) = lower('road_centrelines') AND identifier = 'road_centrelines';
UPDATE gpkg_contents SET table_name = 'road_cl' WHERE lower(table_name )= lower('road_centrelines');
UPDATE gpkg_extensions SET table_name = 'road_cl' WHERE lower(table_name )= lower('road_centrelines');
ALTER TABLE "road_centrelines" RENAME TO "road_cl";DROP TRIGGER "rtree_road_centrelines_geom_insert";
DROP TRIGGER "rtree_road_centrelines_geom_update1";DROP TRIGGER "rtree_road_centrelines_geom_update2";
DROP TRIGGER "rtree_road_centrelines_geom_update3";DROP TRIGGER "rtree_road_centrelines_geom_update4";
DROP TRIGGER "rtree_road_centrelines_geom_delete";;
ALTER TABLE "rtree_road_centrelines_geom" RENAME TO "rtree_road_cl_geom";
CREATE TRIGGER "rtree_road_cl_geom_insert" AFTER INSERT ON "road_cl" WHEN (new."geom" NOT NULL AND NOT ST_IsEmpty(NEW."geom"))
BEGIN INSERT OR REPLACE INTO "rtree_road_cl_geom" VALUES (NEW."id",ST_MinX(NEW."geom"), ST_MaxX(NEW."geom"),ST_MinY(NEW."geom"), ST_MaxY(NEW."geom"));
END;
CREATE TRIGGER "rtree_road_cl_geom_update1" AFTER UPDATE OF "geom" ON "road_cl" WHEN OLD."id" = NEW."id" AND (NEW."geom" NOTNULL AND NOT ST_IsEmpty(NEW."geom")) BEGIN INSERT OR REPLACE INTO "rtree_road_cl_geom" VALUES (NEW."id",ST_MinX(NEW."geom"), ST_MaxX(NEW."geom"),ST_MinY(NEW."geom"), ST_MaxY(NEW."geom"));
END;
CREATE TRIGGER "rtree_road_cl_geom_update2" AFTER UPDATE OF "geom" ON "road_cl" WHEN OLD."id" = NEW."id" AND (NEW."geom" ISNULL OR ST_IsEmpty(NEW."geom")) BEGIN DELETE FROM "rtree_road_cl_geom" WHERE id = OLD."id";
END;
CREATE TRIGGER "rtree_road_cl_geom_update3" AFTER UPDATE ON "road_cl" WHEN OLD."id" != NEW."id" AND (NEW."geom" NOTNULL AND NOT ST_IsEmpty(NEW."geom")) BEGIN DELETE FROM "rtree_road_cl_geom" WHERE id = OLD."id";
INSERT OR REPLACE INTO "rtree_road_cl_geom" VALUES (NEW."id",ST_MinX(NEW."geom"), ST_MaxX(NEW."geom"),ST_MinY(NEW."geom"), ST_MaxY(NEW."geom"));
END;
CREATE TRIGGER "rtree_road_cl_geom_update4" AFTER UPDATE ON "road_cl" WHEN OLD."id" != NEW."id" AND (NEW."geom" ISNULL OR ST_IsEmpty(NEW."geom")) BEGIN DELETE FROM "rtree_road_cl_geom" WHERE id IN (OLD."id", NEW."id");
END;
CREATE TRIGGER "rtree_road_cl_geom_delete" AFTER DELETE ON "road_cl" WHEN old."geom" NOT NULL BEGIN DELETE FROM "rtree_road_cl_geom" WHERE id = OLD."id";
END;