3.4
3.3
A Database Access Object (DAO) is responsible for executing database queries to read and write information from the database.
PKP applications support MySQL and PostgreSQL.
Each DAO is paired with an Entity and converts data between the entity’s DataObject class and tables in the database.
The DAORegistry contains an instance of every DAO in the application.
$authorDao = DAORegistry::getDAO('AuthorDAO');
When a DAO has a different name in OJS and OMP, you can retrieve it through the Application.
// Get a `JournalDAO` or `PressDAO`
$contextDao = Application::get()->getContextDAO();
// Get a `ArticleGalleyDAO` or `PublicationFormatDAO`
$representationDao = Application::get()->getRepresentationDAO();
// Get a `SectionDAO` or `SeriesDAO`
$sectionDao = Application::get()->getSectionDAO();
Use the retrieve() method to get something from the database.
$reviewRoundDao = DAORegistry::getDAO('ReviewRoundDAO');
$result = $reviewRoundDao->retrieve(
'SELECT * FROM review_rounds WHERE review_round_id = ?',
[$reviewRoundId]
);
if ($result->RecordCount()) {
$tableRow = $result->GetRowAssoc(false);
}
DAOs have a _fromRow() method to convert a returned row into the DataObject class.
$reviewRoundDao = DAORegistry::getDAO('ReviewRoundDAO');
$result = $reviewRoundDao->retrieve(
'SELECT * FROM review_rounds WHERE review_round_id = ?',
[$reviewRoundId]
);
if ($result->RecordCount()) {
$reviewRound = $reviewRoundDao->_fromRow($result->GetRowAssoc(false));
}
Most DAOs have a helper method to get a DataObject by ID.
$reviewRound = DAORegistry::getDAO('ReviewRoundDAO')->getById($reviewRoundId);
Use the retrieveRange() method to get a set of rows from the database.
$reviewRoundDao = DAORegistry::getDAO('ReviewRoundDAO');
$result = $reviewRoundDao->retrieveRange(
'SELECT * FROM queries',
[],
new DBRange($count, $pageNumber)
);
$resultFactory = new DAOResultFactory($result, $reviewRoundDao, '_fromRow');
Iterate over a DAOResultFactory to access each result in the set.
while ($reviewRound = $resultFactory->next()) {
echo 'Review Round: ' . $reviewRound->getData('round');
}
Or convert the DAOResultFactory to an array of objects.
$reviewRounds = $resultFactory->toArray();
For complex queries, use the QueryBuilder.
Use the update() method to insert or update records.
// Close all queries for a particular submission
$queryDao = DAORegistry::getDAO('QueryDAO');
$queryDao->update(
'UPDATE queries
SET closed = 1
WHERE assoc_type = ?
AND assoc_id = ?',
[
ASSOC_TYPE_SUBMISSION,
$submissionId,
]
);
Most DAOs include helper methods to insert, update, or delete records when passed a DataObject.
$reviewRoundDao->insertObject($reviewRound);
$reviewRoundDao->updateObject($reviewRound);
$reviewRoundDao->deleteObject($reviewRound);
You can also delete records by an object’s ID.
$reviewRoundDao->deleteById((int) $reviewRoundId);
A query builder should be used to construct complex queries. Query builders extend Laravel’s query builder and provide an expressive API for fetching records of an Entity.
For example, the SubmissionQueryBuilder can be used to retrieve submissions based on several filter parameters.
$qb = new \APP\Services\QueryBuilders\SubmissionQueryBuilder;
$qb->filterByContext($contextId)
->assignedTo($userId)
->orderBy('title');
Once configured, use the query builder to generate the query string and parameter bindings to be passed to a DAO.
$qo = $qb->getQuery();
$submissionDao = DAORegistry::getDAO('SubmissionDAO');
$result = $submissionDao->retrieve($qo->toSql(), $qo->getBindings());
Use the getCount() method to get a count of matching rows.
$count = $qb->assignedTo($userId)->getCount();
Use the getIds() method to get an array of object ids.
$assignedIds = $qb->assignedTo($userId)->getIds();
In most cases, a QueryBuilder will help fulfil the matching EntityReadInterface methods of a Service class.
A QueryBuilder can also be used with Laravel’s query methods.
$qb = new \APP\Services\QueryBuilders\UserQueryBuilder();
$qb->filterByContext($contextId);
// Get all matching emails
$emails = $qb->getQuery()->pluck('u.email');
// Get the first matching result
$user = $qb->getQuery()->first();
// Get the last registered user
$user = $qb->getQuery()->max('u.date_registered');
If an entity is defined using a schema, its DAO should extend the SchemaDAO class. The SchemaDAO implements the insertObject(), updateObject(), deleteObject(), and _fromRow() methods based on the schema.
A DAO may implement additional methods to perform bulk updates or otherwise minimize intensive SQL queries. For example, the following will remove all DOIs from all issues.
DAORegistry::getDAO('IssueDAO')->deleteAllPubIds($contextId, 'doi');
A DAO method like this should be wrapped by a Service method that performs the action so that notifications can be sent, hooks can be called, and related tasks can be executed.
When deciding whether or not to write such a method, consider the performance benefits of your custom DAO method against the following downsides:
Most entities in the application are represented in the database by two tables. For example, the journals table contains a row for each journal.
mysql> describe journals;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| journal_id | bigint(20) | NO | PRI | NULL | auto_increment |
| path | varchar(32) | NO | UNI | NULL | |
| seq | double | NO | | 0 | |
| primary_locale | varchar(14) | NO | | NULL | |
| enabled | tinyint(4) | NO | | 1 | |
+----------------+-------------+------+-----+---------+----------------+
All additional data, including multilingual data, is stored in the journal_settings table.
mysql> describe journal_settings;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| journal_id | bigint(20) | NO | PRI | NULL | |
| locale | varchar(14) | NO | PRI | | |
| setting_name | varchar(255) | NO | PRI | NULL | |
| setting_value | text | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
For example, a journal’s name that appears in English and French Canadian is stored in two rows in the journal_settings table.
mysql> select * from journal_settings where setting_name="name";
+------------+--------+--------------+--------------------------------------+
| journal_id | locale | setting_name | setting_value |
+------------+--------+--------------+--------------------------------------+
| 1 | en_US | name | Journal of Public Knowledge |
| 1 | fr_CA | name | Journal de la connaissance du public |
+------------+--------+--------------+--------------------------------------+
Properties in an entity’s schema that are an array or object are serialized when they are stored in the settings table.
mysql> select * from journal_settings where setting_name="supportedLocales";
+------------+--------+------------------+------------------------+
| journal_id | locale | setting_name | setting_value |
+------------+--------+------------------+------------------------+
| 1 | | supportedLocales | a:1:{i:1;s:5:"en_US";} |
+------------+--------+------------------+------------------------+
Serialized data can not be efficiently searched, so it should never include data that you want to query against.
Learn more about the plugin system.