Bug 958: Fix the Postgres database table issues and its relevant code changes device_group and role_device_group

Review Request #372 — Created July 25, 2024 and submitted

Information
apoorva.sn
AMP
amp_3_7_1
958
Reviewers
pmurugaiyan, prajesh, shuinvy 
Problem


Fix the Postgres database table issues and its relevant code changes 

1.device_group and role_device_group

2. file_type and file_list


Solution


Added id column to device_group and make it the primary key for the table

Added new column device_group_id to role_device_group table and make it the foreign key referring to id from device group.

Since the existing entries in the role_device_group will not have any value for device_group_id column added script to populate the value for that column during the upgrade process

Also made similar changes in file_type and file_list as above
Tested the changes and shared screenshots of the tables before and after upgrade

Description From Last Updated

Can we call it as update_postgres_dml.py (where we can group all the postgres data releted upgrade changes). As we grow …

 pmurugaiyanpmurugaiyan

Duplicate code - can we import and reuse the existing DB class from postgres_db.py?

 pmurugaiyanpmurugaiyan

The SQL queries are expensive here (3 queries) and 2 loops also used. Can we make it as a single …

 pmurugaiyanpmurugaiyan

Why need the build version in this filename? Can it be similar to postgres_db.py file!

 pmurugaiyanpmurugaiyan

Nitpick: remove extra spaces (marked red) from all places.

 prajeshprajesh

do we need log in cse of failure ?

 prajeshprajesh
apoorva.sn
pmurugaiyan
pmurugaiyan 
  1. 
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    2. 

















  2. 
 
    
  
  
    
  
 

   
    
    
    



    
 
    
  
    
   
   
    
    
     
     
     
     
     
    
    Show all issues
   
  
    
 
    

    



    
    Can we call it as update_postgres_dml.py (where we can group all the postgres data releted upgrade changes). As we grow we don't have to maintain individual files for each of those tables.
    
  
    
 
    

 
    
  


    
 
    
 
      

  


    1. 
 
 
      
 
      
  
 
      
 
      
  
      renamed to update_postgres_3_7_1.py
      
 
      

      2. 


 
    


    

 
    

    3. 






  3. 
 
    
  
  
    
  
 

   
    
    
    



    
 
    
  
    
   
   
    
    
     
     
     
     
     
    
    Show all issues
   
  
    
 
    

    



    
    Duplicate code - can we import and reuse the existing DB class from postgres_db.py?
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    4. 






  4. 
 
    
  
  
    
  
 

   
    
    
    



    
 
    
  
    
   
   
    
    
     
     
     
     
     
    
    Show all issues
   
  
    
 
    

    



    
    The SQL queries are expensive here (3 queries) and 2 loops also used. Can we make it as a single query to update the role_device_group table with the help of sub queries?
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    5. 




  5. 
 
    
  
    
   
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    6. 




  

 
















 



 

  

   




apoorva.sn




   

   

  

 



 















 



 

  

   




apoorva.sn




   

   

  

 



 















 





 

  

   




pmurugaiyan




   

   







  

 



 

  


   

   

    
 
pmurugaiyan


    
   


  

  

  



    




  1. 
 
    
  
    
   
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    2. 

















  2. 
 
    
  
  
    
  
 

   
    
    
    



    
 
    
  
    
   
   
    
    
     
     
     
     
     
    
    Show all issues
   
  
    
 
    

    



    
    Why need the build version in this filename? Can it be similar to postgres_db.py file!
    
  
    
 
    

 
    
  


    
 
    
 
      

  


    1. 
 
 
      
 
      
  
 
      
 
      
  
      it is the release version, we can just have upgrade files according to release.
      
 
      

      2. 



    2. 
 
 
      
 
      
  
 
      
 
      
  
      But why its different from postgres_db.py!
      
postgres_db.py - has all the changes for DDL in a single file (DDL)
      
update_posgres_<version>.py - has to be versioned based on release with separate (DML) - also file can we can change to more meaningful names, this gives a impression like we are updating the postgres package itself.
      

      Can we apply the same behavior for both the database related operations!
      
 
      

      3. 



    3. 
 
 
      
 
      
  
 
      
 
      
  
      created 3_7_1 folder under upgrade and added file update_db_schema and update_db_data for updating the columns and data in the tables respectively
      
 
      

      4. 


 
    


    

 
    

    3. 




  3. 
 
    
  
    
   
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    4. 




  

 
















 





 

  

   




shuinvy




   

   







  

 



 

  


   

   

    
 
shuinvy


    
   


  

  

  



    




  1. 
 
    
  
    
   
    Ship It!
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    2. 















  2. 
 
    
  
    
   
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    3. 




  

 
















 



 

  

   




apoorva.sn




   

   

  

 



 















 





 

  

   




prajesh




   

   







  

 



 

  


   

   

    
 
prajesh


    
   


  

  

  



    




  1. 
 
    
  
    
   
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    2. 

















  2. 
 
    
  
  
    
  
 

   
    
    
    



    
 
    
  
    
   
   
    
    
     
     
     
     
     
    
    Show all issues
   
  
    
 
    

    



    
    Nitpick: remove extra spaces (marked red) from all places.
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    3. 






  3. 
 
    
  
  
    
  
 

   
    
    
    



    
 
    
  
    
   
   
    
    
     
     
     
     
     
    
    Show all issues
   
  
    
 
    

    



    
    do we need log in cse of failure ?
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    4. 




  4. 
 
    
  
    
   
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    5. 




  

 
















 



 

  

   




apoorva.sn




   

   

  

 



 















 



 

  

   




apoorva.sn




   

   

  

 



 















 





 

  

   




shuinvy




   

   







  

 



 

  


   

   

    
 
shuinvy


    
   


  

  

  



    




  1. 
 
    
  
    
   
    Ship It!
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    2. 















  2. 
 
    
  
    
   
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    3. 




  

 
















 





 

  

   




prajesh




   

   







  

 



 

  


   

   

    
 
prajesh


    
   


  

  

  



    




  1. 
 
    
  
    
   
    Ship It!
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    2. 















  2. 
 
    
  
    
   
    
  
    
 
    

 
    
  


    
 
    
 
      

  

 
    


    

 
    

    3. 




  

 
















 



 

  

   




apoorva.sn




   

   

  

 



 

  


   

   

    
 Review request changed

    
   


  

  

  






 

  Status:
  

  Closed (submitted)

  
 







 


 










  

 









     

    

   

  

  

   
   Loading...