//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** @author  Vinay Bingi, John Miller
 *  @version 1.6
 *  @date    Mon Jun 25 16:56:30 EDT 2018
 *  @see     LICENSE (MIT style license file).
 */

package scalation
package columnar_db

import scala.collection.mutable.ArrayBuffer
import scala.reflect.ClassTag

import scalation.linalgebra._
import scalation.linalgebra.MatrixKind._
//import scalation.math.{Complex, Rational, Real}
//import scalation.math.StrO.StrNum
import scalation.util.{banner, ReArray}

import TableObj._
import columnar_db._

//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** The `RelationSQL` class provides an SQL-like API to data stored internally
 *  in a `Relation` object.
 *  @param name     the name of the relation
 *  @param colName  the names of columns
 *  @param col      the Scala Vector of columns making up the columnar relation
 *  @param key      the column number for the primary key (< 0 => no primary key)
 *  @param domain   an optional string indicating domains for columns (e.g., 'SD' = 'StrNum', 'Double')
 *  @param fKeys    an optional sequence of foreign keys - Seq (column name, ref table name, ref column position)
 */
class RelationSQL (name: String, colName: Seq [String], col: Vector [Vec] = null,
                   key: Int = 0, domain: String = null, fKeys: Seq [(String, String, Int)] = null)
      extends Tabular with Serializable
{
    /** the debug flag
     */
    private val DEBUG = true

    /** the internal representation - class delegates work to `Relation` operations
     */
    private val r = new Relation (name, colName, col, key, domain, fKeys)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Return the internal representation.
     */
    def repr: Relation = r

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Construct a new `RelationSQL` object from an existing relation 'r'.
     *  @param r  the existing relation
     */
    def this (r: Relation) = this (r.name, r.colName, r.col, r.key, r.domain, r.fKeys)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Select the attributes to return in the answer to the query.
     *  @param cName  the attribute names
     */
    def select (cName: String*): RelationSQL =
    {
        new RelationSQL (r.project (cName :_*))
    } // select

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Aggregate/project on the given columns (an extended projection operator that
     *  applies aggregate operators to aggregation columns and regular projection
     *  to projection columns).
     *  @see en.wikipedia.org/wiki/Relational_algebrap
     *  @param aggCol  the columns to aggregate on: (aggregate function, new column name, old column name)*
     *  @param cName   the other columns to project on
     */
    def eselect (aggCol: AggColumn*)(cName: String*): RelationSQL =
    {
        new RelationSQL (r.eproject (aggCol: _*)(cName: _*))
    } // eselect

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Join 'this' relation and 'r2' by performing a "natural-join".
     *  @param r2  the other relation
     */
    def join (r2: RelationSQL): RelationSQL =
    {
        new RelationSQL ((r join r2.repr).asInstanceOf [Relation])
    } // join

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Join 'this' relation and 'r2' by performing an "equi-join".  Rows from both
     *  relations are compared requiring 'cName1' values to equal 'cName2' values.
     *  Disambiguate column names by appending "2" to the end of any duplicate column name.
     *  @param cName1  the join column name of this relation (e.g., the Foreign Key)
     *  @param cName2  the join column name of relation r2 (e.g., the Primary Key)
     *  @param r2      the rhs relation in the join operation
     */
    def join (cName1: String, cName2: String, r2: RelationSQL): RelationSQL =
    {
        new RelationSQL (r.join (Seq (cName1), Seq (cName2), r2.repr))
    } // join

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Join 'this' relation and 'r2' by performing an "equi-join".  Rows from both
     *  relations are compared requiring 'cName1' values to equal 'cName2' values.
     *  Disambiguate column names by appending "2" to the end of any duplicate column name.
     *  @param cName1  the join column names of this relation (e.g., the Foreign Key)
     *  @param cName2  the join column names of relation r2 (e.g., the Primary Key)
     *  @param r2      the rhs relation in the join operation
     */
    def join (cName1: Seq [String], cName2: Seq [String], r2: RelationSQL): RelationSQL =
    {
        new RelationSQL (r.join (cName1, cName2, r2.repr))
    } // join

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** The where function filters on a predicate, returning the relation satisfying
     *  the predicate (column compare with constant)
     *  @param  cName  the column name used in predicate
     *  @param  p  the predicate (T => Boolean)
     *  @tparam T  the predicate type
     */
    def where [T: ClassTag] (cName: String, p: T => Boolean): RelationSQL =
    {
        new RelationSQL (r.select (cName, p))
    } // where

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** The where function filters on (multiple) predicates (logic is and),
     *  returning the relation satisfying the predicates (column compare with constant)
     *  @param  p  tuple(1): column name, tuple(2): predicate (T => Boolean)
     *  @tparam T  the predicate type
     */
    def where2 [T: ClassTag] (p: Predicate [T]*): RelationSQL =
    {
        var pos = ArrayBuffer [Int] ()
        for (i <- p.indices) {
            val p_i = p(i)
            val pos1 = Vec.filterPos (col(r.colMap(p_i._1)), p_i._2)
            if (DEBUG) println (s"where: p_$i = ${p_i}, pos1 = $pos1")
            if (i > 0) pos = pos intersect pos1 else pos ++= pos1
        } // for
        new RelationSQL (r.selectAt (pos))
    } // where2

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Group 'this' relation by the specified column names, returning 'this' relation.
     *  @param cName  the group column names
     */
    def groupBy (cName: String*): RelationSQL =
    {
        new RelationSQL (r.groupBy (cName :_*))
    } // groupBy

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Order (ascending) the rows in the relation by the selected columns '_cName'.
     *  A stable sorting is used to allow sorting on multiple columns.
     *  @param cName  the column names that are to be sorted
     */
    def orderBy (cName: String*): RelationSQL =
    {
        new RelationSQL (r.orderBy (cName :_*))
    } // orderBy

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Order (descending) the rows in the relation by the selected columns '_cName'.
     *  A stable sorting is used to allow sorting on multiple columns.
     *  @param cName  the column names that are to be sorted
     */
    def reverseOrderBy (cName: String*): RelationSQL =
    {
        new RelationSQL (r.reverseOrderBy (cName :_*))
    } // reverseOrderBy

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Union 'this' relation and 'r2'.  Check that the two relations are compatible.
     *  If they are not, return the first 'this' relation.
     *  @param r2  the other relation
     */
    def union (r2: RelationSQL): RelationSQL =
    {
        new RelationSQL (r union r2.repr)
    } // union

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Intersect 'this' relation and 'r2'.  Check that the two relations are compatible.
     *  Use index to perform intersect operation.
     *  @param r2  the other relation
     */
    def intersect (r2: RelationSQL): RelationSQL =
    {
        new RelationSQL (r intersect r2.repr)
    } // intersect

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Intersect 'this' relation and 'r2'.  Check that the two relations are compatible.
     *  Do not use index to finish intersect operation.
     *  FIX: should merge 'intersect' and 'intersect2'.
     *  @param r2  the other relation
     */
    def intersect2 (r2: RelationSQL): RelationSQL =
    {
        new RelationSQL (r intersect2 r2.repr)
    } // intersect2

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Take the difference of 'this' relation and 'r2' ('this - r2').  Check that
     *  the two relations are compatible.
     *  @param r2  the other relation
     */
    def minus (r2: RelationSQL): RelationSQL =
    {
        new RelationSQL (r minus r2.repr)
    } // minus

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Take the difference of 'this' relation and 'r2' ('this - r2'). Indexed minus.
     *  Check that the two relations are compatible.
     *  @param r2  the other relation
     */
    def minus2 (r2: RelationSQL): RelationSQL =
    {
        new RelationSQL (r minus2 r2.repr)
    } // minus

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Stack two columns into one by projecting onto the two columns and taking
     *  their union.
     *  @param cName1  the first column name
     *  @param cName2  the second column name
     */
    def stack (cName1: String, cName2: String): RelationSQL = 
    {
        new RelationSQL (r.project (cName1) union r.project (cName2)) 
    } // stack

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Insert 'rows' to 'this' relation.
     *  @param rows  the rows to be added to the realtion  
     */
    def insert (rows: Row*)
    {
        for (row <- rows) r.add (row)
    } // insert

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Materialize 'this' relation by copying the inserted rows into the relation.
     *  It needs to be called by the end of the relation construction.
     *  FIX - currently wipes out existing rows.
     */
    def materialize ()
    {
        r.materialize ()
    } // materialize

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Determine whether any rows/tuples exist in 'this' relation.
     */
    def exists: Boolean = r.exists

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert 'this' relation into a matrix of doubles, e.g.,
     *  <p>
     *       in the regression equation: 'xb = y' create matrix 'xy'
     *  <p>
     *  @param colPos  the column positions to use for the matrix
     *  @param kind    the kind of matrix to create
     */
    def toMatriD (colPos: Seq [Int], kind: MatrixKind = DENSE): MatriD =
    {
        r.toMatriD (colPos, kind)
    } // toMatriD

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert 'this' relation into a matrix of doubles and a vector of doubles.
     *  <p>
     *       in the regression equation: 'xb = y' create matrix 'x' and vector 'y'
     *  <p>
     *  @param colPos   the column positions to use for the matrix
     *  @param colPosV  the column position to use for the vector
     *  @param kind     the kind of matrix to create
     */
    def toMatriDD (colPos: Seq [Int], colPosV: Int, kind: MatrixKind = DENSE): (MatriD, VectorD) =
    {
        r.toMatriDD (colPos, colPosV, kind)
    } // toMatriDD

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert 'this' relation into a matrix of doubles and a vector of integers.
     *  <p>
     *       in the regression equation: 'xb = y' create matrix 'x' and vector 'y'
     *  <p>
     *  @param colPos   the column positions to use for the matrix
     *  @param colPosV  the column position to use for the vector
     *  @param kind     the kind of matrix to create
     */
    def toMatriDI (colPos: Seq [Int], colPosV: Int, kind: MatrixKind = DENSE): (MatriD, VectorI) =
    {
        r.toMatriDI (colPos, colPosV, kind)
    } // toMatriDI

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert 'this' relation into a matrix of integers.
     *  <p>
     *       in the regression equation: 'xb = y' create matrix 'xy'
     *  <p>
     *  @param colPos  the column positions to use for the matrix
     *  @param kind    the kind of matrix to create
     */
    def toMatriI (colPos: Seq [Int], kind: MatrixKind = DENSE): MatriI =
    {
        r.toMatriI (colPos, kind)
    } // toMatriI

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert 'this' relation into a matrix of integers.  It will convert
     *  doubles and strings to integers.
     *  <p>
     *       in the regression equation: 'xb = y' create matrix 'xy'
     *  <p>
     *  @param colPos  the column positions to use for the matrix
     *  @param kind    the kind of matrix to create
     */
    def toMatriI2 (colPos: Seq [Int] = null, kind: MatrixKind = DENSE): MatriI =
    {
        r.toMatriI2 (colPos, kind)
    } // toMatriI2

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert 'this' relation into a matrix of integers and a vector of integers.
     *  <p>
     *       in the regression equation: 'xb = y' create matrix 'x' and vector 'y'
     *  <p>
     *  @param colPos   the column positions to use for the matrix
     *  @param colPosV  the column position to use for the vector
     *  @param kind     the kind of matrix to create
     */
    def toMatriII (colPos: Seq [Int], colPosV: Int, kind: MatrixKind = DENSE): (MatriI, VectorI) =
    {
        r.toMatriII (colPos, colPosV, kind)
    } // toMatriII

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colPos' column of 'this' relation into a vector of complex numbers.
     *  @param colPos  the column position to use for the vector
     */
    def toVectorC (colPos: Int = 0): VectorC = r.toVectorC (colPos)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colName' column of 'this' relation into a vector of complex numbers.
     *  @param colName  the column name to use for the vector
     */
    def toVectorC (colName: String): VectorC = r.toVectorC (colName)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colPos' column of 'this' relation into a vector of doubles.
     *  @param colPos  the column position to use for the vector
     */
    def toVectorD (colPos: Int = 0): VectorD = r.toVectorD (colPos)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colName' column of 'this' relation into a vector of doubles.
     *  @param colName  the column name to use for the vector
     */
    def toVectorD (colName: String): VectorD = r.toVectorD (colName)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colPos' column of 'this' relation into a vector of integers.
     *  @param colPos  the column position to use for the vector
     */
    def toVectorI (colPos: Int = 0): VectorI = r.toVectorI (colPos)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colName' column of 'this' relation into a vector of integers.
     *  @param colName  the column name to use for the vector
     */
    def toVectorI (colName: String): VectorI = r.toVectorI (colName)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colPos' column of 'this' relation into a vector of long integers.
     *  @param colPos  the column position to use for the vector
     */
    def toVectorL (colPos: Int = 0): VectorL = r.toVectorL (colPos)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colName' column of 'this' relation into a vector of long integers.
     *  @param colName  the column name to use for the vector
     */
    def toVectorL (colName: String): VectorL = r.toVectorL (colName)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colPos' column of 'this' relation into a vector of rational integers.
     *  @param colPos  the column position to use for the vector
     */
    def toVectorQ (colPos: Int = 0): VectorQ = r.toVectorQ (colPos)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colName' column of 'this' relation into a vector of rational numbers.
     *  @param colName  the column name to use for the vector
     */
    def toVectorQ (colName: String): VectorQ = r.toVectorQ (colName)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colPos' column of 'this' relation into a vector of real numbers.
     *  @param colPos  the column position to use for the vector
     */
    def toVectorR (colPos: Int = 0): VectorR = r.toVectorR (colPos)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colName' column of 'this' relation into a vector of real numbers.
     *  @param colName  the column name to use for the vector
     */
    def toVectorR (colName: String): VectorR = r.toVectorR (colName)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colPos' column of 'this' relation into a vector of string-num.
     *  @param colPos  the column position to use for the vector
     */
    def toVectorS (colPos: Int = 0): VectorS = r.toVectorS (colPos)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colName' column of 'this' relation into a vector of string-num.
     *  @param colName  the column name to use for the vector
     */
    def toVectorS (colName: String): VectorS = r.toVectorS (colName)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colPos' column of 'this' relation into a vector of time-num.
     *  @param colPos  the column position to use for the vector
     */
    def toVectorT (colPos: Int = 0): VectorT = r.toVectorT (colPos)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Convert the 'colName' column of 'this' relation into a vector of time-num.
     *  @param colName  the column name to use for the vector
     */
    def toVectorT (colName: String): VectorT = r.toVectorT (colName)

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Show 'this' relation row by row.
     *  @param limit  the limit on the number of rows to display
     */
    def show (limit: Int = Int.MaxValue) { r.show (limit) }

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Save 'this' relation in a file using serialization.
     */
    def save () { r.save () }

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Generate an index for 'this' relation on its primary key.
     *  @param reset  if reset is true, use old index to build new index; otherwise, create new index
     */
    def generateIndex (reset: Boolean = false) { r.generateIndex (reset) }

} // RelationSQL class


