zms.zmssqldb.ZMSSqlDb

class documentation

class ZMSSqlDb(zmscustom.ZMSCustom): (source)

Custom ZMS content type that exposes relational database backed records.

Method	`ajaxGetAutocompleteColumns`	Return matching entity column names for autocomplete widgets.
Method	`ajaxGetObjOptions`	Return autocomplete option labels for a configured entity column.
Method	`assemble_query_result`	Normalize a database query result into ZMS column and record structures.
Method	`assembleFilter`	Assemble the SQL filter expression for a where clause.
Method	`commit`	Commit the current database transaction.
Method	`delete_blob`	Delete a blob after authorizing the remote caller.
Method	`execute`	Execute an SQL statement using the configured database adapter.
Method	`executeQuery`	Execute an SQL modifying statement and return the affected row count.
Method	`filteredChildNodes`	Return an empty list because SQL database objects do not expose child nodes.
Method	`get_blob`	Return the raw data payload of a stored blob.
Method	`getDA`	Return the configured database adapter object.
Method	`getEntities`	Retrieve and process a comprehensive list of entity descriptors from the database.
Method	`getEntitiesSQLAlchemyDA`	Return a list of entity descriptors by reflecting the database schema via SQLAlchemy.
Method	`getEntity`	Return an entity descriptor with metadata and columns for a given table.
Method	`getEntityColumn`	Return a column descriptor with metadata and value for a given table column.
Method	`getEntityDetailsGridContext`	Build the full rendering context for `manage_zmi_details_grid`.
Method	`getEntityPK`	Return the primary-key column name of an entity.
Method	`getEntityRecordHandler`	Build a helper that post-processes rows for export and display.
Method	`getEntityTarget`	Resolve the effective target entity for relation-like stereotypes.
Method	`getFk`	Resolve or create a referenced row for a foreign-key relation.
Method	`getModel`	Return the parsed SQL model and refresh the cached copy when needed.
Method	`getModelContainer`	Return the Zope object that stores the serialized SQL model definition.
Method	`manage_changeConfiguration`	Update SQL entity configuration and field metadata from the management UI.
Method	`manage_changeProperties`	Persist SQL database properties and the serialized model definition.
Method	`query`	Execute a select statement and normalize the result for ZMS forms.
Method	`record_encode__`	Decode byte values in a database row according to the given encoding.
Method	`recordSet_Delete`	Delete a row from the selected record-set table.
Method	`recordSet_Filter`	Apply persisted and submitted filters to the current record-set query.
Method	`recordSet_Init`	Initialize record-set request state for listing and filtering.
Method	`recordSet_Insert`	Insert a new row into the selected record-set table.
Method	`recordSet_Select`	Build the base select statement for a record-set query.
Method	`recordSet_Sort`	Apply order-by clauses to the current record-set query.
Method	`recordSet_Update`	Update an existing row in the selected record-set table.
Method	`recordSet_UpdateIntersections`	Synchronize intersection tables for multi-select style columns.
Method	`rollback`	Roll back the current database transaction.
Method	`set_blob`	Store a blob payload after authorizing the remote caller.
Method	`setModel`	Replace the persisted SQL model definition.
Method	`sql_quote__`	Quote a value according to the declared type of a table column.
Method	`substitute_params`	Substitute positional parameter markers with quoted SQL literals.
Class Variable	`__ac_permissions__`	Undocumented
Class Variable	`__administratorPermissions__`	Undocumented
Class Variable	`__authorPermissions__`	Undocumented
Class Variable	`manage_configuration`	Undocumented
Class Variable	`manage_configuration_table`	Undocumented
Class Variable	`manage_main`	Undocumented
Class Variable	`manage_options`	Undocumented
Class Variable	`manage_properties`	Undocumented
Class Variable	`manage_zmi_details_form`	Undocumented
Class Variable	`manage_zmi_details_grid`	Undocumented
Class Variable	`manage_zmi_input_form`	Undocumented
Class Variable	`manage_zmi_lazy_select_form`	Undocumented
Class Variable	`meta_id`	Undocumented
Class Variable	`meta_type`	Undocumented
Class Variable	`security`	Undocumented
Class Variable	`valid_types`	Undocumented
Instance Variable	`charset`	Undocumented
Instance Variable	`connection_id`	Undocumented
Instance Variable	`model`	Undocumented
Instance Variable	`model_xml`	Undocumented
Instance Variable	`table_filter`	Undocumented
Method	`_delete_blob`	Delete the file referenced by a blob column and return its replacement value.
Method	`_get_blob`	Load a blob from the filesystem and return it as a ZMS file object.
Method	`_set_blob`	Store blob data on the server filesystem and return the stored filename.

