public function after_save(Table $table, Record $new_record)
{
// Don't save changes to the changes tables.
if (in_array($table->getName(), self::tableNames())) {
return false;
}
$changesetsTable = $this->db->getTable('changesets', false);
if (!$changesetsTable || !$changesetsTable->getRecord(self::$currentChangesetId)) {
throw new \Exception("Failed to open changeset #" . self::$currentChangesetId);
}
// Save a change for each changed column.
foreach ($table->getColumns() as $column) {
$col_name = $column->isForeignKey() ? $column->getName() . Record::FKTITLE : $column->getName();
$old_val = is_callable(array($this->oldRecord, $col_name)) ? $this->oldRecord->{$col_name}() : null;
$new_val = $new_record->{$col_name}();
if ($new_val == $old_val) {
// Ignore unchanged columns.
continue;
}
$sql = "INSERT INTO changes SET " . " `changeset` = :changeset, " . " `change_type` = 'field', " . " `table_name` = :table_name, " . " `column_name` = :column_name, " . " `record_ident` = :record_ident, " . " `old_value` = :old_value, " . " `new_value` = :new_value ";
$data = array('changeset' => self::$currentChangesetId, 'table_name' => $table->getName(), 'column_name' => $column->getName(), 'record_ident' => $new_record->getPrimaryKey(), 'old_value' => $old_val, 'new_value' => $new_val);
// Save the change record.
$this->db->query($sql, $data);
}
// Close the changeset if required.
if (!self::$keepChangesetOpen) {
$this->closeChangeset();
}
}
/**
* Get a Table object.
*
* @param string $tableName
* @return \Tabulate\DB\Table
* @throws \Exception
*/
protected function getTable($tableName)
{
$table = $this->db->getTable($tableName);
if ($table === false) {
http_response_code(404);
throw new \Exception("Table '" . $tableName . "' not found.");
}
return $table;
}
public function tearDown()
{
// Close any still-open changeset.
$changeTracker = new \Tabulate\DB\ChangeTracker($this->db);
$changeTracker->closeChangeset();
// Remove all tables.
$this->db->query('SET FOREIGN_KEY_CHECKS = 0');
foreach ($this->db->getTableNames(false) as $tbl) {
$this->db->query("DROP TABLE IF EXISTS `{$tbl}`");
}
$this->db->query('SET FOREIGN_KEY_CHECKS = 1');
parent::tearDown();
}
protected function installData(\Tabulate\DB\Database $db)
{
$this->write("Confirming existance of administrative user, group, and grant");
// Can't log changes without a user (admin, in this case). So we create a user manually.
$pwd = password_hash('admin', PASSWORD_DEFAULT);
$adminUserData = ['id' => Users::ADMIN, 'name' => 'Admin', 'email' => Config::siteEmail(), 'password' => $pwd];
$adminSql = "INSERT IGNORE INTO `users` SET `id`=:id, `name`=:name, `email`=:email, `password`=:password";
$db->query($adminSql, $adminUserData);
// Then we want to create a second user (anon), but this time recording changes. The change-tracker needs to
// know about permissions, so before creating the 2nd user that we need to grant permission to admin.
// Permissions are granted to groups, not users, so we put admin in an admin group first (manually).
$params2 = ['id' => Groups::ADMINISTRATORS, 'name' => 'Administrators'];
$db->query("INSERT IGNORE INTO `groups` SET `id`=:id, `name`=:name", $params2);
$params3 = ['user' => Users::ADMIN, 'group' => Groups::ADMINISTRATORS];
$db->query("INSERT IGNORE INTO `group_members` SET `user`=:user, `group`=:group", $params3);
// Now we can grant everything (on everything) to the admin group.
$db->query("INSERT IGNORE INTO `grants` SET `group`=:group", ['group' => Groups::ADMINISTRATORS]);
// And finally 'reset' the DB so it knows about the above new records.
$db->reset();
// Start tracking changes now that there's a user to attribute it to.
$db->setCurrentUser(Users::ADMIN);
$changeTracker = new \Tabulate\DB\ChangeTracker($db);
$changeTracker->openChangeset('Installation', true);
// Create remaining default users and groups.
if (!$db->getTable('users')->getRecord(Users::ANON)) {
$this->write("Inserting user 'Anonymous'");
$db->getTable('users')->saveRecord(['id' => Users::ANON, 'name' => 'Anonymous']);
}
if (!$db->getTable('groups', false)->getRecord(Groups::GENERAL_PUBLIC)) {
$this->write("Inserting group 'General Public'");
$db->getTable('groups', false)->saveRecord(['id' => Groups::GENERAL_PUBLIC, 'name' => 'General Public']);
}
// Add Anon user to the General Public group.
$groupMembers = $db->getTable('group_members', false);
$groupMembers->addFilter('user', '=', Users::ANON);
$groupMembers->addFilter('group', '=', Groups::GENERAL_PUBLIC);
if ($groupMembers->getRecordCount() === 0) {
$this->write("Adding user 'Anonymous' to group 'General Public'");
$groupMembers->saveRecord(['group' => Groups::GENERAL_PUBLIC, 'user' => Users::ANON]);
}
// Add first report (to list reports).
if (0 == $db->query("SELECT COUNT(*) FROM `" . Reports::reportsTableName() . "`")->fetchColumn()) {
// Create the default report, to list all reports.
$templateString = "<dl>\n" . "{% for report in reports %}\n" . " <dt><a href='{{baseurl}}/reports/{{report.id}}'>{{report.title}}</a></dt>\n" . " <dd>{{report.description}}</dd>\n" . "{% endfor %}\n" . "</dl>";
$sql1 = "INSERT INTO `" . Reports::reportsTableName() . "` SET" . " id = " . Reports::DEFAULT_REPORT_ID . ", " . " title = 'Reports', " . " description = 'List of all Reports.'," . " template = :template;";
$db->query($sql1, ['template' => $templateString]);
// And the query for the above report.
$query = "SELECT * FROM " . Reports::reportsTableName();
$sql2 = "INSERT INTO `" . Reports::reportSourcesTableName() . "` SET " . " report = " . Reports::DEFAULT_REPORT_ID . "," . " name = 'reports'," . " query = :query;";
$db->query($sql2, ['query' => $query]);
}
// Finish up.
$changeTracker->closeChangeset();
}
/**
* Get a list of a table's records' IDs and titles, filtered by
* `$_GET['term']`, for foreign-key fields. Only used when there are more
* than N records in a foreign table (otherwise the options are presented in
* a select list).
* @param \WP_REST_Request $request The request, with a 'table_name' parameter.
* @return array
*/
public function foreign_key_values(\WP_REST_Request $request)
{
if (!isset($this->get['term'])) {
return array();
}
$db = new Database($this->wpdb);
$table = $db->getTable($request->get_param('table_name'));
if (!$table instanceof Table) {
return array();
}
// First get any exact matches.
$out = $this->foreign_key_values_build($table, '=', $this->get['term']);
// Then get any 'contains' matches.
$out += $this->foreign_key_values_build($table, 'like', '%' . $this->get['term'] . '%');
return $out;
}
protected function set_up($args)
{
$db = new Database($this->wpdb);
$this->table = $db->getTable($args['table']);
// Check that a point column exists.
$points = $this->table->getColumns('point');
if (empty($points)) {
// @TODO Show error.
return;
}
$point_col = array_shift($points);
$this->point_col_name = $point_col->getName();
// Apply filters.
$filter_param = isset($args['filter']) ? $args['filter'] : array();
$this->table->addFilters($filter_param);
$this->table->addFilter($this->point_col_name, 'not empty', '');
}
public function testBasics()
{
$db = new Database();
// Make sure the default records were created.
$db->setCurrentUser(Users::ADMIN);
$this->assertEquals(2, $db->getTable('users')->getRecordCount());
$this->assertEquals(2, $db->getTable('groups')->getRecordCount());
$this->assertEquals(2, $db->getTable('group_members')->getRecordCount());
// The anon user can't see anything.
$db->setCurrentUser(Users::ANON);
$this->assertEquals(Users::ANON, $db->getCurrentUser());
$this->assertEmpty($db->getTables());
// The admin user can see everything.
$db->setCurrentUser(Users::ADMIN);
$expectedTables = ['changes', 'changesets', 'grants', 'group_members', 'groups', 'sessions', 'test_table', 'test_types', 'users', 'report_sources', 'reports'];
$this->assertEquals($expectedTables, $db->getTableNames(), '', 0, 1, true, true);
}
public function save($args)
{
if (!isset($args['schema'])) {
$url = admin_url('admin.php?page=tabulate_schema');
wp_redirect($url);
}
$db = new Database($this->wpdb);
$schema = $db->getTable($args['schema']);
$new_name = $args['schema'];
if ($schema instanceof Table && !empty($args['new_name'])) {
$schema->rename($args['new_name']);
$new_name = $schema->getName();
}
$template = new Template('schema.html');
$template->addNotice('updated', 'Schema updated.');
$url = admin_url('admin.php?page=tabulate_schema&schema=' . $new_name);
wp_redirect($url);
}
/**
* Get a Template instance based on a given report's template string and
* populated with all of the report's source queries.
* @param int $report_id
* @return \Tabulate\Template
*/
public function get_template($report_id)
{
// Find the report.
$reports = $this->db->getTable(self::reportsTableName());
$report = $reports->getRecord($report_id);
if (!$report) {
throw new \Exception("Report {$report_id} not found.");
}
$template = new \Tabulate\Template(false, $report->template());
$template->title = $report->title();
$template->file_extension = $report->file_extension();
$template->mime_type = $report->mime_type();
// Populate with source data.
$sql = "SELECT * FROM `" . self::reportSourcesTableName() . "` WHERE report = " . $report_id;
$sources = $this->db->query($sql)->fetchAll();
foreach ($sources as $source) {
$data = $this->db->query($source->query)->fetchAll();
$template->{$source->name} = $data;
}
// Return the template.
return $template;
}
public function delete($args)
{
$db = new Database();
$table = $db->getTable($args['table']);
$recordIdent = isset($args['ident']) ? $args['ident'] : false;
if (!$recordIdent) {
$this->redirect($table->getUrl());
}
// Ask for confirmation.
if (!isset($_POST['confirm_deletion'])) {
$template = new \Tabulate\Template('record/delete.html');
$template->table = $table;
$template->record = $table->getRecord($recordIdent);
return $template->render();
}
// Delete the record.
try {
$this->wpdb->query('BEGIN');
$table->deleteRecord($recordIdent);
$this->wpdb->query('COMMIT');
} catch (\Exception $e) {
$template = $this->getTemplate($table);
$template->record = $table->getRecord($recordIdent);
$template->addNotice('error', $e->getMessage());
return $template->render();
}
wp_redirect($table->getUrl());
exit;
}
