react-native-fts-sqlite-library

React Native Full Text Search

Usage no npm install needed!

<script type="module">
  import reactNativeFtsSqliteLibrary from 'https://cdn.skypack.dev/react-native-fts-sqlite-library';
</script>

README

react-native-fts-sqlite-library

Typescript Support

Getting started

$ npm install react-native-fts-sqlite-library --save

Mostly automatic installation

$ react-native link react-native-fts-sqlite-library

Manual installation

Android

  1. Open up android/app/src/main/java/[...]/MainActivity.java
  • Add import com.fts.RNFtsSqliteLibraryPackage; to the imports at the top of the file
  • Add new RNFtsSqliteLibraryPackage() to the list returned by the getPackages() method
  1. Append the following lines to android/settings.gradle:
    include ':react-native-fts-sqlite-library'
project(':react-native-fts-sqlite-library').projectDir = new File(rootProject.projectDir, 	'../node_modules/react-native-fts-sqlite-library/android')
  2. Insert the following lines inside the dependencies block in android/app/build.gradle:
      compile project(':react-native-fts-sqlite-library')

Usage

Create Table


import RNFtsSqliteLibrary from 'react-native-fts-sqlite-librfts';

let error = null;

let DBClient = null;

try {
    DBClient = await RNFtsSqliteLibrary.newClientDB({
        dbName: 'CarShop.db',
        dbVersion: 1
    });
} catch(err) {
    error = err;
}

try {
    await DBClient.beginTransaction();
} catch(err) {
    error = err;
}

try {
    await DBClient.execSQL('CREATE TABLE IF NOT EXISTS car (' + 
    'car_id INTEGER PRIMARY KEY AUTOINCREMENT, model VARCHAR NOT NULL, ' + 
    'brand VARCHAR NOT NULL, year DATE NOT NULL, created_at DATE NOT NULL' + 
    ')');
} catch(err) {
    error = err;
}

try {
    await DBClient.execSQL('CREATE TABLE IF NOT EXISTS inventory (' + 
    'inventory_id INTEGER PRIMARY KEY AUTOINCREMENT, total INTEGER NOT NULL, car_id INTEGER, ' + 
    'FOREIGN KEY (car_id) REFERENCES car(car_id)' +
    ')');
} catch(err) {
    error = err;
}

if (error) {
    try {
        await DBClient.rollback();
        console.error(error.message);
    } catch(err) {
        console.error(err.message);
    }

    try {
        await DBClient.close();
    } catch(err) {
        console.error(err.message);
    }

    return;
}

try {
    await DBClient.commit();
} catch(err) {
    console.error(err.message);
}

try {
    await DBClient.close();
} catch(err) {
    console.error(err.message);
}

Insert


import RNFtsSqliteLibrary from 'react-native-fts-sqlite-librfts';

let error = null;

let DBClient = null;

try {
    DBClient = await RNFtsSqliteLibrary.newClientDB({
        dbName: 'CarShop.db',
        dbVersion: 1
    });
} catch(err) {
    error = err;
}

const tableManager = DBClient.tableManager;

try {
    await tableManager.beginTransaction();
} catch(err) {
    error = err;
}

const createdAt = moment().format('YYYY-MM-DD HH:mm:ss.SSS');

const car1ToSave = {
    model: 'Volkswagen', 
    brand: 'Jetta GLI', 
    year: {type: 'date', value: '2018', format: 'YYYY'}, //moment format
    created_at: {type: 'date', value: createdAt, format: 'YYYY-MM-DD HH:mm:ss.SSS'}, //moment format
};

let car1Id;

try {
    car1Id = await tableManager.insert('car', car1ToSave);
        
    if (car1Id && Number(car1Id) < 1) {
        error = "insert fail";
    } 
} catch(err) {
    error = err;
}

const inventory1ToSave = {
    total: 290,
    car_id: car1Id
};

try {
    let inventory1Id = await tableManager.insert('inventory', inventory1ToSave);
        
    if (inventory1Id && Number(inventory1Id) < 1) {
        error = "insert fail";
    } 
} catch(err) {
    error = err;
}

const car2ToSave = {
    model: 'Mercedes', 
    brand: 'A 200', 
    year: {type: 'date', value: '2019', format: 'YYYY'}, //moment format
    created_at: {type: 'date', value: createdAt, format: 'YYYY-MM-DD HH:mm:ss.SSS'}, //moment format
};

let car2Id;

try {
    car2Id = await tableManager.insert('car', car2ToSave);
    
    if (car2Id && Number(car2Id) < 1) {
        error = "insert fail";
    } 
} catch(err) {
    error = err;
}

const inventory2ToSave = {
    total: 94,
    car_id: car2Id
};

