Querying new format databases

Question

With server 1.x I could write multiple helpful scripts for extracting the data from OCSWEB database.

With new format of databases, where ACCOUNTINFO was divided for two parts, it is almost impossible to surely extract this admin info: how can I guess what does "fields_4" field contain?

Of course, I can query ACCOUNTINFO_CONFIG, look for "name", then calculate field name from "show_order", but MySQL does not support indirect addressing of table columns (i.e. column's name must be explicit and can not be stored in variable neither it can be result of the expression), and thus I can't use in my SQL scripts the results of calculations of the field name.

Hardcoding the fileds' names is not a very good idea.

You made he database convenient to process with PHP or other web-programming language, but that made it unusable for "regular" programming, if they can't construct the scripts in realtime. :-(

Answer 1

I have to agree that the DB change makes it much harder to access info - and that direct mysql access is always easier and more available in other languages than any kind of abstraction..  And I haven't come up with a pure-sql way to get the field names either, although it is still possible in scripting languages.    But for read-only access you might be able to use the SOAP interface to collect the info as XML which, in the current version will have the field names you created for the associated values.

http://wiki.ocsinventory-ng.org/index.php/Developers:Web_services

Comments

Thank you, but some times I need r/w access etc,,,

I almost wrote the trigger which will (re)create the view, which will represent the accountinfo table in the way I would see it.

1. getting all field names from accountinfo_config WHERE ID>2 as CSV string#ID=2 is the TAG field

2. temporary stored procedure (created and destroyed in same query) cycles thru that CSV and makes middle part of SELECT operator: concat('fields_', ID) as the_name_I_need

3. that middle part is being inserted into the string for PREPARE, which (re)creates the view I mentioned above

It seems that I can optimize this methodics, The trigger must be pushed on very rare ACCOUNTINFO structure changes...

---

I do this as a hack in perl

# find friendly field names to access as $acctmap{'NAME'} in sql.
# load column name map
$isql = qq(SELECT upper(NAME), ID from accountinfo_config where ACCOUNT_TYPE = 'COMPUTERS');
$isth = $idbh->prepare ($isql) or die "Couldn't prepare statement: " . $idbh->errstr;
$isth->execute() or warn "Couldn't execute statement: " . $isth->errstr;
while ( ($name,$field) = $isth->fetchrow_array()) {
    $acctmap{$name} = "fields_$field";
    if ($name eq 'TAG') {$acctmap{$name} = 'TAG'; }  #special case...
}

And subsequent SQL can user field names like accountinfo.$acctmap{'LOCATION'}.  But yeah, it's a hack.

---

That is!

 

set @stmt=concat(
    'create or replace view ocsweb.accinfo as select hardware_id, tag,',
    (select group_concat(concat(' fields_', ID, ' as ', name))
        from accountinfo_config
        where id > 2 and ACCOUNT_TYPE = 'computers'),
    ' from accountinfo');
# select @stmt; #for debugging perposes
prepare statement from @stmt; execute statement; deallocate prepare statement;

This snippet cycles thru all "fields_XXX" and creates human-readable representation of ACCOUNTINFO! It may be used as trigger or as a part of larger query, should it be used from only one place.

I did that!

---

See the spippet above: let the server does the job, not client! :-)

---

Thanks - I'm not all that concerned about client vs. server execution since what I'm doing runs on the same host anyway, but I do like the idea of being more language independent.  I may end up rewriting some or all of the processing in groovy instead of perl and a view would make the sql part all the same.

---

Well... the question should be treated as closed.

I described that research in my blog (in Russian): http://tm-experimental.blogspot.ru/2014/11/ocs-ng-inventory-2x-accountinfo.html