def ajaxGetAutocompleteColumns(self, tableName, fmt=None, REQUEST=None): (source) ¶

Return matching entity column names for autocomplete widgets.

Parameters
tableName:str	Entity name.
fmt:str	Optional output format, e.g. `'json'`.
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Current request containing filter values.
Returns
str	Plain-text or JSON encoded column list.

def ajaxGetObjOptions(self, REQUEST): (source) ¶

Return autocomplete option labels for a configured entity column.

Parameters
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Current request containing entity, column, and filter values.
Returns
str	Plain-text or JSON encoded option list.

def assemble_query_result(self, res, encoding=None): (source) ¶

Normalize a database query result into ZMS column and record structures.

Parameters
res:object	Raw adapter result.
encoding:str	Optional text encoding applied to byte columns.
Returns
dict	Dictionary with `columns` and `records` lists.

def assembleFilter(self, l): (source) ¶

Assemble the SQL filter expression for a where clause.

Parameters
l:list	Filter descriptors for columns and operators.
Returns
str	SQL expression joined with `AND`.

def commit(self): (source) ¶

Commit the current database transaction.

Makes all changes made since the previous commit or rollback permanent and releases the database locks currently held by the connection object.

def delete_blob(self, auth_user, tablename, id, rowid, REQUEST=None, RESPONSE=None): (source) ¶

Delete a blob after authorizing the remote caller.

Parameters
auth_user:str	User id to authorize.
tablename:str	Entity name.
id:str	Blob column id.
rowid:object	Primary-key value of the affected row.
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Optional request context.
RESPONSE:ZPublisher.HTTPResponse.HTTPResponse	Optional response context.
Returns
object	Replacement value returned by `_delete_blob`.

def execute(self, sql, params=(), max_rows=0, encoding=None): (source) ¶

Execute an SQL statement using the configured database adapter.

Supports parameter markers of the Python DB API.

Parameters
sql:str	SQL statement.
params:tuple	Values for the parameter markers.
max_rows:int	Maximum number of rows to return, or `0` for unlimited.
encoding:str	Optional text encoding applied to byte columns.
Returns
object	Raw or normalized query result depending on the adapter.

def executeQuery(self, sql): (source) ¶

Execute an SQL modifying statement and return the affected row count.

Parameters
sql:str	SQL modification statement.
Returns
int	Number of affected rows.

def filteredChildNodes(self, REQUEST={}, meta_types=None): (source) ¶

Return an empty list because SQL database objects do not expose child nodes.

Parameters
REQUEST:dict	Optional request context.
meta_types:object	Ignored meta-type selector.
Returns
list	Always an empty list.

def get_blob(self, tablename, id, rowid, REQUEST=None, RESPONSE=None): (source) ¶

Return the raw data payload of a stored blob.

Parameters
tablename:str	Entity name.
id:str	Blob column id.
rowid:object	Primary-key value of the affected row.
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Optional request context.
RESPONSE:ZPublisher.HTTPResponse.HTTPResponse	Optional response used to set headers.
Returns
bytes	Raw blob data.

def getDA(self): (source) ¶

Return the configured database adapter object.

Returns
object	Database adapter referenced by `connection_id`, or `None`.

def getEntities(self): (source) ¶

Retrieve and process a comprehensive list of entity descriptors from the database.

This method performs the following operations:

Attempts to return a cached result from the HTTP request buffer
Fetches entities from custom connection-specific methods (if defined)
Falls back to SQLAlchemy-based entity retrieval if available
Builds entities from database table browsers with column metadata
Merges custom entities and properties from the data model configuration
Applies sorting and default values to all entities and columns
Caches and returns the final entity list

The method handles multiple database connection types (SQLite, Oracle, etc.) and parses column type information from database descriptions. It supports custom entity definitions through model configuration files and merges database-introspected data with user-defined model properties.

Returns

list of dict

List of entity descriptors, each containing:

id: Entity identifier
type: Entity type (e.g., 'table')
label: Human-readable entity name
sort_id: Uppercase label for sorting
columns: List of column descriptors with type, size, and custom properties
interface: Optional interface specification
not_found: Flag indicating if entity exists only in model (custom entity)

def getEntitiesSQLAlchemyDA(self): (source) ¶

Return a list of entity descriptors by reflecting the database schema via SQLAlchemy.

Returns
list	List of entity descriptors.

def getEntity(self, tableName): (source) ¶

Return an entity descriptor with metadata and columns for a given table.

Parameters
tableName:str	Entity name.
Returns
dict	Entity descriptor with metadata and columns.

def getEntityColumn(self, tableName, columnName, row=None): (source) ¶

Return a column descriptor with metadata and value for a given table column.

