From 047523a5083efd83dcd94b6d2763c9fddb913c90 Mon Sep 17 00:00:00 2001 From: paulr Date: Wed, 2 Nov 2011 13:39:57 +1100 Subject: [PATCH] User page code for user/group admin database code unit tests for database bits --- gwvplib/gwvpdatabase.php | 270 ++++++++++++++++++++++++++++++++++++++++++++- gwvplib/gwvpdebug.php | 42 +++++++ gwvplib/gwvpsetup.php | 2 +- gwvplib/gwvpuseradmin.php | 135 ++++++++++++++++++++++- gwvplib/gwvpweb.php | 2 +- unittests/sqlitedb.php | 96 +++++++++++++++- www/css/normal.css | 29 +++++- 7 files changed, 564 insertions(+), 12 deletions(-) diff --git a/gwvplib/gwvpdatabase.php b/gwvplib/gwvpdatabase.php index 1f0282f..c2d37cf 100644 --- a/gwvplib/gwvpdatabase.php +++ b/gwvplib/gwvpdatabase.php @@ -82,18 +82,28 @@ function gwvp_isDBSetup() function gwvp_ConnectDB() { - global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_url, $DB_CONNECTION; + global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION; // first check if $DB_CONNECTION IS live if($DB_CONNECTION != false) return $DB_CONNECTION; + if($db_type == "sqlite") { + $db_url = $db_name; + if(!file_exists($db_name)) { + error_log("$db_name does not exist - problem"); + } + } + // and here we go with pdo. + error_log("attmpting to open db, $db_type:$db_url"); try { $DB_CONNECTION = new PDO("$db_type:$db_url"); } catch(PDOException $exep) { error_log("execpt on db open"); return false; } + + return $DB_CONNECTION; } function gwvp_createUser($email, $fullname, $password, $nick, $desc, $status) @@ -102,6 +112,7 @@ function gwvp_createUser($email, $fullname, $password, $nick, $desc, $status) // TODO: change from sha1 $shapass = sha1($password); + //error_log("Create user called with $email"); $conn->query("insert into users values (null, '$fullname', '$shapass', '$nick', '$email', '$desc', '$status')"); /* * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT, @@ -179,6 +190,90 @@ function gwvp_deleteGroup($groupname) $conn->query($sql); } +function gwvp_getGroupsForUser($email) +{ + $conn = gwvp_ConnectDB(); + + /* + select g.groups_name from + group_membership gm, groups g, users u + where + gm.groupmember_userid=u.users_id and + u.user_email='$email' and + gm.groupmember_groupid=g.groups_id and + g.groups_name='$groupname' + */ + + $sql = " + select g.groups_name from + group_membership gm, groups g, users u + where + gm.groupmember_userid=u.users_id and + u.user_email='$email' and + gm.groupmember_groupid=g.groups_id + "; + + $res = $conn->query($sql); + + $return = false; + $rn = 0; + foreach($res as $u_res) { + $return[$rn] = $u_res[0]; + $rn++; + } + + return $return; +} + +function gwvp_getGroupsOwnedByUser($email) +{ + $conn = gwvp_ConnectDB(); + + /* + select g.groups_name from + group_membership gm, groups g, users u + where + gm.groupmember_userid=u.users_id and + u.user_email='$email' and + gm.groupmember_groupid=g.groups_id and + g.groups_name='$groupname' + */ + + $sql = " + select g.groups_name from + groups g, users u + where + u.user_email='$email' and + u.users_id=g.groups_owner_id + "; + + $res = $conn->query($sql); + + $return = false; + $rn = 0; + foreach($res as $u_res) { + $return[$rn] = $u_res[0]; + $rn++; + } + + return $return; + +} + +function gwvp_groupOwner($groupname) +{ + $conn = gwvp_ConnectDB(); + + $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id"; + + $res = $conn->query($sql); + $return = false; + foreach($res as $u_res) { + $return = $u_res[0]; + } + + return $return; +} function gwvp_getGroups() { @@ -231,9 +326,182 @@ function gwvp_getUserId($useremail) return $return; } +function gwvp_getUserEmail($id) +{ + $conn = gwvp_ConnectDB(); + + $sql = "select user_email from users where users_id='$id'"; + + $res = $conn->query($sql); + $return = false; + foreach($res as $u_res) { + $return = $u_res["user_email"]; + } + + return $return; +} + function gwvp_addGroupMember($email, $groupname) { + $conn = gwvp_ConnectDB(); + + $uid = gwvp_getUserId($email); + $gid = gwvp_getGroupId($groupname); + + /* + * CREATE TABLE "group_membership" ( + "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT, + "groupmember_groupid" INTEGER, + "groupmember_userid" INTEGER + + */ + if($uid!=false&&$gid!=false) $sql = "insert into group_membership values (null, '$gid', '$uid')"; + else return false; + + $conn->query($sql); + + return true; +} + +function gwvp_IsGroupMember($email, $groupname) +{ + $conn = gwvp_ConnectDB(); + + // i think this is right + $sql = " + select count(*) from + group_membership gm, groups g, users u + where + gm.groupmember_userid=u.users_id and + u.user_email='$email' and + gm.groupmember_groupid=g.groups_id and + g.groups_name='$groupname' + "; + + $res = $conn->query($sql); + $result = 0; + foreach($res as $u_res) { + $result = $u_res[0]; + } + + if($result == 0) return false; + if($result == 1) return true; +} + +function gwvp_IsUserAdmin($email) +{ + $conn = gwvp_ConnectDB(); + + $id = gwvp_getUserId($email); + $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'"; + + $res = $conn->query($sql); + $rn = 0; + foreach($res as $u_res) { + $gid[$rn] = $u_res["groupmember_groupid"]; + $rn++; + } + + foreach($gid as $gid_t) { + /* + * CREATE TABLE groups ( + "groups_id" INTEGER, + "groups_name" TEXT, + "groups_is_admin" INTEGER, + "groups_owner_id" INTEGER + )'; + + */ + + $sql = "select groups_is_admin from groups where groups_id='$gid_t'"; + $res = $conn->query($sql); + foreach($res as $u_res) { + if($u_res["groups_is_admin"] == "1") return true; + } + } + + return false; +} + +function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $nickname=null, $desc=null, $status=null) +{ + /* + * "users_id" INTEGER PRIMARY KEY AUTOINCREMENT, + "user_full_name" TEXT, + "user_password" TEXT, + "user_nickname" TEXT, + "user_email" TEXT, + "user_desc" TEXT, + "user_status" INTEGER + + */ + + $conn = gwvp_ConnectDB(); + + if($email != null) { + $sql = "update users set user_email='$email' where users_id='$userid'"; + $conn->query($sql); + } + + if($fullname != null) { + $sql = "update users set user_full_name='$fullname' where users_id='$userid'"; + $conn->query($sql); + } + + if($password != null) { + $shapass = sha1($password); + $sql = "update users set user_password='$shapass' where users_id='$userid'"; + $conn->query($sql); + } + + if($nickname != null) { + $sql = "update users set user_nickname='$nickname' where users_id='$userid'"; + $conn->query($sql); + } + + if($desc != null) { + $sql = "update users set user_desc='$desc' where users_id='$userid'"; + $conn->query($sql); + } + + if($status != null) { + $sql = "update users set user_status='$status' where users_id='$userid'"; + $conn->query($sql); + } + + return true; +} + + +function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null) +{ + /* + * CREATE TABLE groups ( + "groups_id" INTEGER, + "groups_name" TEXT, + "groups_is_admin" INTEGER, + "groups_owner_id" INTEGER + )'; + + */ + $conn = gwvp_ConnectDB(); + + if($groupname != null) { + $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'"; + $conn->query($sql); + } + + if($group_is_admin != null) { + $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'"; + $conn->query($sql); + } + + if($groups_owner_id != null) { + $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'"; + $conn->query($sql); + } + return true; } /* functions we'll need to access data: * diff --git a/gwvplib/gwvpdebug.php b/gwvplib/gwvpdebug.php index c580ad1..ae98816 100644 --- a/gwvplib/gwvpdebug.php +++ b/gwvplib/gwvpdebug.php @@ -58,6 +58,45 @@ function gwvp_DebugCall() gwvp_SendMessage("info", "random message"); header("Location: $BASE_URL/debug"); break; + case "createusersandgroups": + // gwvp_createUser($email, $fullname, $password, $nick, $desc, $status) + for($i=20; $i<120; $i++) { + gwvp_createUser("test$i@test.com", "test$i", "asdf", "test$i", "test$i user", 0); + } + + // function gwvp_createGroup($group_name, $is_admin, $owner_id) + for($i=20; $i<40; $i++) { + if(($i%5) == 0) $isadmin = 1; + else $isadmin = 0; + gwvp_createGroup("testgroup$i", "$isadmin", "$i"); + } + + // gwvp_addGroupMember($email, $groupname) + // + for($i=20; $i<120; $i++) { + for($k=0; $k<4; $k++) { + $gid = rand(20,39); + gwvp_addGroupMember("test$i@test.com", "testgroup$gid"); + } + } + gwvp_SendMessage("info", "Create users and groups done"); + header("Location: $BASE_URL/debug"); + break; + case "recreatedb": + global $db_name; + unlink("$db_name"); + gwvp_dbCreateSQLiteStructure("$db_name"); + gwvp_SendMessage("info", "blank db re-created"); + header("Location: $BASE_URL/debug"); + break; + case "dropallusersandgroups": + $conn = gwvp_ConnectDB(); + $conn->query("delete from users"); + $conn->query("delete from groups"); + $conn->query("delete from group_membership"); + gwvp_SendMessage("info", "Drop all users and groups done"); + header("Location: $BASE_URL/debug"); + break; default: gwvp_goMainPage("gwvp_DebugBody"); return; @@ -74,6 +113,9 @@ function gwvp_DebugBody() ?> Generate error message
Generate info message
+ Create a bunch of users and groups
+ Drop users/groups/groupmemberships
+ Recreate db
User/Group Administration"; + echo "On this page you can manage users, groups, group membership and update your profile
"; + echo ""; + + // Header part of table + echo ""; + + echo ""; + // user profile bit + echo ""; + + // now the group bit for the user + echo ""; + echo ""; + + + echo ""; + + // create user bit + echo ""; + + // user list + echo "

My Profile

My Groups

"; + echo "User profile bits go here"; + echo ""; + echo "User owned groups, and groups their a member of go here"; + echo "

Users

Groups

"; + + echo "Create User
"; + echo "
"; + echo ""; + echo ""; + echo ""; + echo ""; + echo ""; + echo ""; + echo ""; + echo ""; + echo "
EMailFull Name
PasswordPassword Confirm
NicknameDescription
"; + echo "
"; + + echo "		"; + + // Create group + echo "
"; + echo ""; + echo ""; + echo ""; + echo ""; + echo ""; + echo "
Group Name
Admin Group?
Owner
"; + echo "
"; + + + echo "
"; + echo ""; + echo ""; + /* + * $returns[$rn]["id"] = $u_res["users_id"]; + $returns[$rn]["fullname"] = $u_res["user_full_name"]; + $returns[$rn]["password"] = $u_res["user_password"]; + $returns[$rn]["nickname"] = $u_res["user_nickname"]; + $returns[$rn]["email"] = $u_res["user_email"]; + $returns[$rn]["desc"] = $u_res["user_desc"]; + $returns[$rn]["status"] = $u_res["user_status"]; + + */ + foreach($users as $u_users) { + $email = $u_users["email"]; + $fullname = $u_users["fullname"]; + $nickname = $u_users["nickname"]; + $desc = $u_users["desc"]; + $status = $u_users["status"]; + if(gwvp_IsUserAdmin($email) == 1) { + $globaladmin = "Yes"; + } else { + $globaladmin = "No"; + } + + $ingroups = gwvp_getGroupsForUser($email); + $ugroups = ""; + foreach($ingroups as $grname) { + $ugroups .= "$grname "; + } + trim($ugroups); + + $ownedgroups = gwvp_getGroupsOwnedByUser($email); + $ogroups = ""; + if($ownedgroups == false) $ogroups = "-"; + else { + foreach($ownedgroups as $gr_u) { + $ogroups .= "$gr_u "; + } + } + + echo ""; + } + echo "
EMailNick NameFull NameIn GroupsOwns GroupsDescriptionIs Global Admin?Status
$email$nickname$fullname$ugroups$ogroups$desc$globaladmin$status
"; + + echo "		"; + + // group part of table + + echo ""; + echo ""; + foreach($groups as $u_groups) { + /* + * $returns[$rn]["id"] = $u_res["groups_id"]; + $returns[$rn]["name"] = $u_res["groups_name"]; + if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true; + else $return[$rn]["admin"] = false; + $returns[$rn]["admin"] = $u_res["groups_is_admin"]; + $returns[$rn]["ownerid"] = $u_res["groups_owner_id"]; + + */ + $gname = $u_groups["name"]; + $owner = gwvp_getUserEmail($u_groups["ownerid"]); + if($u_groups["admin"]) $gadmin = "Yes"; + else $gadmin = "No"; + echo ""; + } + echo "
Group NameOwnerGlobal Admin Group?
$gname$owner$gadmin
"; + echo "
"; } diff --git a/gwvplib/gwvpweb.php b/gwvplib/gwvpweb.php index 474cdff..01215ab 100644 --- a/gwvplib/gwvpweb.php +++ b/gwvplib/gwvpweb.php @@ -89,7 +89,7 @@ function gwvp_goMainPage($bodyFunction = null) echo ""; if(isset($_SESSION["message"])) { - echo ""; } diff --git a/unittests/sqlitedb.php b/unittests/sqlitedb.php index 21de42a..c5c0d37 100644 --- a/unittests/sqlitedb.php +++ b/unittests/sqlitedb.php @@ -17,9 +17,9 @@ require_once("gwvplib.php"); global $db_type, $db_url; $db_type = "sqlite"; -$db_url = "/tmp/unittestdb.db"; +$db_name = "/tmp/unittestdb.db"; -gwvp_dbCreateSQLiteStructure("$db_url"); +gwvp_dbCreateSQLiteStructure("$db_name"); gwvp_ConnectDB(); @@ -27,6 +27,7 @@ gwvp_ConnectDB(); // test users creation gwvp_createUser("test@test.com", "test user", "password", "nick", "desc", "0"); gwvp_createUser("test2@test.com", "test2 user", "password", "nick2", "desc2", "0"); +gwvp_createUser("test3@test.com", "test2 user", "password", "nick2", "desc2", "0"); $users = gwvp_getUsers(); echo "Users\n"; echo "\tCreate test@test.com\n"; @@ -67,8 +68,9 @@ if($users[0]["email"] != "test2@test.com") { // function gwvp_createGroup($group_name, $is_admin, $owner_id) echo "\n\nGroups\n"; echo "\tCreate testgroups\n"; -gwvp_createGroup("testgroup", true, "1"); -gwvp_createGroup("testgroup2", true, "1"); +gwvp_createGroup("testgroup", true, "3"); +gwvp_createGroup("testgroup2", false, "3"); +gwvp_createGroup("testgroup3", true, "2"); $groups = gwvp_getGroups(); if($groups[0]["name"] != "testgroup") { echo "\t\tGroup 0 isnt correct: ".$groups[0]["name"]."\n"; @@ -103,5 +105,89 @@ if($groups[0]["name"] != "testgroup2") { echo "\t\tGroup 0 correct\n"; } -unlink($db_url); + +// setup some user permissions to groups +echo "\n\nGroup Memberships\n"; +echo "\tAdd to groups\n"; +if(!gwvp_addGroupMember("test2@test.com", "testgroup2")) { + echo "\t\tadd test2@test.com to testgroup2 failed\n"; +} else { + echo "\t\tadd test2@test.com to testgroup2 ok\n"; +} +if(!gwvp_addGroupMember("test3@test.com", "testgroup3")) { + echo "\t\tadd test3@test.com to testgroup3 failed\n"; +} else { + echo "\t\tadd test2@test.com to testgroup2 ok\n"; +} + +echo "\tTest Group Membership\n"; +// function gwvp_IsGroupMember($email, $groupname) +if(!gwvp_isGroupMember("test2@test.com", "testgroup2")) { + echo "\t\ttest2@test.com in testgorup2 FAILED\n"; +} else { + echo "\t\ttest2@test.com in testgroup2 ok\n"; +} +if(!gwvp_isGroupMember("test3@test.com", "testgroup3")) { + echo "\t\ttest2@test.com in testgorup2 FAILED\n"; +} else { + echo "\t\ttest3@test.com in testgroup3 ok\n"; +} +if(gwvp_isGroupMember("test2@test.com", "testgroup3")) { + echo "\t\ttest2@test.com in testgorup3 FAILED\n"; +} else { + echo "\t\ttest2@test.com in testgroup3 ok\n"; +} +if(gwvp_isGroupMember("test3@test.com", "testgroup2")) { + echo "\t\ttest3@test.com in testgorup2 FAILED\n"; +} else { + echo "\t\ttest3@test.com in testgroup2 ok\n"; +} + +echo "\tTest Admin Role\n"; +if(gwvp_IsUserAdmin("test2@test.com")) { + echo "\t\tUser test2@test.com reported admin FAILED\n"; +} else { + echo "\t\tUset test2@test.com reported as not admin, ok\n"; +} +if(!gwvp_IsUserAdmin("test3@test.com")) { + echo "\t\tUser test3@test.com reported not admin FAILED\n"; +} else { + echo "\t\tUset test3@test.com reported as admin, ok\n"; +} + +echo "\ttest group memberships\n"; +gwvp_createGroup("testgroup4", false, "1"); +gwvp_createGroup("testgroup5", true, "1"); +gwvp_addGroupMember("test3@test.com", "testgroup4"); +gwvp_addGroupMember("test3@test.com", "testgroup5"); +gwvp_addGroupMember("test2@test.com", "testgroup5"); +$groupstest2 = gwvp_getGroupsForUser("test2@test.com"); +$groupstest3 = gwvp_getGroupsForUser("test3@test.com"); +echo "\t\tGroups for test2 should be testgroup2, testgroup5 - "; +if($groupstest2[0] != "testgroup2"||$groupstest2[1] != "testgroup5") { + echo "FAILED!\n"; +} else { + echo "ok\n"; +} +echo "\t\tGroups for test3 should be testgroup3, testgroup4, testgroup5 - "; +if($groupstest3[0] != "testgroup3"||$groupstest3[1] != "testgroup4"||$groupstest3[2] != "testgroup5") { + echo "FAILED!\n"; +} else { + echo "ok\n"; +} + +echo "\tTest Group Ownership\n"; +if(gwvp_groupOwner("testgroup2") != "test3@test.com") { + echo "\t\tTest owner of testgroup2 = test3@test.com - FAILED!\n"; +} else { + echo "\t\tTest owner of testgroup2 = test3@test.com - ok!\n"; +} +if(gwvp_groupOwner("testgroup3") != "test2@test.com") { + echo "\t\tTest owner of testgroup3 = test2@test.com - FAILED!\n"; +} else { + echo "\t\tTest owner of testgroup3 = test2@test.com - ok!\n"; +} + + +unlink($db_name); ?> \ No newline at end of file diff --git a/www/css/normal.css b/www/css/normal.css index 78f157d..7a72dd3 100644 --- a/www/css/normal.css +++ b/www/css/normal.css @@ -130,16 +130,39 @@ div.wrapper { input[type=text] { width: 200px; - border: 1px solid + border: 1px solid; } input.login { width: 100px; - border: 1px solid + border: 1px solid; } .loginbutton { - border: 1px solid + border: 1px solid; +} + +.buttons { + border: 1px solid; +} + +.mycheckbox { + border: 0px solid; + border-color:#000; +} + +.myselect { + + border-color:#000; +} + +#myidselect { + + border-color:#000; +} + +.selectpretty select { + border: 1px solid; } input.long { -- 1.7.0.4
"; + echo "
"; gwvp_MessageBuilder(); echo "