Problem

We want to show our reasoning as to why someone would want to use this particular library and the challenges we have faced with each release. To this end, we have created a Technical Data Questionnaire. This Questionnaire is cloned from novoda/novoda.

Solution

Here's a template, created from the Gradle Build Properties Plugin, that can be used to create a Data Questionnaire for this repository. The Data Questionnaire should be added to this Repository's Wiki Section.

Template

Overview of the project. What are its technical capabilities compared to the industry standard?

Development of a plugin system for open source applications that require private keys and configs to be ran but these should not be shared in public locations. for example an analytics library that needs a key to login to the system but not all people should access to this key. The project focuses on making the possibility of adding private information as seamless as possible, so that the projects can be used simply but not expose information. This offers features not available currently on the market and although other variations of this technique are possible they do not make the solution quite as easy.

What platforms and tools did you use to make it? (encouraged to namedrop components/languages/databases etc)

Groovy, Gradle for development, Guava, Truth and Findbugs for testing

Describe, in detail, the technical challenges faced (specifically what development required more than routine techniques) and what was engineered to overcome them?

It is really important to discuss the iterative journey to explain at each stage of production (each release) what was engineered.

Write this as if you are explaining the challenges to a technical colleague.

Attempt to write at least four paragraphs that cover these points:

• Technical advances
• How uncertainties were overcome
• Any iterative approaches
• Final solution

Currently to solve the problem of not sharing private keys, developers would cut and paste when they were needed for production and a lot of human interaction was needed to keep these keys private, but still useful. Many repositories would require five or more keys, therefore it required coordination with the build system to make sure each was put in the right place and used at the right time. The gradle build properties plugin automates all of this and allows you to drop in one file, that is read by the gradle system and pulls out the properties or keys and puts them in the right place. This is a technical advancement on any of the current solutions as before it was not centralised or automated meaning keys where in many different places and the chance for security issues was high. With this centralised solution it can allow us to keep all the keys in a secure place and use security credential to access them at the time needed.

A big uncertainty was our knowledge around connecting a remote secure location for the keys to the build system that would be using them. We did not know if we could access or communicate with a secure system in a timely fashion that would allow for the normal build process. This uncertainty was overcome through investigating distribution systems based on maven repositories. With this investigation it was found two way secure communication could happen and therefore we could separate the private keys from the public repository.

When injecting the private keys into a gradle build it has to be done at the correct step. With the creation of this plugin we were unsure in our knowledge of gradle plugins and where we would hook into the build system. This problem was overcome with the development approach of investigating other gradle plugins, this lead us to the Android plugin that also hooks in for other reasons, and we adapted and expanded on this knowledge to allow our key injection to work.

The first approach was to load from a local properties file, take the keys from a separate file, this split apart building and key loading. The second was being able to override at runtime any errors, therefore giving fallback strategies if keys were not available. For example if the properties file was missing a private key, the key could also be set as a system property and we could now fallback from one to the other in a recursive fashion. We then added other fallbacks for secondary files, command line and hard coded values. We then iterated again to allow for the fallback file to be a remote file and take security credentials to open or load it.

The final solution now allows for developers to create open source repositories that use private keys without them needing to be public. The integration is as simple as defining a file, that is not checked into version control but can be in one of multiple places, and then providing fallback strategies if this secure file is not there, for example providing fallback debug keys that can be public and checked into the repository. This simplified any developers use of private keys in a public or private repositories and will likely lead to a decrease in their time spent in this area by a factor of 10x.

Hello,

I don't really know if I am something wrong,but I have a problem while reading from the migrations file. Maybe can be an encoding problem when reading using the InputStreamReader or then, when executing: SQLFile.statementsFrom(reader);
I thought that probably could be malformed chars that I was getting from the server, but when updating the db (I am updating in case hashCodes of the Strings are different), that is after the migrations file is executed for first time, the string is inserted correctly. It's html content.

This is my sql script (I think that github is going to format this in html, sorry for that ):

CREATE TABLE 'faq'
    (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, hash_code INTEGER);

CREATE TABLE 'faq_question'
    (_id INTEGER PRIMARY KEY AUTOINCREMENT,
    question TEXT,
    answer TEXT,
    hash_code INTEGER,
    faq_id INTEGER,
    FOREIGN KEY (faq_id) REFERENCES faq(_id) ON DELETE CASCADE);

CREATE TABLE 'about_the_brand'
    (_id INTEGER PRIMARY KEY AUTOINCREMENT,
    about TEXT,
    hash_code INTEGER);

