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 2376) +++ /branches/amp_3_7_1/src/webui/webui/htdocs/new/src/cm/lib/postgres_db.py (working copy) @@ -265,80 +265,95 @@ )''' self.execute_sql(create_table_sql) - def create_table_file_type(self): - print('Creating table file_type...') - 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) - save_sql = '''INSERT INTO file_type(name) values ('command')''' - self.execute_sql_ingnore_exception(save_sql) - save_sql = '''INSERT INTO file_type(name) values ('diff')''' - self.execute_sql_ingnore_exception(save_sql) - save_sql = '''INSERT INTO file_type(name) values ('system')''' - self.execute_sql_ingnore_exception(save_sql) - save_sql = '''INSERT INTO file_type(name) values ('backup')''' - self.execute_sql_ingnore_exception(save_sql) - save_sql = '''INSERT INTO file_type(name) values ('customize')''' - self.execute_sql_ingnore_exception(save_sql) - save_sql = '''INSERT INTO file_type(name) values ('template')''' - self.execute_sql_ingnore_exception(save_sql) - save_sql = '''INSERT INTO file_type(name) values ('vs')''' - self.execute_sql_ingnore_exception(save_sql) - - def create_table_file(self): - print('Creating table file_list...') - # when type is backup or device , use 'modify_time' to save device name. - create_table_sql = ''' CREATE TABLE IF NOT EXISTS file_list( - name varchar(128) primary key, - create_time varchar(64) NOT NULL, - modify_time varchar(64) DEFAULT NULL, - type varchar(32) NOT NULL, - device_type varchar(32) DEFAULT NULL, - 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): - print('Creating table tar file...') - create_table_sql = ''' CREATE TABLE IF NOT EXISTS tar_file( + def create_table_file_type(self): + print('Creating table file_type...') + create_table_sql = ''' CREATE TABLE IF NOT EXISTS file_type( + name varchar(32) primary key, + extend jsonb + ); + ALTER TABLE file_list DROP CONSTRAINT file_list_type_fkey; + ALTER TABLE file_type DROP CONSTRAINT file_type_pkey; + ALTER TABLE file_type ADD CONSTRAINT file_type_unq_key UNIQUE (name); + ALTER TABLE file_type ADD COLUMN IF NOT EXISTS id SERIAL PRIMARY KEY; + ''' + self.execute_sql(create_table_sql) + save_sql = '''INSERT INTO file_type(name) values ('device')''' + self.execute_sql_ingnore_exception(save_sql) + save_sql = '''INSERT INTO file_type(name) values ('command')''' + self.execute_sql_ingnore_exception(save_sql) + save_sql = '''INSERT INTO file_type(name) values ('diff')''' + self.execute_sql_ingnore_exception(save_sql) + save_sql = '''INSERT INTO file_type(name) values ('system')''' + self.execute_sql_ingnore_exception(save_sql) + save_sql = '''INSERT INTO file_type(name) values ('backup')''' + self.execute_sql_ingnore_exception(save_sql) + save_sql = '''INSERT INTO file_type(name) values ('customize')''' + self.execute_sql_ingnore_exception(save_sql) + save_sql = '''INSERT INTO file_type(name) values ('template')''' + self.execute_sql_ingnore_exception(save_sql) + save_sql = '''INSERT INTO file_type(name) values ('vs')''' + self.execute_sql_ingnore_exception(save_sql) + + +def create_table_file_list(self): + print('Creating table file_list...') + # when type is backup or device , use 'modify_time' to save device name. + create_table_sql = ''' CREATE TABLE IF NOT EXISTS file_list( + name varchar(128) primary key, + create_time varchar(64) NOT NULL, + modify_time varchar(64) DEFAULT NULL, + type varchar(32) NOT NULL, + device_type varchar(32) DEFAULT NULL, + 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); + ALTER TABLE file_list DROP CONSTRAINT file_list_pkey; + ALTER TABLE file_list ADD COLUMN IF NOT EXISTS id SERIAL PRIMARY KEY; + ALTER TABLE file_list ADD CONSTRAINT file_list_unq_key UNIQUE (name); + ALTER TABLE file_list ADD CONSTRAINT file_list_type_fkey FOREIGN KEY (type) REFERENCES file_type(name) ON DELETE CASCADE; + ''' + self.execute_sql(create_table_sql) + + +def create_table_tar_file(self): + print('Creating table tar file...') + create_table_sql = ''' CREATE TABLE IF NOT EXISTS tar_file( id serial primary key , name varchar(64) NOT NULL, vsite_name varchar(64) DEFAULT NULL, extend jsonb ) ''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) - def create_table_proxy_cm(self): - print('Creating table proxy_cm...') - create_table_sql = '''CREATE TABLE IF NOT EXISTS proxy_cm ( + +def create_table_proxy_cm(self): + print('Creating table proxy_cm...') + create_table_sql = '''CREATE TABLE IF NOT EXISTS proxy_cm ( name varchar(32) NOT NULL, ip varchar(64) NOT NULL, PRIMARY KEY (name), extend jsonb )''' - self.execute_sql(create_table_sql) - save_sql = '''INSERT INTO proxy_cm(name, ip) values ('default', '0.0.0.0')''' - self.execute_sql_ingnore_exception(save_sql) + self.execute_sql(create_table_sql) + save_sql = '''INSERT INTO proxy_cm(name, ip) values ('default', '0.0.0.0')''' + self.execute_sql_ingnore_exception(save_sql) - def create_table_zone(self): - print('Creating table zone...') - create_table_sql = '''CREATE TABLE IF NOT EXISTS zone ( + +def create_table_zone(self): + print('Creating table zone...') + create_table_sql = '''CREATE TABLE IF NOT EXISTS zone ( name varchar(32) NOT NULL, PRIMARY KEY (name) )''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) + - def create_table_cm_role(self): - print('Creating table role...') - create_table_sql = ''' +def create_table_cm_role(self): + print('Creating table role...') + create_table_sql = ''' CREATE TABLE IF NOT EXISTS cm_role ( name varchar(64) primary key, description varchar(32) NOT NULL, @@ -349,11 +364,12 @@ extend jsonb ) ''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) + - def create_table_cm_acl_resource(self): - print('Creating table acl resource...') - create_table_sql = ''' +def create_table_cm_acl_resource(self): + print('Creating table acl resource...') + create_table_sql = ''' CREATE TABLE IF NOT EXISTS cm_acl_resource ( id serial primary key , group_name varchar(64) NOT NULL, @@ -365,11 +381,12 @@ extend jsonb ) ''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) + - def create_table_cm_vpn_resource(self): - print('Creating table vpn resource...') - create_table_sql = ''' +def create_table_cm_vpn_resource(self): + print('Creating table vpn resource...') + create_table_sql = ''' CREATE TABLE IF NOT EXISTS cm_vpn_resource ( id serial primary key , group_name varchar(64) NOT NULL, @@ -383,11 +400,12 @@ extend jsonb ) ''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) + - def create_table_audit_user(self): - print('Creating table audit_user...') - create_table_sql = ''' +def create_table_audit_user(self): + print('Creating table audit_user...') + create_table_sql = ''' CREATE TABLE IF NOT EXISTS audit_user( id serial primary key , user_name varchar(16) NOT NULL, @@ -397,11 +415,12 @@ ); ALTER TABLE audit_user ADD COLUMN IF NOT EXISTS lasted timestamp with time zone; ''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) + - def create_table_system_user(self): - print('Creating table system_user...') - create_table_sql = ''' +def create_table_system_user(self): + print('Creating table system_user...') + create_table_sql = ''' CREATE TABLE IF NOT EXISTS system_user( name_name varchar(64) primary key, level varchar(16) NOT NULL, @@ -410,21 +429,23 @@ extend jsonb ) ''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) - def create_table_extension_log(self): - print('Creating table extension log...') - create_table_sql = '''CREATE TABLE IF NOT EXISTS ext_log ( + +def create_table_extension_log(self): + print('Creating table extension log...') + create_table_sql = '''CREATE TABLE IF NOT EXISTS ext_log ( name varchar(64) NOT NULL, action varchar(20) NOT NULL, time varchar(30) NOT NULL, result varchar(20) NOT NULL )''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) - def create_table_user_authorization(self): - print('Creating table user_authorization...') - create_table_sql = '''CREATE TABLE IF NOT EXISTS user_authorization ( + +def create_table_user_authorization(self): + print('Creating table user_authorization...') + create_table_sql = '''CREATE TABLE IF NOT EXISTS user_authorization ( id serial primary key, username varchar(128) NOT NULL UNIQUE, auth varchar(8192) DEFAULT NULL @@ -437,11 +458,12 @@ (role_id) REFERENCES role (id); UPDATE user_authorization set user_type='Super Admin' where id=1; ''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) - def create_table_operation_log(self): - print('Creating table operation_log...') - create_table_sql = '''CREATE TABLE IF NOT EXISTS operation_log ( + +def create_table_operation_log(self): + print('Creating table operation_log...') + create_table_sql = '''CREATE TABLE IF NOT EXISTS operation_log ( id serial primary key, username varchar(128) NOT NULL, client_ip varchar(64) NOT NULL, @@ -450,40 +472,44 @@ module varchar(20), operation varchar(1024) )''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) - def create_table_schedule_backup_all(self): - print('Creating table schedule_backup_all...') - create_table_sql = '''CREATE TABLE IF NOT EXISTS schedule_backup_all ( + +def create_table_schedule_backup_all(self): + print('Creating table schedule_backup_all...') + create_table_sql = '''CREATE TABLE IF NOT EXISTS schedule_backup_all ( task_name varchar(128) NOT NULL UNIQUE, device_name varchar(128) NOT NULL, interval INTEGER NOT NULL, unit varchar(10) NOT NULL )''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) - def create_log_settings_table(self): - print('Creating table log_settings...') - create_table_sql = ''' CREATE TABLE IF NOT EXISTS log_settings( + +def create_log_settings_table(self): + print('Creating table log_settings...') + create_table_sql = ''' CREATE TABLE IF NOT EXISTS log_settings( enable integer default 1, level INTEGER default 2 )''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) - def create_log_host_table(self): - print('Creating table log_host...') - # protocol: 0 - TCP, 1 - UDP - create_table_sql = ''' CREATE TABLE IF NOT EXISTS log_host( + +def create_log_host_table(self): + print('Creating table log_host...') + # protocol: 0 - TCP, 1 - UDP + create_table_sql = ''' CREATE TABLE IF NOT EXISTS log_host( host varchar(128), port integer NOT NULL, protocol integer default 1, PRIMARY KEY (host, port, protocol) )''' - self.execute_sql(create_table_sql) + self.execute_sql(create_table_sql) - def create_table_role(self): - print('Creating role table...') - create_table_query = ''' + +def create_table_role(self): + print('Creating role table...') + create_table_query = ''' CREATE TABLE IF NOT EXISTS role( id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ), role_name varchar(128), @@ -491,11 +517,12 @@ CONSTRAINT role_primary_key PRIMARY KEY (id) ) ''' - self.execute_sql(create_table_query) + self.execute_sql(create_table_query) + - def create_table_role_device_group(self): - print('Creating role_device_group table...') - create_table_query = ''' +def create_table_role_device_group(self): + print('Creating role_device_group table...') + create_table_query = ''' CREATE TABLE IF NOT EXISTS role_device_group ( id serial primary key, role_id integer, @@ -504,7 +531,7 @@ FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE CASCADE, FOREIGN KEY (device_group_name) REFERENCES device_group(name) ON DELETE CASCADE );''' - self.execute_sql(create_table_query) + self.execute_sql(create_table_query) if __name__ == '__main__': @@ -525,7 +552,7 @@ db.create_table_update() db.create_table_task() db.create_table_file_type() - db.create_table_file() + db.create_table_file_list() db.create_table_audit_user() db.create_table_extension_log() db.create_table_role() @@ -537,4 +564,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