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_is_admin" INTEGER,
30 "groups_owner_id" INTEGER
34 CREATE TABLE "repos" (
35 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
37 "repos_description" TEXT,
41 // this looks like null, <repoid>, <read|visible|write>, user:<uid>|group:<gid>|authed|anon
42 // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc)
43 // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo
44 // TODO: is this sufficient? i have to think about it
46 CREATE TABLE "repoperms" (
47 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
49 "repoperms_type" TEXT,
54 CREATE TABLE "config" (
60 CREATE TABLE "group_membership" (
61 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
62 "groupmember_groupid" INTEGER,
63 "groupmember_userid" INTEGER
67 $DB_CONNECTION = new PDO("sqlite:$dbloc");
68 } catch(PDOException $exep) {
69 error_log("execpt on db open");
74 $DB_CONNECTION->query($usersql);
75 $DB_CONNECTION->query($groupsql);
76 $DB_CONNECTION->query($reposql);
77 $DB_CONNECTION->query($configsql);
78 $DB_CONNECTION->query($groupmemsql);
81 function gwvp_isDBSetup()
83 // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay
84 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_url;
86 if($db_type == "sqlite") {
87 if(file_exists($db_url)) return true;
91 // TODO now for the connectables
95 function gwvp_ConnectDB()
97 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION;
99 // first check if $DB_CONNECTION IS live
100 if($DB_CONNECTION != false) return $DB_CONNECTION;
102 if($db_type == "sqlite") {
104 if(!file_exists($db_name)) {
105 error_log("$db_name does not exist - problem");
109 // and here we go with pdo.
110 error_log("attmpting to open db, $db_type:$db_url");
112 $DB_CONNECTION = new PDO("$db_type:$db_url");
113 } catch(PDOException $exep) {
114 error_log("execpt on db open");
118 return $DB_CONNECTION;
121 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
123 $conn = gwvp_ConnectDB();
125 // TODO: change from sha1
126 $shapass = sha1($password);
127 //error_log("Create user called with $email");
128 $conn->query("insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')");
130 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
131 "user_full_name" TEXT,
132 "user_password" TEXT,
133 "user_username" TEXT,
136 "user_status" INTEGER
141 function gwvp_getUser($username=null, $email=null, $id=null)
143 $conn = gwvp_ConnectDB();
145 if($username != null) {
146 $res = $conn->query("select * from users where user_username='$username'");
147 } else if($email != null) {
148 $res = $conn->query("select * from users where user_email='$email'");
149 } else if($id != null) {
150 $res = $conn->query("select * from users where users_id='$id'");
154 foreach($res as $u_res) {
155 $returns["id"] = $u_res["users_id"];
156 $returns["fullname"] = $u_res["user_full_name"];
157 $returns["password"] = $u_res["user_password"];
158 $returns["username"] = $u_res["user_username"];
159 $returns["email"] = $u_res["user_email"];
160 $returns["desc"] = $u_res["user_desc"];
161 $returns["status"] = $u_res["user_status"];
168 function gwvp_getUsers()
170 $conn = gwvp_ConnectDB();
172 $res = $conn->query("select * from users");
176 foreach($res as $u_res) {
177 $returns[$rn]["id"] = $u_res["users_id"];
178 $returns[$rn]["fullname"] = $u_res["user_full_name"];
179 $returns[$rn]["password"] = $u_res["user_password"];
180 $returns[$rn]["username"] = $u_res["user_username"];
181 $returns[$rn]["email"] = $u_res["user_email"];
182 $returns[$rn]["desc"] = $u_res["user_desc"];
183 $returns[$rn]["status"] = $u_res["user_status"];
190 function gwvp_deleteUser($email)
192 $conn = gwvp_ConnectDB();
194 $sql = "delete from users where user_email='$email'";
199 function gwvp_createGroup($group_name, $is_admin, $owner_id)
201 $conn = gwvp_ConnectDB();
204 * CREATE TABLE groups (
207 "groups_is_admin" INTEGER,
208 "groups_owner_id" INTEGER
217 $sql = "insert into groups values( null, '$group_name', '$is_admin_t', '$owner_id')";
223 function gwvp_deleteGroup($groupname)
225 $conn = gwvp_ConnectDB();
227 $sql = "delete from groups where groups_name='$groupname'";
232 function gwvp_getGroupsForUser($email)
234 $conn = gwvp_ConnectDB();
237 select g.groups_name from
238 group_membership gm, groups g, users u
240 gm.groupmember_userid=u.users_id and
241 u.user_email='$email' and
242 gm.groupmember_groupid=g.groups_id and
243 g.groups_name='$groupname'
247 select g.groups_name from
248 group_membership gm, groups g, users u
250 gm.groupmember_userid=u.users_id and
251 u.user_email='$email' and
252 gm.groupmember_groupid=g.groups_id
255 $res = $conn->query($sql);
259 foreach($res as $u_res) {
260 $return[$rn] = $u_res[0];
267 function gwvp_getGroupsOwnedByUser($email)
269 $conn = gwvp_ConnectDB();
272 select g.groups_name from
273 group_membership gm, groups g, users u
275 gm.groupmember_userid=u.users_id and
276 u.user_email='$email' and
277 gm.groupmember_groupid=g.groups_id and
278 g.groups_name='$groupname'
282 select g.groups_name from
285 u.user_email='$email' and
286 u.users_id=g.groups_owner_id
289 $res = $conn->query($sql);
293 foreach($res as $u_res) {
294 $return[$rn] = $u_res[0];
302 function gwvp_groupOwner($groupname)
304 $conn = gwvp_ConnectDB();
306 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
308 $res = $conn->query($sql);
310 foreach($res as $u_res) {
317 function gwvp_getGroups()
319 $conn = gwvp_ConnectDB();
321 $res = $conn->query("select * from groups");
325 foreach($res as $u_res) {
326 $returns[$rn]["id"] = $u_res["groups_id"];
327 $returns[$rn]["name"] = $u_res["groups_name"];
328 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
329 else $return[$rn]["admin"] = false;
330 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
331 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
338 function gwvp_getGroupId($groupname)
340 $conn = gwvp_ConnectDB();
342 $sql = "select groups_id from groups where groups_name='$groupname'";
344 $res = $conn->query($sql);
346 foreach($res as $u_res) {
347 $return = $u_res["groups_id"];
353 function gwvp_getUserId($useremail=null, $username = null)
355 $conn = gwvp_ConnectDB();
357 if($useremail != null) {
358 $sql = "select users_id from users where user_email='$useremail'";
359 } else if($username != null) {
360 $sql = "select users_id from users where user_username='$username'";
363 $res = $conn->query($sql);
365 foreach($res as $u_res) {
366 $return = $u_res["users_id"];
372 function gwvp_getUserEmail($id)
374 $conn = gwvp_ConnectDB();
376 $sql = "select user_email from users where users_id='$id'";
378 $res = $conn->query($sql);
380 foreach($res as $u_res) {
381 $return = $u_res["user_email"];
387 function gwvp_addGroupMember($email, $groupname)
389 $conn = gwvp_ConnectDB();
391 $uid = gwvp_getUserId($email);
392 $gid = gwvp_getGroupId($groupname);
395 * CREATE TABLE "group_membership" (
396 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
397 "groupmember_groupid" INTEGER,
398 "groupmember_userid" INTEGER
401 if($uid!=false&&$gid!=false) $sql = "insert into group_membership values (null, '$gid', '$uid')";
409 function gwvp_IsGroupMember($email, $groupname)
411 $conn = gwvp_ConnectDB();
413 // i think this is right
416 group_membership gm, groups g, users u
418 gm.groupmember_userid=u.users_id and
419 u.user_email='$email' and
420 gm.groupmember_groupid=g.groups_id and
421 g.groups_name='$groupname'
424 $res = $conn->query($sql);
426 foreach($res as $u_res) {
430 if($result == 0) return false;
431 if($result == 1) return true;
434 function gwvp_IsUserAdmin($email=null, $username = null)
436 $conn = gwvp_ConnectDB();
439 // TODO: clean this up, this should be a single query - idiot
441 $id = gwvp_getUserId($email);
442 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
443 } else if($username != null) {
444 $id = gwvp_getUserId(null, $username);
445 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
448 $res = $conn->query($sql);
450 foreach($res as $u_res) {
451 $gid[$rn] = $u_res["groupmember_groupid"];
455 foreach($gid as $gid_t) {
457 * CREATE TABLE groups (
460 "groups_is_admin" INTEGER,
461 "groups_owner_id" INTEGER
466 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
467 $res = $conn->query($sql);
468 foreach($res as $u_res) {
469 if($u_res["groups_is_admin"] == "1") return true;
476 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
479 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
480 "user_full_name" TEXT,
481 "user_password" TEXT,
482 "user_username" TEXT,
485 "user_status" INTEGER
489 $conn = gwvp_ConnectDB();
492 $sql = "update users set user_email='$email' where users_id='$userid'";
496 if($fullname != null) {
497 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
501 if($password != null) {
502 $shapass = sha1($password);
503 $sql = "update users set user_password='$shapass' where users_id='$userid'";
507 if($username != null) {
508 $sql = "update users set user_username='$username' where users_id='$userid'";
513 $sql = "update users set user_desc='$desc' where users_id='$userid'";
517 if($status != null) {
518 $sql = "update users set user_status='$status' where users_id='$userid'";
526 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
529 * CREATE TABLE groups (
532 "groups_is_admin" INTEGER,
533 "groups_owner_id" INTEGER
537 $conn = gwvp_ConnectDB();
539 if($groupname != null) {
540 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
544 if($group_is_admin != null) {
545 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
549 if($groups_owner_id != null) {
550 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
557 function gwvp_AddRepo($reponame, $repodesc, $repoowner)
559 $conn = gwvp_ConnectDB();
561 $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')";
566 function gwvp_GetRepoList()
568 $conn = gwvp_ConnectDB();
572 CREATE TABLE "repos" (
573 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
575 "repos_description" TEXT,
576 "repos_owner" INTEGER
581 $sql = "select * from repos";
583 $res = $conn->query($sql);
587 foreach($res as $u_res) {
588 $return[$rn]["id"] = $u_res["repos_id"];
589 $return[$rn]["name"] = $u_res["repos_name"];
590 $return[$rn]["description"] = $u_res["repos_description"];
591 $return[$rn]["owner"] = $u_res["repos_owner"];
597 /* functions we'll need to access data:
600 * getUserData(username)
602 * getGroupData(groupname)
604 * addGroupMember(...)
605 * deleteGroupMember(...)