ucfopen
Configurable Table Names for Postgres Compatibility
Heroku needs compatibility with Postgres in order to use the free tier without a credit card. Adding this compatibility will also allow a larger number of open source users to install UDOIT without adding another database server to their infrastructure.
user is a reserved word in Postgres. If we were writing SQL by hand, we could put double quotes around user. We must do this manually every time this table is referenced (https://github.com/doctrine/orm/issues/5874). This will be fixed in Doctrine 3, but it's currently in beta.
In my experience, reserved words tend to come up again and again as pain points .... if it's not too big of a lift in a young project, I would recommend changing the column names so you don't have to worry about escaping. Then a CI job to create the database on your supported platforms would prevent any future additions of reserved words.
The (valid) counterpoint: MySQL 8 and newer versions of DBs continue to (rapidly) expand the list of reserved words.
Would it make sense to make a column prefix (like wordpress) variable? udoit_users, udoit_reports, etc?
I'm going to push this one out to 3.1.0. This is not an immediate issue because the user table has been renamed to users, but the points brought up by @ottenhoff and @ssilverm prove that this is worth revisiting.
Does this mean that 3.0 is incompatible with the Heroku free tier? Champlain is looking to upgrade later in the winter.
Helpful to know if this would keep us from doing that.
@cooperfellows 3.0.0 is compatible with the Heroku free tier. All of the Heroku Postgres issues have been resolved at this time. This request is just to make the table names customizable so that institutions have the option.
It seems like we don't have consensus, so I'm removing this from the 3.1.0 milestone. We can revisit this for a future release.