آشنایی با دستورات SQL و دیتابیس SQLite در اندروید

دیتابیس (database) چیست؟

برای ذخیره داده‎ها راه‎های مختلفی وجود دارد، به عنوان مثال استفاده از فایل یا زبان‎هایی مثل JSON یا XML. یک راه نسبتا بهتر برای داده‎هایی با حجم بالا Database (دیتابیس یا پایگاه داده) است. در یک دیتابیس رابطه ای (Relational Database) ذخیره سازی داده ها ساختار جدولی دارد. در هر ستون ما ویژگی‎ها را داریم و هر ردیف یک آیتم و یک رکورد است.

فواید استفاده از دیتابیس

• دلایل زیادی برای استفاده از دیتابیس است که شاید مهم‎ترین و اولین قدرتمندتر بودن آن باشد.
• سریع تر است و query ها در زمان کمتری نتیجه می‎دهند.
• برای داده‎هایی با حجم بالا مناسب است.
• امن است چون متدها و روش‎هایی برای بازیابی اطلاعات دارد.
• مانند فایل نیست و چندین کاربر میتوانند view و edit کنند.

ابزار کار با دیتابیس

دیتابیس های مختلفی وجد دارند  که هر کدام ویژگی‎های خود را دارند و بسته به شرایط مناسب یا نامناسب هستند:

• Oracle
• Microsoft SQL Server
• Microsoft Access
• PostgreSQL
• SQLite
• MySQL

جداول پایگاه داده

همانگونه که بیان شد دیتابیس ساختار جدولی دارد. بعضی از این جداول بیانگر نوع موجودیت یا Entity و بعضی از آن ها بیانگر رابطه یا Relation هستند. معمولا ابتدا آن ها بوسیله طراحان در مدل ERD (Entity Relation Diagram) طراحی شده و سپس پیاده سازی میشوند.

در هر جدول یک ستون وجود دارد که PK یا کلید اصلی است به این معنی که هیچ دو رکوردی یک PK یکسان ندارد و میتوان بوسیله آن یک رکورد یکتا انتخاب شود.

برای برقراری ارتباط بین جداول باید PK جدول دیگر را در یک جدول دیگر استفاده کنیم. در این صورت آن کلید اصلی در جدول جدید یک FK یا کلید خارجی می‎شود.

به این موضوع توجه داشته باشید که طراحی پایگاه داده خود بحث وسیعی است و نیاز به آموزش کامل تری دارد. در این نوشته با موارد ابتدایی آن آشنا خواهید شد تا در اندروید با پیاده‎سازی آن آشنا شویم.

زبان SQL

SQL یا Structured Query Language زبانی است که ما می‎توانیم با استفاده از آن ساختار دیتابیس را ساخته تغییر داد و اطلاعات را خواند، ثبت کرد، آپدیت کرد و حذف کرد.  (CRUD Create Read Update Delete)

برای استفاده از دیتابیس باید به صورت اجمالی با دستورات SQL آشنا بشویم.

دستور CREATE/DELETE DATABASE

ساخت یک پایگاه داده جدید با استفاده از دستور CREATE DATABASE است. یک دیتابیس شامل تعدادی جدول است.

CREATE DATABASE name;
DROP DATABASE name;
CREATE DATABASE warcraft;
USE warcraft;

برای استفاده از دیتابیس باید از use استفاده کرد.

دستور CREATE/DELETE TABLE

برای ساختن یک جدول در یک دیتابیس ابتدا آن را USE کرده سپس به صورت زیر عمل میکنیم:

CREATE TABLE IF NOT EXISTS name (
   columnName type constraints,
   ...
   columnName type constraints
);

DROP TABLE name;
 
CREATE TABLE students (
   id INTEGER,
   name VARCHAR(20),
   email VARCHAR(32),
   password VARCHAR(16)
);

برای هر ستون باید طول و نوع متغیر را تعریف کنید.

دستور SELECT

انتخاب و خواندن یک سری داده از جدول(ها) بوسیله این دستور انجام میشود.

SELECT column(s) FROM table WHERE condition;
SELECT name, population FROM cities;

