// 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 SQLProductSpecificHelperMicrosoft_SQL_Server extends SQLProductSpecificAbstractHelper
{
    private int extractMajorVersion( String pProductVersion )
    {
        if ( pProductVersion.startsWith( "9." ) )
        {
            return 9;
        }
        int at = pProductVersion.indexOf( '.' );
        switch ( at )
        {
            case -1:
                break;
            case 0:
                return -1;
            default:
                pProductVersion = pProductVersion.substring( at );
                break;
        }
        try
        {
            return Integer.parseInt( pProductVersion );
        }
        catch ( NumberFormatException e )
        {
            return -1;
        }
    }

    /**
     * @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 )
    {
        if ( extractMajorVersion( pProductVersion ) < 9 )
        {
            throw new UnsupportedOperationException( "Microsoft SQL Server version " + pProductVersion + " does NOT support OFFSET &/ LIMIT or the ROW_NUMBER() function.  You must use a stored procedure!" );
        }
        return "SELECT" + //
               " " + pSelectColumns + //
               " FROM " + //                                                    ROWNUM is 1 based
               "   (SELECT ROW_NUMBER() OVER(" + pOrderBy + ") AS zOurTempRowNum, * FROM " + pTableName + " " + pSelectionWhereClause + ") a" + //
               "      WHERE zOurTempRowNum > " + pOffset + " AND zOurTempRowNum <= " + (pOffset + pLimit) + //
               "      ORDER BY zOurTempRowNum";
    }

    @Override
    public boolean validateConnection( Connection pConnection, DBinfo pDBinfo )
            throws SQLException
    {
        return LLvalidateConWith( pConnection, //
                                  "SELECT TOP 1 1 AS Yup FROM sysobjects WHERE xtype='S'" );
    }

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

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