// This Source Code is in the Public Domain per: http://litesoft.org/License.txt
package org.litesoft.sql;

import java.sql.*;

import org.litesoft.db.*;

public class SQLProductSpecificHelperOracle extends SQLProductSpecificAbstractHelper
{
    /**
     * @param pProductVersion       - Of The DataBase
     * @param pTableName            - Of the Schema's Table Name
     * @param pSelectColumns        - Of the Table (comma separated)
     * @param pSelectionWhereClause - null means all
     * @param pOrderBy              - null means order immaterial
     * @param pOffset               - Rows to Skip (>= 0 verified)
     * @param pLimit                - Max Rows to return (>= 1 verified) @return !Null
     */
    @Override
    public String buildQuery( String pProductVersion, String pTableName, //
                              String pSelectColumns, String pSelectionWhereClause, String pOrderBy, //
                              long pOffset, int pLimit )
    {
        String innerSelect = "SELECT * FROM " + pTableName + //
                             " " + pSelectionWhereClause + //
                             " " + pOrderBy;
        if ( pOffset == 0 )
        {
            return "SELECT" + //
                   " " + pSelectColumns + //
                   " FROM (" + innerSelect + ") WHERE ROWNUM <= " + pLimit; //   ROWNUM is 1 based
        }
        return "SELECT" + //
               " " + pSelectColumns + //
               " FROM " + //                                                    ROWNUM is 1 based
               "    (SELECT ROWNUM zOurTempRowNum, a.* FROM (" + innerSelect + ") a" + //
               "        WHERE ROWNUM <= " + (pOffset + pLimit) + ")" + //
               " WHERE zOurTempRowNum > " + pOffset;

        //SELECT * FROM
        //    (SELECT a.*, ROWNUM RNUM FROM
        //        (SELECT * FROM table WHERE blah = 2 ORDER BY thing1, thing2) a
        //      WHERE ROWNUM <= 150)
        //    WHERE RNUM >= 101

        // also suggested:

        //Select * from mytable where rownum<=15 minus select * from mytable where rownum<=10
    }

    @Override
    public boolean validateConnection( Connection pConnection, DBinfo pDBinfo )
            throws SQLException
    {
        return LLvalidateConWith( pConnection, //
                                  "SELECT count(0) FROM DUAL" );
    }

    @Override
    public boolean tableExists( Connection pConnection, DBinfo pDBinfo, String pTableName )
            throws SQLException, MultipleTablesException
    {
        return LLtableExists( pConnection, pDBinfo, pTableName, //
                              "SELECT count(0) FROM " + pTableName );
    }

    @Override
    public boolean columnExists( Connection pConnection, DBinfo pDBinfo, String pTableName, String pColumnName )
            throws SQLException, MultipleTablesException
    {
        return LLcolumnExists( pConnection, pDBinfo, pTableName, pColumnName,  //
                               "SELECT count(" + pColumnName + ") FROM " + pTableName );
    }
}