X-Git-Url: http://git.pjr.cc/?p=gwvp.git;a=blobdiff_plain;f=gwvplib%2Fgwvpdatabase.php;h=7338e686eab0efd8cc6ffa9991ce6af51b0439d4;hp=267a6e099cb8dd000160cdddccc16aee5bf9be12;hb=5e67e4fe3908ac2cfffced7f462e4f373d1cdbf1;hpb=d6d341a5742ed8021fa768c924846e04d46156e1 diff --git a/gwvplib/gwvpdatabase.php b/gwvplib/gwvpdatabase.php index 267a6e0..7338e68 100644 --- a/gwvplib/gwvpdatabase.php +++ b/gwvplib/gwvpdatabase.php @@ -6,7 +6,7 @@ $DB_CONNECTION = false; // i need to figure out how i do data encapsulation here. We'll support mysql and sqlite3 off the bat if we can - sqlite3 comes first tho function gwvp_dbCreateMysqlStructure() { - + } function gwvp_dbCreateSQLiteStructure($dbloc) @@ -21,15 +21,16 @@ function gwvp_dbCreateSQLiteStructure($dbloc) "user_desc" TEXT, "user_status" INTEGER )'; - + $groupsql = ' CREATE TABLE groups ( "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT, "groups_name" TEXT, + "groups_description" TEXT, "groups_is_admin" INTEGER, "groups_owner_id" INTEGER )'; - + $reposql = ' CREATE TABLE "repos" ( "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT, @@ -37,28 +38,40 @@ function gwvp_dbCreateSQLiteStructure($dbloc) "repos_description" TEXT, "repos_owner" INTEGER )'; - + + // this looks like null, , , user:|group:|authed|anon + // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc) + // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo + // TODO: is this sufficient? i have to think about it + $repoperms = ' + CREATE TABLE "repoperms" ( + "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT, + "repo_id" INTEGER, + "repoperms_type" TEXT, + "repoperms_ref" TEXT + )'; + $configsql = ' CREATE TABLE "config" ( "config_name" TEXT, "config_value" TEXT )'; - + $groupmemsql = ' CREATE TABLE "group_membership" ( "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT, "groupmember_groupid" INTEGER, "groupmember_userid" INTEGER )'; - + try { $DB_CONNECTION = new PDO("sqlite:$dbloc"); } catch(PDOException $exep) { error_log("execpt on db open"); return false; } - - + + $DB_CONNECTION->query($usersql); $DB_CONNECTION->query($groupsql); $DB_CONNECTION->query($reposql); @@ -66,16 +79,87 @@ function gwvp_dbCreateSQLiteStructure($dbloc) $DB_CONNECTION->query($groupmemsql); } +function gwvp_forceDisconnect() +{ + + global $DB_CONNECTION; + + $DB_CONNECTION = false; +} + + +function gwvp_getConfigVal($confname) +{ + /* + * $configsql = ' + CREATE TABLE "config" ( + "config_name" TEXT, + "config_value" TEXT + )'; + + */ + + $conn = gwvp_ConnectDB(); + + $sql = "select * from config where config_name='$confname'"; + + $res = $conn->query($sql); + + $return = null; + foreach($res as $val) { + $return = $val; + } + +} + +function gwvp_eraseConfigVal($confname) +{ + /* + * $configsql = ' + CREATE TABLE "config" ( + "config_name" TEXT, + "config_value" TEXT + )'; + + */ + + $conn = gwvp_ConnectDB(); + + $sql = "delete from config where config_name='$confname'"; + + return $conn->query($sql); +} + +function gwvp_setConfigVal($confname, $confval) +{ + /* + * $configsql = ' + CREATE TABLE "config" ( + "config_name" TEXT, + "config_value" TEXT + )'; + + */ + gwvp_eraseConfigVal($confname); + + $conn = gwvp_ConnectDB(); + + $sql = "insert into config values('$confname', '$confval')"; + + return $conn->query($sql); +} + + function gwvp_isDBSetup() { // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_url; - + if($db_type == "sqlite") { if(file_exists($db_url)) return true; else return false; } - + // TODO now for the connectables // gwvp_ConnectDB(); } @@ -83,17 +167,17 @@ function gwvp_isDBSetup() function gwvp_ConnectDB() { global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION; - + // first check if $DB_CONNECTION IS live if($DB_CONNECTION != false) return $DB_CONNECTION; - + if($db_type == "sqlite") { $db_url = $db_name; if(!file_exists($db_name)) { error_log("$db_name does not exist - problem"); - } + } } - + // and here we go with pdo. error_log("attmpting to open db, $db_type:$db_url"); try { @@ -102,36 +186,66 @@ function gwvp_ConnectDB() error_log("execpt on db open"); return false; } - + return $DB_CONNECTION; } +// TODO: we have to define what "Status" is function gwvp_createUser($email, $fullname, $password, $username, $desc, $status) { $conn = gwvp_ConnectDB(); - + // TODO: change from sha1 $shapass = sha1($password); //error_log("Create user called with $email"); - $conn->query("insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')"); + $sql = "insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')"; + error_log("Creating user, $sql"); + return $conn->query($sql); /* * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT, - "user_full_name" TEXT, - "user_password" TEXT, - "user_username" TEXT, - "user_email" TEXT, - "user_desc" TEXT, - "user_status" INTEGER + "user_full_name" TEXT, + "user_password" TEXT, + "user_username" TEXT, + "user_email" TEXT, + "user_desc" TEXT, + "user_status" INTEGER */ } +function gwvp_getUser($username=null, $email=null, $id=null) +{ + $conn = gwvp_ConnectDB(); + + if($username != null) { + $res = $conn->query("select * from users where user_username='$username'"); + } else if($email != null) { + $res = $conn->query("select * from users where user_email='$email'"); + } else if($id != null) { + $res = $conn->query("select * from users where users_id='$id'"); + } else return false; + + $returns = false; + foreach($res as $u_res) { + $returns["id"] = $u_res["users_id"]; + $returns["fullname"] = $u_res["user_full_name"]; + $returns["password"] = $u_res["user_password"]; + $returns["username"] = $u_res["user_username"]; + $returns["email"] = $u_res["user_email"]; + $returns["desc"] = $u_res["user_desc"]; + $returns["status"] = $u_res["user_status"]; + } + + return $returns; + +} + function gwvp_getUsers() { $conn = gwvp_ConnectDB(); - + $res = $conn->query("select * from users"); - + $returns = false; $rn = 0; foreach($res as $u_res) { @@ -144,28 +258,28 @@ function gwvp_getUsers() $returns[$rn]["status"] = $u_res["user_status"]; $rn++; } - + return $returns; } function gwvp_deleteUser($email) { $conn = gwvp_ConnectDB(); - + $sql = "delete from users where user_email='$email'"; - + $conn->query($sql); } -function gwvp_createGroup($group_name, $is_admin, $owner_id) +function gwvp_createGroup($group_name, $group_desc, $is_admin, $owner_id) { $conn = gwvp_ConnectDB(); - + /* * CREATE TABLE groups ( - "groups_id" INTEGER, - "groups_name" TEXT, - "groups_is_admin" INTEGER, + "groups_id" INTEGER, + "groups_name" TEXT, + "groups_is_admin" INTEGER, "groups_owner_id" INTEGER )'; @@ -175,18 +289,19 @@ function gwvp_createGroup($group_name, $is_admin, $owner_id) } else { $is_admin_t = 0; } - $sql = "insert into groups values( null, '$group_name', '$is_admin_t', '$owner_id')"; + $sql = "insert into groups values( null, '$group_name', '$group_desc', '$is_admin_t', '$owner_id')"; + $conn->query($sql); - + } function gwvp_deleteGroup($groupname) { $conn = gwvp_ConnectDB(); - + $sql = "delete from groups where groups_name='$groupname'"; - + $conn->query($sql); } @@ -195,15 +310,15 @@ function gwvp_getGroupsForUser($email) $conn = gwvp_ConnectDB(); /* - select g.groups_name from - group_membership gm, groups g, users u - where - gm.groupmember_userid=u.users_id and - u.user_email='$email' and - gm.groupmember_groupid=g.groups_id and - g.groups_name='$groupname' - */ - + select g.groups_name from + group_membership gm, groups g, users u + where + gm.groupmember_userid=u.users_id and + u.user_email='$email' and + gm.groupmember_groupid=g.groups_id and + g.groups_name='$groupname' + */ + $sql = " select g.groups_name from group_membership gm, groups g, users u @@ -212,16 +327,16 @@ function gwvp_getGroupsForUser($email) u.user_email='$email' and gm.groupmember_groupid=g.groups_id "; - + $res = $conn->query($sql); - + $return = false; $rn = 0; foreach($res as $u_res) { $return[$rn] = $u_res[0]; $rn++; } - + return $return; } @@ -230,15 +345,15 @@ function gwvp_getGroupsOwnedByUser($email) $conn = gwvp_ConnectDB(); /* - select g.groups_name from - group_membership gm, groups g, users u - where - gm.groupmember_userid=u.users_id and - u.user_email='$email' and - gm.groupmember_groupid=g.groups_id and - g.groups_name='$groupname' - */ - + select g.groups_name from + group_membership gm, groups g, users u + where + gm.groupmember_userid=u.users_id and + u.user_email='$email' and + gm.groupmember_groupid=g.groups_id and + g.groups_name='$groupname' + */ + $sql = " select g.groups_name from groups g, users u @@ -246,41 +361,41 @@ function gwvp_getGroupsOwnedByUser($email) u.user_email='$email' and u.users_id=g.groups_owner_id "; - + $res = $conn->query($sql); - + $return = false; $rn = 0; foreach($res as $u_res) { $return[$rn] = $u_res[0]; $rn++; } - + return $return; - + } function gwvp_groupOwner($groupname) { $conn = gwvp_ConnectDB(); - + $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id"; $res = $conn->query($sql); $return = false; foreach($res as $u_res) { - $return = $u_res[0]; + $return = $u_res[0]; } - + return $return; } function gwvp_getGroups() { $conn = gwvp_ConnectDB(); - + $res = $conn->query("select * from groups"); - + $returns = false; $rn = 0; foreach($res as $u_res) { @@ -292,81 +407,176 @@ function gwvp_getGroups() $returns[$rn]["ownerid"] = $u_res["groups_owner_id"]; $rn++; } - + return $returns; } function gwvp_getGroupId($groupname) { $conn = gwvp_ConnectDB(); - + $sql = "select groups_id from groups where groups_name='$groupname'"; - + $res = $conn->query($sql); $return = false; foreach($res as $u_res) { $return = $u_res["groups_id"]; } - + return $return; } -function gwvp_getUserId($useremail) +function gwvp_getGroup($gid) { + /* + * $groupsql = ' + CREATE TABLE groups ( + "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT, + "groups_name" TEXT, + "groups_is_admin" INTEGER, + "groups_owner_id" INTEGER + )'; + + */ $conn = gwvp_ConnectDB(); - $sql = "select users_id from users where user_email='$useremail'"; + $sql = "select * from groups where groups_id='$gid'"; $res = $conn->query($sql); $return = false; foreach($res as $u_res) { - $return = $u_res["users_id"]; + $return["id"] = $u_res["groups_id"]; + $return["name"] = $u_res["groups_name"]; + if($u_res["groups_is_admin"] == 1) { + $return["isadmin"] = true; + } else { + $return["isadmin"] = false; + } + $return["ownerid"] = $u_res["groups_owner_id"]; + $return["description"] = $u_res["groups_description"]; } return $return; } +function gwvp_getUserId($useremail=null, $username = null) +{ + $conn = gwvp_ConnectDB(); + + if($useremail != null) { + $sql = "select users_id from users where user_email='$useremail'"; + } else if($username != null) { + $sql = "select users_id from users where user_username='$username'"; + } else return false; + + $res = $conn->query($sql); + $return = false; + foreach($res as $u_res) { + $return = $u_res["users_id"]; + } + + return $return; +} + +function gwvp_getUserName($id = null, $email=null) +{ + $conn = gwvp_ConnectDB(); + + if($email != null) { + $sql = "select user_username from users where user_email='$email'"; + } else if($id != null) { + $sql = "select user_username from users where users_id='$id'"; + } else return false; + + $res = $conn->query($sql); + $return = false; + foreach($res as $u_res) { + $return = $u_res["user_username"]; + } + + return $return; +} + + function gwvp_getUserEmail($id) { $conn = gwvp_ConnectDB(); - + $sql = "select user_email from users where users_id='$id'"; - + $res = $conn->query($sql); $return = false; foreach($res as $u_res) { $return = $u_res["user_email"]; } - + return $return; } +function gwvp_deleteGroupMemberByID($uid, $gid) +{ + $conn = gwvp_ConnectDB(); + + /* + * CREATE TABLE "group_membership" ( + "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT, + "groupmember_groupid" INTEGER, + "groupmember_userid" INTEGER + + */ + $sql = "delete from group_membership where groupmember_groupid='$gid' and groupmember_userid='$uid'"; + + $conn->query($sql); + + return true; +} + + +function gwvp_addGroupMemberByID($uid, $gid) +{ + $conn = gwvp_ConnectDB(); + + /* + * CREATE TABLE "group_membership" ( + "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT, + "groupmember_groupid" INTEGER, + "groupmember_userid" INTEGER + + */ + $sql = "insert into group_membership values (null, '$gid', '$uid')"; + + $conn->query($sql); + + return true; +} + + function gwvp_addGroupMember($email, $groupname) { $conn = gwvp_ConnectDB(); - + $uid = gwvp_getUserId($email); $gid = gwvp_getGroupId($groupname); - + /* * CREATE TABLE "group_membership" ( - "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT, - "groupmember_groupid" INTEGER, - "groupmember_userid" INTEGER + "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT, + "groupmember_groupid" INTEGER, + "groupmember_userid" INTEGER */ - if($uid!=false&&$gid!=false) $sql = "insert into group_membership values (null, '$gid', '$uid')"; + if($uid!=false&&$gid!=false) gwvp_addGroupMemberByID($uid, $gid); else return false; - + $conn->query($sql); - + return true; } function gwvp_IsGroupMember($email, $groupname) { $conn = gwvp_ConnectDB(); - + // i think this is right $sql = " select count(*) from @@ -377,49 +587,57 @@ function gwvp_IsGroupMember($email, $groupname) gm.groupmember_groupid=g.groups_id and g.groups_name='$groupname' "; - + $res = $conn->query($sql); $result = 0; foreach($res as $u_res) { $result = $u_res[0]; } - + if($result == 0) return false; if($result == 1) return true; } -function gwvp_IsUserAdmin($email) +function gwvp_IsUserAdmin($email=null, $username = null) { $conn = gwvp_ConnectDB(); - - $id = gwvp_getUserId($email); - $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'"; - + + + // TODO: clean this up, this should be a single query - idiot + if($email != null) { + $id = gwvp_getUserId($email); + $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'"; + } else if($username != null) { + $id = gwvp_getUserId(null, $username); + $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'"; + } else return false; + $res = $conn->query($sql); $rn = 0; + $gid = false; foreach($res as $u_res) { - $gid[$rn] = $u_res["groupmember_groupid"]; + $gid[$rn] = $u_res["groupmember_groupid"]; $rn++; } - - foreach($gid as $gid_t) { - /* - * CREATE TABLE groups ( - "groups_id" INTEGER, - "groups_name" TEXT, - "groups_is_admin" INTEGER, - "groups_owner_id" INTEGER - )'; - */ - + if($gid !== false) foreach($gid as $gid_t) { + /* + * CREATE TABLE groups ( + "groups_id" INTEGER, + "groups_name" TEXT, + "groups_is_admin" INTEGER, + "groups_owner_id" INTEGER + )'; + + */ + $sql = "select groups_is_admin from groups where groups_id='$gid_t'"; $res = $conn->query($sql); foreach($res as $u_res) { if($u_res["groups_is_admin"] == "1") return true; - } + } } - + return false; } @@ -427,27 +645,27 @@ function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $ { /* * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT, - "user_full_name" TEXT, - "user_password" TEXT, - "user_username" TEXT, - "user_email" TEXT, - "user_desc" TEXT, - "user_status" INTEGER + "user_full_name" TEXT, + "user_password" TEXT, + "user_username" TEXT, + "user_email" TEXT, + "user_desc" TEXT, + "user_status" INTEGER */ - + $conn = gwvp_ConnectDB(); - + if($email != null) { $sql = "update users set user_email='$email' where users_id='$userid'"; $conn->query($sql); } - + if($fullname != null) { $sql = "update users set user_full_name='$fullname' where users_id='$userid'"; $conn->query($sql); } - + if($password != null) { $shapass = sha1($password); $sql = "update users set user_password='$shapass' where users_id='$userid'"; @@ -468,7 +686,7 @@ function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $ $sql = "update users set user_status='$status' where users_id='$userid'"; $conn->query($sql); } - + return true; } @@ -477,15 +695,15 @@ function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $ { /* * CREATE TABLE groups ( - "groups_id" INTEGER, - "groups_name" TEXT, - "groups_is_admin" INTEGER, + "groups_id" INTEGER, + "groups_name" TEXT, + "groups_is_admin" INTEGER, "groups_owner_id" INTEGER )'; */ $conn = gwvp_ConnectDB(); - + if($groupname != null) { $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'"; $conn->query($sql); @@ -495,16 +713,57 @@ function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $ $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'"; $conn->query($sql); } - + if($groups_owner_id != null) { $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'"; $conn->query($sql); } - + return true; } + +function gwvp_AddRepo($reponame, $repodesc, $repoowner) +{ + $conn = gwvp_ConnectDB(); + + $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')"; + + $conn->query($sql); +} + +function gwvp_GetRepoList() +{ + $conn = gwvp_ConnectDB(); + + /* + * $reposql = ' + CREATE TABLE "repos" ( + "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT, + "repos_name" TEXT, + "repos_description" TEXT, + "repos_owner" INTEGER + )'; + + */ + + $sql = "select * from repos"; + + $res = $conn->query($sql); + + $return = false; + $rn = 0; + foreach($res as $u_res) { + $return[$rn]["id"] = $u_res["repos_id"]; + $return[$rn]["name"] = $u_res["repos_name"]; + $return[$rn]["description"] = $u_res["repos_description"]; + $return[$rn]["owner"] = $u_res["repos_owner"]; + $rn++; + } + + return $return; +} /* functions we'll need to access data: - * + * * getUsers(pattern) * getUserData(username) * getGroups(pattern) @@ -512,7 +771,7 @@ function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $ * modifyGroup(...) * addGroupMember(...) * deleteGroupMember(...) - * + * * createUser(...) * deleteUser(...) * modifyUser(...)