How to improve your code-notes on the revision of the code on the Falcon Space platform

In this article, we will analyze the main inaccuracies, errors that occur in the code in projects on Falcon Space.

The main part concerns the SQL code, but these tips and principles can also be translated into other languages.

Stored procedures MS SQL

Specifying variable names

Adhere to a certain standard on the project (no matter what it will be, it is important to adhere to a single standard). 

The name of a function or procedure is a verb + object (SaveFile). The name of the table is a list of entities in the plural (products).

Use subsystem prefixes in the names, for example, msg_getFile

Name tables with the subsystem prefix and in the plural ord_cartItems

We use lowerCamelCase for long names with the subsystem prefix at the beginning.

Do not give names related to time-new_topSuppliers (it is better then to give the version to the form and call topSuppliers2)

The procedure name should not be misleading. If the procedure is being read (GetItems), then there is no need to make any modifications to this entity in it (for example, we start updating the status of this entity). Another example: isDb-this translates as "This is a database", and not a sign that it is a dashboard

In any case, do not make spelling mistakes, translation errors, inaccurate reflection of the essence of the element in the names of variables and methods. An inaccurate crooked name will then drag on throughout the project, and also complicates maintenance in the future.

Working with SELECT

ID-it is better to always put the column (id) at the very beginning. 

There is no need to make multi-storey subqueries, it is better to get actively used data in several places of the request via inner join

Do not use the dbo.getUserRoles function and other heavy functions in where, order by, group by. In some cases, replace the role selection with join (otherwise it works slowly on big data).

Keep in mind that there may be more than 1 value in the subquery (at least use top 1)! 

-- you don't need to do this
where value >= (select startingPrice from ms_auctionItems where id = cast(@itemID as int))
-- instead, the option is better to use in
where userID = (select id from as_users where username=@username)  

Make it a rule to name the table briefly (especially when JOINING) and refer to it when accessing columns (this will reduce the likelihood of collisions when editing the query and when adding new columns to the database structure): 

-- bad
select name from products

--good
select p.name from products p

There is no point in using where isnull (orderID, 0) = 0, when orderID is a foreign key, orderID cannot be equal to 0:

select code from as_coupons where isnull(orderID,0)<>0)

-- you can just use OrderID is not null

Security

Be sure to check that the components have the Role fields set - this will basically cut off access to this component by the role

When checking access, rely only on @username, it is this parameter in the procedures that determines who is currently working with the system. In any case, do not rely on the business input data when verifying access (for example, on the OrderID). An attacker can change the request manually and pass someone else's OrderID.

Lines 

If part of the string is null, then the entire string will be null. Therefore, when concatenating strings into one for individual variables, use isnull

When casting types from a string, use try_cast, try_convert instead of cast and convert. This will avoid an exception (the function will return null if it is impossible to cast the type to the specified one)

The structure of the code of procedures and functions

In functions, @res denotes the output variable and it is always returned at the end of the function

Use try catch to handle unexpected situations (for example, giving some typical error result in SELECT 1)

Use iif instead of case (this is more readable and looks more compact in select queries).

Sign the output SELECT with such comments (so you will not get confused with the order of the output select)

-- SELECT 2
select 1 Result, '' Msg

When writing code, immediately put the begin end pairs. This reduces the risk of forgetting to close the paired brackets

For IF, While, always use begin end - this makes it easier to read and reduces the risk of making errors when changing the code. 

Observe indents that reflect the hierarchy of code and queries (especially in the case of complex subqueries in where or select).

Take out typical things in functions, especially if they are used in a variety of places (for example, getting a link to a post of a speaker). This will make it easier to make changes to the code in the future.

It is better to do all the processing (grinding, trim, lowercase, etc.) of input parameters at the very beginning and give a negative response to the user with bad data, and not somewhere in the depth of the procedure code. 

It is better to clean the garbage in the code of the procedure (irrelevant comments and code). A lot of garbage complicates reading and tracking the logic of the procedure.

insert/ update it is better to accompany with comments what we are doing according to business logic (Updating the current employee)

In complex incomprehensible places of the code, you either need to write a detailed comment on what is happening, but the best option is to rewrite it so that it is simple and clear what is happening in the code (possibly through the use of understandable names for intermediate variables).

If the procedure requires multiple changes in different tables, use transactions. Make them as short as possible and do data manipulation in the same order (this reduces the risk of deadlocks).

Various

You can't search for an entity by name (for example, status). The name may change. You need to search by code name (at least by id, but it will then be a "magic number"). 

select @statusDoneID = id from fin_financeStatuses where name = 'Done' -- bad

select @statusDoneID = id from fin_financeStatuses where code= 'done' -- good

We try not to use magic numbers (in extreme cases, explain them through comments). 

