codekingpro/portable-devtools
114k
1##########################################################################2#3# pgAdmin 4 - PostgreSQL Tools4#5# Copyright (C) 2013 - 2024, The pgAdmin Development Team6# This software is released under the PostgreSQL Licence7#8##########################################################################9 10""" Implements Table Node """11 12import json13import re14 15import pgadmin.browser.server_groups.servers.databases as database16from flask import render_template, request, jsonify, url_for, current_app17from flask_babel import gettext18from pgadmin.browser.server_groups.servers.databases.schemas.utils \19 import SchemaChildModule, DataTypeReader, VacuumSettings20from pgadmin.browser.server_groups.servers.utils import parse_priv_to_db21from pgadmin.utils.ajax import make_json_response, internal_server_error, \22 make_response as ajax_response, gone23from .utils import BaseTableView24from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry25from pgadmin.browser.server_groups.servers.databases.schemas.tables.\26 constraints.foreign_key import utils as fkey_utils27from .schema_diff_table_utils import SchemaDiffTableCompare28from pgadmin.browser.server_groups.servers.databases.schemas.tables.\29 columns import utils as column_utils30from pgadmin.browser.server_groups.servers.databases.schemas.tables.\31 constraints.exclusion_constraint import utils as exclusion_utils32from pgadmin.utils.exception import ExecuteError33 34 35class TableModule(SchemaChildModule):36 """37 class TableModule(SchemaChildModule)38 39 A module class for Table node derived from SchemaChildModule.40 41 Methods:42 -------43 * __init__(*args, **kwargs)44 - Method is used to initialize the Table and it's base module.45 46 * get_nodes(gid, sid, did, scid, tid)47 - Method is used to generate the browser collection node.48 49 * node_inode()50 - Method is overridden from its base class to make the node as leaf node.51 52 * script_load()53 - Load the module script for schema, when any of the server node is54 initialized.55 """56 _NODE_TYPE = 'table'57 _COLLECTION_LABEL = gettext("Tables")58 59 def __init__(self, *args, **kwargs):60 """61 Method is used to initialize the TableModule and it's base module.62 63 Args:64 *args:65 **kwargs:66 """67 super().__init__(*args, **kwargs)68 self.max_ver = None69 self.min_ver = None70 71 def get_nodes(self, gid, sid, did, scid):72 """73 Generate the collection node74 """75 if self.has_nodes(sid, did, scid=scid,76 base_template_path=BaseTableView.BASE_TEMPLATE_PATH):77 yield self.generate_browser_collection_node(scid)78 79 @property80 def script_load(self):81 """82 Load the module script for database, when any of the database node is83 initialized.84 """85 return database.DatabaseModule.node_type86 87 @property88 def csssnippets(self):89 """90 Returns a snippet of css to include in the page91 """92 snippets = [93 render_template(94 self._COLLECTION_CSS,95 node_type=self.node_type,96 ),97 render_template(98 self._NODE_CSS,99 node_type=self.node_type,100 ),101 render_template(102 self._NODE_CSS,103 node_type='table',104 file_name='table-inherited',105 ),106 render_template(107 self._NODE_CSS,108 node_type='table',109 file_name='table-inherits',110 ),111 render_template(112 self._NODE_CSS,113 node_type='table',114 file_name='table-multi-inherit',115 ),116 ]117 118 for submodule in self.submodules:119 snippets.extend(submodule.csssnippets)120 121 return snippets122 123 def register(self, app, options):124 """125 Override the default register function to automagically register126 sub-modules at once.127 """128 from .columns import blueprint as module129 self.submodules.append(module)130 131 from .compound_triggers import blueprint as module132 self.submodules.append(module)133 134 from .constraints import blueprint as module135 self.submodules.append(module)136 137 from .indexes import blueprint as module138 self.submodules.append(module)139 140 from .partitions import blueprint as module141 self.submodules.append(module)142 143 from .row_security_policies import blueprint as module144 self.submodules.append(module)145 146 from .rules import blueprint as module147 self.submodules.append(module)148 149 from .triggers import blueprint as module150 self.submodules.append(module)151 152 super().register(app, options)153 154 155blueprint = TableModule(__name__)156 157 158class TableView(BaseTableView, DataTypeReader, SchemaDiffTableCompare):159 """160 This class is responsible for generating routes for Table node161 162 Methods:163 -------164 * __init__(**kwargs)165 - Method is used to initialize the TableView and it's base view.166 167 * list()168 - This function is used to list all the Table nodes within that169 collection.170 171 * nodes()172 - This function will used to create all the child node within that173 collection, Here it will create all the Table node.174 175 * properties(gid, sid, did, scid, tid)176 - This function will show the properties of the selected Table node177 178 * create(gid, sid, did, scid)179 - This function will create the new Table object180 181 * update(gid, sid, did, scid, tid)182 - This function will update the data for the selected Table node183 184 * delete(gid, sid, scid, tid):185 - This function will drop the Table object186 187 * truncate(gid, sid, scid, tid):188 - This function will truncate table object189 190 * set_trigger(gid, sid, scid, tid):191 - This function will enable/disable trigger(s) on table object192 193 * reset(gid, sid, scid, tid):194 - This function will reset table object statistics195 196 * msql(gid, sid, did, scid, tid)197 - This function is used to return modified SQL for the selected198 Table node199 200 * get_sql(did, scid, tid, data)201 - This function will generate sql from model data202 203 * sql(gid, sid, did, scid, tid):204 - This function will generate sql to show it in sql pane for the205 selected Table node.206 207 * dependency(gid, sid, did, scid, tid):208 - This function will generate dependency list show it in dependency209 pane for the selected Table node.210 211 * dependent(gid, sid, did, scid, tid):212 - This function will generate dependent list to show it in dependent213 pane for the selected node.214 215 * get_types(self, gid, sid, did, scid)216 - This function will return list of types available for columns node217 via AJAX response218 219 * get_oftype(self, gid, sid, did, scid, tid)220 - This function will return list of types available for table node221 via AJAX response222 223 * get_inherits(self, gid, sid, did, scid, tid)224 - This function will return list of tables availablefor inheritance225 via AJAX response226 227 * get_relations(self, gid, sid, did, scid, tid)228 - This function will return list of tables available for like/relation229 via AJAX response230 231 * get_columns(gid, sid, did, scid, foid=None):232 - Returns the Table Columns.233 234 * get_table_vacuum(gid, sid, did, scid=None, tid=None):235 - Fetch the default values for table auto-vacuum236 237 * get_toast_table_vacuum(gid, sid, did, scid=None, tid=None)238 - Fetch the default values for toast table auto-vacuum239 240 * get_index_constraint_sql(self, did, tid, data):241 - This function will generate modified sql for index constraints242 (Primary Key & Unique)243 244 * select_sql(gid, sid, did, scid, foid):245 - Returns sql for Script246 247 * insert_sql(gid, sid, did, scid, foid):248 - Returns sql for Script249 250 * update_sql(gid, sid, did, scid, foid):251 - Returns sql for Script252 253 * delete_sql(gid, sid, did, scid, foid):254 - Returns sql for Script255 256 * compare(**kwargs):257 - This function will compare the table nodes from two258 different schemas.259"""260 261 node_type = blueprint.node_type262 263 parent_ids = [264 {'type': 'int', 'id': 'gid'},265 {'type': 'int', 'id': 'sid'},266 {'type': 'int', 'id': 'did'},267 {'type': 'int', 'id': 'scid'}268 ]269 ids = [270 {'type': 'int', 'id': 'tid'}271 ]272 273 operations = dict({274 'obj': [275 {'get': 'properties', 'delete': 'delete', 'put': 'update'},276 {'get': 'list', 'post': 'create', 'delete': 'delete'}277 ],278 'delete': [{'delete': 'delete'}, {'delete': 'delete'}],279 'children': [{'get': 'children'}],280 'nodes': [{'get': 'node'}, {'get': 'nodes'}],281 'sql': [{'get': 'sql'}],282 'msql': [{'get': 'msql'}, {'get': 'msql'}],283 'stats': [{'get': 'statistics'}, {'get': 'statistics'}],284 'dependency': [{'get': 'dependencies'}],285 'dependent': [{'get': 'dependents'}],286 'get_oftype': [{'get': 'get_oftype'}, {'get': 'get_oftype'}],287 'get_inherits': [{'get': 'get_inherits'}, {'get': 'get_inherits'}],288 'get_relations': [{'get': 'get_relations'}, {'get': 'get_relations'}],289 'truncate': [{'put': 'truncate'}],290 'reset': [{'delete': 'reset'}],291 'set_trigger': [{'put': 'enable_disable_triggers'}],292 'get_types': [{'get': 'types'}, {'get': 'types'}],293 'get_columns': [{'get': 'get_columns'}, {'get': 'get_columns'}],294 'get_table_vacuum': [{}, {'get': 'get_table_vacuum'}],295 'get_toast_table_vacuum': [{}, {'get': 'get_toast_table_vacuum'}],296 'all_tables': [{}, {'get': 'get_all_tables'}],297 'get_access_methods': [{}, {'get': 'get_access_methods'}],298 'get_table_access_methods': [{}, {'get': 'get_table_access_methods'}],299 'get_oper_class': [{}, {'get': 'get_oper_class'}],300 'get_operator': [{}, {'get': 'get_operator'}],301 'get_attach_tables': [302 {'get': 'get_attach_tables'},303 {'get': 'get_attach_tables'}],304 'select_sql': [{'get': 'select_sql'}],305 'insert_sql': [{'get': 'insert_sql'}],306 'update_sql': [{'get': 'update_sql'}],307 'delete_sql': [{'get': 'delete_sql'}],308 'count_rows': [{'get': 'count_rows'}],309 'compare': [{'get': 'compare'}, {'get': 'compare'}],310 'get_op_class': [{'get': 'get_op_class'}, {'get': 'get_op_class'}],311 })312 313 @BaseTableView.check_precondition314 def list(self, gid, sid, did, scid):315 """316 This function is used to list all the table nodes within that317 collection.318 319 Args:320 gid: Server group ID321 sid: Server ID322 did: Database ID323 scid: Schema ID324 325 Returns:326 JSON of available table nodes327 """328 SQL = render_template(329 "/".join([self.table_template_path, self._PROPERTIES_SQL]),330 did=did, scid=scid,331 datlastsysoid=self._DATABASE_LAST_SYSTEM_OID332 )333 status, res = self.conn.execute_dict(SQL)334 335 if not status:336 return internal_server_error(errormsg=res)337 return ajax_response(338 response=res['rows'],339 status=200340 )341 342 def get_icon_css_class(self, table_info, default_val='icon-table'):343 if ('is_inherits' in table_info and344 table_info['is_inherits'] > '0') or \345 ('coll_inherits' in table_info and346 len(table_info['coll_inherits']) > 0):347 348 if ('is_inherited' in table_info and349 table_info['is_inherited'] > '0')\350 or ('relhassubclass' in table_info and351 table_info['relhassubclass']):352 default_val = 'icon-table-multi-inherit'353 else:354 default_val = 'icon-table-inherits'355 elif ('is_inherited' in table_info and356 table_info['is_inherited'] > '0')\357 or ('relhassubclass' in table_info and358 table_info['relhassubclass']):359 default_val = 'icon-table-inherited'360 361 return super().\362 get_icon_css_class(table_info, default_val)363 364 @BaseTableView.check_precondition365 def node(self, gid, sid, did, scid, tid):366 """367 This function is used to list all the table nodes within that368 collection.369 370 Args:371 gid: Server group ID372 sid: Server ID373 did: Database ID374 scid: Schema ID375 tid: Table ID376 377 Returns:378 JSON of available table nodes379 """380 res = []381 SQL = render_template(382 "/".join([self.table_template_path, self._NODES_SQL]),383 scid=scid, tid=tid384 )385 status, rset = self.conn.execute_2darray(SQL)386 if not status:387 return internal_server_error(errormsg=rset)388 if len(rset['rows']) == 0:389 return gone(gettext("Could not find the table."))390 391 table_information = rset['rows'][0]392 icon = self.get_icon_css_class(table_information)393 394 res = self.blueprint.generate_browser_node(395 table_information['oid'],396 scid,397 table_information['name'],398 icon=icon,399 tigger_count=table_information['triggercount'],400 has_enable_triggers=table_information['has_enable_triggers'],401 is_partitioned=self.is_table_partitioned(table_information)402 )403 404 return make_json_response(405 data=res,406 status=200407 )408 409 @BaseTableView.check_precondition410 def nodes(self, gid, sid, did, scid):411 """412 This function is used to list all the table nodes within that413 collection.414 415 Args:416 gid: Server group ID417 sid: Server ID418 did: Database ID419 scid: Schema ID420 421 Returns:422 JSON of available table nodes423 """424 res = []425 SQL = render_template(426 "/".join([self.table_template_path, self._NODES_SQL]),427 scid=scid428 )429 status, rset = self.conn.execute_2darray(SQL)430 if not status:431 return internal_server_error(errormsg=rset)432 433 for row in rset['rows']:434 icon = self.get_icon_css_class(row)435 436 res.append(437 self.blueprint.generate_browser_node(438 row['oid'],439 scid,440 row['name'],441 icon=icon,442 tigger_count=row['triggercount'],443 has_enable_triggers=row['has_enable_triggers'],444 is_partitioned=self.is_table_partitioned(row),445 description=row['description']446 ))447 448 return make_json_response(449 data=res,450 status=200451 )452 453 @BaseTableView.check_precondition454 def get_all_tables(self, gid, sid, did, scid, tid=None):455 """456 Args:457 gid: Server Group Id458 sid: Server Id459 did: Database Id460 scid: Schema Id461 tid: Table Id462 463 Returns:464 Returns the lits of tables required for constraints.465 """466 try:467 SQL = render_template(468 "/".join([469 self.table_template_path, 'get_tables_for_constraints.sql'470 ]),471 show_sysobj=self.blueprint.show_system_objects472 )473 474 status, res = self.conn.execute_dict(SQL)475 476 if not status:477 return internal_server_error(errormsg=res)478 479 return make_json_response(480 data=res['rows'],481 status=200482 )483 484 except Exception as e:485 return internal_server_error(errormsg=str(e))486 487 @BaseTableView.check_precondition488 def get_table_vacuum(self, gid, sid, did, scid=None, tid=None):489 """490 Fetch the default values for table auto-vacuum491 fields, return an array of492 - label493 - name494 - setting495 values496 """497 res = self.get_vacuum_table_settings(self.conn, sid)498 return ajax_response(499 response=res,500 status=200501 )502 503 @BaseTableView.check_precondition504 def get_toast_table_vacuum(self, gid, sid, did, scid=None, tid=None):505 """506 Fetch the default values for toast table auto-vacuum507 fields, return an array of508 - label509 - name510 - setting511 values512 """513 res = self.get_vacuum_toast_settings(self.conn, sid)514 return ajax_response(515 response=res,516 status=200517 )518 519 @BaseTableView.check_precondition520 def get_access_methods(self, gid, sid, did, scid, tid=None):521 """522 This function returns access methods.523 524 Args:525 gid: Server Group ID526 sid: Server ID527 did: Database ID528 scid: Schema ID529 tid: Table ID530 exid: Exclusion constraint ID531 532 Returns:533 534 """535 res = exclusion_utils.get_access_methods(self.conn)536 537 return make_json_response(538 data=res,539 status=200540 )541 542 @BaseTableView.check_precondition543 def get_table_access_methods(self, gid, sid, did, scid, tid=None):544 """545 This function returns access methods for table.546 547 Args:548 gid: Server Group ID549 sid: Server ID550 did: Database ID551 scid: Schema ID552 tid: Table ID553 554 Returns:555 Returns list of access methods for table556 """557 res = BaseTableView.get_access_methods(self)558 559 return make_json_response(560 data=res,561 status=200562 )563 564 @BaseTableView.check_precondition565 def get_oper_class(self, gid, sid, did, scid, tid=None):566 """567 568 Args:569 gid: Server Group ID570 sid: Server ID571 did: Database ID572 scid: Schema ID573 tid: Table ID574 exid: Exclusion constraint ID575 576 Returns:577 578 """579 data = request.args if request.args else None580 try:581 if data and 'indextype' in data:582 result = exclusion_utils.get_oper_class(583 self.conn, data['indextype'])584 585 return make_json_response(586 data=result,587 status=200588 )589 except Exception as e:590 return internal_server_error(errormsg=str(e))591 592 @BaseTableView.check_precondition593 def get_operator(self, gid, sid, did, scid, tid=None):594 """595 596 Args:597 gid: Server Group ID598 sid: Server ID599 did: Database ID600 scid: Schema ID601 tid: Table ID602 exid: Exclusion constraint ID603 604 Returns:605 606 """607 data = request.args608 try:609 result = exclusion_utils.get_operator(610 self.conn, data.get('col_type', None),611 self.blueprint.show_system_objects)612 613 return make_json_response(614 data=result,615 status=200616 )617 except Exception as e:618 return internal_server_error(errormsg=str(e))619 620 @BaseTableView.check_precondition621 def properties(self, gid, sid, did, scid, tid):622 """623 This function will show the properties of the selected table node.624 625 Args:626 gid: Server Group ID627 sid: Server ID628 did: Database ID629 scid: Schema ID630 scid: Schema ID631 tid: Table ID632 633 Returns:634 JSON of selected table node635 """636 status, res = self._fetch_table_properties(did, scid, tid)637 if not status:638 return res639 if not res['rows']:640 return gone(gettext(self.not_found_error_msg()))641 642 return super().properties(643 gid, sid, did, scid, tid, res=res644 )645 646 @BaseTableView.check_precondition647 def get_op_class(self, gid, sid, did, scid, tid=None):648 """649 This function will return list of op_class method650 for each access methods available via AJAX response651 """652 res = dict()653 try:654 655 # for row in rset['rows']:656 # # Fetching all the op_classes for each access method657 SQL = render_template(658 "/".join([self.table_template_path, 'get_op_class.sql'])659 )660 status, result = self.conn.execute_2darray(SQL)661 if not status:662 return internal_server_error(errormsg=res)663 664 op_class_list = []665 666 for r in result['rows']:667 op_class_list.append({'label': r['opcname'],668 'value': r['opcname']})669 670 return make_json_response(671 data=op_class_list,672 status=200673 )674 675 except Exception as e:676 return internal_server_error(errormsg=str(e))677 678 @BaseTableView.check_precondition679 def types(self, gid, sid, did, scid, tid=None, clid=None):680 """681 Returns:682 This function will return list of types available for column node683 for node-ajax-control684 """685 condition = self.get_types_condition_sql(686 self.blueprint.show_system_objects)687 688 status, types = self.get_types(self.conn, condition, True, sid)689 690 if not status:691 return internal_server_error(errormsg=types)692 693 return make_json_response(694 data=types,695 status=200696 )697 698 @BaseTableView.check_precondition699 def get_columns(self, gid, sid, did, scid, tid=None):700 """701 Returns the Table Columns.702 703 Args:704 gid: Server Group Id705 sid: Server Id706 did: Database Id707 scid: Schema Id708 tid: Table Id709 710 Returns:711 JSON Array with below parameters.712 name: Column Name713 ctype: Column Data Type714 inherited_from: Parent Table from which the related column715 is inheritted.716 """717 res = []718 data = request.args if request.args else None719 try:720 if data and 'tid' in data:721 SQL = render_template(722 "/".join([723 self.table_template_path,724 self._GET_COLUMNS_FOR_TABLE_SQL725 ]),726 tid=data['tid'], conn=self.conn727 )728 elif data and 'tname' in data:729 SQL = render_template(730 "/".join([731 self.table_template_path,732 self._GET_COLUMNS_FOR_TABLE_SQL733 ]),734 tname=data['tname'], conn=self.conn735 )736 737 if SQL:738 status, res = self.conn.execute_dict(SQL)739 if not status:740 return internal_server_error(errormsg=res)741 res = res['rows']742 743 return make_json_response(744 data=res,745 status=200746 )747 748 except Exception as e:749 return internal_server_error(errormsg=str(e))750 751 @BaseTableView.check_precondition752 def get_oftype(self, gid, sid, did, scid, tid=None):753 """754 Returns:755 This function will return list of types available for table node756 for node-ajax-control757 """758 res = []759 try:760 SQL = render_template(761 "/".join([self.table_template_path, 'get_oftype.sql']),762 scid=scid,763 server_type=self.manager.server_type,764 show_sys_objects=self.blueprint.show_system_objects765 )766 status, rset = self.conn.execute_2darray(SQL)767 if not status:768 return internal_server_error(errormsg=res)769 for row in rset['rows']:770 # Get columns for all 'OF TYPES'.771 SQL = render_template(772 "/".join(773 [self.table_template_path,774 self._GET_COLUMNS_FOR_TABLE_SQL]775 ), tid=row['oid'], conn=self.conn776 )777 778 status, type_cols = self.conn.execute_dict(SQL)779 if not status:780 return internal_server_error(errormsg=type_cols)781 782 res.append({783 'label': row['typname'],784 'value': row['typname'],785 'tid': row['oid'],786 'oftype_columns': type_cols['rows']787 })788 return make_json_response(789 data=res,790 status=200791 )792 793 except Exception as e:794 return internal_server_error(errormsg=str(e))795 796 @BaseTableView.check_precondition797 def get_inherits(self, gid, sid, did, scid, tid=None):798 """799 Returns:800 This function will return list of tables available for inheritance801 while creating new table802 """803 try:804 res = []805 SQL = render_template(806 "/".join([self.table_template_path, 'get_inherits.sql']),807 show_system_objects=self.blueprint.show_system_objects,808 tid=tid,809 scid=scid,810 server_type=self.manager.server_type811 )812 status, rset = self.conn.execute_2darray(SQL)813 if not status:814 return internal_server_error(errormsg=res)815 for row in rset['rows']:816 res.append(817 {'label': row['inherits'], 'value': row['inherits'],818 'tid': row['oid']819 }820 )821 return make_json_response(822 data=res,823 status=200824 )825 826 except Exception as e:827 return internal_server_error(errormsg=str(e))828 829 @BaseTableView.check_precondition830 def get_attach_tables(self, gid, sid, did, scid, tid=None):831 """832 Returns:833 This function will return list of tables available to be attached834 to the partitioned table.835 """836 try:837 res = []838 SQL = render_template(839 "/".join([840 self.partition_template_path, 'get_attach_tables.sql'841 ]),842 tid=tid843 )844 845 status, rset = self.conn.execute_2darray(SQL)846 if not status:847 return internal_server_error(errormsg=res)848 849 for row in rset['rows']:850 res.append(851 {'label': row['table_name'], 'value': row['oid']}852 )853 854 return make_json_response(855 data=res,856 status=200857 )858 859 except Exception as e:860 return internal_server_error(errormsg=str(e))861 862 @BaseTableView.check_precondition863 def get_relations(self, gid, sid, did, scid, tid=None):864 """865 Returns:866 This function will return list of tables available for867 like/relation combobox while creating new table868 """869 res = []870 try:871 SQL = render_template(872 "/".join([self.table_template_path, 'get_relations.sql']),873 show_sys_objects=self.blueprint.show_system_objects,874 server_type=self.manager.server_type875 )876 status, rset = self.conn.execute_2darray(SQL)877 if not status:878 return internal_server_error(errormsg=res)879 for row in rset['rows']:880 res.append(881 {882 'label': row['like_relation'],883 'value': row['like_relation']884 }885 )886 return make_json_response(887 data=res,888 status=200889 )890 891 except Exception as e:892 return internal_server_error(errormsg=str(e))893 894 def _parser_data_input_from_client(self, data):895 """896 This function is used to parse the data.897 :param data:898 :return:899 """900 # Parse privilege data coming from client according to database format901 if 'relacl' in data:902 data['relacl'] = parse_priv_to_db(data['relacl'], self.acl)903 904 # Parse & format columns905 data = column_utils.parse_format_columns(data)906 data = TableView.check_and_convert_name_to_string(data)907 908 # 'coll_inherits' is Array but it comes as string from browser909 # We will convert it again to list910 if 'coll_inherits' in data and \911 isinstance(data['coll_inherits'], str):912 data['coll_inherits'] = json.loads(913 data['coll_inherits']914 )915 916 if 'foreign_key' in data:917 for c in data['foreign_key']:918 schema, table = fkey_utils.get_parent(919 self.conn, c['columns'][0]['references'])920 c['remote_schema'] = schema921 c['remote_table'] = table922 923 def _check_for_table_partitions(self, data):924 """925 This function is used to check for table partition.926 :param data:927 :return:928 """929 partitions_sql = ''930 if self.is_table_partitioned(data):931 data['relkind'] = 'p'932 # create partition scheme933 data['partition_scheme'] = self.get_partition_scheme(data)934 partitions_sql = self.get_partitions_sql(data)935 return partitions_sql936 937 @BaseTableView.check_precondition938 def create(self, gid, sid, did, scid):939 """940 This function will creates new the table object941 942 Args:943 gid: Server Group ID944 sid: Server ID945 did: Database ID946 scid: Schema ID947 """948 data = request.form if request.form else json.loads(949 request.data950 )951 952 for k, v in data.items():953 try:954 # comments should be taken as is because if user enters a955 # json comment it is parsed by loads which should not happen956 if k in ('description',):957 data[k] = v958 else:959 data[k] = json.loads(v)960 except (ValueError, TypeError, KeyError):961 data[k] = v962 963 required_args = [964 'name'965 ]966 967 for arg in required_args:968 if arg not in data:969 return make_json_response(970 status=410,971 success=0,972 errormsg=gettext(973 "Could not find the required parameter ({})."974 ).format(arg)975 )976 977 # Parse privilege data coming from client according to database format978 self._parser_data_input_from_client(data)979 980 try:981 partitions_sql = self._check_for_table_partitions(data)982 983 # Update the vacuum table settings.984 BaseTableView.update_vacuum_settings(self, 'vacuum_table', data)985 # Update the vacuum toast table settings.986 BaseTableView.update_vacuum_settings(self, 'vacuum_toast', data)987 988 sql = render_template(989 "/".join([self.table_template_path, self._CREATE_SQL]),990 data=data, conn=self.conn991 )992 993 # Append SQL for partitions994 sql += '\n' + partitions_sql995 996 status, res = self.conn.execute_scalar(sql)997 if not status:998 return internal_server_error(errormsg=res)999 1000 # PostgreSQL truncates the table name to 63 characters.1001 # Have to truncate the name like PostgreSQL to get the1002 # proper OID1003 CONST_MAX_CHAR_COUNT = 631004 1005 if len(data['name']) > CONST_MAX_CHAR_COUNT:1006 data['name'] = data['name'][0:CONST_MAX_CHAR_COUNT]1007 1008 # Get updated schema oid1009 sql = render_template(1010 "/".join([self.table_template_path, self._GET_SCHEMA_OID_SQL]),1011 tname=data['name'],1012 sname=data['schema'],1013 conn=self.conn1014 )1015 1016 status, new_scid = self.conn.execute_scalar(sql)1017 if not status:1018 return internal_server_error(errormsg=new_scid)1019 1020 # we need oid to add object in tree at browser1021 sql = render_template(1022 "/".join([self.table_template_path, self._OID_SQL]),1023 scid=new_scid, data=data, conn=self.conn1024 )1025 1026 status, tid = self.conn.execute_scalar(sql)1027 if not status:1028 return internal_server_error(errormsg=tid)1029 1030 return jsonify(1031 node=self.blueprint.generate_browser_node(1032 tid,1033 new_scid,1034 data['name'],1035 icon=self.get_icon_css_class(data),1036 is_partitioned=self.is_table_partitioned(data)1037 )1038 )1039 except Exception as e:1040 return internal_server_error(errormsg=str(e))1041 1042 @BaseTableView.check_precondition1043 def update(self, gid, sid, did, scid, tid):1044 """1045 This function will update an existing table object1046 1047 Args:1048 gid: Server Group ID1049 sid: Server ID1050 did: Database ID1051 scid: Schema ID1052 tid: Table ID1053 """1054 data = request.form if request.form else json.loads(1055 request.data1056 )1057 1058 for k, v in data.items():1059 try:1060 # comments should be taken as is because if user enters a1061 # json comment it is parsed by loads which should not happen1062 if k in ('description',):1063 data[k] = v1064 else:1065 data[k] = json.loads(v)1066 except (ValueError, TypeError, KeyError):1067 data[k] = v1068 1069 try:1070 status, res = self._fetch_table_properties(did, scid, tid)1071 if not status:1072 return res1073 1074 lock_on_table = self.get_table_locks(did, res['rows'][0])1075 if lock_on_table != '':1076 return ExecuteError(1077 error_msg=str(lock_on_table.json['info']))1078 1079 return super().update(1080 gid, sid, did, scid, tid, data=data, res=res)1081 except Exception as e:1082 current_app.logger.exception(e)1083 return internal_server_error(errormsg=str(e))1084 1085 @BaseTableView.check_precondition1086 def delete(self, gid, sid, did, scid, tid=None):1087 """1088 This function will deletes the table object1089 1090 Args:1091 gid: Server Group ID1092 sid: Server ID1093 did: Database ID1094 scid: Schema ID1095 tid: Table ID1096 """1097 if tid is None:1098 data = request.form if request.form else json.loads(1099 request.data1100 )1101 else:1102 data = {'ids': [tid]}1103 1104 try:1105 for tid in data['ids']:1106 SQL = render_template(1107 "/".join([self.table_template_path, self._PROPERTIES_SQL]),1108 did=did, scid=scid, tid=tid,1109 datlastsysoid=self._DATABASE_LAST_SYSTEM_OID1110 )1111 status, res = self.conn.execute_dict(SQL)1112 if not status:1113 return internal_server_error(errormsg=res)1114 1115 if not res['rows']:1116 return make_json_response(1117 success=0,1118 errormsg=gettext(1119 'Error: Object not found.'1120 ),1121 info=gettext(1122 self.not_found_error_msg() + '\n'1123 )1124 )1125 1126 lock_on_table = self.get_table_locks(did, res['rows'][0])1127 if lock_on_table != '':1128 return lock_on_table1129 1130 status, res = super().delete(gid, sid, did,1131 scid, tid, res)1132 1133 if not status:1134 return internal_server_error(errormsg=res)1135 1136 return make_json_response(1137 success=1,1138 info=gettext("Table dropped")1139 )1140 1141 except Exception as e:1142 return internal_server_error(errormsg=str(e))1143 1144 @BaseTableView.check_precondition1145 def truncate(self, gid, sid, did, scid, tid):1146 """1147 This function will truncate the table object1148 1149 Args:1150 gid: Server Group ID1151 sid: Server ID1152 did: Database ID1153 scid: Schema ID1154 tid: Table ID1155 """1156 1157 try:1158 SQL = render_template(1159 "/".join([self.table_template_path, self._PROPERTIES_SQL]),1160 did=did, scid=scid, tid=tid,1161 datlastsysoid=self._DATABASE_LAST_SYSTEM_OID1162 )1163 status, res = self.conn.execute_dict(SQL)1164 if not status:1165 return internal_server_error(errormsg=res)1166 1167 if len(res['rows']) == 0:1168 return gone(gettext(self.not_found_error_msg()))1169 1170 return super().truncate(1171 gid, sid, did, scid, tid, res1172 )1173 1174 except Exception as e:1175 return internal_server_error(errormsg=str(e))1176 1177 @BaseTableView.check_precondition1178 def enable_disable_triggers(self, gid, sid, did, scid, tid):1179 """1180 This function will enable/disable trigger(s) on the table object1181 1182 Args:1183 gid: Server Group ID1184 sid: Server ID1185 did: Database ID1186 scid: Schema ID1187 tid: Table ID1188 """1189 # Below will decide if it's simple drop or drop with cascade call1190 data = request.form if request.form else json.loads(1191 request.data1192 )1193 # Convert str 'true' to boolean type1194 is_enable_trigger = data['is_enable_trigger']1195 1196 try:1197 SQL = render_template(1198 "/".join([self.table_template_path, self._PROPERTIES_SQL]),1199 did=did, scid=scid, tid=tid,1200 datlastsysoid=self._DATABASE_LAST_SYSTEM_OID