Revision 47606 trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/src/test/resources/org/gvsig/sqlite/dal/performChanges.sql

View differences:

performChanges.sql
1 
-- normalize-spaces false
2 
-- strip-start false
3 
-- remove-nl false
1 4 

  		




  2
  
  
    
-- Insert SQL


  		




  3
  
  
    
INSERT INTO "test" ( 


  		




  4
  
  
    
    "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", 


  		




  5
  
  
    
    "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", 


  		




  6
  
  
    
    "Decimal", "Geometry" 


  		




  7
  
  
    
  ) 


  		




  8
  
  
    
  VALUES ( 


  		




  9
  
  
    
     ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? 


  		




  
  5
  
    
-- begin performChanges


  		




  
  6
  
    
INSERT INTO "test" ( "ID", "Byte", "Bool1", "Long", "Timestamp", "Date", "Time", "Bool2", "String", "Bool3", "Double", "Bool4", "Float", "Bool5", "Decimal", "Geometry" ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )


  		




  
  7
  
    
-- end performChanges


  		




  
  8
  
    

  		




  
  9
  
    
-- begin performChanges


  		




  
  10
  
    
DELETE FROM "test" WHERE ( ("ID") = (?) )


  		




  
  11
  
    
-- end performChanges


  		




  
  12
  
    

  		




  
  13
  
    
-- begin performChanges


  		




  
  14
  
    
UPDATE "test" SET "Byte" = ?, "Bool1" = ?, "Long" = ?, "Timestamp" = ?, "Date" = ?, "Time" = ?, "Bool2" = ?, "String" = ?, "Bool3" = ?, "Double" = ?, "Bool4" = ?, "Float" = ?, "Bool5" = ?, "Decimal" = ?, "Geometry" = ? WHERE ( ("ID") = (?) )


  		




  
  15
  
    
-- end performChanges


  		




  
  16
  
    

  		




  
  17
  
    
-- begin performChanges


  		




  
  18
  
    
ALTER TABLE "test" ADD COLUMN "Byte" TINYINT DEFAULT NULL NULL


  		




  
  19
  
    
-- end performChanges


  		




  
  20
  
    

  		




  
  21
  
    
-- begin performChanges


  		




  
  22
  
    
ALTER TABLE "test" ADD COLUMN "Bool1" BOOLEAN DEFAULT NULL NULL


  		




  
  23
  
    
-- end performChanges


  		




  
  24
  
    

  		




  
  25
  
    
-- begin performChanges


  		




  
  26
  
    
ALTER TABLE "test" ADD COLUMN "Long" BIGINT DEFAULT NULL NULL


  		




  
  27
  
    
-- end performChanges


  		




  
  28
  
    

  		




  
  29
  
    
-- begin performChanges


  		




  
  30
  
    
ALTER TABLE "test" ADD COLUMN "Timestamp" DATETIME DEFAULT NULL NULL


  		




  
  31
  
    
-- end performChanges


  		




  
  32
  
    

  		




  
  33
  
    
-- begin performChanges


  		




  
  34
  
    
ALTER TABLE "test" ADD COLUMN "Date" DATE DEFAULT NULL NULL


  		




  
  35
  
    
-- end performChanges


  		




  
  36
  
    

  		




  
  37
  
    
-- begin performChanges


  		




  
  38
  
    
ALTER TABLE "test" ADD COLUMN "Time" TIME DEFAULT NULL NULL


  		




  
  39
  
    
-- end performChanges


  		




  
  40
  
    

  		




  
  41
  
    
-- begin performChanges


  		




  
  42
  
    
ALTER TABLE "test" ADD COLUMN "Bool2" BOOLEAN DEFAULT NULL NULL


  		




  
  43
  
    
-- end performChanges


  		




  
  44
  
    

  		




  
  45
  
    
-- begin performChanges


  		




  
  46
  
    
ALTER TABLE "test" ADD COLUMN "String" VARCHAR(30) DEFAULT NULL NULL


  		




  
  47
  
    
-- end performChanges


  		




  
  48
  
    

  		




  
  49
  
    
-- begin performChanges


  		




  
  50
  
    
ALTER TABLE "test" ADD COLUMN "Bool3" BOOLEAN DEFAULT NULL NULL


  		




  
  51
  
    
-- end performChanges


  		




  
  52
  
    

  		




  
  53
  
    
-- begin performChanges


  		




  
  54
  
    
ALTER TABLE "test" ADD COLUMN "Double" DOUBLE PRECISION DEFAULT NULL NULL


  		




  
  55
  
    
-- end performChanges


  		




  
  56
  
    

  		




  
  57
  
    
-- begin performChanges


  		




  
  58
  
    
ALTER TABLE "test" ADD COLUMN "Bool4" BOOLEAN DEFAULT NULL NULL


  		




  
  59
  
    
-- end performChanges


  		




  
  60
  
    

  		




  
  61
  
    
-- begin performChanges


  		




  
  62
  
    
ALTER TABLE "test" ADD COLUMN "Float" FLOAT DEFAULT NULL NULL


  		




  
  63
  
    
-- end performChanges


  		




  
  64
  
    

  		




  
  65
  
    
-- begin performChanges


  		




  
  66
  
    
ALTER TABLE "test" ADD COLUMN "Bool5" BOOLEAN DEFAULT NULL NULL


  		




  
  67
  
    
-- end performChanges


  		




  
  68
  
    

  		




  
  69
  
    
-- begin performChanges


  		




  
  70
  
    
ALTER TABLE "test" ADD COLUMN "Decimal" DECIMAL(6,3) DEFAULT NULL NULL


  		




  
  71
  
    
-- end performChanges


  		




  
  72
  
    

  		




  
  73
  
    
-- begin performChanges


  		




  
  74
  
    
ALTER TABLE "test" ADD COLUMN "Geometry" BLOB DEFAULT NULL NULL


  		




  
  75
  
    
-- end performChanges


  		




  
  76
  
    

  		




  
  77
  
    
-- begin performChanges


  		




  
  78
  
    
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) )


  		




  
  79
  
    
