A Programmer's Guide to Starting a Software Company and Building an Enterprise Application - Article 5
A Programmer's Guide to Starting a Software Company and Building an Enterprise Application
Introduction
This is the fifth in a series of columns in which I tell you how I started SplendidCRM Software, Inc. I hope that my entrepreneurial experience inspires you. In my opinion, the creation of a company can be a wonderful adventure.
Article 5
Every month, I get a couple of calls or emails from companies that want me to use their outsourcing services. The emails are easy to handle because all I have to do is to drag the email to the spam folder. The phone calls are much harder because, as a business, you have to answer the phone politely and you have to assume that the caller is a potential customer. While I could hang-up once I determine that he/she is an outsourcer, I generally prefer to politely say that we don't use any outsourcing services. And just so that I am clear, I will provide a definition here of what outsourcing means to me. I define "outsourcing" as the use of any outside resource to perform any duty. With this definition, I have deliberately combined companies that call themselves "outsourcers" and companies that call themselves "staffing augmentation companies".
As a developer, I've observed several companies outsource their software development to the detriment of their entire projects. The problem is that the decision makers assume that developers are a commodity and that a cheaper, outsourced, developer can be just as productive as an expensive, in-house developer. This, of course, is not true. Like everything else in the world, you get what you pay for.
As a business owner, my theory is simple: don't outsource your core competency. To put it in more concrete terms, if you have a software company, you should not hire consultants to develop your software. I'm not implying that all outsourcing is bad, just that you must be smart about it. For example, there is little sense for a small software company to hire its own lawyer or even a full-time accountant. I would even go further to say that graphics and artwork can be outsourced if these parts of the project are not critical to the project. However, if you are creating a game where the artwork is critical to the success of the game, then it does not make sense to outsource the artwork.
I don't like the idea of outsourcing to developers because they are the ones who have the most knowledge about the application. Does it really make sense to train a developer, have him create some "secret sauce", and then have him go away, never to be heard of again? Beside the fact that training a new developer to replace the first is a wasted cost, the real pain comes when the new developer tries to understand the "secret sauce". The same holds true for quality-assurance (QA) professionals. Does it really make sense to train someone on how to test your application, only to have all that training go out the window when the engagement ends?
So why would the outsourced developer go away? There are many reasons. First, you could stop paying the outsourcing company because of your notion that the project is complete (software is never complete, it simply reaches a stage where it can be distributed). Second, the developer could move on to other projects. Third, the developer could get a full-time job at a company that values its developers. The bottom line is that you have no opportunity to encourage the developer to stay.
The advantage of a full-time employee doing the development is that you will have a colleague who is committed to the success of the project. The full-time employee knows that if he/she does a bad job, the project may fail, the job may end, and even the company may go bankrupt. So the employee has an incentive to do a great job. For the outsourced developer, little incentive exists. If the project fails, he will be assigned to another project. The job will never end because outsourcing companies are always hiring developers, regardless of their talent.
In an attempt to smooth the transition from business discussion to technical discussion, I'm going to follow scheduling as a theme. Whereas outsourcing amounts to the scheduling of people, my technical discussion will be about scheduling application tasks.
CRON
CRON had its origins in Unix roughly 30 years ago. Its goal was to provide a simple, yet flexible, way to schedule re-occurring tasks. The CRON syntax allows you to specify that a task be run every minute, every hour, every day, or every Sunday. You can specify that a task be run on the 1st of every month at 2 AM.
The CRON syntax is a set of 5 fields separated by spaces or double colons (SplendidCRM uses the double-colon syntax). The first field is the minute that the task is to run, the second field is the hour the task is to run, the third field is the day of the month, the fourth is the month, and the fifth is the day of the week. The minutes field has a range from 0 to 59, but it can also include a hyphen to imply a range of minutes, or the minutes can be comma separated. The hour field is very similar, but the range must be 0 to 23. The day of month has a range of 1 to 31, the month has a range of 1 to 12 and the day of week has a range of 0 to 6, with 0 meaning Sunday. All fields can accept an asterisk (*) to match any field value. The following is a good reference for CRON: http://www.adminschoice.com/docs/crontab.htm.
My goal here is not to teach you how to use CRON, but instead to show you how I implemented CRON as a SQL function. With this in mind, I will give you a few sample CRON strings before I dive into the implementation.
CRON Task Frequency *::*::*::*::* Run every minute 0::*::*::*::* Run every hour, at the top of the hour 0,15,30,45::*::*::*::* Run every 15 minutes 0::8::*::*::2,5 Run at 8 AM every Tuesday and Friday 0::4::1::*::* Run at 4 AM on the first of the month 0::23::*::*::1-6 Run at 11 PM Monday through Saturday
I wanted the CRON logic in the database so that I could create a simple select statement that would return a list of jobs that need to be run. Here is what the select statement looks like in the code:
select *
from vwSCHEDULERS_Run
order by NEXT_RUN
Under the covers, the view is a bit more complicated. First, I check to see if the scheduled task is active, and if it is within the right date and time range. The next condition is our CRON logic. One thing you will notice is that we round the time down to the nearest 5-minute interval. The reason for this is to reduce the number of database queries from 60 per hour to 12 per hour. (In SplendidCRM, no scheduled task is so critical that it must run every minute.) The last filter is to ensure that we don't run the task multiple times within the same 5 minute interval.
select vwSCHEDULERS.*
, dbo.fnTimeRoundMinutes(getdate(), 5) as NEXT_RUN
from vwSCHEDULERS
where STATUS = N'Active'
and (DATE_TIME_START is null or getdate() > DATE_TIME_START)
and (DATE_TIME_END is null or getdate() < DATE_TIME_END )
and (TIME_FROM is null or getdate() > _
(dbo.fnDateAdd_Time(TIME_FROM, dbo.fnDateOnly(getdate()))))
and (TIME_TO is null or getdate() < _
(dbo.fnDateAdd_Time(TIME_TO , dbo.fnDateOnly(getdate()))))
and dbo.fnCronRun(JOB_INTERVAL, dbo.fnTimeRoundMinutes(getdate(), 5), 5) = 1
and (LAST_RUN is null or dbo.fnTimeRoundMinutes(getdate(), 5) > _
dbo.fnTimeRoundMinutes(LAST_RUN, 5))
The CRON function can be broken down into 6 sections. The first section parses the string into the five fields used by CRON (minute, hour, date of month, month, day of week). The remaining five sections process each of the five fields.
The parsing is relatively simple. I initialize the five variables that will be used to store the five fields, then I proceed to parse the input for each of the five fields.
set @CurrentPosR = 1;
-- Minute
if @CurrentPosR <= len(@CRON) begin -- then
set @NextPosR = charindex('::', @CRON, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON) + 1;
end -- if;
set @CRON_MINUTE = substring(@CRON, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 2;
end -- if;
The heart of the CRON function is the remaining five sections, but they all follow a similar pattern. First, there is the quick test to determine if a field contains an asterisk (*), which means that the field can be ignored. Then it loops through each comma-separated value in the field. Finally, if the value contains a hyphen (-), it loops between start and end values. The code looks very complicated, but when you take apart each step, it is not hard to follow. It all boils down to a compare between the value specified and the current value. For the month field, we check if any of the values specified match the current month, and if there is, we continue validating the remaining fields. If there is no match, then we return from the function as quickly as possible.
There is one exception to the pattern that we have established. For the day-of-month field, a value of 31 has a special meaning to be the last day of the month. This requires additional logic to first determine the last day of the month and to correct the value with the last day.
/*
Field Descriptions:
minute hour dayOfMonth month dayOfWeek
where:
minute values range from 0 to 59
hour values range from 0 to 23
dayOfMonth values range from 1 to 31
month values range from 1 to 12
dayOfWeek values range from 0 to 6, with 0 meaning Sunday
Field Values:
NUM A single value
NUM-NUM A range of values
NUM,NUM-NUM,... A comma separated list of values or ranges
(remember no spaces after commas!)
* wildcard, meaning match all possible values
*/
-- 12/31/2007 Paul. Round the minutes down to the nearest divisor.
-- We must round down the minutes because the current time will be round down.
Create Function dbo.fnCronRun(@CRON_INPUT nvarchar(100), _
@CURRENT_TIME datetime, @MINUTE_DIVISOR_INPUT int)
returns bit
with encryption
as
begin
declare @CRON nvarchar(100);
declare @MINUTE_DIVISOR int;
declare @CurrentPosR int;
declare @NextPosR int;
declare @CRON_TEMP nvarchar(100);
declare @CRON_MONTH nvarchar(100);
declare @CRON_DAYOFMONTH nvarchar(100);
declare @CRON_DAYOFWEEK nvarchar(100);
declare @CRON_HOUR nvarchar(100);
declare @CRON_MINUTE nvarchar(100);
declare @CRON_VALUE nvarchar(100);
declare @CRON_VALUE_START nvarchar(100);
declare @CRON_VALUE_END nvarchar(100);
declare @CRON_VALUE_INT int;
declare @CRON_VALUE_START_INT int;
declare @CRON_VALUE_END_INT int;
-- 01/01/2008 Paul. We need a failsafe int that will help ensure
-- that a loop never exceed its limit.
-- For example, the months loop should not exceed 12 iterations,
-- a day loop should not exceed 31,
-- an hour loop should not exceed 24 and a minute loop should not exceed 60.
declare @FAIL_SAFE_INT int;
declare @CURRENT_MONTH int;
declare @CURRENT_DAYOFMONTH int;
declare @CURRENT_LASTDAYOFMONTH int;
declare @CURRENT_WEEK int;
declare @CURRENT_DAYOFWEEK int;
declare @CURRENT_HOUR int;
declare @CURRENT_MINUTE int;
declare @MATCH_CURRENT_MONTH bit;
declare @MATCH_CURRENT_DAYOFMONTH bit;
declare @MATCH_CURRENT_DAYOFWEEK bit;
declare @MATCH_CURRENT_HOUR bit;
declare @MATCH_CURRENT_MINUTE bit;
-- 08/26/2008 Paul. Parameters are read-only in PostgreSQL,
-- so @CRON and @MINUTE_DIVISOR need to be a local variable.
set @CRON = @CRON_INPUT;
set @MINUTE_DIVISOR = @MINUTE_DIVISOR_INPUT;
-- 12/30/2007 Paul. Exit early if everything is possible.
if charindex(' ', @CRON) > 0 begin -- then
set @CRON = replace(@CRON, ' ', '');
----print 'Remove spaces';
end -- if;
if @CURRENT_TIME is null begin -- then
----print 'Current date/time not specified';
return 0;
end else if @CRON is null or @CRON = '' or @CRON = '*::*::*::*::*' begin -- then
----print 'Current pattern matches everything';
return 1;
end -- if;
if @MINUTE_DIVISOR is null or @MINUTE_DIVISOR < 1 begin -- then
set @MINUTE_DIVISOR = 5;
end -- if;
--print 'CRON ' + @CRON;
--print 'Current Time ' + convert(varchar(30), @CURRENT_TIME, 101) +
--' ' + convert(varchar(30), @CURRENT_TIME, 114);
-- 12/31/2007 Paul. If the values are not specified, then assume everything.
set @CRON_MONTH = '*';
set @CRON_DAYOFMONTH = '*';
set @CRON_DAYOFWEEK = '*';
set @CRON_HOUR = '*';
set @CRON_MINUTE = '*';
set @CurrentPosR = 1;
-- Minute
if @CurrentPosR <= len(@CRON) begin -- then
set @NextPosR = charindex('::', @CRON, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON) + 1;
end -- if;
set @CRON_MINUTE = substring(@CRON, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 2;
end -- if;
-- Hour
if @CurrentPosR <= len(@CRON) begin -- then
set @NextPosR = charindex('::', @CRON, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON) + 1;
end -- if;
set @CRON_HOUR = substring(@CRON, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 2;
end -- if;
-- Day of Month
if @CurrentPosR <= len(@CRON) begin -- then
set @NextPosR = charindex('::', @CRON, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON) + 1;
end -- if;
set @CRON_DAYOFMONTH = substring(@CRON, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 2;
end -- if;
-- Month
if @CurrentPosR <= len(@CRON) begin -- then
set @NextPosR = charindex('::', @CRON, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON) + 1;
end -- if;
set @CRON_MONTH = substring(@CRON, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 2;
end -- if;
-- Day of Week
if @CurrentPosR <= len(@CRON) begin -- then
set @NextPosR = charindex('::', @CRON, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON) + 1;
end -- if;
set @CRON_DAYOFWEEK = substring(@CRON, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 2;
end -- if;
set @MATCH_CURRENT_MONTH = 1;
set @CURRENT_MONTH = datepart(month, @CURRENT_TIME);
--print 'Current Month ' + cast(@CURRENT_MONTH as varchar(10));
if @CRON_MONTH is not null and @CRON_MONTH <> '*' begin -- then
set @CurrentPosR = 1;
set @CRON_TEMP = @CRON_MONTH;
set @MATCH_CURRENT_MONTH = 0;
while @CurrentPosR <= len(@CRON_TEMP) and @MATCH_CURRENT_MONTH = 0 begin -- do
set @NextPosR = charindex(',', @CRON_TEMP, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON_TEMP) + 1;
end -- if;
set @CRON_VALUE = substring(@CRON_TEMP, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 1;
set @NextPosR = charindex('-', @CRON_VALUE);
if @NextPosR is not null and @NextPosR > 0 begin -- then
set @CRON_VALUE_START = substring(@CRON_VALUE, 1, @NextPosR - 1);
set @CRON_VALUE_END = substring(@CRON_VALUE, @NextPosR + 1, _
len(@CRON_VALUE) - @NextPosR);
if @CRON_VALUE_START is not null and isnumeric(@CRON_VALUE_START) = 1 _
and @CRON_VALUE_END is not null and isnumeric(@CRON_VALUE_END) = 1 begin -- then
set @CRON_VALUE_START_INT = cast(@CRON_VALUE_START as int);
set @CRON_VALUE_END_INT = cast(@CRON_VALUE_END as int);
----print '@CRON_VALUE_START = ' + cast(@CRON_VALUE_START_INT as varchar(10));
----print '@CRON_VALUE_END = ' + cast(@CRON_VALUE_END_INT as varchar(10));
if @CRON_VALUE_START_INT is not null and _
@CRON_VALUE_END_INT is not null begin -- then
set @FAIL_SAFE_INT = 0;
set @CRON_VALUE_INT = @CRON_VALUE_START_INT;
while @FAIL_SAFE_INT < 12 and @CRON_VALUE_INT <= _
@CRON_VALUE_END_INT and @MATCH_CURRENT_MONTH = 0 begin -- do
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = @CURRENT_MONTH begin -- then
--print '@CURRENT_MONTH between @CRON_VALUE_START_INT and @CRON_VALUE_END_INT';
set @MATCH_CURRENT_MONTH = 1;
end -- if;
set @FAIL_SAFE_INT = @FAIL_SAFE_INT + 1;
set @CRON_VALUE_INT = @CRON_VALUE_INT + 1;
end -- while;
end -- if;
end -- if;
end else begin
if @CRON_VALUE is not null and isnumeric(@CRON_VALUE) = 1 begin -- then
set @CRON_VALUE_INT = cast(@CRON_VALUE as int);
----print '@CRON_VALUE_INT = ' + cast(@CRON_VALUE_INT as varchar(10));
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = @CURRENT_MONTH begin -- then
--print '@CRON_VALUE_INT = @CURRENT_MONTH';
set @MATCH_CURRENT_MONTH = 1;
end -- if;
end -- if;
end -- if;
end -- while;
-- 12/31/2007 Paul. Exit early if we can confirm that there is no match.
-- This will save CPU cycles.
if @MATCH_CURRENT_MONTH = 0 begin -- then
--print '@MATCH_CURRENT_MONTH failed';
return 0;
end -- if;
end -- if;
set @MATCH_CURRENT_DAYOFMONTH = 1;
set @CURRENT_DAYOFMONTH = datepart(day, @CURRENT_TIME);
--print 'Current DayOfMonth ' + cast(@CURRENT_DAYOFMONTH as varchar(10));
-- 12/31/2007 Paul. Last Day of Month seems expensive,
-- so only compute if necessary, when value specified = 31.
--set @CURRENT_LASTDAYOFMONTH = datepart(day, dateadd_
--(day, -1, dateadd(month, 1, dateadd(day, 1 - @CURRENT_DAYOFMONTH, @CURRENT_TIME))));
----print 'Current LastDayOfMonth ' + cast(@CURRENT_LASTDAYOFMONTH as varchar(10));
if @CRON_DAYOFMONTH is not null and @CRON_DAYOFMONTH <> '*' begin -- then
set @CurrentPosR = 1;
set @CRON_TEMP = @CRON_DAYOFMONTH;
set @MATCH_CURRENT_DAYOFMONTH = 0;
while @CurrentPosR <= len(@CRON_TEMP) and @MATCH_CURRENT_DAYOFMONTH = 0 begin -- do
set @NextPosR = charindex(',', @CRON_TEMP, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON_TEMP) + 1;
end -- if;
set @CRON_VALUE = substring(@CRON_TEMP, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 1;
set @NextPosR = charindex('-', @CRON_VALUE);
if @NextPosR is not null and @NextPosR > 0 begin -- then
set @CRON_VALUE_START = substring(@CRON_VALUE, 1, @NextPosR - 1);
set @CRON_VALUE_END = substring(@CRON_VALUE, @NextPosR + 1, _
len(@CRON_VALUE) - @NextPosR);
if @CRON_VALUE_START is not null and isnumeric(@CRON_VALUE_START) = 1 _
and @CRON_VALUE_END is not null and isnumeric(@CRON_VALUE_END) = 1 begin -- then
set @CRON_VALUE_START_INT = cast(@CRON_VALUE_START as int);
set @CRON_VALUE_END_INT = cast(@CRON_VALUE_END as int);
----print '@CRON_VALUE_START = ' + cast(@CRON_VALUE_START_INT as varchar(10));
----print '@CRON_VALUE_END = ' + cast(@CRON_VALUE_END_INT as varchar(10));
if @CRON_VALUE_START_INT is not null and _
@CRON_VALUE_END_INT is not null begin -- then
set @FAIL_SAFE_INT = 0;
set @CRON_VALUE_INT = @CRON_VALUE_START_INT;
while @FAIL_SAFE_INT < 31 and @CRON_VALUE_INT <= _
@CRON_VALUE_END_INT and @MATCH_CURRENT_DAYOFMONTH = 0 begin -- do
-- 12/31/2007 Paul. The value 31 has a special meaning,
-- it means the last day of the month
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = 31 begin -- then
if @CURRENT_LASTDAYOFMONTH is null begin -- then
set @CURRENT_LASTDAYOFMONTH = datepart(day, dateadd(day, -1, _
dateadd(month, 1, dateadd(day, 1 - @CURRENT_DAYOFMONTH, @CURRENT_TIME))));
--print 'Current LastDayOfMonth ' + _
cast(@CURRENT_LASTDAYOFMONTH as varchar(10));
end -- if;
if @CRON_VALUE_INT > @CURRENT_LASTDAYOFMONTH begin -- then
set @CRON_VALUE_INT = @CURRENT_LASTDAYOFMONTH;
end -- if;
end -- if;
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = _
@CURRENT_DAYOFMONTH begin -- then
--print '@CURRENT_DAYOFMONTH between @CRON_VALUE_START_INT and
--@CRON_VALUE_END_INT';
set @MATCH_CURRENT_DAYOFMONTH = 1;
end -- if;
set @FAIL_SAFE_INT = @FAIL_SAFE_INT + 1;
set @CRON_VALUE_INT = @CRON_VALUE_INT + 1;
end -- while;
end -- if;
end -- if;
end else begin
if @CRON_VALUE is not null and isnumeric(@CRON_VALUE) = 1 begin -- then
set @CRON_VALUE_INT = cast(@CRON_VALUE as int);
----print '@CRON_VALUE_INT = ' + cast(@CRON_VALUE_INT as varchar(10));
-- 12/31/2007 Paul. The value 31 has a special meaning,
-- it means the last day of the month
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = 31 begin -- then
if @CURRENT_LASTDAYOFMONTH is null begin -- then
set @CURRENT_LASTDAYOFMONTH = datepart(day, dateadd(day, -1, _
dateadd(month, 1, dateadd(day, 1 - @CURRENT_DAYOFMONTH, @CURRENT_TIME))));
--print 'Current LastDayOfMonth ' + cast(@CURRENT_LASTDAYOFMONTH as varchar(10));
end -- if;
if @CRON_VALUE_INT > @CURRENT_LASTDAYOFMONTH begin -- then
set @CRON_VALUE_INT = @CURRENT_LASTDAYOFMONTH;
end -- if;
end -- if;
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = _
@CURRENT_DAYOFMONTH begin -- then
--print '@CRON_VALUE_INT = @CURRENT_DAYOFMONTH';
set @MATCH_CURRENT_DAYOFMONTH = 1;
end -- if;
end -- if;
end -- if;
end -- while;
-- 12/31/2007 Paul. Exit early if we can confirm that there is no match.
-- This will save CPU cycles.
if @MATCH_CURRENT_DAYOFMONTH = 0 begin -- then
--print '@MATCH_CURRENT_DAYOFMONTH failed';
return 0;
end -- if;
end -- if;
set @MATCH_CURRENT_DAYOFWEEK = 1;
set @CURRENT_WEEK = datepart(week, @CURRENT_TIME);
--print 'Current Week ' + cast(@CURRENT_WEEK as varchar(10));
set @CURRENT_DAYOFWEEK = datepart(weekday, @CURRENT_TIME) - 1;
--print 'Current DayOfWeek ' + cast(@CURRENT_DAYOFWEEK as varchar(10));
if @CRON_DAYOFWEEK is not null and @CRON_DAYOFWEEK <> '*' begin -- then
set @CurrentPosR = 1;
set @CRON_TEMP = @CRON_DAYOFWEEK;
set @MATCH_CURRENT_DAYOFWEEK = 0;
while @CurrentPosR <= len(@CRON_TEMP) and @MATCH_CURRENT_DAYOFWEEK = 0 begin -- do
set @NextPosR = charindex(',', @CRON_TEMP, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON_TEMP) + 1;
end -- if;
set @CRON_VALUE = substring(@CRON_TEMP, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 1;
set @NextPosR = charindex('-', @CRON_VALUE);
if @NextPosR is not null and @NextPosR > 0 begin -- then
set @CRON_VALUE_START = substring(@CRON_VALUE, 1, @NextPosR - 1);
set @CRON_VALUE_END = substring(@CRON_VALUE, @NextPosR + 1, _
len(@CRON_VALUE) - @NextPosR);
if @CRON_VALUE_START is not null and isnumeric(@CRON_VALUE_START) = 1 _
and @CRON_VALUE_END is not null and isnumeric(@CRON_VALUE_END) = 1 begin -- then
set @CRON_VALUE_START_INT = cast(@CRON_VALUE_START as int);
set @CRON_VALUE_END_INT = cast(@CRON_VALUE_END as int);
----print '@CRON_VALUE_START = ' + cast(@CRON_VALUE_START_INT as varchar(10));
----print '@CRON_VALUE_END = ' + cast(@CRON_VALUE_END_INT as varchar(10));
if @CRON_VALUE_START_INT is not null and @CRON_VALUE_END_INT _
is not null begin -- then
set @FAIL_SAFE_INT = 0;
set @CRON_VALUE_INT = @CRON_VALUE_START_INT;
while @FAIL_SAFE_INT < 7 and @CRON_VALUE_INT <= _
@CRON_VALUE_END_INT and @MATCH_CURRENT_DAYOFWEEK = 0 begin -- do
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = _
@CURRENT_DAYOFWEEK begin -- then
--print '@CURRENT_DAYOFWEEK between @CRON_VALUE_START_INT
--and @CRON_VALUE_END_INT';
set @MATCH_CURRENT_DAYOFWEEK = 1;
end -- if;
set @FAIL_SAFE_INT = @FAIL_SAFE_INT + 1;
set @CRON_VALUE_INT = @CRON_VALUE_INT + 1;
end -- while;
end -- if;
end -- if;
end else begin
if @CRON_VALUE is not null and isnumeric(@CRON_VALUE) = 1 begin -- then
set @CRON_VALUE_INT = cast(@CRON_VALUE as int);
----print '@CRON_VALUE_INT = ' + cast(@CRON_VALUE_INT as varchar(10));
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = _
@CURRENT_DAYOFWEEK begin -- then
--print '@CRON_VALUE_INT = @CURRENT_DAYOFWEEK';
set @MATCH_CURRENT_DAYOFWEEK = 1;
end -- if;
end -- if;
end -- if;
end -- while;
-- 12/31/2007 Paul. Exit early if we can confirm that there is no match.
-- This will save CPU cycles.
if @MATCH_CURRENT_DAYOFWEEK = 0 begin -- then
--print '@MATCH_CURRENT_DAYOFWEEK failed';
return 0;
end -- if;
end -- if;
set @MATCH_CURRENT_HOUR = 1;
set @CURRENT_HOUR = datepart(hour, @CURRENT_TIME);
--print 'Current Hour ' + cast(@CURRENT_HOUR as varchar(10));
if @CRON_HOUR is not null and @CRON_HOUR <> '*' begin -- then
set @CurrentPosR = 1;
set @CRON_TEMP = @CRON_HOUR;
set @MATCH_CURRENT_HOUR = 0;
while @CurrentPosR <= len(@CRON_TEMP) and @MATCH_CURRENT_HOUR = 0 begin -- do
set @NextPosR = charindex(',', @CRON_TEMP, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON_TEMP) + 1;
end -- if;
set @CRON_VALUE = substring(@CRON_TEMP, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 1;
set @NextPosR = charindex('-', @CRON_VALUE);
if @NextPosR is not null and @NextPosR > 0 begin -- then
set @CRON_VALUE_START = substring(@CRON_VALUE, 1, @NextPosR - 1);
set @CRON_VALUE_END = substring(@CRON_VALUE, @NextPosR + 1, _
len(@CRON_VALUE) - @NextPosR);
if @CRON_VALUE_START is not null and isnumeric(@CRON_VALUE_START) = 1 _
and @CRON_VALUE_END is not null and isnumeric(@CRON_VALUE_END) = 1 begin -- then
set @CRON_VALUE_START_INT = cast(@CRON_VALUE_START as int);
set @CRON_VALUE_END_INT = cast(@CRON_VALUE_END as int);
----print '@CRON_VALUE_START = ' + cast(@CRON_VALUE_START_INT as varchar(10));
----print '@CRON_VALUE_END = ' + cast(@CRON_VALUE_END_INT as varchar(10));
if @CRON_VALUE_START_INT is not null and _
@CRON_VALUE_END_INT is not null begin -- then
set @FAIL_SAFE_INT = 0;
set @CRON_VALUE_INT = @CRON_VALUE_START_INT;
while @FAIL_SAFE_INT < 24 and @CRON_VALUE_INT <= _
@CRON_VALUE_END_INT and @MATCH_CURRENT_HOUR = 0 begin -- do
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = @CURRENT_HOUR begin -- then
--print '@CURRENT_HOUR between @CRON_VALUE_START_INT and @CRON_VALUE_END_INT';
set @MATCH_CURRENT_HOUR = 1;
end -- if;
set @FAIL_SAFE_INT = @FAIL_SAFE_INT + 1;
set @CRON_VALUE_INT = @CRON_VALUE_INT + 1;
end -- while;
end -- if;
end -- if;
end else begin
if @CRON_VALUE is not null and isnumeric(@CRON_VALUE) = 1 begin -- then
set @CRON_VALUE_INT = cast(@CRON_VALUE as int);
----print '@CRON_VALUE_INT = ' + cast(@CRON_VALUE_INT as varchar(10));
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = @CURRENT_HOUR begin -- then
--print '@CRON_VALUE_INT = @CURRENT_HOUR';
set @MATCH_CURRENT_HOUR = 1;
end -- if;
end -- if;
end -- if;
end -- while;
-- 12/31/2007 Paul. Exit early if we can confirm that there is no match.
-- This will save CPU cycles.
if @MATCH_CURRENT_HOUR = 0 begin -- then
--print '@MATCH_CURRENT_HOUR failed';
return 0;
end -- if;
end -- if;
set @MATCH_CURRENT_MINUTE = 1;
set @CURRENT_MINUTE = datepart(minute, @CURRENT_TIME);
--print 'Current Minute ' + cast(@CURRENT_MINUTE as varchar(10));
if @CRON_MINUTE is not null and @CRON_MINUTE <> '*' begin -- then
set @CurrentPosR = 1;
set @CRON_TEMP = @CRON_MINUTE;
set @MATCH_CURRENT_MINUTE = 0;
while @CurrentPosR <= len(@CRON_TEMP) and @MATCH_CURRENT_MINUTE = 0 begin -- do
set @NextPosR = charindex(',', @CRON_TEMP, @CurrentPosR);
if @NextPosR = 0 or @NextPosR is null begin -- then
set @NextPosR = len(@CRON_TEMP) + 1;
end -- if;
set @CRON_VALUE = substring(@CRON_TEMP, @CurrentPosR, @NextPosR - @CurrentPosR);
set @CurrentPosR = @NextPosR + 1;
set @NextPosR = charindex('-', @CRON_VALUE);
if @NextPosR is not null and @NextPosR > 0 begin -- then
set @CRON_VALUE_START = substring(@CRON_VALUE, 1, @NextPosR - 1);
set @CRON_VALUE_END = substring(@CRON_VALUE, @NextPosR + 1, _
len(@CRON_VALUE) - @NextPosR);
if @CRON_VALUE_START is not null and isnumeric(@CRON_VALUE_START) = 1 _
and @CRON_VALUE_END is not null and isnumeric(@CRON_VALUE_END) = 1 begin -- then
set @CRON_VALUE_START_INT = cast(@CRON_VALUE_START as int);
set @CRON_VALUE_END_INT = cast(@CRON_VALUE_END as int);
----print '@CRON_VALUE_START = ' + cast(@CRON_VALUE_START_INT as varchar(10));
----print '@CRON_VALUE_END = ' + cast(@CRON_VALUE_END_INT as varchar(10));
if @CRON_VALUE_START_INT is not null and @CRON_VALUE_END_INT _
is not null begin -- then
set @FAIL_SAFE_INT = 0;
set @CRON_VALUE_INT = @CRON_VALUE_START_INT;
while @FAIL_SAFE_INT < 60 and @CRON_VALUE_INT <= _
@CRON_VALUE_END_INT and @MATCH_CURRENT_MINUTE = 0 begin -- do
-- 12/31/2007 Paul. Round the minutes down to the nearest divisor.
set @CRON_VALUE_INT = @CRON_VALUE_INT - (@CRON_VALUE_INT % @MINUTE_DIVISOR);
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = @CURRENT_MINUTE begin -- then
--print '@CURRENT_MINUTE between @CRON_VALUE_START_INT and @CRON_VALUE_END_INT';
set @MATCH_CURRENT_MINUTE = 1;
end -- if;
-- 01/01/2008 Paul. It is extremely important that we increment
-- by the amount of the minute divisor.
-- Otherwise we would enter an endless loop where we increment,
-- but then round down.
set @FAIL_SAFE_INT = @FAIL_SAFE_INT + 1;
set @CRON_VALUE_INT = @CRON_VALUE_INT + @MINUTE_DIVISOR;
end -- while;
end -- if;
end -- if;
end else begin
if @CRON_VALUE is not null and isnumeric(@CRON_VALUE) = 1 begin -- then
set @CRON_VALUE_INT = cast(@CRON_VALUE as int);
-- 12/31/2007 Paul. Round the minutes down to the nearest divisor.
set @CRON_VALUE_INT = @CRON_VALUE_INT - (@CRON_VALUE_INT % @MINUTE_DIVISOR);
----print '@CRON_VALUE_INT = ' + cast(@CRON_VALUE_INT as varchar(10));
if @CRON_VALUE_INT is not null and @CRON_VALUE_INT = @CURRENT_MINUTE begin -- then
--print '@CRON_VALUE_INT = @CURRENT_MINUTE';
set @MATCH_CURRENT_MINUTE = 1;
end -- if;
end -- if;
end -- if;
end -- while;
-- 12/31/2007 Paul. Exit early if we can confirm that there is no match.
-- This will save CPU cycles.
if @MATCH_CURRENT_MINUTE = 0 begin -- then
--print '@MATCH_CURRENT_MINUTE failed';
return 0;
end -- if;
end -- if;
-- 12/31/2007 Paul. We should have already exited
-- if we do not match the current day/time. The goal is to save CPU cycles.
----print 'Match Current Month ' + cast(@MATCH_CURRENT_MONTH as varchar(10));
----print 'Match Current DayOfMonth ' + cast(@MATCH_CURRENT_DAYOFMONTH as varchar(10));
----print 'Match Current DayOfWeek ' + cast(@MATCH_CURRENT_DAYOFWEEK as varchar(10));
----print 'Match Current Hour ' + cast(@MATCH_CURRENT_HOUR as varchar(10));
----print 'Match Current Minute ' + cast(@MATCH_CURRENT_MINUTE as varchar(10));
--if @MATCH_CURRENT_MONTH = 0 or @MATCH_CURRENT_DAYOFMONTH = 0 or _
@MATCH_CURRENT_DAYOFWEEK = 0 or @MATCH_CURRENT_HOUR = 0 or _
@MATCH_CURRENT_MINUTE = 0 begin -- then
-- ----print 'At least one item did not match';
-- return 0;
--end -- if;
--print 'CRON matched!';
return 1;
end
GO
Grant Execute on dbo.fnCronRun to public;
GO
I hope that you have enjoyed this fifth article in the series. Please watch for article 6 within the next few weeks.