tasks

tasks — Task registry backed by SQLite (via sigma/sqlite JDBC wrapper).

Overview

A persistent task registry to schedule and track execution of automation scripts across devices.

• Main table: tasks (one row per task)
• Child table: task_paths (one task → many paths)
• Devices master: task_devices (deduplicated by (device_name, device_sn))
• Relation table: task_path_devices (many-to-many between paths and devices)

The public API returns camelCase fields and keeps backward-friendly names where required。

Key Features

• Connection & Pragmas: WAL, NORMAL sync, busy timeout.
• Create: tasksCreate() supports both the simple signature and a full object with options.
• Update: tasksUpdate() handles camelCase → snake_case mapping for main task fields and
  recomputes plan_time when repeat/time is changed.
• Delete: tasksDelete() & tasksDeleteMany() with cascade cleanup via FKs.
• Query: tasksList() / tasksGet() aggregate paths and devices into options for output.
• Search/Count: LIKE-based search across key fields; status grouping.
• Controls: tasksPause() / tasksResume() / tasksStop() delegate to the underlying executor.

Status Values (negative convention kept)

0 : FILE_NOT_FOUND — Script path does not exist (pre-run validation failure)
-1 : RUNNING
-2 : STOPPED_NORMAL
-3 : STOPPED_ERROR
-4 : PAUSED
-5 : USER_TERMINATED
-6 : SCHEDULED (default upon creation)

Storage — Tables

tasks (main)

• id INTEGER PK AUTOINCREMENT
• auto_connect INTEGER NOT NULL DEFAULT 0
• auto_select INTEGER NOT NULL DEFAULT 0
• start_time INTEGER NOT NULL // ms epoch
• end_time INTEGER // ms epoch
• error_message TEXT NOT NULL DEFAULT ''
• exec_count INTEGER NOT NULL DEFAULT 1
• exec_device TEXT NOT NULL DEFAULT '-'
• expiration INTEGER NOT NULL DEFAULT 0
• iter_count INTEGER NOT NULL DEFAULT 1
• plan_time INTEGER // ms epoch; computed from time/repeat
• re_exec INTEGER NOT NULL DEFAULT 0
• status INTEGER NOT NULL DEFAULT -6
• task_id INTEGER NOT NULL UNIQUE // business-visible id
• task_name TEXT NOT NULL UNIQUE
• week_json TEXT // optional serialized repeat weekdays ["0".."6"]
• created_at INTEGER NOT NULL // ms epoch
• updated_at INTEGER NOT NULL // ms epoch
• timezone TEXT NOT NULL // e.g. "Asia/Shanghai"
• sigma_test_status TEXT DEFAULT NULL // e.g. "{T:320,P:280,F:30,E:10}"

Indexes: by status, plan_time, updated_at, and LIKE on task_name.

task_paths

• id INTEGER PK AUTOINCREMENT
• task_row_id INTEGER NOT NULL // FK → tasks.id (row id)
• path TEXT NOT NULL
• created_at INTEGER NOT NULL // ms epoch
• updated_at INTEGER NOT NULL // ms epoch

task_devices (master)

• id INTEGER PK AUTOINCREMENT
• device_name TEXT NOT NULL
• device_sn TEXT NOT NULL
• created_at INTEGER NOT NULL // ms epoch
• updated_at INTEGER NOT NULL // ms epoch
• UNIQUE(device_name, device_sn)

task_path_devices (relation)

• (task_path_id INTEGER, task_device_id INTEGER) as composite PK
• created_at INTEGER NOT NULL // ms epoch
• FKs cascade on delete of path/device

Returned Shapes

Both tasksList() and tasksGet() return objects in this format (example array with one item):

[{
"autoConnect": false,
"autoSelect": false,
"startTime": 1757922442739,
"endTime": 1757922442782,
"errorMessage": "",
"execCount": 1,
"execDevice": "-",
"expiration": 0,
"iterCount": 1,
"reExec": false,
"options": [
    { "device_names": [], "device_sn": [], "path": "C:/.../1.js" }
],
"status": -2,
"taskID": 1759213730566,
"taskName": "1111(3)",
"createdAt": 1757922442739,
"updatedAt": 1757922442739,
"timezone": "Asia/Shanghai",
"sigma_test_status": "{T:320,P:280,F:30,E:10}"
}]

Notes:

• createdAt / updatedAt are long (ms epoch), not ISO strings.

Simple Overload Enhancements

• Adds support for tasksCreate(name, scriptFile, options) signature.
• The options object may include:
  • iteration: number — number of executions.
  • time: string — "YYYY-MM-DD HH:mm[:ss]" (one-shot) or "HH:mm[:ss]" (repeat).
  • repeat: number[] — weekdays [0..6]; e.g., [1,3,5] for Mon/Wed/Fri.
  • deviceName: string|string[] — single device (also accepts one-element array).
  • deviceNames: string[] — multiple devices.
  • deviceSN: string|string[] and deviceSNs: string[] — optional serial numbers.
