bianbian coding life

便便代码人生: 关注技术, 翻译文档, 偶尔动动手

Archive for the 'Database' Category

Database, mysql, PostgreSQL, Oracle…

[原] 保留数据表内最后N条记录的SQL语句

Posted by bianbian on 2008-04-02 09:16


本文Tags: ,

SQL语句的需求真是无止境，今天又有一个：需要保留数据表内最后N条记录。
这个需求还是经常碰到的：
比如实时更新的滚动新闻，只需要保留最后的N条，过期的就删除；或者临时上载文件存放表，每天只允许保留N个文件，过期的就连文件一起删除（数据库存放文件路径方式，除了删除记录，还要删除文件——意味着需要取出将被删除的过期记录）。
因为事先不知道总记录长度，常规做法应该select count(*)一遍，然后获得要删除的记录条数。
不过为了提高数据库性能，应尽量减少查询结果返回次数——其实就是能不能一句SQL搞定？

  1. //删除记录，假设N为100
  2. DELETE FROM table WHERE id NOT IN ( SELECT id FROM table ORDER BY id DESC LIMIT 100 );
  3.  
  4. //取出过期记录
  5. SELECT * FROM  table WHERE id NOT IN ( SELECT id FROM table ORDER BY id DESC LIMIT 100 );
标签: ,

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

Posted in Database, Technology | No Comments »

[转] Hibernate映射对象标识符(OID)与数据库主键对应关系

Posted by bianbian on 2007-07-13 04:36


本文Tags:

