Subversion Repositories oidplus

Compare Revisions

Regard whitespace Rev 1170 → Rev 1171

/trunk/doc/developer_notes/database/access_todo.txt
6,13 → 6,28
 
The following things need to be addressed in order to make OIDplus compatible with Microsoft Access:
 
- When *.accdb file is opened in Microsoft Access, and the page is loaded, then you get the message "Table oidplus_config" is missing
(Because the system thinks that the "select" error comes from a missing table rather than a locking issue)
 
- ODBC wrong datatype for prepared statements
$res2 = OIDplus::db()->query("select username, severity from ###log_user ".
"where log_id = ?", array($row['id']));
log_id = '50' ==> it should be 50, not '50'
The prepared statements of PDO work correctly
==> TODO: Fixed, but now we need to make sure that the data types in the query-array are correct:
wrong:
$res2 = OIDplus::db()->query("...", array($row['id']));
correct:
$res2 = OIDplus::db()->query("...", array((int)$row['id']));
Also for booleans!
 
- Add DSN example strings as example how to connect to Access
C:\inetpub\wwwroot\oidplus\plugins\database\odbc\OIDplusDatabasePluginODBC.class.php
C:\inetpub\wwwroot\oidplus\plugins\database\pdo\OIDplusDatabasePluginPDO.class.php
OIDplus::baseConfig()->setValue('ODBC_DSN', 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\inetpub\wwwroot\oidplus\userdata\database\oidplus.accdb;');
OIDplus::baseConfig()->setValue('ODBC_DSN', 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\inetpub\\wwwroot\\oidplus\\trunk\\userdata\\database\\oidplus.accdb;');
OIDplus::baseConfig()->setValue('ODBC_USERNAME', '');
OIDplus::baseConfig()->setValue('ODBC_PASSWORD', '');
OIDplus::baseConfig()->setValue('PDO_DSN', 'odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\inetpub\wwwroot\oidplus\userdata\database\oidplus.accdb;');
OIDplus::baseConfig()->setValue('PDO_DSN', 'odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\inetpub\\wwwroot\\oidplus\\trunk\\userdata\\database\\oidplus.accdb;');
OIDplus::baseConfig()->setValue('PDO_USERNAME', '');
OIDplus::baseConfig()->setValue('PDO_PASSWORD', '');
 
20,9 → 35,10
 
* C:\inetpub\wwwroot\oidplus\setup\sql\struct_access.sql
"Drop table if exists"
Problem: Inside access, we need to execute each single CREATE TABLE command by hand! We cannot just copy-paste the whole code!
Problem: Inside Access, we need to execute each single CREATE TABLE command by hand! We cannot just copy-paste the whole code!
=> However, other tools like HickelSOFT DBTool can correctly send SQL queries to the ACCDB file via ADO
 
* Should we give the user an empty *.accdb file?
* Should we give the user an empty *.accdb file (as well as an empty SQLite3 file)?
 
- Test if everything works, including Unicode and boolean flags (checkboxes etc.)
 
/trunk/includes/classes/OIDplusObject.class.php
226,17 → 226,17
 
if (!OIDplus::baseConfig()->getValue('OBJECT_CACHING', true)) {
if (!$ra) {
$res = OIDplus::db()->query("select oChild.id as id, oChild.ra_email as child_mail, oParent.ra_email as parent_mail from ###objects as oChild ".
$res = OIDplus::db()->query("select oChild.id as child_id, oChild.ra_email as child_mail, oParent.ra_email as parent_mail from ###objects as oChild ".
"left join ###objects as oParent on oChild.parent = oParent.id");
$res->naturalSortByField('oChild.id');
while ($row = $res->fetch_array()) {
if (!OIDplus::authUtils()->isRaLoggedIn($row['parent_mail']) && OIDplus::authUtils()->isRaLoggedIn($row['child_mail'])) {
$x = self::parse($row['id']); // can be NULL if namespace was disabled
$x = self::parse($row['child_id']); // can be NULL if namespace was disabled
if ($x) $out[] = $x;
}
}
} else {
$res = OIDplus::db()->query("select oChild.id as id from ###objects as oChild ".
$res = OIDplus::db()->query("select oChild.id as child_id from ###objects as oChild ".
"left join ###objects as oParent on oChild.parent = oParent.id ".
"where (".OIDplus::db()->getSlang()->isNullFunction('oParent.ra_email',"''")." <> ? and ".
OIDplus::db()->getSlang()->isNullFunction('oChild.ra_email',"''")." = ?) or ".
244,7 → 244,7
array($ra, $ra, $ra));
$res->naturalSortByField('oChild.id');
while ($row = $res->fetch_array()) {
$x = self::parse($row['id']); // can be NULL if namespace was disabled
$x = self::parse($row['child_id']); // can be NULL if namespace was disabled
if ($x) $out[] = $x;
}
}
/trunk/plugins/viathinksoft/adminPages/500_list_ras/OIDplusPageAdminListRAs.class.php
41,23 → 41,23
private function get_ralist(): array {
$tmp = array();
if (OIDplus::db()->getSlang()->id() == 'mysql') {
$res = OIDplus::db()->query("select distinct BINARY(email) as email from ###ra"); // "binary" because we want to ensure that 'distinct' is case sensitive
$res = OIDplus::db()->query("select distinct BINARY(email) as distinct_email from ###ra"); // "binary" because we want to ensure that 'distinct' is case sensitive
} else {
$res = OIDplus::db()->query("select distinct email as email from ###ra"); // distinct in PGSQL is always case sensitive
$res = OIDplus::db()->query("select distinct email as distinct_email from ###ra"); // distinct in PGSQL is always case sensitive
}
while ($row = $res->fetch_array()) {
$tmp[$row['email']] = 1;
$tmp[$row['distinct_email']] = 1;
}
if (OIDplus::db()->getSlang()->id() == 'mysql') {
$res = OIDplus::db()->query("select distinct BINARY(ra_email) as ra_email from ###objects");
$res = OIDplus::db()->query("select distinct BINARY(ra_email) as distinct_ra_email from ###objects");
} else {
$res = OIDplus::db()->query("select distinct ra_email as ra_email from ###objects");
$res = OIDplus::db()->query("select distinct ra_email as distinct_ra_email from ###objects");
}
while ($row = $res->fetch_array()) {
if (!isset($tmp[$row['ra_email']])) {
$tmp[$row['ra_email']] = 0;
if (!isset($tmp[$row['distinct_ra_email']])) {
$tmp[$row['distinct_ra_email']] = 0;
} else {
$tmp[$row['ra_email']] = 2;
$tmp[$row['distinct_ra_email']] = 2;
}
}
ksort($tmp);
/trunk/plugins/viathinksoft/adminPages/600_log/OIDplusPageAdminLogEvents.class.php
60,7 → 60,7
// ---
$users = array();
$res2 = OIDplus::db()->query("select username, severity from ###log_user ".
"where log_id = ?", array($row['id']));
"where log_id = ?", array((int)$row['id']));
while ($row2 = $res2->fetch_array()) {
$users[] = $row2['username'];
if ($row2['username'] == 'admin') $severity = $row2['severity'];
69,7 → 69,7
// ---
$objects = array();
$res2 = OIDplus::db()->query("select object, severity from ###log_object ".
"where log_id = ?", array($row['id']));
"where log_id = ?", array((int)$row['id']));
while ($row2 = $res2->fetch_array()) {
$objects[] = $row2['object'];
}
/trunk/plugins/viathinksoft/database/odbc/OIDplusDatabaseConnectionODBC.class.php
119,11 → 119,16
} else {
$replace = $arg ? '1' : '0';
}
} else if (is_int($arg)) {
$replace = $arg;
} else if (is_float($arg)) {
$replace = number_format($arg, 10, '.', '');
} else {
// TODO: More types?
if ($this->slangDetectionDone) {
$replace = "'".$this->getSlang()->escapeString($arg)."'"; // TODO: types
$replace = "'".$this->getSlang()->escapeString($arg ?? '')."'";
} else {
$replace = "'".str_replace("'", "''", $arg)."'"; // TODO: types
$replace = "'".str_replace("'", "''", $arg)."'";
}
}
$pos = strpos($sql, $needle);
195,6 → 200,7
 
if (!$this->conn) {
$message = odbc_errormsg();
$message = vts_utf8_encode($message); // Make UTF-8 if it is NOT already UTF-8. Important for German Microsoft Access.
throw new OIDplusConfigInitializationException(trim(_L('Connection to the database failed!').' '.$message));
}
 
/trunk/plugins/viathinksoft/database/pdo/OIDplusDatabaseConnectionPDO.class.php
132,7 → 132,7
public function error(): string {
$err = $this->last_error;
if ($err == null) $err = '';
return $err;
return vts_utf8_encode($err);
}
 
/**
160,6 → 160,7
$this->conn = new \PDO($dsn, $username, $password, $options);
} catch (\PDOException $e) {
$message = $e->getMessage();
$message = vts_utf8_encode($message); // Make UTF-8 if it is NOT already UTF-8. Important for German Microsoft Access.
throw new OIDplusConfigInitializationException(trim(_L('Connection to the database failed!').' '.$message));
}
 
/trunk/plugins/viathinksoft/publicPages/093_rainfo/OIDplusPagePublicRaInfo.class.php
151,10 → 151,10
if (OIDplus::db()->getSlang()->id() == 'mysql') {
$res = OIDplus::db()->query("select distinct BINARY(email) as email from ###ra"); // "binary" because we want to ensure that 'distinct' is case sensitive
} else {
$res = OIDplus::db()->query("select distinct email as email from ###ra"); // distinct in PGSQL is always case sensitive
$res = OIDplus::db()->query("select distinct email as distinct_email from ###ra"); // distinct in PGSQL is always case sensitive
}
while ($row = $res->fetch_array()) {
$out[] = 'oidplus:rainfo$'.$row['email'];
$out[] = 'oidplus:rainfo$'.$row['distinct_email'];
}
}
 
/trunk/plugins/viathinksoft/raPages/099_object_log/OIDplusPageRaObjectLog.class.php
83,7 → 83,7
while ($row = $res->fetch_array()) {
$users = array();
$res2 = OIDplus::db()->query("select username, severity from ###log_user ".
"where log_id = ?", array($row['id']));
"where log_id = ?", array((int)$row['id']));
while ($row2 = $res2->fetch_array()) {
$users[] = $row2['username'];
}
/trunk/setup/sql/struct_access.sql
1,7 → 1,7
 
CREATE TABLE [config](
[name] varchar(50) NOT NULL,
[value] text NOT NULL,
[value] memo NOT NULL,
[description] varchar(255) null,
[protected] bit NOT NULL,
[visible] bit NOT NULL,
39,7 → 39,7
[id] varchar(255) NOT NULL,
[parent] varchar(255) NULL,
[title] varchar(255) NULL,
[description] text NULL,
[description] memo NULL,
[ra_email] varchar(100) NULL,
[confidential] bit NOT NULL,
[created] datetime null,
82,7 → 82,7
[id] AUTOINCREMENT,
[unix_ts] long NOT NULL,
[addr] varchar(45) NOT NULL,
[event] text NOT NULL,
[event] memo NOT NULL,
primary key
(
[id]