Customizing A Broker SQL Pattern File

If there is no Broker SQL Pattern file available for your database system yet or you want to maintain additional information for the users the you can develop your own bsp file.

Here are the steps:

1.	Create a new SIT folder in your project.

•	Right click to the site icon on the project explorer and choose "New Folder" on the context menu

•	Check the "Server Item Types" checkbox and select "Broker SQL Pattern" on the upcoming dialog

•	Click on [OK] button

•	Right click on the "Broker SQL Pattern" folder on the project explorer and select "Shell \| Open Folder" command on the context menu. We will copy a bsp file under this folder.

2.	Copy one of the existing bsp files as an example

•	Goto folder {INSTALL_DIR}\lib\sys\moremotion\ROOT\WEB-INF\MM-INF\config\security\bsp using the windows explorer.

•	Copy, e.g. "mysql.bsp" file and paste into the folder "Broker SQL Pattern" Folder.

•	Rename the copied file, e.g. to "new.bsp".

•	Right click to "Broker SQL Pattern" folder on the project explorer and select Insert File command on the context menu.

•	Select the bsp file to include it into the project.

InsertBSP

3.	Create a new Broker Definition in the securitybrokers file

•

If the new broker definition supposed to be valid in the current project only, use the menu "Project | Security Broker Definitions...". The designer will copy {INSTALL_DIR}\ib\sys\moremotion\ROOT\WEB-INF\MM-INF\config\securitybrokers.xml file under the {PROJECT_DIR} and will open it with mmEd editor.

If you want that the new broker definition become visible to all the projects open the {INSTALL_DIR}\ib\sys\moremotion\ROOT\WEB-INF\MM-INF\config\securitybrokers.xml file with a text editor.

•	Copy & Paste a definition block and modify as follows

<class>org.moremotion.mmcomp.security.RelDBSecurityBroker</class>

<param name="debug">false</param>

</securityBroker>

4.	Open the bsp file with a text editor and modify the SQL Patterns as required. Note: You should stop and restart the web server each time you make a modification to the bsp file.

5.	Select the new broker definition both in the "Security Broker" property of the SecurityDomain element.

Modifying the SQL Patterns in the bsp File

Although the functionality provided by the SQL Patterns are obvious and therefore can easily be customized to add extra user attributes or changed to create a new bsp file for a new database system, it is still worth to mention about some points:

Checking the existence of the security tables

#Check Tables

{

SHOW TABLES LIKE 'users';

}

The SQL Defined with this pattern is executed to check whether the security tables already exist. If the SQL used here returns a result with at least one record, the broker assumes that the security tables exist; Otherwise it will use the '#Create Tables' SQL to create the tables and afterwards will use '#Fill In Initial Data' SQL to fill in the initial data to the security tables.

Auto Increment IDs vs. Sequence tables

Most of the database systems support automatically incremented ID columns but some of them, i.e. Oracle not. See below the two different implementations of the '#Insert CheckPoint' SQL Pattern for two database systems.

MySQL Version

#Insert CheckPoint

{

INSERT INTO cps (NAME, DESCR)

VALUES('@{cpname}', '@{cpdescr}')

}

Oracle Version

#Insert CheckPoint

{

INSERT INTO scott.cps (ID, NAME, DESCR)

VALUES( scott.cps_id_seq.NEXTVAL, '@{cpname}', '@{cpdescr}')

}

Adding Extra User Attributes to the Users

If you want to maintain extra information for the users you should modify the following SQL Patterns:

1.	'#Create Tables' SQL (CREATE TABLE users statement)

2.	'#Fill In Initial Data' SQL (INSERT INTO users statement)

3.	'#Update User' SQL

4.	'#Insert User' SQL

5.	'#Get User Info By Name' SQL

6.	'#Get User Info By Email' SQL

7.	'#Get All Users' SQL

8.	'#Search Users' SQL

Example:

Assume that we want to maintain an additional field called 'DEPT'. The modified SQL Patterns would be as follows.

#Create Tables

{

...

CREATE TABLE users (

ID int(11) NOT NULL auto_increment,

NAME varchar(50) NOT NULL default '',

PASSWORD varchar(50) NOT NULL default '',

FULLNAME varchar(80) NOT NULL default '',

EMAIL varchar(128) NOT NULL default '',

DISABLED varchar(5) NOT NULL default '',

DEPT int(8) default 0,

PRIMARY KEY (ID),

KEY NAME (NAME)

);

...

}

#Fill In Initial Data

{

...

INSERT INTO users (NAME, PASSWORD, FULLNAME, DEPT)

VALUES('sa', '6D81B7AA5F5C76AC475E', 'System Administrator', 0);

...

}

#Update User

{

UPDATE users SET

PASSWORD = '@{password}',

FULLNAME = '@{fullname}',

EMAIL = '@{email}',

DISABLED = '@{disabled}',

DEPT = @{DEPT}

WHERE NAME = '@{username}'

}

#Insert User

{

INSERT INTO users

(NAME, PASSWORD, FULLNAME, EMAIL, DISABLED, DEPT)

VALUES('@{username}', '@{password}', '@{fullname}', '@{email}', '@{disabled}', @{DEPT})

}

#Get User Info By Name

{

SELECT ID AS ID, NAME AS NAME, PASSWORD AS PASSWORD, FULLNAME AS FULLNAME,

EMAIL AS EMAIL, DISABLED AS DISABLED, DEPT

FROM users WHERE NAME = '@{username}'

}

#Get User Info By Email

{

SELECT ID AS ID, NAME AS NAME, PASSWORD AS PASSWORD, FULLNAME AS FULLNAME,

EMAIL AS EMAIL, DISABLED AS DISABLED, DEPT

FROM users WHERE EMAIL = '@{email}'

}

#Get All Users

{

SELECT ID AS ID, NAME AS NAME, PASSWORD AS PASSWORD, FULLNAME AS FULLNAME,

EMAIL AS EMAIL, DISABLED AS DISABLED, DEPT

FROM users

}

#Search Users

{

SELECT ID AS ID, NAME AS NAME, PASSWORD AS PASSWORD, FULLNAME AS FULLNAME,

EMAIL AS EMAIL, DISABLED AS DISABLED, DEPT

FROM users

WHERE

(NAME = '' OR NAME LIKE '%@{username}%') AND

(FULLNAME = '' OR FULLNAME LIKE '%@{fullname}%') AND

(EMAIL = '' OR EMAIL LIKE '%@{email}%' ) AND

(DISABLED = '' OR DISABLED LIKE '%@{disabled}%') AND

(DEPT = '' OR DEPT = '@{DEPT}')

}

Note: You should stop and restart the web server each time you make a modification to the bsp file.