Strabon

changeset 492:6c92f5b28e76

Indices on hash_values and datetime_values now constructed during initialization, if they have not initially been created.
Measure taken to avoid issues with 'deprecated' db dumps
author Manos Karpathiotakis <mk@di.uoa.gr>
date Mon Jul 23 14:33:56 2012 +0300 (2012-07-23)
parents 23a9e69cb926
children b51a8e33b86b
files generaldb/src/main/java/org/openrdf/sail/generaldb/schema/ValueTable.java generaldb/src/main/java/org/openrdf/sail/generaldb/schema/ValueTableFactory.java scripts/v2.2/runNoaRefinementChain.sh-bakcup
line diff
     1.1 --- a/generaldb/src/main/java/org/openrdf/sail/generaldb/schema/ValueTable.java	Mon Jul 23 14:30:44 2012 +0300
     1.2 +++ b/generaldb/src/main/java/org/openrdf/sail/generaldb/schema/ValueTable.java	Mon Jul 23 14:33:56 2012 +0300
     1.3 @@ -11,6 +11,7 @@
     1.4  import java.sql.Types;
     1.5  import java.util.ArrayList;
     1.6  import java.util.List;
     1.7 +import java.util.Map;
     1.8  import java.util.concurrent.BlockingQueue;
     1.9  
    1.10  import org.openrdf.sail.generaldb.GeneralDBSqlTable;
    1.11 @@ -123,12 +124,12 @@
    1.12  	}
    1.13  
    1.14  	public void initialize()
    1.15 -		throws SQLException
    1.16 -	{
    1.17 +			throws SQLException
    1.18 +			{
    1.19  		StringBuilder sb = new StringBuilder();
    1.20  		/****************/
    1.21 -//		sb.append("INSERT INTO ").append(getInsertTable().getName());
    1.22 -//		sb.append(" (id, value) VALUES (?, ?)");
    1.23 +		//		sb.append("INSERT INTO ").append(getInsertTable().getName());
    1.24 +		//		sb.append(" (id, value) VALUES (?, ?)");
    1.25  		sb.append("INSERT INTO ").append(getInsertTable().getName());
    1.26  		GeneralDBSqlTable table = (GeneralDBSqlTable)getInsertTable();
    1.27  		sb.append(table.buildInsertValue(sql(sqlType, length)));
    1.28 @@ -155,16 +156,27 @@
    1.29  			}
    1.30  		}
    1.31  		else {
    1.32 +			//Adding index on datetime values to tackle the case of "deprecated" existing db dumps
    1.33 +			if(this.getName().equalsIgnoreCase("datetime_values") || this.getName().equalsIgnoreCase("hash_values"))
    1.34 +			{
    1.35 +				Map<String, List<String>> allIndices = table.getIndexes();
    1.36 +				if(allIndices.size()<2)
    1.37 +				{
    1.38 +					//Datetime values index does not exist - only primary key constraint is present
    1.39 +					table.index(VALUE_INDEX);
    1.40 +				}
    1.41 +				
    1.42 +			}
    1.43  			table.count();
    1.44  		}
    1.45  		if (temporary != null && !temporary.isCreated()) {
    1.46  			createTemporaryTable(temporary);
    1.47  		}
    1.48 -	}
    1.49 +			}
    1.50  
    1.51  	public void close()
    1.52 -		throws SQLException
    1.53 -	{
    1.54 +			throws SQLException
    1.55 +			{
    1.56  		if (insertSelect != null) {
    1.57  			insertSelect.close();
    1.58  		}
    1.59 @@ -172,11 +184,11 @@
    1.60  			temporary.close();
    1.61  		}
    1.62  		table.close();
    1.63 -	}
    1.64 +			}
    1.65  
    1.66  	public synchronized void insert(Number id, String value)
    1.67 -		throws SQLException, InterruptedException
    1.68 -	{
    1.69 +			throws SQLException, InterruptedException
    1.70 +			{
    1.71  		ValueBatch batch = getValueBatch();
    1.72  		if (isExpired(batch)) {
    1.73  			batch = newValueBatch();
    1.74 @@ -186,11 +198,11 @@
    1.75  		batch.setString(2, value);
    1.76  		batch.addBatch();
    1.77  		queue(batch);
    1.78 -	}
    1.79 +			}
    1.80  
    1.81  	public synchronized void insert(Number id, Number value)
    1.82 -		throws SQLException, InterruptedException
    1.83 -	{
    1.84 +			throws SQLException, InterruptedException
    1.85 +			{
    1.86  		ValueBatch batch = getValueBatch();
    1.87  		if (isExpired(batch)) {
    1.88  			batch = newValueBatch();
    1.89 @@ -200,7 +212,7 @@
    1.90  		batch.setObject(2, value);
    1.91  		batch.addBatch();
    1.92  		queue(batch);
    1.93 -	}
    1.94 +			}
    1.95  
    1.96  	public ValueBatch getValueBatch() {
    1.97  		return this.batch;
    1.98 @@ -217,8 +229,8 @@
    1.99  	}
   1.100  
   1.101  	public void initBatch(ValueBatch batch)
   1.102 -		throws SQLException
   1.103 -	{
   1.104 +			throws SQLException
   1.105 +			{
   1.106  		batch.setTable(table);
   1.107  		batch.setBatchStatement(prepareInsert(INSERT));
   1.108  		batch.setMaxBatchSize(getBatchSize());
   1.109 @@ -229,11 +241,11 @@
   1.110  			}
   1.111  			batch.setInsertStatement(insertSelect);
   1.112  		}
   1.113 -	}
   1.114 +			}
   1.115  
   1.116  	public void queue(ValueBatch batch)
   1.117 -		throws SQLException, InterruptedException
   1.118 -	{
   1.119 +			throws SQLException, InterruptedException
   1.120 +			{
   1.121  		this.batch = batch;
   1.122  		if (queue == null) {
   1.123  			batch.flush();
   1.124 @@ -241,17 +253,17 @@
   1.125  		else {
   1.126  			queue.put(batch);
   1.127  		}
   1.128 -	}
   1.129 +			}
   1.130  
   1.131  	public void optimize()
   1.132 -		throws SQLException
   1.133 -	{
   1.134 +			throws SQLException
   1.135 +			{
   1.136  		table.optimize();
   1.137 -	}
   1.138 +			}
   1.139  
   1.140  	public boolean expunge(String condition)
   1.141 -		throws SQLException
   1.142 -	{
   1.143 +			throws SQLException
   1.144 +			{
   1.145  		synchronized (table) {
   1.146  			int count = table.executeUpdate(EXPUNGE + condition);
   1.147  			if (count < 1)
   1.148 @@ -259,11 +271,11 @@
   1.149  			table.modified(0, count);
   1.150  			return true;
   1.151  		}
   1.152 -	}
   1.153 +			}
   1.154  
   1.155  	public List<Long> maxIds(int shift, int mod)
   1.156 -		throws SQLException
   1.157 -	{
   1.158 +			throws SQLException
   1.159 +			{
   1.160  		String column = "id";
   1.161  		StringBuilder expr = new StringBuilder();
   1.162  		expr.append("MOD((").append(column);
   1.163 @@ -296,36 +308,36 @@
   1.164  		finally {
   1.165  			st.close();
   1.166  		}
   1.167 -	}
   1.168 +			}
   1.169  
   1.170  	public String sql(int type, int length) {
   1.171  		switch (type) {
   1.172 -			case Types.VARCHAR:
   1.173 -				if (length > 0)
   1.174 -					return "VARCHAR(" + length + ")";
   1.175 -				return "TEXT";
   1.176 -			case Types.LONGVARCHAR:
   1.177 -				if (length > 0)
   1.178 -					return "LONGVARCHAR(" + length + ")";
   1.179 -				return "TEXT";
   1.180 -			case Types.BIGINT:
   1.181 -				return "BIGINT";
   1.182 -			case Types.INTEGER:
   1.183 -				return "INTEGER";
   1.184 -			case Types.SMALLINT:
   1.185 -				return "SMALLINT";
   1.186 -			case Types.FLOAT:
   1.187 -				return "FLOAT";
   1.188 -			case Types.DOUBLE:
   1.189 -				return "DOUBLE";
   1.190 -			case Types.DECIMAL:
   1.191 -				return "DECIMAL";
   1.192 -			case Types.BOOLEAN:
   1.193 -				return "BOOLEAN";
   1.194 -			case Types.TIMESTAMP:
   1.195 -				return "TIMESTAMP";
   1.196 -			default:
   1.197 -				throw new AssertionError("Unsupported SQL Type: " + type);
   1.198 +		case Types.VARCHAR:
   1.199 +			if (length > 0)
   1.200 +				return "VARCHAR(" + length + ")";
   1.201 +			return "TEXT";
   1.202 +		case Types.LONGVARCHAR:
   1.203 +			if (length > 0)
   1.204 +				return "LONGVARCHAR(" + length + ")";
   1.205 +			return "TEXT";
   1.206 +		case Types.BIGINT:
   1.207 +			return "BIGINT";
   1.208 +		case Types.INTEGER:
   1.209 +			return "INTEGER";
   1.210 +		case Types.SMALLINT:
   1.211 +			return "SMALLINT";
   1.212 +		case Types.FLOAT:
   1.213 +			return "FLOAT";
   1.214 +		case Types.DOUBLE:
   1.215 +			return "DOUBLE";
   1.216 +		case Types.DECIMAL:
   1.217 +			return "DECIMAL";
   1.218 +		case Types.BOOLEAN:
   1.219 +			return "BOOLEAN";
   1.220 +		case Types.TIMESTAMP:
   1.221 +			return "TIMESTAMP";
   1.222 +		default:
   1.223 +			throw new AssertionError("Unsupported SQL Type: " + type);
   1.224  		}
   1.225  	}
   1.226  
   1.227 @@ -343,34 +355,34 @@
   1.228  	}
   1.229  
   1.230  	protected PreparedStatement prepareInsert(String sql)
   1.231 -		throws SQLException
   1.232 -	{
   1.233 +			throws SQLException
   1.234 +			{
   1.235  		return table.prepareStatement(sql);
   1.236 -	}
   1.237 +			}
   1.238  
   1.239  	protected PreparedStatement prepareInsertSelect(String sql)
   1.240 -		throws SQLException
   1.241 -	{
   1.242 +			throws SQLException
   1.243 +			{
   1.244  		return table.prepareStatement(sql);
   1.245 -	}
   1.246 +			}
   1.247  
   1.248  	protected void createTable(RdbmsTable table)
   1.249 -		throws SQLException
   1.250 -	{
   1.251 +			throws SQLException
   1.252 +			{
   1.253  		StringBuilder sb = new StringBuilder();
   1.254  		sb.append("  id ").append(sql(idType, -1)).append(" NOT NULL,\n");
   1.255  		sb.append("  value ").append(sql(sqlType, length));
   1.256  		sb.append(" NOT NULL\n");
   1.257  		table.createTable(sb);
   1.258 -	}
   1.259 +			}
   1.260  
   1.261  	protected void createTemporaryTable(RdbmsTable table)
   1.262 -		throws SQLException
   1.263 -	{
   1.264 +			throws SQLException
   1.265 +			{
   1.266  		StringBuilder sb = new StringBuilder();
   1.267  		sb.append("  id ").append(sql(idType, -1)).append(" NOT NULL,\n");
   1.268  		sb.append("  value ").append(sql(sqlType, length));
   1.269  		sb.append(" NOT NULL\n");
   1.270  		table.createTemporaryTable(sb);
   1.271 -	}
   1.272 +			}
   1.273  }
     2.1 --- a/generaldb/src/main/java/org/openrdf/sail/generaldb/schema/ValueTableFactory.java	Mon Jul 23 14:30:44 2012 +0300
     2.2 +++ b/generaldb/src/main/java/org/openrdf/sail/generaldb/schema/ValueTableFactory.java	Mon Jul 23 14:33:56 2012 +0300
     2.3 @@ -21,8 +21,8 @@
     2.4  /**
     2.5   * Factory class used to create or load the database tables.
     2.6   * 
     2.7 - * @author James Leigh
     2.8 - * 
     2.9 + * @author Initial rdbms version: James Leigh
    2.10 + * @author generaldb version: Manos Karpathiotakis
    2.11   */
    2.12  public class ValueTableFactory {
    2.13  
    2.14 @@ -128,7 +128,6 @@
    2.15  		
    2.16  		/****************************************************************/
    2.17  		//TODO
    2.18 -		//GeoValueTable myAddition = createGeoValueTable(conn,queue,"manolis_values",VARCHAR,VCL);
    2.19  		GeoValueTable myAddition = createGeoValueTable(conn,queue,"geo_values",VARCHAR,VCL);
    2.20  		literals.setGeoSpatialTable(myAddition);
    2.21  		return literals;
     3.1 --- a/scripts/v2.2/runNoaRefinementChain.sh-bakcup	Mon Jul 23 14:30:44 2012 +0300
     3.2 +++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.3 @@ -1,167 +0,0 @@
     3.4 -#!/bin/bash
     3.5 -LOC="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
     3.6 -
     3.7 -ENDPOINT="http://localhost:8080/endpoint"
     3.8 -DB="endpoint"
     3.9 -GRIDURL="http://kk.di.uoa.gr/grid_4.nt"
    3.10 -
    3.11 -#dataDir="http://localhost/noa-teleios/out_triples/"
    3.12 -dataDir="http://godel.di.uoa.gr/hotspots/"
    3.13 -name="HMSG2_IR_039_s7_070825"
    3.14 -suffix=".hotspots.n3"
    3.15 -
    3.16 -logFile="chain.log"
    3.17 -#countWTime="/usr/bin/time -p   %e"
    3.18 -#echo > ${logFile}
    3.19 -
    3.20 -function timer()
    3.21 -{
    3.22 -    if [[ $# -eq 0 ]]; then
    3.23 -        echo $(date '+%s')
    3.24 -    else
    3.25 -        local  stime=$1
    3.26 -        etime=$(date '+%s')
    3.27 -
    3.28 -        if [[ -z "$stime" ]]; then stime=$etime; fi
    3.29 -
    3.30 -        dt=$((etime - stime))
    3.31 -        ds=$((dt % 60))
    3.32 -        dm=$(((dt / 60) % 60))
    3.33 -        dh=$((dt / 3600))
    3.34 -        printf '%d:%02d:%02d' $dh $dm $ds
    3.35 -    fi
    3.36 -}
    3.37 -
    3.38 -#prin
    3.39 -#tmr1=$(timer)
    3.40 -#meta
    3.41 -#tmr2=$(timer)
    3.42 -#ektypwsi
    3.43 -#printf 'LALA %s %s\n' $((tmr2-tmr1)) $(timer $tmr1)
    3.44 -
    3.45 -
    3.46 -deleteSeaHotspots=`cat ${LOC}/DeleteInSea.sparql` # | sed 's/\"/\\\"/g'`
    3.47 -refinePartialSeaHotspots=`cat ${LOC}/Refine.sparql` # | sed 's/\"/\\\"/g'`
    3.48 -refineTimePersistence=`cat ${LOC}/TimePersistence.sparql` # | sed 's/\"/\\\"/g'`
    3.49 -
    3.50 -# Initialize
    3.51 -sudo service postgresql restart
    3.52 -echo "Dropping endpoint database";
    3.53 -sudo -u postgres dropdb ${DB}
    3.54 -echo "Creating endpoint database"
    3.55 -sudo -u postgres createdb ${DB} -T template_postgis
    3.56 -echo "restarting tomcat"
    3.57 -sudo service tomcat7 restart
    3.58 -
    3.59 -echo "initializing database"
    3.60 -echo "S D R TP" >>stderr.txt
    3.61 -
    3.62 - ../endpoint store ${ENDPOINT} N-Triples -u ${GRIDURL}
    3.63 -
    3.64 -#./scripts/endpoint query ${ENDPOINT} "SELECT (COUNT(*) AS ?C) WHERE {?s ?p ?o}" 
    3.65 -#sudo -u postgres psql -d endpoint -c 'CREATE INDEX datetime_values_idx_value ON datetime_values USING btree(value)';
    3.66 -#sudo -u postgres psql -d endpoint -c 'VACUUM ANALYZE;';
    3.67 -
    3.68 -
    3.69 -#echo "Continue?"
    3.70 -#read a
    3.71 -
    3.72 -
    3.73 -
    3.74 -
    3.75 -for h in `seq 0 23 `; do
    3.76 -    for m in `seq 0 15 45`; do
    3.77 -            time=`printf "%02d%02d\n" $h $m`
    3.78 -            time2=`printf "%02d:%02d\n" $h $m`
    3.79 -            file=${dataDir}${name}_${time}$suffix
    3.80 -#            file=${dataUrl}${name}_${time}$suffix
    3.81 -    
    3.82 -            # store file
    3.83 -            echo -n "storing " $file; echo; echo; 
    3.84 -	  # echo "Hotspot : " $h:$m >> stderr.txt
    3.85 -#            ${countTime} ./strabon -db endpoint store $file      
    3.86 -
    3.87 - tmr1=$(timer)
    3.88 -            ../endpoint store ${ENDPOINT} N-Triples -u ${file} 
    3.89 - tmr2=$(timer)
    3.90 -printf '%s ' $((tmr2-tmr1)) >>stderr.txt
    3.91 -           
    3.92 -	   # sudo -u postgres psql -d endpoint -c 'VACUUM ANALYZE;';
    3.93 -
    3.94 -            echo;echo;echo;echo "File ${file} stored!" >> ${logFile}
    3.95 -#            echo "Continue?"
    3.96 -#            read a
    3.97 -         
    3.98 -            # deleteSeaHotspots
    3.99 -            echo -n "Going to deleteSeaHotspots 2007-08-25T${time2}:00 " ;echo; echo; echo;
   3.100 -            query=`echo "${deleteSeaHotspots}" | sed "s/TIMESTAMP/2007-08-25T${time2}:00/g" | \
   3.101 -                sed "s/PROCESSING_CHAIN/DynamicThresholds/g" | \
   3.102 -                sed "s/SENSOR/MSG2/g"`
   3.103 -#            ${countTime} ./strabon -db endpoint update "${query}"
   3.104 -
   3.105 -tmr1=$(timer)           
   3.106 -  ../endpoint update ${ENDPOINT} "${query}"  
   3.107 -            
   3.108 -tmr2=$(timer)
   3.109 -printf '%s ' $((tmr2-tmr1)) >>stderr.txt
   3.110 -
   3.111 -            echo;echo;echo;echo "File ${file} deleteSeaHotspots done!"
   3.112 -#            echo "Continue?"
   3.113 -#            read a
   3.114 -            
   3.115 -            # refinePartialSeaHotspots
   3.116 -            echo -n "refinePartialSeaHotspots 2007-08-25T${time2}:00 "  ; echo; echo ; echo;
   3.117 -            query=`echo "${refinePartialSeaHotspots}" | sed "s/TIMESTAMP/2007-08-25T${time2}:00/g" | \
   3.118 -                sed "s/PROCESSING_CHAIN/DynamicThresholds/g" | \
   3.119 -                sed "s/SENSOR/MSG2/g"`
   3.120 -#            ${countTime} ./strabon -db endpoint update "${query}"
   3.121 -tmr1=$(timer)
   3.122 -              ../endpoint update ${ENDPOINT} "${query}"
   3.123 -            
   3.124 -tmr2=$(timer)
   3.125 -printf '%s ' $((tmr2-tmr1)) >>stderr.txt
   3.126 -
   3.127 -            echo "File ${file} refinePartialSeaHotspots done!"
   3.128 -#            echo "Continue?"
   3.129 -#            read a
   3.130 -
   3.131 -            # refineTimePersistence
   3.132 -            echo -n "Going to refineTimePersistence 2007-08-25T${time2}:00 ";echo;echo;echo; 
   3.133 -            min_acquisition_time=`date --date="2007-08-25 ${time2}:00 EEST -30 minutes" +%Y-%m-%dT%H:%m:00`
   3.134 -            query=`echo "${refineTimePersistence}" | sed "s/TIMESTAMP/2007-08-25T${time2}:00/g" | \
   3.135 -                sed "s/PROCESSING_CHAIN/DynamicThresholds/g" | \
   3.136 -                sed "s/SENSOR/MSG2/g" | \
   3.137 -                sed "s/ACQUISITIONS_IN_HALF_AN_HOUR/3.0/g" | \
   3.138 -                sed "s/MIN_ACQUISITION_TIME/${min_acquisition_time}/g"`
   3.139 -
   3.140 -#            echo "Query:"
   3.141 -#            echo "${query}"
   3.142 -#            echo "Continue?"
   3.143 -#            read a
   3.144 -#            ${countTime} ./strabon -db endpoint update "${query}"
   3.145 -#            ${countTime} ../endpoint update ${ENDPOINT} "${query}"
   3.146 -
   3.147 - sudo -u postgres psql -d ${DB} -c 'VACUUM ANALYZE;';
   3.148 -
   3.149 -
   3.150 -tmr1=$(timer)
   3.151 -              ../endpoint update ${ENDPOINT} "${query}"
   3.152 - tmr2=$(timer)
   3.153 -printf '%s \n' $((tmr2-tmr1)) >>stderr.txt
   3.154 -           
   3.155 -            echo;echo;echo;echo "File ${file} timePersistence done!"
   3.156 -#            echo "Continue?"
   3.157 -#            read a
   3.158 -    done
   3.159 -done
   3.160 -
   3.161 -
   3.162 -#for f in `ls /home/konstantina/noa-teleios/out_triples/HMSG2_IR_039_s7_070825_*.hotspots.n3`
   3.163 -#do
   3.164 -
   3.165 -#    echo "Store $f"
   3.166 -#	${countTime} ./scripts/strabon -db endpoint store $f
   3.167 -#	
   3.168 -#	
   3.169 -#done
   3.170 -