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,
42 CREATE TABLE "config" (
48 CREATE TABLE "group_membership" (
49 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
50 "groupmember_groupid" INTEGER,
51 "groupmember_userid" INTEGER
55 $DB_CONNECTION = new PDO("sqlite:$dbloc");
56 } catch(PDOException $exep) {
57 error_log("execpt on db open");
62 $DB_CONNECTION->query($usersql);
63 $DB_CONNECTION->query($groupsql);
64 $DB_CONNECTION->query($reposql);
65 $DB_CONNECTION->query($configsql);
66 $DB_CONNECTION->query($groupmemsql);
69 function gwvp_isDBSetup()
71 // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay
72 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_url;
74 if($db_type == "sqlite") {
75 if(file_exists($db_url)) return true;
79 // TODO now for the connectables
83 function gwvp_ConnectDB()
85 global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION;
87 // first check if $DB_CONNECTION IS live
88 if($DB_CONNECTION != false) return $DB_CONNECTION;
90 if($db_type == "sqlite") {
92 if(!file_exists($db_name)) {
93 error_log("$db_name does not exist - problem");
97 // and here we go with pdo.
98 error_log("attmpting to open db, $db_type:$db_url");
100 $DB_CONNECTION = new PDO("$db_type:$db_url");
101 } catch(PDOException $exep) {
102 error_log("execpt on db open");
106 return $DB_CONNECTION;
109 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
111 $conn = gwvp_ConnectDB();
113 // TODO: change from sha1
114 $shapass = sha1($password);
115 //error_log("Create user called with $email");
116 $conn->query("insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')");
118 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
119 "user_full_name" TEXT,
120 "user_password" TEXT,
121 "user_username" TEXT,
124 "user_status" INTEGER
129 function gwvp_getUser($username=null, $email=null, $id=null)
131 $conn = gwvp_ConnectDB();
133 if($username != null) {
134 $res = $conn->query("select * from users where user_username='$username'");
135 } else if($email != null) {
136 $res = $conn->query("select * from users where user_email='$email'");
137 } else if($id != null) {
138 $res = $conn->query("select * from users where users_id='$id'");
142 foreach($res as $u_res) {
143 $returns["id"] = $u_res["users_id"];
144 $returns["fullname"] = $u_res["user_full_name"];
145 $returns["password"] = $u_res["user_password"];
146 $returns["username"] = $u_res["user_username"];
147 $returns["email"] = $u_res["user_email"];
148 $returns["desc"] = $u_res["user_desc"];
149 $returns["status"] = $u_res["user_status"];
156 function gwvp_getUsers()
158 $conn = gwvp_ConnectDB();
160 $res = $conn->query("select * from users");
164 foreach($res as $u_res) {
165 $returns[$rn]["id"] = $u_res["users_id"];
166 $returns[$rn]["fullname"] = $u_res["user_full_name"];
167 $returns[$rn]["password"] = $u_res["user_password"];
168 $returns[$rn]["username"] = $u_res["user_username"];
169 $returns[$rn]["email"] = $u_res["user_email"];
170 $returns[$rn]["desc"] = $u_res["user_desc"];
171 $returns[$rn]["status"] = $u_res["user_status"];
178 function gwvp_deleteUser($email)
180 $conn = gwvp_ConnectDB();
182 $sql = "delete from users where user_email='$email'";
187 function gwvp_createGroup($group_name, $is_admin, $owner_id)
189 $conn = gwvp_ConnectDB();
192 * CREATE TABLE groups (
195 "groups_is_admin" INTEGER,
196 "groups_owner_id" INTEGER
205 $sql = "insert into groups values( null, '$group_name', '$is_admin_t', '$owner_id')";
211 function gwvp_deleteGroup($groupname)
213 $conn = gwvp_ConnectDB();
215 $sql = "delete from groups where groups_name='$groupname'";
220 function gwvp_getGroupsForUser($email)
222 $conn = gwvp_ConnectDB();
225 select g.groups_name from
226 group_membership gm, groups g, users u
228 gm.groupmember_userid=u.users_id and
229 u.user_email='$email' and
230 gm.groupmember_groupid=g.groups_id and
231 g.groups_name='$groupname'
235 select g.groups_name from
236 group_membership gm, groups g, users u
238 gm.groupmember_userid=u.users_id and
239 u.user_email='$email' and
240 gm.groupmember_groupid=g.groups_id
243 $res = $conn->query($sql);
247 foreach($res as $u_res) {
248 $return[$rn] = $u_res[0];
255 function gwvp_getGroupsOwnedByUser($email)
257 $conn = gwvp_ConnectDB();
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 and
266 g.groups_name='$groupname'
270 select g.groups_name from
273 u.user_email='$email' and
274 u.users_id=g.groups_owner_id
277 $res = $conn->query($sql);
281 foreach($res as $u_res) {
282 $return[$rn] = $u_res[0];
290 function gwvp_groupOwner($groupname)
292 $conn = gwvp_ConnectDB();
294 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
296 $res = $conn->query($sql);
298 foreach($res as $u_res) {
305 function gwvp_getGroups()
307 $conn = gwvp_ConnectDB();
309 $res = $conn->query("select * from groups");
313 foreach($res as $u_res) {
314 $returns[$rn]["id"] = $u_res["groups_id"];
315 $returns[$rn]["name"] = $u_res["groups_name"];
316 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
317 else $return[$rn]["admin"] = false;
318 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
319 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
326 function gwvp_getGroupId($groupname)
328 $conn = gwvp_ConnectDB();
330 $sql = "select groups_id from groups where groups_name='$groupname'";
332 $res = $conn->query($sql);
334 foreach($res as $u_res) {
335 $return = $u_res["groups_id"];
341 function gwvp_getUserId($useremail=null, $username = null)
343 $conn = gwvp_ConnectDB();
345 if($useremail != null) {
346 $sql = "select users_id from users where user_email='$useremail'";
347 } else if($username != null) {
348 $sql = "select users_id from users where user_username='$username'";
351 $res = $conn->query($sql);
353 foreach($res as $u_res) {
354 $return = $u_res["users_id"];
360 function gwvp_getUserEmail($id)
362 $conn = gwvp_ConnectDB();
364 $sql = "select user_email from users where users_id='$id'";
366 $res = $conn->query($sql);
368 foreach($res as $u_res) {
369 $return = $u_res["user_email"];
375 function gwvp_addGroupMember($email, $groupname)
377 $conn = gwvp_ConnectDB();
379 $uid = gwvp_getUserId($email);
380 $gid = gwvp_getGroupId($groupname);
383 * CREATE TABLE "group_membership" (
384 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
385 "groupmember_groupid" INTEGER,
386 "groupmember_userid" INTEGER
389 if($uid!=false&&$gid!=false) $sql = "insert into group_membership values (null, '$gid', '$uid')";
397 function gwvp_IsGroupMember($email, $groupname)
399 $conn = gwvp_ConnectDB();
401 // i think this is right
404 group_membership gm, groups g, users u
406 gm.groupmember_userid=u.users_id and
407 u.user_email='$email' and
408 gm.groupmember_groupid=g.groups_id and
409 g.groups_name='$groupname'
412 $res = $conn->query($sql);
414 foreach($res as $u_res) {
418 if($result == 0) return false;
419 if($result == 1) return true;
422 function gwvp_IsUserAdmin($email=null, $username = null)
424 $conn = gwvp_ConnectDB();
427 // TODO: clean this up, this should be a single query - idiot
429 $id = gwvp_getUserId($email);
430 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
431 } else if($username != null) {
432 $id = gwvp_getUserId(null, $username);
433 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
436 $res = $conn->query($sql);
438 foreach($res as $u_res) {
439 $gid[$rn] = $u_res["groupmember_groupid"];
443 foreach($gid as $gid_t) {
445 * CREATE TABLE groups (
448 "groups_is_admin" INTEGER,
449 "groups_owner_id" INTEGER
454 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
455 $res = $conn->query($sql);
456 foreach($res as $u_res) {
457 if($u_res["groups_is_admin"] == "1") return true;
464 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
467 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
468 "user_full_name" TEXT,
469 "user_password" TEXT,
470 "user_username" TEXT,
473 "user_status" INTEGER
477 $conn = gwvp_ConnectDB();
480 $sql = "update users set user_email='$email' where users_id='$userid'";
484 if($fullname != null) {
485 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
489 if($password != null) {
490 $shapass = sha1($password);
491 $sql = "update users set user_password='$shapass' where users_id='$userid'";
495 if($username != null) {
496 $sql = "update users set user_username='$username' where users_id='$userid'";
501 $sql = "update users set user_desc='$desc' where users_id='$userid'";
505 if($status != null) {
506 $sql = "update users set user_status='$status' where users_id='$userid'";
514 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
517 * CREATE TABLE groups (
520 "groups_is_admin" INTEGER,
521 "groups_owner_id" INTEGER
525 $conn = gwvp_ConnectDB();
527 if($groupname != null) {
528 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
532 if($group_is_admin != null) {
533 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
537 if($groups_owner_id != null) {
538 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
544 /* functions we'll need to access data:
547 * getUserData(username)
549 * getGroupData(groupname)
551 * addGroupMember(...)
552 * deleteGroupMember(...)