|
Here you will learn how to obtain different user data from system database.
Make sure to run PgSQL database first, before running queries.
Getting user-specific information from the system database
Selecting user info:
select users.* from users, user_account where users.id=user_account.user_id and user_account.account_id= n
(n = user account id)
Selecting mail accounts info:
select m.* from mailboxes m, parent_child p where p.child_type=1002 and p.child_id=m.id and p.account_id= n
(n = user account id)
Selecting resellers info:
select u.* from users u, resellers r, user_account a where u.id=r.admin_id and r.id=a.user_id and account_id= n
(n = user account id)
Selecting credit card info:
select c.* from users u, user_billing_infos u_b, credit_card c, user_account a
where u.id = u_b.user_id and c.id = u_b.billing_info_id and u.id=a.user_id and a.account_id= n
(n = user account id)
Selecting virtual FTP info:
select f.* from ftp_vuser f, parent_child p where p.child_type=2003 and p.child_id=f.id and p.account_id= n
(n = user account id)
Selecting user's home directory
To select user's home directory from the system database and connect it to account:
select * from unix_user where unix_user.id = parent_child.child_id and parent_child.account_id = n
(n = user account id)
Selecting user's website directory
Website directory always equals user's home directory and domain name.
Selecting user's hosting plan
To select the name of the plan, run the following query:
select plans.description from plans, accounts where accounts.id = n and plans.id = accounts.plan_id;
(n = user account id)
Selecting domain name
To obtain data on domain names, run the following query:
select domains.name from domains, parent_child where domains.id = parent_child.child_id and parent_child.account_id = n
(n = your id)
Selecting IP address
To retrieve data on all IPs, run the following query:
select IP from l_server_ips, parent_child where child_id = l_server_ips.r_id and parent_child.child_type = 8 and parent_child.account_id = n
(n = your id).
There can be multiple IPs per domain.
Selecting IP of the domain:
To retrieve IP for the given domain name, run the following command:
select IP from l_server_ips, domains, parent_child where domains.id = parent_child.parent_id and parent_child.child_id = l_server_ips.r_id and domains.name ='YOUR_DOMAIN_NAME'
(enter your domain name)
Selecting account ID for the domain:
To get the data about account ID of the given domain, execute the query:
select account_id from domains, parent_child where domains.id = parent_child.child_id and domains.name ='DOMAIN_NAME'
Selecting logical server of the domain:
To retrieve IP for the given domain name, run the following command:
select hostid from unix_user, parent_child where parent_child.account_id = YOUR_ACCOUNT_ID and child_id = unix_user.id
Selecting shared IP of the domain:
To get the information about shared IPs for the domain, run:
select IP from l_server_ips where where l_server_id ='HOSTID' and flag =' SHARED_IP'
selecting user's e-mail address
To receive data on clients contact emails, run the following query:
select * from contact_info, accounts where contact_info.id = accounts.ci_id and accounts.id = n
(n = user account id);
Selecting user's billing info
To select billing info from accounts, run the following query:
select * from billing_info, accounts where billing_info.id = accounts.bi_id and accounts.id =n
(n = user account id);
Verify users
To verify whether a user exists in H-Sphere:
select id from users where username='USER_NAME' and password='PASSWORD'
(enter user name and password)
Defining physical/logical servers
Run the following commands to get info about physical/logical servers:
- by IP address:
select l_server_id from l_server_ips where l_server_ips = 'YOUR_IP_ADDRESS'
(enter your IP address)
- by logical server:
select p_server_id from l_server where id = 'YOUR__L_SERVER_ID'
(enter your logical server id)
- selecting info about physical server:
select * from p_server where id = 'YOUR_P_SERVER_ID_'
(enter your physical server id)
|
