Tutos Homepage / Docs / Database Database Documentation

OUTDATED !!

information on the current database structure is here http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/tutos/tutos/php/db/table.pinc?rev=HEAD&sortby=date&content-type=text/vnd.viewcvs-markup">http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/tutos/tutos/php/db/table.pinc?rev=HEAD&sortby=date&content-type=text/vnd.viewcvs-markup

This is a overview of all database tables that build a TUTOS installation.
These tables are created by running the scheme.sh script at installation time.
So the most actual description could be found by looking at table.pinc.

TABLE: people
id int4 primary keygenerated by sequencer
adr_id int4 not null reference to addresses
login varchar(20) unique login name
last_seen datetime not null last login time
last_host varchar(20)   last login host
admin int4 not null 0 == not admin
1 == admin
pw varchar(20) not null encrypted password
prefteam int4 default 1 team to display in calendar sheet
1 ==
or reference to teams
overlib int4 default 1 1 == yes
0 == no
lang varchar(5)   language
'auto' == use http accept
tz varchar(30)   timezone name
the list of possible timezones is
stored in [tutosdir]/php/config.pinc
theme varchar(10) default 'tutos'theme name (see html dir)
This table has an entry for every TUTOS user.

TABLE: addresses
id int4 primary keygenerated by sequencer
title varchar(10)   title
f_name varchar(30)   first name
m_name varchar(3)   middle initial
l_name varchar(30) not null last name
birthday date   date of birth
creator int4 not null the id of the creator
reference to addresses
creation datetime not null date of creation

TABLE: location
id int4 primary keygenerated by sequencer
category int4 not null 1 == private 2 == buisness
c_id int4   reference to companies
d_id int4   reference to department
phone_1 varchar(30)   phone number
phone_2 varchar(30)   phone number
fax_1 varchar(30)   fax number
email_1 varchar(30)   email
email_2 varchar(30)   email
street1 varchar(30)   street
street2 varchar(30)   street
city varchar(30)   city name
zip varchar(10)   city zip code
state varchar(30)   state in country
country varchar(3)   country code

TABLE: adrloc
adr_id int4 not nullreference to addresses
loc_id int4 not nullreferemce to location
name varchar(30)not nullname of location
category int4   1 == private 2 == buisness

TABLE: teams
id int4 primary keygenerated by sequencer
name varchar(30)   name of team
owner int4 not null reference to addresses
creation datetime not null date of creation
Teams are collections of TUTOS users (See people and adrteam)

TABLE: adrteam
adr_id int4 not null reference to addresses
team_id int4 not null reference to teams

TABLE: calendar
id int4 primary keygenerated by sequencer
a_start datetime not null Start of event
a_end datetime not null End of event
description varchar(100) not null what happens
trace int4 not null 0 == no voting
1 == vote
mod_allow int4 not null 0 == everybody
1 == participants
2 == owner
outside int4 default 0 0 == local
1 == outside
remember int4   0 == no
else == seconds before a_start
remembered int4   0 == no
1 == yes
visitor int4   the id of the visitor
refernce to addresses
product int4   appointment is related to this product
refernce to products
repeat int4 default 0 0 == appointment is a not repeating appointment
1 == every week on r_arg
2 == every month on day r_arg
3 == every year on day/month r_arg
4 == every day r_arg
r_arg varchar(5)   additional args for repeat
r_ignore int4 default 0 0 == respect end date
1 == ignore end date
creator int4 not null the id of the creator
reference to addresses
creation datetime not null date of creation

TABLE: participants
adr_id int4  refernce to addresses
app_id int4  refernce to calendar
state int4 default 01 == yes
2 == no
0 == not decided
value checked by constraint

TABLE: products
id int4 primary keygenerated by sequencer
name varchar(40)   product/project name
version varchar(10)   version of product/project
description text   description of product/project
desc1 varchar(50)   description of product/project
desc2 varchar(50)   description of product/project
state int4   state of product/project
0 == prestate
1 == ongoing
2 == finished
3 == cancelled
path varchar(100)   a URL-path to more information
price float   the price a customer has to pay
currency varchar(4)   the currency of price
probability float   the probability to get a order if in prestate
creator int4 not null the id of the creator
reference to addresses
creation datetime not null date of creation

