Source code

Download source archive ↓

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