INSERT INTO 'about_the_brand'
    (about, hash_code) VALUES ("<h2>ANOTHER WORLD LEADER</h2>\r\n\r\n<p>Since 2001, Boards & More has been marketing kites, kiteboards and\r\naccessories under the name North Kiteboarding.</p>\r\n\r\n<p>From the very beginning North Kiteboarding became a leading brand on the market thanks to both the innovative design and high quality.</p>\r\n\r\n<p>The North team strives to create pioneering and high quality products, always one step ahead at the forefront of the kiteboarding industry.</p>\r\n\r\n<p>In order to integrate the latest global trends, our products are tested and developed by our team-riders and designers at the 3 kite hot spots in Tarifa, Gorge and Hawaii.</p>\r\n\r\n<p>North Kiteboarding immediately assimilates new trends and styles, and has set new standards from the very outset, thanks to the high level of both team riders and designers. Amongst these innovations and standards are the patented Fifth-line-system, several security features as well as top-level shapes of kites and boards.</p>\r\n", -1574256766);

////////////////////////
I also attach a couple of pictures as you can see what happens first time when reading from the file, and after loading same string from the server.

I wish I had some time to research on this and solve the problem (in case it's something wrong with the read encoding) but I have to end up with a sprint, so I'll try to have a look over the weekend more deeply, but I would really appreciate if you can let me know your thoughts.

Many thanks for your time and excuse me for the inconveniences (probably I am missing something).

Hi Novoda team,

I'm trying to add sqlite-provider to my project.

Based on "Adding to your project" section of README.md file.
I've add this line to my app module gradle file:
compile 'com.novoda:sqlite-provider:1.0.3

But I've got an error:
Error:Failed to find: com.novoda:sqlite-provider:1.0.3

Android studio 0.8.14, gradle 2.1, repositories { jcenter() }

any ideas?

Demo projects are maven projects, gradle support should be added

In the light of adding a more streamlined process (see #16) I want to recommend applying the gitflow branching model for this library as it makes it easy to have a living project and yet a stable environment.

What do we need to start this?

• Basically we'd move our development branch from master to develop.
• Master will become our stable branch for the tested and reviewed release version.
• All features will be developed in feature branches and then first merged into develop for integration testing.
• After proper tests we start a release which automatically creates a tag and merges the changes back to master and develop.
• No direct commits to master. Ever.

Easy start

To make it easier to handle the development there are some tools:

• git flow command to extend command line git
• git flow for IDEA/Android Studio ❤️
• Atlassian's Sourcetree also supports git flow natively

Possible problems?

Maybe there are some configuration issues with sonartype and jenkins but I think that's nothing that can't be overcome.

Questions? Opinions?

An update method returns number of rows affected, not a rowID. Code should read:

int count = getWritableDatabase().update(UriUtils.getItemDirID(uri), insertValues, selection, selectionArgs);

......

return count;

The notifyUriChange() call is notifying some completely unrelated observer.

@Override
    protected int updateInTransaction(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
        ContentValues insertValues = (values != null) ? new ContentValues(values) : new ContentValues();

        int rowId = getWritableDatabase().update(UriUtils.getItemDirID(uri), insertValues, selection, selectionArgs);

        if (rowId > 0) {
            Uri insertUri = ContentUris.withAppendedId(uri, rowId);
            notifyUriChange(insertUri);
            return rowId;
        }
        throw new SQLException("Failed to update row into " + uri + " because it does not exists.");
    }

Have this error when trying to use 1.0.1 in my build.gradle, like this

dependencies {
  ...
  compile 'com.novoda:sqliteprovider-core:1.0.1'
  ...
}

It seems like something went wrong during the release process.
Other internal projects didn't notice the issue because they are still using 1.0.1-SNAPSHOT

SQLiteContentProviderImpl has the following implementation of getDatabaseHelper():

@Override
protected SQLiteOpenHelper getDatabaseHelper(Context context) {
    try {
        return new ExtendedSQLiteOpenHelper(context, getCursorFactory());
    } catch (IOException e) {
        Log.Provider.e(e);
        throw new IllegalStateException(e.getMessage());
    }
}

It looks like I can redefine this function and return my own SQLiteOpenHelper. But it doesn't work: somewhere in the lib will be casting back to ExtendedSQLiteOpenHelper:

Caused by: java.lang.ClassCastException: ru.jollydroid.app.Db cannot be cast to novoda.lib.sqliteprovider.sqlite.ExtendedSQLiteOpenHelper
at novoda.lib.sqliteprovider.provider.SQLiteContentProviderImpl.onCreate(SQLiteContentProviderImpl.java:43)
at android.content.ContentProvider.attachInfo(ContentProvider.java:1748)

I think you need to split ExtendedSQLiteOpenHelper to two classes. One with common helper functions like getColumns() and getTables, and one with implementation of your helper.

https://github.com/novoda/SQLiteProvider/blob/master/demo-simple/src/com/novoda/sqliteprovider/demo/simple/ui/MainFragment.java#L59

This would crash if there was actually nothing in the DB, it is missing a return statement

   if (!cursor.moveToFirst()) {
       Log.d("demo", "Nothing in DB, returning early");
       return;
   }

A problem occurred configuring project ':app'.

Could not resolve all dependencies for configuration ':app:_debugCompile'.
Could not find com.novoda:sqliteprovider-core:1.0.1.
Required by:

Same issue as before.
Gradle.

Is there a way to enable logging for all executed statements?

The ON DELETE CASCADE statement is not respected for foreign keys.
I created the foreign_keys branch with a modified version of the demo-simple project to show this.
For a quick example, given the following DB definition

CREATE TABLE 'city'(
    city_id INTEGER PRIMARY KEY,
    name TEXT
);
CREATE TABLE 'shop'(
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    postcode TEXT,
    city_id TEXT,
    FOREIGN KEY (city_id) REFERENCES city(city_id) ON DELETE CASCADE
);

INSERT INTO 'city'
    (city_id, name) VALUES (1, "London");
INSERT INTO 'city'
    (city_id, name) VALUES (2, "Brighton");
INSERT INTO 'shop'
    (name, postcode, city_id) VALUES ("TheBigBang Shop", "L300RA", 1);
INSERT INTO 'shop'
    (name, postcode, city_id) VALUES ("Dragons Cave", "SK85NX", 1);
INSERT INTO 'shop'
    (name, postcode, city_id) VALUES ("Firework Corner", "BN14FT", 2);
INSERT INTO 'shop'
    (name, postcode, city_id) VALUES ("Asda", "L319BH", 2);

When deleting the city with city_id=1, the relative shops are not deleted.

The class defines a private static String STATEMENT_END_CHARACTER = ";". This will result in statements containing a line ending with ; to be broken up, resulting in SQL errors. Such statements can be for example a INSERT TRIGGER. Some other statement separator should be used, such as a zero-length line.

This can be reproduced with the demo-simple only on Marshmallow:

Create 1040 migration files with size 0 bytes and start the app. Depending whether debuggable is switched on the app will crash at file 553 or 1039. In real world apps this Exception is thrown after 6 files.

Looking at the source it seems there is a reader.close() missing in finally. But even adding that does not prevent the Exception from being thrown. The problem only occurs on clean install.

java.io.FileNotFoundException: migrations/553_DEPRECATED.SQL
at android.content.res.AssetManager.openAsset(Native Method)
at android.content.res.AssetManager.open(AssetManager.java:313)
at novoda.lib.sqliteprovider.migration.Migrations.migrate(Migrations.java:91)
at novoda.lib.sqliteprovider.sqlite.ExtendedSQLiteOpenHelper.onCreate(ExtendedSQLiteOpenHelper.java:76)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:251)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)
at novoda.lib.sqliteprovider.provider.SQLiteContentProvider.insert(SQLiteContentProvider.java:94)
at android.content.ContentProvider$Transport.insert(ContentProvider.java:263)
at android.content.ContentResolver.insert(ContentResolver.java:1231)
at com.novoda.sqliteprovider.demo.simple.ui.MainFragment.saveNewShopToDatabase(MainFragment.java:44)
at com.novoda.sqliteprovider.demo.simple.ui.MainFragment.onCreate(MainFragment.java:32)
at android.support.v4.app.Fragment.performCreate(Fragment.java:1766)
at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:917)
at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1116)
at android.support.v4.app.FragmentManagerImpl.addFragment(FragmentManager.java:1218)
at android.support.v4.app.FragmentManagerImpl.onCreateView(FragmentManager.java:2170)
at android.support.v4.app.FragmentActivity.onCreateView(FragmentActivity.java:300)
at android.view.LayoutInflater.createViewFromTag(LayoutInflater.java:748)
at android.view.LayoutInflater.createViewFromTag(LayoutInflater.java:704)
at android.view.LayoutInflater.rInflate(LayoutInflater.java:835)
at android.view.LayoutInflater.rInflateChildren(LayoutInflater.java:798)
at android.view.LayoutInflater.inflate(LayoutInflater.java:515)
at android.view.LayoutInflater.inflate(LayoutInflater.java:423)
at android.view.LayoutInflater.inflate(LayoutInflater.java:374)
at com.android.internal.policy.PhoneWindow.setContentView(PhoneWindow.java:393)
at android.app.Activity.setContentView(Activity.java:2166)
at com.novoda.sqliteprovider.demo.simple.ui.MainActivity.onCreate(MainActivity.java:13)

