6.893 Architecture of Database Systems Term Project

Defining New Functions for the WSTD Data Type

As mentioned in the previous section, most of the functions for the new WSTD data type are implemented in PL/pgSQL. The following is a list of the functions implemented:

Same_year (WSTD-1, WSTD-2) returns Boolean; this returns true if the values are in the same year.
Same_day_of_week (WSTD-1, WSTD-2) returns Boolean; this returns true if the two values are the same day of the week.
Distance (WSTD-1, WSTD-2) returns integer; this returns the number of trading days between the two WSTDs.
Mon (WSTD), Tues (WSTD), Wed (WTD), Thu (WSTD), Fri (WSTD) returns Boolean; these have the obvious interpretation.
DJIA (WSTD) returns float; returns the Dow Jones Industrial Average on this date
Date (WSTD) returns Date; returns the calendar date of the given WSTD. This function was renamed Get_Date (WSTD) because of naming conflicts.
Greater_than (WSTD-1, WSTD-2) returns Boolean
Less_than (WSTD-1, WSTD-2) returns Boolean
Greater_Equal (WSTD-1, WSTD-2) returns Boolean
Less_Equal (WSTD-1, WSTD-2) returns Boolean
Equals (WSTD-1, WSTD-2) returns Boolean

The following is the definition of the functions for POSTGRES in a file called create_WSTD.sql.:

CREATE FUNCTION wstd_in(opaque)
   RETURNS wstd
   AS '/home/benleong/6.893/Project/wstd.so'
   LANGUAGE 'c';

CREATE FUNCTION wstd_out(opaque)
   RETURNS opaque
   AS '/home/benleong/6.893/Project/wstd.so'
   LANGUAGE 'c';

CREATE TYPE wstd (
   internallength = 8,
   input = wstd_in,
   output = wstd_out
);


-----------------------------------------------------------
-- (a) Function to check if 2 wstds are in the same year --
-----------------------------------------------------------

CREATE FUNCTION Same_year(wstd, wstd)
   RETURNS bool AS '

   BEGIN

     IF get_year($1) = get_year($2) THEN
         RETURN 1;
     ELSE
         RETURN 0;
     END IF;

   END;

'  LANGUAGE 'plpgsql';

------------------------------------------------------------------------
-- (b) Function to return the number of trading days between 2 wstds  --
------------------------------------------------------------------------

-- First a helper function
CREATE FUNCTION DayofWeek(wstd)
   RETURNS int AS '

   BEGIN

   RETURN extract(dow from get_date($1));

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION Same_day_of_week(wstd, wstd)
   RETURNS bool AS '

   BEGIN

     IF dayofweek($1) = dayofweek($2) THEN
       RETURN 1;
     ELSE
       RETURN 0;
     END IF;

   END;

'  LANGUAGE 'plpgsql';

------------------------------------------------------------------------
-- (c) Function to return the number of trading days between 2 wstds  --
------------------------------------------------------------------------

CREATE FUNCTION Distance(wstd, wstd)
   RETURNS int AS '

   DECLARE
       startdate date;
       enddate date;

   BEGIN

     IF $1 > $2 THEN
	startdate := get_date($2);
	enddate := get_date($1);
     ELSE

	IF $1 < $2 THEN
	startdate := get_date($1);
	enddate := get_date($2);
        ELSE
	startdate := get_date($1);
	enddate := get_date($1);
        END IF;

     END IF;

     RETURN (SELECT  count(*) FROM data WHERE date < enddate AND date >= startdate)


   END;

'  LANGUAGE 'plpgsql';

------------------------------------------------------------------------
-- (d) Functions to check the day of week of a particular trading day --
------------------------------------------------------------------------

CREATE FUNCTION Mon(wstd)
   RETURNS bool AS '

   BEGIN

     IF dayofweek($1) = 1 THEN
       RETURN 1;
     ELSE
       RETURN 0;
     END IF;

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION Tues(wstd)
   RETURNS bool AS '

   BEGIN

     IF dayofweek($1) = 2 THEN
       RETURN 1;
     ELSE
       RETURN 0;
     END IF;

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION Wed(wstd)
   RETURNS bool AS '

   BEGIN

     IF dayofweek($1) = 3 THEN
       RETURN 1;
     ELSE
       RETURN 0;
     END IF;

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION Thu(wstd)
   RETURNS bool AS '

   BEGIN

     IF dayofweek($1) = 4 THEN
       RETURN 1;
     ELSE
       RETURN 0;
     END IF;

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION Fri(wstd)
   RETURNS bool AS '

   BEGIN

     IF dayofweek($1) = 5 THEN
       RETURN 1;
     ELSE
       RETURN 0;
     END IF;

   END;

'  LANGUAGE 'plpgsql';

----------------------------------------------------------------------------
-- (e) Function to returnthe DJIA (at close) on a particular trading day --
----------------------------------------------------------------------------

CREATE FUNCTION DJIA(wstd)
   RETURNS float AS '

   BEGIN

       RETURN (SELECT close FROM data WHERE year = get_year($1) AND tday = get_day($1));

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION OPEN(wstd)
   RETURNS float AS '

   BEGIN

       RETURN (SELECT open FROM data WHERE year = get_year($1) AND tday = get_day($1));

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION HIGH(wstd)
   RETURNS float AS '

   BEGIN

       RETURN (SELECT high FROM data WHERE year = get_year($1) AND tday = get_day($1));

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION LOW(wstd)
   RETURNS float AS '

   BEGIN

       RETURN (SELECT low FROM data WHERE year = get_year($1) AND tday = get_day($1));

   END;

