# Taskboard SQL: die gleiche Anwendung auf SQLite mit echten SQL und einer Transaktion

> Halten Sie die Taskboard-Sitzungen, Rollen und CSRF, speichern Sie die Aufgaben in einem SQLite-Datei über den nativen QueryBuilder und bewegen Sie die fertiggelegten Aufgaben in eine atomische SQL-Transaktion.

Framework: total5
Product: Total.js 5 (total5@0.0.19)
Language: de
Content-SHA256: db60adccf258018ee450b36dac4dc817bfb264adedee8066553ffdc5ae3831ec
Snapshot: c0700e321579725c5cf4f5ace1ad7d2866d1b4f93c24a63c0b41b6b7998e976c
Sources: total5-installed@total5@0.0.19
Review: not-recorded

Diese Anleitung bringt die [Taskboard-Referenz](/en/docs/taskboard-reference/) von eingebettetem NoSQL zu **echtem SQL**. Die Sitzungen, die drei Benutzer, der CSRF-Schutz und die Besitzerregeln bleiben gleich. Die Aufgaben leben jetzt in einer SQLite-Datei, und die Anwendung spricht über den nativen QueryBuilder `DB()` mit einem kleinen, der Anwendung gehörenden Treiber mit ihnen, genau wie im [SQLite-Rezept](/en/total5/query-builder-sqlite/).

Das Neue ist eine **Transaktion**: *Erledigte archivieren* kopiert die erledigten Aufgaben in eine Archivtabelle und löscht sie in einem atomaren Schritt aus der Aufgabentabelle, sodass eine Aufgabe nie an beiden Orten oder an keinem ist. Alles sind gebundene Parameter und Positivlisten; kein Wert aus einer Anfrage landet je im SQL-Text.

SQLite kommt von Node selbst (`node:sqlite`, hier mit Node 24 getestet), es gibt also keine npm-Abhängigkeit. Der Treiber ist synchron, was für eine kleine Anwendung und diese Referenz in Ordnung ist; ein stark ausgelasteter Produktivdienst würde einen asynchronen Treiber für seinen Datenbankserver und dieselben QueryBuilder-Aufrufe nutzen. Kopiere die Dateien wie in der ersten Anleitung in ein neues Verzeichnis (`demo: true`, die drei lokalen Benutzer, `total5@0.0.19`, nur Loopback); lösche `taskboard.sqlite`, um mit einer leeren Datenbank zu beginnen.

## Server-Dateien

```text config
name : Taskboard SQL
demo : true
```

```js definitions/taskboard.js
var crypto = Total.Crypto;
var demo = CONF.demo === true;
if (demo && (process.argv.includes('--release') || process.env.NODE_ENV === 'production'))
	throw new Error('Disable demo mode before a release deployment');
var secret = process.env.TASKBOARD_SECRET || (demo ? 'local-taskboard-demo-secret-not-for-public-use' : '');
if (secret.length < 32)
	throw new Error('TASKBOARD_SECRET needs at least 32 characters');
CONF.secret_csrf = secret;

MAIN.taskUsers = Object.create(null);
for (var name of ['alice', 'bob', 'admin']) {
	var password = process.env['TASKBOARD_' + name.toUpperCase() + '_PASSWORD'] || (demo ? name + '-demo-password' : '');
	if (password.length < 12)
		throw new Error('Configure a password of at least 12 characters for ' + name);
	var salt = crypto.randomBytes(16).toString('hex');
	MAIN.taskUsers[name] = { id: name, name: name, role: name === 'admin' ? 'admin' : 'member', salt: salt, hash: crypto.scryptSync(password, salt, 32) };
}

MAIN.taskAuth = {
	secret: secret,
	cookie: 'taskboard_session',
	expire: '30 minutes',
	options: { httponly: true, samesite: 'Strict', secure: !demo },
	onread: function(meta, next) {
		NOSQL('task-sessions').read().where('id', meta.sessionid).callback(function(err, session) {
			var user = !err && session && session.expire > Date.now() && MAIN.taskUsers[session.userid];
			user ? next(null, { id: user.id, name: user.name, role: user.role }) : next('Session not found');
		});
	},
	onlogout: function(session) {
		NOSQL('task-sessions').remove().where('id', session.sessionid).callback(NOOP);
	},
	onremove: function(id) {
		NOSQL('task-sessions').remove().where('id', id).callback(NOOP);
	}
};
AUTH(MAIN.taskAuth);

MIDDLEWARE('taskcsrf', function($, next) {
	DEF.onCSRFcheck($) ? next() : $.invalid(403);
});
```

