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