1. table¶

Last page update: November 23, 2011

TODO rewrite the following index!!!

introduction

definition

creation of a table

config_db

columns, validations in a column

examples

1.1. introduction¶

A table is one of the most important part of your project. In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. Each row is identified by the table’s primary key (pkey).

1.2. definition¶

DbModelSrc.table(name, pkey=None, lastTS=None, rowcaption=None, sqlname=None, sqlschema=None, comment=None, name_short=None, name_long=None, name_full=None, **kwargs)¶

Add a database table to the structure and returns it

Parameters:

name – the table name

pkey – the record primary key

lastTS – the date of the last modification (TS = timestamp)

rowcaption – TODO

sqlname – TODO

sqlschema – actual sql name of the schema. For more information check the about a schema documentation section

comment – the table’s comment

name_short – the name_short of the table

name_long – the name_long of the table

name_full – the name_full of the table

**kwargs –

name_plural - the name_plural of the table

1.3. creation of a table¶

First write something like the following line:
#!/usr/bin/env python
/usr/bin/env is the address of the location of python (most of the time! Put your correct python location).

Then write the following line for the utf-8 encoding:
# encoding: utf-8
Now we have to introduce the right class for a table.

There are two options, that are:

Table (basic table): a standard table

HTable: a hierarchic table

Here we describe the most common table, that is the Table class:
class Table(object):
TODO

1.4. config_db¶

To use a table you have to call the following method:

Table_counter.config_db(pkg)¶

Configure the database, creating the database table and some columns

Parameters: pkg – the package object

So, write inside your class Table(object): the following method:
def config_db(self, pkg):
Inside the config_db method you can create a table:

1.5. table method¶

introduce a table:
tbl = pkg.table('company', pkey='id', rowcaption='@registry_id.name',
                 name_long='Company', name_plural='Companies')
TODO automethod of table method!

Here we list the table methods attributes:

audit

group

format

indexed

names: name_full, name_long, name_plural name_short

pkey

rowcaption

_sendback

1.5.1. audit¶

TODO

consente di visualizzare (DOVE??? Mi sembra una cosa di adm) le modifiche
ad un record. Non fa niente quando si crea un nuovo record.

1.5.2. group¶

TODO

1.5.3. format¶

Specify the punctuation of a numerical column. For example you can specify the character that specifies the separation between integers and the decimals.

Syntax:
format='#.###,00'
you have to use # for the integers and 0 for the decimals.
Example:
format='#.###,00'
TODO I’m not sure of the meaning of # and 0...

1.5.4. indexed¶

boolan. If True, create an SQL index of the relative column

1.5.5. name_full¶

The full name of the column. More information here

1.5.6. name_long¶

The long name of the column. More information here

1.5.7. name_plural¶

The name plural of the column. More information here

1.5.8. name_short¶

The short name of the column. More information here

1.5.9. pkey¶

TODO

1.5.10. rowcaption¶

TODO

1.5.11. _sendback¶

boolean. If True, the value of the column is passed during the form save, even if it is not change.

It is useful when you have to check a column value even if it doesn’t change (using for example the onLoading or the onSaving method).

1.5.12. sysFields¶

TableBase.sysFields(tbl, id=True, ins=True, upd=True, ldel=True, draftField=False, md5=False, group='zzz', group_name='!!System')¶

Add some useful columns for tables management (first of all, the id column)

Parameters:

tbl – the table object

id – boolean. If True, create automatically an id column. The id column is normally used as the primary key (pkey) of a table

ins – boolean. If True, create the __ins_ts column. Allow to know the time (date and hour) of a record entry

upd – boolean. If True, create the __mod_ts column. Allow to know the time (date and hour) of a record modify

ldel – boolean. If True, create the __del_ts column. Allow to know the time (date and hour) of a record delete

draftField – TODO

md5 – boolean. TODO

group – a hierarchical path of logical categories and subacategories the columns belong to. For more information, check the group section

group_name – TODO

To call it in the table page, type:
self.sysFields(tbl)

1.5.13. htableFields¶

