CallSite Server Installation
 
 

Section: II. C. 3

CallSite Database Guide

Database Tables definition

The CallSite database must contain at least 5 tables: Users, Chat_Servers, Ads, Html_Panels, Scroll_Texts
The Users table is also used by SiteSticky.

Users Information table:

This table is used to store the user related information.
The minimum required fields are in violet, green and blue in the table below. The violet fields are required for both CallSite and SiteSticky. The green fields are required for SiteSticky only. The blue fields are required for CallSite only. The minimum suggested fields are in black.
You can add as many fields as you want to store information about your users.
This table can have any name in your database. It can also be multiple tables. The fields can also have any name.
In the case you have different table name and fields name than below, you must modify the bridge.cfg file and write the SQL requests appropriate for your table(s).
 

+---------------+--------------+------+-----+---------------------+----------------+

| Field         | Type         | Null | Key | Default             | Extra          |

+---------------+--------------+------+-----+---------------------+----------------+

| User_ID       | int(10)      |      | PRI | 0                   | auto_increment |

| Nickname      | varchar(50)  |      | UNI |                     |                |

| Password      | varchar(20)  |      |     |                     |                |

| Admin         | tinyint(4)   |      |     | 0                   |                |

| First         | varchar(30)  |      |     |                     |                |

| Last          | varchar(30)  |      |     |                     |                |

| Email         | varchar(60)  |      |     |                     |                |

| Homepage      | varchar(80)  |      |     |                     |                |

| Age           | tinyint(4)   |      |     | 0                   |                |

| Gender        | tinyint(4)   |      |     | 0                   |                |

| Comments      | varchar(255) |      |     |                     |                |

| AcceptVoice   | tinyint(4)   |      |     | 1                   |                |

| AcceptPlugins | tinyint(4)   |      |     | 1                   |                |

| Sound         | tinyint(4)   |      |     | 1                   |                |

| Away          | tinyint(4)   |      |     | 0                   |                |

| Avatar        | varchar(255) |      |     | 0                   |                |

| Invisible     | tinyint(4)   |      |     | 0                   |                |

| Notify_list   | text         | YES  |     | NULL                |                |

| Bookmarks     | text         | YES  |     | NULL                |                |

| Ignore_list   | text         | YES  |     | NULL                |                |

| BlockProfile  | tinyint(4)   | YES  |     | 1                   |                |

+---------------+--------------+------+-----+---------------------+----------------+

User_ID: identification number for the users
Nickname: nickname (screen name) to log in CallSite
Password: password to log in CallSite
Admin: determine whether the user is an administrator or not (value= 1 for admin user, value= 0 for standard user)
First: First name
Last: Last name
 Email: email address
Homepage: homepage URL
Age: age in years
Gender: gender (male = 1, female = 2)
Comments: comments the users wants to write about him/herself
AcceptVoice: whether the other user accepts voice communication or not
AcceptPlugins: whether the other user accepts the plugins or not
Sound: whether the other user makes a sound or not
Away: whether the other user is away or not
Avatar: path to the image file
Invisible: whether the other user has set himself/herself as invisible or not
Notify_list: notify list (friend list) of the user (displayed in the Friends tab of CallSite)
Bookmarks: bookmarks of the users (displayed in the Bookmarks tab of CallSite)
Ignore_list: ignore list
BlockProfile: determine if the other users will have the access to the information of this user (by clicking on the info button of CallSite)

CallSite Chat_Servers table:

This table is used to store the chat servers related information. There must be one record per server.   

+----------------+---------+------+-----+---------+----------------+

| Field          | Type    | Null | Key | Default | Extra          |

+----------------+---------+------+-----+---------+----------------+

| Server_ID      | int(10) |      | PRI | 0       | auto_increment |

| Server_address | text    |      |     | NULL    |                |

+----------------+---------+------+-----+---------+----------------+

The Server_address field contains all the server information and must be formatted as follow:

