Major DataBase overhaul and restructuring [$20]

[E3V3A]

As we have noticed in many recent issues and many past discussions, there is a terrible urgent need to restructure our original database (DB). This DB was originally made for our very first attempts in building this app. However, we've come to realize that the way it is used and structured and how its tables are internally labelled is really confusing at best. It was made according to very weak documentation and poor understanding of what we actually needed.

After several weeks of thinking about this, on and off, I've come to the point where it's time to get it done. Here is a brand new proposal that should cover most features in the near future and hopefully most of the beta stage life-time.

This is the way we will do it:

1. I finish the DB documents and table ER diagram, then
2. you guys make a critical re-view and feedback.
3. I make the required updates, if any, and go back to (1), else
4. we re-code the DB into app.

NOTE: I suggest keeping the old tables while adding the new ones, to maintain simple and progressive migration.

There will surely be loads of questions surrounding this, but I think I might have covered most of it in the (still to be finished) documents:

• General DB functional overview
• Detailed DB tables Descriptions
• Default DB data

Then there will be new App help pages and an FAQ, to be implemented inside app. (Separate issue.)

• General App functionality
• Internal App help pages
• Internal App FAQ
• TBA

---

Here are the old currently used DB tables:

---

Here are the NEW DB tables:

UPDATED: 2015-07-31

---

Reference Issues: These are issues that directly depend on these tables.
#13, #69,#71, #91, #93, #97, #100, #117, #176, #201, (#203 ?),

More descriptions will be added below.

There is a $10 open bounty on this issue. Add to the bounty at Bountysource.

[E3V3A]

The tables are probably more clear when put in the perspective of the functional overview.

Brief Table Explanation

Table	Brief Description
android_metadata	AOS required junk
defaultlocation	MCC data to choose regional OCID subset and map location.
API_keys	API KEYs used for OCID, MLS or other external DBs
DBi_bts	Local and internally measured: less-volatile/fixed BTS info
DBi_measures	Local and internally measured: volatile network RF info
DBe_import	Externally Imported BTS data from OCID or MLS etc.
DBe_capabilities	Externally Imported BTS/MNO capabilities (country dep.)
SectorType	MNO dependent BTS Sector Numbering Description
CounterMeasures	Description and tuning parameters for possible counter measures
DetectionFlags	Our Detection Matrix including parameter & tuning settings
silentsms	Type-0 silent SMS (0SMS) detection data
EventLog	A persistent log of detection events (LAC/CID/PSC/gps etc.)

---

The DetectionFlags table is explained like this:

id:             --
code:           The IMSI-Catcher-Catcher Variable Name (S1,...,L4, etc.)
name:           The internal AIMSICD name (if any)
description:    Detailed description of detection flag.
-----------------------------------------------------------------------
p1:          INT [1-3]  "color code"; Used to give a rough measure of 
                        variable precedence.
p2:          INT [1-3]  "Variable Interception Priority": To what extent 
                        the variable is used to for tracking your network 
                        connections.
p3:          INT [1-3]  "Variable Localization Priority": To what extent
                        the variable is used to localize the victim.
-----------------------------------------------------------------------
== larger INT is more important ==
-----------------------------------------------------------------------
p1..3_fine   REAL [0-1] For fine-tuning and settings of variables
app_text     TEXT       Short text to be shown in app when pushing (i)
func_use     TEXT       Where in the Java code it is used
istatus      INT [0-3]  Implementation Status:
                        0 = not implemented
                        1 = work in progress
                        2 = implemented and complete
                        3 = deprecated
CM_id       TEXT       Description of possible counter measures, if any.

[E3V3A]

Reserved again

[SecUpwN]

Thank you for opening this important Issue, @E3V3A. Let us know once you finished the diagrams.

[tobykurien]

A major db change like this is a lot of work, with basically no gain to the end user (at least in the short term). Hope we find a developer willing to undertake this task.

[E3V3A]

@tobykurien
I completely disagree. It will have a huge impact on the app, because of performance gains (less code to process complex measurements) and allow for far quicker detection/measurement code development, as it will clarify and simplify several operations. The end user will benefit from having a working app faster than if we choose to ignore this. I didn't say this will be easy, but once the extra tables have been inserted into the DB, the migration should be rather straight forward. However, I don't think writing Java code to first build the DB structure, and then populate it with the defaults, is a very efficient way to do this. Much easier is to just ask SQLite3 to import the SQL statements for building the DB. No additional Java code necessary. Or better, perhaps we just ship the app with a pre-populated DB? How does other DB heavy apps do this?

[He3556]

