Open roskakori opened 9 years ago
Goal: a CID can act as base for interaction with SQL databases. In particular, it can be converted to:
create table
not null
with default
unique index
IsUnique
constraint
Choice
AbstractFieldFormat.sql_type()
tuple
(type, length, precision)
type
None
char
varchar
assert length is None
assert precision is None
(some,)
(some)
fields.AbstractFieldFormat.sql_ansi_type()
def sql_ansi_type(self): return ('varchar', None if self.length is None else self.length.upper)
fields.*FieldFormat.sql_ansi_type()
sql
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)` """
def createTableStatement(self): pass def createIndexStatements(self): pass def constraintStatements(self): pass
def bcpStatment(self, data_path): pass
def sqlldrControl(self, data_path): pass
def insertStatement(self, data_path): pass
def bulkInsertStatement(self, data_path): pass
IntegerFieldFormat
Decimal
Pattern, RegEx, Text:
DateTime
Integer
between 1 and 10
(between 1 and 10)
(between 20 and 30)
between 1 and "upper length/default"
between "upper length/default" and 1
(value == 1) or (value == 2) or (value == 3)
between 1 and 3
Simplified inheritance hierarchy according to Skype conference.
Goal: a CID can act as base for interaction with SQL databases. In particular, it can be converted to:
create table
statements , including:not null
unless it is allowed to be emptywith default
(unconditionally)unique index
in case ofIsUnique
checksconstraint
checks for e.g. range, length and validChoice
values.Design
AbstractFieldFormat.sql_type()
which returns atuple
of(type, length, precision)
, where onlytype
is required and must be one of:None
, meaning:char
andvarchar
assume 254assert length is None
assert precision is None
Note: a tuple with a single element is written(some,)
, not(some)
.fields.AbstractFieldFormat.sql_ansi_type()
:fields.*FieldFormat.sql_ansi_type()
sql
with a classSqlFactory
:98: Add option to build SQL create table statement
99: Add option to build bcp import script
100: Add option to build the contents for an SQL*Loader control file
101: Add option to build T/SQL bulk insert statements
Data type conversion
IntegerFieldFormat
Decimal
Choice
IntegerFieldFormat
Pattern, RegEx, Text:
DateTime
Rules
Integer
between 1 and 10
(between 1 and 10)
or(between 20 and 30)
between 1 and "upper length/default"
between "upper length/default" and 1
(value == 1) or (value == 2) or (value == 3)
; better:between 1 and 3