server host|port1|port2|channel name|channel title|channel icon location|default room|applet location (url)

example of Server_address value:
www.netdive.com|2112|2113|English Channel|This is the English channel.|images/gbflag.gif|Space|http://www.netdive.com/sitesticky/applet/


CallSite Ads table:

This table is used to store the advertisements related information. There must be one record per ad. 

+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |

+-----------+--------------+------+-----+---------+----------------+

| Ad_ID     | bigint(15)   |      | PRI | 0       | auto_increment |

| Ad_type   | tinyint(4)   |      | MUL | 0       |                |

| Image_url | varchar(255) |      |     |         |                |

| Link_url  | varchar(255) |      |     |         |                |

| Disp_time | tinyint(4)   |      |     |         |                |

+-----------+--------------+------+-----+---------+----------------+

Ad_type: contains the ad type information (square or banner), Ad_type = 1 for ad banners displayed at the bottom of CallSite agent application. Ad_type = 2 for square banners displayed at the right of CallSite agent application
Image_url: location of the ad graphic resource
Link_url: url of the page where displayed to the user when he/she clicks the ad.
Disp_time: number of seconds the ad is displayed to CallSite users.

CallSite Html_Panels table:

This table is used to store the html panels related information. There must be one record per panel. 

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| Panel_ID    | int(10)      |      | PRI | 0       | auto_increment |

| Title       | varchar(255) |      | UNI |         |                |

| Page_url    | varchar(255) |      |     |         |                |

| Edit_url    | varchar(255) |      |     |         |                |

| Refresh_time| tinyint(4)   |      |     |         |                |

+-------------+--------------+------+-----+---------+----------------+

Title: text displayed on the top of the tab in CallSite agent interface.
Page_url: URL of the html page displayed inside the tab window.
Edit_url: URL of the html page used by the CallSite agents to edit the content of the Page.
Refresh_time: number of seconds after which the HTML page is updated.

CallSite Scroll_Texts table:

This table is used to store the scroll text panel related information. There must be one record per text. 

+------------------+--------------+------+-----+---------+----------------+

| Field            | Type         | Null | Key | Default | Extra          |

+------------------+--------------+------+-----+---------+----------------+

| Scroll_ID        | bigint(14)   |      | PRI | 0       | auto_increment |

| ScrollText       | varchar(255) |      |     |         |                |

| ScrollText_url   | varchar(255) |      |     |         |                |

| ScrollText_color | varchar(15)  |      |     |         |                |

+------------------+--------------+------+-----+---------+----------------+

ScrollText: text string displayed to the customer
ScrollText_url: URL to which the scrolling text links
ScrollText_color: color of the scrolling text (format RRR:GGG:BBB, example: 255:240:210)

Bridge Configuration

Database bridge configuration file:

The bridge.cfg configuration file is used by CallSite and SiteSticky servers to connect to the database and to retrieve SQL requests. This file is located in the CallSite & SiteSticky 'server' directory.

This file contains:

  • The information to connect to your SQL database:
  • Driver name
  • URL to database in JDBC format
  • database name
  • username to connect to your database
  • password to connect to your database
  • The templates for the SQL requests:

    The SQL requests can be written with the following rules:
    • The requests must use standard SQL
    • The following tags are used to write the requests: $Nickname$, $Password$, $Admin$, $AcceptVoice$, $AcceptPlugins$, $Sound$, $Away$, $Avatar$, $Invisible$, $First$, $Last$, $Email$, $Homepage$, $Age$, $Gender$, $Comments$, $Notify_List$, $Bookmarks$, $Ignore_list$
    • Those tags will be replaced by the actual value sent by CallSite & SiteSticky servers when the request template is parsed by the bridge
    • The select requests must be written such as the results are returned in the order indicated below.


Notes:

- Every time you modify the bridge.cfg file you must restart the server to load the modifications