原文：http://www.blogjava.net/action/archive/2007/05/22/119134.html
Hibernate采用对象标识符,也就是通常我们所说的OID来创建对象和数据库表里记录的对应关系,对象的OID和表里的主键对应,所以说OID是非常重要的,不应该让程序来给它赋值.数据库区分同一表的不同记录是用主键来区分.数据库中的主键最重要的3个基本要素就是不允许为null,不允许有重复值,主键永远不会改变.所以通常我们设计表都会设计主键的值为自动增加,没有业务逻辑含义的一组数字,当然针对每个数据库,设置的方法也不同.但是都非常简单.加一个属性就可以了.
而JAVA区分同一类的不同对象是用内存地址,在JAVA语言中判断两个对象的引用变量是否想相等,有以下两种比较方式.1)用运算符”==”比较内存地址,此外还可以用Object的equals方法也是按内存地址比较.2)比较两个对象的值是否相同,JAVA中的一些覆盖了Object类的equals方法实现比较合适.例如String和Date类,还有JAVA包装类.如果是String.equals(String)这种方式的比较就是比较这两个String的值的.如果是Object原是的equals方法就是比较地址了.这点很容易混淆.
通常,为了包装Hibernate的OID的唯一性和不可变性,由Hibernate或者底层数据库来给OID赋值比较合理.因此我们在编程的时候最好把持久化类的OID设置为private或者protected类型,这样可以防止JAVA程序随便更改OID.而OID的get方法我们还是要设置为public类型,这样方便我们读取. 在对象-关系映射文件里的 1)increment 代理主键,hibernate自动以递增的方式来生成标识符,每次增加1.
2)identity 代理主键,由底层数据库生成标识符,前提就是底层的数据库支持自动增长的类型.
3)sequence 代理主键,hibernate根据底层数据库生成的标识符,前提是底层数据库支持序列
4)hilo 代理主键,hibernate根据higg/low算法来生成的标识符,把特定表的字段作为high的值,默认选用hibernate_unique_key表的next_hi字段
5)native 代理主键,根据底层数据库对自动生成标识符的支持能力,还选择identity,sequence,或hilo.
6)uuid.hex 代理主键,hibernate采用128位的UUID算法生成标识符,UUID算法能够在网络环境下生成唯一字符串标识符.不过字符串要比数据占用的空间多的多.所以不流行使用.
7)assigned 适用于自然主键,由JAVA应用程序负责生成标识符,为了能让JAVA设置OID.不能吧setId方法设置为非公共类型了,这种方式也尽量避免使用.
这里个人觉得第一种方式,也就是说由Hibernate来生成对象标识符的方式比较好.但是这种方式的缺点是只能一个Hibernate对应一个数据库的表.当同时创建了SeesionFactory实例的时候.两个或者更多的Hibernate对应同一个数据库的时候就会插入出错.这个时候我们可以选择第二种方式把标识符的生成工作交给底层数据库.还有一个小知识点要注意就是OID必须定义为long,int,short类型,如果定义为byte会报异常,这里推荐用long.
总结一下,这7中生成OID标识符的方法,increment 比较常用,把标识符生成的权力交给Hibernate处理.但是当同时多个Hibernate应用操作同一个数据库,甚至同一张表的时候.就推荐使用identity 依赖底层数据库实现,但是数据库必须支持自动增长,sequence 以来底层数据库实现,但是数据库必须支持系列.hilo 根据特定的表实现.这三种方式了.当然针对不同的数据库选择不同的方法.如果你不能确定你使用的数据库具体支持什么的情况下.可以选择第三种.或者用native 让Hibernate来帮选择identity,sequence,或hilo.后边的自然主键不推荐使用,因为自然主键就是具有业务含义的主键,在现在的软件开发结构中,已经很少有人用了.下面总结一下几种常用数据库,可以使用的标识符类型.
MySQL:identity数据库底层实现,需要支持自动增长,increment由Hibernate实现,hilo用特定的表实现,
MSSQL:identity数据库底层实现,需要支持自动增长,increment由Hibernate实现,hilo用特定的表实现,
Oracle:sequence数据库底层实现,需要支持序列,increment由Hibernate实现,hilo用特定的表实现,
以上不难发现,所有的数据库都支持Hibernate用increment实现OID的生成,MYSQL和MSSQL数据库底层实现支持自动增长,而Oracle支持序列,还有用特殊表的实现方式这三个数据库都支持.还有一种实现方式适用于所有的数据库,就是native,由Hibernate去选择使用什么样的方式来生成IOD对象标识符,这种方式也是跨平台的.下面是各种设置方式的例子*.hbm.xml文件四个.例题来源孙MM的<<精通Hibernate>>一书.我非常喜欢这本书,讲的非常简单明了.感兴趣的朋友可以去买一本看看(当当打7.3折哦).

  1. <?xml version="1.0"?>
  2. <!DOCTYPE hibernate-mapping
  3. PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
  4. "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
  5. <hibernate-mapping>
  6.  
  7.   <class name="mypack.HiloTester"  table="HILO_TESTER">
  8.    
  9.     <id name="id" type="long" column="ID">
  10.     <generator class="hilo">
  11.                 <param name="table">hi_value</param>
  12.                 <param name="column">next_value</param>
  13.                 <param name="max_lo">100</param>
  14.         </generator>
  15.     </id>
  16.  
  17.     <property name="name" type="string" >
  18.         <column name="name" length="15" />
  19.     </property>
  20.    
  21.   </class>
  22.  
  23. </hibernate-mapping>
  24.  
  25. <?xml version="1.0"?>
  26. <!DOCTYPE hibernate-mapping
  27. PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
  28. "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
  29. <hibernate-mapping>
  30.  
  31.   <class name="mypack.IdentityTester"  table="IDENTITY_TESTER">
  32.    
  33.     <id name="id" type="long" column="ID">
  34.       <generator class="identity"/>
  35.     </id>
  36.  
  37.     <property name="name" type="string" >
  38.         <column name="name" length="15"/>
  39.     </property>
  40.    
  41.   </class>
  42.  
  43. </hibernate-mapping>
  44.  
  45. <?xml version="1.0"?>
  46. <!DOCTYPE hibernate-mapping
  47. PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
  48. "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
  49. <hibernate-mapping>
  50.  
  51.   <class name="mypack.NativeTester" table="NATIVE_TESTER" >
  52.    
  53.     <id name="id" type="long" column="ID">
  54.       <generator class="native"/>
  55.     </id>
  56.  
  57.     <property name="name" type="string" >
  58.         <column name="name" length="15" />
  59.     </property>
  60.    
  61.   </class>
  62.  
  63. </hibernate-mapping>
  64.  
  65. <?xml version="1.0"?>
  66. <!DOCTYPE hibernate-mapping
  67. PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
  68. "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
  69. <hibernate-mapping>
  70.  
  71.   <class name="mypack.IncrementTester" table="INCREMENT_TESTER" >
  72.    
  73.     <id name="id" type="long" column="ID">
  74.       <meta attribute="scope-set">private</meta>
  75.       <generator class="increment"/>
  76.     </id>
  77.  
  78.     <property name="name" type="string" >
  79.         <column name="NAME" length="15" />
  80.     </property>
  81.    
  82.   </class>
  83.  
  84. </hibernate-mapping>
