UnQL

Check-in [4893830330]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added demonstration script #1.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 489383033043d4ea919af27e920de2c173d7e65e
User & Date: drh 2011-06-30 23:22:11
Context
2011-06-30
23:40
Tweak the Makefile so that it builds out-of-the-box on Linux and Mac. check-in: 6a68e48733 user: drh tags: trunk
23:22
Added demonstration script #1. check-in: 4893830330 user: drh tags: trunk
22:24
Update adds new fields as appropriate. check-in: 05d60ec33a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added doc/demo1.txt.

































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
-- Uncomment the following line for verbose output:
.set echo

-- This is UnQL demostration script #1.  To run this script, get the xjd1
-- sources and compile them.  Then type:
--
--    ./xjd1 demo1.txt
--
-- where "demo1.txt" is the name of this script.  As you have no doubt
-- already guessed, "--" starts a comment.
--
-- Lines that begin with "." are processed by the xjd1 shell.  I emphasize
-- that the dot-commands are not part of UnQL.  They are specific to this
-- particular prototype implementation.
--
-- All other lines of input that are not dot-commands are passed through
-- into the UnQL interpreter and database engine.   We begin with a 
-- dot-command to open a new database file.  The ".new" command first deletes
-- the file named if it already exists, then opens it.  (Use .open to 
-- open a preexisting file.)
--
.new test.db

-- The database is initially empty.  We'll need to create a few collections
-- to experiment with.  The idea of automatically creating collections by
-- doing an INSERT has not yet been implemented.
--
CREATE COLLECTION abc;
CREATE COLLECTION def;

-- No insert some content.  The content can be any valid JSON string - 
-- not just a JSON object, but any valid JSON.  Note that (as with 
-- Javascript) we do not require the double-quotes around the field
-- labels of an object on input.  However, the double-quotes will be
-- inserted upon output.
--
INSERT INTO abc VALUE 1234;
INSERT INTO abc VALUE 3.141592653;
INSERT INTO abc VALUE "This is a string";
INSERT INTO abc VALUE ["this","is","an","array"];
INSERT INTO abc VALUE { type: "message",  content: "This is an object" };
INSERT INTO abc VALUE {
  type:"nested",
  content: {
    content: "nested object",
    x:1, 
    y: {str:"hi", str2:"there"},
    z:true
  }
};

-- Now query the whole table.
--
SELECT FROM abc;


-- By default, the entire document is returned.  But you can construct
-- an alternative document in between the SELECT and FROM keywords.
--
SELECT { x:abc.type, y:abc.content.x, z:abc.content.x+50 } FROM abc;

-- You can of course put a WHERE clause to restrict the search.
--
SELECT FROM abc WHERE abc.type=="message";

-- You can delete a single entry:
--
DELETE FROM abc WHERE abc==1234;
SELECT FROM abc;

-- Or delete everything all at once:
--
DELETE FROM abc;
SELECT FROM abc;

-- The WHERE clause processing is still just a skeleton.  Real soon now,
-- you'll be able to use AND and OR with complex expressions in the WHERE
-- clause in order to delete varying amounts of content.
--
-- Here's an example of an UPSERT:  Incrementing a counter on a webpage.
--
UPDATE abc SET abc.n=abc.n+1 WHERE abc.page=="/page/one"
  ELSE INSERT {page:"/page/one", n: 1, create_time: 1234567};
SELECT FROM abc;
UPDATE abc SET abc.n=abc.n+1 WHERE abc.page=="/page/one"
  ELSE INSERT {page:"/page/one", n: 1, create_time: 1234567};
SELECT FROM abc;
UPDATE abc SET abc.n=abc.n+1 WHERE abc.page=="/page/one"
  ELSE INSERT {page:"/page/one", n: 1, create_time: 1234567};
SELECT FROM abc;

-- The final demonstration for today is the ability to add new fields
-- to an object using UPDATE:
--
UPDATE abc SET abc.x = 54321, abc.y = {this:"is", a:"test"};
SELECT FROM abc;

