### Navigation

- index
- modules |
- next |
- previous |
- Camelot v0.1 documentation »
- Camelot Documentation contents »
- Camelot Documentation »

# Creating models with Elixir/SQLAlchemy/Camelot

Release: | 0.1
Date: | June 12, 2010

The Elixir website provides a complete overview of the creation of models that should be read before reading this section.

This section describes the various field types that can be used to construct models. Fields from SQLAlchemy and Camelot are described.

Field types handled through introspection :

Field type | Default delegate | Default editor
BOOLEAN | BoolDelegate |
Boolean | BoolDelegate |
Code | CodeDelegate |
Color | ColorDelegate |
Date | DateDelegate |
DateTime | DateTimeDelegate |
Enumeration | EnumerationDelegate |
File | FileDelegate |
Float | FloatDelegate |
INT | IntegerDelegate |
IPAddress | CodeDelegate |
Image | ImageDelegate |
Integer | IntegerDelegate |
Language | EnumerationDelegate |
Numeric | FloatDelegate |
Rating | StarDelegate |
RichText | RichTextDelegate |
String | PlainTextDelegate |
Text | PlainTextDelegate |
Time | TimeDelegate |
Unicode | PlainTextDelegate |
VirtualAddress | VirtualAddressDelegate |

## SQLAlchemy field types

SQLAlchemy provides a number of field types that map to available data types in SQL, more information on those can be found on the SQLAlchemy website .

## Camelot field types

Camelot extends the SQLAlchemy field types with a number of its own field types. Those field types are automatically mapped to a specific delegate taking care of the visualisation.

Those fields are stored in the `camelot.types` module.

class `camelot.types.``Code`(parts, separator=u'.', **kwargs)

  SQLAlchemy column type to store codes. Where a code is a list of strings on which a regular expression can be enforced.

  This column type accepts and returns a list of strings and stores them as a string joined with points.

  eg: `['08', 'AB']` is stored as `08.AB`

  `impl`
    alias of `Unicode`

class `camelot.types.``Color`

  The Color field returns and accepts tuples of the form (r,g,b,a) where r,g,b,a are integers between 0 and 255. The color is stored as an hexadecimal string of the form AARRGGBB into the database, where AA is the transparency, RR is red, GG is green BB is blue:

      class MovieType(Entity):
        color = Field(camelot.types.Color())

  The colors are stored in the database as strings

  `impl`
    alias of `Unicode`

class `camelot.types.``Enumeration`(choices=[], **kwargs)

  The enumeration field stores integers in the database, but represents them as strings. This allows efficient storage and querying while preserving readable code.

  Typical use of this field would be a status field.

  Enumeration fields are visualized as a combo box, where the labels in the combo box are the capitalized strings:

      class Movie(Entity):
        title = Field(Unicode(60), required=True)
        state = Field(camelot.types.Enumeration([(1,'planned'), (2,'recording'), (3,'finished'), (4,'canceled')]),
                                                index=True, required=True, default='planning')

  `impl`
    alias of `Integer`

class `camelot.types.``File`(max_length=100, upload_to='', storage=<class 'camelot.core.files.storage.Storage'>, **kwargs)

  Sqlalchemy column type to store files. Only the location of the file is stored

  This column type accepts and returns a StoredFile, and stores them in the directory specified by settings.MEDIA_ROOT. The name of the file is stored as a string in the database. A subdirectory upload_to can be specified:

      class Movie(Entity):
        script = Field(camelot.types.File(upload_to='script'))

  `impl`
    alias of `Unicode`
  `stored_file_implementation`
    alias of `StoredFile`

class `camelot.types.``Image`(max_length=100, upload_to='', storage=<class 'camelot.core.files.storage.Storage'>, **kwargs)

  Sqlalchemy column type to store images

  This column type accepts and returns a StoredImage, and stores them in the directory specified by settings.MEDIA_ROOT. The name of the file is stored as a string in the database.

  The Image field type provides the same functionallity as the File field type, but the files stored should be images.

  `stored_file_implementation`
    alias of `StoredImage`

class `camelot.types.``Language`

  The languages are stored as ISO codes in the database

  `impl`
    alias of `Unicode`

