Fix columns with utc_timestamp default#77
Fix columns with utc_timestamp default#77c9845 wants to merge 4 commits intomysql2sqlite:masterfrom c9845:fix-utc-timestamp-columns
Conversation
…ect the "direct pipe" method as noted in the README. Direct piping to sqlite3 is typically how this script will be used since you get a sqlite database file as a result.
MySQL allows using UTC_TIMESTAMP as the default value for a column (i.e. DatetimeCreated DATETIME DEFAULT UTC_TIMESTAMP). This is useful in MySQL since the timezone of the default value will always be UTC. Compare this against using CURRENT_TIMESTAMP where the timezone of the default value will be the timezone of the system or server. UTC_TIMESTAMP does not exists in SQLite; SQLite only uses CURRENT_TIMESTAMP and it returns a value in the UTC timezone. So if we want to convert from MySQL to SQLite, we need to convert UTC_TIMESTAMP to CURRENT_TIMESTAMP.
| / (PRIMARY |primary )?(KEY|key)/ { gsub( /\([0-9]+\)/, "" ) } | ||
|
|
||
| # Replace utc_timestamp with CURRENT_TIMESTAMP. utc_timestamp does not exist on sqlite but sqlites CURRENT_TIMESTAMP uses utc timezone. | ||
| / (utc_timestamp)/ { gsub( /utc_timestamp/, "current_timestamp" ) } |
There was a problem hiding this comment.
Could you please take a look at the rather bigger PR I referenced? We need thorough testing and there are good examples.
Could you also make both your PRs mutually non-conflicting?
My main concern is though, that this change seems extremely dangerous. The other such generic substitutions are usually based on the fact, that the input string always appears in the input and thus they match once and then not any more. But utc_timestamp is not such a regular case and thus this will much more likely destroy the values in the DB.
But maybe I'm missing something (one thing I know for sure I'm missing is time).
There was a problem hiding this comment.
Hmm, not sure how I did that with the conflicting commits...brain fart or something. I think I fixed it.
In regards to danger, I agree, but I can't figure out another way to handle this. I tried putting the gsub inside the /^(CREATE... block but it won't run there. It would probably help if I understood awk a bit better. I will try to dive in more.
There was a problem hiding this comment.
Well, I'd try to get rid of gsub in favor of sub. Second, there are few variables (inTrigger inView aInc firstInTable) designating the scope (and state) where the parsing currently takes place. This way you can easily detect whether you're in a CREATE scope or not.
c9845
changed the title
2 participants
Replace
utc_timestampwithCURRENT_TIMESTAMP.MySQL allows using
UTC_TIMESTAMPas the default value for a column (i.e.DatetimeCreated DATETIME DEFAULT UTC_TIMESTAMP). This is useful in MySQL since the timezone of the default value will always be UTC. Compare this against usingCURRENT_TIMESTAMPwhere the timezone of the default value will be the timezone of the system or server.UTC_TIMESTAMPdoes not exists in SQLite; SQLite only usesCURRENT_TIMESTAMPand it returns a value in the UTC timezone. So if we want to convert from MySQL to SQLite, we need to convertUTC_TIMESTAMPtoCURRENT_TIMESTAMP.