JS SQLITE, read one database and write to another

Question

I have the following function which reads approx 15,000 rows from one database, formats the data and writes to another. This is currently taking about 6 seconds to execute. Is there a way to do this without using javascript to format the data, and just using an sqlite query? Or another way to speed it up?

Thanks

      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();
      };

CL. · Accepted Answer · 2013-04-05 14:20:32Z

1

Attach one database to the other, then use 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;

answered Apr 5, 2013 at 14:20

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex McDaid Over a year ago

Amazing. Thank You. (null) is because a third party function is already reading the database.

Collectives™ on Stack Overflow

JS SQLITE, read one database and write to another

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related