On Thu, Aug 24, 2017 at 8:28 PM, Harshal Dhumal < harshal.dhu...@enterprisedb.com> wrote:
> > > -- > *Harshal Dhumal* > *Sr. Software Engineer* > > EnterpriseDB India: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > On Thu, Aug 24, 2017 at 9:44 PM, Dave Page <dp...@pgadmin.org> wrote: > >> >> >> On Thu, Aug 24, 2017 at 10:36 AM, Surinder Kumar < >> surinder.ku...@enterprisedb.com> wrote: >> >>> Hi Dave, >>> >>> On Thu, Aug 24, 2017 at 2:28 PM, Dave Page <dp...@pgadmin.org> wrote: >>> >>>> Anyone object to doing a release on 14th September, wrapping the code >>>> on Monday 11th? This seems like the best option for our QA folks who will >>>> be off for EID somewhen in the two weeks before. >>>> >>>> Assuming not, should this be 1.7 or 2.0? >>>> >>>> If we go with 2.0, it'll be for "safety" given the proposed changes to >>>> path management to allow both server and desktop modes to work out of the >>>> box on Linux. >>>> >>>> If we do that, we also need to ensure that any changes to the config >>>> database are backwards compatible, as a 2.0 release would be a side-by-side >>>> installation. Surinder; was it you that had looked into that? >>>> >>> I had looked into this and here are my findings: >>> 1. If we are using newer version of pgAdmin and the go back to older >>> version of pgAdmin, then on running `python pgAdmin4.py`. the >>> flask-migrate(Alembic) try to perform downgrade by one step only(ie. it can >>> switch back to one migration only when we run `python pgAdmin4.py`). But >>> we have multiple database revisions to be migrated. So migration fails here. >>> >>> 2. When Alebmic downgrade is performed by one step, it looks for >>> downgrade function in that specific database revision, but in our code we >>> didn't written downgrade function. But if we have written downgrade >>> statement, still there is an issue: >>> ie. If we add a new column to a table xyz using ALTER statement like: >>> >>> ``` >>> >>> def upgrade(): >>> >>> verison = get_version() >>> >>> >>> db.engine.execute( >>> >>> 'ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)' >>> >>> ) >>> >>> def downgrade(): >>> >>> pass >>> ``` >>> then on downgrade it executes `downgrade` method, so downgrade should >>> have code like >>> `ALTER TABLE server DROP COLUMN hostaddr ` >>> but in sqlite DROP COLUMN statements don't work. >>> So, this is a an issue with Sqlite database. However, an alternative way >>> is also given. Here is link >>> <https://stackoverflow.com/questions/5938048/delete-column-from-sqlite-table> >>> >>> >>> Still, I didn't find any other solution on upgrading/downgrading >>> database revisions without errors. >>> It is an issue with Flask-Migrate(Alembic) plugin. >>> >> >> >> Urgh. So I guess the other option is that we version the DB filename as >> well. The downside of that is that users will want to migrate their >> settings - which may be awkward as we'll have no real way of knowing where >> they are. >> >> Thoughts? >> >> Or should we write our own custom backword migrations? For eg. dropping > column can be achieved by creating another table excluding the columns > which we want to drop then copy data to new table and then drop old table > and rename new table to old name. And also sqlite database schema which we > have in pgAdmin4 is small so writing and maintaining custom migration won > be that hard. > The problem is that we don't want to migrate backwards; we want both versions to be able to run with the same database (for example, because you might have multiple versions installed with the EDB PG installer as I do on my laptop). Previously, we always made sure our changes were backwards compatible (e.g. by only adding new columns, never removing or renaming them), and our home-grown migration code only cared about upgrading the database to the current version; it wouldn't complain if the database was of a newer version. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
