gwvplib/gwvpdatabase.php

   1 <?php
   2
   3 global $DB_CONNECTION;
   4 $DB_CONNECTION = false;
   5
   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()
   8 {
   9
  10 }
  11
  12 function gwvp_dbCreateSQLiteStructure($dbloc)
  13 {
  14         $usersql = '
  15                 CREATE TABLE "users" (
  16             "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  17             "user_full_name" TEXT,
  18             "user_password" TEXT,
  19             "user_username" TEXT,
  20             "user_email" TEXT,
  21             "user_desc" TEXT,
  22             "user_status" INTEGER
  23                 )';
  24
  25         $groupsql = '
  26                 CREATE TABLE groups (
  27             "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  28             "groups_name" TEXT,
  29             "groups_is_admin" INTEGER,
  30                 "groups_owner_id" INTEGER
  31                 )';
  32
  33         $reposql = '
  34                 CREATE TABLE "repos" (
  35             "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  36             "repos_name" TEXT,
  37             "repos_description" TEXT,
  38             "repos_owner" INTEGER
  39                 )';
  40
  41         $configsql = '
  42                 CREATE TABLE "config" (
  43             "config_name" TEXT,
  44             "config_value" TEXT
  45                 )';
  46
  47         $groupmemsql = '
  48                 CREATE TABLE "group_membership" (
  49             "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  50             "groupmember_groupid" INTEGER,
  51             "groupmember_userid" INTEGER
  52                 )';
  53
  54         try {
  55                 $DB_CONNECTION = new PDO("sqlite:$dbloc");
  56         } catch(PDOException $exep) {
  57                 error_log("execpt on db open");
  58                 return false;
  59         }
  60
  61
  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);
  67 }
  68
  69 function gwvp_isDBSetup()
  70 {
  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;
  73
  74         if($db_type == "sqlite") {
  75                 if(file_exists($db_url)) return true;
  76                 else return false;
  77         }
  78
  79         // TODO now for the connectables
  80         // gwvp_ConnectDB();
  81 }
  82
  83 function gwvp_ConnectDB()
  84 {
  85         global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION;
  86
  87         // first check if $DB_CONNECTION IS live
  88         if($DB_CONNECTION != false) return $DB_CONNECTION;
  89
  90         if($db_type == "sqlite") {
  91                 $db_url = $db_name;
  92                 if(!file_exists($db_name)) {
  93                         error_log("$db_name does not exist - problem");
  94                 }
  95         }
  96
  97         // and here we go with pdo.
  98         error_log("attmpting to open db, $db_type:$db_url");
  99         try {
 100                 $DB_CONNECTION = new PDO("$db_type:$db_url");
 101         } catch(PDOException $exep) {
 102                 error_log("execpt on db open");
 103                 return false;
 104         }
 105
 106         return $DB_CONNECTION;
 107 }
 108
 109 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
 110 {
 111         $conn = gwvp_ConnectDB();
 112
 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')");
 117         /*
 118          *          "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 119             "user_full_name" TEXT,
 120             "user_password" TEXT,
 121             "user_username" TEXT,
 122             "user_email" TEXT,
 123             "user_desc" TEXT,
 124             "user_status" INTEGER
 125
 126          */
 127 }
 128
 129 function gwvp_getUsers()
 130 {
 131         $conn = gwvp_ConnectDB();
 132
 133         $res = $conn->query("select * from users");
 134
 135         $returns = false;
 136         $rn = 0;
 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"];
 145                 $rn++;
 146         }
 147
 148         return $returns;
 149 }
 150
 151 function gwvp_deleteUser($email)
 152 {
 153         $conn = gwvp_ConnectDB();
 154
 155         $sql = "delete from users where user_email='$email'";
 156
 157         $conn->query($sql);
 158 }
 159
 160 function gwvp_createGroup($group_name, $is_admin, $owner_id)
 161 {
 162         $conn = gwvp_ConnectDB();
 163
 164         /*
 165          *              CREATE TABLE groups (
 166             "groups_id" INTEGER,
 167             "groups_name" TEXT,
 168             "groups_is_admin" INTEGER,
 169                 "groups_owner_id" INTEGER
 170                 )';
 171
 172          */
 173         if($is_admin) {
 174                 $is_admin_t = 1;
 175         } else {
 176                 $is_admin_t = 0;
 177         }
 178         $sql = "insert into groups values( null, '$group_name', '$is_admin_t', '$owner_id')";
 179
 180         $conn->query($sql);
 181
 182 }
 183
 184 function gwvp_deleteGroup($groupname)
 185 {
 186         $conn = gwvp_ConnectDB();
 187
 188         $sql = "delete from groups where groups_name='$groupname'";
 189
 190         $conn->query($sql);
 191 }
 192
 193 function gwvp_getGroupsForUser($email)
 194 {
 195         $conn = gwvp_ConnectDB();
 196
 197         /*
 198                                 select g.groups_name from
 199                                 group_membership gm, groups g, users u
 200                         where
 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'
 205         */
 206
 207         $sql = "
 208                         select g.groups_name from
 209                                 group_membership gm, groups g, users u
 210                         where
 211                                 gm.groupmember_userid=u.users_id and
 212                                 u.user_email='$email' and
 213                                 gm.groupmember_groupid=g.groups_id
 214         ";
 215
 216         $res = $conn->query($sql);
 217
 218         $return = false;
 219         $rn = 0;
 220         foreach($res as $u_res) {
 221                 $return[$rn] = $u_res[0];
 222                 $rn++;
 223         }
 224
 225         return $return;
 226 }
 227
 228 function gwvp_getGroupsOwnedByUser($email)
 229 {
 230         $conn = gwvp_ConnectDB();
 231
 232         /*
 233                                 select g.groups_name from
 234                                 group_membership gm, groups g, users u
 235                         where
 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'
 240         */
 241
 242         $sql = "
 243                         select g.groups_name from
 244                                 groups g, users u
 245                         where
 246                                 u.user_email='$email' and
 247                                 u.users_id=g.groups_owner_id
 248         ";
 249
 250         $res = $conn->query($sql);
 251
 252         $return = false;
 253         $rn = 0;
 254         foreach($res as $u_res) {
 255                 $return[$rn] = $u_res[0];
 256                 $rn++;
 257         }
 258
 259         return $return;
 260
 261 }
 262
 263 function gwvp_groupOwner($groupname)
 264 {
 265         $conn = gwvp_ConnectDB();
 266
 267         $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
 268
 269         $res = $conn->query($sql);
 270         $return = false;
 271         foreach($res as $u_res) {
 272                 $return = $u_res[0];
 273         }
 274
 275         return $return;
 276 }
 277
 278 function gwvp_getGroups()
 279 {
 280         $conn = gwvp_ConnectDB();
 281
 282         $res = $conn->query("select * from groups");
 283
 284         $returns = false;
 285         $rn = 0;
 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"];
 293                 $rn++;
 294         }
 295
 296         return $returns;
 297 }
 298
 299 function gwvp_getGroupId($groupname)
 300 {
 301         $conn = gwvp_ConnectDB();
 302
 303         $sql = "select groups_id from groups where groups_name='$groupname'";
 304
 305         $res = $conn->query($sql);
 306         $return = false;
 307         foreach($res as $u_res) {
 308                 $return = $u_res["groups_id"];
 309         }
 310
 311         return $return;
 312 }
 313
 314 function gwvp_getUserId($useremail)
 315 {
 316         $conn = gwvp_ConnectDB();
 317
 318         $sql = "select users_id from users where user_email='$useremail'";
 319
 320         $res = $conn->query($sql);
 321         $return = false;
 322         foreach($res as $u_res) {
 323                 $return = $u_res["users_id"];
 324         }
 325
 326         return $return;
 327 }
 328
 329 function gwvp_getUserEmail($id)
 330 {
 331         $conn = gwvp_ConnectDB();
 332
 333         $sql = "select user_email from users where users_id='$id'";
 334
 335         $res = $conn->query($sql);
 336         $return = false;
 337         foreach($res as $u_res) {
 338                 $return = $u_res["user_email"];
 339         }
 340
 341         return $return;
 342 }
 343
 344 function gwvp_addGroupMember($email, $groupname)
 345 {
 346         $conn = gwvp_ConnectDB();
 347
 348         $uid = gwvp_getUserId($email);
 349         $gid = gwvp_getGroupId($groupname);
 350
 351         /*
 352          *              CREATE TABLE "group_membership" (
 353             "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 354             "groupmember_groupid" INTEGER,
 355             "groupmember_userid" INTEGER
 356
 357          */
 358         if($uid!=false&&$gid!=false) $sql = "insert into group_membership values (null, '$gid', '$uid')";
 359         else return false;
 360
 361         $conn->query($sql);
 362
 363         return true;
 364 }
 365
 366 function gwvp_IsGroupMember($email, $groupname)
 367 {
 368         $conn = gwvp_ConnectDB();
 369
 370         // i think this is right
 371         $sql = "
 372                         select count(*) from
 373                                 group_membership gm, groups g, users u
 374                         where
 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'
 379                         ";
 380
 381         $res = $conn->query($sql);
 382         $result = 0;
 383         foreach($res as $u_res) {
 384                 $result = $u_res[0];
 385         }
 386
 387         if($result == 0) return false;
 388         if($result == 1) return true;
 389 }
 390
 391 function gwvp_IsUserAdmin($email)
 392 {
 393         $conn = gwvp_ConnectDB();
 394
 395         $id = gwvp_getUserId($email);
 396         $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
 397
 398         $res = $conn->query($sql);
 399         $rn = 0;
 400         foreach($res as $u_res) {
 401                 $gid[$rn] = $u_res["groupmember_groupid"];
 402                 $rn++;
 403         }
 404
 405         foreach($gid as $gid_t) {
 406         /*
 407          *              CREATE TABLE groups (
 408             "groups_id" INTEGER,
 409             "groups_name" TEXT,
 410             "groups_is_admin" INTEGER,
 411                 "groups_owner_id" INTEGER
 412                 )';
 413
 414          */
 415
 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;
 420                 }
 421         }
 422
 423         return false;
 424 }
 425
 426 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
 427 {
 428         /*
 429          *          "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 430             "user_full_name" TEXT,
 431             "user_password" TEXT,
 432             "user_username" TEXT,
 433             "user_email" TEXT,
 434             "user_desc" TEXT,
 435             "user_status" INTEGER
 436
 437          */
 438
 439         $conn = gwvp_ConnectDB();
 440
 441         if($email != null) {
 442                 $sql = "update users set user_email='$email' where users_id='$userid'";
 443                 $conn->query($sql);
 444         }
 445
 446         if($fullname != null) {
 447                 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
 448                 $conn->query($sql);
 449         }
 450
 451         if($password != null) {
 452                 $shapass = sha1($password);
 453                 $sql = "update users set user_password='$shapass' where users_id='$userid'";
 454                 $conn->query($sql);
 455         }
 456
 457         if($username != null) {
 458                 $sql = "update users set user_username='$username' where users_id='$userid'";
 459                 $conn->query($sql);
 460         }
 461
 462         if($desc != null) {
 463                 $sql = "update users set user_desc='$desc' where users_id='$userid'";
 464                 $conn->query($sql);
 465         }
 466
 467         if($status != null) {
 468                 $sql = "update users set user_status='$status' where users_id='$userid'";
 469                 $conn->query($sql);
 470         }
 471
 472         return true;
 473 }
 474
 475
 476 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
 477 {
 478         /*
 479          *              CREATE TABLE groups (
 480             "groups_id" INTEGER,
 481             "groups_name" TEXT,
 482             "groups_is_admin" INTEGER,
 483                 "groups_owner_id" INTEGER
 484                 )';
 485
 486          */
 487         $conn = gwvp_ConnectDB();
 488
 489         if($groupname != null) {
 490                 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
 491                 $conn->query($sql);
 492         }
 493
 494         if($group_is_admin != null) {
 495                 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
 496                 $conn->query($sql);
 497         }
 498
 499         if($groups_owner_id != null) {
 500                 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
 501                 $conn->query($sql);
 502         }
 503
 504         return true;
 505 }
 506 /* functions we'll need to access data:
 507  *
 508  * getUsers(pattern)
 509  * getUserData(username)
 510  * getGroups(pattern)
 511  * getGroupData(groupname)
 512  * modifyGroup(...)
 513  * addGroupMember(...)
 514  * deleteGroupMember(...)
 515  *
 516  * createUser(...)
 517  * deleteUser(...)
 518  * modifyUser(...)
 519  * createRepo(...)
 520  * deleteRepo(...)
 521  * getRepos()
 522  */
 523
 524
 525
 526 ?>