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