Record and Selection
The Record class implements the behaviour of a new or existing SQL record. All method names
start with an underscore to avoid collision with all possible field name. The record is constructed in such
a way, that all fields are available as object attribute names.
Create a new record instance.
All attributes that don't start with '_' are assumed to be fields.
There are 3 different strategies to fill the fields:
If the id is None, then insert a new record in the database upon writing (e.g. deletion) and set the new created id.
if the id is set and he forceinsert attribute is True, then insert a new record with this id. Note that this is mainly
used when initializing a table from an application. Or else there may be duplicate id’s in the databasae.
Otherwise just perform an update in the database for the defined id.
Create a new record instance.
All attributes that don't start with '_' are assumed to be fields.
Python example
record['abc']
record.abc
record['abc']
record.abc
There are 3 different strategies to fill the fields:
| id | _initialized | |
| None | False | Fields are empty and can be changed individually. |
| >0 | False | Try to read from the database upon first attribute get, then set _initialized to True |
| >0 | True | No change |
The Selection class implements the behavior of an SQL selection of records.
Best usage is to get a selection by calling the function getselection
A Selection instance behaves “lazy”: it does not actually read from the database, until it really needs the information. And then the following happens:
If the query attribute is defined, that this string is used as SQL query. The attributes where, order, start and slice are ignored then.
All method names start with an underscore to avoid collision with all possible field name. The record is constructed in such a way, that all fields are available as object attribute names.
TODO If the tablename of a selection contains one or more periods, then it is assumed that the selection is a result of a join operation. All fields in are then available with periods. @@@ Better than a separate Join class? Selection(self.agent, 'element.item_id', where='...', order='item.name') # Inner join # SELECT column_name(s) # FROM table_name1 # INNER JOIN table_name2 # ON table_name1.column_name=table_name2.column_name # # Left join # SELECT column_name(s) # FROM table_name1 # LEFT JOIN table_name2 # ON table_name1.column_name=table_name2.column_name # # Right join # SELECT column_name(s) # FROM table_name1 # RIGHT JOIN table_name2 # ON table_name1.column_name=table_name2.column_name # # Full join # SELECT column_name(s) # FROM table_name1 # FULL JOIN table_name2 # ON table_name1.column_name=table_name2.column_name
Best usage is to get a selection by calling the function getselection
Python example
getselection(agent, tablename, where=where, order=order, start=start, slice=slice)
getselection(agent, query=query)
getselection(agent, tablename, where=where, order=order, start=start, slice=slice)
getselection(agent, query=query)
A Selection instance behaves “lazy”: it does not actually read from the database, until it really needs the information. And then the following happens:
- Read a selection of (raw) records using SQL where as clause.
- The raw records are in the list.
- Only when a record is referred to by an index, then it gets instantiated to a Record instance, where it will replace the original dict in the list.
If the query attribute is defined, that this string is used as SQL query. The attributes where, order, start and slice are ignored then.
All method names start with an underscore to avoid collision with all possible field name. The record is constructed in such a way, that all fields are available as object attribute names.
TODO If the tablename of a selection contains one or more periods, then it is assumed that the selection is a result of a join operation. All fields in are then available with periods. @@@ Better than a separate Join class? Selection(self.agent, 'element.item_id', where='...', order='item.name') # Inner join # SELECT column_name(s) # FROM table_name1 # INNER JOIN table_name2 # ON table_name1.column_name=table_name2.column_name # # Left join # SELECT column_name(s) # FROM table_name1 # LEFT JOIN table_name2 # ON table_name1.column_name=table_name2.column_name # # Right join # SELECT column_name(s) # FROM table_name1 # RIGHT JOIN table_name2 # ON table_name1.column_name=table_name2.column_name # # Full join # SELECT column_name(s) # FROM table_name1 # FULL JOIN table_name2 # ON table_name1.column_name=table_name2.column_name
Usage of Record
A Record is a the main element to access an SQL database through Xierpa. The Record object knows how to communicate with the agent (an open database connector) to perform select, insert or update queries.Record objects can best be seen as a virtual piece of the database, since the reading is automatic and transparant. Even subsequent reading and writing on the same record might not even cause actual operations on the database. The record will only take action when the object is either deleted or asked to perform a synchronization call through record._sync().
from xpyth.xierpa.db.record import getrecord record = getrecord(agent, 'address', readonly=False) record.lastname = 'Apple' record = None
from xpyth.xierpa.db.record import getrecord record = getrecord(agent, 'address', readonly=False) record.lastname = 'Apple' record._sync() print record.id
record._sync() print record.id
Similar the following code would read an existing record and perform an SQL update query.
from xpyth.xierpa.db.record import getrecord record = getrecord(agent, 'address', id=123, readonly=False) record.lastname = 'Apple' record._sync()
It is not allowed to change an id. Any attempt to perform e.g. record.id = 123 will raise an error. However there are situations in which the record id must be set (e.g. when a set of records is initialized from an external source or in a Selection. In that situation a special method can be used:
record._forceid(123)
Relations
The arrow in a graffle define the relations between table as can be seen in the following example.
many2one
From the viewpoint of the authorization table there is only one possible address address record to be selected. The relation from that point of view will answer a Record instance. To make this work there is a special field definition for the relational fields. From the outside they behave as a bigint, but they know how to derive the name of the related table from the name of the connection (see the chapter about relation path names). e.g. address_id. The “_id” indicates that the field is not a a number but it should respond as a relation to the table address.
The usage of a many2one relation is relatively simple.
authorization.address_id.email
one2many
From the viewpoint of the address table there are many possible roles to be selected. The relation from that point of view will answer a Selection instance. To make this work, there is a special field definition (which does not actually exists in the table address) named e.g. authorization_many. The “_many” is the counterpart of fieldnames ending with “_id”, to show that they relate to many other records. The complete set, including these relational fields are defined in the graffle export file datamodel.graffle.sqlmodel.py.
if 'admin' in address.authorization_many.name:
['admin', 'user', 'student', 'teacher']
This “chaining” of related tables is a very important feature of Xierpa. Since many of Record and Selection method answer self or a new instance, the result can be used to request a new method.
Selection chains
Since most Selection methods answer either a new Selection or a Record it is possible to chain the requests. Methods like _select and _where answer an adjusted Selection instance. Note that since the original selection was not initialized, there is no overhead of records actually accesing the database. Some examples:
from xpyth.xierpa.db.agent import newagent
agent = newagent('/xierpa/hoynck_work/_graffles/datamodel.graffle', 'hoynck')
agent.opendb()
orgs = getselection(agent, 'org') # In potential all organizations
deps = getselection(agent, 'dep') # In potential all departments
...
# Get list names of all organizations
orgs.name
...
# Get a list of names of all departments of all organizations
orgs.dep_many.name
...
# Get a list of names of all organizations of all departments
deps.org_id.name
...
# Get a Selection of all departments of all organizations that fit the where
orgs._where("name~*'abc'").dep_many
...
# Get a list of lastnames of all departments with a name that hold "abc"
# for all organizations in the database, ordered by the name of the departments.
orgs.dep_many._where("name~*'abc'")._order('name').adr_many.lastname
...
# Answer a list of names of the 2nd and 3rd organization, ordered by name
deps.org_id._order('name DESC')._start(2)._slice(2).name
deps.org_id._select(order='name DESC', start=2, slice=2).name
| Relation | Result |
|---|---|
| getrecord() | Record |
| Record.field | (field type) |
| Record.table_id | Record |
| Record.table_many | Selection |
| getselection() | Selection |
| Selection.field | List of (field type) |
| Selection.table_id | Selection |
| Selection.table_many | Selection |
| Selection._where() | Selection |
| Selection._where()._order() | Selection |
| Selection._where()._order()._start() | Selection |
| Selection._where()._order()._start()._slice() | Selection |
| Selection._select() | Selection |
| Selection[2] | Record |
If one of the relations does not exist, then respectively a NoneRecord or NoneSelection is answered. The resulting behaviour of none-relations in a chained query is as follows:
| Relation | Result |
|---|---|
| NoneRecord.field | None |
| NoneRecord.table_id | NoneRecord |
| NoneRecord.table_many | NoneSelection |
| NoneSelection.field | List of None |
| NoneSelection.table_id | NoneSelection |
| NoneSelection.table_many | NoneSelection |
| NoneSelection._where() | NoneSelection |
| NoneSelection._where()._order() | NoneSelection |
| NoneSelection._where()._order()._start() | NoneSelection |
| NoneSelection._where()._order()._start()._slice() | NoneSelection |
| NoneSelection._select() | NoneSelection |
| NoneSelection[2] | NoneRecord |
view
Future development, not implemented yet. The Selection and Record method view, join, rjoin and ljoin allows to to extend a set queries into a single view query before the selection does execute one.
query
If the getselection is called with the query attribute set, then this string is uses as plain SQL string. In that case the other attributes where, order, start and slice are ignored.
Special fields
If the record definition contains the fields creationdate, creationtime, modificationdate and modificationtime, then the value of these fields is automatically updated by the record upon writing to the database by calling respectievely the methods:record._setcreationtime() record._setmodificationtime()
Normalized fields (local and global)
In a graffle it is possible to defined normalized fields. They are marked with (n) directly after a field in the table definition box. This will make the graffle parser build a unique table for this field that holds the normalized field values. The main fields acts a a reference to this table. The parsers does automatically construct unique field names. Similar it work with global normalized fields written as fieldname(g). In that case one global table will be generated. This works as follows.| table | graffle field | table field | normalized table | getting normalized value |
|---|---|---|---|---|
| abc | myfield(n) | abc_myfield_n_id | abc_myfield_n | record.abc_myfield_n_id.value |
| abc | myfield(g) | myfield_g_id | myfield_g | record.myfield_g_id.value |
Virtual fields
Sometimes one needs to store additional information in a record. In practice this leads to adding fields to a record, even if they are only in one particular situation. In order to add the functionality of multiple “miscellaneous” fields to any record, the following technique is available. If a record is addressed by a field name that ends with _f then this field is assumed to be “virtual”. In that case an additional related table must be defined. The interpreation of the field name is detected by the Record attribute methods, so in practice there is no difference in usage between normal fields and virtual fields.| table | field name | field table | getting virtual value |
|---|---|---|---|
| abc | myfield_f | abc_f | record.myfield_f |
Note: Cleared fields are not deleted, they stay with a NULL value related to the record.
An overview of Record
A Record is the main unit of Xierpa database information. The following are some examples of what can be done with a Record instance.
from xpyth.xierpa.db.selection import getrecord
record = getrecord('address', id=123)
print record.firstname
print 'admin' in record.authorization_many.name
| Method | Attributes | Docstring or default value |
|---|---|---|
| _close |
Delete the record from the weak agent cache. In earlier versions the record would try to sync itself
but this is causing problems when the database connection was closed upon deletion earlier than
the garbage collection of self. So all changed records now should be “manually” saved to the database.
This does not seem to be a problem, since we generally do a record._sync() anyway. Instead we no raise an error if the record indicates to be changed at this point. If it is a new record (without id) and changed then ignore the error message. Python example record._close() |
|
| _copy |
Answer a duplicate of the current record where the id is set to None, so it will
be inserted in the table. See also: _new |
|
| _delete |
Delete the record from the database by the SQL delete query,
but only when record has an id and it is not _readonly.
Python example record._delete() |
|
| _dict |
The _dict method answers a dict with all field/values.
|
|
| _fieldnames |
The _fieldnames method does answer an unsorted list of fieldnames of the record as defined
in the graffle. This is equivalent to the agent call self.__agent.fieldnames(self.table). Python example record._fieldnames() agent.fieldnames(tablename) |
|
| _forceid | id |
It is not allowed to change the id field of a record. Any attempt to perform e.g.
r.id = 123 will raise an error. However there are situations in which the record id
must be set (e.g. when a set of records is initialized from an external source or in a Selection. In that
situation this method can be used. Python example record._forceid(123) |
| _get | field |
The get method answers the content of the field field. If the field does not
exist, then answer None. We want records
to be compatible with regular Python dict objects, so also get is defined
as method. This implies that field names are restricted not to be named “get” or “has_key”.
Note that the following notations are all equivalent:
Python example record._get('lastname') record.get('lastname') record['lastname'] record.lastname with the standard Python exception that the _get will not raise a KeyError if the key does not exist. It answers None instead. |
| _getalien | field |
The _getalien method answers the content of the field field, without checking if
the field exists in the table description. This can be used if the content of the record was derived from
a custom query, so it contains fields of joins with others tables.
|
| _getchildfieldmanyname |
The _getchildfieldmanyname method answers the field of the child reference for this record.
If the self is not an element (if it does not have referencing child fields), then answer None.
|
|
| _getchildfieldname |
The _getchildfieldname method answers the field of the parent reference for this record.
If the self is not an element (if it does not have a referencing parent field), then answer None.
|
|
| _getchildren | order slice start where |
The _getchildren method answers the children of the record as value of
self.<tablename>_child_<tablename>_many._where(where).
If the field does not exists, then this record is not part of a hierarchy. In that case
answer an empty list. The where is used as additional selector. The default behaviour of the attribute is 'not deleted' if there is a file named self.FIELD_DELETED. The optional order attribute is used to define the order of the selection. |
| _getparent | table |
The _getparent method answers the parent of the record as value of
self.<tablename>_child_<tablename>_id. If the field does not exist, then this record
is not part of a hierarchy and None is answered. The optional table is to make instances of Record and JoinedRecord compatible. |
| _getreadonly |
The _getreadonly method answers the _readonly flag of the record.
|
|
| _gettable |
The _gettable method answers the name of the table of the record instance.
|
|
| _has_key | field |
The _has_key answers a boolean id the key is available as field. We want records
to be compatible with regular dict objects, so also has_key is defined
as method. This implies that field names are restricted not to be named "get" or "has_key".
Note that the following notations are all equivalent:
Python example record._hasfield('lastname') record._has_key('lastname') record.has_key('lastname') |
| _hasfield | field |
The _has_key answers a boolean id the key is available as field. We want records
to be compatible with regular dict objects, so also has_key is defined
as method. This implies that field names are restricted not to be named "get" or "has_key".
Note that the following notations are all equivalent:
Python example record._hasfield('lastname') record._has_key('lastname') record.has_key('lastname') |
| _isfolder |
The _isfolder method answers the boolean if the record is a folder (which means that is the field with name
'%s_child_%s_id' % (self.__table, self.__table) exists.
|
|
| _isnew |
Answer a boolean value True if this record will be written with the SQL insert query
or False with SQL update query. otherwise. This method is equivalent to
record.id is None.
Python example record._isnew() |
|
| _new | data |
Answer a duplicate of the current record where the id is set to None, so it will
be inserted in the table. Don’t copy any of the other fields, except the many2one relations.
This means that the location of the new record is equal to self, except that it is empty. The data attribute can be used to initialize the fields by supplying a dict with field/value pairs. See also: _relateto Python example record = address.department_id._new() record = address.department_id._new(data={'name': 'New department'}) |
| _newchild |
The _newchild method ansers a new child element record of self if the field
self.<tablename>_child_<tablename>_many._where(where) exists.
If the field does not exist, then this record is not part of a hierarchy. In that case
a TypeError is raised. The new element has the attribute readonly flag set to False.
If the deleted field exists, it is set to False. The new record
is not synced, so the calling application can set the content of other field values first.
|
|
| _newrelation | readonly relation table |
Create a new record from the table named table and relate it to self using the
optional relation named field as connection. If the relation is not defined, then use
the default relation name table.name + '_id'. Answer the new record. The readonly defines if the new record is readonly or not. Since we can assume that the user wants to write in this new record, the default value of the readonly attribute is False. See also: _relateto See also: _new See also: Agent.getrecord Python example self._newrelation('project') self._newrelation('address_author_responsible') |
| _readdata |
If not yet initialized and there is an id value set, then force the record to read field values
from the database.
Python example record._readdata() |
|
| _relateto | record relation |
Relate the record to self using the relation named field as connection.
If relation is None then try to derive the relation name from the names
of the two tables. If all fails, then raise an error. The method does check if the relating record has an id value. If not, then the record is synchronized (that is performing an SQL insert query). See also: _newrelation See also: Agent.getrecord Python example record._relateto(anotherrecord) record._relateto(anotherrecord, 'address_author_responsible') |
| _setcreationtime |
Set the creationdate and creationtime of the record to the current date/time. This method is automatically called when the _sync applies an SQL insert query. Python example record._setcreationtime() |
|
| _setdata | data |
Initialize all fields from the dataset, this can only be done once for a record.
When setting the field values, temporarily set the record _readonly flag to False.
Only set the fields that exist in the dataset.
We don’t forget to reset the record __changedfields attribute finally,
since the setattr makes it true. It was a very old and nasty bug that slowed down the use of record[field] dramatically by reading the record from database for every entry. Adding self.__initialized = True makes an initialized record not be read again in self._readdata(). Python example record._setdata(data) |
| _setdummy | dummy |
The _setdummy method sets the __dummy flag of the record to the boolean
dummy attribute value. If the flag is True then the record behaves
normally except that it does not write in _sync().
|
| _setmodificationtime |
Set the creationdate and creationtime of the record to the current date/time. This method is always automatically called when the _sync applies writing query. Python example record._setmodificationtime() |
|
| _setparent | recordorid |
The _setparent method sets the parent of the record self.<tablename>_child_<tablename>_id
to recordorid. If recordorid is a Record instance, then get the id from it. If recordorid is None then ignore the request. The readonly attribute of self is adjusted in order to make the parent change. Finally the original value of restored. If the field does not exist then the record is not part of a hierarchy. In that case a TypeError is raised. |
| _setreadonly | readonly |
The _setreadonly method sets the __readonly flag of the record to the
boolean readonly attribute value.
|
| _show | Show the content of all fields |
|
| _sync | force updatemodificationtime |
Recursively synchronize the record and all related records with the database.
Then sync the record, but only if writeable and something changed. Normally this method is called
automatically when the record object is deleted. But sometimes it is useful to know the new id
value of a record. The force attribute will overrule the record.__changedfields, so the synchronization will always be written. The attribute does not overrule the record._readonly setting of the record. If the self.__dummy is set to True (using self._setdummy(True)) then no actual writing takes place. This can be used for testing applications without writing to a database. Also when records are not allowed to write themselves while still the record fields need to be changed, then the dummy flag can be used. The optional updatemodificationtime (with default value True) makes the modification date and time update automatically. Python example record._sync() print record.id |
| get | field |
The get method answers the content of the field field. If the field does not
exist, then answer None. We want records
to be compatible with regular Python dict objects, so also get is defined
as method. This implies that field names are restricted not to be named “get” or “has_key”.
Note that the following notations are all equivalent:
Python example record._get('lastname') record.get('lastname') record['lastname'] record.lastname with the standard Python exception that the _get will not raise a KeyError if the key does not exist. It answers None instead. |
| has_key | field |
The _has_key answers a boolean id the key is available as field. We want records
to be compatible with regular dict objects, so also has_key is defined
as method. This implies that field names are restricted not to be named "get" or "has_key".
Note that the following notations are all equivalent:
Python example record._hasfield('lastname') record._has_key('lastname') record.has_key('lastname') |
An overview of Selection
A Selection instance behaves just like any other list object. The following are some examples of what can be done with a Selection instance.
from xpyth.xierpa.db.selection import getselection
selection = getselection('address')
len(selection)
selection[0].firstname
'admin' in selection[0].authorization_many.name
| Method | Attributes | Docstring or default value |
|---|---|---|
| _append | item |
The _append method appends the record or selection to the current record list.
|
| _deleteall |
The _deleteall method deletes all records in the current selection.
|
|
| _dict | field |
Answer a dictionary where the key the content of the defined field and the values are a new Selection
instances of records. The order of of the records in the selections is identical as they came from the query result. If the fields is omitted of None, then use the 'id' as field name. Note: We don’t need to instantiate the records, since we put them directly into the new selection. This selection instance will instantiate Record objects if needed. |
| _fieldnames |
The _fieldnames method does answer an unsorted list of fieldnames of the record as defined
in the graffle. This is equivalent to the agent call self.__agent.fieldnames(self.table). Python example record._fieldnames() agent.fieldnames(tablename) |
|
| _find | field operator value |
The _find does answer a new Selection with only the records that fit the field/value
combination as compared by the value of the operator attribute. The operator attribute can be = (default), ~* and ~, so the comparison behaves identical to SQL queries. |
| _get | index |
Answer the record at position index. We want selections
to be compatible with regular dict objects, so also get is defined
as method. This implies that field names are restricted not to be named "get" or "has_key".
|
| _getids |
Answer a list of all ids of the current selection. Note: We don’t need to instantiate the records, since we get them directly from the raw selected data. |
|
| _getparent | table |
The _getparent method does answer a list of all parent records of self in the order of self,
where all doubles are removed. The optional table is to make the call compatible with the
Join instance.
|
| _getrecords |
The _getrecords answers the current list of (all instantiated) records.
|
|
| _gettable |
The _gettable method answers the table name of the selection.
|
|
| _getwhere |
The _getwhere method answers the self.__where clause of the selection. This method is
here mostly for debugging reasons, since the moment a Selection is generated and the actual reading
of record is not the same. The method allows the programmer to see what the actual where clause is that the
selection will be selecting with.
|
|
| _initialize | ||
| _instantiate | index |
|
| _instantiateall | ||
| _keys |
Ansert a unsorted list of fields name for the table of this selection.
|
|
| _list | field |
The _list method does answer a list of the values of the named field for all records
in the current selection. The _list method is equivalent to the use of field names directly as
attributes to a Selection instance.
The following lines are equivalent.
Python example selection._list('lastname') selection.lastname |
| _listnotempty | field |
The _listnotempty method does answer a list of the not-empty values of the named field
for all records. If the fields is omitted of None, then use the 'id' as field name. |
| _max | field |
Answer the maximum value of the field content as interpreted as number.
|
| _min | field |
Answer the minimum value of the field content as interpreted as number.
|
| _order | order |
The primary usage of the _order method is to modify the order value of the current
Selection instance if it is generated by a one2many reference. The method answers a
new Selection instance with all values copied from self except for the changed
order value. Note that there is not really overhead creating a new Selection instance,
since a selection only reads data when the content is actually referenced. If the order attribute is a list, then convert it to a comma separated string. Otherwise the order attribute has the standard SQL syntax. See also: select Python example organization.order('id') organization.department_id.order('name DESC') organization.department_id.address_id.order('lastname, firstname ASC') |
| _select | order slice start where |
The primary usage of the _select method is modify (a combination of) the where, order,
start and slice values of the current
Selection instance if it is generated by a one2many reference. The method
answers a new Selection instance with all values copied from self except
the values of the defined attributes. Note that there is not really overhead creating a new
Selection instance, since a selection only reads data when the content is actually referenced. If the current selection already has a where clause defined, then merge the two clauses with an AND. See also: select Python example organization.department_id.address._select(where="firstname ~* 'Jan'", order='lastname', start=10). |
| _set | field |
Answer an unsorted list of the unique values of the named field for all records. Note: We don’t need to instantiate the records, since we get them directly from the raw selected data. |
| _slice | slice |
The primary usage of the _slice method is to modify the slice value of the current
Selection instance if it is generated by a one2many reference. The method
answers a new Selection instance with all values copied from self except
for the changed slice value. Note that there is not really overhead creating a new
Selection instance, since a selection only reads data when the content is actually referenced. See also: select Python example organization.department_id.order('name') |
| _start | start |
The primary usage of the _start method is to modify the start value of the current
Selection instance if it is generated by a one2many reference. The method
answers a new Selection instance with all values copied from self except
for the changed start value. Note that there is not really overhead creating a new
Selection instance, since a selection only reads data when the content is actually referenced. See also: select Python example organization.department_id.order('name') |
| _sum | field |
Answer the total sum of the value of field.
|
| _where | where |
The primary usage of the _where method is to modify the where clause of the current
Selection instance if it is generated by a one2many reference. The method answers a
new Selection instance with all values copied from self except for the changed
where clause. Note that there is not really overhead creating a new Selection
instance, since a selection only reads data when the content is actually referenced. If the current selection already has a where clause defined, then merge the two clauses with a bracket (...) and (...). If where is None, then ignore the clause and only use the existing internal self.__where. See also: select Python example organization.department_id._where("name = 'Sales'") |
| get | index |
Answer the record at position index. We want selections
to be compatible with regular dict objects, so also get is defined
as method. This implies that field names are restricted not to be named "get" or "has_key".
|
Join and JoinedRecord
The JoinedRecord class implements the behaviour of a new or existing SQL record as result of a join.
The record is constructed in such a way, that all fields are available as object attribute names. To avoid collision
between names of different tables, the table name is added to the start of a field name, followed by an underscore,
but if the fieldname already starts with the table name (as e.g. in normalized fields and named relations) then the
field name remains untouched.
All attributes that don't start with '_' are assumed to be fields.
All attributes that don't start with '_' are assumed to be fields.
Python example
record['address_myname']
record.address_myname
record['address_myname']
record.address_myname
The Join class implements the behaviour of an SQL joined selection of records.
Best usage is to get a selection by calling the function getjoin
examples
A Join instance behaves “lazy”: it does not actually read from the database, until it really needs the information, e.g. if an individual record or record field is addressed. And then the following happens:
The tables attribute must be a list with table names or a comma separated string. The table name can be followed by the name of the relation as in 'activity.activity_author_address_id'. If omitted then the default relation 'activity.address_id' or 'activity.address_many is used', depending on the direction of the relation.
All method names start with an underscore to avoid collision with all possible field name. The record is constructed in such a way, that all fields are available as object attribute names.
The type of join is defined in the table-relation pattern. It can have one of the values Join.JOIN_INNER, Join.JOIN_LEFT, Join.JOIN_RIGHT, Join.JOIN_FULL.
Set the initialized attribute to True to prevent the selection to read from the database. This option is used, when the application wants to fill the selection with the _appendraw method. Default value for initialized is False
Best usage is to get a selection by calling the function getjoin
Python example
getjoin(agent, tables, where=where, order=order, start=start, slice=slice)
getjoin(agent, tables, where=where, order=order, start=start, slice=slice)
examples
Python example
getjoin(self.agent, 'item.element_many->element,
getjoin(self.agent, 'item.element_many->element,
A Join instance behaves “lazy”: it does not actually read from the database, until it really needs the information, e.g. if an individual record or record field is addressed. And then the following happens:
- Read a selection of (raw) records using SQL where as clause.
- The raw records are dictionaries in a list.
- Only when a record is referred to by an index in the selected list, then it gets instantiated to a JoinedRecord instance, where it will replace the original dict in the list.
The tables attribute must be a list with table names or a comma separated string. The table name can be followed by the name of the relation as in 'activity.activity_author_address_id'. If omitted then the default relation 'activity.address_id' or 'activity.address_many is used', depending on the direction of the relation.
All method names start with an underscore to avoid collision with all possible field name. The record is constructed in such a way, that all fields are available as object attribute names.
The type of join is defined in the table-relation pattern. It can have one of the values Join.JOIN_INNER, Join.JOIN_LEFT, Join.JOIN_RIGHT, Join.JOIN_FULL.
| Type | Left table | Right table | Result |
|---|---|---|---|
| INNER | A | B | Intersection of A and B |
| LEFT | A | B | All of A + intersection |
| RIGHT | A | B | All of B + intersection |
| FULL | A | B | All of A and all of B |
Set the initialized attribute to True to prevent the selection to read from the database. This option is used, when the application wants to fill the selection with the _appendraw method. Default value for initialized is False
Usage of Join
The Join instance implements the standart SQL join as SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name, which allows to read the fields of a joined selection. This is much more efficient than “hopping” from one table to another using the relations. Also a Join allows the result of a selection be sorted on the fields of multiple columns.The difference with the basic Selection is that the JoinedRecord instances, as produced by the join cannot be used to update the content of fields.
The JoinedSelection class has the following methods defined:
| Method | Attributes | Docstring or default value |
|---|---|---|
| _fieldnames |
The _fieldnames method does answer an unsorted list of fieldnames of the record as defined
in the graffle. This is equivalent to the agent call self.__agent.fieldnames(self.table). Python example record._fieldnames() agent.fieldnames(tablename) |
|
| _fields |
The _fields runs through the attributes of self to find a get all available field names and
answers them in a list.
|
|
| _getchildren | order slice start where |
The _getchildren method answers always an empty list. For now joined records are not supposed to have children.
|
| _getparent | table |
The _getparent method answers the parent record of joined table table.
|
| _gettables |
The _gettables method answers the list of tuples of joined table names and relation names.
|
|
| _setdata | data |
Initialize all fields from the tables dataset, this can only be done once for a record. If the self.__descriptor
is defined, then use that as field information. Otherwise just copy the plain dictionary data key/value.
Python example record._setdata(data) |
| Method | Attributes | Docstring or default value |
|---|---|---|
| _buildquery |
The _buildquery method builds the query for the four difference join types. The difference is made
by the pattern in the table-relation self.JOIN_INNER, self.JOIN_LEFT, self.JOIN_RIGHT,
self.JOIN_FULL.
|
|
| _buildqueryfieldpart | query |
The _buildqueryfieldpart method calculates the fields part of the query.
Note that this protocol currently only works on single relation. So it does not work if multiple
records of the same table are selected in the same join.
|
| _buildqueryjoinpart | query relations |
The recursive _buildqueryjoinpart is a private method of _buildquery to build part
of the join query.
|
| _fields | ||
| _initialize |
The _initialize method makes sure that the data of the selection is actually read.
This is used to allow the joined selection to perform lazy, it only reads the data when it really
needs it.
|
|
| _instantiate | index |
The _instantiate method will convert the raw data that was passed from the agent
into a JoinedRecord instance.
|