[root] /weblog /database




login:

password:

title search:




 

Wed May 07 19:19:11 HKT 2008

database



(google search) (amazon search) second
download here

Tue Mar 18 01:25:23 HKT 2008 From /weblog/database

Databases and Objects


A nice article which talk about relation DB and OO. But I have an difference opinions, I think that advantage of relational database is the problem that OO like to prevent.

The main advantage of relational database is the relatioal model make it easy to perform reporting. However, relation model is more difficult than hierarchical model (OO model) to enable encapsulation and polymorphism.

Turn out, if we stand on OO programmer point of view, we will complaint that most RDBMS is in fact just a giant global variable; If we stand as DBA point of view, we will complaint other thing.

Can we create something that optimize for both world? I don’t think so…

http://jooto.com/blog/index.php/2005/11/17/databases-and-objects/

Update: a message also discuss about this: http://groups.yahoo.com/group/pragprog/message/5682

Update #2: A further analysis of ORM: http://www.codinghorror.com/blog/archives/000621.html

(google search) (amazon search)


Thu Feb 21 22:27:02 HKT 2008 From /weblog/database

oracle


Some comment saying that you cannot complain oracle for non-standard setup... However, should I break anything else just to install oracle? Anyway, may be a useful resource of installing oracle cluster - http://thedailywtf.com/forums/65743/ShowPost.aspx

Oracle SQL links - http://mycodeblog.blogspot.com/2007/06/sql-cheat-sheet.html

Pure java solution to full text search - http://www.infoq.com/news/2007/10/lucene-oracle http://marceloochoa.blogspot.com[..]nning-lucene-inside-your-oracle-jvm.html http://dbprism.cvs.sourceforge.net/dbprism/ojvm/

Oracle does allow definitions of user-defined types.Please see
http://download.oracle.com[..]er.111/b28286/sql_elements001.htm#i46376

User-defined types can then be used for column definitions. See e.g.
the fourth example given here
http://download.oracle.com[..].111/b28286/statements_7002.htm#i2062833

Oracle also allows creation of a table based on one user-defined type,
i.e. an "object table".
See here
http://download.oracle.com[..].111/b28286/statements_7002.htm#i2159410

User-defined types can be changed; see
http://download.oracle.com[..].111/b28286/statements_4002.htm#i2057828
Oracle even offers an option as to whether existing data in tables
based on previous type definition is converted or not; see
http://download.oracle.com[..].111/b28286/statements_4002.htm#i2079300
There are a couple of pages in the Object-Relational Developer's Guide explaining the consequences, briefly...

Note: all links above are to Oracle 11g documentation, but many, if not all, of these features have been available since 9i.

(google search) (amazon search)



Tue Jan 08 21:30:32 HKT 2008 From /weblog/database

query table metadata


Several database using following query:

select column_name, numeric_precision as length, data_type from information_schema.columns where table_name='TEST'

H2, mysql and postgresql support above syntax

http://h2database.com/ipowerb/index.php?act=ST&f=2&t=205

There difference of getColumnName() and getColumnLabel() , for SQL "select name as n from table" getColumnName() with return "n" and getColumnLabel() will return "name" - http://ola-bini.blogspot.com[..]you-using-resultsetmetadatagetcolum.html

(google search) (amazon search)



Sun Jan 06 01:02:40 HKT 2008 From /weblog/database

migration


A very cool tool to support Database versioning, look like this can work independence from Ruby on Rails

http://www.google.com[..]&lr=&q=Ruby+Rails+Migrations&btnG=Search
An example coding with this tool - http://rails.aizatto.com[..]7/activerecord-migrations-without-rails/ http://wiki.rubyonrails.org/rails/pages/UnderstandingMigrations

A very cool link about Database versioning: http://www.cmcrossroads.com/cgi-bin/cmwiki/view/CM/CMandDatabases

An eclipse plugin sound cool - http://dbcopy.sourceforge.net/en/dbcopy/index.html but I cannot get it working, instead, http://dbcopyplugin.sourceforge.net/ work a lot better. However it is better to install using installation jars (and source) of SQuirreL, otherwise there may be version not map issue.

