-----------------------------------------------------------------------
--  Расписание движения поездов
-----------------------------------------------------------------------
--  route_num       рейс
--  departure_dt    дата отправления
--  ticket_cnt      количество билетов (всего)
--  ticket_sold     количество проданных билетов
--
create table routes (
    route_num       char( 5 ) not null,
    departure_dt    date not null,
    tickets_cnt     integer not null,
    tickets_sold    integer,
    constraint AtCnt
        check((tickets_cnt>=tickets_sold) or (tickets_sold is null)),
    constraint AtRoute_PK
        primary key (route_num,departure_dt)
)
@

connect to test@

-----------------------------------------------------------------------
--  Расписание движения поездов
-----------------------------------------------------------------------
--  route_num       рейс
--  departure_dt    дата отправления
--  ticket_cnt      количество билетов (всего)
--  ticket_sold     количество проданных билетов
--
create table routes (
    route_num       char( 5 ) not null,
    departure_dt    date not null,
    tickets_cnt     integer not null,
    tickets_sold    integer,
    constraint AtCnt
        check((tickets_cnt>=tickets_sold) or (tickets_sold is null)),
    constraint AtRoute_PK
        primary key (route_num,departure_dt)
)
@

-----------------------------------------------------------------------
--  Проданные билеты
-----------------------------------------------------------------------
--  route_num       рейс
--  departure_dt    дата отправления
--  tickets_sold    количество проданых билетов
--  desk            номер кассы
--  ts              дата продажи
create table sales (
    route_num       char( 5 ) not null,
    departure_dt    date not null,
    tickets_sold    integer check (tickets_sold> 0 ),
    desk            integer not null,
    ts              timestamp,
    constraint AtRoute_FK foreign key (route_num,departure_dt)
        references routes (route_num,departure_dt) on delete restrict
)
@

create index ISaleDesk
    on sales (desk,ts) allow reverse scans
@

create unique index ISaleRoutes
    on sales (route_num,departure_dt,desk,ts) allow reverse scans
@

---------------------------------------------------------------------
--  Блокировки на билеты
---------------------------------------------------------------------
--  route_num       char(5) not null,
--  departure_dt    date not null,
--  ticket_lock     integer check (ticket_sold>0),
--  desk            integer
create table locks (
    route_num       char( 5 ) not null,
    departure_dt    date not null,
    tickets_lock    integer check (tickets_lock> 0 ),
    desk            integer,
    constraint AtRouteI_FK foreign key (route_num,departure_dt)
        references routes (route_num,departure_dt) on delete restrict
)
@
create unique index ILock
    on locks (route_num,departure_dt,desk) allow reverse scans
@
create unique index ILockDesk
    on locks (desk,route_num,departure_dt) allow reverse scans
@

echo ---------------------------@
echo Заливаем расписание поездов@    
echo ---------------------------@
@
insert into routes
    (route_num,departure_dt,tickets_cnt,tickets_sold)
values
    ('C100',date('25.12.2004'), 1000 , 0 ),
    ('C100',date('26.12.2004'), 1000 , 0 ),
    ('C100',date('27.12.2004'), 1000 , 0 ),
    ('C100',date('28.12.2004'), 1000 , 0 ),
    ('C100',date('29.12.2004'), 1000 , 0 ),
    ('C100',date('30.12.2004'), 1000 , 0 ),
    ('C100',date('31.12.2004'), 1000 , 0 )
@
commit@

select * from routes@

--------------------------------------------------------------
-- Триггер, проверяющий, что есть еще незаблокированные билеты
-- которые можно продать или заблокировать для продажи
--------------------------------------------------------------
create trigger BILocks 
no cascade before insert on locks
referencing new as N
for each row
when
(
    N.tickets_lock
    >
    (select
        routes.tickets_cnt-routes.tickets_sold-
        coalesce((
            select sum(tickets_lock) from locks
                where locks.route_num=N.route_num
                        and locks.departure_dt=N.departure_dt
        ), 0 )
    from
        routes
    where
        routes.route_num=N.route_num
        and routes.departure_dt=N.departure_dt
    )
)
    signal sqlstate '75001' set message_text='Too many tickets'
