Project

General

Profile

« Previous | Next » 

Revision 11210

Added by Aaron Marcuse-Kubitza about 11 years ago

schemas/VegCore/ERD/VegCore.ERD.mwb: event: added unique constraints for every way that the scoping fields can meaningfully be combined. these correspond to the various unique constraints offered by VegBIEN.locationevent. (the event table's constraints are always tricky because there are many ways to define an event. it is important to include all of them so that there is almost always a way to generate a natural key, without resorting to an artificial key from traceable.id_by_source.)

View differences:

schemas/VegCore/ERD/document.mwb.xml
5358 5358 
                      <value type="int" key="deferability">0</value>
5359 5359 
                      <value type="string" key="indexType">UNIQUE</value>
5360 5360 
                      <value type="int" key="isPrimary">0</value>
5361 
                      <value type="string" key="name">event_unique_within_parent</value>
5361 
                      <value type="string" key="name">event_unique_within_parent_by_name</value>
5362 5362 
                      <value type="int" key="unique">1</value>
5363 5363 
                      <value type="string" key="oldName">event_unique_within_parent</value>
5364 5364 
                      <link type="object" struct-name="GrtObject" key="owner">c672743a-a7a0-11e2-b667-080027bcf912</link>
......
5397 5397 
                      <value type="int" key="deferability">0</value>
5398 5398 
                      <value type="string" key="indexType">UNIQUE</value>
5399 5399 
                      <value type="int" key="isPrimary">0</value>
5400 
                      <value type="string" key="name">event_unique_within_subject</value>
5400 
                      <value type="string" key="name">event_unique_within_subject_by_date</value>
5401 5401 
                      <value type="int" key="unique">1</value>
5402 5402 
                      <value type="string" key="oldName">event_unique_within_subject</value>
5403 5403 
                      <link type="object" struct-name="GrtObject" key="owner">c672743a-a7a0-11e2-b667-080027bcf912</link>
......
5425 5425 
                      <value type="string" key="oldName">fk_event_place1_idx</value>
5426 5426 
                      <link type="object" struct-name="GrtObject" key="owner">c672743a-a7a0-11e2-b667-080027bcf912</link>
5427 5427 
                    </value>
5428 
                    <value type="object" struct-name="db.mysql.Index" id="434ef6ca-3132-11e3-8c52-080027bcf912" struct-checksum="0x309b847a">
5429 
                      <value _ptr_="0xd2f6f08" type="list" content-type="object" content-struct-name="db.mysql.IndexColumn" key="columns">
5430 
                        <value type="object" struct-name="db.mysql.IndexColumn" id="4afa80c4-3132-11e3-8c52-080027bcf912" struct-checksum="0x62630b3c">
5431 
                          <value type="int" key="columnLength">0</value>
5432 
                          <value type="string" key="comment"></value>
5433 
                          <value type="int" key="descend">0</value>
5434 
                          <link type="object" struct-name="db.Column" key="referencedColumn">c672785e-a7a0-11e2-b667-080027bcf912</link>
5435 
                          <value type="string" key="name"></value>
5436 
                          <link type="object" struct-name="GrtObject" key="owner">434ef6ca-3132-11e3-8c52-080027bcf912</link>
5437 
                        </value>
5438 
                        <value type="object" struct-name="db.mysql.IndexColumn" id="4b64955e-3132-11e3-8c52-080027bcf912" struct-checksum="0x62630b3c">
5439 
                          <value type="int" key="columnLength">0</value>
5440 
                          <value type="string" key="comment"></value>
5441 
                          <value type="int" key="descend">0</value>
5442 
                          <link type="object" struct-name="db.Column" key="referencedColumn">c6727d54-a7a0-11e2-b667-080027bcf912</link>
5443 
                          <value type="string" key="name"></value>
5444 
                          <link type="object" struct-name="GrtObject" key="owner">434ef6ca-3132-11e3-8c52-080027bcf912</link>
5445 
                        </value>
5446 
                      </value>
5447 
                      <value type="string" key="indexKind"></value>
5448 
                      <value type="int" key="keyBlockSize">0</value>
5449 
                      <value type="string" key="withParser"></value>
5450 
                      <value type="string" key="comment"></value>
5451 
                      <value type="int" key="deferability">0</value>
5452 
                      <value type="string" key="indexType">UNIQUE</value>
5453 
                      <value type="int" key="isPrimary">0</value>
5454 
                      <value type="string" key="name">event_unique_within_parent_by_subject</value>
5455 
                      <value type="int" key="unique">1</value>
5456 
                      <value type="string" key="oldName"></value>
5457 
                      <link type="object" struct-name="GrtObject" key="owner">c672743a-a7a0-11e2-b667-080027bcf912</link>
5458 
                    </value>
5459 
                    <value type="object" struct-name="db.mysql.Index" id="5ac683ea-3132-11e3-8c52-080027bcf912" struct-checksum="0x309b847a">
5460 
                      <value _ptr_="0xd030f78" type="list" content-type="object" content-struct-name="db.mysql.IndexColumn" key="columns">
5461 
                        <value type="object" struct-name="db.mysql.IndexColumn" id="7849a528-3132-11e3-8c52-080027bcf912" struct-checksum="0x62630b3c">
5462 
                          <value type="int" key="columnLength">0</value>
5463 
                          <value type="string" key="comment"></value>
5464 
                          <value type="int" key="descend">0</value>
5465 
                          <link type="object" struct-name="db.Column" key="referencedColumn">c6727d54-a7a0-11e2-b667-080027bcf912</link>
5466 
                          <value type="string" key="name"></value>
5467 
                          <link type="object" struct-name="GrtObject" key="owner">5ac683ea-3132-11e3-8c52-080027bcf912</link>
5468 
                        </value>
5469 
                        <value type="object" struct-name="db.mysql.IndexColumn" id="78aa989c-3132-11e3-8c52-080027bcf912" struct-checksum="0x62630b3c">
5470 
                          <value type="int" key="columnLength">0</value>
5471 
                          <value type="string" key="comment"></value>
5472 
                          <value type="int" key="descend">0</value>
5473 
                          <link type="object" struct-name="db.Column" key="referencedColumn">c6727a2a-a7a0-11e2-b667-080027bcf912</link>
5474 
                          <value type="string" key="name"></value>
5475 
                          <link type="object" struct-name="GrtObject" key="owner">5ac683ea-3132-11e3-8c52-080027bcf912</link>
5476 
                        </value>
5477 
                      </value>
5478 
                      <value type="string" key="indexKind"></value>
5479 
                      <value type="int" key="keyBlockSize">0</value>
5480 
                      <value type="string" key="withParser"></value>
5481 
                      <value type="string" key="comment"></value>
5482 
                      <value type="int" key="deferability">0</value>
5483 
                      <value type="string" key="indexType">INDEX</value>
5484 
                      <value type="int" key="isPrimary">0</value>
5485 
                      <value type="string" key="name">event_unique_within_subject_by_name</value>
5486 
                      <value type="int" key="unique">0</value>
5487 
                      <value type="string" key="oldName"></value>
5488 
                      <link type="object" struct-name="GrtObject" key="owner">c672743a-a7a0-11e2-b667-080027bcf912</link>
5489 
                    </value>
5428 5490 
                  </value>
5429 5491 
                  <value type="string" key="maxRows"></value>
5430 5492 
                  <value type="string" key="mergeInsert"></value>
......
5456 5518 
                  <value type="int" key="commentedOut">0</value>
5457 5519 
                  <value type="string" key="createDate">2013-04-17 13:52</value>
5458 5520 
                  <value _ptr_="0xbec9400" type="dict" key="customData"/>
5459 
                  <value type="string" key="lastChangeDate">2013-10-09 15:27</value>
5521 
                  <value type="string" key="lastChangeDate">2013-10-09 15:30</value>
5460 5522 
                  <value type="int" key="modelOnly">0</value>
5461 5523 
                  <value type="string" key="name">event</value>
5462 5524 
                  <link type="object" struct-name="GrtNamedObject" key="owner">be8cb246-a6e0-11e2-95e9-080027bcf912</link>
......
17897 17959 
        <link type="object" struct-name="db.mgmt.Rdbms" key="rdbms">com.mysql.rdbms.mysql</link>
17898 17960 
        <value _ptr_="0xbe91d50" type="list" content-type="object" content-struct-name="db.Script" key="scripts"/>
17899 17961 
        <value _ptr_="0xaefefb0" type="dict" key="syncProfiles">
17900 
          <value type="object" struct-name="db.mgmt.SyncProfile" id="5a8d5ac6-3131-11e3-8c52-080027bcf912" struct-checksum="0xc64972c2" key="">
17901 
            <value _ptr_="0xd25d848" type="dict" key="lastKnownDBNames">
17962 
          <value type="object" struct-name="db.mgmt.SyncProfile" id="045d9d9a-3132-11e3-8c52-080027bcf912" struct-checksum="0xc64972c2" key="">
17963 
            <value _ptr_="0xd08cda0" type="dict" key="lastKnownDBNames">
17902 17964 
              <value type="string" key="016697c0-23ec-11e3-ab7c-080027bcf912">name</value>
17903 17965 
              <value type="string" key="01b2e3fe-f6ed-11e2-9cf1-080027bcf912">fk_georeferencing_party_list1</value>
17904 17966 
              <value type="string" key="01b2e606-f6ed-11e2-9cf1-080027bcf912">georeferenced_by</value>
......
18537 18599 
    <value type="object" struct-name="app.DocumentInfo" id="BEE3982F-55A1-4B2C-914A-CD6953803AB9" struct-checksum="0xbba780b8" key="info">
18538 18600 
      <value type="string" key="author">Aaron Marcuse-Kubitza</value>
18539 18601 
      <value type="string" key="caption">New Model</value>
18540 
      <value type="string" key="dateChanged">2013-10-09 15:27</value>
18602 
      <value type="string" key="dateChanged">2013-10-09 15:31</value>
18541 18603 
      <value type="string" key="dateCreated">2012-01-19 14:06</value>
18542 18604 
      <value type="string" key="description"></value>
18543 18605 
      <value type="string" key="project">Name of the project</value>
schemas/VegCore/VegCore.my.sql
209 209 
  `date_range` varbinary(767) DEFAULT NULL,
210 210 
  `participants` varbinary(767) DEFAULT NULL,
211 211 
  PRIMARY KEY (`id`),
212 
  UNIQUE KEY `event_unique_within_parent` (`parent`,`name`),
213 
  UNIQUE KEY `event_unique_within_subject` (`subject`,`date_range`,`participants`),
212 
  UNIQUE KEY `event_unique_within_parent_by_name` (`parent`,`name`),
213 
  UNIQUE KEY `event_unique_within_subject_by_date` (`subject`,`date_range`,`participants`),
214 
  UNIQUE KEY `event_unique_within_parent_by_subject` (`parent`,`subject`),
214 215 
  KEY `fk_event1_idx` (`parent`),
215 216 
  KEY `fk_event_party_list1_idx` (`participants`),
216 217 
  KEY `fk_event_place1_idx` (`subject`),
218 
  KEY `event_unique_within_subject_by_name` (`subject`,`name`),
217 219 
  CONSTRAINT `fk_event_party_list1` FOREIGN KEY (`participants`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
218 220 
  CONSTRAINT `fk_event1` FOREIGN KEY (`parent`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
219 221 
  CONSTRAINT `fk_event_place1` FOREIGN KEY (`subject`) REFERENCES `traceable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
schemas/VegCore/VegCore.pg.sql
211 211 
  "date_range" text DEFAULT NULL,
212 212 
  "participants" text DEFAULT NULL,
213 213 
  PRIMARY KEY ("id"),
214 
  /*CONSTRAINT "event_unique_within_parent" */UNIQUE ("parent","name"),
215 
  /*CONSTRAINT "event_unique_within_subject" */UNIQUE ("subject","date_range","participants"),
214 
  /*CONSTRAINT "event_unique_within_parent_by_name" */UNIQUE ("parent","name"),
215 
  /*CONSTRAINT "event_unique_within_subject_by_date" */UNIQUE ("subject","date_range","participants"),
216 
  /*CONSTRAINT "event_unique_within_parent_by_subject" */UNIQUE ("parent","subject"),
216 217 
  /*KEY "fk_event1_idx" ("parent")*/CHECK (true),
217 218 
  /*KEY "fk_event_party_list1_idx" ("participants")*/CHECK (true),
218 219 
  /*KEY "fk_event_place1_idx" ("subject")*/CHECK (true),
220 
  /*KEY "event_unique_within_subject_by_name" ("subject","name")*/CHECK (true),
219 221 
  /*CONSTRAINT "fk_event_party_list1" FOREIGN KEY ("participants") REFERENCES "party_list" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
220 222 
  /*CONSTRAINT "fk_event1" FOREIGN KEY ("parent") REFERENCES "event" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
221 223 
  /*CONSTRAINT "fk_event_place1" FOREIGN KEY ("subject") REFERENCES "traceable" ("id") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),

Also available in: Unified diff