static int create_group_common (CcnetGroupManager *mgr, const char *group_name, const char *user_name, GError **error) { CcnetDB *db = mgr->priv->db; gint64 now = get_current_time(); char sql[512]; int group_id = -1; char *user_name_l = g_ascii_strdown (user_name, -1); if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) snprintf (sql, sizeof(sql), "INSERT INTO \"Group\"(group_name, " "creator_name, timestamp) VALUES('%s', '%s', " "%"G_GINT64_FORMAT")", group_name, user_name_l, now); else snprintf (sql, sizeof(sql), "INSERT INTO `Group`(`group_name`, " "`creator_name`, `timestamp`) VALUES('%s', '%s', " "%"G_GINT64_FORMAT")", group_name, user_name_l, now); if (ccnet_db_query (db, sql) < 0) { g_set_error (error, CCNET_DOMAIN, 0, "Failed to create group"); goto out; } if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) snprintf (sql, sizeof(sql),"SELECT group_id FROM \"Group\" WHERE " "group_name = '%s' AND creator_name = '%s' AND" " timestamp = %"G_GINT64_FORMAT"", group_name, user_name_l, now); else snprintf (sql, sizeof(sql),"SELECT `group_id` FROM `Group` WHERE " "`group_name` = '%s' AND `creator_name` = '%s' AND" " `timestamp` = %"G_GINT64_FORMAT"", group_name, user_name_l, now); group_id = ccnet_db_get_int (db, sql); if (group_id < 0) { g_set_error (error, CCNET_DOMAIN, 0, "Failed to create group"); goto out; } snprintf (sql, sizeof(sql), "INSERT INTO GroupUser VALUES (%d, '%s', %d)", group_id, user_name_l, 1); if (ccnet_db_query (db, sql) < 0) { if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) snprintf (sql, sizeof(sql), "DELETE FROM \"Group\" WHERE group_id=%d", group_id); else snprintf (sql, sizeof(sql), "DELETE FROM `Group` WHERE group_id=%d", group_id); ccnet_db_query (db, sql); g_set_error (error, CCNET_DOMAIN, 0, "Failed to create group"); group_id = -1; goto out; } out: g_free (user_name_l); return group_id; }
GList* ccnet_group_manager_get_all_groups (CcnetGroupManager *mgr, int start, int limit, GError **error) { GList *ret = NULL; char sql[256]; if (ccnet_db_type(mgr->priv->db) == CCNET_DB_TYPE_PGSQL) { if (start == -1 && limit == -1) { snprintf (sql, sizeof(sql), "SELECT group_id, group_name, " "creator_name, timestamp FROM \"Group\""); } else { snprintf (sql, sizeof(sql), "SELECT group_id, group_name, " "creator_name, timestamp FROM \"Group\" " "ORDER BY group_id LIMIT %d OFFSET %d", limit, start); } } else { if (start == -1 && limit == -1) { snprintf (sql, sizeof(sql), "SELECT `group_id`, `group_name`, " "`creator_name`, `timestamp` FROM `Group`"); } else { snprintf (sql, sizeof(sql), "SELECT `group_id`, `group_name`, " "`creator_name`, `timestamp` FROM `Group` LIMIT %d, %d", start, limit); } } if (ccnet_db_foreach_selected_row (mgr->priv->db, sql, get_all_ccnetgroups_cb, &ret) < 0) return NULL; return g_list_reverse (ret); }
int ccnet_group_manager_remove_group (CcnetGroupManager *mgr, int group_id, const char *user_name, GError **error) { CcnetDB *db = mgr->priv->db; char sql[512]; /* No permission check here, since both group staff and seahub staff * can remove group. */ if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) snprintf (sql, sizeof(sql), "DELETE FROM \"Group\" WHERE group_id=%d", group_id); else snprintf (sql, sizeof(sql), "DELETE FROM `Group` WHERE group_id=%d", group_id); ccnet_db_query (db, sql); snprintf (sql, sizeof(sql), "DELETE FROM GroupUser WHERE group_id=%d", group_id); ccnet_db_query (db, sql); return 0; }
static int open_db (CcnetUserManager *manager) { CcnetDB *db = NULL; switch (ccnet_db_type(manager->session->db)) { /* To be compatible with the db file layout of 0.9.1 version, * we don't use conf-dir/ccnet.db for user and peer info, but * user conf-dir/PeerMgr/peermgr.db and conf-dir/PeerMgr/usermgr.db instead. */ case CCNET_DB_TYPE_SQLITE: db = open_sqlite_db (manager); break; case CCNET_DB_TYPE_PGSQL: case CCNET_DB_TYPE_MYSQL: db = manager->session->db; break; } if (!db) return -1; manager->priv->db = db; return check_db_table (db); }
static gboolean check_group_exists (CcnetDB *db, int group_id) { if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) return ccnet_db_statement_exists (db, "SELECT group_id FROM \"Group\" WHERE " "group_id=?", 1, "int", group_id); else return ccnet_db_statement_exists (db, "SELECT group_id FROM `Group` WHERE " "group_id=?", 1, "int", group_id); }
static gboolean check_group_exists (CcnetDB *db, int group_id) { char sql[512]; if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) snprintf (sql, sizeof(sql), "SELECT group_id FROM \"Group\" WHERE " "group_id=%d", group_id); else snprintf (sql, sizeof(sql), "SELECT group_id FROM `Group` WHERE " "group_id=%d", group_id); return ccnet_db_check_for_existence (db, sql); }
static int check_db_table (CcnetDB *db) { char *sql; int db_type = ccnet_db_type (db); if (db_type == CCNET_DB_TYPE_MYSQL) { sql = "CREATE TABLE IF NOT EXISTS EmailUser (" "id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, " "email VARCHAR(255), passwd CHAR(41), " "is_staff BOOL NOT NULL, is_active BOOL NOT NULL, " "ctime BIGINT, UNIQUE INDEX (email))" "ENGINE=INNODB"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE TABLE IF NOT EXISTS Binding (email VARCHAR(255), peer_id CHAR(41)," "UNIQUE INDEX (peer_id), INDEX (email(20)))" "ENGINE=INNODB"; if (ccnet_db_query (db, sql) < 0) return -1; } else if (db_type == CCNET_DB_TYPE_SQLITE) { sql = "CREATE TABLE IF NOT EXISTS EmailUser (" "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," "email TEXT, passwd TEXT, is_staff bool NOT NULL, " "is_active bool NOT NULL, ctime INTEGER)"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE UNIQUE INDEX IF NOT EXISTS email_index on EmailUser (email)"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE TABLE IF NOT EXISTS Binding (email TEXT, peer_id TEXT)"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE INDEX IF NOT EXISTS email_index on Binding (email)"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE UNIQUE INDEX IF NOT EXISTS peer_index on Binding (peer_id)"; if (ccnet_db_query (db, sql) < 0) return -1; } return 0; }
int ccnet_group_manager_set_group_creator (CcnetGroupManager *mgr, int group_id, const char *user_name) { CcnetDB *db = mgr->priv->db; char *sql; if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) { sql = "UPDATE \"Group\" SET creator_name = ? WHERE group_id = ?"; } else { sql = "UPDATE `Group` SET creator_name = ? WHERE group_id = ?"; } ccnet_db_statement_query (db, sql, 2, "string", user_name, "int", group_id); return 0; }
GList* ccnet_group_manager_get_all_groups (CcnetGroupManager *mgr, int start, int limit, GError **error) { CcnetDB *db = mgr->priv->db; GList *ret = NULL; int rc; if (ccnet_db_type(mgr->priv->db) == CCNET_DB_TYPE_PGSQL) { if (start == -1 && limit == -1) { rc = ccnet_db_statement_foreach_row (db, "SELECT group_id, group_name, " "creator_name, timestamp FROM \"Group\" " "ORDER BY timestamp DESC", get_all_ccnetgroups_cb, &ret, 0); } else { rc = ccnet_db_statement_foreach_row (db, "SELECT group_id, group_name, " "creator_name, timestamp FROM \"Group\" " "ORDER BY timestamp DESC LIMIT ? OFFSET ?", get_all_ccnetgroups_cb, &ret, 2, "int", limit, "int", start); } } else { if (start == -1 && limit == -1) { rc = ccnet_db_statement_foreach_row (db, "SELECT `group_id`, `group_name`, " "`creator_name`, `timestamp` FROM `Group` " "ORDER BY timestamp DESC", get_all_ccnetgroups_cb, &ret, 0); } else { rc = ccnet_db_statement_foreach_row (db, "SELECT `group_id`, `group_name`, " "`creator_name`, `timestamp` FROM `Group` " "ORDER BY timestamp DESC LIMIT ?, ?", get_all_ccnetgroups_cb, &ret, 2, "int", start, "int", limit); } } if (rc < 0) return NULL; return g_list_reverse (ret); }
CcnetGroup * ccnet_group_manager_get_group (CcnetGroupManager *mgr, int group_id, GError **error) { CcnetDB *db = mgr->priv->db; char *sql; CcnetGroup *ccnetgroup = NULL; if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) sql = "SELECT * FROM \"Group\" WHERE group_id = ?"; else sql = "SELECT * FROM `Group` WHERE group_id = ?"; if (ccnet_db_statement_foreach_row (db, sql, get_ccnetgroup_cb, &ccnetgroup, 1, "int", group_id) < 0) return NULL; return ccnetgroup; }
CcnetGroup * ccnet_group_manager_get_group (CcnetGroupManager *mgr, int group_id, GError **error) { CcnetDB *db = mgr->priv->db; char sql[512]; CcnetGroup *ccnetgroup = NULL; if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) snprintf (sql, sizeof(sql), "SELECT * FROM \"Group\" WHERE group_id = %d", group_id); else snprintf (sql, sizeof(sql), "SELECT * FROM `Group` WHERE group_id = %d", group_id); if (ccnet_db_foreach_selected_row (db, sql, get_ccnetgroup_cb, &ccnetgroup) < 0) return NULL; return ccnetgroup; }
static int open_db (CcnetGroupManager *manager) { CcnetDB *db = NULL; switch (ccnet_db_type(manager->session->db)) { case CCNET_DB_TYPE_SQLITE: db = open_sqlite_db (manager); break; case CCNET_DB_TYPE_PGSQL: case CCNET_DB_TYPE_MYSQL: db = manager->session->db; break; } if (!db) return -1; manager->priv->db = db; return check_db_table (db); }
int ccnet_group_manager_set_group_creator (CcnetGroupManager *mgr, int group_id, const char *user_name) { CcnetDB *db = mgr->priv->db; char sql[512]; if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) { snprintf (sql, sizeof(sql), "UPDATE \"Group\" SET creator_name = '%s' " "WHERE group_id = %d", user_name, group_id); } else { snprintf (sql, sizeof(sql), "UPDATE `Group` SET creator_name = '%s' " "WHERE group_id = %d", user_name, group_id); } ccnet_db_query (db, sql); return 0; }
int ccnet_group_manager_remove_group (CcnetGroupManager *mgr, int group_id, const char *user_name, GError **error) { CcnetDB *db = mgr->priv->db; char *sql; /* No permission check here, since both group staff and seahub staff * can remove group. */ if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) sql = "DELETE FROM \"Group\" WHERE group_id=?"; else sql = "DELETE FROM `Group` WHERE group_id=?"; ccnet_db_statement_query (db, sql, 1, "int", group_id); sql = "DELETE FROM GroupUser WHERE group_id=?"; ccnet_db_statement_query (db, sql, 1, "int", group_id); return 0; }
static int check_db_table (CcnetDB *db) { char *sql; int db_type = ccnet_db_type (db); if (db_type == CCNET_DB_TYPE_MYSQL) { sql = "CREATE TABLE IF NOT EXISTS `Group` (`group_id` INTEGER" " PRIMARY KEY AUTO_INCREMENT, `group_name` VARCHAR(255)," " `creator_name` VARCHAR(255), `timestamp` BIGINT)" "ENGINE=INNODB"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE TABLE IF NOT EXISTS `GroupUser` (`group_id` INTEGER," " `user_name` VARCHAR(255), `is_staff` tinyint, UNIQUE INDEX" " (`group_id`, `user_name`), INDEX (`user_name`))" "ENGINE=INNODB"; if (ccnet_db_query (db, sql) < 0) return -1; } else if (db_type == CCNET_DB_TYPE_SQLITE) { sql = "CREATE TABLE IF NOT EXISTS `Group` (`group_id` INTEGER" " PRIMARY KEY AUTOINCREMENT, `group_name` VARCHAR(255)," " `creator_name` VARCHAR(255), `timestamp` BIGINT)"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE TABLE IF NOT EXISTS `GroupUser` (`group_id` INTEGER, " "`user_name` VARCHAR(255), `is_staff` tinyint)"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE UNIQUE INDEX IF NOT EXISTS groupid_username_indx on " "`GroupUser` (`group_id`, `user_name`)"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE INDEX IF NOT EXISTS username_indx on " "`GroupUser` (`user_name`)"; if (ccnet_db_query (db, sql) < 0) return -1; } else if (db_type == CCNET_DB_TYPE_PGSQL) { sql = "CREATE TABLE IF NOT EXISTS \"Group\" (group_id SERIAL" " PRIMARY KEY, group_name VARCHAR(255)," " creator_name VARCHAR(255), timestamp BIGINT)"; if (ccnet_db_query (db, sql) < 0) return -1; sql = "CREATE TABLE IF NOT EXISTS GroupUser (group_id INTEGER," " user_name VARCHAR(255), is_staff smallint, UNIQUE " " (group_id, user_name))"; if (ccnet_db_query (db, sql) < 0) return -1; if (!pgsql_index_exists (db, "groupuser_username_idx")) { sql = "CREATE INDEX groupuser_username_idx ON GroupUser (user_name)"; if (ccnet_db_query (db, sql) < 0) return -1; } } return 0; }
static int create_group_common (CcnetGroupManager *mgr, const char *group_name, const char *user_name, GError **error) { CcnetDB *db = mgr->priv->db; gint64 now = get_current_time(); char *sql; int group_id = -1; char *user_name_l = g_ascii_strdown (user_name, -1); if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) sql = "INSERT INTO \"Group\"(group_name, " "creator_name, timestamp) VALUES(?, ?, ?)"; else sql = "INSERT INTO `Group`(group_name, " "creator_name, timestamp) VALUES(?, ?, ?)"; if (ccnet_db_statement_query (db, sql, 3, "string", group_name, "string", user_name_l, "int64", now) < 0) { g_set_error (error, CCNET_DOMAIN, 0, "Failed to create group"); goto out; } if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) sql = "SELECT group_id FROM \"Group\" WHERE " "group_name = ? AND creator_name = ? " "AND timestamp = ?"; else sql = "SELECT group_id FROM `Group` WHERE " "group_name = ? AND creator_name = ? " "AND timestamp = ?"; group_id = ccnet_db_statement_get_int (db, sql, 3, "string", group_name, "string", user_name_l, "int64", now); if (group_id < 0) { g_set_error (error, CCNET_DOMAIN, 0, "Failed to create group"); goto out; } sql = "INSERT INTO GroupUser VALUES (?, ?, ?)"; if (ccnet_db_statement_query (db, sql, 3, "int", group_id, "string", user_name_l, "int", 1) < 0) { if (ccnet_db_type(db) == CCNET_DB_TYPE_PGSQL) sql = "DELETE FROM \"Group\" WHERE group_id=?"; else sql = "DELETE FROM `Group` WHERE group_id=?"; ccnet_db_statement_query (db, sql, 1, "int", group_id); g_set_error (error, CCNET_DOMAIN, 0, "Failed to create group"); group_id = -1; goto out; } out: g_free (user_name_l); return group_id; }