Scripting SQLite with Dot Commands – Guide

scriptingsqlite

Is it possible to write scripts that contain SQLite dot commands ( vis. .read file.sql; .separator ,; .import file.csv; )?

I'm building and repeatedly rebuilding an SQLite database and need to type in roughly twenty four dot command statements every time I rebuild the database. It would be really nice if I could put those commands in a script and have SQLite read them.

Is there a way to put dot commands ( not SQL statements ) into a script and have SQLite run them?

I'm on Mac OS X using bash.

Best Answer

I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately.

The gist of it? Put your commands in a text file and direct it to SQLite using the input file descriptor, or just script everything in a bash script.

First Method:

sqlite3 database.db < commands.txt

Second Method:

#!/bin/bash --
sqlite3 -batch $1 <<"EOF"
CREATE TABLE log_entry ( <snip> );
.separator "\t"
.import logfile.log log_entry
EOF

And then on the command line:

import.sh database.db

Related Solutions

Postgresql – Advice on scripting a “whitewashed” and downsized development db from main db

What you're referring to is usually called Test Data Management. There are several commercial solutions, e.g. IBM Optim Test Data Management (disclosure: I work for IBM, but don't speak for them). I'm not aware of any free products that can perform that function, so I'm afraid you'll have to stick to your Python scripts unless you have deep pockets.

On a side note, I would suggest using the same database platform, i.e. PostgreSQL, for both production and development -- it will probably save you some troubleshooting time later.

SQL Server – Scripting an Application Role with Hashed Password

You can connect using the DAC (Dedicated Administrator Connection), and pull the password column from sys.sysowners. First, connect using:

ADMIN:Server\Instance

Then:

SELECT password_hash = [password]
  FROM sys.sysowners
  WHERE name = N'MyAppRole';

This view is only visible when using the DAC, and the column is not exposed in the parent view that is visible (sys.database_principals). Please be careful with the DAC, of course.

That all said, this doesn't help you. CREATE APPLICATION ROLE is much different from CREATE LOGIN, in that you can't supply a hashed password, only plain text. And don't even think about reverse engineering the hashed value, because modern versions of SQL Server use elaborate methods to encrypt a password. In fact if you try this yourself you will see that a different hash is created every time, even in the same statement:

SELECT PWDENCRYPT(N'foo'), PWDENCRYPT(N'foo');

Results (on my machine, this time), noting that yours will vary:

0x0200185968C35F22AF70...   0x0200D6C77A1D84A8467F...

So, I would recommend either:

Storing the application password in source control somewhere, or wherever you store other system passwords currently, and using that to generate the script to deploy the app roles to another server (or source control the entire CREATE APPLICATION ROLE script); or,
Using regular roles instead of application roles.