#!/usr/bin/env python

# Makes PostgreSQL CREATE TABLE and COPY FROM statements from a CSV header

import csv

import os.path

import sys

sys.path.append(os.path.dirname(__file__)+"/../lib")

import csvs

import opts

import sql

def esc_name(name):

    return sql.esc_name_by_engine('PostgreSQL', name, preserve_case=True)

def main():

    # Usage

    env_names = []

    def usage_err():

        raise SystemExit('Usage: '+opts.env_usage(env_names)+' '+sys.argv[0]

            +' <header >ddl')

    # Get config from env vars

    schema = opts.get_env_var('schema', None, env_names)

    table = opts.get_env_var('table', None, env_names)

    if schema == None or table == None: usage_err()

    # Get format info

    info = csvs.stream_info(sys.stdin, parse_header=True)

    dialect = info.dialect

    out = sys.stdout

    qual_table = esc_name(schema)+'.'+esc_name(table)

    # Write CREATE TABLE statement

    out.write('CREATE TABLE '+qual_table+' (\n')

    out.write('    row_num serial NOT NULL\n')

    for col_name in info.header:

        out.write('    , '+esc_name(col_name)+' text\n')

    out.write(');\n')

    # Write COPY FROM statement

    statement = ('COPY '+qual_table+" FROM STDIN DELIMITER '" +dialect.delimiter

        +"' NULL E'\N'")

    if not csvs.is_tsv(dialect):

        statement += ' CSV'

        if dialect.quoting != csv.QUOTE_NONE:

            statement += " QUOTE '"+dialect.quotechar+"'"

            if dialect.doublequote:

                statement += " ESCAPE '"+dialect.quotechar+"'"

    statement += ';\n'

    out.write(statement)

main()