server/initializers/database.ts

   1 import { QueryTypes, Transaction } from 'sequelize'
   2 import { Sequelize as SequelizeTypescript } from 'sequelize-typescript'
   3 import { AbuseModel } from '@server/models/abuse/abuse'
   4 import { VideoAbuseModel } from '@server/models/abuse/video-abuse'
   5 import { VideoCommentAbuseModel } from '@server/models/abuse/video-comment-abuse'
   6 import { isTestInstance } from '../helpers/core-utils'
   7 import { logger } from '../helpers/logger'
   8 import { AccountModel } from '../models/account/account'
   9 import { AccountBlocklistModel } from '../models/account/account-blocklist'
  10 import { AccountVideoRateModel } from '../models/account/account-video-rate'
  11 import { UserModel } from '../models/account/user'
  12 import { UserNotificationModel } from '../models/account/user-notification'
  13 import { UserNotificationSettingModel } from '../models/account/user-notification-setting'
  14 import { UserVideoHistoryModel } from '../models/account/user-video-history'
  15 import { ActorModel } from '../models/activitypub/actor'
  16 import { ActorFollowModel } from '../models/activitypub/actor-follow'
  17 import { ApplicationModel } from '../models/application/application'
  18 import { AvatarModel } from '../models/avatar/avatar'
  19 import { OAuthClientModel } from '../models/oauth/oauth-client'
  20 import { OAuthTokenModel } from '../models/oauth/oauth-token'
  21 import { VideoRedundancyModel } from '../models/redundancy/video-redundancy'
  22 import { PluginModel } from '../models/server/plugin'
  23 import { ServerModel } from '../models/server/server'
  24 import { ServerBlocklistModel } from '../models/server/server-blocklist'
  25 import { ScheduleVideoUpdateModel } from '../models/video/schedule-video-update'
  26 import { TagModel } from '../models/video/tag'
  27 import { ThumbnailModel } from '../models/video/thumbnail'
  28 import { VideoModel } from '../models/video/video'
  29 import { VideoBlacklistModel } from '../models/video/video-blacklist'
  30 import { VideoCaptionModel } from '../models/video/video-caption'
  31 import { VideoChangeOwnershipModel } from '../models/video/video-change-ownership'
  32 import { VideoChannelModel } from '../models/video/video-channel'
  33 import { VideoCommentModel } from '../models/video/video-comment'
  34 import { VideoFileModel } from '../models/video/video-file'
  35 import { VideoImportModel } from '../models/video/video-import'
  36 import { VideoPlaylistModel } from '../models/video/video-playlist'
  37 import { VideoPlaylistElementModel } from '../models/video/video-playlist-element'
  38 import { VideoShareModel } from '../models/video/video-share'
  39 import { VideoStreamingPlaylistModel } from '../models/video/video-streaming-playlist'
  40 import { VideoTagModel } from '../models/video/video-tag'
  41 import { VideoViewModel } from '../models/video/video-view'
  42 import { CONFIG } from './config'
  43
  44 require('pg').defaults.parseInt8 = true // Avoid BIGINT to be converted to string
  45
  46 const dbname = CONFIG.DATABASE.DBNAME
  47 const username = CONFIG.DATABASE.USERNAME
  48 const password = CONFIG.DATABASE.PASSWORD
  49 const host = CONFIG.DATABASE.HOSTNAME
  50 const port = CONFIG.DATABASE.PORT
  51 const poolMax = CONFIG.DATABASE.POOL.MAX
  52
  53 const sequelizeTypescript = new SequelizeTypescript({
  54   database: dbname,
  55   dialect: 'postgres',
  56   host,
  57   port,
  58   username,
  59   password,
  60   pool: {
  61     max: poolMax
  62   },
  63   benchmark: isTestInstance(),
  64   isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE,
  65   logging: (message: string, benchmark: number) => {
  66     if (process.env.NODE_DB_LOG === 'false') return
  67
  68     let newMessage = message
  69     if (isTestInstance() === true && benchmark !== undefined) {
  70       newMessage += ' | ' + benchmark + 'ms'
  71     }
  72
  73     logger.debug(newMessage)
  74   }
  75 })
  76
  77 async function initDatabaseModels (silent: boolean) {
  78   sequelizeTypescript.addModels([
  79     ApplicationModel,
  80     ActorModel,
  81     ActorFollowModel,
  82     AvatarModel,
  83     AccountModel,
  84     OAuthClientModel,
  85     OAuthTokenModel,
  86     ServerModel,
  87     TagModel,
  88     AccountVideoRateModel,
  89     UserModel,
  90     AbuseModel,
  91     VideoCommentAbuseModel,
  92     VideoAbuseModel,
  93     VideoModel,
  94     VideoChangeOwnershipModel,
  95     VideoChannelModel,
  96     VideoShareModel,
  97     VideoFileModel,
  98     VideoCaptionModel,
  99     VideoBlacklistModel,
 100     VideoTagModel,
 101     VideoCommentModel,
 102     ScheduleVideoUpdateModel,
 103     VideoImportModel,
 104     VideoViewModel,
 105     VideoRedundancyModel,
 106     UserVideoHistoryModel,
 107     AccountBlocklistModel,
 108     ServerBlocklistModel,
 109     UserNotificationModel,
 110     UserNotificationSettingModel,
 111     VideoStreamingPlaylistModel,
 112     VideoPlaylistModel,
 113     VideoPlaylistElementModel,
 114     ThumbnailModel,
 115     PluginModel
 116   ])
 117
 118   // Check extensions exist in the database
 119   await checkPostgresExtensions()
 120
 121   // Create custom PostgreSQL functions
 122   await createFunctions()
 123
 124   if (!silent) logger.info('Database %s is ready.', dbname)
 125 }
 126
 127 // ---------------------------------------------------------------------------
 128
 129 export {
 130   initDatabaseModels,
 131   sequelizeTypescript
 132 }
 133
 134 // ---------------------------------------------------------------------------
 135
 136 async function checkPostgresExtensions () {
 137   const promises = [
 138     checkPostgresExtension('pg_trgm'),
 139     checkPostgresExtension('unaccent')
 140   ]
 141
 142   return Promise.all(promises)
 143 }
 144
 145 async function checkPostgresExtension (extension: string) {
 146   const query = `SELECT 1 FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
 147   const options = {
 148     type: QueryTypes.SELECT as QueryTypes.SELECT,
 149     raw: true
 150   }
 151
 152   const res = await sequelizeTypescript.query<object>(query, options)
 153
 154   if (!res || res.length === 0) {
 155     // Try to create the extension ourselves
 156     try {
 157       await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
 158
 159     } catch {
 160       const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
 161         `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
 162       throw new Error(errorMessage)
 163     }
 164   }
 165 }
 166
 167 function createFunctions () {
 168   const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(text)
 169   RETURNS text AS
 170 $func$
 171 SELECT public.unaccent('public.unaccent', $1::text)
 172 $func$  LANGUAGE sql IMMUTABLE;`
 173
 174   return sequelizeTypescript.query(query, { raw: true })
 175 }