Open source cms for mobile and desktop websites


Data Pool 2 SQL Base Module

class nive.utils.dataPool2.base.Base(connection=None, structure=None, root='', useTrashcan=False, useBackups=False, codePage='utf-8', dbCodePage='utf-8', connParam=None, debug=0, log='sql.log', **kw)

Data Pool 2 SQL Base implementation

Manage typed data units consisting of meta layer, data layer and files. Meta layer is the same for all units, data layer is based on types and files are stored by key. Meta and data is mapped to database, files are stored in filesystem.

Pool structure is required and must contain the used database fields as list (table names and column names).

Entries are referenced by as unique ID.

All basic SQL is handled internally.

root: string. filesystem root path codePage: string. the codepage used on output dbCodePage: string. database codepage structure: PoolStructure object defining the database structure and field types. version: string. the default version useBackups: bool. store backup versions of files on replace useTrashcan: bool. moves files to trashcan rather than delete physically debug: number. turn debugging on. 0 = off, 1=on (no traceback), 2...20=on (traceback lines) log: string. log file path for debugging

A list consisting of meta layer fields which are used in preloading

“meta_flds”: ( (“pool_dataref”, “integer not null default 0”), (“pool_datatbl”, “ENUM() not null”), )


Returns the database connection and attempts a reconnect or verifies the connection depending on configuration settings.


Returns the previously used connection without verifying the connection. The returned connection may be none or not connected. Can be used in cases if the connection will only be used to finish previous actions e.g. Commit(). Lookup is faster and will not waste unnecessary resources.


Start a database transaction, if supported


Rollback the changes made to the database, if supported


Commit the changes made to the database, if supported


SQL statement format string to be used as placeholder for values. e.g. mysql=%s, sqlite=?

FmtSQLSelect(flds, parameter=None, dataTable=u'', start=0, max=0, **kw)

Create a select statement based on pool_structure and given parameters.

Aggregate functions can be included in flds but must be marked with the prefix - e.g. -count(*)

Tables in the generated statement get an alias prefix: meta__ for meta table data__ for data table

flds: select fields parameter: where clause parameter dataTable: add join statement for the table start: start number of record to be returned max: maximum nubers of records in result

**kw: singleTable: 1/0 skip join. only use datatable for select version: version key. select content from specific version operators: operators used for fields in where clause: =,LIKE,<,>,... jointype: type of join. default = INNER logicalOperator = link between conditions. default = and. and, or, not condition = custom condition statement. default = empty join = custom join statement. default = empty groupby: add GROUP BY statement sort: result sort order ascending: result sort order ascending or descending

GetFulltextSQL(searchPhrase, flds, parameter, dataTable=u'', **kw)

generate sql statement for fulltext query

searchPhrase: text to be searched

For further options see -> FmtSQLSelect

Execute(sql, values=None, cursor=None)

Execute a query on the database. Returns the dbapi cursor. Use cursor.fetchall() or cursor.fetchone() to retrieve results. The cursor should be closed after usage.

Query(sql, values=None, cursor=None, getResult=True)

execute a query on the database. non unicode texts are converted according to codepage settings.

SelectFields(table, fields, idValues, cursor=None, idColumn=None)

Select row with multiple fields in the table. Set idColumn to the column name of the unique id column

table: table name fields: list of field names to be returned idValues: list of matching id values to be returned idColumn: default ‘id’. id column name

returns matching records

InsertFields(table, data, cursor=None, idColumn=None)

Insert row with multiple fields in the table. Codepage and dates are converted automatically Set idColumn to the column name of the auto increment unique id column to get it returned.

returns the converted data

UpdateFields(table, id, data, cursor=None, idColumn=u'id', autoinsert=False)

Updates multiple fields in the table. If autoinsert is True the a new record is automatically inserted if it does not exist. Also the function returns the converted data and id (non zero if a new record was inserted)

If autoinsert is False the function returns the converted data.

DeleteRecords(table, parameter, cursor=None)

