Document Management SystemProject Type: software

Document Management System

Uploaded image for project: 'Document Management System'
  1. Document Management System
  2. DMS-1663

DMS transaction history : Dynamic trigger generation for history table

    Details

    • Company:
      All Clients/Multiple Clients

      Description

      DMS transaction history : Dynamic trigger generation for history table

      CC : Samir Rohan J Khandave

        Attachments

          Issue Links

          relates to

          New Feature - A new feature of the product, which has yet to be developed. DMS-343 DMS transaction history

          • High - Serious problem that could block progress.
          • Done

            Activity

            Ascending order - Click to sort in descending order
            Hide
            Permalink
            rushikesh.bane Rushikesh Dattatray Bane (Inactive) added a comment -

            Hi Sonali Farakate,

             

            The script for creating the History table along with triggers is completed.

            I have attached the script in this Jira. Also, there are inline comments for using that query.

            Create_Trigger_and_HistoryTable_MYSQL.sql

             

            -------------------------------------------------------------------------------------------------

            Naming Conventions  : 

            For eg : 
            Main Table - Communication
            For this below will be the outcome from the script :

            History table -  Communication_History
            Triggers Name - trg_Communication_insert, trg_Communication_update, trg_Communication_delete

            -------------------------------------------------------------------------------------------------

             

            Below are some changes that I have done and impediments that I'm facing  : 

            Changes Server Level :

            Disable - secure-file-priv option - As we are writing out queries into a file and not creating directly, this feature needs to be disabled. I have disabled it for local server and will disable for rest of environments as required.

             

            Impediments :

            Queries are written into a file: As of now, we cannot have the drop and exists statements in a dynamic query as it is not a feature in MYSQL.
            We have to change the path for writing out the queries in the file which will give us all the statements. If we want to pass the path as variable and parameter to SP, the query needs to be a prepared statement which will take time with analysis. 

             

            We can have this in the further scope of development. Please review the script and let me know if any queries.

             

            Regards,

            Rushikesh

            CC - Rohan J Khandave Samir Satya

             

             

            Show
            rushikesh.bane Rushikesh Dattatray Bane (Inactive) added a comment - Hi Sonali Farakate ,   The script for creating the History table along with triggers is completed. I have attached the script in this Jira. Also, there are inline comments for using that query. Create_Trigger_and_HistoryTable_MYSQL.sql   ------------------------------------------------------------------------------------------------- Naming Conventions   :  For eg :   Main Table - Communication For this below will be the outcome from the script : History table -  Communication_History Triggers Name - trg_Communication_insert, trg_Communication_update, trg_Communication_delete -------------------------------------------------------------------------------------------------   Below are some changes that I have done and impediments that I'm facing  :  Changes Server Level : Disable - secure-file-priv option - As we are writing out queries into a file and not creating directly, this feature needs to be disabled. I have disabled it for local server and will disable for rest of environments as required.   Impediments : Queries are written into a file: As of now, we cannot have the drop and exists statements in a dynamic query as it is not a feature in MYSQL. We have to change the path for writing out the queries in the file which will give us all the statements. If we want to pass the path as variable and parameter to SP, the query needs to be a prepared statement which will take time with analysis.    We can have this in the further scope of development. Please review the script and let me know if any queries.   Regards, Rushikesh CC - Rohan J Khandave Samir Satya    
            Hide
            Permalink
            sonali.farakate Sonali Farakate (Inactive) added a comment - - edited

            Hi Rohan J Khandave,

            I have updated the trigger creation script shared by Rushikesh Dattatray Bane.
            Changes Done:

            1. Added column ModifiedUserID
            2. Added SystemTime as per UTC
            3. Separated History table creation part.

            As per the new script I have created the triggers for following tables,

            1. address
            2. assignment
            3. company
            4. codelist
            5. companysourceproductmapping
            6. dmscustomfieldmapping
            7. dmsfields
            8. document
            9. email
            10. emailaction
            11. emailplaceholder
            12. phone
            13. sourceproductuser
            14. sourcesystemfieldlabel
            15. sourcesystemfields
            16. users
            17. person
            18. assignmentcustomattributedata
            19. personcustomattributedata
            • For each table 3 triggers are created with name like
            1. Trigger_tablename_Insert
            2. Trigger_tablename_Update
            3. Trigger_tablename_Delete
            • Stored procedure 'CreateTriggersForTable' will generate the triggers to insert data in history table.

            How to use SP CreateTriggersForTable?
            1. Inputs : dbName,tableName
            2. Change : Need to update the value of OUTFILE in SP as per the requirement(i.e. need to provide the folder path with filename.sql) 
            3. Output : This SP will create a file(with provided name in OUTFILE) in which we get the 3 triggers generated for history table.

            NOTE :
            Need to create 3 different files of these with above mentioned naming convention and check-in into '\DMS\Databases Objects\05_Triggers' folder.
            Need to generate these triggers each time on ALTER table and update the existing triggers with new.

            Thanks,
            Sonali Farakate

             CC : Samir 

            Show
            sonali.farakate Sonali Farakate (Inactive) added a comment - - edited Hi Rohan J Khandave , I have updated the trigger creation script shared by Rushikesh Dattatray Bane . Changes Done: Added column ModifiedUserID Added SystemTime as per UTC Separated History table creation part. As per the new script I have created the triggers for following tables, address assignment company codelist companysourceproductmapping dmscustomfieldmapping dmsfields document email emailaction emailplaceholder phone sourceproductuser sourcesystemfieldlabel sourcesystemfields users person assignmentcustomattributedata personcustomattributedata For each table 3 triggers are created with name like Trigger_tablename_Insert Trigger_tablename_Update Trigger_tablename_Delete Stored procedure 'CreateTriggersForTable' will generate the triggers to insert data in history table. How to use SP CreateTriggersForTable? 1. Inputs : dbName,tableName 2. Change : Need to update the value of OUTFILE in SP as per the requirement(i.e. need to provide the folder path with filename.sql)  3. Output : This SP will create a file(with provided name in OUTFILE) in which we get the 3 triggers generated for history table. NOTE : Need to create 3 different files of these with above mentioned naming convention and check-in into '\DMS\Databases Objects\05_Triggers' folder. Need to generate these triggers each time on ALTER table and update the existing triggers with new. Thanks, Sonali Farakate  CC : Samir  
            Hide
            Permalink
            priya.dhamande Priya Dhamande (Inactive) added a comment -

            No QA inputs required. So, marking jira done.

            Show
            priya.dhamande Priya Dhamande (Inactive) added a comment - No QA inputs required. So, marking jira done.

              People

              Assignee:
              priya.dhamande Priya Dhamande (Inactive)
              Reporter:
              sonali.farakate Sonali Farakate (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 28h
                  28h