db.java

与postgresql数据库结合的数据库水印软件包

/*
 * 
 * Copyright 2003,2004 The Watermill Team
 *
 * This file is part of Watermill.
 *
 *    Watermill is free software; you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation; either version 2 of the License, or
 *    (at your option) any later version.
 *
 *    Watermill is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with Watermill; if not, write to the Free Software
 *    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 */
package Watermill.relational;

import java.sql.*;
import java.util.*;
import Watermill.kernel.*;


public class DB extends Document {
    private String dbName,dbUser,dbPassword;
    private transient Connection con=null;

    public Connection getConnection(){
	Connection connection=null;
	if (con!=null) return con;
	try {
	    Class.forName("org.postgresql.Driver"); //load the driver
	    
	    connection=DriverManager.getConnection("jdbc:postgresql:"+dbName, dbUser, dbPassword);
	    
	    DatabaseMetaData dbmd = connection.getMetaData(); //get MetaData to confirm connection
	    Msg.debug("Connection to "+dbmd.getDatabaseProductName()+" "+ dbmd.getDatabaseProductVersion()+" successful."); 
	}
	catch (Exception e){
	    Msg.fatal(e.getMessage());
	}
	con=connection;
	Msg.debug("Creating a connection.");
	return connection;
    }

    public DB(String iDbName,String iDbUser,String iDbPassword){
	dbName=iDbName;
	dbUser=iDbUser;
	dbPassword=iDbPassword;
    }

    public Value getValue(Identifier iid){
	try {
	    RIdentifier id=(RIdentifier)iid;
	    Connection connection=getConnection();
	    Statement stm = connection.createStatement();
	    RKeyValue kv=id.getKeyValue();
	    RKey k=kv.getKey();
	    String keyAttribute=k.getName();
	    String keyTable=k.getTable();
	    String keyValue=kv.getKeyRef();
	    String valueAttribute=id.getName();
	    
	    Chrono.setMode(Chrono.queryMode);
	    String queryString = "SELECT "+valueAttribute+" FROM "+keyTable+" WHERE "+keyAttribute+"='"+keyValue+"'";
	    ResultSet rs = stm.executeQuery(queryString);
	    rs.next();
	    Chrono.setMode(Chrono.cpuMode);
	    try {
		return new Value(Integer.parseInt(rs.getString(1)));
	    }
	    catch (Exception e){
		return null;
	    }
	}
	catch (Exception e){
	    Msg.fatal(e);
	    return null;
	}
    }
    
    public void setValue(Identifier iid,Value v){
	try {
	    RIdentifier id=(RIdentifier)iid;
	    Connection connection=getConnection();
	    Statement stm = connection.createStatement();
	    RKeyValue kv=id.getKeyValue();
	    RKey k=kv.getKey();
	    String keyAttribute=k.getName();
	    String keyTable=k.getTable();
	    String keyValue=kv.getKeyRef();
	    String valueAttribute=id.getName();
	
	    String queryString = "UPDATE "+keyTable+" SET "+valueAttribute+"="+v.get()+" WHERE "+keyAttribute+"='"+keyValue+"'";
	    Msg.debug(queryString);
	    stm.execute(queryString);
	}
	catch (Exception e){
	    Msg.fatal(e);
	}
    }

    public void addValue(Identifier iid,Value v){
	try {
	    RIdentifier id=(RIdentifier)iid;
	    Connection connection=getConnection();
	    Statement stm = connection.createStatement();
	    RKeyValue kv=id.getKeyValue();
	    RKey k=kv.getKey();
	    String keyAttribute=k.getName();
	    String keyTable=k.getTable();
	    String keyValue=kv.getKeyRef();
	    String valueAttribute=id.getName();
	
	    String queryString = "UPDATE "+keyTable+" SET "+valueAttribute+"="+valueAttribute+"+"+v.get()+" WHERE "+keyAttribute+"='"+keyValue+"'";
	    stm.execute(queryString);
	}
	catch (Exception e){
	    Msg.fatal(e);
	}
    }
    
    public Schema getSchema(){
	return null;
    }

    public Identifiers getModifiableValuesIdentifiers(LocalConstraints lcs){
	Identifiers res=new Identifiers();

	for(Enumeration e=lcs.elements();e.hasMoreElements();){
	    LocalConstraint lc=(LocalConstraint)(e.nextElement());
	    res.addAll(getModifiableValuesIdentifiers(lc));
	}
	return res;
    }

    public RKey getTableKey(String tableName){
 	try {
 	    Connection connection=getConnection();
	    DatabaseMetaData dbmd = connection.getMetaData(); //get MetaData to confirm connection
	    ResultSet rs=dbmd.getPrimaryKeys(null,null,tableName);
	    rs.next();
	    return new RKey(tableName,rs.getString("COLUMN_NAME"));
	}
	catch (Exception e){
	    Msg.fatal(e);
	    return null;
	}
    }
    