//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** The `RelationSQL` companion object provides factory methods for creating `RelationSQL`
 *  object.
 */
object RelationSQL
{
    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Create a SQL-relation object.
     *  @param name     the name of the relation
     *  @param colName  the names of columns
     *  @param col      the Scala Vector of columns making up the columnar relation
     *  @param key      the column number for the primary key (< 0 => no primary key)
     *  @param domain   an optional string indicating domains for columns (e.g., 'SD' = 'StrNum', 'Double')
     *  @param fKeys    an optional sequence of foreign keys - Seq (column name, ref table name, ref column position)
     */
    def apply (name: String, colName: Seq [String], col: Vector [Vec] = null,
               key: Int = 0, domain: String = null, fKeys: Seq [(String, String, Int)] = null): RelationSQL =
    {
        new RelationSQL (name, colName, col, key, domain, fKeys)
    } // apply

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Create a SQL-relation from a sequence of row/tuples.  These rows must be converted
     *  to columns.
     *  @param name     the name of the relation
     *  @param colName  the names of columns
     *  @param row      the sequence of rows to be converted to columns for the columnar relation
     *  @param key      the column number for the primary key (< 0 => no primary key)
     *  @param domain   an optional string indicating domains for columns (e.g., 'SD' = 'StrNum', 'Double')
     *  @param fKeys    an optional sequence of foreign keys - Seq (column name, ref table name, ref column position)
     */
    def apply (name: String, colName: Seq [String], row: Seq [Row],
               key: Int, domain: String, fKeys: Seq [(String, String, Int)]): RelationSQL =
    {
        val equivCol = Vector.fill [Vec] (colName.length)(null)
        val r2 = new Relation (name, colName, equivCol, key, domain, fKeys)
        for (tuple <- row) r2.add (tuple)
        new RelationSQL (r2.materialize ())
    } // apply

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Create a SQL-relation from a saved relation.
     *  @param relName  the name of the relation
     */
    def apply (relName: String): RelationSQL = new RelationSQL (Relation (relName))