only single line sql statements supported for now

This code is wrong:

@Override
protected Uri insertInTransaction(Uri uri, ContentValues values) {
    long rowId = helper.insert(uri, values);
    if (rowId > 0) {
        Uri newUri = ContentUris.withAppendedId(uri, rowId);
        notifyUriChange(newUri);
        return newUri;
    }
    throw new SQLException("Failed to insert row into " + uri);
}

The Android documentation for android.database.sqlite.SQLiteDatabase.insert() states:
@return the row ID of the newly inserted row, or -1 if an error occurred
This Android documentation is misleading, because a valid row ID can in fact be -1 or any other negative number. This happens when a table contains a single INTEGER PRIMARY KEY, in which case sqlite will use this key as row ID. For example:

CREATE TABLE tbl (id INTEGER NOT NULL, PRIMARY KEY (id) ON CONFLICT REPLACE);
INSERT INTO tbl(id) VALUES (-1);

The row ID of this row will be -1.
Your code should correctly read:

@Override
protected Uri insertInTransaction(Uri uri, ContentValues values) {
    long rowId = helper.insert(uri, values);
    Uri newUri = ContentUris.withAppendedId(uri, rowId);
    notifyUriChange(newUri);
    return newUri;
}

I have the following relations table:

CREATE TABLE "image_likers" (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    image_id INTEGER,
    user_id INTEGER,

    UNIQUE(image_id, user_id) ON CONFLICT REPLACE,
    FOREIGN KEY (image_id) REFERENCES "images" (_id),
    FOREIGN KEY (user_id) REFERENCES "users" (_id)
);