```js definitions/sqlite.js
var DatabaseSync = require('node:sqlite').DatabaseSync;
var db = new DatabaseSync(PATH.root('taskboard.sqlite'));

db.exec('CREATE TABLE IF NOT EXISTS tasks (id TEXT PRIMARY KEY, title TEXT NOT NULL CHECK(length(title) BETWEEN 1 AND 80), done INTEGER NOT NULL DEFAULT 0 CHECK(done IN (0, 1)), owner TEXT NOT NULL, created INTEGER NOT NULL) STRICT');
db.exec('CREATE TABLE IF NOT EXISTS task_archive (id TEXT PRIMARY KEY, title TEXT NOT NULL, owner TEXT NOT NULL, created INTEGER NOT NULL, archived INTEGER NOT NULL) STRICT');

var COLUMNS = ['id', 'title', 'done', 'owner', 'created'];

function column(name) {
	if (!COLUMNS.includes(name))
		throw new Error('Column not allowed');
	return '"' + name + '"';
}

function where(options, params) {
	var filters = options.filter.map(function(f) {
		if (f.type !== 'where' || f.comparer !== '=')
			throw new Error('Filter not supported');
		params.push(f.name === 'done' ? (f.value ? 1 : 0) : f.value);
		return column(f.name) + ' = ?';
	});
	return filters.length ? ' WHERE ' + filters.join(' AND ') : '';
}

function row(item) {
	item.done = item.done === 1;
	return item;
}

// The only table the QueryBuilder can reach is tasks; values are always bound parameters
NEWDB('sqlite', function(options, callback) {
	try {
		if (options.table !== 'tasks')
			throw new Error('Table not allowed');
		var params = [];
		var condition = where(options, params);
		var result;
		if (['find', 'read'].includes(options.exec)) {
			var sort = (options.sort || ['created_asc']).map(function(s) {
				var match = /^(title|created)_(asc|desc)$/.exec(s);
				if (!match)
					throw new Error('Sort not allowed');
				return column(match[1]) + ' ' + match[2];
			}).join(',');
			var rows = db.prepare('SELECT id, title, done, owner, created FROM tasks' + condition + ' ORDER BY ' + sort + ' LIMIT 200').all(...params).map(row);
			result = options.exec === 'read' ? rows[0] || null : rows;
		} else if (options.exec === 'insert') {
			var p = options.payload;
			db.prepare('INSERT INTO tasks (id, title, done, owner, created) VALUES (?, ?, ?, ?, ?)').run(p.id, p.title, p.done ? 1 : 0, p.owner, p.created);
			result = { id: p.id };
		} else if (options.exec === 'update') {
			if (!condition)
				throw new Error('Filter required');
			var keys = Object.keys(options.payload);
			var set = keys.map(key => column(key) + ' = ?').join(',');
			result = db.prepare('UPDATE tasks SET ' + set + condition).run(...keys.map(key => key === 'done' ? (options.payload[key] ? 1 : 0) : options.payload[key]), ...params).changes;
		} else if (options.exec === 'remove') {
			if (!condition)
				throw new Error('Filter required');
			result = db.prepare('DELETE FROM tasks' + condition).run(...params).changes;
		} else
			throw new Error('Operation not supported');
		callback(null, result);
	} catch (e) {
		callback(e);
	}
});

// One atomic step: the completed tasks (of one owner, or of everybody for the administrator) move to the archive.
// Either both statements are applied or none is.
MAIN.archiveTasks = function(owner) {
	var params = owner ? [owner] : [];
	var filter = owner ? ' AND owner = ?' : '';
	db.exec('BEGIN IMMEDIATE');
	try {
		db.prepare('INSERT INTO task_archive (id, title, owner, created, archived) SELECT id, title, owner, created, ? FROM tasks WHERE done = 1' + filter).run(Date.now(), ...params);
		var moved = db.prepare('DELETE FROM tasks WHERE done = 1' + filter).run(...params).changes;
		db.exec('COMMIT');
		return moved;
	} catch (e) {
		db.exec('ROLLBACK');
		throw e;
	}
};

MAIN.archivedTasks = function(owner) {
	var filter = owner ? ' WHERE owner = ?' : '';
	return db.prepare('SELECT id, title, owner FROM task_archive' + filter + ' ORDER BY archived DESC, id LIMIT 200').all(...(owner ? [owner] : []));
};
```

