slog.sheaflabs.com

h-sphere parent_child recursion

[2018-12-11] #code

This script takes a h-sphere accountid and attempts to recurse the entire parentchild tree and fetch data from known associated tables. The list of associations is not complete but easily edited. Called with beast.php?account_id=123. It is intended only to give insight and a quick view of the H-Sphere account and associations.

# beast.assoc.php
<?php
$_direct_assoc = array(
'3ldomain'=>array(
    'table'=>'domains',
    'index'=>'id',
    'display'=>array('name')
),
'3l_dns_zone'=>array(
    'table'=>'dns_zones',
    'index'=>'id',
    'display'=>array('name','email','refresh','retry','expire','minimum','master','slave1','slave2')
),
'a_record'=>array(
    'table'=>'dns_records',
    'index'=>'id',
    'display'=>array('name','data','ttl','zone_id')
),
'antispam'=>array(
    'table'=>'antispam',
    'index'=>'id',
    'display'=>array('local','use_mdomain_prefs')
),
'antivirus'=>array(
    'table'=>'antivirus',
    'index'=>'id',
    'display'=>array('local','use_mdomain_prefs')
),
'cgi'=>array(
    'table'=>'apache_mime',
    'index'=>'id',
    'display'=>array('ext','mime_type','type','vhost_id')
),
'cgidir'=>array(
    'table'=>'apache_cgidir',
    'index'=>'id',
    'display'=>array('dir','alias')
),
'cname_record'=>array(
    'table'=>'dns_records',
    'index'=>'id',
    'display'=>array('name','data','ttl','zone_id')
),
'crontab'=>array(
    'table'=>'crontab',
    'index'=>'id',
    'display'=>array('login','num','command','mailto')
),
'cust_dns_record'=>array(
    'table'=>'dns_records',
    'index'=>'id',
    'display'=>array('name','data','ttl','zone_id')
),
'dns_zone'=>array(
    'table'=>'dns_zones',
    'index'=>'id',
    'display'=>array('name','email','refresh','retry','expire','minimum','master','slave1','slave2')
),
'domain'=>array(
    'table'=>'domains',
    'index'=>'id',
    'display'=>array('name')
),
'nodomain'=>array(
    'table'=>'domains',
    'index'=>'id',
    'display'=>array('name')
),
'domain_alias'=>array(
    'table'=>'domains',
    'index'=>'id',
    'display'=>array('name')
),
'domain_alias_a_record'=>array(
    'table'=>'dns_records',
    'index'=>'id',
    'display'=>array('name','data','ttl','zone_id')
),
'errorlog'=>array(
    'table'=>'apache_log',
    'index'=>'id',
    'display'=>array('file_name','logtype')
),
'ftp_user_traffic'=>array(
    'table'=>'traffics',
    'index'=>'id',
    'display'=>array('tt_size','tt_type')
),
'ftp_traffic'=>array(
    'table'=>'traffics',
    'index'=>'id',
    'display'=>array('tt_size','tt_type')
),
'hosting'=>array(
    'table'=>'apache_vhost',
    'index'=>'id',
    'display'=>array('host_id','indx','symlink','ssi','multiview','dir'/*,'entry'*/)
),
'hosting_alias'=>array(
    'table'=>'domain_resource_alias',
    'index'=>'id',
    'display'=>array('alias_domain_name','actual_resource_type','actual_domain_name')
),
'http_traffic'=>array(
    'table'=>'traffics',
    'index'=>'id',
    'display'=>array('tt_size','tt_type')
),
'ip'=>array(
    'table'=>'resource_amount',
    'index'=>'id',
    'display'=>array('amount')
),
'idomain_alias'=>array(
    'table'=>'vhost_alias',
    'index'=>'id',
    'display'=>array('alias')
),
'l_server'=>array(
    'table'=>'l_server',
    'index'=>'id',
    'display'=>array('name','group_id','p_server_id','type_id','signup')
),
'mail_domain'=>array(
    'table'=>'mail_domain',
    'index'=>'id',
    'display'=>array('catch_all')
),
'mail_forward'=>array(
    'table'=>'mail_forwards',
    'index'=>'id',
    'display'=>array('email_local','email_foreign')
),
'mail_quota'=>array(
    'table'=>'quotas',
    'index'=>'id',
    'display'=>array('size_mb')
),
'mail_domain_alias'=>array(
    'table'=>'mail_domain_aliases',
    'index'=>'id',
    'display'=>array('domain_alias')
),
'mail_service_alias'=>array(
    'table'=>'domain_resource_alias',
    'index'=>'id',
    'display'=>array('alias_domain_name','actual_resource_type','actual_domain_name')
),
'mailbox'=>array(
    'table'=>'mailboxes',
    'index'=>'id',
    'display'=>array('full_email','discard_mail','email')
),
'mail_service'=>array(
    'table'=>'mail_services',
    'index'=>'id',
    'display'=>array('mail_server')
),
'mail_traffic'=>array(
    'table'=>'traffics',
    'index'=>'id',
    'display'=>array('tt_size','tt_type')
),
'MySQL'=>array(
    'table'=>'mysqlres',
    'index'=>'id',
    'display'=>array('mysql_host_id'),
    'expand'=>array('mysql_host_id'=>'l_server')
),
'MySQLDatabase'=>array(
    'table'=>'mysqldb',
    'index'=>'id',
    'display'=>array('db_name','db_description','locked_by','parent_id')
),
'mysqldb_quota'=>array(
    'table'=>'quotas',
    'index'=>'id',
    'display'=>array('size_mb')
),
'MySQLUser'=>array(
    'table'=>'mysql_users',
    'index'=>'id',
    'display'=>array('login','locked_by','parent_id')
),
'mx'=>array(
    'table'=>'dns_records',
    'index'=>'id',
    'display'=>array('name','data','ttl','pref','zone_id')
),
'php3'=>array(
    'table'=>'resource_version',
    'index'=>'resource_id',
    'display'=>array('version')
),
'php3entry'=>array(
    'table'=>'apache_mime',
    'index'=>'id',
    'display'=>array('ext','mime_type','type','vhost_id')
),
'quota'=>array(
    'table'=>'quotas',
    'index'=>'id',
    'display'=>array('size_mb')
),
'referrerlog'=>array(
    'table'=>'apache_log',
    'index'=>'id',
    'display'=>array('logtype','file_name')
),
'subdomain'=>array(
    'table'=>'domains',
    'index'=>'id',
    'display'=>array('name')
),
'sharedssl'=>array(
    'table'=>'shared_ssl',
    'index'=>'id',
    'display'=>array('name','zone_id')
),
'sshresource'=>array(
    'table'=>'shells',
    'index'=>'id',
    'display'=>array('shell')
),
'ssi'=>array(
    'table'=>'apache_mime',
    'index'=>'id',
    'display'=>array('ext','mime_type','type','vhost_id')
),
'spf'=>array(
    'table'=>'spf',
    'index'=>'id',
    'display'=>array('processing')
),
'traffic'=>array(
    'table'=>'traffics',
    'index'=>'id',
    'display'=>array('tt_size','tt_type')
),
'transferlog'=>array(
    'table'=>'apache_log',
    'index'=>'id',
    'display'=>array('file_name','logtype')
),
'unixuser'=>array(
    'table'=>'unix_user',
    'index'=>'id',
    'display'=>array('login','group_name','user_id','dir','hostid'),
    'expand'=>array('hostid'=>'l_server')
),
'unixsubuser'=>array(
    'table'=>'unix_user',
    'index'=>'id',
    'display'=>array('login','group_name','user_id','dir','hostid'),
    'expand'=>array('hostid'=>'l_server')
),
'vhost_alias'=>array(
    'table'=>'vhost_alias',
    'index'=>'id',
    'display'=>array('alias')
),
'webalizer'=>array(
    'table'=>'apache_webalizer',
    'index'=>'id',
    'display'=>array('dir')
),
);

