How I create a database for my applications

Hello! My name is Oleg and I am an amateur programmer for Android. Amateur because at the moment I make money programming in a completely different direction. And this is a hobby to which I devote my free time. Unfortunately, I don’t have any familiar Android programmers and I draw all my basic knowledge either from books or from the Internet. In all those books and articles on the Internet that I read, creating a database for the application is given very little space and in fact all the description boils down to creating a class that inherits SQLiteOpenHelper and then injecting SQL code into Java code. If we do not assume that we get poorly readable code (and if there are more than 10 tables in our application, then all these relationships between tables are still hell), then in principle you can certainly live, but somehow you don’t feel like it.
I forgot to say the most important thing, I can say that this is my first attempt at writing here. And so it went.


If in our application there are more than 5 tables, then it would not be bad to use any tool for the visual design of the database architecture. Since this is a hobby for me, I use an absolutely free tool called Oracle SQL Developer Data Modeler (you can download it here ).


This program allows you to visually draw tables and build relationships with them. Many errors in designing a database architecture can be avoided with this design approach (I’m already talking to you as a professional database programmer). It looks like this:


')
Having designed the architecture itself, we proceed to the more tedious part, which consists in creating sql code for creating tables. To help with this issue, I already use a tool called SQLiteStudio (in turn, you can download it here ).



This tool is an analogue of such well-known products like SQL Naviagator, Toad etc. But as the name implies, it is sharpened to work with SQLite. It allows you to visually create a database and get the DDL code of the created tables. By the way, it also allows you to create views (View), which you can also use in your application if you wish. I don’t know how correct the approach is to using views in Android programs, but in one of my applications I used them.



As a matter of fact, I no longer use any third-party tools, and then the magic begins with Android Studio. As I wrote above, if you start to embed SQL code in Java code, then we will get poorly readable, and therefore poorly extensible code. Therefore, I put all the SQL statements in external files that I have in the assets directory. In Android Studio, it looks like this:



Now, let's look at the code inside my DBHelper that I use in my projects. First, the class variables and the constructor (here without any surprises):

private static final String TAG = "RoadMap4.DBHelper"; String mDb = "db_"; String mData = "data_"; Context mContext; int mVersion; public DBHelper(Context context, String name, int version) { super(context, name, null, version); mContext = context; mVersion = version; } 

Now the onCreate method and here it becomes more interesting:

  @Override public void onCreate(SQLiteDatabase db) { ArrayList<String> tables = getSQLTables(); for (String table: tables){ db.execSQL(table); } ArrayList<HashMap<String, ContentValues>> dataSQL = getSQLDatas(); for (HashMap<String, ContentValues> hm: dataSQL){ for (String table: hm.keySet()){ Log.d(TAG, "insert into " + table + " " + hm.get(table)); long rowId = db.insert(table, null, hm.get(table)); } } } 

Logically, it is divided into two cycles, in the first cycle I get a list of SQL instructions for creating the database and then executing them, in the second cycle I fill in the tables created earlier with the initial data. And so, the first step:

  private ArrayList<String> getSQLTables() { ArrayList<String> tables = new ArrayList<>(); ArrayList<String> files = new ArrayList<>(); AssetManager assetManager = mContext.getAssets(); String dir = mDb + mVersion; try { String[] listFiles = assetManager.list(dir); for (String file: listFiles){ files.add(file); } Collections.sort(files, new QueryFilesComparator()); BufferedReader bufferedReader; String query; String line; for (String file: files){ Log.d(TAG, "file db is " + file); bufferedReader = new BufferedReader(new InputStreamReader(assetManager.open(dir + "/" + file))); query = ""; while ((line = bufferedReader.readLine()) != null){ query = query + line; } bufferedReader.close(); tables.add(query); } } catch (IOException e) { e.printStackTrace(); } return tables; } 

Everything is simple enough, we just read the contents of the files, and concatenate the contents of each file into an array element. Please note that I sort the list of files, since the tables may have foreign keys, which means the tables must be created in a specific order. I use the numbering in the file name, and with it, I sort it.

  private class QueryFilesComparator implements Comparator<String>{ @Override public int compare(String file1, String file2) { Integer f2 = Integer.parseInt(file1.substring(0, 2)); Integer f1 = Integer.parseInt(file2.substring(0, 2)); return f2.compareTo(f1); } } 