```js actions/tasks.js
function owned(builder, user) {
	return user.role === 'admin' ? builder : builder.where('owner', user.id);
}

NEWACTION('Tasks|list', {
	user: true,
	action: function($) {
		owned(DB().find('sqlite/tasks').sort('title'), $.user).callback(function(err, tasks) {
			err ? $.invalid(500) : $.callback(tasks);
		});
	}
});

NEWACTION('Tasks|create', {
	user: true,
	input: '*title:String(80)',
	action: function($, model) {
		var task = { id: UID(), title: model.title, done: false, owner: $.user.id, created: Date.now() };
		DB().insert('sqlite/tasks', task).callback(function(err) {
			err ? $.invalid(500) : $.callback(task);
		});
	}
});

NEWACTION('Tasks|update', {
	user: true,
	params: '*id:UID',
	input: '*done:Boolean',
	action: function($, model) {
		owned(DB().update('sqlite/tasks', { done: model.done }).id($.params.id), $.user).callback(function(err, count) {
			if (err)
				return $.invalid(500);
			count ? $.success() : $.invalid(404);
		});
	}
});

NEWACTION('Tasks|remove', {
	user: true,
	params: '*id:UID',
	action: function($) {
		if ($.user.role !== 'admin')
			return $.invalid(403);
		DB().remove('sqlite/tasks').id($.params.id).callback(function(err, count) {
			if (err)
				return $.invalid(500);
			count ? $.success() : $.invalid(404);
		});
	}
});

NEWACTION('Tasks|archive', {
	user: true,
	action: function($) {
		try {
			$.callback({ moved: MAIN.archiveTasks($.user.role === 'admin' ? null : $.user.id) });
		} catch (e) {
			$.invalid(500);
		}
	}
});

NEWACTION('Tasks|archived', {
	user: true,
	action: function($) {
		$.callback(MAIN.archivedTasks($.user.role === 'admin' ? null : $.user.id));
	}
});
```

```js controllers/taskboard.js
var attempts = Object.create(null);

exports.install = function() {
	ROUTE('GET /', function($) { $.file(PATH.public('index.html')); });
	ROUTE('GET /session/', session);
	ROUTE('POST /login/ @json <2kb #taskcsrf', login);
	ROUTE('+POST /logout/ @json #taskcsrf', logout);
	ROUTE('+GET /tasks/ --> *Tasks|list');
	ROUTE('+POST /tasks/ <2kb #taskcsrf --> *Tasks|create');
	ROUTE('+PATCH /tasks/{id}/ <2kb #taskcsrf --> *Tasks|update');
	ROUTE('+DELETE /tasks/{id}/ #taskcsrf --> *Tasks|remove');
	ROUTE('+POST /archive/ #taskcsrf --> *Tasks|archive');
	ROUTE('+GET /archive/ --> *Tasks|archived');
};

function session($) {
	$.response.headers['cache-control'] = 'no-store';
	$.json({ user: $.user || null, csrf: $.csrf() });
}

function login($) {
	var key = $.ip;
	var recent = (attempts[key] || []).filter(time => time > Date.now() - 600000);
	attempts[key] = recent;
	if (recent.length >= 5) return $.invalid(429);
	var user = MAIN.taskUsers[String($.body.name || '').toLowerCase()];
	var salt = user ? user.salt : 'dummy-salt';
	var actual = Total.Crypto.scryptSync(String($.body.password || ''), salt, 32);
	var valid = user && Total.Crypto.timingSafeEqual(actual, user.hash);
	if (!valid) {
		recent.push(Date.now());
		return $.invalid(401);
	}
	var id = UID();
	NOSQL('task-sessions').insert({ id: id, userid: user.id, expire: Date.now() + 1800000 }).callback(function(err) {
		if (err) return $.invalid(500);
		MAIN.taskAuth.authcookie($, id, user.id, '30 minutes');
		$.json({ user: { id: user.id, name: user.name, role: user.role }, csrf: $.csrf() });
	});
}

function logout($) {
	NOSQL('task-sessions').remove().where('id', $.sessionid).callback(function(err) {
		if (err) return $.invalid(500);
		MAIN.taskAuth.logout($);
		$.cookie(MAIN.taskAuth.cookie, '', '-1 year', MAIN.taskAuth.options);
		$.json({ ok: true });
	});
}
```

