mRFC 0041: Database views
- Revision history
- Background
- View declaration
- Table scope
- View properties
- Metadata properties
- Registering view
- Join scope
- Constraints
- Managing view
- Sitegroup context
- Query views
Revision history
- 2009-05-06 - created by Piotr Pokora
- 2009-05-06 - added query notes by Piotr Pokora
- 2009-05-06 - fixed condition example by Piotr Pokora
- 2009-06-05 - Changed table scope information by Piotr Pokora
- 2010-01-21 - Described metadata class usage by Piotr Pokora
Background
Database views implementation should provide flexible way to creating database joins of any type. Views shall be represented by special, abstract midgard_view class. User defined views shall be registered as midgard_view derived classes. Such, shall be available as any other typical classes, and database access shall be encapsulated and completely hidden for them.
View declaration
Views shall be declared in xml files. View element shall have only one attribute with its name definition. Any other attributes (e.g. with table name) shall not be allowed and considered as explicit error. View shall be defined with reserved word view with reserved name attribute. Implementation shall be responsible for checking name uniqueness. Which means, an explicit error should be raised if view name is already registered as any other class.
<view name="groups_with_coord" />
Table scope
Table scope, used in FROM SQL part, shall be configured by property's attribute of view. Reserved words table or class shall be defined, and application shall raise error if none of them is found as defined attribute. table attribute shall take precedence, and if not found, class shall be taken into account. A special class attribute defines implicitly table which is defined for given class.
<view name="groups_with_coord" table="grp_table"/>
View properties
Implementation shall allow user, to register view properties. These should represent database view fields (columns). Special, reserved word 'property' shall be used to register view property (and thus, field). A reserved, and mandatory word use shall be used as allowed property's element attribute. Such attribute's value shall define class name, and its property which shall be used as database data source. Both shall be separated by semicolon.
<property name="guid" use="midgard_group:guid" />
Optionaly, property element could have description defined as child element. Description shall be defined with reserved word description and with mandatory, reserved value named atribute.
<property name="guid" use="midgard_group:guid">
<decription value="Stores value of a guid which identifies group" />
</property>
Metadata properties
User shall be allowed to include metadata properties in a view which is registered. Reserved metadata word shall be used with class and property name to explicitly set metadata column as view's property's storage. Such shall be defined as custom view property, with special dot separator '.' used between metadata keyword and its property name.
<property name="created" use="midgard_group:metadata.created" />
Metadata keyword doesn't describe particular metadata class which is registered as any class' one. User shall be aware of specific metadata class which should be taken into account. Explicit error shall be thrown, if class, which we register view's property for, doesn't have metadata or its metadata doesn't have defined property.
(Note: adding metadata properties is valid since 9.09.2 version)
Registering view
Views shall be registered during application startup. Midgard unified configuration file shall provide a special key to define path to directory, with view xml files. midgard_view derived classes shall be registered after all user defined MgdSchema classes has been registered. After such step, views shall be created in database.
Join scope
Reserved, join named element shall define database join. There shall be no limit how many joins could be done per database view. Join type, shall be defined with reserved 'type' attribute. A value for such attribute shall be one of reserved ones:
- inner
- self
- left
- left outer
- right
- right outer
class named and reserved attribute shall define which, (already defined) class' table is joined in a view. As an alternative, table can be defined explicitly using reserved word table.
<join type="left" class="midgard_member" />
Implementation shall raise error, if given class is not yet registered.
Join condition
To assure join condition, a special, reserved word condition shall be used as join element's child one. For such, two attributes shall be used: left and right. A value for both shall be class name and its property separated by semicolon.
<join type="left" class="midgard_member">
<condition left="midgard_group:id" right="midgard_member:gid" />
</join>
Constraints
Optionaly, view definition could have constraints elements to limit records selected from database. This shall be defined with reserved word constraint and with allowed, reserved attributes:
- property - Class name and its property separated by semicolon
- operator - An SQL operator, the same which is allowed in midgard_query_builder.
- value - Any, user defined value.
- value_type
Only value_type shall be optional. This shall define value type, and shall accept the same value types which are accepted in MgdSchema xml files.
<constraint property="midgard_group:owner" operator=">" value="0" value_type="unsigned integer" />
Managing view
Midgard core shall provide midgard_view abstract class implementation. Such, shall allow programmer:
- Create view in database if it doesn't exist
- Remove view from database if it exists
- Check if database view exists
Every database view represented by midgard_view derived class shall be considered read-only.
Sitegroup context
midgard_view implementation shall guaranteed sitegroup context. This shall be resolved by implicit constraints or joins usage.
An implicit left join shall be added to every view. Such join shall provide condition, where equality of sitegroup fields is assured.
An implicit constraint shall be added to every view. Such constraint shall limit selected records to these, for which sitegroup field's value is equal to current application sitegroup.
Query views
Midgard core shall implement views such way to allow query them with midgard_query_builder or midgard_collector. Optionally midgard_view derived classes shall be usable also with midgard_reflection_property or midgard_replicator. For the latter, only serialization shall be allowed.