Search code examples
androidsqliteandroid-recyclerviewdatabase-performanceandroid-database

Best way to store a file size in SQLite

i am developing gallery app using RecyclerView and for each image i want to store image size in database but i cannot figure out which is the optimal way of storing size in database should i go for string like "2.5 KB" "500 MB" or with real numbers in Mb or bytes please guide me which method is best in performance for reading and writing currently i am getting file size at runtime in RecyclerView's onBindViewHolder method like this

public static String getFormatedSize(Context activityContext, long size) 
    {
        return android.text.format.Formatter.formatShortFileSize(activityContext, size);

    }

but now i want to store predefined size please guide me.

EDIT: why are you guys down voting if you read my question carefully i just want to store image's size not actual image for example image size is 1MB i just want to store size not actual image so please stop down voting

Solution

  • Generally storing numbers as opposed to text is better/more efficient.

    1) A number will likely take less storage considering :-

    INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. Datatypes In SQLite Version 3

    • 8 Bytes is a pretty large number. You should probably save as a long and use the Cursor's getLong method if you need to extract the value for numerical purposes.

    2) Numbers will probably be more flexible and more easily sorted and compared.

    • e.g. How would 1MB, 1KB and 1GB be sorted?

    You could easily convert the number to a formatted String :-

    Assume a table named filesizes were (column is named filesize) :- enter image description here

    Then :-

    SELECT 
CASE 
    WHEN filesize < 1024 THEN filesize || 'B'
    WHEN filesize >=  1024 AND filesize < (1024 * 1024) THEN (filesize / 1024) || 'KB'
    WHEN filesize >= (1024 * 1024)  AND filesize < (1024 * 1024 * 1024) THEN (filesize / (1024 * 1024)) || 'MB'
    WHEN filesize >= (1024 * 1024 * 1024) AND filesize < (1024 * 1024 * 1024 *1024) THEN (filesize / (1024 * 1024 * 1024)) || 'GB'
    WHEN filesize >= (1024 * 1024 * 1024 * 1024) THEN (filesize / (1024 * 1024 * 1024 * 1024)) || 'TB'
END AS size
FROM filesizes

    Would result in :-

    enter image description here

    The following would provide result in values to 2 decimal points (obviously not bytes) :-

    SELECT 
CASE 
    WHEN filesize < 1024 THEN filesize || 'B'
    WHEN filesize >=  1024 AND filesize < (1024 * 1024) THEN ROUND((CAST(filesize AS REAL) / 1024),2) || 'KB'
    WHEN filesize >= (1024 * 1024)  AND filesize < (1024 * 1024 * 1024) THEN ROUND((CAST(filesize AS REAL) / (1024 * 1024)),2) || 'MB'
    WHEN filesize >= (1024 * 1024 * 1024) AND filesize < (1024 * 1024 * 1024 *1024) THEN ROUND((CAST(filesize AS REAL) / (1024 * 1024 * 1024)),2) || 'GB'
    WHEN filesize >= (1024 * 1024 * 1024 * 1024) THEN ROUND((CAST(filesize AS REAL) / (1024 * 1024 * 1024 * 1024)),2) || 'TB'
