Acces to db made easy

- Prev: Widgets
- Next: SqlMask

# Sqlwidget

This is the base class for SqlMask and SqlTable that implements the common interface.

## mandatory argument

The first and mandatory argument is the `mapper` of the object that will be retrieved and displayed. Alternatively any other objects from which the sqlwidget infers them (e.g.: the tablename and the metadata where the table can be autoloaded):

- a mapper
- a mapped class
- table name (requires you also pass metadata)

In older releases different options: `class_`, `table`, or `mapper` where used. Now a DeprecationWarning is raised if you use those opts.

## main options

Any sqlwidget needs a `session` as well.

Metadata is also used when auto-loading tables referenced by foreign keys to display a better representation of the referenced record.

### dbproxy

Since a typical scenario is to have to provide a session different in each SqlWidget and a metadata, an object is provided -`dbproxy`- that can be initialized from the engine specification:

    from sqlkit.widgets import SqlMask, SqlTable
    from sqlkit import dbproxy
    db = proxy.DbProxy(engine="sqlite:///model/movies.sqlite")
    SqlTable("movies", dbproxy=db)

below you can see some alternatives that would work as well:

    Session = sessionmaker(bind=self.metadata.bind, autocommit=False)
    sess = Session()
    meta = MetaData()
    meta.bind = "sqlite:///model/movies.sqlite"
    #
    SqlTable("movies", session=sess, metadata=meta)

    # passing a mapped class (Movie here is build with declarative layer):
    # the metadata is found from the mapper.local_table.metadata

    SqlTable(Movies, session=sess)

Session are created with `autoflush=False`, `expire_on_commit=False` but can be changed when building `DbProxy`.

Since version 0.8.6 default value for autocommit has been turned to True to prevent idle in transaction in postgresql.

## session & expire_on_commit

The reason to have `expire_on_commit=False` is that if you don’t set it, after every commit, you have to reload all objects and the interface turns very slow, especially when working with a remote database.

`expire_on_commit` is a recent addition to sqlalchemy session (around sa rel. 5.03rc) so I try it and fallback to default that would turn to be slower. Previous 0.5 rel called it `autoflush`

## Attributes

`filter_panel`

  The filter_panel widget

`related`

  a container for all related sqlwidgets (i.e.: SqlWidgets that have a relation with this sqlwidget defined by sqlalchemy and that are displayed in the widget).

  This is used in all situation in which you need to fine-tune the configuration of a related table (completion, layout, ...)

`completions`

  a container of all completion objects. Needed to change the behavior of some completion (see Completion)

`gui_fields`

  a container for all validation fields. A validation fields is an object that lives in `sqlkit.fields` and that knows how to represent a field and how to validate it.

`gui_field_mapping`

  A dict used to force a map between a `sqlkit.Field` and a gui field. Read more in Fields.

`query`

  the sqlalchemy query object (`session.query(mapper)`) with all constraints applied. Can be manipulated as necessary as long as it stay a query object.

`layout`

  the layout definition for this SqlWidget. This is the used definition for SqlMask while for SqlTable is only used if the record is opened in SqlMask (right click on the record in SqlTable).

  You can also set it on a related table:

      GUI="director @ m2m=movies"
      t = SqlMask(Director, layout=GUI, ...)
      t.related.movies.layout = "title @ m2m=actors"

`lay_obj`

  the sqlkit.layout.Layout object used to create the layout

`current`

  the object represented by a mask or a possible selected record in a table or None. In `SqlTable` in the transient in which you are saving a record in a table (when selection changes) `self.current` will point to the obj that is to be saved (while the selected object may already be another one).

`field_list`

  a list of the field names the GUI is handling. It comprises PropertyLoaders (i.e. properties of the class that act as a loader of other info - all relation are seen as PropertLoaders w/o column)

`order_by`

  an order_by string or clause element. Same as parameter passed to the class. It’s a property, can be set in any moment.

