112 lines
3.1 KiB
JavaScript
112 lines
3.1 KiB
JavaScript
// db/init.js
|
|
const db = require('./knex');
|
|
const { initWsTables } = require("./dbWs");
|
|
|
|
async function init() {
|
|
|
|
// -------------------------------
|
|
// 1) Tabella PHOTOS
|
|
// -------------------------------
|
|
const existsPhotos = await db.schema.hasTable('photos');
|
|
|
|
if (!existsPhotos) {
|
|
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');
|
|
|
|
// 🔥 SOFT DELETE + TIMESTAMPS
|
|
t.datetime('created_at').defaultTo(db.fn.now());
|
|
t.datetime('updated_at').defaultTo(db.fn.now());
|
|
t.datetime('deleted_at').nullable();
|
|
});
|
|
|
|
console.log("✔ Tabella 'photos' creata correttamente");
|
|
|
|
} else {
|
|
console.log("✔ Tabella 'photos' già esistente");
|
|
|
|
// Aggiungi colonne mancanti (retrocompatibilità)
|
|
const cols = [
|
|
'_indexHash',
|
|
'fast_hash',
|
|
'created_at',
|
|
'updated_at',
|
|
'deleted_at'
|
|
];
|
|
|
|
for (const col of cols) {
|
|
const exists = await db.schema.hasColumn('photos', col);
|
|
if (!exists) {
|
|
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(`✔ Colonna '${col}' aggiunta`);
|
|
}
|
|
}
|
|
}
|
|
|
|
// -------------------------------
|
|
// 2) RIMOZIONE TABELLA PHOTO_CHANGES
|
|
// -------------------------------
|
|
const existsChanges = await db.schema.hasTable('photo_changes');
|
|
|
|
if (existsChanges) {
|
|
await db.schema.dropTable('photo_changes');
|
|
console.log("✔ Tabella 'photo_changes' eliminata (non più necessaria)");
|
|
} else {
|
|
console.log("✔ Tabella 'photo_changes' non presente (ok)");
|
|
}
|
|
|
|
// -------------------------------
|
|
// 3) NUOVA TABELLA deleted_hard
|
|
// -------------------------------
|
|
const existsDeletedHard = await db.schema.hasTable('deleted_hard');
|
|
|
|
if (!existsDeletedHard) {
|
|
await db.schema.createTable('deleted_hard', (t) => {
|
|
t.string('id').primary();
|
|
t.string('user');
|
|
t.datetime('deleted_at').defaultTo(db.fn.now());
|
|
});
|
|
|
|
console.log("✔ Tabella 'deleted_hard' creata correttamente");
|
|
} else {
|
|
console.log("✔ Tabella 'deleted_hard' già esistente");
|
|
}
|
|
|
|
// -------------------------------
|
|
// 4) TABELLE WEBSOCKET
|
|
// -------------------------------
|
|
await initWsTables();
|
|
console.log("✔ Tabelle WebSocket inizializzate");
|
|
|
|
process.exit();
|
|
}
|
|
|
|
init();
|