AnnotatedSQL lib - automatic database generation in Android

When developing an application for Android, we often write scripts with our hands to create the circuit.
Everything would be fine when you need to do this once, but when the application develops, you often have to change the database.
And when it is spread over several classes, then there are problems, somewhere I forgot to add / remove a column, change the type, and so on. Also copy-paste “helps”: added a column - forgot to put a comma.

Just to solve these problems this library was invented.



AnnotatedSQL is a library that will generate code for creating an annotation database. Annotations are not runtime , but are processed by the preprocessor at compile time. Thus, we do not affect the project and the final apk.

Actually, the lib consists of two pieces: a jar with annotations and a preprocessor.
Annotations are put in the libs folder of the project.
Well, the use of the preprocessor depends on the IDE and the method of building the project.

If you use Eclipse , then copy the plugin to the plugins folder and restart eclipse if necessary. then we go to the settings of the Java Compiler -> Annotation Processing project and select the folder where to generate the code there. Obviously you need to put the standard gen folder . Next, go to Factory Path and select our plugin. That's it.

For IDEA, the plugin did not compile, litter.

For use with ant - you just need to add a preprocessor in the classpath, do it something like this
ant clean release -cp ../com.annotatedsql.AnnotatedSQL_1.0.12.jar


Using

Let's move on to more technical things. So we know how to connect the lib, but what does it do?
As I have already said, it generates a database from annotations, or rather creates a class with the help of which the database will be generated.
As usual, for use in the code, we describe the plates as interfaces with the name of the table and the columns in it.

For example, we will have an application that should display data on the results of sports competitions. Take a football match for now.
Obviously, we should have several tags in the database. This is a team, result and championship. We will describe them.
I usually describe all interfaces within one class, let's call it FStore, for example. In addition to the description of the tablets, it contains the name of the database, its version and a couple of utility methods.

public class FStore {
	public static final String DB_NAME = "fmanager";
	public static final int DB_VERSION = 34;
..........
       public static interface TeamTable{
		String TABLE_NAME = "team_table";
		String ID = "_id";
		String TITLE = "title";
		String CHEMP_ID = "chemp_id";
		String IS_FAV = "is_fav";
       }
       public static interface ChempTable{
		String TABLE_NAME = "chemp_table";
		String CONTENT_PATH = "chemps";
		String ID = "_id";
		String TITLE = "title";
       }
       public static interface ResultsTable{
		String CONTENT_PATH = "results";
		String PATH_VIEW = "results_view";
		String TABLE_NAME = "result_table";
		String ID = "_id";
		String TEAM_ID = "team_id";
		String POINTS = "points";
		String CHEMP_ID = "chemp_id";
		String GAMES = "games";
		String WINS = "wins";
		String TIE = "tie";
		String LOSE = "lose";
		String BALLS = "balls";
		String GOALS = "goals";
       }
........
}

While we do not pay attention to CONTENT_PATH and all sorts of PATH_VIEW. These are constants for accessing the content provider.
So, we present the amount of manual work to create the circuit.
In addition, in order to get the result in a readable form, we need to join the plates on top of each other. This can be done in the content provider, but I prefer to use View , here is another big sql piece for writing.

To facilitate our work, this lib was written. So let's get started.

Schema

FStore - mark with the annotation Schema ("SqlSchema") and set the name of the class that will contain the code. the class will be generated in the same package where the FStore lies

@Schema("SqlSchema")
public class FStore {

Table, Index, PrimaryKey

Label description is marked with Table annotation and set table name

@Table(ChempTable.TABLE_NAME)
public static interface ChempTable{
................
@Table(TeamTable.TABLE_NAME)
public static interface TeamTable{
...............
@Table(ResultsTable.TABLE_NAME)
@Index(name = "chemp_index", columns = ResultsTable.CHEMP_ID)
@PrimaryKey(collumns = {ResultsTable.TEAM_ID, ResultsTable.CHEMP_ID})
public static interface ResultsTable{

As we see on the table, we can hang the creation of an index, and a complex key. Everything seems to be simple and requires no explanation.

Column, PrimaryKey, Autoincrement, NotNull

These annotations are for fields, and are obvious to use too.

	@Table(TeamTable.TABLE_NAME)
	public static interface TeamTable{
		String TABLE_NAME = "team_table";
		@PrimaryKey
		@Column(type = Type.INTEGER)
		String ID = "_id";
		@NotNull
		@Column(type = Type.TEXT)
		String TITLE = "title";
		@Column(type = Type.INTEGER)
		@NotNull
		String CHEMP_ID = "chemp_id";
		@Column(type = Type.INTEGER, defVal="0")
		String IS_FAV = "is_fav";
	}

Simpleview

And the last, very important, element of the system and not quite trivial is SimpleView .
It provides basic functionality for creating simple views. There is still an INNER JOIN, but I will add others.