标签:

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

  • 无相关日志.

Posted in Database, Java, Technology | No Comments »

[原]php使用PostgreSQL的编码问题

Posted by bianbian on 2007-03-15 02:43


本文Tags: , , ,

php 5.2 PostgreSQL 8.1
数据库是用UTF8编码建的：
./createdb -E UNICODE xxxx
但是用php插入中文数据的时候会报错：
ERROR: invalid byte sequence for encoding “UTF8″ ….

看字面意思，好像是因为数据库是UTF8，而插入的数据不是UTF8导致的错误。到psql终端下：

# \encoding
UTF8
# show client_encoding;
UTF8

前面一个UTF8是数据库的编码，后一个表示客户端数据编码（就是插入数据的编码）。
怎么解决？

本问题已经解决，请参考这里：php用UTF-8编码总结

标签: , , ,

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

Posted in Database, Technology, php | No Comments »

[原]用pgAdmin建表的时候一定不要大写

Posted by bianbian on 2007-03-12 05:35


本文Tags: ,

想为了省事，没装PowerDesign，直接用了pgAdmin设计表。
我K，被它害死了：每个表每个字段有大写的都用了引号引起来。。。
导致的后果就是：拼SQL语句的时候必须也加上引号，不然每次都
ERROR：relocation [TableName] does not exist…..

于是简单的“insert into Table(Column1,Column2)...”必须写成“insert into "Table"("Column1", "Column2")....”

我说，还不如重新建表吧。。。K pgAdmin它大爷。。。。

标签: ,

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

Posted in Database, Technology | No Comments »

[原]C语言连接postgres的实例 - libpq使用

Posted by bianbian on 2006-12-20 03:28


本文Tags: ,

看过长篇文档后，啥废话也不说了，来个实例总结：

  1. char *sql = "SELECT userid,password FROM users WHERE ....";
  2. PGconn *conn = PQconnectdb("host=localhost user=postgres password= dbname=db port=5432");
  3. if (PQstatus(conn) == CONNECTION_OK)
  4. {
  5.   PQsetClientEncoding(conn, "UNICODE");
  6.   PGresult *res = PQexec(conn, sql);
  7.   if (res != null)
  8.   {
  9.     int status = PQresultStatus(res);
  10.     if (status == PGRES_COMMAND_OK || status == PGRES_TUPLES_OK)
  11.     {
  12.       char *res_userid = PQgetvalue(res, 0, 0);
  13.       char *res_password = PQgetvalue(res, 0, 1);
  14.       ....
  15.     }
  16.     PQclear(res);
  17.   }
  18. }
  19. PQfinish(conn);
标签: ,

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

Posted in C/C++, Database, Technology | 2 Comments »

[原]DBBalancer: 连接池, 负载均衡, 数据库同步的中间层解决方案