-- end performChanges


  		




  
  80
  
    

  		




  
  81
  
    
-- begin performChanges


  		




  
  82
  
    
CREATE VIRTUAL TABLE "rtree_test_Geometry" USING rtree(id, minx, maxx, miny, maxy)


  		




  
  83
  
    
-- end performChanges


  		




  
  84
  
    

  		




  
  85
  
    
-- begin performChanges


  		




  
  86
  
    
CREATE TRIGGER "rtree_test_Geometry_delete" AFTER DELETE ON "test"


  		




  
  87
  
    
  WHEN old."Geometry" NOT NULL


  		




  
  88
  
    
BEGIN


  		




  
  89
  
    
  DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID";


  		




  
  90
  
    
END


  		




  
  91
  
    
-- end performChanges


  		




  
  92
  
    

  		




  
  93
  
    
-- begin performChanges


  		




  
  94
  
    
CREATE TRIGGER "rtree_test_Geometry_insert" AFTER INSERT ON "test"


  		




  
  95
  
    
  WHEN (new."Geometry" NOT NULL AND NOT ST_IsEmpty(NEW."Geometry"))


  		




  
  96
  
    
BEGIN


  		




  
  97
  
    
  INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES (


  		




  
  98
  
    
    NEW."ID",


  		




  
  99
  
    
    ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"),


  		




  
  100
  
    
    ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry")


  		




  10
  101
  
    
  );


  		




  
  102
  
    
END


  		




  
  103
  
    
-- end performChanges


  		




  11
  104
  
    

  		




  12
  
  
    
-- Delete SQL


  		




  13
  
  
    
DELETE FROM "test" WHERE ( ("ID") = (?) );


  		




  
  105
  
    
-- begin performChanges


  		




  
  106
  
    
