MySQL

MySQL

This page is dedicated to the documentation of how we have encoded oneye user data into a mySQL database.

Any suggestions, concerns, problems or other should be brought to light on the forums, specifically in this thread. (To those used to MediaWiki-type wiki, think of the forum page linked as the discussion page for this wiki page).

The Codebase

As with all current development on oneye, the current code for the implementation is publicly accessible on GitHub. Specifically, here: https://github.com/s0600204/oneye/tree/userConfig

The Database

The database format is mySQL.

The method used to connect to the database from PHP is the mySQLi Extension, included by default with PHP and compatible with databases created with mySQL v4.1 and later (current stable version: v5.1).

Constants

The values of these constants will be used by oneye to connect to the database. The root user will, of course, be able to change the values.

Constant Description	Constant	Default Value	Notes
mySQL Server Address	MYSQL_SERVER	127.0.0.1
Database Name	MYSQL_DBNAME	oneyeDB
Database Username	MYSQL_USERNAME	www-data
Database Password	MYSQL_PASSWORD		Stored encrypted

The Tables

List of currently implementated tables:

tblUser - Main table that contains user data.
tblGroups - Table listing which users belong to which groups. Always checked.
tblMaintainedUsers - This table lists the maintainers and their assigned users. Only checked if the maintainer field for a user in tblUser is '1'.
tblMaintainedGroups - This table lists the maintainers and their assigned groups. Only checked if the maintainer field for a user in tblUser is '1'.

The Fields

tblUser

Field Name	Type	Length	Permitted to be Null	Default Value	Other notes
username	varchar	32	No		Primary Key
password	char	32	No
email	varchar	64	Yes
quota	integer		Yes	NULL	Unsigned, NULL = 'System Default'
fullname	varchar	64	Yes
createDate	integer		Yes	0	Unsigned
lastLogin	integer		Yes	0	Unsigned
expirationDate	integer		Yes	0	Unsigned
disabled	char	1	Yes	0
admin	char	1	Yes	0
maintainer	char	1	Yes	0

tblGroups

Field Name	Type	Length	Permitted to be Null	Default Value	Other notes
username	varchar	32	No		Primary Key
groupname	varchar	32	No		Primary Key

tblMaintainedUsers

Field Name	Type	Length	Permitted to be Null	Default Value	Other notes
maintainer	varchar	32	No		Primary Key
username	varchar	32	No		Primary Key

tblMaintainedGroups

Field Name	Type	Length	Permitted to be Null	Default Value	Other notes
maintainer	varchar	32	No		Primary Key
groupname	varchar	32	No		Primary Key

Reference

For reference purposes:

List of field types in mySQL databases:

String types:
- varchar - Variable length. Ideal storage if we don't know how long a string is going to be. Requires specification of a maximum length.
- char - Fixed length. Ideal if a string will always be a certain length. Maximum length is 255 characters.
Integer types: (Each has a different range of possible values)
- tinyint - 255 possible values
- smallint - 65,535 possible values
- mediumint - 16,777,215 possible values
- int/integer - 4,294,967,295 possible values
- bigint - 18,446,744,073,709,551,615 possible values (wow!)
Real Number type: (Has decimal point)
- decimal/numeric - The number of decimal places must be set upon table definition.
- float - Can have as many decimal places as required, up to a maximum of 23.
- double - Twice the potential accuracy of float (also takes up twice as much space)

Current values being saved in xml

username
password (hashed)
email
quota (in bytes)
fullname
createDate (date expressed as number of seconds since unix epoch)
expirationDate
disabled
lastLogin (date expressed as number of seconds since unix epoch)
group (there can be more than one of these)
maintainer (0 or 1)
maintain (group the user maintains) (there can be more than one of these)
mngUser (there can be more than one of these)
admin (0 or 1)

Table of Contents