	@SimpleView(ResultView.VIEW_NAME)
	public static interface ResultView{
		String VIEW_NAME = "result_view";
		@From(ResultsTable.TABLE_NAME)
		String TABLE_RESULT = "table_result";
		@Join(srcTable = TeamTable.TABLE_NAME, srcColumn = TeamTable.ID, destTable = ResultView.TABLE_RESULT, destColumn = ResultsTable.TEAM_ID)
		String TABLE_TEAM = "table_team";
		@Join(srcTable = ChempTable.TABLE_NAME, srcColumn = ChempTable.ID, destTable = ResultView.TABLE_RESULT, destColumn = ResultsTable.CHEMP_ID)
		String TABLE_CHEMP = "table_chemp";
	}

Consider the annotations inside our view:
From - this is the plate from which we will do from :) Important - next, when joining, you need to use not the table name, but this constant.

Join - actually tables of joins. In our case, it is necessary to join the table of the team and the championship.

srcTable is the source table.
destTable is the new name for the from / join table in the view. In our case

String TABLE_RESULT = "table_result";

Another very important note - in the view, the field names are generated according to the following pattern: An exception is the _id field from the From table , which would use the cursor in the adapter. Therefore, to find the column index you need to use something like
_

columnPoints = cursor.getColumnIndex(ResultView.TABLE_RESULT + "_" + ResultsTable.POINTS);

a bit uncomfortable, but this is done once per

public void changeCursor(Cursor cursor) {

You can still use such a helper

public class ColumnMappingHelper {
	private HashMap> indexes = new HashMap>();
	public int getColumn(Cursor c, String table, String column){
		HashMap columns = indexes.get(table);
		if(columns != null){
			Integer index = columns.get(column);
			if(index != null)
				return index;
		}
		if(columns == null){
			columns = new HashMap();
			indexes.put(table, columns);
		}
		int index = c.getColumnIndex(table + "_" + column);
		columns.put(column, index);
		return index;
	}
}

and use it like that

mappingHelper.getColumn(cursor, ResultView.TABLE_RESULT, ResultsTable.POINTS);

Result

Generated file SqlSchema.java

public class SqlSchema{
	 private static final String SQL_CREATE_RESULT_TABLE = "create table result_table( balls INTEGER, chemp_id INTEGER NOT NULL, games INTEGER NOT NULL, goals INTEGER, _id INTEGER, lose INTEGER, points INTEGER NOT NULL, team_id INTEGER NOT NULL, tie INTEGER, wins INTEGER, PRIMARY KEY( team_id, chemp_id))";
	 private static final String SQL_CREATE_CHEMP_TABLE = "create table chemp_table( _id INTEGER PRIMARY KEY, title TEXT)";
	 private static final String SQL_CREATE_TEAM_TABLE = "create table team_table( chemp_id INTEGER NOT NULL, _id INTEGER PRIMARY KEY, is_fav INTEGER DEFAULT (0), title TEXT NOT NULL)";
	 private static final String SQL_CREATE_CHEMP_INDEX = "create index idx_chemp_index on result_table( chemp_id)";
	 private static final String SQL_CREATE_RESULT_VIEW = "CREATE VIEW result_view AS SELECT   table_chemp._id as table_chemp__id, table_chemp.title as table_chemp_title, table_result.balls as table_result_balls, table_result.chemp_id as table_result_chemp_id, table_result.games as table_result_games, table_result.goals as table_result_goals, table_result._id, table_result.lose as table_result_lose, table_result.points as table_result_points, table_result.team_id as table_result_team_id, table_result.tie as table_result_tie, table_result.wins as table_result_wins, table_team.chemp_id as table_team_chemp_id, table_team._id as table_team__id, table_team.is_fav as table_team_is_fav, table_team.title as table_team_title FROM result_table AS table_result JOIN chemp_table AS table_chemp ON table_chemp._id = table_result.chemp_id JOIN team_table AS table_team ON table_team._id = table_result.team_id";
	 public static void onCreate(final SQLiteDatabase db) {
		db.execSQL(SQL_CREATE_RESULT_TABLE);
		db.execSQL(SQL_CREATE_CHEMP_TABLE);
		db.execSQL(SQL_CREATE_TEAM_TABLE);
		db.execSQL(SQL_CREATE_SCORE_TABLE);
		db.execSQL(SQL_CREATE_CHEMP_INDEX);
		db.execSQL(SQL_CREATE_RESULT_VIEW);
		db.execSQL(SQL_CREATE_SCORE_VIEW);
	}
	public static void onDrop(final SQLiteDatabase db){
		db.execSQL("drop table if exists result_table");
		db.execSQL("drop table if exists chemp_table");
		db.execSQL("drop table if exists team_table");
		db.execSQL("drop table if exists score_table");
		db.execSQL("drop view if exists result_view");
		db.execSQL("drop view if exists score_view");
	}
}

The use of constants from the description of the plates is not required, because the file is generated and clearly follows what you wrote in the table declaration

Using SqlSchema

	private class AnnotationSql extends SQLiteOpenHelper {
		public AnnotationSql(Context context) {
			super(context, FStore.DB_NAME, null, FStore.DB_VERSION);
		}
		@Override
		public void onCreate(SQLiteDatabase db) {
			SqlSchema.onCreate(db);
			init(db);
		}
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			SqlSchema.onDrop(db);
			onCreate(db);
		}
	}

Plans

1. Add different types of joins
2. Add Columns annotation for a join to rake only the required fields

References

Binaries: github.com/hamsterksu/Android-AnnotatedSQL-binaries
Sources: github.com/hamsterksu/Android-AnnotatedSQL

License: MIT

Thank you all!

Update # 1 :

I do not generate OpenHelper schema updates , you write it yourself. so no one bothers to write complex logic there, and the generated script will work for onCreate .
In onUpgrade, you can write adding / deleting / changing fields very simply - you have the names of tables and fields.
In the diagram, I will make the members open - then you can access them and, after changing the tables, recreate the views