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