create a shell script to run sqlite commands from

Question

i want to create a new db of sqlite using A SHELL SCRIPT on an ubuntu10.10 OS....Any ideas??

I tried 'create.sh' file with following code...

#!/bin/bash
sqlite ex3.db
create table t1(f1 integer primary key,f2 text)

than run a ./create.sh from termminal but it leads me to sqlite> prompt...I dont see created DB ex3 anywhere..

Please help ...

Answer 1

Example of CREATE and INSERT of create.sh:

#!/bin/bash
DB_NAME=ex3.sqlite
DB_TABLE=t1

sqlite3 $DB_NAME << EOF

DROP TABLE IF EXISTS $DB_TABLE;

CREATE TABLE $DB_TABLE (
    "f1" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL,
    "f2" TEXT NOT NULL DEFAULT "f2-text"
);

INSERT INTO $DB_TABLE (f1, f2) VALUES (1, "text 1");
INSERT INTO $DB_TABLE (f1, f2) VALUES (2, "text 2");

EOF

Answer 2

Can you use the param -line on sqlite3, for example:

$sqlite3 -line teste.db 'create table table_name (field_name integer)'
$sqlite3 -line teste.db 'insert into table_name (123)'
$sqlite3 -line teste.db 'select * from table_name'

Answer 3

#!/bin/bash

sqlite3 ex3.db "create table t1(f1 integer primary key,f2 text)"

should work I think, unfortunately, not able to check right now.

Answer 4

You want to feed your SQL DDL commands to SQLite through the standard input:

#!/bin/bash
echo 'create table t1(f1 integer primary key,f2 text);' | sqlite ex3.db

Answer 5

One more way to accomplish this can be using SQL_ENTRY_TAG_N

#!/bin/bash
sqlite3 mydatabase <<SQL_ENTRY_TAG_1
SELECT * FROM sqlite_master;
SQL_ENTRY_TAG_1

see snippet for details