# Braintacle's patched version of OCS Inventory NG

## About this version

This is a patched version of OCS inventory NG 2.0.5/1.3.3 that uses a database abstraction layer for all database access, allowing to work with databases other than MySQL. It was developed and tested with PostgreSQL, which is the recommended database for this version. MySQL is also supported. Other databases might work, but are entirely untested.

This project is not provided by the OCS Inventory NG team. Do not bother them with any issues you encounter with this version. Use Braintacle's support tracker or mailing list instead.

This version comes bundled with Braintacle. It is not a requirement for using Braintacle if you use MySQL – Braintacle works with an original OCS Inventory NG database, too.

This version of the ocsreports console is deprecated. It will be maintained for important bugfixes, but no longer receive any major upgrades. Most notably, it will stick at version 1.3.3. Version 2.x will not be ported. New features will be implemented in the Braintacle console only.

The agent that runs on the clients does not make any direct database connections. All interaction runs through the communication server via HTTP/HTTPS. No special version of the agent is required for the modified server. Use any of the OCS Inventory NG agents.

### Differences to the original version

Besides the different database access methods, more changes have been made to the code:

- Some changes to the database schema were necessary for portability. Braintacle and its bundled communication server work with both schema types (Braintacle and OCS), but an unmodified OCS Inventory NG installation will not work with the Braintacle schema.
- The setup must be done manually, giving the administrator more control over the installation process. This is better than having a script messing with the system, which might break things and makes uninstallation more difficult. Detailed installation instructions are given in this document.
- The communication server can optionally log to syslog instead of an extra log file.
- The communication server supports the MSofficeKey plugin out of the box – no patching necessary.
- The workaround for bug #530881 is included.
- The caching feature is disabled by default. Leave it disabled.
- A lot of improvements under the hood.

## Installation

### Install Braintacle first

This version cannot be run standalone. It depends on a properly configured Braintacle installation. In particular, the database.ini file needs to be set up for the schema manager (see below). Refer to README.txt in Braintacle's base directory for instructions.

### Create the database and user

If you don't already have the database set up somewhere, you need to create it along with a database user. Install a PostgreSQL (recommended) or MySQL server, if not already available, and log into that database server with superuser privileges (typically the 'postgres' user for PostgreSQL, 'root' for MySQL).

For PostgreSQL, run:

`CREATE USER ocs WITH PASSWORD 'ocs';
CREATE DATABASE braintacle OWNER ocs ENCODING 'UTF8';`

For MySQL, run:

`CREATE USER ocs IDENTIFIED BY 'ocs';
CREATE DATABASE braintacle CHARACTER SET utf8;
GRANT ALL ON braintacle.* TO ocs;`

You can choose any database name, user name or password. UTF8 encoding is strongly recommended.

To create and initialize the tables, log out from the database and run the schema manager script in the tools directory:

`php tools/schema-manager.php --force`

This will also create a default account 'admin' with the password 'admin'. This should be changed immediately. Point your browser to the URL of your braintacle installation, log in and click 'Preferences', then 'Users'. Click on 'Edit', enter a new password, and optionally a different user name.

### Install the communication server

#### Requirements

The communication server requires an Apache installation. Other web servers are not supported because it heavily relies on mod_perl and its interface to Apache internals. mod_perl2 is available as a native package for most GNU/Linux distributions: libapache2-mod-perl2 (Debian/Ubuntu), mod_perl (Fedora), apache2-mod_perl (Suse). A Perl interpreter is installed by default most GNU/Linux distributions. Some nonstandard perl modules are required. There are several ways to install them, in preferred order:

1. As a native package from a GNU/Linux distribution, see the table below
2. Via the cpan command line utility
3. Manual download from CPAN (take care of dependencies for yourself)

Module | Debian/Ubuntu | Fedora | SUSE
--- | --- | --- | ---
Compress::Zlib | (already present) | perl-IO-Compress | ?
DBI | libdbi-perl | perl-DBI | perl-DBI
DBD::Pg (PostgreSQL only) | libdbd-pg-perl | perl-DBD-Pg | perl-DBD-Pg
DBD::mysql (MySQL only) | libdbd-mysql-perl | perl-DBD-MySQL | perl-DBD-mysql
Apache::DBI | libapache-dbi-perl | perl-Apache-DBI | perl-Apache-DBI
Date::Calc | libdate-calc-perl | perl-Date-Calc | perl-Date-Calc
XML::Simple | libxml-simple-perl | perl-XML-Simple | perl-XML-Simple
Sys::Syslog (optional) | (already present) | (already present) | (already present)
SOAP::Lite (optional) | libsoap-lite-perl | perl-SOAP-Lite | perl-SOAP-Lite
XML::Entities (optional) | (not available, use CPAN) | perl-XML-Entities | perl-XML-Entities

