sqlite 如何在React Native中存储相同时间段的时间序列数据

xoshrz7s  于 2022-11-15  发布在  SQLite
关注(0)|答案(1)|浏览(164)

我的团队希望评估当人们使用arudino uno锻炼时的微电流，arudino uno是一款传感器和蓝牙通信到React的本地应用程序。我使用Sqlite作为数据库。
问题1.如何在SQLite数据库中一秒钟存储100000个数据？
问题2。如何每隔0.001s插入数据？我必须获得具有相同时间段的数据。它应该是时间序列数据。

这是我的1024个数据的时间索引。如你所见，每个指数的时间段都不同。
我想这是因为在SQLITE时间内插入了数据。现在，我一个接一个地插入数据，插入一个数据需要很长时间。
我用的是Arduino UNO 9600波特率。我猜数据每隔0.001s就会变成React原生数据，但React原生SQLite数据插入需要很长时间，所以索引之间的时间间隔不是0.001s。
如何插入具有相同时间段的数据？？我发现我必须一次插入1024个数据。但我必须找到如何制作它。这是正确的方式吗？？
我的数据库结构是

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'))

这是代码

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

问题
1.获取1024个数据需要1分钟。
1.它的时间周期并不都相等。
[“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”]

sqlite

1条答案

按热度按时间
yhxst69z

yhxst69z1#

您可以在react-native-sqlite中使用此命令添加多行，如下所示：

myDb.transaction((tx)=>{
  tx.executeSql(        
    'INSERT INTO ${muscleTableName} (musclePositionId,power) VALUES (?,?),(?,?),(?,?)',
    ['Up',2,'Down',2,'Up',3],
    (tx, results) => {               
      if (results.rowsAffected > 0 ) {
        console.log('Insert success');              
      } else {
        console.log('Insert failed');
      }
    }
  );
});

您可以查看此old answer以了解更多详细信息。

赞(0）分享  回复(0）举报  2022-11-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com