package org.hivedb.meta.directory;
import org.hivedb.meta.PartitionDimension;
import org.hivedb.meta.Resource;
import org.hivedb.meta.ResourceIndex;
import org.hivedb.meta.SecondaryIndex;
import org.hivedb.util.database.Schemas;
/***
* Methods for generating SQL strings used to read and write from the HiveDB directory.
* @author bcrawford
*
*/
public class IndexSqlFormatter {
/**
*
* Primary index methods
*
*/
public String insertPrimaryIndexKey(PartitionDimension partitionDimension) {
return String.format(
"insert into %s (id, node, status) values(?, ?, 0)",
Schemas.getPrimaryIndexTableName(partitionDimension));
}
public String selectKeySemaphoreOfPrimaryIndexKey(PartitionDimension partitionDimension) {
return String.format("select id,node,status from %s where id = ?", Schemas.getPrimaryIndexTableName(partitionDimension));
}
public String selectResourceIdsOfPrimaryIndexKey(ResourceIndex resourceIndex) {
return String.format("select id from %s where pkey = ?", Schemas.getResourceIndexTableName(resourceIndex.getResource()));
}
public String checkExistenceOfPrimaryKey(PartitionDimension partitionDimension) {
return String.format("select id from %s where id = ?", Schemas.getPrimaryIndexTableName(partitionDimension));
}
public String updateReadOnlyOfPrimaryIndexKey(PartitionDimension partitionDimension) {
return String.format("update %s set status = ? where id = ?", Schemas.getPrimaryIndexTableName(partitionDimension));
}
public String deletePrimaryIndexKey(PartitionDimension partitionDimension) {
return String.format("delete from %s where id = ?", Schemas.getPrimaryIndexTableName(partitionDimension));
}
/**
*
* Secondary index methods
*
*/
public String insertSecondaryIndexKey(SecondaryIndex secondaryIndex) {
return String.format("insert into %s (id, pkey) values(?, ?)", Schemas.getSecondaryIndexTableName(secondaryIndex));
}
public String selectSecondaryIndexKeysOfPrimaryKey(SecondaryIndex secondaryIndex) {
if (ResourceIndex.class.isInstance(secondaryIndex))
// index of a resource
return selectSecondaryIndexKeyOfResourceId(secondaryIndex);
else if (secondaryIndex.getResource().isPartitioningResource())
// secondary index of a resource that is also the partition dimension
return String.format(
"select s.id from %s s where s.pkey = ?",
Schemas.getSecondaryIndexTableName(secondaryIndex));
else
// secondary index of a resource with a different partition dimension
return String.format(
"select s.id from %s p join %s r on r.pkey = p.id join %s s on s.pkey = r.id where p.id = ?",
Schemas.getPrimaryIndexTableName(secondaryIndex.getResource().getPartitionDimension()),
Schemas.getResourceIndexTableName(secondaryIndex.getResource()),
Schemas.getSecondaryIndexTableName(secondaryIndex));
}
public String selectKeySemaphoresOfSecondaryIndexKey(SecondaryIndex secondaryIndex) {
if (ResourceIndex.class.isInstance(secondaryIndex))
// index of a resource
return String.format(
"select distinct r.id as id,p.node,p.status from %s p join %s r on r.pkey = p.id where r.id = ?",
Schemas.getPrimaryIndexTableName(secondaryIndex.getResource().getPartitionDimension()),
Schemas.getSecondaryIndexTableName(secondaryIndex.getResource().getIdIndex()));
else if (secondaryIndex.getResource().isPartitioningResource())
// secondary index of a resource that is also the partition dimension
return String.format(
"select distinct s.id as id,p.node,p.status from %s p join %s s on s.pkey = p.id where s.id = ?",
Schemas.getPrimaryIndexTableName(secondaryIndex.getResource().getPartitionDimension()),
Schemas.getSecondaryIndexTableName(secondaryIndex));
else
// secondary index of a resource that is not also the partition dimension
return String.format(
"select distinct s.id as id,p.node,p.status from %s p join %s r on r.pkey = p.id join %s s on s.pkey = r.id where s.id = ?",
Schemas.getPrimaryIndexTableName(secondaryIndex.getResource().getPartitionDimension()),
Schemas.getResourceIndexTableName(secondaryIndex.getResource()),
Schemas.getSecondaryIndexTableName(secondaryIndex));
}
public String selectPrimaryIndexKeysOfSecondaryIndexKey( SecondaryIndex secondaryIndex) {
if (ResourceIndex.class.isInstance(secondaryIndex))
// index of a resource
return String.format(
"select p.id from %s p join %s r on r.pkey = p.id where r.id = ?",
Schemas.getPrimaryIndexTableName(secondaryIndex.getResource().getPartitionDimension()),
Schemas.getResourceIndexTableName(secondaryIndex.getResource()));
else if (secondaryIndex.getResource().isPartitioningResource())
// secondary index of a resource that is also the partition dimension
return String.format(
"select p.id from %s p join %s s on s.pkey = p.id where s.id = ?",
Schemas.getPrimaryIndexTableName(secondaryIndex.getResource().getPartitionDimension()),
Schemas.getSecondaryIndexTableName(secondaryIndex));
else
// secondary index of a resource that is not also the partition dimension
return String.format(
"select p.id from %s p join %s r on r.pkey = p.id join %s s on s.pkey = r.id where s.id = ?",
Schemas.getPrimaryIndexTableName(secondaryIndex.getResource().getPartitionDimension()),
Schemas.getResourceIndexTableName(secondaryIndex.getResource()),
Schemas.getSecondaryIndexTableName(secondaryIndex));
}
public String checkExistenceOfSecondaryIndexSql( SecondaryIndex secondaryIndex) {
return String.format("select id from %s where id = ? and pkey = ? limit 1", Schemas.getSecondaryIndexTableName(secondaryIndex));
}
public String checkExistenceOfResourceIndexSql( ResourceIndex secondaryIndex) {
return String.format("select id from %s where id = ? limit 1", Schemas.getResourceIndexTableName(secondaryIndex.getResource()));
}
public String updateSecondaryIndexKey( SecondaryIndex secondaryIndex) {
return String.format("update %s set pkey = ? where id = ? and pkey = ?", Schemas.getSecondaryIndexTableName(secondaryIndex));
}
public String deleteAllSecondaryIndexKeysForResourceId(SecondaryIndex secondaryIndex) {
return String.format("delete from %s where pkey = ?", Schemas.getSecondaryIndexTableName(secondaryIndex));
}
public String deleteSingleSecondaryIndexKey(SecondaryIndex secondaryIndex) {
return String.format("delete from %s where id =? and pkey = ?", Schemas.getSecondaryIndexTableName(secondaryIndex));
}
/***
* Resource methods
*/
public String insertResourceId(Resource resource) {
return String.format("insert into %s (id, pkey) values(?, ?)", Schemas.getResourceIndexTableName(resource));
}
public String selectResourceIdsOfSecondaryIndexKey(SecondaryIndex secondaryIndex) {
final Resource resource = secondaryIndex.getResource();
if (resource.isPartitioningResource())
return selectPrimaryIndexKeysOfSecondaryIndexKey(secondaryIndex);
return String.format(
"select r.id from %s r join %s s on s.pkey = r.id where s.id = ?",
Schemas.getResourceIndexTableName(resource),
Schemas.getSecondaryIndexTableName(secondaryIndex));
}
public String selectKeySemaphoresOfResourceId(Resource resource) {
return String.format(
"select r.id as id,p.node,p.status from %s p join %s r on r.pkey = p.id where r.id = ?",
Schemas.getPrimaryIndexTableName(resource.getPartitionDimension()),
Schemas.getResourceIndexTableName(resource));
}
public String selectPrimaryIndexKeysOfResourceId(Resource resource) {
return String.format(
"select p.id from %s p join %s r on r.pkey = p.id where r.id = ?",
Schemas.getPrimaryIndexTableName(resource.getPartitionDimension()),
Schemas.getResourceIndexTableName(resource));
}
public String selectSecondaryIndexKeyOfResourceId(SecondaryIndex secondaryIndex) {
return String.format(
"select s.id from %s s where s.pkey = ?",
Schemas.getSecondaryIndexTableName(secondaryIndex),
Schemas.getResourceIndexTableName(secondaryIndex.getResource()));
}
public String updateResourceId( Resource resource) {
return String.format("update %s set pkey = ? where id = ?", Schemas.getResourceIndexTableName(resource));
}
public String deleteResourceId(Resource resource) {
return String.format("delete from %s where id = ?", Schemas.getResourceIndexTableName(resource));
}
public String selectForUpdateLock(String table, String column) {
return String.format("select * from %s where %s = ? for update", table, column);
}
public String selectCompositeKeyForUpdateLock(String table, String column1, String column2) {
return String.format("select * from %s where %s = ? and %s = ? for update", table, column1, column2);
}
}