i agree with toby on some points. Lot's of work and no new detection mechanism. On the other hand we need to change the db (earlier or later) - maybe we can implement it step by step, when we really need to use a new table for a new method or detection. I still don't understand the db in detail - the tables on the right are for saving the positive detection events? Is there text somewhere to explain the new tables and the values a little bit?

[E3V3A]

@He3556 Yes, I'm working on it, yes that's right about the DetectionFlags table. I added the details above. Actually, the items in that table are all pre-populated while the actual detections should be kept in an array in the app, with a reference to the _id_ of the detection in that table. So in that respect the table is not actually needed, apart from providing the descriptions and settings for each Detection.

[SecUpwN]

Is there text somewhere to explain the new tables and the values a little bit?

@E3V3A, please make sure that our Technical Overview stays up to date to avoid questions like this.

[tobykurien]

My personal preference is to evolve an app as I go along, i.e. add a few detection mechanisms first (even if messy), learn what the patterns are, then refactor accordingly. While I agree that we probably need these new tables at some point, I am not convinced that we know enough right now to be able to efficiently design them and implement them.

Since you're adding new tables, rather than modifying existing ones, it isn't as bad. You can indeed use SQL statements to populate the tables (or even put a pre-loaded database into the assets folder), that's how it's done by default - not via Java code. The thing is, you need Java code to wrap those tables for use in the code, so SQL statements aren't enough by themselves. Putting in a pre-populated db doesn't save much work. On top of that, you need to code in the CRUD operations on each field in each table, and then use the data appropriately. Think that for each field of each table you add, you are adding hours to possibly days of work, because each field must be initialized, loaded, saved, updated, upgraded, and used in the code appropriately. Each of those operations could spawn multiple bugs/issues.

With the above in mind, I'd work on adding one field/table at a time, as and when we need them, rather than adding a whole bunch in one go. But this is just my methodology/philosophy, so feel free to ignore.

[SecUpwN]

My personal preference is to evolve an app as I go along, i.e. add a few detection mechanisms first (even if messy), learn what the patterns are, then refactor accordingly.

Does that mean you are preferring to create a whole new App, or is this meant as "work on adding one field/table at a time, as and when we need them"? I also like your approach much more. Nothing against what @E3V3A said, but this is pretty much like completely re-inventing our whole App.

[tobykurien]

No I definitely don't want to create a whole new app, but rather slowly evolve the existing one.

[SecUpwN]

@tobykurien, here ya go, now is our chance to rock this stuff since @E3V3A will be away for a week. so let's surpise him, shall we? please tell me what exactly I can tackle here and I'll do it right away! 😎

[E3V3A]

Updated the new ER-diagram with an EventLog table, to be used to export events. Since the event log should be persistent even after wiping the DBi_* and DBe_* tables, it is put in its own table, instead of using referred SQL connections to the other tables.

[SecUpwN]

Just found greenDAO by @greenrobot, which is essentially an open source project on GitHub to help Android developers working with data stored in SQLite. Thought I'd toss that in, maybe even helpful.

[E3V3A]

@SecUpwN Thanks for trying to help, but this is already covered. We already have full access to any DB SW tools we need. (Do you remember adding those sponsor images?)

[SecUpwN]

Do you remember adding those sponsor images?

Oh right. Thank you for reminding me. I'm outa here for now.

[E3V3A]

I have an SQL script aimsicd.sql, that will construct the entire schema of the new tables above. This need to be distributed by our app and then imported/executed in app via a command line Java call using:

# cd /data/data/com.SecUpwN.AIMSICD/databases/
# cat aimsicd.sql | sqlite3 aimsicd.db

Where shall I put it? I.e. in what GitHub directory? in assets?

[SecUpwN]

assets/databases should be the right place. Also found Android SQLiteAssetHelper by @jgilfelt.

[E3V3A]

@SecUpwN Good thing to know, but I think we've got this one covered. We don't need dozens of pages of code to do this. Just the two lines above. We don't even need those lines, if we supply a pre-populated aimsicd.db. That saves a lot of Java coding!

[SecUpwN]

@E3V3A, I have just sent @banjaxbanjo a EUR 50 UKASH code since he really did an awesome job. Please take some quality time to add a testing procedure here so that we can work to finally close this.

[SecUpwN]

Please take some quality time to add a testing procedure here so that we can work to finally close this.

@E3V3A, why won't you respond on this important question? Again "no time and on the phone"? ;-)
@DJaeger, if you've got a good testing procedure in mind, feel invited to post your proposal here.

[E3V3A]

why won't you respond on this important question?

Because every time I ask someone to test something, there is no response. So I'm done with writing testing procedures that nobody give a fcuk about. Some people don't even bother to test their own PR's...

