7.2. Database structure

Simple database layout is provided below. For more details (data types, fields restrictions and default values) please refer to lms.mysql, lms.pgsql in doc/ directory.

7.2.1. LMS users ('users')

id - serial number
login - login
name - first and last name
email - user's email address
phone - user's phone number
position - user's position name
rights - binary access rights
hosts - list of hosts allowed to login
passwd - password to login
ntype - supported notofication types
lastlogindate - date of last login
lastloginip - IP of last login
failedlogindate - date of last failed login attempt
failedloginip - IP of last failed login attempt
deleted - if is deleted boolean (0/1)

7.2.2. Customers ('customers')

id - serial number
lastname - last/company name
name - first name
divisionid - identifier of company/division
status - customer status (3-connected, 2-awaiting, 1-prospect)
type - legal personality (0-private person, 1-legal entity)
email - email address
pin - pin number (for authentication)
address - street address (street, apartment, flat, etc)
zip - zip code
city - location (city)
countryid - country identifier
post_address - correspondence address - street, apartment, flat, etc
post_zip - correspondence address - zip code
post_city - correspondence address - location (city)
post_countryid - correspondence address - country identifier
ten - tax exempt number
ssn - social security number
regon - business registration number
rbe - register of business entities
icn - identity card number
info - additional information
notes - notes
creationdate - record creation date
moddate - record modification date
creatorid - serial of LMS user who created this record
modid - serial of LMS user last modification this record
deleted - if is deleted from database boolean (0/1)
message - message to be displayed if warnings enabled
cutoffstop - date to which customers cutting off is disabled
paytime - invoices deadline in days
paytype - invoices payment type identifier (see documents table)
einvoice - enables e-invoices
invoicenotice - enables invoices delivery via e-mail
mailingnotice - enables messages delivery via e-mail or sms

7.2.3. Customer groups ('customergroups')

id - serial number
name - group name
description - group description

7.2.4. Customer groups - assignments ('customerassignments')

id - serial number
customergroupid - group serial number
customerid - customer serial number

7.2.5. Customer groups - users access ('excludedgroups')

id - serial number
userid - user serial number
customergroupid - group serial number

7.2.6. Networks ('networks')

id - serial number
name - network name
address - IP address
mask - network mask
interface - network interface (eg. eth1)
gateway - gateway IP address
dns - IP address of dns server
dns2 - IP address of secondary dns server
domain - domain of the network
wins - WINS server address
dhcpstart - first address of dynamic DHCP range
dhcpend - last address of dynamic DHCP range
disabled - disabled/enabled flag (1/0)
notes - additional notes

7.2.7. Network devices ('netdevices')

id - serial number
name - name
location - physical location
description - device summary
producer - manufacturer's name
model - model number
serialnumber - products serial number (not DB identifier)
ports - number of connections available
purchasetime - purchase date
guaranteeperiod - period in months (NULL - lifetime)
shortname - shortname (radius)
nastype - NAS type identifier (radius)
clients - max. number of clients (radius)
secret - password (radius)
community - SNMP community
channelid - STM channel identifier (ewx_channels table)

7.2.8. Network connections ('netlinks')

id - serial number
src - connection's beginning
dst - connection's end
type - type of connection (0-cable, 1-wireless)
srcport - begin's port number
dstport - end's port number

7.2.9. Computers and IP addresses ('nodes')

id - serial number
name - device name
ipaddr - IP address
passwd - computer password for radius/pppoe login
ownerid - serial number of the owner ('0' if network device)
creationdate - creation timestamp
moddate - last modification timestamp
creatorid - creator's serial number
modid - modifier's serial number
netdev - serial number of connected network device
linktype - type of connection (0-cable, 1-wireless)
port - port number in device
access - connected/disconnected (cutoff) (1/0)
warning - should be warned with administration message? (1/0)
chkmac - enable/disable MAC checking? (1/0)
halfduplex - half/full duplex mode (0/1)
lastonline - last network activity timestamp
info - additional information
location_address - address - street, apartment, flat, etc
location_zip - address - zip code
location_city - address - location (city)
nas - NAS flag (1/0)

7.2.10. MAC addresses ('macs')

id - serial number
mac - MAC address
nodeid - IP address identifier (nodes table)

7.2.11. Node groups ('nodegroups')

id - serial number
name - group name
prio - group order
description - group description

7.2.12. Node groups - assignments ('nodegroupassignments')

