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_getRepoOwner($repoid)
290 $conn = gwvp_ConnectDB();
292 $sql = "select repos_owner from repos where repos_id='$repoid'";
294 $res = $conn->query($sql);
297 foreach($res as $rown) {
298 $return = $rown["repos_owner"];
303 function gwvp_getOwnedRepos($userid = null, $username = null)
305 $conn = gwvp_ConnectDB();
307 if($username != null) {
308 $details = gwvp_getUser($username);
309 $uid = $details["id"];
310 $sql = "select * from repos where repos_owner='$uid'";
311 $res = $conn->query($sql);
312 error_log("sql: $sql");
313 } else if($userid != null) {
314 $sql = "select * from repos where repos_owner='$userid'";
315 $res = $conn->query($sql);
316 error_log("sql: $sql");
320 * CREATE TABLE "repos" (
321 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
323 "repos_description" TEXT,
324 "repos_owner" INTEGER
331 foreach($res as $u_res) {
332 $returns[$rn]["id"] = $u_res["repos_id"];
333 $returns[$rn]["name"] = $u_res["repos_name"];
334 $returns[$rn]["description"] = $u_res["repos_description"];
341 function gwvp_getUsers()
343 $conn = gwvp_ConnectDB();
345 $res = $conn->query("select * from users");
349 foreach($res as $u_res) {
350 $returns[$rn]["id"] = $u_res["users_id"];
351 $returns[$rn]["fullname"] = $u_res["user_full_name"];
352 $returns[$rn]["password"] = $u_res["user_password"];
353 $returns[$rn]["username"] = $u_res["user_username"];
354 $returns[$rn]["email"] = $u_res["user_email"];
355 $returns[$rn]["desc"] = $u_res["user_desc"];
356 $returns[$rn]["status"] = $u_res["user_status"];
363 function gwvp_deleteUser($email)
365 $conn = gwvp_ConnectDB();
367 $sql = "delete from users where user_email='$email'";
372 function gwvp_createGroup($group_name, $group_desc, $is_admin, $owner_id)
374 $conn = gwvp_ConnectDB();
377 * CREATE TABLE groups (
380 "groups_is_admin" INTEGER,
381 "groups_owner_id" INTEGER
390 $sql = "insert into groups values( null, '$group_name', '$group_desc', '$is_admin_t', '$owner_id')";
397 function gwvp_deleteGroup($groupname)
399 $conn = gwvp_ConnectDB();
401 $sql = "delete from groups where groups_name='$groupname'";
406 function gwvp_getGroupsForUser($email = null, $userid = null)
408 $conn = gwvp_ConnectDB();
411 select g.groups_name from
412 group_membership gm, groups g, users u
414 gm.groupmember_userid=u.users_id and
415 u.user_email='$email' and
416 gm.groupmember_groupid=g.groups_id and
417 g.groups_name='$groupname'
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
428 } else if($userid != null) {
430 select g.groups_name from
431 group_membership gm, groups g, users u
433 gm.groupmember_userid=u.users_id and
434 u.users_id='$userid' and
435 gm.groupmember_groupid=g.groups_id
439 $res = $conn->query($sql);
443 foreach($res as $u_res) {
444 $return[$rn] = $u_res[0];
451 function gwvp_getGroupsOwnedByUser($email)
453 $conn = gwvp_ConnectDB();
456 select g.groups_name from
457 group_membership gm, groups g, users u
459 gm.groupmember_userid=u.users_id and
460 u.user_email='$email' and
461 gm.groupmember_groupid=g.groups_id and
462 g.groups_name='$groupname'
466 select g.groups_name from
469 u.user_email='$email' and
470 u.users_id=g.groups_owner_id
473 $res = $conn->query($sql);
477 foreach($res as $u_res) {
478 $return[$rn] = $u_res[0];
486 function gwvp_groupOwner($groupname)
488 $conn = gwvp_ConnectDB();
490 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
492 $res = $conn->query($sql);
494 foreach($res as $u_res) {
501 function gwvp_getGroups()
503 $conn = gwvp_ConnectDB();
505 $res = $conn->query("select * from groups");
509 foreach($res as $u_res) {
510 $returns[$rn]["id"] = $u_res["groups_id"];
511 $returns[$rn]["name"] = $u_res["groups_name"];
512 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
513 else $return[$rn]["admin"] = false;
514 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
515 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
522 function gwvp_getGroupId($groupname)
524 $conn = gwvp_ConnectDB();
526 $sql = "select groups_id from groups where groups_name='$groupname'";
528 $res = $conn->query($sql);
530 foreach($res as $u_res) {
531 $return = $u_res["groups_id"];
537 function gwvp_getGroup($gid = null, $gname = null)
541 CREATE TABLE groups (
542 "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
544 "groups_is_admin" INTEGER,
545 "groups_owner_id" INTEGER
549 $conn = gwvp_ConnectDB();
552 $sql = "select * from groups where groups_id='$gid'";
553 } else if ($gname != null) {
554 $sql = "select * from groups where groups_name='$gname'";
557 $res = $conn->query($sql);
559 foreach($res as $u_res) {
560 $return["id"] = $u_res["groups_id"];
561 $return["name"] = $u_res["groups_name"];
562 if($u_res["groups_is_admin"] == 1) {
563 $return["isadmin"] = true;
565 $return["isadmin"] = false;
567 $return["ownerid"] = $u_res["groups_owner_id"];
568 $return["description"] = $u_res["groups_description"];
574 function gwvp_getUserId($useremail=null, $username = null)
576 $conn = gwvp_ConnectDB();
578 if($useremail != null) {
579 $sql = "select users_id from users where user_email='$useremail'";
580 } else if($username != null) {
581 $sql = "select users_id from users where user_username='$username'";
584 $res = $conn->query($sql);
586 foreach($res as $u_res) {
587 $return = $u_res["users_id"];
593 function gwvp_getUserName($id = null, $email=null)
595 $conn = gwvp_ConnectDB();
598 $sql = "select user_username from users where user_email='$email'";
599 } else if($id != null) {
600 $sql = "select user_username from users where users_id='$id'";
603 $res = $conn->query($sql);
605 foreach($res as $u_res) {
606 $return = $u_res["user_username"];
613 function gwvp_getUserEmail($id)
615 $conn = gwvp_ConnectDB();
617 $sql = "select user_email from users where users_id='$id'";
619 $res = $conn->query($sql);
621 foreach($res as $u_res) {
622 $return = $u_res["user_email"];
628 function gwvp_deleteGroupMemberByID($uid, $gid)
630 $conn = gwvp_ConnectDB();
633 * CREATE TABLE "group_membership" (
634 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
635 "groupmember_groupid" INTEGER,
636 "groupmember_userid" INTEGER
639 $sql = "delete from group_membership where groupmember_groupid='$gid' and groupmember_userid='$uid'";
647 function gwvp_addGroupMemberByID($uid, $gid)
649 $conn = gwvp_ConnectDB();
652 * CREATE TABLE "group_membership" (
653 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
654 "groupmember_groupid" INTEGER,
655 "groupmember_userid" INTEGER
658 $sql = "insert into group_membership values (null, '$gid', '$uid')";
666 function gwvp_addGroupMember($email, $groupname)
668 $conn = gwvp_ConnectDB();
670 $uid = gwvp_getUserId($email);
671 $gid = gwvp_getGroupId($groupname);
674 * CREATE TABLE "group_membership" (
675 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
676 "groupmember_groupid" INTEGER,
677 "groupmember_userid" INTEGER
680 if($uid!=false&&$gid!=false) gwvp_addGroupMemberByID($uid, $gid);
686 function gwvp_IsGroupMember($email, $groupname)
688 $conn = gwvp_ConnectDB();
690 // i think this is right
693 group_membership gm, groups g, users u
695 gm.groupmember_userid=u.users_id and
696 u.user_email='$email' and
697 gm.groupmember_groupid=g.groups_id and
698 g.groups_name='$groupname'
701 $res = $conn->query($sql);
703 foreach($res as $u_res) {
707 if($result == 0) return false;
708 if($result == 1) return true;
711 function gwvp_IsUserAdmin($email=null, $username = null, $userid = null)
713 $conn = gwvp_ConnectDB();
716 // TODO: clean this up, this should be a single query - idiot
718 $id = gwvp_getUserId($email);
719 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
720 } else if($userid != null) {
721 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$userid'";
722 } else if($username != null) {
723 $id = gwvp_getUserId(null, $username);
724 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
727 $res = $conn->query($sql);
730 foreach($res as $u_res) {
731 $gid[$rn] = $u_res["groupmember_groupid"];
735 if($gid !== false) foreach($gid as $gid_t) {
737 * CREATE TABLE groups (
740 "groups_is_admin" INTEGER,
741 "groups_owner_id" INTEGER
746 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
747 $res = $conn->query($sql);
748 foreach($res as $u_res) {
749 if($u_res["groups_is_admin"] == "1") return true;
756 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
759 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
760 "user_full_name" TEXT,
761 "user_password" TEXT,
762 "user_username" TEXT,
765 "user_status" INTEGER
769 $conn = gwvp_ConnectDB();
772 $sql = "update users set user_email='$email' where users_id='$userid'";
776 if($fullname != null) {
777 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
781 if($password != null) {
782 $shapass = sha1($password);
783 $sql = "update users set user_password='$shapass' where users_id='$userid'";
787 if($username != null) {
788 $sql = "update users set user_username='$username' where users_id='$userid'";
793 $sql = "update users set user_desc='$desc' where users_id='$userid'";
797 if($status != null) {
798 $sql = "update users set user_status='$status' where users_id='$userid'";
806 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
809 * CREATE TABLE groups (
812 "groups_is_admin" INTEGER,
813 "groups_owner_id" INTEGER
817 $conn = gwvp_ConnectDB();
819 if($groupname != null) {
820 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
824 if($group_is_admin != null) {
825 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
829 if($groups_owner_id != null) {
830 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
837 function gwvp_GetRepoList()
839 $conn = gwvp_ConnectDB();
843 CREATE TABLE "repos" (
844 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
846 "repos_description" TEXT,
847 "repos_owner" INTEGER
852 $sql = "select * from repos";
854 $res = $conn->query($sql);
858 foreach($res as $u_res) {
859 $return[$rn]["id"] = $u_res["repos_id"];
860 $return[$rn]["name"] = $u_res["repos_name"];
861 $return[$rn]["description"] = $u_res["repos_description"];
862 $return[$rn]["owner"] = $u_res["repos_owner"];
869 function gwvp_AddRepo($reponame, $repodesc, $repoowner, $defaultperms = 0)
871 $conn = gwvp_ConnectDB();
873 $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')";
877 $sql = "select repos_id from repos where repos_name='$reponame'";
878 $res = $conn->query($sql);
880 foreach($res as $repos) {
881 $rid = $repos["repos_id"];
884 * CREATE TABLE "repoperms" (
885 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
887 "repoperms_type" TEXT,
894 // 0 - anyone can clone/read, only owner can write
895 // 1 - noone can clone/read, repo is visible (i.e. name), only owner can read/write repo
896 // 2 - only owner can see anything
900 switch($defaultperms) {
902 gwvp_addRepoPermission($rid, "visible", "anon");
905 // by 2, we do nothing, owner already has full perms
908 gwvp_addRepoPermission($rid, "read", "anon");
913 function gwvp_getRepoPermissions($repoid)
916 * // this looks like null, <repoid>, <read|visible|write>, user:<uid>|group:<gid>|authed|anon
917 // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc)
918 // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo
919 // TODO: is this sufficient? i have to think about it
921 CREATE TABLE "repoperms" (
922 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
924 "repoperms_type" TEXT,
929 $conn = gwvp_ConnectDB();
931 $sql = "select * from repoperms where repo_id='$repoid'";
933 $res = $conn->query($sql);
937 foreach($res as $perm) {
938 $returns[$rn]["permid"] = $perm["repoperms_id"];
939 $returns[$rn]["type"] = $perm["repoperms_type"];
940 $returns[$rn]["ref"] = $perm["repoperms_ref"];
947 function gwvp_addRepoPermission($repoid, $permtype, $permref)
949 $conn = gwvp_ConnectDB();
951 $sql = "insert into repoperms values(null, '$repoid', '$permtype', '$permref')";
953 return $conn->query($sql);
955 /* functions we'll need to access data:
958 * getUserData(username)
960 * getGroupData(groupname)
962 * addGroupMember(...)
963 * deleteGroupMember(...)