(google search) (amazon search)



Mon Dec 31 17:17:55 HKT 2007 From /weblog/database

column-db


Normally, we store object like

1,Smith,Joe,40000;2,Jones,Mary,50000;3,Johnson,Cathy,44000;

However, some say store as

1,2,3;Smith,Jones,Johnson;Joe,Mary,Cathy;40000,50000,44000;

is better for many cases...

http://www.infoq.com/news/2007/09/row-vs-column-dbs

(google search) (amazon search)



Wed Nov 14 19:05:19 HKT 2007 From /weblog/database

alternatives


An arguement of use / not use RDBMS http://www.cincomsmalltalk.com[..]gView?showComments=true&entry=3366636502 .

Hsql is a very nice tool for development: fast, support most sql standard, easy to setup. In addition, there is a one really nice feature. It will store the INSERT, UPDATE and DELETE statement in a script file to persist the database status. This make debug a lot simpler. Highly recommend!

http://hsqldb.sourceforge.net/

Besides, a nice introduction of SQLite and Java Berkeley DB - http://www-900.ibm.com[..]operWorks/cn/java/l-embed-db/index.shtml

A solution of close don't commit data to disk: http://blog.taragana.com[..]ion-close-doesnt-write-to-disk-solution/ but for me this is feature than bug, as I can easily keep my data back to the copy I want.

A document base DB, it is not hold a table ( or relation ) , it hold a Document , what I understand it is a map - http://www.couchdbwiki.com/index.php?title=Main_Page

(google search) (amazon search)


Tue Nov 13 20:30:06 HKT 2007 From /weblog/database

ORM


A presentation about Politics issues of using ORM persistence - http://www.softwaresummit.com[..]ers/SmithDonaldPoliticsOfPersistence.pdf

A checklist of features of ORM library, which, I think, may be too detail to consider that much? http://madgeek.com/Articles/ORMapping/EN/mapping.htm

Some problem of ORM nowadays - http://blogs.ittoolbox.com[..]e-ubiquitous-bastardization-of-orm-17100

This post rise a good point, if the ORM tool doesn't provide a simpler solution, why bother to use it? Just because many people using it? http://en.newinstance.it/2006/10/23/trimming-jdbc/

We have been using hibernate for while, actually it is good. But LGPL block us from using it anymore...

Here is some other experience about this - http://shinetech.com/pages/viewpage.action?pageId=649 and the discussion of this article - http://www.theserverside.com[..]m/news/thread.tss?thread_id=41174#212825

Recently come across this ORM tool, look nice:
http://www.jenkov.com/mrpersister/introduction.tmpl major forces are:

Read objects of any size in just 3 lines of code.
Automatic and manual mapping of objects to database tables.
No config files required.
SQL as query language.
New: Automatic connection and transaction management.
Automatic SQL generation for most trivial tasks.
Batch updates of multiple objects.
Compound primary key support.
Partial object reading and writing.
Read Filters.
Simplifies trivial JDBC tasks.
Interleave custom JDBC with Mr. Persister operations.
Fully Pluggable Design.

And there is one try to use javadoc doclet approach to make all those SQL documented at javadoc also, look like an interesting idea - http://voruta.sourceforge.net/

(google search) (amazon search)


Mon Oct 15 19:32:55 HKT 2007 From /weblog/database

ansi joining


A nice article about standard ANSI joining syntax:

http://www.oracle.com/technology/oramag/oracle/01-nov/o61sql.html

Recognizing well known patterns involving outer joins will form a ground-level vocabulary for your further explorations of your data. This article examined syntax, surprises, and rules of thumb with respect to the usage of joins.

http://www.onjava.com/lpt/a/4443

A blog with nice graphic show how join work - http://www.codinghorror.com/blog/archives/000976.html

(google search) (amazon search)



Mon Aug 06 18:23:10 HKT 2007 From /weblog/database