`session`

  the sqlalchemy session used for querying

`defaults`

  an instance of `sqlkit.db.defaults.Defauls` instantiated with local=True as explained in Defaults local to the application. Any default set with this instance will only be visible in this sqlwidget.

`title`

  the title of the Window.

`mode`

  the mode describing permissions of the widget. See set_mode

`noup`

  noup can be a set of field_name or a comma separated string with possible +- sign to add/remove field_names to the set of field_names that will not be possible to update

  Note that to add a non editable field_name you must used ‘+field_name’. Using simply ‘field_name’ will reset the list to only that field_name

`actiongroup_*`

  see UiManager: menu and actions

`ui_manager`

  see UiManager: menu and actions

`relationship_leader`

  Th case this sqlwidget is representing a relation of a SqlMask, that SqlMask is referred to as a relationship_leader.

## Signals

pre-display:

A record is about to be displayed.

`pre_display_cb(sqlwidget, obj):`

  Parameters:

  - sqlwidget – the widget that emitted the signal
  - obj – the object that is about to be displayed

record-selected:

A record has been displayed in Mask or selected in Table. The callback will just receive the widget as argument.

`records_selected_cb(sqlwidget):`

  Parameters: | sqlwidget – the widget that emitted the signal

record-saved:

A record has been saved. This signal is not emitted from within session extension. That means you are sure there will be just one signal for each button press on “save” button. This is issued from within the `SqlWidget.commit()` method independently from the fact that a real modification occurred, so you are not guaranteed any modification took place. Was originally added to implement a destroy of the widget when the save operation was performed.

The callback will just receive the widget as argument.

`record_saved_cb(sqlwidget):`

  Parameters: | sqlwidget – the widget that emitted the signal

record-new:

A new record has been added. The callback will just receive the widget as argument.

`record_new_cb(sqlwidget):`

  Parameters: | sqlwidget – the widget that emitted the signal

record-deleted:

A record has been deleted. The callback will receive the widget and the obj.

`record_new_cb(sqlwidget, deleted_obj):`

  Parameters:

  - sqlwidget – the widget that emitted the signal
  - deleted_obj – the obje that was deleted

Note that this signal is emitted only for records deleted explicitly, i.e. records that where the `current record` in a mask/table. If a record is deleted as a side effect (e.g.: becouse cascade=”delete-orphan” is set) no signal is emitted for that obj.

records-displayed:

Records have been displayed. This may be after `SqlWidget.reload()` or `SqlWidget.set_records()`. The callback will just receive the widget as argument. See also context changed to see another signal that better tracks any change

`records_displayed_cb(sqlwidget):`

  Parameters: | sqlwidget – the widget that emitted the signal

after-flush:

flush has occurred, normally commit should not add any errors. This is implemented with a SessionExtension: if you used the default session obtained via get_session() you are assured that it will be correct. If you create a session by yourself, be sure to add `sqlkit.db.proxy.SKSessionExtension` to the session extensions or you won’t have this signal. Read more detailed explanation in hook on_after_flush

`after_flush_callback`(sqlwidget, object, session)

  Parameters:

  - sqlwidget – the sqlwidget that emitted the signal
  - object – the object that was current when session was flushed. Current means that it was the main object represented. Many other widgets may be present, possibly in “dirty state”, “new” or “deleted”, but current was the one selected in a table or displayed in the main mask
  - session – the session that was flushed. The moment in which the signal is emitted you can still dig into `session.dirty`, `session.new` and `session.deleted` and find which attributes have been changed (you may want to use `get_differences()`)

after-commit: | run from within `after-commit` SessionExtension. Callback signature is identical to `after_flush_callback` above.

delete-event:

emitted when the sqlwidget is destroyed

`delete_event_callback`(sqlwidget)

  Parameters: | sqlwidget – the sqlwidget that emitted the signal

## Hooks

Beside signals there is another way to add controls: hooks. A hook is a function that will be called in particular moments only if present.