// type_names known to not have a normal relationship model

$_ignore_assoc = array('billviewer','tt','account_preview','easyapp');
?>

# beast.php
<?php
if(empty($_REQUEST['account_id'])) {
exit;
}

class pg {
    public function __construct() {
        $this->pgcon = pg_connect("host=1.2.3.4 port=5432 dbname=hsphere user=custom password=yourpassword") or die('DB Connection ERROR: ' . pg_last_error());
    }

    public function q($_q) {
        $_res = pg_query($this->pgcon,$_q);
    return $_res;
    }

    public function srl($_q,$_index) {
        $_res = $this->q($_q);
        while($_row = pg_fetch_assoc($_res)) {
            $_data[$_row[$_index]] = $_row;
        }
        return $_data;
    }

    public function srr($_q) {
        $_res = $this->q($_q);
        $_row = pg_fetch_assoc($_res);
        return $_row;
    }
}

function hs_assoc_fetch($_assoc,$_index_id) {
    global $_pg,$_direct_assoc;
    $_q = "SELECT ".implode(",",$_direct_assoc[$_assoc]['display'])." FROM ".$_direct_assoc[$_assoc]['table']." WHERE ".$_direct_assoc[$_assoc]['index']." = '".$_index_id."';";
    return $_pg->srr($_q);
}

$_pg = new pg();

include('beast.assoc.php');

$_q = "select * from type_name ORDER by id;";
$_tns = $_pg->srl($_q,"id");

$_q = "select * from parent_child where parent_id='".pg_escape_string($_REQUEST['account_id'])."' and parent_type=0 and child_type !=0 order by child_type;";
$_masters = $_pg->srl($_q,"child_id");

