Create the database model source

We can define database model source as a recipe of the database's structure, GnrSqlDb is handling.

There are three ways to edit model source:

add bit by bit "pieces" of db using the functional sintax of GnrStructure?. 

mydb = GnrSqlDb(ConnectionParams)
pkg = mydb.packageSrc()
mytable=pkg.table()
(...)

load the entire model from an XML representation 

mydb = GnrSqlDb(ConnectionParams)
mydb.loadModelFromXml('/sample.xml')

import it automatically from the structure of the db. 

mydb = GnrSqlDb(ConnectionParams)
mydb.loadModelFromDb()

Adding a package

The first element added to the structure is a package item. Our databases must have at least one package as root element. A Package represents a set of tables.

Python source: 

pkg = root.package('video', comment='video package',
                   name_short='video', name_long='video', 
                   name_full='video')

the package method has these params:

  • name: package name

  • comment: comment about package

  • name_short: name_short
  • name_long: name_long

  • name_full: name_full

the XML equivalent of this step 

<packages tag="package_list">
    <video comment="video package" name_short="video"
           tag="package" name_full="video"
           name_long="video">
    </video>
</packages>

Packages itesm are contained by an element called element of type package_list.

Adding tables

A table item must be added as a child of package

Python source: 

director = pkg.table('director', name_short='Dir', name_long='Director')

the table method has these params:

  • name: table name
  • comment: about this table
  • name_short: name_short
  • name_long: name_long
  • name_full: name_full
  • pkey: the name of the column which is primary key

XML source: 

<packages tag="package_list">
    <video comment="video package" name_short="video"
           tag="package" name_full="video"
           name_long="video">
        <tables tag="table_list">
            <director pkey="id" tag="table" name_short="Dir" name_long="Director">
            </director>
        </tables>
    </video>
</packages>

In the same way we can add other tables such as movie and dvd

Adding columns

Now we can add some columns as children of a single table: 

dir_id = director.column('id', dtype='L')
director.column('name', name_short='N.', name_long='Name')
director.column('birthday',name_short='Bd', name_long='Birthday', dtype='D')
director.column('nationality', name_short='Ntl',name_long='Nationality')

column method has these params:

  • name: table name
  • comment: about this column
  • name_short: name_short
  • name_long: name_long
  • name_full: name_full
  • name_full: name_full
  • dtype: the type of data you can insert in. You can set as 'D','L','R','serial','B','A'
  • default: set a default value
  • notnull
  • len_max: it sets the maximum len of the string you insert (dtype='A')
  • len_min: it sets the maximum len of the string you insert (dtype='A')

  • len_show:

In stand of using dtype param in column() you can use one of these methods with the same parameters of column:

  • dataColumn() = column(dtype='D') DATE
  • stringColumn() = column(dtype='A') VARCHAR
  • intColumn() = column(dtype='L') LONGINTEGER
  • realColumn() = column(dtype='R') REAL
  • stringColumn() = column(dtype='A') VARCHAR
  • serialColumn() = column(dtype='serial') SERIAL
  • boolColumn() = column(dtype='B') BOOLEAN

If you use column() without dtype param the type of column is text.

So the same model can be written in this way 

dir_id = director.intColumn('id')
director.column('name', name_short='N.', name_long='Name')
director.dateColumn('birthday',name_short='Bd', name_long='Birthday')
director.column('nationality', name_short='Ntl',name_long='Nationality')

The XML source is the same: 

<packages tag="package_list">
    <video comment="video package" name_short="video"
           tag="package" name_full="video"
           name_long="video">
        <tables tag="table_list">
            <director pkey="id" tag="table" name_short="Dir" name_long="Director">
            <columns tag="columns_list">
                        <id dtype="L" tag="column"/>
                        <name name_short="N." tag="column" name_long="Name"/>
                        <birthday dtype="D" name_short="Bd" tag="column" name_long="Birthday"/>
                        <nationality name_short="Ntl" tag="column" name_long="Nationality"/>
            </columns>
            </director>
        </tables>
    </video>
</packages>

Primary key

In the previous paragraph we omitted the setting of a table's primary key. You can set it in these ways:

  • as table parameter(python)/attribute(xml)
  • using the table's method pkey()
  • using the column's method isPkey()

When you define a table you can set pkey as param 

director = pkg.table('director', name_short='Dir', name_long='Director', pkey='id')

The pkey() method

You can also specify primary key with the method pkey.

  • param column: the name of the column (Multi columns pkey is to be implemented) 
    director = pkg.table('director', name_short='Dir', name_long='Director')
dir_id = director.intColumn('id')
director.pkey('id')

The isPkey() method

You can set your primary key directly on the column with no params 

director = pkg.table('director', name_short='Dir', name_long='Director')
dir_id = director.intColumn('id').isPkey()

Indexes

Similar to primary key you can set some table indexes in these ways:

  • index_() focusing on the table with these params
    • columns: list, or tuple, or string separated by commas
    • name: index name
    • unique param: unicity bound. If unique set 'y'
  • isIndexed() focusing the column with these param
    • name: index name
    • unique param: unicity bound. If unique set 'y' 
#a index_
movie.index_('title', name='i_title')

#b isIndexed
movie.intColumn('year', name_short='Yr',name_long='Year').isIndexed()

#b isIndexed movie.intColumn('year', name_short='Yr',name_long='Year').isIndexed() }}}

You can set unique constrants of an index using these methods:

  • unique() from the table
    • columns: list, or tuple, or string separated by commas
    • name: index name
  • isUnique() from the index with no params 
movie.unique('director')

movie.column('genre', name_short='Gnr',name_long='Genre',
              validate_case='upper', validate_len='3,10').isUnique()

Adding relations

Let's see how to add relations between tables. We want to link the column director of the table movie with the director table using the foreign key dir_id. In order to do this we use the method relate that can takes as parameter the related column variable or its name. 

#1) column object
director_col.relate(dir_id)

#2) string composed by "table_name.column.name"
director_col.relate('director.id')


#3) string composed by "package_name.table_name.column.name"
director_col.relate('video.director.id')

relate() params are:

  • target: the column this column is related to
  • o_name_short=None,o_name=None, o_name_full=None. Name of the relation
  • m_name_short=None,m_name=None, m_name_full=None. Name of the relation
  • case_insensitive

Notes

You can set valdator's bag into your structure

the attribute/param pcol...

Anything else?