[github/wallabag/wallabag.git] / inc / poche / Database.class.php

<?php
/**
 * wallabag, self hostable application allowing you to not miss any content anymore
 *
 * @category   wallabag
 * @author     Nicolas Lœuillet <nicolas@loeuillet.org>
 * @copyright  2013
 * @license    http://www.wtfpl.net/ see COPYING file
 */

class Database {
    var $handle;
    private $order = array(
      'ia' => 'ORDER BY entries.id',
      'id' => 'ORDER BY entries.id DESC',
      'ta' => 'ORDER BY lower(entries.title)',
      'td' => 'ORDER BY lower(entries.title) DESC',
      'default' => 'ORDER BY entries.id'
    );

    function __construct() 
    {
        switch (STORAGE) {
            case 'sqlite':
                $db_path = 'sqlite:' . STORAGE_SQLITE;
                $this->handle = new PDO($db_path);
                break;
            case 'mysql':
                $db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
                $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); 
                break;
            case 'postgres':
                $db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
                $this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD); 
                break;
        }

        $this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        Tools::logm('storage type ' . STORAGE);
    }

    private function getHandle() {
        return $this->handle;
    }

    public function isInstalled() {
        $sql = "SELECT username FROM users";
        $query = $this->executeQuery($sql, array());
        if ($query == false) {
            die(STORAGE . ' database looks empty. You have to create it (you can find database structure in install folder).');
        }
        $hasAdmin = count($query->fetchAll());

        if ($hasAdmin == 0) 
            return false;

        return true;
    }

    public function checkTags() {

        if (STORAGE == 'sqlite') {
            $sql = '
                CREATE TABLE IF NOT EXISTS tags (
                    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
                    value TEXT
                )';
        }
        elseif(STORAGE == 'mysql') {
            $sql = '
                CREATE TABLE IF NOT EXISTS `tags` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `value` varchar(255) NOT NULL,
                  PRIMARY KEY (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
            ';
        }
        else {
            $sql = '
                CREATE TABLE tags (
                  id bigserial primary key,
                  value varchar(255) NOT NULL
                );
            ';
        }

        $query = $this->executeQuery($sql, array());

        if (STORAGE == 'sqlite') {
            $sql = '
                CREATE TABLE IF NOT EXISTS tags_entries (
                    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
                    entry_id INTEGER,
                    tag_id INTEGER,
                    FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE,
                    FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
                )';
        }
        elseif(STORAGE == 'mysql') {
            $sql = '
                CREATE TABLE IF NOT EXISTS `tags_entries` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `entry_id` int(11) NOT NULL,
                  `tag_id` int(11) NOT NULL,
                  FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE,
                  FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE,
                  PRIMARY KEY (`id`)
                ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
            ';
        }
        else {
            $sql = '
                CREATE TABLE tags_entries (
                  id bigserial primary key,
                  entry_id integer NOT NULL,
                  tag_id integer NOT NULL
                )
            ';
        }

        $query = $this->executeQuery($sql, array());
    }

    public function install($login, $password) {
        $sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)';
        $params = array($login, $password, $login, ' ');
        $query = $this->executeQuery($sql, $params);

        $sequence = '';
        if (STORAGE == 'postgres') {
            $sequence = 'users_id_seq';
        }

        $id_user = intval($this->getLastId($sequence));

        $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
        $params = array($id_user, 'pager', PAGINATION);
        $query = $this->executeQuery($sql, $params);

        $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
        $params = array($id_user, 'language', LANG);
        $query = $this->executeQuery($sql, $params);
        
        $sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
        $params = array($id_user, 'theme', DEFAULT_THEME);
        $query = $this->executeQuery($sql, $params);

        return TRUE;
    }

    public function getConfigUser($id) {
        $sql = "SELECT * FROM users_config WHERE user_id = ?";
        $query = $this->executeQuery($sql, array($id));
        $result = $query->fetchAll();
        $user_config = array();
        
        foreach ($result as $key => $value) {
            $user_config[$value['name']] = $value['value'];
        }

        return $user_config;
    }

    public function userExists($username) {
        $sql = "SELECT * FROM users WHERE username=?";
        $query = $this->executeQuery($sql, array($username));
        $login = $query->fetchAll();
        if (isset($login[0])) {
            return true;
        } else {
            return false;
        }
    }

    public function login($username, $password, $isauthenticated=false) {
        if ($isauthenticated) {
          $sql = "SELECT * FROM users WHERE username=?";
          $query = $this->executeQuery($sql, array($username));
        } else {
          $sql = "SELECT * FROM users WHERE username=? AND password=?";
          $query = $this->executeQuery($sql, array($username, $password));
        }
        $login = $query->fetchAll();

        $user = array();
        if (isset($login[0])) {
            $user['id'] = $login[0]['id'];
            $user['username'] = $login[0]['username'];
            $user['password'] = $login[0]['password'];
            $user['name'] = $login[0]['name'];
            $user['email'] = $login[0]['email'];
            $user['config'] = $this->getConfigUser($login[0]['id']);
        }

        return $user;
    }

    public function updatePassword($userId, $password)
    {
        $sql_update = "UPDATE users SET password=? WHERE id=?";
        $params_update = array($password, $userId);
        $query = $this->executeQuery($sql_update, $params_update);
    }
    
    public function updateUserConfig($userId, $key, $value) {
        $config = $this->getConfigUser($userId);
        
        if (! isset($config[$key])) {
            $sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)";
        }
        else {
            $sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?";
        }

        $params = array($value, $userId, $key);
        $query = $this->executeQuery($sql, $params);
    }

    private function executeQuery($sql, $params) {
        try
        {
            $query = $this->getHandle()->prepare($sql);
            $query->execute($params);
            return $query;
        }
        catch (Exception $e)
        {
            Tools::logm('execute query error : '.$e->getMessage());
            return FALSE;
        }
    }

    public function updateContentAndTitle($id, $title, $body, $user_id) {
        $sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?';
        $params_action = array($body, $title, $id, $user_id);
        $query = $this->executeQuery($sql_action, $params_action);

        return $query;
    }

    public function retrieveUnfetchedEntries($user_id, $limit) {

        $sql_limit = "LIMIT 0,".$limit;
        if (STORAGE == 'postgres') {
            $sql_limit = "LIMIT ".$limit." OFFSET 0";
        }

        $sql        = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND user_id=? ORDER BY id " . $sql_limit;
        $query      = $this->executeQuery($sql, array($user_id));
        $entries    = $query->fetchAll();

        return $entries;
    }

    public function retrieveAll($user_id) {
        $sql        = "SELECT * FROM entries WHERE content <> '' AND user_id=? ORDER BY id";
        $query      = $this->executeQuery($sql, array($user_id));
        $entries    = $query->fetchAll();

        return $entries;
    }

    public function retrieveOneById($id, $user_id) {
        $entry  = NULL;
        $sql    = "SELECT * FROM entries WHERE id=? AND user_id=?";
        $params = array(intval($id), $user_id);
        $query  = $this->executeQuery($sql, $params);
        $entry  = $query->fetchAll();

        return isset($entry[0]) ? $entry[0] : null;
    }

    public function retrieveOneByURL($url, $user_id) {
        $entry  = NULL;
        $sql    = "SELECT * FROM entries WHERE content <> '' AND url=? AND user_id=?";
        $params = array($url, $user_id);
        $query  = $this->executeQuery($sql, $params);
        $entry  = $query->fetchAll();

        return isset($entry[0]) ? $entry[0] : null;
    }

    public function reassignTags($old_entry_id, $new_entry_id) {
        $sql    = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?";
        $params = array($new_entry_id, $old_entry_id);
        $query  = $this->executeQuery($sql, $params);
    }

    public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) {
        switch ($view) {
            case 'archive':
                $sql    = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
                $params = array($user_id, 1);
                break;
            case 'fav' :
                $sql    = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_fav=? ";
                $params = array($user_id, 1);
                break;
            case 'tag' :
                $sql    = "SELECT entries.* FROM entries
                LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
                WHERE entries.content <> '' AND
                entries.user_id=? AND tags_entries.tag_id = ? ";
                $params = array($user_id, $tag_id);
                break;
            default:
                $sql    = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
                $params = array($user_id, 0);
                break;
        }

                $sql .= $this->getEntriesOrder().' ' . $limit;

                $query = $this->executeQuery($sql, $params);
                $entries = $query->fetchAll();

                return $entries;
        }

    public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
        switch ($view) {
            case 'archive':
                    $sql    = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
                $params = array($user_id, 1);
                break;
            case 'fav' :
                    $sql    = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_fav=? ";
                $params = array($user_id, 1);
                break;
            case 'tag' :
                $sql    = "SELECT count(*) FROM entries
                    LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
                    WHERE entries.content <> '' AND
                    entries.user_id=? AND tags_entries.tag_id = ? ";
                $params = array($user_id, $tag_id);
                break;
            default:
                $sql    = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
                $params = array($user_id, 0);
                break;
        }

        $query = $this->executeQuery($sql, $params);
        list($count) = $query->fetch();

        return $count;
    }

    public function updateContent($id, $content, $user_id) {
        $sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?';
        $params_action = array($content, $id, $user_id);
        $query = $this->executeQuery($sql_action, $params_action);
        return $query;
    }

    public function add($url, $title, $content, $user_id) {
        $sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)';
        $params_action = array($url, $title, $content, $user_id);
        $query = $this->executeQuery($sql_action, $params_action);
        return $query;
    }

    public function deleteById($id, $user_id) {
        $sql_action     = "DELETE FROM entries WHERE id=? AND user_id=?";
        $params_action  = array($id, $user_id);
        $query          = $this->executeQuery($sql_action, $params_action);
        return $query;
    }

    public function favoriteById($id, $user_id) {
        $sql_action     = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
        $params_action  = array($id, $user_id);
        $query          = $this->executeQuery($sql_action, $params_action);
    }

    public function archiveById($id, $user_id) {
        $sql_action     = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
        $params_action  = array($id, $user_id);
        $query          = $this->executeQuery($sql_action, $params_action);
    }

    public function archiveAll($user_id) {
        $sql_action     = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?";
        $params_action  = array($user_id, 1, 0);
        $query          = $this->executeQuery($sql_action, $params_action);
    }

    public function getLastId($column = '') {
        return $this->getHandle()->lastInsertId($column);
    }

    public function retrieveAllTags($user_id) {
        $sql = "SELECT DISTINCT tags.* FROM tags
          LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
          LEFT JOIN entries ON tags_entries.entry_id=entries.id
          WHERE entries.content <> '' AND entries.user_id=?";
        $query = $this->executeQuery($sql, array($user_id));
        $tags = $query->fetchAll();

        return $tags;
    }

    public function retrieveTag($id, $user_id) {
        $tag  = NULL;
        $sql    = "SELECT DISTINCT tags.* FROM tags
          LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
          LEFT JOIN entries ON tags_entries.entry_id=entries.id
          WHERE entries.content <> '' AND tags.id=? AND entries.user_id=?";
        $params = array(intval($id), $user_id);
        $query  = $this->executeQuery($sql, $params);
        $tag  = $query->fetchAll();

        return isset($tag[0]) ? $tag[0] : null;
    }

    public function retrieveEntriesByTag($tag_id, $user_id) {
        $sql = 
            "SELECT entries.* FROM entries
            LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
            WHERE entries.content <> '' AND
            tags_entries.tag_id = ? AND entries.user_id=?";
        $query = $this->executeQuery($sql, array($tag_id, $user_id));
        $entries = $query->fetchAll();

        return $entries;
    }

    public function retrieveTagsByEntry($entry_id) {
        $sql = 
            "SELECT tags.* FROM tags
            LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
            WHERE tags_entries.entry_id = ?";
        $query = $this->executeQuery($sql, array($entry_id));
        $tags = $query->fetchAll();

        return $tags;
    }

    public function removeTagForEntry($entry_id, $tag_id) {
        $sql_action     = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?";
        $params_action  = array($tag_id, $entry_id);
        $query          = $this->executeQuery($sql_action, $params_action);
        return $query;
    }

    public function retrieveTagByValue($value) {
        $tag  = NULL;
        $sql    = "SELECT * FROM tags WHERE value=?";
        $params = array($value);
        $query  = $this->executeQuery($sql, $params);
        $tag  = $query->fetchAll();

        return isset($tag[0]) ? $tag[0] : null;
    }

    public function createTag($value) {
        $sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
        $params_action = array($value);
        $query = $this->executeQuery($sql_action, $params_action);
        return $query;
    }

    public function setTagToEntry($tag_id, $entry_id) {
        $sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
        $params_action = array($tag_id, $entry_id);
        $query = $this->executeQuery($sql_action, $params_action);
        return $query;
    }

        private function getEntriesOrder() {
            if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) {
                return $this->order[$_SESSION['sort']];
            }
            else {
                return $this->order['default'];
            }
        }

}
Commit	Line	Data
14890de3	1	<?php
14890de3	2	/**
c95b78a8	3	* wallabag, self hostable application allowing you to not miss any content anymore
14890de3	4	*
c95b78a8 NL	5	* @category wallabag
c95b78a8 NL	6	* @author Nicolas Lœuillet <nicolas@loeuillet.org>
14890de3	7	* @copyright 2013
	8	* @license http://www.wtfpl.net/ see COPYING file
	9	*/
	10
