rik-database

Provides a database classes for a specific project named 'RIK'

Examples

import { 
    Version, 
    IVersion, 
    DbSettings, 
    Rik, 
    NetworkGlobalSettings,
    NetworkInterfaceSettings, 
    INetworkInterfaceSettings,
    Plst } from 'rik-database';

const config = {
    client: 'pg',
    connection: 
    { 
        host: 'your host', 
        user: 'user', 
        password: 'password', 
        database: 'databaseName', 
        port: 'your port' 
    } 
};  

DbSettings.setConfig(config);

export class Db {

    public async getDataVersion(): Promise<void> {
        let data = await Version.query().orderBy('id', 'desc').first();

        console.log('\n+------- Versions -------+');
        if(data){ console.log(data); }
        else{ console.log('Table is empty.'); }
    }

    public async addNewVersion(version: IVersion): Promise<void> {
        let newVersion = await Version.query().insert(version);       
        console.log('\nNew version has been added. Version: ', newVersion);
    }

    public async updateVersion(id: number, version: IVersion): Promise<void> {
        let updateVersion = await Version.query().patchAndFetchById(id, version);      
        console.log('\nVersion has been updated. Version: ', updateVersion);
    }

    public async deleteVersionBy(id: number): Promise<void> {
        let deletedVersion = await Version.query().deleteById(id);
        console.log('\nVesions has been deleted, numbers: ', deletedVersion);
    }

    public async getTfzu(req: number): Promise<void> {
        let task = Plst.query().skipUndefined()
                            .select('ECO1','PNUM','a.ATTE as FZ_TFZU_1')
                            .leftOuterJoin('ATST as a', Rik.raw('cast("PLST"."FZ_TFZU_1" as int)'), 'a.ATNU')
                            .where('PLST.PNUM', '=', req)
                            .andWhere('PLST.GPR1', '>', 0)
                            .andWhere('PLST.WALO', '=', 0);
        
        console.log('[SQL]: ', task.toSql());    
        let data = await task;        

        console.log(data);
    }

    /**Inserting object array  */
    public async setSettings(req: any, res: any): Promise<void> {
        try {

            let set = [
                new NetworkInterfaceSettings({
                    name: 'et34',
                    method_name: 'auto',
                    ip_address: '127.0.0.1',
                    gateway: '127.0.0.1'
                }),
                new NetworkInterfaceSettings({
                    name: 'et82',
                    method_name: 'manual',
                    ip_address: '127.0.0.1',
                    gateway: '127.0.0.1'
                })
            ];

            let task = NetworkInterfaceSettings.query().insertAndFetch(set);

            console.log('sql request:', task.toSql());

            let data = await task;

            console.log(data);
        } catch (error) {
            console.log('[SEARCHDB:setSettings]:', error);
        }
    }

    public async setSettingsWithTransaction(req: any, res: any): Prommise<void> {
        try {

            if(req){

                const { interfacesSettings } = req;

                //region NetworkInterfaceSettings
                const interfacesSettingsItems = new Array<NetworkInterfaceSettings>();
                const interfacesSettingsInsertsItems = new Array<NetworkInterfaceSettings>();
                const interfacesSettingsUpdatesItems = new Array<NetworkInterfaceSettings>();
                //endregion

                //region For typescript
                const networkInterfacesSettings = interfacesSettings as Array<INetworkInterfaceSettings>;  
                //endregion
                
                //region Creates instances items 
                if(interfacesSettings instanceof Array && networkInterfacesSettings !== null && networkInterfacesSettings.length > 0){
                                                       
                    networkInterfacesSettings.forEach(element => {
                        interfacesSettingsItems.push(new NetworkInterfaceSettings(element));
                    });                   
                }
                //endregion

                //region gets items which are needed to update or gets items which are needed to insert
                if(interfacesSettingsItems.length > 0){
                    for (let i = 0; i < interfacesSettingsItems.length; i++) {
                        let item = interfacesSettingsItems[i];
                        
                        let updateInterfaceItem = await NetworkInterfaceSettings.query().where('name', '=', item.name);
                        if(updateInterfaceItem !== null && updateInterfaceItem !== undefined && updateInterfaceItem.length > 0){
                            interfacesSettingsUpdatesItems.push(item)
                        }
                        else{
                            interfacesSettingsInsertsItems.push(item);
                        }
                    }
                }
                //endregion

                console.log('updates interfaces items', interfacesSettingsUpdatesItems);
                console.log('inserts interfaces items', interfacesSettingsInsertsItems);
                
                let countItems = 0                 
                
                function addItems (params: number | Array<number>) {
                    if(params instanceof Array){
                        countItems += params.length;
                    }
                    else{
                        countItems += params;
                    }
                }       

                const trx = await Rik.transaction.start(DbSettings.knex);
                try {

                    let insertsInterfacesItems = await NetworkInterfaceSettings.query(trx).insertAndFetch(interfacesSettingsInsertsItems);
                    addItems(insertsInterfacesItems.length);                        

                    for (let i = 0; i < interfacesSettingsUpdatesItems.length; i++) {
                        let item = interfacesSettingsUpdatesItems[i];
                        
                        let result = await NetworkInterfaceSettings.query(trx).patch(item).where('name', '=', item.name);
                        
                        addItems(result);
                    }
                    await trx.commit();
                } catch (error) {
                    trx.rollback(error);
                    throw error;
                }

                console.log('Updated and inserted item numbers:', countItems);                                    
            }
        } catch (error) {
            console.log('[setSettings]:', error);
        }
    }
}

(async () => {
    try {      
        let db = new Db();
        et version = { current_version: '2.2.2.5', description: 'Some info' }

        let interfacesSettings = [
            {
                name: 'eqwe1',
                method_name: 'auto',
                ip_address: '127.0.0.1',
                gateway: '127.0.0.1',
            },
            {
                name: 'et24fn62',
                method_name: 'manual',
                ip_address: '127.0.0.1',
                gateway: '127.0.0.1',
            }
        ];

        let req = {
            interfacesSettings
        }

        //await db.getDataVersion();
        //await db.addNewVersion(version)
        //await db.updateVersion(9, version);
        //await db.deleteVersionBy(10);
        //console.log('config: ', DbSettings.config);
        //await db.getTfzu(1);
        //await db.setSettings(3, 4);
        await db.setSettingsWithTransaction(req, 4);

} catch (error) { console.error(error); } })()