Rhetorical Figures Development (2)

Location of source files

The development files

• On server "healthdoc.cs"
• "/var/www/healthdoc/rhetfigdev"

The live application files

• On server "healthdoc.cs"
• "/var/www/healthdoc/rhetfig"

Database design

Tables and fields

Users Table

• This table is used for storing user information
• the data is stored in the default collection "latin1_swedish_ci"

The mySQL code to create the database is the following:

CREATE TABLE users (
   id MEDIUMINT NOT NULL AUTO_INCREMENT,
   email TEXT NOT NULL,
   fname TEXT NOT NULL,
   password TEXT NOT NULL,
   reviewer TEXT NOT NULL,
   PRIMARY KEY (id)
   )

• id
  • This field stores the unique id of a user
  • This field is set to be the primary key, where it will auto increment with each insertion of data
  • it stores integer variable
• email
  • this field stores the email of a user
  • The value stored is also unique
  • This unique value is used as the user login
• fname
  • stores the full name of a registered user
• password
  • This field stores the password of the user
  • The value stored has been "md5" hashed
  • The hashed value is then encrypted using the "c_crypt" function in the functions section
• reviewer
  • This field stores the permission level of the user
  • there are 4 permission levels
    • when the user do not exist in the table the permission is "guest"
    • when "reviewer" equals "false" for a user the permission is "regular user"
    • when "reviewer" equals "true" for a user the permission is "reviewer"
    • when "reviewer" equals "admin" for a user the permission is "administrator"

RhetoricalFigures Table

• This table is the main database for Rhetorical Figures to be stored.
• The data is stored in the collection of "utf8_unicode_ci"
• this is to enable user to enter special characters into the data base.

The mySQL code to create the database is the following:

       
CREATE TABLE test2
   (
   id MEDIUMINT NOT NULL AUTO_INCREMENT,
   name text NOT NULL,
   source text NOT NULL,
   original_source text NOT NULL,
   synonyms text NOT NULL,
   etymology text NOT NULL,
   type text NOT NULL,
   linguistic_domain text NOT NULL,
   definition text NOT NULL,
   example text NOT NULL,
   formal_specification text NOT NULL,
   conceptual_specification text NOT NULL,
   linguistic_cues text NOT NULL,
   type_of text NOT NULL,
   part_of text NOT NULL,
   related_figures text NOT NULL,
   notes text NOT NULL,
   confidence text NOT NULL,
   reviewed text NOT NULL,
   reviewer text NOT NULL,
   last_editor text NOT NULL,
   reviewer_comments text NOT NULL,
   PRIMARY KEY (id)
   )DEFAULT CHARACTER SET = utf8
   COLLATE = utf8_unicode_ci 

• id
  • This field stores the unique id of a figure
  • This field is set to be the primary key, where it will auto increment with each insertion of data
  • it stores integer variable
• The following fields are stored in the database as "mySQL text" (The data type that can store a long piece of string)
  • name (figure name)
  • source
  • original_source (Earliest Source)
  • synonyms (this field is stored with delimiter ",")
  • etymology
  • type
  • linguistic_domain
  • definition
  • example
  • formal_specification
  • conceptual_specification
  • linguistic_cues
  • type_of (Kind Of)
  • part_of
  • related_figures (this field is stored with delimiter ",")
  • notes
  • confidence
  • reviewed
  • reviewer
  • last_editor
  • reviewer_comments (Editorial Notes)

Temp Table

• A temporary database for the soul purpose of displaying a list of figures
  • the temp table allowed displaying synonyms with figures possible
  • the temp table allowed displaying figures beside the related synonyms
  • the temp table allowed the alphabetical order of the list possible
• this table is different from the 2 above, where it retrieves data and could not be modified by user
• this table is updated every time a field in the RhetoricalFigures table is modified
• see file "flistpre.php" in the Application code files and descriptions section for details on how the temp table is modified

The mySQL code to create the database is the following:

CREATE TABLE temp (
      id MEDIUMINT NOT NULL,
      list TEXT NOT NULL,
      figure TEXT NOT NULL,
      reviewed TEXT NOT NULL
   )

• id
  • stores the id retrieved from the figures table of a figure
  • this field is empty if the data stored is not a figure
