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