github.com/yongjacky/phoenix-go-orm-builder@v0.3.5/builder_limit_test.go (about)

     1  // Copyright 2018 The Xorm Authors. All rights reserved.
     2  // Use of this source code is governed by a BSD-style
     3  // license that can be found in the LICENSE file.
     4  
     5  package builder
     6  
     7  /*
     8  func TestBuilder_Limit4Mssql(t *testing.T) {
     9  	sqlFromFile, err := readPreparationSQLFromFile("testdata/mssql_fiddle_data.sql")
    10  	assert.NoError(t, err)
    11  	f, err := newFiddler("", MSSQL, sqlFromFile)
    12  	assert.NoError(t, err)
    13  	assert.NotEmpty(t, f.sessionCode)
    14  
    15  	// simple -- MsSQL style
    16  	sql, err := Dialect(MSSQL).Select("a", "b", "c").From("table1").
    17  		OrderBy("a ASC").Limit(5).ToBoundSQL()
    18  	assert.NoError(t, err)
    19  	assert.EqualValues(t, "SELECT a,b,c FROM (SELECT TOP 5 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM table1 ORDER BY a ASC) at", sql)
    20  	assert.NoError(t, f.executableCheck(sql))
    21  
    22  	// simple with where -- MsSQL style
    23  	sql, err = Dialect(MSSQL).Select("a", "b", "c").From("table1").
    24  		Where(Neq{"a": "3"}).OrderBy("a ASC").Limit(5, 10).ToBoundSQL()
    25  	assert.NoError(t, err)
    26  	assert.EqualValues(t, "SELECT a,b,c FROM (SELECT TOP 15 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM table1 WHERE a<>'3' ORDER BY a ASC) at WHERE at.RN>10", sql)
    27  	assert.NoError(t, f.executableCheck(sql))
    28  
    29  	// union with limit -- MsSQL style
    30  	sql, err = Dialect(MSSQL).Select("a", "b", "c").From(
    31  		Dialect(MSSQL).Select("a", "b", "c").From("table1").Where(Neq{"a": "1"}).
    32  			OrderBy("a ASC").Limit(5, 6).Union("ALL",
    33  			Select("a", "b", "c").From("table1").Where(Neq{"b": "2"}).OrderBy("a DESC").Limit(10)), "at").
    34  		OrderBy("b DESC").Limit(7, 9).ToBoundSQL()
    35  	assert.NoError(t, err)
    36  	assert.EqualValues(t, "SELECT a,b,c FROM (SELECT TOP 16 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM ((SELECT a,b,c FROM (SELECT TOP 11 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM table1 WHERE a<>'1' ORDER BY a ASC) at WHERE at.RN>6) UNION ALL (SELECT a,b,c FROM (SELECT TOP 10 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM table1 WHERE b<>'2' ORDER BY a DESC) at)) at ORDER BY b DESC) at WHERE at.RN>9", sql)
    37  	assert.NoError(t, f.executableCheck(sql))
    38  }
    39  
    40  func TestBuilder_Limit4MysqlLike(t *testing.T) {
    41  	sqlFromFile, err := readPreparationSQLFromFile("testdata/mysql_fiddle_data.sql")
    42  	assert.NoError(t, err)
    43  	f, err := newFiddler("", MYSQL, sqlFromFile)
    44  	assert.NoError(t, err)
    45  	assert.NotEmpty(t, f.sessionCode)
    46  
    47  	// simple -- MySQL/SQLite/PostgreSQL style
    48  	sql, err := Dialect(MYSQL).Select("a", "b", "c").From("table1").OrderBy("a ASC").
    49  		Limit(5, 10).ToBoundSQL()
    50  	assert.NoError(t, err)
    51  	assert.EqualValues(t, "SELECT a,b,c FROM table1 ORDER BY a ASC LIMIT 5 OFFSET 10", sql)
    52  	assert.NoError(t, f.executableCheck(sql))
    53  
    54  	// simple -- MySQL/SQLite/PostgreSQL style
    55  	sql, err = Dialect(MYSQL).Select("a", "b", "c").From("table1").
    56  		OrderBy("a ASC").Limit(5).ToBoundSQL()
    57  	assert.NoError(t, err)
    58  	assert.EqualValues(t, "SELECT a,b,c FROM table1 ORDER BY a ASC LIMIT 5", sql)
    59  	assert.NoError(t, f.executableCheck(sql))
    60  
    61  	// simple with where -- MySQL/SQLite/PostgreSQL style
    62  	sql, err = Dialect(MYSQL).Select("a", "b", "c").From("table1").
    63  		Where(Eq{"a": "1", "b": "1"}).OrderBy("a ASC").Limit(5, 10).ToBoundSQL()
    64  	assert.NoError(t, err)
    65  	assert.EqualValues(t, "SELECT a,b,c FROM table1 WHERE a='1' AND b='1' ORDER BY a ASC LIMIT 5 OFFSET 10", sql)
    66  	assert.NoError(t, f.executableCheck(sql))
    67  
    68  	// union -- MySQL/SQLite/PostgreSQL style
    69  	sql, err = Dialect(MYSQL).Select("a", "b", "c").From(
    70  		Dialect(MYSQL).Select("a", "b", "c").From("table1").Where(Eq{"a": "1"}).OrderBy("a ASC").
    71  			Limit(5, 9).Union("ALL",
    72  			Select("a", "b", "c").From("table1").Where(Eq{"a": "2"}).OrderBy("a DESC").Limit(10)), "at").
    73  		Limit(5, 10).ToBoundSQL()
    74  	assert.NoError(t, err)
    75  	assert.EqualValues(t, "SELECT a,b,c FROM ((SELECT a,b,c FROM table1 WHERE a='1' ORDER BY a ASC LIMIT 5 OFFSET 9) UNION ALL (SELECT a,b,c FROM table1 WHERE a='2' ORDER BY a DESC LIMIT 10)) at LIMIT 5 OFFSET 10", sql)
    76  	assert.NoError(t, f.executableCheck(sql))
    77  }
    78  
    79  func TestBuilder_Limit4Oracle(t *testing.T) {
    80  	sqlFromFile, err := readPreparationSQLFromFile("testdata/oracle_fiddle_data.sql")
    81  	assert.NoError(t, err)
    82  	f, err := newFiddler("", ORACLE, sqlFromFile)
    83  	assert.NoError(t, err)
    84  	assert.NotEmpty(t, f.sessionCode)
    85  
    86  	// simple -- OracleSQL style
    87  	sql, err := Dialect(ORACLE).Select("a", "b", "c").From("table1").OrderBy("a ASC").
    88  		Limit(5, 10).ToBoundSQL()
    89  	assert.NoError(t, err)
    90  	assert.EqualValues(t, "SELECT a,b,c FROM (SELECT * FROM (SELECT a,b,c,ROWNUM RN FROM table1 ORDER BY a ASC) at WHERE at.RN<=15) att WHERE att.RN>10", sql)
    91  	assert.NoError(t, f.executableCheck(sql))
    92  
    93  	// simple with join -- OracleSQL style
    94  	sql, err = Dialect(ORACLE).Select("a", "b", "c", "d").From("table1 t1").
    95  		InnerJoin("table2 t2", "t1.id = t2.ref_id").OrderBy("a ASC").Limit(5, 10).ToBoundSQL()
    96  	assert.NoError(t, err)
    97  	assert.EqualValues(t, "SELECT a,b,c,d FROM (SELECT * FROM (SELECT a,b,c,d,ROWNUM RN FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.ref_id ORDER BY a ASC) at WHERE at.RN<=15) att WHERE att.RN>10", sql)
    98  	assert.NoError(t, f.executableCheck(sql))
    99  
   100  	// simple -- OracleSQL style
   101  	sql, err = Dialect(ORACLE).Select("a", "b", "c").From("table1").
   102  		OrderBy("a ASC").Limit(5).ToBoundSQL()
   103  	assert.NoError(t, err)
   104  	assert.EqualValues(t, "SELECT a,b,c FROM (SELECT a,b,c,ROWNUM RN FROM table1 ORDER BY a ASC) at WHERE at.RN<=5", sql)
   105  	assert.NoError(t, f.executableCheck(sql))
   106  
   107  	// simple with where -- OracleSQL style
   108  	sql, err = Dialect(ORACLE).Select("a", "b", "c").From("table1").Where(Neq{"a": "10", "b": "20"}).
   109  		OrderBy("a ASC").Limit(5, 1).ToBoundSQL()
   110  	assert.NoError(t, err)
   111  	assert.EqualValues(t, "SELECT a,b,c FROM (SELECT * FROM (SELECT a,b,c,ROWNUM RN FROM table1 WHERE a<>'10' AND b<>'20' ORDER BY a ASC) at WHERE at.RN<=6) att WHERE att.RN>1", sql)
   112  	assert.NoError(t, f.executableCheck(sql))
   113  
   114  	// union with limit -- OracleSQL style
   115  	sql, err = Dialect(ORACLE).Select("a", "b", "c").From(
   116  		Dialect(ORACLE).Select("a", "b", "c").From("table1").
   117  			Where(Neq{"a": "0"}).OrderBy("a ASC").Limit(5, 10).Union("ALL",
   118  			Select("a", "b", "c").From("table1").Where(Neq{"b": "48"}).
   119  				OrderBy("a DESC").Limit(10)), "at").
   120  		Limit(3).ToBoundSQL()
   121  	assert.NoError(t, err)
   122  	assert.EqualValues(t, "SELECT a,b,c FROM (SELECT a,b,c,ROWNUM RN FROM ((SELECT a,b,c FROM (SELECT * FROM (SELECT a,b,c,ROWNUM RN FROM table1 WHERE a<>'0' ORDER BY a ASC) at WHERE at.RN<=15) att WHERE att.RN>10) UNION ALL (SELECT a,b,c FROM (SELECT a,b,c,ROWNUM RN FROM table1 WHERE b<>'48' ORDER BY a DESC) at WHERE at.RN<=10)) at) at WHERE at.RN<=3", sql)
   123  	assert.NoError(t, f.executableCheck(sql))
   124  }*/