SQLite

** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.51 2004/05/24 12:55:55 danielk1977 Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "os.h"

  sqlite3_set_result_string(context, zBest, -1);
}

/*
** Return the type of the argument.
*/
static void typeofFunc(sqlite_func *context, int argc, sqlite3_value **argv){
  const char *z = 0;
  assert( argc==2 );
  switch( sqlite3_value_type(argv[0]) ){
    case SQLITE3_NULL: z = "null" ; break;
    case SQLITE3_INTEGER: z = "integer" ; break;
    case SQLITE3_TEXT: z = "text" ; break;
    case SQLITE3_FLOAT: z = "real" ; break;
    case SQLITE3_BLOB: z = "blob" ; break;
  }
  sqlite3_set_result_string(context, z, -1);
}

/*
** Implementation of the length() function
*/
static void lengthFunc(sqlite_func *context, int argc, sqlite3_value **argv){
  const char *z;

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.249 2004/05/24 12:55:55 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>

/*
** Each SQL function is defined by an instance of the following
** structure.  A pointer to this structure is stored in the sqlite.aFunc
** hash table.  When multiple functions have the same name, the hash table
** points to a linked list of these structures.
*/
struct FuncDef {
  void (*xFunc)(sqlite_func*,int,sqlite3_value**);  /* Regular function */
  void (*xStep)(sqlite_func*,int,const char**);  /* Aggregate function step */
  void (*xFinalize)(sqlite_func*);           /* Aggregate function finializer */
  signed char nArg;         /* Number of arguments.  -1 means unlimited */
  signed char dataType;     /* Arg that determines datatype.  -1=NUMERIC, */
                            /* -2=TEXT. -3=SQLITE_ARGS */
  u8 includeTypes;          /* Add datatypes to args of xFunc and xStep */
  void *pUserData;          /* User data parameter */

# 2003 September 6
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the sqlite_bind API.
#
# $Id: bind.test,v 1.7 2004/05/24 12:55:55 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test bind-1.1 {
  db close

  sqlite3_bind_int32 $VM 2 -2000000000
  sqlite3_bind_int32 $VM 3 2000000000
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 123 456 789 2 123 -2000000000 2000000000}
do_test bind-2.4 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {integer integer integer integer integer integer}
do_test bind-2.5 {
  execsql {
    DELETE FROM t1;
  }
} {}

# 64 bit Integers
do_test bind-3.1 {
  sqlite3_bind_int64 $VM 1 32
  sqlite3_bind_int64 $VM 2 -2000000000000
  sqlite3_bind_int64 $VM 3 2000000000000
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 32 -2000000000000 2000000000000}
do_test bind-3.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {integer integer integer}
do_test bind-3.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# Doubles
do_test bind-4.1 {
  sqlite3_bind_double $VM 1 1234.1234
  sqlite3_bind_double $VM 2 0.00001
  sqlite3_bind_double $VM 3 123456789
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 1234.1234 1e-05 123456789}
do_test bind-4.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {real real real}
do_test bind-4.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# NULL
do_test bind-5.1 {
  sqlite3_bind_null $VM 1
  sqlite3_bind_null $VM 2
  sqlite3_bind_null $VM 3 
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 {} {} {}}
do_test bind-5.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {null null null}
do_test bind-5.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# UTF-8 text
do_test bind-6.1 {
  sqlite3_bind_text $VM 1 hellothere 5
  sqlite3_bind_text $VM 2 "." 2
  sqlite3_bind_text $VM 3 world -1
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 hello . world}
do_test bind-6.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {text text text}
do_test bind-6.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# UTF-16 text
do_test bind-7.1 {
  sqlite3_bind_text16 $VM 1 [encoding convertto unicode hellothere] 10
  sqlite3_bind_text16 $VM 2 [encoding convertto unicode ""] 0
  sqlite3_bind_text16 $VM 3 [encoding convertto unicode world] 10
  sqlite_step $VM N VALUES COLNAMES
  sqlite3_reset $VM
  execsql {SELECT rowid, * FROM t1}
} {1 hello {} world}
do_test bind-7.2 {
  execsql {SELECT typeof(a), typeof(b), typeof(c) FROM t1}
} {text text text}
do_test bind-7.3 {
  execsql {
    DELETE FROM t1;
  }
} {}

# Test that the 'out of range' error works.

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.24 2004/05/24 12:55:55 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {

    CREATE TABLE t6(a,b,c,
      FOREIGN KEY (x,b) REFERENCES t4(x,y)
    );
  }
} {1 {unknown column "x" in foreign key definition}}


# Test for the "typeof" function. More tests for the
# typeof() function are found in bind.test and types.test.
#
do_test table-11.1 {
  execsql {
    CREATE TABLE t7(
       a integer primary key,
       b number(5,10),
       c character varying (8),
       d VARCHAR(9),
       e clob,
       f BLOB,
       g Text,
       h
    );
    INSERT INTO t7(a) VALUES(1);
    SELECT typeof(a), typeof(b), typeof(c), typeof(d),
           typeof(e), typeof(f), typeof(g), typeof(h)
    FROM t7 LIMIT 1;
  }
} {integer null null null null null null null} 
do_test table-11.2 {
  execsql {
    SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
    FROM t7 LIMIT 1;
  }
} {null null null null}

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.6 2004/05/24 12:55:55 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage

# Each element of the following list represents one test case.
#
# The first value of each sub-list is an SQL literal. The following
# four value are the storage classes that would be used if the
# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
# or NONE, respectively.
set values {
  { 5.0    integer real    text real    }
  { 5      integer integer text integer }
  { '5.0'  integer real    text text    }
  { '-5.0' integer real    text text    }
  { '-5.0' integer real    text text    }
  { '5'    integer integer text text    }
  { 'abc'  text    text    text text    }
  { NULL   null    null    null null    }
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# INSERT INTO <table> VALUE(<values>);
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "DELETE FROM t1;"
  execsql "INSERT INTO t1 VALUES($lit, $lit, $lit, $lit);"
  do_test types-1.1.$tnum {
    execsql {
      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# INSERT INTO t1 SELECT ....
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "DELETE FROM t1;"
  execsql "INSERT INTO t1 SELECT $lit, $lit, $lit, $lit;"
  do_test types-1.2.$tnum {
    execsql {
      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

# This code tests that the storage classes specified above (in the $values
# table) are correctly assigned when values are inserted using a statement
# of the form:
#
# UPDATE <table> SET <column> = <value>;
#
set tnum 1
foreach val $values {
  set lit [lindex $val 0]
  execsql "UPDATE t1 SET i = $lit, n = $lit, t = $lit, o = $lit;"
  do_test types-1.3.$tnum {
    execsql {
      SELECT typeof(i), typeof(n), typeof(t), typeof(o) FROM t1;
    }
  } [lrange $val 1 end]
  incr tnum
}

execsql {
  DROP TABLE t1;