database style


A pointer to various database style discussion, like [application database], [integration database], [silly database], [smart database] ...

http://www.pervasivecode.com[..]n-of-stupid-databases-being-a-good-idea/

(google search) (amazon search)


Thu Jun 14 00:59:09 HKT 2007 From /weblog/database

Database Refactorings


List of Database Refactorings

Pramod has been working on defining the various refactorings that he's used in the approach defined in our paper on evolutionary database design Here we keep a list of the refactorings that he has defined so far.

http://martinfowler.com/dbrefact/

There is some tools support, but not sure if that really work... - http://www.simple-talk.com[..]-database-development-with-sql-refactor/

Video of tutorial presentation, very nice - http://www.infoq.com/presentations/ambler-database-refactoring

(google search) (amazon search)


Wed Jun 13 23:34:34 HKT 2007 From /weblog/database

solve sudoku in sql


A very complicate TSQL sample... in my opinions: http://www.vsj.co.uk/articles/display.asp?id=540

Even more implementation - http://lambda-the-ultimate.org/node/2291

which introduce some interesting function in vendor specific SQL, like Oracle MODEL - http://download-uk.oracle.com[..]erver.102/b14223/sqlmodel.htm#sthref1855

(google search) (amazon search)


Thu May 31 19:33:16 HKT 2007 From /weblog/database

null



Just find out that difference database sort null in timestamp column in difference way:

MySQL: NULL values are presented first, or last if you specify DESC to sort in descending order. Exception: In MySQL 4.0.2 through 4.0.10, NULL values sort first regardless of sort order.

http://dev.mysql.com/doc/mysql/en/problems-with-null.html

SQL server 2000, Sybase 12.5 and hsql 1.8.0: NULL values are presented first, or last if you specify DESC to sort in descending order.

Oracle 8: NULL values are presented last, or first if you specify DESC to sort in descending order.

A more detail default sorting behaviour:
{null, -1, +1} // ASC sort for informix, microsoft, mysql and sybase
{-1, +1, null} // ASC sort for IBM, Ingres, InterBase, Oracle and PostgreSQL
{+1, -1, null} // ASC sort for informix, InterBase, PostgreSQL, microsoft, mysql and sybase
{null, +1, -1} // ASC sort for IBM, Ingres and Oracle

A discussion about using NULL or not - http://discuss.joelonsoftware.com/default.asp?design.4.403282 http://discuss.joelonsoftware.com/default.asp?design.4.494249

One solution - http://web.onetel.com[..]Manifesto/Missing-info-without-nulls.pdf

(google search) (amazon search)


Wed Apr 11 00:51:38 HKT 2007 From /weblog/database

JDBC 4


The one I long for is the SQL Exception Handling Enhancements, look like I don't need to issue an select everytime before insert or delete to prevent SQL exception from duplicate record or foreign key constraint

However, not sure why we need Annotation-Based SQL Queries, better than putting SQL as String?

http://www.artima.com/lejava/articles/jdbc_fourP.html
http://java.sys-con.com/read/111252_p.htm
http://javaswamy.blogspot.com/2006/08/jdbc-40-resources.html
http://www.javaspecialists.co.za/archive/newsletter.do?issue=138
http://today.java.net[..]day/2007/04/10/whats-new-in-jdbc-40.html

(google search) (amazon search)



Sat Mar 03 03:01:17 HKT 2007 From /weblog/database/performance

index


A simple example of how to use EXPLAIN to find out the bottleneck of the query and add suitable index to suitable column - http://hackmysql.com/case4

Bitmap Index vs. B-tree Index: Which and When? - http://www.oracle.com/technology/pub/articles/sharma_indexes.html http://publib.boulder.ibm.com[..]/v5r3/index.jsp?topic=/rzajq/perf21c.htm

(google search) (amazon search)





Wed Oct 18 16:17:25 HKT 2006 From /weblog/database

Gotchas


Various problems from various databases

http://sql-info.de/mysql/gotchas.html#1_13

(google search) (amazon search)