{"action":"created","issue":{"url":"https://api.github.com/repos/letsencrypt/boulder/issues/149","labels_url":"https://api.github.com/repos/letsencrypt/boulder/issues/149/labels{/name}","comments_url":"https://api.github.com/repos/letsencrypt/boulder/issues/149/comments","events_url":"https://api.github.com/repos/letsencrypt/boulder/issues/149/events","html_url":"https://github.com/letsencrypt/boulder/pull/149","id":72868095,"number":149,"title":"Switch SQL backend to gorp","user":{"login":"rolandshoemaker","id":5080674,"avatar_url":"https://avatars.githubusercontent.com/u/5080674?v=3","gravatar_id":"","url":"https://api.github.com/users/rolandshoemaker","html_url":"https://github.com/rolandshoemaker","followers_url":"https://api.github.com/users/rolandshoemaker/followers","following_url":"https://api.github.com/users/rolandshoemaker/following{/other_user}","gists_url":"https://api.github.com/users/rolandshoemaker/gists{/gist_id}","starred_url":"https://api.github.com/users/rolandshoemaker/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rolandshoemaker/subscriptions","organizations_url":"https://api.github.com/users/rolandshoemaker/orgs","repos_url":"https://api.github.com/users/rolandshoemaker/repos","events_url":"https://api.github.com/users/rolandshoemaker/events{/privacy}","received_events_url":"https://api.github.com/users/rolandshoemaker/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":5,"created_at":"2015-05-03T21:23:08Z","updated_at":"2015-05-07T01:56:00Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/letsencrypt/boulder/pulls/149","html_url":"https://github.com/letsencrypt/boulder/pull/149","diff_url":"https://github.com/letsencrypt/boulder/pull/149.diff","patch_url":"https://github.com/letsencrypt/boulder/pull/149.patch"},"body":"Pretty much does what it says on the tin, but going into a bit more detail...\r\n\r\nThis switches the `SA` from using the `database/sql` backend for creating tables / inserting / updating / deleting / querying etc etc etc to the [`gorp`](https://github.com/go-gorp/gorp) (v1) library. This allows us to use all of the `core` objects as anonymous members of model structs instead of having to store the entire object as JSON (although a few inner objects are still stored as JSON for now). This means individual fields of `core` objects can be queried without having to decode the entire JSON object (i.e. we could now clean up expired `pending_/authz` by just looking at the `expires` column, yay!). These models are then used to do all of the `CREATE TABLE IF NOT EXIST` stuff and magically spit out an appropriate table schema. This PR doesn't change the public interface of `SQLStorageAuthority`, except for making `DumpTables` public, but makes major changes to the overall SQL table schemas.\r\n\r\nThis also solves the *extremely* annoying dialect problems we have been having up until now (providing dialects for SQLite3, MySQL, and PostgreSQL plus SQL Server and Oracle if people are into that, as well as managing the various placeholder formats `(?, ?) vs ($1, $2) etc`).\r\n\r\nWe also get basic [optimistic locking](https://github.com/go-gorp/gorp#optimistic-locking) for `UPDATE` statements without having to implement it ourselves, which should be useful.\r\n\r\nOne annoyance is that `gorp.v1`, which I've vendored, doesn't support manually setting column types or widths (or indexes) via struct tags and doesn't support adding multiple indexes with different increment types `none vs. AUTOINCREMENT`, but some fixes are in the works ([1](https://github.com/go-gorp/gorp/pull/173), [2](https://github.com/go-gorp/gorp/pull/246), [3](https://github.com/go-gorp/gorp/pull/233), [4](https://github.com/go-gorp/gorp/pull/201)) in their master (dev) branch for these problems, which is also *relatively* stable and could easily be vendored instead if the `v1` library if these PRs were to be merged down the road.\r\n\r\nFinally, this depreciates #131, #132, and #127, kinda...\r\n\r\nPing @jsha + @jcjones for thoughts & review, I'm almost definitely missing something here."},"comment":{"url":"https://api.github.com/repos/letsencrypt/boulder/issues/comments/99678679","html_url":"https://github.com/letsencrypt/boulder/pull/149#issuecomment-99678679","issue_url":"https://api.github.com/repos/letsencrypt/boulder/issues/149","id":99678679,"user":{"login":"jsha","id":220205,"avatar_url":"https://avatars.githubusercontent.com/u/220205?v=3","gravatar_id":"","url":"https://api.github.com/users/jsha","html_url":"https://github.com/jsha","followers_url":"https://api.github.com/users/jsha/followers","following_url":"https://api.github.com/users/jsha/following{/other_user}","gists_url":"https://api.github.com/users/jsha/gists{/gist_id}","starred_url":"https://api.github.com/users/jsha/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jsha/subscriptions","organizations_url":"https://api.github.com/users/jsha/orgs","repos_url":"https://api.github.com/users/jsha/repos","events_url":"https://api.github.com/users/jsha/events{/privacy}","received_events_url":"https://api.github.com/users/jsha/received_events","type":"User","site_admin":false},"created_at":"2015-05-07T01:56:00Z","updated_at":"2015-05-07T01:56:00Z","body":"Done reviewing for now. It looks like this will be a huge help, thanks for putting the time into it.\r\n\r\nAs a high-level comment: Why do we have separate model structs vs the real structs we use? Is that a pattern encouraged by gorp, or were you trying to avoid modifying the structs in objects.go? Because you should definitely feel free to modify the structs in object.go. For instance, there's no reason to have a mismatch between CertificateStatus in objects.go and the CertificateStatus model; We should just update the CertificateStatus in objects.go. Really I should have done that when I added the new fields, but didn't think of it, partly because I'm still in the midst of that change."}}