This is an old revision of the document!
Table of Contents
mySQL
This page is dedicated to the development of a standard way of encoding user data into a mySQL database.
For the moment, the technical details of how to connect oneye to the database are not important. What's currently being discussed is how data should be stored within the database. This requires the following decisions:
- What tables are required, and what they should be called
- What fields are required inside those tables, and
- What the fields should be named
- What format the fields should be (Number, String, etc.)
- What length the fields should be
Please note that modifications should only be made to this page after they've been discussed in this thread on the forums. Any concerns, problems or other should be brought to light there, not here. (To those used to MediaWiki-type wiki, think of the forum page linked as the discussion page for this wiki page).
The Database
The database format is mySQL.
The proposed method 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 suggested tables:
- tblUser - Main table that contains user data
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 | ||
varchar | 64 | Yes | NULL | ||
quota | integer | Yes | NULL | ||
fullname | varchar | 64 | Yes | NULL | |
createDate | integer | Yes | NULL | ||
expirationDate | integer | Yes | 0 | ||
disabled | char | 1 | Yes | 0 | |
lastLogin | integer | Yes | NULL | ||
group | varchar | 128 | Yes | NULL | |
admin | char | 1 | Yes | NULL | default could be '0' |
maintainer | char | 1 | Yes | NULL | default could be '0' |
maintain | varchar | 128 | Yes | NULL | |
mngUsers | varchar | 1024 | Yes | NULL |
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)