Database Structure

The SyntaxCMS backend is a balance between flexibility, efficiency, and accessibility. All instance-specific information is stored in the database: preferences, metadata, and content.

Preferences

The pxdb_prefs table stores preferences that are used by the core data access API (pxdb_*) classes. The pxdb_prefs table is very simple, and there is an API provided by the pxdb_prefs class that can be used to access and modify values in this table. You can also use this table for your own purposes -- e.g. to store and access configuration information for your site / web application.

Metadata

There are a number of tables that store SyntaxCMS metadata; the essential tables are: datatypes, typesfields, dbfields, relationships, views, and viewfields.

Data Objects Tables
datatypes Objects used by your web application (e.g. documents, directory members, tasks)
typesfields The fields that compose a datatype and their properties (e.g. multiple? unique? auto-generated?)
dbfields The fields that correspond to `records` columns and the corresponding field metatypes (e.g. txt, ltxt, data, pick lists, file upload, etc.)
relationships Describes the ways that datatypes can be related
views A view is a subset of a datatype's fields (e.g. for creating multi-page forms, a different view would have the fields needed for each page)
viewfields The actual fields and other properties contained in a view

See the Data Object page for more information about how datatypes are represented.

Relationship Tables
primaryrel The master lookup table for bi-directional relationships. (This is the default relationship table)
secondaryrel A secondary lookup table where objects are related to other objects with no relationship type specified.
noderel A heirarchical relationship table (parents and children), useful for categorization.
Privilege Tables
field_priv This is where object field privileges are stored. A given user may not have privileges to view certain fields in a datatype. (e.g. you may want only admins to be able to choose categories for a document)
priv This is where data privileges are stored. Similar to field_priv, you may wish to restrict access to certain records -- e.g. a document that only project managers should be able to read / download.

Content

All SyntaxCMS content is stored in the `records` table. What this means is that all types of data (documents, people, tasks, etc.) can be treated in a similar fashion -- and related to each other. Of course it makes it more difficult to browse through your data at the db table level: to do this you'll want to sort / filter by typeid (datatype).

This does, however, have very profound implications for your ability to filter data. Because all data is stored in a single table and uses a single lookup table (`primaryrel`) it is easy to isolate a slice of data on your site. Using the pxdb_search class, for example, you could easily create a filter that could be applied to *all* the content on your site. E.g. allowing users the ability to filter everything on the site by a specific project.

  1. // build the project filter piece
  2. $projF = &pxdb_search::filter(DT_PROJECT);
  3. $projF->add_value('name', 'Benchmarking'); // filter on value of 'Benchmarking' for project name
  4.  
  5. // now build a filter that can be applied to objects that are related to project
  6. $projrelF = &pxdb_search::filter(array(DT_PERSON, DT_DOCUMENT, DT_EVENT));
  7. $projrelF->add_related($projF);
  8.  
  9. // now you can filter any SQL statement. or use this filter in a pxdb_collection class
  10.  
  11. $sql = "SELECT * FROM records WHERE name like '%africa%'";
  12.  
  13. $rs = $db->Execute($projrelF->filter_sql($sql));
  14. // or: $q = mysql_query($projrelF->filter_sql($sql)) );
  15.  
  16. // -- OR --
  17.  
  18. // get proj-filtered documents:
  19. $data = &pxdb_collection(DT_DOCUMENT);
  20. $data->find($projrelF);
  21. while ($rec = &$data->fetch_record())
  22. {
  23.     print $rec->get_field('name');
  24. }