- To allow CallSite & SiteSticky to connect to your database, you must create a user with the following rights:

  • insert, delete, update, select on the Users table
  • connection allowed from ANY HOSTS for this user
  • for MySQL use the following commands:
    • GRANT select, insert, delete, update ON your_database.your_table_name TO your_username@"%" IDENTIFIED BY 'your_password'
    • GRANT select, insert, delete, update ON your_database.your_table_name TO your_username@localhost IDENTIFIED BY 'your_password'

 

Template SQL requests details :

Please read the following carefully. It informs you about the different SQL queries required for the DB Bridge. You can adapt every request to your specific database by replacing the fields and table names which we have given in the following examples by yours. You can also completely rewrite the SQL. In that case, please keep the same order in the fields for the result of select queries.

InsertQuery:

Target:
This request is used by the bridge to insert a new user in the database.

Tags: $Nickname$, $Password$, $Admin$, $AcceptVoice$, $AcceptPlugins$, $Sound$, $Away$, $Avatar$, $Invisible$, $First$, $Last$, $Email$, $Homepage$, $Age$, $Gender$, $Comments$, $BlockProfile$

Example:
INSERT INTO <table name> (User_ID, Nickname, Password, Admin, AcceptVoice, AcceptPlugins, Sound, Away, Avatar, Invisible, First, Last, Email, Homepage, Age, Gender, Comments, BlockProfile) VALUES (NULL, $Nickname$, $Password$, $Admin$, $AcceptVoice$, $AcceptPlugins$, $Sound$, $Away$, $Avatar$, $Invisible$, $First$, $Last$, $Email$, $Homepage$, $Age$, $Gender$, $Comments$, $BlockProfile$);

UpdateNotifyBookmarks:

Target:
This request is used by the bridge to load the update the user bookmarks list and friends list.

Tags: $Nickname$, $Notify_List$, $Bookmarks$, $Ignore_list$

Example:
UPDATE <table name> SET Notify_list=$Notify_list$, Bookmarks=$Bookmarks$, Ignore_list=$Ignore_list$ WHERE Nickname=$Nickname$

UpdateInfoQuery:

Target:
This request is used by the bridge to update the personal information values in the database.

Tags: $Nickname$, $Password$, $First$, $Last$, $Email$, $Homepage$, $Age$, $Gender$, $Comments$, , $BlockProfile$

Example:
UPDATE <table name> SET Nickname=$New_Nickname$, Password=$Password$, First=$First$, Last$Last$, Email=$Email$, Homepage=$Homepage$, Age=$Age$, Gender=$Gender$, Comments=$Comments$, BlockProfile=$BlockProfile$ WHERE Nickname=$Nickname$;

SelectNotifyBookmarks:

Target:
This request is used by the bridge to load the user Notify List (friends list), Bookmarks and Ignore_list from the database.

Fields Order:
The following order of the fields for the result of the select request must be respected: Notify_list, Bookmarks,Ignore_list

Tags: $Nickname$

Example:
SELECT Notify_list, Bookmarks, Ignore_list FROM <table name> WHERE Nickname=$Nickname$

SelectUserQuery:

Target:
This request is used by the bridge to select the user information in the database.

Fields Order:
The following order of the fields in the select request must be respected: Password, Admin, AcceptVoice, AcceptPlugins, Sound, Away, Avatar, Invisible, First, Last, Email, Homepage, Age, Gender, Comments, BlockProfile

Tags: $Nickname$

Example:
SELECT Password, Admin, AcceptVoice, AcceptPlugins, Sound, Away, Avatar, Invisible, First, Last, Email, Homepage, Age, Gender, Comments, BlockProfile FROM <table name> WHERE Nickname=$Nickname$;

NumberOfUsersQuery:

Target:
This request is used by the bridge to count the number of users in the database.

Tags: none

Example:
SELECT COUNT(*) FROM <table name>;

ListOfUsersQuery:

Target:
This request is used by the bridge to select the list of usernames in the database.

Tags: none

Example:
SELECT Nickname FROM <table name>;