Parameters
tableName:str	Entity name.
columnName:str	Column name.
row:dict	Optional row data for value extraction.
Returns
dict	Column descriptor with metadata and value.

def getEntityDetailsGridContext(self, request): (source) ¶

Build the full rendering context for manage_zmi_details_grid.

This moves detail-grid preparation logic from TAL into Python so the template focuses on markup only.

Parameters
request:ZPublisher.HTTPRequest.HTTPRequest	Current request.
Returns
dict	Context dictionary used by `zmi_details_grid.zpt`.

def getEntityPK(self, tableName): (source) ¶

Return the primary-key column name of an entity.

Parameters
tableName:str	Entity name.
Returns
str	Primary-key column identifier.

def getEntityRecordHandler(self, tableName, stereotypes=None, colNames=None): (source) ¶

Build a helper that post-processes rows for export and display.

Parameters
tableName:str	Entity name.
stereotypes:list	Column stereotypes to keep or resolve.
colNames:list	Optional whitelist of exported column names.
Returns
object	Record handler instance.

def getEntityTarget(self, sourceTableName, targetTableName): (source) ¶

Resolve the effective target entity for relation-like stereotypes.

If the referenced entity is an intersection table, return the opposite foreign-key target. Otherwise return the referenced entity itself.

Parameters
sourceTableName:str	Source entity name.
targetTableName:str	Referenced entity or intersection table name.
Returns
dict	Resolved target entity descriptor.

def getFk(self, tablename, id, name, value, createIfNotExists=True): (source) ¶

Resolve or create a referenced row for a foreign-key relation.

Parameters
tablename:str	Name of the SQL table.
id:str	Foreign-key column id.
name:str	Display column used for lookup.
value:object	Submitted foreign-key value.
createIfNotExists:bool	Create a new referenced row when no match exists.
Returns
int	Referenced row id.

def getModel(self): (source) ¶

Return the parsed SQL model and refresh the cached copy when needed.

Returns
list	Parsed SQL model definition.

def getModelContainer(self): (source) ¶

Return the Zope object that stores the serialized SQL model definition.

Returns
object	DTML method containing the SQL model XML.

def manage_changeConfiguration(self, lang, btn='', key='all', REQUEST=None, RESPONSE=None): (source) ¶

Update SQL entity configuration and field metadata from the management UI.

Parameters
lang:str	Active language.
btn:str	Submitted action button id.
key:str	Configuration subsection to update.
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Current request containing submitted configuration values.
RESPONSE:ZPublisher.HTTPResponse.HTTPResponse	Response used for redirect handling.
Returns
object	Redirect response back to the configuration form.

def manage_changeProperties(self, lang, REQUEST=None, RESPONSE=None): (source) ¶

Persist SQL database properties and the serialized model definition.

Parameters
lang:str	Active language.
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Current request containing form values.
RESPONSE:ZPublisher.HTTPResponse.HTTPResponse	Response used for redirect handling.
Returns
object	Redirect response to the properties form.

def query(self, sql, max_rows=0, encoding=None): (source) ¶

Execute a select statement and normalize the result for ZMS forms.

Parameters
sql:str	Select statement.
max_rows:int	Maximum number of rows to return, or `0` for unlimited.
encoding:str	Optional text encoding applied to byte columns.
Returns
dict	Dictionary with `columns` and `records` lists.

def record_encode__(self, cols, record, encoding='utf-8'): (source) ¶

Decode byte values in a database row according to the given encoding.

Parameters
cols:list	Column descriptors for the row.
record:dict	Raw database record.
encoding:str	Text encoding used for byte strings.
Returns
dict	Normalized row dictionary.

def recordSet_Delete(self, tablename, rowid): (source) ¶

Delete a row from the selected record-set table.

Parameters
tablename:str	Name of the SQL table.
rowid:object	Primary-key value of the row to delete.

def recordSet_Filter(self, REQUEST): (source) ¶

Apply persisted and submitted filters to the current record-set query.

Parameters
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Triggering request.

def recordSet_Init(self, REQUEST): (source) ¶

Initialize record-set request state for listing and filtering.

Parameters
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Triggering request.

def recordSet_Insert(self, tablename, values={}, update_intersections=False): (source) ¶

Insert a new row into the selected record-set table.

Parameters
tablename:str	Name of the SQL table.
values:dict	Column values to insert.
update_intersections:bool	Update related intersection tables afterwards.
Returns
object	Primary-key value of the inserted row.

def recordSet_Select(self, tablename, select=None, where=None): (source) ¶

Build the base select statement for a record-set query.

Parameters
tablename:str	Entity name.
select:str	Optional explicit select clause.
where:str	Optional initial where clause.
Returns
str	SQL select statement.