Hooks are the main way to customize the behavior of a sqlwidget. Some of the hooks (`on_validation.*`) are related to validation other are related to configuration (`on init`), others (`on_activate`) may be used to save typing.

Hooks are searched for in the methods of the instance of a class declared in the optional `hooks` argument or in the global registered hooks (see below).

Hooks can be registered using `sqlkit.db.utils.register_hook()` (and `get_hook <sqlkit.db.utils.get_hook()` from utils module) so that any sqlwidget built on that table will use those hooks (unless the table is part of a join or another selectable!!). The advantage is that browsing data (e.g. using right click on a table row) can lead to opening tables that are not configured: registering hooks is a way to enforce configuration (and possibly constraints) on any widget.

As layout hooks can be registered with a nick (default is `default`) so that you can register different hooks for different editing flavors. E.g/: you can have a table for people and you can decide to open it with customer or provider layout/hooks just registering both layout and hooks and using argument `layout_nick`.

Use it with care as it may lead in situation in which not all fields are present (due e.g. to a different layout or different field_list)

The following hooks are defined:

on_change_value__field_name:

this hook is used to trace changes in the widget. It’s mainly meant to be used interactively but it is also triggered when `set_value` is invoked with initial=False. It’s not changed if the value is set on record change.

Currently it behaves differently for Table widgets or Mask widgets.

Mask widgets invoke it each meaningful changes i.e. each char for varchar/int field, each time a value is choosen for enum/foreign key fields, when a date is selected or validated for Date widgets.

Table widgets invoke this hook only when the field is leaved or activated (Return is pressed).

Note

Implementation of this hooks is currently limited to some fields: Varchar, Int, Float, Numeric, Enum, ForeignKey, Date, DateTime, Bool. Text, Images, Time and Interval are not implemented. Example 63c is a complete example that shows it.

`on_change_value__field_name(sqlwidget, field_name, value,`
`fkvalue, field)`

  Parameters:

  - sqlwidget – the sqlwidget (SqlMask or SqlTable) that runs the hook
  - field_name – the field_name
  - value – the new value
  - fkvalue – if field is a ForeignKey or enum, the displayed value
  - field – the field

on_completion__field_name:

called when a completion is chosen

`on_completion__field_name`(sqlwidget, field_name, obj)

  Parameters:

  - sqlwidget – the sqlwidget (SqlMask or SqlTable) that runs the hook
  - field_name – the field_name
  - obj –

    the matched object in the completion. This obj has attributes for each field_name named in `attrs` attribute of the completion. You can add field_names to that attribute if you need them in this hook function:

        sqlwidget.completions.director_id.attrs += ['nation']

    allows you to add a completion hook:

        def on_completion__director_id(self, sqlwidget, field_name, obj):
            print obj.nation
            sqlwidget.set_value('nation', obj.nation)

    you can also reach field attributes as dict values: obj[‘nation’].

save_as:

New in version 0.9.3.

this hook is invoked each time a record is saved as a duplicate of another one. After the new record is filled and before it’s saved you can customize at your will. Callback function:

`on_save_as(sqlwidget, old, new):`

  Parameters:

  - sqlwidget – the mask/table that invoked the hook
  - old – the old object that was copied
  - new – the new object

When this hook is present, no warning on how the copy is handled is raised as it’s considered that the programmer has already coped with all the tricky issues.

on_validation:

called when all the values have been collected in the object, before calling validation on all fields and related widgets. That’s a good point to implement any procedure to add automatism’s. Within this hook you can propagate errors to the validation machinery in two of ways:

1. raising `sqlkit.exc.ValidationError`. Simple when just one error is found
2. filling self.validation_errors: a dict holding all errors. The key is the field_name or “record_validation”, the value a list of error messages

`on_validation`(sqlwidget)

  Parameters: | sqlwidget – the sqlwidget (SqlMask or SqlTable) that run the hook

on_field_validation__field_name:

