1#!/usr/bin/python 2# -*- coding: utf-8 -*- 3 4# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> 5# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) 6 7from __future__ import absolute_import, division, print_function 8__metaclass__ = type 9 10ANSIBLE_METADATA = { 11 'metadata_version': '1.1', 12 'status': ['preview'], 13 'supported_by': 'community' 14} 15 16DOCUMENTATION = r''' 17--- 18module: postgresql_table 19short_description: Create, drop, or modify a PostgreSQL table 20description: 21- Allows to create, drop, rename, truncate a table, or change some table attributes. 22version_added: '2.8' 23options: 24 table: 25 description: 26 - Table name. 27 required: true 28 aliases: 29 - name 30 type: str 31 state: 32 description: 33 - The table state. I(state=absent) is mutually exclusive with I(tablespace), I(owner), I(unlogged), 34 I(like), I(including), I(columns), I(truncate), I(storage_params) and, I(rename). 35 type: str 36 default: present 37 choices: [ absent, present ] 38 tablespace: 39 description: 40 - Set a tablespace for the table. 41 required: false 42 type: str 43 owner: 44 description: 45 - Set a table owner. 46 type: str 47 unlogged: 48 description: 49 - Create an unlogged table. 50 type: bool 51 default: no 52 like: 53 description: 54 - Create a table like another table (with similar DDL). 55 Mutually exclusive with I(columns), I(rename), and I(truncate). 56 type: str 57 including: 58 description: 59 - Keywords that are used with like parameter, may be DEFAULTS, CONSTRAINTS, INDEXES, STORAGE, COMMENTS or ALL. 60 Needs I(like) specified. Mutually exclusive with I(columns), I(rename), and I(truncate). 61 type: str 62 columns: 63 description: 64 - Columns that are needed. 65 type: list 66 elements: str 67 rename: 68 description: 69 - New table name. Mutually exclusive with I(tablespace), I(owner), 70 I(unlogged), I(like), I(including), I(columns), I(truncate), and I(storage_params). 71 type: str 72 truncate: 73 description: 74 - Truncate a table. Mutually exclusive with I(tablespace), I(owner), I(unlogged), 75 I(like), I(including), I(columns), I(rename), and I(storage_params). 76 type: bool 77 default: no 78 storage_params: 79 description: 80 - Storage parameters like fillfactor, autovacuum_vacuum_treshold, etc. 81 Mutually exclusive with I(rename) and I(truncate). 82 type: list 83 elements: str 84 db: 85 description: 86 - Name of database to connect and where the table will be created. 87 type: str 88 aliases: 89 - login_db 90 session_role: 91 description: 92 - Switch to session_role after connecting. 93 The specified session_role must be a role that the current login_user is a member of. 94 - Permissions checking for SQL commands is carried out as though 95 the session_role were the one that had logged in originally. 96 type: str 97 cascade: 98 description: 99 - Automatically drop objects that depend on the table (such as views). 100 Used with I(state=absent) only. 101 type: bool 102 default: no 103 version_added: '2.9' 104notes: 105- If you do not pass db parameter, tables will be created in the database 106 named postgres. 107- PostgreSQL allows to create columnless table, so columns param is optional. 108- Unlogged tables are available from PostgreSQL server version 9.1. 109seealso: 110- module: postgresql_sequence 111- module: postgresql_info 112- module: postgresql_tablespace 113- module: postgresql_owner 114- module: postgresql_privs 115- module: postgresql_copy 116- name: CREATE TABLE reference 117 description: Complete reference of the CREATE TABLE command documentation. 118 link: https://www.postgresql.org/docs/current/sql-createtable.html 119- name: ALTER TABLE reference 120 description: Complete reference of the ALTER TABLE command documentation. 121 link: https://www.postgresql.org/docs/current/sql-altertable.html 122- name: DROP TABLE reference 123 description: Complete reference of the DROP TABLE command documentation. 124 link: https://www.postgresql.org/docs/current/sql-droptable.html 125- name: PostgreSQL data types 126 description: Complete reference of the PostgreSQL data types documentation. 127 link: https://www.postgresql.org/docs/current/datatype.html 128author: 129- Andrei Klychkov (@Andersson007) 130extends_documentation_fragment: postgres 131''' 132 133EXAMPLES = r''' 134- name: Create tbl2 in the acme database with the DDL like tbl1 with testuser as an owner 135 postgresql_table: 136 db: acme 137 name: tbl2 138 like: tbl1 139 owner: testuser 140 141- name: Create tbl2 in the acme database and tablespace ssd with the DDL like tbl1 including comments and indexes 142 postgresql_table: 143 db: acme 144 table: tbl2 145 like: tbl1 146 including: comments, indexes 147 tablespace: ssd 148 149- name: Create test_table with several columns in ssd tablespace with fillfactor=10 and autovacuum_analyze_threshold=1 150 postgresql_table: 151 name: test_table 152 columns: 153 - id bigserial primary key 154 - num bigint 155 - stories text 156 tablespace: ssd 157 storage_params: 158 - fillfactor=10 159 - autovacuum_analyze_threshold=1 160 161- name: Create an unlogged table in schema acme 162 postgresql_table: 163 name: acme.useless_data 164 columns: waste_id int 165 unlogged: true 166 167- name: Rename table foo to bar 168 postgresql_table: 169 table: foo 170 rename: bar 171 172- name: Rename table foo from schema acme to bar 173 postgresql_table: 174 name: acme.foo 175 rename: bar 176 177- name: Set owner to someuser 178 postgresql_table: 179 name: foo 180 owner: someuser 181 182- name: Change tablespace of foo table to new_tablespace and set owner to new_user 183 postgresql_table: 184 name: foo 185 tablespace: new_tablespace 186 owner: new_user 187 188- name: Truncate table foo 189 postgresql_table: 190 name: foo 191 truncate: yes 192 193- name: Drop table foo from schema acme 194 postgresql_table: 195 name: acme.foo 196 state: absent 197 198- name: Drop table bar cascade 199 postgresql_table: 200 name: bar 201 state: absent 202 cascade: yes 203''' 204 205RETURN = r''' 206table: 207 description: Name of a table. 208 returned: always 209 type: str 210 sample: 'foo' 211state: 212 description: Table state. 213 returned: always 214 type: str 215 sample: 'present' 216owner: 217 description: Table owner. 218 returned: always 219 type: str 220 sample: 'postgres' 221tablespace: 222 description: Tablespace. 223 returned: always 224 type: str 225 sample: 'ssd_tablespace' 226queries: 227 description: List of executed queries. 228 returned: always 229 type: str 230 sample: [ 'CREATE TABLE "test_table" (id bigint)' ] 231storage_params: 232 description: Storage parameters. 233 returned: always 234 type: list 235 sample: [ "fillfactor=100", "autovacuum_analyze_threshold=1" ] 236''' 237 238try: 239 from psycopg2.extras import DictCursor 240except ImportError: 241 # psycopg2 is checked by connect_to_db() 242 # from ansible.module_utils.postgres 243 pass 244 245from ansible.module_utils.basic import AnsibleModule 246from ansible.module_utils.database import pg_quote_identifier 247from ansible.module_utils.postgres import ( 248 connect_to_db, 249 exec_sql, 250 get_conn_params, 251 postgres_common_argument_spec, 252) 253 254 255# =========================================== 256# PostgreSQL module specific support methods. 257# 258 259class Table(object): 260 def __init__(self, name, module, cursor): 261 self.name = name 262 self.module = module 263 self.cursor = cursor 264 self.info = { 265 'owner': '', 266 'tblspace': '', 267 'storage_params': [], 268 } 269 self.exists = False 270 self.__exists_in_db() 271 self.executed_queries = [] 272 273 def get_info(self): 274 """Getter to refresh and get table info""" 275 self.__exists_in_db() 276 277 def __exists_in_db(self): 278 """Check table exists and refresh info""" 279 if "." in self.name: 280 schema = self.name.split('.')[-2] 281 tblname = self.name.split('.')[-1] 282 else: 283 schema = 'public' 284 tblname = self.name 285 286 query = ("SELECT t.tableowner, t.tablespace, c.reloptions " 287 "FROM pg_tables AS t " 288 "INNER JOIN pg_class AS c ON c.relname = t.tablename " 289 "INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid " 290 "WHERE t.tablename = '%s' " 291 "AND n.nspname = '%s'" % (tblname, schema)) 292 res = exec_sql(self, query, add_to_executed=False) 293 if res: 294 self.exists = True 295 self.info = dict( 296 owner=res[0][0], 297 tblspace=res[0][1] if res[0][1] else '', 298 storage_params=res[0][2] if res[0][2] else [], 299 ) 300 301 return True 302 else: 303 self.exists = False 304 return False 305 306 def create(self, columns='', params='', tblspace='', 307 unlogged=False, owner=''): 308 """ 309 Create table. 310 If table exists, check passed args (params, tblspace, owner) and, 311 if they're different from current, change them. 312 Arguments: 313 params - storage params (passed by "WITH (...)" in SQL), 314 comma separated. 315 tblspace - tablespace. 316 owner - table owner. 317 unlogged - create unlogged table. 318 columns - column string (comma separated). 319 """ 320 name = pg_quote_identifier(self.name, 'table') 321 322 changed = False 323 324 if self.exists: 325 if tblspace == 'pg_default' and self.info['tblspace'] is None: 326 pass # Because they have the same meaning 327 elif tblspace and self.info['tblspace'] != tblspace: 328 self.set_tblspace(tblspace) 329 changed = True 330 331 if owner and self.info['owner'] != owner: 332 self.set_owner(owner) 333 changed = True 334 335 if params: 336 param_list = [p.strip(' ') for p in params.split(',')] 337 338 new_param = False 339 for p in param_list: 340 if p not in self.info['storage_params']: 341 new_param = True 342 343 if new_param: 344 self.set_stor_params(params) 345 changed = True 346 347 if changed: 348 return True 349 return False 350 351 query = "CREATE" 352 if unlogged: 353 query += " UNLOGGED TABLE %s" % name 354 else: 355 query += " TABLE %s" % name 356 357 if columns: 358 query += " (%s)" % columns 359 else: 360 query += " ()" 361 362 if params: 363 query += " WITH (%s)" % params 364 365 if tblspace: 366 query += " TABLESPACE %s" % pg_quote_identifier(tblspace, 'database') 367 368 if exec_sql(self, query, ddl=True): 369 changed = True 370 371 if owner: 372 changed = self.set_owner(owner) 373 374 return changed 375 376 def create_like(self, src_table, including='', tblspace='', 377 unlogged=False, params='', owner=''): 378 """ 379 Create table like another table (with similar DDL). 380 Arguments: 381 src_table - source table. 382 including - corresponds to optional INCLUDING expression 383 in CREATE TABLE ... LIKE statement. 384 params - storage params (passed by "WITH (...)" in SQL), 385 comma separated. 386 tblspace - tablespace. 387 owner - table owner. 388 unlogged - create unlogged table. 389 """ 390 changed = False 391 392 name = pg_quote_identifier(self.name, 'table') 393 394 query = "CREATE" 395 if unlogged: 396 query += " UNLOGGED TABLE %s" % name 397 else: 398 query += " TABLE %s" % name 399 400 query += " (LIKE %s" % pg_quote_identifier(src_table, 'table') 401 402 if including: 403 including = including.split(',') 404 for i in including: 405 query += " INCLUDING %s" % i 406 407 query += ')' 408 409 if params: 410 query += " WITH (%s)" % params 411 412 if tblspace: 413 query += " TABLESPACE %s" % pg_quote_identifier(tblspace, 'database') 414 415 if exec_sql(self, query, ddl=True): 416 changed = True 417 418 if owner: 419 changed = self.set_owner(owner) 420 421 return changed 422 423 def truncate(self): 424 query = "TRUNCATE TABLE %s" % pg_quote_identifier(self.name, 'table') 425 return exec_sql(self, query, ddl=True) 426 427 def rename(self, newname): 428 query = "ALTER TABLE %s RENAME TO %s" % (pg_quote_identifier(self.name, 'table'), 429 pg_quote_identifier(newname, 'table')) 430 return exec_sql(self, query, ddl=True) 431 432 def set_owner(self, username): 433 query = "ALTER TABLE %s OWNER TO %s" % (pg_quote_identifier(self.name, 'table'), 434 pg_quote_identifier(username, 'role')) 435 return exec_sql(self, query, ddl=True) 436 437 def drop(self, cascade=False): 438 if not self.exists: 439 return False 440 441 query = "DROP TABLE %s" % pg_quote_identifier(self.name, 'table') 442 if cascade: 443 query += " CASCADE" 444 return exec_sql(self, query, ddl=True) 445 446 def set_tblspace(self, tblspace): 447 query = "ALTER TABLE %s SET TABLESPACE %s" % (pg_quote_identifier(self.name, 'table'), 448 pg_quote_identifier(tblspace, 'database')) 449 return exec_sql(self, query, ddl=True) 450 451 def set_stor_params(self, params): 452 query = "ALTER TABLE %s SET (%s)" % (pg_quote_identifier(self.name, 'table'), params) 453 return exec_sql(self, query, ddl=True) 454 455 456# =========================================== 457# Module execution. 458# 459 460 461def main(): 462 argument_spec = postgres_common_argument_spec() 463 argument_spec.update( 464 table=dict(type='str', required=True, aliases=['name']), 465 state=dict(type='str', default="present", choices=["absent", "present"]), 466 db=dict(type='str', default='', aliases=['login_db']), 467 tablespace=dict(type='str'), 468 owner=dict(type='str'), 469 unlogged=dict(type='bool', default=False), 470 like=dict(type='str'), 471 including=dict(type='str'), 472 rename=dict(type='str'), 473 truncate=dict(type='bool', default=False), 474 columns=dict(type='list'), 475 storage_params=dict(type='list'), 476 session_role=dict(type='str'), 477 cascade=dict(type='bool', default=False), 478 ) 479 module = AnsibleModule( 480 argument_spec=argument_spec, 481 supports_check_mode=True, 482 ) 483 484 table = module.params["table"] 485 state = module.params["state"] 486 tablespace = module.params["tablespace"] 487 owner = module.params["owner"] 488 unlogged = module.params["unlogged"] 489 like = module.params["like"] 490 including = module.params["including"] 491 newname = module.params["rename"] 492 storage_params = module.params["storage_params"] 493 truncate = module.params["truncate"] 494 columns = module.params["columns"] 495 cascade = module.params["cascade"] 496 497 if state == 'present' and cascade: 498 module.warn("cascade=true is ignored when state=present") 499 500 # Check mutual exclusive parameters: 501 if state == 'absent' and (truncate or newname or columns or tablespace or like or storage_params or unlogged or owner or including): 502 module.fail_json(msg="%s: state=absent is mutually exclusive with: " 503 "truncate, rename, columns, tablespace, " 504 "including, like, storage_params, unlogged, owner" % table) 505 506 if truncate and (newname or columns or like or unlogged or storage_params or owner or tablespace or including): 507 module.fail_json(msg="%s: truncate is mutually exclusive with: " 508 "rename, columns, like, unlogged, including, " 509 "storage_params, owner, tablespace" % table) 510 511 if newname and (columns or like or unlogged or storage_params or owner or tablespace or including): 512 module.fail_json(msg="%s: rename is mutually exclusive with: " 513 "columns, like, unlogged, including, " 514 "storage_params, owner, tablespace" % table) 515 516 if like and columns: 517 module.fail_json(msg="%s: like and columns params are mutually exclusive" % table) 518 if including and not like: 519 module.fail_json(msg="%s: including param needs like param specified" % table) 520 521 conn_params = get_conn_params(module, module.params) 522 db_connection = connect_to_db(module, conn_params, autocommit=False) 523 cursor = db_connection.cursor(cursor_factory=DictCursor) 524 525 if storage_params: 526 storage_params = ','.join(storage_params) 527 528 if columns: 529 columns = ','.join(columns) 530 531 ############## 532 # Do main job: 533 table_obj = Table(table, module, cursor) 534 535 # Set default returned values: 536 changed = False 537 kw = {} 538 kw['table'] = table 539 kw['state'] = '' 540 if table_obj.exists: 541 kw = dict( 542 table=table, 543 state='present', 544 owner=table_obj.info['owner'], 545 tablespace=table_obj.info['tblspace'], 546 storage_params=table_obj.info['storage_params'], 547 ) 548 549 if state == 'absent': 550 changed = table_obj.drop(cascade=cascade) 551 552 elif truncate: 553 changed = table_obj.truncate() 554 555 elif newname: 556 changed = table_obj.rename(newname) 557 q = table_obj.executed_queries 558 table_obj = Table(newname, module, cursor) 559 table_obj.executed_queries = q 560 561 elif state == 'present' and not like: 562 changed = table_obj.create(columns, storage_params, 563 tablespace, unlogged, owner) 564 565 elif state == 'present' and like: 566 changed = table_obj.create_like(like, including, tablespace, 567 unlogged, storage_params) 568 569 if changed: 570 if module.check_mode: 571 db_connection.rollback() 572 else: 573 db_connection.commit() 574 575 # Refresh table info for RETURN. 576 # Note, if table has been renamed, it gets info by newname: 577 table_obj.get_info() 578 db_connection.commit() 579 if table_obj.exists: 580 kw = dict( 581 table=table, 582 state='present', 583 owner=table_obj.info['owner'], 584 tablespace=table_obj.info['tblspace'], 585 storage_params=table_obj.info['storage_params'], 586 ) 587 else: 588 # We just change the table state here 589 # to keep other information about the dropped table: 590 kw['state'] = 'absent' 591 592 kw['queries'] = table_obj.executed_queries 593 kw['changed'] = changed 594 db_connection.close() 595 module.exit_json(**kw) 596 597 598if __name__ == '__main__': 599 main() 600