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_getUser($username=null, $email=null, $id=null)
 130 {
 131         $conn = gwvp_ConnectDB();
 132
 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'");
 139         } else return false;
 140
 141         $returns = false;
 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"];
 150         }
 151
 152         return $returns;
 153
 154 }
 155
 156 function gwvp_getUsers()
 157 {
 158         $conn = gwvp_ConnectDB();
 159
 160         $res = $conn->query("select * from users");
 161
 162         $returns = false;
 163         $rn = 0;
 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"];
 172                 $rn++;
 173         }
 174
 175         return $returns;
 176 }
 177
 178 function gwvp_deleteUser($email)
 179 {
 180         $conn = gwvp_ConnectDB();
 181
 182         $sql = "delete from users where user_email='$email'";
 183
 184         $conn->query($sql);
 185 }
 186
 187 function gwvp_createGroup($group_name, $is_admin, $owner_id)
 188 {
 189         $conn = gwvp_ConnectDB();
 190
 191         /*
 192          *              CREATE TABLE groups (
 193             "groups_id" INTEGER,
 194             "groups_name" TEXT,
 195             "groups_is_admin" INTEGER,
 196                 "groups_owner_id" INTEGER
 197                 )';
 198
 199          */
 200         if($is_admin) {
 201                 $is_admin_t = 1;
 202         } else {
 203                 $is_admin_t = 0;
 204         }
 205         $sql = "insert into groups values( null, '$group_name', '$is_admin_t', '$owner_id')";
 206
 207         $conn->query($sql);
 208
 209 }
 210
 211 function gwvp_deleteGroup($groupname)
 212 {
 213         $conn = gwvp_ConnectDB();
 214
 215         $sql = "delete from groups where groups_name='$groupname'";
 216
 217         $conn->query($sql);
 218 }
 219
 220 function gwvp_getGroupsForUser($email)
 221 {
 222         $conn = gwvp_ConnectDB();
 223
 224         /*
 225                                 select g.groups_name from
 226                                 group_membership gm, groups g, users u
 227                         where
 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'
 232         */
 233
 234         $sql = "
 235                         select g.groups_name from
 236                                 group_membership gm, groups g, users u
 237                         where
 238                                 gm.groupmember_userid=u.users_id and
 239                                 u.user_email='$email' and
 240                                 gm.groupmember_groupid=g.groups_id
 241         ";
 242
 243         $res = $conn->query($sql);
 244
 245         $return = false;
 246         $rn = 0;
 247         foreach($res as $u_res) {
 248                 $return[$rn] = $u_res[0];
 249                 $rn++;
 250         }
 251
 252         return $return;
 253 }
 254
 255 function gwvp_getGroupsOwnedByUser($email)
 256 {
 257         $conn = gwvp_ConnectDB();
 258
 259         /*
 260                                 select g.groups_name from
 261                                 group_membership gm, groups g, users u
 262                         where
 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'
 267         */
 268
 269         $sql = "
 270                         select g.groups_name from
 271                                 groups g, users u
 272                         where
 273                                 u.user_email='$email' and
 274                                 u.users_id=g.groups_owner_id
 275         ";
 276
 277         $res = $conn->query($sql);
 278
 279         $return = false;
 280         $rn = 0;
 281         foreach($res as $u_res) {
 282                 $return[$rn] = $u_res[0];
 283                 $rn++;
 284         }
 285
 286         return $return;
 287
 288 }
 289
 290 function gwvp_groupOwner($groupname)
 291 {
 292         $conn = gwvp_ConnectDB();
 293
 294         $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
 295
 296         $res = $conn->query($sql);
 297         $return = false;
 298         foreach($res as $u_res) {
 299                 $return = $u_res[0];
 300         }
 301
 302         return $return;
 303 }
 304
 305 function gwvp_getGroups()
 306 {
 307         $conn = gwvp_ConnectDB();
 308
 309         $res = $conn->query("select * from groups");
 310
 311         $returns = false;
 312         $rn = 0;
 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"];
 320                 $rn++;
 321         }
 322
 323         return $returns;
 324 }
 325
 326 function gwvp_getGroupId($groupname)
 327 {
 328         $conn = gwvp_ConnectDB();
 329
 330         $sql = "select groups_id from groups where groups_name='$groupname'";
 331
 332         $res = $conn->query($sql);
 333         $return = false;
 334         foreach($res as $u_res) {
 335                 $return = $u_res["groups_id"];
 336         }
 337
 338         return $return;
 339 }
 340
 341 function gwvp_getUserId($useremail=null, $username = null)
 342 {
 343         $conn = gwvp_ConnectDB();
 344
 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'";
 349         } else return false;
 350
 351         $res = $conn->query($sql);
 352         $return = false;
 353         foreach($res as $u_res) {
 354                 $return = $u_res["users_id"];
 355         }
 356
 357         return $return;
 358 }
 359
 360 function gwvp_getUserEmail($id)
 361 {
 362         $conn = gwvp_ConnectDB();
 363
 364         $sql = "select user_email from users where users_id='$id'";
 365
 366         $res = $conn->query($sql);
 367         $return = false;
 368         foreach($res as $u_res) {
 369                 $return = $u_res["user_email"];
 370         }
 371
 372         return $return;
 373 }
 374
 375 function gwvp_addGroupMember($email, $groupname)
 376 {
 377         $conn = gwvp_ConnectDB();
 378
 379         $uid = gwvp_getUserId($email);
 380         $gid = gwvp_getGroupId($groupname);
 381
 382         /*
 383          *              CREATE TABLE "group_membership" (
 384             "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 385             "groupmember_groupid" INTEGER,
 386             "groupmember_userid" INTEGER
 387
 388          */
 389         if($uid!=false&&$gid!=false) $sql = "insert into group_membership values (null, '$gid', '$uid')";
 390         else return false;
 391
 392         $conn->query($sql);
 393
 394         return true;
 395 }
 396
 397 function gwvp_IsGroupMember($email, $groupname)
 398 {
 399         $conn = gwvp_ConnectDB();
 400
 401         // i think this is right
 402         $sql = "
 403                         select count(*) from
 404                                 group_membership gm, groups g, users u
 405                         where
 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'
 410                         ";
 411
 412         $res = $conn->query($sql);
 413         $result = 0;
 414         foreach($res as $u_res) {
 415                 $result = $u_res[0];
 416         }
 417
 418         if($result == 0) return false;
 419         if($result == 1) return true;
 420 }
 421
 422 function gwvp_IsUserAdmin($email=null, $username = null)
 423 {
 424         $conn = gwvp_ConnectDB();
 425
 426
 427         // TODO: clean this up, this should be a single query - idiot
 428         if($email != null) {
 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'";
 434         } else return false;
 435
 436         $res = $conn->query($sql);
 437         $rn = 0;
 438         foreach($res as $u_res) {
 439                 $gid[$rn] = $u_res["groupmember_groupid"];
 440                 $rn++;
 441         }
 442
 443         foreach($gid as $gid_t) {
 444         /*
 445          *              CREATE TABLE groups (
 446             "groups_id" INTEGER,
 447             "groups_name" TEXT,
 448             "groups_is_admin" INTEGER,
 449                 "groups_owner_id" INTEGER
 450                 )';
 451
 452          */
 453
 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;
 458                 }
 459         }
 460
 461         return false;
 462 }
 463
 464 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
 465 {
 466         /*
 467          *          "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 468             "user_full_name" TEXT,
 469             "user_password" TEXT,
 470             "user_username" TEXT,
 471             "user_email" TEXT,
 472             "user_desc" TEXT,
 473             "user_status" INTEGER
 474
 475          */
 476
 477         $conn = gwvp_ConnectDB();
 478
 479         if($email != null) {
 480                 $sql = "update users set user_email='$email' where users_id='$userid'";
 481                 $conn->query($sql);
 482         }
 483
 484         if($fullname != null) {
 485                 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
 486                 $conn->query($sql);
 487         }
 488
 489         if($password != null) {
 490                 $shapass = sha1($password);
 491                 $sql = "update users set user_password='$shapass' where users_id='$userid'";
 492                 $conn->query($sql);
 493         }
 494
 495         if($username != null) {
 496                 $sql = "update users set user_username='$username' where users_id='$userid'";
 497                 $conn->query($sql);
 498         }
 499
 500         if($desc != null) {
 501                 $sql = "update users set user_desc='$desc' where users_id='$userid'";
 502                 $conn->query($sql);
 503         }
 504
 505         if($status != null) {
 506                 $sql = "update users set user_status='$status' where users_id='$userid'";
 507                 $conn->query($sql);
 508         }
 509
 510         return true;
 511 }
 512
 513
 514 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
 515 {
 516         /*
 517          *              CREATE TABLE groups (
 518             "groups_id" INTEGER,
 519             "groups_name" TEXT,
 520             "groups_is_admin" INTEGER,
 521                 "groups_owner_id" INTEGER
 522                 )';
 523
 524          */
 525         $conn = gwvp_ConnectDB();
 526
 527         if($groupname != null) {
 528                 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
 529                 $conn->query($sql);
 530         }
 531
 532         if($group_is_admin != null) {
 533                 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
 534                 $conn->query($sql);
 535         }
 536
 537         if($groups_owner_id != null) {
 538                 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
 539                 $conn->query($sql);
 540         }
 541
 542         return true;
 543 }
 544 /* functions we'll need to access data:
 545  *
 546  * getUsers(pattern)
 547  * getUserData(username)
 548  * getGroups(pattern)
 549  * getGroupData(groupname)
 550  * modifyGroup(...)
 551  * addGroupMember(...)
 552  * deleteGroupMember(...)
 553  *
 554  * createUser(...)
 555  * deleteUser(...)
 556  * modifyUser(...)
 557  * createRepo(...)
 558  * deleteRepo(...)
 559  * getRepos()
 560  */
 561
 562
 563
 564 ?>