```html public/index.html
<!DOCTYPE html>
<html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1">
<title>Taskboard SQL · Total.js</title>
<style>
body{font:16px system-ui;background:#f4f5f7;color:#172033;margin:0}main{max-width:720px;margin:8vh auto;padding:30px;background:white;border-radius:18px}header{display:flex;align-items:center;justify-content:space-between;gap:15px}h1{margin:0}p{color:#536078}label{display:block;margin-top:14px}input,button{font:inherit;padding:10px;border:1px solid #bbc4d1;border-radius:8px}input[type=text],input[type=password]{box-sizing:border-box;width:100%;margin-top:5px}button{cursor:pointer;background:#152d57;color:white}form button{margin-top:15px}ul{padding:0;list-style:none}li{display:flex;align-items:center;gap:10px;padding:14px 0;border-bottom:1px solid #eee}.task-title{flex:1;overflow-wrap:anywhere}.muted{font-size:14px;color:#536078}#message{min-height:24px;color:#9d251e}[hidden]{display:none!important}
</style></head><body><main>
<header><h1>Taskboard SQL</h1><span class="muted">Total.js reference</span></header>
<p>Private tasks stored in SQLite, with an atomic archive of the completed ones.</p>
<p id="message" role="status" aria-live="polite"></p>
<form id="login"><h2>Sign in</h2><label for="name">User</label><input id="name" autocomplete="username" required><label for="password">Password</label><input id="password" type="password" autocomplete="current-password" required><button>Sign in</button></form>
<section id="workspace" hidden><header><p id="identity"></p><button id="logout" type="button">Sign out</button></header>
<form id="create"><label for="title">New task</label><input id="title" type="text" maxlength="80" required><button>Add task</button></form>
<ul id="tasks" aria-label="Tasks"></ul><p id="empty">No tasks yet.</p>
<button id="archive" type="button">Archive completed</button>
<h2>Archive</h2><ul id="archived" aria-label="Archived tasks"></ul></section>
</main><script src="/app.js"></script></body></html>
```

```js public/app.js
(function() {
	var user = null;
	var csrf = '';
	var busy = false;
	var byid = id => document.getElementById(id);
	async function request(path, method, body) {
		var response = await fetch(path, { method: method || 'GET', credentials: 'same-origin', headers: { 'content-type': 'application/json', 'x-csrf-token': csrf }, body: body === undefined ? undefined : JSON.stringify(body) });
		var data = await response.json().catch(() => null);
		if (!response.ok) {
			if (response.status === 401) { user = null; show(); }
			throw new Error('Request refused (' + response.status + ')');
		}
		if (data === null) throw new Error('Unexpected server response');
		return data;
	}
	function show() {
		byid('login').hidden = !!user;
		byid('workspace').hidden = !user;
		byid('identity').textContent = user ? user.name + ' · ' + user.role : '';
	}
	async function load() {
		var tasks = await request('/tasks/');
		var archived = await request('/archive/');
		byid('tasks').replaceChildren();
		byid('empty').hidden = tasks.length > 0;
		tasks.forEach(function(task) {
			var row = document.createElement('li');
			var checkbox = document.createElement('input');
			checkbox.type = 'checkbox'; checkbox.checked = task.done; checkbox.setAttribute('aria-label', 'Complete ' + task.title);
			checkbox.addEventListener('change', () => work(async () => { await request('/tasks/' + task.id + '/', 'PATCH', { done: checkbox.checked }); await load(); }));
			var title = document.createElement('span'); title.className = 'task-title'; title.textContent = task.title;
			row.append(checkbox, title);
			if (user.role === 'admin') {
				var remove = document.createElement('button'); remove.type = 'button'; remove.textContent = 'Delete';
				remove.addEventListener('click', () => work(async () => { await request('/tasks/' + task.id + '/', 'DELETE'); await load(); }));
				row.append(remove);
			}
			byid('tasks').append(row);
		});
		byid('archived').replaceChildren();
		archived.forEach(function(task) {
			var row = document.createElement('li');
			row.textContent = task.title;
			byid('archived').append(row);
		});
	}
	async function work(fn) {
		if (busy) return;
		busy = true; byid('message').textContent = '';
		document.querySelectorAll('input,button').forEach(control => control.disabled = true);
		try { await fn(); } catch (err) { byid('message').textContent = err.message; }
		finally { busy = false; document.querySelectorAll('input,button').forEach(control => control.disabled = false); }
	}
	byid('login').addEventListener('submit', function(event) {
		event.preventDefault(); work(async function() {
			var result = await request('/login/', 'POST', { name: byid('name').value, password: byid('password').value });
			user = result.user; csrf = result.csrf; byid('password').value = ''; show(); await load();
		});
	});
	byid('create').addEventListener('submit', function(event) {
		event.preventDefault(); work(async function() { await request('/tasks/', 'POST', { title: byid('title').value }); byid('title').value = ''; await load(); });
	});
	byid('archive').addEventListener('click', () => work(async function() { await request('/archive/', 'POST', {}); await load(); }));
	byid('logout').addEventListener('click', () => work(async function() { await request('/logout/', 'POST', {}); user = null; byid('tasks').replaceChildren(); show(); }));
	work(async function() { var result = await request('/session/'); user = result.user; csrf = result.csrf; show(); if (user) await load(); });
})();
```

## Was geprüft wird


Verwandte Rezepte: [SQLite und der QueryBuilder](/en/total5/query-builder-sqlite/), [QueryBuilder-Vertrag](/en/total5/query-builder-contract/), [CSRF](/en/total5/csrf-protection/) und [Taskboard Live](/en/docs/taskboard-live/).