class `camelot.types.``Rating`(*args, **kwargs)

  The rating field is an integer field that is visualized as a number of stars that can be selected:

      class Movie(Entity):
        title = Field(Unicode(60), required=True)
        rating = Field(camelot.types.Rating())

  `impl`
    alias of `Integer`

class `camelot.types.``RichText`(*args, **kwargs)

  RichText fields are unlimited text fields which contain html. The html will be rendered in a rich text editor.

  `impl`
    alias of `UnicodeText`

class `camelot.types.``VirtualAddress`(*args, **kwargs)

  A single field that can be used to enter phone numbers, fax numbers, email addresses, im addresses. The editor provides soft validation of the data entered. The address or number is stored as a string in the database.

  This column type accepts and returns tuples of strings, the first string is the `virtual_address_type`, and the second the address itself.

  eg: `('email','project-camelot@conceptive.be')` is stored as `mail://project-camelot@conceptive.be`

  `impl`
    alias of `Unicode`

## Python properties as fields

Normal python properties can be used as fields on forms as well. In that case, there will be no introspection to find out how to display the property. Therefore the delegate attribute should be specified explicitely.

>     from camelot.admin.object_admin import ObjectAdmin
>     from camelot.view.controls import delegates

>     class Coordinate(object):

>       def __init__(self):
>         self.id = 1
>         self.x = 0
>         self.y = 0

>       class Admin(ObjectAdmin):
>         form_display = ['x', 'y']
>         field_attributes = dict(x=dict(delegate=delegates.FloatDelegate),
>                                 y=dict(delegate=delegates.FloatDelegate),)

## Attach actions to field changes

Whenever the value of a field is changed, an action on the model can be triggered by using properties to manipulate the field instead of manipulating it directly. The example below demonstrates how the value of y should be chopped when x is changed.

>     from camelot.admin.object_admin import ObjectAdmin
>     from camelot.view.controls import delegates

>     class Coordinate(object):

>       def __init__(self):
>         self.id = 1
>         self.x = 0.0
>         self.y = 0.0

>       def _get_x(self):
>         return self.x

>       def _set_x(self, x):
>         self.x = x
>         self.y = max(self.y,x)

>       _x = property(_get_x, _set_x)

>       class Admin(ObjectAdmin):
>         form_display = ['_x', 'y',]
>         field_attributes = dict(_x=dict(delegate=delegates.FloatDelegate, name='x'),
>                                 y=dict(delegate=delegates.FloatDelegate),)
>         form_size = (100,100)

## Fields calculated by the database

Having certain summary fields of your models filled by the database has the advantage that the heavy processing is moved from the client to the server. Moreover if the summary builds on information in related records, having the database build the summary reduces the need to transfer additional data from the database to the server.

To display fields in the table and the form view that are the result of a calculation done by the database, a ColumnProperty needs to be defined in the Elixir model. In this ColumnProperty, the sql query can be defined using sqlalchemy statements. Then use the field attributes mechanism to specify which delegate needs to be used to render the field.

As an example we will create a budget with multiple budget lines, where the total budget is calculated by the database

    from elixir.properties import ColumnProperty
    from camelot.view.controls import delegates
    from sqlalchemy import sql, and_

    class Budget(Entity):
      lines = OneToMany('BudgetLine')
      total = ColumnProperty(lambda c:sql.select([sql.func.sum(BudgetLine.amount)], and_(BudgetLine.budget_id==Budget.id)))

       class Admin(EntityAdmin):
        name = 'Budgets'
        list_display = [ 'total', 'lines']
        field_attributes = {'total':{'delegate':delegates.FloatDelegate}}

    class BudgetLine(Entity):
      budget = ManyToOne('Budget', required=True, ondelete='cascade', onupdate='cascade')
      amount = Field(Float(precision=2), default=0)

      class Admin(EntityAdmin):
        name = 'Budget lines'
        list_display = ['amount',]

When the user presses F9, all data in the application is refreshed from the database, and thus all fields are recalculated.

An explanation of the lambda function inside the ColumnProperty can be found in the ElixirColumnProperty and the SqlalchemyMappers documentation.

### Navigation

- index
- modules |
- next |
- previous |
- Camelot v0.1 documentation »
- Camelot Documentation contents »
- Camelot Documentation »