def sync_geometry_columns(schema=None, table=None):
"""
Adds one or more entries to the PostGIS geometry_columns
:param schema: Optional database schema to which to limit search
:param table: Optional table name to which to limit search
:return:
"""
tables_with_geometry = InformationSchema.objects.tables_with_geometry(schema=schema, table=table)
for information_scheme in tables_with_geometry:
conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
sql = "select ST_CoordDim({2}), ST_SRID({2}), ST_GeometryType({2}) from {1}.{0}".format(information_scheme.table_name, information_scheme.table_schema, information_scheme.column_name)
ret = cursor.execute(sql)
if ret and len(ret) > 0:
coord, srid, geom_type = ret[0]
else:
coord, srid, geom_type = (2, 4326, 'GEOMETRY')
geometry_record, new_record = GeometryColumns.objects.get_or_create(
f_table_name=information_scheme.table_name,
f_geometry_column=information_scheme.column_name,
f_table_schema=information_scheme.table_schema,
defaults=dict(
coord_dimension=coord,
srid=srid,
type=geom_type,
))
if not new_record:
geometry_record.coord_dimension = coord
geometry_record.srid = srid
geometry_record.type = geom_type
geometry_record.save()
def import_db(self, database_name, local_dump_file):
# Try to connect
main_db = pg_connection_parameters(settings.DATABASES['default'])
db = merge(main_db, dict(database=database_name))
db_conn_string = "--host={host} --port={port} --user={user}".format(**db)
self.run_as_pg('createdb {db_conn_string} {name}'.format(
db_conn_string=db_conn_string,
name=database_name), **db)
self.run_as_pg('psql {db_conn_string} -c "CREATE EXTENSION IF NOT EXISTS POSTGIS" {name}'.format(
db_conn_string=db_conn_string,
name=database_name), **db)
self.run_as_pg('psql {db_conn_string} -c "CREATE EXTENSION IF NOT EXISTS DBLINK" {name}'.format(
db_conn_string=db_conn_string,
name=database_name), **db)
self.run_as_pg('''psql {db_conn_string} -c 'ALTER DATABASE {name} SET search_path = "$user",public,postgis;' postgres'''.format(
db_conn_string=db_conn_string,
name=database_name), **db)
self.run_as_pg('psql {db_conn_string} -f {local_dump_file} {name}'.format(
db_conn_string=db_conn_string,
local_dump_file=local_dump_file,
name=database_name), **db)
def import_db(self, database_name, local_dump_file):
# Try to connect
main_db = pg_connection_parameters(settings.DATABASES['default'])
db = merge(main_db, dict(database=database_name))
db_conn_string = "--host={host} --port={port} --user={user}".format(
**db)
self.run_as_pg(
'createdb {db_conn_string} {name}'.format(
db_conn_string=db_conn_string, name=database_name), **db)
self.run_as_pg(
'psql {db_conn_string} -c "CREATE EXTENSION IF NOT EXISTS POSTGIS" {name}'
.format(db_conn_string=db_conn_string, name=database_name), **db)
self.run_as_pg(
'psql {db_conn_string} -c "CREATE EXTENSION IF NOT EXISTS DBLINK" {name}'
.format(db_conn_string=db_conn_string, name=database_name), **db)
self.run_as_pg(
'''psql {db_conn_string} -c 'ALTER DATABASE {name} SET search_path = "$user",public,postgis;' postgres'''
.format(db_conn_string=db_conn_string, name=database_name), **db)
self.run_as_pg(
'psql {db_conn_string} -f {local_dump_file} {name}'.format(
db_conn_string=db_conn_string,
local_dump_file=local_dump_file,
name=database_name), **db)
def report_sql_values(pSQL, fetch_type):
# with transaction.commit_manually():
try:
conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
curs = conn.cursor()
except Exception, E:
print str(E)
def drop_schema(self, schema, connection=connection):
if self.schema_exists(schema):
logger.info("Dropping schema %s" % schema)
conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = connection.cursor()
cursor.execute('drop schema {0} cascade'.format(schema))
logger.info("Schema %s dropped" % schema)
def copy_from_text_to_db(text_file, table_name):
conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
curs = conn.cursor()
try:
logger.debug("Text file: {0}, Table name: {1}".format(text_file, table_name))
curs.copy_from(text_file, table_name)
except Exception, E:
print str(E)
raise Exception('Original Message: {0}'.format(E.message))
def execute_sql(pSQL):
conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
curs = conn.cursor()
try:
logger.debug("executing custom sql: {0}".format(pSQL))
curs.execute(pSQL)
except Exception, E:
print str(E)
raise Exception('SQL: {0}. Original Message: {1}'.format(pSQL, E.message))
def drop_db(database_name):
"""
Drops and recreates the given database, which must be a database that present
in the default database server
"""
# Try to connect
db = pg_connection_parameters(settings.DATABASES['default'])
conn = psycopg2.connect(**db)
cur = conn.cursor()
conn.set_isolation_level(0)
cur.execute("""DROP DATABASE %s""" % database_name)
def drop_db(database_name):
"""
Drops and recreates the given database, which must be a database that present
in the default database server
"""
# Try to connect
db = pg_connection_parameters(settings.DATABASES['default'])
conn = psycopg2.connect(**db)
cur = conn.cursor()
conn.set_isolation_level(0)
cur.execute("""DROP DATABASE %s""" % database_name)
def create_schema(self, schema, connection=connection):
if not self.schema_exists(schema):
logger.info("Creating schema %s" % schema)
conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = connection.cursor()
cursor.execute('create schema {0}'.format(schema))
logger.info("Schema %s created" % schema)
# This has to be here to create the schema immediately. I don't know why
if transaction.is_managed():
transaction.commit()
else:
logger.info("Schema %s already exists" % schema)
def register_geometry_columns(schema=None):
select_tables = "Truncate geometry_columns cascade; \n" \
"select table_name, table_schema from information_schema.columns " \
"where column_name = 'wkb_geometry' " \
"and table_schema = '{0}';".format(schema)
if not schema:
select_tables = "Truncate geometry_columns cascade; \n" \
"select table_name, table_schema from information_schema.columns " \
"where column_name = 'wkb_geometry';"
params = pg_connection_parameters(settings.DATABASES['default'])
connection = psycopg2.connect(**params)
gCurs = connection.cursor()
try:
print select_tables
gCurs.execute(select_tables)
except Exception, E:
print str(E)
raise
def register_geometry_columns(schema=None):
select_tables = "Truncate geometry_columns cascade; \n" \
"select table_name, table_schema from information_schema.columns " \
"where column_name = 'wkb_geometry' " \
"and table_schema = '{0}';".format(schema)
if not schema:
select_tables = "Truncate geometry_columns cascade; \n" \
"select table_name, table_schema from information_schema.columns " \
"where column_name = 'wkb_geometry';"
params = pg_connection_parameters(settings.DATABASES['default'])
connection = psycopg2.connect(**params)
gCurs = connection.cursor()
try:
print select_tables
gCurs.execute(select_tables)
except Exception, E:
print str(E)
raise
def import_data(self, **kwargs):
"""
Imports data from an external source to create the test data
:return a two item tuple containing the region that was imported and a list of the imported projects
"""
# Calculate a sample lat/lon box of the config_entity
config_entity = self.config_entity
if self.test:
bounds = chop_geom(config_entity.bounds, 0.90)
logger.info(u"Creating subselection with extents: {0}. This will be used to crop any table that doesn't have a sample version".format(bounds))
conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
for db_entity in self.db_entities:
# This is the index on wkb_geometry.
spatial_index_name = '{schema}_{key}_geom_idx'.format(schema=db_entity.schema, key=db_entity.key)
table = db_entity.table
if db_entity.has_file_url:
# Remove any table of the same name from the import schema. This is unlikley since imported
# tables have timestamps
drop_table('"%s"."%s"' % (settings.IMPORT_SCHEMA, db_entity.key))
sql_file_path = file_url_to_path(db_entity.url)
# Create a command that pipes shp2pgsql to psql
db_entity.srid = db_entity.srid or '4326'
logger.info("verifying SRID {0}".format(db_entity.srid))
verify_srid(db_entity.srid)
# Create the import schema if needed
PGNamespace.objects.create_schema(settings.IMPORT_SCHEMA)
# Import the table
import_sql_command = '/usr/bin/psql {0} -f {1}'.format(self.target_database_connection, sql_file_path)
stdin = "{0}\n{1}".format(self.arguments.get('password', None), self.target_database.get('PASSWORD', None))
results = self.command_execution.run(import_sql_command, stdin=stdin)
if results.returncode:
raise Exception(results.stderr.text)
# We expect a table in the public schema with a named based on db_entity.key
# Move the table from the public schema to the db_entity schema
move_to_schema = "alter table {0}.{1} set schema {2};".format(settings.IMPORT_SCHEMA, db_entity.key, db_entity.schema)
logger.info("Moving import file table to schema: %s" % move_to_schema)
cursor.execute(move_to_schema)
# Drop the constraint that enforces the srid of the wkb_geometry if one exists
drop_constraint = '''alter table {0}.{1} drop constraint if exists enforce_srid_wkb_geometry'''.format(db_entity.schema, db_entity.key)
logger.info("Dropping constraint on wkb_geometry: %s" % drop_constraint)
cursor.execute(drop_constraint)
# Note we're not creating an index on wkb_geometry
# here because imported files already have an index
# created.
elif db_entity.has_db_url:
# The import database currently stores tables as
# public.[config_entity.key]_[feature_class._meta.db_table (with schema removed)][_sample (for samples)]
#
# We always use the table name without the word sample for the target table name
if settings.USE_SAMPLE_DATA_SETS or self.test:
source_table = "{0}_{1}_{2}".format(
config_entity.import_key or config_entity.key, db_entity.table, 'sample')
else:
source_table = "{0}_{1}".format(config_entity.import_key or config_entity.key, db_entity.table)
connection_dict = postgres_url_to_connection_dict(db_entity.url)
self._dump_tables_to_target(
'-t %s' % source_table,
source_schema='public',
target_schema=db_entity.schema,
source_table=source_table,
target_table=table,
connection_dict=connection_dict)
# Create a spatial index
spatial_index = '''create index {index_name} on {schema}.{key} using GIST (wkb_geometry);'''.format(
index_name=spatial_index_name,
schema=db_entity.schema, key=db_entity.key)
cursor.execute(spatial_index)
# Whether the table comes from our server or an upload, we want to transform the SRID to 4326
transform_to_4326 = 'ALTER TABLE {schema}.{table} ALTER COLUMN wkb_geometry ' \
'TYPE Geometry(geometry, 4326) ' \
'USING ST_Transform(ST_Force_2d(wkb_geometry), 4326);'.format
logger.info("Transforming to 4326: %s" % transform_to_4326(schema=db_entity.schema, table=db_entity.table))
cursor.execute(transform_to_4326(schema=db_entity.schema, table=db_entity.table))
# Now cluster the data and vacuum so that future joins are faster:
# * CLUSTER rewrites the data on disk so that rows that are spatially near each
# other are also near each other on disk
# * VACUUM cleans up disk space, removing sparse holes on disk.
# * ANALYZE regenerates statistics about wkb_geometry so that the query planner can make
# better decisions.
logger.info('Clustering %s.%s to optimize spatial joins', db_entity.schema, table)
cluster = 'CLUSTER {index_name} ON {target_schema}.{target_table};'.format(
index_name=spatial_index_name,
target_schema=db_entity.schema,
target_table=table)
cursor.execute(cluster)
logger.info('Vacuuming and analyzing %s.%s.', db_entity.schema, table)
analyze = 'VACUUM ANALYZE {target_schema}.{target_table};'.format(
target_schema=db_entity.schema,
target_table=table)
cursor.execute(analyze)
logger.info("Finished importing data for DbEntity table {0}.{1}".format(db_entity.schema, db_entity.key))
def import_data(self, **kwargs):
"""
Imports data from an external source to create the test data
:return a two item tuple containing the region that was imported and a list of the imported projects
"""
# Calculate a sample lat/lon box of the config_entity
config_entity = self.config_entity
if self.test:
bounds = chop_geom(config_entity.bounds, 0.90)
logger.info(
u"Creating subselection with extents: {0}. This will be used to crop any table that doesn't have a sample version"
.format(bounds))
conn = psycopg2.connect(
**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
for db_entity in self.db_entities:
# This is the index on wkb_geometry.
spatial_index_name = '{schema}_{key}_geom_idx'.format(
schema=db_entity.schema, key=db_entity.key)
table = db_entity.table
if db_entity.has_file_url:
# Remove any table of the same name from the import schema. This is unlikley since imported
# tables have timestamps
drop_table('"%s"."%s"' %
(settings.IMPORT_SCHEMA, db_entity.key))
sql_file_path = file_url_to_path(db_entity.url)
# Create a command that pipes shp2pgsql to psql
db_entity.srid = db_entity.srid or '4326'
logger.info("verifying SRID {0}".format(db_entity.srid))
verify_srid(db_entity.srid)
# Create the import schema if needed
PGNamespace.objects.create_schema(settings.IMPORT_SCHEMA)
# Import the table
import_sql_command = '/usr/bin/psql {0} -f {1}'.format(
self.target_database_connection, sql_file_path)
stdin = "{0}\n{1}".format(
self.arguments.get('password', None),
self.target_database.get('PASSWORD', None))
results = self.command_execution.run(import_sql_command,
stdin=stdin)
if results.returncode:
raise Exception(results.stderr.text)
# We expect a table in the public schema with a named based on db_entity.key
# Move the table from the public schema to the db_entity schema
move_to_schema = "alter table {0}.{1} set schema {2};".format(
settings.IMPORT_SCHEMA, db_entity.key, db_entity.schema)
logger.info("Moving import file table to schema: %s" %
move_to_schema)
cursor.execute(move_to_schema)
# Drop the constraint that enforces the srid of the wkb_geometry if one exists
drop_constraint = '''alter table {0}.{1} drop constraint if exists enforce_srid_wkb_geometry'''.format(
db_entity.schema, db_entity.key)
logger.info("Dropping constraint on wkb_geometry: %s" %
drop_constraint)
cursor.execute(drop_constraint)
# Note we're not creating an index on wkb_geometry
# here because imported files already have an index
# created.
elif db_entity.has_db_url:
# The import database currently stores tables as
# public.[config_entity.key]_[feature_class._meta.db_table (with schema removed)][_sample (for samples)]
#
# We always use the table name without the word sample for the target table name
if settings.USE_SAMPLE_DATA_SETS or self.test:
source_table = "{0}_{1}_{2}".format(
config_entity.import_key or config_entity.key,
db_entity.table, 'sample')
else:
source_table = "{0}_{1}".format(
config_entity.import_key or config_entity.key,
db_entity.table)
connection_dict = postgres_url_to_connection_dict(
db_entity.url)
self._dump_tables_to_target('-t %s' % source_table,
source_schema='public',
target_schema=db_entity.schema,
source_table=source_table,
target_table=table,
connection_dict=connection_dict)
# Create a spatial index
spatial_index = '''create index {index_name} on {schema}.{key} using GIST (wkb_geometry);'''.format(
index_name=spatial_index_name,
schema=db_entity.schema,
key=db_entity.key)
cursor.execute(spatial_index)
# Whether the table comes from our server or an upload, we want to transform the SRID to 4326
transform_to_4326 = 'ALTER TABLE {schema}.{table} ALTER COLUMN wkb_geometry ' \
'TYPE Geometry(geometry, 4326) ' \
'USING ST_Transform(ST_Force_2d(wkb_geometry), 4326);'.format
logger.info("Transforming to 4326: %s" % transform_to_4326(
schema=db_entity.schema, table=db_entity.table))
cursor.execute(
transform_to_4326(schema=db_entity.schema,
table=db_entity.table))
# Now cluster the data and vacuum so that future joins are faster:
# * CLUSTER rewrites the data on disk so that rows that are spatially near each
# other are also near each other on disk
# * VACUUM cleans up disk space, removing sparse holes on disk.
# * ANALYZE regenerates statistics about wkb_geometry so that the query planner can make
# better decisions.
logger.info('Clustering %s.%s to optimize spatial joins',
db_entity.schema, table)
cluster = 'CLUSTER {index_name} ON {target_schema}.{target_table};'.format(
index_name=spatial_index_name,
target_schema=db_entity.schema,
target_table=table)
cursor.execute(cluster)
logger.info('Vacuuming and analyzing %s.%s.', db_entity.schema,
table)
analyze = 'VACUUM ANALYZE {target_schema}.{target_table};'.format(
target_schema=db_entity.schema, target_table=table)
cursor.execute(analyze)
logger.info(
"Finished importing data for DbEntity table {0}.{1}".format(
db_entity.schema, db_entity.key))
def create_primary_key_column_from_another_column(cls,
schema,
table,
primary_key_column,
from_column=None):
"""
Adds the column of the given type to the given table if absent
:param schema: The database schema name
:param table: The table name
:param primary_key_column: Name of primary key column to create. If a primary key already exists it will be
renamed from this, unless from_column is specified, in which case the existing primary_key will lose its constraint
"""
full_tablename = '"{schema}"."{table}"'.format(schema=schema,
table=table)
conn = psycopg2.connect(
**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
existing_primary_key = InformationSchema.get_primary_key_name(
schema, table)
if existing_primary_key:
logger.info('Found existing primary key %s' % existing_primary_key)
if not InformationSchema.objects.has_column(schema, table,
primary_key_column):
# If not create a primary key or alter the existing one's name
# Copy values from the from_column to the new primary_key_column
if existing_primary_key and not from_column:
# Rename the primary key to primary_key_column and end
alter_source_id_sql = 'alter table {full_tablename} rename column {existing_primary_key} to {primary_key_column}'.format(
full_tablename=full_tablename,
existing_primary_key=existing_primary_key,
primary_key_column=primary_key_column)
logger.info(
'Existing primary key exists and no from column is specified. Executing: %s'
% alter_source_id_sql)
cursor.execute(alter_source_id_sql)
return
if from_column:
# Create a new primary key column without values
create_column_sql = 'alter table {full_tablename} add column {primary_key_column} integer'.format(
full_tablename=full_tablename,
primary_key_column=primary_key_column)
logger.info(
'From column is specified to be primary key source. Executing: %s'
% create_column_sql)
cursor.execute(create_column_sql)
# Copy values from the from_column, always casting to integer
update_sql = 'update {full_tablename} set {primary_key_column} = cast({from_column} AS integer)'.format(
full_tablename=full_tablename,
primary_key_column=primary_key_column,
from_column=from_column)
logger.info(
'Copying values from column to primary key. Executing: %s'
% update_sql)
cursor.execute(update_sql)
else:
# Populate with a serial primary key
alter_source_id_sql = 'alter table {full_tablename} add column {primary_key_column} serial primary key'.format(
full_tablename=full_tablename,
primary_key_column=primary_key_column)
logger.info(
'Copying values from column to primary key. Executing: %s'
% alter_source_id_sql)
cursor.execute(alter_source_id_sql)
# Drop the original_primary_key column if it exists
if existing_primary_key:
alter_source_id_sql = 'alter table {full_tablename} drop column {existing_primary_key}'.format(
full_tablename=full_tablename,
existing_primary_key=existing_primary_key)
logger.info(
'Existing primary key being removed. Executing: %s' %
alter_source_id_sql)
cursor.execute(alter_source_id_sql)
if from_column:
# Create the primary key constraint if we haven't yet
alter_source_id_sql = 'alter table {full_tablename} add constraint {table}_{schema}_{primary_key_column}_pk primary key ({primary_key_column})'.format(
full_tablename=full_tablename,
table=table,
schema=schema,
primary_key_column=primary_key_column)
logger.info('Adding constraint to primary key. Executing: %s' %
alter_source_id_sql)
cursor.execute(alter_source_id_sql)
elif existing_primary_key != primary_key_column:
# If there a column matching primary_key_column that isn't the primary key, we need to rename
# the primary_key_column to something unique and then rename existing_primary_key to primary_key_column
# Find a unique column to rename primary_key_column (e.g. renamed id to id_1, or id_2, etc)
unique_column = increment_key(primary_key_column)
while InformationSchema.objects.has_column(schema, table,
unique_column):
unique_column = increment_key(unique_column)
# Rename the primary_key_column
rename_primary_key_column_name_sql = 'alter table {full_tablename} rename column {primary_key_column} to {unique_column}'.format(
full_tablename=full_tablename,
primary_key_column=primary_key_column,
unique_column=unique_column)
logger.info(
'Existing column with primary key name exists that needs to be renamed: %s'
% rename_primary_key_column_name_sql)
cursor.execute(rename_primary_key_column_name_sql)
# Rename the existing_primary_key to primary_key_column (e.g. rename to ogc_fid to id for uploaded tables)
rename_existing_primary_key_sql = 'alter table {full_tablename} rename column {existing_primary_key} to {primary_key_column}'.format(
full_tablename=full_tablename,
existing_primary_key=existing_primary_key,
primary_key_column=primary_key_column)
logger.info(
'Existing primary key exists that needs to be renamed to desired primary key column name. Executing: %s'
% rename_existing_primary_key_sql)
cursor.execute(rename_existing_primary_key_sql)
def create_primary_key_column_from_another_column(cls, schema, table, primary_key_column, from_column=None):
"""
Adds the column of the given type to the given table if absent
:param schema: The database schema name
:param table: The table name
:param primary_key_column: Name of primary key column to create. If a primary key already exists it will be
renamed from this, unless from_column is specified, in which case the existing primary_key will lose its constraint
"""
full_tablename = '"{schema}"."{table}"'.format(schema=schema, table=table)
conn = psycopg2.connect(**pg_connection_parameters(settings.DATABASES['default']))
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
existing_primary_key = InformationSchema.get_primary_key_name(schema, table)
if existing_primary_key:
logger.info('Found existing primary key %s' % existing_primary_key)
if not InformationSchema.objects.has_column(schema, table, primary_key_column):
# If not create a primary key or alter the existing one's name
# Copy values from the from_column to the new primary_key_column
if existing_primary_key and not from_column:
# Rename the primary key to primary_key_column and end
alter_source_id_sql = 'alter table {full_tablename} rename column {existing_primary_key} to {primary_key_column}'.format(
full_tablename=full_tablename, existing_primary_key=existing_primary_key, primary_key_column=primary_key_column)
logger.info('Existing primary key exists and no from column is specified. Executing: %s' % alter_source_id_sql)
cursor.execute(alter_source_id_sql)
return
if from_column:
# Create a new primary key column without values
create_column_sql = 'alter table {full_tablename} add column {primary_key_column} integer'.format(
full_tablename=full_tablename, primary_key_column=primary_key_column)
logger.info('From column is specified to be primary key source. Executing: %s' % create_column_sql)
cursor.execute(create_column_sql)
# Copy values from the from_column, always casting to integer
update_sql = 'update {full_tablename} set {primary_key_column} = cast({from_column} AS integer)'.format(
full_tablename=full_tablename, primary_key_column=primary_key_column, from_column=from_column)
logger.info('Copying values from column to primary key. Executing: %s' % update_sql)
cursor.execute(update_sql)
else:
# Populate with a serial primary key
alter_source_id_sql = 'alter table {full_tablename} add column {primary_key_column} serial primary key'.format(
full_tablename=full_tablename, primary_key_column=primary_key_column)
logger.info('Copying values from column to primary key. Executing: %s' % alter_source_id_sql)
cursor.execute(alter_source_id_sql)
# Drop the original_primary_key column if it exists
if existing_primary_key:
alter_source_id_sql = 'alter table {full_tablename} drop column {existing_primary_key}'.format(
full_tablename=full_tablename, existing_primary_key=existing_primary_key)
logger.info('Existing primary key being removed. Executing: %s' % alter_source_id_sql)
cursor.execute(alter_source_id_sql)
if from_column:
# Create the primary key constraint if we haven't yet
alter_source_id_sql = 'alter table {full_tablename} add constraint {table}_{schema}_{primary_key_column}_pk primary key ({primary_key_column})'.format(
full_tablename=full_tablename, table=table, schema=schema, primary_key_column=primary_key_column)
logger.info('Adding constraint to primary key. Executing: %s' % alter_source_id_sql)
cursor.execute(alter_source_id_sql)
elif existing_primary_key != primary_key_column:
# If there a column matching primary_key_column that isn't the primary key, we need to rename
# the primary_key_column to something unique and then rename existing_primary_key to primary_key_column
# Find a unique column to rename primary_key_column (e.g. renamed id to id_1, or id_2, etc)
unique_column = increment_key(primary_key_column)
while InformationSchema.objects.has_column(schema, table, unique_column):
unique_column = increment_key(unique_column)
# Rename the primary_key_column
rename_primary_key_column_name_sql = 'alter table {full_tablename} rename column {primary_key_column} to {unique_column}'.format(
full_tablename=full_tablename, primary_key_column=primary_key_column, unique_column=unique_column)
logger.info('Existing column with primary key name exists that needs to be renamed: %s' % rename_primary_key_column_name_sql)
cursor.execute(rename_primary_key_column_name_sql)
# Rename the existing_primary_key to primary_key_column (e.g. rename to ogc_fid to id for uploaded tables)
rename_existing_primary_key_sql = 'alter table {full_tablename} rename column {existing_primary_key} to {primary_key_column}'.format(
full_tablename=full_tablename, existing_primary_key=existing_primary_key, primary_key_column=primary_key_column)
logger.info('Existing primary key exists that needs to be renamed to desired primary key column name. Executing: %s' % rename_existing_primary_key_sql)
cursor.execute(rename_existing_primary_key_sql)
