function version_1($pdo)
{
$pdo->exec("\n CREATE TABLE config (\n language CHAR(5) DEFAULT 'en_US',\n webhooks_token VARCHAR(255),\n timezone VARCHAR(50) DEFAULT 'UTC'\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n CREATE TABLE users (\n id INT NOT NULL AUTO_INCREMENT,\n username VARCHAR(50),\n password VARCHAR(255),\n is_admin TINYINT DEFAULT 0,\n default_project_id INT DEFAULT 0,\n PRIMARY KEY (id)\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n CREATE TABLE projects (\n id INT NOT NULL AUTO_INCREMENT,\n name VARCHAR(50) UNIQUE,\n is_active TINYINT DEFAULT 1,\n token VARCHAR(255),\n PRIMARY KEY (id)\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n CREATE TABLE project_has_users (\n id INT NOT NULL AUTO_INCREMENT,\n project_id INT,\n user_id INT,\n PRIMARY KEY (id),\n UNIQUE KEY `idx_project_user` (project_id, user_id),\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,\n FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n CREATE TABLE columns (\n id INT NOT NULL AUTO_INCREMENT,\n title VARCHAR(255),\n position INT NOT NULL,\n project_id INT NOT NULL,\n task_limit INT DEFAULT '0',\n UNIQUE KEY `idx_title_project` (title, project_id),\n PRIMARY KEY (id),\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n CREATE TABLE tasks (\n id INT NOT NULL AUTO_INCREMENT,\n title VARCHAR(255),\n description TEXT,\n date_creation INT,\n date_completed INT,\n date_due INT,\n color_id VARCHAR(50),\n project_id INT,\n column_id INT,\n owner_id INT DEFAULT '0',\n position INT,\n score INT,\n is_active TINYINT DEFAULT 1,\n PRIMARY KEY (id),\n INDEX `idx_task_active` (is_active),\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,\n FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n CREATE TABLE comments (\n id INT NOT NULL AUTO_INCREMENT,\n task_id INT,\n user_id INT,\n date INT,\n comment TEXT,\n PRIMARY KEY (id),\n FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE,\n FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n CREATE TABLE actions (\n id INT NOT NULL AUTO_INCREMENT,\n project_id INT,\n event_name VARCHAR(50),\n action_name VARCHAR(50),\n PRIMARY KEY (id),\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n CREATE TABLE action_has_params (\n id INT NOT NULL AUTO_INCREMENT,\n action_id INT,\n name VARCHAR(50),\n value VARCHAR(50),\n PRIMARY KEY (id),\n FOREIGN KEY(action_id) REFERENCES actions(id) ON DELETE CASCADE\n ) ENGINE=InnoDB CHARSET=utf8\n ");
$pdo->exec("\n INSERT INTO users\n (username, password, is_admin)\n VALUES ('admin', '" . \password_hash('admin', PASSWORD_BCRYPT) . "', '1')\n ");
$pdo->exec("\n INSERT INTO config\n (webhooks_token)\n VALUES ('" . Security::generateToken() . "')\n ");
}
/**
* Regenerate all tokens (projects and webhooks)
*
* @access public
*/
public function regenerateTokens()
{
$this->db->table(self::TABLE)->update(array('webhooks_token' => Security::generateToken(), 'api_token' => Security::generateToken()));
$projects = $this->db->table(Project::TABLE)->findAllByColumn('id');
foreach ($projects as $project_id) {
$this->db->table(Project::TABLE)->eq('id', $project_id)->update(array('token' => Security::generateToken()));
}
}
/**
* Regenerate a token
*
* @access public
* @param string $option Parameter name
*/
public function regenerateToken($option)
{
return $this->db->table(self::TABLE)->eq('option', $option)->update(array('value' => Security::generateToken()));
}
/**
* Enable public access for a user
*
* @access public
* @param integer $user_id User id
* @return bool
*/
public function enablePublicAccess($user_id)
{
return $this->db->table(self::TABLE)->eq('id', $user_id)->save(array('token' => Security::generateToken()));
}
function version_1($pdo)
{
$pdo->exec("\n CREATE TABLE config (\n language CHAR(5) DEFAULT 'en_US',\n webhooks_token VARCHAR(255) DEFAULT '',\n timezone VARCHAR(50) DEFAULT 'UTC',\n api_token VARCHAR(255) DEFAULT ''\n );\n\n CREATE TABLE users (\n id SERIAL PRIMARY KEY,\n username VARCHAR(50),\n password VARCHAR(255),\n is_admin BOOLEAN DEFAULT '0',\n default_project_id INTEGER DEFAULT 0,\n is_ldap_user BOOLEAN DEFAULT '0',\n name VARCHAR(255),\n email VARCHAR(255),\n google_id VARCHAR(255),\n github_id VARCHAR(30)\n );\n\n CREATE TABLE remember_me (\n id SERIAL PRIMARY KEY,\n user_id INTEGER,\n ip VARCHAR(40),\n user_agent VARCHAR(255),\n token VARCHAR(255),\n sequence VARCHAR(255),\n expiration INTEGER,\n date_creation INTEGER,\n FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE\n );\n\n CREATE TABLE last_logins (\n id SERIAL PRIMARY KEY,\n auth_type VARCHAR(25),\n user_id INTEGER,\n ip VARCHAR(40),\n user_agent VARCHAR(255),\n date_creation INTEGER,\n FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE\n );\n\n CREATE TABLE projects (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255) UNIQUE,\n is_active BOOLEAN DEFAULT '1',\n token VARCHAR(255),\n last_modified INTEGER DEFAULT 0\n );\n\n CREATE TABLE project_has_users (\n id SERIAL PRIMARY KEY,\n project_id INTEGER,\n user_id INTEGER,\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,\n FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,\n UNIQUE(project_id, user_id)\n );\n\n CREATE TABLE project_has_categories (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255),\n project_id INTEGER,\n UNIQUE (project_id, name),\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE\n );\n\n CREATE TABLE columns (\n id SERIAL PRIMARY KEY,\n title VARCHAR(255),\n position INTEGER,\n project_id INTEGER,\n task_limit INTEGER DEFAULT 0,\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,\n UNIQUE (title, project_id)\n );\n\n CREATE TABLE tasks (\n id SERIAL PRIMARY KEY,\n title VARCHAR(255),\n description TEXT,\n date_creation INTEGER,\n color_id VARCHAR(255),\n project_id INTEGER,\n column_id INTEGER,\n owner_id INTEGER DEFAULT 0,\n position INTEGER,\n is_active BOOLEAN DEFAULT '1',\n date_completed INTEGER,\n score INTEGER,\n date_due INTEGER,\n category_id INTEGER DEFAULT 0,\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,\n FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE\n );\n\n CREATE TABLE task_has_subtasks (\n id SERIAL PRIMARY KEY,\n title VARCHAR(255),\n status SMALLINT DEFAULT 0,\n time_estimated INTEGER DEFAULT 0,\n time_spent INTEGER DEFAULT 0,\n task_id INTEGER NOT NULL,\n user_id INTEGER,\n FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE\n );\n\n CREATE TABLE task_has_files (\n id SERIAL PRIMARY KEY,\n name VARCHAR(255),\n path VARCHAR(255),\n is_image BOOLEAN DEFAULT '0',\n task_id INTEGER,\n FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE\n );\n\n CREATE TABLE comments (\n id SERIAL PRIMARY KEY,\n task_id INTEGER,\n user_id INTEGER,\n date INTEGER,\n comment TEXT,\n FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE,\n FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE\n );\n\n CREATE TABLE actions (\n id SERIAL PRIMARY KEY,\n project_id INTEGER,\n event_name VARCHAR(50),\n action_name VARCHAR(50),\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE\n );\n\n CREATE TABLE action_has_params (\n id SERIAL PRIMARY KEY,\n action_id INTEGER,\n name VARCHAR(50),\n value VARCHAR(50),\n FOREIGN KEY(action_id) REFERENCES actions(id) ON DELETE CASCADE\n );\n ");
$pdo->exec("\n INSERT INTO users\n (username, password, is_admin)\n VALUES ('admin', '" . \password_hash('admin', PASSWORD_BCRYPT) . "', '1')\n ");
$pdo->exec("\n INSERT INTO config\n (webhooks_token, api_token)\n VALUES ('" . Security::generateToken() . "', '" . Security::generateToken() . "')\n ");
}
function version_1($pdo)
{
$pdo->exec("\n CREATE TABLE config (\n language TEXT DEFAULT 'en_US',\n webhooks_token TEXT DEFAULT ''\n )\n ");
$pdo->exec("\n CREATE TABLE users (\n id INTEGER PRIMARY KEY,\n username TEXT,\n password TEXT,\n is_admin INTEGER DEFAULT 0,\n default_project_id INTEGER DEFAULT 0\n )\n ");
$pdo->exec("\n CREATE TABLE projects (\n id INTEGER PRIMARY KEY,\n name TEXT NOCASE UNIQUE,\n is_active INTEGER DEFAULT 1\n )\n ");
$pdo->exec("\n CREATE TABLE columns (\n id INTEGER PRIMARY KEY,\n title TEXT,\n position INTEGER,\n project_id INTEGER,\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,\n UNIQUE (title, project_id)\n )\n ");
$pdo->exec("\n CREATE TABLE tasks (\n id INTEGER PRIMARY KEY,\n title TEXT NOCASE NOT NULL,\n description TEXT,\n date_creation INTEGER,\n color_id TEXT,\n project_id INTEGER,\n column_id INTEGER,\n owner_id INTEGER DEFAULT '0',\n position INTEGER,\n is_active INTEGER DEFAULT 1,\n FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,\n FOREIGN KEY(column_id) REFERENCES columns(id) ON DELETE CASCADE\n )\n ");
$pdo->exec("\n INSERT INTO users\n (username, password, is_admin)\n VALUES ('admin', '" . \password_hash('admin', PASSWORD_BCRYPT) . "', '1')\n ");
$pdo->exec("\n INSERT INTO config\n (webhooks_token)\n VALUES ('" . Security::generateToken() . "')\n ");
}
/**
* Enable public access for a project
*
* @access public
* @param integer $project_id Project id
* @return bool
*/
public function enablePublicAccess($project_id)
{
return $this->exists($project_id) && $this->db->table(self::TABLE)->eq('id', $project_id)->save(array('is_public' => 1, 'token' => Security::generateToken()));
}
/**
* Return a new sequence token and update the database
*
* @access public
* @param string $token Session token
* @return string
*/
public function update($token)
{
$new_sequence = Security::generateToken();
$this->db->table(self::TABLE)->eq('token', $token)->update(array('sequence' => $new_sequence));
return $new_sequence;
}