@
commit@

------------------------------------------------------------
-- Продаем именно столько билетов, сколько заблокировали
------------------------------------------------------------
create trigger BISales
no cascade before insert on sales
referencing new as N
for each row
begin atomic
    set (N.tickets_sold,N.desk,N.ts)=(
        select
            tickets_lock,
            desk,
            current timestamp
        from
            locks
        where
            locks.route_num=N.route_num
            and locks.departure_dt=N.departure_dt
                and locks.desk=N.desk
        );
end
@
commit@

----------------------------------------------------------
-- После того как все продано увеличиваем кол-во проданных
-- билетов в расписании и снимаем блокировку с билетов
----------------------------------------------------------
create trigger AISales
after insert on sales
referencing new as N
for each row
begin atomic
    update routes
        set tickets_sold=tickets_sold+N.tickets_sold
            where routes.route_num=N.route_num
                    and routes.departure_dt=N.departure_dt;
    delete from locks
        where locks.desk=N.desk;
end
@
commit@

db2 => select * from locks
ROUTE_NUM DEPARTURE_DT TICKETS_LOCK DESK
--------- ------------ ------------ -----------
C100       31 . 12 . 2004              600             1 
C100       31 . 12 . 2004                4             2 
   2  записей выбрано.

db2 => select * from locks

ROUTE_NUM DEPARTURE_DT TICKETS_LOCK DESK
--------- ------------ ------------ -----------
C100       31 . 12 . 2004              600             1 

   1  записей выбрано.

db2 => select * from sales

ROUTE_NUM DEPARTURE_DT TICKETS_SOLD DESK        TS
--------- ------------ ------------ ----------- --------------------------
C100       31 . 12 . 2004                4             2   2004 - 12 - 14 - 15 . 21 . 26 . 515000 

   1  записей выбрано.

db2 => select * from routes

ROUTE_NUM DEPARTURE_DT TICKETS_CNT TICKETS_SOLD
--------- ------------ ----------- ------------
C100       25 . 12 . 2004            1000              0 
C100       26 . 12 . 2004            1000              0 
C100       27 . 12 . 2004            1000              0 
C100       28 . 12 . 2004            1000              0 
C100       29 . 12 . 2004            1000              0 
C100       30 . 12 . 2004            1000              0 
C100       31 . 12 . 2004            1000              4 

   7  записей выбрано.

DBNAME=test
DBUSER=db2admin
DBPASSWORD=ibmdb2

TARGET=reserve.dll
DEF=reserve.def
LIBS=db2api.lib

CPPOBJS=reserve.obj
CFLAGS= -Z7 -O2 -G5 -c -W2 -D_X86_= 1  -DWIN32 -GR -GX
LINKFLAGS= /DEBUG /DLL

.SUFFIXES:
.SUFFIXES: .obj .sqx .cpp

.cpp.obj:
	cl $(CFLAGS) $*.cpp

.sqx.obj:
	echo CONNECT TO $(DBNAME) USER $(DBUSER) USING '$(DBPASSWORD)' > $*.db2
	echo PREP $*.sqx BINDFILE >> $*.db2
	echo BIND $*.bnd >> $*.db2
    @if exist $*.log del $*.log
    d:\sqllib\bin\db2clpex db2 -z $*.log -vf $*.db2
    type $*.log
	cl $(CFLAGS) $*.cxx

$(TARGET): $(CPPOBJS) Makefile $(DEF)
	link $(LINKFLAGS) -out:$(TARGET) $(SQXOBJS) $(CPPOBJS) $(LIBS) -def:$(DEF)
    copy $(TARGET) d:\sqllib\function

LIBRARY reserve
EXPORTS reserve

#include <stdlib.h>
#include <stdio.h>
#include <time.h>
#include <windows.h>
#include <sqlenv.h>
#include <sqludf.h>

#define CREATE_MUTEX  1 
#define RELEASE_MUTEX  0 

