SplendidCRM Coding Conventions

Introduction

The SplendidCRM database is generated by SQL statements stored in files. The reason we do it this way is so that the generation of the database becomes a repeatable process.

A repeatable process also allows us to track the versions of each database object independently. We encourage all developers to utilize a version-control system.

We have separated the SQL objects into folders partly both to organize and to help manage dependencies. SQL tables, functions, views and procedures all may -- or may not -- have a dependency on another SQL object. We use the folders listed in Figure 1 to organize the SQL Scripts.

1. ProceduresDDL

2. BaseTables

3. Tables

4. Functions

5. ViewsDDL

6. Views

7. Procedures

8. Data

Figure 1. SQL Scripts Folder Tree

At the root of the SQL Scripts folder is the Build.bat file. This file is used to combine all the SQL scripts into a single SQL file that can be used to create all the necessary SQL objects in a SplendidCRM database.

SQL Script file names must follow the following naming convention in order for the Build.bat file to build properly. The file extension 0.sql is reserved for special objects. Otherwise, all tables, functions, views, or procedures should start with the file extension 1.sql. Tables, functions, views or procedures that are dependent on SQL objects of the same type, should have a file extension that is incremented by 1. For example, all tables that have a foreign key to another table should have a file extension of at least 2.sql.

Figure 2. Build.bat Batch file

copy ProceduresDDL\*.0.sql + ProceduresDDL\*.1.sql + ProceduresDDL\*.2.sql + ProceduresDDL\*.3.sql + ProceduresDDL\*.4.sql "ProceduresDDL.sql"

copy BaseTables\*.0.sql + BaseTables\*.1.sql + BaseTables\*.2.sql + BaseTables\*.3.sql + BaseTables\*.4.sql "BaseTables.sql"

copy Tables\*.0.sql + Tables\*.1.sql + Tables\*.2.sql + Tables\*.3.sql + Tables\*.4.sql "Tables.sql"

copy Functions\*.0.sql + Functions\*.1.sql + Functions\*.2.sql + Functions\*.3.sql + Functions\*.4.sql "Functions.sql"

copy ViewsDDL\*.0.sql + ViewsDDL\*.1.sql + ViewsDDL\*.2.sql + ViewsDDL\*.3.sql + ViewsDDL\*.4.sql "ViewsDDL.sql"

copy Views\*.0.sql + Views\*.1.sql + Views\*.2.sql + Views\*.3.sql + Views\*.4.sql "Views.sql"

copy Procedures\*.0.sql + Procedures\*.1.sql + Procedures\*.2.sql + Procedures\*.3.sql + Procedures\*.4.sql "Procedures.sql"

copy Triggers\*.0.sql + Triggers\*.1.sql + Triggers\*.2.sql + Triggers\*.3.sql + Triggers\*.4.sql "Triggers.sql"

copy Data\*.0.sql + Data\*.1.sql + Data\*.2.sql + Data\*.3.sql + Data\*.4.sql "Data.sql"

Copy "ProceduresDDL.sql" + "BaseTables.sql" + "Tables.sql" + "Functions.sql" + "ViewsDDL.sql" + "Views.sql" + "Procedures.sql" + "Data.sql" "Build.sql"

Figure 3. SQL Scripts BaseTables folder

In Figure 3, observe how the relationship table ACCOUNTS_BUGS has an extension of 2.sql –because it is dependent on both the ACCOUNTS table and the BUGS table.

NOTE: The _Comment files are required in order for the batch file to work properly. They are place holders for the file extensions in order to prevent the batch file from failing because a particular file extension does not exist.

SQL Object Naming

SplendidCRM tables should use the pluralized name, in uppercase, with underscores to separate words. Column names should also use uppercase with underscores to separate words. We chose to use uppercase because some database platforms, such as Oracle and DB2, will always promote a table name to upper case. As we use the database schema information in various parts of our system, it is important that names are consistent. Also, table names should be less than or equal to 28 characters. A 30-character limit is imposed by Oracle, and we will need to reserve two characters for view names.

