Postgresql 8.2.3 chm
http://wordpress.conch520.com.cn/chm/postgresql/
http://doc.itchinese.com/
http://www.iteye.com/topic/13042?page=1
http://www.iteye.com/topic/13042?page=6
http://hi.baidu.com/ecaol/blog/item/bda0d01622b16519962b4339.html
ConnectionPool .java
package com.javaeye.lindows.database; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.Driver; import java.sql.DriverManager; import java.sql.sqlException; import java.sql.Statement; import java.util.Enumeration; import java.util.Vector; /** * @author Lindows http://wordpress.conch520.com.cn/chm/postgresql/ http://hi.baidu.com/ecaol/blog/item/bda0d01622b16519962b4339.html */ public class ConnectionPool { private String jdbcDriver = ""; // 数据库驱动 private String dbUrl = ""; // 数据 URL private String dbUsername = ""; // 数据库用户名 private String dbPassword = ""; // 数据库用户密码 private String testTable = ""; // 测试连接是否可用的测试表名，默认没有测试表 private int initialConnections = 10; // 连接池的初始大小 private int incrementalConnections = 5;// 连接池自动增加的大小 private int maxConnections = 50; // 连接池最大的大小 private Vector connections = null; // 存放连接池中数据库连接的向量,初始时为 null // 它中存放的对象为 PooledConnection 型 /** * * 构造函数 * * @param jdbcDriver * String JDBC 驱动类串 * @param dbUrl * String 数据库 URL * @param dbUsername * String 连接数据库用户名 * @param dbPassword * String 连接数据库用户的密码 */ public ConnectionPool(String jdbcDriver,String dbUrl,String dbUsername,String dbPassword) { this.jdbcDriver = jdbcDriver; this.dbUrl = dbUrl; this.dbUsername = dbUsername; this.dbPassword = dbPassword; } /** * 返回连接池的初始大小 * * @return 初始连接池中可获得的连接数量 */ public int getInitialConnections() { return this.initialConnections; } /** * 设置连接池的初始大小 * * @param 用于设置初始连接池中连接的数量 */ public void setInitialConnections(int initialConnections) { this.initialConnections = initialConnections; } /** * 返回连接池自动增加的大小 、 * * @return 连接池自动增加的大小 */ public int getIncrementalConnections() { return this.incrementalConnections; } /** * 设置连接池自动增加的大小 * * @param 连接池自动增加的大小 */ public void setIncrementalConnections(int incrementalConnections) { this.incrementalConnections = incrementalConnections; } /** * 返回连接池中最大的可用连接数量 * * @return 连接池中最大的可用连接数量 */ public int getMaxConnections() { return this.maxConnections; } /** * 设置连接池中最大可用的连接数量 * * @param 设置连接池中最大可用的连接数量值 */ public void setMaxConnections(int maxConnections) { this.maxConnections = maxConnections; } /** * 获取测试数据库表的名字 * * @return 测试数据库表的名字 */ public String getTestTable() { return this.testTable; } /** * 设置测试表的名字 * * @param testTable * String 测试表的名字 */ public void setTestTable(String testTable) { this.testTable = testTable; } /** * 创建一个数据库连接池，连接池中的可用连接的数量采用类成员 initialConnections 中设置的值 */ public synchronized void createPool() throws Exception { // 确保连接池没有创建 // 如果连接池己经创建了，保存连接的向量 connections 不会为空 if (connections != null) { return; // 如果己经创建，则返回 } // 实例化 JDBC Driver 中指定的驱动类实例 Driver driver = (Driver) (Class.forName(this.jdbcDriver).newInstance()); DriverManager.registerDriver(driver); // 注册 JDBC 驱动程序 // 创建保存连接的向量,初始时有 0 个元素 connections = new Vector(); // 根据 initialConnections 中设置的值，创建连接。 createConnections(this.initialConnections); System.out.println(" 数据库连接池创建成功！ "); } /** * 创建由 numConnections 指定数目的数据库连接,并把这些连接 放入 connections 向量中 * * @param numConnections * 要创建的数据库连接的数目 */ @SuppressWarnings("unchecked") private void createConnections(int numConnections) throws sqlException { // 循环创建指定数目的数据库连接 for (int x = 0; x < numConnections; x++) { // 是否连接池中的数据库连接的数量己经达到最大？最大值由类成员 maxConnections // 指出，如果 maxConnections 为 0 或负数，表示连接数量没有限制。 // 如果连接数己经达到最大，即退出。 if (this.maxConnections > 0 && this.connections.size() >= this.maxConnections) { break; } // add a new PooledConnection object to connections vector // 增加一个连接到连接池中（向量 connections 中） try { connections.addElement(new PooledConnection(newConnection())); } catch (sqlException e) { System.out.println(" 创建数据库连接失败！ " + e.getMessage()); throw new sqlException(); } System.out.println(" 数据库连接己创建 ......"); } } /** * 创建一个新的数据库连接并返回它 * * @return 返回一个新创建的数据库连接 */ private Connection newConnection() throws sqlException { // 创建一个数据库连接 Connection conn = DriverManager.getConnection(dbUrl,dbUsername,dbPassword); // 如果这是第一次创建数据库连接，即检查数据库，获得此数据库允许支持的 // 最大客户连接数目 // connections.size()==0 表示目前没有连接己被创建 if (connections.size() == 0) { DatabaseMetaData MetaData = conn.getMetaData(); int driverMaxConnections = MetaData.getMaxConnections(); // 数据库返回的 driverMaxConnections 若为 0 ，表示此数据库没有最大 // 连接限制，或数据库的最大连接限制不知道 // driverMaxConnections 为返回的一个整数，表示此数据库允许客户连接的数目 // 如果连接池中设置的最大连接数量大于数据库允许的连接数目,则置连接池的最大 // 连接数目为数据库允许的最大数目 if (driverMaxConnections > 0 && this.maxConnections > driverMaxConnections) { this.maxConnections = driverMaxConnections; } } return conn; // 返回创建的新的数据库连接 } /** * * 通过调用 getFreeConnection() 函数返回一个可用的数据库连接,如果当前没有可用的数据库连接，并且更多的数据库连接不能创 * 建（如连接池大小的限制），此函数等待一会再尝试获取。 * * @return 返回一个可用的数据库连接对象 */ public synchronized Connection getConnection() throws sqlException { // 确保连接池己被创建 if (connections == null) { return null; // 连接池还没创建，则返回 null } Connection conn = getFreeConnection(); // 获得一个可用的数据库连接 // 如果目前没有可以使用的连接，即所有的连接都在使用中 while (conn == null) { // 等一会再试 wait(250); conn = getFreeConnection(); // 重新再试，直到获得可用的连接，如果 // getFreeConnection() 返回的为 null // 则表明创建一批连接后也不可获得可用连接 } return conn;// 返回获得的可用的连接 } /** * * 本函数从连接池向量 connections 中返回一个可用的的数据库连接，如果 * * 当前没有可用的数据库连接，本函数则根据 incrementalConnections 设置 * * 的值创建几个数据库连接，并放入连接池中。 * * 如果创建后，所有的连接仍都在使用中，则返回 null * * @return 返回一个可用的数据库连接 */ private Connection getFreeConnection() throws sqlException { // 从连接池中获得一个可用的数据库连接 Connection conn = findFreeConnection(); if (conn == null) { // 如果目前连接池中没有可用的连接 // 创建一些连接 createConnections(incrementalConnections); // 重新从池中查找是否有可用连接 conn = findFreeConnection(); if (conn == null) { // 如果创建连接后仍获得不到可用的连接，则返回 null return null; } } return conn; } /** * * 查找连接池中所有的连接，查找一个可用的数据库连接， * * 如果没有可用的连接，返回 null * * * * @return 返回一个可用的数据库连接 */ private Connection findFreeConnection() throws sqlException { Connection conn = null; PooledConnection pConn = null; // 获得连接池向量中所有的对象 Enumeration enumerate = connections.elements(); // 遍历所有的对象，看是否有可用的连接 while (enumerate.hasMoreElements()) { pConn = (PooledConnection) enumerate.nextElement(); if (!pConn.isBusy()) { // 如果此对象不忙，则获得它的数据库连接并把它设为忙 conn = pConn.getConnection(); pConn.setBusy(true); // 测试此连接是否可用 if (!testConnection(conn)) { // 如果此连接不可再用了，则创建一个新的连接， // 并替换此不可用的连接对象，如果创建失败，返回 null try { conn = newConnection(); } catch (sqlException e) { System.out.println(" 创建数据库连接失败！ " + e.getMessage()); return null; } pConn.setConnection(conn); } break; // 己经找到一个可用的连接，退出 } } return conn;// 返回找到到的可用连接 } /** * * 测试一个连接是否可用，如果不可用，关掉它并返回 false * * 否则可用返回 true * * * * @param conn * 需要测试的数据库连接 * * @return 返回 true 表示此连接可用， false 表示不可用 */ private boolean testConnection(Connection conn) { try { // 判断测试表是否存在 if (testTable.equals("")) { // 如果测试表为空，试着使用此连接的 setAutoCommit() 方法 // 来判断连接否可用（此方法只在部分数据库可用，如果不可用,// 抛出异常）。注意：使用测试表的方法更可靠 conn.setAutoCommit(true); } else {// 有测试表的时候使用测试表测试 // check if this connection is valid Statement stmt = conn.createStatement(); stmt.execute("select count(*) from " + testTable); } } catch (sqlException e) { // 上面抛出异常，此连接己不可用，关闭它，并返回 false; closeConnection(conn); return false; } // 连接可用，返回 true return true; } /** * * 此函数返回一个数据库连接到连接池中，并把此连接置为空闲。 * * 所有使用连接池获得的数据库连接均应在不使用此连接时返回它。 * * * * @param 需返回到连接池中的连接对象 */ public void returnConnection(Connection conn) { // 确保连接池存在，如果连接没有创建（不存在），直接返回 if (connections == null) { System.out.println(" 连接池不存在，无法返回此连接到连接池中 !"); return; } PooledConnection pConn = null; Enumeration enumerate = connections.elements(); // 遍历连接池中的所有连接，找到这个要返回的连接对象 while (enumerate.hasMoreElements()) { pConn = (PooledConnection) enumerate.nextElement(); // 先找到连接池中的要返回的连接对象 if (conn == pConn.getConnection()) { // 找到了,设置此连接为空闲状态 pConn.setBusy(false); break; } } } /** * * 刷新连接池中所有的连接对象 * * */ public synchronized void refreshConnections() throws sqlException { // 确保连接池己创新存在 if (connections == null) { System.out.println(" 连接池不存在，无法刷新 !"); return; } PooledConnection pConn = null; Enumeration enumerate = connections.elements(); while (enumerate.hasMoreElements()) { // 获得一个连接对象 pConn = (PooledConnection) enumerate.nextElement(); // 如果对象忙则等 5 秒,5 秒后直接刷新 if (pConn.isBusy()) { wait(5000); // 等 5 秒 } // 关闭此连接，用一个新的连接代替它。 closeConnection(pConn.getConnection()); pConn.setConnection(newConnection()); pConn.setBusy(false); } } /** * * 关闭连接池中所有的连接，并清空连接池。 */ public synchronized void closeConnectionPool() throws sqlException { // 确保连接池存在，如果不存在，返回 if (connections == null) { System.out.println(" 连接池不存在，无法关闭 !"); return; } PooledConnection pConn = null; Enumeration enumerate = connections.elements(); while (enumerate.hasMoreElements()) { pConn = (PooledConnection) enumerate.nextElement(); // 如果忙，等 5 秒 if (pConn.isBusy()) { wait(5000); // 等 5 秒 } // 5 秒后直接关闭它 closeConnection(pConn.getConnection()); // 从连接池向量中删除它 connections.removeElement(pConn); } // 置连接池为空 connections = null; } /** * * 关闭一个数据库连接 * * * * @param 需要关闭的数据库连接 */ private void closeConnection(Connection conn) { try { conn.close(); } catch (sqlException e) { System.out.println(" 关闭数据库连接出错： " + e.getMessage()); } } /** * * 使程序等待给定的毫秒数 * * * * @param 给定的毫秒数 */ private void wait(int mSeconds) { try { Thread.sleep(mSeconds); } catch (InterruptedException e) { } } /** * * * * 内部使用的用于保存连接池中连接对象的类 * * 此类中有两个成员，一个是数据库的连接，另一个是指示此连接是否 * * 正在使用的标志。 */ class PooledConnection { Connection connection = null;// 数据库连接 boolean busy = false; // 此连接是否正在使用的标志，默认没有正在使用 // 构造函数，根据一个 Connection 构告一个 PooledConnection 对象 public PooledConnection(Connection connection) { this.connection = connection; } // 返回此对象中的连接 public Connection getConnection() { return connection; } // 设置此对象的，连接 public void setConnection(Connection connection) { this.connection = connection; } // 获得对象连接是否忙 public boolean isBusy() { return busy; } // 设置对象的连接正在忙 public void setBusy(boolean busy) { this.busy = busy; } } } // ======================================= // // 这个例子是根据POSTGREsql数据库写的， // 请用的时候根据实际的数据库调整。 // // 调用方法如下： // // ① ConnectionPool connPool // = new ConnectionPool("org.postgresql.Driver" //,"jdbc:postgresql://dbURI:5432/DBName" //,"postgre" //,"postgre"); // // ② connPool .createPool(); // Connection conn = connPool .getConnection();
-- -- Postgresql database dump -- -- Started on 2008-04-24 18:05:37 SET client_encoding = 'UTF8'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; SET search_path = public,pg_catalog; -- -- TOC entry 1506 (class 1259 OID 19105) -- Dependencies: 6 -- Name: seq_crm_attachment; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_attachment INCREMENT BY 1 MAXVALUE 99999999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_attachment OWNER TO root; -- -- TOC entry 1879 (class 0 OID 0) -- Dependencies: 1506 -- Name: seq_crm_attachment; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_attachment',8,true); SET default_tablespace = ''; SET default_with_oids = false; -- -- TOC entry 1507 (class 1259 OID 19107) -- Dependencies: 1796 1797 6 -- Name: attachment; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE attachment ( id integer DEFAULT nextval('seq_crm_attachment'::regclass) NOT NULL,Feedback_id integer NOT NULL,file_name character varying(255),file_path character varying(255) NOT NULL,description character varying(255),file_original_name character varying(255) NOT NULL,file_size integer,mime_type character varying(255),create_time timestamp without time zone DEFAULT now() ); ALTER TABLE public.attachment OWNER TO root; -- -- TOC entry 1520 (class 1259 OID 19344) -- Dependencies: 6 -- Name: seq_crm_audit_log; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_audit_log INCREMENT BY 1 MAXVALUE 9999999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_audit_log OWNER TO root; -- -- TOC entry 1881 (class 0 OID 0) -- Dependencies: 1520 -- Name: seq_crm_audit_log; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_audit_log',1,true); -- -- TOC entry 1508 (class 1259 OID 19115) -- Dependencies: 1798 6 -- Name: audit_log; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE audit_log ( id integer DEFAULT nextval('seq_crm_audit_log'::regclass) NOT NULL,user_id integer NOT NULL,log_event character varying(255),event_type character varying(255),log_time timestamp without time zone ); ALTER TABLE public.audit_log OWNER TO root; -- -- TOC entry 1519 (class 1259 OID 19341) -- Dependencies: 6 -- Name: seq_crm_Feedback; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_Feedback START WITH 1 INCREMENT BY 1 MAXVALUE 99999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_Feedback OWNER TO root; -- -- TOC entry 1884 (class 0 OID 0) -- Dependencies: 1519 -- Name: seq_crm_Feedback; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_Feedback',false); -- -- TOC entry 1509 (class 1259 OID 19122) -- Dependencies: 1799 1800 6 -- Name: Feedback; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE Feedback ( id integer DEFAULT nextval('seq_crm_Feedback'::regclass) NOT NULL,creator_user_id integer NOT NULL,referral_id integer NOT NULL,action_master_id integer NOT NULL,message text,create_time timestamp without time zone DEFAULT now() ); ALTER TABLE public.Feedback OWNER TO root; -- -- TOC entry 1521 (class 1259 OID 19347) -- Dependencies: 6 -- Name: seq_crm_mail_queue; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_mail_queue START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_mail_queue OWNER TO root; -- -- TOC entry 1885 (class 0 OID 0) -- Dependencies: 1521 -- Name: seq_crm_mail_queue; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_mail_queue',false); -- -- TOC entry 1510 (class 1259 OID 19130) -- Dependencies: 1801 1802 6 -- Name: mail_queue; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE mail_queue ( id integer DEFAULT nextval('seq_crm_mail_queue'::regclass) NOT NULL,subject character varying(45) NOT NULL,send_time timestamp without time zone DEFAULT now(),failure_count smallint,mail_from character varying(255),mail_to character varying(255),mail_cc character varying(255),dead character(1) ); ALTER TABLE public.mail_queue OWNER TO root; -- -- TOC entry 1522 (class 1259 OID 19350) -- Dependencies: 6 -- Name: seq_crm_master; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_master START WITH 1 INCREMENT BY 1 MAXVALUE 99999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_master OWNER TO root; -- -- TOC entry 1887 (class 0 OID 0) -- Dependencies: 1522 -- Name: seq_crm_master; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_master',false); -- -- TOC entry 1511 (class 1259 OID 19138) -- Dependencies: 1803 6 -- Name: master; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE master ( id integer DEFAULT nextval('seq_crm_master'::regclass) NOT NULL,master_type_id integer NOT NULL,master_order smallint NOT NULL,master_name character varying(255) NOT NULL,master_value character varying(255),master_format character varying(255),parent_id integer ); ALTER TABLE public.master OWNER TO root; -- -- TOC entry 1523 (class 1259 OID 19353) -- Dependencies: 6 -- Name: seq_crm_master_type; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_master_type START WITH 1 INCREMENT BY 1 MAXVALUE 999999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_master_type OWNER TO root; -- -- TOC entry 1889 (class 0 OID 0) -- Dependencies: 1523 -- Name: seq_crm_master_type; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_master_type',false); -- -- TOC entry 1512 (class 1259 OID 19145) -- Dependencies: 1804 6 -- Name: master_type; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE master_type ( id integer DEFAULT nextval('seq_crm_master_type'::regclass) NOT NULL,description character varying(255) ); ALTER TABLE public.master_type OWNER TO root; -- -- TOC entry 1524 (class 1259 OID 19356) -- Dependencies: 6 -- Name: seq_crm_office; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_office START WITH 1 INCREMENT BY 1 MAXVALUE 99999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_office OWNER TO root; -- -- TOC entry 1891 (class 0 OID 0) -- Dependencies: 1524 -- Name: seq_crm_office; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_office',false); -- -- TOC entry 1513 (class 1259 OID 19149) -- Dependencies: 1805 6 -- Name: office; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE office ( id integer DEFAULT nextval('seq_crm_office'::regclass) NOT NULL,office_type character(1) NOT NULL,office_name character varying(255) NOT NULL,addr_street character varying(255),office_code character varying(255) NOT NULL,addr_city character varying(255),addr_postcode character varying(255),phone character varying(255),fax character varying(255),status_master_id integer,addr_province_master_id integer NOT NULL ); ALTER TABLE public.office OWNER TO root; -- -- TOC entry 1525 (class 1259 OID 19359) -- Dependencies: 6 -- Name: seq_crm_patient; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_patient START WITH 1 INCREMENT BY 1 MAXVALUE 99999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_patient OWNER TO root; -- -- TOC entry 1893 (class 0 OID 0) -- Dependencies: 1525 -- Name: seq_crm_patient; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_patient',false); -- -- TOC entry 1514 (class 1259 OID 19156) -- Dependencies: 1806 1807 6 -- Name: patient; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE patient ( id integer DEFAULT nextval('seq_crm_patient'::regclass) NOT NULL,referring_office_id integer NOT NULL,hc_type_master_id integer,hc_number character varying(255),hc_version character varying(255),emr_id character varying(255),first_name character varying(255) NOT NULL,last_name character varying(255) NOT NULL,birthday date,sex character(1),addr_province_master_id integer,home_phone character varying(255),work_phone character varying(255),cell_phone character varying(255),contact_person_name character varying(255),contact_person_phone character varying(255),update_time time without time zone,create_time time without time zone DEFAULT now() ); ALTER TABLE public.patient OWNER TO root; -- -- TOC entry 1526 (class 1259 OID 19362) -- Dependencies: 6 -- Name: seq_crm_referral; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_referral START WITH 1 INCREMENT BY 1 MAXVALUE 999999999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_referral OWNER TO root; -- -- TOC entry 1894 (class 0 OID 0) -- Dependencies: 1526 -- Name: seq_crm_referral; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_referral',false); -- -- TOC entry 1515 (class 1259 OID 19164) -- Dependencies: 1808 1809 6 -- Name: referral; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE referral ( id integer DEFAULT nextval('seq_crm_referral'::regclass) NOT NULL,ref_status_master_id integer NOT NULL,patient_id integer NOT NULL,ref_office_id integer NOT NULL,ref_doc_user_id integer NOT NULL,int_office_id integer NOT NULL,int_doc_user_id integer,urgency_master_id integer NOT NULL,notifier_office_id integer,special_request text,create_time timestamp without time zone DEFAULT now(),update_time timestamp without time zone,appt_id character varying(255),appt_time timestamp without time zone,ref_detail_xml character varying(255) NOT NULL,referral_process_flag character(1) NOT NULL ); ALTER TABLE public.referral OWNER TO root; -- -- TOC entry 1527 (class 1259 OID 19365) -- Dependencies: 6 -- Name: seq_crm_role; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_role START WITH 1 INCREMENT BY 1 MAXVALUE 99999999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_role OWNER TO root; -- -- TOC entry 1896 (class 0 OID 0) -- Dependencies: 1527 -- Name: seq_crm_role; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_role',false); -- -- TOC entry 1516 (class 1259 OID 19172) -- Dependencies: 1810 6 -- Name: role; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE role ( id integer DEFAULT nextval('seq_crm_role'::regclass) NOT NULL,role_name character varying(25) ); ALTER TABLE public.role OWNER TO root; -- -- TOC entry 1528 (class 1259 OID 19368) -- Dependencies: 6 -- Name: seq_crm_user; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_user START WITH 1 INCREMENT BY 1 MAXVALUE 99999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_user OWNER TO root; -- -- TOC entry 1898 (class 0 OID 0) -- Dependencies: 1528 -- Name: seq_crm_user; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_user',false); -- -- TOC entry 1517 (class 1259 OID 19176) -- Dependencies: 1811 6 -- Name: user; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE "user" ( id integer DEFAULT nextval('seq_crm_user'::regclass) NOT NULL,default_user_office_role_id integer NOT NULL,email character varying(255) NOT NULL,password character varying(255) NOT NULL,title character varying(255),last_name character varying(255),private_phone character varying(255),office_phone character varying(255),status_master_id integer NOT NULL,default_filters_xml text,expiry_date date ); ALTER TABLE public."user" OWNER TO root; -- -- TOC entry 1529 (class 1259 OID 19371) -- Dependencies: 6 -- Name: seq_crm_user_office_role; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE seq_crm_user_office_role START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999999 NO MINVALUE CACHE 1; ALTER TABLE public.seq_crm_user_office_role OWNER TO root; -- -- TOC entry 1900 (class 0 OID 0) -- Dependencies: 1529 -- Name: seq_crm_user_office_role; Type: SEQUENCE SET; Schema: public; Owner: root -- SELECT pg_catalog.setval('seq_crm_user_office_role',false); -- -- TOC entry 1518 (class 1259 OID 19183) -- Dependencies: 1812 6 -- Name: user_office_role; Type: TABLE; Schema: public; Owner: root; Tablespace: -- CREATE TABLE user_office_role ( id integer DEFAULT nextval('seq_crm_user_office_role'::regclass) NOT NULL,office_id integer NOT NULL,role_id integer NOT NULL,provider_id character varying(45) ); INSERT INTO "master" ("id","master_type_id","master_order","master_name","master_value","master_format","parent_id") VALUES (1001,'CATARACT',NULL,NULL),(1002,2,'Ready for surgery',1001),(1003,3,'Patient undecided',(1004,4,'Premium Options Discussed',(1005,5,'RETINA',(1006,6,'Diabetes',1005),(1007,7,'ARMD',(1008,'Retinal breaks',(1009,9,'Plaquenil check',(1010,10,'GLAUCOMA',(1011,11,'High IOP',1010),(1012,12,'Field loss',(1013,13,'Disc cupping',(1014,14,'Narrow angles',(1015,15,'PLASTICS',(1016,16,'Eyelid / Conjunctiva',1015),(1017,17,'Tearing',(1018,18,'Orbit',(1019,19,'Cosmetic',(2001,'Referral',(2002,'Accept',(2003,'Feedback',(2004,'Reject',(2005,'Reschedule',(2006,'Cancel',(2007,'Complete',(2008,'Notify(Not yet)',(2009,'Notify(L/M)',(2010,'Notify(N/A)',(2011,'Notify(C/F)',(2012,'no-show',(3001,(3002,(3003,'Booked',(3004,'Awaiting',(3005,(3006,(3007,'Booked(L/M)',(3008,'Booked(N/A)',(3009,'Booked(C/F)',(4001,'PDF',(4002,'Newfoundland-Labrador',(5001,'Ontario',(5002,'Alberta',(5003,'British Columbia',(5004,'Manitoba',(5005,'New Brunswick',(5006,'Newfoundland and Labrador',(5007,'Northwest Territories',(5008,'Nova Scotia',(5009,'Nunavut Territory',(5010,'Prince Edward Island',(5011,'Quebec',(5012,'Saskatchewan',(5013,'Yukon Territory',(5014,'Other',(6001,'Routine',(6002,'ASAP',(6003,'Urgent',(7001,'Deactived',(7002,'Activated',(7003,'Invited',(7004,'Deleted',(8001,'Upload file max size','2',(8002,'Sent mail times','3',(8003,'User login Failed times',(8004,'FlipView has some days',(8005,'User login Failed time limit','5',NULL); INSERT INTO "master_type" ("id","description") VALUES (1,'Reason For Referral'),(2,'Feedback Status'),(3,'Referral Status'),(4,'File type'),(5,'Province Name'),(6,'Referral Priority'),(7,'User or Office Status'),(8,'System Prefernce'); INSERT INTO "role" ("id","role_name") VALUES (1,'Admin'),'Referral Doctor'),'Referral Secretary'),'Internal Doctor'),'Internal Secretary'); INSERT INTO "user" ("id","default_user_office_role_id","email","password","title","first_name","last_name","private_phone","cell_phone","office_phone","status_master_id","default_filters_xml","expiry_date") VALUES (1,'admin@163.com','96E79218965EB72C92A549DD5A330112','yang','yajun','(250) 250-2500','',7002,NULL); COMMIT; ALTER TABLE public.user_office_role OWNER TO root; -- TOC entry 1792 (class 2606 OID 17013) -- Dependencies: 1496 1496 -- Name: pk_attachment_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY attachment ADD CONSTRAINT pk_attachment_id PRIMARY KEY (id); -- -- TOC entry 1794 (class 2606 OID 17020) -- Dependencies: 1497 1497 -- Name: pk_audit_log; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY audit_log ADD CONSTRAINT pk_audit_log PRIMARY KEY (id); -- -- TOC entry 1796 (class 2606 OID 17011) -- Dependencies: 1498 1498 -- Name: pk_Feedback_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY Feedback ADD CONSTRAINT pk_Feedback_id PRIMARY KEY (id); -- -- TOC entry 1798 (class 2606 OID 17022) -- Dependencies: 1499 1499 -- Name: pk_mail_queue_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY mail_queue ADD CONSTRAINT pk_mail_queue_id PRIMARY KEY (id); -- -- TOC entry 1800 (class 2606 OID 17024) -- Dependencies: 1500 1500 -- Name: pk_master_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY master ADD CONSTRAINT pk_master_id PRIMARY KEY (id); -- -- TOC entry 1802 (class 2606 OID 17026) -- Dependencies: 1501 1501 -- Name: pk_master_type_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY master_type ADD CONSTRAINT pk_master_type_id PRIMARY KEY (id); -- -- TOC entry 1804 (class 2606 OID 17028) -- Dependencies: 1502 1502 -- Name: pk_office_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY office ADD CONSTRAINT pk_office_id PRIMARY KEY (id); -- -- TOC entry 1806 (class 2606 OID 17030) -- Dependencies: 1503 1503 -- Name: pk_patient_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY patient ADD CONSTRAINT pk_patient_id PRIMARY KEY (id); -- -- TOC entry 1808 (class 2606 OID 17304) -- Dependencies: 1504 1504 -- Name: pk_referral_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY referral ADD CONSTRAINT pk_referral_id PRIMARY KEY (id); -- -- TOC entry 1810 (class 2606 OID 17306) -- Dependencies: 1505 1505 -- Name: pk_role_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY role ADD CONSTRAINT pk_role_id PRIMARY KEY (id); -- -- TOC entry 1812 (class 2606 OID 17308) -- Dependencies: 1506 1506 -- Name: pk_user_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY "user" ADD CONSTRAINT pk_user_id PRIMARY KEY (id); -- -- TOC entry 1814 (class 2606 OID 17310) -- Dependencies: 1507 1507 -- Name: pk_user_office_role_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace: -- ALTER TABLE ONLY user_office_role ADD CONSTRAINT pk_user_office_role_id PRIMARY KEY (id); -- -- TOC entry 1818 (class 2606 OID 17311) -- Dependencies: 1500 1799 1498 -- Name: action_master-id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY Feedback ADD CONSTRAINT "action_master-id" FOREIGN KEY (action_master_id) REFERENCES master(id); -- -- TOC entry 1823 (class 2606 OID 17316) -- Dependencies: 1500 1799 1502 -- Name: addr_province_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY office ADD CONSTRAINT addr_province_master_id FOREIGN KEY (addr_province_master_id) REFERENCES master(id); -- -- TOC entry 1819 (class 2606 OID 17321) -- Dependencies: 1506 1811 1498 -- Name: create_user_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY Feedback ADD CONSTRAINT create_user_id FOREIGN KEY (creator_user_id) REFERENCES "user"(id); -- -- TOC entry 1816 (class 2606 OID 17326) -- Dependencies: 1496 1498 1795 -- Name: Feedback_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY attachment ADD CONSTRAINT Feedback_id FOREIGN KEY (Feedback_id) REFERENCES Feedback(id); -- -- TOC entry 1815 (class 2606 OID 17014) -- Dependencies: 1498 1496 1795 -- Name: fk_Feedback_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY attachment ADD CONSTRAINT fk_Feedback_id FOREIGN KEY (Feedback_id) REFERENCES Feedback(id); -- -- TOC entry 1817 (class 2606 OID 17331) -- Dependencies: 1811 1497 1506 -- Name: fk_user_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY audit_log ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES "user"(id); -- -- TOC entry 1825 (class 2606 OID 17336) -- Dependencies: 1799 1500 1503 -- Name: hctype_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY patient ADD CONSTRAINT hctype_master_id FOREIGN KEY (hc_type_master_id) REFERENCES master(id); -- -- TOC entry 1828 (class 2606 OID 17341) -- Dependencies: 1504 1506 1811 -- Name: int_doc_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY referral ADD CONSTRAINT int_doc_id FOREIGN KEY (int_doc_user_id) REFERENCES "user"(id); -- -- TOC entry 1829 (class 2606 OID 17346) -- Dependencies: 1502 1803 1504 -- Name: int_office_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY referral ADD CONSTRAINT int_office_id FOREIGN KEY (int_office_id) REFERENCES office(id); -- -- TOC entry 1826 (class 2606 OID 17351) -- Dependencies: 1503 1500 1799 -- Name: mast_province_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY patient ADD CONSTRAINT mast_province_id FOREIGN KEY (addr_province_master_id) REFERENCES master(id); -- -- TOC entry 1824 (class 2606 OID 17356) -- Dependencies: 1502 1799 1500 -- Name: master_status_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY office ADD CONSTRAINT master_status_id FOREIGN KEY (status_master_id) REFERENCES master(id); -- -- TOC entry 1821 (class 2606 OID 17361) -- Dependencies: 1501 1500 1801 -- Name: master_type_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY master ADD CONSTRAINT master_type_id FOREIGN KEY (master_type_id) REFERENCES master_type(id); -- -- TOC entry 1830 (class 2606 OID 17366) -- Dependencies: 1504 1803 1502 -- Name: notifier_office_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY referral ADD CONSTRAINT notifier_office_id FOREIGN KEY (notifier_office_id) REFERENCES office(id); -- -- TOC entry 1838 (class 2606 OID 17371) -- Dependencies: 1502 1803 1507 -- Name: office_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY user_office_role ADD CONSTRAINT office_id FOREIGN KEY (office_id) REFERENCES office(id); -- -- TOC entry 1822 (class 2606 OID 17376) -- Dependencies: 1799 1500 1500 -- Name: parent_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY master ADD CONSTRAINT parent_id FOREIGN KEY (parent_id) REFERENCES master(id); -- -- TOC entry 1827 (class 2606 OID 17381) -- Dependencies: 1503 1803 1502 -- Name: pffice_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY patient ADD CONSTRAINT pffice_id FOREIGN KEY (referring_office_id) REFERENCES office(id); -- -- TOC entry 1831 (class 2606 OID 17386) -- Dependencies: 1811 1506 1504 -- Name: ref_doc_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY referral ADD CONSTRAINT ref_doc_id FOREIGN KEY (ref_doc_user_id) REFERENCES "user"(id); -- -- TOC entry 1832 (class 2606 OID 17391) -- Dependencies: 1803 1504 1502 -- Name: ref_office_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY referral ADD CONSTRAINT ref_office_id FOREIGN KEY (ref_office_id) REFERENCES office(id); -- -- TOC entry 1820 (class 2606 OID 17396) -- Dependencies: 1498 1807 1504 -- Name: referral_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY Feedback ADD CONSTRAINT referral_id FOREIGN KEY (referral_id) REFERENCES referral(id); -- -- TOC entry 1833 (class 2606 OID 17401) -- Dependencies: 1504 1805 1503 -- Name: referral_patient_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY referral ADD CONSTRAINT referral_patient_id FOREIGN KEY (patient_id) REFERENCES patient(id); -- -- TOC entry 1834 (class 2606 OID 17406) -- Dependencies: 1500 1799 1504 -- Name: referral_status_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY referral ADD CONSTRAINT referral_status_master_id FOREIGN KEY (ref_status_master_id) REFERENCES master(id); -- -- TOC entry 1839 (class 2606 OID 17411) -- Dependencies: 1809 1507 1505 -- Name: role_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY user_office_role ADD CONSTRAINT role_id FOREIGN KEY (role_id) REFERENCES role(id); -- -- TOC entry 1836 (class 2606 OID 17416) -- Dependencies: 1500 1506 1799 -- Name: status_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY "user" ADD CONSTRAINT status_master_id FOREIGN KEY (status_master_id) REFERENCES master(id); -- -- TOC entry 1835 (class 2606 OID 17421) -- Dependencies: 1500 1799 1504 -- Name: urgency_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY referral ADD CONSTRAINT urgency_master_id FOREIGN KEY (urgency_master_id) REFERENCES master(id); -- -- TOC entry 1840 (class 2606 OID 17426) -- Dependencies: 1507 1811 1506 -- Name: user_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY user_office_role ADD CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES "user"(id); -- -- TOC entry 1837 (class 2606 OID 17431) -- Dependencies: 1813 1507 1506 -- Name: user_office_role_id; Type: FK CONSTRAINT; Schema: public; Owner: root -- ALTER TABLE ONLY "user" ADD CONSTRAINT user_office_role_id FOREIGN KEY (default_user_office_role_id) REFERENCES user_office_role(id); -- -- TOC entry 1856 (class 0 OID 0) -- Dependencies: 3 -- Name: public; Type: ACL; Schema: -; Owner: root -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM root; GRANT ALL ON SCHEMA public TO root; GRANT ALL ON SCHEMA public TO PUBLIC; -- Completed on 2008-04-24 18:05:38 -- -- Postgresql database dump complete --
连接池原理图.rar
http://dl.iteye.com/topics/download/9f84c62e-20c3-3194-94c6-7dfdc0def9b8
end