def recordSet_Sort(self, REQUEST): (source) ¶

Apply order-by clauses to the current record-set query.

Parameters
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Triggering request.

def recordSet_Update(self, tablename, rowid, values={}, old_values={}, update_intersections=False): (source) ¶

Update an existing row in the selected record-set table.

Parameters
tablename:str	Name of the SQL table.
rowid:object	Primary-key value of the row to update.
values:dict	New column values.
old_values:dict	Optional previous values used for change detection.
update_intersections:bool	Update related intersection tables afterwards.
Returns
object	Primary-key value of the updated row.

def recordSet_UpdateIntersections(self, tablename, rowid, values={}): (source) ¶

Synchronize intersection tables for multi-select style columns.

Parameters
tablename:str	Name of the SQL table.
rowid:object	Primary-key value of the parent row.
values:dict	Submitted column values.

def rollback(self): (source) ¶

Roll back the current database transaction.

Undo all changes made in the current transaction and release the database locks currently held by the connection object.

def set_blob(self, auth_user, tablename, id, rowid=None, xml=None, REQUEST=None, RESPONSE=None): (source) ¶

Store a blob payload after authorizing the remote caller.

Parameters
auth_user:str	User id to authorize.
tablename:str	Entity name.
id:str	Blob column id.
rowid:object	Optional primary-key value of the affected row.
xml:str	Optional XML payload containing the uploaded file.
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Optional request context.
RESPONSE:ZPublisher.HTTPResponse.HTTPResponse	Optional response context.
Returns
str	Stored filename.

def setModel(self, newModel): (source) ¶

Replace the persisted SQL model definition.

Parameters
newModel:str	Serialized SQL model payload.

def sql_quote__(self, tablename, columnname, v): (source) ¶

Quote a value according to the declared type of a table column.

Parameters
tablename:str	Entity name.
columnname:str	Column identifier.
v:object	Raw value to quote.
Returns
str	SQL literal suitable for inline substitution.

def substitute_params(self, sql, params=()): (source) ¶

Substitute positional parameter markers with quoted SQL literals.

Parameters
sql:str	SQL statement containing `?` placeholders.
params:tuple	Values for the placeholders.
Returns
str	SQL statement with substituted literals.

__ac_permissions__ = (source) ¶

Undocumented

__administratorPermissions__: tuple[str, ...] = (source) ¶

Undocumented

__authorPermissions__: tuple[str, ...] = (source) ¶

Undocumented

manage_configuration = (source) ¶

Undocumented

manage_configuration_table = (source) ¶

Undocumented

manage_main = (source) ¶

Undocumented

manage_options: tuple = (source) ¶

Undocumented

manage_properties = (source) ¶

Undocumented

manage_zmi_details_form = (source) ¶

Undocumented

manage_zmi_details_grid = (source) ¶

Undocumented

manage_zmi_input_form = (source) ¶

Undocumented

manage_zmi_lazy_select_form = (source) ¶

Undocumented

meta_id: str = (source) ¶

Undocumented

meta_type: str = (source) ¶

Undocumented

security = (source) ¶

Undocumented

valid_types: dict = (source) ¶

Undocumented

charset = (source) ¶

Undocumented

connection_id = (source) ¶

Undocumented

model = (source) ¶

Undocumented

model_xml = (source) ¶

Undocumented

table_filter = (source) ¶

Undocumented

def _delete_blob(self, tablename, id, rowid): (source) ¶

Delete the file referenced by a blob column and return its replacement value.

Parameters
tablename:str	Entity name.
id:str	Blob column id.
rowid:object	Primary-key value of the affected row.
Returns
object	Replacement column value after deletion.

def _get_blob(self, tablename, id, rowid, cache='public, max-age=3600', REQUEST=None, RESPONSE=None): (source) ¶

Load a blob from the filesystem and return it as a ZMS file object.

Parameters
tablename:str	Entity name.
id:str	Blob column id.
rowid:object	Primary-key value of the affected row.
cache:str	Cache-Control header value for responses.
REQUEST:ZPublisher.HTTPRequest.HTTPRequest	Optional request context.
RESPONSE:ZPublisher.HTTPResponse.HTTPResponse	Optional response used to set headers.
Returns
object	Image or file wrapper, or `None` when no blob exists.

def _set_blob(self, tablename, id, rowid=None, file=None, xml=None): (source) ¶

Store blob data on the server filesystem and return the stored filename.

Parameters
tablename:str	Entity name.
id:str	Blob column id.
rowid:object	Optional primary-key value of the affected row.
file:object	Uploaded file object.
xml:str	Optional XML payload used to reconstruct the uploaded file.
Returns
str	Stored filename.