    //::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    /** Create a SQL-relation from a saved relation.
     *  FIX - should handle other than 2 relations
     *  @param relNames  the names of the relations
     */
    def from (relNames: String*): (RelationSQL, RelationSQL) =
    {
        (Catalog.getRelation (relNames(0)).asInstanceOf [RelationSQL],
         Catalog.getRelation (relNames(1)).asInstanceOf [RelationSQL])
    } // from

} // RelationSQL object


//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** The `RelationSQLTest` object is used to t3est the `RelationSQL` class.
 *  > runMain scalation.columnar_db.RelationSQLTest
 */
object RelationSQLTest extends App
{
    val professor = RelationSQL ("professor",
        Seq ("pid", "name", "department", "title"),
        Seq (Vector [Any] (1, "jackson", "pharm", 4),
             Vector [Any] (2, "ken", "cs", 2),
             Vector [Any] (3, "pan", "pharm", 0),
             Vector [Any] (4, "yang", "gis", 3),
             Vector [Any] (5, "zhang", "cs", 0),
             Vector [Any] (6, "Yu", "cs", 0)),
        -1, "ISSI", Seq ((null, null, -1)))

    val professor2 = RelationSQL ("professor2",
        Seq ("pid", "name", "department", "title"),
        Seq (Vector [Any] (7, "LiLy", "gis", 5),
             Vector [Any] (8, "Marry", "gis", 5),
             Vector [Any] (6, "Yu", "cs", 0),
             Vector [Any] (0, "Kate", "cs", 5)),
        0, "ISSI", Seq ((null, null, -1)))