• When the counts of deviceName(s) and deviceSN(s) differ, the shorter array is automatically padded with empty strings "".
• All inserts (tasks, task_paths, and task_path_devices) occur within the same transaction.
• Backward compatible with (name, scriptFile, iteration?, time?, repeat?).

Example Usage

var tasks = require("sigma/tasks");

// Simple creation (single path, no devices):
tasks.tasksCreate("nightly", "C:/Scripts/a.js", 1, "2025-12-01 08:00");


// Listing (aggregates options from normalized tables):
var list = tasks.tasksList({ status: tasks.STATUS.SCHEDULED, limit: 20 });
print(JSON.stringify(list, null, 2));

// Simplified object overload
tasksCreate("task001", "F:/test/test.js", { iteration: 2 });
tasksCreate("task002", "F:/test/test.js", { iteration: 1, time: "2020-06-30 14:30" });
tasksCreate("task003", "F:/test/test.js", { iteration: 1, time: "14:30", repeat: [3,5] });
// Single device
tasksCreate("task004", "F:/File/text.js", { iteration: 1, time: "14:30", repeat: [3,5], deviceName: ["PIONEER K88L"] });
// Multiple devices
tasksCreate("task005", "E:/File/test.js", { iteration: 1, time: "2020-07-30 15:30", deviceNames: ["PIONEER K88L","PIONEER K88L1"] });
// If deviceNames and deviceSNs differ in length, the shorter is automatically padded with ""

Design Notes

• All timestamps use ms epoch. Timezone is captured per task at creation/update.
• Devices are deduplicated globally; many-to-many relation binds devices to per-path rows.
• CRUD on tasks cascades to paths and relations via FKs. Device master rows are retained
  unless you explicitly garbage-collect unreferenced devices.

Functions

hasJsExt("main.js");    // → true
hasJsExt("unit.tst");   // → true
hasJsExt("macro.scp");  // → true
hasJsExt("readme.txt"); // → false

// Example 1: Change all SCHEDULED (-6) tasks whose name contains "nightly" to RUNNING (-1)
tasksChangeStatus(STATUS.SCHEDULED, STATUS.RUNNING, {
  nameLike: "nightly"
});

// Example 2: Pause specific tasks by mixed identifiers (row id, task_id, or task_name)
tasksChangeStatus(STATUS.RUNNING, STATUS.PAUSED, {
  idsOrNames: [42, "9876543210", "daily_sync"]
});

// Example 3: Perform large updates in batches of 500 rows
let offset = 0, totalUpdated = 0;
while (true) {
  const r = tasksChangeStatus(STATUS.SCHEDULED, STATUS.FILE_NOT_FOUND, {
    limit: 500,
    offset
  });
  if (!r || r.matched === 0) break;
  totalUpdated += r.updated;
  offset += 500;
}
console.log("Total updated rows:", totalUpdated);

// Example 4: No-op when fromStatus and toStatus are the same
// => returns { matched: 0, updated: 0, ids: [] }
tasksChangeStatus(STATUS.PAUSED, STATUS.PAUSED);

// Count everything
var { total, byStatus } = tasksCount();

// Count only RUNNING and SCHEDULED on a specific local day
var res = tasksCount({
  status: [STATUS.RUNNING, STATUS.SCHEDULED],
  endTimeOn: "2025-09-15"
});
// res.total -> total rows whose status ∈ {-1,-6} on that day

// Repeat every Mon & Wed at 00:00 local time:
var tid = tasksCreate("close huawei camera", "C:\\scripts\\close-huawei-camera.js", undefined, undefined, [1,3]);

// One-shot at a fixed datetime:
var tid2 = tasksCreate("one-shot", "E:\\File\\test.js", 2, "2025-09-01 08:00");

// One-time task with seconds
var tid3 = tasks.tasksCreate("one-shot-with-seconds", "E:\\File\\test.js", 1, "2025-09-01 08:01:20");

// Weekly task with seconds (if repeat supports seconds)
var today = (new Date()).getDay();
var tid4 = tasks.tasksCreate("weekly-with-seconds", "E:\\File\\test.js", 1, "08:01:20", [today, (today + 2) % 7]);

// 1) Non-atomic: expect partial success
var r1 = tasksCreateMany([
  { name: "cm_ok1", scriptFile: "C:/a.js" },
  { name: "cm_dup", scriptFile: "C:/a.js" }, // duplicate on purpose
  { name: "cm_ok2", scriptFile: "C:/a.js" }
], { atomic: false });
// r1.created >= 1 and r1.failed >= 1

// 2) Atomic: duplicate causes whole batch to fail
var r2 = tasksCreateMany([
  { name: "cm_ok1", scriptFile: "C:/a.js" },
  { name: "cm_dup", scriptFile: "C:/a.js" }, // duplicate
  { name: "cm_ok2", scriptFile: "C:/a.js" }
], { atomic: true });
// r2.created === 0, r2.failed === 3

