# Changes to the database schema

The schema is slightly different from the original OCS schema for two reasons:

- Compatibility with non-MySQL databases
- Constraints enforced by MDB2_Schema

This document lists the differences. Compatibility with OCSInventory should not be affected unless otherwise noticed.

## Multicolumn primary keys with autoincrement fields

Affected tables: accesslog, blacklist_subnet, drives, inputs, journallog, memories, modems, monitors, networks, ports, printers, registry, slots, snmp_cards, snmp_cartridges, snmp_cpus, snmp_drives, snmp_fans, snmp_inputs, snmp_localprinters, snmp_memories, snmp_modems, snmp_networks, snmp_ports, snmp_powersupplies, snmp_softwares, snmp_sounds, snmp_storages, snmp_switchs, snmp_trays, snmp_videos, softwares, sounds, storages, videos, virtualmachines

MDB2_Schema won't accept multicolumn primary keys if one of the columns is an autoincrement field. Since an autoincrement field is sufficient as a primary key anyway (and the extra columns dont have any effect at all), the additional columns are no longer used as primary key. A separate non-unique index is created over the additional columns.

## Autoincrement fields forced to be primary key

Affected tables: blacklist_macaddresses, blacklist_serials, blacklist_subnet

MDB2_Schema allows at most 1 autoincrement field per table and forces it to be the primary key. This is the typical usage for an autoincrement field and anything else rarely makes sense. Some tables however have an autoincrement field but a different field as primary key. This is not supported by MDB2_Schema.

The autoincrement field is now turned into the primary key and a separate unique index and not null constraint is created over the other column(s).

## TIMESTAMP data type

Affected tables: conntrack, deleted_equiv, drives, locks, netmap

The special behavior of the TIMESTAMP data type is a feature of MySQL only. The affected columns are created as ordinary fields without MySQL magic. The INSERT/UPDATE queries have to be modified to explicitly set their value to CURRENT_TIMESTAMP where necessary.

## Maximum LOB size

Affected tables: accesslog, download_available, languages, operators, snmp_softwares, temp_files

MDB2 only knows about the abstract CLOB and BLOB types without further differentiation. MySQL provides datatypes which differ in the maximum allowed length. As a result, the fields can get longer than intended (MySQL TEXT/BLOB vs. LONGTEXT/LONGBLOB). This should not cause any problems.

## Other changed datatypes

The field hardware.etime was changed to INTEGER because it gets fed with integer values. MySQL seems to tolerate this on DATETIME fields, too, but SQL standard doesn't.

The fields name and tag in the engine_mutex table have been shortened from 255 to 50 characters to work around the maximum index length with UTF-8 encoding. This is more than enough for all uses of this table.

## Additional indexes

Affected tables: registry

OCS Inventory 1.02 introduced caching of certain columns. This is not supported by Braintacle. I made sure that the affected columns are indexed and added an index where it was missing in the original schema.

## Bogus default values removed

Affected tables: blacklist_macaddresses, blacklist_serials, blacklist_subnet, conntrack, download_history, groups, operators, tags

Empty strings as default values cause a lot of trouble with MDB2_Schema and don't make much sense anyway. They have been removed (i.e. implicitly set to NULL).

Some other valid defaults (like 0 for integers) have been removed because they don't make sense in that particular context. For example, if the application always explicitly sets a value for a particular column, we don't need a meaningless default.

## Other changes

The NOT NULL constraint is removed from download_servers.add_port. This column is never used, so that insertions would always fail.

The NOT NULL constraint is kept for revalidate_from. Program logic ensures non-NULL values anyway, and bad things might happen when a NULL value accidentally slips in and is used for calculations.

The indexes have different, automatically assigned names. This should not be a problem since they are never referenced by name.

The blacklist tables are not initialized with a shipped set of values. Only a small fraction of these will ever show up, and they can easily be added manually. Having a lot of unnecessary values around might hurt performance on huge databases.