Transcript Entities and the Data Model (Part 1 of 2)

Sage CRM Developers Course
Entities and the
Data Model (Part 1)
Looking ahead to the classes
DP01: Introduction to the Development
Partner Program
DP02: Entities and the Data Model (Part 1 of
2)
DP03: Entities and the Data Model (Part 2 of
2)
DP04: Implementing Screen Based Rules
(Part 1 of 2)
DP05: Implementing Screen Based Rules
(Part 2 of 2)
DP06: Screen and User Independent
Business Rules
DP07: Workflow (Part 1 of 2)
DP08: Workflow (Part 2 of 2)
DP09: Using the API Objects in ASP Pages
(Part 1 of 2)
DP10 : Using the API Objects in ASP Pages
(Part 2 of 2)
DP11: Using the Component Manager
DP12: Programming for the Advanced Email
Manager
DP13: Using the Web Services API
DP14: Using the Web Services API (Part 2 of
2)
DP15: Coding the Web Self Service COM API
(Part 1 of 2)
DP16: Coding the Web Self Service COM API
(Part 2 of 2)
DP17: Using the .NET API (Part 1 of 2)
DP18: Using the .NET API (Part 2 of 2)
Agenda
How data and record identity is handled
Record Object vs. SQLQuery Object
Role of Stored Procedures
Allowed Database Changes
Meta Data and Physical Data Types
New Tables and Fields in CRM
Linking to External Database tables
How and where table data is stored in Meta Data
How data and record
identity is handled
Record Object vs
SQLQuery Object
Role of Stored
Procedures
New Sage CRM v7.2 Installs on
MS SQL Server use SQL Identity
Columns
In Sage CRM v7.1 and earlier Primary Key values were generated and maintained by
stored procedures. This was to create common method mechanism between main
install and SOLO.
SOLO dropped in favour of new Mobile Apps
New installs on SQL Server will now use SQL Identities
Significant performance improvements
New version of Stored Procedure for existing installs
NO Change for Oracle
New Installs on MS SQL
Server use SQL Identity
Columns
SELECT
t.TABLE_NAME
,c.COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS AS c
JOIN
INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
WHERE
COLUMNPROPERTY(OBJECT_ID(c.TABL
E_NAME)
,c.COLUMN_NAME,'IsIdentity') = 1 AND
t.TABLE_TYPE = 'Base Table'
eWare_get_identity_id stored
procedure use in QueryObject code
Problems will arise with add-on code that does a direct insert using
eware_get_identity_id in Query object.
‘hidden’ or automatic rules within CRM
– Validation Rules
– Table Level Scripts
– ASP and .NET Application Extensions
Record object not effected
Inserts using QueryObject
Sage CRM v7.1
var strSQL = "DECLARE @ret int";
strSQL += "EXEC @ret=eware_get_identity_id 'cases'";
strSQL += "INSERT cases";
strSQL += "(case_caseid, Case_Description, Case_PrimaryCompanyId)";
strSQL += "VALUES (@ret, 'abc',10)";
var myQuery = CRM.CreateQueryObj(strSQL,"");
myQuery.ExecSQL()
Sage CRM v7.2
var strSQL = "INSERT INTO Cases";
strSQL += "(Case_Description, Case_PrimaryCompanyId)";
strSQL += "VALUES ('abc',10)")";
var myQuery = CRM.CreateQueryObj(strSQL,"");
myQuery.ExecSQL()
NOTE: Upgraded Systems will still use old ID mechanism.
Oracle and Inserts
No equivalent stored procedure in Oracle so
there is no safe way of getting ids directly.
SEQUENCES are used to control the identities.
Sequence created in the CRM database for
each table with the name
TablePrefix_SEQUENCE
Eg comp_sequence, pers_sequence etc.
Cases and CaseProgress share the same sequence as do
Opportunity and OpportunityProgress.
Next value from a sequence obtained by calling
the NextVal function of the sequence.
Returns the next val and increments the
sequence.
SELECT Comp_Sequence.NextVal from DUAL;
NOTE: Does not take into account the
rep_ranges table, and therefore is not advised
SOLO allocates Ranges to client machines
Held in Rep_ranges table.
When not using API object you will need to check that data
is not going out of range.
The process is as follows
Look up Range_RangeStart and Range_RangeEnd in
Rep_Ranges where Range_TableID is the identifier of the
table. TableID is 5 for the Company table.
Call SELECT Comp_Sequence.NEXTVAL FROM DUAL to
get the next sequence number.
If the sequence range is outside the range found in step 1
(which it will be because you dropped the sequence and
recreated with a sequence start of 1), then assume that this
range has been filled up, so move to the next available
range.
Ranges are allocated in blocks of 50000, so the first range
is 8001 to 58000, the second range is 58001 to 108000, the
third range is 108001 to 158000, and so on.
Problems may arise if more than 1 process is
trying to allocate a new range at the same time.
NOTE: Do not run process when CRM is
running.
NOTE: Accessing the ranges table at the back
end is not supported.
CRM Components & Database
Type
System Variables available in component allows
the Install to be checked.
Write components to accommodate database type.
sViewText="CREATE VIEW vMyPhone AS SELECT";
if (iDatabase == IOracle)
{
sViewText = sViewText + " Phon_CountryCode || N ' ' ||Phon_AreaCode ||
N ' ' || Phon_Number";
//iDatabase - returns the current installed database.
// Constants returned
//
ISQLServer
//
IOracle
//
//
//
//
//
e.g.
if (iDatabase == IOracle)
{
//Do this;
}
}
else
{
sViewText = sViewText + "RTRIM(ISNULL(Phon_CountryCode, '')) + ' '
+RTRIM(ISNULL (Phon_AreaCode, '')) + ' ' +
RTRIM(ISNULL(Phon_Number, ''))";
}
sViewText = sViewText + " AS Phon_FullNumber, Phone.* FROM Phone ";
if (iDatabase == IOracle)
{
sViewText = sViewText + ", Custom_Captions WHERE LOWER
(TRIM(Phon_Type))= LOWER(TRIM(Capt_Code(+))) AND";
}
else
{
sViewText = sViewText + "LEFT JOIN Custom_Captions ON Phon_Type =
Capt_Code WHERE";
}
sViewText = sViewText +" Phon_Deleted IS NULL";
AddView("vMyPhone", "Phone", "This selects all of the phone numbers",
sViewText, false, false, false, false, false);
Meta Data and Physical Data Types
ALLOWED DATABASE CHANGES
Business Rules in Database
CRM makes use of physical data types but meta data ‘adds value’
Custom_edits
See Developer Guide reference for discussion of EntryBlock.EntryType property in API
CRM DOES NOT use constraints within database.
When installing CRM the only SQL rule that is written into the tables structure is whether
the Primary Key column is NOT NULL.
All constraints implemented in application layer.
CRM FieldTypes & SQL Server
Datatypes
CRM Field Type
Product
IntelligentSelect
Multiselect
DateOnly
Currency
Currency_CID
SearchSelectAdvanced
Minutes
CurrencySymbols
Text
StoredProc
Checkbox
PhoneNumber
MultilineText
emailaddress
WWWURL
Selection
UserSelect
TeamSelect
Integer
Numeric
DateTime
Data Type
int
nvarchar
nvarchar
Datetime
Numeric
Int
Int
Int
Int
nvarchar
nvarchar
nvarchar
nvarchar
ntext
nvarchar
nvarchar
nvarchar
Int
Int
int
numeric
datetime
How and where table
data is stored in Meta
Data
Meta Data Definitions of Tables
Custom_Databases
Custom_Table
Manages all the connections to remote databases
All passwords encrypted using encryption
algorithms
All database connections are opened at the first
CRM logon
bord_tableid
Custom_Tables
Bord_WebServiceTable enables extra objects to be
exposed to the WSDL
Custom_Edits
EntryType controls how the system displays fields
EntrySize controls how many characters can be
entered on screen (can be changed, but must be
<= column width)
Colp_System may cause fields not to appear for
selection. You can undo this, but be careful
Custom_Views
The whole system uses views to display data
Views should be maintained from within the CRM
system, never from SQL Server
SQLLite relevant for SOLO only
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
17
Address
CaseProgress
Cases
Communication
Company
Email
Library
Marketing
Notes
Opportunity
OpportunityItem
OpportunityProgress
Person
Phone
Products
Team
Basic Table Description
CREATE TABLE [dbo].[MyTable](
[mytb_mytableid] [int] NOT NULL,
[mytb_CreatedBy] [int] NULL,
[mytb_CreatedDate] [datetime] NULL,
[mytb_UpdatedBy] [int] NULL,
[mytb_UpdatedDate] [datetime] NULL,
[mytb_TimeStamp] [datetime] NULL,
[mytb_Deleted] [int] NULL,
[mytb_Secterr] [int] NULL,
[mytb_Workflowid] [int] NULL,
[mytb_Description] [nchar](30))
Create externally and then link
Not able to become full entity
Create table via interface
Advanced Customization Wizard.
Entity Wizard
All Ids managed by CRM
Tabl_Secterr for primary entities only, enforces security on that entity.
Security is discussed more later in the course
New Table and Field definition will be in custom_tables and custom_edits
Problems
Problems occur if developer introduces
Database constraints
Foreign key constraints
Checks
Other changes such as identity columns.
Holding Data in CRM
New Columns in Tables
Adding columns to Entities, Company,
Person, etc
Marketing table for Company and
Person
Rows as Columns
Key Attribute Data
– DD tables
– Target List technique for SQL
– Logs for use in screens
Data Sets
– User_settings
– Custom_sysparams
Columns versus Datasets
Most data is held within a table within columns
E.g. User_userid, user_lastname, user_firstname.
Some instances of using Datasets (or rows) to model attributes
User Preferences
– Held in the usersettings table as a record set
– E.g. To find a users timezone preference you would have to look in the usersettings table.
Custom_sysparams
– Hold system settings
Datasets and Customizations
Can only use simple meta data definitions for screens where data is
within a single row.
Consider Company Summary Screen and
– Companyboxlong
– Addressboxshort
– personboxshort
Require Advanced Customization for Screens and Lists for
Phone/Email screens
Custom_sysparams
User_settings
Key Attribute Profiling
Examples of creating screens not based on single rows are covered
in later part of the course
Q&A
Looking ahead to the classes
DP01: Introduction to the Development
Partner Program
DP02: Entities and the Data Model (Part 1 of
2)
DP03: Entities and the Data Model (Part 2 of
2)
DP04: Implementing Screen Based Rules
(Part 1 of 2)
DP05: Implementing Screen Based Rules
(Part 2 of 2)
DP06: Screen and User Independent
Business Rules
DP07: Workflow (Part 1 of 2)
DP08: Workflow (Part 2 of 2)
DP09: Using the API Objects in ASP Pages
(Part 1 of 2)
DP10 : Using the API Objects in ASP Pages
(Part 2 of 2)
DP11: Using the Component Manager
DP12: Programming for the Advanced Email
Manager
DP13: Using the Web Services API
DP14: Using the Web Services API (Part 2 of
2)
DP15: Coding the Web Self Service COM API
(Part 1 of 2)
DP16: Coding the Web Self Service COM API
(Part 2 of 2)
DP17: Using the .NET API (Part 1 of 2)
DP18: Using the .NET API (Part 2 of 2)