// 3) Mixed shapes are fine
var r3 = tasksCreateMany([
  { name: "jobA", scriptFile: "C:/a.js", time: "2025-12-01 08:00" }, // simplified
  {
    taskName: "jobB",                                         // full
    options: [
      { path: "C:/x.js", device_names:["P40"], device_sn:["XYZ"] }
    ],
    iteration: 1,
    repeat: [1,3,5],
    time: "08:30"
  }
]);

tasksCreateMany([
  { name:"jobA", scriptFile:"C:\\a.js", repeat:[1,3], time:"08:00" },
  { name:"jobB", scriptFile:"C:\\b.js", iteration:2, time:"2025-09-01 08:00" }
]);

tasksCreateMany(
  [{ name:"A", scriptFile:"C:\\a.js" }, { name:"B", scriptFile:"C:\\b.js"}],
  { atomic: true }
);

tasksDelete("nightly-sync");
tasksDelete(12);        // row id
tasksDelete("1000234"); // taskID (string-numeric)

var out = tasksDeleteMany(["nightly", 12, "1002001"]);

// or
var out = tasksDeleteMany(["nightly", 12, "1002001"], { atomic: true });

if (tasksExists("nightly")) print("exists");

tasksGet("nightly");         // by taskName
tasksGet(42);                // by row id
tasksGet("1759213730566");   // by taskID (string)

// 1) All tasks (no filters)
tasksList();

// 2) Multiple status on a specific local day
tasksList({
  status: [STATUS.SCHEDULED, STATUS.RUNNING],
  endTimeOn: "2025-09-15",
  limit: 50
});

// 3) Single status on a specific local day
tasksList({ status: STATUS.SCHEDULED, endTimeOn: "2025-09-15", limit: 20 });

// 4) LIKE on task_name
tasksList({ nameLike: "promo_50%_off" }); // literal '%' and '_' supported

// Example: Create a task, wait for 5 seconds, then pause it.

var { tasksCreate, tasksPause } = require("sigma/tasks");
var task001 = tasksCreate('task001', 'E:/File/test.js', 1);
if (task001 === true) {
  delay(5000);
  var ret = tasksPause('task001');
  if (ret === true) {
    print("Congratulations, this API executes successfully.\nReturn value: " + ret);
  } else {
    print("Sorry! " + lastError() + "\nReturn value: " + ret);
  }
} else {
  print(lastError());
}


// If successful, the output will be:

Congratulations, this API executes successfully.
Return value: true


// If failed:

Sorry! <error message>
Return value: false

// Development only:
tasksReset(true);

// Example: Create a task, pause it, then resume it.

var { tasksCreate, tasksPause, tasksResume} = require("sigma/tasks");
var tasksCreate = tasksCreate('task001', 'E:/File/test.js', 1);
if (tasksCreate === true) {
  delay(5000);
  var tasksPause = tasksPause('task001');
  var tasksResume = tasksResume('task001');
  if (tasksPause === true && tasksResume === true) {
    print("Congratulations, this API executes successfully.\nReturn value: " + taskResume);
  } else {
    print("Sorry! " + lastError() + "\nReturn value: " + taskResume);
  }
} else {
  print(lastError());
}


// If successful, the output will be:

Congratulations, this API executes successfully.
Return value: true


// If failed:

Sorry! <error message>
Return value: false

// 1) Basic text search
tasksSearch("camera"); // by name/content

// 2) Search within certain status on a local day
tasksSearch("device-XYZ", {
  status: [STATUS.SCHEDULED, STATUS.RUNNING],
  endTimeOn: "2025-09-15",
  limit: 10
});

// 3) by status string
tasksSearch(String(STATUS.SCHEDULED), { limit: 10 });

tasksSearch("device-XYZ", { limit: 10 });

// Example: Create a task, wait 5 seconds, then stop it.

var { tasksCreate, tasksStop} = require("sigma/tasks");
var tasksCreate = tasksCreate('task001', 'E:/File/test.js', 1);
if (tasksCreate === true) {
  delay(5000);
  var tasksStop = tasksStop('task001');
  if (tasksStop === true) {
    print("Congratulations, this API executes successfully.\nReturn value: " + taskStop);
  } else {
    print("Sorry! " + lastError() + "\nReturn value: " + taskStop);
  }
} else {
  print(lastError());
}


// If successful:

Congratulations, this API executes successfully.
Return value: true


// If failed:

Sorry! <error message>
Return value: false

// Update name:
tasksUpdate("nightly", { taskName: "nightly-v2" });


// Switch to weekly repeat (Mon-Fri) at 08:30:
tasksUpdate("nightly-v2", { repeat: [1,2,3,4,5], time: "08:30" });


// One-shot reschedule: row id
tasksUpdate(15, { time: "2025-12-01 10:00" });

// taskID
tasksUpdate("1756350622382", { time: "2025-12-01 10:00" });