4 global $DB_CONNECTION;
\r
5 $DB_CONNECTION = false;
\r
8 global $db_url, $db_type;
\r
9 error_log("in include for database, $db_type, $db_name");
12 function gwvpmini_DBExists()
14 global $WEB_ROOT_FS, $BASE_URL, $data_directory, $db_type, $db_name;
16 // oh this isnt working. poo.
17 error_log("checking for $db_name, $db_type");
19 if($db_type == "sqlite") {
20 if(file_exists($db_name)) {
25 error_log("no exists");
31 function gwvpmini_getUser($username=null, $email=null, $id=null)
\r
33 $conn = gwvpmini_ConnectDB();
\r
35 if($username != null) {
\r
36 $res = $conn->query("select * from users where user_username='$username'");
\r
37 } else if($email != null) {
\r
38 $res = $conn->query("select * from users where user_email='$email'");
\r
39 } else if($id != null) {
\r
40 $res = $conn->query("select * from users where user_id='$id'");
\r
41 } else return false;
\r
44 foreach($res as $u_res) {
\r
45 $returns["id"] = $u_res["user_id"];
\r
46 $returns["fullname"] = $u_res["user_full_name"];
\r
47 $returns["password"] = $u_res["user_password"];
\r
48 $returns["username"] = $u_res["user_username"];
\r
49 $returns["email"] = $u_res["user_email"];
\r
50 $returns["desc"] = $u_res["user_desc"];
\r
51 $returns["status"] = $u_res["user_status"];
\r
59 function gwvpmini_getRepo($ownerid=null, $name=null, $id=null)
\r
61 $conn = gwvpmini_ConnectDB();
65 CREATE TABLE "repos" (
66 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
68 "repos_description" TEXT,
69 "repos_owner" INTEGER,
70 "repos_readperms" TEXT,
76 if($ownerid != null) {
\r
77 $res = $conn->query("select * from repos where repos_owner='$ownerid'");
\r
78 } else if($name != null) {
\r
79 $res = $conn->query("select * from repos where repos_name='$name'");
\r
80 } else if($id != null) {
\r
81 $res = $conn->query("select * from repos where repos_id='$id'");
\r
82 } else return false;
\r
85 if(!$res) return false;
\r
86 foreach($res as $u_res) {
\r
87 $returns["id"] = $u_res["repos_id"];
\r
88 $returns["name"] = $u_res["repos_name"];
\r
89 $returns["desc"] = $u_res["repos_description"];
\r
90 $returns["ownerid"] = $u_res["repos_owner"];
\r
91 $returns["perms"] = $u_res["repos_readperms"];
\r
98 function gwvpmini_RemoveRepoDB($id)
100 $conn = gwvpmini_ConnectDB();
\r
102 if($id < 0) return;
\r
104 $sql = "delete from repos where repos_id='$id'";
\r
106 return $conn->query($sql);
\r
110 function gwvpmini_RemoveUser($uid)
112 $conn = gwvpmini_ConnectDB();
\r
116 $sql = "delete from users where user_id='$uid'";
118 return $conn->query($sql);
121 function gwvpmini_DisableUser($uid)
123 $conn = gwvpmini_ConnectDB();
\r
125 if($uid < 0) return;
\r
127 $sql = "update users set user_status=1 where user_id='$uid'";
\r
129 return $conn->query($sql);
132 function gwvpmini_EnableUser($uid)
\r
134 $conn = gwvpmini_ConnectDB();
\r
136 if($uid < 0) return;
\r
138 $sql = "update users set user_status=0 where user_id='$uid'";
\r
140 return $conn->query($sql);
\r
144 function gwvpmini_ConnectDB()
\r
146 global $WEB_ROOT_FS, $BASE_URL, $data_directory, $db_type, $db_name, $DB_CONNECTION;
\r
148 // first check if $DB_CONNECTION IS live
\r
149 error_log("in connection $db_type, $db_name");
\r
151 if($DB_CONNECTION != false) return $DB_CONNECTION;
\r
153 if($db_type == "sqlite") {
\r
154 $db_url = $db_name;
\r
155 if(!file_exists($db_name)) {
\r
156 error_log("$db_name does not exist - problem");
157 // TODO: NEED A SETUP AGENT!
158 gwvpmini_dbCreateSQLiteStructure($db_name);
159 gwvpmini_setConfigVal("repodir", "$data_directory/repos");
\r
163 // and here we go with pdo.
\r
164 error_log("attmpting to open db, $db_type:$db_url");
\r
166 $DB_CONNECTION = new PDO("$db_type:$db_url");
\r
167 } catch(PDOException $exep) {
\r
168 error_log("execpt on db open");
\r
172 return $DB_CONNECTION;
\r
175 function gwvpmini_UpdateStatusFromConfirm($confirmhash)
177 $conn = gwvpmini_ConnectDB();
\r
179 $sql = "select count(*) from users where user_status='2:$confirmhash'";
181 $res = $conn->query($sql);
183 if(!$res) return false;
185 foreach($res as $row) {
190 $sql = "update users set user_status='0' where user_status='2:$confirmhash'";
191 return $conn->query($sql);
196 function gwvpmini_AddUser($username, $password, $fullname, $email, $desc, $level, $status)
201 $conn = gwvpmini_ConnectDB();
\r
203 $sql = "insert into 'users' values ( null, '$fullname', '".sha1($password)."', '$username', '$email', '$desc', '$level', '$status')";
\r
205 $res = $conn->query($sql);
208 $sql = "select user_id from users where user_username='$username'";
\r
209 $res = $conn->query($sql);
\r
210 if(!$res) return -1;
\r
213 foreach($res as $row) {
221 function gwvpmini_dbCreateSQLiteStructure($dbloc)
\r
224 CREATE TABLE "users" (
\r
225 "user_id" INTEGER PRIMARY KEY AUTOINCREMENT,
\r
226 "user_full_name" TEXT,
\r
227 "user_password" TEXT,
\r
228 "user_username" TEXT,
\r
233 UNIQUE(user_username)
\r
236 $initialuser_admin = '
237 insert into "users" values ( null, "Administrator", "'.sha1("password").'", "admin", "admin@localhost", "the admin", "1", "0");
\r
240 $initialuser_user = '
\r
241 insert into "users" values ( null, "User", "'.sha1("password").'", "user", "user@localhost", "the user", "0", "0");
\r
245 CREATE TABLE "repos" (
\r
246 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
\r
248 "repos_description" TEXT,
\r
249 "repos_owner" INTEGER,
250 "repos_readperms" TEXT,
254 // this looks like null, <repoid>, <read|visible|write>, user:<uid>|group:<gid>|authed|anon
\r
255 // where authed = any authenticated user, anon = everyone (logged in, not logged in, etc)
\r
256 // read|visible|write = can clone from repo|can see repo exists and see description but not clone from it|can push to repo
\r
257 // TODO: is this sufficient? i have to think about it
\r
260 CREATE TABLE "config" (
\r
261 "config_name" TEXT,
\r
262 "config_value" TEXT
\r
266 $DB_CONNECTION = new PDO("sqlite:$dbloc");
\r
267 } catch(PDOException $exep) {
\r
268 error_log("execpt on db open");
\r
272 $DB_CONNECTION->query($usersql);
\r
273 $DB_CONNECTION->query($initialuser_admin);
274 $DB_CONNECTION->query($initialuser_user);
275 $DB_CONNECTION->query($reposql);
\r
276 $DB_CONNECTION->query($configsql);
\r
279 function gwvpmini_getConfigVal($confname)
\r
283 CREATE TABLE "config" (
\r
284 "config_name" TEXT,
\r
285 "config_value" TEXT
\r
290 $conn = gwvpmini_ConnectDB();
\r
292 $sql = "select config_value from config where config_name='$confname'";
\r
294 $res = $conn->query($sql);
\r
297 foreach($res as $val) {
\r
298 $return = $val["config_value"];
\r
304 function gwvpmini_eraseConfigVal($confname)
\r
308 CREATE TABLE "config" (
\r
309 "config_name" TEXT,
\r
310 "config_value" TEXT
\r
315 $conn = gwvpmini_ConnectDB();
\r
317 $sql = "delete from config where config_name='$confname'";
\r
319 return $conn->query($sql);
\r
322 function gwvpmini_GetRepoId($reponame)
327 CREATE TABLE "repos" (
328 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
330 "repos_description" TEXT,
331 "repos_owner" INTEGER
336 $conn = gwvpmini_ConnectDB();
338 $sql = "select repos_id from repos where repos_name='$reponame'";
340 $res = $conn->query($sql);
344 foreach($res as $row) {
345 $reval = (int)$row[0];
351 function gwvpmini_GetRepoDescFromName($reponame)
\r
356 CREATE TABLE "repos" (
\r
357 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
\r
359 "repos_description" TEXT,
\r
360 "repos_owner" INTEGER
\r
365 $conn = gwvpmini_ConnectDB();
\r
367 $sql = "select repos_description from repos where repos_name='$reponame'";
368 error_log("desc for name sql: $sql");
\r
370 $res = $conn->query($sql);
\r
373 if(!$res) return -1;
\r
374 foreach($res as $row) {
\r
381 function gwvpmini_GetRepoOwnerDetailsFromName($reponame)
\r
386 CREATE TABLE "repos" (
\r
387 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
\r
389 "repos_description" TEXT,
\r
390 "repos_owner" INTEGER
\r
393 "user_id" INTEGER PRIMARY KEY AUTOINCREMENT,
394 "user_full_name" TEXT,
395 "user_password" TEXT,
396 "user_username" TEXT,
401 UNIQUE(user_username)
\r
405 $conn = gwvpmini_ConnectDB();
\r
407 $sql = "select users.* from repos,users where repos_name='$reponame' and repos_owner=user_id";
\r
409 $res = $conn->query($sql);
\r
412 if(!$res) return -1;
\r
413 foreach($res as $row) {
415 error_log("STUFF2: ".print_r($row,true));
\r
416 $retval["id"] = $row["user_id"];
417 $retval["fullname"] = $row["user_full_name"];
418 $retval["username"] = $row["user_username"];
419 $retval["email"] = $row["user_email"];
420 $retval["desc"] = $row["user_desc"];
421 $retval["level"] = $row["user_level"];
422 $retval["status"] = $row["user_status"];
\r
428 function gwvpmini_setConfigVal($confname, $confval)
\r
432 CREATE TABLE "config" (
\r
433 "config_name" TEXT,
\r
434 "config_value" TEXT
\r
438 gwvpmini_eraseConfigVal($confname);
\r
440 $conn = gwvpmini_ConnectDB();
442 $sql = "delete from config where config_name='$confname'";
443 $conn->query($sql);
\r
445 $sql = "insert into config values('$confname', '$confval')";
\r
447 return $conn->query($sql);
\r
450 function gwvpmini_AddRepo($name, $desc, $ownerid, $perms = "perms-public")
453 error_log("addrepo in db for $name, $desc, $ownerid");
454 $conn = gwvpmini_ConnectDB();
\r
456 $sql = "insert into repos values (null, '$name', '$desc', '$ownerid', '$perms')";
\r
458 $conn->query($sql);
\r
461 function gwvpmini_GetUserId($username)
463 $conn = gwvpmini_ConnectDB();
\r
465 $sql = "select user_id from users where user_username='$username'";
467 error_log("userid sql $sql");
\r
469 $res = $conn->query($sql);
472 foreach($res as $row) {
479 function gwvpmini_GetUserNameFromEmail($email)
\r
481 $conn = gwvpmini_ConnectDB();
\r
483 $sql = "select user_username from users where user_email='$email'";
\r
485 error_log("username sql $sql");
\r
487 $res = $conn->query($sql);
\r
490 foreach($res as $row) {
\r
496 function gwvpmini_GetOwnedRepos($username)
\r
499 * CREATE TABLE "repos" (
500 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
502 "repos_description" TEXT,
503 "repos_owner" INTEGER
507 $conn = gwvpmini_ConnectDB();
510 $uid = gwvpmini_GetUserId($username);
511 $sql = "select * from repos where repos_owner='$uid'";
512 error_log("owned repos sql $sql for username $username");
513 $res = $conn->query($sql);
514 if($username == "") return false;
\r
517 foreach($res as $row) {
518 $id = $row["repos_id"];
519 $retval[$id]["name"] = $row["repos_name"];
520 $retval[$id]["desc"] = $row["repos_description"];
521 $retval[$id]["id"] = $row["repos_id"];
522 error_log(print_r($row, true));
525 error_log(print_r($retval, true));
\r
529 function gwvpmini_userLevel($id)
531 $conn = gwvpmini_ConnectDB();
533 $sql = "select user_level from users where user_id='$id'";
535 $res = $conn->query($sql);
539 foreach($res as $row) {
546 function gwvpmini_GetUsers($startat = 0, $num = 10)
548 $conn = gwvpmini_ConnectDB();
551 * CREATE TABLE "users" (
552 "user_id" INTEGER PRIMARY KEY AUTOINCREMENT,
553 "user_full_name" TEXT,
554 "user_password" TEXT,
555 "user_username" TEXT,
563 $sql = "select * from users where user_id>='$startat' order by user_id asc limit $num";
565 $res = $conn->query($sql);
568 foreach($res as $row) {
569 $id = $row["user_id"];
570 $retval[$id]["fullname"] = $row["user_full_name"];
571 $retval[$id]["username"] = $row["user_username"];
572 $retval[$id]["email"] = $row["user_email"];
573 $retval[$id]["desc"] = $row["user_desc"];
574 $retval[$id]["level"] = $row["user_level"];
575 $retval[$id]["status"] = $row["user_status"];
576 $retval[$id]["id"] = $row["user_id"];
582 function gwvp_findPeopleLike($search)
\r
584 $conn = gwvpmini_ConnectDB();
586 $sql = "select * from users where user_username like '%$search%' or user_full_name like '%$search%'";
\r
588 $res = $conn->query($sql);
591 error_log("SERACHUSER: $sql returned false");
594 error_log("SERACHUSER: $sql returned true");
598 foreach($res as $row) {
\r
599 $id = $row["user_id"];
\r
600 $retval[$id]["fullname"] = $row["user_full_name"];
\r
601 $retval[$id]["username"] = $row["user_username"];
\r
602 $retval[$id]["email"] = $row["user_email"];
\r
603 $retval[$id]["desc"] = $row["user_desc"];
\r
604 $retval[$id]["level"] = $row["user_level"];
\r
605 $retval[$id]["status"] = $row["user_status"];
\r
606 $retval[$id]["id"] = $row["user_id"];
\r
609 error_log("SEARCHUSER: array is ".print_r($retval, true));
\r
613 function gwvpmini_GetRepos($startat=0, $num=200)
\r
615 $conn = gwvpmini_ConnectDB();
\r
618 * CREATE TABLE "repos" (
619 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
621 "repos_description" TEXT,
622 "repos_owner" INTEGER
627 $sql = "select * from repos where repos_id > '$startat' order by repos_id asc limit $num";
\r
629 $res = $conn->query($sql);
\r
632 foreach($res as $row) {
\r
633 $id = $row["repos_id"];
\r
634 $retval[$id]["name"] = $row["repos_name"];
\r
635 $retval[$id]["desc"] = $row["repos_description"];
\r
636 $retval[$id]["owner"] = $row["repos_owner"];
\r
637 $retval[$id]["id"] = $row["repos_id"];
645 function gwvp_findReposLike($search)
\r
647 $conn = gwvpmini_ConnectDB();
650 * CREATE TABLE "repos" (
651 "repos_id" INTEGER PRIMARY KEY AUTOINCREMENT,
653 "repos_description" TEXT,
654 "repos_owner" INTEGER
659 $sql = "select * from repos where repos_name like '%$search%' or repos_description like '%$search%'";
661 $res = $conn->query($sql);
663 if(!$res) return false;
666 foreach($res as $row) {
667 $id = $row["repos_id"];
668 $retval[$id]["name"] = $row["repos_name"];
669 $retval[$id]["desc"] = $row["repos_description"];
670 $retval[$id]["owner"] = $row["repos_owner"];
671 $retval[$id]["id"] = $row["repos_id"];
678 function gwvpmini_GetNRepos()
680 $conn = gwvpmini_ConnectDB();
682 $sql = "select count(*) from repos";
684 $res = $conn->query($sql);
687 foreach($res as $row) {
695 function gwvpmini_GetNUsers()
\r
697 $conn = gwvpmini_ConnectDB();
\r
699 $sql = "select count(*) from users";
\r
701 $res = $conn->query($sql);
\r
704 foreach($res as $row) {
\r