290 lines
8.7 KiB
JavaScript
290 lines
8.7 KiB
JavaScript
// db/init.js
|
|
const db = require('./knex');
|
|
|
|
/* ============================================================
|
|
FOTO
|
|
============================================================ */
|
|
async function ensurePhotos() {
|
|
const exists = await db.schema.hasTable('photos');
|
|
if (!exists) {
|
|
await db.schema.createTable('photos', (t) => {
|
|
t.string('id').primary();
|
|
t.string('user');
|
|
t.string('cartella');
|
|
t.string('name');
|
|
t.string('path');
|
|
t.string('thub1');
|
|
t.string('thub2');
|
|
t.string('mime_type');
|
|
t.integer('width');
|
|
t.integer('height');
|
|
t.integer('rotation');
|
|
t.integer('size_bytes');
|
|
t.integer('mtimeMs');
|
|
t.integer('duration_ms');
|
|
t.string('taken_at');
|
|
t.string('data');
|
|
t.float('lat');
|
|
t.float('lon');
|
|
t.float('alt');
|
|
t.text('location');
|
|
t.string('_indexHash');
|
|
t.string('fast_hash');
|
|
t.datetime('created_at').defaultTo(db.fn.now());
|
|
t.datetime('updated_at').defaultTo(db.fn.now());
|
|
t.datetime('deleted_at').nullable();
|
|
});
|
|
console.log("✔ photos creata");
|
|
} else {
|
|
const cols = [
|
|
'thub1','thub2','fast_hash','created_at','updated_at','deleted_at'
|
|
];
|
|
for (const col of cols) {
|
|
const existsCol = await db.schema.hasColumn('photos', col);
|
|
if (!existsCol) {
|
|
await db.schema.alterTable('photos', (t) => {
|
|
if (col === 'created_at' || col === 'updated_at')
|
|
t.datetime(col).defaultTo(db.fn.now());
|
|
else if (col === 'deleted_at')
|
|
t.datetime(col).nullable();
|
|
else
|
|
t.string(col);
|
|
});
|
|
console.log(`✔ aggiunta colonna photos.${col}`);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/* ============================================================
|
|
FOTO — deleted_hard
|
|
============================================================ */
|
|
async function ensureDeletedHard() {
|
|
const exists = await db.schema.hasTable('deleted_hard');
|
|
if (!exists) {
|
|
await db.schema.createTable('deleted_hard', (t) => {
|
|
t.string('id').primary();
|
|
t.string('user');
|
|
t.datetime('deleted_at').defaultTo(db.fn.now());
|
|
});
|
|
console.log("✔ deleted_hard creata");
|
|
}
|
|
}
|
|
|
|
/* ============================================================
|
|
WS
|
|
============================================================ */
|
|
async function ensureWsSessions() {
|
|
const exists = await db.schema.hasTable('ws_sessions');
|
|
if (!exists) {
|
|
await db.schema.createTable('ws_sessions', (t) => {
|
|
t.string('session_id').notNullable();
|
|
t.string('device_id').notNullable();
|
|
t.string('user').notNullable();
|
|
t.integer('connected_at').notNullable();
|
|
t.integer('last_ack').notNullable();
|
|
t.integer('last_sync').nullable();
|
|
t.boolean('need_full_sync').notNullable().defaultTo(false);
|
|
|
|
t.primary(['session_id', 'device_id']);
|
|
});
|
|
console.log("✔ ws_sessions creata");
|
|
} else {
|
|
const cols = [
|
|
{ name: 'device_id', type: 'string' },
|
|
{ name: 'last_sync', type: 'integer' }
|
|
];
|
|
|
|
for (const c of cols) {
|
|
const existsCol = await db.schema.hasColumn('ws_sessions', c.name);
|
|
if (!existsCol) {
|
|
await db.schema.alterTable('ws_sessions', (t) => {
|
|
if (c.type === 'string') t.string(c.name).notNullable().defaultTo('default');
|
|
if (c.type === 'integer') t.integer(c.name).nullable();
|
|
});
|
|
console.log(`✔ aggiunta colonna ws_sessions.${c.name}`);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
async function ensureWsPending() {
|
|
const exists = await db.schema.hasTable('ws_pending_events');
|
|
if (!exists) {
|
|
await db.schema.createTable('ws_pending_events', (t) => {
|
|
t.string('event_id').primary();
|
|
t.string('session_id').notNullable();
|
|
t.text('payload').notNullable();
|
|
t.integer('sent_at').notNullable();
|
|
t.integer('retries').notNullable().defaultTo(0);
|
|
});
|
|
console.log("✔ ws_pending_events creata");
|
|
}
|
|
}
|
|
|
|
async function ensurePendingEvents() {
|
|
const exists = await db.schema.hasTable('pending_events');
|
|
if (!exists) {
|
|
await db.schema.createTable('pending_events', (t) => {
|
|
t.increments('id').primary();
|
|
t.string('user_id').notNullable();
|
|
t.string('session_id').notNullable();
|
|
t.string('device_id').notNullable();
|
|
t.string('event_id').notNullable();
|
|
t.text('payload').notNullable();
|
|
t.integer('created_at').notNullable();
|
|
|
|
t.unique(['user_id', 'session_id', 'device_id', 'event_id']);
|
|
});
|
|
console.log("✔ pending_events creata");
|
|
}
|
|
}
|
|
|
|
/* ============================================================
|
|
🏃 ATTIVITÀ — nuova tabella
|
|
============================================================ */
|
|
async function ensureActivities() {
|
|
const exists = await db.schema.hasTable('activities');
|
|
if (!exists) {
|
|
await db.schema.createTable('activities', (t) => {
|
|
t.string('id').primary();
|
|
t.string('user');
|
|
t.string('cartella');
|
|
|
|
t.string('file_name');
|
|
t.string('file_extension');
|
|
t.string('format');
|
|
t.string('display_name');
|
|
t.string('route_name');
|
|
t.string('title');
|
|
t.string('description');
|
|
t.string('notes');
|
|
t.string('sport');
|
|
t.string('activity_type');
|
|
t.string('device_manufacturer');
|
|
t.string('device_model');
|
|
t.string('source_app');
|
|
|
|
t.bigInteger('start_time_millis');
|
|
t.bigInteger('end_time_millis');
|
|
t.bigInteger('duration_millis');
|
|
|
|
t.float('distance_meters');
|
|
t.float('elevation_gain_meters');
|
|
t.float('elevation_loss_meters');
|
|
|
|
t.float('min_elevation_meters');
|
|
t.float('max_elevation_meters');
|
|
|
|
t.float('file_distance_meters');
|
|
t.float('file_elevation_gain_meters');
|
|
|
|
t.float('computed_distance_meters');
|
|
t.float('computed_elevation_gain_meters');
|
|
|
|
t.float('average_speed_mps');
|
|
t.float('max_speed_mps');
|
|
t.float('average_pace_sec_per_km');
|
|
|
|
t.integer('average_heart_rate');
|
|
t.integer('max_heart_rate');
|
|
t.integer('average_cadence');
|
|
t.integer('max_cadence');
|
|
|
|
t.integer('calories');
|
|
t.integer('power_avg');
|
|
t.integer('power_max');
|
|
|
|
t.integer('point_count');
|
|
t.integer('segment_count');
|
|
t.integer('lap_count');
|
|
|
|
t.float('min_latitude');
|
|
t.float('max_latitude');
|
|
t.float('min_longitude');
|
|
t.float('max_longitude');
|
|
t.float('center_latitude');
|
|
t.float('center_longitude');
|
|
|
|
t.bigInteger('size_bytes');
|
|
t.bigInteger('last_modified_millis');
|
|
t.bigInteger('date_added_millis');
|
|
t.bigInteger('date_modified_millis');
|
|
t.bigInteger('last_scan_millis');
|
|
|
|
t.boolean('is_favorite');
|
|
t.boolean('is_hidden');
|
|
t.integer('rating');
|
|
|
|
t.text('tags');
|
|
t.text('track_preview');
|
|
t.string('scan_status');
|
|
t.text('scan_error');
|
|
t.string('path');
|
|
t.text('diagnostics');
|
|
t.text('location');
|
|
t.string('fast_hash');
|
|
t.datetime('deleted_at').nullable();
|
|
t.datetime('created_at').defaultTo(db.fn.now());
|
|
t.datetime('updated_at').defaultTo(db.fn.now());
|
|
});
|
|
console.log("✔ activities creata");
|
|
}
|
|
}
|
|
|
|
/* ============================================================
|
|
ATTIVITÀ — deleted_hard
|
|
============================================================ */
|
|
async function ensureDeletedHardActivities() {
|
|
const exists = await db.schema.hasTable('deleted_hard_activities');
|
|
if (!exists) {
|
|
await db.schema.createTable('deleted_hard_activities', (t) => {
|
|
t.string('id').primary();
|
|
t.string('user');
|
|
t.datetime('deleted_at').defaultTo(db.fn.now());
|
|
});
|
|
console.log("✔ deleted_hard_activities creata");
|
|
}
|
|
}
|
|
|
|
/* ============================================================
|
|
ATTIVITÀ — sync_done
|
|
============================================================ */
|
|
async function ensureSyncDoneActivities() {
|
|
const exists = await db.schema.hasTable('sync_done_activities');
|
|
if (!exists) {
|
|
await db.schema.createTable('sync_done_activities', (t) => {
|
|
t.string('session_id');
|
|
t.string('device_id');
|
|
t.bigInteger('last_sync');
|
|
});
|
|
console.log("✔ sync_done_activities creata");
|
|
}
|
|
}
|
|
|
|
/* ============================================================
|
|
INIT
|
|
============================================================ */
|
|
async function init() {
|
|
try {
|
|
console.log("🔧 init DB...");
|
|
await ensurePhotos();
|
|
await ensureDeletedHard();
|
|
|
|
await ensureActivities();
|
|
await ensureDeletedHardActivities();
|
|
await ensureSyncDoneActivities();
|
|
|
|
await ensureWsSessions();
|
|
await ensureWsPending();
|
|
await ensurePendingEvents();
|
|
|
|
console.log("✅ init completato");
|
|
process.exit(0);
|
|
} catch (err) {
|
|
console.error("❌ errore init:", err);
|
|
process.exit(1);
|
|
}
|
|
}
|
|
|
|
init();
|