    banner ("professor")
    professor.show ()

    banner ("professor2")
    professor2.show ()

    banner ("Example SQL-like Queries")

    professor.where [Int] ("title", _ == 4)
        .select ("pid", "name", "department").show ()

    (professor minus professor2).show ()

    // FIX - fails when Vector [Any] (6, "Yu", "cs", 0) is in professor2
    (professor join professor2)
        .where [String] ("department", _ == "cs")
        .groupBy ("title")
        .select ("pid", "name", "department", "title").show ()

} // RelationSQLTest


//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** The `RelationSQLTest2` object tests the 'save' method.
 *  > runMain scalation.columnar_db.RelationSQLTest2
 */
object RelationSQLTest2 extends App
{
    val professor = RelationSQL ("professor",
        Seq ("pid", "name", "department", "title"),
        Seq (Vector [Any] (1, "jackson", "pharm", 4),
             Vector [Any] (2, "ken", "cs", 2),
             Vector [Any] (3, "pan", "pharm", 0),
             Vector [Any] (4, "yang", "gis", 3),
             Vector [Any] (5, "zhang", "cs", 0),
             Vector [Any] (6, "Yu", "cs", 0)),
        -1, "ISSI", Seq ((null, null, -1)))

    professor.save ()

} // RelationSQLTest2


