Database model

GnrSqlDb needs to know a full set of informations about the database:

• its structure: packages, tables and columns etc.
• relations

We call this set of informations the database model. There are two sides of the model: one is a Bag representation called model.src, it can be loaded or saved as XML file. The second is model.obj which is a hierarchical structure of objects such as tables, columns, records, built using the model.src as a recipe

Model Source

There are three ways to provide model source:

• adding "pieces" of db using the functional sintax of GnrStructures.

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

• loading the entire model from an XML representation

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

• importing it automatically from the structure of the db.

  mydb = GnrSqlDb(ConnectionParams)
  mydb.importModelFromDb()
  

Writing a DbModelSrc in Python

Since we think that writing XML is very boring, the most commonly used way to create a database model is using GnrStructures. In the next example we will create a simple database containing movies, director and dvd. In order to do this we have to write a mixin class and define just one single method called configure. In the configure method we describe the database model.

Adding 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. The method package adds a Package to the model, it takes as parameters metadata such as comment, name_short, name_long, etc.

pkg = mydb.packageSrc('video',comment='My movie catalog',
                    name_short='Video',
                    name_long='Video Catalog',
                    name_full='John's video catalog')

Adding tables

Then we start adding tables to the package: people, cast, movie, dvd.

table paramethers are:

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

from config_db method we can add:

#dvd table:
dvd = pkg.table('dvd', name_short='Dvd',name_long='Dvd', name_full='Dvd', pkey='code')

Adding columns

Then we add to each table its columns. (in the example we choose dvd)

column method has these params:

• param name: column name
• param dtype: it can be omitted for text and varchar columns
• param size: string, 'min:max' or fixed lenght 'len'. (If there isn't dtype and size has not ':' eg '30' and dtype is char, if size is like ':30' or '2:30' dtype is varchar)
• param comment: comment about column
• param sqlname
• param name_short: name_short
• param name_long: name_long
• param name_full: name_full
• param default
• param notnull
• param unique
• param indexed

dvd = pkg.table('dvd', name_short='Dvd', name_long='Dvd', pkey='code')
dvd_id = dvd.column('code', 'L')
dvd.column('movie_id', name_short='Mid', name_long='Movie id')
dvd.column('purchasedate', 'D', name_short='Pdt', name_long='Purchase date')
dvd.column('available', name_short='Avl', name_long='Available')

Adding relations

Let's see how to add relations between tables. We want to link the column movie_id of the table dvd with the id of the table movie using movie_id as foreign key.

dvd.column('movie_id').relation('movie.id')
# Note: we can use column method as getter

Adding indexes

To add index to a table.

Params:

• param columns: list, or tuple, or string separated by commas
• param name: index name
• param unique: unicity bound

pkg.table('movie').index('title', 'i_title',  unique='y')
# Note: table is used as getter

Python vs. XML

Here is a comparison of python complete source and its XML equivalent

• Python

      pkg = mydb.packageSrc('video')
      pkg.attributes.update(comment='video package',name_short='video', name_long='video', name_full='video')
  
      people = pkg.table('people', name_short='people', name_long='People', 
                           rowcaption='name,year:%s (%s)', pkey='id')
      people.column('id', 'L')
      people.column('name', name_short='N.', name_long='Name')
      people.column('year', 'L', name_short='Yr', name_long='Birth Year')
      people.column('nationality', name_short='Ntl',name_long='Nationality')
      
      
      cast = pkg.table('cast', name_short='cast', name_long='Cast', 
                           rowcaption='', pkey='id')
      cast.column('id', 'L')
      cast.column('movie_id','L', name_short='Mid', 
                             name_long='Movie id').relation('movie.id')
      cast.column('person_id','L',name_short='Prs', 
                             name_long='Person id').relation('people.id')
      cast.column('role', name_short='Rl.',name_long='Role')
      cast.column('prizes', name_short='Priz.',name_long='Prizes', size='40')
      
      
      movie = pkg.table('movie', name_short='Mv',name_long='Movie',
                         rowcaption='title', pkey='id')
      movie.column('id', 'L')
      movie.column('title', name_short='Ttl.',name_long='Title',
                              validate_case='capitalize', validate_len='3,40')
      movie.index('title', 'i_title',  unique='y')
      movie.column('genre', name_short='Gnr',name_long='Genre',
                              validate_case='upper', validate_len='3,10',indexed='y')
      movie.column('year', 'L', name_short='Yr',name_long='Year',indexed='y')
      movie.column('nationality', name_short='Ntl', name_long='Nationality')
      movie.column('description', name_short='Dsc', name_long='Movie description')
      
      
      dvd = pkg.table('dvd', name_short='Dvd', name_long='Dvd', pkey='code')
      dvd_id = dvd.column('code', 'L')
      dvd.column('movie_id', name_short='Mid', name_long='Movie id').relation('movie.id')
      dvd.column('purchasedate', 'D', name_short='Pdt', name_long='Purchase date')
      dvd.column('available', name_short='Avl', name_long='Available')
  
  

• XML:

  <?xml version="1.0" encoding="UTF-8"?>
  <GenRoBag>
      <packages tag="package_list">
          <video name_short="video" comment="video package" tag="package" name_full="video" name_long="video">
              <tables tag="table_list">
                  <people pkey="id" tag="table" rowcaption="name,year:%s (%s)" name_short="people" name_long="People">
                      <columns tag="column_list">
                          <id dtype="L" tag="column"/>
                          <name name_long="Name" name_short="N." tag="column"/>
                          <year name_long="Birth Year" dtype="L" name_short="Yr" tag="column"/>
                          <nationality name_long="Nationality" name_short="Ntl" tag="column"/>
                      </columns>
                  </people>
                  <cast pkey="id" tag="table" rowcaption="" name_short="cast" name_long="Cast">
                      <columns tag="column_list">
                          <id dtype="L" tag="column"/>
                          <movie_id name_long="Movie id" dtype="L" name_short="Mid" tag="column">
                              <relation related_column="movie.id" mode="relation"/>
                          </movie_id>
                          <person_id name_long="Person id" dtype="L" name_short="Prs" tag="column">
                              <relation related_column="people.id" mode="relation"/>
                          </person_id>
                          <role name_long="Role" name_short="Rl." tag="column"/>
                          <prizes name_long="Prizes" name_short="Priz." tag="column" size="40"/>
                      </columns>
                  </cast>
                  <movie pkey="id" tag="table" rowcaption="title" name_short="Mv" name_long="Movie">
                      <columns tag="column_list">
                          <id dtype="L" tag="column"/>
                          <title validate_case="capitalize" validate_len="3,40" name_long="Title" name_short="Ttl." tag="column"/>
                          <genre validate_case="upper" validate_len="3,10" name_long="Genre" name_short="Gnr" tag="column" indexed="y"/>
                          <year name_long="Year" dtype="L" name_short="Yr" tag="column" indexed="y"/>
                          <nationality name_long="Nationality" name_short="Ntl" tag="column"/>
                          <description name_long="Movie description" name_short="Dsc" tag="column"/>
                      </columns>
                      <indexes tag="index_list">
                          <i_title unique="y" tag="index" columns="title"/>
                      </indexes>
                  </movie>
                  <dvd pkey="code" tag="table" name_short="Dvd" name_long="Dvd">
                      <columns tag="column_list">
                          <code dtype="L" tag="column"/>
                          <movie_id name_long="Movie id" name_short="Mid" tag="column">
                              <relation related_column="movie.id" mode="relation"/>
                          </movie_id>
                          <purchasedate name_long="Purchase date" dtype="D" name_short="Pdt" tag="column"/>
                          <available name_long="Available" name_short="Avl" tag="column"/>
                      </columns>
                  </dvd>
              </tables>
          </video>
      </packages>
  </GenRoBag>
  

Model Object

When the model.src is defined we can buld the model.obj hierarchy of Sql Objects calling db method startup

mydb.startup()