def meta_data_to_db_insert_text(info_and_data_list, db_type, db_statement_sep=None):
"""
Convert a list of meta-data texts (along with table info objects) into a text containing insert
statement for a given database.
@param IN info_and_data_list List of TableInfo object and meta-data text pairs
@param IN db_type DB type (MySQL, Oracle, ...)
@param IN db_statement_sep Separator to use for the insert statements; default: ";"
@return The insert statements as a string
"""
if db_statement_sep is None:
db_statement_sep = ";"
# Get the DB-specific functions/classes
try:
xy_to_db_name_func = _name_func_by_db_type[db_type.lower()]
meta_data_converter = _meta_data_converter_cls_by_db_type[db_type.lower()]()
except KeyError:
raise Exception("DB type not supported: '%s'" % db_type)
# Identify the order of insertion
info_and_data_by_table_name = dict([(item[0].name, item) for item in info_and_data_list])
table_info_list = [item[0] for item in info_and_data_list]
table_order = get_table_creation_order(table_info_list)
# Process each table in the insertion order
output_lines = []
for table_name in table_order:
table_info, meta_data_text = info_and_data_by_table_name[table_name]
# Convert the meta-data in the data text
db_data_text = meta_data_converter.meta_to_db_text(meta_data_text)
# Get the DB table and column names
db_table_name = xy_to_db_name_func(table_name)
db_col_names = [xy_to_db_name_func(col_info.name) for col_info in table_info.columns]
db_col_list_str = ", ".join(db_col_names)
nb_col_names = len(db_col_names)
# Process the data rows
rows = db_data_text.splitlines()
for row in rows:
row = row.strip()
if not row or row.startswith("//"):
continue
values = row.split("\t")
if len(values) != nb_col_names:
raise Exception("Incorrect number of values (%d expected):\n%s" % (nb_col_names,
values))
insert_statement = "INSERT INTO %s (%s) VALUES (%s)%s" % (
db_table_name,
db_col_list_str,
", ".join(values),
db_statement_sep)
output_lines.append(insert_statement)
return "\n".join(output_lines)
def table_info_list_to_django_model_text(table_info_list, xy_to_db_name_func):
"""
Convert a list of table info objects into a Django model text.
@param IN table_info_list List of table info objects
@param IN xy_to_db_name_func Function taking a Xylinq name and returning its DB-compatible name
@return The Django model text as a string
"""
# Using """""" so that Perforce does not try any substitution here when committing this file
result_lines = [
"""\"\"\"
Django model for Xylinq.
$Header"""""": $
$Author"""""": $
$DateTime"""""": $
$Change"""""": $
\"\"\"
from django.db import models""",
]
table_info_by_name = dict([(table_info.name, table_info) for table_info in table_info_list])
table_order = get_table_creation_order(table_info_list)
for table_name in table_order:
table_info = table_info_by_name[table_name]
# Determine the field to use as primary key:
# - the table's primary key if it is single-column
# - the first single-column table's unique key otherwise
# Also identify the other unique fields and the composite unique keys
primary_field = None
unique_fields = []
unique_composites = []
pk_col_names = table_info.primary_key.column_names
if len(pk_col_names) == 1:
primary_field = _get_field_name(pk_col_names[0])
else:
unique_composites.append([_get_field_name(col_name) for col_name in pk_col_names])
for uk_info in table_info.unique_keys:
uk_col_names = uk_info.column_names
if len(uk_col_names) == 1:
if primary_field is None:
primary_field = _get_field_name(uk_col_names[0])
else:
unique_fields.append(_get_field_name(uk_col_names[0]))
else:
unique_composites.append([_get_field_name(col_name) for col_name in uk_col_names])
if primary_field is None:
raise Exception("Cannot find a single field to use as primary key for table %s" %
table_name)
# One-to-one and many-to-one fields
# Map a field name to a (target class name, target field name) tuple
one_to_one_fields = {}
many_to_one_fields = {}
for fk_info in table_info.foreign_keys:
fk_col_names = fk_info.column_names
if len(fk_col_names) == 1:
field_name = _get_field_name(fk_col_names[0])
target_cls_name = _get_class_name(fk_info.target_table_name)
target_field_name = _get_field_name(fk_info.target_column_names[0])
if field_name == primary_field or field_name in unique_fields:
one_to_one_fields[field_name] = (target_cls_name, target_field_name)
else:
many_to_one_fields[field_name] = (target_cls_name, target_field_name)
# Class header line
cls_name = _get_class_name(table_name)
result_lines.append("""
class %s(models.Model):""" % cls_name)
# Meta class lines
db_table_name = xy_to_db_name_func(table_name)
result_lines.append(""" class Meta:
db_table = u"%s\"""" % db_table_name)
# The "managed" attribute is only supported by Django's dev version for now, so commenting
# out the below line
#result_lines.append(""" managed = False""")
if unique_composites:
result_lines.append(""" unique_together = (""")
for composite in unique_composites:
result_lines.append(""" ("%s"),""" % "\", \"".join(composite))
result_lines.append(""" )""")
# Fields lines
for col_info in table_info.columns:
col_name = col_info.name
field_name = _get_field_name(col_name)
db_col_name = xy_to_db_name_func(col_name)
pk_str = field_name == primary_field and ", primary_key=True" or ""
# NOTE:
# The Django doc mentions the following about the "null" attribute:
# "
# Avoid using null on string-based fields such as CharField and TextField unless you
# have an excellent reason. If a string-based field has null=True, that means it has
# two possible values for "no data": NULL, and the empty string. In most cases, it's
# redundant to have two possible values for "no data"; Django convention is to use
# the empty string, not NULL.
# "
# However, I believe this is only relevant if we plan to enter data through Django's
# admin site, which is not our case.
# On top of that, some DBMS do not consider empty string and null as the same.
# Here, we set the "null" attribute as it is in the DB independently of the type.
if field_name == primary_field:
null_str = ""
else:
null_str = ", null=%s" % (col_info.nullable and "True" or "False")
if field_name in one_to_one_fields:
target_cls_name, target_field_name = one_to_one_fields[field_name]
if target_cls_name == cls_name:
target_cls_name = "\"self\""
result_lines.append(
""" %s = models.OneToOneField(%s, to_field="%s", db_column="%s"%s%s)""" % (
field_name, target_cls_name, target_field_name, db_col_name, pk_str, null_str))
elif field_name in many_to_one_fields:
target_cls_name, target_field_name = many_to_one_fields[field_name]
if target_cls_name == cls_name:
target_cls_name = "\"self\""
result_lines.append(
""" %s = models.ForeignKey(%s, to_field="%s", db_column="%s"%s%s)""" % (
field_name, target_cls_name, target_field_name, db_col_name, pk_str, null_str))
else:
type_cls_name, type_attrs_str = _get_field_type(col_info)
unique_str = field_name in unique_fields and ", unique=True" or ""
result_lines.append(
""" %s = models.%s(%sdb_column="%s"%s%s%s)""" % (
field_name, type_cls_name, type_attrs_str, db_col_name, pk_str, unique_str,
null_str))
return "\n".join(result_lines)
def table_info_list_to_django_model_text(table_info_list, xy_to_db_name_func):
"""
Convert a list of table info objects into a Django model text.
@param IN table_info_list List of table info objects
@param IN xy_to_db_name_func Function taking a Xylinq name and returning its DB-compatible name
@return The Django model text as a string
"""
# Using """""" so that Perforce does not try any substitution here when committing this file
result_lines = [
"""\"\"\"
Django model for Xylinq.
$Header"""
""": $
$Author"""
""": $
$DateTime"""
""": $
$Change"""
""": $
\"\"\"
from django.db import models""",
]
table_info_by_name = dict([(table_info.name, table_info)
for table_info in table_info_list])
table_order = get_table_creation_order(table_info_list)
for table_name in table_order:
table_info = table_info_by_name[table_name]
# Determine the field to use as primary key:
# - the table's primary key if it is single-column
# - the first single-column table's unique key otherwise
# Also identify the other unique fields and the composite unique keys
primary_field = None
unique_fields = []
unique_composites = []
pk_col_names = table_info.primary_key.column_names
if len(pk_col_names) == 1:
primary_field = _get_field_name(pk_col_names[0])
else:
unique_composites.append(
[_get_field_name(col_name) for col_name in pk_col_names])
for uk_info in table_info.unique_keys:
uk_col_names = uk_info.column_names
if len(uk_col_names) == 1:
if primary_field is None:
primary_field = _get_field_name(uk_col_names[0])
else:
unique_fields.append(_get_field_name(uk_col_names[0]))
else:
unique_composites.append(
[_get_field_name(col_name) for col_name in uk_col_names])
if primary_field is None:
raise Exception(
"Cannot find a single field to use as primary key for table %s"
% table_name)
# One-to-one and many-to-one fields
# Map a field name to a (target class name, target field name) tuple
one_to_one_fields = {}
many_to_one_fields = {}
for fk_info in table_info.foreign_keys:
fk_col_names = fk_info.column_names
if len(fk_col_names) == 1:
field_name = _get_field_name(fk_col_names[0])
target_cls_name = _get_class_name(fk_info.target_table_name)
target_field_name = _get_field_name(
fk_info.target_column_names[0])
if field_name == primary_field or field_name in unique_fields:
one_to_one_fields[field_name] = (target_cls_name,
target_field_name)
else:
many_to_one_fields[field_name] = (target_cls_name,
target_field_name)
# Class header line
cls_name = _get_class_name(table_name)
result_lines.append("""
class %s(models.Model):""" % cls_name)
# Meta class lines
db_table_name = xy_to_db_name_func(table_name)
result_lines.append(""" class Meta:
db_table = u"%s\"""" % db_table_name)
# The "managed" attribute is only supported by Django's dev version for now, so commenting
# out the below line
#result_lines.append(""" managed = False""")
if unique_composites:
result_lines.append(""" unique_together = (""")
for composite in unique_composites:
result_lines.append(""" ("%s"),""" %
"\", \"".join(composite))
result_lines.append(""" )""")
# Fields lines
for col_info in table_info.columns:
col_name = col_info.name
field_name = _get_field_name(col_name)
db_col_name = xy_to_db_name_func(col_name)
pk_str = field_name == primary_field and ", primary_key=True" or ""
# NOTE:
# The Django doc mentions the following about the "null" attribute:
# "
# Avoid using null on string-based fields such as CharField and TextField unless you
# have an excellent reason. If a string-based field has null=True, that means it has
# two possible values for "no data": NULL, and the empty string. In most cases, it's
# redundant to have two possible values for "no data"; Django convention is to use
# the empty string, not NULL.
# "
# However, I believe this is only relevant if we plan to enter data through Django's
# admin site, which is not our case.
# On top of that, some DBMS do not consider empty string and null as the same.
# Here, we set the "null" attribute as it is in the DB independently of the type.
if field_name == primary_field:
null_str = ""
else:
null_str = ", null=%s" % (col_info.nullable and "True"
or "False")
if field_name in one_to_one_fields:
target_cls_name, target_field_name = one_to_one_fields[
field_name]
if target_cls_name == cls_name:
target_cls_name = "\"self\""
result_lines.append(
""" %s = models.OneToOneField(%s, to_field="%s", db_column="%s"%s%s)"""
% (field_name, target_cls_name, target_field_name,
db_col_name, pk_str, null_str))
elif field_name in many_to_one_fields:
target_cls_name, target_field_name = many_to_one_fields[
field_name]
if target_cls_name == cls_name:
target_cls_name = "\"self\""
result_lines.append(
""" %s = models.ForeignKey(%s, to_field="%s", db_column="%s"%s%s)"""
% (field_name, target_cls_name, target_field_name,
db_col_name, pk_str, null_str))
else:
type_cls_name, type_attrs_str = _get_field_type(col_info)
unique_str = field_name in unique_fields and ", unique=True" or ""
result_lines.append(
""" %s = models.%s(%sdb_column="%s"%s%s%s)""" %
(field_name, type_cls_name, type_attrs_str, db_col_name,
pk_str, unique_str, null_str))
return "\n".join(result_lines)
def meta_data_to_db_insert_text(info_and_data_list,
db_type,
db_statement_sep=None):
"""
Convert a list of meta-data texts (along with table info objects) into a text containing insert
statement for a given database.
@param IN info_and_data_list List of TableInfo object and meta-data text pairs
@param IN db_type DB type (MySQL, Oracle, ...)
@param IN db_statement_sep Separator to use for the insert statements; default: ";"
@return The insert statements as a string
"""
if db_statement_sep is None:
db_statement_sep = ";"
# Get the DB-specific functions/classes
try:
xy_to_db_name_func = _name_func_by_db_type[db_type.lower()]
meta_data_converter = _meta_data_converter_cls_by_db_type[
db_type.lower()]()
except KeyError:
raise Exception("DB type not supported: '%s'" % db_type)
# Identify the order of insertion
info_and_data_by_table_name = dict([(item[0].name, item)
for item in info_and_data_list])
table_info_list = [item[0] for item in info_and_data_list]
table_order = get_table_creation_order(table_info_list)
# Process each table in the insertion order
output_lines = []
for table_name in table_order:
table_info, meta_data_text = info_and_data_by_table_name[table_name]
# Convert the meta-data in the data text
db_data_text = meta_data_converter.meta_to_db_text(meta_data_text)
# Get the DB table and column names
db_table_name = xy_to_db_name_func(table_name)
db_col_names = [
xy_to_db_name_func(col_info.name)
for col_info in table_info.columns
]
db_col_list_str = ", ".join(db_col_names)
nb_col_names = len(db_col_names)
# Process the data rows
rows = db_data_text.splitlines()
for row in rows:
row = row.strip()
if not row or row.startswith("//"):
continue
values = row.split("\t")
if len(values) != nb_col_names:
raise Exception(
"Incorrect number of values (%d expected):\n%s" %
(nb_col_names, values))
insert_statement = "INSERT INTO %s (%s) VALUES (%s)%s" % (
db_table_name, db_col_list_str, ", ".join(values),
db_statement_sep)
output_lines.append(insert_statement)
return "\n".join(output_lines)
