Convert CID to SQL statements

[roskakori]

trafficstars

Goal: a CID can act as base for interaction with SQL databases. In particular, it can be converted to:

• create table statements , including:
  • field name
  • datatype
  • not null unless it is allowed to be empty
  • with default (unconditionally)
  • unique index in case of IsUnique checks
  • constraint checks for e.g. range, length and valid Choice values.
• load/unload scripts (for delimited and fixed formats)

Design

• Add AbstractFieldFormat.sql_type() which returns a tuple of (type, length, precision), where only type is required and must be one of:
  • 'char'
  • 'date'
  • 'datetime'
  • 'decimal'
  • 'int'
  • 'varchar' All other value in this tuple can be None, meaning:
  • length:
    • for char and varchar assume 254
    • for decimal: use as scale (number of digits)
    • for everything else: assert length is None
  • precision:
    • for decimal: use as precision (number of digits after dot)
    • for everything else: assert precision is None Note: a tuple with a single element is written (some,), not (some).
• Implement fields.AbstractFieldFormat.sql_ansi_type():

def sql_ansi_type(self):
    return ('varchar', None if self.length is None else self.length.upper)

• Implement fields.*FieldFormat.sql_ansi_type()
• Add module sql with a class SqlFactory:

class AnsiSqlDialect(object):
    def __init__(self):
        self._keyword = set([...])

    def sql_type(self, sql_ansi_type):
        """Same kind of tuple as with py:meth`fields.AbstractFieldFormat.sql_ansi_type().`"""
        return sql_ansi_type

    def sql_escaped(self, text):
        # TODO: Escape characters < 32.
        return "'" + text.replace("'", "''") + "'"

    @property
    def keywords(self):
        return self._keywords

    def is_keyword(self, word):
        assert word is not None
        return word in self.keywords

ANSI_SQL_DIALECT = AnsiSqlDialect()

class SqlFactory(object):
    def __init__(self, cid, table, dialect=ANSI_SQL_DIALECT):
        self._cid = cid
        self... = ...
    @property
    def cid(self):
        return self._cid
    def sql_fields(self):
        """
        Tuples `(field_name, field_type, length, precision, is_not_null, default_value)`
        """

#98: Add option to build SQL create table statement

def createTableStatement(self):
    pass
def createIndexStatements(self):
    pass
def constraintStatements(self):
    pass

#99: Add option to build bcp import script

def bcpStatment(self, data_path):
    pass

#100: Add option to build the contents for an SQL*Loader control file

def sqlldrControl(self, data_path):
    pass

def insertStatement(self, data_path):
    pass

#101: Add option to build T/SQL bulk insert statements

def bulkInsertStatement(self, data_path):
    pass

Data type conversion

IntegerFieldFormat

• no rule and length: integer
• no rule but length: range = between -(10 * (length - 1) + 1) and (10*length - 1)
• range between 32767 and -32768: smallint
• range between 2^31-1 and -2^31: integer
• ms,db2: between 2^63-1 and -2^63: bigint
• otherwise: decimal(number_of_digits, 0)

Decimal

• no rule and length: decimal(32,8)
• no rule but length: precision = round(0.75 * length), scale = length - precision --> decimal(precision, scale)
• with rule and scale > 0: decimal(precision, scale)
• with rule and scale = 0: like IntegerFieldFormat

Choice

• if all choice values are integers: like IntegerFieldFormat
• otherwise: varchar(maximum length of choice texts)

Pattern, RegEx, Text:

• if only lower or no length: varchar(255)
• if only upper length: varchar(length)
• if fixed length: char(length)

DateTime

• if only hh, mm, ss: time
• if only YYYY or YY, MM, TT: date
• if both of above: datetime

Rules

Integer

• 1...10 --> between 1 and 10
• 1...10, 20...30 --> (between 1 and 10) or (between 20 and 30)
• 1... --> between 1 and "upper length/default"
• ...1 --> between "upper length/default" and 1
• 1, 2, 3 --> (value == 1) or (value == 2) or (value == 3); better: between 1 and 3

[roskakori]

Simplified inheritance hierarchy according to Skype conference.