id - serial number
nodegroupid - group serial number
nodeid - node serial number

7.2.13. NAS Device Types ('nastypes')

id - serial number
name - type name

7.2.14. Financial operations ('cash')

id - serial number
time - timestamp of operation
type - type of operation (1-payment, 0-liability)
userid - LMS user id
value - amount in dollars
taxid - tax rate identifier
customerid - customer's serial number ('0' - does not apply)
docid - serial number for document (e.g. invoice) related to this operation
itemid - document item identifier
importid - import identifier
sourceid - import source identifier
comment - description of operation

7.2.15. Import of financial operations ('cashimport')

id - serial number
date - timestamp of operation
customer - customer data
value - amount
taxid - tax rate identifier
customerid - customer's serial number
description - operation description
hash - unique operation identifier
sourceid - import source identifier
sourcefileid - import file identifier
closed - yes (1), if operation was moved to cash table

7.2.16. Cash import sources ('cashsources')

id - serial number
name - name
description - additional information

7.2.17. Cash import packages ('sourcefiles')

id - serial number
name - filename
idate - date/time of import
userid - user identifier

7.2.18. Subscription fees ('tariffs')

id - serial number
name - subscription name
type - subscription type (see lib/definitions.php)
value - amount
taxid - tax rate identifier
period - payment period (for specified tariff value)
prodid - product/service classification number
uprate - upload warranty
upceil - upload boundary
downrate - download warranty
downceil - download boundary
climit - limit of concurrent connections
plimit - limit of packets per second
uprate_n - upload warranty at night
upceil_n - upload boundary at night
downrate_n - download warranty at night
downceil_n - download boundary at night
climit_n - limit of concurrent connections at night
plimit_n - limit of packets per second at night
dlimit - limit of data per time unit
domain_limit - limit of domains
alias_limit - limit of aliases
sh_limit - limit of shell accounts
mail_limit - limit of mail accounts
www_limit - limit of www accounts
ftp_limit - limit of ftp_accounts
sql_limit - limit of sql accounts
quota_sh_limit - quota limit of shell account
quota_mail_limit - quota limit of mail account
quota_wwww_limit - quota limit of www account
quota_ftp_limit - quota limit of ftp account
quota_sql_limit - quota limit of sql account
description - description for subscription

7.2.19. Promotions ('promotions')

id - serial number
name - promotion name
description - additional information
disabled - status

7.2.20. Promotion Schemas ('promotionschemas')

id - serial number
name - schema name
description - additional information
promotionid - promotion identifier
data - schema periods definition
disabled - status
continuation - contract continuation option
ctariffid - additional continuation tariff identifier

7.2.21. Schema-To-Tariff assignments ('promotionassignments')

id - serial number
promotionschemaid - schema identifier
tariffid - subscription identifier
data - schema values definition

7.2.22. Custom liabilities ('liabilities')

id - serial number
name - liability name/description
value - amount
taxid - tax rate identifier
prodid - product/service classification number

7.2.23. Solid payments ('payments')

id - serial number
name - name
value - amount
creditor - creditor name
period - interval of operation: daily/weekly/monthly/quarterly/annually (1/2/3/4/5)
at - pay day
description - description for payment

7.2.24. Financial assignments ('assignments')

id - serial number
tariffid - subscription serial number
liabilityid - liability serial number
customerid - customer serial number
period - interval of operation: daily/weekly/monthly/quarterly/annually (1/2/3/4/5)
at - pay day
datefrom - start date for assignment
dateto - end date for assignment
invoice - invoice writeout? (1 - yes, 0 - no)
discount - discount percentage
suspended - is this payment suspended? (1 - yes, 0 - no)
settlement - do deficient period settlement? (1 - yes, 0 - no)
paytype - invoice payment type identifier
numberplanid - numbering plan identifier

7.2.25. Nodes-tariffs assignments ('nodeassignments')

id - serial number
assignmentid - financial assignment serial number
nodeid - node serial number

7.2.26. Tax rates ('taxes')

id - serial number
value - tax value
taxed - "is taxed" flag
label - rate label
validfrom - binding period start
validto - binding period end

7.2.27. Documents numbering plans ('numberplans')

id - serial number
template - number template (pattern)
period - numbering time span: day/week/month/quarter/year
doctype - document type
isdefault - '1' if this plan is default for respondent doctype, else '0'

7.2.28. Numbering plans to divisions assignments ('numberplanassignments')

id - serial number
planid - numbering plan identifier
divisionid - division identifier

