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_getUsers()
131 $conn = gwvp_ConnectDB();
133 $res = $conn->query("select * from users");
137 foreach($res as $u_res) {
138 $returns[$rn]["id"] = $u_res["users_id"];
139 $returns[$rn]["fullname"] = $u_res["user_full_name"];
140 $returns[$rn]["password"] = $u_res["user_password"];
141 $returns[$rn]["username"] = $u_res["user_username"];
142 $returns[$rn]["email"] = $u_res["user_email"];
143 $returns[$rn]["desc"] = $u_res["user_desc"];
144 $returns[$rn]["status"] = $u_res["user_status"];
151 function gwvp_deleteUser($email)
153 $conn = gwvp_ConnectDB();
155 $sql = "delete from users where user_email='$email'";
160 function gwvp_createGroup($group_name, $is_admin, $owner_id)
162 $conn = gwvp_ConnectDB();
165 * CREATE TABLE groups (
168 "groups_is_admin" INTEGER,
169 "groups_owner_id" INTEGER
178 $sql = "insert into groups values( null, '$group_name', '$is_admin_t', '$owner_id')";
184 function gwvp_deleteGroup($groupname)
186 $conn = gwvp_ConnectDB();
188 $sql = "delete from groups where groups_name='$groupname'";
193 function gwvp_getGroupsForUser($email)
195 $conn = gwvp_ConnectDB();
198 select g.groups_name from
199 group_membership gm, groups g, users u
201 gm.groupmember_userid=u.users_id and
202 u.user_email='$email' and
203 gm.groupmember_groupid=g.groups_id and
204 g.groups_name='$groupname'
208 select g.groups_name from
209 group_membership gm, groups g, users u
211 gm.groupmember_userid=u.users_id and
212 u.user_email='$email' and
213 gm.groupmember_groupid=g.groups_id
216 $res = $conn->query($sql);
220 foreach($res as $u_res) {
221 $return[$rn] = $u_res[0];
228 function gwvp_getGroupsOwnedByUser($email)
230 $conn = gwvp_ConnectDB();
233 select g.groups_name from
234 group_membership gm, groups g, users u
236 gm.groupmember_userid=u.users_id and
237 u.user_email='$email' and
238 gm.groupmember_groupid=g.groups_id and
239 g.groups_name='$groupname'
243 select g.groups_name from
246 u.user_email='$email' and
247 u.users_id=g.groups_owner_id
250 $res = $conn->query($sql);
254 foreach($res as $u_res) {
255 $return[$rn] = $u_res[0];
263 function gwvp_groupOwner($groupname)
265 $conn = gwvp_ConnectDB();
267 $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
269 $res = $conn->query($sql);
271 foreach($res as $u_res) {
278 function gwvp_getGroups()
280 $conn = gwvp_ConnectDB();
282 $res = $conn->query("select * from groups");
286 foreach($res as $u_res) {
287 $returns[$rn]["id"] = $u_res["groups_id"];
288 $returns[$rn]["name"] = $u_res["groups_name"];
289 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
290 else $return[$rn]["admin"] = false;
291 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
292 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
299 function gwvp_getGroupId($groupname)
301 $conn = gwvp_ConnectDB();
303 $sql = "select groups_id from groups where groups_name='$groupname'";
305 $res = $conn->query($sql);
307 foreach($res as $u_res) {
308 $return = $u_res["groups_id"];
314 function gwvp_getUserId($useremail)
316 $conn = gwvp_ConnectDB();
318 $sql = "select users_id from users where user_email='$useremail'";
320 $res = $conn->query($sql);
322 foreach($res as $u_res) {
323 $return = $u_res["users_id"];
329 function gwvp_getUserEmail($id)
331 $conn = gwvp_ConnectDB();
333 $sql = "select user_email from users where users_id='$id'";
335 $res = $conn->query($sql);
337 foreach($res as $u_res) {
338 $return = $u_res["user_email"];
344 function gwvp_addGroupMember($email, $groupname)
346 $conn = gwvp_ConnectDB();
348 $uid = gwvp_getUserId($email);
349 $gid = gwvp_getGroupId($groupname);
352 * CREATE TABLE "group_membership" (
353 "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
354 "groupmember_groupid" INTEGER,
355 "groupmember_userid" INTEGER
358 if($uid!=false&&$gid!=false) $sql = "insert into group_membership values (null, '$gid', '$uid')";
366 function gwvp_IsGroupMember($email, $groupname)
368 $conn = gwvp_ConnectDB();
370 // i think this is right
373 group_membership gm, groups g, users u
375 gm.groupmember_userid=u.users_id and
376 u.user_email='$email' and
377 gm.groupmember_groupid=g.groups_id and
378 g.groups_name='$groupname'
381 $res = $conn->query($sql);
383 foreach($res as $u_res) {
387 if($result == 0) return false;
388 if($result == 1) return true;
391 function gwvp_IsUserAdmin($email)
393 $conn = gwvp_ConnectDB();
395 $id = gwvp_getUserId($email);
396 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
398 $res = $conn->query($sql);
400 foreach($res as $u_res) {
401 $gid[$rn] = $u_res["groupmember_groupid"];
405 foreach($gid as $gid_t) {
407 * CREATE TABLE groups (
410 "groups_is_admin" INTEGER,
411 "groups_owner_id" INTEGER
416 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
417 $res = $conn->query($sql);
418 foreach($res as $u_res) {
419 if($u_res["groups_is_admin"] == "1") return true;
426 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
429 * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
430 "user_full_name" TEXT,
431 "user_password" TEXT,
432 "user_username" TEXT,
435 "user_status" INTEGER
439 $conn = gwvp_ConnectDB();
442 $sql = "update users set user_email='$email' where users_id='$userid'";
446 if($fullname != null) {
447 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
451 if($password != null) {
452 $shapass = sha1($password);
453 $sql = "update users set user_password='$shapass' where users_id='$userid'";
457 if($username != null) {
458 $sql = "update users set user_username='$username' where users_id='$userid'";
463 $sql = "update users set user_desc='$desc' where users_id='$userid'";
467 if($status != null) {
468 $sql = "update users set user_status='$status' where users_id='$userid'";
476 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
479 * CREATE TABLE groups (
482 "groups_is_admin" INTEGER,
483 "groups_owner_id" INTEGER
487 $conn = gwvp_ConnectDB();
489 if($groupname != null) {
490 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
494 if($group_is_admin != null) {
495 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
499 if($groups_owner_id != null) {
500 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
506 /* functions we'll need to access data:
509 * getUserData(username)
511 * getGroupData(groupname)
513 * addGroupMember(...)
514 * deleteGroupMember(...)