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

[Johannes Vetter]

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

signature.asc
Description: Dies ist ein digital signierter Nachrichtenteil