• list
  • this field stores the list variable where it is either a synonym or a figure name
  • The synonym name is retrieved while retrieve a figure
  • a check is performed in "flistpre.php" to avoid the same synonyms to be entered into the tempt able
• figure
  • This field is to store the figures that relate to a synonym as a link for displaying purposes
  • eg. (figure1 has 2 synonyms "synonym1, synonym2". figure2 has 1 synonym "synonym2")
  • for synonym2 in the list field, the figure field will be "(figure1)(figure2)" as a links to the figure display "get"
• reviewed
  • this variable is retrieved from the figures table
  • this field is empty for synonyms that are in the list

Application code files and descriptions

admin.php (permissions: admin)

• admin.php contains the code for the functions of import and export of the 2 main database tables: users, test2(it is the test database for the rhetorical figures)
• it takes $_GET variables for specific actions, such as 'iu' (import users), 'if' (import figures), 'eu' (export users), and 'ef' (export figures).
• if 'eu' or 'ef' then it will directly export the database table into a '.csv' file
• if 'iu' or 'if' then it will direct the user to the import tables interface (which is contained in this file)
• This interface contains the following
  • browse for file name
  • submit button
• import tables interface will let the user to select a file with extension '.csv' for import.
• when the form is submitted it will redirect to the file 'upload_file.php'

changepwreg.php (permissions: user+)

• this file contains the code to carry out the action sent by changepw.php,
• Take user id and full name from cookie
• Take 3 variables from changepw.php: $_POST['npw'] (new password), $_POST['cnpw'] (confirm new password), $_POST['opw'] (old password)
• if any of the field :npw, cnpw, opw are empty the file redirect to 'message.php' with get 'message=emptyfield'
• if the old password is not your current password then redirect to 'message.php' with get 'message=failopw'
• if the newpassword is not the same as the confirm new password then redirect to 'message.php' with get 'message=failcomf'
• if no redirect occurs then update the old password to the new password, and redirect to 'message.php' with get 'message=changepw'

changepw.php (permissions: user+)

• Creates a form to change password with fields:
  • Old password
  • New password
  • Confirm new password
• Sends post variables to changepw2.php

default.css

• this is the css file for the site, which is included in every display page
• it is the same css file used in the original Google apps site

delete.php (permissions: reviewer+)

• This file takes '$_GET[id]' from 'flist.php'
• From '$_GET[id]', name of figure is know with '$name=$row[name]'
• It will delete the figure with the id got from full list
• After the figure is deleted, it will update the 'temp' table
• After everything is done, it will redirect to 'message.php' with get 'message=delete&name=$name'

edit.php (permissions: user+)

• displaying the edit form
• set each field to the value related to the editing figure
• values are passed to "editreg.php"
• default value for field 'reviewed, type, linguistic domain, and type_of, and confidence' were specially set
• they reason being, these fields are not text fields

editreg.php (permissions: user+)

• takes variables sent by 'edit.php'
• all the field variables are processed by the 'escape()' function to enable security (this function will be explained in detail in the functions section). this includes
  • SQL escape string
  • HTML Strip tag
  • XSS, Javasrcipt and other scripting escape process
• Figure name change is checked, (if the figure name was changed to an existing figure, then it will direct the user to 'message.php')
• Variable Synonyms are trimmed, so the synonyms entered to the database are comma delimited (ignoring whitespace between the delimiters)
• Data fields for that particular figure is updated
• 'temp' table is updated (because data in the 'figures' table has been changed)

entry.php (permissions: user+)

• this file provide the user with a form to enter new figure
• a form asking for figure name will be seen first
• checks if the figure already exist or not
• if exist, direct to the figure that exist in the database using 'message.php'
• if not, direct to new figure data entry form (with in the file)

entryreg.php (permissions: user+)

• takes field variables from 'entry.php'
• all the field variables are processed by the 'escape()' function to enable security.
• variable Synonyms are trimmed, so the synonyms entered to the database are comma delimited (ignoring whitespace between the delimiters)
• Figure with the data fields are inserted into the figures database
• 'temp' table is updated

flist.php (permissions: all)