SQL functions should always start with a lowercase “fn”. If the function is used in conjunction with a single table, then that table name should be in the function name.

SQL views should always start with a lowercase “vw”. The view should always include the base table name. If there is a specific context that the table is used, that context name should be placed at the end of the table name, separated by an underscore. For example, there should be an _Edit view and a _List view for every module. Even though Oracle and DB2 will promote view names to uppercase, we still prefer to use mixed case.

SQL procedures should always start with a lowercase “sp”, but never “sp_”. The “sp_” procedures have a special meaning within SQL Server and can lead to reduced performance. The procedure should always include the table name, plus the function of the procedure. For example, there should be an _Update and a _Delete for every module, and most modules have both _MassUpdate and _MassDelete procedures.

We try and keep procedure names under 30 characters, but procedures can exceed this limit so long as all procedures are unique to 30 characters. SplendidCRM will truncate any procedure name to 30 characters before performing the database call.

SQL Data Types

SplendidCRM was designed to be Microsoft-centric, so some data types are specific to SQL Server. However, we have tried not to pick types that cannot be represented in Oracle or DB2. One general rule is that we always use fields that support Unicode. The following data types are used within SplendidCRM:

1. bit

2. datetime

3. float(53)

4. image

5. int

6. money

7. ntext

8. nvarchar

9. uniqueidentifier

nvarchar fields usually have a size of 100 or 150 for names. Drop-down lists are typically nvarchar(25), but can be up to nvarchar(50).

SQL Object Creation

A Table should be created only if it does not already exist.

Figure 4. Sample table script

