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 and lms.sqlite in doc/ directory.

7.2.1. LMS users ('admins')

id - serial number
login - login
name - first and last name
email - user's email address
rights - binary access rights
hosts - list of hosts allowed to login
passwd - password to login
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 ('users')

id - serial number
lastname - last/company name
name - first name
status - customer status (3-connected, 2-awaiting, 1-prospect)
email - email address
phone1 - phone
phone2 - phone
phone3 - phone
gguin - internet (gadu-gadu) messenger serial number
pin - pin number (for authentication)
address - street address (street, apartment, flat, etc)
zip - zip code
city - location (city)
nip - tax exempt number
pesel - social security number
info - additional informations
serviceaddr - correspondence address
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

7.2.3. Customer groups ('usergroups')

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

7.2.4. Customer groups - continued... ('userassignments')

id - serial number
usergroupid - group serial number
userid - customer serial number

7.2.5. 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

7.2.6. Computers and network devices ('nodes')

id - serial number
name - device name
mac - MAC address
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)
access - connected/disconnected (cutoff) (1/0)
warning - should be warned with administration message? (1/0)
lastonline - last network activity timestamp
info - additional information

7.2.7. Network devices - continued... ('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

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)

7.2.9. Financial operations ('cash')

id - serial number
time - timestamp of operation
adminid - LMS user id
type - type of operation (income, expenditure, payment, debt)
value - amount in dollars
taxvalue - tax rate in percent
userid - customer's serial number ('0' - does not apply)
invoiceid - serial number for invoice related to this operation
itemid - invoice item identifier
comment - description of operation

7.2.10. Import of financial operations ('cashimport')

id - serial number
date - timestamp of operation
customer - customer data
value - amount
taxvalue - tax rate in percent
customerid - customer's serial number
description - operation description
hash - unique operation identifier
closed - yes (1), if operation was moved to cash table

7.2.11. Subscription fees ('tariffs')

id - serial number
name - subscription name
value - amount
taxvalue - tax rate in percent
pkwiu - 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
description - description for subscription

7.2.12. Solid payments ('payments')

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

7.2.13. Financial assignments ('assignments')

id - serial number
tariffid - subscription serial number
userid - customer serial number
period - interval of operation: weekly/monthly/quarterly/annually (0/1/2/3)
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)

7.2.14. Invoices ('invoices')

id - serial number
number - number in year
cdate - date of write out
paytime - deadline in days
paytype - payment method (cash/transfer/etc.)
customerid - customer (buyer) serial number
name - name of buyer
address - address of buyer
nip - SSN of buyer
pesel - ID (passport number) of buyer
zip - zip code of buyer
phone - phone of buyer
city - location of buyer
finished - is this invoice complete? (0/1)

7.2.15. Invoices - continued... ('invoicecontents')

invoiceid - invoice serial number
itemid - invoice item identifier
value - amount
taxvalue - tax rate in percent
pkwiu - product/service classification number
content - used unit (usually 'pc.')
count - unit count
description - description for invoice
tariffid - subscription serial number

7.2.16. 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

7.2.17. Domains ('domains')

id - serial number
name - domain name
description - comments

7.2.18. Aliases ('aliases')

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

7.2.19. History ('timestamps')

time - last modified timestamp
tablename - name of modified table

7.2.20. Bandwidth consumption statistics ('stats')

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

7.2.21. Helpdesk - Request Tracking ('rtqueues')

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

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

id - serial number
queueid - queue serial number
requestor - reporter name and email
userid - customer serial number (if reported by customer)
subject - ticket name)
state - status (0-new, 1-open, 2-resolved, 3-dead)
owner - LMS user serial number (ticket owner)
createtime - timestamp of report

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

id - serial number
ticketid - ticket serial number
adminid - LMS user serial number (if ticket sender)
userid - 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.24. Helpdesk - Request Tracking - continued... ('rtattachments')

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

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

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

7.2.26. 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.27. Timetable - events ('events')

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

7.2.28. Timetable - assignments ('eventassignments')

eventid - event identifier
adminid - user identifier

7.2.29. Sessions ('sessions')

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

7.2.30. Daemon configuration - hosts ('daemonhosts')

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

7.2.31. 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 informations
disabled - status (enabled/disabled)

7.2.32. Daemon configuration - options ('daemonconfig')

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

7.2.33. Database information ('dbinfo')

keytype - type
keyvalue - value