SQLite

Check-in [2b3511eca7]
Login

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

Overview
Comment:Add pattern matching to the .table command (CVS 129)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2b3511eca7e562ef2428cec2f7eeca1d26b6c1c8
User & Date: drh 2000-08-08 20:19:09.000
Context
2000-08-09
17:17
bug fix (CVS 130) (check-in: e8882dac23 user: drh tags: trunk)
2000-08-08
20:19
Add pattern matching to the .table command (CVS 129) (check-in: 2b3511eca7 user: drh tags: trunk)
2000-08-04
14:56
:-) (CVS 128) (check-in: d53cccda4f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/shell.c.
︙︙
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains code to implement the "sqlite" command line
** utility for accessing SQLite databases.
**
** $Id: shell.c,v 1.19 2000/08/02 13:47:42 drh Exp $
*/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "sqlite.h"
#include <unistd.h>
#include <ctype.h>








|








20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains code to implement the "sqlite" command line
** utility for accessing SQLite databases.
**
** $Id: shell.c,v 1.20 2000/08/08 20:19:09 drh Exp $
*/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "sqlite.h"
#include <unistd.h>
#include <ctype.h>
︙︙
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405

  ".mode MODE             Set mode to one of \"line\", \"column\", "
                                      "\"list\", or \"html\"\n"
  ".mode insert TABLE     Generate SQL insert statements for TABLE\n"
  ".output FILENAME       Send output to FILENAME\n"
  ".output stdout         Send output to the screen\n"
  ".schema ?TABLE?        Show the CREATE statements\n"
  ".separator STRING      Change separator string for \"list\" mode\n"
  ".tables                List names all tables in the database\n"
  ".timeout MS            Try opening locked tables for MS milliseconds\n"
  ".width NUM NUM ...     Set column widths for \"column\" mode\n"
;

/*
** If an input line begins with "." then invoke this routine to
** process that line.








|








391
392
393
394
395
396
397
398
399
400
401
402
403
404
405

  ".mode MODE             Set mode to one of \"line\", \"column\", "
                                      "\"list\", or \"html\"\n"
  ".mode insert TABLE     Generate SQL insert statements for TABLE\n"
  ".output FILENAME       Send output to FILENAME\n"
  ".output stdout         Send output to the screen\n"
  ".schema ?TABLE?        Show the CREATE statements\n"
  ".separator STRING      Change separator string for \"list\" mode\n"
  ".tables ?PATTERN?      List names of tables matching a pattern\n"
  ".timeout MS            Try opening locked tables for MS milliseconds\n"
  ".width NUM NUM ...     Set column widths for \"column\" mode\n"
;

/*
** If an input line begins with "." then invoke this routine to
** process that line.
︙︙
570
571
572
573
574
575
576
577
578
579
580
581











582
583
584
585
586
587
588

  if( c=='s' && strncmp(azArg[0], "separator", n)==0 && nArg==2 ){
    sprintf(p->separator, "%.*s", (int)ArraySize(p->separator)-1, azArg[1]);
  }else

  if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 ){
    struct callback_data data;
    char *zErrMsg = 0;
    static char zSql[] = 
      "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
    memcpy(&data, p, sizeof(data));
    data.showHeader = 0;
    data.mode = MODE_List;











    sqlite_exec(db, zSql, callback, &data, &zErrMsg);
    if( zErrMsg ){
      fprintf(stderr,"Error: %s\n", zErrMsg);
      free(zErrMsg);
    }
  }else









|
<



>
>
>
>
>
>
>
>
>
>
>








570
571
572
573
574
575
576
577

578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598

  if( c=='s' && strncmp(azArg[0], "separator", n)==0 && nArg==2 ){
    sprintf(p->separator, "%.*s", (int)ArraySize(p->separator)-1, azArg[1]);
  }else

  if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 ){
    struct callback_data data;
    char *zErrMsg = 0;
    char zSql[1000];

    memcpy(&data, p, sizeof(data));
    data.showHeader = 0;
    data.mode = MODE_List;
    if( nArg==1 ){
      sprintf(zSql,
        "SELECT name FROM sqlite_master "
        "WHERE type='table' "
        "ORDER BY name");
    }else{
      sprintf(zSql,
        "SELECT name FROM sqlite_master "
        "WHERE type='table' AND name LIKE '%%%.100s%%' "
        "ORDER BY name", azArg[1]);
    }
    sqlite_exec(db, zSql, callback, &data, &zErrMsg);
    if( zErrMsg ){
      fprintf(stderr,"Error: %s\n", zErrMsg);
      free(zErrMsg);
    }
  }else
︙︙
Changes to src/shell.tcl. 
1
2
3
4
5


6
7
8
9
10
11
12

#!/usr/bin/wish
#
# A GUI shell for SQLite
#



############################################################################
# A console widget for Tcl/Tk.  Invoke console:create with a window name,
# a prompt string, and a title to get a new top-level window that allows 
# the user to enter tcl commands.  This is mainly useful for testing and
# debugging.
#
# Copyright (C) 1998, 1999 D. Richard Hipp






>
>








1
2
3
4
5
6
7
8
9
10
11
12
13
14

#!/usr/bin/wish
#
# A GUI shell for SQLite
#

# The following code is slighly modified from the original.  See comments
# for the modifications...
############################################################################
# A console widget for Tcl/Tk.  Invoke console:create with a window name,
# a prompt string, and a title to get a new top-level window that allows 
# the user to enter tcl commands.  This is mainly useful for testing and
# debugging.
#
# Copyright (C) 1998, 1999 D. Richard Hipp
︙︙
273
274
275
276
277
278
279

280
281






282
283
284
285
286
287
288

  $w insert end \n
  $w mark set out end
  if {$v(prior)==""} {
    set cmd $line
  } else {
    set cmd $v(prior)\n$line
  }

  if {[info complete $cmd]} {
    set rc [catch {uplevel #0 $cmd} res]






    if {![winfo exists $w]} return
    if {$rc} {
      $w insert end $res\n err
    } elseif {[string length $res]>0} {
      $w insert end $res\n ok
    }
    set v(prior) {}








>
|
|
>
>
>
>
>
>








275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297

  $w insert end \n
  $w mark set out end
  if {$v(prior)==""} {
    set cmd $line
  } else {
    set cmd $v(prior)\n$line
  }
##### Original
# if {[info complete $cmd]} {    }
#   set rc [catch {uplevel #0 $cmd} res]
##### New
  global DB 
  if {[$DB complete $cmd]} {
    set CODE {}
    set rc [catch {$DB eval $cmd RESULT $CODE}]
##### End Of Changes
    if {![winfo exists $w]} return
    if {$rc} {
      $w insert end $res\n err
    } elseif {[string length $res]>0} {
      $w insert end $res\n ok
    }
    set v(prior) {}
︙︙
Changes to www/changes.tcl.
︙︙
12
13
14
15
16
17
18





19
20
21
22
23
24
25

}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}






chng {2000 Aug 4} {
<li>Documentation updates</li>
<li>Added "busy" and "timeout" methods to the Tcl interface</li>
}

chng {2000 Aug 3} {








>
>
>
>
>








12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2000 Aug 8} {
<li>Added pattern matching to the ".table" command in the "sqlite"
command shell.</li>
}

chng {2000 Aug 4} {
<li>Documentation updates</li>
<li>Added "busy" and "timeout" methods to the Tcl interface</li>
}

chng {2000 Aug 3} {
︙︙
Changes to www/sqlite.tcl. 
1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.12 2000/08/04 13:49:03 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








1
2
3
4
5
6
7
8
9
10
11

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: sqlite.tcl,v 1.13 2000/08/08 20:19:09 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
395
396
397
398
399
400
401
402

















403
404
405
406
407
408
409

<blockquote><pre>
SELECT sql FROM sqlite_master
WHERE tbl_name LIKE '%s' AND type!='meta'
ORDER BY type DESC, name
</pre></blockquote>

<p>The <b>%s</b> in the query above is replaced by the argument
to ".schema", of course.</p>


















<h2>Converting An Entire Database To An ASCII Text File</h2>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite</b>.</p>









|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426

<blockquote><pre>
SELECT sql FROM sqlite_master
WHERE tbl_name LIKE '%s' AND type!='meta'
ORDER BY type DESC, name
</pre></blockquote>

<p>The <b>%s</b> in the query above is replaced by the argument
to ".schema", of course.  Notice that the argument to the ".schema"
command appears to the right of an SQL LIKE operator.  So you can
use wildcards in the name of the table.  For example, to get the
schema for all tables whose names contain the character string
"abc" you could enter:</p>}

Code {
sqlite> (((.schema %abc%)))
}

puts {
<p>
Along these same lines,
the ".table" command also accepts a pattern as its first argument.
If you give an argument to the .table command, a "%" is both
appended and prepended and a LIKE clause is added to the query.
This allows you to list only those tables that match a particular
pattern.</p>

<h2>Converting An Entire Database To An ASCII Text File</h2>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite</b>.</p>
︙︙