Develdoc:Tables

From PMWH2 - PHPMyWebHosting's official wiki
Jump to: navigation, search
up

Database tables

We want to provide you some information about the used database tables in PHPMyWebHosting. This should be useful while you are coding your contribution. When you take a look at the database you installed PHPMyWebHosting in you find the following tables:

Table customers

Layout 

 CREATE TABLE `customers` (
   `customer` varchar(100) NOT NULL default ,
   `groups` int(3) NOT NULL default '2',
   `email` varchar(100) NOT NULL default ,
   `realname` varchar(30) NOT NULL default ,
   `customer_number` varchar(30) NOT NULL default '0',
   `street` varchar(100) NOT NULL default ,
   `postcode` varchar(10) NOT NULL default ,
   `city` varchar(50) NOT NULL default ,
   `country` varchar(50) NOT NULL default ,
   `telephone` varchar(50) NOT NULL default ,
   `facsimile` varchar(50) NOT NULL default ,
   `password` varchar(100) NOT NULL default ,
   `language` varchar(30) NOT NULL default 'english',
   `webspace` int(10) NOT NULL default '0',
   `traffic` int(10) NOT NULL default '0',
   `domains` int(10) NOT NULL default '0',
   `subdomains` int(10) NOT NULL default '0',
   `emails` int(10) NOT NULL default '0',
   `forwards` int(10) NOT NULL default '0',
   `ftpaccounts` int(10) NOT NULL default '0',
   `dbases` int(10) NOT NULL default '0',
   `standard_subdomain` enum('Y','N') NOT NULL default 'N',
   `creator` varchar(100) NOT NULL default '0',
   PRIMARY KEY  (`customer`)
 ) TYPE=MyISAM;

Description

customer This is the login name for the customer. It must be unique
groups This is the group the customer is member of
email This is the email address of the customer
realname The customers real name
customer_number Your internal for accounting - unused
street, postcode, city,
country, telephone, facsimile 		self-explaining - unused
password This is the password of the customer stored as mysql's password()
package The id of the package the customer is assigned to
language Language choosen by the customer last time logged in
dbuser Database username of this customer
webspace Count of dusk space the customer may use
traffic Count of traffic the customer may use
domains Count of domains the customer may have
subdomains Count of subdomains the customer may create
emails Count of email addresses the customer may create
forwards Count of forwards the customer may create
ftpaccounts Count of ftp accounts the customer may create
dbases Count of databases the customer may create
standard_subdomain Enum: If subdomain "www" should be automatically created
creator A reference to the id of customers table who created this entry

Table dbase

Layout 

 CREATE TABLE `dbase` (
   `customer` varchar(250) NOT NULL default '0',
   `database_user` varchar(20) NOT NULL default ,
   `database_name` varchar(250) NOT NULL default ,
   PRIMARY KEY  (`customer`,`database_user`)
 ) TYPE=MyISAM;

Description

customer_id Reference to customer.id
database_user Name of the customer owning this database
database_name Name of the database created for this customer

Table domains

Layout 

 CREATE TABLE `domains` (
   `customer` varchar(20) NOT NULL default ,
   `domain` varchar(60) NOT NULL default ,
   `data` text NOT NULL,
   `top` enum('Y','N') NOT NULL default 'N',
   PRIMARY KEY  (`domain`)
 ) TYPE=MyISAM;

Description

customer : reference to customer.customer => Will be changed to customer.id later
domain domain name
data Apache vhost data. Only filled for subdomains => may be moved to table records later when powerdns is integrated
top Enum: Yes if this domain is the top level domain

Table forwardings

Layout 

 CREATE TABLE `forwardings` (
 `source` varchar(80) NOT NULL default ,
 `destination` text NOT NULL,
 PRIMARY KEY  (`source`)

) TYPE=MyISAM; Description

source Email address or catchall (@domain.tld)
destination comma seperated list of recipient email addresses or catchall

Table groups

Layout 

 CREATE TABLE `groups` (
   `id` int(10) NOT NULL auto_increment,
   `name` varchar(200) NOT NULL default ,
   PRIMARY KEY  (`id`)
 ) TYPE=MyISAM;

Description

id The group id for representation in PHPMyWebHosting
name The group name. Add as php constant.

Table language

Layout 

 CREATE TABLE `language` (
   `file` varchar(30) NOT NULL default ,
   `original` varchar(50) NOT NULL default ,
   `encoding` varchar(40) NOT NULL default ,
   `default_lang` enum('Y','N') NOT NULL default 'N',
   PRIMARY KEY  (`file`)
 ) TYPE=MyISAM;

Description

file The name of the language. See table translations.
original The name of the language in their own language.
encoding Encoding of the website for a representation according to the choosen language.
default_lang Defines the default language choosen by PMWH-Admin.

