Re: [GNUe-dev] Proposal for an implementation of Schema.Creation

[Jason Cater]

Johannes, 

This looks pretty good, though I have two suggestions:

> class gnue-common.datasources.GSchemaCreation.GSchemaCreation:
>
>   This class defines the interface which descendants (drivers) have to
>   implement. All these functions are virtual if not otherwise stated and
>   therefor just do nothing.

Given that paragraph description, I wonder if this base class should go into 
gnue.common.datasources.drivers.Base.Schema.Creation, instead of 
gnue.common.datasources.GSchemaCreation. Especially if the class' only 
purpose is for the datadrivers to extend it. 

> gnue-common.datasources.drivers.<vendors>.Schema.Creation.Creation:
>
>   These 'per-driver' implementations would derive from...

I would strongly consider adding a flag to these classes along the lines of 
SUPPORTS_WRITABLE_SCHEMAS=(True|False) that indicates whether this database 
driver can write schemas directly to the database, or whether the user will 
have to use another tool to load our gsd->sql files. 

At one time (though it may now), the oracle driver did not support writing DDL 
commands.  So it would be best to mark the ones that can't, so we can warn 
the user up front before their expectation gets too high.   

Hopefully, the vast majority (or, even all of them) will support writing. 

That's my two cents. 

-- Jason 

