/**
* Sets a new title in the database;
*
* @param string|null $title set null to derive from PID
*/
public function setTitle($title)
{
if ($title === null) {
$title = noNS($this->pid);
}
// only one of these will succeed
$sql = "UPDATE OR IGNORE titles SET title = ? WHERE pid = ?";
$this->sqlite->query($sql, array($title, $this->pid));
$sql = "INSERT OR IGNORE INTO titles (title, pid) VALUES (?,?)";
$this->sqlite->query($sql, array($title, $this->pid));
$this->title = $title;
}
/**
* Completely remove the database and reinitialize it
*
* You do not want to call this except for testing!
*/
public function resetDB()
{
if (!$this->sqlite) {
return;
}
$file = $this->sqlite->getAdapter()->getDbFile();
if (!$file) {
return;
}
unlink($file);
clearstatcache(true, $file);
$this->init();
}
/**
* retrieve the data saved for the page from the database. Usually there is no need to call this function.
* Call @see SchemaData::getData instead.
*/
protected function getDataFromDB()
{
list($sql, $opt) = $this->buildGetDataSQL();
$res = $this->sqlite->query($sql, $opt);
$data = $this->sqlite->res2arr($res);
return $data;
}
/**
* Delete all data associated with this schema
*
* This is really all data ever! Be careful!
*/
public function delete()
{
if (!$this->id) {
throw new StructException('can not delete unsaved schema');
}
$this->sqlite->query('BEGIN TRANSACTION');
$sql = "DROP TABLE ?";
$this->sqlite->query($sql, 'data_' . $this->table);
$this->sqlite->query($sql, 'multi_' . $this->table);
$sql = "DELETE FROM schema_assignments WHERE tbl = ?";
$this->sqlite->query($sql, $this->table);
$sql = "DELETE FROM schema_assignments_patterns WHERE tbl = ?";
$this->sqlite->query($sql, $this->table);
$sql = "SELECT T.id\n FROM types T, schema_cols SC, schemas S \n WHERE T.id = SC.tid\n AND SC.sid = S.id\n AND S.tbl = ?";
$sql = "DELETE FROM types WHERE id IN ({$sql})";
$this->sqlite->query($sql, $this->table);
$sql = "SELECT id\n FROM schemas \n WHERE tbl = ?";
$sql = "DELETE FROM schema_cols WHERE sid IN ({$sql})";
$this->sqlite->query($sql, $this->table);
$sql = "DELETE FROM schemas WHERE tbl = ?";
$this->sqlite->query($sql, $this->table);
$this->sqlite->query('COMMIT TRANSACTION');
$this->sqlite->query('VACUUM');
// a deleted schema should not be used anymore, but let's make sure it's somewhat sane anyway
$this->id = 0;
$this->chksum = '';
$this->columns = array();
$this->maxsort = 0;
$this->ts = 0;
}
public function test_build_update()
{
// arrange
$initialdata = array();
$initialdata['new']['new1']['sort'] = 70;
$initialdata['new']['new1']['label'] = 'testcolumn';
$initialdata['new']['new1']['ismulti'] = 0;
$initialdata['new']['new1']['config'] = '{"prefix": "", "postfix": ""}';
$initialdata['new']['new1']['class'] = 'Text';
$initialdata['new']['new1']['isenabled'] = '1';
$testname = 'testTable';
$testname = Schema::cleanTableName($testname);
$builder = new SchemaBuilder($testname, $initialdata);
$result = $builder->build();
$this->assertSame($result, '1', 'Prerequiste setup in order to have basis which to change during act');
$updatedata = array();
$updatedata['id'] = "1";
$updatedata['cols']['1']['sort'] = 65;
$updatedata['cols']['1']['label'] = 'testColumn';
$updatedata['cols']['1']['ismulti'] = 1;
$updatedata['cols']['1']['config'] = '{"prefix": "pre", "postfix": "fix"}';
$updatedata['cols']['1']['class'] = 'Text';
$updatedata['cols']['1']['isenabled'] = '1';
// act
$builder = new SchemaBuilder($testname, $updatedata);
$result = $builder->build();
$res = $this->sqlite->query("SELECT * FROM types");
$actual_types = $this->sqlite->res2arr($res);
$this->sqlite->res_close($res);
$expected_types = array(array('id' => "1", 'class' => 'Text', 'ismulti' => "0", 'label' => "testcolumn", 'config' => '{"prefix": "", "postfix": ""}'), array('id' => "2", 'class' => 'Text', 'ismulti' => "1", 'label' => "testColumn", 'config' => '{"prefix": "pre", "postfix": "fix"}'));
// assert
$this->assertSame($result, '2');
$this->assertEquals($actual_types, $expected_types);
}
/**
* Add an additional column to the existing data table
*
* @param int $index the new column index to add
* @return bool
*/
protected function addDataTableColumn($index)
{
$tbl = 'data_' . $this->table;
$sql = " ALTER TABLE {$tbl} ADD COLUMN col{$index} DEFAULT ''";
if (!$this->sqlite->query($sql)) {
return false;
}
return true;
}
/**
* initializes the DB connection
*
* @return helper_plugin_sqlite|null
*/
public function getDBHelper()
{
if (!is_null($this->sqlite)) {
return $this->sqlite;
}
$this->sqlite = plugin_load('helper', 'sqlite');
if (!$this->sqlite) {
msg('The rating plugin requires the sqlite plugin', -1);
$this->sqlite = null;
return null;
}
$ok = $this->sqlite->init('rating', __DIR__ . '/db');
if (!$ok) {
msg('rating plugin sqlite initialization failed', -1);
$this->sqlite = null;
return null;
}
return $this->sqlite;
}
/**
* fetch all pages where the schema isn't assigned, yet and reevaluate the page assignments for those pages and assign when needed
*
* @param $table
*/
public function propagatePageAssignments($table)
{
$sql = 'SELECT pid FROM schema_assignments WHERE tbl != ? OR assigned != 1';
$res = $this->sqlite->query($sql, $table);
$pagerows = $this->sqlite->res2arr($res);
$this->sqlite->res_close($res);
foreach ($pagerows as $row) {
$tables = $this->getPageAssignments($row['pid'], true);
if (in_array($table, $tables)) {
$this->assignPageSchema($row['pid'], $table);
}
}
}
/**
* Execute this search and return the result
*
* The result is a two dimensional array of Value()s.
*
* This will always query for the full result (not using offset and limit) and then
* return the wanted range, setting the count (@see getCount) to the whole result number
*
* @return Value[][]
*/
public function execute()
{
list($sql, $opts) = $this->getSQL();
/** @var \PDOStatement $res */
$res = $this->sqlite->query($sql, $opts);
if ($res === false) {
throw new StructException("SQL execution failed for\n\n{$sql}");
}
$this->result_pids = array();
$result = array();
$cursor = -1;
$pageidAndRevOnly = array_reduce($this->columns, function ($pageidAndRevOnly, Column $col) {
return $pageidAndRevOnly && $col->getTid() == 0;
}, true);
while ($row = $res->fetch(\PDO::FETCH_ASSOC)) {
$cursor++;
if ($cursor < $this->range_begin) {
continue;
}
if ($this->range_end && $cursor >= $this->range_end) {
continue;
}
$C = 0;
$resrow = array();
$isempty = true;
foreach ($this->columns as $col) {
$val = $row["C{$C}"];
if ($col->isMulti()) {
$val = explode(self::CONCAT_SEPARATOR, $val);
}
$value = new Value($col, $val);
$isempty &= $this->isEmptyValue($value);
$resrow[] = $value;
$C++;
}
// skip empty rows
if ($isempty && !$pageidAndRevOnly) {
$cursor--;
continue;
}
$this->result_pids[] = $row['PID'];
$result[] = $resrow;
}
$this->sqlite->res_close($res);
$this->count = $cursor + 1;
return $result;
}
public function test_saveData()
{
// arrange
$testdata = array('testcolumn' => 'value1_saved', 'testMulitColumn' => array("value2.1_saved", "value2.2_saved", "value2.3_saved"));
// act
$schemaData = meta\AccessTable::byTableName('testtable', 'testpage', time());
$result = $schemaData->saveData($testdata);
// assert
/** @noinspection SqlResolve */
$res = $this->sqlite->query("SELECT pid, col1, col2 FROM data_testtable WHERE pid = ? ORDER BY rev DESC LIMIT 1", array('testpage'));
$actual_saved_single = $this->sqlite->res2row($res);
$expected_saved_single = array('pid' => 'testpage', 'col1' => 'value1_saved', 'col2' => 'value2.1_saved');
/** @noinspection SqlResolve */
$res = $this->sqlite->query("SELECT colref, row, value FROM multi_testtable WHERE pid = ? ORDER BY rev DESC LIMIT 3", array('testpage'));
$actual_saved_multi = $this->sqlite->res2arr($res);
$expected_saved_multi = array(array('colref' => '2', 'row' => '1', 'value' => "value2.1_saved"), array('colref' => '2', 'row' => '2', 'value' => "value2.2_saved"), array('colref' => '2', 'row' => '3', 'value' => "value2.3_saved"));
$this->assertTrue($result, 'should be true on success');
$this->assertEquals($expected_saved_single, $actual_saved_single, 'single value fields');
$this->assertEquals($expected_saved_multi, $actual_saved_multi, 'multi value fields');
}
private function getTestPageId()
{
$res = $this->db->query('SELECT pid FROM pages WHERE page = ?', 'test');
$pid = (int) $this->db->res2single($res);
return $pid;
}