So what I would expect is that every relation, made of image_id and user_id is unique, not one column by itself. Trying that in an SQLite client works.

So having the following content would work and be ok:

1, 100, 200 // User 200 likes image 100
2, 101, 200 // User 200 likes image 101
3, 102, 200 // User 200 likes image 102

So like having the same user_id in multiple rows is fine, same for image_id. Just not the same combinations.

Adding another row like this should not add another one but replace row 1:
image_id = 100, user_id = 200

Using an SQLite client, all is fine with that. But the SQLiteProvider only evaluates one unique constraint, so for one column. The problem seems to be in InsertHelper.insert(...).

final String firstConstrain = dbHelper.getFirstConstrain(table, insertValues);

This line returns only the "image_id" column as unique constraint and then tries an update on that:

UPDATE image_likers SET image_id=?,user_id=? WHERE image_id=?

As a result, whenever it finds an existing image_id, it updates the row, which means that all of the images have at most 1 liker.
Instead it should only update the row if it has the same image_id - user_id combination.

Might that be a bug?

A problem occurred configuring project ':app'.

Could not resolve all dependencies for configuration ':app:_debugCompile'.
Could not find com.novoda:sqliteprovider-core:1.0.1.
Required by:

apply plugin: 'android'

android {
compileSdkVersion 19
buildToolsVersion "19.0.3"

defaultConfig {
    minSdkVersion 14
    targetSdkVersion 19
}

buildTypes {
    release {
        runProguard false
        proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.txt'
    }
}

}
dependencies {
compile 'com.android.support:support-v4:+'
compile 'com.novoda:sqliteprovider-core:1.0.1'
}

Please add tags to your repository to mark the release version.

SQLiteContentProvider#bulkInsert() does not give any indication to the caller when the transaction was not successful. Whatever happens, it returns the count of initial values, whereas it should be "The number of values that were inserted", as done by android.content.ContentProvider#bulkInsert(), which returns 0, when an exception occurred.

I experienced this behaviour when I was inserting entities which contained the same value in unique columns. Nothing was inserted, but I expected some kind of UniqueConstraintException, which would be indicated with a return value of 0.

Currently it is possible to provide some SQL scripts for database setup.
Do you also plan to support the usage of distributed databases?

modify or del ,I don't know how to,any help?

On the project page I read about planned ("TODO)" join support with the following sample:

Uri: content:///parent/1/child/2
Sql: select * from child inner join parent on parent._id=child.parent_id;

Is there anything new about the status?

I also saw something about an "expand" query parameter. Can I realize something like a join with it? If so how would that look like?

Thanks!

I moved the SQL statements in the tests from files to Strings as the files weren't reading, but they should be moved back at some stage: 7bdf42b

(recommended 0.32ms)

I noticed that multiple notifications are sent when bulkInsert is used. I figured out this because you iterating each ContentValue and run an individual transaction insert on the item.

@Override
public int bulkInsert(Uri uri, ContentValues[] values) {
    int numValues = values.length;
    SQLiteDatabase mDb = mOpenHelper.getWritableDatabase();
    mDb.beginTransactionWithListener(this);
    try {
        for (int i = 0; i < numValues; i++) {
            Uri result = insertInTransaction(uri, values[i]);
            if (result != null) {
                mNotifyChange = true;
            }
            mDb.yieldIfContendedSafely();
        }
        mDb.setTransactionSuccessful();
    } finally {
        mDb.endTransaction();
    }

    onEndTransaction();
    return numValues;
}

I cannot see the reason why you are using insert in favor of bulkInsert. Could you please clarify?

Yep.