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_description" TEXT,
  30             "groups_is_admin" INTEGER,
  31                 "groups_owner_id" INTEGER
  32                 )';
  33
  34         $reposql = '
  35                 CREATE TABLE "repos" (
  36             "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  37             "repos_name" TEXT,
  38             "repos_description" TEXT,
  39             "repos_owner" INTEGER
  40                 )';
  41
  42         // this looks like null, <repoid>, <read|visible|write>, user:<uid>|group:<gid>|authed|anon
  43         // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc)
  44         // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo
  45         // TODO: is this sufficient? i have to think about it
  46         $repoperms = '
  47                 CREATE TABLE "repoperms" (
  48                 "repoperms_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  49                 "repo_id" INTEGER,
  50                 "repoperms_type" TEXT,
  51                 "repoperms_ref" TEXT
  52         )';
  53
  54         $configsql = '
  55                 CREATE TABLE "config" (
  56             "config_name" TEXT,
  57             "config_value" TEXT
  58                 )';
  59
  60         $groupmemsql = '
  61                 CREATE TABLE "group_membership" (
  62             "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
  63             "groupmember_groupid" INTEGER,
  64             "groupmember_userid" INTEGER
  65                 )';
  66
  67         try {
  68                 $DB_CONNECTION = new PDO("sqlite:$dbloc");
  69         } catch(PDOException $exep) {
  70                 error_log("execpt on db open");
  71                 return false;
  72         }
  73
  74
  75         $DB_CONNECTION->query($usersql);
  76         $DB_CONNECTION->query($groupsql);
  77         $DB_CONNECTION->query($reposql);
  78         $DB_CONNECTION->query($configsql);
  79         $DB_CONNECTION->query($groupmemsql);
  80 }
  81
  82 function gwvp_isDBSetup()
  83 {
  84         // for sqlite, we just check if the db exists, for everyone else, we check for a conneciton and go yay or nay
  85         global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_url;
  86
  87         if($db_type == "sqlite") {
  88                 if(file_exists($db_url)) return true;
  89                 else return false;
  90         }
  91
  92         // TODO now for the connectables
  93         // gwvp_ConnectDB();
  94 }
  95
  96 function gwvp_ConnectDB()
  97 {
  98         global $WEB_ROOT_FS, $BASE_URL, $repo_base, $data_directory, $db_type, $db_name, $DB_CONNECTION;
  99
 100         // first check if $DB_CONNECTION IS live
 101         if($DB_CONNECTION != false) return $DB_CONNECTION;
 102
 103         if($db_type == "sqlite") {
 104                 $db_url = $db_name;
 105                 if(!file_exists($db_name)) {
 106                         error_log("$db_name does not exist - problem");
 107                 }
 108         }
 109
 110         // and here we go with pdo.
 111         error_log("attmpting to open db, $db_type:$db_url");
 112         try {
 113                 $DB_CONNECTION = new PDO("$db_type:$db_url");
 114         } catch(PDOException $exep) {
 115                 error_log("execpt on db open");
 116                 return false;
 117         }
 118
 119         return $DB_CONNECTION;
 120 }
 121
 122 // TODO: we have to define what "Status" is
 123 function gwvp_createUser($email, $fullname, $password, $username, $desc, $status)
 124 {
 125         $conn = gwvp_ConnectDB();
 126
 127         // TODO: change from sha1
 128         $shapass = sha1($password);
 129         //error_log("Create user called with $email");
 130         $sql = "insert into users values (null, '$fullname', '$shapass', '$username', '$email', '$desc', '$status')";
 131         error_log("Creating user, $sql");
 132         return $conn->query($sql);
 133         /*
 134          *          "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 135          "user_full_name" TEXT,
 136          "user_password" TEXT,
 137          "user_username" TEXT,
 138          "user_email" TEXT,
 139          "user_desc" TEXT,
 140          "user_status" INTEGER
 141
 142          */
 143 }
 144
 145 function gwvp_getUser($username=null, $email=null, $id=null)
 146 {
 147         $conn = gwvp_ConnectDB();
 148
 149         if($username != null) {
 150                 $res = $conn->query("select * from users where user_username='$username'");
 151         } else if($email != null) {
 152                 $res = $conn->query("select * from users where user_email='$email'");
 153         } else if($id != null) {
 154                 $res = $conn->query("select * from users where users_id='$id'");
 155         } else return false;
 156
 157         $returns = false;
 158         foreach($res as $u_res) {
 159                 $returns["id"] = $u_res["users_id"];
 160                 $returns["fullname"] = $u_res["user_full_name"];
 161                 $returns["password"] = $u_res["user_password"];
 162                 $returns["username"] = $u_res["user_username"];
 163                 $returns["email"] = $u_res["user_email"];
 164                 $returns["desc"] = $u_res["user_desc"];
 165                 $returns["status"] = $u_res["user_status"];
 166         }
 167
 168         return $returns;
 169
 170 }
 171
 172 function gwvp_getUsers()
 173 {
 174         $conn = gwvp_ConnectDB();
 175
 176         $res = $conn->query("select * from users");
 177
 178         $returns = false;
 179         $rn = 0;
 180         foreach($res as $u_res) {
 181                 $returns[$rn]["id"] = $u_res["users_id"];
 182                 $returns[$rn]["fullname"] = $u_res["user_full_name"];
 183                 $returns[$rn]["password"] = $u_res["user_password"];
 184                 $returns[$rn]["username"] = $u_res["user_username"];
 185                 $returns[$rn]["email"] = $u_res["user_email"];
 186                 $returns[$rn]["desc"] = $u_res["user_desc"];
 187                 $returns[$rn]["status"] = $u_res["user_status"];
 188                 $rn++;
 189         }
 190
 191         return $returns;
 192 }
 193
 194 function gwvp_deleteUser($email)
 195 {
 196         $conn = gwvp_ConnectDB();
 197
 198         $sql = "delete from users where user_email='$email'";
 199
 200         $conn->query($sql);
 201 }
 202
 203 function gwvp_createGroup($group_name, $group_desc, $is_admin, $owner_id)
 204 {
 205         $conn = gwvp_ConnectDB();
 206
 207         /*
 208          *              CREATE TABLE groups (
 209          "groups_id" INTEGER,
 210          "groups_name" TEXT,
 211          "groups_is_admin" INTEGER,
 212                 "groups_owner_id" INTEGER
 213                 )';
 214
 215          */
 216         if($is_admin) {
 217                 $is_admin_t = 1;
 218         } else {
 219                 $is_admin_t = 0;
 220         }
 221         $sql = "insert into groups values( null, '$group_name', '$group_desc', '$is_admin_t', '$owner_id')";
 222
 223
 224         $conn->query($sql);
 225
 226 }
 227
 228 function gwvp_deleteGroup($groupname)
 229 {
 230         $conn = gwvp_ConnectDB();
 231
 232         $sql = "delete from groups where groups_name='$groupname'";
 233
 234         $conn->query($sql);
 235 }
 236
 237 function gwvp_getGroupsForUser($email)
 238 {
 239         $conn = gwvp_ConnectDB();
 240
 241         /*
 242          select g.groups_name from
 243          group_membership gm, groups g, users u
 244          where
 245          gm.groupmember_userid=u.users_id and
 246          u.user_email='$email' and
 247          gm.groupmember_groupid=g.groups_id and
 248          g.groups_name='$groupname'
 249          */
 250
 251         $sql = "
 252                         select g.groups_name from
 253                                 group_membership gm, groups g, users u
 254                         where
 255                                 gm.groupmember_userid=u.users_id and
 256                                 u.user_email='$email' and
 257                                 gm.groupmember_groupid=g.groups_id
 258         ";
 259
 260         $res = $conn->query($sql);
 261
 262         $return = false;
 263         $rn = 0;
 264         foreach($res as $u_res) {
 265                 $return[$rn] = $u_res[0];
 266                 $rn++;
 267         }
 268
 269         return $return;
 270 }
 271
 272 function gwvp_getGroupsOwnedByUser($email)
 273 {
 274         $conn = gwvp_ConnectDB();
 275
 276         /*
 277          select g.groups_name from
 278          group_membership gm, groups g, users u
 279          where
 280          gm.groupmember_userid=u.users_id and
 281          u.user_email='$email' and
 282          gm.groupmember_groupid=g.groups_id and
 283          g.groups_name='$groupname'
 284          */
 285
 286         $sql = "
 287                         select g.groups_name from
 288                                 groups g, users u
 289                         where
 290                                 u.user_email='$email' and
 291                                 u.users_id=g.groups_owner_id
 292         ";
 293
 294         $res = $conn->query($sql);
 295
 296         $return = false;
 297         $rn = 0;
 298         foreach($res as $u_res) {
 299                 $return[$rn] = $u_res[0];
 300                 $rn++;
 301         }
 302
 303         return $return;
 304
 305 }
 306
 307 function gwvp_groupOwner($groupname)
 308 {
 309         $conn = gwvp_ConnectDB();
 310
 311         $sql = "select u.user_email from users u, groups g where g.groups_name='$groupname' and g.groups_owner_id=u.users_id";
 312
 313         $res = $conn->query($sql);
 314         $return = false;
 315         foreach($res as $u_res) {
 316                 $return = $u_res[0];
 317         }
 318
 319         return $return;
 320 }
 321
 322 function gwvp_getGroups()
 323 {
 324         $conn = gwvp_ConnectDB();
 325
 326         $res = $conn->query("select * from groups");
 327
 328         $returns = false;
 329         $rn = 0;
 330         foreach($res as $u_res) {
 331                 $returns[$rn]["id"] = $u_res["groups_id"];
 332                 $returns[$rn]["name"] = $u_res["groups_name"];
 333                 if($u_res["groups_is_admin"]=="1") $return[$rn]["admin"] = true;
 334                 else $return[$rn]["admin"] = false;
 335                 $returns[$rn]["admin"] = $u_res["groups_is_admin"];
 336                 $returns[$rn]["ownerid"] = $u_res["groups_owner_id"];
 337                 $rn++;
 338         }
 339
 340         return $returns;
 341 }
 342
 343 function gwvp_getGroupId($groupname)
 344 {
 345         $conn = gwvp_ConnectDB();
 346
 347         $sql = "select groups_id from groups where groups_name='$groupname'";
 348
 349         $res = $conn->query($sql);
 350         $return = false;
 351         foreach($res as $u_res) {
 352                 $return = $u_res["groups_id"];
 353         }
 354
 355         return $return;
 356 }
 357
 358 function gwvp_getGroup($gid)
 359 {
 360         /*
 361          *      $groupsql = '
 362                 CREATE TABLE groups (
 363             "groups_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 364             "groups_name" TEXT,
 365             "groups_is_admin" INTEGER,
 366                 "groups_owner_id" INTEGER
 367                 )';
 368
 369          */
 370         $conn = gwvp_ConnectDB();
 371
 372         $sql = "select * from groups where groups_id='$gid'";
 373
 374         $res = $conn->query($sql);
 375         $return = false;
 376         foreach($res as $u_res) {
 377                 $return["id"] = $u_res["groups_id"];
 378                 $return["name"] = $u_res["groups_name"];
 379                 if($u_res["groups_is_admin"] == 1) {
 380                         $return["isadmin"] = true;
 381                 } else {
 382                         $return["isadmin"] = false;
 383                 }
 384                 $return["ownerid"] = $u_res["groups_owner_id"];
 385                 $return["description"] = $u_res["groups_description"];
 386         }
 387
 388         return $return;
 389 }
 390
 391 function gwvp_getUserId($useremail=null, $username = null)
 392 {
 393         $conn = gwvp_ConnectDB();
 394
 395         if($useremail != null) {
 396                 $sql = "select users_id from users where user_email='$useremail'";
 397         } else if($username != null) {
 398                 $sql = "select users_id from users where user_username='$username'";
 399         } else return false;
 400
 401         $res = $conn->query($sql);
 402         $return = false;
 403         foreach($res as $u_res) {
 404                 $return = $u_res["users_id"];
 405         }
 406
 407         return $return;
 408 }
 409
 410 function gwvp_getUserName($id = null, $email=null)
 411 {
 412         $conn = gwvp_ConnectDB();
 413
 414         if($email != null) {
 415                 $sql = "select user_username from users where user_email='$email'";
 416         } else if($id != null) {
 417                 $sql = "select user_username from users where users_id='$id'";
 418         } else return false;
 419
 420         $res = $conn->query($sql);
 421         $return = false;
 422         foreach($res as $u_res) {
 423                 $return = $u_res["user_username"];
 424         }
 425
 426         return $return;
 427 }
 428
 429
 430 function gwvp_getUserEmail($id)
 431 {
 432         $conn = gwvp_ConnectDB();
 433
 434         $sql = "select user_email from users where users_id='$id'";
 435
 436         $res = $conn->query($sql);
 437         $return = false;
 438         foreach($res as $u_res) {
 439                 $return = $u_res["user_email"];
 440         }
 441
 442         return $return;
 443 }
 444
 445 function gwvp_addGroupMember($email, $groupname)
 446 {
 447         $conn = gwvp_ConnectDB();
 448
 449         $uid = gwvp_getUserId($email);
 450         $gid = gwvp_getGroupId($groupname);
 451
 452         /*
 453          *              CREATE TABLE "group_membership" (
 454          "groupmember_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 455          "groupmember_groupid" INTEGER,
 456          "groupmember_userid" INTEGER
 457
 458          */
 459         if($uid!=false&&$gid!=false) $sql = "insert into group_membership values (null, '$gid', '$uid')";
 460         else return false;
 461
 462         $conn->query($sql);
 463
 464         return true;
 465 }
 466
 467 function gwvp_IsGroupMember($email, $groupname)
 468 {
 469         $conn = gwvp_ConnectDB();
 470
 471         // i think this is right
 472         $sql = "
 473                         select count(*) from
 474                                 group_membership gm, groups g, users u
 475                         where
 476                                 gm.groupmember_userid=u.users_id and
 477                                 u.user_email='$email' and
 478                                 gm.groupmember_groupid=g.groups_id and
 479                                 g.groups_name='$groupname'
 480                         ";
 481
 482         $res = $conn->query($sql);
 483         $result = 0;
 484         foreach($res as $u_res) {
 485                 $result = $u_res[0];
 486         }
 487
 488         if($result == 0) return false;
 489         if($result == 1) return true;
 490 }
 491
 492 function gwvp_IsUserAdmin($email=null, $username = null)
 493 {
 494         $conn = gwvp_ConnectDB();
 495
 496
 497         // TODO: clean this up, this should be a single query - idiot
 498         if($email != null) {
 499                 $id = gwvp_getUserId($email);
 500                 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
 501         } else if($username != null) {
 502                 $id = gwvp_getUserId(null, $username);
 503                 $sql = "select groupmember_groupid from group_membership where groupmember_userid='$id'";
 504         } else return false;
 505
 506         $res = $conn->query($sql);
 507         $rn = 0;
 508         $gid = false;
 509         foreach($res as $u_res) {
 510                 $gid[$rn] = $u_res["groupmember_groupid"];
 511                 $rn++;
 512         }
 513
 514         foreach($gid as $gid_t) {
 515                 /*
 516                  *              CREATE TABLE groups (
 517                  "groups_id" INTEGER,
 518                  "groups_name" TEXT,
 519                  "groups_is_admin" INTEGER,
 520                  "groups_owner_id" INTEGER
 521                  )';
 522
 523                  */
 524
 525                 $sql = "select groups_is_admin from groups where groups_id='$gid_t'";
 526                 $res = $conn->query($sql);
 527                 foreach($res as $u_res) {
 528                         if($u_res["groups_is_admin"] == "1") return true;
 529                 }
 530         }
 531
 532         return false;
 533 }
 534
 535 function gwvp_ModifyUser($userid, $email=null, $fullname=null, $password=null, $username=null, $desc=null, $status=null)
 536 {
 537         /*
 538          *          "users_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 539          "user_full_name" TEXT,
 540          "user_password" TEXT,
 541          "user_username" TEXT,
 542          "user_email" TEXT,
 543          "user_desc" TEXT,
 544          "user_status" INTEGER
 545
 546          */
 547
 548         $conn = gwvp_ConnectDB();
 549
 550         if($email != null) {
 551                 $sql = "update users set user_email='$email' where users_id='$userid'";
 552                 $conn->query($sql);
 553         }
 554
 555         if($fullname != null) {
 556                 $sql = "update users set user_full_name='$fullname' where users_id='$userid'";
 557                 $conn->query($sql);
 558         }
 559
 560         if($password != null) {
 561                 $shapass = sha1($password);
 562                 $sql = "update users set user_password='$shapass' where users_id='$userid'";
 563                 $conn->query($sql);
 564         }
 565
 566         if($username != null) {
 567                 $sql = "update users set user_username='$username' where users_id='$userid'";
 568                 $conn->query($sql);
 569         }
 570
 571         if($desc != null) {
 572                 $sql = "update users set user_desc='$desc' where users_id='$userid'";
 573                 $conn->query($sql);
 574         }
 575
 576         if($status != null) {
 577                 $sql = "update users set user_status='$status' where users_id='$userid'";
 578                 $conn->query($sql);
 579         }
 580
 581         return true;
 582 }
 583
 584
 585 function gwvp_ModifyGroup($groupid, $groupname = null, $group_is_admin = null, $groups_owner_id = null)
 586 {
 587         /*
 588          *              CREATE TABLE groups (
 589          "groups_id" INTEGER,
 590          "groups_name" TEXT,
 591          "groups_is_admin" INTEGER,
 592                 "groups_owner_id" INTEGER
 593                 )';
 594
 595          */
 596         $conn = gwvp_ConnectDB();
 597
 598         if($groupname != null) {
 599                 $sql = "update groups set groups_name='$groupname' where groups_id='$groupid'";
 600                 $conn->query($sql);
 601         }
 602
 603         if($group_is_admin != null) {
 604                 $sql = "update groups set groups_is_admin='$group_is_admin' where groups_id='$groupid'";
 605                 $conn->query($sql);
 606         }
 607
 608         if($groups_owner_id != null) {
 609                 $sql = "update groups set groups_owner_id='$groups_owner_id' where groups_id='$groupid'";
 610                 $conn->query($sql);
 611         }
 612
 613         return true;
 614 }
 615
 616 function gwvp_AddRepo($reponame, $repodesc, $repoowner)
 617 {
 618         $conn = gwvp_ConnectDB();
 619
 620         $sql = "insert into repos values (null, '$reponame', '$repodesc', '$repoowner')";
 621
 622         $conn->query($sql);
 623 }
 624
 625 function gwvp_GetRepoList()
 626 {
 627         $conn = gwvp_ConnectDB();
 628
 629         /*
 630          *      $reposql = '
 631                 CREATE TABLE "repos" (
 632                 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
 633                 "repos_name" TEXT,
 634                 "repos_description" TEXT,
 635                 "repos_owner" INTEGER
 636                 )';
 637
 638          */
 639
 640         $sql = "select * from repos";
 641
 642         $res = $conn->query($sql);
 643
 644         $return = false;
 645         $rn = 0;
 646         foreach($res as $u_res) {
 647                 $return[$rn]["id"] = $u_res["repos_id"];
 648                 $return[$rn]["name"] = $u_res["repos_name"];
 649                 $return[$rn]["description"] = $u_res["repos_description"];
 650                 $return[$rn]["owner"] = $u_res["repos_owner"];
 651                 $rn++;
 652         }
 653
 654         return $return;
 655 }
 656 /* functions we'll need to access data:
 657  *
 658  * getUsers(pattern)
 659  * getUserData(username)
 660  * getGroups(pattern)
 661  * getGroupData(groupname)
 662  * modifyGroup(...)
 663  * addGroupMember(...)
 664  * deleteGroupMember(...)
 665  *
 666  * createUser(...)
 667  * deleteUser(...)
 668  * modifyUser(...)
 669  * createRepo(...)
 670  * deleteRepo(...)
 671  * getRepos()
 672  */
 673
 674
 675
 676 ?>