'  LANGUAGE 'plpgsql';

CREATE FUNCTION CLOSE(wstd)
   RETURNS float AS '

   BEGIN

       RETURN (SELECT close FROM data WHERE year = get_year($1) AND tday = get_day($1));

   END;

'  LANGUAGE 'plpgsql';

------------------------------------------------------------------------
-- (f) Function to return the actual date of a particular trading day --
--     This function was renamed to avoid naming conflict.            --
------------------------------------------------------------------------

CREATE FUNCTION Get_Date(wstd)
   RETURNS date AS '

   BEGIN

       RETURN (SELECT date FROM data WHERE year = get_year($1) AND tday = get_day($1));

    END;

'  LANGUAGE 'plpgsql';

---------------------------------------
-- (g) Comparison Functions for wstd --
---------------------------------------

CREATE FUNCTION Greater_than(wstd, wstd) RETURNS bool AS '
   BEGIN
   RETURN wstd_gt($1, $2);
   END;
'  LANGUAGE 'plpgsql';

CREATE FUNCTION Less_than(wstd, wstd) RETURNS bool AS '
   BEGIN
   RETURN wstd_lt($1, $2);
   END;
'  LANGUAGE 'plpgsql';

CREATE FUNCTION Greater_Equal(wstd, wstd) RETURNS bool AS '
   BEGIN
   RETURN wstd_ge($1, $2);
   END;
'  LANGUAGE 'plpgsql';

CREATE FUNCTION Less_Equal(wstd, wstd) RETURNS bool AS '
   BEGIN
   RETURN wstd_le($1, $2);
   END;
'  LANGUAGE 'plpgsql';

CREATE FUNCTION Equals(wstd, wstd) RETURNS bool AS '
   BEGIN
   RETURN wstd_eq($1, $2);
   END;
'  LANGUAGE 'plpgsql';

---------------------------------------------------
-- Methods to get at the internal representation --
---------------------------------------------------

CREATE FUNCTION Get_Year(wstd) RETURNS int
   AS '/home/benleong/6.893/Project/wstd.so' LANGUAGE 'c';

CREATE FUNCTION Get_Day(wstd) RETURNS int
   AS '/home/benleong/6.893/Project/wstd.so' LANGUAGE 'c';

--------------------------------------------------
-- To build the B-tree, we define the operators --
--------------------------------------------------
-- first, define the required operators
CREATE FUNCTION wstd_lt(wstd, wstd) RETURNS bool
   AS '/home/benleong/6.893/Project/wstd.so' LANGUAGE 'c';
CREATE FUNCTION wstd_le(wstd, wstd) RETURNS bool
   AS '/home/benleong/6.893/Project/wstd.so' LANGUAGE 'c';
CREATE FUNCTION wstd_eq(wstd, wstd) RETURNS bool
   AS '/home/benleong/6.893/Project/wstd.so' LANGUAGE 'c';
CREATE FUNCTION wstd_ge(wstd, wstd) RETURNS bool
   AS '/home/benleong/6.893/Project/wstd.so' LANGUAGE 'c';
CREATE FUNCTION wstd_gt(wstd, wstd) RETURNS bool
   AS '/home/benleong/6.893/Project/wstd.so' LANGUAGE 'c';

CREATE OPERATOR < (
   leftarg = wstd, rightarg = wstd, procedure = wstd_lt,
   restrict = scalarltsel, join = scalarltjoinsel
);

CREATE OPERATOR <= (
   leftarg = wstd, rightarg = wstd, procedure = wstd_le,
   restrict = scalarltsel, join = scalarltjoinsel
);

CREATE OPERATOR = (
   leftarg = wstd, rightarg = wstd, procedure = wstd_eq,
   restrict = eqsel, join = eqjoinsel
);

CREATE OPERATOR >= (
   leftarg = wstd, rightarg = wstd, procedure = wstd_ge,
   restrict = scalargtsel, join = scalargtjoinsel
);

CREATE OPERATOR > (
   leftarg = wstd, rightarg = wstd, procedure = wstd_gt,
   restrict = scalargtsel, join = scalargtjoinsel
);


-----------------------------------------------------------------------------------------------------
-- We also need a comparison function wstd_cmp(A,B) to allow us to build the b-tree  which returns --
-- Returns  -1 if A < B                                                                            __
--           0 if A = B                                                                            __
--           1 if A > B                                                                            __
-----------------------------------------------------------------------------------------------------

CREATE FUNCTION wstd_cmp(wstd, wstd) RETURNS int4
   AS '/home/benleong/6.893/Project/wstd.so' LANGUAGE 'c';

Loading Data for the Dow Jones Industrial Average(DJIA)

As many of the functions above are implemented as table lookups, an important component is the loading of the data into POSTGRES. Essential what I did to want to write a simple script to format the data into form which is directly loadable into POSTGRES and then used the following script to load the data:

CREATE TABLE data  (
    year	int,    -- Year
    tday	int,    -- Trading day in given year
    date	date,   -- Actual date for trading day
    open	real,   -- DJIA at opening
    high	real,   -- DJIA high
    low		real,   -- DJIA low
    close	real    -- DJIA at closing
);

COPY data FROM '/home/benleong/6.893/Project/outdata.txt' USING DELIMITERS '|';

It is clear above that the DJIA data is stored as a table with the following fields:

Year
Trading day in given year
Actual calendar date
Value of DJIA at opening of trading day
Highest value of DJIA on given day
Lowest value of DJIA on given day
Value of DJIA at close of trading day

Previous Up Next