If you have used an original OCS Inventory NG server before, most of these modules will probably already be installed. Beware of the additional dependency on Date::Calc!

Sys::Syslog is only required if you want to use syslog instead of logging directly to a file (see below). The last 2 modules are only required for the SOAP service. This is optional and entirely untested.

#### Installation

Create a copy of the sample configuration file config/braintacle-server.conf.template and make it known to Apache. If you don't run multiple virtual hosts or want to make the application accessible on all virtual hosts, you can simply copy it to a directory where Apache will read it (typically /etc/apache2/conf.d or similar):

`cp /usr/local/share/braintacle/config/braintacle-server.conf.template /etc/apache2/conf.d/braintacle-server.conf`

To limit the application to a particular virtual host, copy the file somewhere else and include it in the <VirtualHost> block:

`Include /usr/local/share/braintacle/config/braintacle-server.conf`

The PerlSwitches directive in that file has no effect inside a <VirtualHost> section. Place it outside the section to make it work.

Create a copy of config/braintacle-server-app.conf.template in a place outside the Apache configuration:

`cp /usr/local/share/braintacle/config/braintacle-server-app.conf.template /usr/local/share/braintacle/config/braintacle-server-app.conf`

Edit both files to match your environment. Do not edit the template files directly as they will be overwritten upon upgrading.

It is important that mod_perl is loaded before the file is included. This is the case in most standard Apache setups where modules are set up before conf.d gets evaluated. If mod_perl is loaded from a config file in the same directory, make sure that its name comes before the Braintacle configuration file in alphabetical order. Rename the file if necessary.

The braintacle-server-app.conf file contains your database password in cleartext and should only be readable for your web server, for example:

`chgrp www-data /usr/local/share/braintacle/config/braintacle-server-app.conf
chmod 640 /usr/local/share/braintacle/config/braintacle-server-app.conf`

#### Configuring logging

This version of the communication server contains a patch to support syslog instead of direct logging to a file. This is enabled by default. No further action is required for this configuration.

Note that the Sys::Syslog module is required to make this work. It is typically present on *NIX systems, so that it works out of the box. On Windows, it could be installed manually, but it's difficult to get usable output there. It is recommended to set up a log file manually on Windows. You can also do this on *NIX systems if you don't want to use syslog.

To set up a log file, create a directory for them with write permissions for the web server, and possibly no read permissions for the rest of the world. For example, the following would work for Debian:

`mkdir /var/log/braintacle
chown www-data:www-data /var/log/braintacle
chmod 750 /var/log/braintacle`

Other distributions might have different user and group names for the web server.

Now edit braintacle-server-app.conf and set OCS_OPT_LOGPATH to the directory you just created.

You may want to rotate the logs regularly to prevent them from growing infinitely. A sample logrotate configuration file is shipped in config/logrotate.template. Copy this file to /etc/logrotate.d/braintacle and edit it to suit your needs.

#### Starting the server

To finish installation, reload the Apache configuration. Depending on your configuration, you may see some errors in the Apache log file like this:

    braintacle-server: Can't load SOAP::Transport::HTTP* - Web service will be unavailable

These messages are harmless and can be ignored if you don't plan to use the SOAP service.

### Installation of administration console (optional and deprecated)

The old administration console (ocsreports) is not needed for most everyday tasks. It should not be used except for some operations not yet supported by the Braintacle console:

- Creating groups. Braintacle can display and delete groups and manage manual membership and assigned packages, but not yet create new groups.
- Overriding global configuration for specific computers or groups. The global configuration can already be done in Braintacle.
- Defining and querying inventoried registry keys.

Some other operations are intentionally not supported by Braintacle, like multiple download servers or agent upload. Do not use ocsreports for these operations – they are deprecated and untested.

#### Requirements

The database must already be set up with the schema manager.

You need the pgsql/mysql PHP extension. It is avaliable as a package for GNU/Linux distributions:

PHP extension | Debian/Ubuntu | Fedora | SUSE
--- | --- | --- | ---
pgsql | php5-pgsql | php-pgsql | php5-pgsql
mysql | php5-mysql | php-mysql | php5-mysql

#### Configuration

1. Create a copy of config/ocsreports.conf.template and make it known to Apache. Edit the file to suit your needs.
2. Copy config/dbconfig.inc.php.template to ocsinventory/ocsreports/dbconfig.inc.php and edit it to match your database setup.
3. Since dbconfig.inc.php contains your database credentials, it should not be world readable. It must of course be readable for your web server.

## Technical details

The following information may be helpful if you try to hack the code yourself. If you just want to use it you can skip the rest of this document.

### Database Abstraction layers

