JS SQLITE，读取一个数据库并写入另一个数据库

JS SQLITE, read one database and write to another

本文关键字：数据库一个另一个 SQLITE 读取 JS 更新时间：2023-09-26

我有以下函数，它从一个数据库读取大约15,000行，格式化数据并写入另一个数据库。当前执行该操作大约需要6秒。有没有一种方法可以做到这一点，而不使用javascript格式化数据，只是使用sqlite查询?或者用其他方法加快速度?

感谢

      var events = db.open(events.sqlite3);
      function updateTimetable() {
        var rows = users.execute("SELECT id, complete, strftime('%s000',time) AS realTime, strftime('%H:%M',time) AS Time FROM exerciseLists WHERE patientId = ? ORDER BY time ASC", Alloy.Globals.currentUser);
          events.execute("DELETE FROM Events");
          events.execute('BEGIN');
          while (rows.isValidRow()) {
              var colorType;
              var active;
              var time = rows.fieldByName('Time');
              var realTime = rows.fieldByName('realTime');
              var now = new Date().getTime();
              if (realTime < now) {
                  colorType = 'red';
                  active = 'no';
              } else {
                  colorType = 'blue';
                  active = 'no';
              };
              if (rows.fieldByName('complete')) {
                  colorType = 'yellow';
              }
              if (Alloy.Globals.sessionButton.button.sessionId === rows.fieldByName('id')) {
                  colorType = 'green';
                  active = 'yes';
              }
              function pad(n) {
                  return n < 10 ? '0' + n : n
              }
              var d = new Date(parseInt(realTime));
              var dYear = d.getFullYear();
              var dDay = pad(d.getDate());
              var dMonth = pad(d.getMonth()+1);
              var dHour = pad(d.getHours()+1);
              var dMinute = pad(d.getMinutes()+1);
              var dFinal = dYear + "-" + dMonth + "-" + dDay + " " + dHour + ":" + dMinute;
              events.execute("INSERT INTO Events (title, date_start, date_end, note, location, identifier, type, attendees, organizer) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", time, dFinal, '(null)', rows.fieldByName('id').toString(), colorType, rows.fieldByName('id').toString(), active, '(null)', '(null)');
              rows.next();
          }
          rows.close();
          events.execute('COMMIT');
          Alloy.Globals.refreshTimetable();
      };

将一个数据库连接到另一个数据库，然后使用INSERT ... SELECT ...:

ATTACH 'users.sqlite' AS users_db;
INSERT INTO Events (title, date_start, date_end,
                    note, location, identifier,
                    type, attendees, organizer)
SELECT strftime('%H:%M', time),
       strftime('%Y-%m-%d %H:%M', time),
       '(null)',  -- why not NULL?
       id,
       CASE WHEN id = ? /* sessionId */   THEN 'green'
            WHEN complete                 THEN 'yellow'
            WHEN time < CURRENT_TIMESTAMP THEN 'red'
            ELSE                               'blue'
       END,
       id,
       CASE WHEN id = ? /* sessionId */ THEN 'yes'
            ELSE                             'no'
       END,
       '(null)',
       '(null)'
FROM users_db.exerciseLists
WHERE patientId = ?
ORDER BY time;