From 5b951784ab5510d1e6c56e7479dfb12e2a745caf Mon Sep 17 00:00:00 2001
From: pnixon <pnixon>
Date: Fri, 18 Mar 2005 18:46:00 +0000
Subject: [PATCH] userinfo.sql for oracle

---
 sql/oracle/userinfo.sql | 34 ++++++++++++++++++++++++++++++++++
 1 file changed, 34 insertions(+)
 create mode 100644 sql/oracle/userinfo.sql

diff --git a/sql/oracle/userinfo.sql b/sql/oracle/userinfo.sql
new file mode 100644
index 0000000..0b73f62
--- /dev/null
+++ b/sql/oracle/userinfo.sql
@@ -0,0 +1,34 @@
+/*
+ * $Id$
+ *
+ */
+
+/*
+ * Table structure for table 'userinfo'
+ */
+
+CREATE TABLE userinfo (
+    id INT PRIMARY KEY,
+    username VARCHAR(128),
+    name VARCHAR(128),
+    mail VARCHAR(128),
+    department VARCHAR(128),
+    workphone VARCHAR(128),
+    homephone VARCHAR(128),
+    mobile VARCHAR(128) 
+);
+CREATE INDEX userinfo_department_idx ON userinfo (department);
+CREATE INDEX userinfo_username_idx ON userinfo (username);
+CREATE SEQUENCE userinfo_seq START WITH 1 INCREMENT BY 1;
+
+
+/* Trigger to emulate a serial # on the primary key */
+CREATE OR REPLACE TRIGGER userinfo_serialnumber
+        BEFORE INSERT OR UPDATE OF id ON userinfo
+        FOR EACH ROW
+        BEGIN
+                if ( :new.id = 0 or :new.id is null ) then
+                        SELECT userinfo_seq.nextval into :new.id from dual;
+                end if;
+        END;
+/
-- 
2.39.5