OCS Database Schema

Question

Hi,

I am going to start working on project about adding some features to ocs inventory. I need the OCS database schema to understand the relations between tables and what kind of data can I get from.

I have tried to dump the SQL database and generate an ERP model using MYSQL WORKBENCH but I was able to see only tables no primary key, no foreign key and especailly no relations between tables.

Could any one help me in this subject?

 

Thanks in advance and waiting for your feedback.

Answer 1

There is no schema of the actual database. The relation between the tables are hard coded in OCS with "JOIN" and not in the database.

We want to rewrite all the database for the version 3.0.

What sort of feature do you want to add ?

Comments

I am going to build a reporting tool

---

You can probably do most reports with a left outer joins of hardware.id and other table's hardware_id columns.  But I don't think there is any way to handle the names of any custom fields you've added in the accountinfo table directly in sql.   Alternatively you can get an XML dump of all the data through the soap interface if your reporting tools can parse it.

---

Depending what you're looking for, it's possible to retrieve the data using various joins, but it will make your database server work hard to show your reports.

Here's an example:

 

select A.id, A.name, D.SSN, C.tvalue, E.tvalue, A.DEVICEID from hardware A left join accountinfo B on A.id=B.hardware_id left join config C on (C.name like 'ACCOUNT_VALUE_TAG_%' and C.ivalue = B.tag) left join config E on (E.name like 'ACCOUNT_VALUE_MACHINE_%' and E.ivalue = B.fields_3) left join bios D on A.id=D.hardware_id where A.DEVICEID <> '_SYSTEMGROUP_' ;

---

The main thing would be to document the schema well enough to insert new entries as well as reporting from external programs. That would include a way to get the custom accountinfo field names and values in a pure-sql query.   Adding foreign key constraints and cascading delete triggers to enforce consistency would be a nice touch too.    Of course if a rest or soap interface is complete enough to add and update records, direct DB access would be less important.