import { inject , injectable } from 'inversify' ; import { FindConditions , LessThan , LessThanOrEqual , MoreThanOrEqual , ObjectLiteral } from 'typeorm' ; import { QueryDeepPartialEntity } from 'typeorm/query-builder/QueryPartialEntity' ; import * as apid from '../../../api' ; import * as mapid from '../../../node_modules/mirakurun/api' ; import Program from '../../db/entities/Program' ; import DateUtil from '../../util/DateUtil' ; import StrUtil from '../../util/StrUtil' ; import IConfigFile from '../IConfigFile' ; import IConfiguration from '../IConfiguration' ; import ILogger from '../ILogger' ; import ILoggerModel from '../ILoggerModel' ; import IPromiseRetry from '../IPromiseRetry' ; import DBUtil from './DBUtil' ; import IChannelTypeIndex from './IChannelTypeHash' ; import IDBOperator from './IDBOperator' ; import IProgramDB , { FindRuleOption , FindScheduleIdOption , FindScheduleOption , ProgramUpdateValues , ProgramWithOverlap , } from './IProgramDB' ; interface FindQuery { strs : string [ ] ; param : ObjectLiteral ; } interface KeywordOption { cs : boolean ; regexp : boolean ; name : boolean ; description : boolean ; extended : boolean ; } @ injectable ( ) export default class ProgramDB implements IProgramDB { private log : ILogger ; private config : IConfigFile ; private op : IDBOperator ; private promieRetry : IPromiseRetry ; constructor ( @ inject ( 'ILoggerModel' ) logger , @ inject ( 'IConfiguration' ) conf , @ inject ( 'IDBOperator' ) op , @ inject ( 'IPromiseRetry' ) promieRetry ) { this . log = logger . getLogger ( ) ; this . config = conf . getConfig ( ) ; this . op = op ; this . promieRetry = promieRetry ; } param channelTypes param programs public async insert ( channelTypes , programs ) { const updateTime = new Date ( ) . getTime ( ) ; const values = [ ] ; for ( const program of programs ) { const value = this . createProgramValue ( channelTypes , program , updateTime ) ; if ( value !== null ) { values . push ( value ) ; } } const connection = await this . op . getConnection ( ) ; const queryRunner = connection . createQueryRunner ( ) ; await queryRunner . startTransaction ( ) ; let hasError = false ; try { await queryRunner . manager . delete ( Program , { } ) ; for ( const value of values ) { await queryRunner . manager . insert ( Program , value ) ; } await queryRunner . commitTransaction ( ) ; } catch ( err ) { console . error ( err ) ; hasError = true ; await queryRunner . rollbackTransaction ( ) ; } finally { await queryRunner . release ( ) ; } if ( hasError ) { throw new Error ( 'InsertError' ) ; } } param channelTypes param program param updateTime return private createProgramValue ( channelTypes , program , updateTime ) { if ( typeof program . name === 'undefined' ) { return null ; } if ( typeof channelTypes [ program . networkId ] === 'undefined' || typeof channelTypes [ program . networkId ] [ program . serviceId ] === 'undefined' ) { return null ; } const channelId = channelTypes [ program . networkId ] [ program . serviceId ] . id ; const channelType = channelTypes [ program . networkId ] [ program . serviceId ] . type ; const channel = channelTypes [ program . networkId ] [ program . serviceId ] . channel ; let genre1 = null ; let subGenre1 = null ; let genre2 = null ; let subGenre2 = null ; let genre3 = null ; let subGenre3 = null ; if ( typeof program . genres !== 'undefined' ) { if ( program . genres [ 0 ] . lv1 < 0xe ) { genre1 = program . genres [ 0 ] . lv1 ; subGenre1 = typeof program . genres [ 0 ] . lv2 === 'undefined' ? null : program . genres [ 0 ] . lv2 ; } if ( program . genres . length > 1 && program . genres [ 1 ] . lv1 < 0xe ) { genre2 = program . genres [ 1 ] . lv1 ; subGenre2 = typeof program . genres [ 1 ] . lv2 === 'undefined' ? null : program . genres [ 1 ] . lv2 ; } if ( program . genres . length > 2 && program . genres [ 2 ] . lv1 < 0xe ) { genre3 = program . genres [ 2 ] . lv1 ; subGenre3 = typeof program . genres [ 2 ] . lv2 === 'undefined' ? null : program . genres [ 2 ] . lv2 ; } } const jaDate = DateUtil . getJaDate ( new Date ( program . startAt ) ) ; const name = this . config . needToReplaceEnclosingCharacters === true ? StrUtil . toDBStr ( StrUtil . replaceEnclosedCharacters ( program . name ) ) : StrUtil . toDBStr ( program . name ) ; const halfWidthName = StrUtil . toHalf ( name ) ; const value = { id : program . id , updateTime : updateTime , channelId : channelId , eventId : program . eventId , serviceId : program . serviceId , networkId : program . networkId , startAt : program . startAt , endAt : program . startAt + program . duration , startHour : jaDate . getHours ( ) , week : jaDate . getDay ( ) , duration : program . duration , isFree : program . isFree , name : name , halfWidthName : halfWidthName , shortName : StrUtil . deleteBrackets ( halfWidthName ) , genre1 : genre1 , subGenre1 : subGenre1 , genre2 : genre2 , subGenre2 : subGenre2 , genre3 : genre3 , subGenre3 : subGenre3 , channelType : channelType , channel : channel , } ; if ( typeof program . description === 'undefined' || program . description . length === 0 ) { value . description = null ; value . halfWidthDescription = null ; } else { const description = this . config . needToReplaceEnclosingCharacters === true ? StrUtil . toDBStr ( StrUtil . replaceEnclosedCharacters ( program . description ) ) : StrUtil . toDBStr ( program . description ) ; value . description = description ; value . halfWidthDescription = StrUtil . toHalf ( description ) ; } if ( typeof program . extended === 'undefined' ) { value . extended = null ; value . halfWidthExtended = null ; value . rawExtended = null ; value . rawHalfWidthExtended = null ; } else { const extended = this . createExtendedStr ( program . extended ) ; value . extended = extended ; value . halfWidthExtended = StrUtil . toHalf ( extended ) ; value . rawExtended = JSON . stringify ( program . extended ) ; const halfRawExtended = { } ; for ( const key in program . extended ) { halfRawExtended [ StrUtil . toHalf ( key ) ] = StrUtil . toHalf ( program . extended [ key ] ) ; } value . rawHalfWidthExtended = JSON . stringify ( halfRawExtended ) ; } if ( typeof program . video !== 'undefined' ) { value . videoType = program . video . type ; value . videoResolution = program . video . resolution ; value . videoStreamContent = program . video . streamContent ; value . videoComponentType = program . video . componentType ; } if ( typeof ( program as any ) . audio !== 'undefined' ) { value . audioSamplingRate = ( program as any ) . audio . samplingRate ; value . audioComponentType = ( program as any ) . audio . componentType ; } if ( typeof ( program as any ) . audios !== 'undefined' ) { for ( const audio of ( program as any ) . audios ) { if ( audio . isMain === false ) { continue ; } value . audioSamplingRate = audio . samplingRate ; value . audioComponentType = audio . componentType ; } } return value ; } param extended return private createExtendedStr ( extended ) { let str = '' ; for ( const key in extended ) { if ( key . slice ( 0 , 1 ) === '◇' ) { str += `\n${ key }\n${ extended [ key ] }` ; } else { str += `\n◇${ key }\n${ extended [ key ] }` ; } } const ret = StrUtil . toDBStr ( str ) . trim ( ) ; return this . config . needToReplaceEnclosingCharacters === true ? StrUtil . replaceEnclosedCharacters ( ret ) : ret ; } param channelTypes param values return public async update ( channelTypes , values ) { const updateTime = new Date ( ) . getTime ( ) ; const insertValues = [ ] ; for ( const program of values . insert ) { const value = this . createProgramValue ( channelTypes , program , updateTime ) ; if ( value !== null ) { insertValues . push ( value ) ; } } for ( const program of values . update ) { const value = this . createProgramValue ( channelTypes , program , updateTime ) ; if ( value !== null ) { insertValues . push ( value ) ; } } const connection = await this . op . getConnection ( ) ; const queryRunner = connection . createQueryRunner ( ) ; await queryRunner . startTransaction ( ) ; let hasError = false ; try { for ( const id of values . delete ) { await queryRunner . manager . delete ( Program , id ) . catch ( err => { this . log . system . error ( `program delete error: ${ id }` ) ; this . log . system . error ( err ) ; } ) ; } for ( const value of insertValues ) { await queryRunner . manager . insert ( Program , value ) . catch ( async ( err ) => { await queryRunner . manager . update ( Program , value . id , value ) . catch ( serr => { this . log . system . error ( 'program update error' ) ; this . log . system . error ( err ) ; this . log . system . error ( serr ) ; } ) ; } ) ; } await queryRunner . commitTransaction ( ) ; } catch ( err ) { console . error ( err ) ; hasError = true ; await queryRunner . rollbackTransaction ( ) ; } finally { await queryRunner . release ( ) ; } if ( hasError ) { throw new Error ( 'UpdateError' ) ; } } param time return public async deleteOld ( time ) { const connection = await this . op . getConnection ( ) ; const repository = connection . getRepository ( Program ) ; await this . promieRetry . run ( ( ) => { return repository . delete ( { endAt : LessThan ( time ) , } ) ; } ) ; } param programId return public async findId ( programId ) { const connection = await this . op . getConnection ( ) ; const repository = connection . getRepository ( Program ) ; const result = await this . promieRetry . run ( ( ) => { return repository . findOne ( { where : [ { id : programId } ] , } ) ; } ) ; return typeof result === 'undefined' ? null : result ; } param searchOption param reserveOption return public async findRule ( option ) { const connection = await this . op . getConnection ( ) ; const query = { strs : [ ] , param : { } , } ; this . setKeywordQuery ( option . searchOption , query ) ; this . setChannelQuery ( option . searchOption , query ) ; this . setGenresQuery ( option . searchOption , query ) ; this . setTimesQuery ( option . searchOption , query ) ; this . setFreeQuery ( option . searchOption , query ) ; this . setDurationMinQuery ( option . searchOption , query ) ; this . setDurationMaxQuery ( option . searchOption , query ) ; this . setSearchPeriodsQuery ( option . searchOption , query ) ; let str = '' ; for ( let i = 0 ; i < query . strs . length ; i ++ ) { if ( query . strs [ i ] . length === 0 ) { continue ; } str += `(${ query . strs [ i ] })` ; if ( i < query . strs . length - 1 ) { str += ' and ' ; } } if ( str . length === 0 ) { throw new Error ( 'InvalidFindRuleOption' ) ; } let select = await connection . createQueryBuilder ( ) . select ( 'program' ) ; if ( typeof option . reserveOption !== 'undefined' && option . reserveOption . avoidDuplicate === true ) { select = select . addSelect ( this . createOverlapQueryStr ( option . reserveOption . periodToAvoidDuplicate ) , 'overlap' ) ; } const queryBuilder = select . from ( Program , 'program' ) . where ( str , query . param ) . andWhere ( `${ new Date ( ) . getTime ( ) } <= program.endAt` ) . orderBy ( 'program.startAt' , 'ASC' ) . limit ( option . limit ) ; const result = await this . promieRetry . run ( ( ) => { return queryBuilder . getRawAndEntities ( ) ; } ) ; return result . entities . map ( ( entity , i ) => { ( < any > entity ) . overlap = Boolean ( ! ! result . raw [ i ] . overlap ) ; return < any > entity ; } ) ; } param searchOption param query private setKeywordQuery ( searchOption , query ) { if ( typeof searchOption . keyword !== 'undefined' ) { this . setKeywordOption ( searchOption . keyword , this . createKeywordOption ( searchOption , false ) , 'keyword' , false , query ) ; } if ( typeof searchOption . ignoreKeyword !== 'undefined' ) { this . setKeywordOption ( searchOption . ignoreKeyword , this . createKeywordOption ( searchOption , true ) , 'ignoreKeyword' , true , query ) ; } } param keyword param option param valueBaseName param isIgnore param query private setKeywordOption ( keyword , option , valueBaseName , isIgnore , query ) { const or = [ ] ; if ( option . regexp === true ) { const regexp = this . op . getRegexpStr ( option . cs ) ; const valueName = `${ valueBaseName }Regexp` ; query . param [ valueName ] = keyword ; if ( option . cs === true ) { if ( option . name === true ) { or . push ( `CAST(halfWidthName AS BINARY) ${ regexp } :${ valueName }` ) ; } if ( option . description === true ) { or . push ( `CAST(COALESCE(halfWidthDescription,'') AS BINARY) ${ regexp } :${ valueName }` ) ; } if ( option . extended === true ) { or . push ( `CAST(COALESCE(halfWidthExtended,'') AS BINARY) ${ regexp } :${ valueName }` ) ; } } else { if ( option . name === true ) { or . push ( `halfWidthName ${ regexp } :${ valueName }` ) ; } if ( option . description === true ) { or . push ( `COALESCE(halfWidthDescription,'') ${ regexp } :${ valueName }` ) ; } if ( option . extended === true ) { or . push ( `COALESCE(halfWidthExtended,'') ${ regexp } :${ valueName }` ) ; } } } else { const keywords = StrUtil . toHalf ( keyword ) . split ( / / ) ; const like = this . op . getLikeStr ( option . cs ) ; const nameAnd = [ ] ; const descriptionAnd = [ ] ; const extendedAnd = [ ] ; keywords . forEach ( ( str , i ) => { str = `%${ str }%` ; if ( option . name === true ) { const valueName = `${ valueBaseName }Name${ i }` ; nameAnd . push ( `halfWidthName ${ like } :${ valueName }` ) ; query . param [ valueName ] = str ; } if ( option . description === true ) { const valueName = `${ valueBaseName }Description${ i }` ; descriptionAnd . push ( `COALESCE(halfWidthDescription,'') ${ like } :${ valueName }` ) ; query . param [ valueName ] = str ; } if ( option . extended === true ) { const valueName = `${ valueBaseName }Extended${ i }` ; extendedAnd . push ( `COALESCE(halfWidthExtended,'') ${ like } :${ valueName }` ) ; query . param [ valueName ] = str ; } } ) ; if ( nameAnd . length > 0 ) { or . push ( `(${ DBUtil . createAndQuery ( nameAnd ) })` ) ; } if ( descriptionAnd . length > 0 ) { or . push ( `(${ DBUtil . createAndQuery ( descriptionAnd ) })` ) ; } if ( extendedAnd . length > 0 ) { or . push ( `(${ DBUtil . createAndQuery ( extendedAnd ) })` ) ; } } const orStr = DBUtil . createOrQuery ( or ) ; query . strs . push ( isIgnore ? `(not (${ orStr }))` : orStr ) ; } param option param isIgnore return private createKeywordOption ( option , isIgnore ) { const keyOption = { cs : isIgnore ? ! ! option . ignoreKeyCS : ! ! option . keyCS , regexp : isIgnore ? ! ! option . ignoreKeyRegExp : ! ! option . keyRegExp , name : isIgnore ? ! ! option . ignoreName : ! ! option . name , description : isIgnore ? ! ! option . ignoreDescription : ! ! option . description , extended : isIgnore ? ! ! option . ignoreExtended : ! ! option . extended , } ; if ( this . op . isEnableCS ( ) === false ) { keyOption . cs = false ; } if ( this . op . isEnabledRegexp ( ) === false ) { keyOption . regexp = false ; } return keyOption ; } param searchOption param query private setChannelQuery ( searchOption , query ) { if ( typeof searchOption . channelIds !== 'undefined' ) { this . createInQuery ( query , 'channelId' , searchOption . channelIds ) ; } else { const channelTypes = [ ] ; if ( ! ! searchOption . GR === true ) { channelTypes . push ( 'GR' ) ; } if ( ! ! searchOption . BS === true ) { channelTypes . push ( 'BS' ) ; } if ( ! ! searchOption . CS === true ) { channelTypes . push ( 'CS' ) ; } if ( ! ! searchOption . SKY === true ) { channelTypes . push ( 'SKY' ) ; } this . createInQuery ( query , 'channelType' , channelTypes ) ; } } param option param query private setGenresQuery ( option , query ) { if ( typeof option . genres === 'undefined' || option . genres . length === 0 ) { return ; } const strs = [ ] ; for ( let i = 0 ; i < option . genres . length ; i ++ ) { const genreBaseName = `genre${ i }` ; const subGenreBaseName = `subgenre${ i }` ; query . param [ genreBaseName ] = option . genres [ i ] . genre ; if ( typeof option . genres [ i ] . subGenre === 'undefined' ) { strs . push ( '(' + `genre1 = :${ genreBaseName } or ` + `genre2 = :${ genreBaseName } or ` + `genre3 = :${ genreBaseName }` + ')' ) ; } else { strs . push ( '(' + '(' + `genre1 = :${ genreBaseName } and ` + `subGenre1 = :${ subGenreBaseName }` + ') or ' + '(' + `genre2 = :${ genreBaseName } and ` + `subGenre2 = :${ subGenreBaseName }` + ') or ' + '(' + `genre3 = :${ genreBaseName } and ` + `subGenre3 = :${ subGenreBaseName }` + ')' + ')' ) ; query . param [ subGenreBaseName ] = option . genres [ i ] . subGenre ; } } query . strs . push ( DBUtil . createOrQuery ( strs ) ) ; } param searchOption param query private setTimesQuery ( option , query ) { if ( typeof option . times === 'undefined' || option . times . length === 0 ) { return ; } const strs = [ ] ; for ( let i = 0 ; i < option . times . length ; i ++ ) { const weeks = [ ] ; if ( ( option . times [ i ] . week & 0x01 ) !== 0 ) { weeks . push ( 0 ) ; } if ( ( option . times [ i ] . week & 0x02 ) !== 0 ) { weeks . push ( 1 ) ; } if ( ( option . times [ i ] . week & 0x04 ) !== 0 ) { weeks . push ( 2 ) ; } if ( ( option . times [ i ] . week & 0x08 ) !== 0 ) { weeks . push ( 3 ) ; } if ( ( option . times [ i ] . week & 0x10 ) !== 0 ) { weeks . push ( 4 ) ; } if ( ( option . times [ i ] . week & 0x20 ) !== 0 ) { weeks . push ( 5 ) ; } if ( ( option . times [ i ] . week & 0x40 ) !== 0 ) { weeks . push ( 6 ) ; } if ( weeks . length === 0 ) { continue ; } const weekBaseColumnName = `week${ i }` ; let queryStr = `week in (:...${ weekBaseColumnName })` ; query . param [ weekBaseColumnName ] = weeks ; const start = option . times [ i ] . start ; const range = option . times [ i ] . range ; if ( typeof start !== 'undefined' && typeof range !== 'undefined' ) { const startHourBaseColumnName = `time${ i }` ; const endTime = start + range - 1 ; if ( start === endTime ) { queryStr += ` and startHour = :${ startHourBaseColumnName }` ; query . param [ startHourBaseColumnName ] = start ; } else { const times = [ ] ; for ( let j = start ; j <= endTime ; j ++ ) { times . push ( j % 24 ) ; } queryStr += `and startHour in (:...${ startHourBaseColumnName })` ; query . param [ startHourBaseColumnName ] = times ; } } strs . push ( `(${ queryStr })` ) ; } query . strs . push ( DBUtil . createOrQuery ( strs ) ) ; } param searchOption param query private setFreeQuery ( option , query ) { if ( ! ! option . isFree === false ) { return ; } const column = 'isFree' ; query . strs . push ( `isFree = :${ column }` ) ; query . param [ column ] = this . op . convertBoolean ( true ) ; } param searchOption param query private setDurationMinQuery ( option , query ) { if ( typeof option . durationMin === 'undefined' ) { return ; } const column = 'durationMin' ; query . strs . push ( `duration >= :${ column }` ) ; query . param [ column ] = option . durationMin * 1000 ; } param searchOption param query private setDurationMaxQuery ( option , query ) { if ( typeof option . durationMax === 'undefined' ) { return ; } const column = 'durationMax' ; query . strs . push ( `duration <= :${ column }` ) ; query . param [ column ] = option . durationMax * 1000 ; } param searchOption param query private setSearchPeriodsQuery ( option , query ) { if ( typeof option . searchPeriods === 'undefined' || option . searchPeriods . length === 0 ) { return ; } const or = [ ] ; for ( let i = 0 ; i < option . searchPeriods . length ; i ++ ) { const startAtColumn = `sarchPeriodsStartAt${ i }` ; const endAtColumn = `sarchPeriodsEndAt${ i }` ; query . param [ startAtColumn ] = option . searchPeriods [ i ] . startAt ; query . param [ endAtColumn ] = option . searchPeriods [ i ] . endAt ; or . push ( `(startAt >= :${ startAtColumn } and startAt <= :${ endAtColumn })` ) ; } query . strs . push ( DBUtil . createOrQuery ( or ) ) ; } param query param column param values private createInQuery ( query , column , values ) { if ( values . length === 0 ) { return ; } query . strs . push ( `${ column } in (:...${ column })` ) ; query . param [ column ] = values ; } param periodToAvoidDuplicate private createOverlapQueryStr ( periodToAvoidDuplicate ) { const period = typeof periodToAvoidDuplicate !== 'undefined' && periodToAvoidDuplicate > 0 ? periodToAvoidDuplicate * 24 * 60 * 60 * 1000 : 0 ; const now = new Date ( ) . getTime ( ) ; let str = 'case when id in ' + '(' + 'select P.id from program as P, recorded_history as R ' + 'where P.shortName = R.name ' + 'and P.channelId = R.channelId ' ; if ( period > 0 ) { str += `and R.endAt >= ${ now - period } and R.endAt <= ${ now } and P.endAt <= (R.endAt + ${ period }) ` ; } else { str += `and R.endAt <= ${ now } ` ; } str += ') then 1 else 0 end' ; return str ; } param channelId param startAt return public async findChannelIdAndTime ( channelId , startAt ) { const connection = await this . op . getConnection ( ) ; const repository = connection . getRepository ( Program ) ; const result = await this . promieRetry . run ( ( ) => { return repository . find ( { channelId : channelId , startAt : LessThanOrEqual ( startAt ) , endAt : MoreThanOrEqual ( startAt ) , } ) ; } ) ; return result . length === 0 ? null : result [ 0 ] ; } return public async findAll ( ) { const connection = await this . op . getConnection ( ) ; const repository = connection . getRepository ( Program ) ; return await this . promieRetry . run ( ( ) => { return repository . find ( { order : { startAt : 'ASC' , } , } ) ; } ) ; } param option return public async findSchedule ( option ) { const connection = await this . op . getConnection ( ) ; let queryOption ; if ( typeof ( < FindScheduleIdOption > option ) . channelId !== 'undefined' ) { queryOption = { startAt : LessThanOrEqual ( option . endAt ) , endAt : MoreThanOrEqual ( option . startAt ) , channelId : ( < FindScheduleIdOption > option ) . channelId , } ; if ( typeof option . isFree !== 'undefined' ) { queryOption . isFree = option . isFree ; } } else if ( typeof ( < FindScheduleOption > option ) . types !== 'undefined' && ( < FindScheduleOption > option ) . types . length > 0 ) { queryOption = [ ] ; for ( const type of ( < FindScheduleOption > option ) . types ) { const op = { startAt : LessThanOrEqual ( option . endAt ) , endAt : MoreThanOrEqual ( option . startAt ) , channelType : type , } ; if ( typeof option . isFree !== 'undefined' ) { op . isFree = option . isFree ; } queryOption . push ( op ) ; } } else { throw new Error ( 'FindScheduleOptionError' ) ; } const repository = connection . getRepository ( Program ) ; return await this . promieRetry . run ( ( ) => { return repository . find ( { where : queryOption , order : { startAt : 'ASC' , } , } ) ; } ) ; } param option return public async findBroadcasting ( option ) { let time = new Date ( ) . getTime ( ) ; if ( typeof option . time !== 'undefined' ) { time += option . time ; } const connection = await this . op . getConnection ( ) ; const repository = connection . getRepository ( Program ) ; return await this . promieRetry . run ( ( ) => { return repository . find ( { where : { startAt : LessThanOrEqual ( time ) , endAt : MoreThanOrEqual ( time ) , } , order : { startAt : 'ASC' , } , } ) ; } ) ; } }