The perl code uses the DBI class as an abstraction layer. Making the driver configurable instead of hardcoding to "mysql" does most of the trick.

The PHP part is much trickier. It uses native MySQL library functions which have to be changed one by one. Since the MDB2 API is much different from the MySQL API, direct use would be a painful and error prone procedure. So I wrote some wrapper functions mdb2_foo() to mimic the behaior of the corresponding mysql_foo() functions. This way a simple search-and-replace operation brings us halfway to our goal. See ocsreports/require/function_mdb2.php for supported functions and caveats.

There are some limitations:

- Some functions are not strictly compatible with their mysql counterparts and require adjustments to the calling code. See the comments for details.
- mdb2_query() only supports SELECT, INSERT, UPDATE and DELETE operations. There were occasional uses of SHOW and ALTER TABLE, which are not portable. MDB2 provides abstraction for these operations. This required rewriting the code portion. The same operations replace mysql_field_type() and mysql_fetch_field().
- There is no mdb2_fetch_array() function. I could have implemented one, but using either mdb2_fetch_assoc() or mdb2_fetch_row() is cleaner and sufficient in most cases. There were a few cases where both associative and numeric indices werde needed. These are special cases where only the first entry is needed, so I wrote a workaround directly in the code.

Some functions have extra optional arguments for enhanced functionality. For example, mdb2_query() supports prepared statements via 2 extra arguments. Since these arguments are optional, it is still suitable as a drop-in replacement for mysql_query().

### SQL compatibility

Unfortunately, the API switch does only half of the job. There were many MySQL-specific expressions present in the code. This started with inconsistent quoting (MySQL allows single and double quotes for string literals, while standard SQL requires single quotes) and other obvious stuff like "LIMIT a,b" instead of standard "LIMIT b OFFSET a". There were some less obvious issues like columns in GROUP BY clauses which are not present in the FROM clause (this is allowed by MySQL, but not standard SQL). It even turned out that some of these grouping statements were completely unnecessary.

I converted the statements to all standard SQL, assuming this will be understood by all databases. MDB2's Function module helps increasing portability. Since the provided database backend can be queried at runtime, DBMS-specific extensions like PostgreSQLs ILIKE operator can be used without breaking compatibility with other DBMS.

### Spelling of column identifiers

Another problem is case-sensitiveness of array/hash keys. Whenever a database function returns an array (PHP) or hash (Perl) with column identifiers as keys, it's up to the API whether these are uppercase or lowercase. PostgreSQL seems to return all lowercase identifiers, while MySQL seems to adapt the spelling from the query string (or table creation statement in case of `"SELECT * FROM...")`. Unfortunately, `$foo['bar']` is not the same as `$foo['BAR']`. The same applies to object members returned by mdb2_fetch_object().

Since this behavior would make database applications effectively unportable, both DBI and MDB2 provide an option to convert identifiers to all uppercase or all lowercase. It's then up to the application to use all uppercase or all lowercase throughout the code.

The perl code consistently uses uppercase spelling. The only necessary step was turning on uppercase conversion globally.

The PHP code was not that friendly. While the majority of the code uses lowercase spelling, there was a significant amount of uppercase use. MDB2 converts the identifiers to lowercase. Logging E_NOTICE messages helps spotting non-lowercase identifiers even for complex, dynamically generated queries. Speaking of which...

### Getting rid of E_NOTICE messages

Unfortunately, this code was developed with E_NOTICE logging turned off. Turning it on initially resulted in tons of messages flooding the logfile, making it nearly impossible to find the real errors between all those mostly harmless ones. Most of these result from situations like this:

    if ($_GET['foo'] == 'bar')...

which drops an E_NOTICE if the parameter 'foo' is not present. This is not an error – the code behaves just as expected. I wrote a helper function called check_param() to handle this. This workaround is quite clumsy and not a substitute for a more sophisticated parameter handling, but this would require a LOT more changes to the code, making it nearly impossible to merge future upstream changes.

### Tracking down problems

If you observe erratic behavior, check your logfiles. The answer is probably out there. Many of the issues introduced by my patches result from code expecting uppercase indices (remember, they all get converted to lowercase). A less frequent class of bugs results from substituting mysql_fetch_array() and MYSQL_FETCHMODE_BOTH (the default) with either mdb2_fetch_assoc() (associative indices, sufficient in most cases) or mdb2_fetch_row() (numeric indices, not so often) in rare cases where both associative and numeric indices are actually needed. All these bugs are accompanied by an "undefined index" message. Thanks to the logged filename and line number, the source of the error is easily spotted and fixed.

Another problem with case-changed indices are case-sensitive string comparisions. These are harder to track down because they fail silently without an error message. Similarly, string comparisions at SQL level behave differently: in MySQL they are case insensitive while in PostgreSQL they are case sensitive.