On 20 August 2018 at 16:58, Ishara Cooray <[email protected]> wrote: > For me, PRODUCT_VERSION column in every table seems to be redundant. > Could you please explain the reason for introducing this column in each > table? Is this for auditing? >
The reason for this is so the on the fly migration code is able to detect if it needs to migrate a given row. For example if running version 3.1.0, 1. A row from a table is retrieved 2. If the value of the PRODUCT_VERSION column of the row is 3.0.0 migration code will run to convert the data and update the value of PRODUCT_VERSION to 3.1.0 once row migration has occurred. 3. On subsequent retrievals of the same row since PRODUCT_VERSION is 3.1.0 migration code will not execute against the row. > Thanks & Regards, > Ishara Cooray > Senior Software Engineer > Mobile : +9477 262 9512 > WSO2, Inc. | http://wso2.com/ > Lean . Enterprise . Middleware > > On Mon, Aug 20, 2018 at 4:03 PM, Uvindra Dias Jayasinha <[email protected]> > wrote: > >> Small calcification regarding this statement, >> >> For the 3.0.0 release we just need to implement steps *1* and *2* above. >>> Step *3* can be done for all subsequent releases. >>> >> >> I specifically meant the changes to the DB schema when it comes to steps >> 1 and 2 . Obviously no migration logic will be needed for 3.0.0 itself >> >> On 20 August 2018 at 15:58, Uvindra Dias Jayasinha <[email protected]> >> wrote: >> >>> In the past the APIM product has relied on an external component such as >>> a migration client for upgrading from a given product version to a higher >>> version.The end user was required to configure the latest product that they >>> are upgrading to against their current data(databases, synapse files, >>> registry) and run the migration client manually to upgrade the product. >>> This can be a cumbersome and error prone process to accomplish for end >>> users, making product version upgrades time consuming. >>> >>> To overcome the above problem on the fly upgrades were proposed where >>> the product code detects if relevant data being accessed needs to be >>> migrated to the latest version and migrates the data when the code is >>> executed when the respective feature is used. Upgrading product data is >>> much easier from 3.0.0 onwards because all data related to the product is >>> stored in a central database.This means that the end user does not need to >>> get involved in upgrading, it happens without them even being aware as they >>> use the latest version of the product by pointing it against their current >>> database.This makes for a more pleasant user experience when upgrading, >>> putting the burden of the upgrade to be added by the developer into the >>> functional code itself. >>> >>> The following outlines a design that can be supported from 3.0.0 onwards >>> to outline a uniform way of handling product upgrades. This is inspired by >>> the methodology used by FlywayDB to enable DB migrations[1] but also >>> taking into account the requirement of being able to run on the fly at >>> runtime(Note: DB schema changes between releases will need to be handled >>> via DB vendor specific scripts prepared by the team to be run by the >>> customer against their DB). >>> >>> >>> *1.* A new table will be added to the schema called >>> PRODUCT_VERSION_AUDIT to track the product version upgrades that take place >>> on a given dataset >>> >>> PRODUCT_VERSION_AUDIT >>> VERSION VARCHAR(5) >>> CREATED_TIME TIMESTAMP(6) >>> >>> If a user begins using APIM version 3.0.0 and then upgrades to version >>> 3.1.0 the table will contain the following values, >>> >>> VERSION CREATED_TIME >>> 3.0.0 2018-11-11 13:23:44 >>> 3.1.0 2019-10-14 9:26:22 >>> >>> This gives a historical view of the product versions a customer has been >>> using. A new row will be inserted into the table when a given product >>> version is started for the first time. >>> >>> >>> >>> *2*. Each table in the database will have a new column called >>> PRODUCT_VERSION(VARCHAR(5)) added. When a row is inserted for the first >>> time it will populate this column with the current product version being >>> used. >>> For example the AM_API table could have the following entries for a >>> customer using APIM 3.0.0, >>> >>> UUID PROVIDER NAME VERSION CONTEXT PRODUCT_VERSION >>> 123e4567-e89b-12d3-a456-426655440000 admin abc 1.0.0 /abc 3.0.0 >>> 00112233-4455-6677-8899-aabbccddeeff admin xyz 1.0.0 /xyz 3.0.0 >>> >>> >>> Lets assume when upgrading to 3.1.0 the leading '/' character in the >>> context needs to be removed. On the fly migration code will run when a >>> given row is accessed by the DAO layer to remove the '/'. Once the >>> migration of the row is completed the PRODUCT_VERSION column will be >>> updated with the value 3.1.0 to signify that the migration for this row has >>> been completed. The PRODUCT_VERSION column can be validated to check if >>> migration code needs to be executed. So assuming the API abc is accessed >>> first the table will look as follows after migration, >>> >>> >>> UUID PROVIDER NAME VERSION CONTEXT PRODUCT_VERSION >>> 123e4567-e89b-12d3-a456-426655440000 admin abc 1.0.0 abc 3.1.0 >>> 00112233-4455-6677-8899-aabbccddeeff admin xyz 1.0.0 /xyz 3.0.0 >>> >>> >>> As a pre-requisite the product team will need to create respective DB >>> scripts for the schema changes that will take place with a given release. >>> This will only include schema modifications. Customer will need to run >>> these manually against their DB but actual data migration will take place >>> automatically under the hood. >>> >>> >>> *3*. New Java interfaces will be added for each DB entity that will >>> responsible for migrating the respective entity. For example for APIs we >>> can have, >>> >>> public interface APIMigrator { >>> API migrate(PreparedStatement statement) throws MigrationException;} >>> >>> >>> >>> This will accept the PreparedStatement created for data retrieval and >>> returns the migrated API object. The implemenataion of the above could look >>> as follows, >>> >>> public class APIMigratorImpl implements APIMigrator { >>> >>> public API migrate(PreparedStatement statement) throws >>> MigrationException { >>> API api = null; >>> try (ResultSet rs = statement.executeQuery()) { >>> while (rs.next()) { >>> String dataProductVersion = rs.getString("PRODUCT_VERSION"); >>> >>> // Assume that currentProductVersion == "3.2.0" >>> >>> while (!currentProductVersion.equals(dataProductVersion)) } >>> if ("3.0.0".equals(dataProductVersion)) { >>> // Logic to migrate data to next available version 3.1.0 >>> // And update the PRODUCT_VERSION column of the row to >>> 3.1.0 >>> >>> dataProductVersion = "3.1.0"; >>> >>> } >>> >>> if ("3.1.0".equals(dataProductVersion)) { >>> // Logic to migrate data to next available version 3.2.0 >>> // And update the PRODUCT_VERSION column of the row to >>> 3.2.0 >>> >>> dataProductVersion = "3.2.0"; >>> } >>> } >>> } >>> } >>> return api; >>> } >>> } >>> >>> >>> The above interface implementation will be called from within DOA layer >>> before returning an object instance. >>> >>> For the 3.0.0 release we just need to implement steps *1* and *2* >>> above. Step *3* can be done for all subsequent releases. >>> >>> >>> >>> [1] https://flywaydb.org >>> >>> >>> >>> -- >>> Regards, >>> Uvindra >>> >>> Mobile: 777733962 >>> >> >> >> >> -- >> Regards, >> Uvindra >> >> Mobile: 777733962 >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > _______________________________________________ > Architecture mailing list > [email protected] > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > > -- Regards, Uvindra Mobile: 777733962
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
