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_forceDisconnect()
85 global $DB_CONNECTION;
87 $DB_CONNECTION = false;
91 function gwvp_getConfigVal($confname)
95 CREATE TABLE "config" (
102 $conn = gwvp_ConnectDB();
104 $sql = "select * from config where config_name='$confname'";
106 $res = $conn->query($sql);
109 foreach($res as $val) {
115 function gwvp_eraseConfigVal($confname)
119 CREATE TABLE "config" (
126 $conn = gwvp_ConnectDB();
128 $sql = "delete from config where config_name='$confname'";
130 return $conn->query($sql);
133 function gwvp_setConfigVal($confname, $confval)
137 CREATE TABLE "config" (
143 gwvp_eraseConfigVal($confname);
145 $conn = gwvp_ConnectDB();
147 $sql = "insert into config values('$confname', '$confval')";
149 return $conn->query($sql);
153 function gwvp_isDBSetup()
155 // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay
156 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_url;
158 if($db_type == "sqlite") {
159 if(file_exists($db_url)) return true;
163 // TODO now for the connectables
167 function gwvp_ConnectDB()
169 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION;
171 // first check if $DB_CONNECTION IS live
172 if($DB_CONNECTION != false) return $DB_CONNECTION;
174 if($db_type == "sqlite") {
176 if(!file_exists($db_name)) {
177 error_log("$db_name does not exist - problem");
181 // and here we go with pdo.
182 error_log("attmpting to open db, $db_type:$db_url");
184 $DB_CONNECTION = new PDO("$db_type:$db_url");
185 } catch(PDOException $exep) {
186 error_log("execpt on db open");
190 return $DB_CONNECTION;
193 // TODO: we have to define what "Status" is
194 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
196 $conn = gwvp_ConnectDB();
198 // TODO: change from sha1
199 $shapass = sha1($password);
200 //error_log("Create user called with $email");
201 $sql = "insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')";
202 error_log("Creating user, $sql");
203 return $conn->query($sql);
205 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
206 "user_full_name" TEXT,
207 "user_password" TEXT,
208 "user_username" TEXT,
211 "user_status" INTEGER
216 function gwvp_getUser($username=null, $email=null, $id=null)
218 $conn = gwvp_ConnectDB();
220 if($username != null) {
221 $res = $conn->query("select * from users where user_username='$username'");
222 } else if($email != null) {
223 $res = $conn->query("select * from users where user_email='$email'");
224 } else if($id != null) {
225 $res = $conn->query("select * from users where users_id='$id'");
229 foreach($res as $u_res) {
230 $returns["id"] = $u_res["users_id"];
231 $returns["fullname"] = $u_res["user_full_name"];
232 $returns["password"] = $u_res["user_password"];
233 $returns["username"] = $u_res["user_username"];
234 $returns["email"] = $u_res["user_email"];
235 $returns["desc"] = $u_res["user_desc"];
236 $returns["status"] = $u_res["user_status"];
243 function gwvp_getUsers()
245 $conn = gwvp_ConnectDB();
247 $res = $conn->query("select * from users");
251 foreach($res as $u_res) {
252 $returns[$rn]["id"] = $u_res["users_id"];
253 $returns[$rn]["fullname"] = $u_res["user_full_name"];
254 $returns[$rn]["password"] = $u_res["user_password"];
255 $returns[$rn]["username"] = $u_res["user_username"];
256 $returns[$rn]["email"] = $u_res["user_email"];
257 $returns[$rn]["desc"] = $u_res["user_desc"];
258 $returns[$rn]["status"] = $u_res["user_status"];
265 function gwvp_deleteUser($email)
267 $conn = gwvp_ConnectDB();
269 $sql = "delete from users where user_email='$email'";
274 function gwvp_createGroup($group_name, $group_desc, $is_admin, $owner_id)
276 $conn = gwvp_ConnectDB();
279 * CREATE TABLE groups (
282 "groups_is_admin" INTEGER,
283 "groups_owner_id" INTEGER
292 $sql = "insert into groups values( null, '$group_name', '$group_desc', '$is_admin_t', '$owner_id')";
299 function gwvp_deleteGroup($groupname)
301 $conn = gwvp_ConnectDB();
303 $sql = "delete from groups where groups_name='$groupname'";
308 function gwvp_getGroupsForUser($email)
310 $conn = gwvp_ConnectDB();
313 select g.groups_name from
314 group_membership gm, groups g, users u
316 gm.groupmember_userid=u.users_id and
317 u.user_email='$email' and
318 gm.groupmember_groupid=g.groups_id and
319 g.groups_name='$groupname'
323 select g.groups_name from
324 group_membership gm, groups g, users u
326 gm.groupmember_userid=u.users_id and
327 u.user_email='$email' and
328 gm.groupmember_groupid=g.groups_id
331 $res = $conn->query($sql);
335 foreach($res as $u_res) {
336 $return[$rn] = $u_res[0];
343 function gwvp_getGroupsOwnedByUser($email)
345 $conn = gwvp_ConnectDB();
348 select g.groups_name from
349 group_membership gm, groups g, users u
351 gm.groupmember_userid=u.users_id and
352 u.user_email='$email' and
353 gm.groupmember_groupid=g.groups_id and
354 g.groups_name='$groupname'
358 select g.groups_name from
361 u.user_email='$email' and
362 u.users_id=g.groups_owner_id
365 $res = $conn->query($sql);
369 foreach($res as $u_res) {
370 $return[$rn] = $u_res[0];
378 function gwvp_groupOwner($groupname)
380 $conn = gwvp_ConnectDB();
382 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
384 $res = $conn->query($sql);
386 foreach($res as $u_res) {
393 function gwvp_getGroups()
395 $conn = gwvp_ConnectDB();
397 $res = $conn->query("select * from groups");
401 foreach($res as $u_res) {
402 $returns[$rn]["id"] = $u_res["groups_id"];
403 $returns[$rn]["name"] = $u_res["groups_name"];
404 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
405 else $return[$rn]["admin"] = false;
406 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
407 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
414 function gwvp_getGroupId($groupname)
416 $conn = gwvp_ConnectDB();
418 $sql = "select groups_id from groups where groups_name='$groupname'";
420 $res = $conn->query($sql);
422 foreach($res as $u_res) {
423 $return = $u_res["groups_id"];
429 function gwvp_getGroup($gid)
433 CREATE TABLE groups (
434 "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
436 "groups_is_admin" INTEGER,
437 "groups_owner_id" INTEGER
441 $conn = gwvp_ConnectDB();
443 $sql = "select * from groups where groups_id='$gid'";
445 $res = $conn->query($sql);
447 foreach($res as $u_res) {
448 $return["id"] = $u_res["groups_id"];
449 $return["name"] = $u_res["groups_name"];
450 if($u_res["groups_is_admin"] == 1) {
451 $return["isadmin"] = true;
453 $return["isadmin"] = false;
455 $return["ownerid"] = $u_res["groups_owner_id"];
456 $return["description"] = $u_res["groups_description"];
462 function gwvp_getUserId($useremail=null, $username = null)
464 $conn = gwvp_ConnectDB();
466 if($useremail != null) {
467 $sql = "select users_id from users where user_email='$useremail'";
468 } else if($username != null) {
469 $sql = "select users_id from users where user_username='$username'";
472 $res = $conn->query($sql);
474 foreach($res as $u_res) {
475 $return = $u_res["users_id"];
481 function gwvp_getUserName($id = null, $email=null)
483 $conn = gwvp_ConnectDB();
486 $sql = "select user_username from users where user_email='$email'";
487 } else if($id != null) {
488 $sql = "select user_username from users where users_id='$id'";
491 $res = $conn->query($sql);
493 foreach($res as $u_res) {
494 $return = $u_res["user_username"];
501 function gwvp_getUserEmail($id)
503 $conn = gwvp_ConnectDB();
505 $sql = "select user_email from users where users_id='$id'";
507 $res = $conn->query($sql);
509 foreach($res as $u_res) {
510 $return = $u_res["user_email"];
516 function gwvp_deleteGroupMemberByID($uid, $gid)
518 $conn = gwvp_ConnectDB();
521 * CREATE TABLE "group_membership" (
522 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
523 "groupmember_groupid" INTEGER,
524 "groupmember_userid" INTEGER
527 $sql = "delete from group_membership where groupmember_groupid='$gid' and groupmember_userid='$uid'";
535 function gwvp_addGroupMemberByID($uid, $gid)
537 $conn = gwvp_ConnectDB();
540 * CREATE TABLE "group_membership" (
541 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
542 "groupmember_groupid" INTEGER,
543 "groupmember_userid" INTEGER
546 $sql = "insert into group_membership values (null, '$gid', '$uid')";
554 function gwvp_addGroupMember($email, $groupname)
556 $conn = gwvp_ConnectDB();
558 $uid = gwvp_getUserId($email);
559 $gid = gwvp_getGroupId($groupname);
562 * CREATE TABLE "group_membership" (
563 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
564 "groupmember_groupid" INTEGER,
565 "groupmember_userid" INTEGER
568 if($uid!=false&&$gid!=false) gwvp_addGroupMemberByID($uid, $gid);
576 function gwvp_IsGroupMember($email, $groupname)
578 $conn = gwvp_ConnectDB();
580 // i think this is right
583 group_membership gm, groups g, users u
585 gm.groupmember_userid=u.users_id and
586 u.user_email='$email' and
587 gm.groupmember_groupid=g.groups_id and
588 g.groups_name='$groupname'
591 $res = $conn->query($sql);
593 foreach($res as $u_res) {
597 if($result == 0) return false;
598 if($result == 1) return true;
601 function gwvp_IsUserAdmin($email=null, $username = null)
603 $conn = gwvp_ConnectDB();
606 // TODO: clean this up, this should be a single query - idiot
608 $id = gwvp_getUserId($email);
609 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
610 } else if($username != null) {
611 $id = gwvp_getUserId(null, $username);
612 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
615 $res = $conn->query($sql);
618 foreach($res as $u_res) {
619 $gid[$rn] = $u_res["groupmember_groupid"];
623 if($gid !== false) foreach($gid as $gid_t) {
625 * CREATE TABLE groups (
628 "groups_is_admin" INTEGER,
629 "groups_owner_id" INTEGER
634 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
635 $res = $conn->query($sql);
636 foreach($res as $u_res) {
637 if($u_res["groups_is_admin"] == "1") return true;
644 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
647 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
648 "user_full_name" TEXT,
649 "user_password" TEXT,
650 "user_username" TEXT,
653 "user_status" INTEGER
657 $conn = gwvp_ConnectDB();
660 $sql = "update users set user_email='$email' where users_id='$userid'";
664 if($fullname != null) {
665 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
669 if($password != null) {
670 $shapass = sha1($password);
671 $sql = "update users set user_password='$shapass' where users_id='$userid'";
675 if($username != null) {
676 $sql = "update users set user_username='$username' where users_id='$userid'";
681 $sql = "update users set user_desc='$desc' where users_id='$userid'";
685 if($status != null) {
686 $sql = "update users set user_status='$status' where users_id='$userid'";
694 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
697 * CREATE TABLE groups (
700 "groups_is_admin" INTEGER,
701 "groups_owner_id" INTEGER
705 $conn = gwvp_ConnectDB();
707 if($groupname != null) {
708 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
712 if($group_is_admin != null) {
713 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
717 if($groups_owner_id != null) {
718 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
725 function gwvp_AddRepo($reponame, $repodesc, $repoowner)
727 $conn = gwvp_ConnectDB();
729 $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')";
734 function gwvp_GetRepoList()
736 $conn = gwvp_ConnectDB();
740 CREATE TABLE "repos" (
741 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
743 "repos_description" TEXT,
744 "repos_owner" INTEGER
749 $sql = "select * from repos";
751 $res = $conn->query($sql);
755 foreach($res as $u_res) {
756 $return[$rn]["id"] = $u_res["repos_id"];
757 $return[$rn]["name"] = $u_res["repos_name"];
758 $return[$rn]["description"] = $u_res["repos_description"];
759 $return[$rn]["owner"] = $u_res["repos_owner"];
765 /* functions we'll need to access data:
768 * getUserData(username)
770 * getGroupData(groupname)
772 * addGroupMember(...)
773 * deleteGroupMember(...)