DB structure

Hi!
Where can see what any descriptions about structure of data mysql base
racktables?

Alexey Andriyanov

2013-02-06 10:51:08 UTC

Hello, Yury.

There is no documentation for SQL schema. The developers strongly
recommend to automate inserting/updating data by using RackTables' PHP
library functions, not raw SQL queries. The PHP interface is more stable
and convenient than SQL.

The documentation for these functions is lacking as well, but there are
some samples to start digging:

http://wiki.racktables.org/index.php/RackTablesDevelGuide#API
http://wiki.racktables.org/index.php/FAQ#How_do_I_manage_tags_on_a_series_of_objects_.28networks_etc.29_automaticaly.3F

Post by Yury Soldatov
Hi!
Where can see what any descriptions about structure of data mysql base
racktables?

--
Best regards,
Alexey

Les Mikesell

2013-02-06 16:50:18 UTC

There is no documentation for SQL schema. The developers strongly recommend
to automate inserting/updating data by using RackTables' PHP library
functions, not raw SQL queries. The PHP interface is more stable and
convenient than SQL.

It might be convenient for an experienced php developer working on the
same machine. I don't see how it is convenient from another language
or remotely. Has anyone wrapped a complete import/export to xml or
json around it yet? Or anything generic enough for a non-php
application to manipulate?

--
Les Mikesell
lesmikesell-***@public.gmane.org

Denis Ovsienko

2013-02-06 17:56:43 UTC

Ian Bettinger's RackTables API seems to address exactly these concerns:
https://github.com/ibettinger/racktables

Perhaps Ian can comment on this in more details.

--
Denis Ovsienko

Ian Bettinger

2013-02-06 18:51:24 UTC

Hi Yuri,

Yes, the API is intended to allow remote access. Basically it's a single
page (api.php):
https://github.com/ibettinger/racktables/blob/master/wwwroot/api.php

The documentation for it is similarly lacking, but if you add it to your
Racktables installation you can try a few sample queries like:

gets all values in chapter 11:
https://<<YOURHOST>>/api.php?method=get_chapter&chapter_no=11&style=o

gets basic info on object ID 1827:
https://<<YOURHOST>>/api.php?method=get_object&object_id=1827

gets object ID 1827, including attribute data:
https://
<<YOURHOST>>/api.php?method=get_object&object_id=1827&include_attrs=1

gets object ID 1827, including attribute data for all attributes, including
unset ones:
https://
<<YOURHOST>>/api.php?method=get_object&object_id=1827&include_attrs=1&include_unset_attrs=1

gets all objects in the depot with object type 4 (servers)
https://<<YOURHOST>>/api.php?method=get_depot&andor=and&cft[4]

Most exceptions are printed out as JSON responses.

Hope this helps. I'm working on a more robust integrated version (for
example this API basically circumvents the Racktables permissions model)
but it will probably be a couple months before that becomes a reality.

Cheers,
Ian

ps: I put up a copy of the Python client library I'm working on (still
pretty primative) here: https://github.com/ibettinger/racktables-py-client

Post by Alexey Andriyanov

Post by Alexey Andriyanov
There is no documentation for SQL schema. The developers strongly

recommend

Post by Alexey Andriyanov
to automate inserting/updating data by using RackTables' PHP library
functions, not raw SQL queries. The PHP interface is more stable and
convenient than SQL.

https://github.com/ibettinger/racktables
Perhaps Ian can comment on this in more details.
--
Denis Ovsienko

Les Mikesell

2013-02-06 19:15:23 UTC

Post by Ian Bettinger
Hi Yuri,
Yes, the API is intended to allow remote access. Basically it's a single
https://github.com/ibettinger/racktables/blob/master/wwwroot/api.php
The documentation for it is similarly lacking, but if you add it to your
https://<<YOURHOST>>/api.php?method=get_chapter&chapter_no=11&style=o
https://<<YOURHOST>>/api.php?method=get_object&object_id=1827
https://<<YOURHOST>>/api.php?method=get_object&object_id=1827&include_attrs=1
gets object ID 1827, including attribute data for all attributes, including
https://<<YOURHOST>>/api.php?method=get_object&object_id=1827&include_attrs=1&include_unset_attrs=1
gets all objects in the depot with object type 4 (servers)
https://<<YOURHOST>>/api.php?method=get_depot&andor=and&cft[4]
Most exceptions are printed out as JSON responses.
Hope this helps. I'm working on a more robust integrated version (for
example this API basically circumvents the Racktables permissions model) but
it will probably be a couple months before that becomes a reality.