Changes to src/shell.c.

114
115
116
117
118
119
120

121
122
123
124
125
126
127

128
129
130
131
132
133
134
...
183
184
185
186
187
188
189

190
191
192
193
194
195
196
...
479
480
481
482
483
484
485

486
487
488
489
490
491
492
...
496
497
498
499
500
501
502
503




504
505
506
507
508
509
510
...
513
514
515
516
517
518
519

520
521
522
523
524
525
526

/*
** Flag names
*/
#define SHELL_PARSER_TRACE     0x00001
#define SHELL_CMD_TRACE        0x00002
#define SHELL_ECHO             0x00004

static const struct {
  const char *zName;
  int iValue;
} FlagNames[] = {
  {  "parser-trace",   SHELL_PARSER_TRACE },
  {  "cmd-trace",      SHELL_CMD_TRACE    },
  {  "echo",           SHELL_ECHO         },

};

/*
** Convert a flag name into a mask.
*/
static int flagMask(const char *zName){
  int i;
................................................................................
static int shellTestcase(Shell *p, int argc, char **argv){
  if( argc>=2 ){
    int n = strlen(argv[1]);
    if( n>=sizeof(p->zTestCase) ) n = sizeof(p->zTestCase)-1;
    memcpy(p->zTestCase, argv[1], n);
    p->zTestCase[n] = 0;
    xjd1StringTruncate(&p->testOut);

  }
  return 0;
}

/*
** Return non-zero if string z matches glob pattern zGlob and zero if the
** pattern does not match.
................................................................................

/*
** Run a single statment.
*/
static void processOneStatement(Shell *p, const char *zCmd){
  xjd1_stmt *pStmt;
  int N, rc;

  if( p->shellFlags & SHELL_ECHO ){
    fprintf(stdout, "%s\n", zCmd);
  }
  xjd1_config(p->pDb, XJD1_CONFIG_PARSERTRACE, 
              (p->shellFlags & SHELL_PARSER_TRACE)!=0);
  rc = xjd1_stmt_new(p->pDb, zCmd, &pStmt, &N);
  if( rc==XJD1_OK ){
................................................................................
      free(zTrace);
    }
    do{
      rc = xjd1_stmt_step(pStmt);
      if( rc==XJD1_ROW ){
        const char *zValue;
        xjd1_stmt_value(pStmt, &zValue);
        if( p->zTestCase[0]==0 ){




          printf("%s\n", zValue);
        }else{
          appendTestOut(p, zValue, -1);
        }
      }
    }while( rc==XJD1_ROW );
    xjd1_stmt_delete(pStmt);
................................................................................
      appendTestOut(p, xjd1_errcode_name(p->pDb), -1);
      appendTestOut(p, xjd1_errmsg(p->pDb), -1);
    }
    fprintf(stderr, "%s:%d: ERROR: %s\n",
            p->zFile, p->nLine, xjd1_errmsg(p->pDb));
    p->nErr++;
  }

}

