0

In the spirit of test-driven design I want to test whether the tables get properly created in my SQLite3 database. For that purpose I want to list the table names via an SQL query.

I created the tables with:

public static void setupTables(){
    try(Statement stmt = con.createStatement()) {
        String sql_student = ("CREATE TABLE IF NOT EXISTS " + STUDENT_TABLE + "(\n "
                + ID + " integer PRIMARY KEY,\n "
                + FIRSTNAME + " text,\n "
                + LASTNAME + " text\n"
                + ");");
        String sql_teacher = ("CREATE TABLE IF NOT EXISTS " + TEACHER_TABLE + "(\n "
                + ID + " integer PRIMARY KEY,\n "
                + FIRSTNAME + " text,\n "
                + LASTNAME + " text\n"
                + ");");

        stmt.execute(sql_student);
        stmt.execute(sql_teacher);

        stmt.close();

    }catch(Exception e){
        e.printStackTrace();
    }
}

What's the query I need for my test? Do I need to be concerned about order, or does the order in which the tables get created gurantee that they will always stored in the same order?

Christian
  • 25,249
  • 40
  • 134
  • 225

1 Answers1

1

1) ATTACH mydb.db as my_db 2) SELECT name FROM my_db.sqlite_master where type='table'; 3) For temporary tables: SELECT name FROM sqlite_temp_master WHERE type='table';

Hope it will help!

Dina Bogdan
  • 4,345
  • 5
  • 27
  • 56
  • Is there a reason the ATTACH is needed? It sounds to me like it results in the database to have to be a file and that it won't work with an in-memory database. – Christian Feb 24 '18 at 09:08
  • Only if you don't already have your database attached. If you don't have it attached, then the helper functions from .tables and .schema will query the SQLITE_MASTER table for the main database. – Dina Bogdan Feb 24 '18 at 09:12
  • I would expect that a connection that I open in Java is specific to a certain database and that I have multiple connection options if I have multiple databases. Is there a good reason for structuring things in Java in a way where you always use attach? – Christian Feb 24 '18 at 09:15
  • I don't think that every SQLConnection that you've opened in java it's not attached to the correct database. So, I think that you can skip the first step. – Dina Bogdan Feb 24 '18 at 09:17
  • Why did you downvoted me? – Dina Bogdan Feb 24 '18 at 10:51
  • I didn't. I think it's most likely that CL cast that vote. – Christian Feb 24 '18 at 20:52
  • Can you please upvote me? Or i delete the answer... – Dina Bogdan Feb 24 '18 at 20:53