/** * 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; }
/** * 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; }
/** * 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; }