bc1ee852	11	class Database {
14890de3	12	var $handle;
032e0ca1 MR	13	private $order = array(
	14	'ia' => 'ORDER BY entries.id',
	15	'id' => 'ORDER BY entries.id DESC',
	16	'ta' => 'ORDER BY lower(entries.title)',
	17	'td' => 'ORDER BY lower(entries.title) DESC',
	18	'default' => 'ORDER BY entries.id'
	19	);
	20
	21	function __construct()
68857cea NL	22	{
	23	switch (STORAGE) {
	24	case 'sqlite':
	25	$db_path = 'sqlite:' . STORAGE_SQLITE;
	26	$this->handle = new PDO($db_path);
	27	break;
	28	case 'mysql':
	29	$db_path = 'mysql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
	30	$this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD);
	31	break;
	32	case 'postgres':
	33	$db_path = 'pgsql:host=' . STORAGE_SERVER . ';dbname=' . STORAGE_DB;
	34	$this->handle = new PDO($db_path, STORAGE_USER, STORAGE_PASSWORD);
	35	break;
	36	}
	37
14890de3	38	$this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
580d60b9	39	Tools::logm('storage type ' . STORAGE);
14890de3	40	}
	41
	42	private function getHandle() {
	43	return $this->handle;
	44	}
	45
