open) {
$this->open('db.sqlite');
$this->open = true;
} else {
$this->close();
$this->open('db.sqlite');
}
// Create tables if they don't exist
if (!@$this->query('SELECT * FROM dossiers LIMIT 1')) {
$res = $this->query('
CREATE TABLE dossiers (
name varchar(128),
slugname varchar(128),
cloud_link varchar(1024),
wiki_link varchar(1024),
pads_link varchar(1024),
last_edit_date datetime
)
');
if ($res)
echo "created dossiers table
";
else
echo "error creating dossiers table: ".$this->lastErrorMsg()."
";
}
if (!@$this->query('SELECT * FROM projects LIMIT 1')) {
$res = $this->query('
CREATE TABLE projects (
name varchar(128),
slugname varchar(128),
dossier_id integer,
main_pad varchar(1024),
cover_pad varchar(1024),
pads text,
last_edit_date datetime
)
');
if ($res)
echo "created projects table
";
else
echo "error creating projects table: ".$this->lastErrorMsg()."
";
}
// Add columns that don't exist (retro-compatibility)
// TODO: have a version number (in db/table comment ?) so that we don't have to blindly query
@$this->query('ALTER TABLE dossiers ADD pads_link varchar(1024)');
return true;
}
public function GetObjects ($type) {
$this->ensure_connection();
if (!in_array($type,['dossiers','projects'])) {
echo "mistake '$type'
";
return [];
}
$results = $this->query("SELECT rowid, * FROM $type");
$objects = [];
while ($o = $results->fetchArray(SQLITE3_ASSOC))
$objects[$o['rowid']] = $o;
return $objects;
}
public function GetEmptyObject ($type) {
switch ($type) {
case 'dossier':
return [
'rowid'=>'',
'name'=>'',
'slugname'=>'',
'cloud_link'=>'',
'wiki_link'=>'',
'pads_link'=>'',
'last_edit_date'=>'',
];
case 'project':
return [
'rowid'=>'',
'name'=>'',
'slugname'=>'',
'dossier_id'=>'',
'dossier'=>'',
'main_pad'=>'',
'cover_pad'=>'',
'pads'=>'',
'last_edit_date'=>'',
];
}
}
public function CreateOrEdit ($type,$params) {
switch ($type) {
case 'dossier':
return $this->CreateOrEditDossier($params);
case 'project':
return $this->CreateOrEditProject($params);
}
}
public function CreateOrEditDossier ($params) {
$this->ensure_connection();
// EDITION
if (isset($params['rowid']) && $params['rowid']) {
$q = $this->prepare("
UPDATE dossiers
SET
name = :name,
cloud_link = :cloud_link,
wiki_link = :wiki_link,
pads_link = :pads_link,
last_edit_date = datetime('now')
WHERE
rowid = :rowid
");
$q->bindValue(':name', $params['name'], SQLITE3_TEXT);
$q->bindValue(':cloud_link', $params['cloud_link'], SQLITE3_TEXT);
$q->bindValue(':wiki_link', $params['wiki_link'], SQLITE3_TEXT);
$q->bindValue(':pads_link', $params['pads_link'], SQLITE3_TEXT);
$q->bindValue(':rowid', $params['rowid'], SQLITE3_INTEGER);
$result = $q->execute();
if (!$result)
echo "Erreur à l'édition: ".$this->lastErrorMsg()."
";
else
return $params['rowid'];
}
// CREATION
else {
$slug = preg_replace('_\W+_','-',strtolower($params['name']));
$slug = preg_replace('_^-|-$_','',$slug);
$q = $this->prepare("
INSERT INTO dossiers
(name, slugname, cloud_link, wiki_link, pads_link, last_edit_date)
VALUES
(:name, :slugname, :cloud_link, :wiki_link, :pads_link, datetime('now'))
");
$q->bindValue(':name', $params['name'], SQLITE3_TEXT);
$q->bindValue(':slugname', $slug, SQLITE3_TEXT);
$q->bindValue(':cloud_link', $params['cloud_link'], SQLITE3_TEXT);
$q->bindValue(':wiki_link', $params['wiki_link'], SQLITE3_TEXT);
$q->bindValue(':pads_link', $params['pads_link'], SQLITE3_TEXT);
$result = $q->execute();
if ($result)
return $this->lastInsertRowID();
}
return false;
}
public function CreateOrEditProject ($params) {
$this->ensure_connection();
// $params['pads'] = json_encode($params['pads']);
// EDITION
if (isset($params['rowid']) && $params['rowid']) {
$q = $this->prepare("
UPDATE projects
SET
name = :name,
dossier_id = :dossier_id,
main_pad = :main_pad,
cover_pad = :cover_pad,
pads = :pads,
last_edit_date = datetime('now')
WHERE
rowid = :rowid
");
$q->bindValue(':name', $params['name'], SQLITE3_TEXT);
$q->bindValue(':dossier_id', $params['dossier_id'], SQLITE3_INTEGER);
$q->bindValue(':main_pad', $params['main_pad'], SQLITE3_TEXT);
$q->bindValue(':cover_pad', $params['cover_pad'], SQLITE3_TEXT);
$q->bindValue(':pads', 'TODO', SQLITE3_TEXT);
$q->bindValue(':rowid', $params['rowid'], SQLITE3_INTEGER);
$result = $q->execute();
if (!$result)
echo "Erreur à l'édition: ".$this->lastErrorMsg()."
";
else
return $params['rowid'];
}
// CREATION
else {
$slug = preg_replace('_\W+_','-',strtolower($params['name']));
$slug = preg_replace('_^-|-$_','',$slug);
$q = $this->prepare("
INSERT INTO projects
(name, slugname, dossier_id, main_pad, cover_pad, last_edit_date)
VALUES
(:name, :slugname, :dossier_id, :main_pad, :cover_pad, datetime('now'))
");
$q->bindValue(':name', $params['name'], SQLITE3_TEXT);
$q->bindValue(':slugname', $slug, SQLITE3_TEXT);
$q->bindValue(':dossier_id', $params['dossier_id'], SQLITE3_INTEGER);
$q->bindValue(':main_pad', $params['main_pad'], SQLITE3_TEXT);
$q->bindValue(':cover_pad', $params['cover_pad'], SQLITE3_TEXT);
$q->bindValue(':pads', $params['pads'], SQLITE3_TEXT);
$result = $q->execute();
if ($result)
return $this->lastInsertRowID();
}
return false;
}
public function Delete ($type,$id) {
switch ($type) {
case 'dossier':
return $this->DeleteDossier($id);
case 'project':
return $this->DeleteProject($id);
}
}
public function DeleteDossier ($id) {
$this->ensure_connection();
$q = $this->prepare("DELETE FROM dossiers WHERE rowid = :rowid");
$q->bindValue(':rowid', $id, SQLITE3_INTEGER);
$result = $q->execute();
if (!$result)
echo "Error deleting dossier $id: ".$this->lastErrorMsg()."
";
return $result?true:false;
}
public function DeleteProject ($id) {
$this->ensure_connection();
$q = $this->prepare("DELETE FROM projects WHERE rowid = :rowid");
$q->bindValue(':rowid', $id, SQLITE3_INTEGER);
$result = $q->execute();
if (!$result)
echo "Error deleting project $id: ".$this->lastErrorMsg()."
";
return $result?true:false;
}
}