Hi All,

As per discussion with Samir, Moving display web page for history , business logic component removed from sprint 6. These can be taken in future.

Thanks,
Swapnil P.

We have completed Table creation and maintenance script.
This script will automate and maintain ongoing process of maintaining history for DMS.
Currently, we are analyzing how to maintain the history data without triggers.

Hi Samir

We have created the sp and related history tables. Please review the same

Hello Sonali Farakate,

As per discussion with Samir , we can go with triggers for maintaining history of tables.

Below tasks needs to consider into analysis of this ticket.

1. List of tables to create history table.
2. Procedure to create / update history table according to main table.
3. Procedure to create triggers.
4.Common logic to fetch history data.

Thanks,
RohanK

CC – Samir , Satya , Ganesh Sadawarte , Ramya Tantry , Harshveer Singh

Hi Samir,

I have identified the total tables from the database. After discussion with Rohan J Khandave, we will finalize the exact count of the tables for which we need to create history table. 
Once this discussion is done, I will update the ETA for this ticket.

Thanks,
Sonali Farakate

Hi Rohan J Khandave,

Please find attached sheet for the list of tables on production for which we need to create the history table.DMS-343_HistoryTableAnalysis.xls
Please review the same.
I have started analysis of stored procedure to generate triggers.

Thanks,
Sonali Farakate 

Changes done in following stored procedures for History tables

1. DMS_SP_Add_Column
2. DMS_SP_Drop_Column
3. DMS_SP_Modify_Column_DataType
4. DMS_SP_Modify_Column_Rename
5. HistoryTableCreation

Please find following sheet 16July19 - DMS-343_HistoryTableAnalysis.xls for the tables which does not contain the columns 'LastUpdate_dateTime' and 'LastUpdatedby_UserID'.

Thanks,
Sonali Farakate

CC : Rohan J Khandave

MOM:

• Create history tables of the table with columns 'LastUpdate_dateTime' and 'LastUpdatedby_UserID'

1. address
2. codelist
3. companysourceproductmapping
4. dmscustomfieldmapping
5. dmsfields
6. document
7. email
8. emailaction
9. emailplaceholder
10. phone
11. sentform
12. sourceproductuser
13. sourcesystemfieldlabel
14. sourcesystemfields
15. users

• Need to add columns 'LastUpdate_dateTime' and 'LastUpdatedby_UserID' in following tables,

1. assignment
2. assignmentcustomattributedata
3. attachment
4. attachmentdetails
5. attachmentmapping
6. authenticationtokendetails
7. communicationdetails
8. company
9. documentrouting
10. passwordcredentials
11. passwordorder
12. person
13. personcustomattributedata
14. systemutilization
15. useractionpermissions
16. usercompanymapping
17. usernamecredentials
18. usernameorder
    We will focus on candidate level tables first for adding above mentioned columns then the remaining.

• Need not to dump old data of main table into history table.
• Column name consistency will be maintained while adding new columns.
• Rushikesh Dattatray Bane will write the stored procedure for generating triggers dynamically.

Thanks,
Sonali Farakate.

CC : Samir, Rohan J Khandave

Created following tables on local database:

1. address_history
2. assignment_history
3. codelist_history
4. company_history
5. companysourceproductmapping_history
6. dmscustomfieldmapping_history
7. dmsfields_history
8. document_history
9. email_history
10. emailaction_history
11. emailplaceholder_history
12. person_history
13. phone_history
14. sourceproductuser_history
15. sourcesystemfieldlabel_history
16. sourcesystemfields_history
17. users_history

Following are 13 databases where need to add columns 'LastUpdate_dateTime' and 'LastUpdatedby_UserID',

1. Attachment
2. attachmentdetails
3. Attachmentmapping
4. authenticationtokendetails
5. CommunicationDetail
6. documentrouting
7. Passwordcredentials
8. PasswordOrder
9. systemutilization
10. useractionpermissions
11. usercompanymapping
12. usernamecredentials
13. usernameorder

Thanks,
Sonali Farakate.

CC : Rohan J Khandave, Ramya Tantry

Updated column names(for consistency) of following tables,

For following three tables we are unable to create History table due to ERROR : "Error Code: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs"
- sentform
- assignmentcustomattributedata
- personcustomattributedata

To resolve this error we need to change data type of some columns in History table of it. Hence currently kept history table creation of these tables on hold.

Thanks,
Sonali Farakate
CC : Samir,Rohan J Khandave

No Testing input required from QA end. So, marking jira done.

Sachin Hingole Hrishikesh Deshpande Samir Rohan J Khandave