SQL_API_RC SQL_API_FN reserve (
	char *pRoute_Num,
    char *pDeparture_Dt,
    sqlint32 *pTickets_Lock,
    sqlint32 *pDesk
)
{
	struct sqlca sqlca;
    EXEC SQL BEGIN DECLARE SECTION;
		char (*pRN)[ 5 + 1 ]  = (char(*)[ 5 + 1 ])pRoute_Num;
	    char (*pDT)[ 10 + 1 ] = (char(*)[ 10 + 1 ])pDeparture_Dt;
    	sqlint32& pTL     = *pTickets_Lock;
        sqlint32& pD      = *pDesk;
    EXEC SQL END DECLARE SECTION;

	HANDLE h=CreateMutex(NULL,FALSE,"Locker");
    if (h==NULL) {
    	strcpy(sqlca.sqlstate,"75001");
        return  0 ;
    }
    DWORD rc=WaitForSingleObject(h,INFINITE);
   	switch(rc) {
    case WAIT_OBJECT_0:
    	break;
    case WAIT_FAILED:
    case WAIT_ABANDONED:
    case WAIT_TIMEOUT:
	default:
   		CloseHandle(h);
    	strcpy(sqlca.sqlstate,"75002");
        return  0 ;
    }

    EXEC SQL INSERT INTO LOCKS
    	(ROUTE_NUM,DEPARTURE_DT,TICKETS_LOCK,DESK)
        VALUES (:pRN,:pDT,:pTL,:pD) WITH UR;

    ReleaseMutex(h);
   	CloseHandle(h);

	if (SQLCODE) {
    	pTL=- 1 ;
    }
	return  0 ;
}

connect reset@
connect to test@

drop procedure reserve(char( 5 ),date,integer,integer)@


create procedure reserve(
	in Route char( 5 ),
    in Departure date,
    inout TicketsCnt integer,
    in Desk integer
)
modifies sql data
not deterministic
language c
external name 'reserve!reserve'
fenced threadsafe
no external action
parameter style db2sql
@

----------------------------------------------------
-- небольшое тестирование производительности
----------------------------------------------------
create procedure testrs(inout start timestamp,inout stop timestamp) 
language sql
begin
	declare desk integer default  1000 ;
    declare cnt integer default  1 ;
    set start=current timestamp;
    while desk> 0  do
    	call reserve('C100',cast('25.12.2004' as date),cnt,desk);
        set desk=desk- 1 ;
    end while;
    set stop=current timestamp;
end
@

quit@

=================================================
-- Результаты выполнения
=================================================

db2 => call testrs(NULL,NULL)

  Value of output parameters
  --------------------------
  Parameter Name  : START
  Parameter Value :  2004 - 12 - 15 - 02 . 27 . 00 . 937003 

  Parameter Name  : STOP
  Parameter Value :  2004 - 12 - 15 - 02 . 27 . 03 . 531000 

  Return Status =  0 

db2 => select count(*) from locks

 1 
-----------
        1000 

   1  record(s) selected.

db2 => rollback
DB20000I  The SQL command completed successfully.
db2 => select count(*) from locks

 1 
-----------
           0 

   1  record(s) selected.

db2 =>



-------------------------------------------------
-- блокируем 200 билетов в 1 окошке
-------------------------------------------------
db2 => set isolation ur
DB20000I  The SQL command completed successfully.
db2 => call reserve('C100','26.12.2004', 200 , 1 )

  Value of output parameters
  --------------------------
  Parameter Name  : TICKETSCNT
  Parameter Value :  200 

  Return Status =  0 

db2 =>


--------------------------------------------------------------
-- пытаемся блокировать 991 билет во 2 окошке - не получается
-- т.к. 991+200 > 1000
--------------------------------------------------------------
db2 => call reserve('C100','26.12.2004', 991 , 2 )

  Value of output parameters
  --------------------------
  Parameter Name  : TICKETSCNT
  Parameter Value : - 1 

  Return Status =  0 


---------------------------------------
-- десять билетов блокируется запросто
---------------------------------------
db2 => call reserve('C100','26.12.2004', 10 , 2 )

  Value of output parameters
  --------------------------
  Parameter Name  : TICKETSCNT
  Parameter Value :  10 

  Return Status =  0 

db2 =>