def __get_general_pub_info(self): """Get and return general publication information. Returns: Dict with publication information if successful, False otherwise. """ # Check pg_publication.pubtruncate exists (supported from PostgreSQL 11): pgtrunc_sup = exec_sql(self, ("SELECT 1 FROM information_schema.columns " "WHERE table_name = 'pg_publication' " "AND column_name = 'pubtruncate'"), add_to_executed=False) if pgtrunc_sup: query = ( "SELECT r.rolname AS pubowner, p.puballtables, p.pubinsert, " "p.pubupdate , p.pubdelete, p.pubtruncate FROM pg_publication AS p " "JOIN pg_catalog.pg_roles AS r " "ON p.pubowner = r.oid " "WHERE p.pubname = '%s'" % self.name) else: query = ( "SELECT r.rolname AS pubowner, p.puballtables, p.pubinsert, " "p.pubupdate , p.pubdelete FROM pg_publication AS p " "JOIN pg_catalog.pg_roles AS r " "ON p.pubowner = r.oid " "WHERE p.pubname = '%s'" % self.name) result = exec_sql(self, query, add_to_executed=False) if result: return result[0] else: return False
def create(self, kind='physical', immediately_reserve=False, output_plugin=False, just_check=False): if self.exists: if self.kind == kind: return False else: self.module.warn("slot with name '%s' already exists " "but has another type '%s'" % (self.name, self.kind)) return False if just_check: return None if kind == 'physical': # Check server version (needs for immedately_reserverd needs 9.6+): if self.cursor.connection.server_version < 96000: query = "SELECT pg_create_physical_replication_slot('%s')" % self.name else: query = "SELECT pg_create_physical_replication_slot('%s', %s)" % ( self.name, immediately_reserve) elif kind == 'logical': query = "SELECT pg_create_logical_replication_slot('%s', '%s')" % ( self.name, output_plugin) self.changed = exec_sql(self, query, ddl=True)
def drop(self, schema, cascade=False, concurrent=True): """Drop PostgreSQL index. Return True if success, otherwise, return False. Args: schema (str) -- name of the index schema Kwargs: cascade (bool) -- automatically drop objects that depend on the index, default False concurrent (bool) -- build index in concurrent mode, default True """ if not self.exists: return False query = 'DROP INDEX' if concurrent: query += ' CONCURRENTLY' if not schema: query += ' public.%s' % self.name else: query += ' %s.%s' % (schema, self.name) if cascade: query += ' CASCADE' self.executed_query = query if exec_sql(self, query, ddl=True, add_to_executed=False): return True return False
def create(self): """Implements CREATE SEQUENCE command behavior.""" query = ['CREATE SEQUENCE'] query.append(self.__add_schema()) if self.module.params.get('data_type'): query.append('AS %s' % self.module.params['data_type']) if self.module.params.get('increment'): query.append('INCREMENT BY %s' % self.module.params['increment']) if self.module.params.get('minvalue'): query.append('MINVALUE %s' % self.module.params['minvalue']) if self.module.params.get('maxvalue'): query.append('MAXVALUE %s' % self.module.params['maxvalue']) if self.module.params.get('start'): query.append('START WITH %s' % self.module.params['start']) if self.module.params.get('cache'): query.append('CACHE %s' % self.module.params['cache']) if self.module.params.get('cycle'): query.append('CYCLE') return exec_sql(self, ' '.join(query), ddl=True)
def __exists_in_db(self): """Check index existence, collect info, add it to self.info dict. Return True if the index exists, otherwise, return False. """ query = ("SELECT i.schemaname, i.tablename, i.tablespace, " "pi.indisvalid, c.reloptions " "FROM pg_catalog.pg_indexes AS i " "JOIN pg_catalog.pg_class AS c " "ON i.indexname = c.relname " "JOIN pg_catalog.pg_index AS pi " "ON c.oid = pi.indexrelid " "WHERE i.indexname = '%s'" % self.name) res = exec_sql(self, query, add_to_executed=False) if res: self.exists = True self.info = dict( name=self.name, state='present', schema=res[0][0], tblname=res[0][1], tblspace=res[0][2] if res[0][2] else '', valid=res[0][3], storage_params=res[0][4] if res[0][4] else [], ) return True else: self.exists = False return False
def __exists_in_db(self): """Check table exists and refresh info""" if "." in self.name: schema = self.name.split('.')[-2] tblname = self.name.split('.')[-1] else: schema = 'public' tblname = self.name query = ("SELECT t.tableowner, t.tablespace, c.reloptions " "FROM pg_tables AS t " "INNER JOIN pg_class AS c ON c.relname = t.tablename " "INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid " "WHERE t.tablename = '%s' " "AND n.nspname = '%s'" % (tblname, schema)) res = exec_sql(self, query, add_to_executed=False) if res: self.exists = True self.info = dict( owner=res[0][0], tblspace=res[0][1] if res[0][1] else '', storage_params=res[0][2] if res[0][2] else [], ) return True else: self.exists = False return False
def reassign(self, old_owners, fail_on_role): """Implements REASSIGN OWNED BY command. If success, set self.changed as True. Arguments: old_owners (list): The ownership of all the objects within the current database, and of all shared objects (databases, tablespaces), owned by these roles will be reassigned to self.role. fail_on_role (bool): If True, fail when a role from old_owners does not exist. Otherwise just warn and continue. """ roles = [] for r in old_owners: if self.check_role_exists(r, fail_on_role): roles.append(pg_quote_identifier(r, 'role')) # Roles do not exist, nothing to do, exit: if not roles: return False old_owners = ','.join(roles) query = ['REASSIGN OWNED BY'] query.append(old_owners) query.append('TO %s' % pg_quote_identifier(self.role, 'role')) query = ' '.join(query) self.changed = exec_sql(self, query, ddl=True)
def copy_from(self): """Implements COPY FROM command behavior.""" self.src = self.module.params['copy_from'] self.dst = self.module.params['dst'] query_fragments = ['COPY %s' % pg_quote_identifier(self.dst, 'table')] if self.module.params.get('columns'): query_fragments.append('(%s)' % ','.join(self.module.params['columns'])) query_fragments.append('FROM') if self.module.params.get('program'): query_fragments.append('PROGRAM') query_fragments.append("'%s'" % self.src) if self.module.params.get('options'): query_fragments.append(self.__transform_options()) # Note: check mode is implemented here: if self.module.check_mode: self.changed = self.__check_table(self.dst) if self.changed: self.executed_queries.append(' '.join(query_fragments)) else: if exec_sql(self, ' '.join(query_fragments), ddl=True): self.changed = True
def set_owner(self): """Implements ALTER SEQUENCE OWNER TO command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append('OWNER TO %s' % pg_quote_identifier(self.module.params['owner'], 'role')) return exec_sql(self, ' '.join(query), ddl=True)
def drop(self, cascade=False): if not self.exists: return False query = "DROP TABLE %s" % pg_quote_identifier(self.name, 'table') if cascade: query += " CASCADE" return exec_sql(self, query, ddl=True)
def __get_tables_pub_info(self): """Get and return tables that are published by the publication. Returns: List of dicts with published tables. """ query = ("SELECT schemaname, tablename " "FROM pg_publication_tables WHERE pubname = '%s'" % self.name) return exec_sql(self, query, add_to_executed=False)
def set_schema(self): """Implements ALTER SEQUENCE SET SCHEMA command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append( 'SET SCHEMA %s' % pg_quote_identifier(self.module.params['newschema'], 'schema')) return exec_sql(self, ' '.join(query), ddl=True)
def rename(self): """Implements ALTER SEQUENCE RENAME TO command behavior.""" query = ['ALTER SEQUENCE'] query.append(self.__add_schema()) query.append( 'RENAME TO %s' % pg_quote_identifier(self.module.params['rename_to'], 'sequence')) return exec_sql(self, ' '.join(query), ddl=True)
def drop(self): """Implements DROP SEQUENCE command behavior.""" query = ['DROP SEQUENCE'] query.append(self.__add_schema()) if self.module.params.get('cascade'): query.append('CASCADE') return exec_sql(self, ' '.join(query), ddl=True)
def get_pg_version(self): query = "SELECT version()" raw = exec_sql(self, query, add_to_executed=False)[0][0] if raw: self.is_available = True raw = raw.split()[1].split('.') self.version = dict( major=int(raw[0]), minor=int(raw[1]), )
def __check_table(self, table): """Check table or SQL in transaction mode for check_mode. Return True if it is OK. Arguments: table (str) - Table name that needs to be checked. It can be SQL SELECT statement that was passed instead of the table name. """ if 'SELECT ' in table.upper(): # In this case table is actually SQL SELECT statement. # If SQL fails, it's handled by exec_sql(): exec_sql(self, table, add_to_executed=False) # If exec_sql was passed, it means all is OK: return True exec_sql(self, 'SELECT 1 FROM %s' % pg_quote_identifier(table, 'table'), add_to_executed=False) # If SQL was executed successfully: return True
def get_stat(self): """Get and return index statistics. Return index statistics dictionary if index exists, otherwise False. """ query = ("SELECT * FROM pg_stat_user_indexes " "WHERE indexrelname = '%s' " "AND schemaname = '%s'" % (self.name, self.schema)) result = exec_sql(self, query, add_to_executed=False) if result: return [dict(row) for row in result] else: return False
def create(self, tblname, idxtype, columns, cond, tblspace, storage_params, concurrent=True): """Create PostgreSQL index. Return True if success, otherwise, return False. Args: tblname (str) -- name of a table for the index idxtype (str) -- type of the index like BTREE, BRIN, etc columns (str) -- string of comma-separated columns that need to be covered by index tblspace (str) -- tablespace for storing the index storage_params (str) -- string of comma-separated storage parameters Kwargs: concurrent (bool) -- build index in concurrent mode, default True """ if self.exists: return False if idxtype is None: idxtype = "BTREE" query = 'CREATE INDEX' if concurrent: query += ' CONCURRENTLY' query += ' %s' % self.name if self.schema: query += ' ON %s.%s ' % (self.schema, tblname) else: query += 'public.%s ' % tblname query += 'USING %s (%s)' % (idxtype, columns) if storage_params: query += ' WITH (%s)' % storage_params if tblspace: query += ' TABLESPACE %s' % tblspace if cond: query += ' WHERE %s' % cond self.executed_query = query if exec_sql(self, query, ddl=True, add_to_executed=False): return True return False
def __get_rel_info(self): """Get and return state of relations replicated by the subscription. Returns: List of dicts containing relations state if successful, False otherwise. """ query = ("SELECT c.relname, r.srsubstate, r.srsublsn " "FROM pg_catalog.pg_subscription_rel r " "JOIN pg_catalog.pg_subscription s ON s.oid = r.srsubid " "JOIN pg_catalog.pg_class c ON c.oid = r.srrelid " "WHERE s.subname = '%s'" % self.name) result = exec_sql(self, query, add_to_executed=False) if result: return [dict(row) for row in result] else: return False
def create_like(self, src_table, including='', tblspace='', unlogged=False, params='', owner=''): """ Create table like another table (with similar DDL). Arguments: src_table - source table. including - corresponds to optional INCLUDING expression in CREATE TABLE ... LIKE statement. params - storage params (passed by "WITH (...)" in SQL), comma separated. tblspace - tablespace. owner - table owner. unlogged - create unlogged table. """ changed = False name = pg_quote_identifier(self.name, 'table') query = "CREATE" if unlogged: query += " UNLOGGED TABLE %s" % name else: query += " TABLE %s" % name query += " (LIKE %s" % pg_quote_identifier(src_table, 'table') if including: including = including.split(',') for i in including: query += " INCLUDING %s" % i query += ')' if params: query += " WITH (%s)" % params if tblspace: query += " TABLESPACE %s" % pg_quote_identifier(tblspace, 'database') if exec_sql(self, query, ddl=True): changed = True if owner: changed = self.set_owner(owner) return changed
def __is_owner(self): """Return True if self.role is the current object owner.""" if self.obj_type == 'table': query = ("SELECT 1 FROM pg_tables WHERE tablename = '%s' " "AND tableowner = '%s'" % (self.obj_name, self.role)) elif self.obj_type == 'database': query = ("SELECT 1 FROM pg_database AS d " "JOIN pg_roles AS r ON d.datdba = r.oid " "WHERE d.datname = '%s' " "AND r.rolname = '%s'" % (self.obj_name, self.role)) elif self.obj_type == 'function': query = ("SELECT 1 FROM pg_proc AS f " "JOIN pg_roles AS r ON f.proowner = r.oid " "WHERE f.proname = '%s' " "AND r.rolname = '%s'" % (self.obj_name, self.role)) elif self.obj_type == 'sequence': query = ("SELECT 1 FROM pg_class AS c " "JOIN pg_roles AS r ON c.relowner = r.oid " "WHERE c.relkind = 'S' AND c.relname = '%s' " "AND r.rolname = '%s'" % (self.obj_name, self.role)) elif self.obj_type == 'schema': query = ("SELECT 1 FROM information_schema.schemata " "WHERE schema_name = '%s' " "AND schema_owner = '%s'" % (self.obj_name, self.role)) elif self.obj_type == 'tablespace': query = ("SELECT 1 FROM pg_tablespace AS t " "JOIN pg_roles AS r ON t.spcowner = r.oid " "WHERE t.spcname = '%s' " "AND r.rolname = '%s'" % (self.obj_name, self.role)) elif self.obj_type == 'view': query = ("SELECT 1 FROM pg_views " "WHERE viewname = '%s' " "AND viewowner = '%s'" % (self.obj_name, self.role)) elif self.obj_type == 'matview': query = ("SELECT 1 FROM pg_matviews " "WHERE matviewname = '%s' " "AND matviewowner = '%s'" % (self.obj_name, self.role)) return exec_sql(self, query, add_to_executed=False)
def __exec_sql(self, query, check_mode=False): """Execute SQL query. Note: If we need just to get information from the database, we use ``exec_sql`` function directly. Args: query (str): Query that needs to be executed. Kwargs: check_mode (bool): If True, don't actually change anything, just add ``query`` to ``self.executed_queries`` and return True. Returns: True if successful, False otherwise. """ if check_mode: self.executed_queries.append(query) return True else: return exec_sql(self, query, ddl=True)
def __get_general_subscr_info(self): """Get and return general subscription information. Returns: Dict with subscription information if successful, False otherwise. """ query = ("SELECT d.datname, r.rolname, s.subenabled, " "s.subconninfo, s.subslotname, s.subsynccommit, " "s.subpublications FROM pg_catalog.pg_subscription s " "JOIN pg_catalog.pg_database d " "ON s.subdbid = d.oid " "JOIN pg_catalog.pg_roles AS r " "ON s.subowner = r.oid " "WHERE s.subname = '%s' AND d.datname = '%s'" % (self.name, self.db)) result = exec_sql(self, query, add_to_executed=False) if result: return result[0] else: return False
def get_info(self): """Getter to refresh and get sequence info""" query = ("SELECT " "s.sequence_schema AS schemaname, " "s.sequence_name AS sequencename, " "pg_get_userbyid(c.relowner) AS sequenceowner, " "s.data_type::regtype AS data_type, " "s.start_value AS start_value, " "s.minimum_value AS min_value, " "s.maximum_value AS max_value, " "s.increment AS increment_by, " "s.cycle_option AS cycle " "FROM information_schema.sequences s " "JOIN pg_class c ON c.relname = s.sequence_name " "LEFT JOIN pg_namespace n ON n.oid = c.relnamespace " "WHERE NOT pg_is_other_temp_schema(n.oid) " "AND c.relkind = 'S'::\"char\" " "AND sequence_name = '%s' " "AND sequence_schema = '%s'" % (self.name, self.schema)) res = exec_sql(self, query, add_to_executed=False) if not res: self.exists = False return False if res: self.exists = True self.schema = res[0]['schemaname'] self.name = res[0]['sequencename'] self.owner = res[0]['sequenceowner'] self.data_type = res[0]['data_type'] self.start_value = res[0]['start_value'] self.minvalue = res[0]['min_value'] self.maxvalue = res[0]['max_value'] self.increment = res[0]['increment_by'] self.cycle = res[0]['cycle']
def copy_to(self): """Implements COPY TO command behavior.""" self.src = self.module.params['src'] self.dst = self.module.params['copy_to'] if 'SELECT ' in self.src.upper(): # If src is SQL SELECT statement: query_fragments = ['COPY (%s)' % self.src] else: # If src is a table: query_fragments = [ 'COPY %s' % pg_quote_identifier(self.src, 'table') ] if self.module.params.get('columns'): query_fragments.append('(%s)' % ','.join(self.module.params['columns'])) query_fragments.append('TO') if self.module.params.get('program'): query_fragments.append('PROGRAM') query_fragments.append("'%s'" % self.dst) if self.module.params.get('options'): query_fragments.append(self.__transform_options()) # Note: check mode is implemented here: if self.module.check_mode: self.changed = self.__check_table(self.src) if self.changed: self.executed_queries.append(' '.join(query_fragments)) else: if exec_sql(self, ' '.join(query_fragments), ddl=True): self.changed = True
def set_stor_params(self, params): query = "ALTER TABLE %s SET (%s)" % (pg_quote_identifier(self.name, 'table'), params) return exec_sql(self, query, ddl=True)
def set_tblspace(self, tblspace): query = "ALTER TABLE %s SET TABLESPACE %s" % (pg_quote_identifier(self.name, 'table'), pg_quote_identifier(tblspace, 'database')) return exec_sql(self, query, ddl=True)
def set_owner(self, username): query = "ALTER TABLE %s OWNER TO %s" % (pg_quote_identifier(self.name, 'table'), pg_quote_identifier(username, 'role')) return exec_sql(self, query, ddl=True)
def rename(self, newname): query = "ALTER TABLE %s RENAME TO %s" % (pg_quote_identifier(self.name, 'table'), pg_quote_identifier(newname, 'table')) return exec_sql(self, query, ddl=True)
def truncate(self): query = "TRUNCATE TABLE %s" % pg_quote_identifier(self.name, 'table') return exec_sql(self, query, ddl=True)