analytics/informix/extend/TimeSeries.6.00.FC8/examples/examples_setup.sql

-- Setup patterns for calendars

insert into CalendarPatterns
  values('workmonth',
         '{1 on}, month');

insert into CalendarPatterns
  values('fourday_day',
         '{1 off, 4 on, 2 off}, day');

insert into CalendarPatterns
  values('workweek_day',
         '{1 off, 5 on, 1 off}, day');

insert into CalendarPatterns
  values('workweek_week',
         '{1 on}, week');

insert into CalendarPatterns
  values('workweek_hour',
	   '{32 off, 9 on, 15 off, 9 on, 15 off, 9 on, 15 off, 9 on, 15 off, 9 on, 31 off}, hour');

insert into CalendarTable(c_name, c_calendar)
  values ('weekcal',
          'startdate(2011-01-02 00:00:00.00000), pattstart(2011-01-02 00:00:00.00000), pattname(workweek_week)');

insert into CalendarTable(c_name, c_calendar)
  values ('monthcal',
          'startdate(2011-01-01 00:00:00.00000), pattstart(2011-01-01 00:00:00.00000), pattname(workmonth)');

insert into CalendarTable(c_name, c_calendar)
  values ('daycal',
          'startdate(2011-01-02 00:00:00.00000), pattstart(2011-01-02 00:00:00.00000), pattname(workweek_day)');

insert into CalendarTable(c_name, c_calendar)
  values ('fourdaycal',
          'startdate(2011-01-02 00:00:00.00000), pattstart(2011-01-02 00:00:00.00000), pattname(fourday_day)');

insert into CalendarTable(c_name, c_calendar)
  values ('hourcal',
          'startdate(2011-01-02 00:00:00.00000), pattstart(2011-01-02 00:00:00.00000), pattname(workweek_hour)');

create row type stock_trade(
        timestamp       datetime year to fraction(5),
        price           double precision,
        vol             double precision,
        trade           int,
        broker          int,
        buyer           int,
        seller          int
);

execute procedure TSContainerCreate('ctnr_stock', 'rootdbs', 'stock_trade', 0, 0);

create row type one_real (timestamp datetime year to fraction(5), result real);

create row type stock_bar (
	timestamp datetime year to fraction(5), 
	high real, 
	low real, 
	final real, 
	vol real);

create row type stock_bar_union (
	timestamp datetime year to fraction(5), 
	high real, 
	low real, 
	final real, 
	vol real,
	high2 real, 
	low2 real, 
	final2 real, 
	vol2 real);

create table daily_stocks( 
	stock_id int, 
	stock_name lvarchar,
	stock_data TimeSeries(stock_bar));

create table rel_ticks (trade stock_trade,
			stock_id int);

insert into rel_ticks values (
	row('2011-01-10 00:00:00.00000', 66, 55, 44, 33, 22, 11)::stock_trade,
	600);

insert into rel_ticks values (
	row('2011-01-11 00:00:00.00000', 11, 22, 33, 44, 55, 66)::stock_trade,
	600);

insert into daily_stocks values (
	901,
	'IBM',
	'origin(2011-01-03 00:00:00.00000), calendar(daycal), [(356, 310, 340, 999), (156, 110, 140, 111)]');

insert into daily_stocks values (
	902,
	'HWP',
	'origin(2011-01-03 00:00:00.00000), calendar(daycal), [(403, 401, 402, 253), (503, 501, 502, 302)]');

update daily_stocks
	set stock_data = PutElem(stock_data,row('2011-01-06 00:00:00.00000',99,54,66,888)::stock_bar)
	where stock_id = 901;

create table activity_stocks(
        stock_id	int,
        activity_data	TimeSeries(stock_trade)
);

insert into activity_stocks values (
        600,
	'irregular, container(ctnr_stock), origin(2011-01-03 00:00:00.00000), calendar(daycal), [(6.25,1000,1,7,2,1)@2011-01-04 12:58:09.12345, (6.50, 2000, 1,8,3,1)@2011-01-05 12:58:09.23456]');

create table activity_load_tab(stock_id int, set_data multiset(stock_trade not null));

insert into activity_load_tab values (
    600,
    'multiset{row("2011-01-06 13:14:15.98765",6.75,3000,1,9,4,1),row("2011-01-06 14:13:12.56789",7.00,4000,1,10,5,1)}');

create function Interp(TimeSeries) returns TimeSeries
    external name '$USERFUNCDIR/Interpolate.bld(ts_interp)'
    language c not variant;

create procedure TSIncLoad( table_name lvarchar,
                            file_name lvarchar,
                            calendar_name lvarchar,
                            origin datetime year to day,
                            threshold integer,
                            regular boolean,
                            container_name lvarchar,
                            nelems integer)
    external name '$USERFUNCDIR/Loader.bld(TSIncLoad)'
    language C;

create function TsToList(TimeSeries, integer) returns list
    external name '$USERFUNCDIR/TsToList.bld(ts_to_list)'
    language c not variant;