import os
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.ext.sqlsoup import SqlSoup
import logging
import sys
from sqlalchemy.ext.sqlsoup import Session
from sqlalchemy.orm import sessionmaker



logging.basicConfig(
    format="%(message)s",
    level=logging.DEBUG,
    stream=sys.stdout)


logger = logging.getLogger(__name__)

db_name = 'simple.db'

#delete db_name if it already exists
if os.path.isfile(db_name):
    logger.debug(os.linesep.join([
                "\ndeleting db \"%s\"" % db_name,
                "since it already exists"]))
    os.remove(db_name)

url = 'sqlite:///%s' % ( db_name)
print "url: \"%s\"" % url

db = SqlSoup(url)

db.engine.echo = True


#create the file_route table
file_route_table = Table(
    'file_routes', db._metadata.metadata,
    Column('id', Integer, primary_key=True,autoincrement=True),
    Column('src_path', String()),
    Column('dest_path', String()))


file_route_table.create()

#db._metadata.metadata.create_all()

print "inserting"
ob = db.file_routes.insert(
    src_path='build_dir/htmldocs/index.html',
    dest_path='htmldocs/index.html')



# Session = sessionmaker(
#     bind=db.engine, autoflush=True, transactional=True)

# session = Session()

# session.save(ob)

# session.commmit()

#import pdb; pdb.set_trace()

#i = db._metadata.tables['file_routes'].insert()
#i.execute({ 'id' : 1,
#            'src_path' : 'build_dir/htmldocs/index.html',
#           'dest_path' : 'htmldocs/index.html'})


print "before flush"

#import pdb; pdb.set_trace()
db.flush()
#db.expunge_all()
#import pdb; pdb.set_trace()
#session = Session()
#session.flush()
#session.close()

print "done"