Thanks but my data isn't in racktables yet and probably won't be until
there is a way to automate the input and track object keys that can
let other programs make changes or stay in sync. Is there a way to
import things? And if there is/will be, what happens if you don't
initially know the positions within the racks for the items?

--
Les Mikesell
lesmikesell-***@public.gmane.org

Ian Bettinger

2013-02-06 19:41:16 UTC

Just use the add object method:
https://
<<YOURHOST>>/api.php?method=add_object&object_name=my-new-server&object_type_id=4

You don't need to know the allocation to add an object -- it'll just show
up as "Unmounted." The API has a method, "update_object_allocation," which
can be used to mount the object when you're ready.

Once the host is added, yes, you need to be able to track the hostname <->
racktables object id mapping. My scripts etc that use Racktables typically
use either "get_depot" to get the full depot mapping, or "search" to try to
find a single server or whatever.
https://<<YOURHOST>>/api.php?method=search&term=<<HOSTNAME OR WHATEVER>>

--Ian

Post by Ian Bettinger

<<YOURHOST>>/api.php?method=get_object&object_id=1827&include_attrs=1

Post by Ian Bettinger
gets object ID 1827, including attribute data for all attributes,

including

Post by Ian Bettinger
https://

<<YOURHOST>>/api.php?method=get_object&object_id=1827&include_attrs=1&include_unset_attrs=1

Post by Ian Bettinger
gets all objects in the depot with object type 4 (servers)
https://<<YOURHOST>>/api.php?method=get_depot&andor=and&cft[4]
Most exceptions are printed out as JSON responses.
Hope this helps. I'm working on a more robust integrated version (for
example this API basically circumvents the Racktables permissions model)

but

Post by Ian Bettinger
it will probably be a couple months before that becomes a reality.

Les Mikesell

2013-02-06 20:57:04 UTC

https://<<YOURHOST>>/api.php?method=add_object&object_name=my-new-server&object_type_id=4

Thanks - maybe I'm a little dense, but I don't understand how working
with an undocumented API is supposed to be easier than an undocumented
sql schema. How do I know what any of those elements are or what
constraints there are on their relationships?

You don't need to know the allocation to add an object -- it'll just show up
as "Unmounted." The API has a method, "update_object_allocation," which can
be used to mount the object when you're ready.

I was sort of hoping I could get them grouped into locations/racks
(which I have for most things), and then use the racktables web
interface to adjust the positioning of items in a rack when there is
time for a physical audit of a rack at a time.

Once the host is added, yes, you need to be able to track the hostname <->
racktables object id mapping. My scripts etc that use Racktables typically
use either "get_depot" to get the full depot mapping, or "search" to try to
find a single server or whatever.
https://<<YOURHOST>>/api.php?method=search&term=<<HOSTNAME OR WHATEVER>>

We very frequently swap hardware, keeping the hostnames. But the
other databases tracking inventory that I'd like to keep in sync will
have a different identifier that needs to track the old box that will
be re-purposed too.

--
Les Mikesell
lesmikesell-***@public.gmane.org

Denis Ovsienko

2013-02-07 06:58:24 UTC

https://<<YOURHOST>>/api.php?method=add_object&object_name=my-new-server&object_type_id=4

If you can afford donating a few workhours to write missing piece of documentation, Ian can help publishing it.

You don't need to know the allocation to add an object -- it'll just show up
as "Unmounted." The API has a method, "update_object_allocation," which can
be used to mount the object when you're ready.

There's the "zero-unit" mounting slot which is purposed exactly for this, is it assignable through this API?

The "asset tag" object's column is purposed for tracking physical assets, assuming, that each piece of hardware is labelled with a unique tag. If this is not so, I'd consider adding such labels or used RackTables object_id number to label the servers. Hostname, as a temporary property of a physical server, doesn't work in some cases.

--
Denis Ovsienko

Les Mikesell

2013-02-07 17:53:15 UTC

https://<<YOURHOST>>/api.php?method=add_object&object_name=my-new-server&object_type_id=4

If you can afford donating a few workhours to write missing piece of documentation, Ian can help publishing it.

Like I said, I'm dense... I don't see any chance of being able to
succeed at decoding php that is accessing an undocumented db, and
especially not without understanding the underlying sql. But if I
understood the sql I probably wouldn't need an api to copy back and
forth to a different db where I could access it with tools like jasper
reports, etc.

