SplendidCRM Coding Conventions
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.
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"
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.
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.
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).
A Table should be created only if it does not already exist.
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
GO
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.
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.vwTEAMS') and OBJECTPROPERTY(id, N'IsView') = 1)
Drop View dbo.vwTEAMS;
-- Copyright (C) 2005 SplendidCRM Software, Inc. All rights reserved.
-- NOTICE: This code has not been licensed under any public license.
Create View dbo.vwTEAMS
with encryption
as
select ID
, NAME
, PRIVATE
from TEAMS
where DELETED = 0
Grant Select on dbo.vwTEAMS to public;
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.
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.vwNOTES') and OBJECTPROPERTY(id, N'IsView') = 1)
Drop View dbo.vwNOTES;
Create View dbo.vwNOTES
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.
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;
Create View dbo.vwNOTES_List
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.
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;
Create View dbo.vwNOTES_Edit
select vwNOTES.*
, NOTES.DESCRIPTION
left outer join NOTES
on NOTES.ID = vwNOTES.ID
Grant Select on dbo.vwNOTES_Edit to public;
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.
if exists (select * from dbo.sysobjects where id = object_id(N'spTEAMS_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
Drop Procedure dbo.spTEAMS_Update;
Create Procedure dbo.spTEAMS_Update
( @ID uniqueidentifier output
, @MODIFIED_USER_ID uniqueidentifier
, @NAME nvarchar(128)
, @DESCRIPTION ntext
, @PRIVATE bit
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
, DESCRIPTION
values ( @ID
, @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 ;
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.
if exists (select * from dbo.sysobjects where id = object_id(N'spTEAMS_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
Drop Procedure dbo.spTEAMS_Delete;
Create Procedure dbo.spTEAMS_Delete
( @ID uniqueidentifier
set DELETED = 1
, MODIFIED_USER_ID = @MODIFIED_USER_ID
where ID = @ID;
Grant Execute on dbo.spTEAMS_Delete to public;
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
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
If you discover any errors or omissions in this document, please email support@splendidcrm.com.