با * تمام ستون‎ها را می‎توان انتخب کرد.
با SELECT DISTINCT می‎توان فقط رکوردهای غیر تکراری را انتخاب کرد.

عبارت WHERE

برای فیلتر کردن اطلاعات از where استفاده می‎شود و شرط‎هایی که نوشته می‎شوند با استفاده از موارد زیر نوشته می‎شود:

=, >, >=, <, <=
not equal (some systems support != )
BETWEEN min AND max
LIKE pattern ) put % on ends to search for prefix/suffix/substring(
IN (value, value, ..., value)
condition1 AND condition2 ; condition1 OR condition2;

به عنوان نمونه دستورات SELECT زیر را ببینید:

SELECT name, population FROM cities WHERE country_code = "FSM";
SELECT name, gnp FROM countries WHERE gnp > 2000000;
SELECT * FROM cities WHERE code = 'USA' AND population >= 2000000;
SELECT code, name, population FROM countries WHERE name LIKE 'United%';

عبارت ORDER BY و LIMIT

برای مرتب سازی و محدود کردن از این دو کلیدواژه استفاده میشود.

SELECT code, name, population FROM countries WHERE name LIKE 'United%' ORDER BY population;
SELECT * FROM countries ORDER BY population DESC, gnp; 
SELECT name FROM cities WHERE name LIKE 'K%' LIMIT 5;

DESC و ASC باعث می‎شود نتیجه نزولی یا صعودی مرتب شود. در اعداد ارزش آن معیار است و در کلمات ترتیب حروف الفبا.

JOIN

زمانی که چندین جدول با هم رابطه دارند برای نشان دادن اطلاعات آن باید با استفاده از کلید خارجی FK و دستور JOIN میتوان اشتراک چندین table را بدست آورد. بعنوان مثال:

SELECT column(s) FROM table1 name1
   JOIN table2 name2 ON condition(s)
   ...
   JOIN tableN nameN ON condition(s)
   WHERE condition;

SELECT name, course_id, grade FROM students s JOIN grades g ON s.id = g.student_id 
WHERE s.name = 'Bart';

دستور INSERT

جهت ثبت داده های جدید در دیتابیس از دستور INSERT‌ استفاده می شود:

INSERT INTO table (columnName, ..., columnName) VALUES (value, value, ..., value);
INSERT INTO students (name, email) VALUES ("Lewis", "lewis@fox.com");

دستور Update

جهت تغییر داده ها در دیتابیس از دستور UPDATE استفاده می شود:

UPDATE table SET column1 = value1,
		...,
		columnN = valueN
		WHERE condition;
 
UPDATE students SET email = "lisasimpson@gmail.com" WHERE id = 888;

دستور DELETE

جهت حذف داده ها در دیتابیس از دستور DELETE‌ استفاده می شود:

DELETE FROM table WHERE condition;
DELETE FROM students WHERE id = 888;

دستور ALTER

در صورت نیاز میتوانید ساختار جدول ها را با استفاده از دستور ALTER تغییر دهید:

--تغییر نام جدول
ALTER TABLE name RENAME TO newName; 

--اضافه کردن ستون 
ALTER TABLE name ADD COLUMN columnName type constraints; 

--حذف یک ستون 
ALTER TABLE name DROP COLUMN columnName;  

-- تغییر نوع 
ALTER TABLE name CHANGE COLUMN oldColumnName newColumnName type constraints; 

کار با دیتابیس SQLiteDatabase در اندروید

برای استفاده از پایگاه داده در اندروید سه قدم کلی در پیش داریم:

• تعریف ساختار داده ها (اسکیما : schema)
• ساختن SQLiteOpenHelper
• استفاده کردن از دیتابیس در کد جاوا

DB Schema

ابتدا باید دیتابیس به طور کامل و صحیح طراحی شده باشد. بعنوان مثال یک برنامه ساده تسک منیجر در نظر بگیرید. در اینصورت ما دو نوع موجودیت داریم یک user و دیگری task و چون چندی رابطه بین آن‎ یک به n است. در نتیجه ما به دو جدول نیاز داریم و جدول جدیدی برای نمایش relation آن‎ها لازم نیست. بطور خلاصه شکل دیاگرام بصورت زیر است.

بعد از طراحی دیتابیس یک کلاس POJO (Plain Old Java Object)  ساخته و به ازای هر جدول یک کلاس داخل آن ساخته میشود و نام  آن و ستون های آن بصورت ثابت در آن ذخیر میشوند. بعنوان مثال همین برنامه به صورت زیر نمایش داده میشود.

کد اندروید لازم به صورت زیر خواهد بود:

public class TaskManagerDBSchema {

    public static final String NAME = "task_manager.db";

    // Tables
    public static final class Users {

        public static final String NAME = "users";

        public static final class Cols {
            public static final String _ID = "_id";
            public static final String USERNAME = "username";
            public static final String PASSWORD = "password";
        }
    }

    public static final class Tasks {

        public static final String NAME = "tasks";

        public static final class Cols {
            public static final String _ID = "_id";
            public static final String USER_ID = "user_id";
            public static final String TITLE = "title";
            public static final String DESCRIPTION = "description";
            public static final String DATE = "date";
            public static final String STATUS = "status";
        }
    }
}

بدین شکل کلاس SQLSchema ساخته شده و به مرحله دوم میرویم.

SQLiteOpenHelper

مرحله دوم ساختن OpenHelper است که از SQLiteOpenHelper ارث‎بری میکند. باید constructor و دو متد onCreate و onUpdate را برای آن پیاده سازی نمود.

public class TaskManagerOpenHelper extends SQLiteOpenHelper {

    public static final int VERSION = 1;

    public TaskManagerOpenHelper(@Nullable Context context) {
        super(context, TaskManagerDBSchema.NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

        sqLiteDatabase.execSQL("CREATE TABLE " + TaskManagerDBSchema.Users.NAME + "(" +
                TaskManagerDBSchema.Users.Cols._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                TaskManagerDBSchema.Users.Cols.USERNAME + ", " +
                TaskManagerDBSchema.Users.Cols.PASSWORD +
                ");");

	...

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

در constructor ورژن را به عنوان ورودی به super() می‎دهیم. علاوه بر آن متد onCreate برای اولین دفعه اجرا است. در حقیقت در این جا می‎آید چک می‎کند نسخه‎ای قدیمی‎تر بوده onUpdate را اجرا می‎کند و در صورتی که در آپدیت جدید تغییری رخ داده باشد کدها و دستورات لازم برای اجرای SQL را آن‎جا قرار دهیم و درصورتی که هیچ دیتابیسی نبوده باید table ها در onCreate برای شروع استفاده از database ساخته شوند. به همین ترتیب همه‎ی table ها باید ساخته شود.

در این جا میبینید با متد exeSQL می‎توان کد خام و هر نوع Query دلخواه برای اجرا روی database داده شود.

استفاده کردن از دیتابیس در کد جاوا

حال همه‎چیز برای استفاده از دیتابیس در برنامه اندروید مهیا است. با داشتن context و با استفاده از OpenHelper ساخته شده میتوان آبجکت SQLiteDatabase را بگیریم.

public class Repository {

    // Singleton Repository
    private SQLiteDatabase database;
    private Context context;

    private Repository(Context context) {
        this.context = context.getApplicationContext();
        this.database = new TaskManagerOpenHelper(context).getWritableDatabase();
    }
    …
}

با دادن context و متد getWriteableDatabase اکنون قادریم تغییراتی در آن ایجاد کنیم یا اطلاعاتی استخراج کنیم. دقت کنید که داده هایی که میخواهیم لحاظ کنیم باید از طریق یک نوع داده به نام ContentValue انجام بشود. بعنوان مثال برای اضافه کردن یک user به جدول داریم.

public void addTaskForUser(String userId, Task task) {
    int id = task.getId();
    ContentValues contentValues = new ContentValues();
    contentValues.put(TaskManagerDBSchema.Task.Cols.TASK_ID, id);
    contentValues.put(TaskManagerDBSchema.Task.Cols.USER_ID, Integer.parseInt(userId));

    database.insertOrThrow(TaskManagerDBSchema.Task.NAME, // اسم جدول
		null, 	ستون ها //
		contentValues);  مقادیر // 
}

همانطور که دیده میشود با استفاده از یک نوع داده به نام ContentValue که ساختار key  و value دارد یک user به دیتابیس اضافه میگردد. متدهای دیگر برای استفاده از دیتابیس:

هنگام کوئری گرفتن که به شکل زیر است نوعی که برگرداننده میشود از نوع Cursor است و باید آن را iterate کرد تا به ته cursor رسید. در حقیقت cursor شامل تعدادی رکورد است که بر اساس query زده شده شما در آن وجود دارند.  ورودی‎های یک query به صورت زیر است:

Cursor cursor = db.query(
    FeedEntry.TABLE_NAME,   // جدول مورد نظر
    projection,             // ستون ها
    selection,              // WHERE 
    selectionArgs,          // The values for the WHERE clause
    null,                   // don't group the rows
    null,                   // don't filter by row groups
    sortOrder               // مرتب سازی ها
    );

برای مثال در برنامه نمونه TaskManagement خواهیم داشت:

// TaskManagerExample (get the user object by username)
Cursor cursor = database.query(TaskManagerDBSchema.Users.NAME,
	null, TaskManagerDBSchema.Users.Cols.USERNAME + " = ? ",
	new String[]{username}, null, null, null);
try {
	while (cursor.moveToNext()){
		…
		long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
		cursor.moveToNext();
	}
} finally {
	cursor.close();
}

ما برای درج اطلاعات باید اطلاعات را به ContentValue تبدیل کنیم و همینطور برای خواندن آنها باید آنها را به گونه ای parse کنیم البته کلاسی به نام  CursorWrapper کار ما را راحت تر کرده و آبجکت مورد نظر را به ما میدهد.

کلاس CursorWrapper

با استفاده از کلاس CursorWrapper میتوان تبدیل Content Value  های دربافت شده را در جا در هنگام کوئری گرفتن انجام داد. مقادیر در آبجکت مورد نظر set میشوند. به عنوان مثال به جای یک نوع داده ای Content Value یک مدل مانند User برگردانده شود.

برای ساختن این کلاس به شکل زیر عمل میکنیم:

برای هر table یک متد با نام همان getEntityName اضافه کرده و مقدار برگشتی آن را همان نوع متناظر با مدلملن میگذاریم سپس نحوه کاری را که هر دفعه باید تکرار شود را پیاده سازی مینماییم.

public class TaskManagerCursorWrapper extends CursorWrapper {
   public User getUser() {
      int id = getInt(getColumnIndex(TaskManagerDBSchema.Users.Cols._ID));
      String username = getString(getColumnIndex(TaskManagerDBSchema.Users.Cols.USERNAME));
      String password = getString(getColumnIndex(TaskManagerDBSchema.Users.Cols.PASSWORD));
      User user = new User(id, username, password);
      
      return user;
   }
}

در نهایت باید تغییری نیز در هنگام خواندن cursor انجام بدهیم.

Cursor cursor = database.query(TaskManagerDBSchema.Users.NAME,
   null, TaskManagerDBSchema.Users.Cols.USERNAME + " = ? ",
   new String[]{username}, null, null, null);

TaskManagerCursorWrapper cursorWrapper =
   new TaskManagerCursorWrapper(cursor);
try {   
   cursorWrapper.moveToFirst();   
   while (!cursorWrapper.isAfterLast()) {   
       myUserList.add(cursorWrapper.getUser());
       cursor.moveToNext();   
   } 
} finally { 
   cursor.close();
   cursorWrapper.close();
}

با استفاده از getUser در جا آبجکت مدلمان را در دست داریم و نیازی به parse کردن اطلاعات دیگر نداریم.

ORM چیست؟

لازم است توضیح کوتاهی در مورد ORM یا Object Relational Mapper بدهیم. کار با ORM بسیار ساده است و به گونه ای میتوان پل ارتباطی بین آبجکت‎ها یا مدل‎های برنامه و دیتابیس ما باشد. کتابخانه‎های معروف ORM شامل Room یا GreenDao  هستند. دلیل استفاده و فواید آن ها هم همین ORM بودن است و هم اینکه به داشتن cache دارای عملکرد خوبی هستند.