db PostgreSQL 8.2.3

前端之家收集整理的这篇文章主要介绍了db PostgreSQL 8.2.3前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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

  1. package com.javaeye.lindows.database;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DatabaseMetaData;
  5. import java.sql.Driver;
  6. import java.sql.DriverManager;
  7. import java.sql.sqlException;
  8. import java.sql.Statement;
  9. import java.util.Enumeration;
  10. import java.util.Vector;
  11.  
  12.  
  13. /**
  14. * @author Lindows
  15. http://wordpress.conch520.com.cn/chm/postgresql/
  16. http://hi.baidu.com/ecaol/blog/item/bda0d01622b16519962b4339.html
  17. */
  18. public class ConnectionPool {
  19.  
  20. private String jdbcDriver = ""; // 数据库驱动
  21.  
  22. private String dbUrl = ""; // 数据 URL
  23.  
  24. private String dbUsername = ""; // 数据库用户名
  25.  
  26. private String dbPassword = ""; // 数据库用户密码
  27.  
  28. private String testTable = ""; // 测试连接是否可用的测试表名,默认没有测试表
  29.  
  30. private int initialConnections = 10; // 连接池的初始大小
  31.  
  32. private int incrementalConnections = 5;// 连接池自动增加的大小
  33.  
  34. private int maxConnections = 50; // 连接池最大的大小
  35.  
  36. private Vector connections = null; // 存放连接池中数据库连接的向量,初始时为 null
  37.  
  38. // 它中存放的对象为 PooledConnection 型
  39.  
  40. /**
  41. *
  42. * 构造函数
  43. *
  44. * @param jdbcDriver
  45. * String JDBC 驱动类串
  46. * @param dbUrl
  47. * String 数据库 URL
  48. * @param dbUsername
  49. * String 连接数据库用户名
  50. * @param dbPassword
  51. * String 连接数据库用户的密码
  52. */
  53.  
  54. public ConnectionPool(String jdbcDriver,String dbUrl,String dbUsername,String dbPassword) {
  55.  
  56. this.jdbcDriver = jdbcDriver;
  57.  
  58. this.dbUrl = dbUrl;
  59.  
  60. this.dbUsername = dbUsername;
  61.  
  62. this.dbPassword = dbPassword;
  63.  
  64. }
  65.  
  66. /**
  67. * 返回连接池的初始大小
  68. *
  69. * @return 初始连接池中可获得的连接数量
  70. */
  71.  
  72. public int getInitialConnections() {
  73.  
  74. return this.initialConnections;
  75.  
  76. }
  77.  
  78. /**
  79. * 设置连接池的初始大小
  80. *
  81. * @param 用于设置初始连接池中连接的数量
  82. */
  83.  
  84. public void setInitialConnections(int initialConnections) {
  85.  
  86. this.initialConnections = initialConnections;
  87.  
  88. }
  89.  
  90. /**
  91. * 返回连接池自动增加的大小 、
  92. *
  93. * @return 连接池自动增加的大小
  94. */
  95.  
  96. public int getIncrementalConnections() {
  97.  
  98. return this.incrementalConnections;
  99.  
  100. }
  101.  
  102. /**
  103. * 设置连接池自动增加的大小
  104. *
  105. * @param 连接池自动增加的大小
  106. */
  107.  
  108. public void setIncrementalConnections(int incrementalConnections) {
  109.  
  110. this.incrementalConnections = incrementalConnections;
  111.  
  112. }
  113.  
  114. /**
  115. * 返回连接池中最大的可用连接数量
  116. *
  117. * @return 连接池中最大的可用连接数量
  118. */
  119.  
  120. public int getMaxConnections() {
  121.  
  122. return this.maxConnections;
  123.  
  124. }
  125.  
  126. /**
  127. * 设置连接池中最大可用的连接数量
  128. *
  129. * @param 设置连接池中最大可用的连接数量
  130. */
  131.  
  132. public void setMaxConnections(int maxConnections) {
  133.  
  134. this.maxConnections = maxConnections;
  135.  
  136. }
  137.  
  138. /**
  139. * 获取测试数据库表的名字
  140. *
  141. * @return 测试数据库表的名字
  142. */
  143.  
  144. public String getTestTable() {
  145.  
  146. return this.testTable;
  147.  
  148. }
  149.  
  150. /**
  151. * 设置测试表的名字
  152. *
  153. * @param testTable
  154. * String 测试表的名字
  155. */
  156.  
  157. public void setTestTable(String testTable) {
  158.  
  159. this.testTable = testTable;
  160.  
  161. }
  162.  
  163. /**
  164. * 创建一个数据库连接池,连接池中的可用连接的数量采用类成员 initialConnections 中设置的值
  165. */
  166.  
  167. public synchronized void createPool() throws Exception {
  168.  
  169. // 确保连接池没有创建
  170.  
  171. // 如果连接池己经创建了,保存连接的向量 connections 不会为空
  172.  
  173. if (connections != null) {
  174.  
  175. return; // 如果己经创建,则返回
  176.  
  177. }
  178.  
  179. // 实例化 JDBC Driver 中指定的驱动类实例
  180.  
  181. Driver driver = (Driver) (Class.forName(this.jdbcDriver).newInstance());
  182.  
  183. DriverManager.registerDriver(driver); // 注册 JDBC 驱动程序
  184.  
  185. // 创建保存连接的向量,初始时有 0 个元素
  186.  
  187. connections = new Vector();
  188.  
  189. // 根据 initialConnections 中设置的值,创建连接。
  190.  
  191. createConnections(this.initialConnections);
  192.  
  193. System.out.println(" 数据库连接池创建成功! ");
  194.  
  195. }
  196.  
  197. /**
  198. * 创建由 numConnections 指定数目的数据库连接,并把这些连接 放入 connections 向量中
  199. *
  200. * @param numConnections
  201. * 要创建的数据库连接的数目
  202. */
  203.  
  204. @SuppressWarnings("unchecked")
  205. private void createConnections(int numConnections) throws sqlException {
  206.  
  207. // 循环创建指定数目的数据库连接
  208.  
  209. for (int x = 0; x < numConnections; x++) {
  210.  
  211. // 是否连接池中的数据库连接的数量己经达到最大?最大值由类成员 maxConnections
  212.  
  213. // 指出,如果 maxConnections 为 0 或负数,表示连接数量没有限制。
  214.  
  215. // 如果连接数己经达到最大,即退出
  216.  
  217. if (this.maxConnections > 0
  218. && this.connections.size() >= this.maxConnections) {
  219.  
  220. break;
  221.  
  222. }
  223.  
  224. // add a new PooledConnection object to connections vector
  225.  
  226. // 增加一个连接到连接池中(向量 connections 中)
  227.  
  228. try {
  229.  
  230. connections.addElement(new PooledConnection(newConnection()));
  231.  
  232. } catch (sqlException e) {
  233.  
  234. System.out.println(" 创建数据库连接失败! " + e.getMessage());
  235.  
  236. throw new sqlException();
  237.  
  238. }
  239.  
  240. System.out.println(" 数据库连接己创建 ......");
  241.  
  242. }
  243.  
  244. }
  245.  
  246. /**
  247. * 创建一个新的数据库连接并返回它
  248. *
  249. * @return 返回一个新创建的数据库连接
  250. */
  251.  
  252. private Connection newConnection() throws sqlException {
  253.  
  254. // 创建一个数据库连接
  255.  
  256. Connection conn = DriverManager.getConnection(dbUrl,dbUsername,dbPassword);
  257.  
  258. // 如果这是第一次创建数据库连接,即检查数据库,获得此数据库允许支持
  259.  
  260. // 最大客户连接数目
  261.  
  262. // connections.size()==0 表示目前没有连接己被创建
  263.  
  264. if (connections.size() == 0) {
  265.  
  266. DatabaseMetaData MetaData = conn.getMetaData();
  267.  
  268. int driverMaxConnections = MetaData.getMaxConnections();
  269.  
  270. // 数据库返回的 driverMaxConnections 若为 0 ,表示此数据库没有最大
  271.  
  272. // 连接限制,或数据库的最大连接限制不知道
  273.  
  274. // driverMaxConnections 为返回的一个整数,表示此数据库允许客户连接的数目
  275.  
  276. // 如果连接池中设置的最大连接数量大于数据库允许的连接数目,则置连接池的最大
  277.  
  278. // 连接数目为数据库允许的最大数目
  279.  
  280. if (driverMaxConnections > 0
  281. && this.maxConnections > driverMaxConnections) {
  282.  
  283. this.maxConnections = driverMaxConnections;
  284.  
  285. }
  286.  
  287. }
  288.  
  289. return conn; // 返回创建的新的数据库连接
  290.  
  291. }
  292.  
  293. /**
  294. *
  295. * 通过调用 getFreeConnection() 函数返回一个可用的数据库连接,如果当前没有可用的数据库连接,并且更多的数据库连接不能创
  296. * 建(如连接池大小的限制),此函数等待一会再尝试获取
  297. *
  298. * @return 返回一个可用的数据库连接对象
  299. */
  300.  
  301. public synchronized Connection getConnection() throws sqlException {
  302.  
  303. // 确保连接池己被创建
  304.  
  305. if (connections == null) {
  306.  
  307. return null; // 连接池还没创建,则返回 null
  308.  
  309. }
  310.  
  311. Connection conn = getFreeConnection(); // 获得一个可用的数据库连接
  312.  
  313. // 如果目前没有可以使用的连接,即所有的连接都在使用中
  314.  
  315. while (conn == null) {
  316.  
  317. // 等一会再试
  318.  
  319. wait(250);
  320.  
  321. conn = getFreeConnection(); // 重新再试,直到获得可用的连接,如果
  322.  
  323. // getFreeConnection() 返回的为 null
  324.  
  325. // 则表明创建一批连接后也不可获得可用连接
  326.  
  327. }
  328.  
  329. return conn;// 返回获得的可用的连接
  330.  
  331. }
  332.  
  333. /**
  334. *
  335. * 本函数从连接池向量 connections 中返回一个可用的的数据库连接,如果
  336. *
  337. * 当前没有可用的数据库连接,本函数则根据 incrementalConnections 设置
  338. *
  339. * 的值创建几个数据库连接,并放入连接池中。
  340. *
  341. * 如果创建后,所有的连接仍都在使用中,则返回 null
  342. *
  343. * @return 返回一个可用的数据库连接
  344. */
  345.  
  346. private Connection getFreeConnection() throws sqlException {
  347.  
  348. // 从连接池中获得一个可用的数据库连接
  349.  
  350. Connection conn = findFreeConnection();
  351.  
  352. if (conn == null) {
  353.  
  354. // 如果目前连接池中没有可用的连接
  355.  
  356. // 创建一些连接
  357.  
  358. createConnections(incrementalConnections);
  359.  
  360. // 重新从池中查找是否有可用连接
  361.  
  362. conn = findFreeConnection();
  363.  
  364. if (conn == null) {
  365.  
  366. // 如果创建连接后仍获得不到可用的连接,则返回 null
  367.  
  368. return null;
  369.  
  370. }
  371.  
  372. }
  373.  
  374. return conn;
  375.  
  376. }
  377.  
  378. /**
  379. *
  380. * 查找连接池中所有的连接,查找一个可用的数据库连接,
  381. *
  382. * 如果没有可用的连接,返回 null
  383. *
  384. *
  385. *
  386. * @return 返回一个可用的数据库连接
  387. */
  388.  
  389. private Connection findFreeConnection() throws sqlException {
  390.  
  391. Connection conn = null;
  392.  
  393. PooledConnection pConn = null;
  394.  
  395. // 获得连接池向量中所有的对象
  396.  
  397. Enumeration enumerate = connections.elements();
  398.  
  399. // 遍历所有的对象,看是否有可用的连接
  400.  
  401. while (enumerate.hasMoreElements()) {
  402.  
  403. pConn = (PooledConnection) enumerate.nextElement();
  404.  
  405. if (!pConn.isBusy()) {
  406.  
  407. // 如果此对象不忙,则获得它的数据库连接并把它设为忙
  408.  
  409. conn = pConn.getConnection();
  410.  
  411. pConn.setBusy(true);
  412.  
  413. // 测试此连接是否可用
  414.  
  415. if (!testConnection(conn)) {
  416.  
  417. // 如果此连接不可再用了,则创建一个新的连接,
  418.  
  419. // 并替换此不可用的连接对象,如果创建失败,返回 null
  420.  
  421. try {
  422.  
  423. conn = newConnection();
  424.  
  425. } catch (sqlException e) {
  426.  
  427. System.out.println(" 创建数据库连接失败! " + e.getMessage());
  428.  
  429. return null;
  430.  
  431. }
  432.  
  433. pConn.setConnection(conn);
  434.  
  435. }
  436.  
  437. break; // 己经找到一个可用的连接,退出
  438.  
  439. }
  440.  
  441. }
  442.  
  443. return conn;// 返回找到到的可用连接
  444.  
  445. }
  446.  
  447. /**
  448. *
  449. * 测试一个连接是否可用,如果不可用,关掉它并返回 false
  450. *
  451. * 否则可用返回 true
  452. *
  453. *
  454. *
  455. * @param conn
  456. * 需要测试的数据库连接
  457. *
  458. * @return 返回 true 表示此连接可用, false 表示不可用
  459. */
  460.  
  461. private boolean testConnection(Connection conn) {
  462.  
  463. try {
  464.  
  465. // 判断测试表是否存在
  466.  
  467. if (testTable.equals("")) {
  468.  
  469. // 如果测试表为空,试着使用此连接的 setAutoCommit() 方法
  470.  
  471. // 来判断连接否可用(此方法只在部分数据库可用,如果不可用,// 抛出异常)。注意:使用测试表的方法更可靠
  472.  
  473. conn.setAutoCommit(true);
  474.  
  475. } else {// 有测试表的时候使用测试表测试
  476.  
  477. // check if this connection is valid
  478.  
  479. Statement stmt = conn.createStatement();
  480.  
  481. stmt.execute("select count(*) from " + testTable);
  482.  
  483. }
  484.  
  485. } catch (sqlException e) {
  486.  
  487. // 上面抛出异常,此连接己不可用,关闭它,并返回 false;
  488.  
  489. closeConnection(conn);
  490.  
  491. return false;
  492.  
  493. }
  494.  
  495. // 连接可用,返回 true
  496.  
  497. return true;
  498.  
  499. }
  500.  
  501. /**
  502. *
  503. * 此函数返回一个数据库连接到连接池中,并把此连接置为空闲。
  504. *
  505. * 所有使用连接池获得的数据库连接均应在不使用此连接时返回它。
  506. *
  507. *
  508. *
  509. * @param 需返回到连接池中的连接对象
  510. */
  511.  
  512. public void returnConnection(Connection conn) {
  513.  
  514. // 确保连接池存在,如果连接没有创建(不存在),直接返回
  515.  
  516. if (connections == null) {
  517.  
  518. System.out.println(" 连接池不存在,无法返回此连接到连接池中 !");
  519.  
  520. return;
  521.  
  522. }
  523.  
  524. PooledConnection pConn = null;
  525.  
  526. Enumeration enumerate = connections.elements();
  527.  
  528. // 遍历连接池中的所有连接,找到这个要返回的连接对象
  529.  
  530. while (enumerate.hasMoreElements()) {
  531.  
  532. pConn = (PooledConnection) enumerate.nextElement();
  533.  
  534. // 先找到连接池中的要返回的连接对象
  535.  
  536. if (conn == pConn.getConnection()) {
  537.  
  538. // 找到了,设置此连接为空闲状态
  539.  
  540. pConn.setBusy(false);
  541.  
  542. break;
  543.  
  544. }
  545.  
  546. }
  547.  
  548. }
  549.  
  550. /**
  551. *
  552. * 刷新连接池中所有的连接对象
  553. *
  554. *
  555. */
  556.  
  557. public synchronized void refreshConnections() throws sqlException {
  558.  
  559. // 确保连接池己创新存在
  560.  
  561. if (connections == null) {
  562.  
  563. System.out.println(" 连接池不存在,无法刷新 !");
  564.  
  565. return;
  566.  
  567. }
  568.  
  569. PooledConnection pConn = null;
  570.  
  571. Enumeration enumerate = connections.elements();
  572.  
  573. while (enumerate.hasMoreElements()) {
  574.  
  575. // 获得一个连接对象
  576.  
  577. pConn = (PooledConnection) enumerate.nextElement();
  578.  
  579. // 如果对象忙则等 5 秒,5 秒后直接刷新
  580.  
  581. if (pConn.isBusy()) {
  582.  
  583. wait(5000); // 等 5 秒
  584.  
  585. }
  586.  
  587. // 关闭此连接,用一个新的连接代替它。
  588.  
  589. closeConnection(pConn.getConnection());
  590.  
  591. pConn.setConnection(newConnection());
  592.  
  593. pConn.setBusy(false);
  594.  
  595. }
  596.  
  597. }
  598.  
  599. /**
  600. *
  601. * 关闭连接池中所有的连接,并清空连接池。
  602. */
  603.  
  604. public synchronized void closeConnectionPool() throws sqlException {
  605.  
  606. // 确保连接池存在,如果不存在,返回
  607.  
  608. if (connections == null) {
  609.  
  610. System.out.println(" 连接池不存在,无法关闭 !");
  611.  
  612. return;
  613.  
  614. }
  615.  
  616. PooledConnection pConn = null;
  617.  
  618. Enumeration enumerate = connections.elements();
  619.  
  620. while (enumerate.hasMoreElements()) {
  621.  
  622. pConn = (PooledConnection) enumerate.nextElement();
  623.  
  624. // 如果忙,等 5 秒
  625.  
  626. if (pConn.isBusy()) {
  627.  
  628. wait(5000); // 等 5 秒
  629.  
  630. }
  631.  
  632. // 5 秒后直接关闭
  633.  
  634. closeConnection(pConn.getConnection());
  635.  
  636. // 从连接池向量中删除
  637.  
  638. connections.removeElement(pConn);
  639.  
  640. }
  641.  
  642. // 置连接池为空
  643.  
  644. connections = null;
  645.  
  646. }
  647.  
  648. /**
  649. *
  650. * 关闭一个数据库连接
  651. *
  652. *
  653. *
  654. * @param 需要关闭数据库连接
  655. */
  656.  
  657. private void closeConnection(Connection conn) {
  658.  
  659. try {
  660.  
  661. conn.close();
  662.  
  663. } catch (sqlException e) {
  664.  
  665. System.out.println(" 关闭数据库连接出错: " + e.getMessage());
  666.  
  667. }
  668.  
  669. }
  670.  
  671. /**
  672. *
  673. * 使程序等待给定的毫秒数
  674. *
  675. *
  676. *
  677. * @param 给定的毫秒数
  678. */
  679.  
  680. private void wait(int mSeconds) {
  681.  
  682. try {
  683.  
  684. Thread.sleep(mSeconds);
  685.  
  686. } catch (InterruptedException e) {
  687.  
  688. }
  689.  
  690. }
  691.  
  692. /**
  693. *
  694. *
  695. *
  696. * 内部使用的用于保存连接池中连接对象的类
  697. *
  698. * 此类中有两个成员,一个是数据库的连接,另一个是指示此连接是否
  699. *
  700. * 正在使用的标志。
  701. */
  702.  
  703. class PooledConnection {
  704.  
  705. Connection connection = null;// 数据库连接
  706.  
  707. boolean busy = false; // 此连接是否正在使用的标志,默认没有正在使用
  708.  
  709. // 构造函数,根据一个 Connection 构告一个 PooledConnection 对象
  710.  
  711. public PooledConnection(Connection connection) {
  712.  
  713. this.connection = connection;
  714.  
  715. }
  716.  
  717. // 返回此对象中的连接
  718.  
  719. public Connection getConnection() {
  720.  
  721. return connection;
  722.  
  723. }
  724.  
  725. // 设置此对象的,连接
  726.  
  727. public void setConnection(Connection connection) {
  728.  
  729. this.connection = connection;
  730.  
  731. }
  732.  
  733. // 获得对象连接是否忙
  734.  
  735. public boolean isBusy() {
  736.  
  737. return busy;
  738.  
  739. }
  740.  
  741. // 设置对象的连接正在忙
  742.  
  743. public void setBusy(boolean busy) {
  744.  
  745. this.busy = busy;
  746.  
  747. }
  748.  
  749. }
  750.  
  751. }
  752.  
  753. // =======================================
  754. //
  755. // 这个例子是根据POSTGREsql数据库写的,
  756. // 请用的时候根据实际的数据库调整。
  757. //
  758. // 调用方法如下:
  759. //
  760. // ① ConnectionPool connPool
  761. // = new ConnectionPool("org.postgresql.Driver"
  762. //,"jdbc:postgresql://dbURI:5432/DBName"
  763. //,"postgre"
  764. //,"postgre");
  765. //
  766. // ② connPool .createPool();
  767. //   Connection conn = connPool .getConnection();
  1. --
  2. -- Postgresql database dump
  3. --
  4.  
  5. -- Started on 2008-04-24 18:05:37
  6.  
  7. SET client_encoding = 'UTF8';
  8. SET standard_conforming_strings = off;
  9. SET check_function_bodies = false;
  10. SET client_min_messages = warning;
  11. SET escape_string_warning = off;
  12.  
  13. SET search_path = public,pg_catalog;
  14.  
  15. --
  16. -- TOC entry 1506 (class 1259 OID 19105)
  17. -- Dependencies: 6
  18. -- Name: seq_crm_attachment; Type: SEQUENCE; Schema: public; Owner: root
  19. --
  20.  
  21. CREATE SEQUENCE seq_crm_attachment
  22. INCREMENT BY 1
  23. MAXVALUE 99999999999999999
  24. NO MINVALUE
  25. CACHE 1;
  26.  
  27.  
  28. ALTER TABLE public.seq_crm_attachment OWNER TO root;
  29.  
  30. --
  31. -- TOC entry 1879 (class 0 OID 0)
  32. -- Dependencies: 1506
  33. -- Name: seq_crm_attachment; Type: SEQUENCE SET; Schema: public; Owner: root
  34. --
  35.  
  36. SELECT pg_catalog.setval('seq_crm_attachment',8,true);
  37.  
  38.  
  39. SET default_tablespace = '';
  40.  
  41. SET default_with_oids = false;
  42.  
  43. --
  44. -- TOC entry 1507 (class 1259 OID 19107)
  45. -- Dependencies: 1796 1797 6
  46. -- Name: attachment; Type: TABLE; Schema: public; Owner: root; Tablespace:
  47. --
  48.  
  49. CREATE TABLE attachment (
  50. 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()
  51. );
  52.  
  53.  
  54. ALTER TABLE public.attachment OWNER TO root;
  55.  
  56.  
  57. --
  58. -- TOC entry 1520 (class 1259 OID 19344)
  59. -- Dependencies: 6
  60. -- Name: seq_crm_audit_log; Type: SEQUENCE; Schema: public; Owner: root
  61. --
  62.  
  63. CREATE SEQUENCE seq_crm_audit_log
  64. INCREMENT BY 1
  65. MAXVALUE 9999999999999999
  66. NO MINVALUE
  67. CACHE 1;
  68.  
  69.  
  70. ALTER TABLE public.seq_crm_audit_log OWNER TO root;
  71.  
  72. --
  73. -- TOC entry 1881 (class 0 OID 0)
  74. -- Dependencies: 1520
  75. -- Name: seq_crm_audit_log; Type: SEQUENCE SET; Schema: public; Owner: root
  76. --
  77.  
  78. SELECT pg_catalog.setval('seq_crm_audit_log',1,true);
  79.  
  80.  
  81. --
  82. -- TOC entry 1508 (class 1259 OID 19115)
  83. -- Dependencies: 1798 6
  84. -- Name: audit_log; Type: TABLE; Schema: public; Owner: root; Tablespace:
  85. --
  86.  
  87.  
  88. CREATE TABLE audit_log (
  89. 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
  90. );
  91.  
  92.  
  93. ALTER TABLE public.audit_log OWNER TO root;
  94.  
  95.  
  96. --
  97. -- TOC entry 1519 (class 1259 OID 19341)
  98. -- Dependencies: 6
  99. -- Name: seq_crm_Feedback; Type: SEQUENCE; Schema: public; Owner: root
  100. --
  101.  
  102. CREATE SEQUENCE seq_crm_Feedback
  103. START WITH 1
  104. INCREMENT BY 1
  105. MAXVALUE 99999999999999
  106. NO MINVALUE
  107. CACHE 1;
  108.  
  109.  
  110. ALTER TABLE public.seq_crm_Feedback OWNER TO root;
  111.  
  112. --
  113. -- TOC entry 1884 (class 0 OID 0)
  114. -- Dependencies: 1519
  115. -- Name: seq_crm_Feedback; Type: SEQUENCE SET; Schema: public; Owner: root
  116. --
  117.  
  118. SELECT pg_catalog.setval('seq_crm_Feedback',false);
  119.  
  120.  
  121. --
  122. -- TOC entry 1509 (class 1259 OID 19122)
  123. -- Dependencies: 1799 1800 6
  124. -- Name: Feedback; Type: TABLE; Schema: public; Owner: root; Tablespace:
  125. --
  126. CREATE TABLE Feedback (
  127. 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()
  128. );
  129.  
  130.  
  131. ALTER TABLE public.Feedback OWNER TO root;
  132.  
  133.  
  134. --
  135. -- TOC entry 1521 (class 1259 OID 19347)
  136. -- Dependencies: 6
  137. -- Name: seq_crm_mail_queue; Type: SEQUENCE; Schema: public; Owner: root
  138. --
  139.  
  140. CREATE SEQUENCE seq_crm_mail_queue
  141. START WITH 1
  142. INCREMENT BY 1
  143. MAXVALUE 9999999999999
  144. NO MINVALUE
  145. CACHE 1;
  146.  
  147.  
  148. ALTER TABLE public.seq_crm_mail_queue OWNER TO root;
  149.  
  150. --
  151. -- TOC entry 1885 (class 0 OID 0)
  152. -- Dependencies: 1521
  153. -- Name: seq_crm_mail_queue; Type: SEQUENCE SET; Schema: public; Owner: root
  154. --
  155.  
  156. SELECT pg_catalog.setval('seq_crm_mail_queue',false);
  157.  
  158.  
  159. --
  160. -- TOC entry 1510 (class 1259 OID 19130)
  161. -- Dependencies: 1801 1802 6
  162. -- Name: mail_queue; Type: TABLE; Schema: public; Owner: root; Tablespace:
  163. --
  164. CREATE TABLE mail_queue (
  165. 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)
  166. );
  167.  
  168.  
  169. ALTER TABLE public.mail_queue OWNER TO root;
  170.  
  171. --
  172. -- TOC entry 1522 (class 1259 OID 19350)
  173. -- Dependencies: 6
  174. -- Name: seq_crm_master; Type: SEQUENCE; Schema: public; Owner: root
  175. --
  176.  
  177. CREATE SEQUENCE seq_crm_master
  178. START WITH 1
  179. INCREMENT BY 1
  180. MAXVALUE 99999999999999
  181. NO MINVALUE
  182. CACHE 1;
  183.  
  184.  
  185. ALTER TABLE public.seq_crm_master OWNER TO root;
  186.  
  187. --
  188. -- TOC entry 1887 (class 0 OID 0)
  189. -- Dependencies: 1522
  190. -- Name: seq_crm_master; Type: SEQUENCE SET; Schema: public; Owner: root
  191. --
  192.  
  193. SELECT pg_catalog.setval('seq_crm_master',false);
  194.  
  195.  
  196. --
  197. -- TOC entry 1511 (class 1259 OID 19138)
  198. -- Dependencies: 1803 6
  199. -- Name: master; Type: TABLE; Schema: public; Owner: root; Tablespace:
  200. --
  201.  
  202. CREATE TABLE master (
  203. 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
  204. );
  205.  
  206.  
  207. ALTER TABLE public.master OWNER TO root;
  208.  
  209.  
  210. --
  211. -- TOC entry 1523 (class 1259 OID 19353)
  212. -- Dependencies: 6
  213. -- Name: seq_crm_master_type; Type: SEQUENCE; Schema: public; Owner: root
  214. --
  215.  
  216. CREATE SEQUENCE seq_crm_master_type
  217. START WITH 1
  218. INCREMENT BY 1
  219. MAXVALUE 999999999999999
  220. NO MINVALUE
  221. CACHE 1;
  222.  
  223.  
  224. ALTER TABLE public.seq_crm_master_type OWNER TO root;
  225.  
  226. --
  227. -- TOC entry 1889 (class 0 OID 0)
  228. -- Dependencies: 1523
  229. -- Name: seq_crm_master_type; Type: SEQUENCE SET; Schema: public; Owner: root
  230. --
  231.  
  232. SELECT pg_catalog.setval('seq_crm_master_type',false);
  233.  
  234.  
  235. --
  236. -- TOC entry 1512 (class 1259 OID 19145)
  237. -- Dependencies: 1804 6
  238. -- Name: master_type; Type: TABLE; Schema: public; Owner: root; Tablespace:
  239. --
  240. CREATE TABLE master_type (
  241. id integer DEFAULT nextval('seq_crm_master_type'::regclass) NOT NULL,description character varying(255)
  242. );
  243.  
  244.  
  245. ALTER TABLE public.master_type OWNER TO root;
  246.  
  247. --
  248. -- TOC entry 1524 (class 1259 OID 19356)
  249. -- Dependencies: 6
  250. -- Name: seq_crm_office; Type: SEQUENCE; Schema: public; Owner: root
  251. --
  252.  
  253. CREATE SEQUENCE seq_crm_office
  254. START WITH 1
  255. INCREMENT BY 1
  256. MAXVALUE 99999999999999
  257. NO MINVALUE
  258. CACHE 1;
  259.  
  260.  
  261. ALTER TABLE public.seq_crm_office OWNER TO root;
  262.  
  263. --
  264. -- TOC entry 1891 (class 0 OID 0)
  265. -- Dependencies: 1524
  266. -- Name: seq_crm_office; Type: SEQUENCE SET; Schema: public; Owner: root
  267. --
  268.  
  269. SELECT pg_catalog.setval('seq_crm_office',false);
  270.  
  271.  
  272. --
  273. -- TOC entry 1513 (class 1259 OID 19149)
  274. -- Dependencies: 1805 6
  275. -- Name: office; Type: TABLE; Schema: public; Owner: root; Tablespace:
  276. --
  277.  
  278.  
  279. CREATE TABLE office (
  280. 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
  281. );
  282.  
  283.  
  284. ALTER TABLE public.office OWNER TO root;
  285.  
  286.  
  287. --
  288. -- TOC entry 1525 (class 1259 OID 19359)
  289. -- Dependencies: 6
  290. -- Name: seq_crm_patient; Type: SEQUENCE; Schema: public; Owner: root
  291. --
  292.  
  293. CREATE SEQUENCE seq_crm_patient
  294. START WITH 1
  295. INCREMENT BY 1
  296. MAXVALUE 99999999999999
  297. NO MINVALUE
  298. CACHE 1;
  299.  
  300.  
  301. ALTER TABLE public.seq_crm_patient OWNER TO root;
  302.  
  303. --
  304. -- TOC entry 1893 (class 0 OID 0)
  305. -- Dependencies: 1525
  306. -- Name: seq_crm_patient; Type: SEQUENCE SET; Schema: public; Owner: root
  307. --
  308.  
  309. SELECT pg_catalog.setval('seq_crm_patient',false);
  310.  
  311.  
  312. --
  313. -- TOC entry 1514 (class 1259 OID 19156)
  314. -- Dependencies: 1806 1807 6
  315. -- Name: patient; Type: TABLE; Schema: public; Owner: root; Tablespace:
  316. --
  317.  
  318. CREATE TABLE patient (
  319. 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()
  320. );
  321.  
  322.  
  323. ALTER TABLE public.patient OWNER TO root;
  324.  
  325.  
  326. --
  327. -- TOC entry 1526 (class 1259 OID 19362)
  328. -- Dependencies: 6
  329. -- Name: seq_crm_referral; Type: SEQUENCE; Schema: public; Owner: root
  330. --
  331.  
  332. CREATE SEQUENCE seq_crm_referral
  333. START WITH 1
  334. INCREMENT BY 1
  335. MAXVALUE 999999999999999999
  336. NO MINVALUE
  337. CACHE 1;
  338.  
  339.  
  340. ALTER TABLE public.seq_crm_referral OWNER TO root;
  341.  
  342. --
  343. -- TOC entry 1894 (class 0 OID 0)
  344. -- Dependencies: 1526
  345. -- Name: seq_crm_referral; Type: SEQUENCE SET; Schema: public; Owner: root
  346. --
  347.  
  348. SELECT pg_catalog.setval('seq_crm_referral',false);
  349.  
  350.  
  351. --
  352. -- TOC entry 1515 (class 1259 OID 19164)
  353. -- Dependencies: 1808 1809 6
  354. -- Name: referral; Type: TABLE; Schema: public; Owner: root; Tablespace:
  355. --
  356. CREATE TABLE referral (
  357. 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
  358. );
  359.  
  360.  
  361. ALTER TABLE public.referral OWNER TO root;
  362.  
  363. --
  364. -- TOC entry 1527 (class 1259 OID 19365)
  365. -- Dependencies: 6
  366. -- Name: seq_crm_role; Type: SEQUENCE; Schema: public; Owner: root
  367. --
  368.  
  369. CREATE SEQUENCE seq_crm_role
  370. START WITH 1
  371. INCREMENT BY 1
  372. MAXVALUE 99999999999999999
  373. NO MINVALUE
  374. CACHE 1;
  375.  
  376.  
  377. ALTER TABLE public.seq_crm_role OWNER TO root;
  378.  
  379. --
  380. -- TOC entry 1896 (class 0 OID 0)
  381. -- Dependencies: 1527
  382. -- Name: seq_crm_role; Type: SEQUENCE SET; Schema: public; Owner: root
  383. --
  384.  
  385. SELECT pg_catalog.setval('seq_crm_role',false);
  386.  
  387.  
  388. --
  389. -- TOC entry 1516 (class 1259 OID 19172)
  390. -- Dependencies: 1810 6
  391. -- Name: role; Type: TABLE; Schema: public; Owner: root; Tablespace:
  392. --
  393.  
  394. CREATE TABLE role (
  395. id integer DEFAULT nextval('seq_crm_role'::regclass) NOT NULL,role_name character varying(25)
  396. );
  397.  
  398.  
  399. ALTER TABLE public.role OWNER TO root;
  400.  
  401. --
  402. -- TOC entry 1528 (class 1259 OID 19368)
  403. -- Dependencies: 6
  404. -- Name: seq_crm_user; Type: SEQUENCE; Schema: public; Owner: root
  405. --
  406.  
  407. CREATE SEQUENCE seq_crm_user
  408. START WITH 1
  409. INCREMENT BY 1
  410. MAXVALUE 99999999999999
  411. NO MINVALUE
  412. CACHE 1;
  413.  
  414.  
  415. ALTER TABLE public.seq_crm_user OWNER TO root;
  416.  
  417. --
  418. -- TOC entry 1898 (class 0 OID 0)
  419. -- Dependencies: 1528
  420. -- Name: seq_crm_user; Type: SEQUENCE SET; Schema: public; Owner: root
  421. --
  422.  
  423. SELECT pg_catalog.setval('seq_crm_user',false);
  424.  
  425.  
  426. --
  427. -- TOC entry 1517 (class 1259 OID 19176)
  428. -- Dependencies: 1811 6
  429. -- Name: user; Type: TABLE; Schema: public; Owner: root; Tablespace:
  430. --
  431. CREATE TABLE "user" (
  432. 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
  433. );
  434.  
  435.  
  436. ALTER TABLE public."user" OWNER TO root;
  437.  
  438. --
  439. -- TOC entry 1529 (class 1259 OID 19371)
  440. -- Dependencies: 6
  441. -- Name: seq_crm_user_office_role; Type: SEQUENCE; Schema: public; Owner: root
  442. --
  443.  
  444. CREATE SEQUENCE seq_crm_user_office_role
  445. START WITH 1
  446. INCREMENT BY 1
  447. MAXVALUE 9999999999999
  448. NO MINVALUE
  449. CACHE 1;
  450.  
  451.  
  452. ALTER TABLE public.seq_crm_user_office_role OWNER TO root;
  453.  
  454. --
  455. -- TOC entry 1900 (class 0 OID 0)
  456. -- Dependencies: 1529
  457. -- Name: seq_crm_user_office_role; Type: SEQUENCE SET; Schema: public; Owner: root
  458. --
  459.  
  460. SELECT pg_catalog.setval('seq_crm_user_office_role',false);
  461.  
  462.  
  463. --
  464. -- TOC entry 1518 (class 1259 OID 19183)
  465. -- Dependencies: 1812 6
  466. -- Name: user_office_role; Type: TABLE; Schema: public; Owner: root; Tablespace:
  467. --
  468.  
  469. CREATE TABLE user_office_role (
  470. 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)
  471. );
  472.  
  473. INSERT INTO "master" ("id","master_type_id","master_order","master_name","master_value","master_format","parent_id") VALUES
  474. (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);
  475.  
  476.  
  477. INSERT INTO "master_type" ("id","description") VALUES
  478. (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');
  479.  
  480. INSERT INTO "role" ("id","role_name") VALUES
  481. (1,'Admin'),'Referral Doctor'),'Referral Secretary'),'Internal Doctor'),'Internal Secretary');
  482.  
  483.  
  484. 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
  485. (1,'admin@163.com','96E79218965EB72C92A549DD5A330112','yang','yajun','(250) 250-2500','',7002,NULL);
  486. COMMIT;
  487.  
  488.  
  489.  
  490. ALTER TABLE public.user_office_role OWNER TO root;
  491.  
  492.  
  493.  
  494.  
  495.  
  496. -- TOC entry 1792 (class 2606 OID 17013)
  497. -- Dependencies: 1496 1496
  498. -- Name: pk_attachment_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  499. --
  500.  
  501. ALTER TABLE ONLY attachment
  502. ADD CONSTRAINT pk_attachment_id PRIMARY KEY (id);
  503.  
  504.  
  505. --
  506. -- TOC entry 1794 (class 2606 OID 17020)
  507. -- Dependencies: 1497 1497
  508. -- Name: pk_audit_log; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  509. --
  510.  
  511. ALTER TABLE ONLY audit_log
  512. ADD CONSTRAINT pk_audit_log PRIMARY KEY (id);
  513.  
  514.  
  515. --
  516. -- TOC entry 1796 (class 2606 OID 17011)
  517. -- Dependencies: 1498 1498
  518. -- Name: pk_Feedback_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  519. --
  520.  
  521. ALTER TABLE ONLY Feedback
  522. ADD CONSTRAINT pk_Feedback_id PRIMARY KEY (id);
  523.  
  524.  
  525. --
  526. -- TOC entry 1798 (class 2606 OID 17022)
  527. -- Dependencies: 1499 1499
  528. -- Name: pk_mail_queue_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  529. --
  530.  
  531. ALTER TABLE ONLY mail_queue
  532. ADD CONSTRAINT pk_mail_queue_id PRIMARY KEY (id);
  533.  
  534.  
  535. --
  536. -- TOC entry 1800 (class 2606 OID 17024)
  537. -- Dependencies: 1500 1500
  538. -- Name: pk_master_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  539. --
  540.  
  541. ALTER TABLE ONLY master
  542. ADD CONSTRAINT pk_master_id PRIMARY KEY (id);
  543.  
  544.  
  545. --
  546. -- TOC entry 1802 (class 2606 OID 17026)
  547. -- Dependencies: 1501 1501
  548. -- Name: pk_master_type_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  549. --
  550.  
  551. ALTER TABLE ONLY master_type
  552. ADD CONSTRAINT pk_master_type_id PRIMARY KEY (id);
  553.  
  554.  
  555. --
  556. -- TOC entry 1804 (class 2606 OID 17028)
  557. -- Dependencies: 1502 1502
  558. -- Name: pk_office_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  559. --
  560.  
  561. ALTER TABLE ONLY office
  562. ADD CONSTRAINT pk_office_id PRIMARY KEY (id);
  563.  
  564.  
  565. --
  566. -- TOC entry 1806 (class 2606 OID 17030)
  567. -- Dependencies: 1503 1503
  568. -- Name: pk_patient_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  569. --
  570.  
  571. ALTER TABLE ONLY patient
  572. ADD CONSTRAINT pk_patient_id PRIMARY KEY (id);
  573.  
  574.  
  575. --
  576. -- TOC entry 1808 (class 2606 OID 17304)
  577. -- Dependencies: 1504 1504
  578. -- Name: pk_referral_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  579. --
  580.  
  581. ALTER TABLE ONLY referral
  582. ADD CONSTRAINT pk_referral_id PRIMARY KEY (id);
  583.  
  584.  
  585. --
  586. -- TOC entry 1810 (class 2606 OID 17306)
  587. -- Dependencies: 1505 1505
  588. -- Name: pk_role_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  589. --
  590.  
  591. ALTER TABLE ONLY role
  592. ADD CONSTRAINT pk_role_id PRIMARY KEY (id);
  593.  
  594.  
  595. --
  596. -- TOC entry 1812 (class 2606 OID 17308)
  597. -- Dependencies: 1506 1506
  598. -- Name: pk_user_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  599. --
  600.  
  601. ALTER TABLE ONLY "user"
  602. ADD CONSTRAINT pk_user_id PRIMARY KEY (id);
  603.  
  604.  
  605. --
  606. -- TOC entry 1814 (class 2606 OID 17310)
  607. -- Dependencies: 1507 1507
  608. -- Name: pk_user_office_role_id; Type: CONSTRAINT; Schema: public; Owner: root; Tablespace:
  609. --
  610.  
  611. ALTER TABLE ONLY user_office_role
  612. ADD CONSTRAINT pk_user_office_role_id PRIMARY KEY (id);
  613.  
  614.  
  615. --
  616. -- TOC entry 1818 (class 2606 OID 17311)
  617. -- Dependencies: 1500 1799 1498
  618. -- Name: action_master-id; Type: FK CONSTRAINT; Schema: public; Owner: root
  619. --
  620.  
  621. ALTER TABLE ONLY Feedback
  622. ADD CONSTRAINT "action_master-id" FOREIGN KEY (action_master_id) REFERENCES master(id);
  623.  
  624.  
  625. --
  626. -- TOC entry 1823 (class 2606 OID 17316)
  627. -- Dependencies: 1500 1799 1502
  628. -- Name: addr_province_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  629. --
  630.  
  631. ALTER TABLE ONLY office
  632. ADD CONSTRAINT addr_province_master_id FOREIGN KEY (addr_province_master_id) REFERENCES master(id);
  633.  
  634.  
  635. --
  636. -- TOC entry 1819 (class 2606 OID 17321)
  637. -- Dependencies: 1506 1811 1498
  638. -- Name: create_user_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  639. --
  640.  
  641. ALTER TABLE ONLY Feedback
  642. ADD CONSTRAINT create_user_id FOREIGN KEY (creator_user_id) REFERENCES "user"(id);
  643.  
  644.  
  645. --
  646. -- TOC entry 1816 (class 2606 OID 17326)
  647. -- Dependencies: 1496 1498 1795
  648. -- Name: Feedback_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  649. --
  650.  
  651. ALTER TABLE ONLY attachment
  652. ADD CONSTRAINT Feedback_id FOREIGN KEY (Feedback_id) REFERENCES Feedback(id);
  653.  
  654.  
  655. --
  656. -- TOC entry 1815 (class 2606 OID 17014)
  657. -- Dependencies: 1498 1496 1795
  658. -- Name: fk_Feedback_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  659. --
  660.  
  661. ALTER TABLE ONLY attachment
  662. ADD CONSTRAINT fk_Feedback_id FOREIGN KEY (Feedback_id) REFERENCES Feedback(id);
  663.  
  664.  
  665. --
  666. -- TOC entry 1817 (class 2606 OID 17331)
  667. -- Dependencies: 1811 1497 1506
  668. -- Name: fk_user_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  669. --
  670.  
  671. ALTER TABLE ONLY audit_log
  672. ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES "user"(id);
  673.  
  674.  
  675. --
  676. -- TOC entry 1825 (class 2606 OID 17336)
  677. -- Dependencies: 1799 1500 1503
  678. -- Name: hctype_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  679. --
  680.  
  681. ALTER TABLE ONLY patient
  682. ADD CONSTRAINT hctype_master_id FOREIGN KEY (hc_type_master_id) REFERENCES master(id);
  683.  
  684.  
  685. --
  686. -- TOC entry 1828 (class 2606 OID 17341)
  687. -- Dependencies: 1504 1506 1811
  688. -- Name: int_doc_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  689. --
  690.  
  691. ALTER TABLE ONLY referral
  692. ADD CONSTRAINT int_doc_id FOREIGN KEY (int_doc_user_id) REFERENCES "user"(id);
  693.  
  694.  
  695. --
  696. -- TOC entry 1829 (class 2606 OID 17346)
  697. -- Dependencies: 1502 1803 1504
  698. -- Name: int_office_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  699. --
  700.  
  701. ALTER TABLE ONLY referral
  702. ADD CONSTRAINT int_office_id FOREIGN KEY (int_office_id) REFERENCES office(id);
  703.  
  704.  
  705. --
  706. -- TOC entry 1826 (class 2606 OID 17351)
  707. -- Dependencies: 1503 1500 1799
  708. -- Name: mast_province_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  709. --
  710.  
  711. ALTER TABLE ONLY patient
  712. ADD CONSTRAINT mast_province_id FOREIGN KEY (addr_province_master_id) REFERENCES master(id);
  713.  
  714.  
  715. --
  716. -- TOC entry 1824 (class 2606 OID 17356)
  717. -- Dependencies: 1502 1799 1500
  718. -- Name: master_status_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  719. --
  720.  
  721. ALTER TABLE ONLY office
  722. ADD CONSTRAINT master_status_id FOREIGN KEY (status_master_id) REFERENCES master(id);
  723.  
  724.  
  725. --
  726. -- TOC entry 1821 (class 2606 OID 17361)
  727. -- Dependencies: 1501 1500 1801
  728. -- Name: master_type_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  729. --
  730.  
  731. ALTER TABLE ONLY master
  732. ADD CONSTRAINT master_type_id FOREIGN KEY (master_type_id) REFERENCES master_type(id);
  733.  
  734.  
  735. --
  736. -- TOC entry 1830 (class 2606 OID 17366)
  737. -- Dependencies: 1504 1803 1502
  738. -- Name: notifier_office_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  739. --
  740.  
  741. ALTER TABLE ONLY referral
  742. ADD CONSTRAINT notifier_office_id FOREIGN KEY (notifier_office_id) REFERENCES office(id);
  743.  
  744.  
  745. --
  746. -- TOC entry 1838 (class 2606 OID 17371)
  747. -- Dependencies: 1502 1803 1507
  748. -- Name: office_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  749. --
  750.  
  751. ALTER TABLE ONLY user_office_role
  752. ADD CONSTRAINT office_id FOREIGN KEY (office_id) REFERENCES office(id);
  753.  
  754.  
  755. --
  756. -- TOC entry 1822 (class 2606 OID 17376)
  757. -- Dependencies: 1799 1500 1500
  758. -- Name: parent_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  759. --
  760.  
  761. ALTER TABLE ONLY master
  762. ADD CONSTRAINT parent_id FOREIGN KEY (parent_id) REFERENCES master(id);
  763.  
  764.  
  765. --
  766. -- TOC entry 1827 (class 2606 OID 17381)
  767. -- Dependencies: 1503 1803 1502
  768. -- Name: pffice_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  769. --
  770.  
  771. ALTER TABLE ONLY patient
  772. ADD CONSTRAINT pffice_id FOREIGN KEY (referring_office_id) REFERENCES office(id);
  773.  
  774.  
  775. --
  776. -- TOC entry 1831 (class 2606 OID 17386)
  777. -- Dependencies: 1811 1506 1504
  778. -- Name: ref_doc_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  779. --
  780.  
  781. ALTER TABLE ONLY referral
  782. ADD CONSTRAINT ref_doc_id FOREIGN KEY (ref_doc_user_id) REFERENCES "user"(id);
  783.  
  784.  
  785. --
  786. -- TOC entry 1832 (class 2606 OID 17391)
  787. -- Dependencies: 1803 1504 1502
  788. -- Name: ref_office_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  789. --
  790.  
  791. ALTER TABLE ONLY referral
  792. ADD CONSTRAINT ref_office_id FOREIGN KEY (ref_office_id) REFERENCES office(id);
  793.  
  794.  
  795. --
  796. -- TOC entry 1820 (class 2606 OID 17396)
  797. -- Dependencies: 1498 1807 1504
  798. -- Name: referral_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  799. --
  800.  
  801. ALTER TABLE ONLY Feedback
  802. ADD CONSTRAINT referral_id FOREIGN KEY (referral_id) REFERENCES referral(id);
  803.  
  804.  
  805. --
  806. -- TOC entry 1833 (class 2606 OID 17401)
  807. -- Dependencies: 1504 1805 1503
  808. -- Name: referral_patient_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  809. --
  810.  
  811. ALTER TABLE ONLY referral
  812. ADD CONSTRAINT referral_patient_id FOREIGN KEY (patient_id) REFERENCES patient(id);
  813.  
  814.  
  815. --
  816. -- TOC entry 1834 (class 2606 OID 17406)
  817. -- Dependencies: 1500 1799 1504
  818. -- Name: referral_status_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  819. --
  820.  
  821. ALTER TABLE ONLY referral
  822. ADD CONSTRAINT referral_status_master_id FOREIGN KEY (ref_status_master_id) REFERENCES master(id);
  823.  
  824.  
  825. --
  826. -- TOC entry 1839 (class 2606 OID 17411)
  827. -- Dependencies: 1809 1507 1505
  828. -- Name: role_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  829. --
  830.  
  831. ALTER TABLE ONLY user_office_role
  832. ADD CONSTRAINT role_id FOREIGN KEY (role_id) REFERENCES role(id);
  833.  
  834.  
  835. --
  836. -- TOC entry 1836 (class 2606 OID 17416)
  837. -- Dependencies: 1500 1506 1799
  838. -- Name: status_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  839. --
  840.  
  841. ALTER TABLE ONLY "user"
  842. ADD CONSTRAINT status_master_id FOREIGN KEY (status_master_id) REFERENCES master(id);
  843.  
  844.  
  845. --
  846. -- TOC entry 1835 (class 2606 OID 17421)
  847. -- Dependencies: 1500 1799 1504
  848. -- Name: urgency_master_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  849. --
  850.  
  851. ALTER TABLE ONLY referral
  852. ADD CONSTRAINT urgency_master_id FOREIGN KEY (urgency_master_id) REFERENCES master(id);
  853.  
  854.  
  855. --
  856. -- TOC entry 1840 (class 2606 OID 17426)
  857. -- Dependencies: 1507 1811 1506
  858. -- Name: user_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  859. --
  860.  
  861. ALTER TABLE ONLY user_office_role
  862. ADD CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES "user"(id);
  863.  
  864.  
  865. --
  866. -- TOC entry 1837 (class 2606 OID 17431)
  867. -- Dependencies: 1813 1507 1506
  868. -- Name: user_office_role_id; Type: FK CONSTRAINT; Schema: public; Owner: root
  869. --
  870.  
  871. ALTER TABLE ONLY "user"
  872. ADD CONSTRAINT user_office_role_id FOREIGN KEY (default_user_office_role_id) REFERENCES user_office_role(id);
  873.  
  874.  
  875. --
  876. -- TOC entry 1856 (class 0 OID 0)
  877. -- Dependencies: 3
  878. -- Name: public; Type: ACL; Schema: -; Owner: root
  879. --
  880.  
  881. REVOKE ALL ON SCHEMA public FROM PUBLIC;
  882. REVOKE ALL ON SCHEMA public FROM root;
  883. GRANT ALL ON SCHEMA public TO root;
  884. GRANT ALL ON SCHEMA public TO PUBLIC;
  885.  
  886.  
  887. -- Completed on 2008-04-24 18:05:38
  888.  
  889. --
  890. -- Postgresql database dump complete
  891. --
  892.  

连接池原理图.rar

http://dl.iteye.com/topics/download/9f84c62e-20c3-3194-94c6-7dfdc0def9b8

end

猜你在找的Postgre SQL相关文章