[SecUpwN]

So I'm done with writing testing procedures that nobody give a fcuk about.

@E3V3A, I can understand your frustration and will work on enforcing people to respond to your questions if not answered within a few days in the future. Please add a testing procedure for this now.

[E3V3A]

I've just updated OP with a new ER design image. It is based on the latest aimsicd5.sql SQL script, that we should eventually use to create our original aimsicd.db file. However, the migration is not yet fully complete, as there were many small changes after code migration. These now need to be fixed. And developers are required to follow the ER diagram for fixing issues and implementing new functions. As I'm the maintainer of the DB create script, please contact me if you have any special design requirements.

@SecUpwN can you please copy the image in OP and place it on the wiki where it belongs?

[E3V3A]

Addendum:
The new con_state column in DBi_measure should contain the AOS current connection and
data activity states as given by the API with:

  DATA_ACTIVITY:   No,In,Ou,IO,Do
  DATA_CONNECTION: Di,Ct,Cd,Su
  CALL_STATE:      idle,ringing,offhook
  SERVICE_STATE:   emergency_only,in,out,off

See: PhoneStateListener developer info.

We should also add the current roaming state to this column..

[SecUpwN]

@SecUpwN can you please copy the image in OP and place it on the wiki where it belongs?

@E3V3A, done and made available in Technical Overview.

[E3V3A]

Addendum:
Because signals measured in [dBm] are always negative and signals measured in [ASU], (Arbitrary Strength Unit) can be both positive and negative in UMTS, we should change these fields to use REAL data type.

• DBe_import:avg_signal: REAL
• DBi_measure:rx_signal: REAL

[SecUpwN]

Good morning members of @CellularPrivacy/database! This Issue is one of the most important Issues we need to solve ASAP. It has been open for nearly 2 years, was one of the hardcore reasons why E:V:A (our project inventor) left and still blocks all further serious IMSI-Catcher detection mechanisms. I would like everyone from the database team to closely read this whole Issue (yes, I know it's a pain, but necessary) so that we can move forward to finally close this bug for good. Keep in mind: Very URGENT!

Quick sum-up of what you need to have a look at:

• Read the functional table overview. @banjaxbanjo, which tables are still missing?
• Update con_state column to contain AOS current connection and
  data activity states
• Change DBe_import:avg_signal and DBi_measure:rx_signal to REAL data type

In the end, this is what our database needs to look like (documented in Technical Overview):

[larsgrefer]

I've started the branch feature/realm

PR's to this branch are welcome

[SecUpwN]

I've started the branch feature/realm

Thanks, @larsgrefer. I hope this brings us forward. A few questions the others might have as well:

1. How come you specifically decided to move to REALM?
2. Will REALM be able to cover all the planned tables?
3. What are the advantages over the current SQL table?
4. Should we close this Issue for good in favor of Use Realm as Database [$30] #655?

Our originial inventor E:V:A (who officially left our project and now mainly idles and monitors what we do) was mostly against anything else than SQL and did make very valid points about that. Since he was (is) our database guru, I feel like it is a good thing mentioning him here in the hope that you guys will have a friendly and respectful discussion here. @E3V3A, I would be happy to see your questions to @larsgrefer.

[larsgrefer]

How come you specifically decided to move to REALM?

It seems very interesting to me and I'd like to give it a try

Will REALM be able to cover all the planned tables?

Sure

What are the advantages over the current SQL table?

We don't have to worry about OR-Mapping and get a nice java-api.
And of course we can get rid of our 2000+ lines monster-class

[Nordlenning]

20 USD bounty on this issue so far
https://www.bountysource.com/issues/7069218-major-database-overhaul-and-restructuring

[SecUpwN]

Manually updated this thread title as well and would like to reward it to @larsgrefer when merging #827.

[larsgrefer]

resolved for now in #827 and #655

[agilob]

Not resolved. This issue was about expanding data categories that are collected by AIMSICD to get more information about cells, networks and devices! Not about using ORM as database middle-man!

[larsgrefer]

Many of the problems/whishes named in this issue are solved.

Our persistence layer is on a clean state now. We have nicely named classes and fields. There are no unused classes or fields. The multi-threading problem is also fixed.

[SecUpwN]

I agree with you. The main move to a working and easily expandable database has been accomplished.

@larsgrefer, even if not much, I'd like to say THANK YOU for investing heart blood with the $20 bounty.
Since @Nordlenning has been a donator for this Issue as well, I'd like him to state if he agrees. Do you?

[Nordlenning]

Moving to a working and easily expandable database is great.
I would like to reward it to @larsgrefer. His energy is needed elsewhere..