[db-api/configuration] simplify db-api and configuration so that all the connection information is in the repository url, closes #2521848
eg no more specific option of pyro ns host, group, etc. This also fixes broken ZMQ
sources.
Changes:
* dropped pyro-ns-host, pyro-instance-id, pyro-ns-group from client side config, in
favor of repository-uri. No migration done, supposing there is **no web-only config**
in the wild. Also stop discovering the connection method through the repo_method class
attribute of the configuration, varying according to the configuration class. This is
a first step on the way to a simpler configuration handling. Notice those pyro options
are still available for repository only / all-in-one configurations as they are needed
to configure the pyro server.
* stop telling connection method using ConnectionProperties, this is so boring. Also,
drop _cnxtype from Connection and cnxtype from Session. The former is replaced by a
is_repo_in_memory property and the later is totaly useless.
* deprecate in_memory_cnx which becomes useless, use _repo_connect instead
def migrate_varchar_to_nvarchar():
dbdriver = config.sources()['system']['db-driver']
if dbdriver != "sqlserver2005":
return
introspection_sql = """\
SELECT table_schema, table_name, column_name, is_nullable, character_maximum_length
FROM information_schema.columns
WHERE data_type = 'VARCHAR' and table_name <> 'SYSDIAGRAMS'
"""
has_index_sql = """\
SELECT i.name AS index_name,
i.type_desc,
i.is_unique,
i.is_unique_constraint
FROM sys.indexes AS i, sys.index_columns as j, sys.columns as k
WHERE is_hypothetical = 0 AND i.index_id <> 0
AND i.object_id = j.object_id
AND i.index_id = j.index_id
AND i.object_id = OBJECT_ID('%(table)s')
AND k.name = '%(col)s'
AND k.object_id=i.object_id
AND j.column_id = k.column_id;"""
generated_statements = []
for schema, table, column, is_nullable, length in sql(introspection_sql, ask_confirm=False):
qualified_table = '[%s].[%s]' % (schema, table)
rset = sql(has_index_sql % {'table': qualified_table, 'col':column},
ask_confirm = False)
drops = []
creates = []
for idx_name, idx_type, idx_unique, is_unique_constraint in rset:
if is_unique_constraint:
drops.append('ALTER TABLE %s DROP CONSTRAINT %s' % (qualified_table, idx_name))
creates.append('ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)' % (qualified_table, idx_name, column))
else:
drops.append('DROP INDEX %s ON %s' % (idx_name, qualified_table))
if idx_unique:
unique = 'UNIQUE'
else:
unique = ''
creates.append('CREATE %s %s INDEX %s ON %s(%s)' % (unique, idx_type, idx_name, qualified_table, column))
if length == -1:
length = 'max'
if is_nullable == 'YES':
not_null = 'NULL'
else:
not_null = 'NOT NULL'
alter_sql = 'ALTER TABLE %s ALTER COLUMN %s NVARCHAR(%s) %s' % (qualified_table, column, length, not_null)
generated_statements+= drops + [alter_sql] + creates
for statement in generated_statements:
print statement
sql(statement, ask_confirm=False)
commit()
migrate_varchar_to_nvarchar()