DeleteQuery:

Target:
This request is used by the bridge to delete a user in the database.

Tags: $Nickname$

Example:
DELETE FROM <table name> WHERE Nickname=$Nickname$;

SelectChatServers:

Target:
This request is used by the bridge to select the chat servers information in the database.

Tags: none

Example:
SELECT Server_address FROM Chat_Server;

SelectHtmlPanels:

Target:
This request is used by the bridge to select the html panels information in the database.

Tags: none

Example:
SELECT Title, Page_url, Edit_url FROM Html_Panels;

SelectAddBanners:

Target:
This request is used by the bridge to select the ad banners information in the database.

Tags: none

Example:
SELECT Image_url, Disp_time, Link_url FROM Ads WHERE Ad_type=1;

SelectAddPanels:

Target:
This request is used by the bridge to select the ad panels (square) information in the database.

Tags: none

Example:
SELECT Image_url, Link_url FROM Ads WHERE Ad_type=2

SelectScrollTexts:

Target:
This request is used by the bridge to select the scroll text information in the database.

Tags: none

Example:
SELECT ScrollText, ScrollText_url, ScrollText_color FROM Scroll_Texts

Bridge.cfg sample file :

The example file below will work with the database default structure provided above in Database Tables definition chapter.

URL=jdbc:mysql://www.netdive.com/netdive
Driver=org.gjt.mm.mysql.Driver
DBName=netdive
UserName=myusername
Password=mypassword
InsertQuery=INSERT INTO Users (User_ID, Nickname, Password, Admin, AcceptVoice, AcceptPlugins, Sound, Away, Avatar, Invisible, First, Last, Email, Homepage, Age, Gender, Comments,BlockProfile) VALUES (NULL, $Nickname$, $Password$, $Admin$, $AcceptVoice$, $AcceptPlugins$, $Sound$, $Away$, $Avatar$, $Invisible$, $First$, $Last$, $Email$, $Homepage$, $Age$, $Gender$, $Comments$,$BlockProfile$)
UpdateUserQuery=UPDATE Users SET Nickname=$New_Nickname$, Password=$Password$, First=$First$, Last=$Last$, Email=$Email$, Homepage=$Homepage$, Age=$Age$, Gender=$Gender$, Comments=$Comments$, Avatar=$Avatar$,AcceptVoice=$AcceptVoice$, AcceptPlugins=$AcceptPlugins$, Sound=$Sound$, Away=$Away$, Admin=$Admin$, Invisible=$Invisible$, BlockProfile=$BlockProfile$ WHERE Nickname=$Nickname$
SelectQuery=SELECT Password, Admin, AcceptVoice, AcceptPlugins, Sound, Away, Avatar, Invisible, First, Last, Email, Homepage, Age, Gender, Comments, BlockProfile FROM Users WHERE Nickname=$Nickname$
NumberOfUsersQuery=SELECT COUNT(*) FROM Users
ListOfUsersQuery=SELECT Nickname FROM Users
DeleteQuery=DELETE FROM Users WHERE Nickname=$Nickname$
SelectNotifyBookmarks=SELECT Notify_list, Bookmarks, Ignore_list FROM Users WHERE Nickname=$Nickname$
UpdateNotifyBookmarks=UPDATE Users SET Notify_list=$Notify_list$, Bookmarks=$Bookmarks$, Ignore_list=$Ignore_list$ WHERE Nickname=$Nickname$
SelectChatServers=SELECT Server_address FROM Chat_Servers
SelectHtmlPanels=SELECT Title, Page_url, Edit_url FROM Html_Panels
SelectAddBanners=SELECT Image_url, Disp_time, Link_url FROM Ads WHERE Ad_type=1
SelectAddPanels=SELECT Image_url, Link_url FROM Ads WHERE Ad_type=2
SelectScrollTexts=SELECT ScrollText, ScrollText_url, ScrollText_color FROM Scroll_Texts