7.2.29. Cash registries ('cashregs')

id - serial number
name - registry name
description - additional description
in_numberplanid - numbering plan identifier for cash-in receipts
out_numberplanid - numbering plan identifier for cash-out receipts
disabled - summary disabling (0/1)

7.2.30. Cash registries - access rights ('cashrights')

id - serial number
regid - registry serial number
userid - user serial number
rights - (1-read, 2-write, 3-advanced)

7.2.31. Cash registries - cash history ('cashreglog')

id - serial number
regid - registry serial number
userid - user serial number
time - entry timestamp
value - real cash state value
snapshot - cash state value
description - additional information

7.2.32. Documents: invoices, receipts, contracts, etc. ('documents')

id - serial number
number - document number (%N)
extnumber - additional (extended) number part (%I)
numberplanid - numbering plan identifier
type - document type (1 - invoice, 2 - cash receipt)
cdate - date of write out
paytime - deadline in days
paytype - payment type (1-cash, 2-transfer, 3-transfer/cash, 4-card, 5-compensation, 6-barter, 7-contract)
customerid - customer (buyer) serial number
userid - user serial number
divisionid - identifier of company/division
name - name of customer
address - address of customer
ssn - SSN of customer
ten - Tax Exempt Number of customer
zip - zip code of customer
city - location of customer
countryid - country identifier
closed - is document (invoice) closed (accounted)? (0/1)
reference - document ID reference
reason - e.g. invoice note reason

7.2.33. Non-financial documents contents ('documentcontents')

docid - document serial number
title - document title
fromdate - start of binding period
todate - end of binding period
filename - document file name
contenttype - file type
md5sum - file md5 sum
description - additional information

7.2.34. Invoices ('invoicecontents')

docid - invoice serial number
itemid - invoice item identifier
value - amount
discount - discount percentage
taxid - tax rate identifier
prodid - product/service classification number
content - used unit (usually 'pc.')
count - unit count
description - description for invoice
tariffid - subscription serial number

7.2.35. Debit notes ('debitnotecontents')

docid - debit note serial number
itemid - debit note item identifier
value - amount
description - description for the note item

7.2.36. Cash Receipts ('receiptcontents')

docid - receipt serial number
itemid - receipt item identifier
regid - registry serial number
value - amount
description - description for receipt item

7.2.37. Documents - access rights ('docrights')

userid - user identifier
doctype - document type id (see lib/definitions.php)
rights - (1-read, 2-create, 3-confirm, 4-edit, 5-delete)

7.2.38. Internet Messengers ('imessengers')

id - serial number
customerid - customer serial number
uid - messenger user name/identifier
type - messenger type (0-gadu-gadu, 1-yahoo, 2-skype)

7.2.39. Customers contacts ('customer contacts')

id - serial number
customerid - customer serial number
phone - phone number
name - contact name/description
type - contact type (sum of: 1-mobile, 2-fax)

7.2.40. Domains ('domains')

id - serial number
name - domain name
description - comments
type - DNS type ('MASTER', 'SLAVE', 'NATIVE')
master - master DNS server
account - email address of DNS administrator
last_check - timestamp
notified_serial - timestamp

7.2.41. DNS Records ('records')

id - serial number
domain_id - domain serial number
name - name
type - record type (MX, SOA, A, AAAA, etc.)
content - data
ttl - TTL
prio - priority
change_date - last change timestamp

7.2.42. Accounts ('passwd')

id - serial number
ownerid - customer serial number (0 - "system" account)
login - login name
password - password encrypted with crypt()
realname - additional name
lastlogin - last login date
uid - account system UID (usually ownerid+200)
home - account home directory
type - account type (binary sum: 1-shell, 2-email, 4-www, 8-ftp)
expdate - account expire date
domainid - domain serial number
createtime - account creation date
quota_sh - shell space limits
quota_mail - email space limits
quota_www - www space limits
quota_ftp - ftp space limits
quota_sql - sql database space limits
mail_forward - account for mail forwarding
mail_bcc - account for blind carbon copy mail
description - additional information

7.2.43. Aliases ('aliases')

id - serial number
login - account name (without domain)
domainid - account serial number

7.2.44. Alias-to-account assignments ('aliasassignments')

id - serial number
aliasid - alias serial number
accountid - account serial number
mail_forward - forward address

7.2.45. VoIP Accounts ('voipaccounts')