Posted by bianbian on 2006-12-15 05:51


本文Tags:

不过这是很老的开源项目了，2001以后都没有更新。。。。。我只是了解一下除了Java做连接池外还有没有其他办法。
[原创翻译] DBBalancer是数据库服务器和客户端（C/C++, TCL, Java JDBC, Perl DBI等)之间的中间层. 尽管目前只支持PostgreSQL, 但是这个架构以后可以推广到其他数据库. 它最大的优点(恕我直言 ;-))就是不需要改变任何一行现有代码, 因为负载均衡是在Postgres的协议层完成的.
DBBalancer有很多功能:
它是一个连接池…. 能完成负载均衡…. 数据库同步….. 而且能同时做这些事情.
http://dbbalancer.sourceforge.net/

[Original] DBBalancer is some sort of middleware that would sit in between of database clients, like C, C++, TCL, Java JDBC, Perl DBI, and a database server. Currently the only server supported is Postgres, but the architecture is open to embrace more servers in a future. One of his strongest (IMHO ;-)) points is that it can be tried or used without changing a line of the existing code, because the balancing is done at Postgres protocol level.

DBBalancer can do different things.

It’s a connection pool…
… a load balancer,
.. and a database replicator.
And can be used any combination of these things at the same time.

标签:

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

Posted in Database, Technology | 1 Comment »

[原]postgreSQL 数据库备份和恢复

Posted by bianbian on 2006-11-19 05:30


本文Tags: , , ,

备份:
./pg_dump -U 用户名 -c -Fp 数据库名 -f 文件.sql
恢复:
./psql -U 用户名 < 文件.sql

标签: , , ,

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

Posted in Database, Technology | No Comments »

[原]用PL/pgSQL写postgreSQL的存储过程

Posted by bianbian on 2006-10-27 01:40


本文Tags: , ,

今天学会了用 PL/pgSQL 写 postgreSQL 的存储过程，网上资料实在少得可怜，唯一能搜到的一些还是抄来抄去的；还是翻postgresql的文档吧，把今天解决的问题说一下吧，希望对其他人有帮助。
问题是这样的，有一张message表：
CREATE TABLE message
(
id int8 NOT NULL,
receiveuserid int8,
senduserid int8,
receivedelete bool DEFAULT false,
senddelete bool DEFAULT false,
……
CONSTRAINT usermessage_pkey PRIMARY KEY (id)
)
略去其他字段，senduserid是发信息的用户id，senddelete如为true则表示这条消息被发信人所删除；至于receive我就不用说了。一条信息只有被发信人和收信人都删除，才能真正从表里删除（这是显然的，否则收信人删了一条消息后，发信人的“发件箱”里就会找不到这条消息）。
所以删除消息（可能是多条消息）的时候要进行各种判断（是否是发信人？是否是收信人？是否真正删除？），用单条SQL语句完成这个工作显然有些困难（当然用循环嵌套select不考虑效率的话也是可以实现的），只好写存储过程了。
顺便介绍常用的PL/pgSQL结构和语法吧：

  • 结构

    • PL/pgSQL是一种块结构的语言，比较方便的是用pgAdmin III新建Function，填入一些参数就可以了。基本上是这样的：

    CREATE OR REPLACE FUNCTION 函数名(参数1，[整型 int4, 整型数组 _int4, …])
    RETURNS 返回值类型 AS
    $BODY$
    DECLARE
    变量声明
    BEGIN
    函数体
    END;
    $BODY$
    LANGUAGE ‘plpgsql’ VOLATILE;

  • 变量类型

    • 除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。

  • 赋值

    • 赋值和Pascal有点像：“变量 := 表达式;”
    有些奇怪的是连接字符串的是“||”，比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;

  • 判断

    • 判断又和VB有些像：
    IF 条件 THEN
    …
    ELSEIF 条件 THEN
    …
    ELSE
    …
    END IF;

  • 循环

    • 循环有好几种写法：
    WHILE expression LOOP
    statements
    END LOOP;
    还有常用的一种是：（从1循环到9可以写成FOR i IN 1..9 LOOP）
    FOR name IN [ REVERSE ] expression .. expression LOOP
    statements
    END LOOP;

  • 其他

    • 还有几个常用的函数：
    SELECT INTO record …; 表示将select的结果赋给record变量（RECORD类型）
    PERFORM query; 表示执行query并丢弃结果
    EXECUTE sql; 表示执行sql语句，这条可以动态执行sql语句（特别是由参数传入构造sql语句的时候特别有用）

