分类目录归档:Database

Database, mysql, PostgreSQL, Oracle…

[原] Linq不能使用CLR Stored Procedure返回ISingleResult

使用C#写CLR存储过程确实很方便。
不过遗憾的是,如果CLR里通过SqlContext.Pipe.Send()返回Select结果的话,并不能被Linq to SQL自动识别而返回ISingleResult的。(因为部署CLR存储过程实际产生的代码是不包含select语句的)
换句话说,所有CLR存储过程的返回值在Linq to SQL里都会被当成int。
解决办法:
1)如果该存储过程没有out的参数,比较好办:使用ExecuteQuery并EXEC 存储过程
如:

这种方法能取到结果,但是没有办法获得out参数。

2)如果该存储过程又有out的参数需要获得值,又返回select结果集的:
非常麻烦,只能部署CLR存储过程前,先手动在数据库里create procedure(同名,里面填上参数和select语句),
然后骗取Linq to SQL生成返回ISingleResult的代码,然后drop procedure,再部署CLR存储过程。
否则,即使把返回int的代码手动改成返回ISingleResult的,也会报错。我这里一直报 ArgumentOutOfRangeException
所以针对这种存储过程,最好还是T-SQL语句写。CLR的反而好麻烦。

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

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

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

原文: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类型,这样方便我们读取. 在对象-关系映射文件里的>一书.我非常喜欢这本书,讲的非常简单明了.感兴趣的朋友可以去买一本看看(当当打7.3折哦).

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

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编码总结

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

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

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

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

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

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

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

不过这是很老的开源项目了,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.

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

今天学会了用 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语句的时候特别有用)

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

测试使用:
postgres PL/pgSQL

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

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

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

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 语句如下:

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