{"action":"created","comment":{"url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/comments/83510474","pull_request_review_id":4351711,"id":83510474,"diff_hunk":"@@ -136,6 +136,72 @@ describe('Discover model primary keys', function() {\n       }\n     });\n   });\n+\n+  it('should return just the primary keys and not the foreign keys for inventory', function(done) {\n+    db.discoverPrimaryKeys('inventory', function(err, models) {\n+      if (err) {\n+        console.error(err);\n+        done(err);\n+      } else {\n+        models.forEach(function(m) {\n+          // console.dir(m);\n+          assert(m.tableName === 'inventory');\n+          assert(m.columnName === 'id');\n+          assert(m.pkName.match(/_pk$/));\n+        });\n+        done(null, models);\n+      }\n+    });\n+  });\n+\n+  it('should return just the primary keys and not the foreign keys for dbo.inventory', function(done) {\n+    db.discoverPrimaryKeys('inventory', {owner: 'dbo'}, function(err, models) {\n+      if (err) {\n+        console.error(err);\n+        done(err);\n+      } else {\n+        models.forEach(function(m) {\n+          // console.dir(m);\n+          assert(m.tableName === 'inventory');\n+          assert(m.columnName === 'id');\n+          assert(m.pkName === 'inventory_pk');\n+        });\n+        done(null, models);\n+      }\n+    });\n+  });\n+  it('should return the primary key for version which consists of two columns', function(done) {\n+    db.discoverPrimaryKeys('version', function(err, models) {\n+      if (err) {\n+        console.error(err);\n+        done(err);\n+      } else {\n+        assert(models.length === 2);\n+        models.forEach(function(m) {\n+          // console.dir(m);\n+          assert(m.tableName === 'version');\n+          assert(m.pkName.match(/_pk$/));\n+        });\n+        done(null, models);\n+      }\n+    });\n+  });\n+  it('should return the primary key for dbo.version which consists of two columns', function(done) {\n+    db.discoverPrimaryKeys('version', {owner: 'dbo'}, function(err, models) {\n+      if (err) {\n+        console.error(err);\n+        done(err);\n+      } else {\n+        assert(models.length === 2);\n+        models.forEach(function(m) {\n+          // console.dir(m);\n+          assert(m.tableName === 'version');\n+          assert(m.pkName.match(/_pk$/));\n+        });\n+        done(null, models);","path":"test/discover.test.js","position":50,"original_position":66,"commit_id":"f56a1093d04fe48010a75da9700abeb3372e67ee","original_commit_id":"f56a1093d04fe48010a75da9700abeb3372e67ee","user":{"login":"superkhau","id":1617364,"avatar_url":"https://avatars.githubusercontent.com/u/1617364?v=3","gravatar_id":"","url":"https://api.github.com/users/superkhau","html_url":"https://github.com/superkhau","followers_url":"https://api.github.com/users/superkhau/followers","following_url":"https://api.github.com/users/superkhau/following{/other_user}","gists_url":"https://api.github.com/users/superkhau/gists{/gist_id}","starred_url":"https://api.github.com/users/superkhau/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/superkhau/subscriptions","organizations_url":"https://api.github.com/users/superkhau/orgs","repos_url":"https://api.github.com/users/superkhau/repos","events_url":"https://api.github.com/users/superkhau/events{/privacy}","received_events_url":"https://api.github.com/users/superkhau/received_events","type":"User","site_admin":false},"body":"done();","created_at":"2016-10-14T22:12:43Z","updated_at":"2016-10-14T22:14:04Z","html_url":"https://github.com/strongloop/loopback-connector-mssql/pull/99#discussion_r83510474","pull_request_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/99","_links":{"self":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/comments/83510474"},"html":{"href":"https://github.com/strongloop/loopback-connector-mssql/pull/99#discussion_r83510474"},"pull_request":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/99"}}},"pull_request":{"url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/99","id":87123802,"html_url":"https://github.com/strongloop/loopback-connector-mssql/pull/99","diff_url":"https://github.com/strongloop/loopback-connector-mssql/pull/99.diff","patch_url":"https://github.com/strongloop/loopback-connector-mssql/pull/99.patch","issue_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/issues/99","number":99,"state":"open","locked":false,"title":"Fixes #79 discovery of primary keys","user":{"login":"devotis","id":465989,"avatar_url":"https://avatars.githubusercontent.com/u/465989?v=3","gravatar_id":"","url":"https://api.github.com/users/devotis","html_url":"https://github.com/devotis","followers_url":"https://api.github.com/users/devotis/followers","following_url":"https://api.github.com/users/devotis/following{/other_user}","gists_url":"https://api.github.com/users/devotis/gists{/gist_id}","starred_url":"https://api.github.com/users/devotis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/devotis/subscriptions","organizations_url":"https://api.github.com/users/devotis/orgs","repos_url":"https://api.github.com/users/devotis/repos","events_url":"https://api.github.com/users/devotis/events{/privacy}","received_events_url":"https://api.github.com/users/devotis/received_events","type":"User","site_admin":false},"body":"The query executed in lib/discovery.js **queryForPrimaryKeys returns not only primary keys but also foreign keys**. Which in turn causes schemas to be generated with foreign key properties with an id property > 0. Which in turn generates an error when updating model instance foreign key properties through updateAttributes: *\"id property (some foreign key column) cannot be updated from A to\r\n B\"*\r\n\r\nRoot cause is the query defined in the queryForPrimaryKeys function on the test table inventory.\r\n\r\n```\r\nSELECT\r\n  kc.table_schema     AS \"owner\", \r\n  kc.table_name       AS \"tableName\",\r\n  kc.column_name      AS \"columnName\",\r\n  kc.ordinal_position AS \"keySeq\",\r\n  kc.constraint_name  AS \"pkName\"\r\nFROM information_schema.table_constraints tc\r\nJOIN information_schema.key_column_usage kc\r\n  ON kc.table_name      = tc.table_name\r\n AND kc.table_schema    = tc.table_schema\r\nWHERE tc.constraint_type  ='PRIMARY KEY'\r\n  AND kc.ordinal_position IS NOT NULL\r\n  AND kc.table_schema     = 'dbo'\r\n  AND kc.table_name       = 'inventory'\r\nORDER BY kc.table_schema, kc.table_name, kc.ordinal_position;\r\n```\r\n\r\nwhich returns:\r\n```\r\nowner tableName columnName  keySeq pkName                        \r\ndbo   inventory location_id 1      location_fk                   \r\ndbo   inventory id          1      PK__inventor__3213E83F07F6335A\r\ndbo   inventory product_id  1      product_fk \r\n```\r\n\r\nBut it should return:\r\n```\r\nowner tableName columnName  keySeq pkName                        \r\ndbo   inventory id          1      PK__inventor__3213E83F07F6335A\r\n```\r\n\r\nThis PR adds the missing join-on criterium.\r\n```\r\nJOIN information_schema.key_column_usage kc\r\n  ON kc.table_name      = tc.table_name\r\n AND kc.constraint_name = tc.constraint_name -- < the missing criterium\r\n AND kc.table_schema    = tc.table_schema\r\n```\r\n\r\nWithout that criterium you're getting every key column for the same table which are all foreign keys too.","created_at":"2016-09-28T13:50:11Z","updated_at":"2016-10-14T22:14:04Z","closed_at":null,"merged_at":null,"merge_commit_sha":"04c6a9b85263c089732fdcee5d014457e9c34f8b","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/99/commits","review_comments_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/99/comments","review_comment_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/comments{/number}","comments_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/issues/99/comments","statuses_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/statuses/f56a1093d04fe48010a75da9700abeb3372e67ee","head":{"label":"devotis:master","ref":"master","sha":"f56a1093d04fe48010a75da9700abeb3372e67ee","user":{"login":"devotis","id":465989,"avatar_url":"https://avatars.githubusercontent.com/u/465989?v=3","gravatar_id":"","url":"https://api.github.com/users/devotis","html_url":"https://github.com/devotis","followers_url":"https://api.github.com/users/devotis/followers","following_url":"https://api.github.com/users/devotis/following{/other_user}","gists_url":"https://api.github.com/users/devotis/gists{/gist_id}","starred_url":"https://api.github.com/users/devotis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/devotis/subscriptions","organizations_url":"https://api.github.com/users/devotis/orgs","repos_url":"https://api.github.com/users/devotis/repos","events_url":"https://api.github.com/users/devotis/events{/privacy}","received_events_url":"https://api.github.com/users/devotis/received_events","type":"User","site_admin":false},"repo":{"id":69467288,"name":"loopback-connector-mssql","full_name":"devotis/loopback-connector-mssql","owner":{"login":"devotis","id":465989,"avatar_url":"https://avatars.githubusercontent.com/u/465989?v=3","gravatar_id":"","url":"https://api.github.com/users/devotis","html_url":"https://github.com/devotis","followers_url":"https://api.github.com/users/devotis/followers","following_url":"https://api.github.com/users/devotis/following{/other_user}","gists_url":"https://api.github.com/users/devotis/gists{/gist_id}","starred_url":"https://api.github.com/users/devotis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/devotis/subscriptions","organizations_url":"https://api.github.com/users/devotis/orgs","repos_url":"https://api.github.com/users/devotis/repos","events_url":"https://api.github.com/users/devotis/events{/privacy}","received_events_url":"https://api.github.com/users/devotis/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/devotis/loopback-connector-mssql","description":"LoopBack connector for Microsoft SQL Server","fork":true,"url":"https://api.github.com/repos/devotis/loopback-connector-mssql","forks_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/forks","keys_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/keys{/key_id}","collaborators_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/teams","hooks_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/hooks","issue_events_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/issues/events{/number}","events_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/events","assignees_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/assignees{/user}","branches_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/branches{/branch}","tags_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/tags","blobs_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/git/refs{/sha}","trees_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/git/trees{/sha}","statuses_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/statuses/{sha}","languages_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/languages","stargazers_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/stargazers","contributors_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/contributors","subscribers_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/subscribers","subscription_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/subscription","commits_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/commits{/sha}","git_commits_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/git/commits{/sha}","comments_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/comments{/number}","issue_comment_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/issues/comments{/number}","contents_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/contents/{+path}","compare_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/compare/{base}...{head}","merges_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/merges","archive_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/downloads","issues_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/issues{/number}","pulls_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/pulls{/number}","milestones_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/milestones{/number}","notifications_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/labels{/name}","releases_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/releases{/id}","deployments_url":"https://api.github.com/repos/devotis/loopback-connector-mssql/deployments","created_at":"2016-09-28T13:42:48Z","updated_at":"2016-09-28T13:42:49Z","pushed_at":"2016-10-14T15:10:49Z","git_url":"git://github.com/devotis/loopback-connector-mssql.git","ssh_url":"git@github.com:devotis/loopback-connector-mssql.git","clone_url":"https://github.com/devotis/loopback-connector-mssql.git","svn_url":"https://github.com/devotis/loopback-connector-mssql","homepage":"https://docs.strongloop.com/display/APIC/SQL+Server+connector","size":245,"stargazers_count":0,"watchers_count":0,"language":"JavaScript","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"strongloop:master","ref":"master","sha":"d83fd9577f5cdabd4d328dae4b12fdf226919aa7","user":{"login":"strongloop","id":3020012,"avatar_url":"https://avatars.githubusercontent.com/u/3020012?v=3","gravatar_id":"","url":"https://api.github.com/users/strongloop","html_url":"https://github.com/strongloop","followers_url":"https://api.github.com/users/strongloop/followers","following_url":"https://api.github.com/users/strongloop/following{/other_user}","gists_url":"https://api.github.com/users/strongloop/gists{/gist_id}","starred_url":"https://api.github.com/users/strongloop/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/strongloop/subscriptions","organizations_url":"https://api.github.com/users/strongloop/orgs","repos_url":"https://api.github.com/users/strongloop/repos","events_url":"https://api.github.com/users/strongloop/events{/privacy}","received_events_url":"https://api.github.com/users/strongloop/received_events","type":"Organization","site_admin":false},"repo":{"id":16129899,"name":"loopback-connector-mssql","full_name":"strongloop/loopback-connector-mssql","owner":{"login":"strongloop","id":3020012,"avatar_url":"https://avatars.githubusercontent.com/u/3020012?v=3","gravatar_id":"","url":"https://api.github.com/users/strongloop","html_url":"https://github.com/strongloop","followers_url":"https://api.github.com/users/strongloop/followers","following_url":"https://api.github.com/users/strongloop/following{/other_user}","gists_url":"https://api.github.com/users/strongloop/gists{/gist_id}","starred_url":"https://api.github.com/users/strongloop/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/strongloop/subscriptions","organizations_url":"https://api.github.com/users/strongloop/orgs","repos_url":"https://api.github.com/users/strongloop/repos","events_url":"https://api.github.com/users/strongloop/events{/privacy}","received_events_url":"https://api.github.com/users/strongloop/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/strongloop/loopback-connector-mssql","description":"LoopBack connector for Microsoft SQL Server","fork":false,"url":"https://api.github.com/repos/strongloop/loopback-connector-mssql","forks_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/forks","keys_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/keys{/key_id}","collaborators_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/teams","hooks_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/hooks","issue_events_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/issues/events{/number}","events_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/events","assignees_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/assignees{/user}","branches_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/branches{/branch}","tags_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/tags","blobs_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/git/refs{/sha}","trees_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/git/trees{/sha}","statuses_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/statuses/{sha}","languages_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/languages","stargazers_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/stargazers","contributors_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/contributors","subscribers_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/subscribers","subscription_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/subscription","commits_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/commits{/sha}","git_commits_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/git/commits{/sha}","comments_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/comments{/number}","issue_comment_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/issues/comments{/number}","contents_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/contents/{+path}","compare_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/compare/{base}...{head}","merges_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/merges","archive_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/downloads","issues_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/issues{/number}","pulls_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls{/number}","milestones_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/milestones{/number}","notifications_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/labels{/name}","releases_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/releases{/id}","deployments_url":"https://api.github.com/repos/strongloop/loopback-connector-mssql/deployments","created_at":"2014-01-22T05:34:06Z","updated_at":"2016-09-19T13:31:10Z","pushed_at":"2016-10-14T20:01:00Z","git_url":"git://github.com/strongloop/loopback-connector-mssql.git","ssh_url":"git@github.com:strongloop/loopback-connector-mssql.git","clone_url":"https://github.com/strongloop/loopback-connector-mssql.git","svn_url":"https://github.com/strongloop/loopback-connector-mssql","homepage":"https://docs.strongloop.com/display/APIC/SQL+Server+connector","size":283,"stargazers_count":28,"watchers_count":28,"language":"JavaScript","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":32,"mirror_url":null,"open_issues_count":40,"forks":32,"open_issues":40,"watchers":28,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/99"},"html":{"href":"https://github.com/strongloop/loopback-connector-mssql/pull/99"},"issue":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/issues/99"},"comments":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/issues/99/comments"},"review_comments":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/99/comments"},"review_comment":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/pulls/99/commits"},"statuses":{"href":"https://api.github.com/repos/strongloop/loopback-connector-mssql/statuses/f56a1093d04fe48010a75da9700abeb3372e67ee"}}}}
{"actor":{"display_login":"superkhau"}}