最后，贴出解决上面这个问题的存储过程吧：

  1. CREATE OR REPLACE FUNCTION message_deletes(ids "varchar", userid int8)
  2.   RETURNS int4 AS
  3. $BODY$
  4. DECLARE
  5.   r RECORD;
  6.   del bool;
  7.   num int4 := 0;
  8.   sql "varchar";
  9. BEGIN
  10.   sql := 'select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in (' || ids || ')';
  11.   FOR r IN EXECUTE sql LOOP
  12.     del := false;
  13.     IF r.receiveuserid=userid and r.senduserid=userid THEN
  14.       del := true;
  15.     ELSEIF r.receiveuserid=userid THEN
  16.       IF r.senddelete=false THEN
  17.         update message set receivedelete=true where id = r.id;
  18.       ELSE
  19.         del := true;
  20.       END IF;
  21.     ELSEIF r.senduserid=userid THEN
  22.       IF r.receivedelete=false THEN
  23.         update message set senddelete=true where id = r.id;
  24.       ELSE
  25.         del := true;
  26.       END IF;
  27.     END IF;
  28.     IF del THEN
  29.       delete from message where id = r.id;
  30.       num := num + 1;
  31.     END IF;
  32.   END LOOP;
  33.   return num;
  34. END;
  35. $BODY$
  36.   LANGUAGE 'plpgsql' VOLATILE;

测试使用：
postgres PL/pgSQL

标签: , ,

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

Posted in Database, Technology | 1 Comment »

[原]PostgreSQL complex query(PostgreSQL的复杂查询)

Posted by bianbian on 2006-09-19 02:27


本文Tags: ,

试了一会儿，先记下来。省得以后忘掉。

Well, the tables are: (tag的表情况，usertags表记录了每个用户的tag（一行记录一个tag），usersites表记录了每个url对应的多个tags，urltags表记录了一个usersiteid对应多个usertagid（反过来也是一个usertagid对应多个usersiteid）)

  1. usertags                  usersites                       urltags
  2. =======================================================================
  3. id tagname userid      id url title userid tags      id usersiteid usertagid

now we need to get all tagnames, tagid, and used times of a tag, sorted by used times, how to do it? (现在我们需要一次查询得到所有的tagname、每个tag对应的tagid和使用次数并按使用次数排序)
SQL 语句如下：

  1. SELECT count(t.usersiteid) AS count, t.usertagid, n.tagname
  2.   FROM urltags t, usertags n
  3.     WHERE t.usertagid=n.id
  4.     AND usertagid in ( select DISTINCT on (usertagid) usertagid from urltags )
  5.   group by t.usertagid,n.tagname
  6.   order by count DESC

中间的子查询“select DISTINCT on (usertagid) usertagid from urltags ”查询出所有对照表里的usertagid，其中DISTINCT on(usertagid)表示只查询出usertagid字段不重复的记录（即多个相同的usertagid值的话只取一个）。DISTINCT的写法可能只是postgreSQL的特殊函数，不过相信其他数据库应该也有不同写法的支持。
执行结果截图，应该是对的吧。。。。呵呵：
Postgresql_query

标签: ,

遵守创作共用协议，转载请链接形式注明来自http://bianbian.org 做人要厚道

相关日志

Posted in Database, Technology | No Comments »