Coverage for mindsdb / integrations / handlers / salesforce_handler / constants.py: 100%
2 statements
« prev ^ index » next coverage.py v7.13.1, created at 2026-01-21 00:36 +0000
1"""
2Constants for Salesforce handler.
3"""
6def get_soql_instructions(integration_name):
7 return f"""This handler executes SOQL (Salesforce Object Query Language), NOT SQL! Follow these rules strictly:
9**BASIC STRUCTURE:**
10- NO "SELECT *" - must explicitly list all fields
11 SQL: SELECT * FROM Account;
12 SOQL: SELECT Id, Name, Industry FROM Account
13- NO table aliases - use full table names only
14 SQL: SELECT a.Name FROM Account a;
15 SOQL: SELECT Name FROM Account
16- NO column aliases - field names cannot be aliased
17 SQL: SELECT Name AS CompanyName FROM Account;
18 SOQL: SELECT Name FROM Account
19- NO DISTINCT keyword - not supported in SOQL
20 SQL: SELECT DISTINCT Industry FROM Account;
21 SOQL: Not possible - use separate logic
22- NO subqueries in FROM clause - only relationship-based subqueries allowed
23 SQL: SELECT * FROM (SELECT Name FROM Account) AS AccountNames;
24 SOQL: Not supported
25- Do not use fields that are not defined in the schema or data catalog. Always reference exact field names.
27**FIELD SELECTION:**
28- Always include Id field when querying
29 CORRECT: SELECT Id, Name, Industry FROM Account
30 INCORRECT: SELECT Name, Industry FROM Account
31- Field names are case-sensitive
32 CORRECT: SELECT CreatedDate FROM Account
33 INCORRECT: SELECT createddate FROM Account
34- Use exact field names from the data catalog
35 CORRECT: SELECT CustomerPriority__c FROM Account
36 INCORRECT: SELECT customer_priority FROM Account
38**FILTERING (WHERE clause):**
39- Date/DateTime fields: Use unquoted literals in YYYY-MM-DD or YYYY-MM-DDThh:mm:ssZ format
40 CORRECT: WHERE CloseDate >= 2025-05-28
41 CORRECT: WHERE CreatedDate >= 2025-05-28T10:30:00Z
42 INCORRECT: WHERE CloseDate >= '2025-05-28'
43 INCORRECT: WHERE CreatedDate >= "2025-05-28"
44- Special date literals: TODAY, YESTERDAY, LAST_WEEK, LAST_MONTH, LAST_QUARTER, LAST_YEAR, THIS_WEEK, THIS_MONTH, THIS_QUARTER, THIS_YEAR
45 CORRECT: WHERE CreatedDate = TODAY
46 CORRECT: WHERE LastModifiedDate >= LAST_MONTH
47 CORRECT: WHERE CloseDate >= THIS_QUARTER
48- Date arithmetic (e.g., TODAY - 10) is not supported. Use literals like LAST_N_DAYS:10 instead.
49 CORRECT: WHERE CloseDate >= LAST_N_DAYS:10
50 INCORRECT: WHERE CloseDate >= TODAY - 10
51- LIKE operator: Only supports % wildcard, NO underscore (_) wildcard
52 CORRECT: WHERE Name LIKE '%Corp%'
53 CORRECT: WHERE Name LIKE 'Acme%'
54 INCORRECT: WHERE Name LIKE 'A_me%'
55- BETWEEN operator: NOT supported, use >= AND <= instead
56 SQL: WHERE CreatedDate BETWEEN '2025-01-01' AND '2025-12-31'
57 SOQL: WHERE CreatedDate >= 2025-01-01 AND CreatedDate <= 2025-12-31
58- Boolean values: Use lowercase true/false, NOT TRUE/FALSE
59 CORRECT: WHERE Active__c = true
60 CORRECT: WHERE IsDeleted = false
61 INCORRECT: WHERE Active__c = TRUE
62 INCORRECT: WHERE IsDeleted = FALSE
63- NULL values: Use lowercase null, NOT NULL
64 CORRECT: WHERE ParentId = null
65 CORRECT: WHERE Description != null
66 INCORRECT: WHERE ParentId IS NULL
67 INCORRECT: WHERE Description IS NOT NULL
68- String values: Use single quotes for strings
69 CORRECT: WHERE Industry = 'Technology'
70 CORRECT: WHERE Name = 'Acme Corp'
71 INCORRECT: WHERE Industry = "Technology"
72- Multi-select picklist fields: Use INCLUDES('value1;value2') or EXCLUDES('value1;value2')
73 CORRECT: WHERE Services__c INCLUDES ('Consulting;Support')
74 CORRECT: WHERE Services__c EXCLUDES ('Training')
75 INCORRECT: WHERE Services__c = 'Consulting'
76- Limited subquery support - only IN/NOT IN with non-correlated subqueries in WHERE clause
77 CORRECT: SELECT Id FROM Contact WHERE Id NOT IN (SELECT WhoId FROM Task)
78 INCORRECT: SELECT Id FROM Contact WHERE NOT EXISTS (SELECT 1 FROM Task WHERE WhoId = Contact.Id)
80**JOINS:**
81- NO explicit JOIN syntax supported
82 SQL: SELECT a.Name, c.FirstName FROM Account a JOIN Contact c ON a.Id = c.AccountId
83 SOQL: Not supported - use relationship traversal (not applicable in this use case)
85**AGGREGATES:**
86- NO COUNT(*) - use COUNT(Id) instead
87 SQL: SELECT COUNT(*) FROM Account
88 SOQL: SELECT COUNT(Id) FROM Account
89- Cannot mix aggregate functions with non-aggregate fields unless using GROUP BY
90 CORRECT: SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry
91 CORRECT: SELECT COUNT(Id) FROM Account
92 INCORRECT: SELECT Industry, Name, COUNT(Id) FROM Account
93- NO GROUP_CONCAT or string aggregation functions
94 SQL: SELECT GROUP_CONCAT(Name) FROM Account
95 SOQL: Not supported
96- NO HAVING clause
97 SQL: SELECT Industry, COUNT(*) FROM Account GROUP BY Industry HAVING COUNT(*) > 5
98 SOQL: Not supported - filter with separate logic
99- GROUP BY has limited field type support
100 CORRECT: SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry
101 INCORRECT: SELECT Description, COUNT(Id) FROM Account GROUP BY Description (textarea fields not supported)
103**FUNCTIONS:**
104- Date functions: CALENDAR_MONTH(), CALENDAR_YEAR(), CALENDAR_QUARTER(), DAY_IN_MONTH(), DAY_IN_WEEK(), DAY_IN_YEAR(), HOUR_IN_DAY(), WEEK_IN_MONTH(), WEEK_IN_YEAR()
105 CORRECT: SELECT Id, Name FROM Account WHERE CALENDAR_YEAR(CreatedDate) = 2025
106 CORRECT: SELECT Id, Name FROM Account WHERE CALENDAR_MONTH(CreatedDate) = 5
107 CORRECT: SELECT Id, Name FROM Account WHERE DAY_IN_WEEK(CreatedDate) = 2
108- NO math functions: ROUND, FLOOR, CEILING, ABS, etc.
109 SQL: SELECT ROUND(AnnualRevenue, 2) FROM Account
110 SOQL: Not supported
111- NO conditional functions: CASE WHEN, COALESCE, NULLIF, etc.
112 SQL: SELECT CASE WHEN Industry = 'Technology' THEN 'Tech' ELSE 'Other' END FROM Account
113 SOQL: Not supported
114- NO string functions except INCLUDES/EXCLUDES for multi-select picklists
115 SQL: SELECT UPPER(Name) FROM Account
116 SOQL: Not supported
118**OPERATORS:**
119- Supported: =, !=, <, >, <=, >=, LIKE, IN, NOT IN, INCLUDES, EXCLUDES
120 CORRECT: WHERE Industry = 'Technology'
121 CORRECT: WHERE AnnualRevenue >= 1000000
122 CORRECT: WHERE Industry IN ('Technology', 'Finance')
123 CORRECT: WHERE Industry NOT IN ('Government', 'Non-Profit')
124 CORRECT: WHERE Services__c INCLUDES ('Consulting')
125- NOT supported: REGEXP, BETWEEN, EXISTS, NOT EXISTS
126 SQL: WHERE Name REGEXP '^[A-Z]'
127 SOQL: Not supported
129**SORTING & LIMITING:**
130- ORDER BY: Fully supported
131 CORRECT: SELECT Id, Name FROM Account ORDER BY Name ASC
132 CORRECT: SELECT Id, Name FROM Account ORDER BY CreatedDate DESC, Name ASC
133 CORRECT: SELECT Id, Name FROM Account ORDER BY Name NULLS LAST
134- LIMIT: Maximum 2000 records, use smaller limits for better performance
135 CORRECT: SELECT Id, Name FROM Account LIMIT 100
136 CORRECT: SELECT Id, Name FROM Account LIMIT 2000
137 INCORRECT: SELECT Id, Name FROM Account LIMIT 5000
138- NO OFFSET: Not supported for pagination
139 SQL: SELECT Id, Name FROM Account LIMIT 10 OFFSET 20
140 SOQL: Not supported
142**DATA TYPES:**
143- picklist: Single-select dropdown, use = operator with string values
144 CORRECT: WHERE Industry = 'Technology'
145 CORRECT: WHERE Rating = 'Hot'
146- reference: Foreign key field, typically ends with Id
147 CORRECT: WHERE OwnerId = '00530000003OOwn'
148 CORRECT: WHERE AccountId = '0013000000UzXyz'
149- boolean: Use lowercase true/false
150 CORRECT: WHERE IsDeleted = false
151 CORRECT: WHERE Active__c = true
152- currency: Numeric field for money values
153 CORRECT: WHERE AnnualRevenue > 1000000
154 CORRECT: WHERE AnnualRevenue >= 500000.50
155- date: Date only, use YYYY-MM-DD format
156 CORRECT: WHERE LastActivityDate = 2025-05-28
157 CORRECT: WHERE SLAExpirationDate__c >= 2025-01-01
158- datetime: Date and time, use YYYY-MM-DDThh:mm:ssZ format
159 CORRECT: WHERE CreatedDate >= 2025-05-28T10:30:00Z
160 CORRECT: WHERE LastModifiedDate = 2025-05-28T00:00:00Z
161- double/int: Numeric fields
162 CORRECT: WHERE NumberOfEmployees > 100
163 CORRECT: WHERE NumberofLocations__c >= 5.5
164- string/textarea: Text fields, use single quotes
165 CORRECT: WHERE Name = 'Acme Corporation'
166 CORRECT: WHERE Description = 'Leading tech company'
167- phone/url/email: Specialized string fields, treat as strings
168 CORRECT: WHERE Phone = '555-1234'
169 CORRECT: WHERE Website = 'https://example.com'
171**COMMON MISTAKES TO AVOID:**
172- Using SELECT * (not allowed)
173 WRONG: SELECT * FROM Account
174 RIGHT: SELECT Id, Name, Industry FROM Account
175- Quoting date literals (dates must be unquoted)
176 WRONG: WHERE CreatedDate >= '2025-01-01'
177 RIGHT: WHERE CreatedDate >= 2025-01-01
178- Using SQL JOIN syntax (not supported)
179 WRONG: SELECT Account.Name FROM Account JOIN Contact ON Account.Id = Contact.AccountId
180 RIGHT: Use relationship traversal (not applicable in this use case)
181- Using BETWEEN operator (not supported)
182 WRONG: WHERE CreatedDate BETWEEN 2025-01-01 AND 2025-12-31
183 RIGHT: WHERE CreatedDate >= 2025-01-01 AND CreatedDate <= 2025-12-31
184- Using uppercase TRUE/FALSE/NULL (must be lowercase)
185 WRONG: WHERE Active__c = TRUE
186 RIGHT: WHERE Active__c = true
187- Using underscore _ in LIKE patterns (only % supported)
188 WRONG: WHERE Name LIKE 'A_me%'
189 RIGHT: WHERE Name LIKE 'A%me%'
190- Mixing aggregate and non-aggregate fields without GROUP BY
191 WRONG: SELECT Name, COUNT(Id) FROM Account
192 RIGHT: SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry
194**EXAMPLE QUERIES:**
195- Basic selection: SELECT Id, Name, Industry FROM Account WHERE Industry = 'Technology'
196- Date filtering: SELECT Id, Name FROM Account WHERE CreatedDate >= 2025-01-01
197- Multiple conditions: SELECT Id, Name FROM Account WHERE Name LIKE '%Corp%' AND Industry IN ('Technology', 'Finance')
198- Aggregation: SELECT Industry, COUNT(Id) FROM Account GROUP BY Industry
199- Boolean and numeric: SELECT Id, Name FROM Account WHERE Active__c = true AND NumberOfEmployees > 100
200- Date functions: SELECT Id, Name FROM Account WHERE CALENDAR_YEAR(CreatedDate) = 2025
201- Null checks: SELECT Id, Name FROM Account WHERE ParentId = null
202- Multi-select picklist: SELECT Id, Name FROM Account WHERE Services__c INCLUDES ('Consulting;Support')
203- Sorting and limiting: SELECT Id, Name FROM Account ORDER BY Name ASC LIMIT 50
206***EXECUTION INSTRUCTIONS. IMPORTANT!***
207After generating the core SOQL (and nothing else), always make sure you wrap it exactly as:
209 SELECT *
210 FROM {integration_name}(
211 /* your generated SOQL goes here, without a trailing semicolon */
212 )
214Return only that wrapper call.
215"""