TableDefinition: Expand foreign key shorthand syntax

Allow the onDelete and onUpdate options to be specified in the shorthand string.
nwoltman · Nov 7, 2017 · ccb3c53 · ccb3c53
1 parent 24badd7
commit ccb3c53
Show file tree

Hide file tree

Showing 4 changed files with 64 additions and 12 deletions.
diff --git a/README.md b/README.md
@@ -1118,22 +1118,41 @@ Note that spatial indexes may each only have 1 column and they may only be defin
 
 ### Foreign Keys
 
-Foreign keys are defined using the `foreignKeys` property, which is an object that maps column names to a reference table column. The reference table column can be specified with either an object or a string of the form `'<table name>.<column name>'`. If an object, the following properties may be set: `table` (required), `column` (required), `onDelete`, `onUpdate`.
+Foreign keys are defined using the `foreignKeys` property, which is an object that maps column names to a reference table column. The reference table column can be specified with either an object or a shorthand string.
+
+If an object, it should have the following properties:
+
++ `table` - The name of the reference table.
++ `column` - The name of the reference column in the reference table.
++ `onDelete` (optional) - One of: `RESTRICT`, `CASCADE`, `SET NULL`, `NO ACTION`, `SET DEFAULT`.
++ `onUpdate` (optional) - Same as `onDelete`.
+
+Alternatively, you can use a shorthand string that has the following form:
+
+```js
+'<table>.<column> [reference_option]' // `[reference_option]` is optional
+```
 
 **Example:**
