package org.sqlite;

import static org.junit.Assert.*;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.BeforeClass;
import org.junit.Test;

/**
 * These tests check whether access to files is woring correctly and some
 * Connection.close() cases.
 */
public class ConnectionTest
{
    @BeforeClass
    public static void forName() throws Exception {
        Class.forName("org.sqlite.JDBC");
    }

    @Test
    public void readOnly() throws SQLException {
        SQLiteConfig config = new SQLiteConfig();
        config.setReadOnly(true);
        Connection conn = DriverManager.getConnection("jdbc:sqlite:", config.toProperties());
        Statement stat = conn.createStatement();
        try {
            stat.executeUpdate("create table A(id, name)");
            stat.executeUpdate("insert into A values(1, 'leo')");
        }
        catch (SQLException e) {
            return; // success
        }
        finally {
            stat.close();
            conn.close();
        }

        fail("read only flag is not properly set");
    }

    @Test
    public void foreignKeys() throws SQLException {
        SQLiteConfig config = new SQLiteConfig();
        config.enforceForeignKeys(true);
        Connection conn = DriverManager.getConnection("jdbc:sqlite:", config.toProperties());
        Statement stat = conn.createStatement();

        try {
            stat
                    .executeUpdate("create table track(id integer primary key, name, aid, foreign key (aid) references artist(id))");
            stat.executeUpdate("create table artist(id integer primary key, name)");

            stat.executeUpdate("insert into artist values(10, 'leo')");
            stat.executeUpdate("insert into track values(1, 'first track', 10)"); // OK

            try {
                stat.executeUpdate("insert into track values(2, 'second track', 3)"); // invalid reference
            }
            catch (SQLException e) {
                return; // successfully detect foreign key constraints
            }
            fail("foreign key constraint must be enforced");
        }
        finally {
            stat.close();
            conn.close();
        }

    }

    @Test
    public void openMemory() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:sqlite:");
        conn.close();
    }

    @Test
    public void isClosed() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:sqlite:");
        conn.close();
        assertTrue(conn.isClosed());
    }

    @Test
    public void openFile() throws SQLException {
        File testdb = new File("test.db");
        if (testdb.exists())
            testdb.delete();
        assertFalse(testdb.exists());
        Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
        conn.close();
        assertTrue(testdb.exists());
        testdb.delete();
    }

    @Test(expected = SQLException.class)
    public void closeTest() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:sqlite:");
        PreparedStatement prep = conn.prepareStatement("select null;");
        ResultSet rs = prep.executeQuery();
        conn.close();
        prep.clearParameters();
    }

    @Test(expected = SQLException.class)
    public void openInvalidLocation() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:sqlite:/");
        conn.close();
    }

    @Test
    public void openResource() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:sqlite::resource:org/sqlite/sample.db");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from coordinate");
        assertTrue(rs.next());
        rs.close();
        stat.close();
        conn.close();

    }

    @Test
    public void openHttpResource() throws SQLException {
        Connection conn = DriverManager
                .getConnection("jdbc:sqlite::resource:http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/java/org/sqlite/sample.db");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from coordinate");
        assertTrue(rs.next());
        rs.close();
        stat.close();
        conn.close();

    }

    @Test
    public void openJARResource() throws SQLException {
        Connection conn = DriverManager
                .getConnection("jdbc:sqlite::resource:jar:http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/resources/testdb.jar!/sample.db");
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from coordinate");
        assertTrue(rs.next());
        rs.close();
        stat.close();
        conn.close();

    }
}

@@ -196,24 +196,60 @@
 ResultSet;%0D%0A
+import java.sql.ResultSetMetaData;%0D%0A
 import java.
@@ -263,24 +263,24 @@
 Exception;%0D%0A
-
 import java.
@@ -351,16 +351,65 @@
 t.Test;%0D
+%0Aimport org.sqlite.SQLiteConfig.SynchronousMode;%0D
 %0A%0D%0A/**%0D%0A
@@ -613,32 +613,32 @@
 ws Exception %7B%0D%0A
-
         Class.fo
@@ -665,32 +665,321 @@
 BC%22);%0D%0A    %7D%0D%0A%0D%0A
+    @Test%0D%0A    public void executeUpdateOnClosedDB() throws SQLException %7B%0D%0A        Connection conn = DriverManager.getConnection(%22jdbc:sqlite:%22);%0D%0A        Statement stat = conn.createStatement();%0D%0A        conn.close();%0D%0A%0D%0A        stat.executeUpdate(%22create table A(id, name)%22);%0D%0A    %7D%0D%0A%0D%0A
     @Test%0D%0A    p
@@ -2584,16 +2584,29 @@
  detect 
+violation of 
 foreign 
@@ -2797,32 +2797,784 @@
   %7D%0D%0A%0D%0A    %7D%0D%0A%0D%0A
+    @Test%0D%0A    public void synchronous() throws SQLException %7B%0D%0A        SQLiteConfig config = new SQLiteConfig();%0D%0A        config.setSynchronous(SynchronousMode.OFF);%0D%0A        Connection conn = DriverManager.getConnection(%22jdbc:sqlite:%22, config.toProperties());%0D%0A        Statement stat = conn.createStatement();%0D%0A%0D%0A        try %7B%0D%0A            ResultSet rs = stat.executeQuery(%22pragma synchronous%22);%0D%0A            if (rs.next()) %7B%0D%0A                ResultSetMetaData rm = rs.getMetaData();%0D%0A                int i = rm.getColumnCount();%0D%0A                int synchronous = rs.getInt(1);%0D%0A                assertEquals(0, synchronous);%0D%0A            %7D%0D%0A%0D%0A        %7D%0D%0A        finally %7B%0D%0A            stat.close();%0D%0A            conn.close();%0D%0A        %7D%0D%0A%0D%0A    %7D%0D%0A%0D%0A
     @Test%0D%0A    p

add synchronous pragma test