GnrHTable.htableFields(tbl)¶
Parameters: tbl – the table object

Create the necessary columns in order to use the HTableHandler component.

In particular it adds:

the “code” column: TODO

the “description” column: TODO

the “child_code” column: TODO

the “parent_code” column: TODO

the “level” column: TODO

You can redefine the first three columns in your table; if you don’t redefine them, they are created with the following features:
tbl.column('code', name_long='!!Code', base_view=True)
tbl.column('description', name_long='!!Description', base_view=True)
tbl.column('child_code', name_long='!!Child code', validate_notnull=True,
            validate_notnull_error='!!Required', base_view=True,
            validate_regex='!\.', validate_regex_error='!!Invalid code: "." char is not allowed')
To call it in the table page, type:
self.htableFields(tbl)

1.6. columns¶

There are a lot of columns type you can use:

the simple column

the relation column (and the relation method)

the aliasColumn

the formulaColumn

the virtualColumn

1.6.1. column¶

DbModelSrc.column(name, dtype=None, size=None, default=None, notnull=None, unique=None, indexed=None, sqlname=None, comment=None, name_short=None, name_long=None, name_full=None, group=None, onInserting=None, onUpdating=None, onDeleting=None, **kwargs)¶

Insert a column into a table

Parameters:

name – the column name. You can specify both the name and the dtype using the following syntax: 'name::datatype'

dtype – the dtype

size – string. 'min:max' or fixed lenght 'len'

default – TODO

notnull – TODO

unique – boolean. Same of the sql UNIQUE

indexed – boolean. If True, allow to create an index for the column data (speed up the queries on the indexed column)

sqlname – TODO

comment – the column’s comment

name_short – the name_short of the column

name_long – the name_long of the column

name_full – the name_full of the column

group – a hierarchical path of logical categories and subacategories the columns belongs to. For more information, check the group section

onInserting – TODO

onUpdating – TODO

onDeleting – TODO

Example:
tbl.column('my_column',size=':15',name_long='!!My column')
where tbl is the table object.

1.6.2. relation column¶

The relation column is a column that allows to build relations between tables.

To create a relation column, you have to attach the relation method to a column:
tbl.column('my_column',size=':15',name_long='!!My column').relation(...)
where tbl is the table object. In the next section we talk about the relation method.

1.6.3. relation method¶

DbModelSrc.relation(related_column, mode='relation', one_name=None, many_name=None, eager_one=None, eager_many=None, one_one=None, child=None, one_group=None, many_group=None, onUpdate=None, onUpdate_sql=None, onDelete=None, onDelete_sql=None, deferred=None, relation_name=None, **kwargs)¶

Add a relation between two tables. This relation can be traveled in the direct direction (check the relation section) or in the inverse direction (check the inverse relation section)

Parameters:

related_column –
string. The path of the related column. Syntax: packageName.tableName.pkeyColumnName, where:

packageName is the name of the package folder (you can omit it if the tables to link live in the same package folder)

tableName is the name of the table to be related

pkeyColumnName is the name of the pkey of the table to be related

mode –
string. The method’s mode. You can choose between:

‘relation’: default mode. It defines a purely logical and case-sensitive relation: there is no referential integrity check

‘foreignkey’: the relation becomes a SQL relation

‘insensitive’: same features of the mode='relation' but the relation is case-insensitive

one_name – the one_to_many relation’s name. e.g: ‘movies’

many_name – the many_to_one relation’s name. e.g: ‘director’

eager_one – boolean. If True the one_to_many relation is eager

eager_many – boolean. If True the many_to_one relation is eager

one_one – TODO

child – TODO

one_group – TODO

many_group – TODO

onUpdate – TODO

onUpdate_sql – TODO

onDelete – ‘C:cascade’ | ‘I:ignore’ | ‘R:raise’

onDelete_sql – TODO

deferred – the same of the sql “DEFERRED”. For more information, check the deferred section

relation_name – string. An attribute of the relation method. It allows to estabilish an alternative string for the inverse relation. For more information, check the relation_name section

