SQLite

The SQLite extension adds the ten functions as table-valued functions; each one runs a query you pass as text, asks a question about every message it returns, and gives the answer as a column, such as 1 or 0 from decide.

.load ./thinkthen_sqlite sqlite3_thinkthensqlite_init
.mode box
CREATE TABLE messages (
  msg_id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO messages VALUES
  (1, 'Please refund my order.'),
  (2, 'Where is my parcel?'),
  (3, 'I was charged twice for one invoice.'),
  (4, 'I was charged twice and my parcel is late again.'),
  (5, 'Morgan works for Example Group.
Please refund my order.'),
  (6, 'Morgan works for Example Group.');

SELECT id AS msg_id, answer AS is_refund, status
FROM thinkthen_decide(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id <= 3',
  'Does the customer request a refund?');

Output

┌────────┬───────────┬──────────┐
│ msg_id │ is_refund │  status  │
├────────┼───────────┼──────────┤
│ 1      │ 1         │ answered │
│ 2      │ 0         │ answered │
│ 3      │ 1         │ answered │
└────────┴───────────┴──────────┘
Copy

Messages 1 and 3 ask for a refund, so is_refund is 1, and message 2 asks about a parcel, so it is 0. The rest of this page runs in the same session, with the same messages table.

Load the extension and ask

Download the SQLite archive for your platform from the 0.2.0 release, check it as Installation shows, and extract it into a folder of its own. Start sqlite3 in that folder:

mkdir thinkthen-sqlite
tar -xzf thinkthen-sqlite-*.tar.gz -C thinkthen-sqlite
cd thinkthen-sqlite
sqlite3

The extension needs SQLite 3.41.0 or later with extension loading on, which the sqlite3 shell has. SELECT thinkthen_version(); prints the version you loaded.

SQLite needs your key in its environment, as Configuration shows. In Python, call connection.enable_load_extension(True), then connection.load_extension('./thinkthen_sqlite'), and run the same SQL.

The first argument is the query to judge, as text. Its record column holds the text, and its id column comes back in id with the same type, so it joins straight back to your table. Name any other argument in WHERE, as WHERE choices = '[...]'. Lists and toolboxes are JSON text.

Each run of a query asks again and is billed again, and a join or a subquery around a function can run it again too. Save answers you need twice with CREATE TABLE refund_answers AS SELECT ..., and join that table.

When decide is not sure

Set a low bar and a high bar with low and high. decide says no below the low bar, yes at or above the high bar, and not sure in between:

SELECT id AS msg_id, answer AS is_refund, status
FROM thinkthen_decide(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id <= 3',
  'Does the customer request a refund?')
WHERE low = 0.2 AND high = 0.8;

Output

┌────────┬───────────┬──────────┐
│ msg_id │ is_refund │  status  │
├────────┼───────────┼──────────┤
│ 1      │ 1         │ answered │
│ 2      │ 0         │ answered │
│ 3      │           │ unsure   │
└────────┴───────────┴──────────┘
Copy

A double charge might be a refund request or might not, so message 3 comes back unsure, with a NULL answer, which the shell prints as a blank. Send it to a person. Test answers with IS TRUE and IS FALSE, which are both false for NULL, so not sure never counts as a no.

When a message fails

A message that can't be judged comes back with status failed, a NULL answer and an error_code, and the query keeps going. Here message 7 has no text, so it fails as record.missing, and no question is sent for it:

SELECT id AS msg_id, answer AS is_refund, status, error_code
FROM thinkthen_decide(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id = 1
   UNION ALL SELECT 7, NULL',
  'Does the customer request a refund?');

Output

┌────────┬───────────┬──────────┬────────────────┐
│ msg_id │ is_refund │  status  │   error_code   │
├────────┼───────────┼──────────┼────────────────┤
│ 1      │ 1         │ answered │                │
│ 7      │           │ failed   │ record.missing │
└────────┴───────────┴──────────┴────────────────┘
Copy

A number or a blob in record fails as record.invalid. A mistake in the call itself, such as leaving out choices, stops the query before anything is sent, with a message that starts with a code such as usage.argument. When every message fails for the same outside reason, such as a refused request, the query raises that error instead. Troubleshooting lists every code.

The ten functions

Each query runs in the session the first example opened. Open a function to see its query and what the shell prints.

decide

Yes or no for each message

SELECT id AS msg_id, answer AS is_refund, probability
FROM thinkthen_decide(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id <= 3',
  'Does the customer request a refund?');

Output

┌────────┬───────────┬─────────────┐
│ msg_id │ is_refund │ probability │
├────────┼───────────┼─────────────┤
│ 1      │ 1         │ 0.99        │
│ 2      │ 0         │ 0.03        │
│ 3      │ 1         │ 0.7         │
└────────┴───────────┴─────────────┘
Copy

answer is 1 for yes and 0 for no. probability is the chance of yes, also when the answer is no. It is high for message 1, low for message 2, and in between for message 3, the double charge. yes_means and no_means say what yes and no mean for your question. decide shows the command.

choose

One of your choices for each message

SELECT id AS msg_id, answer AS topic, status
FROM thinkthen_choose(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id = 1',
  'What is the message about?')
WHERE choices = '["billing", "shipping", "other"]';

Output

┌────────┬─────────┬──────────┐
│ msg_id │  topic  │  status  │
├────────┼─────────┼──────────┤
│ 1      │ billing │ answered │
└────────┴─────────┴──────────┘
Copy

Message 1, a refund request, is about billing. answer is the choice, as text, and detail ->> '$.labels' gives every choice its probability. With threshold, or low and high, choose is not sure when the best choice falls short of the bar. choose shows the command.

tag

Every label that fits each message

SELECT id AS msg_id, answer AS topics, status
FROM thinkthen_tag(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id = 4',
  'Which topics appear?')
WHERE labels = '["billing", "shipping"]';

Output

┌────────┬────────────────────────┬──────────┐
│ msg_id │         topics         │  status  │
├────────┼────────────────────────┼──────────┤
│ 4      │ ["billing","shipping"] │ answered │
└────────┴────────────────────────┴──────────┘
Copy

Message 4 is about billing and shipping at once. answer is a JSON array, which is empty when no label fits. threshold sets the bar a label must reach. tag shows the command.

score

A number from 0 to 1 for each message

SELECT id AS msg_id, answer AS urgency, status
FROM thinkthen_score(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id <= 3',
  'How urgent is the request?')
WHERE levels = '["routine", "urgent"]';

Output

┌────────┬─────────┬──────────┐
│ msg_id │ urgency │  status  │
├────────┼─────────┼──────────┤
│ 1      │ 0.05    │ answered │
│ 2      │ 0.14    │ answered │
│ 3      │ 0.45    │ answered │
└────────┴─────────┴──────────┘
Copy

The double charge, message 3, is the most urgent of the three. answer is a number: 0 means the first level, and 1 the last. List the levels lowest first. score shows the command.

filter

Only the messages that answer yes

SELECT id AS msg_id, status
FROM thinkthen_filter(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id <= 3',
  'Does the customer request a refund?');

Output

┌────────┬──────────┐
│ msg_id │  status  │
├────────┼──────────┤
│ 1      │ answered │
│ 3      │ answered │
└────────┴──────────┘
Copy

Messages 1 and 3 ask for a refund, so filter keeps them. The no and not-sure messages are left out. If a message fails, filter returns the kept messages, then raises one error that says how many failed. Add failures = 'row' to the WHERE clause to get the failed messages back too, with status failed, so you keep the answers you paid for. To keep every message with its answer, use decide. filter shows the command.

rank

Every message in order, highest first

SELECT answer AS urgency_rank, id AS msg_id, status
FROM thinkthen_rank(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id <= 3',
  'How urgent is the request?')
WHERE levels = '["routine", "urgent"]'
ORDER BY urgency_rank;

Output

┌──────────────┬────────┬──────────┐
│ urgency_rank │ msg_id │  status  │
├──────────────┼────────┼──────────┤
│ 1            │ 3      │ answered │
│ 2            │ 2      │ answered │
│ 3            │ 1      │ answered │
└──────────────┴────────┴──────────┘
Copy

The double charge ranks first, the parcel second and the refund request last. answer is a rank from 1, the most urgent first. top = 2 keeps the first two. Failed messages come after the ranked ones with a NULL rank, and top never drops them. rank shows the command.

find

The line that answers in each message

SELECT id AS msg_id,
       answer ->> '$.text' AS requested_action, status
FROM thinkthen_find(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id = 5',
  'Which line states the requested action?');

Output

┌────────┬─────────────────────────┬──────────┐
│ msg_id │    requested_action     │  status  │
├────────┼─────────────────────────┼──────────┤
│ 5      │ Please refund my order. │ answered │
└────────┴─────────────────────────┴──────────┘
Copy

The second line of message 5 states what the customer wants. answer is a JSON object with the line's text, start_offset and end_offset. Offsets count characters from 0, and the end is not part of the line. When no line answers, answer is NULL and status is answered. find shows the command.

annotate

Several questions about each message at once

SELECT id AS msg_id, answer ->> '$.refund' AS is_refund,
       answer ->> '$.topic' AS topic,
       answer ->> '$.urgency' AS urgency
FROM thinkthen_annotate(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id <= 3')
WHERE toolbox = '{
  "refund": {"function": "decide",
    "question": "Does the customer request a refund?"},
  "topic": {"function": "choose",
    "question": "What is the message about?",
    "choices": ["billing", "shipping", "other"]},
  "urgency": {"function": "score",
    "question": "How urgent is the request?",
    "levels": ["routine", "urgent"]}
}';

Output

┌────────┬───────────┬──────────┬─────────┐
│ msg_id │ is_refund │  topic   │ urgency │
├────────┼───────────┼──────────┼─────────┤
│ 1      │ 1         │ billing  │ 0.05    │
│ 2      │ 0         │ shipping │ 0.14    │
│ 3      │ 1         │ billing  │ 0.45    │
└────────┴───────────┴──────────┴─────────┘
Copy

Each message gets all three answers from one query. toolbox names each question, and rank can't be one of them. answer is a JSON object with each question's answer by name. A value is null when that question was not sure or failed, and detail holds each question's full outcome. annotate shows the command.

recognize

The names of the kinds you ask for

SELECT names.id AS msg_id, found.value ->> '$.text' AS name,
       found.value ->> '$.kind' AS kind
FROM thinkthen_recognize(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id = 6') AS names,
  json_each(names.answer) AS found
WHERE names.kinds = '["person", "organization"]';

Output

┌────────┬───────────────┬──────────────┐
│ msg_id │     name      │     kind     │
├────────┼───────────────┼──────────────┤
│ 6      │ Morgan        │ person       │
│ 6      │ Example Group │ organization │
└────────┴───────────────┴──────────────┘
Copy

Message 6 names a person, Morgan, and an organization, Example Group. answer is a JSON array of names, each with text, kind, start_offset, end_offset and probability. json_each lists each name on its own. recognize shows the command.

relate

How the names in each message connect

SELECT links.id AS msg_id,
       link.value ->> '$.source.text' AS person,
       link.value ->> '$.relation' AS relation,
       link.value ->> '$.target.text' AS organization
FROM thinkthen_relate(
  'SELECT msg_id AS id, body AS record FROM messages
   WHERE msg_id = 5') AS links,
  json_each(links.answer) AS link
WHERE links.rules = '[{"relation": "works_for",
  "source": "person", "target": "organization"}]';

Output

┌────────┬────────┬───────────┬───────────────┐
│ msg_id │ person │ relation  │ organization  │
├────────┼────────┼───────────┼───────────────┤
│ 5      │ Morgan │ works_for │ Example Group │
└────────┴────────┴───────────┴───────────────┘
Copy

Morgan works for Example Group, so relate links the two names. Each rule has relation, source and target, plus optional reads and either. answer is a JSON array of relations, each with relation, source, target, either and probability. relate shows the command.

Every option and setting

The SQLite guide lists every argument, result column and setting, such as field = '/text', which judges one field of a JSON message, and params, which binds values to the ? in your query. The extension's source shows how each call works.