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 set_owner(cursor, schema, owner): query = "ALTER SCHEMA %s OWNER TO %s" % ( pg_quote_identifier(schema, 'schema'), pg_quote_identifier(owner, 'role')) cursor.execute(query) executed_queries.append(query) return True
def revoke_database_privileges(cursor, user, db, privs): # Note: priv escaped by parse_privs privs = ', '.join(privs) if user == "PUBLIC": query = 'REVOKE %s ON DATABASE %s FROM PUBLIC' % ( privs, pg_quote_identifier(db, 'database')) else: query = 'REVOKE %s ON DATABASE %s FROM "%s"' % ( privs, pg_quote_identifier(db, 'database'), user) executed_queries.append(query) cursor.execute(query)
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 schema_create(cursor, schema, owner): if not schema_exists(cursor, schema): query_fragments = ['CREATE SCHEMA %s' % pg_quote_identifier(schema, 'schema')] if owner: query_fragments.append('AUTHORIZATION %s' % pg_quote_identifier(owner, 'role')) query = ' '.join(query_fragments) cursor.execute(query) executed_queries.append(query) return True else: schema_info = get_schema_info(cursor, schema) if owner and owner != schema_info['owner']: return set_owner(cursor, schema, owner) else: return False
def __pub_drop_table(self, table, check_mode=False): """Drop a table from the publication. Args: table (str): Table name. Kwargs: check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: True if successful, False otherwise. """ query = ("ALTER PUBLICATION %s DROP TABLE %s" % (pg_quote_identifier( self.name, 'publication'), pg_quote_identifier(table, 'table'))) return self.__exec_sql(query, check_mode=check_mode)
def revoke_table_privileges(cursor, user, table, privs): # Note: priv escaped by parse_privs privs = ', '.join(privs) query = 'REVOKE %s ON TABLE %s FROM "%s"' % ( privs, pg_quote_identifier(table, 'table'), user) executed_queries.append(query) cursor.execute(query)
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 __pub_set_owner(self, role, check_mode=False): """Set a publication owner. Args: role (str): Role (user) name that needs to be set as a publication owner. Kwargs: check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: True if successful, False otherwise. """ query = ("ALTER PUBLICATION %s OWNER TO %s" % (pg_quote_identifier( self.name, 'publication'), pg_quote_identifier(role, 'role'))) return self.__exec_sql(query, check_mode=check_mode)
def __pub_set_tables(self, tables, check_mode=False): """Set a table suit that need to be published by the publication. Args: tables (list): List of tables. Kwargs: check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: True if successful, False otherwise. """ quoted_tables = [pg_quote_identifier(t, 'table') for t in tables] query = ("ALTER PUBLICATION %s SET TABLE %s" % (pg_quote_identifier( self.name, 'publication'), ', '.join(quoted_tables))) return self.__exec_sql(query, check_mode=check_mode)
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 db_delete(cursor, db): if db_exists(cursor, db): query = "DROP DATABASE %s" % pg_quote_identifier(db, 'database') executed_commands.append(query) cursor.execute(query) return True else: return 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 transform_tables_representation(tbl_list): """Add 'public.' to names of tables where a schema identifier is absent and add quotes to each element. Args: tbl_list (list): List of table names. Returns: tbl_list (list): Changed list. """ for i, table in enumerate(tbl_list): if '.' not in table: tbl_list[i] = pg_quote_identifier('public.%s' % table.strip(), 'table') else: tbl_list[i] = pg_quote_identifier(table.strip(), 'table') return tbl_list
def create(self, location): """Create tablespace. Return True if success, otherwise, return False. args: location (str) -- tablespace directory path in the FS """ query = ("CREATE TABLESPACE %s LOCATION '%s'" % (pg_quote_identifier(self.name, 'database'), location)) return exec_sql(self, query, ddl=True)
def schema_delete(cursor, schema, cascade): if schema_exists(cursor, schema): query = "DROP SCHEMA %s" % pg_quote_identifier(schema, 'schema') if cascade: query += " CASCADE" cursor.execute(query) executed_queries.append(query) return True else: return False
def __set_setting(self, setting): """Set tablespace setting. Return True if success, otherwise, return False. args: setting (str) -- string in format "setting_name = 'setting_value'" """ query = "ALTER TABLESPACE %s SET (%s)" % (pg_quote_identifier(self.name, 'database'), setting) return exec_sql(self, query, ddl=True)
def rename(self, newname): """Rename tablespace. Return True if success, otherwise, return False. args: newname (str) -- new name for the tablespace" """ query = "ALTER TABLESPACE %s RENAME TO %s" % (pg_quote_identifier(self.name, 'database'), newname) self.new_name = newname return exec_sql(self, query, ddl=True)
def set_owner(self, new_owner): """Set tablespace owner. Return True if success, otherwise, return False. args: new_owner (str) -- name of a new owner for the tablespace" """ if new_owner == self.owner: return False query = "ALTER TABLESPACE %s OWNER TO %s" % (pg_quote_identifier(self.name, 'database'), new_owner) return exec_sql(self, query, ddl=True)
def __pub_set_param(self, param, value, check_mode=False): """Set an optional publication parameter. Args: param (str): Name of the parameter. value (str): Parameter value. Kwargs: check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: True if successful, False otherwise. """ query = ("ALTER PUBLICATION %s SET (%s = '%s')" % (pg_quote_identifier(self.name, 'publication'), param, value)) return self.__exec_sql(query, check_mode=check_mode)
def create(self, tables, params, owner, check_mode=True): """Create the publication. Args: tables (list): List with names of the tables that need to be added to the publication. params (dict): Dict contains optional publication parameters and their values. owner (str): Name of the publication owner. Kwargs: check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: changed (bool): True if publication has been created, otherwise False. """ changed = True query_fragments = [ "CREATE PUBLICATION %s" % pg_quote_identifier(self.name, 'publication') ] if tables: query_fragments.append("FOR TABLE %s" % ', '.join(tables)) else: query_fragments.append("FOR ALL TABLES") if params: params_list = [] # Make list ["param = 'value'", ...] from params dict: for (key, val) in iteritems(params): params_list.append("%s = '%s'" % (key, val)) # Add the list to query_fragments: query_fragments.append("WITH (%s)" % ', '.join(params_list)) changed = self.__exec_sql(' '.join(query_fragments), check_mode=check_mode) if owner: # If check_mode, just add possible SQL to # executed_queries and return: self.__pub_set_owner(owner, check_mode=check_mode) return changed
def check_pub(self): """Check the publication and refresh ``self.attrs`` publication attribute. Returns: True if the publication with ``self.name`` exists, False otherwise. """ pub_info = self.__get_general_pub_info() if not pub_info: # Publication does not exist: return False self.attrs['owner'] = pub_info.get('pubowner') # Publication DML operations: self.attrs['parameters']['publish'] = {} self.attrs['parameters']['publish']['insert'] = pub_info.get( 'pubinsert', False) self.attrs['parameters']['publish']['update'] = pub_info.get( 'pubupdate', False) self.attrs['parameters']['publish']['delete'] = pub_info.get( 'pubdelete', False) if pub_info.get('pubtruncate'): self.attrs['parameters']['publish']['truncate'] = pub_info.get( 'pubtruncate') # If alltables flag is False, get the list of targeted tables: if not pub_info.get('puballtables'): table_info = self.__get_tables_pub_info() # Join sublists [['schema', 'table'], ...] to ['schema.table', ...] # for better representation: for i, schema_and_table in enumerate(table_info): table_info[i] = pg_quote_identifier('.'.join(schema_and_table), 'table') self.attrs['tables'] = table_info else: self.attrs['alltables'] = True # Publication exists: return True
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 drop(self, cascade=False, check_mode=True): """Drop the publication. Kwargs: cascade (bool): Flag indicates that publication needs to be deleted with its dependencies. check_mode (bool): If True, don't actually change anything, just make SQL, add it to ``self.executed_queries`` and return True. Returns: changed (bool): True if publication has been updated, otherwise False. """ if self.exists: query_fragments = [] query_fragments.append( "DROP PUBLICATION %s" % pg_quote_identifier(self.name, 'publication')) if cascade: query_fragments.append("CASCADE") return self.__exec_sql(' '.join(query_fragments), check_mode=check_mode)
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 drop(self): """Drop tablespace. Return True if success, otherwise, return False. """ return exec_sql(self, "DROP TABLESPACE %s" % pg_quote_identifier(self.name, 'database'), ddl=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)