Script Development Rules and Restrictions
Rules for Checking Script Performance
When you use the script editor, read the following content carefully to understand the rules for checking script performance. In both standard and advanced pages, the performance of scripts referenced by the page will also be analyzed as part of the overall page performance analysis. If there are any performance issues, a dialog box is displayed. For details about how to analyze the performance of standard and advanced pages, see Checking Standard Page Performance and Checking Advanced Page Performance.
- Static check rules:
- Rule 1: Use a WHERE clause in SELECT statements.
If the SELECT statement lacks query conditions, the script performance may be poor due to a large number of query results. If the number of query results is too large, add restrictions or use pagination query. For details about pagination query, see the following recommended code example.
Negative code example:import * as db from 'db'; let errorDemo = db.sql().exec("select object_name from object_demo order by createddate desc")
Positive code example:import * as db from 'db'; let correctDemo = db.sql().exec("select object_name from object_demo where id = ? order by createddate desc")
Recommended code example:import * as db from 'db'; let se = db.sql() let sqlResult = [] for(let i=0; i <1000; i++){ let query_sql = se.exec("select object_name from object_demo limit ${5000*i},5000") sqlResult.push(query_sql) if(query_sql.length<5000){ break } }
- Rule 2: Limit query results to under 5,000 rows in a SELECT statement.
The number of results in a single SELECT query must be below the platform's maximum limit of 5,000 rows. If the number of query results is too large, add restrictions or use pagination query. For details about pagination query, see the following recommended code example.
Negative code example:import * as db from 'db'; let errorDemo = db.sql().exec("select object_name from object_demo where object_type = 'Test' limit 100000")
Positive code example:import * as db from 'db'; let correctDemo = db.sql().exec("select object_name from object_demo where object_type = 'Test' limit 3000")
Recommended code example:import * as db from 'db'; let se = db.sql() let sqlResult = [] for(let i=0; i <1000; i++){ let query_sql = se.exec(`select object_name from object_demo limit ${5000*i},5000`) sqlResult.push(query_sql) if(query_sql.length<5000){ break } }
- Rule 3: Use range query conditions in SELECT statements cautiously.
If range queries ("<>", "<", ">", ">=", "<=") are used in SELECT statements may result in poor script performance due to the large number of results returned. If range queries are necessary, add a LIMIT clause to restrict the number of results and ensure query efficiency.
Negative code example:import * as db from 'db'; let errorDemo = db.sql().exec("select object_name from object_demo where object_num > 50")
Positive code example:import * as db from 'db'; let correctDemo = db.sql().exec("select object_name from object_demo where object_num > 50 limit 3000")
- Rule 4: Ensure query fields are indexed in SELECT statements.
If the fields used in WHERE conditions are not indexed, verify whether an index should be created for these fields to improve query efficiency.
Negative code example:import * as db from 'db'; let errorDemo = db.sql().exec("select object_name from object_demo where object_id = ?")
No index is created for object_id in the object_demo table.
Positive code example:import * as db from 'db'; let correctDemo = db.sql().exec("select object_name from object_demo where id = ?")
An index is created for id in the object_demo table.
- Rule 5: Avoid querying data from four or more tables simultaneously in a SELECT statement.
When you perform multi-table joint queries in SELECT statements, limit the number of tables to no more than three. If you need to query data in four or more tables, associate with fewer than four tables before performing joint queries based on the query result. Ensure that the number of tables in each join query is less than four to ensure the query efficiency.
Negative code example:import * as db from 'db'; let errorDemo = db.sql().exec("select a.item as item1, b.item as item2, c.item as item3 ,d.item as item4, e.item as itmem5 from object_demo1 as a object_demo2 as b, object_demo3 as c ,object_demo4 as d, object_demo5 as e where a.id=b.objectid and b.name = c.objectid and c.name = d.objectid and d.name = e.objectid and e.name ='Test'")
Positive code example:import * as db from 'db'; let sqlResult1 = db.sql().exec("select c.item as item3, d.item as item4, e.item as itmem5 from object_demo3 as c ,object_demo4 as d, object_demo5 as e where c.name = d.objectid and d.name = e.objectid and e.name ='Test'") let sqlResult = [] let item3s = "'"+sqlResult1.map(item=>{item.item3}).join("','")+"'" let sqlResult2 = db.sql().exec('select a.item as item1, b.item as item2, c.item as item3 from object_demo1 as a object_demo2 as b, object_demo3 as c where a.id=b.objectid and b.name = c.objectid and c.item in ('+item3s+')') for(let i in sqlResult1){ for(let j in sqlResult2){ if i.item3 == j.item3: sqlResult.append({ "item1":j.item1, "item2":j.item2, "item3":i.item3, "item4":i.item4, "item5":i.item5 }) } }
- Rule 6: Use aliases for aggregate functions in SELECT statements.
In SELECT statements, aliases must be used in aggregate functions to store query results. Otherwise, compatibility issues may occur due to inconsistent results returned by aggregate functions.
Negative code example:import * as db from 'db'; let errorDemo = db.sql().exec("select count(*) from object_demo where object_name = 'Test'")
Positive code example:import * as db from 'db'; let correctDemo = db.sql().exec("select count(*) as count from object_demo where object_name = 'Test'")
Recommended code example:***Aggregate function examples**** select count(*) as count_res, select max(*) as max_res, select min(*) as min_res, select avg(*) as avg_res, select sum(*) as sum_res
- Rule 7: Do not use query statements in the format of Select From... in SELECT statements.
Do not use query statements in the format of select .... Specify the fields of the select statement.
Negative code example:import * as db from 'db'; let errorDemo = db.sql().exec("select from object_demo where object_name = 'test'")
Positive code example:import * as db from 'db'; let correctDemo = db.sql().exec("select id, object_type from object_demo where object_name = 'test'")
- Rule 8: Use input parameter variables cautiously in SELECT statements.
Exercise caution when using input parameter for values concatenated in SELECT statements to avoid SQL injection risks.
Negative code example:import * as db from 'db'; let errorDemo = "select id,name from object_demo where id = "; errorDemo += input.parameter let errorDemoResult = db.sql().exec(errorDemo)
input.parameter is the input parameter of the script.
Positive code example:import * as db from 'db'; let correctDemo = "select id,name from object_demo where id = ?"; let correctDemoResult = db.sql().exec(correctDemo, { params: [input.parameter] })
input.parameter is the input parameter of the script.
- Rule 9: Use SELECT statements in the for loop cautiously.
Exercise caution when using the SELECT statements in the for loop. Otherwise, memory overflow may occur due to a large amount of data during the result assignment.
Negative code example:import * as db from 'db'; for (let i = 0; i < input.para.length; i++) { db.sql().exec("select id,name from object_demo where id = Test'"); }
Positive code example:import * as db from 'db'; db.sql().exec("select id,name from object_demo where id = 'Test'");
- Rule 10: Use order by cautiously in SELECT statements.
If you need to use order by in SELECT statements, add an index for the sorting field to ensure the query efficiency. If indexes cannot be added, check whether the query performance is poor.
Negative code example:import * as db from 'db'; let errorDemo = db.sql().exec("select object_name from object_demo where object_id = 'Test' Orde by createdDate")
No index is created for createdDate in the object_demo table.
Positive code example:import * as db from 'db'; let correctDemo = db.sql().exec("select object_name from object_demo where id = 'Test' Order by createdDate")
An index is created for createdDate in the object_demo table.
- Rule 1: Use a WHERE clause in SELECT statements.
- Dynamic check rules:
When the script is running, the dynamic check is triggered. Dynamic check rules are based on static check rules (SQL injection and SQL items in the for loop cannot be checked).
- If there are any performance issues, a dialog box is displayed.
- If there is no performance issue, no dialog box is displayed.
Script Development Specifications and Restrictions
When you use the script editor to write code, pay close attention to the following guidelines.
- Naming
All name definitions must reflect their functions. Do not use abbreviations (except proper nouns).
- The script is named in lower camel case, for example, createDeviceInstance.
- The structure is named in upper camel case, for example, QueryPaymentResult.
- Fields in the structure are named in lower camel case, for example, customerName.
- Classes, enumerated values, and APIs are named in upper camel case.
class Comments {...} interface Console {...} enum Direction { Up = 1, Down, Left, Right}
- Functions are named in lower camel case.
addOfferingAssociatePriceAndStock(input: Input): Output {...}
- Attributes or variables are named in lower camel case.
class Comments { userId: String; content: String; } let oneComments = newComments();
If the variable is an odd number, the name contains the object name, for example, level1Catalog and level2Catalog. If the variable is a plural, the name contains the set name, for example, attributeRelationRuleList.
- Script definition rules
- The type, label, description, required, and isCollection fields must be defined. The fields with default values are optional.
- If the field is of the set type, define it as [].
@action.param({ type: "Attribute", label: "Attribute", description: "attributeList", required:false isCollection: true }) attributeList: Attribute[];
- When @action.object is used, you need to clearly define the object in the script and do not reference the object of other scripts.
- If the structure is nested, the granularity decreases from bottom to top.
export class ProductObject { //ignore } export class StockObject { //ignore } export class ProductStock { @action.param({ type: 'Struct', label: "ProductObject", isCollection: false }) productInfomation: ProductObject; @action.param({ type: 'Struct', label: "StockObject", isCollection: false }) stock: StockObject; } @action.object({ type: "param" }) export class Input { @action.param({ type: 'String', label: "productId", isCollection: false }) productId: String; }
- You do not need to define multiple Output objects. You can directly use the defined object output parameters in the method.
Recommendation:
@action.object({ type: "param" }) export class Input { @action.param({ type: 'String', label: "productId", isCollection: false }) productId: String; } @action.object({ type: "param" }) export class getProductStock { @action.method({ input: "Input", output: "ProductStock", label: 'getProductStock' }) getProductStock(input: Input): ProductStock {
Not recommended: If the output parameter object contains an object imported from an external system, define the object in this mode.
@action.object({ type: "param" }) export class Input { @action.param({ type: 'String', label: "productId", isCollection: false }) productId: String; } @action.object({ type: "param" }) export class Output { @action.param({ type: 'Object', isCollection: false }) ProductStock: ProductStock; } @action.object({ type: "param" }) export class getProductStock { @action.method({ input: "Input", output: "Output", label: 'getProductStock' }) getProductStock(input: Input): Output {
- New and modified scripts do not return result codes or information unless there are specific requests from the service.
- Principles and formats of script comments
Unnecessary code cannot exist in the form of comments. Do not add comments for code description. Script comments should be as concise as possible. It is recommended that comments be in English. Comments are not required for input and output parameters.
- Add comments to the metadata description of the function and structure.
/** * Query product details by product ID */ @action.method({ input: "Input", output: "Output", label: 'queryProductDetailForCart' })
- Comments must be added before key service statements in a method.
- A single-line comment in a method starts with slashes (//) and must be placed above or on the right of related code. If the comment is placed above the code, separate the comment from the previous code with a blank line.
- No fixed standard dictates the right number of comments compared to code. If you can remove all the code and still understand the method's purpose through the comments alone—as if they were simple instructions—then your comments are well-placed.
//Check whether the password is correct let password = input.password; if (accountRecord["Password"] != password) { error.name = "CM-001003"; error.message = "Invalid loginId or password."; throw error; }
- Add comments to the metadata description of the function and structure.
- Rules for mutual reference between scripts
- Omit references to non-essential standard libraries or objects in the script.
For example, if no methods from the sys module are used, do not include the following statement:
import * as sys from 'sys';
If only the CA_PC_Offering object is used, delete other objects in the example statement.
@useObject(['CA_PC_FeeItemDef', 'CA_PC_Offering', 'CA_PC_CatalogOffering', 'CA_PC_Catalog', 'CA_PC_OfferingAttribute'])
- Only the used objects are referenced, and import * is not required.
import { OfferingObjectQuery } from './ca_pc__getOfferingObject'; import { setI18nError } from 'context'; import { sql } from 'db';
Not recommended:
import * as queryOfferingObjectAction from './ca_pc__getOfferingObject'; import * as context from 'context'; import * as db from 'db';
- @action.method is a mandatory method annotation and is written above the method.
@action.method({ input: "Input", output: "Output", label: 'queryClassification' }) queryClassification(input: Input): Output { ... }
- To facilitate script calling on the page, only the required objects are exported at the end of the script.
For example, the last line in the getOfferingObject script exports the OfferingObjectQuery object:
export let theAction = new OfferingObjectQuery();
- Common object definitions can be placed in some common scripts (such as pc_XXX.ts and cm_XXX.ts) by category. Other required scripts can be directly referenced.
- Omit references to non-essential standard libraries or objects in the script.
- Syntax rules
- Check whether the object is empty.
if(object){ //ignore }
Example of a collection object:
if(collection&&collection.length!=0){ //ignore }
- The number type is defined as Number, and the date type is defined as Date.
@action.param({ type: "Date", label: "effectiveTime", description: "Date." }) effectiveTime: Date; @action.param({ type: "Number", label: "salePrice", description: "salePrice." }) salePrice: number;
- When you define a variable or array, specify the type and add generics to the collection.
let isDone:Boolean = false; let decLiteral : number =6; let productList = new Array<productObject>();
- Use forEach for traversal loops instead of for... in.
testArray.forEach((value, index, array )=>{ //ignore });
- Use let instead of var for declaration.
Recommendation:
let offeringId : String = "aaa";
Not recommended:
var offeringId = "aaa";
- If the code has object declaration, use .fieldName instead of ['fieldName'] to obtain object fields.
Recommendation:
offeringStruct.id = result[i].base.offeringId;
Not recommended:
offeringStruct.id = result[i]['base']['offeringId'];
- If the default value is required, use "||" instead of if.
function(value){ //Use||Check whether the value is empty value = value || "hello" }
- Use the new method to create a known object.
Recommendation:
let offeringIdRequest = new OfferingIdRequest(); offeringIdRequest.catalogList = catalogList; offeringIdRequest.classificationList:=classificationList; let getOfferingIdByConditionInput = { "OfferingIdRequest": offeringIdRequest }
Not recommended:
let getOfferingIdByConditionInput = { "OfferingIdRequest": { "catalogList": catalogList, "classificationList": classificationList, "status": "", "stockCondition": "", "keyWord": "", "offset": "", "limit": "" } }
- Function syntax
let traitRec = function(xxxx,xxx) { //ignore }
This function can be used only in the statement after the function is defined.
- Use undefined instead of null to declare variables without initial values.
let object = undefined;
- Local variables must be defined in the class. Do not define types or values in the global namespace (that is, do not define variables outside the class). Constants can be defined globally.
let identityIdList = [];
- Use the lambda expression to replace the anonymous function.
Enclose the parameters of the arrow function only when necessary. The following is an example of correctly using arrow:
x => x + x (x,y) => x + y <T>(x: T, y: T) => x === y
- Check whether the object is empty.
- Rules for SQL statements in scripts
- You are not advised concatenating SQL statements to avoid injection risks.
let sql = "select id,name,ExternalCode,FeeType,FeeItemName,FeeItemDescription,Remark,Status from CA_PC_FeeItemDef where 1=1"; if (!InputParams.id && !InputParams.name && !InputParams.feeType && !InputParams.feeItemName && !InputParams.status) { context.setI18nError("ca_pc__001013"); return; } if (InputParams.id) { sql += "and id ='" + InputParams.id + "'" }
- Use either the sql.exec() or sql.excute() method for complex queries involving multiple tables. Compared with the exec() method, the excute() method returns more fields and the number of successful operations.
let result = execsql.exec("select id,name,OfferingId,ParentId,SkuCode,ChannelId,Status,ProductLabel,PriceCode,DefaultChoose,PaymentType from CA_PC_Product where id in (" + str + ")", { params: productId });
For complex queries involving multiple tables, constructing SQL queries by string concatenation is the only method available, but there are certain limitations.
- If str comes from an input parameter, it must be validated before concatenating it into the SQL to prevent SQL injection attacks. If str is derived from internal data, validation may not be necessary.
- You are not advised to directly concatenate input parameters in SQL statements. Instead, placeholders should be used within the SQL, and the actual input parameters should be supplied in an array to the query execution method.
attriSql = "select AttrDef from DE_DeviceDefAttri where DeviceDef=? and ExternalCode=? and AttrType='DYNAMIC' and ValueType='1'"; attriRecords = db.sql().exec(attriSql, { params: [deviceDefId, defExternalCode] });
- For single-table queries and add, delete, and modify SQL operations, use the Orm interface method.
The Options parameter allows you to specify return fields, sorting, aggregation functions, and pagination. If a value is not provided, it defaults to a null type.
let CA_PC_Stock = db.object('CA_PC_Stock'); let amountCount = 0; if (UpdateStock.amount) { let record = { Amount: UpdateStock.amount }; amountCount = CA_PC_Stock.updateByCondition({ "conjunction": "AND", "conditions": [{ "field": "SkuCode", "operator": "eq", "value": UpdateStock.skuCode }] }, record); }
- Do not call methods or operate the database in a loop. You can use in to query the result in the collection.
let productIdList = input.productId; let str = ""; let arr = []; for (let i = 0; i < productIdList.length; i++) { arr[i] = "?"; } str = arr.toString(); let result = execsql.exec("select id,name,OfferingId,ParentId,SkuCode,ChannelId,Status,ProductLabel,PriceCode,DefaultChoose,PaymentType from CA_PC_Product where id in (" + str + ")", { params: productIdList });
- When optimizing SQL statements, use fields with indexes.
- When performing batch operations on the database, use the encapsulated batch operation APIs.
For example, Orm.batchInsert, Orm.batchUpdate, and Orm.deleteByCondition. If parent and child object records are created in batches and the records are in a complete transaction, and all the records are successful or fail to be created, you are advised to use Orm.compositeInsert.
var s = db.object('Customer__CST'); var records = []; var record = { "name": "hello", "count__CST": 123, "Contacts": { "records": [ { "name": "hello_contact1" }, { "name": "hello_contact2" }, ] } }; records.push(record); var ids = s.compositeInsert(records); console.log("id list = ", ids); count = s.count(); console.log("record count = ", count);
- like is recommended for match query, and < and > are recommended for date comparison.
Recommendation:
select id from t where name like 'abc%' select id from t where createdate>='2005-11-30' and createdate< '2005-12-1'
Not recommended:
select id from t where substring(name,1,3)='abc' select id from t where datediff(day,createdate, '2005-11-30')
- Replace in with exists and replace not in with not exists.
Recommendation:
SELECT * FROM EMP (Basic table) WHERE EMPNO > 0 AND EXISTS (SELECT'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')
Not recommended:
SELECT * FROM EMP(Basic table) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')
- Do not use is null or is not null in an index column. Otherwise, the index becomes invalid.
Recommendation:
SELECT ... FROM DEPARTMENT WHERE DEPT_CODE >=0
Not recommended:
SELECT ... FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL
- Notes
- Avoid using the asterisk (*) in the SELECT clause.
- Avoid using the != or <> operators in the WHERE clause.
- Avoid using functions on fields in the WHERE clause.
- Avoid using !=, ||, and + in SQL statements.
- Avoid filtering data within the WHERE conditions.
- Avoid sorting by fields in queries.
- Avoid using multi-table joins and nested queries; do not use more than two tables in a join.
- Avoid repeatedly using the same condition for queries within a loop; handle common data retrieval outside the loop. For example, fetch common data once outside the loop.
- Avoid making multiple queries with the same condition in different methods of the same script; consider defining a class member variable.
- Avoid joint query.
In most cases, the conditions for joins are unique and can be pre-queried separately. For example, use common data as subsequent conditions to prevent join queries.
- Avoid frequent database interaction.
To efficiently query large datasets, such as 5,000 records, avoid making multiple database interactions within a loop. Instead, combine all possible conditions into one query executed outside the loop. Then, iterate over the result set to fetch data.
- Use objects for temporary caching.
For example, once a DeviceDef is queried, store it in a cache using the format id:Object. For subsequent queries, retrieve it directly from the cache.
- You are not advised concatenating SQL statements to avoid injection risks.
- Script code style restrictions
- End each statement with a semicolon. After writing the script, right-click and select Format Document to standardize formatting.
- Use double quotes for string assignments and single quotes for field retrieval.
- Keep related code together and separate unrelated logic with blank lines.
- Always enclose loops and conditional statements braces ({}).
- Place the left brace ({) on the same line as the statement.
- Avoid spaces before commas in parentheses; include a space after colons and semicolons.
for (var i = 0, n = str.length; i < 10; i++) { } if (x < 10) { } function f(x: number, y: string): void { }
- Declare only one variable per statement.
var x = 1; var y = 2;
Over the following style:
var x = 1, y = 2;
- Place the else statement in a new line after the right brace (}).
- A function should perform only one task, even if it is a simple one; it should be encapsulated in its own method.
- Keep individual method bodies concise, ideally within 150 lines of code, to ensure readability and ease of maintenance and testing.
- Script file name extension restrictions
Scripts are stored in the database without paths, so no file extensions are needed.
Import modules without specifying an extension. If an extension is necessary, only use the .ts.
import * as circle from './circle';
- Script loop dependency restrictions
When a module is called cyclically, it may be returned when execution is incomplete. You need to carefully plan module calling to allow cyclic module dependencies to work properly within the application.
Script a:
console.log ('a starts'); exports.done = false; import * as b from 'b'; console.log ('In a, b.done =', b.done); exports.done = true; console.log ('a ends');
Script b:
console.log ('b starts'); exports.done = false; import * as a from 'a'; console.log ('In b, a.done =', a.done); exports.done = true; console.log ('b ends');
Script main:
console.log ('main starts'); import * as a from 'a'; import * as b from 'b'; console.log('In main, a.done =', a.done', b.done =', b.done);
Script main loads script a, script a loads script b. Script b then attempts to load script a again. An incomplete copy of the exports object of script a is returned to script b to prevent infinite loop. Script b loads script a and provides the exports object to script a. When script main loads the two scripts, the two scripts have both been loaded. Therefore, the output is as follows:
main starts a starts b starts In b, a.done = false b ends In a, b.done = true a ends In main, a.done=true,b.done=true
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot