Database What's There
Beginning with Ilios 3.0.0, the database has become secondary in terms of external connectivity to our API. If you draw from Ilios to do reporting (like we all do), we strongly encourage you to pull your data from the API rather than from direct calls to the database. The database moving forward will be seeing frequent enhancements and modifications which could be challenging for ODBC / direct SQL connectivity and data integrity. For further details on the API, please refer to the livedocs here (https://ilios3-demo.ucsf.edu/api/doc), or contact us at support@iliosproject.org.
The latest data dictionary, including deprecations and modifications to the schema, is available here. While we will be keeping this document as fresh as we are able, as noted above we encourage you to move toward drawing data directly -- more robustly, more stably, and with better performance --- from the Ilios API.
The Ilios 3 database no longer contains any discrete stored procedures, functions or triggers. All tables are now keyed and constraints have been fully implemented. Table structures, naming conventions and models remain effectively unchanged.
Logical deletion for curriculum entities is no longer supported in the schema. Delete actions are now captured properly in the internal audit tables.
Please see the current Data Dictionary for further details.
- Collation:
utf_general_ci - Database engines used: InnoDB, MyISAM
- Number of Tables: 100
- Number of Functions: 15
- Number of Procedures: 11
- one sole table
ci_sessionsfor storing user sessions - uses MyISAM engine
- use InnoDB engine
- plethora of functions/procedures, which implement fairly advanced business processes using temporary tables and temporary triggers
- some usage of foreign key constraints, but not completely implemented throughout
- JOIN tables use
TABLE1_x_TABLE2naming convention
Medical Subject Headings (MeSH) database components
- consists of 12 tables and 6 procedures
- use MyISAM engine
- prefixed with
mesh_
ERD available here
Final 2.4.X Data Dictionary available [here] (https://www.dropbox.com/s/ade7p3r6wwykmq8/2.4.7.DataDictionary.pdf?dl=0)