TransferDatabaseHelper.java

package com.seafile.seadroid2.transfer;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.seafile.seadroid2.SeadroidApplication;
import com.seafile.seadroid2.data.DataManager;

import java.io.File;

/**
 * Database helper for transfer list
 *
 * Created by Logan on 14/12/27.
 */
public class TransferDatabaseHelper extends SQLiteOpenHelper {
    private static final String DEBUG_TAG = "TransferDatabaseHelper";

    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "seafile.db";

    // download table
    public static final String DOWNLOAD_TABLE_NAME = "DownloadTask";

    // upload table
    public static final String UPLOAD_TABLE_NAME = "UploadTask";

    public static final String DOWNLOAD_COLUMN_TASKID = "task_id";
    public static final String DOWNLOAD_COLUMN_STATE = "state";
    public static final String DOWNLOAD_COLUMN_REPO_ID = "repo_id";
    public static final String DOWNLOAD_COLUMN_REPO_NAME = "repo_name";
    public static final String DOWNLOAD_COLUMN_PATH_IN_REPO = "path_in_repo";
    public static final String DOWNLOAD_COLUMN_LOCAL_PATH = "local_path";
    public static final String DOWNLOAD_COLUMN_TOTAL_SIZE = "total_size";
    public static final String DOWNLOAD_COLUMN_DOWNLOAD_SIZE = "download_size";
    public static final String DOWNLOAD_COLUMN_ACCOUNT_SIGNATURE = "account_signature"; // account signature

    public static final String UPLOAD_COLUMN_TASKID = "task_id";
    public static final String UPLOAD_COLUMN_STATE = "state";
    public static final String UPLOAD_COLUMN_REPO_ID = "repo_id";
    public static final String UPLOAD_COLUMN_REPO_NAME = "repo_name";
    public static final String UPLOAD_COLUMN_PARENT_DIR = "parent_dir";
    public static final String UPLOAD_COLUMN_LOCAL_PATH = "local_path";
    public static final String UPLOAD_COLUMN_IS_UPDATE = "is_update";
    public static final String UPLOAD_COLUMN_IS_COPY_TO_LOCAL = "is_copy_to_local";
    public static final String UPLOAD_COLUMN_TOTAL_SIZE = "total_size";
    public static final String UPLOAD_COLUMN_UPLOAD_SIZE = "upload_size";
    public static final String UPLOAD_COLUMN_ACCOUNT_SIGNATURE = "account_signature"; // account signature

    private static final String SQL_CREATE_DOWNLOAD_TABLE =
            "CREATE TABLE " + DOWNLOAD_TABLE_NAME + " ("
                    + DOWNLOAD_COLUMN_TASKID + " INTEGER PRIMARY KEY, "
                    + DOWNLOAD_COLUMN_STATE + " TEXT NOT NULL, "
                    + DOWNLOAD_COLUMN_REPO_ID + " TEXT NOT NULL, "
                    + DOWNLOAD_COLUMN_REPO_NAME + " TEXT NOT NULL, "
                    + DOWNLOAD_COLUMN_PATH_IN_REPO + " TEXT NOT NULL, "
                    + DOWNLOAD_COLUMN_LOCAL_PATH + " TEXT NOT NULL, "
                    + DOWNLOAD_COLUMN_TOTAL_SIZE + " LONG, "
                    + DOWNLOAD_COLUMN_DOWNLOAD_SIZE + " LONG, "
                    + DOWNLOAD_COLUMN_ACCOUNT_SIGNATURE + " TEXT NOT NULL);";

    private static final String SQL_CREATE_UPLOAD_TABLE =
            "CREATE TABLE " + UPLOAD_TABLE_NAME + " ("
                    + UPLOAD_COLUMN_TASKID + " INTEGER PRIMARY KEY, "
                    + UPLOAD_COLUMN_STATE + " TEXT NOT NULL, "
                    + UPLOAD_COLUMN_REPO_ID + " TEXT NOT NULL, "
                    + UPLOAD_COLUMN_REPO_NAME + " TEXT NOT NULL, "
                    + UPLOAD_COLUMN_PARENT_DIR + " TEXT NOT NULL, "
                    + UPLOAD_COLUMN_LOCAL_PATH + " TEXT NOT NULL, "
                    + UPLOAD_COLUMN_IS_UPDATE + " INT NOT NULL, "
                    + UPLOAD_COLUMN_IS_COPY_TO_LOCAL + " INT NOT NULL, "
                    + UPLOAD_COLUMN_TOTAL_SIZE + " LONG, "
                    + UPLOAD_COLUMN_UPLOAD_SIZE + " LONG, "
                    + UPLOAD_COLUMN_ACCOUNT_SIGNATURE + " TEXT NOT NULL);";

    private static TransferDatabaseHelper dbHelper = null;

    public static synchronized TransferDatabaseHelper getInstance() {
        if (dbHelper != null)
            return dbHelper;
        dbHelper = new TransferDatabaseHelper(SeadroidApplication.getAppContext());
        return dbHelper;
    }

    private TransferDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        // download table
        db.execSQL(SQL_CREATE_DOWNLOAD_TABLE);
        db.execSQL("CREATE INDEX fileid_index ON " + DOWNLOAD_TABLE_NAME
                + " (" + DOWNLOAD_COLUMN_TASKID + ");");
        db.execSQL("CREATE INDEX repoid_index ON " + DOWNLOAD_TABLE_NAME
                + " (" + DOWNLOAD_COLUMN_REPO_ID + ");");
        db.execSQL("CREATE INDEX account_index ON " + DOWNLOAD_TABLE_NAME
                + " (" + DOWNLOAD_COLUMN_ACCOUNT_SIGNATURE + ");");

        // upload table
        db.execSQL(SQL_CREATE_UPLOAD_TABLE);
        db.execSQL("CREATE INDEX id ON " + UPLOAD_TABLE_NAME
                + " (" + UPLOAD_COLUMN_TASKID + ");");
        db.execSQL("CREATE INDEX repoid ON " + UPLOAD_TABLE_NAME
                + " (" + UPLOAD_COLUMN_REPO_ID + ");");
        db.execSQL("CREATE INDEX signature ON " + UPLOAD_TABLE_NAME
                + " (" + UPLOAD_COLUMN_ACCOUNT_SIGNATURE + ");");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        File dir = new File(DataManager.getExternalRootDirectory());
        for (File f : dir.listFiles()) {
            if (f.isFile()) {
                f.delete();
            }
        }

        db.execSQL("DROP TABLE IF EXISTS " + DOWNLOAD_TABLE_NAME + ";");
        db.execSQL("DROP TABLE IF EXISTS " + UPLOAD_TABLE_NAME + ";");
        onCreate(db);
    }
}