called to validate a single field as for the previous `on_validation`. This is called from within the `field's validation method`

1. raising `sqlkit.exc.ValidationError`. Simple when just one error is found
2. filling self.validation_errors: a dict holding all errors. The key is the field_name or “record_validation”, the value a list of error messages

`on_validation`(sqlwidget, field_name, field_value, field)

on_activate__field_name:

when Return is pressed in Mask or Table. Good to complete fields via calculation on other fields (e.g.: total, vat...). The name derive from the GTK name ‘activate’ that is when you press ‘Return’ in an entry, even thought in a Table’s treeview it’s really connected to the cell’s `edited` signal (limited to Varchar and Numeric columns).

`on_activate__field_name`(sqlwidget, field_name, field)

  Parameters:

  - sqlwidget – the sqlwidget (SqlMask or SqlTable) that run the hook
  - field_name – the field_name
  - field – the `sqlkit.widgets.common.field`

on_init:

run as the last command of __init__. It’s main purpose is to allow to configure a widget in a way that will be handed over to a possible SqlMask generated right-clicking from a table row (see RecordInMask).

`on_init`(sqlwidget)

  Parameters: | sqlwidget – the sqlwidget (SqlMask or SqlTable) that run the hook

on_pre_layout:

run before the layout is setup. It’s main purpose is to allow to add fields in `gui_field_mapping` (that is only useful for SqlMask). Note that you can force a field for a table’s attribute if you want setting info’s field key pointing to that field’s class.

`on_init`(sqlwidget)

  Parameters: | sqlwidget – the sqlwidget (SqlMask or SqlTable) that run the hook

Note

Hooks invoked within the session extensions

The hooks that are called from within the session extensions, can be called several times if there are more sqlwigets sharing the same session (that happens for example each time you open a mask to edit the row of a table i.e.: RecordInMask)

on_after_flush:

run as the signal with the same name from within `after_flush` session extension method.

This hook is completely similar to `after-flush` signal, but is meant to be defined in a separate class so that it’s easier to propagate validation hooks to a spawned child (i.e.: RecordInMask, when opening a mask by right clicking on a table record). You’ll see that `on_after_flush` is called eather.

From the sqlalchemy documentation: “Note that the session’s state is still in pre-flush, i.e. new, dirty, and deleted lists still show pre-flush state as well as the history settings on instance attributes”. This is true for after_commit hook as well, it is not true for after_flush_postexec, that on the other had has already setup relation.

I end up in some circumstances to split the callback in two phases: one that detects if an action is needed from within the after_flush/after_commit phase, the second (may be a mail, or any other action) from within the hook on_record_saved, so that I can use the relations. Beware that you may have one call to `on_after_flush` and more different calls to `on_after_commit`

`on_after_flush_callback`(sqlwidget, object, session)

  Parameters:

  - sqlwidget – the sqlwidget that emitted the signal
  - object – the object that was current when session was flushed. Current means that it was the main object represented. Many other widgets may be present, possibly in “dirty state”, “new” or “deleted”, but current was the one selected in a table or displayed in the main mask
  - session – the session that was flushed. The moment in which the signal is emitted you can still dig into `session.dirty`, `session.new` and `session.deleted` and find which attributes have been changed (you may want to use get_differences), but you won’t have correctly setup relation’s object

on_after_flush_postexec:

run as the signal with the same name from within `after_flush_postexec` session extension method.

As in the precedent hook this is called exactly within the session extension method by the same name. When it’s run the session will have no longer information on session.dirty/session.new/session.delete but will have all relations set-up.

The callback has the same signature as for hook on_after_flush

on_after_commit:
| run after commit after_commit session extension method The callback has the same signature as for hook on_after_flush

on_record_saved:

run from within the commit method of the widget, that

  assures that will be issued just once. It’s just equivalent to the signal with the callback has the same signature:

`record_saved_cb(sqlwidget):`

  Parameters: | sqlwidget – the widget that emitted the signal