• This file displays a list of all the figures including the synonyms
  • figures are displayed as links to a display of its content fields
  • synonyms have figure links attached on the side with '()'
  • the attached figure has the synonym inside its 'synonyms' field
  • if the synonyms have more than one figure relating to it, it will display the figures with '()' right beside each other
  • edit link is displayed beside each figure (if the user has 'user' permissions or higher)
  • allow figure delete option on the top
  • alphabetical index on the top, for anchor search
  • when delete is allow, delete link will appear beside each figure (if the user has 'reviewer' permissions or higher)
• The file sorts the data in alphabetical order
  • Generates a alphabetical index
  • selects from 'temp' table (the temp table will be explained under 'flistpre.php')
  • display 26 lists, starting with each letter, in alphabetical order
• creates anchor on each letter,
• check for figure, and synonym (different displays)

flistpre.php

• takes in $_GET variable as to which page to direct to
  • flist.php
  • search.php
• checks for the existence of the temp table
• if exist create the temp table (important)
  • create temp table with fields
    • id (the id of an object entered into the table), this field will auto increment when new data is inserted
    • list (including all the figures and synonyms)
    • figure (only apply to synonyms, where this is where the attached figures are stored)
    • reviewed (only apply to figures, where this is where the check for the figure's reviewed status get its data)
  • getting data from figures table
    • retrieve all figures, and insert the figure name into the list field of the temp table, insert the reviewed status into the reviewed filed of the table
    • while retrieving a figure, the synonyms are selected
    • using 'implode()' function, and ',' as a delimiter separate the synonyms from the figures table into a array
    • insert each synonym into the field list of the temp table, insert the figure name of the current figure that has been visited with get link into the figure field of the temp table
    • check for duplicates, where 2 or more figures share the same synonyms (by checking if the same synonym already exist in the temp table already, will be the same as checking for more figures sharing the same synonyms)
• after the temp table has been created and data has been inserted, it will direct the user to the appropriate place, (full list),(search list)
• if not check for the directed page and direct user

footer.php (permissions: all)

• display footer for each page
• inform who has logged in
• inform the current version
• will change based on permission

functions.php

• provides personalized functions
  • escape();
  • autolink($text);
  • updatetemp();
  • c_encrypt($data);
  • c_decrypt($data);
  • isuser($id, $perm);
  • ifexist($table, $colms, $colm, $obj, $check);
  • randpw();
• These functions are explained in detail in the functions section

get.php (permissions: all)

• This file allows the display of the content for a certain figure
• It takes $_GET variable for it to retrieve a specific figure, from (flist.php, search.php)
• select the data form the figures table where the name is the $_GET variable
• displaying all fields for that figure
• white spaces are respected in this by 'pre' tag

header.php (permissions: all)

• This is where the header is
• it is a menu for the site, with a search bar on the top right
• administrative tool bar is found here
• will change based on permission

index.php (permissions: all)

• This is the home page of the database

login.php (permissions: all)

• This create the login form interface
• fields: email, password
• link to password request
• Sends $_POST variables to 'loginreg.php'

loginreg.php

• performs the action of login
• get $_POST varable from 'login.php'
• check for existence of email address in the users database
• check for password match
• creates a encrypted cookie with the user id
• direct the user to a message page

logoff.php (permissions: user+)

• deletes the encrypted cookie
• direct the user to a message page

message.php (permissions: all)

• this file displays the message whenever the user has been directed to a message page
• it takes in $_GET variables in order to know which message to display for ever situation

search.php (permissions: all)

• This file displays a searched list
• takes in $_POST variable from the search bar in 'headers.php'
• Using Mysql statement to search for the figures and synonyms from the database
• Getting display from the temp table
• Please see flist.php (very similar)

sqlaccess.php

• Creates connection to the Mysql database

upload_file.php

• performs the action of importing '.csv' file to the database
• displays information about the importing process

usersaction.php

• take on the action send by 'login.php' to send password request emails
• perform the actions from 'users.php' user's list (The content below all belong to administrative permissions)
  • Add new user
  • Reset password
  • Change permission
  • Delete user
• Add new user
  • when this action is called, it will display a form asking for
    • email
    • full name
    • password (this is randomly generated)
    • permissions
  • when the form is submitted it will check for
    • empty fields
    • duplicate email
    • violating conditions will direct to message page
  • Will insert the data into the users database
• Rest password
  • the id of the selected user is passed from 'user.php'
  • will generate random password, encode & encrypt
  • update password field tot hat random password of the user selected
  • create and send email to both administrator and the user selected
• Change permissions
  • the id of the selected user is passed from 'user.php'
  • when this action is called, it will display a form with drop down menu containing the 3 types of permissions
    • Regular user
    • Reviewer
    • Administrator
  • when the form is submitted it will update the permissions of the user selected to the permission level selected
• Delete user
  • the id of the selected user is passed from 'user.php'
  • When this action is called, it will delete the user from the 'users' database

users.php (permissions: admin)

• Displays a list of users from the 'users' database table
• Function links are displayed
  • Add new user
  • Reset password
  • Change permission
  • Delete user
• when any of the links are selected it will direct the user to 'usersaction.php'
  • Add new user 'usersaction.php?action=newuser'
  • Reset password 'usersaction.php?action=reset&id=$row[id]'
    • $row is the array with the data got from users database table
    • the id is preset to each field by using the while loop
  • Change permission 'usersaction.php?action=perm&id=$row[id]'
  • Delete user 'usersaction.php?action=delete&id=$row[id]'
    • this link has 'onclick="return confirm('Are you sure you want to delete $row[email] from the Users list?')'
    • when the user clicks on the link it will prompt a pop up window asking "are you sure?"

vlog.php (permissions: all)

• display a list of the version logs as a link to the txt files

Directory: import (permissions: all, read.write)

• this directory is created in order to allow 'uplaod_file.php' to work
• this directory can be read or write by the puplic
• it temporally contain csv files thats going to be imported to the database
• the files will be deleted after the import is complete

Directory: vlog (permissions: all, read only)

• this is the location where all the version logs are kept

Functions

There are several functions

escape

• this is a function that provides data entry security.
• it takes in variable "$data" and perform actions
• it strips away unwanted characters and code that could potentially cause damage
• First the function checks for special characters that are most common in scripting (eg. XSS) and replace them with a line brake
• for common cross site scripting lines the function strips them away
  • These are done using the string replace function in php
• It will then specially look for HTML tags and strips them away also
  • this is done using the php default function called "strip tag"
• It will then insert escape character "\" when ever there potential mySQL injection
  • this is done using the php default function called "mysql escape"
• when these checks are done the function will then strip away leading and trialling spaces
• the function returns the resulting variable

autolink

• it takes in variable "$text"
• this functions checks for phrases that resembles a link
  • such as "http", "www" ect.
• it will then put HTML link tag around it
• the function returns the resulting variable

updatetemp

• This function check for existing temp table in the database
• if the table exist, it will drop the table
  • This function only drops and delete the table
  • because the table is temporary, it gets recreated each time the "flistpre.php" file is accessed
  • so the full list displayed to the user is always the most updated (thus updating the temp table)
• if there isn't a temp table, then this function does nothing

c_crypt

• it takes in variable "$data"
• it takes in variable "$mode"
  • this determines the mode of the function
    • to Encrypt the mode has to equal to "en"
    • to Decrypt the mode has to equal to "de"
• the function will encrypt the data with a special key-string
  • php string replace function is used to escape special characters that were formed after the encoding
  • it will replace these characters with unique phrases so it is easier to decode the data
• the function ill decrypt the data with the same key-string
  • using the php function to convert the unique phrases back to special characters
  • then decrypting the data using the key-string

isuser

• This function is used to check for user permissions
• it takes in variable "$id" (the user id)
• it takes in Variable "$perm" (the permission level)
• The function will check for the user id
• check the permission of that user from the "users" database table
• then compare it to the "$perm" variable passed in
• the variable return is boolean (True or False)

ifexist

• This function has two functions
  • to check if one object exist in a database, returning True or False
  • to retrieve data from a database, returning the retrieved object in an array
• The function takes in
  • "$table" the table name that the object should be retrieving from
  • "$colms" the columns of the table that wish to be retrieved
  • "$obj" the object to compare to to select the specific one (eg. "Select name from users where name='$obj'")
  • "$check" when $check=True the function will check for existing object and return a boolean value
  • when $check=False the function will retrieve the object specified

randpw

• This function generates a random value with 7 characters

Code logic

How to modify the database

How to add a field