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;
90 function gwvp_isDBSetup()
92 // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay
93 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_url;
95 if($db_type == "sqlite") {
96 if(file_exists($db_url)) return true;
100 // TODO now for the connectables
104 function gwvp_ConnectDB()
106 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION;
108 // first check if $DB_CONNECTION IS live
109 if($DB_CONNECTION != false) return $DB_CONNECTION;
111 if($db_type == "sqlite") {
113 if(!file_exists($db_name)) {
114 error_log("$db_name does not exist - problem");
118 // and here we go with pdo.
119 error_log("attmpting to open db, $db_type:$db_url");
121 $DB_CONNECTION = new PDO("$db_type:$db_url");
122 } catch(PDOException $exep) {
123 error_log("execpt on db open");
127 return $DB_CONNECTION;
130 // TODO: we have to define what "Status" is
131 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
133 $conn = gwvp_ConnectDB();
135 // TODO: change from sha1
136 $shapass = sha1($password);
137 //error_log("Create user called with $email");
138 $sql = "insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')";
139 error_log("Creating user, $sql");
140 return $conn->query($sql);
142 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
143 "user_full_name" TEXT,
144 "user_password" TEXT,
145 "user_username" TEXT,
148 "user_status" INTEGER
153 function gwvp_getUser($username=null, $email=null, $id=null)
155 $conn = gwvp_ConnectDB();
157 if($username != null) {
158 $res = $conn->query("select * from users where user_username='$username'");
159 } else if($email != null) {
160 $res = $conn->query("select * from users where user_email='$email'");
161 } else if($id != null) {
162 $res = $conn->query("select * from users where users_id='$id'");
166 foreach($res as $u_res) {
167 $returns["id"] = $u_res["users_id"];
168 $returns["fullname"] = $u_res["user_full_name"];
169 $returns["password"] = $u_res["user_password"];
170 $returns["username"] = $u_res["user_username"];
171 $returns["email"] = $u_res["user_email"];
172 $returns["desc"] = $u_res["user_desc"];
173 $returns["status"] = $u_res["user_status"];
180 function gwvp_getUsers()
182 $conn = gwvp_ConnectDB();
184 $res = $conn->query("select * from users");
188 foreach($res as $u_res) {
189 $returns[$rn]["id"] = $u_res["users_id"];
190 $returns[$rn]["fullname"] = $u_res["user_full_name"];
191 $returns[$rn]["password"] = $u_res["user_password"];
192 $returns[$rn]["username"] = $u_res["user_username"];
193 $returns[$rn]["email"] = $u_res["user_email"];
194 $returns[$rn]["desc"] = $u_res["user_desc"];
195 $returns[$rn]["status"] = $u_res["user_status"];
202 function gwvp_deleteUser($email)
204 $conn = gwvp_ConnectDB();
206 $sql = "delete from users where user_email='$email'";
211 function gwvp_createGroup($group_name, $group_desc, $is_admin, $owner_id)
213 $conn = gwvp_ConnectDB();
216 * CREATE TABLE groups (
219 "groups_is_admin" INTEGER,
220 "groups_owner_id" INTEGER
229 $sql = "insert into groups values( null, '$group_name', '$group_desc', '$is_admin_t', '$owner_id')";
236 function gwvp_deleteGroup($groupname)
238 $conn = gwvp_ConnectDB();
240 $sql = "delete from groups where groups_name='$groupname'";
245 function gwvp_getGroupsForUser($email)
247 $conn = gwvp_ConnectDB();
250 select g.groups_name from
251 group_membership gm, groups g, users u
253 gm.groupmember_userid=u.users_id and
254 u.user_email='$email' and
255 gm.groupmember_groupid=g.groups_id and
256 g.groups_name='$groupname'
260 select g.groups_name from
261 group_membership gm, groups g, users u
263 gm.groupmember_userid=u.users_id and
264 u.user_email='$email' and
265 gm.groupmember_groupid=g.groups_id
268 $res = $conn->query($sql);
272 foreach($res as $u_res) {
273 $return[$rn] = $u_res[0];
280 function gwvp_getGroupsOwnedByUser($email)
282 $conn = gwvp_ConnectDB();
285 select g.groups_name from
286 group_membership gm, groups g, users u
288 gm.groupmember_userid=u.users_id and
289 u.user_email='$email' and
290 gm.groupmember_groupid=g.groups_id and
291 g.groups_name='$groupname'
295 select g.groups_name from
298 u.user_email='$email' and
299 u.users_id=g.groups_owner_id
302 $res = $conn->query($sql);
306 foreach($res as $u_res) {
307 $return[$rn] = $u_res[0];
315 function gwvp_groupOwner($groupname)
317 $conn = gwvp_ConnectDB();
319 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
321 $res = $conn->query($sql);
323 foreach($res as $u_res) {
330 function gwvp_getGroups()
332 $conn = gwvp_ConnectDB();
334 $res = $conn->query("select * from groups");
338 foreach($res as $u_res) {
339 $returns[$rn]["id"] = $u_res["groups_id"];
340 $returns[$rn]["name"] = $u_res["groups_name"];
341 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
342 else $return[$rn]["admin"] = false;
343 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
344 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
351 function gwvp_getGroupId($groupname)
353 $conn = gwvp_ConnectDB();
355 $sql = "select groups_id from groups where groups_name='$groupname'";
357 $res = $conn->query($sql);
359 foreach($res as $u_res) {
360 $return = $u_res["groups_id"];
366 function gwvp_getGroup($gid)
370 CREATE TABLE groups (
371 "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
373 "groups_is_admin" INTEGER,
374 "groups_owner_id" INTEGER
378 $conn = gwvp_ConnectDB();
380 $sql = "select * from groups where groups_id='$gid'";
382 $res = $conn->query($sql);
384 foreach($res as $u_res) {
385 $return["id"] = $u_res["groups_id"];
386 $return["name"] = $u_res["groups_name"];
387 if($u_res["groups_is_admin"] == 1) {
388 $return["isadmin"] = true;
390 $return["isadmin"] = false;
392 $return["ownerid"] = $u_res["groups_owner_id"];
393 $return["description"] = $u_res["groups_description"];
399 function gwvp_getUserId($useremail=null, $username = null)
401 $conn = gwvp_ConnectDB();
403 if($useremail != null) {
404 $sql = "select users_id from users where user_email='$useremail'";
405 } else if($username != null) {
406 $sql = "select users_id from users where user_username='$username'";
409 $res = $conn->query($sql);
411 foreach($res as $u_res) {
412 $return = $u_res["users_id"];
418 function gwvp_getUserName($id = null, $email=null)
420 $conn = gwvp_ConnectDB();
423 $sql = "select user_username from users where user_email='$email'";
424 } else if($id != null) {
425 $sql = "select user_username from users where users_id='$id'";
428 $res = $conn->query($sql);
430 foreach($res as $u_res) {
431 $return = $u_res["user_username"];
438 function gwvp_getUserEmail($id)
440 $conn = gwvp_ConnectDB();
442 $sql = "select user_email from users where users_id='$id'";
444 $res = $conn->query($sql);
446 foreach($res as $u_res) {
447 $return = $u_res["user_email"];
453 function gwvp_deleteGroupMemberByID($uid, $gid)
455 $conn = gwvp_ConnectDB();
458 * CREATE TABLE "group_membership" (
459 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
460 "groupmember_groupid" INTEGER,
461 "groupmember_userid" INTEGER
464 $sql = "delete from group_membership where groupmember_groupid='$gid' and groupmember_userid='$uid'";
472 function gwvp_addGroupMemberByID($uid, $gid)
474 $conn = gwvp_ConnectDB();
477 * CREATE TABLE "group_membership" (
478 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
479 "groupmember_groupid" INTEGER,
480 "groupmember_userid" INTEGER
483 $sql = "insert into group_membership values (null, '$gid', '$uid')";
491 function gwvp_addGroupMember($email, $groupname)
493 $conn = gwvp_ConnectDB();
495 $uid = gwvp_getUserId($email);
496 $gid = gwvp_getGroupId($groupname);
499 * CREATE TABLE "group_membership" (
500 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
501 "groupmember_groupid" INTEGER,
502 "groupmember_userid" INTEGER
505 if($uid!=false&&$gid!=false) gwvp_addGroupMemberByID($uid, $gid);
513 function gwvp_IsGroupMember($email, $groupname)
515 $conn = gwvp_ConnectDB();
517 // i think this is right
520 group_membership gm, groups g, users u
522 gm.groupmember_userid=u.users_id and
523 u.user_email='$email' and
524 gm.groupmember_groupid=g.groups_id and
525 g.groups_name='$groupname'
528 $res = $conn->query($sql);
530 foreach($res as $u_res) {
534 if($result == 0) return false;
535 if($result == 1) return true;
538 function gwvp_IsUserAdmin($email=null, $username = null)
540 $conn = gwvp_ConnectDB();
543 // TODO: clean this up, this should be a single query - idiot
545 $id = gwvp_getUserId($email);
546 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
547 } else if($username != null) {
548 $id = gwvp_getUserId(null, $username);
549 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
552 $res = $conn->query($sql);
555 foreach($res as $u_res) {
556 $gid[$rn] = $u_res["groupmember_groupid"];
560 foreach($gid as $gid_t) {
562 * CREATE TABLE groups (
565 "groups_is_admin" INTEGER,
566 "groups_owner_id" INTEGER
571 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
572 $res = $conn->query($sql);
573 foreach($res as $u_res) {
574 if($u_res["groups_is_admin"] == "1") return true;
581 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
584 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
585 "user_full_name" TEXT,
586 "user_password" TEXT,
587 "user_username" TEXT,
590 "user_status" INTEGER
594 $conn = gwvp_ConnectDB();
597 $sql = "update users set user_email='$email' where users_id='$userid'";
601 if($fullname != null) {
602 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
606 if($password != null) {
607 $shapass = sha1($password);
608 $sql = "update users set user_password='$shapass' where users_id='$userid'";
612 if($username != null) {
613 $sql = "update users set user_username='$username' where users_id='$userid'";
618 $sql = "update users set user_desc='$desc' where users_id='$userid'";
622 if($status != null) {
623 $sql = "update users set user_status='$status' where users_id='$userid'";
631 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
634 * CREATE TABLE groups (
637 "groups_is_admin" INTEGER,
638 "groups_owner_id" INTEGER
642 $conn = gwvp_ConnectDB();
644 if($groupname != null) {
645 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
649 if($group_is_admin != null) {
650 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
654 if($groups_owner_id != null) {
655 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
662 function gwvp_AddRepo($reponame, $repodesc, $repoowner)
664 $conn = gwvp_ConnectDB();
666 $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')";
671 function gwvp_GetRepoList()
673 $conn = gwvp_ConnectDB();
677 CREATE TABLE "repos" (
678 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
680 "repos_description" TEXT,
681 "repos_owner" INTEGER
686 $sql = "select * from repos";
688 $res = $conn->query($sql);
692 foreach($res as $u_res) {
693 $return[$rn]["id"] = $u_res["repos_id"];
694 $return[$rn]["name"] = $u_res["repos_name"];
695 $return[$rn]["description"] = $u_res["repos_description"];
696 $return[$rn]["owner"] = $u_res["repos_owner"];
702 /* functions we'll need to access data:
705 * getUserData(username)
707 * getGroupData(groupname)
709 * addGroupMember(...)
710 * deleteGroupMember(...)