Database Schema

Introduction
Tasks
Users
Files
Users
Usergroups
Taskgroups
Forum
Logins
Seen
Contacts
Files
Login Attempts
Site Name

Introduction

The individual database tables are listed below, with a brief description of each field.

All of the database table and fields are in the UTF-8 character set. Both the Postgresql and MySQL clients are capable of converting the input and output character sets 'on the fly'.

Individual Database Tables

Tasks

id

Auto incremented task id number (Integer)

parent

The parent task id number for the current entry. All tasks have a parent task id, which can be either a parent task or a project. Projects have no parent id, and default to 0. (Integer)

name

The name of the project / task. (Text)

text

Description field as entered by the user. (Text)

created

Date & time that task was created.

edited

Date & time that task last edited. Defaults to 0.

owner

User id of the owner. See the users table. (Integer)

creator

User id of the creator. See the users table. (Integer)

projectid

Task id of the overall project. For projects this is the same as the task id; for tasks this is always the project's task id. (Integer)

deadline

Date & time of deadline. Time is always 2.00 am of the deadline day, to avoid problems when daylight savings changes occur.

priority

Priority of task from 0 to 4. Highest priority is 4. (Integer)

status

Status of the task. (String)

Accepted strings are: 'created' (new), 'notactive' (planned), 'active', 'cantcomplete' (On Hold), 'done', 'nolimit'.

taskgroupid
Usergroupid

Taskgroup and/or Usergroup id that task belongs to, if any. Defaults to 0. See the usergroups and taskgroups tables. (Integer)

lastforumpost
Lastfileupload

Date & time of last forum post and/or last file upload for this task, if any. Defaults to 0.

globalaccess

All users allowed to view? This is either 't' or 'f' to match Postgresql booleans.

groupaccess

Usergroup is allowed to edit? This is either 't' or 'f' to match Postgresql booleans.

completed

Indicates whether project is completed because all tasks are completed. Not used on task records. (0 or 1)

completion_time

Date & time of completion. Only used on project records.

archive

Indicates whether project is archived. Not used on task records. (0 or 1)

sequence

Integer that increments with each edit. Needed for iCalendar to show that task has been changed. (Integer)

Users

id

Auto incremented user id number (Integer)

name
fullname
password
email

Login, name, password and email respectively. The password is an md5 hash. (Text)

admin

User is an administrator? This is either 't' or 'f' to match Postgresql booleans.

private

User is a private user? (0 or 1)

guest

User is a guest? (0 or 1)

deleted

User has been deleted temporarily? This is either 't' or 'f' to match Postgresql booleans.

locale

The locale of the user. Only used in the UTF-8 versions. Defaults to 'en'.

Usergroups

id

Auto incremented usergroup id number. (Integer)

name
description

Usergroup name and description. (Text)

private

Usergroup is a private? (0 or 1)

Taskgroups

id

Auto incremented taskgroup id number (Integer)

name
description

Taskgroup name and description respectively. (Text)

Forum

id

Auto incremented forum id number (Integer)

parent

Forum id of parent post, if any.

taskid

Task id of related task. See the tasks table. (Integer)

posted
edited

Date & time of posting and editing.

text

The message. (Text)

userid

The user id of the user that posted the message. See the users table. (Integer)

usergroupid

The usergroup id of the usergroup that posted the message. If a usergroup id is set the post will appear in the privated usergroup forums. See the usergroups table. (Integer)

sequence

Integer that increments with each edit. Needed for iCalendar to show that post has been changed. (Integer)

Logins

id

Auto incremented login id number (Integer)

user_id

The users id. See the users table. (Integer)

session_key

Random generated session key valid for each session.

ip

Browsers IP address as detected by PHP.

lastaccess

Time of last action.

token

Random generated session key generated for one action only.

Note: The user's previous login records are all deleted each time they login.

Seen

taskid

The task id. See the tasks table. (Integer)

userid

The users id. See the users table. (Integer)

time

Date & time that the userid has seen the taskid.

Contacts

id

Auto incremented contact id number (Integer)

firstname
lastname
company
tel_home
gsm
fax
tel_business
address
postal
city
email

Contact details (Text)

notes

Notes (Text)

added_by

The user id of the user that posted the contact. See the users table. (Integer)

date

Date & time contact was added.

taskid

The task id related to the contact, if any. See the tasks table. Defaults to 0. (Integer)

Contacts Tasks

Note: This table is present in the schema, but is not used.

Files

id

Auto incremented file id number (Integer)

fileid

Same value as id (above).

filename

Name of uploaded file (Text)

size

Size of file in bytes (Integer)

mime

The mimetype of the file as reported by the browser. (Text)

description

User entered description (Text)

uploaded

Date & time the file was uploaded.

uploader

The user id of the user that uploaded the file. See the users table. (Integer)

taskid

The task id related to the file. See the tasks table. (Integer)

Login Attempt

name

Name used as login. (Text)

ip

Browsers IP address as detected by PHP.

last_attempt

Date & time of last login attempt with this name.

After four unsuccessful login attempts on a username, the code locks out further logins on this username for 10 minutes.

Note: This record is cleared by the program code either, after a successful login, or at any other succesful login after 20 minutes.

Site Name

manager_name
abbr_manager_name

Manager name and abbreviated site name respectively, as entered in the set up program.