View source for Develdoc:Tables

<div align="right">[[Documentation|up]]</div>

===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====
<u>'''Layout'''</u>
  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;

<u>'''Description'''</u>
{| width="100%"
|-
| 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,<br>country, telephone, facsimile 
| valign="top" | 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====
<u>'''Layout'''</u>
  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;
<u>'''Description'''</u>
{| width="100%"
|-
| 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====
<u>'''Layout'''</u>
  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;
<u>'''Description'''</u>
{| width="100%"
|-
| 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====
<u>'''Layout'''</u>
  CREATE TABLE `forwardings` (
  `source` varchar(80) NOT NULL default '',
  `destination` text NOT NULL,
  PRIMARY KEY  (`source`)
) TYPE=MyISAM;
<u>'''Description'''</u>
{| width="100%"
|-
| source 
| Email address or catchall (@domain.tld)
|-
| destination
| comma seperated list of recipient email addresses or catchall
|}

-----

====Table groups====
<u>'''Layout'''</u>
  CREATE TABLE `groups` (
    `id` int(10) NOT NULL auto_increment,
    `name` varchar(200) NOT NULL default '',
    PRIMARY KEY  (`id`)
  ) TYPE=MyISAM;
<u>'''Description'''</u>
{| width="100%"
|-
| id 
| The group id for representation in PHPMyWebHosting
|-
| name 
| The group name. Add as php constant.
|}

-----

====Table language====
<u>'''Layout'''</u>
  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;
<u>'''Description'''</u>
{| width="100%"
|-
| 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====
<u>'''Layout'''</u>
  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;
<u>'''Description'''</u>
{| width="100%"
|-
| 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====
<u>'''Layout'''</u>
  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;
<u>'''Description'''</u>
{| width="100%"
|-
| 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.
|-
| valign="top" | 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====
<u>'''Layout'''</u>
  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;
<u>'''Description'''</u>
{| width="100%"
|-
| 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====
<u>'''Layout'''</u>
  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;
<u>'''Description'''</u>
{| width="100%"
|-
| customer
| customer login
|-
| session
| Session ID
|-
| timestamp
| generated with login (MySQL timestamp)
|-
| ip_addr
| IP address of the customer
|}

-----

====Table transport====
<u>'''Layout'''</u>
  CREATE TABLE `transport` (
    `domain` varchar(128) NOT NULL default '',
    `destination` varchar(128) NOT NULL default '',
    PRIMARY KEY  (`domain`),
    UNIQUE KEY `domain` (`domain`)
  ) TYPE=MyISAM;
<u>'''Description'''</u>
{| width="100%"
|-
| domain
| name of the domain
|-
| destination
| transport for the domain (virtual:, local: or maildrop etc. See master.cf for details)
|}

-----

====Table users====
<u>'''Layout'''</u>
 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;

<u>'''Description'''</u>
{| width="100%"
|-
| 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
|}