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($repoperms);
79 $DB_CONNECTION->query($configsql);
80 $DB_CONNECTION->query($groupmemsql);
83 function gwvp_GetUserStatus($userid)
85 $conn = gwvp_ConnectDB();
87 $sql = "select user_status from users where users_id='$userid'";
89 $res = $conn->query($sql);
92 foreach($res as $val) {
93 $spl = explode(";", $val);
95 $return["statusid"] = $spl[0];
96 $return["extstatus"] = $spl[1];
101 function gwvp_SetUserStatus($userid, $status, $extendedstatus=null)
107 * 2 - password locked
108 * 3 - awaiting registration completion
109 * 4 - awaiting password reset
110 * where use status = 3,4 the key for unlock is set as the extended status
111 * i.e. if a user goes thru registration, when the validation email gets to
112 * them they'll have a key in their email (128 or 256 bit), thats what
113 * the extended status field is used for
116 $conn = gwvp_ConnectDB();
118 if($extendedstatus != null) {
119 $sql = "update users set user_status='$status;$extendedstatus' where users_id='$userid'";
121 $sql = "update users set user_status='$status;0' where users_id='$userid'";
124 return $conn->query($sql);
128 function gwvp_forceDisconnect()
131 global $DB_CONNECTION;
133 $DB_CONNECTION = false;
137 function gwvp_getConfigVal($confname)
141 CREATE TABLE "config" (
148 $conn = gwvp_ConnectDB();
150 $sql = "select config_value from config where config_name='$confname'";
152 $res = $conn->query($sql);
155 foreach($res as $val) {
156 $return = $val["config_value"];
162 function gwvp_eraseConfigVal($confname)
166 CREATE TABLE "config" (
173 $conn = gwvp_ConnectDB();
175 $sql = "delete from config where config_name='$confname'";
177 return $conn->query($sql);
180 function gwvp_setConfigVal($confname, $confval)
184 CREATE TABLE "config" (
190 gwvp_eraseConfigVal($confname);
192 $conn = gwvp_ConnectDB();
194 $sql = "insert into config values('$confname', '$confval')";
196 return $conn->query($sql);
200 function gwvp_isDBSetup()
202 // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay
203 global $WEB_ROOT_FS, $BASE_URL, $data_directory, $db_type, $db_url;
205 if($db_type == "sqlite") {
206 if(file_exists($db_url)) return true;
210 // TODO now for the connectables
214 function gwvp_ConnectDB()
216 global $WEB_ROOT_FS, $BASE_URL, $data_directory, $db_type, $db_name, $DB_CONNECTION;
218 // first check if $DB_CONNECTION IS live
219 if($DB_CONNECTION != false) return $DB_CONNECTION;
221 if($db_type == "sqlite") {
223 if(!file_exists($db_name)) {
224 error_log("$db_name does not exist - problem");
228 // and here we go with pdo.
229 error_log("attmpting to open db, $db_type:$db_url");
231 $DB_CONNECTION = new PDO("$db_type:$db_url");
232 } catch(PDOException $exep) {
233 error_log("execpt on db open");
237 return $DB_CONNECTION;
240 // TODO: we have to define what "Status" is
241 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
243 $conn = gwvp_ConnectDB();
245 // TODO: change from sha1
246 $shapass = sha1($password);
247 //error_log("Create user called with $email");
248 $sql = "insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')";
249 error_log("Creating user, $sql");
250 return $conn->query($sql);
252 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
253 "user_full_name" TEXT,
254 "user_password" TEXT,
255 "user_username" TEXT,
258 "user_status" INTEGER
263 function gwvp_getUser($username=null, $email=null, $id=null)
265 $conn = gwvp_ConnectDB();
267 if($username != null) {
268 $res = $conn->query("select * from users where user_username='$username'");
269 } else if($email != null) {
270 $res = $conn->query("select * from users where user_email='$email'");
271 } else if($id != null) {
272 $res = $conn->query("select * from users where users_id='$id'");
276 foreach($res as $u_res) {
277 $returns["id"] = $u_res["users_id"];
278 $returns["fullname"] = $u_res["user_full_name"];
279 $returns["password"] = $u_res["user_password"];
280 $returns["username"] = $u_res["user_username"];
281 $returns["email"] = $u_res["user_email"];
282 $returns["desc"] = $u_res["user_desc"];
283 $returns["status"] = $u_res["user_status"];
290 function gwvp_getRepoOwner($repoid)
292 $conn = gwvp_ConnectDB();
294 $sql = "select repos_owner from repos where repos_id='$repoid'";
296 $res = $conn->query($sql);
299 foreach($res as $rown) {
300 $return = $rown["repos_owner"];
305 function gwvp_getOwnedRepos($userid = null, $username = null)
307 $conn = gwvp_ConnectDB();
309 if($username != null) {
310 $details = gwvp_getUser($username);
311 $uid = $details["id"];
312 $sql = "select * from repos where repos_owner='$uid'";
313 $res = $conn->query($sql);
314 error_log("sql: $sql");
315 } else if($userid != null) {
316 $sql = "select * from repos where repos_owner='$userid'";
317 $res = $conn->query($sql);
318 error_log("sql: $sql");
322 * CREATE TABLE "repos" (
323 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
325 "repos_description" TEXT,
326 "repos_owner" INTEGER
333 foreach($res as $u_res) {
334 $returns[$rn]["id"] = $u_res["repos_id"];
335 $returns[$rn]["name"] = $u_res["repos_name"];
336 $returns[$rn]["description"] = $u_res["repos_description"];
343 function gwvp_getUsers()
345 $conn = gwvp_ConnectDB();
347 $res = $conn->query("select * from users");
351 foreach($res as $u_res) {
352 $returns[$rn]["id"] = $u_res["users_id"];
353 $returns[$rn]["fullname"] = $u_res["user_full_name"];
354 $returns[$rn]["password"] = $u_res["user_password"];
355 $returns[$rn]["username"] = $u_res["user_username"];
356 $returns[$rn]["email"] = $u_res["user_email"];
357 $returns[$rn]["desc"] = $u_res["user_desc"];
358 $returns[$rn]["status"] = $u_res["user_status"];
365 function gwvp_deleteUser($email)
367 $conn = gwvp_ConnectDB();
369 $sql = "delete from users where user_email='$email'";
374 function gwvp_createGroup($group_name, $group_desc, $is_admin, $owner_id)
376 $conn = gwvp_ConnectDB();
379 * CREATE TABLE groups (
382 "groups_is_admin" INTEGER,
383 "groups_owner_id" INTEGER
392 $sql = "insert into groups values( null, '$group_name', '$group_desc', '$is_admin_t', '$owner_id')";
397 // add the owner to the group
398 $gid = gwvp_getGroupId($group_name);
401 error_log("calling addgroupmember with $owner_id, $gid");
402 gwvp_addGroupMemberById($owner_id, $gid);
407 function gwvp_deleteGroup($groupname)
409 $conn = gwvp_ConnectDB();
411 $sql = "delete from groups where groups_name='$groupname'";
416 function gwvp_getGroupsForUser($email = null, $userid = null)
418 $conn = gwvp_ConnectDB();
421 select g.groups_name from
422 group_membership gm, groups g, users u
424 gm.groupmember_userid=u.users_id and
425 u.user_email='$email' and
426 gm.groupmember_groupid=g.groups_id and
427 g.groups_name='$groupname'
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
438 } else if($userid != null) {
440 select g.groups_name from
441 group_membership gm, groups g, users u
443 gm.groupmember_userid=u.users_id and
444 u.users_id='$userid' and
445 gm.groupmember_groupid=g.groups_id
449 $res = $conn->query($sql);
453 foreach($res as $u_res) {
454 $return[$rn] = $u_res[0];
461 function gwvp_getGroupsOwnedByUser($email)
463 $conn = gwvp_ConnectDB();
466 select g.groups_name from
467 group_membership gm, groups g, users u
469 gm.groupmember_userid=u.users_id and
470 u.user_email='$email' and
471 gm.groupmember_groupid=g.groups_id and
472 g.groups_name='$groupname'
476 select g.groups_name from
479 u.user_email='$email' and
480 u.users_id=g.groups_owner_id
483 $res = $conn->query($sql);
487 foreach($res as $u_res) {
488 $return[$rn] = $u_res[0];
496 function gwvp_groupOwner($groupname)
498 $conn = gwvp_ConnectDB();
500 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
502 $res = $conn->query($sql);
504 foreach($res as $u_res) {
511 function gwvp_getGroups()
513 $conn = gwvp_ConnectDB();
515 $res = $conn->query("select * from groups");
519 foreach($res as $u_res) {
520 $returns[$rn]["id"] = $u_res["groups_id"];
521 $returns[$rn]["name"] = $u_res["groups_name"];
522 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
523 else $return[$rn]["admin"] = false;
524 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
525 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
532 function gwvp_getGroupId($groupname)
534 $conn = gwvp_ConnectDB();
536 $sql = "select groups_id from groups where groups_name='$groupname'";
538 $res = $conn->query($sql);
540 foreach($res as $u_res) {
541 $return = $u_res["groups_id"];
547 function gwvp_getGroup($gid = null, $gname = null)
551 CREATE TABLE groups (
552 "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
554 "groups_is_admin" INTEGER,
555 "groups_owner_id" INTEGER
559 $conn = gwvp_ConnectDB();
562 $sql = "select * from groups where groups_id='$gid'";
563 } else if ($gname != null) {
564 $sql = "select * from groups where groups_name='$gname'";
567 $res = $conn->query($sql);
569 foreach($res as $u_res) {
570 $return["id"] = $u_res["groups_id"];
571 $return["name"] = $u_res["groups_name"];
572 if($u_res["groups_is_admin"] == 1) {
573 $return["isadmin"] = true;
575 $return["isadmin"] = false;
577 $return["ownerid"] = $u_res["groups_owner_id"];
578 $return["description"] = $u_res["groups_description"];
584 function gwvp_getUserId($useremail=null, $username = null)
586 $conn = gwvp_ConnectDB();
588 if($useremail != null) {
589 $sql = "select users_id from users where user_email='$useremail'";
590 } else if($username != null) {
591 $sql = "select users_id from users where user_username='$username'";
594 $res = $conn->query($sql);
596 foreach($res as $u_res) {
597 $return = $u_res["users_id"];
603 function gwvp_getUserName($id = null, $email=null)
605 $conn = gwvp_ConnectDB();
608 $sql = "select user_username from users where user_email='$email'";
609 } else if($id != null) {
610 $sql = "select user_username from users where users_id='$id'";
613 $res = $conn->query($sql);
615 foreach($res as $u_res) {
616 $return = $u_res["user_username"];
623 function gwvp_getUserEmail($id)
625 $conn = gwvp_ConnectDB();
627 $sql = "select user_email from users where users_id='$id'";
629 $res = $conn->query($sql);
631 foreach($res as $u_res) {
632 $return = $u_res["user_email"];
638 function gwvp_deleteGroupMemberByID($uid, $gid)
640 $conn = gwvp_ConnectDB();
643 * CREATE TABLE "group_membership" (
644 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
645 "groupmember_groupid" INTEGER,
646 "groupmember_userid" INTEGER
649 $sql = "delete from group_membership where groupmember_groupid='$gid' and groupmember_userid='$uid'";
657 function gwvp_addGroupMemberByID($uid, $gid)
659 $conn = gwvp_ConnectDB();
662 * CREATE TABLE "group_membership" (
663 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
664 "groupmember_groupid" INTEGER,
665 "groupmember_userid" INTEGER
668 $sql = "insert into group_membership values (null, '$gid', '$uid')";
676 function gwvp_addGroupMember($email, $groupname)
678 $conn = gwvp_ConnectDB();
680 $uid = gwvp_getUserId($email);
681 $gid = gwvp_getGroupId($groupname);
684 * CREATE TABLE "group_membership" (
685 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
686 "groupmember_groupid" INTEGER,
687 "groupmember_userid" INTEGER
690 if($uid!=false&&$gid!=false) gwvp_addGroupMemberByID($uid, $gid);
696 function gwvp_IsGroupMemberById($uid, $gid)
698 $conn = gwvp_ConnectDB();
700 $sql = "select count(*) from group_membership where groupmember_userid='$uid' and groupmember_groupid='$gid'";
702 $res = $conn->query($sql);
704 foreach($res as $u_res) {
708 if($result == 0) return false;
709 if($result == 1) return true;
712 function gwvp_IsGroupMember($email, $groupname)
714 $conn = gwvp_ConnectDB();
716 // i think this is right
719 group_membership gm, groups g, users u
721 gm.groupmember_userid=u.users_id and
722 u.user_email='$email' and
723 gm.groupmember_groupid=g.groups_id and
724 g.groups_name='$groupname'
727 $res = $conn->query($sql);
729 foreach($res as $u_res) {
733 if($result == 0) return false;
734 if($result == 1) return true;
737 function gwvp_IsGroupAdmin($groupname = null, $gid = null)
739 $conn = gwvp_ConnectDB();
741 if($groupname != null) {
742 $sql = "select groups_is_admin from groups where groups_name='$groupname'";
743 } else if($gid != null) {
744 $sql = "select groups_is_admin from groups where groups_id='$gid'";
747 $res = $conn->query($sql);
750 foreach($res as $u_res) {
751 if($u_res["groups_is_admin"] == "1") $return = true;
757 function gwvp_IsRepoOwner($userid, $repoid)
759 $conn = gwvp_ConnectDB();
761 $sql = "select repos_owner from repos where repos_id='$repoid'";
763 $res = $conn->query($sql);
766 foreach($res as $u_res) {
767 $return["owner"] = $u_res["repos_owner"];
770 if($return == false) return false;
771 if($return["owner"] == $userid) return true;
777 function gwvp_IsUserAdmin($email=null, $username = null, $userid = null)
779 $conn = gwvp_ConnectDB();
782 // TODO: clean this up, this should be a single query - idiot
784 $id = gwvp_getUserId($email);
785 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
786 } else if($userid != null) {
787 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$userid'";
788 } else if($username != null) {
789 $id = gwvp_getUserId(null, $username);
790 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
793 $res = $conn->query($sql);
796 foreach($res as $u_res) {
797 $gid[$rn] = $u_res["groupmember_groupid"];
801 if($gid !== false) foreach($gid as $gid_t) {
803 * CREATE TABLE groups (
806 "groups_is_admin" INTEGER,
807 "groups_owner_id" INTEGER
812 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
813 $res = $conn->query($sql);
814 foreach($res as $u_res) {
815 if($u_res["groups_is_admin"] == "1") return true;
822 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
825 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
826 "user_full_name" TEXT,
827 "user_password" TEXT,
828 "user_username" TEXT,
831 "user_status" INTEGER
835 $conn = gwvp_ConnectDB();
838 $sql = "update users set user_email='$email' where users_id='$userid'";
842 if($fullname != null) {
843 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
847 if($password != null) {
848 $shapass = sha1($password);
849 $sql = "update users set user_password='$shapass' where users_id='$userid'";
853 if($username != null) {
854 $sql = "update users set user_username='$username' where users_id='$userid'";
859 $sql = "update users set user_desc='$desc' where users_id='$userid'";
863 if($status != null) {
864 $sql = "update users set user_status='$status' where users_id='$userid'";
872 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
875 * CREATE TABLE groups (
878 "groups_is_admin" INTEGER,
879 "groups_owner_id" INTEGER
883 $conn = gwvp_ConnectDB();
885 if($groupname != null) {
886 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
890 if($group_is_admin != null) {
891 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
895 if($groups_owner_id != null) {
896 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
903 function gwvp_GetRepoId($reponame)
905 $conn = gwvp_ConnectDB();
907 $sql = "select repos_id from repos where repos_name='$reponame'";
909 $res = $conn->query($sql);
912 foreach($res as $u_res) {
913 $return = $u_res["repos_id"];
920 function gwvp_GetRepo($rid)
922 $conn = gwvp_ConnectDB();
924 $sql = "select * from repos where repos_id='$rid'";
926 $res = $conn->query($sql);
929 foreach($res as $u_res) {
930 $return["id"] = $u_res["repos_id"];
931 $return["name"] = $u_res["repos_name"];
932 $return["description"] = $u_res["repos_description"];
933 $return["owner"] = $u_res["repos_owner"];
939 function gwvp_GetRepoList()
941 $conn = gwvp_ConnectDB();
945 CREATE TABLE "repos" (
946 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
948 "repos_description" TEXT,
949 "repos_owner" INTEGER
954 $sql = "select * from repos";
956 $res = $conn->query($sql);
960 foreach($res as $u_res) {
961 $return[$rn]["id"] = $u_res["repos_id"];
962 $return[$rn]["name"] = $u_res["repos_name"];
963 $return[$rn]["description"] = $u_res["repos_description"];
964 $return[$rn]["owner"] = $u_res["repos_owner"];
971 function gwvp_AddRepo($reponame, $repodesc, $repoowner, $defaultperms = 0)
973 $conn = gwvp_ConnectDB();
975 $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')";
979 $sql = "select repos_id from repos where repos_name='$reponame'";
980 $res = $conn->query($sql);
982 foreach($res as $repos) {
983 $rid = $repos["repos_id"];
986 * CREATE TABLE "repoperms" (
987 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
989 "repoperms_type" TEXT,
996 // 0 - anyone can clone/read, only owner can write
997 // 1 - noone can clone/read, repo is visible (i.e. name), only owner can read/write repo
998 // 2 - only owner can see anything
1002 switch($defaultperms) {
1004 gwvp_addRepoPermission($rid, "visible", "anon");
1007 // by 2, we do nothing, owner already has full perms
1010 gwvp_addRepoPermission($rid, "read", "anon");
1015 function gwvp_getRepoPermissions($repoid)
1018 * // this looks like null, <repoid>, <read|visible|write>, user:<uid>|group:<gid>|authed|anon
1019 // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc)
1020 // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo
1021 // TODO: is this sufficient? i have to think about it
1023 CREATE TABLE "repoperms" (
1024 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
1026 "repoperms_type" TEXT,
1027 "repoperms_ref" TEXT
1031 $conn = gwvp_ConnectDB();
1033 $sql = "select * from repoperms where repo_id='$repoid'";
1035 $res = $conn->query($sql);
1039 foreach($res as $perm) {
1040 $returns[$rn]["id"] = $perm["repoperms_id"];
1041 $returns[$rn]["type"] = $perm["repoperms_type"];
1042 $returns[$rn]["ref"] = $perm["repoperms_ref"];
1049 function gwvp_removeRepoPermission($permid)
1051 $conn = gwvp_ConnectDB();
1053 $sql = "delete from repoperms where repoperms_id='$permid'";
1054 error_log("attempting: \"$sql\"");
1056 return $conn->query($sql);
1059 function gwvp_addRepoPermission($repoid, $permtype, $permref)
1061 $conn = gwvp_ConnectDB();
1063 error_log("PERMS: $repoid, $permtype, $permref");
1064 $sql = "insert into repoperms values(null, '$repoid', '$permtype', '$permref')";
1066 return $conn->query($sql);
1068 /* functions we'll need to access data:
1071 * getUserData(username)
1072 * getGroups(pattern)
1073 * getGroupData(groupname)
1075 * addGroupMember(...)
1076 * deleteGroupMember(...)