TABLE: projectroles
adr_id int4   person/team that got a role
pro_id int4   product/project
role_id int4   what role ?
1 == Manager
2 == commercial manager
3 == second manager
4 == sales
5 == developer
6 == customer
This table is new after the 20001016 release. Before there where manager,manager2 and seller fileds in the product table

TABLE: tasks
id int4 primary keygenerated by sequencer
name varchar(40)   task name
description text   description of task
status int4   status
volume int4   work volume
volume_done int4   work volume already done
p_id int4   the id of the parent object
reference to products
or to tasks
worker int4   the id of the person team to do this work
reference to addresses or teams
r_start datetime   real start
r_end datetime   real end
s_start datetime not null scheduled start
s_end datetime not null scheduled end
creator int4 not null the id of the creator
reference to addresses
creation datetime not null date of creation

TABLE: installations
id int4 primary keygenerated by sequencer
customer_id int4   person/team/company
installer_id int4   person/team
seller_id int4   person/team
product_id int4 not null product/project
money float   the price payed by customer
currency varchar(4)   the currency of money
instdate datetime   date of installation
salesdate datetime   date of sale

TABLE: bugs
id int4 primary keygenerated by sequencer
product_id int4   the id of the buggy product
reference to products
or reference to installation
short varchar(40) not null short bug description
description text   description and history of bug
state int4 not null state of bug
class int4 not null class of bug
solver int4   the id of the (assigned) solver
reference to addresses
solvedate datetime not null date of first solve/close
creator int4 not null the id of the creator
reference to addresses
creation datetime not null date of creation

TABLE: companies
id int4 primary keygenerated by sequencer
name varchar(30)   company name
creation datetime not null date of creation

TABLE: departments
id int4 primary keygenerated by sequencer
c_id int4   reference to companies
name varchar(30)   company name
creation datetime not null date of creation

TABLE: notes
id int4 primary keygenerated by sequencer
link_id int4   reference to another objects primary key
name varchar(30)   name in TUTOS
note text   long text
creator int4 not null the id of the creator
reference to addresses
creation datetime not null date of creation

TABLE: urls
id int4 primary keygenerated by sequencer
link_id int4   reference to another objects primary key
url varchar(200)   URL
name varchar(200)   name in TUTOS

TABLE: files
id int4 primary keygenerated by sequencer
link_id int4   reference to another objects primary key
name varchar(200)   name of file in TUTOS
filename varchar(200)   original name of file
loc varchar(200)   local file location
type varchar(40)   filetype MIME
f_size int4   filesize
version varchar(10)   version
locked int4   0 == unlocked
else lockers id
vsys int4   0 == none
2 == tutos
3 == RCS
logtxt text   log
author int4   link to addresses
creator int4 not null the id of the creator
reference to addresses

TABLE: mailboxes
id int4 primary keygenerated by sequencer
link_id int4   reference to another objects primary key
host varchar(40)   host where to find the mailbox
type varchar(10)   imap or pop
uname varchar(20)   username for account
pw varchar(20)   password for account
path varchar(60)   path of imap mailbox
email varchar(60)   emailaddress to reach this box

TABLE: timetrack
id int4 not null reference to another objects primary key
adr_id int4   reference to a user
link_id int4   reference to the object above user worked on
volume float   effort in hours
descriptionvarchar(60)   what was done
v_time datetime   when was it done
creation datetime   time of booking

TABLE: watchlist
obj_id int4 not null reference to another objects primary key
adr_id int4 not null reference to a user

TABLE: history
obj_id int4   reference to another objects primary key
adr_id int4   reference to a user
m_time datetime   time of modification
m_field varchar(60)   modified field
val_old varchar(60)   old value in field
val_new varchar(60)   new value in field
This table is new after the 20001016 release.