CREATE TRIGGER "rtree_test_Geometry_update1" AFTER UPDATE OF "Geometry" ON "test"


  		




  
  107
  
    
  WHEN OLD."ID" = NEW."ID" AND


  		




  
  108
  
    
       (NEW."Geometry" NOTNULL AND NOT ST_IsEmpty(NEW."Geometry"))


  		




  
  109
  
    
BEGIN


  		




  
  110
  
    
  INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES (


  		




  
  111
  
    
    NEW."ID",


  		




  
  112
  
    
    ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"),


  		




  
  113
  
    
    ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry")


  		




  
  114
  
    
  );


  		




  
  115
  
    
END


  		




  
  116
  
    
-- end performChanges


  		




  14
  117
  
    

  		




  15
  
  
    
-- Update SQL


  		




  16
  
  
    
UPDATE "test" 


  		




  17
  
  
    
  SET 


  		




  18
  
  
    
    "Byte" = ?, 


  		




  19
  
  
    
    "Bool1" = ?, 


  		




  20
  
  
    
    "Long" = ?, 


  		




  21
  
  
    
    "Timestamp" = ?, 


  		




  22
  
  
    
    "Date" = ?, 


  		




  23
  
  
    
    "Time" = ?, 


  		




  24
  
  
    
    "Bool2" = ?, 


  		




  25
  
  
    
    "String" = ?, 


  		




  26
  
  
    
    "Bool3" = ?, 


  		




  27
  
  
    
    "Double" = ?, 


  		




  28
  
  
    
    "Bool4" = ?, 


  		




  29
  
  
    
    "Float" = ?, 


  		




  30
  
  
    
    "Bool5" = ?, 


  		




  31
  
  
    
    "Decimal" = ?, 


  		




  32
  
  
    
    "Geometry" = ? 


  		




  33
  
  
    
  WHERE 


  		




  34
  
  
    
    ( ("ID") = (?) );


  		




  
  118
  
    
-- begin performChanges


  		




  
  119
  
    
CREATE TRIGGER "rtree_test_Geometry_update2" AFTER UPDATE OF "Geometry" ON "test"


  		




  
  120
  
    
  WHEN OLD."ID" = NEW."ID" AND


  		




  
  121
  
    
       (NEW."Geometry" ISNULL OR ST_IsEmpty(NEW."Geometry"))


  		




  
  122
  
    
BEGIN


  		




  
  123
  
    
  DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID";


  		




  
  124
  
    
END


  		




  
  125
  
    
-- end performChanges


  		




  35
  126
  
    

  		




  36
  
  
    
-- Update Table SQL


  		




  37
  
  
    
ALTER TABLE "test" ADD COLUMN "Byte" TINYINT DEFAULT NULL NULL;


  		




  38
  
  
    
ALTER TABLE "test" ADD COLUMN "Bool1" BOOLEAN DEFAULT NULL NULL;


  		




  39
  
  
    
ALTER TABLE "test" ADD COLUMN "Long" BIGINT DEFAULT NULL NULL;


  		




  40
  
  
    
ALTER TABLE "test" ADD COLUMN "Timestamp" DATETIME DEFAULT NULL NULL;


  		




  41
  
  
    
ALTER TABLE "test" ADD COLUMN "Date" DATE DEFAULT NULL NULL;


  		




  42
  
  
    
ALTER TABLE "test" ADD COLUMN "Time" TIME DEFAULT NULL NULL;


  		




  43
  
  
    
ALTER TABLE "test" ADD COLUMN "Bool2" BOOLEAN DEFAULT NULL NULL;


  		




  44
  
  
    
ALTER TABLE "test" ADD COLUMN "String" VARCHAR(30) DEFAULT NULL NULL;


  		




  45
  
  
    
ALTER TABLE "test" ADD COLUMN "Bool3" BOOLEAN DEFAULT NULL NULL;


  		




  46
  
  
    
ALTER TABLE "test" ADD COLUMN "Double" DOUBLE PRECISION DEFAULT NULL NULL;


  		




  47
  
  
    
ALTER TABLE "test" ADD COLUMN "Bool4" BOOLEAN DEFAULT NULL NULL;


  		




  48
  
  
    