aa8c9f2a	46	public function isInstalled() {
b916bcfc NL	47	$sql = "SELECT username FROM users";
b916bcfc NL	48	$query = $this->executeQuery($sql, array());
5cfafc61 NL	49	if ($query == false) {
	50	die(STORAGE . ' database looks empty. You have to create it (you can find database structure in install folder).');
	51	}
b916bcfc	52	$hasAdmin = count($query->fetchAll());
aa8c9f2a	53
b916bcfc	54	if ($hasAdmin == 0)
5cfafc61 NL	55	return false;
	56
	57	return true;
	58	}
	59
	60	public function checkTags() {
	61
	62	if (STORAGE == 'sqlite') {
	63	$sql = '
	64	CREATE TABLE IF NOT EXISTS tags (
	65	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
	66	value TEXT
	67	)';
	68	}
	69	elseif(STORAGE == 'mysql') {
	70	$sql = '
	71	CREATE TABLE IF NOT EXISTS `tags` (
	72	`id` int(11) NOT NULL AUTO_INCREMENT,
	73	`value` varchar(255) NOT NULL,
	74	PRIMARY KEY (`id`)
	75	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	76	';
	77	}
	78	else {
	79	$sql = '
	80	CREATE TABLE tags (
	81	id bigserial primary key,
	82	value varchar(255) NOT NULL
	83	);
	84	';
	85	}
aa8c9f2a	86
5cfafc61 NL	87	$query = $this->executeQuery($sql, array());
	88
	89	if (STORAGE == 'sqlite') {
	90	$sql = '
a562e390	91	CREATE TABLE IF NOT EXISTS tags_entries (
5cfafc61 NL	92	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
	93	entry_id INTEGER,
	94	tag_id INTEGER,
	95	FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE,
	96	FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
	97	)';
	98	}
	99	elseif(STORAGE == 'mysql') {
	100	$sql = '
	101	CREATE TABLE IF NOT EXISTS `tags_entries` (
	102	`id` int(11) NOT NULL AUTO_INCREMENT,
	103	`entry_id` int(11) NOT NULL,
	104	`tag_id` int(11) NOT NULL,
	105	FOREIGN KEY(entry_id) REFERENCES entries(id) ON DELETE CASCADE,
	106	FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE,
	107	PRIMARY KEY (`id`)
	108	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	109	';
	110	}
	111	else {
	112	$sql = '
	113	CREATE TABLE tags_entries (
	114	id bigserial primary key,
	115	entry_id integer NOT NULL,
	116	tag_id integer NOT NULL
	117	)
	118	';
	119	}
	120
	121	$query = $this->executeQuery($sql, array());
aa8c9f2a NL	122	}
	123
	124	public function install($login, $password) {
b916bcfc NL	125	$sql = 'INSERT INTO users ( username, password, name, email) VALUES (?, ?, ?, ?)';
	126	$params = array($login, $password, $login, ' ');
	127	$query = $this->executeQuery($sql, $params);
	128
	129	$sequence = '';
	130	if (STORAGE == 'postgres') {
	131	$sequence = 'users_id_seq';
	132	}
	133
	134	$id_user = intval($this->getLastId($sequence));
	135
	136	$sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
00dbaf90	137	$params = array($id_user, 'pager', PAGINATION);
b916bcfc NL	138	$query = $this->executeQuery($sql, $params);
	139
	140	$sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
00dbaf90 NL	141	$params = array($id_user, 'language', LANG);
	142	$query = $this->executeQuery($sql, $params);
	143
	144	$sql = 'INSERT INTO users_config ( user_id, name, value ) VALUES (?, ?, ?)';
	145	$params = array($id_user, 'theme', DEFAULT_THEME);
7ce7ec4c	146	$query = $this->executeQuery($sql, $params);
aa8c9f2a	147
7ce7ec4c NL	148	return TRUE;
7ce7ec4c NL	149	}
aa8c9f2a	150
5846b0f1	151	public function getConfigUser($id) {
7ce7ec4c NL	152	$sql = "SELECT * FROM users_config WHERE user_id = ?";
	153	$query = $this->executeQuery($sql, array($id));
	154	$result = $query->fetchAll();
	155	$user_config = array();
	156
	157	foreach ($result as $key => $value) {
	158	$user_config[$value['name']] = $value['value'];
aa8c9f2a NL	159	}
aa8c9f2a NL	160
7ce7ec4c	161	return $user_config;
aa8c9f2a NL	162	}
aa8c9f2a NL	163
027b4e15 DS	164	public function userExists($username) {
	165	$sql = "SELECT * FROM users WHERE username=?";
	166	$query = $this->executeQuery($sql, array($username));
	167	$login = $query->fetchAll();
	168	if (isset($login[0])) {
	169	return true;
	170	} else {
	171	return false;
	172	}
	173	}
	174
6af66b11 MR	175	public function login($username, $password, $isauthenticated=false) {
	176	if ($isauthenticated) {
	177	$sql = "SELECT * FROM users WHERE username=?";
	178	$query = $this->executeQuery($sql, array($username));
	179	} else {
	180	$sql = "SELECT * FROM users WHERE username=? AND password=?";
	181	$query = $this->executeQuery($sql, array($username, $password));
	182	}
8d3275be	183	$login = $query->fetchAll();
aa8c9f2a	184
7ce7ec4c NL	185	$user = array();
	186	if (isset($login[0])) {
	187	$user['id'] = $login[0]['id'];
	188	$user['username'] = $login[0]['username'];
	189	$user['password'] = $login[0]['password'];
	190	$user['name'] = $login[0]['name'];
	191	$user['email'] = $login[0]['email'];
	192	$user['config'] = $this->getConfigUser($login[0]['id']);
	193	}
aa8c9f2a	194
7ce7ec4c	195	return $user;
aa8c9f2a NL	196	}
aa8c9f2a NL	197
00dbaf90	198	public function updatePassword($userId, $password)
6499b26a	199	{
8d3275be	200	$sql_update = "UPDATE users SET password=? WHERE id=?";
1e98ee1d NL	201	$params_update = array($password, $userId);
1e98ee1d NL	202	$query = $this->executeQuery($sql_update, $params_update);
00dbaf90 NL	203	}
	204
	205	public function updateUserConfig($userId, $key, $value) {
58ace494 NL	206	$config = $this->getConfigUser($userId);
58ace494 NL	207
d1d3498b	208	if (! isset($config[$key])) {
da5fc42f	209	$sql = "INSERT INTO users_config (value, user_id, name) VALUES (?, ?, ?)";
58ace494 NL	210	}
58ace494 NL	211	else {
da5fc42f	212	$sql = "UPDATE users_config SET value=? WHERE user_id=? AND name=?";
58ace494 NL	213	}
	214
	215	$params = array($value, $userId, $key);
	216	$query = $this->executeQuery($sql, $params);
6499b26a NL	217	}
6499b26a NL	218
14890de3	219	private function executeQuery($sql, $params) {
	220	try
	221	{
	222	$query = $this->getHandle()->prepare($sql);
	223	$query->execute($params);
	224	return $query;
	225	}
	226	catch (Exception $e)
	227	{
eb1af592	228	Tools::logm('execute query error : '.$e->getMessage());
bc1ee852	229	return FALSE;
14890de3	230	}
	231	}
	232
53e3158d NL	233	public function updateContentAndTitle($id, $title, $body, $user_id) {
	234	$sql_action = 'UPDATE entries SET content = ?, title = ? WHERE id=? AND user_id=?';
	235	$params_action = array($body, $title, $id, $user_id);
	236	$query = $this->executeQuery($sql_action, $params_action);
	237
	238	return $query;
	239	}
	240
	241	public function retrieveUnfetchedEntries($user_id, $limit) {
	242
	243	$sql_limit = "LIMIT 0,".$limit;
	244	if (STORAGE == 'postgres') {
	245	$sql_limit = "LIMIT ".$limit." OFFSET 0";
	246	}
	247
	248	$sql = "SELECT * FROM entries WHERE (content = '' OR content IS NULL) AND user_id=? ORDER BY id " . $sql_limit;
	249	$query = $this->executeQuery($sql, array($user_id));
	250	$entries = $query->fetchAll();
	251
	252	return $entries;
	253	}
	254
8d3275be	255	public function retrieveAll($user_id) {
53e3158d	256	$sql = "SELECT * FROM entries WHERE content <> '' AND user_id=? ORDER BY id";
8d3275be	257	$query = $this->executeQuery($sql, array($user_id));
44e77bfa	258	$entries = $query->fetchAll();
	259
	260	return $entries;
	261	}
	262
8d3275be	263	public function retrieveOneById($id, $user_id) {
14890de3	264	$entry = NULL;
8d3275be NL	265	$sql = "SELECT * FROM entries WHERE id=? AND user_id=?";
8d3275be NL	266	$params = array(intval($id), $user_id);
14890de3	267	$query = $this->executeQuery($sql, $params);
	268	$entry = $query->fetchAll();
	269
0c2f4537	270	return isset($entry[0]) ? $entry[0] : null;
14890de3	271	}
14890de3	272
488fc63b MR	273	public function retrieveOneByURL($url, $user_id) {
488fc63b MR	274	$entry = NULL;
53e3158d	275	$sql = "SELECT * FROM entries WHERE content <> '' AND url=? AND user_id=?";
488fc63b MR	276	$params = array($url, $user_id);
	277	$query = $this->executeQuery($sql, $params);
	278	$entry = $query->fetchAll();
	279
	280	return isset($entry[0]) ? $entry[0] : null;
	281	}
	282
	283	public function reassignTags($old_entry_id, $new_entry_id) {
	284	$sql = "UPDATE tags_entries SET entry_id=? WHERE entry_id=?";
	285	$params = array($new_entry_id, $old_entry_id);
	286	$query = $this->executeQuery($sql, $params);
	287	}
	288
032e0ca1 MR	289	public function getEntriesByView($view, $user_id, $limit = '', $tag_id = 0) {
	290	switch ($view) {
	291	case 'archive':
53e3158d	292	$sql = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
032e0ca1	293	$params = array($user_id, 1);
14890de3	294	break;
032e0ca1	295	case 'fav' :
53e3158d	296	$sql = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_fav=? ";
032e0ca1	297	$params = array($user_id, 1);
14890de3	298	break;
032e0ca1 MR	299	case 'tag' :
	300	$sql = "SELECT entries.* FROM entries
	301	LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
53e3158d NL	302	WHERE entries.content <> '' AND
53e3158d NL	303	entries.user_id=? AND tags_entries.tag_id = ? ";
032e0ca1	304	$params = array($user_id, $tag_id);
14890de3	305	break;
14890de3	306	default:
53e3158d	307	$sql = "SELECT * FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
032e0ca1	308	$params = array($user_id, 0);
14890de3	309	break;
	310	}
	311
032e0ca1 MR	312	$sql .= $this->getEntriesOrder().' ' . $limit;
	313
	314	$query = $this->executeQuery($sql, $params);
	315	$entries = $query->fetchAll();
	316
	317	return $entries;
	318	}
	319
53e3158d NL	320	public function getEntriesByViewCount($view, $user_id, $tag_id = 0) {
53e3158d NL	321	switch ($view) {
14890de3	322	case 'archive':
53e3158d	323	$sql = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
bc1ee852	324	$params = array($user_id, 1);
14890de3	325	break;
14890de3	326	case 'fav' :
53e3158d	327	$sql = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_fav=? ";
bc1ee852	328	$params = array($user_id, 1);
14890de3	329	break;
53e3158d NL	330	case 'tag' :
	331	$sql = "SELECT count(*) FROM entries
	332	LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
	333	WHERE entries.content <> '' AND
	334	entries.user_id=? AND tags_entries.tag_id = ? ";
	335	$params = array($user_id, $tag_id);
	336	break;
14890de3	337	default:
53e3158d	338	$sql = "SELECT count(*) FROM entries WHERE content <> '' AND user_id=? AND is_read=? ";
8d3275be	339	$params = array($user_id, 0);
14890de3	340	break;
	341	}
	342
8d3275be	343	$query = $this->executeQuery($sql, $params);
032e0ca1	344	list($count) = $query->fetch();
14890de3	345
53e3158d	346	return $count;
14890de3	347	}
14890de3	348
b58e261d NL	349	public function updateContent($id, $content, $user_id) {
	350	$sql_action = 'UPDATE entries SET content = ? WHERE id=? AND user_id=?';
	351	$params_action = array($content, $id, $user_id);
	352	$query = $this->executeQuery($sql_action, $params_action);
	353	return $query;
	354	}
	355
8d3275be	356	public function add($url, $title, $content, $user_id) {
8d3275be NL	357	$sql_action = 'INSERT INTO entries ( url, title, content, user_id ) VALUES (?, ?, ?, ?)';
	358	$params_action = array($url, $title, $content, $user_id);
	359	$query = $this->executeQuery($sql_action, $params_action);
29c6fd46	360	return $query;
14890de3	361	}
14890de3	362
8d3275be	363	public function deleteById($id, $user_id) {
8d3275be NL	364	$sql_action = "DELETE FROM entries WHERE id=? AND user_id=?";
8d3275be NL	365	$params_action = array($id, $user_id);
14890de3	366	$query = $this->executeQuery($sql_action, $params_action);
29c6fd46	367	return $query;
14890de3	368	}
14890de3	369
8d3275be	370	public function favoriteById($id, $user_id) {
bc1ee852	371	$sql_action = "UPDATE entries SET is_fav=NOT is_fav WHERE id=? AND user_id=?";
8d3275be	372	$params_action = array($id, $user_id);
14890de3	373	$query = $this->executeQuery($sql_action, $params_action);
	374	}
	375
8d3275be	376	public function archiveById($id, $user_id) {
bc1ee852	377	$sql_action = "UPDATE entries SET is_read=NOT is_read WHERE id=? AND user_id=?";
8d3275be	378	$params_action = array($id, $user_id);
14890de3	379	$query = $this->executeQuery($sql_action, $params_action);
	380	}
	381
f14807de NL	382	public function archiveAll($user_id) {
	383	$sql_action = "UPDATE entries SET is_read=? WHERE user_id=? AND is_read=?";
	384	$params_action = array($user_id, 1, 0);
	385	$query = $this->executeQuery($sql_action, $params_action);
	386	}
	387
b916bcfc NL	388	public function getLastId($column = '') {
b916bcfc NL	389	return $this->getHandle()->lastInsertId($column);
14890de3	390	}
5bea1a4d	391
b89d5a2b	392	public function retrieveAllTags($user_id) {
e83cf5a7	393	$sql = "SELECT DISTINCT tags.* FROM tags
b89d5a2b NL	394	LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
b89d5a2b NL	395	LEFT JOIN entries ON tags_entries.entry_id=entries.id
53e3158d	396	WHERE entries.content <> '' AND entries.user_id=?";
b89d5a2b	397	$query = $this->executeQuery($sql, array($user_id));
2e2ebe5e NL	398	$tags = $query->fetchAll();
	399
	400	return $tags;
	401	}
	402
b89d5a2b	403	public function retrieveTag($id, $user_id) {
4886ed6d	404	$tag = NULL;
e83cf5a7	405	$sql = "SELECT DISTINCT tags.* FROM tags
b89d5a2b NL	406	LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
b89d5a2b NL	407	LEFT JOIN entries ON tags_entries.entry_id=entries.id
53e3158d	408	WHERE entries.content <> '' AND tags.id=? AND entries.user_id=?";
b89d5a2b	409	$params = array(intval($id), $user_id);
4886ed6d NL	410	$query = $this->executeQuery($sql, $params);
	411	$tag = $query->fetchAll();
	412
	413	return isset($tag[0]) ? $tag[0] : null;
	414	}
	415
b89d5a2b	416	public function retrieveEntriesByTag($tag_id, $user_id) {
4886ed6d	417	$sql =
9de34d4e	418	"SELECT entries.* FROM entries
4886ed6d	419	LEFT JOIN tags_entries ON tags_entries.entry_id=entries.id
53e3158d NL	420	WHERE entries.content <> '' AND
53e3158d NL	421	tags_entries.tag_id = ? AND entries.user_id=?";
b89d5a2b	422	$query = $this->executeQuery($sql, array($tag_id, $user_id));
4886ed6d NL	423	$entries = $query->fetchAll();
	424
	425	return $entries;
	426	}
	427
5bea1a4d	428	public function retrieveTagsByEntry($entry_id) {
2e2ebe5e	429	$sql =
9de34d4e	430	"SELECT tags.* FROM tags
5bea1a4d NL	431	LEFT JOIN tags_entries ON tags_entries.tag_id=tags.id
5bea1a4d NL	432	WHERE tags_entries.entry_id = ?";
2e2ebe5e NL	433	$query = $this->executeQuery($sql, array($entry_id));
2e2ebe5e NL	434	$tags = $query->fetchAll();
5bea1a4d NL	435
	436	return $tags;
	437	}
c432fa16 NL	438
	439	public function removeTagForEntry($entry_id, $tag_id) {
	440	$sql_action = "DELETE FROM tags_entries WHERE tag_id=? AND entry_id=?";
	441	$params_action = array($tag_id, $entry_id);
	442	$query = $this->executeQuery($sql_action, $params_action);
	443	return $query;
	444	}
	445
	446	public function retrieveTagByValue($value) {
	447	$tag = NULL;
	448	$sql = "SELECT * FROM tags WHERE value=?";
	449	$params = array($value);
	450	$query = $this->executeQuery($sql, $params);
	451	$tag = $query->fetchAll();
	452
	453	return isset($tag[0]) ? $tag[0] : null;
	454	}
	455
	456	public function createTag($value) {
	457	$sql_action = 'INSERT INTO tags ( value ) VALUES (?)';
	458	$params_action = array($value);
	459	$query = $this->executeQuery($sql_action, $params_action);
	460	return $query;
	461	}
	462
	463	public function setTagToEntry($tag_id, $entry_id) {
	464	$sql_action = 'INSERT INTO tags_entries ( tag_id, entry_id ) VALUES (?, ?)';
	465	$params_action = array($tag_id, $entry_id);
	466	$query = $this->executeQuery($sql_action, $params_action);
	467	return $query;
	468	}
032e0ca1 MR	469
	470	private function getEntriesOrder() {
	471	if (isset($_SESSION['sort']) and array_key_exists($_SESSION['sort'], $this->order)) {
	472	return $this->order[$_SESSION['sort']];
	473	}
	474	else {
	475	return $this->order['default'];
	476	}
	477	}
	478
2cd263e0	479	}