if not exists (select * from dbo.sysobjects where id = object_id(N'dbo.TEAMS') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

begin

print 'Create Table dbo.TEAMS';

Create Table dbo.TEAMS

( ID uniqueidentifier not null default(newid()) constraint PK_TEAMS primary key

, DELETED bit not null default(0)

, CREATED_BY uniqueidentifier null

, DATE_ENTERED datetime not null default(getdate())

, MODIFIED_USER_ID uniqueidentifier null

, DATE_MODIFIED datetime not null default(getdate())

, NAME nvarchar(128) not null

, DESCRIPTION ntext null

, PRIVATE bit null

)

end

All tables should have an ID as the primary key, plus five other auditing fields (Figure 3):

1. ID

2. DELETED

3. CREATED_BY

4. DATE_ENTERED

5. MODIFIED_USER_ID

6. DATE_MODIFIED

Functions, Views and Procedures should always drop themselves before recreating the object. Drop-before-creation is a standard SQL Server approach, whereas Oracle has a Create or Replace construct. SplendidCRM also uses the “with encryption” keywords so that we can allow our users to redistribute intellectual properly that remains solely owned by SplendidCRM Software, Inc.

For security purposes, we do not grant access to tables, but instead grant access to the functions, views and procedures. The one exception is that CSTM tables are updated directly and therefore need update access.

Figure 5. Sample view script

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.vwTEAMS') and OBJECTPROPERTY(id, N'IsView') = 1)

Drop View dbo.vwTEAMS;

-- NOTICE: This code has not been licensed under any public license.

Create View dbo.vwTEAMS

with encryption

select ID

, NAME

, PRIVATE

from TEAMS

where DELETED = 0

Grant Select on dbo.vwTEAMS to public;

SQL Views

SplendidCRM uses SQL Views as a key component of its Data Access Layer (DAL). The primary advantage to using views in this way is that such use allows the database to optimize the return of relationship data.

SplendidCRM uses the SQL-92 syntax for joining tables. This syntax is supported by current versions of all major databases. SplendidCRM primarily uses INNER JOINs or LEFT OUTER JOINs. We never use RIGHT OUTER JOINs and only use the CROSS JOIN in one area. We also prefer to code using a text editor so that we can format the view in a way that enhances readability.

Every module should have a base view that is derived from the base table and also returns related data. However, the base view should try not to return any NTEXT or IMAGE fields. As a side note, since all fields from the custom table are returned, it is probably best not to place NTEXT or IMAGE data in the custom table.

Figure 6. Sample core view

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.vwNOTES') and OBJECTPROPERTY(id, N'IsView') = 1)

Drop View dbo.vwNOTES;

-- NOTICE: This code has not been licensed under any public license.

Create View dbo.vwNOTES

with encryption

select NOTES.ID

, NOTES.NAME

, NOTES.PARENT_TYPE

, NOTES.CONTACT_ID

, NOTES.PORTAL_FLAG

, NOTES.DATE_ENTERED

, NOTES.DATE_MODIFIED

, NOTES.PARENT_ID

, NOTES.NOTE_ATTACHMENT_ID

, NOTE_ATTACHMENTS.FILENAME

, NOTE_ATTACHMENTS.FILE_MIME_TYPE

, (case when NOTE_ATTACHMENTS.ATTACHMENT is not null then 1 else 0 end) as ATTACHMENT_READY

, vwPARENTS.PARENT_NAME

, vwPARENTS.PARENT_ASSIGNED_USER_ID

, dbo.fnFullName(CONTACTS.FIRST_NAME, CONTACTS.LAST_NAME) as CONTACT_NAME

, CONTACTS.PHONE_WORK as CONTACT_PHONE

, CONTACTS.EMAIL1 as CONTACT_EMAIL

, CONTACTS.ASSIGNED_USER_ID as CONTACT_ASSIGNED_USER_ID

, USERS_CREATED_BY.USER_NAME as CREATED_BY

, USERS_MODIFIED_BY.USER_NAME as MODIFIED_BY

, NOTES_CSTM.*

from NOTES

left outer join NOTE_ATTACHMENTS

on NOTE_ATTACHMENTS.ID = NOTES.NOTE_ATTACHMENT_ID

and NOTE_ATTACHMENTS.DELETED = 0

left outer join vwPARENTS

on vwPARENTS.PARENT_ID = NOTES.PARENT_ID

left outer join CONTACTS

on CONTACTS.ID = NOTES.CONTACT_ID

and CONTACTS.DELETED = 0

left outer join USERS USERS_CREATED_BY

on USERS_CREATED_BY.ID = NOTES.CREATED_BY

left outer join USERS USERS_MODIFIED_BY

on USERS_MODIFIED_BY.ID = NOTES.MODIFIED_USER_ID

left outer join NOTES_CSTM

on NOTES_CSTM.ID_C = NOTES.ID

where NOTES.DELETED = 0

Grant Select on dbo.vwNOTES to public;

Every module should have one view that is used in lists and a second view that is used to return all data for editing.

The view used in lists is typically the same as the base view; however you can return a reduced set or some computed value as your business requires.

Figure 7. Sample list view

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.vwNOTES_List') and OBJECTPROPERTY(id, N'IsView') = 1)

Drop View dbo.vwNOTES_List;

-- NOTICE: This code has not been licensed under any public license.

Create View dbo.vwNOTES_List

with encryption

select *

from vwNOTES

Grant Select on dbo.vwNOTES_List to public;

The edit view is typically the base view plus any NTEXT fields that were excluded from the base.

Figure 8. Sample edit view

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.vwNOTES_Edit') and OBJECTPROPERTY(id, N'IsView') = 1)

Drop View dbo.vwNOTES_Edit;

-- NOTICE: This code has not been licensed under any public license.

Create View dbo.vwNOTES_Edit

with encryption

select vwNOTES.*

, NOTES.DESCRIPTION

from vwNOTES

left outer join NOTES

on NOTES.ID = vwNOTES.ID

Grant Select on dbo.vwNOTES_Edit to public;

SQL Procedures

SplendidCRM uses SQL Procedures as a key component of its Data Access Layer (DAL). The primary advantage of using procedures in this way is for performance reasons. Other advantages to using procedures as the DAL are that (a) it reduces the network traffic, (b) it allows the SQL logic to be pre-compiled (which also helps locate bugs), and (c) it allows some bugs to be fixed without re-deploying the web application. Lastly, by placing logic in procedures, (d) it allows for alternate code to be used on the varying database platforms that SplendidCRM supports.

Every module should have both an _Update and a _Delete procedure. The _Update procedure also functions as an Insert if the record does not exist. Most configuration-related modules also have an _InsertOnly procedure. _InsertOnly procedures are typically only used when initializing a database. The primary reason why we use _InsertOnly procedures is so that we do not update configuration data if it already exists.

Figure 9. Sample update procedure

if exists (select * from dbo.sysobjects where id = object_id(N'spTEAMS_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

Drop Procedure dbo.spTEAMS_Update;

-- NOTICE: This code has not been licensed under any public license.

Create Procedure dbo.spTEAMS_Update

( @ID uniqueidentifier output

, @MODIFIED_USER_ID uniqueidentifier

, @NAME nvarchar(128)

, @DESCRIPTION ntext

, @PRIVATE bit

)

with encryption

begin

set nocount on

if not exists(select * from TEAMS where ID = @ID) begin -- then

if dbo.fnIsEmptyGuid(@ID) = 1 begin -- then

set @ID = newid();

end -- if;

insert into TEAMS

( ID

, CREATED_BY

, DATE_ENTERED

, MODIFIED_USER_ID

, DATE_MODIFIED

, NAME

, DESCRIPTION

, PRIVATE

)

values ( @ID

, @MODIFIED_USER_ID

, getdate()

, @MODIFIED_USER_ID

, getdate()

, @NAME

, @DESCRIPTION

, @PRIVATE

);

end else begin

update TEAMS

set MODIFIED_USER_ID = @MODIFIED_USER_ID

, DATE_MODIFIED = getdate()

, NAME = @NAME

, DESCRIPTION = @DESCRIPTION

, PRIVATE = @PRIVATE

where ID = @ID ;

end -- if;

end

Grant Execute on dbo.spTEAMS_Update to public;

SplendidCRM rarely deletes data; instead it simply marks records for deletion. The primary reason for doing this is to aid in auditing.

Figure 10. Sample Delete procedure

if exists (select * from dbo.sysobjects where id = object_id(N'spTEAMS_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

Drop Procedure dbo.spTEAMS_Delete;

-- NOTICE: This code has not been licensed under any public license.

Create Procedure dbo.spTEAMS_Delete

( @ID uniqueidentifier

, @MODIFIED_USER_ID uniqueidentifier

)

with encryption

begin

set nocount on

update TEAMS

set DELETED = 1

, DATE_MODIFIED = getdate()

, MODIFIED_USER_ID = @MODIFIED_USER_ID

where ID = @ID;

end

Grant Execute on dbo.spTEAMS_Delete to public;

Additional Information

The SplendidCRM Application Platform is constantly being improved. Please visit the SplendidCRM Software website to obtain the most recent version of the software:

http://www.splendidcrm.com

If you have any questions, please post them to the SplendidCRM Support forum:

http://www.splendidcrm.com/Forums/tabid/66/Default.aspx

References

ASP.NET 2.0 Security Practices at a Glance
http://msdn.microsoft.com/en-us/library/ms998372.aspx

Take Advantage of ASP.NET Built-in Features to Fend Off Web Attacks
http://msdn.microsoft.com/en-us/library/ms972969.aspx

Securing ASP.NET Applications
http://www.novologies.com/post/2009/04/08/Securing-ASPNET-Applications.aspx

Jeff Prosise on Hacking ASP.NET Web Applications
http://blogs.msdn.com/mikeormond/archive/2005/02/24/379562.aspx

ASP.NET Security: 8 Ways to Avoid Attack
http://www.devx.com/security/Article/20898/0/page/1

Errors and Omissions

If you discover any errors or omissions in this document, please email support@splendidcrm.com.