{"action":"created","issue":{"url":"https://api.github.com/repos/skeema/skeema/issues/5","repository_url":"https://api.github.com/repos/skeema/skeema","labels_url":"https://api.github.com/repos/skeema/skeema/issues/5/labels{/name}","comments_url":"https://api.github.com/repos/skeema/skeema/issues/5/comments","events_url":"https://api.github.com/repos/skeema/skeema/issues/5/events","html_url":"https://github.com/skeema/skeema/issues/5","id":228608397,"number":5,"title":"Limitations of declarative approach","user":{"login":"shlomi-noach","id":2607934,"avatar_url":"https://avatars2.githubusercontent.com/u/2607934?v=3","gravatar_id":"","url":"https://api.github.com/users/shlomi-noach","html_url":"https://github.com/shlomi-noach","followers_url":"https://api.github.com/users/shlomi-noach/followers","following_url":"https://api.github.com/users/shlomi-noach/following{/other_user}","gists_url":"https://api.github.com/users/shlomi-noach/gists{/gist_id}","starred_url":"https://api.github.com/users/shlomi-noach/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/shlomi-noach/subscriptions","organizations_url":"https://api.github.com/users/shlomi-noach/orgs","repos_url":"https://api.github.com/users/shlomi-noach/repos","events_url":"https://api.github.com/users/shlomi-noach/events{/privacy}","received_events_url":"https://api.github.com/users/shlomi-noach/received_events","type":"User","site_admin":true},"labels":[{"id":472677901,"url":"https://api.github.com/repos/skeema/skeema/labels/feature%20request","name":"feature request","color":"84b6eb","default":false}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2017-05-15T05:18:23Z","updated_at":"2017-05-16T04:41:26Z","closed_at":"2017-05-16T04:41:06Z","body":"`skeema` works in a declarative approach, where the user specifies the desired table structure, as opposed to the `alter` statement which gets there. `skeema` itself infers the statement that would modify the table schema from version `x` to version `y`.\r\n\r\nIllustrated below are two use cases the declarative approach cannot handle, and in fact requires an added insight from the user, namely the very `alter` statement.\r\n\r\n### RENAME (CHANGE) COLUMN\r\n\r\nConsider this [tests from gh-ost](https://github.com/github/gh-ost/blob/d0167600d0eab991cb3e39d57227d80494d62a39/localtests/rename/create.sql):\r\n\r\n```sql\r\ncreate table gh_ost_test (\r\n  id int auto_increment,\r\n  c1 int not null,\r\n  c2 int not null,\r\n  primary key (id)\r\n) auto_increment=1;\r\n\r\ninsert into gh_ost_test ...\r\n```\r\n\r\n```sql\r\nalter table gh_ost_test change column c2 c3 int not null\r\n```\r\n\r\nResulting table:\r\n\r\n```sql\r\ncreate table gh_ost_test (\r\n  id int auto_increment,\r\n  c1 int not null,\r\n  c3 int not null,\r\n  primary key (id)\r\n) auto_increment=1;\r\n```\r\n\r\nWhat would `skeema` infer? It would infer a `DROP COLUMN c2, ADD COLUMN c3 INT NOT NULL`.\r\nHowever that is incorrect and will lose data. The values of `c2` must be transferred to `c3`.\r\n\r\n### 2. DROP+ADD COLUMN\r\n\r\nThis is the opposite for the `RENAME` example.\r\n\r\nConsider [this gh-ost test](https://github.com/github/gh-ost/blob/d0167600d0eab991cb3e39d57227d80494d62a39/localtests/drop-null-add-not-null/create.sql):\r\n\r\n```sql\r\ncreate table gh_ost_test (\r\n  id int auto_increment,\r\n  c1 int null,\r\n  c2 int not null,\r\n  primary key (id)\r\n) auto_increment=1;\r\n```\r\n\r\n```sql\r\nalter table gh_ost_test drop column c1, add column c1 int not null default 47\r\n```\r\n\r\nResulting table:\r\n\r\n\r\n```sql\r\ncreate table gh_ost_test (\r\n  id int auto_increment,\r\n  c1 int null,\r\n  c2 int not null default 47,\r\n  primary key (id)\r\n) auto_increment=1;\r\n```\r\n\r\nWhat would `skeema` infer? It would infer changing of the default, and would _keep_ the values for `c2`, where it should really drop `c2`, discarding existing values, recreating `c2` with the value `47`.\r\n\r\n---\r\n\r\nI would suggest at first add these two in [Unsupported for ALTERs](https://github.com/skeema/skeema/blob/29acd8d9f3cb6365c11d637b191a34c249ddafa6/doc/requirements.md#unsupported-for-alters) so that the issue is known.\r\n\r\nOtherwise I would recommend a potential add-on of the original `alter` statement. If exists, `skeema` would be able to parse it (feel free to use [parser.go](https://github.com/github/gh-ost/blob/d0167600d0eab991cb3e39d57227d80494d62a39/go/sql/parser.go)) and apply the extra insight."},"comment":{"url":"https://api.github.com/repos/skeema/skeema/issues/comments/301673735","html_url":"https://github.com/skeema/skeema/issues/5#issuecomment-301673735","issue_url":"https://api.github.com/repos/skeema/skeema/issues/5","id":301673735,"user":{"login":"shlomi-noach","id":2607934,"avatar_url":"https://avatars2.githubusercontent.com/u/2607934?v=3","gravatar_id":"","url":"https://api.github.com/users/shlomi-noach","html_url":"https://github.com/shlomi-noach","followers_url":"https://api.github.com/users/shlomi-noach/followers","following_url":"https://api.github.com/users/shlomi-noach/following{/other_user}","gists_url":"https://api.github.com/users/shlomi-noach/gists{/gist_id}","starred_url":"https://api.github.com/users/shlomi-noach/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/shlomi-noach/subscriptions","organizations_url":"https://api.github.com/users/shlomi-noach/orgs","repos_url":"https://api.github.com/users/shlomi-noach/repos","events_url":"https://api.github.com/users/shlomi-noach/events{/privacy}","received_events_url":"https://api.github.com/users/shlomi-noach/received_events","type":"User","site_admin":true},"created_at":"2017-05-16T04:41:26Z","updated_at":"2017-05-16T04:41:26Z","body":"> `safe-below-size=1`\r\n\r\nnice!\r\n\r\n>  I haven't ever encountered a real situation where a user wanted to remove a column, and then replace it with another column of same name, but without wanting the old data to be present. If a user wants to\r\n\r\nNeither have I, until https://github.com/github/gh-ost/issues/384. I agree it's a rare case.\r\n\r\nI also agree on the `rename` deploy order problem."}}
{"actor":{"display_login":"shlomi-noach"}}