My team want to evaluate micro-current when people work out from arudino uno,a senser and bluetooth communication to react-native app. I use sqlite as db.
Question1. How to store 100000 datas in a second in sqlite db?
Question2. How to insert data in every 0.001s? I have to get datas that have same time period. It should be time series data.
This is my time index of 1024 datas. As you see the time period of each index are all different.
I guess it is because of inserting a data in sqlite time. For now, I insert data one by one, Inserting One data takes so long.
I use Arduino UNO 9600 baud rate. I guess datas come to react-native in every 0.001s, but react-native sqlite data insertion takes so long.So The time period between index is not 0.001s.
How do I insert those datas with same time period?? I have found out that I have to insert 1024 datas at once. But I have to find how to make it. And It is right way??
my database structure is
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
musclePositionId INTEGER NOT NULL REFERENCES muscle_position(id),
power INTEGER NOT NULL,
created DATETIME NOT NULL DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
and this is code
import { View, Text, StyleSheet, TouchableOpacity ,Alert,Platform,PermissionsAndroid } from 'react-native'
import React, { FC, useLayoutEffect, useState,useEffect } from 'react'
import { SafeAreaView } from 'react-native-safe-area-context'
import { useRoute } from '@react-navigation/native';
import {RootRouteProps} from '../../App';
import { useNavigation } from '@react-navigation/native';
import { createMuscleTable, Muscle, muscleProp,db, muscleTableName,muscleActions,muscleSelector,musclePowerSelector, muscleTimeSelector } from '../redux/slices/muscleSlice';
import { useAppDispatch } from '../hook/hook';
import {BleManager} from 'react-native-ble-plx'
import base64 from 'react-native-base64';
import { useSelector } from 'react-redux';
import LineChart from '../components/LineChart';
const transactionId ="moniter";
const _BleManager = new BleManager();
const MuscleDetailScreen: FC = () => {
const route = useRoute<RootRouteProps<'MuscleDetail'>>();
const {positionName,musclePositionId} = route.params;
const navigation = useNavigation<any>();
const dispatch = useAppDispatch();
const [deviceid,setDeviceid] = useState('');
const [serviceUUID,setServiceUUID] = useState('');
const [characteristicsUUID,setCharacteristicsUUID] = useState('');
const [text1,setText1] = useState('');
const [makedata,setMakedata] = useState<any[]>([]);
const [notificationReceiving,setNotificationReceiving] = useState(false);
const [device, setDevice] = useState<any>(null);
const [phasor, setPhasor] = useState([]);
const muscle = useSelector(muscleSelector);
const musclePowerList = useSelector(musclePowerSelector);
const muscleTimeList = useSelector(muscleTimeSelector);
useEffect(()=>{
//console.log(musclePowerList);
if(musclePowerList.length > 0 ){
// console.log(musclePowerList.length);
if(musclePowerList.length == 1024){
var musclePower = musclePowerList.map(function(item) {
return parseFloat(item);
});
// console.log(musclePower);
var fft = require('fft-js').fft;
var phasors = fft(musclePower);
// console.log(phasors);
setPhasor(phasors);
// console.log(muscle);
console.log(muscleTimeList);
}
}
},[]);
const getAllMuscleList = (returnMessage: string | null) => {
try{
var tempResult : muscleProp[] = [];
var tempMessage : string = '';
db.transaction((tx) => {
tx.executeSql(
`SELECT strftime("%Y-%m-%d %H:%M:%f", created) as created,musclePositionId,power,id FROM ${muscleTableName} where musclePositionId = ${musclePositionId}`,
[],
(tx, results) => {
var temp: muscleProp[] = [];
// console.log('select muscle' + results.rows.length);
for (let i = 0; i < results.rows.length; i++){
temp.push(results.rows.item(i));
}
tempResult = temp;
tempMessage = 'success';
const data: Muscle = {position : tempResult, result : returnMessage? returnMessage : tempMessage} as Muscle;
dispatch(muscleActions.getAllMuscle(data));
}
,(error: any)=>{
console.log(error);
tempResult = [];
tempMessage = error;
const data: Muscle = {position : tempResult, result : tempMessage} as Muscle;
dispatch(muscleActions.getAllMuscle(data));
});
});
}catch(error:any){
const data: Muscle = {position : [], result : error} as Muscle;
dispatch(muscleActions.getAllMuscle(data));
}
}
const createMuscle = (musclePositionId: number,power:number)=>{
try{
var tempMessage:string = '';
db.transaction((tx) =>{
tx.executeSql(
`SELECT * FROM ${muscleTableName} where musclePositionId = ${musclePositionId}`,
[],
(tx, results) => {
// console.log('select muscle' + results.rows.length);
if (results.rows.length < 1024){
tx.executeSql(`INSERT INTO ${muscleTableName} (musclePositionId,power) VALUES (?,?)`,[musclePositionId,power],
(tx,results)=>{
// console.log('Results', results.rowsAffected);
if (results.rowsAffected > 0) {
// console.log('Data Inserted Successfully....');
tempMessage = 'Data Inserted Success';
getAllMuscleList(tempMessage);
} else {
console.log('Data Inserted Failed....');
tempMessage = 'Data Inserted Failed';
getAllMuscleList(tempMessage);
}
},(error: any) => {
console.log(error);
tempMessage = error;
getAllMuscleList(tempMessage);
});
}
}
)
})
}catch(error:any){
getAllMuscleList(error);
}
};
const deleteMuscleByPosition = (musclePositionId: number) =>{
try{
var tempMessage:string = '';
db.transaction((tx) =>{
tx.executeSql(`DELETE from ${muscleTableName} where musclePositionId = ${musclePositionId}`,[],
(tx,results)=>{
// console.log('Results', results.rowsAffected);
if (results.rowsAffected > 0) {
// console.log('Data deleted Successfully....');
tempMessage = 'Data deleted success';
getAllMuscleList(tempMessage);
} else {
console.log('Data deleted Failed....');
tempMessage = 'Data deleted success';
getAllMuscleList(tempMessage);
}
},(error: any) => {
tempMessage = error;
})
})
}catch(error:any){
getAllMuscleList(error);
}
};
useLayoutEffect(() => {
const _BleManager = new BleManager();
_BleManager.cancelTransaction(transactionId)
_BleManager.stopDeviceScan();
_BleManager.destroy();
// disconnect();
stopNotication();
}, []);
useEffect(()=>{
const a = async() => {
await createMuscleTable();
await getAllMuscleList(null);
}
a();
const _BleManager = new BleManager();
setDeviceid('');
setServiceUUID('');
setCharacteristicsUUID('');
setText1('');
setMakedata([]);
setNotificationReceiving(false);
if (Platform.OS === 'android' && Platform.Version >= 23) {
PermissionsAndroid.requestMultiple([PermissionsAndroid.PERMISSIONS.ACCESS_FINE_LOCATION,
PermissionsAndroid.PERMISSIONS.ACCESS_COARSE_LOCATION,
PermissionsAndroid.PERMISSIONS.ACCESS_BACKGROUND_LOCATION])
.then((result)=>{
if (result) {
console.log(" ACCESS_FINE_LOCATION Permission is OK");
console.log(" ACCESS_COARSE_LOCATION Permission is OK");
console.log(" ACCESS_BACKGROUND_LOCATION Permission is OK");
// this.retrieveConnected()
} else {
PermissionsAndroid.request(PermissionsAndroid.PERMISSIONS.ACCESS_FINE_LOCATION ).then((result) => {
if (result) {
console.log(" ACCESS_FINE_LOCATION accept");
} else {
console.log(" ACCESS_FINE_LOCATION refuse");
}
});
PermissionsAndroid.request(PermissionsAndroid.PERMISSIONS.ACCESS_COARSE_LOCATION ).then((result) => {
if (result) {
console.log(" ACCESS_COARSE_LOCATION accept");
} else {
console.log(" ACCESS_COARSE_LOCATION refuse");
}
});
PermissionsAndroid.request(PermissionsAndroid.PERMISSIONS.ACCESS_BACKGROUND_LOCATION ).then((result) => {
if (result) {
console.log(" ACCESS_BACKGROUND_LOCATION accept");
} else {
console.log(" ACCESS_BACKGROUND_LOCATION refuse");
}
});
}
});
}
},[]);
const getServicesAndCharacteristics = (device:any) => {
return new Promise((resolve, reject) => {
device.services().then((services: { characteristics: () => Promise<any>; }[]) => {
const characteristics: any[] = []
console.log("service : ",services)
services.forEach((service: { characteristics: () => Promise<any>; }, i: number) => {
service.characteristics().then(c => {
console.log("service.characteristics")
characteristics.push(c)
console.log(characteristics)
if (i === services.length - 1) {
const temp = characteristics.reduce(
(acc, current) => {
return [...acc, ...current]
},
[]
)
const dialog = temp.find(
(characteristic: { isWritableWithoutResponse: any; }) =>
characteristic.isWritableWithoutResponse
)
if (!dialog) {
reject('No writable characteristic')
}
resolve(dialog)
}
})
})
})
})
}
const stopNotication = () =>{
_BleManager.cancelTransaction(transactionId)
setNotificationReceiving(false)
}
const disconnect = () =>{
return new Promise((resolve, reject) => {
_BleManager.cancelDeviceConnection(deviceid).
then(rest=>{
console.log(rest);
setDeviceid('');
setServiceUUID('');
setCharacteristicsUUID('');
setText1('');
setMakedata([]);
setNotificationReceiving(false);
})
.catch((err)=>console.log("error on cancel connection",err))
})
}
const readData = async(device:any) => {
const services : any = await device.services();
console.log('read data device Service: ' +services);
services.forEach(async (service:any) => {
const characteristics = await device.characteristicsForService(service.uuid);
characteristics.forEach((result:any)=>{
if(result.isNotifiable === true){
console.log("characteristic foreach start")
console.log(result);
result.monitor((err: any, update: any) => {
if (err) {
console.log(`characteristic error: ${err}`);
console.log(JSON.stringify(err));
} else {
// console.log("Is Characteristics Readable:",update.isReadable);
// console.log(base64.decode(update.value));
// console.log("Heart Rate Data:",base64.decode(update.value));
// assuming the device is already connected
var data = parseInt(base64.decode(update.value));
console.log(data);
if(data != null && typeof data == "number"){
createMuscle(musclePositionId,data)
}
// consst heartRateData = Buffer.from(update.value, 'base64').readUInt16LE(0);
// console.log("Heart Beats:",heartRateData);
}
});
}
});
});
}
const scanAndConnect = async () =>{
setText1("Scanning...");
_BleManager.startDeviceScan(null, null, (error, device:any) => {
console.log("Scanning... start Device Scanning...");
if (null) {
console.log('null')
}
if (error) {
Alert.alert("Error in scan=> "+error)
console.log(error);
setText1("");
_BleManager.stopDeviceScan();
return
}
console.log(device.name)
if( /[HMSoft]/g.test( device.name ) )
{
if(device.name == 'HMSoft'){ //T3X1 //TAPP
const serviceUUIDs= device.serviceUUIDs[0]
setText1(`Connecting to ${device.name}`);
_BleManager.stopDeviceScan();
//listener for disconnection
/* this.manager.onDeviceDisconnected(device.id, (error, device) => {
console.log(error);
console.log("errordddd",device);
// if(this.props.device.isConnected) {
// this.scanAndConnect()
// }
});*/
_BleManager.connectToDevice(device.id, {autoConnect:true}).then((device) => {
(async () => {
const services = await device.discoverAllServicesAndCharacteristics()
const characteristic:any = await getServicesAndCharacteristics(services)
setDeviceid(device.id);
setServiceUUID(serviceUUIDs);
setCharacteristicsUUID(characteristic.uuid);
setDevice(device);
setText1(`Connected to ${device.name}`);
})();
setDevice(device);
return device.discoverAllServicesAndCharacteristics()
}).then((device) => {
// return this.setupNotifications(device)
}).then(() => {
console.log("Listening...")
}, (error) => {
Alert.alert("Connection error"+JSON.stringify(error))
})
}
}
});
}
return (
<SafeAreaView style={styles.container}>
<View style={{flex : 10}}>
<Text style={styles.positionNameStyle}> {positionName}</Text>
<Text style={styles.appName}>MuscleDetailScreen </Text>
<View>
{deviceid ?
(
<TouchableOpacity onPress={()=>disconnect()}>
<Text>테스트 종료하기</Text>
</TouchableOpacity>
) : (
null
)
}
{deviceid ?
(
<TouchableOpacity onPress={()=>readData(device)} style={{marginTop: 50}}>
<Text>데이터 읽기</Text>
</TouchableOpacity>
)
: (
null
)
}
<TouchableOpacity onPress={()=>deleteMuscleByPosition(musclePositionId)} style={
{
position:'absolute',
borderColor: '#000000',
borderWidth: StyleSheet.hairlineWidth,
borderRadius: 5,
alignSelf: 'flex-end',
}}>
<Text>Delete All Data</Text>
</TouchableOpacity>
</View>
<View style={{alignItems:'center',marginVertical : 10}}>
<Text>{text1}</Text>
{musclePowerList.length > 0 ?
<View style={{
height: 300,
padding: 20,
flexDirection: "row"
}}>
<View style={{flex: 1}}>
<LineChart
data={musclePowerList}
containerStyle={{ width: "100%", height: 250 }}
/>
</View>
</View>
:
null
}
</View>
</View>
<View style={{flex : 1, flexWrap:'wrap', flexDirection: 'row', justifyContent: 'space-between', alignItems: 'center', alignContent:'center'}}>
<TouchableOpacity onPress={()=>scanAndConnect()} style={styles.touchableOpacityButton}>
<Text style={styles.textStyle}>테스트 시작</Text>
</TouchableOpacity>
<TouchableOpacity onPress={()=>navigation.goBack()} style={styles.touchableOpacityButton}>
<Text style={styles.textStyle}>뒤로가기</Text>
</TouchableOpacity>
</View>
</SafeAreaView>
)
}
const styles = StyleSheet.create({
container : {
flex : 1,
},
positionNameStyle:{
margin: 15,
},
appName : {
alignSelf: 'center',
fontWeight: 'bold',
fontSize : 30
},
touchableOpacityButton:{
backgroundColor: '#68a0cf',
borderRadius: 10,
borderWidth: 1,
width: 100,
height : 100,
borderColor: '#fff',
alignItems: 'center',
justifyContent:'center',
textAlign: 'center',
flex : 1
},
textStyle:{
textAlign:'center',
fontWeight : 'bold'
}
});
export default MuscleDetailScreen
problem
- It takes 1 minute to get 1024 datas.
- Its time period isn't all equal..
["2022-09-08 13:04:58.832", "2022-09-08 13:04:58.835", "2022-09-08 13:04:58.899", "2022-09-08 13:04:58.901", ..., "2022-09-08 13:06:00.406", "2022-09-08 13:06:00.438", "2022-09-08 13:06:00.471", "2022-09-08 13:06:00.502", "2022-09-08 13:06:00.531", "2022-09-08 13:06:00.562", "2022-09-08 13:06:00.593", "2022-09-08 13:06:00.625"]