### Hooks for related widgets

If you need to add hooks for related sqlwidgets (those that display related records as explained in Relationships) you need to add the relationship_path in the method name, i.e. the value you have used in the table definition for the relation (and in the layout as in m2m=actors)

#### example

This example shows how to use hooks to play a sound on completion:

    class Hook(obj):
        def on_completion__genre(self, field_name, obj):
             if obj.genre == 'horror':
                  play('horror.wav')

    MovieMask(class=models.Movie, hooks=Hook(), ...)

### Registered hooks and SessionEtensions

When hooks are registered their customizations are enforced each time the model for which they’re registered is called. That adds some complications you should be sure to understand if using one of these hooks:

> - on_after_flush
> - on_after_commit
> - on_after_flush_postexec

These hooks are called within a SessionExtension that calls hooks on any sqlwidget that may be using the same session. An m2m, m2o relation table share the same session as the Mask that holds them so that it’s pretty normal to have several different tables within the same session.

From the SessionExtension hooks are searched for in any of these so that you should write the hooks keeping in mind it can be called from another sqlwiget’s commit.

As an example, suppose you have a Mask with the following layout:

    first_name
    last_name
    m2m=genres   m2m=actors

suppose movies, genres and actors have registered `on_after_commit` hooks. They will all be called on any commit. Adding a genre object will trigger on_after_commit on the Actor’s table and vice verse.

### get_differences

Module sqlkit.db.utils provides a simple function that yields all modified attributes of an object, along with their old and new values

`get_differences`(obj)

  Parameters:

  - obj – the object to be inspected
  - session – the session the object belongs to

  Return type: | field_name, old_value, new_value. Old value and new value are lists.

`get_history`(obj, field_name)

  Parameters:

  - obj – the object to be inspected
  - field_name – the field_name
  - session – the session the object belongs to

  Return type: | new_value, unchanged_value, old_value, . Old value and new value are lists. The order is different from `get_differences` as this is exactly what is returned from the SA function

## Saving varchar and text fields

Text fields with empty values will be saved as NULL. To change this behavior you need to set blank=True on fields:

    t.gui_fields[field_name].blank = True

## UiManager: menu and actions

Menu entries are handled via standard gtk.UiManager interface. One interface is created for each toplevel Window and for each view in a SqlTable widget. You can see some examples in the demo (70-72).

Standard actions are divided into the following categories:

    General (self.actiongroup_general)
         PendingDifferences
         Quit
         Go
         Modify
         Tools
         File
         Help
         About
    Table (self.actiongroup_table)
         HideColumns
         ShowColumns
         Records
         MaskViewFKey
         Export
         MaskView
         Zoom-fit
    Insert (self.actiongroup_insert)
         New
         Save-as (just for SqlMask)
    Delete (self.actiongroup_delete)
         Delete
         RecordDelete
    Update (self.actiongroup_update)
         Save
         Undo  (just for SqlMask)
    Browse (self.actiongroup_browse)
         Filters
         Reload
    Select (self.actiongroup_select)
         Back
         Forward
    Print (self.actiongroup_print)
         Print
    Debug (self.actiongroup_debug)
         Gtk-tree

While for each table’s view you have:

    Table
         HideColumns
         ShowColumns
         Records
         MaskViewFKey
         Export
         MaskView
         Zoom-fit
    Insert
         New
    Delete
         Delete
         RecordDelete

### Changing an entry

To change an entry you can:

1. add an actiongroup in which you have defined an action with the same name
2. insert this actiongroup before it’s relevant one (position 0 is normally a good choice

Example #72 in the demo shows how to do it

### Adding an entry

the standard way is shown in demo snippet #70:

1. create an xml definition and add it to ui_manager
2. create an action and add it to an actiongroup

You can also use a SqlTable method add_temporary_item that will add a temporary entry, so that it can be contextually changed. This way is demonstrated in demo snippet #71