+
 ```js
 {
   columns: {
     id: /* ... */,
+    uid: /* ... */,
     userID: /* ... */,
     thingOne: /* ... */,
     thingTwo: /* ... */,
   },
+
   foreignKeys: {
-    // String with shorthand reference
+    // String shorthand
     id: 'other_table.id', // shorthand for {table: 'other_table', column: 'id'}
 
-    // Object reference with ON DELETE and ON UPDATE attributes
+    uid: 'other_table.uid CASCADE',
+    // shorthand for {table: 'other_table', column: 'uid', onDelete: 'CASCADE', onUpdate: 'CASCADE'}
+
+    // Object reference with ON DELETE and ON UPDATE options
     userID: {
       table: 'user',
       column: 'id',
@@ -1157,6 +1176,7 @@ Keys required for the example above:
 {
   primaryKey: 'id',
   uniqueKeys: [
+    'uid',
     'userID',
   ],
   indexes: [

diff --git a/jsdoc2md/README.hbs b/jsdoc2md/README.hbs
@@ -232,22 +232,41 @@ Note that spatial indexes may each only have 1 column and they may only be defin
 
 ### Foreign Keys
 
-Foreign keys are defined using the `foreignKeys` property, which is an object that maps column names to a reference table column. The reference table column can be specified with either an object or a string of the form `'<table name>.<column name>'`. If an object, the following properties may be set: `table` (required), `column` (required), `onDelete`, `onUpdate`.
+Foreign keys are defined using the `foreignKeys` property, which is an object that maps column names to a reference table column. The reference table column can be specified with either an object or a shorthand string.
+
+If an object, it should have the following properties:
+
++ `table` - The name of the reference table.
++ `column` - The name of the reference column in the reference table.
++ `onDelete` (optional) - One of: `RESTRICT`, `CASCADE`, `SET NULL`, `NO ACTION`, `SET DEFAULT`.
++ `onUpdate` (optional) - Same as `onDelete`.
+
+Alternatively, you can use a shorthand string that has the following form:
+
+```js
+'<table>.<column> [reference_option]' // `[reference_option]` is optional
+```
 
 **Example:**
+
 ```js
 {
   columns: {
     id: /* ... */,
+    uid: /* ... */,
     userID: /* ... */,
     thingOne: /* ... */,
     thingTwo: /* ... */,
   },
+
   foreignKeys: {
-    // String with shorthand reference
+    // String shorthand
     id: 'other_table.id', // shorthand for {table: 'other_table', column: 'id'}
 
-    // Object reference with ON DELETE and ON UPDATE attributes
+    uid: 'other_table.uid CASCADE',
+    // shorthand for {table: 'other_table', column: 'uid', onDelete: 'CASCADE', onUpdate: 'CASCADE'}
+
+    // Object reference with ON DELETE and ON UPDATE options
     userID: {
       table: 'user',
       column: 'id',
@@ -271,6 +290,7 @@ Keys required for the example above:
 {
   primaryKey: 'id',
   uniqueKeys: [
+    'uid',
     'userID',
   ],
   indexes: [

diff --git a/lib/TableDefinition.js b/lib/TableDefinition.js
@@ -553,15 +553,21 @@ function getNormalizedForeignKeys(foreignKeys) {
 
   for (const keyColumns in foreignKeys) {
     const foreignKeyData = foreignKeys[keyColumns];
-    const normalizedKeyColumns = keyColumns.replace(/\s+/g, ''); // "a,b" is standard, "a, b" is not
+    const normalizedKeyColumns = keyColumns.replace(/\s+/g, ''); // "a,b" is normalized, "a, b" is not
 
     if (typeof foreignKeyData === 'string') {
-      const foreignKeyParts = foreignKeyData.split('.');
+      const foreignKeyParts = foreignKeyData.match(/^\s*(\S+?)\.(\S+)\s*(.+?)?\s*$/);
+      let action = foreignKeyParts[3];
+
+      if (action === undefined || action.toUpperCase() === 'RESTRICT') {
+        action = null;
+      }
+
       normalizedForeignKeys[normalizedKeyColumns] = {
-        table: foreignKeyParts[0],
-        column: foreignKeyParts[1],
-        onDelete: null,
-        onUpdate: null,
+        table: foreignKeyParts[1],
+        column: foreignKeyParts[2],
+        onDelete: action,
+        onUpdate: action,
       };
     } else {
       normalizedForeignKeys[normalizedKeyColumns] = {

diff --git a/test/integration/MySQLPlus.test.js b/test/integration/MySQLPlus.test.js
@@ -372,10 +372,12 @@ describe('MySQLPlus', function() {
         onUpdate: 'NO ACTION',
       },
       d: 'columns_table.id',
+      eb: 'big_table.name NO ACTION',
       'eb, fb': {
         table: 'big_table',
         column: ['name', 'letter'],
       },
+      gc: 'columns_table.id  RESTRICT ',
       'gc, hc': {
         table: 'columns_table',
         column: ['id', 'email'],
@@ -404,7 +406,9 @@ describe('MySQLPlus', function() {
     '  CONSTRAINT `fk_fk_table_b` FOREIGN KEY (`b`) REFERENCES `big_table` (`id`),\n' +
     '  CONSTRAINT `fk_fk_table_c` FOREIGN KEY (`c`) REFERENCES `big_table` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,\n' +
     '  CONSTRAINT `fk_fk_table_d` FOREIGN KEY (`d`) REFERENCES `columns_table` (`id`),\n' +
+    '  CONSTRAINT `fk_fk_table_eb` FOREIGN KEY (`eb`) REFERENCES `big_table` (`name`) ON DELETE NO ACTION ON UPDATE NO ACTION,\n' +
     '  CONSTRAINT `fk_fk_table_eb_fb` FOREIGN KEY (`eb`, `fb`) REFERENCES `big_table` (`name`, `letter`),\n' +
+    '  CONSTRAINT `fk_fk_table_gc` FOREIGN KEY (`gc`) REFERENCES `columns_table` (`id`),\n' +
     '  CONSTRAINT `fk_fk_table_gc_hc` FOREIGN KEY (`gc`, `hc`) REFERENCES `columns_table` (`id`, `email`)\n' +
     ') ENGINE=InnoDB DEFAULT CHARSET=utf8';
 
@@ -440,6 +444,7 @@ describe('MySQLPlus', function() {
         table: 'big_table',
         column: ['name', 'letter'],
       },
+      gc: 'columns_table.id CASCADE',
       'gc, hc': {
         table: 'columns_table',
         column: ['id', 'email'],
@@ -469,6 +474,7 @@ describe('MySQLPlus', function() {
     '  CONSTRAINT `fk_fk_table_c` FOREIGN KEY (`c`) REFERENCES `big_table` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,\n' +
     '  CONSTRAINT `fk_fk_table_d` FOREIGN KEY (`d`) REFERENCES `columns_table` (`id`),\n' +
     '  CONSTRAINT `fk_fk_table_eb_fb` FOREIGN KEY (`eb`, `fb`) REFERENCES `big_table` (`name`, `letter`),\n' +
+    '  CONSTRAINT `fk_fk_table_gc` FOREIGN KEY (`gc`) REFERENCES `columns_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,\n' +
     '  CONSTRAINT `fk_fk_table_gc_hc` FOREIGN KEY (`gc`, `hc`) REFERENCES `columns_table` (`id`, `email`)\n' +
     ') ENGINE=InnoDB DEFAULT CHARSET=utf8';