SQLite

/*
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.58 2002/03/03 23:06:01 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.

  */
  checkList(&sCheck, 1, pBt->page1->freeList, pBt->page1->nFree,
            "Main freelist: ");

  /* Check all the tables.
  */
  for(i=0; i<nRoot; i++){
    if( aRoot[i]==0 ) continue;
    checkTreePage(&sCheck, aRoot[i], 0, "List of tree roots: ", 0,0,0,0);
  }

  /* Make sure every page in the file is referenced
  */
  for(i=1; i<=sCheck.nPage; i++){
    if( sCheck.anRef[i]==0 ){

**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.83 2002/03/03 23:06:01 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 

  ** in the SQLITE_MASTER table of the database.  The record number
  ** for the new table entry should already be on the stack.
  **
  ** If this is a TEMPORARY table, then just create the table.  Do not
  ** make an entry in SQLITE_MASTER.
  */
  if( !pParse->initFlag ){
    int n;
    Vdbe *v;

    v = sqliteGetVdbe(pParse);
    if( v==0 ) return;
    if( p->pSelect==0 ){
      /* A regular table */
      sqliteVdbeAddOp(v, OP_CreateTable, 0, p->isTemp);
      sqliteVdbeChangeP3(v, -1, (char *)&p->tnum, P3_POINTER);
    }else{
      /* A view */
      sqliteVdbeAddOp(v, OP_Integer, 0, 0);
    }
    p->tnum = 0;
    if( !p->isTemp ){
      sqliteVdbeAddOp(v, OP_Pull, 1, 0);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      if( p->pSelect==0 ){
        sqliteVdbeChangeP3(v, -1, "table", P3_STATIC);
      }else{
        sqliteVdbeChangeP3(v, -1, "view", P3_STATIC);
      }
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, p->zName, P3_STATIC);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, p->zName, P3_STATIC);
      sqliteVdbeAddOp(v, OP_Dup, 4, 0);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      if( pSelect ){

  Parse *pParse,     /* The parsing context */
  Token *pBegin,     /* The CREATE token that begins the statement */
  Token *pName,      /* The token that holds the name of the view */
  Select *pSelect    /* A SELECT statement that will become the new view */
){
  Token sEnd;
  Table *p;
  const char *z;
  int n, offset;

  sqliteStartTable(pParse, pBegin, pName, 0);
  p = pParse->pNewTable;
  if( p==0 ){
    sqliteSelectDelete(pSelect);
    return;
  }
  p->pSelect = pSelect;
  if( !pParse->initFlag ){
    if( sqliteViewGetColumnNames(pParse, p) ){
      return;
    }
  }
  sEnd = pParse->sLastToken;
  if( sEnd.z[0]!=0 && sEnd.z[0]!=';' ){
    sEnd.z += sEnd.n;
  }
  sEnd.n = 0;
  n = ((int)sEnd.z) - (int)pBegin->z;
  z = pBegin->z;
  while( n>0 && (z[n-1]==';' || isspace(z[n-1])) ){ n--; }
  sEnd.z = &z[n-1];
  sEnd.n = 1;
  z = p->pSelect->zSelect = sqliteStrNDup(z, n);
  if( z ){
    offset = ((int)z) - (int)pBegin->z;
    sqliteSelectMoveStrings(p->pSelect, offset);
    sqliteEndTable(pParse, &sEnd, 0);
  }
  return;
}

  return pTab;
}

/*
** This routine is called to do the work of a DROP TABLE statement.
** pName is the name of the table to be dropped.
*/
void sqliteDropTable(Parse *pParse, Token *pName, int isView){
  Table *pTable;
  Vdbe *v;
  int base;
  sqlite *db = pParse->db;

  if( pParse->nErr || sqlite_malloc_failed ) return;
  pTable = sqliteTableFromToken(pParse, pName);
  if( pTable==0 ) return;
  if( pTable->readOnly ){
    sqliteSetString(&pParse->zErrMsg, "table ", pTable->zName, 
       " may not be dropped", 0);
    pParse->nErr++;
    return;
  }
  if( isView && pTable->pSelect==0 ){
    sqliteSetString(&pParse->zErrMsg, "use DROP TABLE to delete table ",
      pTable->zName, 0);
    pParse->nErr++;
    return;
  }
  if( !isView && pTable->pSelect ){
    sqliteSetString(&pParse->zErrMsg, "use DROP VIEW to delete view ",
      pTable->zName, 0);
    pParse->nErr++;
    return;
  }

  /* Generate code to remove the table from the master table
  ** on disk.
  */
  v = sqliteGetVdbe(pParse);
  if( v ){

    sqliteBeginWriteOperation(pParse);
    if( !pTable->isTemp ){
      base = sqliteVdbeAddOpList(v, ArraySize(dropTable), dropTable);
      sqliteVdbeChangeP3(v, base+2, pTable->zName, 0);
      changeCookie(db);
      sqliteVdbeChangeP1(v, base+9, db->next_cookie);
    }
    if( !isView ){
      sqliteVdbeAddOp(v, OP_Destroy, pTable->tnum, pTable->isTemp);
      for(pIdx=pTable->pIndex; pIdx; pIdx=pIdx->pNext){
        sqliteVdbeAddOp(v, OP_Destroy, pIdx->tnum, pTable->isTemp);
      }
    }
    sqliteEndWriteOperation(pParse);
  }

  /* Move the table (and all its indices) to the pending DROP queue.
  ** Or, if the table was never committed, just delete it.  If the table
  ** has been committed and is placed on the pending DROP queue, then the

      { OP_Open,        0, 2,        0},
      { OP_Rewind,      0, 6,        0},
      { OP_Column,      0, 3,        0},
      { OP_SetInsert,   0, 0,        0},
      { OP_Next,        0, 3,        0},
      { OP_IntegrityCk, 0, 0,        0},
      { OP_ColumnCount, 1, 0,        0},
      { OP_ColumnName,  0, 0,        "integrity_check"},
      { OP_Callback,    1, 0,        0},
    };
    Vdbe *v = sqliteGetVdbe(pParse);
    if( v==0 ) return;
    sqliteVdbeAddOpList(v, ArraySize(checkDb), checkDb);
  }else

  {}
  sqliteFree(zLeft);
  sqliteFree(zRight);
}

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.55 2002/03/03 23:06:01 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);

resolvetype(A) ::= ABORT.                    { A = OE_Abort; }
resolvetype(A) ::= FAIL.                     { A = OE_Fail; }
resolvetype(A) ::= IGNORE.                   { A = OE_Ignore; }
resolvetype(A) ::= REPLACE.                  { A = OE_Replace; }

////////////////////////// The DROP TABLE /////////////////////////////////////
//
cmd ::= DROP TABLE ids(X).          {sqliteDropTable(pParse,&X,0);}

///////////////////// The CREATE VIEW statement /////////////////////////////
//
cmd ::= CREATE(X) VIEW ids(Y) AS select(S). {
  sqliteCreateView(pParse, &X, &Y, S);
}
cmd ::= DROP VIEW ids(X). {
  sqliteDropTable(pParse, &X, 1);
}

//////////////////////// The SELECT statement /////////////////////////////////
//
cmd ::= select(X).  {
  sqliteSelect(pParse, X, SRT_Callback, 0, 0, 0, 0);
  sqliteSelectDelete(X);

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code to implement the "sqlite" command line
** utility for accessing SQLite databases.
**
** $Id: shell.c,v 1.47 2002/03/03 23:06:01 drh Exp $
*/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "sqlite.h"
#include <ctype.h>
#if !defined(_WIN32) && !defined(WIN32)

  if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 ){
    char **azResult;
    int nRow, rc;
    char *zErrMsg;
    if( nArg==1 ){
      rc = sqlite_get_table(db,
        "SELECT name FROM sqlite_master "
        "WHERE type IN ('table','view') "
        "ORDER BY name",
        &azResult, &nRow, 0, &zErrMsg
      );
    }else{
      rc = sqlite_get_table_printf(db,
        "SELECT name FROM sqlite_master "
        "WHERE type IN ('table','view') AND name LIKE '%%%q%%' "
        "ORDER BY name",
        &azResult, &nRow, 0, &zErrMsg, azArg[1]
      );
    }
    if( zErrMsg ){
      fprintf(stderr,"Error: %s\n", zErrMsg);
      free(zErrMsg);

/*
** 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.101 2002/03/03 23:06:02 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>

void sqliteAddPrimaryKey(Parse*, IdList*, int);
void sqliteAddColumnType(Parse*,Token*,Token*);
void sqliteAddDefaultValue(Parse*,Token*,int);
void sqliteEndTable(Parse*,Token*,Select*);
void sqliteCreateView(Parse*,Token*,Token*,Select*);
int sqliteViewGetColumnNames(Parse*,Table*);
void sqliteViewResetAll(sqlite*);
void sqliteDropTable(Parse*, Token*, int);
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*, int);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, int, Token*, Token*);
void sqliteDropIndex(Parse*, Token*);

# 2002 February 26
#
# 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 VIEW statements.
#
# $Id: view.test,v 1.3 2002/03/03 23:06:02 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test view-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);

do_test view-1.8 {
  db close
  sqlite db test.db
  execsql {
    SELECT * FROM v1 ORDER BY a;
  }
} {2 3 5 6 8 9}

do_test view-2.1 {
  execsql {
    CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5
  };  # No semicolon
  execsql2 {
    SELECT * FROM v2;
  }
} {x 7 a 8 b 9 c 10}
do_test view-2.2 {
  catchsql {
    INSERT INTO v2 VALUES(1,2,3,4);
  }
} {1 {view v2 may not be modified}}
do_test view-2.3 {
  catchsql {
    UPDATE v2 SET a=10 WHERE a=5;
  }
} {1 {view v2 may not be modified}}
do_test view-2.4 {
  catchsql {
    DELETE FROM v2;
  }
} {1 {view v2 may not be modified}}
do_test view-2.5 {
  execsql {
    INSERT INTO t1 VALUES(11,12,13,14);
    SELECT * FROM v2 ORDER BY x;
  }
} {7 8 9 10 11 12 13 14}
do_test view-2.6 {
  execsql {
    SELECT x FROM v2 WHERE a>10
  }
} {11}



finish_test