Sqlite native functions X Sugarrecord

Question

What are the advantages and disadvantages of using SugarRecord when compared to native functions SQLite,

Example when creating a table with Sqlite Native Functions

DatabaseHelper.java
public class DatabaseHelper extends SQLiteOpenHelper {

    // Logcat tag
    private static final String LOG = "DatabaseHelper";

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "contactsManager";

    // Table Names
    private static final String TABLE_TODO = "todos";
    private static final String TABLE_TAG = "tags";
    private static final String TABLE_TODO_TAG = "todo_tags";

    // Common column names
    private static final String KEY_ID = "id";
    private static final String KEY_CREATED_AT = "created_at";

    // NOTES Table - column nmaes
    private static final String KEY_TODO = "todo";
    private static final String KEY_STATUS = "status";

    // TAGS Table - column names
    private static final String KEY_TAG_NAME = "tag_name";

    // NOTE_TAGS Table - column names
    private static final String KEY_TODO_ID = "todo_id";
    private static final String KEY_TAG_ID = "tag_id";

    // Table Create Statements
    // Todo table create statement
    private static final String CREATE_TABLE_TODO = "CREATE TABLE "
            + TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO
            + " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
            + " DATETIME" + ")";

    // Tag table create statement
    private static final String CREATE_TABLE_TAG = "CREATE TABLE " + TABLE_TAG
            + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT,"
            + KEY_CREATED_AT + " DATETIME" + ")";

    // todo_tag table create statement
    private static final String CREATE_TABLE_TODO_TAG = "CREATE TABLE "
            + TABLE_TODO_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_TODO_ID + " INTEGER," + KEY_TAG_ID + " INTEGER,"
            + KEY_CREATED_AT + " DATETIME" + ")";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        // creating required tables
        db.execSQL(CREATE_TABLE_TODO);
        db.execSQL(CREATE_TABLE_TAG);
        db.execSQL(CREATE_TABLE_TODO_TAG);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO_TAG);

        // create new tables
        onCreate(db);
    }

And with the Sugar Sugarrecord

public class Book extends SugarRecord<Book> {
  String title;
  String edition;

  public Book(){
  }

  public Book(String title, String edition){
    this.title = title;
    this.edition = edition;
  }
}

Answer 1

Developing applications with Sugar ORM can be useful for those who need to persist information in local databases and want to take advantage of the ORM model, not needing to use SQL for creating and handling tables.

Sugar ORM is a database persistence library that provides a simple and consistent way to integrate application models into an Sqlite database. The main advantage of the Sugar ORM is its simplicity but it allows the accomplishment of somewhat more complex tasks such as the use of one-to-many relationships.

We can see this "simplicity" in CRUD of the Sugar ORM:

Save:

Book book = new Book("Title here", "2nd edition")
book.save();

Carry:

Book book = Book.findById(Book.class, 1);

Upgrade:

Book book = Book.findById(Book.class, 1);
book.title = "updated title here"; // modify the values
book.edition = "3rd edition";
book.save(); // updates the previous entry with new values.

Delete:

Book book = Book.findById(Book.class, 1);
book.delete();

Mass operations:

List<Book> books = Book.listAll(Book.class);

Book.deleteAll(Book.class);

Already in the Sqllite:

Creating:

/*
 * Creating a todo
 */
public long createToDo(Todo todo, long[] tag_ids) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_TODO, todo.getNote());
    values.put(KEY_STATUS, todo.getStatus());
    values.put(KEY_CREATED_AT, getDateTime());

    // insert row
    long todo_id = db.insert(TABLE_TODO, null, values);

    // assigning tags to todo
    for (long tag_id : tag_ids) {
        createTodoTag(todo_id, tag_id);
    }

    return todo_id;
}

Seeking:

Type search SELECT * FROM todos WHERE id = 1;

/*
 * get single todo
 */
public Todo getTodo(long todo_id) {
    SQLiteDatabase db = this.getReadableDatabase();

    String selectQuery = "SELECT  * FROM " + TABLE_TODO + " WHERE "
            + KEY_ID + " = " + todo_id;

    Log.e(LOG, selectQuery);

    Cursor c = db.rawQuery(selectQuery, null);

    if (c != null)
        c.moveToFirst();

    Todo td = new Todo();
    td.setId(c.getInt(c.getColumnIndex(KEY_ID)));
    td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
    td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

    return td;
}

Type search SELECT * FROM todos;

/*
 * getting all todos
 * */
public List<Todo> getAllToDos() {
    List<Todo> todos = new ArrayList<Todo>();
    String selectQuery = "SELECT  * FROM " + TABLE_TODO;

    Log.e(LOG, selectQuery);

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (c.moveToFirst()) {
        do {
            Todo td = new Todo();
            td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
            td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
            td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

            // adding to todo list
            todos.add(td);
        } while (c.moveToNext());
    }

    return todos;
}

Updating:

/*
 * Updating a todo
 */
public int updateToDo(Todo todo) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_TODO, todo.getNote());
    values.put(KEY_STATUS, todo.getStatus());

    // updating row
    return db.update(TABLE_TODO, values, KEY_ID + " = ?",
            new String[] { String.valueOf(todo.getId()) });
}

Delete:

/*
 * Deleting a todo
 */
public void deleteToDo(long tado_id) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_TODO, KEY_ID + " = ?",
            new String[] { String.valueOf(tado_id) });
}

The downside is that it is still under development and bugs can be found. According to this website: "If you have a more complex database with more than just text fields and whole numbers, or if you have to implement a database migration, which cannot be done by simple SQL statements, you are lost, unfortunately. Anything other than access to simple database objects will result in writing manual code with limited SQL statements."