AnnotatedSQL lib - automatic database generation in Android

When developing an Android application, we often write with our hands scripts to create a schema.
Everything would be fine when it needs to be done once, but when the application develops, it is often necessary to change the base.
And when it is spread over several classes, then problems arise, 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 generates code for creating an annotation database. Annotations are not runtime , but are processed by the preprocessor at compile time. Thus, we can not defective project and the final apk.

Actually, it consists of two pieces: jar with annotations and a preprocessor.
Annotations put in the project libs folder.
But the use of the preprocessor depends on the IDE and the way the project is built.

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

For IDEA plugin did not collect, litter

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


Using

We turn to more technical things. So, we know how to connect it, but what does it do?
As I already said, according to the annotations, the base is generated, or rather the class is created with the help of which the base will be generated.
As is usually the case, for use in code, we describe labels 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 tablets in the database. This is the team, the result and the championship. Let's describe them.
I usually describe all the interfaces within one class, let's call it FStore, for example. In addition to the description of the plates, 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"; } ........ } 

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

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

Schema

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

 @Schema("SqlSchema") public class FStore { 

Table, Index, PrimaryKey

Labels 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 simple and requires no explanation.

Column, PrimaryKey, Autoincrement, NotNull

These annotations are for fields, and are obvious in use.

  @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 INNER JOIN for now, 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 annotations inside our view:
From is the table from which we will do from :) Important - then with joins, it is necessary to use not the name of the table, but this constant.

Join is the join table itself. In our case, it is necessary to score on the team and championship table.

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

 String TABLE_RESULT = "table_result"; 

Another very important note is that in the view the field names are generated according to the following pattern:
<variable_name>_<column_name>

The exception is the _id field from the From table to 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 little awkward but this is done once in

 public void changeCursor(Cursor cursor) { 

You can still make such a helper

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

and yuzay it so

 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 label description is not required, since 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 the Columns annotation for the join to sort out only the required fields.

Links

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

License: MIT

Thanks to all!

Update # 1 : About schema update

I do not generate OpenHelper, you write it yourself. so no one bothers to write complicated logic there, and the gene script will work for onCreate .
In onUpgrade, you can write adding / removing / changing fields quite simply - you have the names of the tables and fields.
In the diagram, I will make the members open - then you can access them and after changing the tables, re-create the views