/*
** Process one or more database commands
*/
static void processScript(Shell *p){
  char *z, c;







>







>







 







>







 







>







 







|
>
>
>
>







 







>







114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
...
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
...
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
...
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
...
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535

/*
** Flag names
*/
#define SHELL_PARSER_TRACE     0x00001
#define SHELL_CMD_TRACE        0x00002
#define SHELL_ECHO             0x00004
#define SHELL_TEST_MODE        0x00008
static const struct {
  const char *zName;
  int iValue;
} FlagNames[] = {
  {  "parser-trace",   SHELL_PARSER_TRACE },
  {  "cmd-trace",      SHELL_CMD_TRACE    },
  {  "echo",           SHELL_ECHO         },
  {  "test-mode",      SHELL_TEST_MODE    },
};

/*
** Convert a flag name into a mask.
*/
static int flagMask(const char *zName){
  int i;
................................................................................
static int shellTestcase(Shell *p, int argc, char **argv){
  if( argc>=2 ){
    int n = strlen(argv[1]);
    if( n>=sizeof(p->zTestCase) ) n = sizeof(p->zTestCase)-1;
    memcpy(p->zTestCase, argv[1], n);
    p->zTestCase[n] = 0;
    xjd1StringTruncate(&p->testOut);
    p->shellFlags |= SHELL_TEST_MODE;
  }
  return 0;
}

/*
** Return non-zero if string z matches glob pattern zGlob and zero if the
** pattern does not match.
................................................................................

/*
** Run a single statment.
*/
static void processOneStatement(Shell *p, const char *zCmd){
  xjd1_stmt *pStmt;
  int N, rc;
  int once = 0;
  if( p->shellFlags & SHELL_ECHO ){
    fprintf(stdout, "%s\n", zCmd);
  }
  xjd1_config(p->pDb, XJD1_CONFIG_PARSERTRACE, 
              (p->shellFlags & SHELL_PARSER_TRACE)!=0);
  rc = xjd1_stmt_new(p->pDb, zCmd, &pStmt, &N);
  if( rc==XJD1_OK ){
................................................................................
      free(zTrace);
    }
    do{
      rc = xjd1_stmt_step(pStmt);
      if( rc==XJD1_ROW ){
        const char *zValue;
        xjd1_stmt_value(pStmt, &zValue);
        if( (p->shellFlags & SHELL_TEST_MODE)==0 ){
          if( once==0 && (p->shellFlags & SHELL_ECHO)!=0 ){
            printf("--------- query results ---------\n");
            once = 1;
          }
          printf("%s\n", zValue);
        }else{
          appendTestOut(p, zValue, -1);
        }
      }
    }while( rc==XJD1_ROW );
    xjd1_stmt_delete(pStmt);
................................................................................
      appendTestOut(p, xjd1_errcode_name(p->pDb), -1);
      appendTestOut(p, xjd1_errmsg(p->pDb), -1);
    }
    fprintf(stderr, "%s:%d: ERROR: %s\n",
            p->zFile, p->nLine, xjd1_errmsg(p->pDb));
    p->nErr++;
  }
  if( once ) printf("---------------------------------\n");
}

/*
** Process one or more database commands
*/
static void processScript(Shell *p){
  char *z, c;

Changes to src/update.c.

116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
                         pCmd->u.update.zName);
  sqlite3_prepare_v2(db, zSql, -1, &pReplace, 0);
  sqlite3_free(zSql);
  if( pQuery && pReplace ){
    while( SQLITE_ROW==sqlite3_step(pQuery) ){
      const char *zJson = (const char*)sqlite3_column_text(pQuery, 1);
      pStmt->pDoc = xjd1JsonParse(zJson, -1);
      if( xjd1ExprTrue(pCmd->u.del.pWhere) ){
        JsonNode *pNewDoc;  /* Revised document content */
        ExprList *pChng;    /* List of changes */
        String jsonNewDoc;  /* Text rendering of revised document */
        int i, n;

        pNewDoc  = xjd1JsonRef(pStmt->pDoc);
        pChng = pCmd->u.update.pChng;







|







116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
                         pCmd->u.update.zName);
  sqlite3_prepare_v2(db, zSql, -1, &pReplace, 0);
  sqlite3_free(zSql);
  if( pQuery && pReplace ){
    while( SQLITE_ROW==sqlite3_step(pQuery) ){
      const char *zJson = (const char*)sqlite3_column_text(pQuery, 1);
      pStmt->pDoc = xjd1JsonParse(zJson, -1);
      if( pCmd->u.del.pWhere==0 || xjd1ExprTrue(pCmd->u.del.pWhere) ){
        JsonNode *pNewDoc;  /* Revised document content */
        ExprList *pChng;    /* List of changes */
        String jsonNewDoc;  /* Text rendering of revised document */
        int i, n;

        pNewDoc  = xjd1JsonRef(pStmt->pDoc);
        pChng = pCmd->u.update.pChng;