Skip to content

Instantly share code, notes, and snippets.

@KatelynHaworth
Last active August 29, 2015 14:24
Show Gist options
  • Save KatelynHaworth/6fda3836227b8ecd2206 to your computer and use it in GitHub Desktop.
Save KatelynHaworth/6fda3836227b8ecd2206 to your computer and use it in GitHub Desktop.
A simple yet powerful helper class to provide the most simplistic way to create & drop tables and insert & select data based on scala case classes in Crate DB (https://crate.io) | Requires the Crate Java Client (https://bintray.com/crate/crate/crate-client/view)
name := "CreateObjectTest"
scalaVersion := "2.11.6" // Should be able to support lower versions
resolvers += "Crate bintray" at "http://dl.bintray.com/crate/crate"
libraryDependencies ++= Seq(
"io.crate" % "crate-client" % "0.49.3"
)
import java.io.IOException
import io.crate.client.CrateClient
/**
* CrateObject supplies methods
* for objects to store them in
* a Crate Database
*
* @author Liam Haworth
* @version 1.0
*/
abstract class CrateObject[T] {
/**
* Generates and executes a SQL statement designed to
* build a table for the object in the selected schema
*
* @param schema The schema to create the table under
* @throws java.io.IOException Thrown when the method fails to successfully execute the sql statement
* @return Boolean Returns true if a new table was created
*/
@throws(classOf[IOException])
def create(schema: String)(implicit crateClient: CrateClient): Boolean = {
if(crateClient == null)
throw new IOException("Requires implicit object crateClient to be initialized")
if(exists(schema)(crateClient))
return false
val tableName = this.getClass.getSimpleName.toLowerCase
var tableColumns = Map.empty[String, String]
this.getClass.getDeclaredFields.foreach { f =>
f.getType match {
case t if t == classOf[String] => tableColumns += f.getName -> "string"
case t if t == classOf[Int] => tableColumns += f.getName -> "integer"
case t if t == classOf[Integer] => tableColumns += f.getName -> "integer"
case t if t == classOf[Boolean] => tableColumns += f.getName -> "boolean"
case t if t == classOf[Short] => tableColumns += f.getName -> "short"
case t if t == classOf[Double] => tableColumns += f.getName -> "double"
}
}
val sqlStatement = s"create table $schema.$tableName (${tableColumns.mkString(", ").replace(" ->", "")})"
try {
crateClient.sql(sqlStatement)
Thread.sleep(1000) // Have to wait for Crate to accept the request and build the table
exists(schema)(crateClient)
}
catch {
case ex: Exception =>
throw new IOException(s"Failed to make table for $schema.$tableName", ex)
}
}
/**
* Checks for the existence of a table for
* this object in the selected schema
*
* @param schema Schema to check under
* @param crateClient Crate client for connection to database
* @return Boolean Returns true if the table exists, false if it doesn't
*/
def exists(schema: String)(implicit crateClient: CrateClient): Boolean = {
val sqlResult = crateClient.sql(s"select * from information_schema.tables where table_name='${this.getClass.getSimpleName.toLowerCase}' and schema_name='$schema'").get()
sqlResult.rowCount() == 1
}
/**
* Drops the table related to this object if it exists
*
* @param schema The schema the table should exist under
* @param crateClient Crate client for connection to database
* @throws java.io.IOException Thrown when the table couldn't be dropped due to an error
* @return Boolean Returns true if the table was dropped
*/
@throws(classOf[IOException])
def drop(schema: String)(implicit crateClient: CrateClient): Boolean = {
if(crateClient == null)
throw new IOException("Requires implicit object crateClient to be initialized")
if(!exists(schema)(crateClient))
return false
try {
crateClient.sql(s"drop table $schema.${this.getClass.getSimpleName.toLowerCase}")
true
}
catch {
case ex: Exception =>
throw new IOException(s"Failed to drop table $schema.${this.getClass.getSimpleName.toLowerCase}", ex)
}
}
/**
* Insets a object into its table in the selected schema
*
* @param schema The schema the object should be stored under
* @param crateClient Crate client for connection to database
* @throws java.io.IOException Thrown when the object couldn't be stored in the database
*/
@throws(classOf[IOException])
def insert(schema: String)(implicit crateClient: CrateClient): Unit = {
if(crateClient == null)
throw new IOException("Requires implicit object crateClient to be initialized")
if(!exists(schema)(crateClient))
throw new IOException(s"Table for $schema.${this.getClass.getSimpleName.toLowerCase} does not exist yet!")
var insertData = Map.empty[String, String]
this.getClass.getDeclaredFields.foreach { f =>
f.setAccessible(true)
insertData += f.getName -> s"'${f.get(this).toString}'"
}
val sqlStatement = s"insert into $schema.${this.getClass.getSimpleName.toLowerCase}(${insertData.keys.mkString(", ")}) values(${insertData.values.mkString(", ")})"
try {
crateClient.sql(sqlStatement)
}
catch {
case ex: Exception =>
throw new IOException(s"Failed to insert object into $schema.${this.getClass.getSimpleName.toLowerCase}", ex)
}
}
/**
* Run a select on the objects table a makes a list of the object from the results
*
* @param schema The schema to find the table under
* @param conditional A SQL conditional to use when selecting
* @param crateClient Crate client for the connection to database
* @throws java.io.IOException Thrown when a failure occurred when selecting a list of objects from the database
* @return
*/
@throws(classOf[IOException])
def select(schema: String, conditional: String = "limit 100")(implicit crateClient: CrateClient): List[T] = {
if(crateClient == null)
throw new IOException("Requires implicit object crateClient to be initialized")
if(!exists(schema)(crateClient))
throw new IOException(s"Table for $schema.${this.getClass.getSimpleName.toLowerCase} does not exist yet!")
try {
val sqlResult = crateClient.sql(s"select * from $schema.${this.getClass.getSimpleName.toLowerCase} $conditional").get()
var selectResult = List.empty[T]
for(args <- sqlResult.rows())
selectResult = selectResult :+ this.getClass.getConstructors()(0).newInstance(args: _*).asInstanceOf[T]
selectResult
}
catch {
case ex: Exception =>
throw new IOException(s"Failed to select objects from $schema.${this.getClass.getSimpleName.toLowerCase}", ex)
}
}
}
case class TestingObject(a: String, b: Int, c: Boolean) extends CrateObject
object Test extends App {
println("Making crate client")
implicit val crateClient = new CrateClient("localhost:4300") //TODO: Change this to your own crate server
println("\nMaking testing object")
val testObject = TestingObject("bla", 333, true)
println("\nCalling #crate(test)")
println(s"Result: ${testObject.create("test")}")
println("\nCalling #exits(test)")
println(s"Result: ${testObject.exists("test")}")
println("\nCalling #insert(test)")
println(s"Result: ${testObject.insert("test")}")
println("\nCalling #select(test)")
println(s"Result: ${testObject.select("test")}")
println("\nCalling #drop(test)")
println(s"Result: ${testObject.drop("test")}")
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment