Proposed Schema for SQL Tables - TeldraIRC

Propposed prefix: tirc_

\\\\\\\\\\\\\
\\ Servers \\
\\\\\\\\\\\\\

Description: Contains a listing of all the servers tirc can connect.
Table Name: servers
Table Structure: UID|Name|Host1|Host2|Host3|nickname|username|password|auth_string|module_id

Structure Explanation:

- UID - (Pkey, Unique, auto-increment): Unique identifies each server this bot will connect to. Used for identifying various settings throughout the database.
- Name - (Not Null): The name that Identifies this server to the user.
- host1 - (Not Null): The IP or DNS name of the server.
- host2: The secondary IP or DNS Name of the server. Used if the primary host fails.
- host3: The territary IP or DNS Name of the server. Used if the secondary host fails. Depending on server settings, gives up connecting once this host is reached.
- nickname - (Not Null): The Nick the bot uses to connect to the server.
- username: The username used to identify with this server. Seperate from nickname (Although can be the same) to maintain NickName transparency.
- password: Encrtyped password used to identify with this server.
- auth_string(Not Null): Specified if a seperate module isn't used to connect to the server, or there was an error loading the module. Can contain the variables %u and %p used place where the username and password go (highly recommended).
- module_id: Identifies the module used for authentication with this server. If no module is found and username and password aren't blank, auth_string is used to authenticate with the service.

\\\\\\\\\\\\\\\\\\\\\
\\ Server Settings \\
\\\\\\\\\\\\\\\\\\\\\

Description: Stores per server configuration information.
Table Name: server_Settings

\\\\\\\\\\\\\\\\\\
\\ Server Users \\
\\\\\\\\\\\\\\\\\\

Description: Contains a listing of users the bot can identify and are assigned to channels on various servers.
Table Name: channel_users
table Structure: UID|Server_ID|nickname|hostmask|email|username|password|password_salt|secret_question|secret_answer|cookie|creation_date|last_login

Structure Explanation:

- Index - (Pkey, Unique, Auto-increment): Unique identity for users per server.
- server_id - (not null): identifies the server that this user was added under.
- nickname - (not null): The last known nickname of the user.
- hostmask - (Not Null): The last known good HostName of the user.
- email: A registered email for the user. Used for accessing account in-case of hostmask change.
- username: A seperate username from the nickname that can be used to authenticate with the bot.
- password: MD5 hashed Password used for additional authentication with the bot (Aside from hostmask).
- password_salt: USed for prevetion of birthday attacks.
- secret_question: A Challenge question for recovering a lost account.
- Secret_answer: A Reply to the challenge question for recovering a lost account.
- cookie: Used for verifying a new hostmask.
- creation_date (Not Null): When the account was first created.
- last_login - (not null): Last time the user was logged into the bot. Used for sorting out old accounts.

\\\\\\\\\\\\\\
\\ Channels \\
\\\\\\\\\\\\\\

Description: Contains a listing of all the channels the bot joins
Table Name: channels
Table Structure: uid|server_id (not null)|name (not null)|key|module_id

Structure Explanation:

- UID - (Pkey, Unique, Auto-Increment): Unique Identifies each channel. Used to tie in user permissions with each channel.
- server_id - (Not Null): the UID of the server this channel belongs to.
- name - (Not Null): The name of the channel to join. Must start with #.
- key: The password used to join the channel.
- module_id: Optional module ID used to handle channel duties (i.e user authenication, spam protection, etc etc etc).

\\\\\\\\\\\\\\\\\\\\\\\\\
\\ Channel Permissions \\
\\\\\\\\\\\\\\\\\\\\\\\\\

Description: Contains user permissions for each channel. Their Access Level
Table Name: channel_permissions
Table Structure: index|channel_id|user_id|access_level|immunity_level

Structure Explanation:

- index - (Pkey, unique, auto-increment): Unique identifier for the permissions.
- channel_id - (Not NUll): the channel ID this user has permission for.
- user_id - (Not Null): the ID of the user that is assigned permission for that channel.
- access_level - (Not Null): The permissions level number. Can be negative. Used to see if can use a command.
- immunity_level - (Not Null): The immunity level number. Can be negative. Used to see if a command can be used against this user.

\\\\\\\\\\\\\\\\\\\\\\\\\\
\\ Module Configuration \\
\\\\\\\\\\\\\\\\\\\\\\\\\\

Description: Holds information about various modules like module name, module id, and load state.