System.Data.SQLite

//---------------------------------------------------------------------
// <copyright file="SkipClause.cs" company="Microsoft">
//      Portions of this file copyright (c) Microsoft Corporation
//      and are released under the Microsoft Pulic License.  See
//      http://archive.msdn.microsoft.com/EFSampleProvider/Project/License.aspx
//      or License.txt for details.
//      All rights reserved.
// </copyright>
//---------------------------------------------------------------------

namespace System.Data.SQLite
{
    using System.Globalization;

    /// <summary>
    /// SkipClause represents the a SKIP expression in a SqlSelectStatement.
    /// It has a count property, which indicates how many rows should be skipped.
    /// </summary>
    class SkipClause : ISqlFragment
    {
        ISqlFragment skipCount;

        /// <summary>
        /// How many rows should be skipped.
        /// </summary>
        internal ISqlFragment SkipCount
        {
            get { return skipCount; }
        }

        /// <summary>
        /// Creates a SkipClause with the given skipCount.
        /// </summary>
        /// <param name="skipCount"></param>
        internal SkipClause(ISqlFragment skipCount)
        {
            this.skipCount = skipCount;
        }

        /// <summary>
        /// Creates a SkipClause with the given skipCount.
        /// </summary>
        /// <param name="skipCount"></param>
        internal SkipClause(int skipCount)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            sqlBuilder.Append(skipCount.ToString(CultureInfo.InvariantCulture));
            this.skipCount = sqlBuilder;
        }

        #region ISqlFragment Members
        /// <summary>
        /// Write out the SKIP part of sql select statement 
        /// It basically writes OFFSET (X).
        /// </summary>
        /// <param name="writer"></param>
        /// <param name="sqlGenerator"></param>
        public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
        {
            writer.Write(" OFFSET ");
            this.SkipCount.WriteSql(writer, sqlGenerator);
        }
        #endregion
    }
}

