src/Sql.scala
1package xstream.examples.piflow
2
3import java.sql.{Connection,DriverManager}
4import com.fasterxml.jackson.databind.JsonNode
5import org.apache.arrow.vector.types.pojo.{ArrowType,Schema}
6import org.apache.arrow.vector.types.FloatingPointPrecision
7import xstream.execution.{StreamDescriptor,InferenceResult}
8
9private[piflow] object Sql {
10 import Support._
11 def quote(s:String):String="\""+s.replace("\"","\"\"")+"\""
12 def database(s:Schema,view:String)(use:Connection=>Unit):Unit={
13 unique(s);fields(s).foreach(scalar);require(view.nonEmpty,"viewName must be nonempty")
14 Class.forName("org.sqlite.JDBC")
15 val c=DriverManager.getConnection("jdbc:sqlite::memory:")
16 try {val st=c.createStatement();try {st.execute("PRAGMA temp_store=MEMORY");st.execute("CREATE TABLE "+quote(view)+names(s).map(quote).mkString("(",",",")"))}finally st.close();use(c)}finally c.close()
17 }
18 def insert(c:Connection,s:Schema,view:String,rows:Iterator[Vector[AnyRef]]):Unit={
19 val stmt=c.prepareStatement("INSERT INTO "+quote(view)+" VALUES "+names(s).map(_=>"?").mkString("(",",",")"))
20 c.setAutoCommit(false)
21 try {rows.foreach{row=>check();row.zipWithIndex.foreach{case(x,i)=>stmt.setObject(i+1,x match {case b:java.lang.Boolean=>if(b)Integer.valueOf(1)else Integer.valueOf(0);case v:java.time.temporal.TemporalAccessor=>v.toString;case d:java.math.BigDecimal=>d.toString;case v=>v})};stmt.executeUpdate()};c.commit()}finally stmt.close()
22 }
23 def query(c:Connection,sql:String)(consume:(Vector[String],Iterator[Vector[AnyRef]])=>Unit):Unit={
24 require(sql.trim.matches("(?is)^(select|with)\\b.*"),"only read-only SELECT/WITH queries are allowed")
25 val lock=c.createStatement();try lock.execute("PRAGMA query_only=ON")finally lock.close()
26 var ticks=0
27 org.sqlite.ProgressHandler.setHandler(c,1000,new org.sqlite.ProgressHandler {
28 def progress():Int={ticks+=1;if(Thread.currentThread.isInterrupted || ticks>10000)1 else 0}
29 })
30 val stmt=c.createStatement()
31 try {
32 val rs=stmt.executeQuery(sql)
33 try {
34 val meta=rs.getMetaData;val ns=(1 to meta.getColumnCount).map(meta.getColumnLabel).toVector
35 require(ns.distinct.size==ns.size,"query must return unique column names")
36 val it=new Iterator[Vector[AnyRef]] {
37 private var ready=false;private var ended=false
38 def hasNext:Boolean={check();if(!ready && !ended){ready=rs.next();ended= !ready};ready}
39 def next():Vector[AnyRef]={if(!hasNext)throw new NoSuchElementException;ready=false;(1 to ns.size).map(rs.getObject).toVector}
40 };consume(ns,it)
41 }finally rs.close()
42 }finally {stmt.close();org.sqlite.ProgressHandler.clearHandler(c)}
43 }
44 def resultSchema(ns:Vector[String],rows:Vector[Vector[AnyRef]],source:Schema):Schema=schema(ns.zipWithIndex.map{case(n,i)=>
45 val values=rows.iterator.map(_(i)).filter(_!=null).toVector
46 val t:ArrowType=if(values.isEmpty)Option(source.findField(n)).map(_.getType).getOrElse(ArrowType.Null.INSTANCE)
47 else if(values.forall(x=>x.isInstanceOf[java.lang.Integer]||x.isInstanceOf[java.lang.Long]))new ArrowType.Int(64,true)
48 else if(values.forall(_.isInstanceOf[Number]))new ArrowType.FloatingPoint(FloatingPointPrecision.DOUBLE)
49 else if(values.forall(_.isInstanceOf[Array[Byte]]))ArrowType.Binary.INSTANCE
50 else {require(values.forall(_.isInstanceOf[String]),"SQL output has mixed incompatible types");ArrowType.Utf8.INSTANCE}
51 field(n,t)
52 })
53}
54class FilterOperator extends StreamOperator {
55 import Support._
56 protected def validate(p:JsonNode):Unit={params(p,Set("condition"));require(text(p,"condition").trim.nonEmpty,"condition must be nonempty")}
57 protected def infer(inputs:Map[String,StreamDescriptor],p:JsonNode):InferenceResult={inputs("source").schema.foreach(s=>fields(s).foreach(scalar));descriptor(inputs("source").schema)}
58 private[piflow] def execute(inputs:Map[String,Source],out:Sink,p:JsonNode):Unit={
59 val in=inputs("source");val s=in.schema;val reserved="__dacp_row_index";require(!names(s).contains(reserved),"reserved SQL column: "+reserved)
60 val indexed=schema(fields(s):+field(reserved,new ArrowType.Int(64,true)));val send=out.records(s)
61 in.records(root=>Sql.database(indexed,"source"){c=>
62 Sql.insert(c,indexed,"source",rows(root).iterator.zipWithIndex.map{case(r,i)=>r:+Integer.valueOf(i)})
63 Sql.query(c,"SELECT "+Sql.quote(reserved)+" FROM source WHERE ("+text(p,"condition")+")"){(_,result)=>projected(root,s,result.map(_.head.asInstanceOf[Number].intValue).toVector,send)}
64 })
65 }
66}
67object Filter extends FilterOperator
68class ExecuteSqlOperator extends StreamOperator {
69 import Support._
70 protected def validate(p:JsonNode):Unit={params(p,Set("viewName","sql"));require(text(p,"viewName").nonEmpty,"viewName must be nonempty");text(p,"sql")}
71 protected def infer(inputs:Map[String,StreamDescriptor],p:JsonNode):InferenceResult={
72 // SQLite expression result types can depend on data. Do not declare guessed types.
73 inputs("source").schema.foreach(s=>fields(s).foreach(scalar));descriptor(None)
74 }
75 private[piflow] def execute(inputs:Map[String,Source],out:Sink,p:JsonNode):Unit={
76 val in=inputs("source");val s=in.schema
77 Sql.database(s,text(p,"viewName")){c=>
78 var inputSize=0L
79 in.records(root=>{val batch=rows(root);batch.foreach{row=>inputSize+=estimated(row);require(inputSize<=MaxBytes,"SQL input exceeds 128 MiB")};Sql.insert(c,s,text(p,"viewName"),batch.iterator)})
80 Sql.query(c,text(p,"sql")){(ns,result)=>
81 var size=0L
82 val data=result.map{row=>size+=estimated(row);require(size<=MaxBytes,"SQL output exceeds 128 MiB");row}.toVector
83 val target=Sql.resultSchema(ns,data,s);emitRows(target,data.iterator,out.records(target))
84 }
85 }
86 }
87}
88object ExecuteSql extends ExecuteSqlOperator