4 $DB_CONNECTION = false;
6 // 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
7 function gwvp_dbCreateMysqlStructure()
12 function gwvp_dbCreateSQLiteStructure($dbloc)
15 CREATE TABLE "users" (
16 "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
17 "user_full_name" TEXT,
27 "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
29 "groups_description" TEXT,
30 "groups_is_admin" INTEGER,
31 "groups_owner_id" INTEGER
35 CREATE TABLE "repos" (
36 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
38 "repos_description" TEXT,
42 // this looks like null, <repoid>, <read|visible|write>, user:<uid>|group:<gid>|authed|anon
43 // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc)
44 // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo
45 // TODO: is this sufficient? i have to think about it
47 CREATE TABLE "repoperms" (
48 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
50 "repoperms_type" TEXT,
55 CREATE TABLE "config" (
61 CREATE TABLE "group_membership" (
62 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
63 "groupmember_groupid" INTEGER,
64 "groupmember_userid" INTEGER
68 $DB_CONNECTION = new PDO("sqlite:$dbloc");
69 } catch(PDOException $exep) {
70 error_log("execpt on db open");
75 $DB_CONNECTION->query($usersql);
76 $DB_CONNECTION->query($groupsql);
77 $DB_CONNECTION->query($reposql);
78 $DB_CONNECTION->query($configsql);
79 $DB_CONNECTION->query($groupmemsql);
82 function gwvp_GetUserStatus($userid)
84 $conn = gwvp_ConnectDB();
86 $sql = "select user_status from users where users_id='$userid'";
88 $res = $conn->query($sql);
91 foreach($res as $val) {
92 $spl = explode(";", $val);
94 $return["statusid"] = $spl[0];
95 $return["extstatus"] = $spl[1];
100 function gwvp_SetUserStatus($userid, $status, $extendedstatus=null)
106 * 2 - password locked
107 * 3 - awaiting registration completion
108 * 4 - awaiting password reset
109 * where use status = 3,4 the key for unlock is set as the extended status
110 * i.e. if a user goes thru registration, when the validation email gets to
111 * them they'll have a key in their email (128 or 256 bit), thats what
112 * the extended status field is used for
115 $conn = gwvp_ConnectDB();
117 if($extendedstatus != null) {
118 $sql = "update users set user_status='$status;$extendedstatus' where users_id='$userid'";
120 $sql = "update users set user_status='$status;0' where users_id='$userid'";
123 return $conn->query($sql);
127 function gwvp_forceDisconnect()
130 global $DB_CONNECTION;
132 $DB_CONNECTION = false;
136 function gwvp_getConfigVal($confname)
140 CREATE TABLE "config" (
147 $conn = gwvp_ConnectDB();
149 $sql = "select * from config where config_name='$confname'";
151 $res = $conn->query($sql);
154 foreach($res as $val) {
160 function gwvp_eraseConfigVal($confname)
164 CREATE TABLE "config" (
171 $conn = gwvp_ConnectDB();
173 $sql = "delete from config where config_name='$confname'";
175 return $conn->query($sql);
178 function gwvp_setConfigVal($confname, $confval)
182 CREATE TABLE "config" (
188 gwvp_eraseConfigVal($confname);
190 $conn = gwvp_ConnectDB();
192 $sql = "insert into config values('$confname', '$confval')";
194 return $conn->query($sql);
198 function gwvp_isDBSetup()
200 // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay
201 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_url;
203 if($db_type == "sqlite") {
204 if(file_exists($db_url)) return true;
208 // TODO now for the connectables
212 function gwvp_ConnectDB()
214 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION;
216 // first check if $DB_CONNECTION IS live
217 if($DB_CONNECTION != false) return $DB_CONNECTION;
219 if($db_type == "sqlite") {
221 if(!file_exists($db_name)) {
222 error_log("$db_name does not exist - problem");
226 // and here we go with pdo.
227 error_log("attmpting to open db, $db_type:$db_url");
229 $DB_CONNECTION = new PDO("$db_type:$db_url");
230 } catch(PDOException $exep) {
231 error_log("execpt on db open");
235 return $DB_CONNECTION;
238 // TODO: we have to define what "Status" is
239 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
241 $conn = gwvp_ConnectDB();
243 // TODO: change from sha1
244 $shapass = sha1($password);
245 //error_log("Create user called with $email");
246 $sql = "insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')";
247 error_log("Creating user, $sql");
248 return $conn->query($sql);
250 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
251 "user_full_name" TEXT,
252 "user_password" TEXT,
253 "user_username" TEXT,
256 "user_status" INTEGER
261 function gwvp_getUser($username=null, $email=null, $id=null)
263 $conn = gwvp_ConnectDB();
265 if($username != null) {
266 $res = $conn->query("select * from users where user_username='$username'");
267 } else if($email != null) {
268 $res = $conn->query("select * from users where user_email='$email'");
269 } else if($id != null) {
270 $res = $conn->query("select * from users where users_id='$id'");
274 foreach($res as $u_res) {
275 $returns["id"] = $u_res["users_id"];
276 $returns["fullname"] = $u_res["user_full_name"];
277 $returns["password"] = $u_res["user_password"];
278 $returns["username"] = $u_res["user_username"];
279 $returns["email"] = $u_res["user_email"];
280 $returns["desc"] = $u_res["user_desc"];
281 $returns["status"] = $u_res["user_status"];
288 function gwvp_getOwnedRepos($userid = null, $username = null)
290 $conn = gwvp_ConnectDB();
292 if($username != null) {
293 $details = gwvp_getUser($username);
294 $uid = $details["id"];
295 $sql = "select * from repos where repos_owner='$uid'";
296 $res = $conn->query($sql);
297 error_log("sql: $sql");
298 } else if($userid != null) {
299 $sql = "select * from repos where repos_owner='$userid'";
300 $res = $conn->query($sql);
301 error_log("sql: $sql");
305 * CREATE TABLE "repos" (
306 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
308 "repos_description" TEXT,
309 "repos_owner" INTEGER
316 foreach($res as $u_res) {
317 $returns[$rn]["id"] = $u_res["repos_id"];
318 $returns[$rn]["name"] = $u_res["repos_name"];
319 $returns[$rn]["description"] = $u_res["repos_description"];
326 function gwvp_getUsers()
328 $conn = gwvp_ConnectDB();
330 $res = $conn->query("select * from users");
334 foreach($res as $u_res) {
335 $returns[$rn]["id"] = $u_res["users_id"];
336 $returns[$rn]["fullname"] = $u_res["user_full_name"];
337 $returns[$rn]["password"] = $u_res["user_password"];
338 $returns[$rn]["username"] = $u_res["user_username"];
339 $returns[$rn]["email"] = $u_res["user_email"];
340 $returns[$rn]["desc"] = $u_res["user_desc"];
341 $returns[$rn]["status"] = $u_res["user_status"];
348 function gwvp_deleteUser($email)
350 $conn = gwvp_ConnectDB();
352 $sql = "delete from users where user_email='$email'";
357 function gwvp_createGroup($group_name, $group_desc, $is_admin, $owner_id)
359 $conn = gwvp_ConnectDB();
362 * CREATE TABLE groups (
365 "groups_is_admin" INTEGER,
366 "groups_owner_id" INTEGER
375 $sql = "insert into groups values( null, '$group_name', '$group_desc', '$is_admin_t', '$owner_id')";
382 function gwvp_deleteGroup($groupname)
384 $conn = gwvp_ConnectDB();
386 $sql = "delete from groups where groups_name='$groupname'";
391 function gwvp_getGroupsForUser($email)
393 $conn = gwvp_ConnectDB();
396 select g.groups_name from
397 group_membership gm, groups g, users u
399 gm.groupmember_userid=u.users_id and
400 u.user_email='$email' and
401 gm.groupmember_groupid=g.groups_id and
402 g.groups_name='$groupname'
406 select g.groups_name from
407 group_membership gm, groups g, users u
409 gm.groupmember_userid=u.users_id and
410 u.user_email='$email' and
411 gm.groupmember_groupid=g.groups_id
414 $res = $conn->query($sql);
418 foreach($res as $u_res) {
419 $return[$rn] = $u_res[0];
426 function gwvp_getGroupsOwnedByUser($email)
428 $conn = gwvp_ConnectDB();
431 select g.groups_name from
432 group_membership gm, groups g, users u
434 gm.groupmember_userid=u.users_id and
435 u.user_email='$email' and
436 gm.groupmember_groupid=g.groups_id and
437 g.groups_name='$groupname'
441 select g.groups_name from
444 u.user_email='$email' and
445 u.users_id=g.groups_owner_id
448 $res = $conn->query($sql);
452 foreach($res as $u_res) {
453 $return[$rn] = $u_res[0];
461 function gwvp_groupOwner($groupname)
463 $conn = gwvp_ConnectDB();
465 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
467 $res = $conn->query($sql);
469 foreach($res as $u_res) {
476 function gwvp_getGroups()
478 $conn = gwvp_ConnectDB();
480 $res = $conn->query("select * from groups");
484 foreach($res as $u_res) {
485 $returns[$rn]["id"] = $u_res["groups_id"];
486 $returns[$rn]["name"] = $u_res["groups_name"];
487 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
488 else $return[$rn]["admin"] = false;
489 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
490 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
497 function gwvp_getGroupId($groupname)
499 $conn = gwvp_ConnectDB();
501 $sql = "select groups_id from groups where groups_name='$groupname'";
503 $res = $conn->query($sql);
505 foreach($res as $u_res) {
506 $return = $u_res["groups_id"];
512 function gwvp_getGroup($gid = null, $gname = null)
516 CREATE TABLE groups (
517 "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
519 "groups_is_admin" INTEGER,
520 "groups_owner_id" INTEGER
524 $conn = gwvp_ConnectDB();
527 $sql = "select * from groups where groups_id='$gid'";
528 } else if ($gname != null) {
529 $sql = "select * from groups where groups_name='$gname'";
532 $res = $conn->query($sql);
534 foreach($res as $u_res) {
535 $return["id"] = $u_res["groups_id"];
536 $return["name"] = $u_res["groups_name"];
537 if($u_res["groups_is_admin"] == 1) {
538 $return["isadmin"] = true;
540 $return["isadmin"] = false;
542 $return["ownerid"] = $u_res["groups_owner_id"];
543 $return["description"] = $u_res["groups_description"];
549 function gwvp_getUserId($useremail=null, $username = null)
551 $conn = gwvp_ConnectDB();
553 if($useremail != null) {
554 $sql = "select users_id from users where user_email='$useremail'";
555 } else if($username != null) {
556 $sql = "select users_id from users where user_username='$username'";
559 $res = $conn->query($sql);
561 foreach($res as $u_res) {
562 $return = $u_res["users_id"];
568 function gwvp_getUserName($id = null, $email=null)
570 $conn = gwvp_ConnectDB();
573 $sql = "select user_username from users where user_email='$email'";
574 } else if($id != null) {
575 $sql = "select user_username from users where users_id='$id'";
578 $res = $conn->query($sql);
580 foreach($res as $u_res) {
581 $return = $u_res["user_username"];
588 function gwvp_getUserEmail($id)
590 $conn = gwvp_ConnectDB();
592 $sql = "select user_email from users where users_id='$id'";
594 $res = $conn->query($sql);
596 foreach($res as $u_res) {
597 $return = $u_res["user_email"];
603 function gwvp_deleteGroupMemberByID($uid, $gid)
605 $conn = gwvp_ConnectDB();
608 * CREATE TABLE "group_membership" (
609 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
610 "groupmember_groupid" INTEGER,
611 "groupmember_userid" INTEGER
614 $sql = "delete from group_membership where groupmember_groupid='$gid' and groupmember_userid='$uid'";
622 function gwvp_addGroupMemberByID($uid, $gid)
624 $conn = gwvp_ConnectDB();
627 * CREATE TABLE "group_membership" (
628 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
629 "groupmember_groupid" INTEGER,
630 "groupmember_userid" INTEGER
633 $sql = "insert into group_membership values (null, '$gid', '$uid')";
641 function gwvp_addGroupMember($email, $groupname)
643 $conn = gwvp_ConnectDB();
645 $uid = gwvp_getUserId($email);
646 $gid = gwvp_getGroupId($groupname);
649 * CREATE TABLE "group_membership" (
650 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
651 "groupmember_groupid" INTEGER,
652 "groupmember_userid" INTEGER
655 if($uid!=false&&$gid!=false) gwvp_addGroupMemberByID($uid, $gid);
663 function gwvp_IsGroupMember($email, $groupname)
665 $conn = gwvp_ConnectDB();
667 // i think this is right
670 group_membership gm, groups g, users u
672 gm.groupmember_userid=u.users_id and
673 u.user_email='$email' and
674 gm.groupmember_groupid=g.groups_id and
675 g.groups_name='$groupname'
678 $res = $conn->query($sql);
680 foreach($res as $u_res) {
684 if($result == 0) return false;
685 if($result == 1) return true;
688 function gwvp_IsUserAdmin($email=null, $username = null)
690 $conn = gwvp_ConnectDB();
693 // TODO: clean this up, this should be a single query - idiot
695 $id = gwvp_getUserId($email);
696 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
697 } else if($username != null) {
698 $id = gwvp_getUserId(null, $username);
699 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
702 $res = $conn->query($sql);
705 foreach($res as $u_res) {
706 $gid[$rn] = $u_res["groupmember_groupid"];
710 if($gid !== false) foreach($gid as $gid_t) {
712 * CREATE TABLE groups (
715 "groups_is_admin" INTEGER,
716 "groups_owner_id" INTEGER
721 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
722 $res = $conn->query($sql);
723 foreach($res as $u_res) {
724 if($u_res["groups_is_admin"] == "1") return true;
731 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
734 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
735 "user_full_name" TEXT,
736 "user_password" TEXT,
737 "user_username" TEXT,
740 "user_status" INTEGER
744 $conn = gwvp_ConnectDB();
747 $sql = "update users set user_email='$email' where users_id='$userid'";
751 if($fullname != null) {
752 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
756 if($password != null) {
757 $shapass = sha1($password);
758 $sql = "update users set user_password='$shapass' where users_id='$userid'";
762 if($username != null) {
763 $sql = "update users set user_username='$username' where users_id='$userid'";
768 $sql = "update users set user_desc='$desc' where users_id='$userid'";
772 if($status != null) {
773 $sql = "update users set user_status='$status' where users_id='$userid'";
781 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
784 * CREATE TABLE groups (
787 "groups_is_admin" INTEGER,
788 "groups_owner_id" INTEGER
792 $conn = gwvp_ConnectDB();
794 if($groupname != null) {
795 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
799 if($group_is_admin != null) {
800 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
804 if($groups_owner_id != null) {
805 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
812 function gwvp_AddRepo($reponame, $repodesc, $repoowner)
814 $conn = gwvp_ConnectDB();
816 $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')";
821 function gwvp_GetRepoList()
823 $conn = gwvp_ConnectDB();
827 CREATE TABLE "repos" (
828 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
830 "repos_description" TEXT,
831 "repos_owner" INTEGER
836 $sql = "select * from repos";
838 $res = $conn->query($sql);
842 foreach($res as $u_res) {
843 $return[$rn]["id"] = $u_res["repos_id"];
844 $return[$rn]["name"] = $u_res["repos_name"];
845 $return[$rn]["description"] = $u_res["repos_description"];
846 $return[$rn]["owner"] = $u_res["repos_owner"];
852 /* functions we'll need to access data:
855 * getUserData(username)
857 * getGroupData(groupname)
859 * addGroupMember(...)
860 * deleteGroupMember(...)