Post by Les Mikesell
I was sort of hoping I could get them grouped into locations/racks
(which I have for most things), and then use the racktables web
interface to adjust the positioning of items in a rack when there is
time for a physical audit of a rack at a time.

There's the "zero-unit" mounting slot which is purposed exactly for this, is it assignable through this API?

Thanks - that sounds promising. What about power connections to PDU's
where I have some identifier but no concept of location?

Post by Les Mikesell
We very frequently swap hardware, keeping the hostnames. But the
other databases tracking inventory that I'd like to keep in sync will
have a different identifier that needs to track the old box that will
be re-purposed too.

We haven't tracked these perfectly, but I can probably work something
out (maybe make one up from a MAC address or bios serial number where
they are missing) and keep them mapped to the object_id in whatever
conversions I do. Most of our recent servers are HPs which come
with a vendor asset tag that you can read both physically and in
software (even on blades, I think). In any case that part is my
problem as long as there is a unique permanent key that I can find
somehow with the api.

--
Les Mikesell
lesmikesell-***@public.gmane.org

Denis Ovsienko

2013-02-10 12:09:09 UTC

[...]

Yes, RackTables database schema is undocumented, in that there are no supplementary notes behind the tables and relations, but the schema itself is open. For example, you can issue a "SHOW CREATE TABLE Object" against the live database and compare the output to the contents of install.php of respective release. The excerpts should normally be the same.

As it was said before, there are few (navigation.php and index.php to start with) core .php files pulling the rest of RackTables together, likewise, there are few core tables in the database. If you want to see the relations between the tables, in the first place study the foreign keys, but keep in mind there may be other constraints or format translations implemented at PHP source code level. That's why using the PHP functions is just safer and saves the time.

Most important, it doesn't require to be a PHP or SQL expert to begin studying RackTables, and if you come across particular tricky points, there are people on this mailing list to ask (somehow the scripts in the contribs repository were written after all). Documenting all the source code and database at once is impossible, but if you figure it out which pieces should be added first (better, write them), RackTables may become more friendly for starters.

Hope that helps.

[...]

There's the "zero-unit" mounting slot which is purposed exactly for this, is it assignable through this API?

Thanks - that sounds promising. What about power connections to PDU's
where I have some identifier but no concept of location?

I never did power lines accounting myself and don't even know how far RackTables is from the most basic way of doing that. There are other users interested in exactly that, however.

--
Denis Ovsienko

Les Mikesell

2013-02-12 17:19:28 UTC

Post by Denis Ovsienko
[...]

Post by Les Mikesell
Like I said, I'm dense... I don't see any chance of being able to
succeed at decoding php that is accessing an undocumented db, and
especially not without understanding the underlying sql. But if I
understood the sql I probably wouldn't need an api to copy back and
forth to a different db where I could access it with tools like jasper
reports, etc.

Something like SchemaSpy will do a reasonable job of showing the table
layout, key relationships, and constraints. But I got the impression
from what you have said before that the schema would be likely to
change.

Post by Denis Ovsienko
As it was said before, there are few (navigation.php and index.php to start with) core .php files pulling the rest of RackTables together, likewise, there are few core tables in the database. If you want to see the relations between the tables, in the first place study the foreign keys, but keep in mind there may be other constraints or format translations implemented at PHP source code level. That's why using the PHP functions is just safer and saves the time.

Maybe, but I'd have to add in the time for learning php - and I don't
think it is documented well enough to avoid understanding the DB
anyway. Also, I'd still have to do a full export into my own DB to use
a fancy report writer like Jasper or the Pentaho tool set that would
understand sql natively.

There's the "zero-unit" mounting slot which is purposed exactly for this, is it assignable through this API?

Thanks - that sounds promising. What about power connections to PDU's
where I have some identifier but no concept of location?

I never did power lines accounting myself and don't even know how far RackTables is from the most basic way of doing that. There are other users interested in exactly that, however.

Initially I'll just want to get the connections mapped to something
without breaking the display functions. I can do the calculations
from the data I have, but our way of doing it is to count the max
power on both sides of a dual power supply, just to ensure that a
failure on on side won't overload the other. Someone else might be
using the average use value for overall heat production calculations,
though.

--
Les Mikesell
lesmikesell-***@public.gmane.org
.

Ian Bettinger

