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.135 2007/01/29 15:50:06 drh Exp $
** $Id: func.c,v 1.136 2007/01/29 17:58:28 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
/* #include <math.h> */
#include <stdlib.h>
#include <assert.h>
#include "vdbeInt.h"

  sqlite3Randomness(sizeof(r), &r);
  if( (r<<1)==0 ) r = 0;  /* Prevent 0x8000.... as the result so that we */
                          /* can always do abs() of the result */
  sqlite3_result_int64(context, r);
}

/*
** Implementation of randomhex(N).  Return a random hexadecimal string
** that is N characters long.
** Implementation of randomblob(N).  Return a random blob
** that is N bytes long.
*/
static void randomHex(
static void randomBlob(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  int n, i, j;
  unsigned char c, zBuf[1001];
  int n;
  unsigned char *p;
  assert( argc==1 );
  n = sqlite3_value_int(argv[0]);
  if( n&1 ) n++;
  if( n<2 ) n = 2;
  if( n>sizeof(zBuf)-1 )  n = sizeof(zBuf)-1;
  sqlite3Randomness(n/2, zBuf);
  if( n<1 ) n = 1;
  p = sqlite3_malloc(n);
  sqlite3Randomness(n, p);
  for(i=n-1, j=n/2-1; i>=1; i-=2, j--){
    static const char zDigits[] = "0123456789ABCDEF";
    c = zBuf[j];
    zBuf[i] = zDigits[c&0xf];
    zBuf[i-1] = zDigits[c>>4];
  }
  zBuf[n] = 0;
  sqlite3_result_text(context, (char*)zBuf, n, SQLITE_TRANSIENT);
  sqlite3_result_blob(context, (char*)p, n, sqlite3_free);
}

/*
** Implementation of the last_insert_rowid() SQL function.  The return
** value is the same as the sqlite3_last_insert_rowid() API function.
*/
static void last_insert_rowid(

  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3_result_text(context, sqlite3_version, -1, SQLITE_STATIC);
}

/* Array for converting from half-bytes (nybbles) into ASCII hex
** digits. */
static const char hexdigits[] = {
  '0', '1', '2', '3', '4', '5', '6', '7',
  '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' 
};

/*
** EXPERIMENTAL - This is not an official function.  The interface may
** change.  This function may disappear.  Do not write code that depends
** on this function.
**
** Implementation of the QUOTE() function.  This function takes a single

    }
    case SQLITE_INTEGER:
    case SQLITE_FLOAT: {
      sqlite3_result_value(context, argv[0]);
      break;
    }
    case SQLITE_BLOB: {
      static const char hexdigits[] = { 
        '0', '1', '2', '3', '4', '5', '6', '7',
        '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' 
      };
      char *zText = 0;
      int nBlob = sqlite3_value_bytes(argv[0]);
      char const *zBlob = sqlite3_value_blob(argv[0]);

      zText = (char *)sqliteMalloc((2*nBlob)+4); 
      if( !zText ){
        sqlite3_result_error(context, "out of memory", -1);

      z[j++] = '\'';
      z[j] = 0;
      sqlite3_result_text(context, z, j, SQLITE_TRANSIENT);
      sqliteFree(z);
    }
  }
}

/*
** The hex() function.  Interpret the argument as a blob.  Return
** a hexadecimal rendering as text.
*/
static void hexFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  int i, n;
  const unsigned char *pBlob;
  char *zHex, *z;
  assert( argc==1 );
  pBlob = sqlite3_value_blob(argv[0]);
  n = sqlite3_value_bytes(argv[0]);
  z = zHex = sqlite3_malloc(n*2 + 1);
  if( zHex==0 ) return;
  for(i=0; i<n; i++, pBlob++){
    unsigned char c = *pBlob;
    *(z++) = hexdigits[(c>>4)&0xf];
    *(z++) = hexdigits[c&0xf];
  }
  *z = 0;
  sqlite3_result_text(context, zHex, n*2, sqlite3_free);
}