On Friday 11 June 2004 03:15 am, Johannes Vetter wrote:
> Hello Folx,
>
> I sat down this week and have thought about a way to implement
> Schema-Creation in a quite flexible way. This is a first draft then,
> please give me some feedback, point out where i did mistakes or which
> parts are just bullshit :)
>
>
> Basic concept for classes needed for Schema.Creation:
> =====================================================
>
> The following class tree is schematic only not subject to change during
> implementation phase (where usually the real tricky things occur). But
> the principal should hold.
>
>
> class gnue-common.datasources.GSchemaCreation.GSchemaCreation:
>
>   This class defines the interface which descendants (drivers) have to
>   implement. All these functions are virtual if not otherwise stated and
>   therefor just do nothing.
>
>   Constructor:
>     __init__ (connection = None, introspector = None, filehandle = None)
>
>       If a connection is given it can be used to perform all requested
>       tasks. A DBSIG2-driver could use its' sql() method to execute the
>       sql statements.
>
>       IF an introspector is given, it could be used to query for
>       existing objects in a database.
>
>       If a filehandle is given it can be used to write all code to it,
>       i.e. gnue-schema could use this option to create sql-files.
>
>
>   Constants:
>     MAX_NAME_LENGTH
>       This value gives the maximum number of characters for an
>       identifier
>
>     MAX_LINE_LENGTH
>       This value gives the maximum number of characters for a single
>       line. This values is used when writing to a filehandle.
>
>     QUOTECHAR
>       Character(-sequence) to be used for quoting strings.
>
>
>   Properties:
>     connection
>       Value of the constructor's connection parameter
>     filenhandle
>       Value of the constructor's filehandle parameter
>     introspector
>       Value of the constructor's introspector parameter
>     isOnline
>       Boolean value which is TRUE, if the output is sent to a connection
>       object and FALSE if the output has to be sent to the filehandle
>
>
>   Public Methods:
>     * The following methods get called with a definition and an optional
>       boolean argument 'codeOnly'. If this flag is TRUE, the called
>       method should only return the code needed to perform its' task.
>       This could be used by 'createTable' to create code for a primary
>       key or things like that.
>
>     * All these functions return a 3-tuple of sequences like
>       (prologue, body, epilogue). These sequences contain the code to
>       perform a given action grouped into categories.
>
>
>     createTable (<Table-Definition>, codeOnly = False)
>       This function creates a table for the given definition. *virtual*
>
>     createPrimaryKey (tablename, <Primarykey-Definition>,
>                       codeOnly = False)
>       This function creates a Primary Key for the given definition.
>       *virtual*
>
>     createIndex (tablename, <Index-Definition>, codeOnly = False)
>       This function creates an index for the given definition. *virtual*
>
>     createConstraint (tablename, <Constraint-Definition>,
>                       codeOnly = False)
>       This function creates a constraint for the given definition.
>       *virtual*
>
>     createField (tablename, <Field-Definition>, forAlter=False,
>                  codeOnly=True)
>       This fuction creates/alters a field in the given table. *virtual*
>
>     alterTable (<Table-Definition>, codeOnly = False)
>       This function alters a table according to the given definition.
>       *virtual*
>
>     ifExist (elementName, elementType):
>       This function return TRUE if the given element could be found by
>       the introspector or FALSE otherwise.
>
>
>     <typename> (<Field-Definition>) *abstract*
>       A descandant of GSchemaCreation can implement functions called
>       '<typename>' where '<typename>' stands for a type-identifier like
>       'string', 'number', 'date', and so on. These functions get a field
>       definition and should return a native datatype representation for
>       this field as string.
>
>
>   Protected Methods:
>
>     _translateType (<Field-Definition>)
>       This function is implemented in GSchemaCreation and usually won't
>       be overridden by a descendant. It takes a field-definiton's type
>       and looks for the proper method to translate this type into a
>       native datatype representation. The search will be started in the
>       current class and if not successfull will be continued in all
>       superclasses. The methods for datatype translation must match the
>       field-definition's typename, i.e. 'string', 'number', 'date'.
>
>
>     _getSequenceName (tablename, <Field-Definition>)
>       This function creates a name for a sequence-like element using the
>       tablename and avilable information from the passed
>       field-definition. The resulting string respects given restrictions
>       in length (MAX_NAME_LENGTH).
>
>     _escapeString (aString) *virtual*
>       This function has to make 'aString' safe for output. Descendants
>       would provide alternate implementations depending of the
>       isOnline-Flag.
>
>     _quoteString (aString)
>       This function has to escape and quote 'aString'. It's doing this
>       using the constant QUOTECHAR and the method '_escapeString'.
>
>
> class gnue-common.datasources.drivers.DBSIG2.Schema.Creation.Creation:
>
>   This class is derived from gnue-common.datasources.GSchemaCreation,
>   and implements the defined interface by means of providing a general
>   implementation for SQL-capable drivers. It would implement
>   type-conversions for SQL-92 standard types, so descendands only have
>   to replace their specials.
>
>
>   Constants:
>
>     ALTER_MULTIPLE
>       Boolean value determining wether a driver can handle multiple
>       fields in a single 'ALTER TABLE' statement or not
>
>
>   Public Methods:
>
>     createTable ()
>       This function iterates over all <Field-Definitons> and calls
>       createField () for each field. Taking the resulting
>       3-tuple it pushes all prologue-code to the tables prologue-, all
>       body-code to the body and all epilogue-code to the resulting
>       epilogue-block. It calls 'createPrimarykey ()', 'createIndex ()'
>       and 'createConstraint ()' in the same manner (if an apropriate
>       definition is included in the given table-definition). All these
>       functions are called with the 'codeOnly' flag set to TRUE, so code
>       could be collected. Depending on the 'codeOnly'-parameter the code
>       would be executed (using the connections.sql () method)
>       automatically. In any cases the 3-tuple of (prologue, body,
>       epilouge) would be returned.
>
>     createPrimarykey ()
>     createIndex ()
>     createConstraint ()
>     createField ()
>     alterTable ()
>       All these functions would be implemented to build up SQL-92
>       conforming code which would be executed directly according to
>       their codeOnly flag.
>
>
> class
> gnue-common.datasources.drivers.<vendors>.Schema.Creation.Creation:
>
>   These 'per-driver' implementations would derive from
>   DBSIG2.Schema.Creation and they only have to replace what's diffrent.
>   Usually this would be datatype-transformation.
>
>
>
> Passing Datadefinitions to the schema creation classes:
> =======================================================
>
> One of the key's to have a simple and usefull Schema.Creation support is
> to use an easy way of defining and passing data definition structures to
> the classes. I've thought of a dictionary-structure with a few
> 'well-known' keys.
>
>
> Table-Definition:
> -----------------
>
> {
>  'name'       : 'tablename',
>  'fields'     : [<Field-Definition>, <Field-Definition>, ...]
>  'primarykey' : <Primarykey-Definition>,
>  'indices'    : [ <Index-Definition>, <IndexDefinition>, ...]
>  'constraints': [ <Constraint-Definition>, <Constraint-Definition>, ...]
> }
>
>
> Field-Definition:
> -----------------
>
> {
>  'name'       : 'Fieldname',
>  'type'       : 'Datatype of the field',
>  'default'    : 'Default value of the field',
>  'defaultwith': 'Type of default',
>  'length'     : 'Length of the field',
>  'precision'  : 'Precision of numeric fields',
>  'nullable'   : 'Field can contain NULLs',
> },
>
> Primarykey-Definition:
> ----------------------
>
> {
>  'name'  : 'Name of the primary key',
>  'fields': ['pkfield', 'pkfield', ...]
> }
>
>
> Index-Definition:
> -----------------
>
> {
>  'name'  : 'Name of the index',
>  'unique': True/False,
>  'fields': ['indexfield', 'indexfield', ...]
> }
>
>
> Constraint-Definition:
> ----------------------
>
> {
>  'name'     : 'Name of the constraint',
>  'type'     : 'Type of the constraint (ie. foreignkey)',
>  'fields'   : ['fieldname', 'fieldname', ...],
>  'reftable' : 'Name of the referenced table',
>  'reffields': ['reffieldname', 'reffieldname', ...]
> }
>
>
> gnue-schema:
> ============
>
> The basic concept of gnue-schema could be changed to use
> *.Schema.Creation instead of using it's own code-creation-facility from
> gnue-common.schema.scripter.processors.*
>
> Passing a filehandle to the Schema.Creation instance it could recieve
> the same goal of creating SQL-files. On the other hand gnue-schema is
> able to change or update a database without the use of SQL-files.
>
>
>
> What needs more consideration:
> ==============================
>
> Some drivers handle specific types using so called DOMAINS. These
> domains are defined once per database. So if such a type is included in
> a table-definition a driver could request to create such a domain (using
> the prolog-part) before the table itself. But if the same type is
> included in the next table-definition this would lead to a duplicate
> definition problem.
>
> Using the 'ifExist ()' method a driver could solve this problem for all
> direct (online) modifications of a database. The problem is still there
> if one would like to create SQL-files for distribution. (The question
> would be why not distribute the gsd-file or something comparable)
>
> Would it be nice if the introspector could return some sort of code
> which contains the test needed? Like:
>
> IF EXISTS (SELECT * FROM systables_or_whatever AND name='Foo')
>   do_something
>
>
>
> Best regards,
> Johannes