Trace the values through execute as_print '123', and not through insert into as_trace (...). Print data is output on the page /start

Use these comments for typical situations: 

--IMPORTANT an important change in a working project (what you need to pay attention to or a temporary solution)
--TODO - i. e., it is necessary to finish
--OPT - need to optimize in the future (potential leak)

When working with large tables, it makes sense to put some kind of limiter on data extraction (for example, top 500). Otherwise, there is a risk of extracting a very large amount of data per page, which will create an extra load on the system. 

Nested procedures should not have additional select outputs, otherwise it will affect the processing of the results of the main procedure (the order of SELECT outputs for the component). If you need data from a nested procedure, you can get it either through the OUTPUT parameter of the procedure, or through insert into table execute SP

Working with platform components 

Working with forms

In general, try to avoid super-universal forms/tables for 2+roles. As a result, it is more difficult to maintain, there are security risks (accidentally forget about handling a situation with a certain role), it is more difficult to test (we made changes for one role, and now we need to test under all roles). There is also a big risk that in the future the forms will be more and more different for different roles (and it will also be more and more difficult to link this to each other).

You don't need to put completely different entities in the data-ItemId (for example, for Role 1, the product id is passed there, and for Role 2, the order id is passed).

There is no need to hide an unnecessary form through the hide or d-none class. You need to remove it from the markup if it is not needed - after all, it still loads and takes up resources

<div class="as-form mt-3 d-none" data-code="topSuppliers" data-itemid="0"></div> 

In the form in saveItem, use the universal parameter @parameters, and not the old method with @field fields (this is more convenient for processing, especially if new fields are added).

In general, prefer local reinitialization in some container via '. pHtml' RefreshContainer, rather than a full page reload (via SuccessURL).

Working with tables

Do not insert the form snippet into a table cell. In this case, it will be initialized N times each time the table is loaded. Use modal forms or a table substring. 

When displaying columns, use isnull for the column. The feature of the component is that if there is a null in 1 row for a column, it will hide the column. Therefore, using isnull makes it possible to avoid this. 

In general, by components

Do not mix large blocks of HTML code and SQL. Simple layout can be left in SQL, and for multi-line blocks it is better to use html blocks (through procedures as_block, as_htmlBlock).

Stick to the structure of procedures for components. There is no need to radically change their structure, because this will complicate the support of such a procedure.

It is not necessary to overload the component unnecessarily(it works for different roles, for different purposes). It is easier to maintain a page / component when it has one clear purpose, where you do not need to take into account the nuances of alternative use and different modes for different roles. 

For notifications , you do not need to write the same repeat in additional as in text (it is better to leave additional empty then. Otherwise, it forces the user to look at the details, and there is the same text that he has already seen).

Working with pages

In most cases, instead of /order?ItemId=12, you can write the address /order/12. The address always starts with a slash!

Layout and working with HTML

Keep track of the hierarchy of titles and the structure of pages aimed at promotion. The page should have 1 h1 tag and the other h2-h6 tags should define the document skeleton and the hierarchy of content on the page (for example, you do not need to do a subsection with h2 in the section with the h3 heading). 

Specify all bootstrap adaptivity classes, not just col-md-9

<div class="col-md-9"></div> - bad
<div class="col-12 col-sm-7 col-md-9 col-lg-10"></div> - good

Do not use the col-xs-12 class (you should use col-12 instead). The total amount in the row should be 12 (not 10). 

Do not use images and other resources as paths to a completely different site. Refer only to the resources that belong to your project. This reduces the project's dependence on external changes.

Addresses to local resources and pages should always start with a slash. For example: /product

<div class="as-form mt-3 d-none" data-code="topSuppliers" data-itemid="0"></div> 

Do not create custom left styles (for buttons, etc.). Use only standard bootstrap buttons. Make the most of the standard bootstrap styles. Use CSS only for additional positioning. And it is better to perform styling by generating a new site theme. 

Never use inline styles directly in the markup. Use the Bootstrap classes to the maximum. If you need to style something else through CSS, then we hang a unique class on the element (with a prefix by subsystem) and write a style for this class in the CSS of the page or global CSS.

Never change the global styles for some local task (this may negatively affect the markup of other pages). Change the CSS only for your custom classes. 

Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started

SQL-tool for creating personal accounts on the site

The essence of the approach and the history of the creation of Falcon Space
Web platform for creating personal accounts

Falcon Space Platform

This is a reduction in the cost of ownership

at the expense of fewer people to support

This is a quick change

while using the program

This is a modern interface

full adaptation for mobile devices

MS SQL web applications. Affiliate program for developers and web studios

You can develop yourself or collaborate with us for web development on the Falcon Space platform using only SQL and HTML.
View examples with SQL code
Platform documentation
Working on MS SQL Server
Note