SQLite "dot" commands

Is there a way (without resorting to SHELL) to execute the “dot” commands for SQLite?
these are commands that can be manually executed via the SQLite command line
for example

sqlite> .output filename
sqlite> .dump
sqlite> .exit

Those are not part of the sqlite database engine itself
They are part of the sqlite command line accessible application which has the sqlite database engine built into it

so - no
not unless you RUN sqlite “the app”

Thanks that is what I was afraid of…

Why is the shell not an option though?

two reasons…

  1. I want to limit anything that would need to be recoded in the future should MAS become a consideration
  2. I’d prefer to stay within a few environments as possible

visit http://sqlite.org/download.html
download the c source for the command line app
mimic what they do for “dump”

[quote=346768:@Norman Palardy]visit http://sqlite.org/download.html
download the c source for the command line app
mimic what they do for “dump”[/quote]
Uh… wading thru 2.5 meg of “C” source code isn’t productive… I “COULD” (and I guess will) write my own versions in pure XOJO, but that is what I was hoping to AVOID by leveraging something that already existed

I’d write a wrapper class around the shell, include the SQLite binary on Windows distributions (if that’s an issue), and worry about MAS later.

Maybe that’s just me.

If the sqlite exe isn’t installed on the target machine then a shell wont work
So you have to include a version as part of your app

Or convert “shell.c” (which is the entire cmd line app) into Xojo code - this is mentioned on sqlite.org
shell.c is 266 K

All the more reason to stay INSIDE the Xojo enviornment… not only am I assured that SQLite is available there, but I know what version… (Currently my Xojo install is 3.14, while my OSX is only 3.08 [go figure] )

sqlite3 comes installed on the Mac though, no?

/usr/bin/sqlite3

Yes, and as I said… it is 3.08

sorry… guess they updated it with Sierra… it is now 3.16

[quote=346774:@Kem Tekinay]sqlite3 comes installed on the Mac though, no?

/usr/bin/sqlite3[/quote]
And for Windows ? Linux ? and is it the right / same / compatible version as what Xojo has compiled in ?
And will it read encrypted db’s ? (no)

Lots of possible issues

All compelling arguments to forego the “dot” commands and implement in pure Xojo…
Tadpole already has all the routines pretty much… just a matter of stitching them together

I already mentioned including the binary with Windows, and including it on the Mac/Linux side should be trivial too if the version matters, but yes, encryption would be an issue.

I’d imagine that recreating this functionality in Xojo code will be quite a bear though.

Darn, cross-posted with Dave… again.

A “bear”? not really… Tadpole can already dump a single table as SQL insert statements…
Getting the DDL from SQLite Master is trivial…
Not much more required than that

Please report back your success and consider publishing that code separately. Many could benefit from that, I’m sure.

[quote=346780:@Kem Tekinay]
I’d imagine that recreating this functionality in Xojo code will be quite a bear though.[/quote]

I dont think so
Looking at this for < 5 minutes it looks pretty reasonable to do in xojo

This dumps one query’s results

static int run_table_dump_query(
  ShellState *p,           /* Query context */
  const char *zSelect,     /* SELECT statement to extract content */
  const char *zFirstRow    /* Print before first row, if not NULL */
){
  sqlite3_stmt *pSelect;
  int rc;
  int nResult;
  int i;
  const char *z;
  rc = sqlite3_prepare_v2(p->db, zSelect, -1, &pSelect, 0);
  if( rc!=SQLITE_OK || !pSelect ){
    utf8_printf(p->out, "/**** ERROR: (%d) %s *****/\
", rc,
                sqlite3_errmsg(p->db));
    if( (rc&0xff)!=SQLITE_CORRUPT ) p->nErr++;
    return rc;
  }
  rc = sqlite3_step(pSelect);
  nResult = sqlite3_column_count(pSelect);
  while( rc==SQLITE_ROW ){
    if( zFirstRow ){
      utf8_printf(p->out, "%s", zFirstRow);
      zFirstRow = 0;
    }
    z = (const char*)sqlite3_column_text(pSelect, 0);
    utf8_printf(p->out, "%s", z);
    for(i=1; i<nResult; i++){
      utf8_printf(p->out, ",%s", sqlite3_column_text(pSelect, i));
    }
    if( z==0 ) z = "";
    while( z[0] && (z[0]!='-' || z[1]!='-') ) z++;
    if( z[0] ){
      raw_printf(p->out, "\
;\
");
    }else{
      raw_printf(p->out, ";\
");
    }
    rc = sqlite3_step(pSelect);
  }
  rc = sqlite3_finalize(pSelect);
  if( rc!=SQLITE_OK ){
    utf8_printf(p->out, "/**** ERROR: (%d) %s *****/\
", rc,
                sqlite3_errmsg(p->db));
    if( (rc&0xff)!=SQLITE_CORRUPT ) p->nErr++;
  }
  return rc;
}

and the whole schema is dumped using

    open_db(p, 0);
    /* When playing back a "dump", the content might appear in an order
    ** which causes immediate foreign key constraints to be violated.
    ** So disable foreign-key constraint enforcement to prevent problems. */
    raw_printf(p->out, "PRAGMA foreign_keys=OFF;\
");
    raw_printf(p->out, "BEGIN TRANSACTION;\
");
    p->writableSchema = 0;
    p->showHeader = 0;
    /* Set writable_schema=ON since doing so forces SQLite to initialize
    ** as much of the schema as it can even if the sqlite_master table is
    ** corrupt. */
    sqlite3_exec(p->db, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0);
    p->nErr = 0;
    if( zLike==0 ){
      run_schema_dump_query(p,
        "SELECT name, type, sql FROM sqlite_master "
        "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
      );
      run_schema_dump_query(p,
        "SELECT name, type, sql FROM sqlite_master "
        "WHERE name=='sqlite_sequence'"
      );
      run_table_dump_query(p,
        "SELECT sql FROM sqlite_master "
        "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
      );
    }else{
      char *zSql;
      zSql = sqlite3_mprintf(
        "SELECT name, type, sql FROM sqlite_master "
        "WHERE tbl_name LIKE %Q AND type=='table'"
        "  AND sql NOT NULL", zLike);
      run_schema_dump_query(p,zSql);
      sqlite3_free(zSql);
      zSql = sqlite3_mprintf(
        "SELECT sql FROM sqlite_master "
        "WHERE sql NOT NULL"
        "  AND type IN ('index','trigger','view')"
        "  AND tbl_name LIKE %Q", zLike);
      run_table_dump_query(p, zSql, 0);
      sqlite3_free(zSql);
    }
    if( p->writableSchema ){
      raw_printf(p->out, "PRAGMA writable_schema=OFF;\
");
      p->writableSchema = 0;
    }
    sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
    sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
    raw_printf(p->out, p->nErr ? "ROLLBACK; -- due to errors\
" : "COMMIT;\
");
    p->showHeader = savedShowHeader;

There is another callback that I havent figured out how it relates but … I’ve spent < 5 minutes looking

That’s it? I’m surprised, but ok, that doesn’t look like too much.