sqllite

SQLITE_DB_PATH=$1
PG_DB_NAME=$2
PG_USER_NAME=$3

SQLITE_DUMP_FILE="sqlite_data.sql"

sqlite3 $SQLITE_DB_PATH .dump > $SQLITE_DUMP_FILE

# PRAGMAs are specific to SQLite3.
sed -i '/PRAGMA/d' $SQLITE_DUMP_FILE
# Convert sequences.
sed -i '/sqlite_sequence/d ; s/integer PRIMARY KEY AUTOINCREMENT/serial PRIMARY KEY/ig' $SQLITE_DUMP_FILE
# Convert column types.
sed -i 's/datetime/timestamp/g ; s/integer[(][^)]*[)]/integer/g ; s/text[(]\([^)]*\)[)]/varchar(\1)/g' $SQLITE_DUMP_FILE

createdb -U $PG_USER_NAME $PG_DB_NAME
psql $PG_DB_NAME $PG_USER_NAME < $SQLITE_DUMP_FILE

# Update Postgres sequences.
psql $PG_DB_NAME $PG_USER_NAME -c "\ds" | grep sequence | cut -d'|' -f2 | tr -d '[:blank:]' |
while read sequence_name; do
  table_name=${sequence_name%_id_seq}

  psql $PG_DB_NAME $PG_USER_NAME -c "select setval('$sequence_name', (select max(id) from $table_name))"
done
====
sed -i '/TINYINT/INTEGER/g' $SQLITE_DUMP_FILE
sed -i '/sqlite_sequence/d ; s/integer NOT NULL PRIMARY KEY AUTOINCREMENT/serial NOT NULL PRIMARY KEY/ig' $SQLITE_DUMP_FILE

#sqllite.sh
-------
   DRUG_TARGETS_ORG
 "name","type"
"target_drugs","TEXT"
"uniprot_id","TEXT"
"Organism","TEXT"


     ENSB_UNIPROT
"name","type"
"ensemble_id","TEXT"
"uniprot_id","TEXT"

========


CREATE TABLE DRUG_TARGETS_ORG(
   ID INT PRIMARY KEY     NOT NULL,
   TARGET_DRUGS          TEXT    NOT NULL,
   UNIPROT_ID           TEXT    NOT NULL,
   ORGANISM            TEXT    NOT NULL
);

CREATE TABLE / PRIMARY KEY will create implicit index "drug_targets_org_pkey" for table "drug_targets_org"
CREATE TABLE


CREATE TABLE ENSB_UNIPROT(
   ID INT PRIMARY KEY     NOT NULL,
   ENSEMBLE_ID        TEXT    NOT NULL,
   UNIPROT_ID           TEXT    NOT NULL
);

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ensb_uniprot_pkey" for table "ensb_uniprot"
CREATE TABLE
=====

COPY wheat FROM 'wheat_crop_data.csv' DELIMITER ';' CSV HEADER