Caspio Bridge's way of working time calculation

Caspio Bridge provides you with all the MS SQL database power when you need to implement working days and hours support in your application, but require you to become acquainted with SQL language in return.

Here is Frank Zamani comments on how it can be implemented in Caspio Bridge:

"This has been done before. The process is straight forward:

1. A web form is used to enter the starting date and the number of hours the project requires.
2. Upon submission, a database trigger (custom SQL code) uses the customer’s lookup tables of holidays and working hours, and comes up with the end date, storing it on the just-inserted record.
3. The web form from step 1 loads a "Search and Report Data Page" against the record that was just submitted and displays the calculated date.

The trigger in step 2 is created by the customer and provided to us or a Caspio engineer builds it for the customer for a small fee."

In case you do not have an idea on how SQL trigger looks like, here is rough draft of one:

-- ================================
-- Author: Caspio, Inc.
-- Create date: September 3, 2009
-- Description: Calculates end date based on start date and number of hours
--  for a project.
--  Replace <MyTable>, <FromDateField>, <ProjectHoursField>,
--  <EndDateField> with the appropriate field names from your table 
-- ================================
CREATE TRIGGER [dbo].[ct_Ins_Upd_CalcDates] 
   ON  <MyTable>
   AFTER INSERT, UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

 DECLARE @PKID int,
  @FromDate_local datetime,
  @projectHours_local decimal(18,2),
  @EndDate_local datetime

 SELECT @PKID = PK_ID, 
  @FromDate_local = ISNULL(<FromDateField>,'01/01/1900'), 
  @projectHours_local = ISNULL(<ProjectHoursField>,0)
 FROM inserted

 IF @FromDate_local <> '1/1/1900'
 BEGIN
  SELECT @EndDate_local = fn_GetEndDate(@FromDate_local, @projectHours_local)

  UPDATE <MyTable>
  SET <EndDateField> = @EndDate_local
  WHERE PK_ID = @PKID
 END
END

Following clarifications should help you better understand the process of building and using such a trigger.

Which name conversions are used to reference tables and columns from the app?

"In SQL actual field names are used. This can also be done in JavaScript. In JavaScript our standard prefix that is added to all field names should be considered. Users can look at the HTML source to see the naming pattern."

If some changes were done in the app we need to change this trigger too every time it happens?

"For SQL code, the table and app can change as long as the particular fields used in the calculation or storage of the value remain intact. JavaScript is more dependent on the app."

When this trigger is created by the customer is there any tools to test or debug it from customers’ side?

"Users can run their SQL code against their account via our API to test but we don’t provide a development environment."

Summary

Caspio Bridge’s capability of executing customer’s own code on server using SQL triggers opens wide horizons for implementing complex business logic in applications. The only problem is that you should be quite familiar with SQL in order to use it to the full extent. There also one more point that draws my attention: though I don’t know how data isolation is implemented in Caspio, but I think that possibility of running own code on server could present higher security risk of unauthorized data access.