def _drop_table(self, table_name): '''Drop a table''' cursor = connection.cursor() if is_configured_postgres(): # cursor.execute("DROP TABLE %s" % table_name) cursor.execute("DROP TABLE %s CASCADE" % table_name) else: cursor.execute("DROP TABLE %s" % table_name)
def _table_exists(self, table_name): '''Check if a table exists''' cursor = connection.cursor() if is_configured_postgres(): cursor.execute("select * from pg_tables where schemaname='public' and tablename='%s'" % table_name) else: cursor.execute("show tables like '%s'" % table_name) return len(cursor.fetchall()) == 1
def _get_db_type(self, type): type = type.lower() if is_configured_mysql(): if type in self.XSD_TO_MYSQL_TYPES: return self.XSD_TO_MYSQL_TYPES[type] return self.XSD_TO_MYSQL_TYPES['default'] elif is_configured_postgres(): if type in self.XSD_TO_PGSQL_TYPES: return self.XSD_TO_PGSQL_TYPES[type] return self.XSD_TO_PGSQL_TYPES['default'] else: if type in self.XSD_TO_DEFAULT_TYPES: return self.XSD_TO_DEFAULT_TYPES[type] return self.XSD_TO_DEFAULT_TYPES['default']
def _execute(self, queries, values): # todo - rollback on fail if queries is None or len(queries) == 0: logging.error("xformmanager: storageutility - xform " + self.formdef.target_namespace + " could not be parsed") return cursor = connection.cursor() cursor.execute(queries, values) if is_configured_mysql(): query = "SELECT LAST_INSERT_ID();" elif is_configured_postgres(): # hat tips: # http://archives.postgresql.org/pgsql-general/2008-08/msg01044.php # http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL query = "SELECT CURRVAL(pg_get_serial_sequence('%s','id'));" % self.table_name else: query = "SELECT LAST_INSERT_ROWID()" cursor.execute(query) row = cursor.fetchone() if row is not None: return row[0] return -1
def queries_to_create_instance_tables(self, elementdef, parent_id, parent_name='', parent_table_name='', domain=None): table_name = format_table_name( formatted_join(parent_name, elementdef.name), self.formdef.version, self.formdef.domain_name ) (next_query, fields) = self._create_instance_tables_query_inner_loop(elementdef, parent_id, parent_name, parent_table_name ) # add this later - should never be called during unit tests if not fields: return next_query queries = '' if is_configured_mysql(): queries = "CREATE TABLE "+ table_name +" ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, " else: queries = "CREATE TABLE "+ table_name +" ( id SERIAL NOT NULL, " if len(fields[0]) == 1: queries = queries + str(fields) else: for field in fields: if len(field)>0: queries = queries + str(field) # remove ? for encoding test queries = queries.replace("?", "__") # we don't really need a parent_id in our top-level table... # should be NOT NULL? if parent_name is not '': if is_configured_mysql(): queries = queries + " parent_id INT(11), " queries = queries + " FOREIGN KEY (parent_id) REFERENCES " + \ format_table_name(parent_table_name, self.formdef.version, self.formdef.domain_name) + \ "(id) ON DELETE SET NULL" elif is_configured_postgres(): queries = queries + " parent_id INTEGER " queries = queries + " REFERENCES " + \ format_table_name(parent_table_name, self.formdef.version, self.formdef.domain_name) + \ "(id) ON DELETE SET NULL" else: queries = queries + " parent_id REFERENCES " + \ format_table_name(parent_table_name, self.formdef.version, self.formdef.domain_name) + \ "(id) ON DELETE SET NULL" else: queries = self._trim2chars(queries) # most of the time, we rely on global mysql config in my.conf/ini if is_configured_postgres(): queries = queries + ", CONSTRAINT %s_pkey PRIMARY KEY (id)" % table_name end_query = ");" # we only specify default engine and character set if it's clear that # we are already doing something against the global config # (i.e. we're settings database_options in settings.py) if hasattr(settings,'DATABASE_OPTIONS') and \ 'init_command' in settings.DATABASE_OPTIONS: if 'innodb' in settings.DATABASE_OPTIONS['init_command'].lower(): end_query = ") ENGINE=InnoDB;" elif 'myisam' in settings.DATABASE_OPTIONS['init_command'].lower(): end_query = ") ENGINE=MyISAM;" queries = queries + end_query + next_query return queries