try {
    let inventory2Id = await tableManager.insert('inventory', inventory2ToSave);
        
    if (inventory2Id && Number(inventory2Id) < 1) {
        error = "insert fail";
    } 
} catch(err) {
    error = err;
}

if (error) {
    try {
        await tableManager.rollback();
        console.error(error.message);
    } catch(err) {
        console.error(err.message);
    }

    try {
        await DBClient.close();
    } catch(err) {
        console.error(err.message);
    }

    return;
}

try {
    await tableManager.commit();
} catch(err) {
    console.error(err.message);
}

try {
    await DBClient.close();
} catch(err) {
    console.error(err.message);
}

Search Query


import RNFtsSqliteLibrary from 'react-native-fts-sqlite-library';

let DBClient = null;

try {
    DBClient = await RNFtsSqliteLibrary.newClientDB({
        dbName: 'CarShop.db',
        dbVersion: 1
    });
} catch(err) {
    error = err;
}

const tableManager = DBClient.tableManager;

let resultSearch1;
let resultSearch2;

try {
    resultSearch1 = await tableManager.querySearch('SELECT * FROM inventory LEFT JOIN car ON car.car_id = inventory.inventory_id');
    resultSearch2 = await tableManager.querySearch(`SELECT STRFTIME('%Y', year) AS yearModel FROM car WHERE yearModel = ?`, ["2018"]);
} catch(err) {
    console.error(err.message);
}

console.info('result1: ', resultSearch1.totalHits);
console.info('result2: ', resultSearch2.totalHits);

if (Number(resultSearch1.totalHits) > 0) {
    resultSearch1.hits.map(obj => console.info(obj));
}

if (Number(resultSearch2.totalHits) > 0) {
    resultSearch2.hits.map(obj => console.info(obj));
}

try {
    await DBClient.close();
} catch(err) {
    console.error(err.message);
}

Search Criteria


//in progress

Update

import RNFtsSqliteLibrary from 'react-native-fts-sqlite-librfts';

let error = null;

let DBClient = null;

try {
    DBClient = await RNFtsSqliteLibrary.newClientDB({
        dbName: 'CarShop.db',
        dbVersion: 1
    });
} catch(err) {
    error = err;
}

const tableManager = DBClient.tableManager;

try {
    await tableManager.beginTransaction();
} catch(err) {
    error = err;
}

const inventoryUpdate = {
    total: 1000
};

try {
    let updatedInventory1Rows = await tableManager.update('inventory', inventoryUpdate, 'car_id = ?', [1]);
    
    if (updatedInventory1Rows && Number(updatedInventory1Rows) < 1) {
        error = "insert fail";
    }
} catch(err) {
    error = err;
}

const carUpdate = {
    year: {type: 'date', value: '2020', format: 'YYYY'}, //moment format
}

try {
    let updatedCar1Rows = await tableManager.update('car', carUpdate, `STRFTIME('%Y', year) = ?`, ['2019']);

    if (updatedCar1Rows && Number(updatedCar1Rows) < 1) {
        error = "insert fail";
    }
} catch(err) {
    error = err;
}

if (error) {
    try {
        await tableManager.rollback();
        console.error(error.message);
    } catch(err) {
        console.error(err.message);
    }

    try {
        await DBClient.close();
    } catch(err) {
        console.error(err.message);
    }

    return;
}

try {
    await tableManager.commit();
} catch(err) {
    console.error(err.message);
}

try {
    await DBClient.close();
} catch(err) {
    console.error(err.message);
}

Delete


import RNFtsSqliteLibrary from 'react-native-fts-sqlite-librfts';

let error = null;

let DBClient = null;

try {
    DBClient = await RNFtsSqliteLibrary.newClientDB({
        dbName: 'CarShop.db',
        dbVersion: 1
    });
} catch(err) {
    error = err;
}

const tableManager = DBClient.tableManager;

try {
    await tableManager.beginTransaction();
} catch(err) {
    error = err;
}

try {
    let deletedInventory1Rows = await tableManager.remove('inventory', 'car_id = ?', [1]);

    if (deletedInventory1Rows && Number(deletedInventory1Rows) < 1) {
        error = "insert fail";
    } 
} catch(err) {
    error = err;
}

try {
    let deletedCar1Rows = await tableManager.remove('car', `STRFTIME('%Y', year) = ?`, ['2018']);

    if (deletedCar1Rows && Number(deletedCar1Rows) < 1) {
        error = "insert fail";
    } 
} catch(err) {
    error = err;
}

if (error) {
    try {
        await tableManager.rollback();
        console.error(error.message);
    } catch(err) {
        console.error(err.message);
    }

    try {
        await DBClient.close();
    } catch(err) {
        console.error(err.message);
    }

    return;
}

try {
    await tableManager.commit();
} catch(err) {
    console.error(err.message);
}

try {
    await DBClient.close();
} catch(err) {
    console.error(err.message);
}