{"action":"created","issue":{"url":"https://api.github.com/repos/perseas/Pyrseas/issues/197","repository_url":"https://api.github.com/repos/perseas/Pyrseas","labels_url":"https://api.github.com/repos/perseas/Pyrseas/issues/197/labels{/name}","comments_url":"https://api.github.com/repos/perseas/Pyrseas/issues/197/comments","events_url":"https://api.github.com/repos/perseas/Pyrseas/issues/197/events","html_url":"https://github.com/perseas/Pyrseas/issues/197","id":373667691,"node_id":"MDU6SXNzdWUzNzM2Njc2OTE=","number":197,"title":"feature request: execute sql in files before running the main yamltodb code","user":{"login":"xclayl","id":232027,"node_id":"MDQ6VXNlcjIzMjAyNw==","avatar_url":"https://avatars1.githubusercontent.com/u/232027?v=4","gravatar_id":"","url":"https://api.github.com/users/xclayl","html_url":"https://github.com/xclayl","followers_url":"https://api.github.com/users/xclayl/followers","following_url":"https://api.github.com/users/xclayl/following{/other_user}","gists_url":"https://api.github.com/users/xclayl/gists{/gist_id}","starred_url":"https://api.github.com/users/xclayl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xclayl/subscriptions","organizations_url":"https://api.github.com/users/xclayl/orgs","repos_url":"https://api.github.com/users/xclayl/repos","events_url":"https://api.github.com/users/xclayl/events{/privacy}","received_events_url":"https://api.github.com/users/xclayl/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":8,"created_at":"2018-10-24T20:39:32Z","updated_at":"2018-11-03T00:10:49Z","closed_at":null,"author_association":"NONE","body":"Some database changes yamltodb will never be able to handle.  Take renaming a table, yamltodb, at best, will see it as a DROP TABLE, and a CREATE TABLE, which isn't correct.  \r\n\r\nCurrently I've written some code to go through a folder and run all the SQL files.  For example this one:\r\n20181023 rename groups.sql:\r\n```\r\nDO $$ \r\n    BEGIN\r\n        ALTER TABLE IF EXISTS app.group RENAME TO groups;\r\n    END;\r\n$$;\r\n```\r\n\r\nIt can run multiple times, so you keep the file around for months until it's deployed to all the environments.  You'd think production would be last, but from experience, there will be some testing environments that are usually the last to get them.\r\n\r\nThe files run in filename order, which is why it starts with a date.\r\n\r\nIt'd be great if Pyrseas handled this natively.  \r\n\r\nWe've been doing this for 10 years with SQL Server with a tool similar to yours, so I'm happy to talk about the ins and outs.\r\n\r\nI think the best thing to say about this is it is predictable for users of yamltodb.  \r\n"},"comment":{"url":"https://api.github.com/repos/perseas/Pyrseas/issues/comments/435543515","html_url":"https://github.com/perseas/Pyrseas/issues/197#issuecomment-435543515","issue_url":"https://api.github.com/repos/perseas/Pyrseas/issues/197","id":435543515,"node_id":"MDEyOklzc3VlQ29tbWVudDQzNTU0MzUxNQ==","user":{"login":"xclayl","id":232027,"node_id":"MDQ6VXNlcjIzMjAyNw==","avatar_url":"https://avatars1.githubusercontent.com/u/232027?v=4","gravatar_id":"","url":"https://api.github.com/users/xclayl","html_url":"https://github.com/xclayl","followers_url":"https://api.github.com/users/xclayl/followers","following_url":"https://api.github.com/users/xclayl/following{/other_user}","gists_url":"https://api.github.com/users/xclayl/gists{/gist_id}","starred_url":"https://api.github.com/users/xclayl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xclayl/subscriptions","organizations_url":"https://api.github.com/users/xclayl/orgs","repos_url":"https://api.github.com/users/xclayl/repos","events_url":"https://api.github.com/users/xclayl/events{/privacy}","received_events_url":"https://api.github.com/users/xclayl/received_events","type":"User","site_admin":false},"created_at":"2018-11-03T00:10:49Z","updated_at":"2018-11-03T00:10:49Z","author_association":"NONE","body":"The query to look for an empty database is alright.  If there are no tables (but views, etc), there's nothing to migrate, so the pre-sql scripts don't need to run.\r\n\r\nThe scripts need to be able to re-run, but later executions, nothing happens.  This is actually much easier in Postgres compared to SQL Server.  \r\n\r\nHere's a sample script that can re-run perfectly fine:\r\n\r\n```\r\nDO $$ \r\n    BEGIN\r\n        ALTER TABLE app.group ADD COLUMN IF NOT EXISTS title character varying(10000) NULL;\r\n    END;\r\n$$;\r\n\r\nDO $$ \r\n    BEGIN\r\n        UPDATE app.group\r\n        SET title = 'unnamed'\r\n        WHERE title IS NULL;\r\n    END;\r\n$$;\r\n\r\n\r\nDO $$ \r\n    BEGIN\r\n        ALTER TABLE app.group ALTER COLUMN title SET NOT NULL;\r\n    END;\r\n$$;\r\n```\r\n\r\nNo need to worry about tracking if the file ran or not. "}}
{"actor":{"display_login":"xclayl"}}