    public Identifiers getModifiableValuesIdentifiers(LocalConstraint lc){
	Identifiers id=new Identifiers();
 	try {
	    Connection connection=getConnection();
	    Statement stm=connection.createStatement();
	    
	    RKey key=getTableKey(lc.tableName);
	    
 	    String query="select "+key.getName()+" from "+lc.tableName;

	    Chrono.setMode(Chrono.queryMode);
	    ResultSet rs=stm.executeQuery(query);
	    while(rs.next()){
		RKeyValue kv=new RKeyValue(key,rs.getString(key.getName()));
		id.add(new RIdentifier(kv,lc.attributeName,lc.distortion));
		Msg.debug("Adding local distortion "+lc.distortion+" to "+kv.toString());
	    }
	    Chrono.setMode(Chrono.cpuMode);
		
 	}catch (Exception e){
	    Msg.fatal(e);
	}
	return id;
    }

    public DependencyMatrix getDependency(GlobalConstraints gcs){
	DependencyMatrix res=new DependencyMatrix();

	for(Enumeration e=gcs.elements();e.hasMoreElements();){
	    GlobalConstraint gc=(GlobalConstraint)(e.nextElement());
	    res.add(getDependency(gc));
	}
	return res;
    }

    public Identifiers getDependency(GlobalConstraint gc){
	Identifiers id=new Identifiers();
 	try {
	    Connection connection=getConnection();
	    Statement stm=connection.createStatement();

 	    String query="select "+gc.keyName+" from "+gc.tableName+" where "+gc.condition;
	    Msg.debug(query);
	    
	    RKey key=new RKey(gc.tableName,gc.keyName);

	    Chrono.setMode(Chrono.queryMode);
	    ResultSet rs=stm.executeQuery(query);
	    Chrono.setMode(Chrono.cpuMode);
	    while(rs.next()){
		RKeyValue kv=new RKeyValue(key,rs.getString(gc.keyName));
		id.add(new RIdentifier(kv,gc.attributeName));
	    }
		
 	}catch (Exception e){
	    Msg.fatal(e);
	}
	return id;
    }

    public static void main(String arg[]){
	DB d=new DB("ggdb","david","");
	Identifier i=new RIdentifier(new RKeyValue(new RKey("personne","nom"),"jean"),"taille");
	d.setValue(i,new Value(131));
	d.addValue(i,new Value(-1));
	
	
    }
    public boolean checkLocalConstraint(Document d,LocalConstraint lc){
	try {
 	    Connection connection=getConnection();
	    Statement stm = connection.createStatement();
	    
	    String queryString = "SELECT "+lc.attributeName+" FROM "+lc.tableName+" order by "+lc.keyName;
	    Msg.debug("checkLocalConstraint:"+queryString);
	    ResultSet rs = stm.executeQuery(queryString);
	    
	    DB d2=(DB)d;
	    Connection connection2=d2.getConnection();
	    Statement stm2=connection2.createStatement();
	    ResultSet rs2=stm2.executeQuery(queryString);
	    
	    while(rs.next()){
		rs2.next();
		
		int val1=Integer.parseInt(rs.getString(1));
		int val2=Integer.parseInt(rs2.getString(1));
		if (Math.abs(val2-val1)>lc.distortion)
		    return false;
	    }
	    return true;
	}
	catch (Exception e){
	    Msg.fatal(e);
	    return false;
	}
    }	
	public boolean checkLocalConstraints(Document d,LocalConstraints lc){
	for(Enumeration e=lc.elements();e.hasMoreElements();){
	    if (!checkLocalConstraint(d,(LocalConstraint)e.nextElement()))
		return false;
	}
	return true;
    }

     public boolean checkGlobalConstraint(Document d,GlobalConstraint gc){
	try {
	    Connection connection=getConnection();
	    Statement stm = connection.createStatement();
	    
	    String queryString = "SELECT sum("+gc.attributeName+") FROM "+gc.tableName+" where "+gc.condition;
	    Msg.debug("checkGlobalConstraint:"+queryString);
	    ResultSet rs = stm.executeQuery(queryString);
	    
	    DB d2=(DB)d;
	    Connection connection2=d2.getConnection();
	    Statement stm2=connection2.createStatement();
	    ResultSet rs2=stm2.executeQuery(queryString);
	    
	    rs.next();
	    rs2.next();

	    int val1=Integer.parseInt(rs.getString(1));
	    int val2=Integer.parseInt(rs2.getString(1));

	    Msg.debug("global distortion : "+(val1-val2));
	    return (Math.abs(val1-val2)<=gc.distortion);
	}
	catch (Exception e){
	    Msg.fatal(e);
	    return false;
	}
    }	

    public boolean checkGlobalConstraints(Document d,GlobalConstraints gcs){
	for(Enumeration e=gcs.elements();e.hasMoreElements();){
	    if (!checkGlobalConstraint(d,(GlobalConstraint)e.nextElement()))
		return false;
	}
	return true;
    }
}