cps-ri/src/main/resources/changelog/db/changes/01-createCPSTables.yaml

   1 databaseChangeLog:
   2   - changeSet:
   3       id: 1-1
   4       author: cps
   5       changes:
   6         - createTable:
   7             columns:
   8               - column:
   9                   autoIncrement: true
  10                   constraints:
  11                     nullable: false
  12                     primaryKey: true
  13                     primaryKeyName: anchor_pkey
  14                   name: id
  15                   type: BIGINT
  16               - column:
  17                   name: name
  18                   type: TEXT
  19               - column:
  20                   name: schema_set_id
  21                   type: INTEGER
  22               - column:
  23                   constraints:
  24                     nullable: false
  25                   name: dataspace_id
  26                   type: INTEGER
  27             tableName: anchor
  28   - changeSet:
  29       id: 1-2
  30       author: cps
  31       changes:
  32         - createTable:
  33             columns:
  34               - column:
  35                   constraints:
  36                     nullable: false
  37                   name: from_fragment_id
  38                   type: BIGINT
  39               - column:
  40                   constraints:
  41                     nullable: false
  42                   name: to_fragment_id
  43                   type: BIGINT
  44               - column:
  45                   constraints:
  46                     nullable: false
  47                   name: relation_type_id
  48                   type: INTEGER
  49               - column:
  50                   constraints:
  51                     nullable: false
  52                   name: from_rel_xpath
  53                   type: TEXT
  54               - column:
  55                   constraints:
  56                     nullable: false
  57                   name: to_rel_xpath
  58                   type: TEXT
  59             tableName: relation
  60   - changeSet:
  61       id: 1-3
  62       author: cps
  63       changes:
  64         - createTable:
  65             columns:
  66               - column:
  67                   constraints:
  68                     nullable: false
  69                   name: relation_type
  70                   type: TEXT
  71               - column:
  72                   autoIncrement: true
  73                   constraints:
  74                     nullable: false
  75                     primaryKey: true
  76                     primaryKeyName: relation_type_pkey
  77                   name: id
  78                   type: INTEGER
  79             tableName: relation_type
  80   - changeSet:
  81       id: 1-4
  82       author: cps
  83       changes:
  84         - createTable:
  85             columns:
  86               - column:
  87                   autoIncrement: true
  88                   constraints:
  89                     nullable: false
  90                     primaryKey: true
  91                     primaryKeyName: fragment_pkey
  92                   name: id
  93                   type: BIGINT
  94               - column:
  95                   constraints:
  96                     nullable: false
  97                   name: xpath
  98                   type: TEXT
  99               - column:
 100                   name: attributes
 101                   type: JSONB
 102               - column:
 103                   name: anchor_id
 104                   type: BIGINT
 105               - column:
 106                   name: parent_id
 107                   type: BIGINT
 108               - column:
 109                   constraints:
 110                     nullable: false
 111                   name: dataspace_id
 112                   type: INTEGER
 113               - column:
 114                   name: schema_node_id
 115                   type: INTEGER
 116             tableName: fragment
 117   - changeSet:
 118       id: 1-5
 119       author: cps
 120       changes:
 121         - createTable:
 122             columns:
 123               - column:
 124                   autoIncrement: true
 125                   constraints:
 126                     nullable: false
 127                     primaryKey: true
 128                     primaryKeyName: schema_set_pkey
 129                   name: id
 130                   type: INTEGER
 131               - column:
 132                   constraints:
 133                     nullable: false
 134                   name: name
 135                   type: TEXT
 136               - column:
 137                   constraints:
 138                     nullable: false
 139                   name: dataspace_id
 140                   type: BIGINT
 141             tableName: schema_set
 142   - changeSet:
 143       id: 1-6
 144       author: cps
 145       changes:
 146         - createTable:
 147             columns:
 148               - column:
 149                   autoIncrement: true
 150                   constraints:
 151                     nullable: false
 152                     primaryKey: true
 153                     primaryKeyName: yang_resource_pkey
 154                   name: id
 155                   type: INTEGER
 156               - column:
 157                   constraints:
 158                     nullable: false
 159                   name: name
 160                   type: TEXT
 161               - column:
 162                   constraints:
 163                     nullable: false
 164                   name: content
 165                   type: TEXT
 166               - column:
 167                   constraints:
 168                     nullable: false
 169                   name: checksum
 170                   type: TEXT
 171             tableName: yang_resource
 172   - changeSet:
 173       id: 1-7
 174       author: cps
 175       changes:
 176         - createTable:
 177             columns:
 178               - column:
 179                   autoIncrement: true
 180                   constraints:
 181                     nullable: false
 182                     primaryKey: true
 183                     primaryKeyName: dataspace_pkey
 184                   name: id
 185                   type: INTEGER
 186               - column:
 187                   constraints:
 188                     nullable: false
 189                   name: name
 190                   type: TEXT
 191             tableName: dataspace
 192   - changeSet:
 193       id: 1-8
 194       author: cps
 195       changes:
 196         - createTable:
 197             columns:
 198               - column:
 199                   constraints:
 200                     nullable: false
 201                   name: schema_node_identifier
 202                   type: TEXT
 203               - column:
 204                   autoIncrement: true
 205                   constraints:
 206                     nullable: false
 207                     primaryKey: true
 208                     primaryKeyName: schema_node_pkey
 209                   name: id
 210                   type: INTEGER
 211             tableName: schema_node
 212   - changeSet:
 213       id: 1-9
 214       author: cps
 215       changes:
 216         - createTable:
 217             columns:
 218               - column:
 219                   constraints:
 220                     nullable: false
 221                   name: schema_set_id
 222                   type: BIGINT
 223               - column:
 224                   constraints:
 225                     nullable: false
 226                   name: yang_resource_id
 227                   type: BIGINT
 228             tableName: schema_set_yang_resources
 229   - changeSet:
 230       id: 1-10
 231       author: cps
 232       changes:
 233         - createIndex:
 234             columns:
 235               - column:
 236                   name: schema_set_id
 237             indexName: FKI_ANCHOR_SCHEMA_SET_ID_FK
 238             tableName: anchor
 239   - changeSet:
 240       id: 1-11
 241       author: cps
 242       changes:
 243         - addUniqueConstraint:
 244             columnNames: dataspace_id, name
 245             constraintName: anchor_dataspace_id_name_key
 246             tableName: anchor
 247   - changeSet:
 248       id: 1-12
 249       author: cps
 250       changes:
 251         - addForeignKeyConstraint:
 252             baseColumnNames: anchor_id
 253             baseTableName: fragment
 254             constraintName: fragment_anchor_id_fkey
 255             deferrable: false
 256             initiallyDeferred: false
 257             onDelete: NO ACTION
 258             onUpdate: NO ACTION
 259             referencedColumnNames: id
 260             referencedTableName: anchor
 261             validate: true
 262   - changeSet:
 263       id: 1-13
 264       author: cps
 265       changes:
 266         - createIndex:
 267             columns:
 268               - column:
 269                   name: from_fragment_id
 270             indexName: FKI_RELATIONS_FROM_ID_FK
 271             tableName: relation
 272   - changeSet:
 273       id: 1-14
 274       author: cps
 275       changes:
 276         - createIndex:
 277             columns:
 278               - column:
 279                   name: to_fragment_id
 280             indexName: FKI_RELATIONS_TO_ID_FK
 281             tableName: relation
 282   - changeSet:
 283       id: 1-15
 284       author: cps
 285       changes:
 286         - createIndex:
 287             columns:
 288               - column:
 289                   name: relation_type_id
 290             indexName: FKI_RELATION_TYPE_ID_FK
 291             tableName: relation
 292   - changeSet:
 293       id: 1-16
 294       author: cps
 295       changes:
 296         - addPrimaryKey:
 297             columnNames: to_fragment_id, from_fragment_id, relation_type_id
 298             constraintName: relation_pkey
 299             tableName: relation
 300   - changeSet:
 301       id: 1-17
 302       author: cps
 303       changes:
 304         - createIndex:
 305             columns:
 306               - column:
 307                   name: anchor_id
 308             indexName: FKI_FRAGMENT_ANCHOR_ID_FK
 309             tableName: fragment
 310   - changeSet:
 311       id: 1-18
 312       author: cps
 313       changes:
 314         - createIndex:
 315             columns:
 316               - column:
 317                   name: dataspace_id
 318             indexName: FKI_FRAGMENT_DATASPACE_ID_FK
 319             tableName: fragment
 320   - changeSet:
 321       id: 1-19
 322       author: cps
 323       changes:
 324         - createIndex:
 325             columns:
 326               - column:
 327                   name: parent_id
 328             indexName: FKI_FRAGMENT_PARENT_ID_FK
 329             tableName: fragment
 330   - changeSet:
 331       id: 1-20
 332       author: cps
 333       changes:
 334         - createIndex:
 335             columns:
 336               - column:
 337                   name: schema_node_id
 338             indexName: FKI_SCHEMA_NODE_ID_TO_ID
 339             tableName: fragment
 340   - changeSet:
 341       id: 1-21
 342       author: cps
 343       changes:
 344         - createIndex:
 345             columns:
 346               - column:
 347                   name: xpath
 348               - column:
 349                   name: dataspace_id
 350             indexName: UQ_FRAGMENT_XPATH
 351             tableName: fragment
 352             unique: true
 353   - changeSet:
 354       id: 1-22
 355       author: cps
 356       changes:
 357         - addUniqueConstraint:
 358             columnNames: dataspace_id, anchor_id, xpath
 359             constraintName: fragment_dataspace_id_anchor_id_xpath_key
 360             tableName: fragment
 361   - changeSet:
 362       id: 1-23
 363       author: cps
 364       changes:
 365         - addForeignKeyConstraint:
 366             baseColumnNames: from_fragment_id
 367             baseTableName: relation
 368             constraintName: relation_from_fragment_id_fkey
 369             deferrable: false
 370             initiallyDeferred: false
 371             onDelete: NO ACTION
 372             onUpdate: NO ACTION
 373             referencedColumnNames: id
 374             referencedTableName: fragment
 375             validate: true
 376   - changeSet:
 377       id: 1-24
 378       author: cps
 379       changes:
 380         - addForeignKeyConstraint:
 381             baseColumnNames: to_fragment_id
 382             baseTableName: relation
 383             constraintName: relation_to_fragment_id_fkey
 384             deferrable: false
 385             initiallyDeferred: false
 386             onDelete: NO ACTION
 387             onUpdate: NO ACTION
 388             referencedColumnNames: id
 389             referencedTableName: fragment
 390             validate: true
 391   - changeSet:
 392       id: 1-25
 393       author: cps
 394       changes:
 395         - addUniqueConstraint:
 396             columnNames: name, dataspace_id
 397             constraintName: schema_set_name_dataspace_id_key
 398             tableName: schema_set
 399   - changeSet:
 400       id: 1-26
 401       author: cps
 402       changes:
 403         - addForeignKeyConstraint:
 404             baseColumnNames: schema_set_id
 405             baseTableName: schema_set_yang_resources
 406             constraintName: schema_set_resource
 407             deferrable: false
 408             initiallyDeferred: false
 409             onDelete: CASCADE
 410             onUpdate: NO ACTION
 411             referencedColumnNames: id
 412             referencedTableName: schema_set
 413             validate: true
 414   - changeSet:
 415       id: 1-27
 416       author: cps
 417       changes:
 418         - addUniqueConstraint:
 419             columnNames: checksum
 420             constraintName: yang_resource_checksum_key
 421             tableName: yang_resource
 422   - changeSet:
 423       id: 1-28
 424       author: cps
 425       changes:
 426         - addUniqueConstraint:
 427             columnNames: name
 428             constraintName: UQ_NAME
 429             tableName: dataspace
 430   - changeSet:
 431       id: 1-29
 432       author: cps
 433       changes:
 434         - addForeignKeyConstraint:
 435             baseColumnNames: dataspace_id
 436             baseTableName: fragment
 437             constraintName: fragment_dataspace_id_fkey
 438             deferrable: false
 439             initiallyDeferred: false
 440             onDelete: NO ACTION
 441             onUpdate: NO ACTION
 442             referencedColumnNames: id
 443             referencedTableName: dataspace
 444             validate: true
 445   - changeSet:
 446       id: 1-30
 447       author: cps
 448       changes:
 449         - addForeignKeyConstraint:
 450             baseColumnNames: dataspace_id
 451             baseTableName: schema_set
 452             constraintName: schema_set_dataspace
 453             deferrable: false
 454             initiallyDeferred: false
 455             onDelete: CASCADE
 456             onUpdate: CASCADE
 457             referencedColumnNames: id
 458             referencedTableName: dataspace
 459             validate: true
 460   - changeSet:
 461       id: 1-31
 462       author: cps
 463       changes:
 464         - createIndex:
 465             columns:
 466               - column:
 467                   name: schema_node_identifier
 468             indexName: PERF_SCHEMA_NODE_SCHEMA_NODE_ID
 469             tableName: schema_node
 470   - changeSet:
 471       id: 1-32
 472       author: cps
 473       changes:
 474         - addForeignKeyConstraint:
 475             baseColumnNames: yang_resource_id
 476             baseTableName: schema_set_yang_resources
 477             constraintName: schema_set_yang_resources_yang_resource_id_fkey
 478             deferrable: false
 479             initiallyDeferred: false
 480             onDelete: NO ACTION
 481             onUpdate: NO ACTION
 482             referencedColumnNames: id
 483             referencedTableName: yang_resource
 484             validate: true
 485   - changeSet:
 486       id: 1-33
 487       author: cps
 488       changes:
 489         - addForeignKeyConstraint:
 490             baseColumnNames: dataspace_id
 491             baseTableName: anchor
 492             constraintName: anchor_dataspace_id_fkey
 493             deferrable: false
 494             initiallyDeferred: false
 495             onDelete: NO ACTION
 496             onUpdate: NO ACTION
 497             referencedColumnNames: id
 498             referencedTableName: dataspace
 499             validate: true
 500   - changeSet:
 501       id: 1-34
 502       author: cps
 503       changes:
 504         - addForeignKeyConstraint:
 505             baseColumnNames: schema_set_id
 506             baseTableName: anchor
 507             constraintName: anchor_schema_set_id_fkey
 508             deferrable: false
 509             initiallyDeferred: false
 510             onDelete: NO ACTION
 511             onUpdate: NO ACTION
 512             referencedColumnNames: id
 513             referencedTableName: schema_set
 514             validate: true
 515   - changeSet:
 516       id: 1-35
 517       author: cps
 518       changes:
 519         - addForeignKeyConstraint:
 520             baseColumnNames: relation_type_id
 521             baseTableName: relation
 522             constraintName: relation_relation_type_id_fkey
 523             deferrable: false
 524             initiallyDeferred: false
 525             onDelete: NO ACTION
 526             onUpdate: NO ACTION
 527             referencedColumnNames: id
 528             referencedTableName: relation_type
 529             validate: true
 530   - changeSet:
 531       id: 1-36
 532       author: cps
 533       changes:
 534         - addForeignKeyConstraint:
 535             baseColumnNames: parent_id
 536             baseTableName: fragment
 537             constraintName: fragment_parent_id_fkey
 538             deferrable: false
 539             initiallyDeferred: false
 540             onDelete: NO ACTION
 541             onUpdate: NO ACTION
 542             referencedColumnNames: id
 543             referencedTableName: fragment
 544             validate: true
 545   - changeSet:
 546       id: 1-37
 547       author: cps
 548       changes:
 549         - addForeignKeyConstraint:
 550             baseColumnNames: schema_node_id
 551             baseTableName: fragment
 552             constraintName: fragment_schema_node_id_fkey
 553             deferrable: false
 554             initiallyDeferred: false
 555             onDelete: NO ACTION
 556             onUpdate: NO ACTION
 557             referencedColumnNames: id
 558             referencedTableName: schema_node
 559             validate: true
 560