程序员的创业与企业级应用开发指南 - 第 5 篇
程序员的创业与企业级应用开发指南
引言
这是我讲述创办 SplendidCRM Software, Inc. 系列专栏的第五篇。我希望我的创业经历能给你带来启发。在我看来，创办一家公司可以是一次美妙的冒险。
第 5 篇
每个月，我都会接到一两通来自希望我使用他们外包服务的公司的电话或邮件。邮件很容易处理，因为我只需要把邮件拖到垃圾邮件文件夹就行了。电话则难处理得多，因为作为一家公司，你必须礼貌地接听电话，并且要假设来电者是一名潜在客户。虽然我可以在确定对方是外包服务商后挂断电话，但我通常会礼貌地表示我们不使用任何外包服务。为了清晰起见，我在这里定义一下对我来说“外包”的含义。我将“外包”定义为使用任何外部资源来执行任何任务。通过这个定义，我特意将那些自称为“外包商”的公司和自称为“人员配置增强公司”的公司合并在一起。
作为一名开发人员，我观察到许多公司将软件开发外包出去，结果却损害了整个项目。问题在于，决策者认为开发人员是一种商品，廉价的外包开发人员可以像昂贵的内部开发人员一样高效。当然，这是不正确的。就像世界上其他任何事物一样，一分钱一分货。
作为一名企业主，我的理论很简单：不要外包你的核心竞争力。更具体地说，如果你是一家软件公司，你不应该聘请顾问来开发你的软件。我并不是说所有外包都是坏事，只是你必须聪明地对待它。例如，一家小型软件公司聘请自己的律师或全职会计师是没有多大意义的。我甚至会进一步说，如果图形和艺术作品不是项目中的关键部分，它们也可以外包。但是，如果你正在创建一个游戏，而艺术作品对游戏的成功至关重要，那么外包艺术作品就没有意义了。
我不喜欢将开发外包出去的想法，因为开发人员对应用程序的了解最多。让他们去培训一个开发人员，让他创造一些“秘方”，然后让他消失，再也联系不上，这真的有意义吗？除了培训新开发人员来取代第一个开发人员是一种浪费成本之外，真正的痛苦在于新开发人员试图理解“秘方”。质量保证 (QA) 专业人员也是如此。培训某人如何测试你的应用程序，然后在合作结束时让所有培训付诸东流，这真的有意义吗？
那么，为什么外包的开发人员会离开呢？原因有很多。首先，你可能会因为认为项目已经完成（软件永远不会完成，它只会达到可以分发的阶段）而停止向外包公司付款。其次，开发人员可能会转向其他项目。第三，开发人员可能会在一家重视开发人员的公司找到全职工作。归根结底，你没有任何机会鼓励开发人员留下。
由全职员工进行开发的好处是，你将拥有一位致力于项目成功的同事。全职员工知道，如果他/她做得不好，项目可能会失败，工作可能会结束，甚至公司可能会破产。因此，员工有动力做好工作。而外包的开发人员几乎没有动力。如果项目失败，他会被分配到另一个项目。这份工作永远不会结束，因为外包公司总是在招聘开发人员，无论他们的才能如何。
为了平滑地从业务讨论过渡到技术讨论，我将以调度作为主题。外包相当于人员的调度，而我的技术讨论将是关于应用程序任务的调度。
CRON
CRON 大约在 30 年前起源于 Unix。其目标是提供一种简单而灵活的方式来安排重复性任务。CRON 语法允许你指定每分钟、每小时、每天或每周日运行任务。你可以指定每月 1 号凌晨 2 点运行任务。
CRON 语法是一组由空格或双冒号分隔的 5 个字段（SplendidCRM 使用双冒号语法）。第一个字段是任务运行的分钟，第二个字段是任务运行的小时，第三个字段是月份中的日期，第四个字段是月份，第五个字段是星期中的日期。分钟字段的范围是 0 到 59，但也可以包含连字符表示分钟范围，或者分钟可以逗号分隔。小时字段非常相似，但范围必须是 0 到 23。月份中的日期范围是 1 到 31，月份范围是 1 到 12，星期中的日期范围是 0 到 6，其中 0 表示星期日。所有字段都可以接受星号 (*) 来匹配任何字段值。以下是 CRON 的一个好参考：http://www.adminschoice.com/docs/crontab.htm。
我在这里的目标不是教你如何使用 CRON，而是向你展示我如何将 CRON 实现为一个 SQL 函数。考虑到这一点，在我深入讲解实现之前，我将提供一些 CRON 字符串示例。
CRON 任务频率 *::*::*::*::* 每分钟运行一次 0::*::*::*::* 每小时运行一次，在整点 0,15,30,45::*::*::*::* 每 15 分钟运行一次 0::8::*::*::2,5 每周二和周五早上 8 点运行 0::4::1::*::* 每月 1 号凌晨 4 点运行 0::23::*::*::1-6 周一至周六晚上 11 点运行
我希望 CRON 逻辑能够放在数据库中，这样我就可以创建一个简单的 select 语句来返回需要运行的任务列表。这是 select 语句在代码中的样子。
select *
from vwSCHEDULERS_Run
order by NEXT_RUN
实际上，视图要复杂一些。首先，我检查计划任务是否处于活动状态，并且是否在正确的日期和时间范围内。下一个条件是我们的 CRON 逻辑。你会注意到我们向下舍入到最近的 5 分钟间隔。这样做的原因是，将每小时的数据库查询次数从 60 次减少到 12 次。（在 SplendidCRM 中，没有哪个计划任务重要到必须每分钟运行一次。）最后一个过滤器是为了确保我们不会在同一 5 分钟间隔内多次运行任务。
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))
CRON 函数可以分解为 6 个部分。第一部分将 string 解析为 CRON 使用的五个字段（分钟、小时、月份中的日期、月份、星期中的日期）。其余五个部分处理五个字段中的每一个。
解析相对简单。我初始化将用于存储五个字段的五个变量，然后逐个解析输入中的五个字段。
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;
CRON 函数的核心是其余五个部分，但它们都遵循相似的模式。首先，有一个快速测试来确定字段是否包含星号 (*)，这意味着该字段可以被忽略。然后，它遍历字段中的每个逗号分隔的值。最后，如果值包含连字符 (-)，它会在起始值和结束值之间进行迭代。代码看起来很复杂，但当你把每一步拆开来看，并不难理解。这一切都归结为对指定值和当前值之间的比较。对于月份字段，我们检查指定的任何值是否与当前月份匹配，如果匹配，我们就继续验证剩余的字段。如果不匹配，我们就尽快从函数返回。
我们的模式有一个例外。对于月份中的日期字段，值为 31 对月末有特殊含义。这需要额外的逻辑来首先确定月份的最后一天，并用最后一天纠正该值。
/*
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
希望大家喜欢这个系列的第五篇文章。请留意未来几周发布的第 6 篇文章。