GPKG_SPATIAL_INDEX top-level constant

List<String> const GPKG_SPATIAL_INDEX

Implementation

const GPKG_SPATIAL_INDEX = [
  """
    CREATE VIRTUAL TABLE rtree_TTT_CCC USING rtree(id, minx, maxx, miny, maxy);
""",
  """
    INSERT OR REPLACE INTO rtree_TTT_CCC
      SELECT III, ST_MinX(CCC), ST_MaxX(CCC), ST_MinY(CCC), ST_MaxY(CCC) FROM TTT
      WHERE NOT ST_IsEmpty(CCC);
""",
  // -- Conditions: Insertion of non-empty geometry
  // --   Actions   : Insert record into rtree
  """
    CREATE TRIGGER rtree_TTT_CCC_insert AFTER INSERT ON TTT
      WHEN (new.CCC NOT NULL AND NOT ST_IsEmpty(NEW.CCC))
    BEGIN
      INSERT OR REPLACE INTO rtree_TTT_CCC VALUES (
        NEW.III,
        ST_MinX(NEW.CCC), ST_MaxX(NEW.CCC),
        ST_MinY(NEW.CCC), ST_MaxY(NEW.CCC)
      );
    END;
""",
  // -- Conditions: Update of geometry column to non-empty geometry
  // --               No row ID change
  // --   Actions   : Update record in rtree
  """
    CREATE TRIGGER rtree_TTT_CCC_update1 AFTER UPDATE OF CCC ON TTT
      WHEN OLD.III = NEW.III AND
          (NEW.CCC NOTNULL AND NOT ST_IsEmpty(NEW.CCC))
    BEGIN
      INSERT OR REPLACE INTO rtree_TTT_CCC VALUES (
        NEW.III,
        ST_MinX(NEW.CCC), ST_MaxX(NEW.CCC),
        ST_MinY(NEW.CCC), ST_MaxY(NEW.CCC)
      );
    END;
""",
  // -- Conditions: Update of geometry column to empty geometry
  // --               No row ID change
  // --   Actions   : Remove record from rtree
  """
    CREATE TRIGGER rtree_TTT_CCC_update2 AFTER UPDATE OF CCC ON TTT
      WHEN OLD.III = NEW.III AND
          (NEW.CCC ISNULL OR ST_IsEmpty(NEW.CCC))
    BEGIN
      DELETE FROM rtree_TTT_CCC WHERE id = OLD.III;
    END;
""",
  // -- Conditions: Update of any column
  // --               Row ID change
  // --              Non-empty geometry
  // --   Actions   : Remove record from rtree for old III
  // --               Insert record into rtree for new III
  """
    CREATE TRIGGER rtree_TTT_CCC_update3 AFTER UPDATE OF CCC ON TTT
      WHEN OLD.III != NEW.III AND
          (NEW.CCC NOTNULL AND NOT ST_IsEmpty(NEW.CCC))
    BEGIN
      DELETE FROM rtree_TTT_CCC WHERE id = OLD.III;
      INSERT OR REPLACE INTO rtree_TTT_CCC VALUES (
        NEW.III,
        ST_MinX(NEW.CCC), ST_MaxX(NEW.CCC),
        ST_MinY(NEW.CCC), ST_MaxY(NEW.CCC)
      );
    END;
""",
  // -- Conditions: Update of any column
  // --               Row ID change
  // --               Empty geometry
  // --   Actions   : Remove record from rtree for old and new III
  """
    CREATE TRIGGER rtree_TTT_CCC_update4 AFTER UPDATE ON TTT
      WHEN OLD.III != NEW.III AND
          (NEW.CCC ISNULL OR ST_IsEmpty(NEW.CCC))
    BEGIN
      DELETE FROM rtree_TTT_CCC WHERE id IN (OLD.III, NEW.III);
    END;
""",
  // -- Conditions: Row deleted
  // --   Actions   : Remove record from rtree for old III
  """
    CREATE TRIGGER rtree_TTT_CCC_delete AFTER DELETE ON TTT
      WHEN old.CCC NOT NULL
    BEGIN
      DELETE FROM rtree_TTT_CCC WHERE id = OLD.III;
    END;
""",
  // -- Register the spatial index extension for this table/column
  """
    INSERT INTO $TABLE_EXTENSIONS(table_name, column_name, extension_name, definition, scope)
      VALUES('TTT', 'CCC', 'gpkg_rtree_index', 'GeoPackage 1.0 Specification Annex L', 'write-only');
"""
];