Revision 47606 trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/src/main/resources/org/gvsig/sqlite/dal/sqltemplates/rtree.sql

View differences:

rtree.sql
12 12 
CREATE TRIGGER "rtree_%table_name$s_%geom_name$s_delete" AFTER DELETE ON "%table_name$s"
13 13 
  WHEN old."%geom_name$s" NOT NULL
14 14 
BEGIN
15 
  DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE %id_name$s = OLD."%id_name$s";
15 
  DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE ID = OLD."%id_name$s";
16 16 
END
17 17 
@end create_trigger_rtree_delete
18 18 

  		




  ......




  56
  56
  
    
  WHEN OLD."%id_name$s" = NEW."%id_name$s" AND


  		




  57
  57
  
    
       (NEW."%geom_name$s" ISNULL OR ST_IsEmpty(NEW."%geom_name$s"))


  		




  58
  58
  
    
BEGIN


  		




  59
  
  
    
  DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE %id_name$s = OLD."%id_name$s";


  		




  
  59
  
    
  DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE ID = OLD."%id_name$s";


  		




  60
  60
  
    
END


  		




  61
  61
  
    
@end create_trigger_rtree_update2


  		




  62
  62
  
    

  		




  ......




  68
  68
  
    
  WHEN OLD."%id_name$s" != NEW."%id_name$s" AND


  		




  69
  69
  
    
       (NEW."%geom_name$s" NOTNULL AND NOT ST_IsEmpty(NEW."%geom_name$s"))


  		




  70
  70
  
    
BEGIN


  		




  71
  
  
    
  DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE %id_name$s = OLD."%id_name$s";


  		




  
  71
  
    
  DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE ID = OLD."%id_name$s";


  		




  72
  72
  
    
  INSERT OR REPLACE INTO "rtree_%table_name$s_%geom_name$s" VALUES (


  		




  73
  73
  
    
    NEW."%id_name$s",


  		




  74
  74
  
    
    ST_MinX(NEW."%geom_name$s"), ST_MaxX(NEW."%geom_name$s"),


  		




  ......




  85
  85
  
    
  WHEN OLD."%id_name$s" != NEW."%id_name$s" AND


  		




  86
  86
  
    
       (NEW."%geom_name$s" ISNULL OR ST_IsEmpty(NEW."%geom_name$s"))


  		




  87
  87
  
    
BEGIN


  		




  88
  
  
    
  DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE %id_name$s IN (OLD."%id_name$s", NEW."%id_name$s");


  		




  
  88
  
    
  DELETE FROM "rtree_%table_name$s_%geom_name$s" WHERE ID IN (OLD."%id_name$s", NEW."%id_name$s");


  		




  89
  89
  
    
END


  		




  90
  90
  
    
@end create_trigger_rtree_update4


  		




  91
  91
  
    

  		




  92
  92
  
    

  		




  93
  93
  
    
@begin create_table_gpkg_extensions


  		




  94
  
  
    
CREATE TABLE IF NOT EXISTS gpkg_extensions (


  		




  95
  
  
    
    table_name TEXT, 


  		




  96
  
  
    
    column_name TEXT, 


  		




  97
  
  
    
    extension_name TEXT NOT NULL, 


  		




  98
  
  
    
    definition TEXT NOT NULL, 


  		




  99
  
  
    
    scope TEXT NOT NULL, 


  		




  100
  
  
    
    CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) )


  		




  
  94
  
    
CREATE TABLE IF NOT EXISTS gpkg_extensions ( table_name TEXT, column_name TEXT, extension_name TEXT NOT NULL, definition TEXT NOT NULL, scope TEXT NOT NULL, CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) )


  		




  101
  95
  
    
@end create_table_gpkg_extensions


  		




  102
  96
  
    

  		




  103
  97
  
    
@begin insert_into_table_gpkg_extensions


  		




  ......




  133
  127
  
    
@begin bbox_of_index


  		




  134
  128
  
    
@param table_name


  		




  135
  129
  
    
@param geom_name


  		




  136
  
  
    
select min(minx) as minx, min(miny) as miny, max(maxx) as maxx,	max(maxy) as maxy from "rtree_%table_name$s_%geom_name$s"


  		




  
  130
  
    
select min(minx) as minx, min(miny) as miny, max(maxx) as maxx, max(maxy) as maxy, count(*) as rows from "rtree_%table_name$s_%geom_name$s"


  		




  137
  131
  
    
@end bbox_of_index


  		




  138
  132
  
    

  		




  139
  133
  
    
@begin has_index


  		












Also available in:  Unified diff