function child_fetch($_pcs) {
    global $_pg,$_tns,$_level,$_direct_assoc,$_missing_assoc,$_ignore_assoc;
    if(!empty($_pcs['child_id']) &amp;&amp; !empty($_pcs['child_type'])) {
        $_q = "select * from parent_child where parent_id='".$_pcs['child_id']."' and parent_type='".$_pcs['child_type']."' order by child_type;";
        $_pcs_cs = $_pg->srl($_q,"child_id");
        if(count($_pcs_cs) >=1 &amp;&amp; !empty($_pcs_cs)) {
            $_level++;
            foreach($_pcs_cs as $_k => $_c) {
                unset($_hs_assoc);
                if(!empty($_direct_assoc[$_tns[$_c['child_type']]['name']]['table'])) {
                    $_hs_assoc = hs_assoc_fetch($_tns[$_c['child_type']]['name'],$_c['child_id']);
                } else {
                    if(!in_array($_tns[$_c['child_type']]['name'],$_ignore_assoc)) {
                        $_missing_assoc[] = $_tns[$_c['child_type']]['name'];
                    }
                }

                echo "<tr>";
                echo "<td width='25%' style='font-size: 10pt'>";
                for($_i=0; $_i < $_level; $_i++) {
                    echo "&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;";
                }
                echo $_tns[$_c['child_type']]['name']." (".$_c['child_type']."/".$_c['child_id'].") <a name='".$_c['child_id']."'/>";
                echo "</td>";
                echo "<td style='font-size: 8pt'>";
                if(is_array($_hs_assoc)) {
                    echo "id '".$_c['child_id']."' in '".$_direct_assoc[$_tns[$_c['child_type']]['name']]['table']."':<br/>";
                    foreach($_hs_assoc as $_k => $_v) {
                        echo "&amp;nbsp;&amp;nbsp;".$_k.'='.$_v.'<br/>';
                    }
                    if(is_array($_direct_assoc[$_tns[$_c['child_type']]['name']]['expand'])) {
                        foreach($_direct_assoc[$_tns[$_c['child_type']]['name']]['expand'] as $_k => $_v) {
                            $_expanded = hs_assoc_fetch($_v,$_hs_assoc[$_k]);
                            if(is_array($_expanded)) {
                                echo "id '".$_hs_assoc[$_k]."' in '".$_direct_assoc[$_v]['table']."':<br/>";
                                foreach($_expanded as $_k => $_v) {
                                    echo "&amp;nbsp;&amp;nbsp;".$_k.'='.$_v.'<br/>';
                                }
                            }
                        }
                    }
                }
                echo "</td>";
                echo "</tr>";

                child_fetch($_c);
            }
            $_level--;
        }
    }
}

echo '<table width="100%" border="1">';
foreach($_masters as $_c) {
    $_level = 0;
    echo "<tr>";
    echo "<td style='font-size: 10pt'>";
    echo $_tns[$_c['child_type']]['name']." (".$_c['child_type']."/".$_c['child_id'].") <a name='".$_c['child_id']."'/>";
    echo "</td>";
    echo "<td style='font-size: 8pt'>";
    unset($_hs_assoc);
    if(!empty($_direct_assoc[$_tns[$_c['child_type']]['name']]['table']) &amp;&amp; !in_array($_tns[$_c['child_type']]['name'],$_ignore_assoc)) {
        $_hs_assoc = hs_assoc_fetch($_tns[$_c['child_type']]['name'],$_c['child_id']);
    }
    if(is_array($_hs_assoc)) {
        echo "id '".$_c['child_id']."' in '".$_direct_assoc[$_tns[$_c['child_type']]['name']]['table']."':<br/>";
        foreach($_hs_assoc as $_k => $_v) {
            echo "&amp;nbsp;&amp;nbsp;".$_k.'='.$_v.'<br/>';
        }
        if(is_array($_direct_assoc[$_tns[$_c['child_type']]['name']]['expand'])) {
            foreach($_direct_assoc[$_tns[$_c['child_type']]['name']]['expand'] as $_k => $_v) {
                $_expanded = hs_assoc_fetch($_v,$_hs_assoc[$_k]);
                if(is_array($_expanded)) {
                    echo "id '".$_hs_assoc[$_k]."' in '".$_direct_assoc[$_v]['table']."':<br/>";
                    foreach($_expanded as $_k => $_v) {
                        echo "&amp;nbsp;&amp;nbsp;".$_k.'='.$_v.'<br/>';
                    }
                }
            }
        }
    } else {
        if(!in_array($_tns[$_c['child_type']]['name'],$_ignore_assoc)) {
            $_missing_assoc[] = $_tns[$_c['child_type']]['name'];
        }
    }
    echo "</td>";
    echo "</tr>";
    child_fetch($_c);
}
echo "</table>";


if(is_array($_missing_assoc)) {
    $_missing_assoc = array_unique($_missing_assoc);
    echo "<hr/>Missing or different association types: ".implode(",",$_missing_assoc);
}
?>

← back to index

slog.sheaflabs.com - Belief in the power of technology to shape or improve human society.

This is an archived version of the original site.