id - serial number
ownerid - customer identifier
login - login
passwd - password
phone - phone number
creationdate - date of account creation
moddate - date of last account modification
creatorid - creator (user) identifier
modid - last change (user) identifier

7.2.46. Bandwidth consumption statistics ('stats')

nodeid - node serial number
dt - timestamp
upload - number of bytes sent
download - number of bytes received

7.2.47. Helpdesk - Request Tracking ('rtqueues')

id - serial number
name - queue name
email - email account for the queue
description - main description for the queue

7.2.48. Helpdesk - Request Tracking - continued... ('rttickets')

id - serial number
queueid - queue serial number
requestor - reporter name and email
customerid - customer serial number (if reported by customer)
subject - ticket name)
state - status (0-new, 1-open, 2-resolved, 3-dead)
cause - request cause (0-unknown, 1-customer, 2-company)
owner - user serial number (ticket's owner)
creatorid - user serial number (ticket's creator)
createtime - timestamp of report

7.2.49. Helpdesk - Request Tracking - continued... ('rtmessages')

id - serial number
ticketid - ticket serial number
userid - LMS user serial number (if ticket sender)
customerid - customer serial number (if ticket sender)
mailfrom - sender email
subject - message subject
messageid - Message-ID message header
inreplyto - thread serial number (if threaded)
replyto - Reply-To message header
headers - all message headers
body - content of message body
createtime - date of creation/send/delivery

7.2.50. Helpdesk - Request Tracking - continued... ('rtattachments')

messageid - message serial number
filename - name of file attachment
contenttype - type of file

7.2.51. Helpdesk - Request Tracking - continued... ('rtnotes')

id - serial number
ticketid - ticket serial number
userid - LMS user serial number
body - content of note
createtime - date of creation

7.2.52. Helpdesk - Request Tracking - continued... ('rtrights')

id - serial number
queueid - queue serial number
userid - LMS user serial number
rights - permissions (1-read, 2-write)

7.2.53. LMS-UI Online Configuration ('uiconfig')

id - serial number
section - config section name
var - config variable name
value - config variable value
description - option description or comment
disabled - is option disabled? (0-active, 1-disabled/default)

7.2.54. Timetable - events ('events')

id - identifier
title - title
description - info
note - note
date - event date
begintime - beginning of event
endtime - end of event
userid - event creator ID
customerid - customer ID
private - status (private/public)
closed - is event closed? (1-yes/0-no)

7.2.55. Timetable - assignments ('eventassignments')

eventid - event identifier
userid - user identifier

7.2.56. Sessions ('sessions')

id - session identifier
ctime - create time
mtime - last modification time
atime - last access time
vdata - verification data
content - data

7.2.57. Hosts ('hosts')

id - identifier
name - host name
description - additional information
lastreload - last reload date
reload - reload order

7.2.58. Daemon configuration - instances ('daemoninstances')

id - identifier
name - instance name
hostid - host identifier
module - module file path and name
crontab - time of reload
priority - reload priority
description - additional information
disabled - status (enabled/disabled)

7.2.59. Daemon configuration - options ('daemonconfig')

id - identifier
instanceid - instance identifier
var - option name
value - option value
description - additional information
disabled - status (enabled/disabled)

7.2.60. States ('states')

id - identifier
name - state name
description - additional information

7.2.61. Zip codes ('zipcodes')

id - identifier
zip - zip code
stateid - state identifier

7.2.62. Countries ('countries')

id - identifier
name - country name

7.2.63. Companies/Divisions ('divisions')

id - identifier
shortname - division short name
name - division long name
address - address
zip - zip code
city - city
countryid - country identifier
ten - tax exempt number
regon - business registration number
account - bank account or mass payments account prefix
description - additional information
status - lock status (1/0)
inv_header - invoice header
inv_footer - invoice footer
inv_author - invoice default author
inv_cplace - invoice creation place
inv_paytime - invoice deadline
inv_paytype - invoice payment type (see documents table)

7.2.64. Messages - list ('messages')

id - serial number
subject - message subject
body - message contents
cdate - creation date
type - type (1-email, 2-sms)
userid - sender (user) identifier
sender - e-mail 'From' header

7.2.65. Messages - items ('messageitems')

id - serial number
messageid - message identifier
customerid - customer identifier
destination - destination (phone num./e-mail)
lastdate - last meaning date
status - sending status (see lib/definitions.php)
error - error message

7.2.66. Database information ('dbinfo')

keytype - type
keyvalue - value