4 $DB_CONNECTION = false;
7 global $db_url, $db_type;
8 error_log("in include for database, $db_type, $db_name");
11 // 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
12 function gwvp_dbCreateMysqlStructure()
17 function gwvp_dbCreateSQLiteStructure($dbloc)
20 CREATE TABLE "users" (
21 "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
22 "user_full_name" TEXT,
32 "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
34 "groups_description" TEXT,
35 "groups_is_admin" INTEGER,
36 "groups_owner_id" INTEGER
40 CREATE TABLE "repos" (
41 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
43 "repos_description" TEXT,
47 // this looks like null, <repoid>, <read|visible|write>, user:<uid>|group:<gid>|authed|anon
48 // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc)
49 // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo
50 // TODO: is this sufficient? i have to think about it
52 CREATE TABLE "repoperms" (
53 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
55 "repoperms_type" TEXT,
60 CREATE TABLE "config" (
66 CREATE TABLE "group_membership" (
67 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
68 "groupmember_groupid" INTEGER,
69 "groupmember_userid" INTEGER
73 $DB_CONNECTION = new PDO("sqlite:$dbloc");
74 } catch(PDOException $exep) {
75 error_log("execpt on db open");
80 $DB_CONNECTION->query($usersql);
81 $DB_CONNECTION->query($groupsql);
82 $DB_CONNECTION->query($reposql);
83 $DB_CONNECTION->query($repoperms);
84 $DB_CONNECTION->query($configsql);
85 $DB_CONNECTION->query($groupmemsql);
88 function gwvp_GetUserStatus($userid)
90 $conn = gwvp_ConnectDB();
92 $sql = "select user_status from users where users_id='$userid'";
94 $res = $conn->query($sql);
97 foreach($res as $val) {
98 $spl = explode(";", $val);
100 $return["statusid"] = $spl[0];
101 $return["extstatus"] = $spl[1];
106 function gwvp_SetUserStatus($userid, $status, $extendedstatus=null)
112 * 2 - password locked
113 * 3 - awaiting registration completion
114 * 4 - awaiting password reset
115 * where use status = 3,4 the key for unlock is set as the extended status
116 * i.e. if a user goes thru registration, when the validation email gets to
117 * them they'll have a key in their email (128 or 256 bit), thats what
118 * the extended status field is used for
121 $conn = gwvp_ConnectDB();
123 if($extendedstatus != null) {
124 $sql = "update users set user_status='$status;$extendedstatus' where users_id='$userid'";
126 $sql = "update users set user_status='$status;0' where users_id='$userid'";
129 return $conn->query($sql);
133 function gwvp_forceDisconnect()
136 global $DB_CONNECTION;
138 $DB_CONNECTION = false;
142 function gwvp_getConfigVal($confname)
146 CREATE TABLE "config" (
153 $conn = gwvp_ConnectDB();
155 $sql = "select config_value from config where config_name='$confname'";
157 $res = $conn->query($sql);
160 foreach($res as $val) {
161 $return = $val["config_value"];
167 function gwvp_eraseConfigVal($confname)
171 CREATE TABLE "config" (
178 $conn = gwvp_ConnectDB();
180 $sql = "delete from config where config_name='$confname'";
182 return $conn->query($sql);
185 function gwvp_setConfigVal($confname, $confval)
189 CREATE TABLE "config" (
195 gwvp_eraseConfigVal($confname);
197 $conn = gwvp_ConnectDB();
199 $sql = "insert into config values('$confname', '$confval')";
201 return $conn->query($sql);
205 function gwvp_isDBSetup()
207 // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay
208 global $WEB_ROOT_FS, $BASE_URL, $data_directory, $db_type, $db_name;
210 // oh this isnt working. poo.
211 error_log("checking for $db_name, $db_type");
213 if($db_type == "sqlite") {
214 if(file_exists($db_name)) {
220 // TODO now for the connectables
224 function gwvp_ConnectDB()
226 global $WEB_ROOT_FS, $BASE_URL, $data_directory, $db_type, $db_name, $DB_CONNECTION;
228 // first check if $DB_CONNECTION IS live
229 error_log("in connection $db_type, $db_name");
231 if($DB_CONNECTION != false) return $DB_CONNECTION;
233 if($db_type == "sqlite") {
235 if(!file_exists($db_name)) {
236 error_log("$db_name does not exist - problem");
240 // and here we go with pdo.
241 error_log("attmpting to open db, $db_type:$db_url");
243 $DB_CONNECTION = new PDO("$db_type:$db_url");
244 } catch(PDOException $exep) {
245 error_log("execpt on db open");
249 return $DB_CONNECTION;
252 // TODO: we have to define what "Status" is
253 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
255 $conn = gwvp_ConnectDB();
257 // TODO: change from sha1
258 $shapass = sha1($password);
259 //error_log("Create user called with $email");
260 $sql = "insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')";
261 error_log("Creating user, $sql");
262 return $conn->query($sql);
264 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
265 "user_full_name" TEXT,
266 "user_password" TEXT,
267 "user_username" TEXT,
270 "user_status" INTEGER
275 function gwvp_getUser($username=null, $email=null, $id=null)
277 $conn = gwvp_ConnectDB();
279 if($username != null) {
280 $res = $conn->query("select * from users where user_username='$username'");
281 } else if($email != null) {
282 $res = $conn->query("select * from users where user_email='$email'");
283 } else if($id != null) {
284 $res = $conn->query("select * from users where users_id='$id'");
288 foreach($res as $u_res) {
289 $returns["id"] = $u_res["users_id"];
290 $returns["fullname"] = $u_res["user_full_name"];
291 $returns["password"] = $u_res["user_password"];
292 $returns["username"] = $u_res["user_username"];
293 $returns["email"] = $u_res["user_email"];
294 $returns["desc"] = $u_res["user_desc"];
295 $returns["status"] = $u_res["user_status"];
302 function gwvp_getRepoOwner($repoid)
304 $conn = gwvp_ConnectDB();
306 $sql = "select repos_owner from repos where repos_id='$repoid'";
308 $res = $conn->query($sql);
311 foreach($res as $rown) {
312 $return = $rown["repos_owner"];
317 function gwvp_getOwnedRepos($userid = null, $username = null)
319 $conn = gwvp_ConnectDB();
321 if($username != null) {
322 $details = gwvp_getUser($username);
323 $uid = $details["id"];
324 $sql = "select * from repos where repos_owner='$uid'";
325 $res = $conn->query($sql);
326 error_log("sql: $sql");
327 } else if($userid != null) {
328 $sql = "select * from repos where repos_owner='$userid'";
329 $res = $conn->query($sql);
330 error_log("sql: $sql");
334 * CREATE TABLE "repos" (
335 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
337 "repos_description" TEXT,
338 "repos_owner" INTEGER
345 foreach($res as $u_res) {
346 $returns[$rn]["id"] = $u_res["repos_id"];
347 $returns[$rn]["name"] = $u_res["repos_name"];
348 $returns[$rn]["description"] = $u_res["repos_description"];
355 function gwvp_getUsers()
357 $conn = gwvp_ConnectDB();
359 $res = $conn->query("select * from users");
363 foreach($res as $u_res) {
364 $returns[$rn]["id"] = $u_res["users_id"];
365 $returns[$rn]["fullname"] = $u_res["user_full_name"];
366 $returns[$rn]["password"] = $u_res["user_password"];
367 $returns[$rn]["username"] = $u_res["user_username"];
368 $returns[$rn]["email"] = $u_res["user_email"];
369 $returns[$rn]["desc"] = $u_res["user_desc"];
370 $returns[$rn]["status"] = $u_res["user_status"];
377 function gwvp_deleteUser($email)
379 $conn = gwvp_ConnectDB();
381 $sql = "delete from users where user_email='$email'";
386 function gwvp_createGroup($group_name, $group_desc, $is_admin, $owner_id)
388 $conn = gwvp_ConnectDB();
391 * CREATE TABLE groups (
394 "groups_is_admin" INTEGER,
395 "groups_owner_id" INTEGER
404 $sql = "insert into groups values( null, '$group_name', '$group_desc', '$is_admin_t', '$owner_id')";
409 // add the owner to the group
410 $gid = gwvp_getGroupId($group_name);
413 error_log("calling addgroupmember with $owner_id, $gid");
414 gwvp_addGroupMemberById($owner_id, $gid);
419 function gwvp_deleteGroup($groupname)
421 $conn = gwvp_ConnectDB();
423 $sql = "delete from groups where groups_name='$groupname'";
428 function gwvp_getGroupsForUser($email = null, $userid = null)
430 $conn = gwvp_ConnectDB();
433 select g.groups_name from
434 group_membership gm, groups g, users u
436 gm.groupmember_userid=u.users_id and
437 u.user_email='$email' and
438 gm.groupmember_groupid=g.groups_id and
439 g.groups_name='$groupname'
443 select g.groups_name from
444 group_membership gm, groups g, users u
446 gm.groupmember_userid=u.users_id and
447 u.user_email='$email' and
448 gm.groupmember_groupid=g.groups_id
450 } else if($userid != null) {
452 select g.groups_name from
453 group_membership gm, groups g, users u
455 gm.groupmember_userid=u.users_id and
456 u.users_id='$userid' and
457 gm.groupmember_groupid=g.groups_id
461 $res = $conn->query($sql);
465 foreach($res as $u_res) {
466 $return[$rn] = $u_res[0];
473 function gwvp_getGroupsOwnedByUser($email)
475 $conn = gwvp_ConnectDB();
478 select g.groups_name from
479 group_membership gm, groups g, users u
481 gm.groupmember_userid=u.users_id and
482 u.user_email='$email' and
483 gm.groupmember_groupid=g.groups_id and
484 g.groups_name='$groupname'
488 select g.groups_name from
491 u.user_email='$email' and
492 u.users_id=g.groups_owner_id
495 $res = $conn->query($sql);
499 foreach($res as $u_res) {
500 $return[$rn] = $u_res[0];
508 function gwvp_groupOwner($groupname)
510 $conn = gwvp_ConnectDB();
512 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
514 $res = $conn->query($sql);
516 foreach($res as $u_res) {
523 function gwvp_getGroups()
525 $conn = gwvp_ConnectDB();
527 $res = $conn->query("select * from groups");
531 foreach($res as $u_res) {
532 $returns[$rn]["id"] = $u_res["groups_id"];
533 $returns[$rn]["name"] = $u_res["groups_name"];
534 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
535 else $return[$rn]["admin"] = false;
536 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
537 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
544 function gwvp_getGroupId($groupname)
546 $conn = gwvp_ConnectDB();
548 $sql = "select groups_id from groups where groups_name='$groupname'";
550 $res = $conn->query($sql);
552 foreach($res as $u_res) {
553 $return = $u_res["groups_id"];
559 function gwvp_getGroup($gid = null, $gname = null)
563 CREATE TABLE groups (
564 "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
566 "groups_is_admin" INTEGER,
567 "groups_owner_id" INTEGER
571 $conn = gwvp_ConnectDB();
574 $sql = "select * from groups where groups_id='$gid'";
575 } else if ($gname != null) {
576 $sql = "select * from groups where groups_name='$gname'";
579 $res = $conn->query($sql);
581 foreach($res as $u_res) {
582 $return["id"] = $u_res["groups_id"];
583 $return["name"] = $u_res["groups_name"];
584 if($u_res["groups_is_admin"] == 1) {
585 $return["isadmin"] = true;
587 $return["isadmin"] = false;
589 $return["ownerid"] = $u_res["groups_owner_id"];
590 $return["description"] = $u_res["groups_description"];
596 function gwvp_getUserId($useremail=null, $username = null)
598 $conn = gwvp_ConnectDB();
600 if($useremail != null) {
601 $sql = "select users_id from users where user_email='$useremail'";
602 } else if($username != null) {
603 $sql = "select users_id from users where user_username='$username'";
606 $res = $conn->query($sql);
608 foreach($res as $u_res) {
609 $return = $u_res["users_id"];
615 function gwvp_getUserName($id = null, $email=null)
617 $conn = gwvp_ConnectDB();
620 $sql = "select user_username from users where user_email='$email'";
621 } else if($id != null) {
622 $sql = "select user_username from users where users_id='$id'";
625 $res = $conn->query($sql);
627 foreach($res as $u_res) {
628 $return = $u_res["user_username"];
635 function gwvp_getUserEmail($id)
637 $conn = gwvp_ConnectDB();
639 $sql = "select user_email from users where users_id='$id'";
641 $res = $conn->query($sql);
643 foreach($res as $u_res) {
644 $return = $u_res["user_email"];
650 function gwvp_deleteGroupMemberByID($uid, $gid)
652 $conn = gwvp_ConnectDB();
655 * CREATE TABLE "group_membership" (
656 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
657 "groupmember_groupid" INTEGER,
658 "groupmember_userid" INTEGER
661 $sql = "delete from group_membership where groupmember_groupid='$gid' and groupmember_userid='$uid'";
669 function gwvp_addGroupMemberByID($uid, $gid)
671 $conn = gwvp_ConnectDB();
674 * CREATE TABLE "group_membership" (
675 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
676 "groupmember_groupid" INTEGER,
677 "groupmember_userid" INTEGER
680 $sql = "insert into group_membership values (null, '$gid', '$uid')";
688 function gwvp_addGroupMember($email, $groupname)
690 $conn = gwvp_ConnectDB();
692 $uid = gwvp_getUserId($email);
693 $gid = gwvp_getGroupId($groupname);
696 * CREATE TABLE "group_membership" (
697 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
698 "groupmember_groupid" INTEGER,
699 "groupmember_userid" INTEGER
702 if($uid!=false&&$gid!=false) gwvp_addGroupMemberByID($uid, $gid);
708 function gwvp_IsGroupMemberById($uid, $gid)
710 $conn = gwvp_ConnectDB();
712 $sql = "select count(*) from group_membership where groupmember_userid='$uid' and groupmember_groupid='$gid'";
714 $res = $conn->query($sql);
716 foreach($res as $u_res) {
720 if($result == 0) return false;
721 if($result == 1) return true;
724 function gwvp_IsGroupMember($email, $groupname)
726 $conn = gwvp_ConnectDB();
728 // i think this is right
731 group_membership gm, groups g, users u
733 gm.groupmember_userid=u.users_id and
734 u.user_email='$email' and
735 gm.groupmember_groupid=g.groups_id and
736 g.groups_name='$groupname'
739 $res = $conn->query($sql);
741 foreach($res as $u_res) {
745 if($result == 0) return false;
746 if($result == 1) return true;
749 function gwvp_IsGroupAdmin($groupname = null, $gid = null)
751 $conn = gwvp_ConnectDB();
753 if($groupname != null) {
754 $sql = "select groups_is_admin from groups where groups_name='$groupname'";
755 } else if($gid != null) {
756 $sql = "select groups_is_admin from groups where groups_id='$gid'";
759 $res = $conn->query($sql);
762 foreach($res as $u_res) {
763 if($u_res["groups_is_admin"] == "1") $return = true;
769 function gwvp_IsRepoOwner($userid, $repoid)
771 $conn = gwvp_ConnectDB();
773 $sql = "select repos_owner from repos where repos_id='$repoid'";
775 $res = $conn->query($sql);
778 foreach($res as $u_res) {
779 $return["owner"] = $u_res["repos_owner"];
782 if($return == false) return false;
783 if($return["owner"] == $userid) return true;
789 function gwvp_IsUserAdmin($email=null, $username = null, $userid = null)
791 $conn = gwvp_ConnectDB();
794 // TODO: clean this up, this should be a single query - idiot
796 $id = gwvp_getUserId($email);
797 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
798 } else if($userid != null) {
799 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$userid'";
800 } else if($username != null) {
801 $id = gwvp_getUserId(null, $username);
802 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
805 $res = $conn->query($sql);
808 foreach($res as $u_res) {
809 $gid[$rn] = $u_res["groupmember_groupid"];
813 if($gid !== false) foreach($gid as $gid_t) {
815 * CREATE TABLE groups (
818 "groups_is_admin" INTEGER,
819 "groups_owner_id" INTEGER
824 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
825 $res = $conn->query($sql);
826 foreach($res as $u_res) {
827 if($u_res["groups_is_admin"] == "1") return true;
834 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
837 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
838 "user_full_name" TEXT,
839 "user_password" TEXT,
840 "user_username" TEXT,
843 "user_status" INTEGER
847 $conn = gwvp_ConnectDB();
850 $sql = "update users set user_email='$email' where users_id='$userid'";
854 if($fullname != null) {
855 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
859 if($password != null) {
860 $shapass = sha1($password);
861 $sql = "update users set user_password='$shapass' where users_id='$userid'";
865 if($username != null) {
866 $sql = "update users set user_username='$username' where users_id='$userid'";
871 $sql = "update users set user_desc='$desc' where users_id='$userid'";
875 if($status != null) {
876 $sql = "update users set user_status='$status' where users_id='$userid'";
884 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
887 * CREATE TABLE groups (
890 "groups_is_admin" INTEGER,
891 "groups_owner_id" INTEGER
895 $conn = gwvp_ConnectDB();
897 if($groupname != null) {
898 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
902 if($group_is_admin != null) {
903 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
907 if($groups_owner_id != null) {
908 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
915 function gwvp_GetRepoId($reponame)
917 $conn = gwvp_ConnectDB();
919 $sql = "select repos_id from repos where repos_name='$reponame'";
921 $res = $conn->query($sql);
924 foreach($res as $u_res) {
925 $return = $u_res["repos_id"];
932 function gwvp_GetRepo($rid)
934 $conn = gwvp_ConnectDB();
936 $sql = "select * from repos where repos_id='$rid'";
938 $res = $conn->query($sql);
941 foreach($res as $u_res) {
942 $return["id"] = $u_res["repos_id"];
943 $return["name"] = $u_res["repos_name"];
944 $return["description"] = $u_res["repos_description"];
945 $return["owner"] = $u_res["repos_owner"];
951 function gwvp_GetRepoList()
953 $conn = gwvp_ConnectDB();
957 CREATE TABLE "repos" (
958 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
960 "repos_description" TEXT,
961 "repos_owner" INTEGER
966 $sql = "select * from repos";
968 $res = $conn->query($sql);
972 foreach($res as $u_res) {
973 $return[$rn]["id"] = $u_res["repos_id"];
974 $return[$rn]["name"] = $u_res["repos_name"];
975 $return[$rn]["description"] = $u_res["repos_description"];
976 $return[$rn]["owner"] = $u_res["repos_owner"];
983 function gwvp_AddRepo($reponame, $repodesc, $repoowner, $defaultperms = 0)
985 $conn = gwvp_ConnectDB();
987 $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')";
991 $sql = "select repos_id from repos where repos_name='$reponame'";
992 $res = $conn->query($sql);
994 foreach($res as $repos) {
995 $rid = $repos["repos_id"];
998 * CREATE TABLE "repoperms" (
999 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
1001 "repoperms_type" TEXT,
1002 "repoperms_ref" TEXT
1008 // 0 - anyone can clone/read, only owner can write
1009 // 1 - noone can clone/read, repo is visible (i.e. name), only owner can read/write repo
1010 // 2 - only owner can see anything
1014 switch($defaultperms) {
1016 gwvp_addRepoPermission($rid, "visible", "anon");
1019 // by 2, we do nothing, owner already has full perms
1022 gwvp_addRepoPermission($rid, "read", "anon");
1027 function gwvp_getRepoPermissions($repoid)
1030 * // this looks like null, <repoid>, <read|visible|write>, user:<uid>|group:<gid>|authed|anon
1031 // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc)
1032 // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo
1033 // TODO: is this sufficient? i have to think about it
1035 CREATE TABLE "repoperms" (
1036 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
1038 "repoperms_type" TEXT,
1039 "repoperms_ref" TEXT
1043 $conn = gwvp_ConnectDB();
1045 $sql = "select * from repoperms where repo_id='$repoid'";
1047 $res = $conn->query($sql);
1051 foreach($res as $perm) {
1052 $returns[$rn]["id"] = $perm["repoperms_id"];
1053 $returns[$rn]["type"] = $perm["repoperms_type"];
1054 $returns[$rn]["ref"] = $perm["repoperms_ref"];
1061 function gwvp_removeRepoPermission($permid)
1063 $conn = gwvp_ConnectDB();
1065 $sql = "delete from repoperms where repoperms_id='$permid'";
1066 error_log("attempting: \"$sql\"");
1068 return $conn->query($sql);
1071 function gwvp_addRepoPermission($repoid, $permtype, $permref)
1073 $conn = gwvp_ConnectDB();
1075 error_log("PERMS: $repoid, $permtype, $permref");
1076 $sql = "insert into repoperms values(null, '$repoid', '$permtype', '$permref')";
1078 return $conn->query($sql);
1080 /* functions we'll need to access data:
1083 * getUserData(username)
1084 * getGroups(pattern)
1085 * getGroupData(groupname)
1087 * addGroupMember(...)
1088 * deleteGroupMember(...)