Table menu

Layout 

 CREATE TABLE `menu` (
   `name` varchar(255) NOT NULL default ,
   `link` varchar(255) NOT NULL default ,
   `groups` int(2) NOT NULL default '0',
   `box` int(10) NOT NULL default '0',
   `sort` int(10) NOT NULL default '0',
   `hide` enum('Y','N') NOT NULL default 'N',
   PRIMARY KEY  (`name`,`groups`,`box`)
 ) TYPE=MyISAM;

Description

name Name of the menu entry. Add as php constant.
link the short form link like "act=....&what=..."
groups Reference to groups.id. If all user should see this entry, add a "-1"
box Number of the box where this entry shows up
sort describes the sort order of the menu entries (in ascending order)
hide Enum: Decides if the menu entry is shown or not

Table pmwh

Layout 

 CREATE TABLE `pmwh` (
   `option_name` varchar(100) NOT NULL default ,
   `option_value` varchar(100) NOT NULL default ,
   `heading` varchar(50) NOT NULL default ,
   `description` varchar(100) NOT NULL default ,
   `type` varchar(30) NOT NULL default ,
   `sort_order` int(10) NOT NULL default '0',
   PRIMARY KEY  (`option_name`)
 ) TYPE=MyISAM;

Description

option_name Name of an option
option_value Value of an option
heading Heading for this option. Keep it short. Add as php constant.
description Description for this option. Keep it detailed. Add as php constant.
type Currently supported:
  • INTEGER: Draws an input type=text
  • TEXT: Draws an input type=text
  • TEMPLATE: Draws a select over template diretory
  • YESNO: Draws a select with yes-no option
sort_order

Table proftpd

Layout 

 CREATE TABLE `proftpd` (
   `domain` varchar(250) NOT NULL default ,
   `username` varchar(250) NOT NULL default ,
   `password` varchar(20) NOT NULL default ,
   `homedir` varchar(250) NOT NULL default ,
   `get_ftp_password` varchar(50) NOT NULL default ,
   `count` int(6) NOT NULL default '0',
   `bytes` bigint(20) NOT NULL default '0',
   `master` varchar(250) NOT NULL default ,
   UNIQUE KEY `username` (`username`)
 ) TYPE=MyISAM;

Description

domain The domain name of the ftp account
username The account's username
password The account's password in cleartext
homedir The directory where the account is chrooted to
get_ftp_password For ramdom generated passwords
count internal counter for proftpd and random passwords
bytes transferred traffic (up+down)
master reference to customers.customer - will be changed later to customer.id

Table sessions

Layout 

 CREATE TABLE `sessions` (
 `customer` varchar(100) NOT NULL default '0',
 `session` varchar(40) NOT NULL default ,
 `timestamp` timestamp(14) NOT NULL,
 `ip_addr` varchar(15) NOT NULL default ,
 PRIMARY KEY  (`customer`)

) TYPE=MyISAM; Description

customer customer login
session Session ID
timestamp generated with login (MySQL timestamp)
ip_addr IP address of the customer

Table transport

Layout 

 CREATE TABLE `transport` (
   `domain` varchar(128) NOT NULL default ,
   `destination` varchar(128) NOT NULL default ,
   PRIMARY KEY  (`domain`),
   UNIQUE KEY `domain` (`domain`)
 ) TYPE=MyISAM;

Description

domain name of the domain
destination transport for the domain (virtual:, local: or maildrop etc. See master.cf for details)

Table users

Layout 

CREATE TABLE `users` (
   `email` varchar(128) NOT NULL default ,
   `password` varchar(128) NOT NULL default ,
   `name` tinytext NOT NULL,
   `uid` int(11) NOT NULL default '99999',
   `gid` int(11) NOT NULL default '99999',
   `homedir` varchar(128) NOT NULL default ,
   `maildir` tinytext NOT NULL,
   `quota` tinytext NOT NULL,
   `postfix` enum('Y','N') NOT NULL default 'Y',
   PRIMARY KEY  (`email`),
   UNIQUE KEY `email` (`email`)
 ) TYPE=MyISAM;

Description

email Full emailadress
password Password for this mailbox in cleartext
name Name of the user of this Emailaddress (for squirrelmail)
uid UID of the vmail user
gid GID of the vmail user
homedir See /etc/postfix/main.cf: virtual_mailbox_base
maildir Maildir to the INBX of this account. Format: domain.tld/user@domain.tld/
quota Quota for this account. Works only if maildrop is activated. Format: xxx S, yyy C, where
  • xxx and yyy are integers
  • S is the size in Bytes
  • C is the quantity of emails

in the mailbox

postfix Decides, if this user may receive and send emails
Retrieved from "http://pmwh.ckvsoft.at/index.php?title=Develdoc:Tables&oldid=26011"