| 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 VARCHAR(64) 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_ipaddr 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 | / |