CODEWITHSUNDEEP
c++csqlite
dlanod
dlanod

Reputation: 8980

Is it possible to programmatically convert SQLite database to SQL statements in C/C++?

I know of the existance of the .dump function in the SQLite command line tool, and Python has an iterdump command that emulates that .dump function.

Is there a standard API call or a C/C++ wrapper that provides that .dump functionality programmatically?

Upvotes: 12

Views: 4003

Answers (3)

TOC
TOC

Reputation: 4446

The API does not seem to have any dump function (https://www.sqlite.org/capi3ref.html), but you can construct your dump by:

  • Creating a new function that will use your buffer result of sqlite3_exec() or sqlite3_get_table() and dump it to a FILE *

  • Use the dump function provided in the source code of SQLite, you can find it in the (shell.c).

Edit: Adding this sample

/* TODO : This is just a sample code, modify it to meet your need */
void select_and_dump_sqlite3_table(sqlite3 *dbh)
{
    FILE    *dump_file;
    int i;
    sqlite3_stmt *stmt;

    dump_file = fopen(path_to_dump_file, "w");
    if (dump_file == NULL) {
        /* Error handling with errno and exit */
    }

    sqlite3_prepare_v2(dbh, "SELECT name, address, phone FROM Person",
                       0, &stmt, NULL);
    /* dump columns names into the file */
    for (i = 0; i < 3; i++) {
        fprintf (dump_file, "%30s | ", sqlite3_column_name(stmt, i));
    }
    printf ("\n");
  
    /* Dump columns data into the file */
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        for (i = 0; i < 3; i++) {
          fprintf (dump_file, "%30s | ", sqlite3_column_text (stmt, i));
        }
      printf ("\n");
    }
    /* We're ready to leave */
    sqlite3_finalize (stmt);
}

Upvotes: 10

SingerOfTheFall
SingerOfTheFall

Reputation: 29966

I don't know if there are pre-made tools for it, but you can implement it yourself.

First, get the schema by reading the master table. After that you will have the database schema (the table names and the columns). You will be able to automatically read all the data and construct SQL for it. That should not be too hard to implement.

Upvotes: 0

Michael
Michael

Reputation: 58457

You can do a SELECT * FROM sqlite_master to get all the tables and indices (each row has a type column that will be 'table' for tables and 'index' for indices, and an sql column that contains the sql statement used to create that table/index).

Then for each table found in sqlite_master, SELECT * from them (each sqlite_master row has a name column) and write out all the data in the tables.

See the SQLite FAQ and command line shell pages for more info.

Upvotes: 3

Related Questions