#!/usr/bin/env python


import dbx.mysql as dbx


def get(db, order):
    print "User [%s]:" % order
    a = db.listQuery("SELECT id, name FROM User ORDER BY %s" % order)
    for x in a:
        print "%5i %s" % (x["id"], x["name"])


def createTable(db):
    if "User" in db.tables():
        print "table exists"
        return

    print "creating table"
    db.query("""
        CREATE TABLE User (
            id int NOT NULL auto_increment,
            name varchar(255) NOT NULL,
            PRIMARY KEY (id)
        )
        """)

    sql = "INSERT INTO User (name) VALUES ('%s')"
    db.query(sql % "david")
    db.query(sql % "sam")
    db.query(sql % "joe")
    db.query(sql % dbx.escape("xxx'yyy"))
    print "last record:", db.queryID()


def test():
    print "MySQL test program"
    
    test_host = "localhost"
    test_user = "test"
    test_pass = "test"
    test_db = "test"
    print "test database:", test_db

    db = dbx.MySQL(test_db, test_host, test_user, test_pass)
    print "version:", db.version()

    createTable(db)

    print "tables:"
    for t in db.tables():
        print "    %s:" % t
        for c in db.columns(t):
            if (c[db.C_NULL]):
                null = ""
            else:
                null = "NOT NULL"
            if c[db.C_DEFAULT] is None:
                default = ""
            elif type(c[db.C_DEFAULT]) is int:
                default = " DEFAULT %i" % c[db.C_DEFAULT]
            else:
                default = " DEFAULT '%s'" % c[db.C_DEFAULT]
            print "        %s %s %s%s" % \
                  (c[db.C_NAME], c[db.C_TYPE], null, default)

    get(db, "id")
    get(db, "name")

    db.close()

    db = dbx.MySQL(test_db, test_host, test_user, test_pass)
    db.query("SELECT something FROM nothing")


def main():
    try:
        test()
    except dbx.SQLError, e:
        print 'error: %s: "%s"' % (e.args[0], e.args[1])
    except dbx.Error, e:
        print "error: %s" % e.args[0]


if __name__ == "__main__":
    main()