With filling the tables all the fun. My tables are filled with not only hard-coded values, but also values ​​from resources and UUID keys (I hope to come sometime to the network version of my program so that my users can work with common data). The very structure of the files with the initial data looks like this:


Despite the fact that my files have the sql extension, inside is not a sql code, but such a thing:

prioritys
pri_id:UUID:UUID
pri_object:string:object_task
pri_name:string:normal
pri_color:color:colorGreen
pri_default:int:1
prioritys
pri_id:UUID:UUID
pri_object:string:object_task
pri_name:string:hold
pri_color:color:colorBlue
pri_default:int:0
prioritys
pri_id:UUID:UUID
pri_object:string:object_task
pri_name:string:important
pri_color:color:colorRed
pri_default:int:0
prioritys
pri_id:UUID:UUID
pri_object:string:object_project
pri_name:string:normal
pri_color:color:colorGreen
pri_default:int:1
prioritys
pri_id:UUID:UUID
pri_object:string:object_project
pri_name:string:hold
pri_color:color:colorBlue
pri_default:int:0
prioritys
pri_id:UUID:UUID
pri_object:string:object_project
pri_name:string:important
pri_color:color:colorRed
pri_default:int:0

The file structure is as follows: I call the split function (":") for a line, and if I get that its size is 1, then this is the name of the table where the data should be written. Otherwise it is the data itself. The first field is the name of the field in the table. The second field is the type by which I determine what I need to write in this field. If this is a UUID, it means I need to generate a unique UUID value. If string means I need to pull out a string value from resources. If color, then again, from the resources you need to pull out the color code. If int or text, then I just convert this value to an int or String without any gestures. The code itself looks like this:

  private ArrayList<HashMap<String, ContentValues>> getSQLDatas() { ArrayList<HashMap<String, ContentValues>> data = new ArrayList<>(); ArrayList<String> files = new ArrayList<>(); AssetManager assetManager = mContext.getAssets(); String dir = mData + mVersion; try { String[] listFiles = assetManager.list(dir); for (String file: listFiles){ files.add(file); } Collections.sort(files, new QueryFilesComparator()); BufferedReader bufferedReader; String line; int separator = 0; ContentValues cv = null; String[] fields; String nameTable = null; String packageName = mContext.getPackageName(); boolean flag = false; HashMap<String, ContentValues> hm; for (String file: files){ Log.d(TAG, "file db is " + file); bufferedReader = new BufferedReader(new InputStreamReader(assetManager.open(dir + "/" + file))); while ((line = bufferedReader.readLine()) != null){ fields = line.trim().split(":"); if (fields.length == 1){ if (flag == true){ hm = new HashMap<>(); hm.put(nameTable, cv); data.add(hm); } //   nameTable = line.trim(); cv = new ContentValues(); continue; } else { if (fields[1].equals("UUID")){ cv.put(fields[0], UUID.randomUUID().toString()); } else if (fields[1].equals("color") || fields[1].equals("string")){ int resId = mContext.getResources().getIdentifier(fields[2], fields[1], packageName); Log.d(TAG, fields[1] + " " + resId); switch (fields[1]){ case "color": cv.put(fields[0], resId); break; case "string": cv.put(fields[0], mContext.getString(resId)); break; default: break; } } else if (fields[1].equals("text")){ cv.put(fields[0], fields[2]); } else if (fields[1].equals("int")){ cv.put(fields[0], Integer.parseInt(fields[2])); } } flag = true; } bufferedReader.close(); } } catch (IOException e) { e.printStackTrace(); } return data; } 

Well, as a postscript: I repeat, saying that I am an amateur in Android programming, which is half-trouble. The second problem is that in my environment there are no programmers for Android and, in fact, there is no one not to consult anyone to brainstorm how best to do something. We have to go by the method of scientific spear, stepping on a rake along the way. Sometimes it hurts, but overall it's cool. The project I'm working on is already undergoing 4 reincarnations. Therefore, please do not shoot the pianist, I play as I can. If write how to do better, I will be grateful and happy.