# Taskboard SQL: la même application sur SQLite avec un vrai SQL et une transaction > Gardez les sessions de Taskboard, les rôles et le CSRF, enregistrez les tâches dans un fichier SQLite à travers le QueryBuilder indigène et déplacez des tâches terminées vers un archive dans une transaction SQL atomique. Framework: total5 Product: Total.js 5 (total5@0.0.19) Language: fr Content-SHA256: 5640209d5fa8f7c36f4e9dbbe450e14ce10f2a6a568e049e846738b907a82e33 Snapshot: c0700e321579725c5cf4f5ace1ad7d2866d1b4f93c24a63c0b41b6b7998e976c Sources: total5-installed@total5@0.0.19 Review: not-recorded Ce guide fait passer la [référence Taskboard](/en/docs/taskboard-reference/) du NoSQL embarqué au **vrai SQL**. Les sessions, les trois utilisateurs, la protection CSRF et les règles du propriétaire restent les mêmes. Les tâches vivent maintenant dans un fichier SQLite, et l'application leur parle via le QueryBuilder natif `DB()` avec un petit pilote qui appartient à l'application, exactement comme dans la [recette SQLite](/en/total5/query-builder-sqlite/). La nouveauté est une **transaction** : *Archiver les terminées* copie les tâches terminées dans une table d'archive et les supprime de la table des tâches en une seule étape atomique, si bien qu'une tâche n'est jamais aux deux endroits ni nulle part. Tout est paramètres liés et listes autorisées ; aucune valeur d'une requête n'est jamais placée dans le texte SQL. SQLite vient de Node lui-même (`node:sqlite`, testé ici avec Node 24), il n'y a donc aucune dépendance npm. Le pilote est synchrone, ce qui convient à une petite application et à cette référence ; un service de production très sollicité utiliserait un pilote asynchrone pour son serveur de base de données et les mêmes appels QueryBuilder. Copiez les fichiers dans un nouveau répertoire comme dans le premier guide (`demo: true`, les trois utilisateurs locaux, `total5@0.0.19`, loopback uniquement) ; supprimez `taskboard.sqlite` pour repartir d'une base vide. ## Fichiers du serveur ```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 Taskboard SQL · Total.js

Taskboard SQL

Total.js reference

Private tasks stored in SQLite, with an atomic archive of the completed ones.

Sign in

``` ```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(); }); })(); ``` ## Ce qui est testé Recettes liées : [SQLite et le QueryBuilder](/en/total5/query-builder-sqlite/), [contrat du QueryBuilder](/en/total5/query-builder-contract/), [CSRF](/en/total5/csrf-protection/) et [Taskboard Live](/en/docs/taskboard-live/).