load "#{File.dirname(__FILE__)}/helper.rb"

class TestMicroSqlPg < Test::Unit::TestCase
  PG_URL = "pg://micro_sql:micro_sql@localhost:5432/micro_sql_test"

  def db
    @db ||= MicroSql.create(PG_URL).tap do |db|
      db.tables.each { |table_name| db.exec("DROP TABLE #{table_name}") }
    end
  end
  
  def test_assert_create_tables
    db.execute_batch <<-SQL
    CREATE TABLE t1(idx INTEGER PRIMARY KEY, b TEXT);
    CREATE TABLE t2(a INTEGER, b TEXT);
    SQL

    assert_equal %w(t1 t2), db.tables.sort
  end
  
  def test_ask_and_exec
    db.execute_batch <<-SQL
      DROP SEQUENCE IF EXISTS t1_idx_seq;
      CREATE SEQUENCE t1_idx_seq;
      CREATE TABLE t1(idx INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('t1_idx_seq'), b TEXT);
      ALTER SEQUENCE t1_idx_seq OWNED BY t1.idx
    SQL

    # -- insert
    r = db.ask("INSERT INTO t1 (idx,b) VALUES(12, '') RETURNING idx")
    assert_equal 12, r
    assert_equal 13, db.exec("INSERT INTO t1 (idx,b) VALUES(13, '') RETURNING idx")

    assert_raise(MicroSql::Error) {  
      assert_equal 1, db.exec("INSERT INTO t1 (idx,b) VALUES(13, '') RETURNING idx")
    }
    assert_raise(MicroSql::Error) {  
      assert_equal 1, db.ask("INSERT INTO t1 (idx,b) VALUES(13, '') RETURNING idx")
    }
    
    # -- update

    assert_equal 1, db.ask("UPDATE t1 SET b = 'b' WHERE idx=12")
    assert_equal 2, db.ask("UPDATE t1 SET b = 'c'")
    assert_equal 0, db.ask("UPDATE t1 SET b = 'b' WHERE idx=12345")
    
    # assert_equal 1, db.exec("INSERT INTO t1 (idx,b) VALUES(13, '')")
    # assert_equal(13, r)
    # 

    # -- delete
    assert_equal 1, db.ask("DELETE FROM t1 WHERE idx=12")
    assert_equal 0, db.exec("DELETE FROM t1 WHERE idx=12")
    assert_equal 1, db.exec("DELETE FROM t1 WHERE idx=13")
  end

  def test_type_conversion
    assert_equal(1, db.ask("SELECT 1"))
    assert_equal('1', db.ask("SELECT '1'"))
  end

  def test_row_conversion
    assert_equal(1, db.ask("SELECT 1"))
    assert_equal([1, 2], db.ask("SELECT 1, 2"))
  end

  def test_arguments
    db.ask "CREATE TABLE t1(idx INTEGER PRIMARY KEY, b TEXT)"
    assert_equal nil, db.ask("SELECT idx FROM t1 WHERE idx=?", 12)

    db.ask("INSERT INTO t1 (idx,b) VALUES(12, 'twelve')")
    db.ask("INSERT INTO t1 (idx,b) VALUES(13, 'thirteen')")
    db.ask("INSERT INTO t1 (idx,b) VALUES(0, NULL)")
  
    assert_equal 3, db.ask("SELECT COUNT(*) FROM t1")
    assert_equal 12, db.ask("SELECT idx FROM t1 WHERE idx=12")
    assert_equal 12, db.ask("SELECT idx FROM t1 WHERE idx=?", 12)
    assert_equal 12, db.ask("SELECT idx FROM t1 WHERE b=?", "twelve")
    assert_equal 12, db.ask("SELECT idx FROM t1 WHERE b=? AND idx=?", "twelve", 12)
  end

  def test_transaction
    db.ask "CREATE TABLE t1(idx INTEGER PRIMARY KEY, b TEXT)"
    
    count = db.transaction do
      db.ask("INSERT INTO t1 (idx,b) VALUES(12, 'twelve')")
      db.ask("SELECT COUNT(*) FROM t1")
    end
    assert_equal 1, count
     
    assert_equal 1, db.ask("SELECT COUNT(*) FROM t1")

    db.transaction do
      db.ask("INSERT INTO t1 (idx,b) VALUES(13, 'twelve')")
      assert_equal 2, db.ask("SELECT COUNT(*) FROM t1")
      db.rollback!
    end
    
    assert_equal 1, db.ask("SELECT COUNT(*) FROM t1")
  end

  def test_nested_transaction
    db.ask "CREATE TABLE t1(idx INTEGER PRIMARY KEY, b TEXT)"
    
    foo = db.transaction do
      db.ask("INSERT INTO t1 (idx,b) VALUES(12, 'twelve')")
      db.transaction do
        db.ask("INSERT INTO t1 (idx,b) VALUES(13, 'thirteen')")
        db.rollback!
      end
      "bar"
    end
    
    assert_equal "bar", foo
    assert_equal 1, db.ask("SELECT COUNT(*) FROM t1")
  end

  def test_insert
    assert_equal([], db.tables)
    t1 = db.table "CREATE TABLE t1(idx INTEGER PRIMARY KEY, b TEXT)"
    
    assert_equal(["t1"], db.tables)
    assert_equal("t1", t1.table_name)

    assert_equal nil, db.table("t2")

    t1.insert [1, "1"], [2, "2"]
    assert_equal [[1], [2]], db.exec("SELECT idx FROM t1")

    assert_equal(3, t1.insert([3, "3"]))

    assert_equal(4, t1.insert(:idx => 4))
  end

  def test_kv_table
    kv = db.key_value_table("kv")
    assert_equal [ "kv_b" ], db.tables

    kv["x"] = "y"
    assert_equal 1, db.ask("SELECT COUNT(*) FROM kv_b")
    assert_equal "y", kv["x"]

    kv["x"] = nil
    assert_equal 0, db.ask("SELECT COUNT(*) FROM kv_b")
    assert_equal nil, kv["x"]

    count = 0
    
    r = kv.cached "x" do "y" end 
    assert_equal(r, "y")

    r = kv.cached "x" do raise "Yo"; "y" end
    assert_equal(r, "y")
  end


  def test_kv_table_binary
    s = (0..255).to_a.pack('c*')

    kv = db.key_value_table("kv")

    kv["bin"] = s
    assert_equal 1, db.ask("SELECT COUNT(*) FROM kv_b")
    assert_equal s, kv["bin"]
  end
end