IDatabase

IDatabase is the top level object that represents the whole database that you will be working on. To get an instance of IDatabase use the following code:

IDatabase db = DbQuery.getDatabase("MyDb");
...

The IDatabase object is always cached; therefore, there's no need to reference it outside. Calling DbQuery.getDatabase("MyDb") will suffice and should not come with overhead processing.

#Using IDatabase In order to use IDatabase, you may need to "open" it first by calling IDatabase.open()

int dbVersion = ...
String dbName = ...

IDatabase db = DbQuery.getDatabase(dbName);
db.open(dbVersion, new SQLiteBuilder(){
   // required
   @Override 
   public Context getContext(){
       return getApplicationContext();
   }

   @Override
   public void onModelCreate(IDatabase db, Modeling modeling){
       ...
   }

   // optional
   @Override 
   public boolean onUpgrade(IDatabase db, int oldVersion, int newVersion){
       ...
   }

   // optional
   @Override
   public boolean onDowngrade(IDatabase db, int oldVersion, int newVersion){
       ...
   }

});

Important You only need to call IDatabase.open() once. However, the open() call must be made before any SQL transaction is called (i.e: IDatabase.get(),IDatabase.raw(), etc...)

#Pre-loaded .db/.sqlite file DbQuery now supports side loading SQLite file externally. To be able to achieve this, use the overloaded open() with the absolute path to where the file is:

File dbFile = ...
IDatabase db = DbQuery.getDatabase("Chinook.sqlite");
db.open(1, dbFile.getAbsolutePath(), new SQLiteBuilder() {
	@Override
	public Context getContext() {
	      return PreloadedDatabaseTest.this.getContext();
	}

	@Override
	public void onModelCreate(IDatabase database, IDatabase.Modeling modeling) {
              ...
	}
});

#IConfig It is important configure your database before calling open(). Although not all, most of the configuration must take before the call to open() or otherwise the changes will not take effect.

• IConfig.setForeignKeySupport() must be called before the open() or else otherwise the changes will not take any effects.
• IConfig.setIdNamingConvention() and IConfig.setAppendTableName() would work anywhere within your code

The best practice is to always configure your database first:

IDatabase db = ...
// configure first
db.getConfig().setIdNamingConvnetion("Id");
db.getConfig().setForeignKeySupport(true);  // enable foreign key
// then call open
db.open(dbVersion, new SQLiteBuilder(){
   ...
});

#Column Id (Naming Convention) For DbQuery to correctly query Id, there's a naming convention needs to be followed. DbQuery always assume (by default) that every table uses column 'Id' as the identifier or Id. However, there's a configuration to change the default behavior on IConfig.

IDatabase db = DbQuery.getDatabase("MyDb");
db.getConfig().setIdNamingConvention("OID");

This code will tell DbQuery to assume that every table uses column OID instead of the default value Id as their column Id. For some database, they may have each table's name appended. For example CustomerId in Customer table, ProductId in Product table. This also can be configured by doing the following:

IDatabase db = DbQuery.getDatabase("MyDb");
db.getConfig().setIdNamingConvention("Id");
db.getConfig().setAppendTableNameForId(true);

This will tell DbQuery to append table name before the idNamingConvention which in this case was Id.

#raw() and execSql() If you ever need to run SQL from the IDatabase, you may use: IDatabase.execSql(String sql) or (the more preferred way) IDatabase.execSql(String sql, Object... args)

To run a complex SQL query and returns, you may use: IDatabase.raw(String sql) or (the more preferred way) IDatabase.raw(String sql, Object... selectionArgs)

#open() and close() You only need to open() once. Best practice is to have open() somewhere in your Application.onCreate().

The close() method will free any resources. After this point, your IDatabase object is useless. You have to call open() to be able to conduct SQL operations. To close the database simply use:

IDatabase db = ...
db.close();

Most of the time, you won't need to close the database. Best practice is to have this method in your Application.onTerminate() when Android terminates your program.

NOTE It is NOT recommended to call open() and close() every time you want to make simple SQL operations. open() method is pretty expensive to call and will require some processing time.

#Modeling At first, you need to model your tables inside the database. To achieve this, DbQuery introduces Modeling object.

The purpose of Modeling is, again, to minimize the need to write SQL. So, there will be no need to write appending Strings like this:

String createTable = "CREATE TABLE Persons " +
                     "(Id      INTEGER primary key autoincrement) " +
                     "(Name    TEXT    not null) " +
                     "(Address TEXT ) " +
                     "(AGE     INTEGER); " +
                     // constraints..
                     "CREATE INDEX Persons_Name_IDX ON Persons(Name)";

With Modeling object, you can neatly write:

...
modeling.add("Persons")
        .addPrimaryKey("Id")
        .add("Name", "TEXT", "not null")
        .add("Address", "TEXT")
        .add("Age", "INTEGER")
        .index("Name");
...

Modeling is part of Migration that is passed inside the Builder.onCreateModel() See a complete Modeling documentation in: https://github.com/bingzer/DbQuery/wiki/Migration#modeling

#Migration See a complete Migration documentation (upgrade/downgrade/create) in: https://github.com/bingzer/DbQuery/wiki/Migration

#Transaction See a complete Transaction documentation in: https://github.com/bingzer/DbQuery/wiki/Transaction