[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;

    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 retrieveAll($user_id) {
        $sql        = "SELECT * FROM entries WHERE 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 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 = '') {
        switch ($_SESSION['sort'])
        {
            case 'ia':
                $order = 'ORDER BY id';
                break;
            case 'id':
                $order = 'ORDER BY id DESC';
                break;
            case 'ta':
                $order = 'ORDER BY lower(title)';
                break;
            case 'td':
                $order = 'ORDER BY lower(title) DESC';
                break;
            default:
                $order = 'ORDER BY id';
                break;
        }

        switch ($view)
        {
            case 'archive':
                $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order;
                $params = array($user_id, 1);
                break;
            case 'fav' :
                $sql    = "SELECT * FROM entries WHERE user_id=? AND is_fav=? " . $order;
                $params = array($user_id, 1);
                break;
            default:
                $sql    = "SELECT * FROM entries WHERE user_id=? AND is_read=? " . $order;
                $params = array($user_id, 0);
                break;
        }

        $sql .= ' ' . $limit;

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

        return $entries;
    }

    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 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.user_id=?";
        $query = $this->executeQuery($sql, array($user_id));
        $tags = $query->fetchAll();

        return $tags;
    }

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