ALTER TABLE "test" ADD COLUMN "Float" FLOAT DEFAULT NULL NULL;


  		




  49
  
  
    
ALTER TABLE "test" ADD COLUMN "Bool5" BOOLEAN DEFAULT NULL NULL;


  		




  50
  
  
    
ALTER TABLE "test" ADD COLUMN "Decimal" DECIMAL(6,3) DEFAULT NULL NULL;


  		




  51
  
  
    
ALTER TABLE "test" ADD COLUMN "Geometry" BLOB DEFAULT NULL NULL;


  		




  52
  
  
    
--ALTER TABLE "test" ADD CONSTRAINT 


  		




  53
  
  
    
--  IF NOT EXISTS "test_GEOM_Geometry" 


  		




  54
  
  
    
--  CHECK NVL2(


  		




  55
  
  
    
--    "Geometry", 


  		




  56
  
  
    
--    ST_GeometryTypeCode("Geometry") = 1 AND 


  		




  57
  
  
    
--      ST_CoordDim("Geometry") = 2 AND 


  		




  58
  
  
    
--      ST_SRID("Geometry") = 4326, 


  		




  59
  
  
    
--    TRUE


  		




  60
  
  
    
--  )


  		




  
  127
  
    
-- begin performChanges


  		




  
  128
  
    
CREATE TRIGGER "rtree_test_Geometry_update3" AFTER UPDATE ON "test"


  		




  
  129
  
    
  WHEN OLD."ID" != NEW."ID" AND


  		




  
  130
  
    
       (NEW."Geometry" NOTNULL AND NOT ST_IsEmpty(NEW."Geometry"))


  		




  
  131
  
    
BEGIN


  		




  
  132
  
    
  DELETE FROM "rtree_test_Geometry" WHERE ID = OLD."ID";


  		




  
  133
  
    
  INSERT OR REPLACE INTO "rtree_test_Geometry" VALUES (


  		




  
  134
  
    
    NEW."ID",


  		




  
  135
  
    
    ST_MinX(NEW."Geometry"), ST_MaxX(NEW."Geometry"),


  		




  
  136
  
    
    ST_MinY(NEW."Geometry"), ST_MaxY(NEW."Geometry")


  		




  
  137
  
    
  );


  		




  
  138
  
    
END


  		




  
  139
  
    
-- end performChanges


  		




  61
  140
  
    

  		




  
  141
  
    
-- begin performChanges


  		




  
  142
  
    
CREATE TRIGGER "rtree_test_Geometry_update4" AFTER UPDATE ON "test"


  		




  
  143
  
    
  WHEN OLD."ID" != NEW."ID" AND


  		




  
  144
  
    
       (NEW."Geometry" ISNULL OR ST_IsEmpty(NEW."Geometry"))


  		




  
  145
  
    
BEGIN


  		




  
  146
  
    
  DELETE FROM "rtree_test_Geometry" WHERE ID IN (OLD."ID", NEW."ID");


  		




  
  147
  
    
END


  		




  
  148
  
    
-- end performChanges


  		




  62
  149
  
    

  		




  63
  
  
    

  		




  
  150
  
    
-- begin performChanges


  		




  
  151
  
    
INSERT INTO "gpkg_extensions" (


  		




  
  152
  
    
    "table_name", 


  		




  
  153
  
    
    "column_name", 


  		




  
  154
  
    
    "extension_name", 


  		




  
  155
  
    
    "definition", 


  		




  
  156
  
    
    "scope"


  		




  
  157
  
    
) VALUES (


  		




  
  158
  
    
    'test', 


  		




  
  159
  
    
    'Geometry', 


  		




  
  160
  
    
    'gpkg_rtree_index', 


  		




  
  161
  
    
    'http://www.geopackage.org/spec/#extension_rtree', 


  		




  
  162
  
    
    'read-write'


  		




  
  163
  
    
)


  		




  
  164
  
    
-- end performChanges


  		












Also available in:  Unified diff