Delete records referenced by parameters


Used for text read from the database. Converts the text to unicode based on self.dbCodePage.


Used for text stored in database. Convert the text to unicode based on self.codePage.

ConvertRecToDict(rec, flds)

Convert a database record tuple to dictionary based on flds list

CreateEntry(pool_datatbl, id=0, user='', **kw)

Create new entry. Requires pool_datatbl as parameter

GetEntry(id, **kw)

Get entry from db by ID

DeleteEntry(id, version=None)

Delete the entry and files


Query database if id exists

GetBatch(ids, **kw)

Get all entries as objects at once. returns a list. supports preload: all, skip, meta

kw: - meta: list of meta pool_datatbl for faster lookup

GetContainedIDs(base=0, sort=u'title', parameter=u'')

Search subtree and returns a list of all contained ids. id needs to be first field, pool_unitref second

GetTree(flds=None, sort=u'title', base=0, parameter=u'')

Loads a subtree as dictionary. The returned dictionary has the following format: {“id”: 123, “items”: [{“id”: 124, “items”: [], “data”: “q”, ....}, ...], “data”: “q”, ....}

id needs to be first field, pool_unitref second


Returns id references of parents for the given id. Maximum 10 parents


Returns titles of parents for the given id. maximum 10 parents

InitFileStorage(root, connectionParam)

Set the local root path for files

GetGroups(id, userid=None, group=None)

Get local group assignment for userid.

id can be a single value or a tuple. If its a tuple the groups for all matching ids are returned.

returns a group assignment list [[“userid”, “groupid”, “id”], ...]

AddGroup(id, userid, group)

Add a local group assignment for userid.

RemoveGroups(id, userid=None, group=None)

Remove a local group assignment for userid or all for the id/ref.


Get all local group assignment for userid.

returns a group assignment list [[“userid”, “groupid”, “id”], ...]


Returns the total number of entries in the pool

class nive.utils.dataPool2.base.Entry(dataPool, id, **kw)

Entry object of data pool


Check if the entry physically exists in the database


Check if the id is valid

Commit(user='', dbCommit=True)

Commit temporary changes (meta, data, files) to database


Undo changes in database

GetMetaField(fld, fromDB=False)

Read a single field from meta layer if fromDB data is loaded from db, not cache

GetDataField(fld, fromDB=False)

Read a single field from data layer if fromDB data is loaded from db, not cache


Read meta layer and return as dictionary


Read data layer and return as dictionary

SetMetaField(fld, data, cache=True)

Update single field to meta layer. Commits changes immediately to database without calling touch.

SetDataField(fld, data, cache=True)

Update single field to data layer Commits changes immediately to database without calling touch.


Tets change date and changed by to now

CommitFile(key, file, cursor=None)

Create a copy of the entry with a new id.

Load(option=u'all', reload=False)

Loads different sets of fields in single sql statement

options: skip all: all meta and data fields stdmeta: fields configured in stdMeta list stdmetadata: fields configured in stdMeta list and all data fields meta: all meta fields


empty cache


Update or create fulltext for entry


read fulltext from entry


Delete fulltext for entry

exception nive.utils.dataPool2.base.NotFound

raised if entry not found

exception nive.utils.dataPool2.base.FileNotFound

raised if physical file not found

class nive.utils.dataPool2.files.File(filekey='', filename='', file=None, size=0, path='', extension='', fileid=0, uid='', tempfile=False, filedict=None, mtime=None, fileentry=None)

File mapping object. The file attribute can be stored as data or readable stream object. This file class is used to map files stored and read from the storage system.

Two modes are supported: - BlobFile: Files stored as Blob files in filesystem - TempFile: Temp files to be stored

Pass a dictionry to set all attributes as filemeta


Set temp file and load file values from file path


check if the file physically exists


This functions writes the file to the pool directory. If the file is not marked as tempfile, nothing is written.

