Tanya
de2144be2a
This commit introduces several new scripts for managing database operations, including user creation, permission grants, and data migrations. It also adds new documentation files to guide users through the setup and configuration processes. Additionally, the project structure is updated to enhance organization and maintainability, ensuring a smoother development experience for contributors. These changes support the ongoing transition to a web-based architecture and improve overall project functionality.
262 lines
8.4 KiB
TypeScript
262 lines
8.4 KiB
TypeScript
import { Client } from 'pg';
|
||
import * as dotenv from 'dotenv';
|
||
import { readFileSync } from 'fs';
|
||
import { join } from 'path';
|
||
|
||
// Load environment variables
|
||
dotenv.config({ path: '.env' });
|
||
|
||
async function checkAndCreateDatabases() {
|
||
// Get connection info from DATABASE_URL or use defaults
|
||
const mainDbUrl = process.env.DATABASE_URL || 'postgresql://postgres@localhost:5432/postgres';
|
||
const authDbUrl = process.env.DATABASE_URL_AUTH || 'postgresql://postgres@localhost:5432/postgres';
|
||
|
||
// Parse connection strings to get connection details
|
||
const parseUrl = (url: string) => {
|
||
const match = url.match(/postgresql:\/\/([^:]+):([^@]+)@([^:]+):(\d+)\/(.+)/) ||
|
||
url.match(/postgresql:\/\/([^@]+)@([^:]+):(\d+)\/(.+)/) ||
|
||
url.match(/postgresql:\/\/([^@]+)@([^:]+)\/(.+)/);
|
||
|
||
if (match) {
|
||
if (match.length === 6) {
|
||
// With password
|
||
return {
|
||
user: match[1],
|
||
password: match[2],
|
||
host: match[3],
|
||
port: parseInt(match[4]),
|
||
database: match[5],
|
||
};
|
||
} else if (match.length === 5) {
|
||
// Without password, with port
|
||
return {
|
||
user: match[1],
|
||
host: match[2],
|
||
port: parseInt(match[3]),
|
||
database: match[4],
|
||
};
|
||
} else if (match.length === 4) {
|
||
// Without password, without port
|
||
return {
|
||
user: match[1],
|
||
host: match[2],
|
||
port: 5432,
|
||
database: match[3],
|
||
};
|
||
}
|
||
}
|
||
|
||
// Fallback to defaults
|
||
return {
|
||
user: 'postgres',
|
||
host: 'localhost',
|
||
port: 5432,
|
||
database: 'postgres',
|
||
};
|
||
};
|
||
|
||
const mainConfig = parseUrl(mainDbUrl);
|
||
const authConfig = parseUrl(authDbUrl);
|
||
|
||
// Connect to postgres database to check/create databases
|
||
const adminClient = new Client({
|
||
user: mainConfig.user,
|
||
password: (mainConfig as any).password,
|
||
host: mainConfig.host,
|
||
port: mainConfig.port,
|
||
database: 'postgres', // Connect to postgres database to create other databases
|
||
});
|
||
|
||
try {
|
||
console.log('🔍 Checking databases...\n');
|
||
await adminClient.connect();
|
||
console.log('✅ Connected to PostgreSQL\n');
|
||
|
||
// Check if punimtag database exists
|
||
const mainDbCheck = await adminClient.query(
|
||
"SELECT 1 FROM pg_database WHERE datname = 'punimtag'"
|
||
);
|
||
|
||
if (mainDbCheck.rows.length === 0) {
|
||
console.log('⚠️ punimtag database does not exist');
|
||
console.log(' This is the main database with photos - it should already exist.');
|
||
console.log(' If you need to create it, please do so manually or ensure your PunimTag setup is complete.\n');
|
||
} else {
|
||
console.log('✅ punimtag database exists\n');
|
||
}
|
||
|
||
// Check if punimtag_auth database exists
|
||
const authDbCheck = await adminClient.query(
|
||
"SELECT 1 FROM pg_database WHERE datname = 'punimtag_auth'"
|
||
);
|
||
|
||
if (authDbCheck.rows.length === 0) {
|
||
console.log('📦 Creating punimtag_auth database...');
|
||
await adminClient.query('CREATE DATABASE punimtag_auth');
|
||
console.log('✅ punimtag_auth database created\n');
|
||
} else {
|
||
console.log('✅ punimtag_auth database exists\n');
|
||
}
|
||
|
||
await adminClient.end();
|
||
|
||
// Now connect to punimtag_auth and create tables
|
||
const authClient = new Client({
|
||
user: authConfig.user,
|
||
password: (authConfig as any).password,
|
||
host: authConfig.host,
|
||
port: authConfig.port,
|
||
database: 'punimtag_auth',
|
||
});
|
||
|
||
try {
|
||
await authClient.connect();
|
||
console.log('🔗 Connected to punimtag_auth database\n');
|
||
|
||
// Check if users table exists
|
||
const usersTableCheck = await authClient.query(`
|
||
SELECT EXISTS (
|
||
SELECT FROM information_schema.tables
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'users'
|
||
);
|
||
`);
|
||
|
||
if (!usersTableCheck.rows[0].exists) {
|
||
console.log('📋 Creating tables in punimtag_auth...');
|
||
|
||
// Read and execute setup-auth-tables.sql
|
||
const setupScript = readFileSync(
|
||
join(__dirname, '../setup-auth-tables.sql'),
|
||
'utf-8'
|
||
);
|
||
|
||
// Split by semicolons and execute each statement
|
||
const statements = setupScript
|
||
.split(';')
|
||
.map(s => s.trim())
|
||
.filter(s => s.length > 0 && !s.startsWith('--') && !s.startsWith('\\'));
|
||
|
||
for (const statement of statements) {
|
||
if (statement.length > 0) {
|
||
try {
|
||
await authClient.query(statement);
|
||
} catch (error: any) {
|
||
// Ignore "already exists" errors
|
||
if (!error.message.includes('already exists')) {
|
||
console.error(`Error executing: ${statement.substring(0, 50)}...`);
|
||
throw error;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
console.log('✅ Tables created\n');
|
||
} else {
|
||
console.log('✅ Tables already exist in punimtag_auth\n');
|
||
}
|
||
|
||
// Check for required migrations
|
||
console.log('🔍 Checking for required migrations...\n');
|
||
|
||
// Check pending_photos table
|
||
const pendingPhotosCheck = await authClient.query(`
|
||
SELECT EXISTS (
|
||
SELECT FROM information_schema.tables
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'pending_photos'
|
||
);
|
||
`);
|
||
|
||
if (!pendingPhotosCheck.rows[0].exists) {
|
||
console.log('📋 Creating pending_photos table...');
|
||
const migrationScript = readFileSync(
|
||
join(__dirname, '../migrations/add-pending-photos-table.sql'),
|
||
'utf-8'
|
||
);
|
||
const statements = migrationScript
|
||
.split(';')
|
||
.map(s => s.trim())
|
||
.filter(s => s.length > 0 && !s.startsWith('--') && !s.startsWith('\\'));
|
||
|
||
for (const statement of statements) {
|
||
if (statement.length > 0) {
|
||
try {
|
||
await authClient.query(statement);
|
||
} catch (error: any) {
|
||
if (!error.message.includes('already exists')) {
|
||
throw error;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
console.log('✅ pending_photos table created\n');
|
||
}
|
||
|
||
// Check email verification columns
|
||
const emailVerificationCheck = await authClient.query(`
|
||
SELECT column_name
|
||
FROM information_schema.columns
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'users'
|
||
AND column_name = 'email_verified'
|
||
`);
|
||
|
||
if (emailVerificationCheck.rows.length === 0) {
|
||
console.log('📋 Adding email verification columns...');
|
||
const migrationScript = readFileSync(
|
||
join(__dirname, '../migrations/add-email-verification-columns.sql'),
|
||
'utf-8'
|
||
);
|
||
const statements = migrationScript
|
||
.split(';')
|
||
.map(s => s.trim())
|
||
.filter(s => s.length > 0 && !s.startsWith('--') && !s.startsWith('\\'));
|
||
|
||
for (const statement of statements) {
|
||
if (statement.length > 0) {
|
||
try {
|
||
await authClient.query(statement);
|
||
} catch (error: any) {
|
||
if (!error.message.includes('already exists')) {
|
||
throw error;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
console.log('✅ Email verification columns added\n');
|
||
}
|
||
|
||
console.log('🎉 Database setup complete!\n');
|
||
console.log('Next steps:');
|
||
console.log('1. Ensure your .env file has correct DATABASE_URL_AUTH');
|
||
console.log('2. Run: npm run prisma:generate:all');
|
||
console.log('3. Create admin user: npx tsx scripts/create-admin-user.ts');
|
||
|
||
} catch (error: any) {
|
||
console.error('\n❌ Error setting up tables:', error.message);
|
||
if (error.message.includes('permission denied')) {
|
||
console.error('\n⚠️ Permission denied. You may need to run this as a PostgreSQL superuser.');
|
||
}
|
||
throw error;
|
||
} finally {
|
||
await authClient.end();
|
||
}
|
||
|
||
} catch (error: any) {
|
||
console.error('\n❌ Error:', error.message);
|
||
if (error.message.includes('password authentication failed')) {
|
||
console.error('\n⚠️ Authentication failed. Please check:');
|
||
console.error(' 1. Database credentials in .env file');
|
||
console.error(' 2. PostgreSQL is running');
|
||
console.error(' 3. User has permission to create databases');
|
||
}
|
||
process.exit(1);
|
||
} finally {
|
||
await adminClient.end();
|
||
}
|
||
}
|
||
|
||
checkAndCreateDatabases();
|
||
|