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
133
134
135
136
137
138
139
140
141
142
143
144
|
<?php
/**
* This class is used to test database is well-constructed.
*/
class FreshRSS_DatabaseDAO extends Minz_ModelPdo {
//MySQL error codes
const ER_BAD_FIELD_ERROR = '42S22';
const ER_BAD_TABLE_ERROR = '42S02';
const ER_TRUNCATED_WRONG_VALUE_FOR_FIELD = '1366';
//MySQL InnoDB maximum index length for UTF8MB4
//https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html
const LENGTH_INDEX_UNICODE = 191;
public function tablesAreCorrect() {
$sql = 'SHOW TABLES';
$stm = $this->bd->prepare($sql);
$stm->execute();
$res = $stm->fetchAll(PDO::FETCH_ASSOC);
$tables = array(
$this->prefix . 'category' => false,
$this->prefix . 'feed' => false,
$this->prefix . 'entry' => false,
$this->prefix . 'entrytmp' => false,
$this->prefix . 'tag' => false,
$this->prefix . 'entrytag' => false,
);
foreach ($res as $value) {
$tables[array_pop($value)] = true;
}
return count(array_keys($tables, true, true)) == count($tables);
}
public function getSchema($table) {
$sql = 'DESC ' . $this->prefix . $table;
$stm = $this->bd->prepare($sql);
$stm->execute();
return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC));
}
public function checkTable($table, $schema) {
$columns = $this->getSchema($table);
$ok = (count($columns) == count($schema));
foreach ($columns as $c) {
$ok &= in_array($c['name'], $schema);
}
return $ok;
}
public function categoryIsCorrect() {
return $this->checkTable('category', array(
'id', 'name',
));
}
public function feedIsCorrect() {
return $this->checkTable('feed', array(
'id', 'url', 'category', 'name', 'website', 'description', 'lastUpdate',
'priority', 'pathEntries', 'httpAuth', 'error', 'keep_history', 'ttl', 'attributes',
'cache_nbEntries', 'cache_nbUnreads',
));
}
public function entryIsCorrect() {
return $this->checkTable('entry', array(
'id', 'guid', 'title', 'author', 'content_bin', 'link', 'date', 'lastSeen', 'hash', 'is_read',
'is_favorite', 'id_feed', 'tags',
));
}
public function entrytmpIsCorrect() {
return $this->checkTable('entrytmp', array(
'id', 'guid', 'title', 'author', 'content_bin', 'link', 'date', 'lastSeen', 'hash', 'is_read',
'is_favorite', 'id_feed', 'tags',
));
}
public function tagIsCorrect() {
return $this->checkTable('tag', array(
'id', 'name', 'attributes',
));
}
public function entrytagIsCorrect() {
return $this->checkTable('entrytag', array(
'id_tag', 'id_entry',
));
}
public function daoToSchema($dao) {
return array(
'name' => $dao['Field'],
'type' => strtolower($dao['Type']),
'notnull' => (bool)$dao['Null'],
'default' => $dao['Default'],
);
}
public function listDaoToSchema($listDAO) {
$list = array();
foreach ($listDAO as $dao) {
$list[] = $this->daoToSchema($dao);
}
return $list;
}
public function size($all = false) {
$db = FreshRSS_Context::$system_conf->db;
$sql = 'SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema=?'; //MySQL
$values = array($db['base']);
if (!$all) {
$sql .= ' AND table_name LIKE ?';
$values[] = $this->prefix . '%';
}
$stm = $this->bd->prepare($sql);
$stm->execute($values);
$res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
return $res[0];
}
public function optimize() {
$ok = true;
$tables = array('category', 'feed', 'entry', 'entrytmp', 'tag', 'entrytag');
foreach ($tables as $table) {
$sql = 'OPTIMIZE TABLE `' . $this->prefix . $table . '`'; //MySQL
$stm = $this->bd->prepare($sql);
$ok &= $stm != false;
if ($stm) {
$ok &= $stm->execute();
}
}
return $ok;
}
}
|
