`

db PostgreSQL 8.2.3

PostgreSQLOfficeWordPressCacheSQL 
阅读更多

PostgreSQL 8.2.3 chm

 http://wordpress.conch520.com.cn/chm/postgresql/

 

http://doc.itchinese.com/

MySQL vs PostgreSQL

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', 1, 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', 1, 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,
    message text,
    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', 1, 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', 1, 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', 1, 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', 1, 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_street character varying(255),
    addr_city character varying(255),
    addr_province_master_id integer,
    addr_postcode character varying(255),
    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', 1, 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', 1, 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', 1, 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),
    first_name character varying(255) NOT NULL,
    last_name character varying(255),
    private_phone character varying(255),
    cell_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', 1, 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,
    user_id integer 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,1,1,'CATARACT',NULL,NULL,NULL),
 (1002,1,2,'Ready for surgery',NULL,NULL,1001),
 (1003,1,3,'Patient undecided',NULL,NULL,1001),
 (1004,1,4,'Premium Options Discussed',NULL,NULL,1001),
 (1005,1,5,'RETINA',NULL,NULL,NULL),
 (1006,1,6,'Diabetes',NULL,NULL,1005),
 (1007,1,7,'ARMD',NULL,NULL,1005),
 (1008,1,8,'Retinal breaks',NULL,NULL,1005),
 (1009,1,9,'Plaquenil check',NULL,NULL,1005),
 (1010,1,10,'GLAUCOMA',NULL,NULL,NULL),
 (1011,1,11,'High IOP',NULL,NULL,1010),
 (1012,1,12,'Field loss',NULL,NULL,1010),
 (1013,1,13,'Disc cupping',NULL,NULL,1010),
 (1014,1,14,'Narrow angles',NULL,NULL,1010),
 (1015,1,15,'PLASTICS',NULL,NULL,NULL),
 (1016,1,16,'Eyelid / Conjunctiva',NULL,NULL,1015),
 (1017,1,17,'Tearing',NULL,NULL,1015),
 (1018,1,18,'Orbit',NULL,NULL,1015),
 (1019,1,19,'Cosmetic',NULL,NULL,1015),
 (2001,2,1,'Referral',NULL,NULL,NULL),
 (2002,2,2,'Accept',NULL,NULL,NULL),
 (2003,2,3,'Feedback',NULL,NULL,NULL),
 (2004,2,4,'Reject',NULL,NULL,NULL),
 (2005,2,5,'Reschedule',NULL,NULL,NULL),
 (2006,2,6,'Cancel',NULL,NULL,NULL),
 (2007,2,7,'Complete',NULL,NULL,NULL),
 (2008,2,8,'Notify(Not yet)',NULL,NULL,NULL),
 (2009,2,9,'Notify(L/M)',NULL,NULL,NULL),
 (2010,2,10,'Notify(N/A)',NULL,NULL,NULL),
 (2011,2,11,'Notify(C/F)',NULL,NULL,NULL),
 (2012,2,12,'no-show',NULL,NULL,NULL),
 (3001,3,1,'Cancel',NULL,NULL,NULL),
 (3002,3,2,'Complete',NULL,NULL,NULL),
 (3003,3,3,'Booked',NULL,NULL,NULL),
 (3004,3,4,'Awaiting',NULL,NULL,NULL),
 (3005,3,5,'no-show',NULL,NULL,NULL),
 (3006,3,6,'Reject',NULL,NULL,NULL),
 (3007,3,7,'Booked(L/M)',NULL,NULL,NULL),
 (3008,3,8,'Booked(N/A)',NULL,NULL,NULL),
 (3009,3,9,'Booked(C/F)',NULL,NULL,NULL),
 (4001,4,1,'PDF',NULL,NULL,NULL),
 (4002,4,2,'Newfoundland-Labrador',NULL,NULL,NULL),
 (5001,5,1,'Ontario',NULL,NULL,NULL),
 (5002,5,2,'Alberta',NULL,NULL,NULL),
 (5003,5,3,'British Columbia',NULL,NULL,NULL),
 (5004,5,4,'Manitoba',NULL,NULL,NULL),
 (5005,5,5,'New Brunswick',NULL,NULL,NULL),
 (5006,5,6,'Newfoundland and Labrador',NULL,NULL,NULL),
 (5007,5,7,'Northwest Territories',NULL,NULL,NULL),
 (5008,5,8,'Nova Scotia',NULL,NULL,NULL),
 (5009,5,9,'Nunavut Territory',NULL,NULL,NULL),
 (5010,5,10,'Prince Edward Island',NULL,NULL,NULL),
 (5011,5,11,'Quebec',NULL,NULL,NULL),
 (5012,5,12,'Saskatchewan',NULL,NULL,NULL),
 (5013,5,13,'Yukon Territory',NULL,NULL,NULL),
 (5014,5,14,'Other',NULL,NULL,NULL),
 (6001,6,1,'Routine',NULL,NULL,NULL),
 (6002,6,2,'ASAP',NULL,NULL,NULL),
 (6003,6,3,'Urgent',NULL,NULL,NULL),
 (7001,7,1,'Deactived',NULL,NULL,NULL),
 (7002,7,2,'Activated',NULL,NULL,NULL),
 (7003,7,3,'Invited',NULL,NULL,NULL),
 (7004,7,4,'Deleted',NULL,NULL,NULL),
 (8001,8,1,'Upload file max size','2',NULL,NULL),
 (8002,8,2,'Sent mail times','3',NULL,NULL),
 (8003,8,3,'User login failed times','3',NULL,NULL),
 (8004,8,4,'FlipView has some days','3',NULL,NULL),
 (8005,8,5,'User login failed  time limit','5',NULL,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'),
 (2,'Referral Doctor'),
 (3,'Referral Secretary'),
 (4,'Internal Doctor'),
 (5,'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,1,'admin@163.com','96E79218965EB72C92A549DD5A330112',NULL,'yang','yajun','(250) 250-2500','(250) 250-2500','',7002,NULL,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

 

分享到：
| db oracle dba
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    PostgreSQL 8.2.3 中文文档

    **PostgreSQL 8.2.3 中文文档** PostgreSQL是一种强大的开源关系数据库管理系统，以其高度的稳定性和灵活性而闻名。8.2.3版本是PostgreSQL的一个重要里程碑，它包含了许多增强特性和修复，使得数据库管理更加高效和...

    postgresql8.2.3用户手册API

    《postgresql8.2.3用户手册API》是PostgreSQL数据库系统在8.2.3版本中的应用程序编程接口（API）的详细指南。这份手册对于开发者来说是宝贵的资源，它涵盖了如何利用API与PostgreSQL数据库进行交互的各种技术。下面...

    PostgreSQL 8.2.3 Chinese documents

    PostgreSQL 8.2.3 Chinese documents

    postgresql 8.2.3 中文文档.CHM

    PostgreSQL 8.2.3 是一款开源的关系型数据库管理系统，以其强大、稳定和高度可扩展性而闻名。这个版本的中文文档以CHM（Compiled Help Manual）格式提供，是学习和理解PostgreSQL 8.2.3功能、操作和查询语言的重要...

    PostgreSQL 8.2.3 中文文档.chm

    PostgreSQL 8.2.3 中文文档.chm，网友制作的postgre手册。postgre各项功能及稳定性均好于mysql，且高度遵循sql标准并有所扩展。自8.x版之后，对Windows的支持也非常完善了。推荐使用。

    postgresql8.2.3 中文文档

    PostgreSQL 8.2.3 是一款开源的关系型数据库管理系统，以其强大的功能、高度的可扩展性和稳定性在IT行业中广受赞誉。这个版本的中文文档是开发者和管理员学习和掌握 PostgreSQL 的宝贵资源，包含了深入的技术细节和...

    PostgreSQL_8.2.3.rar_postgresql_windows 8

    PostgreSQL是一种开源关系型数据库管理系统（RDBMS），它的版本8.2.3是该系统的一个重要里程碑。这个版本在2006年发布，带来了许多改进和新特性，使得PostgreSQL在当时更加稳定和功能强大。对于Windows 8用户来说，...

    PostgreSQL_8.2.3_cn.rar_c++ postgresql

    **PostgreSQL 8.2.3 中文版详解** PostgreSQL 是一款强大的开源关系型数据库管理系统，以其高度的稳定性和灵活性著称。该版本8.2.3是2007年发布的一个重要里程碑，引入了许多增强功能和优化，使得数据库性能得到了...

    PostgreSQL_8.2.3_中文文档和PostgreSQL学习手册(API)

    压缩包里包含两个pdf文档：PostgreSQL_8.2.3_中文文档和PostgreSQL学习手册(API)，为了方便学习和查询，文档左侧有书签。 其次，文档不是扫描版的，可以自由的书写，高清。保证最佳选择。

    OLEDB驱动程序大全 PostgreSQL-OleDB-Provider

    在本话题中，我们将深入探讨“OLEDB驱动程序大全”中的一个特定组件——PostgreSQL OLEDB Provider。 PostgreSQL OLEDB Provider是用于连接和操作PostgreSQL数据库的OLEDB驱动程序。PostgreSQL是一种开源的关系型...

    DB2到GreenPlum/PostgreSQL的转换指南

    ### DB2到GreenPlum/PostgreSQL的转换指南 #### 1. 引言 ##### 1.1 目的 本指南旨在帮助用户理解从DB2迁移到GreenPlum或PostgreSQL过程中所涉及的关键技术和注意事项。由于这两种数据库系统之间存在显著差异，因此...

    timescaledb-postgresql-12_1.7.1-windows-amd64.zip

    《TimescaleDB与PostgreSQL12在Windows 64位环境的应用详解》 TimescaleDB，作为一款专为时间序列数据分析而设计的开源数据库扩展，为PostgreSQL提供了强大的功能支持。在本文中，我们将深入探讨TimescaleDB 1.7.1...

    timescaledb-postgresql-13_2.3.0-windows-amd64.zip

    《时序数据库TimescaleDB与PostgreSQL 13的结合应用》 TimescaleDB是一款针对时间序列数据设计的开源数据库扩展，它构建于PostgreSQL之上，提供了高效的时间序列管理和分析功能。在“timescaledb-postgresql-13_...

    阿里云 专有云企业版 V3.12.0 云原生数据仓库 AnalyticDB PostgreSQL 版 产品简介

    【阿里云专有云企业版 V3.12.0 云原生数据仓库 AnalyticDB PostgreSQL 版产品简介】 阿里云专有云企业版V3.12.0中的云原生数据仓库AnalyticDB PostgreSQL版是一款高性能、高并发、低延迟的数据处理解决方案，专门...

Magicbox
Global site tag (gtag.js) - Google Analytics