2013-02-08 18:34:12 UTC

Hi guys,

Post by Denis Ovsienko
There's the "zero-unit" mounting slot which is purposed exactly for this,

is it assignable through this API?

Yes, here's how to assign both normal mounting and "zero-u" mounting via
the API:

mounts object id 3967 in rack id 18, position 8, front, interior, and rear
atoms (front=0, middle=1, rear=2):
https://<<YOUR
HOST>>/api.php?object_id=3967&method=update_object_allocation&allocate_to[]=atom_18_8_0&allocate_to[]=atom_18_8_0&allocate_to[]=atom_18_8_1&allocate_to[]=atom_18_8_2

mounts object id 3967 as "zero-u" in rack id 18:
https://<<YOUR HOST

/api.php?object_id=3967&method=update_object_allocation&allocate_to[]=zerou_18

Cheers,
Ian

Post by Denis Ovsienko
https://

<<YOURHOST>>/api.php?method=add_object&object_name=my-new-server&object_type_id=4

If you can afford donating a few workhours to write missing piece of
documentation, Ian can help publishing it.

Post by Denis Ovsienko
You don't need to know the allocation to add an object -- it'll just

show up

Post by Denis Ovsienko
as "Unmounted." The API has a method, "update_object_allocation,"

which can

Post by Denis Ovsienko
be used to mount the object when you're ready.

There's the "zero-unit" mounting slot which is purposed exactly for this,
is it assignable through this API?

Post by Denis Ovsienko
Once the host is added, yes, you need to be able to track the hostname

<->

Post by Denis Ovsienko
racktables object id mapping. My scripts etc that use Racktables

typically

Post by Denis Ovsienko
use either "get_depot" to get the full depot mapping, or "search" to

try to

Post by Denis Ovsienko
find a single server or whatever.
https://<<YOURHOST>>/api.php?method=search&term=<<HOSTNAME OR

WHATEVER>>

The "asset tag" object's column is purposed for tracking physical assets,
assuming, that each piece of hardware is labelled with a unique tag. If
this is not so, I'd consider adding such labels or used RackTables
object_id number to label the servers. Hostname, as a temporary property of
a physical server, doesn't work in some cases.
--
Denis Ovsienko

Yury Soldatov

2013-02-07 10:56:57 UTC

Hello!

I need import data from another database.
Could you give two examples of how to add some parameters to the object
and how create object.

As I understood I needed functions are in the ../wwwroot/inc/database.php
What function can add data to the object? What function create object?

How, for example, knowing the name of the object, add to it the serial
number and place it in the rack and unite?

May be i may use Ian's API, but i need simple examples of how it use.

Thanks.

Post by Alexey Andriyanov
Hello, Yury.
There is no documentation for SQL schema. The developers strongly
recommend to automate inserting/updating data by using RackTables' PHP
library functions, not raw SQL queries. The PHP interface is more
stable and convenient than SQL.
The documentation for these functions is lacking as well, but there
http://wiki.racktables.org/index.php/RackTablesDevelGuide#API
http://wiki.racktables.org/index.php/FAQ#How_do_I_manage_tags_on_a_series_of_objects_.28networks_etc.29_automaticaly.3F

Post by Yury Soldatov
Hi!
Where can see what any descriptions about structure of data mysql
base racktables?

Alexey Andriyanov

2013-02-07 16:10:00 UTC

You can examine how web operations work.

Each modification through web causes HTTP POST request being sent with
at least these base parameters: 'page', 'tab', 'op'.
There is the $ophandler array in wwwroot/inc/navigation.php filled with
function names for each triplet of base web operation parameters.

Then, when you determine the handler function name for you operation
(like object creating), you could examine its code in
wwwroot/inc/ophandlers.php. It usually calls appropriate functions from
database.php.

Also, there are some trivial operations which have no special ophandler
function. Then the universal database wrapper called tableHandler takes
place. It makes direct SQL queries, building them by $opspec_list array
data.

Hope this information will help you.

Post by Yury Soldatov
I need import data from another database.
Could you give two examples of how to add some parameters to the
object and how create object.
As I understood I needed functions are in the ../wwwroot/inc/database.php
What function can add data to the object? What function create object?
How, for example, knowing the name of the object, add to it the serial
number and place it in the rack and unite?
May be i may use Ian's API, but i need simple examples of how it use.

--
Best regards,
Alexey

Les Mikesell

2013-02-07 18:03:57 UTC