END AS size
FROM filesizes

    as per :-

    enter image description here

    Of course the disadvantages are :- ?????

    Simple Android Example :-

    DatabaseHelper.java

    public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DBNAME = "ifs";
    public static final int DBVERSION = 1;
    public static final String TBNAME = "imageinfo";
    public static final String IITABLE_ID_COL = BaseColumns._ID;
    public static final String IITABLE_IMAGENAME_COL = "image_name";
    public static final String IITABLE_IMAGESIZE_COL = "image_filesize";
    public static final String IITABLE_FORMATTED_COL = "size";
    SQLiteDatabase mDB;

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crtsql = "CREATE TABLE IF NOT EXISTS " +
                TBNAME + "(" +
                IITABLE_ID_COL + "INTEGER PRIMARY KEY, " +
                IITABLE_IMAGENAME_COL + " TEXT, " +
                IITABLE_IMAGESIZE_COL + " INTEGER" +
                ")";
        db.execSQL(crtsql);
    }

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

    }

    public long insertRow(String imagename, long filesize) {
        ContentValues cv = new ContentValues();
        cv.put(IITABLE_IMAGENAME_COL,imagename);
        cv.put(IITABLE_IMAGESIZE_COL,filesize);
        return mDB.insert(TBNAME,null,cv);
    }
    /*
        SELECT filesize,
        CASE
            WHEN filesize < 1024 THEN filesize|| 'B'
            WHEN filesize >= 1024 AND filesize < 1048576  THEN ROUND((CAST(filesize AS REAL) / 1024),2) || 'KB'
            WHEN filesize >= 1028576  AND filesize < 1073741824 THEN ROUND((CAST(filesize AS REAL) / 1028576),2) || 'MB'
            WHEN filesize >= 1073741824 AND filesize < 1099511627776 THEN  ROUND((CAST(filesize AS REAL) /1073741824 ),2) || 'GB'
            WHEN filesize >= 1099511627776 THEN ROUND((CAST(filesize AS REAL) /1099511627776 ),2) || 'TB'
        END AS size
        FROM filesizes
     */
    public Cursor getFormattedImageList() {
        long kilobytes = 1024,
                megabytes = kilobytes * kilobytes,
                gigabytes = megabytes * kilobytes,
                terabytes = gigabytes * kilobytes;
        String formatted_column = "CASE" +
                // Bytes
                " WHEN " + IITABLE_IMAGESIZE_COL + " < " + kilobytes +
                " THEN " + IITABLE_IMAGESIZE_COL + " || 'B'" +

                // Kilobytes
                " WHEN " + IITABLE_IMAGESIZE_COL + ">= " + kilobytes +
                " AND " + IITABLE_IMAGESIZE_COL + " < " + megabytes +
                " THEN ROUND((CAST(" +
                IITABLE_IMAGESIZE_COL + " AS REAL) / " + kilobytes + "),2) || 'KB'" +

                // MegaBytes
                " WHEN " + IITABLE_IMAGESIZE_COL + ">= " + megabytes +
                " AND " + IITABLE_IMAGESIZE_COL + " < " + gigabytes +
                " THEN ROUND((CAST(" +
                IITABLE_IMAGESIZE_COL + " AS REAL) / " + megabytes + "),2) || 'MB'" +

                // GigaBytes
                " WHEN " + IITABLE_IMAGESIZE_COL + ">= " + gigabytes +
                " AND " + IITABLE_IMAGESIZE_COL + " < " + terabytes +
                " THEN ROUND((CAST(" +
                IITABLE_IMAGESIZE_COL + " AS REAL) / " + gigabytes + "),2) || 'GB'" +

                // Terabytes
                " WHEN " + IITABLE_IMAGESIZE_COL + ">= " + terabytes +
                " THEN ROUND((CAST(" +
                IITABLE_IMAGESIZE_COL + " AS REAL) / " + terabytes + "),2) || 'TB'" +

                " END AS " + IITABLE_FORMATTED_COL;
        Log.d("SQLCASE",formatted_column);
        String[] columns = new String[]{IITABLE_IMAGENAME_COL,formatted_column,IITABLE_IMAGESIZE_COL};
        return mDB.query(TBNAME,columns,null,null,null,null,IITABLE_IMAGENAME_COL);
    }
}

    MainActivity.java

    public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // Instantiate the Database Helper
        DatabaseHelper mDBHlpr = new DatabaseHelper(this);
        // Add some rows to the imageinfo table
        mDBHlpr.insertRow("The Church",432567L);
        mDBHlpr.insertRow("The Shop", 200L);
        mDBHlpr.insertRow("The Green", 123456789L);
        mDBHlpr.insertRow("The Petrol Station", (1024L * 1024L * 1024L) + 24L);
        mDBHlpr.insertRow("The House", 1234567890104444440L);

        // Extract a formatted list
        Cursor csr = mDBHlpr.getFormattedImageList();
        while (csr.moveToNext()) {
            Log.d("IMAGES",
                    "Image is " +
                            csr.getString(
                                    csr.getColumnIndex(
                                            DatabaseHelper
                                                    .IITABLE_IMAGENAME_COL
                                    )
                            ) +
                            " Size is " +
                            csr.getString(
                                    csr.getColumnIndex(
                                            DatabaseHelper
                                                    .IITABLE_FORMATTED_COL
                                    )
                            ) +
                            " (" +
                            String.valueOf(
                                    csr.getLong(
                                            csr.getColumnIndex(
                                                    DatabaseHelper
                                                            .IITABLE_IMAGESIZE_COL
                                            ))) +
                            ")"
            );
        }
        csr.close();
    }
}

    Output to the Log :-

    01-10 21:57:36.552 2619-2619/? D/SQLCASE: CASE WHEN image_filesize < 1024 THEN image_filesize || 'B' WHEN image_filesize>= 1024 AND image_filesize < 1048576 THEN ROUND((CAST(image_filesize AS REAL) / 1024),2) || 'KB' WHEN image_filesize>= 1048576 AND image_filesize < 1073741824 THEN ROUND((CAST(image_filesize AS REAL) / 1048576),2) || 'MB' WHEN image_filesize>= 1073741824 AND image_filesize < 1099511627776 THEN ROUND((CAST(image_filesize AS REAL) / 1073741824),2) || 'GB' WHEN image_filesize>= 1099511627776 THEN ROUND((CAST(image_filesize AS REAL) / 1099511627776),2) || 'TB' END AS size
01-10 21:57:36.552 2619-2619/? D/IMAGES: Image is The Church Size is 422.43KB (432567)
01-10 21:57:36.552 2619-2619/? D/IMAGES: Image is The Green Size is 117.74MB (123456789)
01-10 21:57:36.552 2619-2619/? D/IMAGES: Image is The House Size is 1122832.96TB (1234567890104444440)
01-10 21:57:36.552 2619-2619/? D/IMAGES: Image is The Petrol Station Size is 1.0GB (1073741848)
01-10 21:57:36.552 2619-2619/? D/IMAGES: Image is The Shop Size is 200B (200)