Files are processed in the following order: - a temp path is created - the file is written to this path - the original file is renamed to be deleted on success and stored as file.deleteOnSuccess - the tempfile is renamed to the original path - the original file can be removed by calling Cleanup()

fileentry is the database entry the file is stored for.

class nive.utils.dataPool2.files.FileManager

Data Pool File Manager class for SQL Database with version support.

Files are stored in filesystem, aditional information in database table. Table “pool_files” (“id”, “fileid”, “filekey”, “path”, “filename”, “size”, “extension”, “version”). Field path stores internal path to the file in filesystem without root.

Preperty descriptions are dictionaries with key:value pairs. Property values: id = unit id to store file for (id is required) version = the version of the file filekey = custom value

key: id_filekey_version

directory structure: root/id[-4:-2]00/id_filekey_version.ext


Returns the required file class for File object instantiation

InitFileStorage(root, connectionParam)

Set the local root path for files


search for filename

SearchFiles(parameter, sort=u'filename', start=0, max=100, ascending=1, **kw)

search files

DeleteFiles(id, cursor=None, version=None)

Delete the file with the prop description

class nive.utils.dataPool2.files.FileEntry

Data pool entry extension to handle physical files.

This class provides all functions to store and read files from the backend. Each file is stored with a key (a field name like and other data field) and loaded and stored using the File container class. The file entry has no restrictions on the number of files.

Files(parameter=None, cursor=None, loadFileData=False)

List all files matching the parameters. Returns a dictionary.


return all existing file keys as list

GetFile(key, fileid=None, loadFileData=False)

return the meta file informations from db or None if no matching record found

CommitFiles(files, cursor=None)

Commit multiple files in a row

CommitFile(key, file, cursor=None)

Store the file under key. File can either be a path, dictionary with file informations or a File object.


Cleanup tempfiles after succesful writes


Copy the file If filekey = “” all files are copied


Delete the file with the prop description

RenameFile(key, filename)

Changes the filename field of the file key.

class nive.utils.dataPool2.structure.Wrapper(entry, content=None)

Wrappers are mapping objects for data, files and meta. Content can be accessed as dictionary field. Changes are stored temporarily in memory.


Reset contents, temp data and entry obj


Returns a copy of current content

class nive.utils.dataPool2.structure.MetaWrapper(entry, content=None)

wrapper class for meta content

class nive.utils.dataPool2.structure.DataWrapper(entry, content=None)

wrapper class for data content

class nive.utils.dataPool2.structure.FileWrapper(entry, content=None)

wrapperclass for files. contains only filemta and returns file streams on read. update and __setitem__ take File object with o.file and o.filename attr as parameter entry = {“filename”: “”, “path”: <absolute path for temp files>, “file”: <file stream>}

class nive.utils.dataPool2.structure.PoolStructure(structure=None, fieldtypes=None, stdMeta=None, codepage='utf-8', **kw)

Data Pool 2 Structure handling. Defines a table field mapping. If field types are available serializing and deserializing is performed on database reads and writes.

structure =
     meta:   (field1, field2, ...),
     type1_table: (field5, field6, ...),
     type2_table: (field8, field9, ...),

fieldtypes = 
     meta: {field1: string, field2: number},
     type1_table: {field5: DateTime, field6: text},
     type2_table: {field8: DateTime, field9: text},
stdMeta = (field1, field2)

Deserialization datatypes

string, htext, text, list, code, radio, email, password, url -> unicode
number, float, unit -> number 
bool -> 0/1
file -> bytes
timestamp -> float
date, datetime -> datetime
multilist, checkbox, urllist -> unicode tuple
unitlist -> number tuple
json -> python type list, tuple or dict

Serialization datatypes

string, htext, text, list, code, radio, email, password, url -> unicode
number, float, unit -> number 
bool -> 0/1
file -> bytes
timestamp -> float
date, datetime -> datetime
multilist, checkbox, urllist -> json
unitlist -> json
json -> json

If fieldtype (fieldtypes) information is not given json data is stored with _json_ prefix.

© 2013 Nive GmbH