#!/usr/bin/env python
import optparse
import psycopg2
import sqlite3


def main(sqlite_cursor, pg_cursor):
    sqlite_cursor.execute('SELECT name FROM sqlite_master '
        'WHERE type = ?', ('table',))
    for table, in sqlite_cursor.fetchall():
        pg_cursor.execute('SELECT relname FROM pg_class '
            'WHERE relkind = %s AND relname = %s', ('r', table,))
        if not bool(pg_cursor.rowcount):
            continue
        sqlite_cursor.execute('SELECT * from "%s"' % table)
        columns = [x[0] for x in sqlite_cursor.description]
        query = ('INSERT INTO "%s" (%s) VALUES (%s)'
            % (table, ','.join('"%s"' % x for x in columns),
                ','.join(('%s',) * len(columns))))
        pg_cursor.executemany(query, sqlite_cursor)
        pg_cursor.execute('SELECT MAX(id) FROM "%s"' % table)
        max_id, = pg_cursor.fetchone()
        if table.endswith('__history'):
            sequence = table + '___id_seq'
        else:
            sequence = table + '_id_seq'
        pg_cursor.execute('ALTER SEQUENCE "%s" RESTART WITH %%s'
            % sequence, (max_id,))

if __name__ == '__main__':
    parser = optparse.OptionParser(version='0.1')
    parser.add_option('--sqlite', dest='file', help='SQLite database file')
    parser.add_option('--pg', dest='dsn', help='dsn for PostgreSQL')

    opt, args = parser.parse_args()
    pg_conn, sqlite_conn = None, None
    pg_cursor, sqlite_cursor = None, None
    try:
        pg_conn = psycopg2.connect(opt.dsn)
        pg_cursor = pg_conn.cursor()
        sqlite_conn = sqlite3.connect(opt.file,
            detect_types=sqlite3.PARSE_DECLTYPES)
        sqlite_cursor = sqlite_conn.cursor()
        main(sqlite_cursor, pg_cursor)
        pg_conn.commit()
    finally:
        if pg_cursor:
            pg_cursor.close()
        if pg_conn:
            pg_conn.close()
        if sqlite_cursor:
            sqlite_cursor.close()
        if sqlite_conn:
            sqlite_conn.close()