Feauture/sk/add sq lite

[Yummy-sk]

https://user-images.githubusercontent.com/60822846/168240740-796d6512-8582-486c-ba5b-8bc297e3b4b9.mp4

Summary

• [x] DB 생성
• [x] Timezone 테이블 생성 및 저장
• [X] Schedule 테이블 생성 및 저장
• [x] Read 구현

Timezone

// DB 생성
export const connectTimezoneDB = async () => { 
  return openDatabase({ name: DB, location: 'default' });
};

// Tmezone 테이블 구성
export const createTimezoneTable = async (db: SQLiteDatabase) => {
  const query = `
    CREATE TABLE IF NOT EXISTS ${TIME_ZONE}
    (
      ID INTEGER PRIMARY KEY AUTOINCREMENT,
      CITY TEXT NOT NULL
    );`;

  await db.executeSql(query);
};

// 아이템 전부 가져오기
export const getTimezoneItems = async (
  db: SQLiteDatabase,
): Promise<Array<ICityProps>> => {
  try {
    const timezoneItems: Array<ICityProps> = [];
    const results = await db.executeSql(`SELECT * FROM ${TIME_ZONE}`);

    results.forEach(result => {
      for (let index = 0; index < result.rows.length; index++) {
        timezoneItems.push(result.rows.item(index));
      }
    });

    return timezoneItems as Array<ICityProps>;
  } catch (e: unknown) {
    console.error(e);
    throw Error('Error in getTimezoneItems');
  }
};

// 아이템 추가
export const insertTimezoneItem = async (db: SQLiteDatabase, city: string) => {
  const insertQuery = `INSERT INTO ${TIME_ZONE} (CITY) VALUES ('${city}')`;

  const result = await db.executeSql(insertQuery);

  const { insertId } = result[0];
  return insertId;
};

// 아이템 삭제
export const deleteTimezoneItem = async (db: SQLiteDatabase, id: number) => {
  const deleteQuery = `DELETE from ${TIME_ZONE} where rowid = ${id}`;
  await db.executeSql(deleteQuery);
};

// 테이블 삭제
export const dropTimezoneTable = async (db: SQLiteDatabase) => {
  const query = `DROP TABLE IF EXISTS ${TIME_ZONE}`;

  await db.executeSql(query);
};

만약, 현재 dropTimezoneTable은 사용하고 있지 않으나, 필요시 호출하면 됩니다.

이 코드는 template > TimeZone.tsx에서 사용됩니다.

Schedule

export const createScheduleTable = async (db: SQLiteDatabase) => {
  const query = `
  CREATE TABLE IF NOT EXISTS ${SCHEDULE}
  (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    TITLE TEXT NOT NULL,
    DESCRIPTION TEXT,
    TAG_COLOR TEXT,
    TARGET_TIME TEXT NOT NULL,
    TARGET_CITY TEXT NOT NULL,
    TARGET_DAY TEXT NOT NULL,
    CUR_TIME TEXT NOT NULL,
    CUR_CITY TEXT NOT NULL,
    CUR_DAY TEXT NOT NULL,
    DAY_OF_WEEK TEXT NOT NULL
  )
  `;

  await db.executeSql(query);
};

export const getScheduleItems = async (
  db: SQLiteDatabase,
  dayOfWeek: string,
  curDay: string,
) => {
  try {
    const scheduleItems: Array<IScheduleProps> = [];
    const query = `
      SELECT * FROM ${SCHEDULE}
      WHERE ( DAY_OF_WEEK LIKE "%${dayOfWeek}%" 
      OR CUR_DAY LIKE "%${curDay}%" )
      ORDER BY CUR_TIME ASC
    `;

    const results = await db.executeSql(query);

    results.forEach(result => {
      for (let index = 0; index < result.rows.length; index++) {
        scheduleItems.push(result.rows.item(index));
      }
    });

    return scheduleItems;
  } catch (e: unknown) {
    console.error(e);
    throw Error('Error in getScheduleItems');
  }
};

export const insertScheduleItem = async (db: SQLiteDatabase, schedule: any) => {
  const {
    title,
    description,
    tagColor,
    targetTime,
    targetDay,
    targetCity,
    curTime,
    curDay,
    curCity,
    dayOfWeek,
  } = schedule;

  const insertQuery = `
    INSERT INTO ${SCHEDULE} (TITLE, DESCRIPTION, TAG_COLOR, TARGET_TIME, TARGET_CITY, TARGET_DAY, CUR_TIME, CUR_CITY, CUR_DAY, DAY_OF_WEEK)
    VALUES (
      '${title}', 
      '${description}', 
      '${tagColor}', 
      '${targetTime}', 
      '${targetCity}',
      '${targetDay}',
      '${curTime}', 
      '${curCity}',
      '${curDay}',
      '${dayOfWeek}'
      )
  `;

  const result = await db.executeSql(insertQuery);

  const { insertId } = result[0];
  return insertId;
};

export const deleteScheduleItem = async (db: SQLiteDatabase, id: number) => {
  const deleteQuery = `DELETE from ${SCHEDULE} where rowid = ${id}`;
  await db.executeSql(deleteQuery);
};

export const dropScheduleTable = async (db: SQLiteDatabase) => {
  const query = `DROP TABLE IF EXISTS ${SCHEDULE}`;

  await db.executeSql(query);
};

@IGhost-P 이 코드 메인에다 머지하고, 처리하신 삭제 로직도 머지되었을 때, develop에서 DB 삭제관련 처리를 하면 될 것 같네요!