Newer
Older
Rodrigo Goncalves
committed
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
<?php
namespace Ingress\Services\tikiwiki;
use Exception;
use Ingress\Services\Logger;
use mysqli;
class TikiWikiDB
{
private const SQL_TRACKER_VALUES = <<<EOD
select * from (
select a.* from (
select tt.name tracker_name, tti.itemId item_id, tti.lastModif last_modified, ttf.permName fieldName, ttif.value fieldValue, null as attachment_name, null as attachment_size, null as attachment_type, null as attachment_data
from tiki_tracker_item_fields ttif
inner join tiki_tracker_fields ttf on (ttf.fieldId=ttif.fieldId)
inner join tiki_tracker_items tti on (tti.itemId=ttif.itemId)
inner join tiki_trackers tt on (tt.trackerId=ttf.trackerId)
where ttf.`type` <> 'FG'
UNION
select tt.name tracker_name, tti.itemId item_id, tti.lastModif last_modified, ttf.permName fieldName, ttif.value fieldValue, tf.filename attachment_name, tf.filesize attachment_size, tf.filetype attachment_type, tf.`data` attachment_data
from tiki_tracker_item_fields ttif
inner join tiki_tracker_fields ttf on (ttf.fieldId=ttif.fieldId)
inner join tiki_tracker_items tti on (tti.itemId=ttif.itemId)
inner join tiki_trackers tt on (tt.trackerId=ttf.trackerId)
left outer join tiki_tracker_item_attachments ttia on (ttia.itemId=ttif.itemId)
left outer join tiki_files tf on (ttf.`type`='FG' and tf.fileId=ttif.value)
where ttf.`type` = 'FG' ) as a
order by
a.tracker_name, a.item_id
) as b where b.tracker_name=?
EOD;
private $conn;
public function __construct()
{
$this->conn = $this->getConnection();
}
function getConnection(): mysqli
{
$host = getenv('TIKIWIKIDB_HOST') ?: '127.0.0.1';
$port = getenv('TIKIWIKIDB_PORT') ?: '3306';
$database = getenv('TIKIWIDB_DATABASE') ?: 'tikiwiki';
$username = getenv('TIKIWIKDB_USERNAME') ?: 'tikiwiki';
$password = getenv('TIKIWIKIDB_PASSWORD') ?: 'tikiwiki';
Logger::log("Connecting to TikiDB at $host:$port with database $database and user $username");
// Create a new mysqli instance
$mysqli = new mysqli($host, $username, $password, $database, $port);
// Check for connection errors
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
return $mysqli;
}
function getTrackerData(string $trackerName) {
return $this->_executeQuery(TikiWikiDB::SQL_TRACKER_VALUES, $trackerName);
}
function _executeQuery(string $query, ...$params): array
{
// Prepare the statement
$stmt = $this->conn->prepare($query);
if ($stmt === false) {
throw new Exception("Query preparation failed: " . $this->conn->error);
}
// Bind parameters if there are any
if (!empty($params)) {
// Create a types string based on the types of the passed parameters
$types = '';
foreach ($params as $param) {
if (is_int($param)) {
$types .= 'i'; // Integer
} elseif (is_float($param)) {
$types .= 'd'; // Double
} elseif (is_string($param)) {
$types .= 's'; // String
} else {
$types .= 'b'; // Blob or other types
}
}
// Bind the parameters to the statement
$stmt->bind_param($types, ...$params);
}
// Execute the statement
if (!$stmt->execute()) {
throw new Exception("Query execution failed: " . $this->conn->error);
}
// Get the result set
$result = $stmt->get_result();
if ($result === false) {
throw new Exception("Getting result set failed: " . $this->conn->error);
}
// Fetch all rows as an associative array
$rows = $result->fetch_all(MYSQLI_ASSOC);
// Close the statement
$stmt->close();
return $rows;
}
}