{
  using System;
  using System.Collections.Generic;
  using System.Data.Common.CommandTrees;

    internal class SqlChecker : DbExpressionVisitor<bool>
  {
#if false
    private static Type sql8rewriter;

    static SqlChecker()
    {
        string version =
#if NET_20
            "3.5.0.0";
#else
            "4.0.0.0";
#endif

        sql8rewriter = Type.GetType(String.Format("System.Data.SqlClient.SqlGen.Sql8ExpressionRewriter, System.Data.Entity, Version={0}, Culture=neutral, PublicKeyToken=b77a5c561934e089", version), false);
    }
#endif

    private SqlChecker()
    {
    }

#if false
    /// <summary>
    /// SQLite doesn't support things like SKIP and a few other things.  
    /// So determine if the query has to be rewritten
    /// </summary>
    /// <remarks>
    /// Microsoft went to all the trouble of making things like SKIP work 
    /// on Sql Server 2000 by doing a rewrite of the commandtree.

      SqlChecker visitor = new SqlChecker();
      if (tree.Query.Accept<bool>(visitor))
      {
        tree = sql8rewriter.InvokeMember("Rewrite", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Static, null, null, new object[] { tree }) as DbQueryCommandTree;
      }      
      return tree;
    }
#endif

    public override bool Visit(DbAndExpression expression)
    {
      return VisitBinaryExpression(expression);
    }

    public override bool Visit(DbApplyExpression expression)

    /// CollectionType => select statement
    /// non collection type => select expression
    /// </summary>
    /// <param name="tree"></param>
    /// <returns>The string representing the SQL to be executed.</returns>
    private string GenerateSql(DbQueryCommandTree tree)
    {
#if false
      tree = SqlChecker.Rewrite(tree);
#endif

      selectStatementStack = new Stack<SqlSelectStatement>();
      isParentAJoinStack = new Stack<bool>();

      allExtentNames = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
      allColumnNames = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);

      // Literals will not be converted to parameters.

    /// WHERE Y.[row_number] > count 
    /// ORDER BY sk1, sk2, ...
    /// </summary>
    /// <param name="e"></param>
    /// <returns>A <see cref="SqlBuilder"/></returns>
    public override ISqlFragment Visit(DbSkipExpression e)
    {
        Debug.Assert(e.Count is DbConstantExpression || e.Count is DbParameterReferenceExpression, "DbLimitExpression.Count is of invalid expression type");

        Symbol fromSymbol;
        SqlSelectStatement result = VisitInputExpression(e.Input.Expression, e.Input.VariableName, e.Input.VariableType, out fromSymbol);

        if (!IsCompatible(result, e.ExpressionKind))
        {
            result = CreateNewSelectStatement(result, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
        }

        selectStatementStack.Push(result);
        symbolTable.EnterScope();

        AddFromSymbol(result, e.Input.VariableName, fromSymbol);

        AddSortKeys(result.OrderBy, e.SortOrder);

        symbolTable.ExitScope();
        selectStatementStack.Pop();

        ISqlFragment skipCount = HandleCountExpression(e.Count);

        result.Skip = new SkipClause(skipCount);
        return result;
    }

    /// <summary>
    /// <see cref="Visit(DbFilterExpression)"/>
    /// </summary>
    /// <param name="e"></param>
    /// <returns>A <see cref="SqlSelectStatement"/></returns>

      get { return top; }
      set
      {
        Debug.Assert(top == null, "SqlSelectStatement.Top has already been set");
        top = value;
      }
    }

    private SkipClause skip;
    internal SkipClause Skip
    {
      get { return skip; }
      set
      {
          Debug.Assert(skip == null, "SqlSelectStatement.Skip has already been set");
          skip = value;
      }
    }

    private SqlBuilder select = new SqlBuilder();
    internal SqlBuilder Select
    {
      get { return select; }
    }

      }

      if (this.Top != null)
      {
        this.Top.WriteSql(writer, sqlGenerator);
      }

      if (this.skip != null)
      {
        this.Skip.WriteSql(writer, sqlGenerator);
      }

      --writer.Indent;
    }

    #endregion
  }
}

      this.withTies = withTies;
    }

    #region ISqlFragment Members

    /// <summary>
    /// Write out the TOP part of sql select statement 
    /// It basically writes LIMIT (X).
    /// </summary>
    /// <param name="writer"></param>
    /// <param name="sqlGenerator"></param>
    public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
    {
      writer.Write(" LIMIT ");
      this.TopCount.WriteSql(writer, sqlGenerator);

    </Compile>
    <Compile Include="SQL Generation\DmlSqlGenerator.cs" />
    <Compile Include="SQL Generation\InternalBase.cs" />
    <Compile Include="SQL Generation\ISqlFragment.cs" />
    <Compile Include="SQL Generation\JoinSymbol.cs" />
    <Compile Include="SQL Generation\KeyToListMap.cs" />
    <Compile Include="SQL Generation\MetadataHelpers.cs" />
    <Compile Include="SQL Generation\SkipClause.cs" />
    <Compile Include="SQL Generation\SqlBuilder.cs" />
    <Compile Include="SQL Generation\SqlChecker.cs" />
    <Compile Include="SQL Generation\SqlGenerator.cs" />
    <Compile Include="SQL Generation\SqlSelectStatement.cs" />
    <Compile Include="SQL Generation\SqlWriter.cs" />
    <Compile Include="SQL Generation\StringUtil.cs" />
    <Compile Include="SQL Generation\Symbol.cs" />

    </Compile>
    <Compile Include="SQL Generation\DmlSqlGenerator.cs" />
    <Compile Include="SQL Generation\InternalBase.cs" />
    <Compile Include="SQL Generation\ISqlFragment.cs" />
    <Compile Include="SQL Generation\JoinSymbol.cs" />
    <Compile Include="SQL Generation\KeyToListMap.cs" />
    <Compile Include="SQL Generation\MetadataHelpers.cs" />
    <Compile Include="SQL Generation\SkipClause.cs" />
    <Compile Include="SQL Generation\SqlBuilder.cs" />
    <Compile Include="SQL Generation\SqlChecker.cs" />
    <Compile Include="SQL Generation\SqlGenerator.cs" />
    <Compile Include="SQL Generation\SqlSelectStatement.cs" />
    <Compile Include="SQL Generation\SqlWriter.cs" />
    <Compile Include="SQL Generation\StringUtil.cs" />
    <Compile Include="SQL Generation\Symbol.cs" />

  set savedEncoding [object invoke Console OutputEncoding]
  set encoding [object invoke System.Text.Encoding GetEncoding Windows-1252]

  object invoke Console OutputEncoding $encoding
} -body {
  set code [catch {
    testExec $testLinqExeFile [list -eventflags Wait -directory \
    [file dirname $testLinqExeFile] -stdout output -success 0]
  } error]

  tlog "---- BEGIN STDOUT OUTPUT\n"
  tlog $output
  tlog "\n---- END STDOUT OUTPUT\n"

  list $code [string equal $output [readFile $testLinqOutFile]] \

###############################################################################
#
# tkt-8b7d179c3c.eagle --
#
# Written by Joe Mistachkin.
# Released to the public domain, use at your own risk!
#
###############################################################################

package require Eagle
package require EagleLibrary
package require EagleTest

runTestPrologue

###############################################################################

source [file join $path common.eagle]
runSQLiteTestPrologue

###############################################################################

#
# NOTE: Setup the variables that refer to the various files required by the
#       tests in this file.
#
set testLinqExeFile [getBuildFileName testlinq.exe]
set northwindEfDbFile [file join [file dirname $path] testlinq northwindEF.db]

#
# NOTE: Setup the test constraints specific to the tests in this file.
#
if {![haveConstraint file_[file tail $testLinqExeFile]]} then {
  checkForFile $test_channel $testLinqExeFile
}

if {![haveConstraint file_[file tail $northwindEfDbFile]]} then {
  checkForFile $test_channel $northwindEfDbFile
}

###############################################################################

runTest {test tkt-8b7d179c3c-1.1 {LINQ with Skip and Take} -body {
  set result [list]

  for {set pageSize 0} {$pageSize <= 2} {incr pageSize} {
    set code [catch {
      testExec $testLinqExeFile [list -eventflags Wait -directory \
      [file dirname $testLinqExeFile] -nocarriagereturns -stdout output \
      -success 0] -skip $pageSize
    } error]

    tlog "---- BEGIN STDOUT OUTPUT\n"
    tlog $output
    tlog "\n---- END STDOUT OUTPUT\n"

    lappend result $output
  }

  list $code [expr {$code == 0 ? "" : $error}] $result
} -cleanup {
  unset -nocomplain code output error result pageSize
} -constraints \
{eagle file_testlinq.exe file_northwindEF.db} -result {0 {} {{} {DRACD RATTC\
OLDWO GALED LILAS MAGAA ALFKI CHOPS SAVEA KOENE MAISD FOLKO CACTU OCEAN RANCH\
THECR GOURL GROSR SUPRD HUNGO ISLAT QUICK HUNGC GREAL LEHMS RICSU ERNSH WILMK\
LINOD TRAIH SIMOB OTTIK SPLIR MORGK FOLIG FURIB PRINI AROUT BSBEV CONSH EASTC\
NORTS SEVES BERGS VICTE BOLID FISSA ROMEY BLAUS BONAP MEREP ANATR ANTON CENTC\
PERIC TORTU FRANK TOMSP DUMON FRANR WARTH PARIS SPECD LONEP THEBI REGGC VINET\
WELLI HANAR QUEDE RICAR PICCO HILAA LETSS COMMI FAMIA QUEEN TRADH WHITC GODOS\
SANTG BLONP WANDK FRANS LAMAI BOTTM LAUGB LACOR LAZYK WOLZA VAFFE
} {DRACD RATTC OLDWO GALED LILAS MAGAA ALFKI CHOPS SAVEA KOENE MAISD FOLKO\
CACTU OCEAN RANCH THECR GOURL GROSR SUPRD HUNGO ISLAT QUICK HUNGC GREAL LEHMS\
RICSU ERNSH WILMK LINOD TRAIH SIMOB OTTIK SPLIR MORGK FOLIG FURIB PRINI AROUT\
BSBEV CONSH EASTC NORTS SEVES BERGS VICTE BOLID FISSA ROMEY BLAUS BONAP MEREP\
ANATR ANTON CENTC PERIC TORTU FRANK TOMSP DUMON FRANR WARTH PARIS SPECD LONEP\
THEBI REGGC VINET WELLI HANAR QUEDE RICAR PICCO HILAA LETSS COMMI FAMIA QUEEN\
TRADH WHITC GODOS SANTG BLONP WANDK FRANS LAMAI BOTTM LAUGB LACOR LAZYK WOLZA\
VAFFE
}}}}

###############################################################################

unset -nocomplain testLinqExeFile northwindEfDbFile

###############################################################################

runSQLiteTestEpilogue
runTestEpilogue

using System;
using System.Linq;
using System.Data.Objects;
using System.Text;

namespace testlinq
{
  class Program
  {
      private static int Main(string[] args)
      {
          string arg = null;

          if ((args != null) && (args.Length > 0))
              arg = args[0];

          if (arg == null)
              arg = "";

          arg = arg.Trim().TrimStart('-', '/').ToLowerInvariant();

          switch (arg)
          {
              case "": // String.Empty
              case "old":
                  {
                      return OldTests();
                  }
              case "skip":
                  {
                      int pageSize = 0;

                      if (args.Length > 1)
                      {
                          arg = args[1];

                          if (arg != null)
                              pageSize = int.Parse(arg.Trim());
                      }

                      return SkipTest(pageSize);
                  }
              default:
                  {
                      Console.WriteLine("unknown test \"{0}\"", arg);
                      return 1;
                  }
          }
      }

      private static int SkipTest(int pageSize)
      {
          using (northwindEFEntities db = new northwindEFEntities())
          {
              bool once = false;
              int count = db.Customers.Count();

              int PageCount = (pageSize != 0) ?
                  (count / pageSize) + ((count % pageSize) == 0 ? 0 : 1) : 1;

              for (int pageIndex = 0; pageIndex < PageCount; pageIndex++)
              {
                  var query = db.Customers.OrderBy(p => p.City).
                      Skip(pageSize * pageIndex).Take(pageSize);

                  foreach (Customers customers in query)
                  {
                      if (once)
                          Console.Write(' ');

                      Console.Write(customers.CustomerID);

                      once = true;
                  }
              }
          }

          return 0;
      }

    private static int OldTests()
    {
      using (northwindEFEntities db = new northwindEFEntities())
      {
        {
          string entitySQL = "SELECT VALUE o FROM Orders AS o WHERE SQLite.DatePart('yyyy', o.OrderDate) = 1997;";
          ObjectQuery<Orders> query = db.CreateQuery<Orders>(entitySQL);

      }

      //
      // NOTE: (JJM) Removed on 2011/07/06, makes it harder to run this EXE via
      //       the new unit test suite.
      //
      // Console.ReadKey();

      return 0;
    }
  }
}