Hi All, I have written the migration scripts for the above changes made.
There are two scripts. [1] is already there and the other one has to be generated with the provided executable jar [2]. In order to generate the sql script, you can follow the "How to run the jar" section in README.txt [3]. [1] https://github.com/punnadih/my_personal_repo/blob/master/AppVersionMigration/MigrationScript.sql [2] https://github.com/punnadih/my_personal_repo/blob/master/AppVersionMigration/AppVersionMigration.jar [3] https://github.com/punnadih/my_personal_repo/blob/master/AppVersionMigration/README.txt On Thu, Apr 23, 2015 at 2:32 AM, Punnadi Gunarathna <punn...@wso2.com> wrote: > hi All, > > With the issue discussed in mail thread [1], We decided to move all the > app versions related static and dynamic information into database tables to > gain performance improvements. > This task mainly had 3 parts: > 1. Review existing 'afdb' database > - Incorporate app version related dynamic information to AF_VERSION table > - Enhance DB level performance by introducing Indexes and other feedbacks > > 2. Write a DAO layer for application version > - Define a new DAO class for application version > - Move all existing related CRUD operations to this class; Add new methods > and Edit existing methods to incorporate DB changes > - incorporate new DB changes to existing queries > - Optimize existing queries by using JOINS whenever applicable > - Write JUnit test cases > - Introducing a cache layer for further performance gain > > 3. Change all the methods to read from DB instead of App Version Rxt > Note: In order to make governance working, I kept writing the information > to app version rxt untouched and changed version related information > reading from rxt to DB. > > How I achieved the 1 st task > ------------------------------------------ > > Added new columns to AF_Version table to hold the dynamic data as depicted > below. > > - AUTO_BUILD > - AUTO_DEPLOY > - SUBDOMAIN > > Then conducted a database review with Sumedha. I have incorporated below > review points to enhance the database performance further: > > - Remove unused columns > - Put tenant_id into tables > - Rename the columns - IS_FORKED, PROMOTE_STATUS, USER_ID > - Define proper INDEXES to each table considering their usage > - Shrink the sizes of the columns > > The new EER diagram of 'afdb' database is attached herewith for your > reference. > New 'afdb' sql script with indexes and other modifications is in [5]. > > How I achieved the 2nd task > ------------------------------------------ > > Added new DAO class called "JDBCAppVersionDAO" [2]. Moved existing related > methods from JDBCApplicationDAO.java to this class and introduced new > methods for additional changes made in step 1. > Dimuthu worked on introducing two caches for versions and we used them in > all the applicable methods. > Modified existing queries in SQLConstants.java [3] and added JUnit tests > in JDBCAppVersionDAOTest.java [4]. > > [1] [Architecture][App Fac] Performance Improvements - Issues in enabling > GReg resource Indexing in App Factory > [2] > https://github.com/wso2/product-af/blob/master/modules/components/org.wso2.carbon.appfactory.core/src/main/java/org/wso2/carbon/appfactory/core/dao/JDBCAppVersionDAO.java > [3] > https://github.com/wso2/product-af/blob/master/modules/components/org.wso2.carbon.appfactory.core/src/main/java/org/wso2/carbon/appfactory/core/sql/SQLConstants.java > [4] > https://github.com/wso2/product-af/blob/master/modules/components/org.wso2.carbon.appfactory.core/src/test/java/org/wso2/carbon/appfactory/core/dao/JDBCAppVersionDAOTest.java > [5] > https://github.com/wso2/product-af/blob/master/modules/puppet-manifests/appfactory/modules/mysql/files/sql/afdb_mysql.sql > > > With all these modifications, I hope to see a significant performance gain > in UserHome and Build&Repo page. > The performance gain is yet to measure by comparing App Factory 2.1.0 beta > pack and 2.1.1 M1 pack(to be released soon). > -- > Thanks and Regards, > > Punnadi Gunarathna > Senior Software Engineer, > WSO2, Inc.; http://wso2.com <http://wso2> > Blog: http://hi-my-world.blogspot.com/ > Tel : 94 11 214 5345 > Fax :94 11 2145300 > > > > > > -- Thanks and Regards, Punnadi Gunarathna Senior Software Engineer, WSO2, Inc.; http://wso2.com <http://wso2> Blog: http://hi-my-world.blogspot.com/ Tel : 94 11 214 5345 Fax :94 11 2145300 <http://lalajisureshika.blogspot.com/>
_______________________________________________ Architecture mailing list Architecture@wso2.org https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture