#!/bin/sh
#
# This is a shell archive.  To extract its contents,
# execute this file with /bin/sh to create the file(s):
#
# README		    currentdb.spl	isleapyear.spl	    random.spl
# ages.spl	    cvt_date.spl	isnull.spl	    sign.spl
# anniversary.spl	    dayofyear.spl	iso8601_weekday.spl tounixtime.spl
# birthdays.spl	    frunixtime.spl	iso8601_weeknum.spl txstate.spl
# bittest.spl	    hoursbetween.spl	nvl_int.spl	    yyyymm.spl
# char_at.spl
#
# This shell archive created: Thu Oct 17 12:56:25 EDT 2002
#
echo "Extracting file README"
sed -e 's/^X//' <<\SHAR_EOF > README
XSPL_JL_20021010 README
X
XThis is a collection of stored procedures and functions from Jonathan Leffler
Xthat was submitted to the IIUG Software Repository on October 10, 2002.
X
XSome of the functions in this package are new to the Repository, while others
Xappear individually in Jonathan's other Repository packages.  Of those, some
Xare newer versions of their counterparts that appear elsewhere in the
XRepository as of the submission date of this package.
X
XJonathan will be working to roll these new versions into their respective
Xpackages.  In the meantime, you may want to consider incorporating these
Xversions into any of the other packages that you are currently using.
X
XWalt Hultgren
XOctober 10, 2002
SHAR_EOF
if [ `wc -c < README` -ne      704 ]
then
    echo "Lengths do not match -- Bad Copy of README"
fi
echo "Extracting file ages.spl"
sed -e 's/^X//' <<\SHAR_EOF > ages.spl
X-- @(#)$Id: ages.spl,v 1.2 2000/10/16 18:29:51 jleffler Exp $
X--
X--Date: Fri, 5 Sep 1997 11:47:08 -0700 (PDT)
X--From: Jonathan Leffler <johnl@informix.com>
X--To: "Ing. Melvin Perez Cedano" <cam@codetel.net.do>
X--Subject: Re: How to calculate a person's age?
X--X-Informix-List-Id: <list.16385>
X--
X--On Fri, 5 Sep 1997, Ing. Melvin Perez Cedano wrote:
X--> I trying to calculate how old are a person. I have the birth date in a
X--> column. I have been using a DATETIME YEAR TO YEAR variable in my program
X--> and assigning to it the result of (TODAY - birthdate) UNITS YEAR it
X--> returns NULL. [...]
X-->
X--> How have you done this?
X--
X--Well, I hadn't had to do it, but the following seems to work:
X
XCREATE TEMP TABLE t
X(
X    d1 DATETIME YEAR TO DAY NOT NULL,
X    d2 DATETIME YEAR TO DAY NOT NULL
X);
XINSERT INTO t VALUES("1997-09-05", "1990-09-04");
XINSERT INTO t VALUES("1997-09-05", "1990-09-06");
XINSERT INTO t VALUES("1997-09-05", "1990-09-05");
X
XCREATE PROCEDURE age_in_years(birthday DATETIME YEAR TO DAY,
X                refdate DATETIME YEAR TO DAY DEFAULT CURRENT YEAR TO DAY)
X        RETURNING INTERVAL YEAR(4) TO YEAR;
X
X    DEFINE rv INTERVAL YEAR(4) TO YEAR;
X    LET rv = EXTEND(birthday, YEAR TO YEAR) - EXTEND(refdate, YEAR TO YEAR);
X    IF EXTEND(birthday, MONTH TO DAY) < EXTEND(refdate, MONTH TO DAY) THEN
X        LET rv = rv - 1 UNITS YEAR;
X    END IF;
X    RETURN rv;
XEND PROCEDURE;
X
Xselect d1, d2, age_in_years(d1, d2) age
X    from t;
X
X-- Output
X--d1|d2|age
X--DATETIME YEAR TO DAY|DATETIME YEAR TO DAY|INTERVAL YEAR(4) TO YEAR
X--1997-09-05|1990-09-04|7
X--1997-09-05|1990-09-05|7
X--1997-09-05|1990-09-06|6
X
XCREATE PROCEDURE age_in_years_and_months(birthday DATETIME YEAR TO DAY,
X                    refdate DATETIME YEAR TO DAY DEFAULT CURRENT YEAR TO DAY)
X        RETURNING INTERVAL YEAR(4) TO MONTH;
X    DEFINE rv INTERVAL YEAR(4) TO MONTH;
X
X    LET rv = EXTEND(birthday, YEAR TO MONTH) - EXTEND(refdate, YEAR TO MONTH);
X    IF EXTEND(birthday, DAY TO DAY) < EXTEND(refdate, DAY TO DAY) THEN
X        LET rv = rv - 1 UNITS MONTH;
X    END IF;
X    RETURN rv;
X
XEND PROCEDURE;
X
Xselect d1, d2, age_in_years_and_months(d1, d2) age
X    from t;
X
X-- Output
X--d1|d2|age
X--DATETIME YEAR TO DAY|DATETIME YEAR TO DAY|INTERVAL YEAR(4) TO MONTH
X--1997-09-05|1990-09-04|7-00
X--1997-09-05|1990-09-05|7-00
X--1997-09-05|1990-09-06|6-11
X
SHAR_EOF
if [ `wc -c < ages.spl` -ne     2335 ]
then
    echo "Lengths do not match -- Bad Copy of ages.spl"
fi
echo "Extracting file anniversary.spl"
sed -e 's/^X//' <<\SHAR_EOF > anniversary.spl
X-- @(#)$Id: anniversary.spl,v 1.2 2002/08/29 18:45:06 jleffler Exp $
X--
X-- Function to decide:
X--  1.  Whether reference date is anniversary of given date.
X-- NB: Would not apply to Hebrew calendar, as a specific f'rinstance.
X-- Uses function: isleapyear()
X
XCREATE FUNCTION Anniversary(bd DATE, rd DATE DEFAULT TODAY) RETURNING CHAR(1); --BOOLEAN;
X	IF rd < bd THEN
X		RETURN 'f';	-- Birthday hasn't occurred on or before ref date hence not anniversary
X	ELIF rd = bd THEN
X		RETURN 't';	-- Reference date is zeroth anniversary of birthday
X	ELIF MONTH(bd) != MONTH(rd) THEN
X		RETURN 'f';  -- Different month
X    ELIF MONTH(bd) != 2 THEN
X		-- Not February
X		-- Alternatively: RETURN (DAY(bd) = DAY(rd));
X		IF DAY(bd) = DAY(rd) THEN
X			RETURN 't'; -- Same day, same month
X		ELSE
X			RETURN 'f';	-- Different day of same month
X		END IF;
X	ELIF DAY(bd) = DAY(rd) THEN
X		-- Same day of month in February
X		RETURN 't';
X	ELIF DAY(bd) < 28 OR DAY(rd) < 28 THEN
X		-- Either day is before 28th implies non-anniversary
X		RETURN 'f';
X	ELIF DAY(rd) = 29 THEN
X		RETURN 'f'; -- Ref date of 29th February is only anniversary of leap birthdays,
X					-- but leap birthdays were filtered previously by DAY(bd) = DAY(rd).
X	-- Assert: birthday is 29th February, ref date is 28th February
X	-- Anniversary is false if ref date is in a leap year (anniversary would be 29th Feb).
X	ELIF isleapyear(YEAR(rd)) = 't' THEN
X		RETURN 'f';
X	ELSE
X		RETURN 't';
X    END IF;
XEND FUNCTION;
X
X-- Birthday after reference date
XEXECUTE FUNCTION Anniversary(MDY(3, 1, 2004), MDY(3, 1, 2003));     -- 'f'
X-- Birthday same as reference date
XEXECUTE FUNCTION Anniversary(MDY(3, 1, 2004), MDY(3, 1, 2004));     -- 't'
X-- Different month
XEXECUTE FUNCTION Anniversary(MDY(3, 1, 2000), MDY(4, 1, 2004));     -- 'f'
X-- Not Feb: same day, same month
XEXECUTE FUNCTION Anniversary(MDY(3, 1, 2000), MDY(3, 1, 2004));     -- 't'
X-- Not Feb: different day, same month
XEXECUTE FUNCTION Anniversary(MDY(3, 1, 2000), MDY(3, 2, 2004));     -- 'f'
X
X-- All dates below here are in February!
X-- Same day of month
XEXECUTE FUNCTION Anniversary(MDY(2, 2, 2000), MDY(2, 2, 2004));     -- 't'
X-- Same day of month
XEXECUTE FUNCTION Anniversary(MDY(2, 28, 2000), MDY(2, 28, 2004));   -- 't'
X-- Same day of month
XEXECUTE FUNCTION Anniversary(MDY(2, 28, 2001), MDY(2, 28, 2003));   -- 't'
X-- Same day of month
XEXECUTE FUNCTION Anniversary(MDY(2, 29, 2000), MDY(2, 29, 2004));   -- 't'
X-- Ref day is 29th, birthday isn't
XEXECUTE FUNCTION Anniversary(MDY(2, 28, 2000), MDY(2, 29, 2004));   -- 'f'
X-- Birthday is 29th; ref date isn't and ref year is leap year
XEXECUTE FUNCTION Anniversary(MDY(2, 29, 2000), MDY(2, 28, 2004));   -- 'f'
X-- Birthday is 29th; ref date is 28th and ref year is not leap year;
X-- 28th Feb is best approximation to birthday in given year
XEXECUTE FUNCTION Anniversary(MDY(2, 29, 2000), MDY(2, 28, 2003));   -- 't'
SHAR_EOF
if [ `wc -c < anniversary.spl` -ne     2858 ]
then
    echo "Lengths do not match -- Bad Copy of anniversary.spl"
fi
echo "Extracting file birthdays.spl"
sed -e 's/^X//' <<\SHAR_EOF > birthdays.spl
X-- @(#)$Id: birthdays.spl,v 1.5 2001/03/06 02:00:32 jleffler Exp $
X--
X-- Birthday data type (for Paul Brown's Magnum Opus)
X--
X-- Extended with plain-date versions of the same logic, which could even
X-- be used in pre-9.x versions of Informix databases provided you changed
X-- the BOOLEAN return type into something else (CHAR(1) would be
X-- simplest) and changed FUNCTION into PROCEDURE.
X--
X-- Uses separate function: isleapyear(yyyy INTEGER) RETURNING BOOLEAN;
X
XCREATE ROW TYPE Birthday (MmDd SMALLINT NOT NULL);
X
XCREATE FUNCTION birthday(d DATE) RETURNING birthday;
X    DEFINE ld BOOLEAN;
X    DEFINE mm, dd SMALLINT;
X    IF d IS NULL THEN
X        RAISE EXCEPTION -746, 0, 'attempt TO convert NULL DATE TO birthday';
X    END IF;
X    LET mm = MONTH(d);
X    LET dd = DAY(d);
X    RETURN ROW(mm * 100 + dd)::birthday;
XEND FUNCTION;
X
XCREATE FUNCTION compare(a1 birthday, a2 birthday) RETURNING INTEGER;
X    IF a1.mmdd < a2.mmdd THEN
X        RETURN -1;
X    ELIF a1.mmdd > a2.mmdd THEN
X        RETURN +1;
X    ELSE
X        RETURN 0;
X    END IF;
XEND FUNCTION;
X
X-- I find it incredible that this circumlocution is necessary...
X-- However, it seems to be so: I could not get this accepted.
X--  CREATE FUNCTION Equal(a1 birthday, a2 birthday) RETURNING BOOLEAN;
X--      RETURN (compare(a1, a2) == 0);
X--  END FUNCTION;
X
XCREATE FUNCTION Equal(a1 birthday, a2 birthday) RETURNING BOOLEAN;
X    IF (compare(a1, a2) == 0) THEN
X        RETURN 't';
X    ELSE
X        RETURN 'f';
X    END IF;
XEND FUNCTION;
X
XCREATE FUNCTION isbirthday(bd birthday, rd DATE DEFAULT TODAY) RETURNING BOOLEAN;
X    DEFINE rb birthday;
X    LET rb = birthday(rd);
X    IF bd.mmdd != 0229 THEN
X        RETURN equal(bd, rb);
X    -- Birthday is a leap-day birthday (29th February birthday)
X    ELIF rb.mmdd != 0229 AND rb.mmdd != 0228 THEN
X        RETURN 'f';
X    -- Reference date is 28th or 29th February too
X    ELIF NOT isleapyear(YEAR(rd)) THEN
X        RETURN 't';
X    -- Reference date is 28th or 29th February in a leap year
X    ELIF rb.mmdd = bd.mmdd THEN
X        RETURN 't';
X    ELSE
X        RETURN 'f';
X    END IF;
XEND FUNCTION;
X
X-- Assume DBDATE="DMY4/"
XSELECT y, birthday(y) FROM
XTABLE(SET{'01/01/2000', '28/02/2000', '14/2/2001', '29/02/2000', '01/03/2000'}::SET(DATE NOT NULL)) y(y);
X
XSELECT y dob, z refdate, isbirthday(birthday(y), z) celebrate FROM
XTABLE(SET{'01/01/2000', '14/02/2000', '28/02/2000', '29/02/2000', '01/03/2000'}::SET(DATE NOT NULL)) y(y),
XTABLE(SET{'01/01/2000', '28/02/2000', '29/02/2000', '01/03/2000',
X          '01/01/2001', '14/02/2001', '28/02/2001', '01/03/2001'}::SET(DATE NOT NULL)) z(z);
X
XCREATE FUNCTION same_birthday(dob1 DATE, dob2 DATE) RETURNING BOOLEAN;
X	IF EXTEND(dob1, MONTH TO DAY) = EXTEND(dob2, MONTH TO DAY) THEN
X		RETURN 't';
X	ELSE
X		RETURN 'f';
X	END IF;
XEND FUNCTION;
X
XSELECT y dob, z refdate, samebirthday(y, z) same_birthday FROM
XTABLE(SET{'01/01/2000', '14/02/2000', '28/02/2000', '29/02/2000', '01/03/2000'}::SET(DATE NOT NULL)) y(y),
XTABLE(SET{'01/01/2000', '28/02/2000', '29/02/2000', '01/03/2000',
X          '01/01/2001', '14/02/2001', '28/02/2001', '01/03/2001'}::SET(DATE NOT NULL)) z(z);
X
XCREATE FUNCTION Anniversary(bd DATE, rd DATE DEFAULT TODAY) RETURNING BOOLEAN;
X	IF MONTH(bd) != 2 OR DAY(bd) != 29 THEN
X		RETURN same_birthday(bd, rd);
X	-- Birthday is a leap-day birthday (29th February birthday)
X	ELIF MONTH(rd) != 2 OR (DAY(rd) != 29 AND DAY(rd) != 28) THEN
X		RETURN 'f';
X	-- Reference date is 28th or 29th February too
X	ELIF NOT isleapyear(YEAR(rd)) THEN
X		RETURN 't';
X	-- Reference date is 28th or 29th February in a leap year
X	ELIF DAY(rd) = DAY(bd) THEN
X		RETURN 't';
X	ELSE
X		RETURN 'f';
X	END IF;
XEND FUNCTION;
X
XSELECT y dob, z refdate, anniversary(y, z) celebrate FROM
XTABLE(SET{'01/01/2000', '14/02/2000', '28/02/2000', '29/02/2000', '01/03/2000'}::SET(DATE NOT NULL)) y(y),
XTABLE(SET{'01/01/2000', '28/02/2000', '29/02/2000', '01/03/2000',
X          '01/01/2001', '14/02/2001', '28/02/2001', '01/03/2001'}::SET(DATE NOT NULL)) z(z);
SHAR_EOF
if [ `wc -c < birthdays.spl` -ne     3967 ]
then
    echo "Lengths do not match -- Bad Copy of birthdays.spl"
fi
echo "Extracting file bittest.spl"
sed -e 's/^X//' <<\SHAR_EOF > bittest.spl
X-- @(#)$Id: bittest.spl,v 1.2 2002/10/10 18:39:57 jleffler Exp $
X--
X-- bit_test: return the Nth bit of value
X
XCREATE PROCEDURE bit_test(value INT, bit INT) RETURNING INT;
X
X    DEFINE i INT;
X
X	IF value IS NULL THEN
X		RETURN value;
X	END IF;
X
X    LET i = 0;
X    WHILE (i < bit)
X        IF (value = 0) THEN
X            EXIT WHILE;
X        END IF;
X        LET value = value / 2;
X		LET i = i + 1;
X    END WHILE;
X
X	IF value < 0 THEN
X		LET value = -value;
X	END IF;
X
X    RETURN MOD(value, 2);
X
XEND PROCEDURE;
X
XEXECUTE PROCEDURE bit_test(0, 0);	-- 0
XEXECUTE PROCEDURE bit_test(1, 0);	-- 1
XEXECUTE PROCEDURE bit_test(2, 0);	-- 0
XEXECUTE PROCEDURE bit_test(3, 0);	-- 1
XEXECUTE PROCEDURE bit_test(0, 1);	-- 0
XEXECUTE PROCEDURE bit_test(1, 1);	-- 0
XEXECUTE PROCEDURE bit_test(2, 1);	-- 1
XEXECUTE PROCEDURE bit_test(3, 1);	-- 1
XEXECUTE PROCEDURE bit_test(0, 2);	-- 0
XEXECUTE PROCEDURE bit_test(1, 2);	-- 0
XEXECUTE PROCEDURE bit_test(2, 2);	-- 0
XEXECUTE PROCEDURE bit_test(3, 2);	-- 0
XEXECUTE PROCEDURE bit_test(16, 4);	-- 1
XEXECUTE PROCEDURE bit_test(-16, 4);	-- 1 (-16 => 0xFFFFFFF0)
SHAR_EOF
if [ `wc -c < bittest.spl` -ne     1070 ]
then
    echo "Lengths do not match -- Bad Copy of bittest.spl"
fi
echo "Extracting file char_at.spl"
sed -e 's/^X//' <<\SHAR_EOF > char_at.spl
X-- @(#)$Id: char_at.spl,v 1.1 1999/05/17 23:59:59 jleffler Exp $
X--
X-- CHAR_AT stored procedure, to return character at given position in string
X--
X-- Author: J Leffler
X-- Date: 1999-05-17
X
XCREATE PROCEDURE char_at(str VARCHAR(255), pos SMALLINT) RETURNING CHAR(1);
X
X	DEFINE c CHAR(1);
X
X	IF pos > LENGTH(str) OR pos <= 0 THEN
X		LET c = NULL;
X	ELIF pos <= 16 THEN
X		IF   pos =  1 THEN LET c = str[ 1];
X		ELIF pos =  2 THEN LET c = str[ 2];
X		ELIF pos =  3 THEN LET c = str[ 3];
X		ELIF pos =  4 THEN LET c = str[ 4];
X		ELIF pos =  5 THEN LET c = str[ 5];
X		ELIF pos =  6 THEN LET c = str[ 6];
X		ELIF pos =  7 THEN LET c = str[ 7];
X		ELIF pos =  8 THEN LET c = str[ 8];
X		ELIF pos =  9 THEN LET c = str[ 9];
X		ELIF pos = 10 THEN LET c = str[10];
X		ELIF pos = 11 THEN LET c = str[11];
X		ELIF pos = 12 THEN LET c = str[12];
X		ELIF pos = 13 THEN LET c = str[13];
X		ELIF pos = 14 THEN LET c = str[14];
X		ELIF pos = 15 THEN LET c = str[15];
X		ELIF pos = 16 THEN LET c = str[16];
X		END IF;
X	ELIF pos <=  32 THEN LET c = char_at(str[ 17, 32], pos -  1 * 16);
X	ELIF pos <=  48 THEN LET c = char_at(str[ 33, 48], pos -  2 * 16);
X	ELIF pos <=  64 THEN LET c = char_at(str[ 49, 64], pos -  3 * 16);
X	ELIF pos <=  80 THEN LET c = char_at(str[ 65, 80], pos -  4 * 16);
X	ELIF pos <=  96 THEN LET c = char_at(str[ 81, 96], pos -  5 * 16);
X	ELIF pos <= 112 THEN LET c = char_at(str[ 97,112], pos -  6 * 16);
X	ELIF pos <= 128 THEN LET c = char_at(str[113,128], pos -  7 * 16);
X	ELIF pos <= 144 THEN LET c = char_at(str[129,144], pos -  8 * 16);
X	ELIF pos <= 160 THEN LET c = char_at(str[145,160], pos -  9 * 16);
X	ELIF pos <= 176 THEN LET c = char_at(str[161,176], pos - 10 * 16);
X	ELIF pos <= 192 THEN LET c = char_at(str[177,192], pos - 11 * 16);
X	ELIF pos <= 208 THEN LET c = char_at(str[193,208], pos - 12 * 16);
X	ELIF pos <= 224 THEN LET c = char_at(str[209,224], pos - 13 * 16);
X	ELIF pos <= 240 THEN LET c = char_at(str[225,240], pos - 14 * 16);
X	ELIF pos <= 255 THEN LET c = char_at(str[241,255], pos - 15 * 16);	-- Note asymmetry in upper bound!
X	ELSE                 LET c = NULL;	-- Not reached!
X	END IF;
X
X	RETURN c;
X
XEND PROCEDURE;
X
SHAR_EOF
if [ `wc -c < char_at.spl` -ne     2125 ]
then
    echo "Lengths do not match -- Bad Copy of char_at.spl"
fi
echo "Extracting file currentdb.spl"
sed -e 's/^X//' <<\SHAR_EOF > currentdb.spl
X{   "@(#)$Id: currentdb.spl,v 1.8 2000/11/29 17:49:25 jleffler Exp $"
X#
X# Stored procedure CURRENT_DATABASE written by Jonathan Leffler
X# (jleffler@informix.com), based on a tip from John Lysell
X# (jlysell@informix.com), with corrigenda from Raj Muralidharan
X# (rmurali@informix.com) and Tue Hejlskov Larsen (tue@informix.com).
X# The version dependency was discovered by Glynnie <glynb@my-deja.com>.
X#
X# In IDS versions prior to 7.31.UC2, you can create this stored
X# procedure in the SysMaster database (but you have to be user informix
X# to get the necessary permissions), and then any user in any database
X# can run it (or call it in a SELECT statement) and get the name of the
X# current database.  You can drop the owner part if you are not using a
X# MODE ANSI database:
X#
X# EXECUTE PROCEDURE sysmaster:"informix".current_database()
X# EXECUTE PROCEDURE sysmaster:current_database()
X#
X# However, in 7.31.UC2 (and above, presumably), it appears that the
X# stored procedure will always return 'sysmaster', so you have to
X# install this procedure in each database and run it.  That leaves open
X# the question of whether a generalized tool like this is worth it
X# compared with a simple minded stored procedure:
X#       CREATE PROCEDURE current_database() RETURNING VARCHAR(128);
X#           RETURN "mydbsname";
X#       END PROCEDURE;
X# The main advantage of the more complex procedure is that the procedure
X# text is identical for all databases, whereas the simple procedure has
X# to be customized per database.  "Yer pays yer money and yer takes yer
X# pick", as they almost say.
X# As of 2000-11-29, it has been established that this problem does not
X# affect Foundation 2000 9.21.UC1 (on Solaris).  This suggests that
X# there may be a (minor) bug in the 7.31.UC2 servers.
X#
X# The size of the return parameter needs to be 128 to allow for the 9.2x
X# and later servers where the names of databases, tables and columns is
X# increased to 128.  The quoting conventions used should be safe even
X# with DELIMIDENT set in the environment.
X}
X
XCREATE PROCEDURE current_database() RETURNING VARCHAR(128);
X
X    DEFINE s VARCHAR(128);
X
X    SELECT ODB_DBName
X        INTO s
X        FROM SysMaster:"informix".SysOpenDB
X        WHERE ODB_SessionID = DBINFO('sessionid')
X		  AND ODB_IsCurrent = 'Y';
X
X    RETURN s;
X
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < currentdb.spl` -ne     2315 ]
then
    echo "Lengths do not match -- Bad Copy of currentdb.spl"
fi
echo "Extracting file cvt_date.spl"
sed -e 's/^X//' <<\SHAR_EOF > cvt_date.spl
X-- @(#)$Id: cvt_date.spl,v 1.1 1999/07/07 16:26:49 jleffler Exp $
X--
X-- Convert string 19860623 to DATE (via DATETIME)
X
XCREATE PROCEDURE cvt_date(old CHAR(8)) RETURNING DATE;
X    DEFINE str CHAR(10);
X    DEFINE dt  DATETIME YEAR TO DAY;
X    LET str = old[1,4] || - || old[5,6] || - || old[7,8];
X    LET dt = str;
X    RETURN dt;
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < cvt_date.spl` -ne      343 ]
then
    echo "Lengths do not match -- Bad Copy of cvt_date.spl"
fi
echo "Extracting file dayofyear.spl"
sed -e 's/^X//' <<\SHAR_EOF > dayofyear.spl
X-- @(#)$Id: dayofyear.spl,v 1.1 2001/02/27 20:09:57 jleffler Exp $
X--
X-- Return day of year corresponding to date (default today)
X
XCREATE PROCEDURE day_of_year(dateval DATE DEFAULT TODAY) RETURNING INTEGER;
X	RETURN dateval - MDY(1, 1, YEAR(dateval)) + 1;
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < dayofyear.spl` -ne      270 ]
then
    echo "Lengths do not match -- Bad Copy of dayofyear.spl"
fi
echo "Extracting file frunixtime.spl"
sed -e 's/^X//' <<\SHAR_EOF > frunixtime.spl
X{
X#   "@(#)$Id: frunixtime.spl,v 1.2 2002/09/25 18:10:48 jleffler Exp $"
X#
X# Stored procedure FROM_UNIX_TIME written by Jonathan Leffler
X# (jleffler@us.ibm.com) as counterpart to TO_UNIX_TIME.
X#
X# If you run this procedure with no arguments (use the default), you
X# need to worry about the time zone the database server is using because
X# the value of CURRENT is determined by that, and you need to compensate
X# for it if you are using a different time zone.
X#
X# Note that this version works for dates after 2001-09-09 when the
X# interval between 1970-01-01 00:00:00+00:00 and current exceeds the
X# range of INTERVAL SECOND(9) TO SECOND.  Accepting DECIMAL(18,5) allows
X# it to work for all valid datetime values including fractional seconds.
X# In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is
X# 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is
X# -62135596800 (11 digits).  The integer part of both these values is
X# unrepresentable in a 32-bit integer, of course, so most Unix systems
X# won't handle this range, and the so-called 'Proleptic Gregorian
X# Calendar' used to calculate the dates ignores locale-dependent details
X# such as the loss of days that occurred during the switch between the
X# Julian and Gregorian calendar, but those are minutiae that most people
X# can ignore most of the time.
X}
X
XCREATE PROCEDURE from_unix_time(v DECIMAL(18,5) DEFAULT 0)
X            RETURNING DATETIME YEAR TO FRACTION(5);
X    DEFINE n DATETIME YEAR TO FRACTION(5);
X    DEFINE i1 INTEGER;
X    DEFINE i2 DECIMAL(11,5);
X    LET i1 = v / (24 * 60 * 60);
X    LET i2 = v - (i1 * 24 * 60 * 60);
X    LET n  = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5);
X    LET n  = n + i1 UNITS DAY;
X    LET n  = n + i2 UNITS FRACTION(5);
X    RETURN n;
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < frunixtime.spl` -ne     1783 ]
then
    echo "Lengths do not match -- Bad Copy of frunixtime.spl"
fi
echo "Extracting file hoursbetween.spl"
sed -e 's/^X//' <<\SHAR_EOF > hoursbetween.spl
X#   "@(#)$Id: hoursbetween.spl,v 1.1 1998/09/24 20:19:03 jleffler Exp $"
X#
X# Stored procedure HOURS_BETWEEN written by Jonathan Leffler
X# (jleffler@informix.com).  TO make this a pure number returned,
X# change the RETURNING clause to INTEGER, define s CHAR(10),
X# assign n to s and return s.  The explicit conversion to string
X# is then followed by an implicit conversion to INTEGER.
X
XCREATE PROCEDURE hours_between(d1 DATETIME YEAR TO HOUR,
X                               d2 DATETIME YEAR TO HOUR)
X    RETURNING INTERVAL HOUR(9) TO HOUR;
X    DEFINE n INTERVAL HOUR(9) TO HOUR;
X    LET n = d1 - d2;
X    RETURN n;
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < hoursbetween.spl` -ne      628 ]
then
    echo "Lengths do not match -- Bad Copy of hoursbetween.spl"
fi
echo "Extracting file isleapyear.spl"
sed -e 's/^X//' <<\SHAR_EOF > isleapyear.spl
X-- @(#)$Id: isleapyear.spl,v 1.2 2002/10/10 18:26:19 jleffler Exp $
X--
X-- Is given year a leap year?
X
XCREATE FUNCTION isleapyear(yyyy INTEGER) RETURNING BOOLEAN;
X	IF yyyy IS NULL THEN
X		RETURN NULL;
X    ELIF MOD(yyyy, 4) != 0 THEN
X        RETURN 'f';
X    ELIF MOD(yyyy, 400) == 0 THEN
X        RETURN 't';
X    ELIF MOD(yyyy, 100) == 0 THEN
X        RETURN 'f';
X    ELSE
X        RETURN 't';
X    END IF;
XEND FUNCTION;
SHAR_EOF
if [ `wc -c < isleapyear.spl` -ne      414 ]
then
    echo "Lengths do not match -- Bad Copy of isleapyear.spl"
fi
echo "Extracting file isnull.spl"
sed -e 's/^X//' <<\SHAR_EOF > isnull.spl
X-- @(#)$Id: isnull.spl,v 1.1 1996/08/26 18:35:18 johnl Exp $
X--
X-- isnull: return 1 if value is null, else return 0
X
XCREATE PROCEDURE isnull(val CHAR(30)) RETURNING INTEGER;
X
XIF val IS NULL THEN
X	RETURN 1;
XELSE
X	RETURN 0;
XEND IF;
X
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < isnull.spl` -ne      246 ]
then
    echo "Lengths do not match -- Bad Copy of isnull.spl"
fi
echo "Extracting file iso8601_weekday.spl"
sed -e 's/^X//' <<\SHAR_EOF > iso8601_weekday.spl
X-- @(#)$Id: iso8601_weekday.spl,v 1.1 2001/04/03 19:34:43 jleffler Exp $
X--
X-- Calculate ISO 8601 Week Number for given date
X-- Defines procedure: iso8601_weekday().
X-- Uses procedure: iso8601_weeknum().
X
X{
XAccording to a summary of the ISO 8601:1988 standard "Data Elements and
XInterchange Formats -- Information Interchange -- Representation of
Xdates and times":
X
X    The week notation can also be extended by a number indicating the
X    day of the week.  For example the day 1996-12-31 which is the
X    Tuesday (day 2) of the first week of 1997 can also be written as
X
X        1997-W01-2 or 1997W012
X
X    for applications like industrial planning where many things like
X    shift rotations are organized per week and knowing the week number
X    and the day of the week is more handy than knowing the day of the
X    month.
X
XThis procedure uses iso8601_weeknum() to format the YYYY-Www part of the
Xdate, and appends '-d' to the result, allowing for Informix's coding of
XSunday as day 0 rather than day 7 as required by ISO 8601.
X}
X
XCREATE PROCEDURE iso8601_weekday(dateval DATE DEFAULT TODAY) RETURNING CHAR(10);
X    DEFINE rv CHAR(10);
X    DEFINE dw CHAR(4);
X	LET dw = WEEKDAY(dateval);
X	IF dw = 0 THEN
X		LET dw = 7;
X	END IF;
X	RETURN iso8601_weeknum(dateval) || '-' || dw;
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < iso8601_weekday.spl` -ne     1290 ]
then
    echo "Lengths do not match -- Bad Copy of iso8601_weekday.spl"
fi
echo "Extracting file iso8601_weeknum.spl"
sed -e 's/^X//' <<\SHAR_EOF > iso8601_weeknum.spl
X-- @(#)$Id: iso8601_weeknum.spl,v 1.1 2001/02/27 20:36:25 jleffler Exp $
X--
X-- Calculate ISO 8601 Week Number for given date
X-- Defines procedures: day_one_week_one() and iso8601_weeknum().
X
X{
XAccording to a summary of the ISO 8601:1988 standard "Data Elements and
XInterchange Formats -- Information Interchange -- Representation of
Xdates and times":
X
X    In commercial and industrial applications (delivery times,
X    production plans, etc.), especially in Europe, it is often required
X    to refer to a week of a year.  Week 01 of a year is per definition
X    the first week which has the Thursday in this year, which is
X    equivalent to the week which contains the fourth day of January.  In
X    other words, the first week of a new year is the week which has the
X    majority of its days in the new year.  Week 01 might also contain
X    days from the previous year and the week before week 01 of a year is
X    the last week (52 or 53) of the previous year even if it contains
X    days from the new year.  A week starts with Monday (day 1) and ends
X    with Sunday (day 7).  For example, the first week of the year 1997
X    lasts from 1996-12-30 to 1997-01-05 and can be written in standard
X    notation as
X
X        1997-W01 or 1997W01
X
X    The week notation can also be extended by a number indicating the
X    day of the week.  For example the day 1996-12-31 which is the
X    Tuesday (day 2) of the first week of 1997 can also be written as
X
X        1997-W01-2 or 1997W012
X
X    for applications like industrial planning where many things like
X    shift rotations are organized per week and knowing the week number
X    and the day of the week is more handy than knowing the day of the
X    month.
X
XReferring to the standard itself, section 3.17 defines a calendar week:
X
X    week, calendar: A seven day period within a calendar year, starting
X    on a Monday and identified by its ordinal number within the year;
X    the first calendar week of the year is the one that includes the
X    first Thursday of that year.  In the Gregorian calendar, this is
X    equivalent to the week which includes 4 January.
X
XSection 5.2.3 "Date identified by Calendar week and day numbers" states:
X
X    Calendar week is represented by two numeric digits.  The first
X    calendar week of a year shall be identified as 01 [...]
X
X    Day of the week is represented by one decimal digit.  Monday
X    shall be identified as day 1 of any calendar week [...]
X
XSection 5.2.3.1 "Complete representation" states:
X
X    When the application clearly identifies the need for a complete
X    representation of a date identified by calendar week and day
X    numbers, it shall be one of the alphanumeric representations as
X    follows, where CCYY represents a calendar year, W is the week
X    designator, ww represents the ordinal number of a calendar week
X    within the year, and D represents the ordinal number within the
X    calendar week.
X
X    Basic format: CCYYWwwD
X        Example: 1985W155
X    Extended format: CCYY-Www-D
X        Example: 1985-W15-5
X
XBoth the summary and the formal definition are intuitively clear, but it
Xis not obvious how to translate it into an algorithm.  However, we can
Xdeal with the problem by exhaustively enumerating the seven options for
Xthe day of the week on which 1st January falls (with actual year values
Xfor concreteness):
X
X    1st January 2001 is Monday    => Week 1 starts on 2001-01-01
X    1st January 2002 is Tuesday   => Week 1 starts on 2001-12-31
X    1st January 2003 is Wednesday => Week 1 starts on 2002-12-30
X    1st January 2004 is Thursday  => Week 1 starts on 2003-12-29
X    1st January 2010 is Friday    => Week 1 starts on 2010-01-04
X    1st January 2005 is Saturday  => Week 1 starts on 2005-01-03
X    1st January 2006 is Sunday    => Week 1 starts on 2006-01-02
X
X(Cross-check: 1st January 1997 was a Wednesday; the summary notes state
Xthat week 1 of 1997 started on 1996-12-30, which is consistent with the
Xtable derived for dates in the first decade of the third millennium
Xabove).
X
XWhen working with the Informix DATE types, bear in mind that Informix
Xuses WEEKDAY values 0 = Sunday, 1 = Monday, 6 = Saturday.  When the
Xweekday of the first of January has the value in the LH column, you need
Xto add the value in the RH column to the 1st of January to obtain the
Xdate of the first day of the first week of the year.
X
X    Weekday         Offset to
X    1st January     1st day of week 1
X
X    0               +1
X    1                0
X    2               -1
X    3               -2
X    4               -3
X    5               +3
X    6               +2
X
XThis can be written as MOD(11-w,7)-3 where w is the (Informix encoding
Xof the) weekday of 1st January and the value 11 is used to ensure that
Xno negative values are presented to the MOD operator.  Hence, the
Xexpression for the date corresponding to the 1st day (Monday) of the 1st
Xweek of a given year, yyyy, is:
X
X    d1w1 = MDY(1, 1, yyyy) + MOD(11 - WEEKDAY(MDY(1,1,yyyy)), 7) - 3
X
XThis expression is encapsulated in stored procedure day_one_week_one:
X}
X
XCREATE PROCEDURE day_one_week_one(yyyy INTEGER) RETURNING DATE;
X    DEFINE jan1 DATE;
X    LET jan1 = MDY(1, 1, yyyy);
X    RETURN jan1 + MOD(11 - WEEKDAY(jan1), 7) - 3;
XEND PROCEDURE;
X
X{
XGiven this date d1w1, we can calculate the week number of any other date
Xin the same year as:
X
X    TRUNC((dateval - d1w1) / 7) + 1
X
XThe residual issues are ensuring that the wraparounds are correct.  If
Xthe given date is earlier than the start of the first week of the year
Xthat contains it, then the date belongs to the last week of the previous
Xyear.  If the given date is on or after the start of the first week of
Xthe next year, then the date belongs to the first week of the next year.
X
XGiven these observations, we can write iso8601_weeknum as shown below.
X(Beware: iso8601_week_number() is too long for servers with the
X18-character limit; so is day_one_of_week_one()).
X
XThen comes the interesting testing phase -- when do you get week 53?
XOne answer is on Friday 1st January 2010, which is in 2009-W53 (as,
Xindeed, is Sunday 3rd January 2010).  Similarly, Saturday 1st January
X2005 is in 2004-W53, but Sunday 1st January 2006 is in 2005-W52.
X}
X
XCREATE PROCEDURE iso8601_weeknum(dateval DATE DEFAULT TODAY) RETURNING CHAR(8);
X    DEFINE rv CHAR(8);
X    DEFINE yyyy CHAR(4);
X    DEFINE ww CHAR(2);
X    DEFINE d1w1 DATE;
X    DEFINE tv DATE;
X    DEFINE wn INTEGER;
X    DEFINE yn INTEGER;
X    -- Calculate year and week number.
X    LET yn = YEAR(dateval);
X    LET d1w1 = day_one_week_one(yn);
X    IF dateval < d1w1 THEN
X        -- Date is in early January and is in last week of prior year
X        LET yn = yn - 1;
X        LET d1w1 = day_one_week_one(yn);
X    ELSE
X        LET tv = day_one_week_one(yn + 1);
X        IF dateval >= tv THEN
X            -- Date is in late December and is in the first week of next year
X            LET yn = yn + 1;
X            LET d1w1 = tv;
X        END IF;
X    END IF;
X    LET wn = TRUNC((dateval - d1w1) / 7) + 1;
X    -- Calculation complete: yn is year number and wn is week number.
X    -- Format result.
X    LET yyyy = yn;
X    IF wn < 10 THEN
X        LET ww = '0' || wn;
X    ELSE
X        LET ww = wn;
X    END IF
X    LET rv = yyyy || '-W' || ww;
X    RETURN rv;
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < iso8601_weeknum.spl` -ne     7230 ]
then
    echo "Lengths do not match -- Bad Copy of iso8601_weeknum.spl"
fi
echo "Extracting file nvl_int.spl"
sed -e 's/^X//' <<\SHAR_EOF > nvl_int.spl
X-- @(#)$Id: nvl_int.spl,v 1.1 1996/08/26 18:33:11 johnl Exp $
X--
X-- nvl_integer: return v1 if it is not null else return v2
X
XCREATE PROCEDURE nvl_integer(v1 INTEGER, v2 INTEGER DEFAULT 0)
X    RETURNING INTEGER;
X
X    DEFINE rv INTEGER;
X
X    IF v1 IS NOT NULL THEN
X        LET rv = v1;
X    ELSE
X        LET rv = v2;
X    END IF
X
X    RETURN rv;
X
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < nvl_int.spl` -ne      357 ]
then
    echo "Lengths do not match -- Bad Copy of nvl_int.spl"
fi
echo "Extracting file random.spl"
sed -e 's/^X//' <<\SHAR_EOF > random.spl
X-- @(#)$Id: random.spl,v 1.2 1997/12/08 19:31:44 johnl Exp $
X--
X-- Simple emulation of SRAND and RAND in SPL
X-- Using random number generator suggested by C standard (ISO 9899:1990)
X
XCREATE PROCEDURE sp_setseed(n INTEGER)
X	DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
X	LET seed = n;
XEND PROCEDURE;
X
XCREATE PROCEDURE sp_random() RETURNING INTEGER;
X	DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
X	DEFINE d DECIMAL(20,0);
X	LET d = (seed * 1103515245) + 12345;
X	-- MOD function does not handle 20-digit values...  Dammit!!
X	LET seed = d - 4294967296 * TRUNC(d / 4294967296);
X	RETURN MOD(TRUNC(seed / 65536), 32768);
XEND PROCEDURE;
X
SHAR_EOF
if [ `wc -c < random.spl` -ne      621 ]
then
    echo "Lengths do not match -- Bad Copy of random.spl"
fi
echo "Extracting file sign.spl"
sed -e 's/^X//' <<\SHAR_EOF > sign.spl
X-- @(#)$Id: sign.spl,v 1.1 2002/03/22 18:48:42 jleffler Exp $
X-- @(#)Surrogate for Oracle SIGN() function
X-- @(#)Create SIGN Stored Procedure
X
XCREATE PROCEDURE SIGN(d DECIMAL(32)) RETURNS INTEGER;
X	IF d IS NULL THEN RETURN NULL;
X	ELIF d > 0.0 THEN RETURN +1;
X	ELIF d < 0.0 THEN RETURN -1;
X	ELSE              RETURN  0;
X	END IF;
XEND PROCEDURE;
X
SHAR_EOF
if [ `wc -c < sign.spl` -ne      344 ]
then
    echo "Lengths do not match -- Bad Copy of sign.spl"
fi
echo "Extracting file tounixtime.spl"
sed -e 's/^X//' <<\SHAR_EOF > tounixtime.spl
X{
X#   "@(#)$Id: tounixtime.spl,v 1.6 2002/09/25 18:10:48 jleffler Exp $"
X#
X# Stored procedure TO_UNIX_TIME written by Jonathan Leffler (previously
X# jleffler@informix.com and now jleffler@us.ibm.com).  Includes fix for
X# bug reported by Tsutomu Ogiwara <Tsutomu.Ogiwara@ctc-g.co.jp> on
X# 2001-07-13.  Previous version used DATETIME(0) SECOND TO SECOND
X# instead of DATETIME(0:0:0) HOUR TO SECOND, and when the calculation
X# extended the shorter constant to DATETIME HOUR TO SECOND, it added the
X# current hour and minute fields, as documented in the Informix Guide to
X# SQL: Syntax manual under EXTEND in the section on 'Expression'.
X# Amended 2002-08-23 to handle 'eternity' and annotated more thoroughly.
X# Amended 2002-09-25 to handle fractional seconds, as companion to the
X# new stored procedure FROM_UNIX_TIME().
X#
X# If you run this procedure with no arguments (use the default), you
X# need to worry about the time zone the database server is using because
X# the value of CURRENT is determined by that, and you need to compensate
X# for it if you are using a different time zone.
X#
X# Note that this version works for dates after 2001-09-09 when the
X# interval between 1970-01-01 00:00:00+00:00 and current exceeds the
X# range of INTERVAL SECOND(9) TO SECOND.  Returning DECIMAL(18,5) allows
X# it to work for all valid datetime values including fractional seconds.
X# In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is
X# 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is
X# -62135596800 (11 digits).  Both these values are unrepresentable in
X# 32-bit integers, of course, so most Unix systems won't handle this
X# range, and the so-called 'Proleptic Gregorian Calendar' used to
X# calculate the dates ignores locale-dependent details such as the loss
X# of days that occurred during the switch between the Julian and
X# Gregorian calendar, but those are minutiae that most people can ignore
X# most of the time.
X}
X
XCREATE PROCEDURE to_unix_time(d DATETIME YEAR TO FRACTION(5)
X                                DEFAULT CURRENT YEAR TO FRACTION(5))
X            RETURNING DECIMAL(18,5);
X    DEFINE n DECIMAL(18,5);
X    DEFINE i1 INTERVAL DAY(9) TO DAY;
X    DEFINE i2 INTERVAL SECOND(6) TO FRACTION(5);
X    DEFINE s1 CHAR(15);
X    DEFINE s2 CHAR(15);
X    LET i1 = EXTEND(d, YEAR TO DAY) - DATETIME(1970-01-01) YEAR TO DAY;
X    LET s1 = i1;
X    LET i2 = EXTEND(d, HOUR TO FRACTION(5)) -
X                DATETIME(00:00:00.00000) HOUR TO FRACTION(5);
X    LET s2 = i2;
X    LET n = s1 * (24 * 60 * 60) + s2;
X    RETURN n;
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < tounixtime.spl` -ne     2554 ]
then
    echo "Lengths do not match -- Bad Copy of tounixtime.spl"
fi
echo "Extracting file txstate.spl"
sed -e 's/^X//' <<\SHAR_EOF > txstate.spl
X-- @(#)$Id: txstate.spl,v 1.1 2001/12/14 18:03:10 jleffler Exp $
X-- @(#)Determine current transaction state
X
XCREATE PROCEDURE tx_state() RETURNING VARCHAR(14);
X    DEFINE errcode INTEGER;
X    ON EXCEPTION IN (-256, -535) SET errcode
X        IF errcode = -256 THEN
X            RETURN "TX-Unavailable";
X        ELIF errcode = -535 THEN
X            RETURN "In-TX";
X        END IF;
X    END EXCEPTION
X    BEGIN WORK;
X    ROLLBACK WORK;
X    RETURN "No-TX";
XEND PROCEDURE;
X
SHAR_EOF
if [ `wc -c < txstate.spl` -ne      467 ]
then
    echo "Lengths do not match -- Bad Copy of txstate.spl"
fi
echo "Extracting file yyyymm.spl"
sed -e 's/^X//' <<\SHAR_EOF > yyyymm.spl
X-- @(#)$Id: yyyymm.spl,v 1.1 1999/07/07 16:40:03 jleffler Exp $
X--
X-- Convert DATE to YYYYMM format integer
X--
X-- The extra pair of parentheses seems to be necessary
X-- Non; je ne comprend pas!
X
XCREATE PROCEDURE yyyymm(d DATE) RETURNING INTEGER;
X	RETURN (YEAR(d) * 100 + MONTH(d));
XEND PROCEDURE;
SHAR_EOF
if [ `wc -c < yyyymm.spl` -ne      297 ]
then
    echo "Lengths do not match -- Bad Copy of yyyymm.spl"
fi
echo "Done."
exit 0