//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** The `RelationSQLTest3` object tests 'apply' method to load a saved relation.
 *  > runMain scalation.columnar_db.RelationSQLTest3
 */
object RelationSQLTest3 extends App
{
    RelationSQL ("professor").show ()

} // RelationSQLTest3


//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/** The `RelationSQLTest4` object tests the `RelationSQL` on the traffic schema.
 *  > runMain scalation.columnar_db.RelationSQLTest4
 */
object RelationSQLTest4 extends App
{
    val sensor  = RelationSQL ("sensor",
                               Seq ("sensorId", "model", "latitude", "longitude", "roadId"),
                               null, 0, "ISDDI")
    val road    = RelationSQL ("road",
                               Seq ("roadId", "rdName", "lat1", "long1", "lat2", "long2"),
                               null, 0, "ISDDDD")
    val mroad   = RelationSQL ("road",
                               Seq ("roadId", "rdName", "lanes", "lat1", "long1", "lat2", "long2"),
                               null, 0, "ISIDDDD")
    val traffic = RelationSQL ("traffic",
                               Seq ("time", "sensorId", "count", "speed"),
                               null, 0, "IIID")                                 // also try TIID
    val wsensor = RelationSQL ("sensor",
                               Seq ("sensorId", "model", "latitude", "longitude"),
                               null, 0, "ISDD")
    val weather = RelationSQL ("weather",
                               Seq ("time", "sensorId", "precipitation", "wind"),
                               null, 0, "TIID")

    sensor.insert (Vector [Any] (1, "BD-Loop", 0.0, 0.0, 101),
                   Vector [Any] (2, "BD-Loop", 0.0, 0.0, 101),
                   Vector [Any] (3, "BD-Loop", 0.0, 0.0, 501))

    traffic.insert (Vector [Any] (1, 1,  5, 55.0),
                    Vector [Any] (2, 1, 10, 60.0),
                    Vector [Any] (1, 2, 20, 60.0),
                    Vector [Any] (2, 2, 30, 60.0),
                    Vector [Any] (1, 3, 80, 45.0))

    sensor.materialize ()
    traffic.materialize ()

    sensor.generateIndex ()
    traffic.generateIndex ()

    sensor.show ()
    road.show ()
    mroad.show ()
    traffic.show ()
    wsensor.show ()
    weather.show ()

    (sensor join traffic).where [Int] ("roadId", _ == 101)
                         .select ("sensorId", "time", "count").show ()

    import Relation.avg

    (sensor join traffic).where [Int] ("roadId", _ == 101)
                         .groupBy ("sensorId")                            // FIX - currently fails
                         .eselect ((avg, "acount", "count"))("sensorId")

} // RelationSQLTest4