Postgresql 8.2.3 chm
http://wordpress.conch520.com.cn/chm/postgresql/
http://doc.itchinese.com/
- ·@L_502_1@
- ·PostgreSQL 8.0 中文手册
- ·PostgreSQL JDBC 中文
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