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