Chetan Gaonker | 7f4bf74 | 2016-05-04 15:56:08 -0700 | [diff] [blame^] | 1 | CREATE TABLE radippool ( |
| 2 | id INT PRIMARY KEY, |
| 3 | pool_name VARCHAR(30) NOT NULL, |
| 4 | framedipaddress VARCHAR(30) NOT NULL, |
| 5 | nasipaddress VARCHAR(30) NOT NULL, |
| 6 | pool_key INT NOT NULL, |
| 7 | CalledStationId VARCHAR(64), |
| 8 | CallingStationId VARCHAR(64) NOT NULL, |
| 9 | expiry_time timestamp(0) NOT NULL, |
| 10 | username VARCHAR(100) |
| 11 | ); |
| 12 | |
| 13 | CREATE INDEX radippool_poolname_ipaadr ON radippool (pool_name, framedipaddress); |
| 14 | CREATE INDEX radippool_poolname_expire ON radippool (pool_name, expiry_time); |
| 15 | CREATE INDEX radippool_nasipaddr_key ON radippool (nasipaddress, pool_key); |
| 16 | CREATE INDEX radippool_nasipaddr_calling ON radippool (nasipaddress, callingstationid); |
| 17 | |
| 18 | CREATE SEQUENCE radippool_seq START WITH 1 INCREMENT BY 1; |
| 19 | |
| 20 | CREATE OR REPLACE TRIGGER radippool_serialnumber |
| 21 | BEFORE INSERT OR UPDATE OF id ON radippool |
| 22 | FOR EACH ROW |
| 23 | BEGIN |
| 24 | if ( :new.id = 0 or :new.id is null ) then |
| 25 | SELECT radippool_seq.nextval into :new.id from dual; |
| 26 | end if; |
| 27 | END; |
| 28 | / |