Index: /branches/amp_3_7_1/scripts/avxapps_startup.sh =================================================================== --- /branches/amp_3_7_1/scripts/avxapps_startup.sh (revision 2410) +++ /branches/amp_3_7_1/scripts/avxapps_startup.sh (working copy) @@ -70,6 +70,8 @@ # /usr/bin/python /ca/webui/htdocs/new/src/clean_elastic.py crontab # /usr/bin/python /ca/bin/clean_oper_log.py crontab /usr/bin/python /ca/webui/htdocs/new/src/cm/lib/postgres_db.py + /usr/bin/python /ca/webui/htdocs/new/src/cm/upgrade/3_7_1/update_db_schema.py + /usr/bin/python /ca/webui/htdocs/new/src/cm/upgrade/3_7_1/update_db_data.py rm -rf /var/run/webui_server.pem sed -i -e 's|SELINUX=enforcing|SELINUX=disabled|' /etc/selinux/config Index: /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/lib/postgres_db.py =================================================================== --- /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/lib/postgres_db.py (revision 2410) +++ /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/lib/postgres_db.py (working copy) @@ -243,7 +243,7 @@ CREATE TABLE IF NOT EXISTS device_group ( name character varying(128) COLLATE pg_catalog."default" NOT NULL, CONSTRAINT device_group_pkey PRIMARY KEY (name) - )''' + );''' self.execute_sql(create_table_sql) def create_table_ha_cluster(self): @@ -270,7 +270,7 @@ create_table_sql = ''' CREATE TABLE IF NOT EXISTS file_type( name varchar(32) primary key, extend jsonb - ) ''' + );''' self.execute_sql(create_table_sql) save_sql = '''INSERT INTO file_type(name) values ('device')''' self.execute_sql_ingnore_exception(save_sql) @@ -301,9 +301,7 @@ comment text, FOREIGN KEY (type) REFERENCES file_type(name) ON DELETE CASCADE, extend jsonb - ); - ALTER TABLE file_list ADD COLUMN IF NOT EXISTS device_id varchar(64); - ''' + );''' self.execute_sql(create_table_sql) def create_table_tar_file(self): @@ -421,7 +419,6 @@ result varchar(20) NOT NULL ); ALTER TABLE ext_log ADD COLUMN IF NOT EXISTS id BIGSERIAL; - ALTER TABLE ext_log DROP CONSTRAINT ext_log_pkey; ALTER TABLE ext_log ADD CONSTRAINT ext_log_pkey PRIMARY KEY (id); ''' self.execute_sql(create_table_sql) @@ -541,4 +538,4 @@ db.create_table_config_template() db.create_table_device_config_template() db.create_table_role_device_group() - db.close() + db.close() \ No newline at end of file Index: /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/__init__.py =================================================================== --- /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/__init__.py (revision 0) +++ /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/__init__.py (working copy) @@ -0,0 +1 @@ +__all__ = ["update_db_schema", "update_db_data"] \ No newline at end of file Index: /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/update_db_data.py =================================================================== --- /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/update_db_data.py (revision 0) +++ /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/update_db_data.py (working copy) @@ -0,0 +1,55 @@ +import sys +import os + +# Get the current file's directory +current_dir = os.path.dirname(os.path.abspath(__file__)) + +# Move two levels up +two_levels_up = os.path.abspath(os.path.join(current_dir, "../../")) + +# Add the directory to sys.path +sys.path.append(two_levels_up) + +# Now you can import the module +from lib.postgres_db import DB + + +def update_device_group_id(): + db = DB.get_connected_db() + update_device_id_sql = ''' + WITH device_group_cte AS ( + SELECT name, id + FROM device_group + ) + UPDATE role_device_group + SET device_group_id = device_group_cte.id + FROM device_group_cte + WHERE role_device_group.device_group_name = device_group_cte.name + AND role_device_group.device_group_id IS NULL; + ''' + + db.execute_sql(update_device_id_sql) + db.close() + + +def update_file_type(): + db = DB.get_connected_db() + update_file_type_sql = ''' + WITH file_type_cte AS ( + SELECT name, id + FROM file_type + ) + UPDATE file_list + SET file_type_id = file_type_cte.id + FROM file_type_cte + WHERE file_list.file_type_name = file_type_cte.name + AND file_list.file_type_id IS NULL; + ''' + + db.execute_sql(update_file_type_sql) + db.close() + + +if __name__ == '__main__': + update_device_group_id() + update_file_type() \ No newline at end of file Index: /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/update_db_schema.py =================================================================== --- /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/update_db_schema.py (revision 0) +++ /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/3_7_1/update_db_schema.py (working copy) @@ -0,0 +1,50 @@ +import sys +import os + +# Get the current file's directory +current_dir = os.path.dirname(os.path.abspath(__file__)) + +# Move two levels up +two_levels_up = os.path.abspath(os.path.join(current_dir, "../../")) + +# Add the directory to sys.path +sys.path.append(two_levels_up) + +# Now you can import the module +from lib.postgres_db import DB + + +def alter_table_file_type_list(): + db = DB.get_connected_db() + print("alter_table_file_type_list: {}".format(db)) + update_device_grp_query = '''ALTER TABLE device_group + ADD COLUMN IF NOT EXISTS id SERIAL; + ALTER TABLE role_device_group DROP CONSTRAINT IF EXISTS role_device_group_device_group_name_fkey; + ALTER TABLE device_group DROP CONSTRAINT IF EXISTS device_group_pkey CASCADE; + ALTER TABLE device_group ADD CONSTRAINT device_group_id_pkey PRIMARY KEY (id); + ALTER TABLE role_device_group ADD COLUMN IF NOT EXISTS device_group_id int; + ALTER TABLE role_device_group ADD CONSTRAINT role_dev_grp_name_fkey FOREIGN KEY(device_group_id) + REFERENCES device_group(id) ON DELETE CASCADE;''' + + update_file_type_list_query = '''ALTER TABLE file_type + ADD COLUMN IF NOT EXISTS id SERIAL; + ALTER TABLE file_list ADD COLUMN IF NOT EXISTS device_id varchar(64); + ALTER TABLE file_list DROP CONSTRAINT IF EXISTS file_list_type_fkey; + ALTER TABLE file_type DROP CONSTRAINT IF EXISTS file_type_pkey CASCADE; + ALTER TABLE file_type ADD CONSTRAINT file_type_id_pkey PRIMARY KEY (id); + ALTER TABLE file_list RENAME COLUMN type TO file_type_name; + ALTER TABLE file_list ADD COLUMN IF NOT EXISTS file_type_id int; + ALTER TABLE file_list ADD CONSTRAINT file_list_type_fkey FOREIGN KEY(file_type_id) + REFERENCES file_type(id) ON DELETE CASCADE; ''' + try: + db.execute_sql(update_device_grp_query) + db.execute_sql(update_file_type_list_query) + except Exception as e: + # Unable to import oper_log. Using print for now + print('Exception while executing update queries:{}'.format(e.message)) + finally: + db.close() + + +if __name__ == '__main__': + alter_table_file_type_list() \ No newline at end of file Index: /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/__init__.py (added) =================================================================== --- /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/__init__.py (revision 0) +++ /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/upgrade/__init__.py (revision 0)