For a full explanation of the relation method attributes, please check the relation method - attributes explanation page.

Example:
tbl.column('registry_id',size=':22',name_long='!!Registry id').relation('sw_base.registry.id',mode='foreignkey')

TODO example explanation!

1.6.4. aliasColumn¶

The aliasColumn is a column through which you can give a different name to some columns of a related table.

Example:

TODO

1.6.5. formulaColumn¶

TODO

#THIS: you can use #THIS (only in a formulaColumn) to refer to the table itself.

Example: if you some fields called change_date, vat_rate and vat_rate_new, and you are in the same table in which they are defined, you can make a formulaColumn:

tbl.formulaColumn('current_vat_rate', """CASE WHEN
                                         #THIS.change_date IS NULL
                                         OR
                                         #THIS.vat_rate_new IS NULL
                                         OR
                                         #THIS.change_date <:env_workdate
                                         THEN
                                         #THIS.vat_rate
                                         ELSE #THIS.vat_rate_new
                                         END""")

Note

if you need to refer to another table, use the following syntax:

tableName.tableName_columnName.tableField

1.6.6. virtualColumn¶

TODO

1.6.7. validations in a column¶

TODO –> link to validations...

1.7. section to revise¶

1.7.1. setTagColumn¶

TODO

TableBase.setTagColumn(tbl, name_long=None, group=None)¶

TODO

Parameters:

tbl – the table object

name_long – the name_long

group – a hierarchical path of logical categories and subacategories the columns belongs to. For more information, check the group section

1.8. examples¶

Let’s see a first example:

# encoding: utf-8

class Table(object):
    def config_db(self, pkg):
        tbl = pkg.table('person',pkey='id',name_long='!!people',
                         name_plural='!!People',rowcaption='$name')
        tbl.column('id',size='22',group='_',readOnly=True,name_long='Id')
        tbl.column('name', name_short='N.', name_long='Name')
        tbl.column('year', 'L', name_short='Yr', name_long='Birth Year')
        tbl.column('nationality', name_short='Ntl',name_long='Nationality')
        tbl.column('number','L',name_long='Number')

1. table¶

1.1. introduction¶

1.2. definition¶

1.3. creation of a table¶

1.4. config_db¶

1.5. table method¶

1.5.1. audit¶

1.5.2. group¶

1.5.3. format¶

1.5.4. indexed¶

1.5.5. name_full¶

1.5.6. name_long¶

1.5.7. name_plural¶

1.5.8. name_short¶

1.5.9. pkey¶

1.5.10. rowcaption¶

1.5.11. _sendback¶

1.5.12. sysFields¶

1.5.13. htableFields¶

1.6. columns¶

1.6.1. column¶

1.6.2. relation column¶

1.6.3. relation method¶

1.6.4. aliasColumn¶

1.6.5. formulaColumn¶

1.6.6. virtualColumn¶

1.6.7. validations in a column¶

1.7. section to revise¶

1.7.1. setTagColumn¶

1.8. examples¶

Table Of Contents

Previous topic

Next topic

This Page

Navigation

1. table¶

1.1. introduction¶

1.2. definition¶

1.3. creation of a table¶

1.4. config_db¶

1.5. table method¶

1.5.1. audit¶

1.5.2. group¶

1.5.3. format¶

1.5.4. indexed¶

1.5.5. name_full¶

1.5.6. name_long¶

1.5.7. name_plural¶

1.5.8. name_short¶

1.5.9. pkey¶

1.5.10. rowcaption¶

1.5.11. _sendback¶

1.5.12. sysFields¶

1.5.13. htableFields¶

1.6. columns¶

1.6.1. column¶

1.6.2. relation column¶

1.6.3. relation method¶

1.6.4. aliasColumn¶

1.6.5. formulaColumn¶

1.6.6. virtualColumn¶

1.6.7. validations in a column¶

1.7. section to revise¶

1.7.1. setTagColumn¶

1.8. examples¶

Table Of Contents

Previous topic

Next topic

This Page

Quick search

Navigation