import { ICollection , IInsertItem , IGetItem , IQuery , ensureArray , IInsert , ICollections , fieldType } from 'debe' ; import { DebeAdapter } from 'debe-adapter' ; export abstract class SQLCore extends DebeAdapter { abstract exec < T > ( sql , args , type ? ) ; async initialize ( collections , options ) { await Promise . all ( Object . keys ( collections ) . map ( key => this . createTable ( collections [ key ] ) ) ) ; } close ( ) { } getColumnType ( type , secondType ? ) { if ( type === 'boolean' ) { return 'BOOLEAN' ; } else if ( type === 'json' ) { return 'JSON' ; } else if ( type === 'number' ) { return 'BIGINT' ; } else if ( type === 'string' ) { return 'TEXT' ; } else if ( secondType ) { return this . getColumnType ( secondType ) ; } return 'TEXT' ; } createTableIndex ( collection , field , type ? ) { return `CREATE INDEX IF NOT EXISTS "${ collection . name }_${ field }" ON "${ collection . name }" (${ field })` ; } createInsertStatement ( collection , fields = [ ] ) { fields = fields . length ? fields : Object . keys ( collection . fields ) ; const base = `INSERT INTO "${ collection . name }" (${ fields . join ( ', ' ) }) VALUES (${ fields . map ( ( ) => '?' ) . join ( ', ' ) })` ; const conflict = fields . filter ( x => x !== collection . specialFields . id ) . map ( key => `${ key }=EXCLUDED.${ key }` ) . join ( ', ' ) ; return `${ base } ON CONFLICT(${ collection . specialFields . id }) DO UPDATE SET ${ conflict }` ; } async createTable ( collection ) { await this . exec ( '' , [ `CREATE TABLE IF NOT EXISTS "${ collection . name }" (${ [ ... Object . keys ( collection . fields ) . map ( key => `${ key } ${ this . getColumnType ( collection . fields [ key ] ) }${ collection . specialFields . id === key ? ' PRIMARY KEY' : '' }` ) ] . join ( ', ' ) })` ] ) ; await Promise . all ( Object . keys ( collection . fields ) . map ( key => this . exec ( '' , [ `ALTER TABLE "${ collection . name }" ADD ${ key } ${ this . getColumnType ( collection . fields [ key ] ) }${ collection . specialFields . id === key ? ' PRIMARY KEY' : '' }` ] ) . catch ( ( ) => { } ) ) ) . catch ( ( ) => { } ) ; await this . exec ( '' , [ ... Object . keys ( collection . index ) . filter ( x => x !== collection . specialFields . id ) . map ( key => this . createTableIndex ( collection , key , this . getColumnType ( collection . index [ key ] , collection . fields [ key ] ) ) ) ] ) ; } makeCount ( statement ) { return `SELECT COUNT(*) AS count FROM (${ statement }) AS src` ; } createSelect ( collection , fields = [ ] ) { fields = fields . length ? [ ... new Set ( [ ... fields , 'id' , 'rev' ] ) ] : Object . keys ( collection . fields ) ; return `SELECT ${ fields . join ( ', ' ) }` ; } createOffset ( offset ? ) { return offset !== undefined ? `OFFSET ${ offset }` : '' ; } createLimit ( limit ? ) { if ( limit !== undefined && ! Array . isArray ( limit ) ) { return `LIMIT ${ limit }` ; } if ( limit !== undefined && Array . isArray ( limit ) && limit . length === 1 ) { return `LIMIT ${ limit [ 0 ] }` ; } if ( limit !== undefined && Array . isArray ( limit ) && limit . length === 2 ) { return `LIMIT ${ limit [ 0 ] } OFFSET ${ limit [ 1 ] }` ; } return '' ; } createOrderBy ( collection , orderBy ) { orderBy = ensureArray ( orderBy ) ; return orderBy . length ? `ORDER BY ${ orderBy . join ( ', ' ) }` : '' ; } createQueryStatement ( collection , queryArgs = { } , queryType ) { if ( typeof queryArgs === 'string' || Array . isArray ( queryArgs ) ) { const [ whereStatement , ... args ] = this . createWhereId ( queryArgs ) ; const sql = `
          ${ this . createSelect ( collection ) }
          FROM "${ collection . name }" 
          ${ whereStatement }
        ` . trim ( ) ; return [ queryType === 'count' ? this . makeCount ( sql ) : sql , ... args ] ; } else { const { where = [ ] , orderBy = [ ] , limit = undefined , offset = undefined , select } = queryArgs ; const [ whereStatement , ... args ] = this . createWhere ( collection , where ) ; const sql = `
          ${ this . createSelect ( collection , select ) }
          FROM "${ collection . name }" 
          ${ whereStatement }
          ${ this . createOrderBy ( collection , orderBy ) }
          ${ this . createLimit ( queryType === 'get' ? 1 : limit ) }
          ${ this . createOffset ( offset ) }
        ` . trim ( ) ; return [ queryType === 'count' ? this . makeCount ( sql ) : sql , ... args ] ; } } createWhere ( collection , where ) { where = ensureArray ( where ) ; const [ clause , ... args ] = where ; return [ clause ? `WHERE ${ clause }` : `` , ... args ] ; } createWhereId ( id ) { id = ensureArray ( id ) ; if ( id . length === 0 ) { return [ '' ] ; } else if ( id . length === 1 ) { return [ `WHERE id = ?` , id [ 0 ] ] ; } return [ `WHERE id IN (?)` , ... id ] ; } query < T > ( collection , queryArgs , queryType , skip = false ) { let [ sql , ... args ] = skip === false ? this . createQueryStatement ( collection , queryArgs , queryType ) : queryArgs ; const newArgs = [ ] ; const splitSQL = sql . split ( '?' ) ; let newSQL = '' ; if ( splitSQL . length !== args . length + 1 ) { throw new Error ( 'Length of args != length of "?" in sql statement' ) ; } newSQL = splitSQL [ 0 ] ; for ( var i = 0 ; i < args . length ; i ++ ) { let arg = args [ i ] ; const splitIndex = i + 1 ; if ( Array . isArray ( arg ) ) { const chunkSize = 500 ; if ( arg . length > chunkSize ) { arg = arg . slice ( 0 , chunkSize ) ; } const questionmarks = [ ... Array ( arg . length ) . keys ( ) ] . map ( ( ) => '?' ) ; newSQL += questionmarks . join ( ', ' ) + splitSQL [ splitIndex ] ; newArgs . push ( ... arg ) ; } else { newArgs . push ( arg ) ; newSQL += '?' + splitSQL [ splitIndex ] ; } } sql = newSQL ; args = newArgs ; if ( queryType === 'count' ) { return this . exec < any > ( sql , args , queryType ) ; } else if ( queryType === 'get' ) { return this . exec < any > ( sql , args , queryType ) ; } else { return this . exec < any > ( sql , args , queryType ) ; } } async insert < T > ( collection , value , options ) { const statement = this . createInsertStatement ( collection ) ; const items = value . map ( item => { return Object . keys ( collection . fields ) . map ( key => item [ key ] ) ; } ) ; await this . exec ( statement , items , 'insert' ) ; return value as any ; } async remove < T > ( collection , value ) { collection ; value ; return null as any ; } count ( collection , query ) { return this . query < number > ( collection , query , 'count' ) ; } get ( collection , id ) { return this . query ( collection , id , 'get' ) ; } all ( collection , query ) { return this . query < any > ( collection , query , 'all' ) ; } }