This repository has been archived by the owner on Dec 20, 2023. It is now read-only.
/
db_functions.php
132 lines (103 loc) · 4.7 KB
/
db_functions.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
<?php
# A set of functions for accessing and manipulating the sqlite database that
# maps job ids to output files
# A note about SELinux: If SELinux is enabled, then it's very likely that
# write access by httpd is restricted to files with a specific security
# context. The database file will need this context. You can set the
# default context on files in a specific directory with the command:
# chcon -t httpd_sys_rw_content_t <dir>
# location of various files we'll need. (Things like the sqlite
# db file and the ini file with the MWS values.)
# Defaults to a dir that's one level up from the document root so
# that files in it aren't directly accessible from a browser.
if (! defined( 'SUPPORT_DIR'))
{ define ('SUPPORT_DIR', dirname( $_SERVER['DOCUMENT_ROOT']) . DIRECTORY_SEPARATOR . 'moab_support_files'); }
# For problems creating/opening/reading/writing the SQLite DB
class DbException extends Exception {
private $PDOErrorInfo; // from the PDO::errorInfo()
public function __construct( $errorInfo, $message = "", $code = 0, $previous = NULL) {
parent::__construct( $message, $code, $previous);
$this->PDOErrorInfo = $errorInfo;
}
final public function getErrorInfo() {
return $this->PDOErrorInfo;
}
};
# The name of the table we'll use in the db
define( 'TABLE_NAME', 'Output_Files');
# Opens (or creates, if necessary) the database file. Will also create the one
# table if necessary. Returns a PDO object
function open_db() {
// Try to open the db itself. If that fails, try to create it.
$dbFile = SUPPORT_DIR . DIRECTORY_SEPARATOR . "jobs.sqlite";
$pdo = new PDO( "sqlite:$dbFile", null, null /* array(PDO::ATTR_PERSISTENT => true) */ );
if (! $pdo) {
throw new DbException ( $pdo->errorInfo(), "Error opening (or creating) " . $dbFile);
}
$stmt = 'CREATE TABLE IF NOT EXISTS ' . TABLE_NAME .
' (jobId TEXT, username TEXT, filename TEXT, when_added INTEGER, PRIMARY KEY (jobId))';
// SQLite doesn't have a specific date type. We're using integer seconds (Unix time)
if ( $pdo->exec( $stmt) === false) {
throw new DbException ( $pdo->errorInfo(), 'Error creating ' . TABLE_NAME . ' table');
}
return $pdo;
}
# Adds a jobID and output file tuple to the table.
# Returns nothing on success. Throws an exception if there was a problem
# pdo is a PDO object (with an already opened database).
# jobId and outputFile are both strings
function add_row( $pdo, $jobId, $username, $outputFile) {
$stmt = 'INSERT INTO ' . TABLE_NAME . " VALUES ( \"$jobId\", \"$username\", \"$outputFile\", strftime('%s', 'now'))";
if ($pdo->exec( $stmt) === false) {
throw new DbException ( $pdo->errorInfo(), 'Error inserting row in ' . TABLE_NAME . ' table');
}
}
# Searches the table for the specified jobID and returns the name of the
# associated output file. Returns boolean false if the id doesn't exist
# pdo is a PDO object (with an already opened database).
function find_output_file( $pdo, $jobId) {
$outfile = ''; // empty string
$qstring = 'SELECT filename from ' . TABLE_NAME . ' WHERE jobId == \'' . $jobId . '\'';
$results = $pdo->query( $qstring);
if ($results === false) {
throw new DbException ( $pdo->errorInfo(), 'Error querying ' . TABLE_NAME . ' for job ID ' . $jobId);
}
$row = $results->fetch();
if ($row !== false) {
$outfile = $row['filename'];
} else {
$outfile = false;
}
// This is a sanity check. There should be at most a single row returned.
$row = $results->fetch();
if ($row !== false) {
throw new DbException( $pdo->errorInfo(), 'Multiple results returned from query for job ID ' . $jobId);
}
$results->closeCursor();
return $outfile;
}
# Searches the table for the specified jobID and returns the username
# associated with it. Returns boolean FALSE if the id doesn't exist
# pdo is a PDO object (with an already opened database).
function find_user( $pdo, $jobId) {
$user = ''; // empty string
$qstring = 'SELECT username from ' . TABLE_NAME . ' WHERE jobId == \'' . $jobId . '\'';
$results = $pdo->query( $qstring);
if ($results === false) {
throw new DbException ( $pdo->errorInfo(), 'Error querying ' . TABLE_NAME . ' for job ID ' . $jobId);
}
$row = $results->fetch();
if ($row !== false) {
$user = $row['username'];
} else {
$user = false;
}
// This is a sanity check. There should be at most a single row returned.
$row = $results->fetch();
if ($row !== false) {
throw new DbException( $pdo->errorInfo(), 'Multiple results returned from query for job ID ' . $jobId);
}
$results->closeCursor();
return $user;
}
?>