#ifdef SQLITE_SOUNDEX
/*
** Compute the soundex encoding of a word.
*/
static void soundexFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
static void soundexFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  char zResult[8];
  const u8 *zIn;
  int i, j;
  static const unsigned char iCode[] = {
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

    { "round",              1, 0, SQLITE_UTF8,    0, roundFunc  },
    { "round",              2, 0, SQLITE_UTF8,    0, roundFunc  },
    { "upper",              1, 0, SQLITE_UTF8,    0, upperFunc  },
    { "lower",              1, 0, SQLITE_UTF8,    0, lowerFunc  },
    { "coalesce",          -1, 0, SQLITE_UTF8,    0, ifnullFunc },
    { "coalesce",           0, 0, SQLITE_UTF8,    0, 0          },
    { "coalesce",           1, 0, SQLITE_UTF8,    0, 0          },
    { "hex",                1, 0, SQLITE_UTF8,    0, hexFunc    },
    { "ifnull",             2, 0, SQLITE_UTF8,    1, ifnullFunc },
    { "random",            -1, 0, SQLITE_UTF8,    0, randomFunc },
    { "randomhex",          1, 0, SQLITE_UTF8,    0, randomHex  },
    { "randomblob",         1, 0, SQLITE_UTF8,    0, randomBlob },
    { "nullif",             2, 0, SQLITE_UTF8,    1, nullifFunc },
    { "sqlite_version",     0, 0, SQLITE_UTF8,    0, versionFunc},
    { "quote",              1, 0, SQLITE_UTF8,    0, quoteFunc  },
    { "last_insert_rowid",  0, 1, SQLITE_UTF8,    0, last_insert_rowid },
    { "changes",            0, 1, SQLITE_UTF8,    0, changes    },
    { "total_changes",      0, 1, SQLITE_UTF8,    0, total_changes },
#ifdef SQLITE_SOUNDEX

# 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 built-in functions.
#
# $Id: func.test,v 1.56 2007/01/29 15:50:06 drh Exp $
# $Id: func.test,v 1.57 2007/01/29 17:58:28 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {

do_test func-9.2 {
  execsql {
    SELECT typeof(random());
  }
} {integer}
do_test func-9.3 {
  execsql {
    SELECT randomhex(32) is not null;
    SELECT randomblob(32) is not null;
  }
} {1}
do_test func-9.4 {
  execsql {
    SELECT typeof(randomhex(32));
    SELECT typeof(randomblob(32));
  }
} {text}
} {blob}
do_test func-9.5 {
  execsql {
    SELECT length(randomhex(32)), length(randomhex(-5)),
           length(randomhex(2000)), length(randomhex(31));
    SELECT length(randomblob(32)), length(randomblob(-5)),
           length(randomblob(2000))
  }
} {32 2 1000 32}
} {32 1 2000}

# The "hex()" function was added in order to be able to render blobs
# generated by randomblob().  So this seems like a good place to test
# hex().
#
do_test func-9.10 {
  execsql {SELECT hex(x'00112233445566778899aAbBcCdDeEfF')}
} {00112233445566778899AABBCCDDEEFF}

# Use the "sqlite_register_test_function" TCL command which is part of
# the text fixture in order to verify correct operation of some of
# the user-defined SQL function APIs that are not used by the built-in
# functions.
#
set ::DB [sqlite3_connection_pointer db]

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.119 2007/01/29 15:50:06 drh Exp $}
set rcsid {$Id: lang.tcl,v 1.120 2007/01/29 17:58:28 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}

<tr>
<td valign="top" align="right">ifnull(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
both arguments are NULL then NULL is returned. This behaves the same as 
<b>coalesce()</b> above.</td>
</tr>

<tr>
<td valign="top" align="right">hex(<i>X</i>)</td>
<td valign="top">The argument is interpreted as a BLOB.  The result
is a hexadecimal rendering of the content of that blob.</td>
</tr>

<tr>
<td valign="top" align="right">last_insert_rowid()</td>
<td valign="top">Return the ROWID of the last row insert from this
connection to the database.  This is the same value that would be returned
from the <b>sqlite_last_insert_rowid()</b> API function.</td>
</tr>

<tr>
<td valign="top" align="right">random(*)</td>
<td valign="top">Return a pseudo-random integer
between -9223372036854775808 and +9223372036854775807.</td>
</tr>

<tr>
<td valign="top" align="right">randomhex(<i>N</i>)</td>
<td valign="top">Return a pseudo-random hexadecimal string that is
<i>N</i> characters in length.  <i>N</i> should be an even integer between
<td valign="top" align="right">randomblob(<i>N</i>)</td>
<td valign="top">Return a <i>N</i>-byte blob containing pseudo-random bytes.
<i>N</i> should be a postive integer.</td>
2 and 1000.  The intended use of this function is to generate
universally unique identifiers (UUID).  For that purpose, it is recommended
that <i>N</i> be at least 32.</td>
</tr>

<tr>
<td valign="top" align="right">round(<i>X</i>)<br>round(<i>X</i>,<i>Y</i>)</td>
<td valign="top">Round off the number <i>X</i> to <i>Y</i> digits to the
right of the decimal point.  If the <i>Y</i> argument is omitted, 0 is 
assumed.</td>