You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
334 lines
11 KiB
334 lines
11 KiB
/*
|
|
* Copyright (c) 2006-2021, RT-Thread Development Team
|
|
*
|
|
* SPDX-License-Identifier: Apache-2.0
|
|
*
|
|
* Change Logs:
|
|
* Date Author Notes
|
|
* 2025-10-20 Administrator the first version
|
|
*/
|
|
#include <rtthread.h>
|
|
#include <rtdevice.h>
|
|
#include <board.h>
|
|
#include <dfs_posix.h>
|
|
#include "DB_SQLite.h"
|
|
|
|
#include "sqlite3.h"
|
|
#include <dbhelper.h>
|
|
#define DB_NAME "/SC828.db"
|
|
|
|
int db_HelperInit;
|
|
sqlite3 **db;
|
|
|
|
static const char *sql_upgrade_workorder_steps =
|
|
// "PRAGMA foreign_keys = OFF; "
|
|
// "BEGIN TRANSACTION; "
|
|
// "CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM WorkorderSteps; "
|
|
// "DROP TABLE WorkorderSteps; "
|
|
"CREATE TABLE WorkorderSteps ("
|
|
"WorkOrder VARCHAR,"
|
|
"DYELOT VARCHAR,"
|
|
"ProgramID VARCHAR,"
|
|
"Program VARCHAR,"
|
|
"ReDye INT DEFAULT (0),"
|
|
"Mode VARCHAR,"
|
|
"Step INT,"
|
|
"StepID VARCHAR,"
|
|
"StepName VARCHAR,"
|
|
"ParameterName VARCHAR,"
|
|
"Parameter1 DOUBLE,"
|
|
"Parameter2 DOUBLE,"
|
|
"Parameter3 DOUBLE,"
|
|
"Parameter4 DOUBLE,"
|
|
"Parameter5 DOUBLE,"
|
|
"Parameter6 INT,"
|
|
"Parameter7 INT,"
|
|
"Parameter8 INT,"
|
|
"Parameter9 INT,"
|
|
"Parameter10 INT,"
|
|
"Remark VARCHAR,"
|
|
"StepTime INT,"
|
|
"StepID_S1 VARCHAR,"
|
|
"StepID_S2 VARCHAR,"
|
|
"StepID_S3 VARCHAR,"
|
|
"StepName_S1 VARCHAR,"
|
|
"StepName_S2 VARCHAR,"
|
|
"StepName_S3 VARCHAR,"
|
|
"Parameter1_S1 DOUBLE,"
|
|
"Parameter1_S2 DOUBLE,"
|
|
"Parameter1_S3 DOUBLE,"
|
|
"Parameter2_S1 DOUBLE,"
|
|
"Parameter2_S2 DOUBLE,"
|
|
"Parameter2_S3 DOUBLE,"
|
|
"Parameter3_S1 DOUBLE,"
|
|
"Parameter3_S2 DOUBLE,"
|
|
"Parameter3_S3 DOUBLE,"
|
|
"Parameter4_S1 DOUBLE,"
|
|
"Parameter4_S2 DOUBLE,"
|
|
"Parameter4_S3 DOUBLE,"
|
|
"Parameter5_S1 DOUBLE,"
|
|
"Parameter5_S2 DOUBLE,"
|
|
"Parameter5_S3 DOUBLE"
|
|
"); ";
|
|
|
|
static const char *sql_upgrade_workorder_set =
|
|
// "PRAGMA foreign_keys = OFF; "
|
|
// "BEGIN TRANSACTION; "
|
|
// "CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM WorkOrderSet; "
|
|
// "DROP TABLE WorkOrderSet; "
|
|
"CREATE TABLE WorkOrderSet ("
|
|
"WorkOrder VARCHAR,"
|
|
"ReDye INT DEFAULT (0),"
|
|
"PumpSpeed INT,"
|
|
"Blower INT,"
|
|
"Swing INT,"
|
|
"ClothWheel INT,"
|
|
"Nozzle INT"
|
|
"); ";
|
|
|
|
static const char *sql_upgrade_workorder =
|
|
// "PRAGMA foreign_keys = OFF; "
|
|
// "BEGIN TRANSACTION; "
|
|
// "CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM WorkOrder; "
|
|
//// "DROP TABLE WorkOrder; "
|
|
"CREATE TABLE WorkOrder ("
|
|
"WorkOrder VARCHAR,"
|
|
"Dyelot VARCHAR,"
|
|
"ReDye INT DEFAULT (0),"
|
|
"ProgramName VARCHAR,"
|
|
"StartTime DATETIME,"
|
|
"EndTime DATETIME,"
|
|
"Time TEXT,"
|
|
"lock INT,"
|
|
"State INT,"
|
|
"ProgramID VARCHAR,"
|
|
"Machines VARCHAR,"
|
|
"color VARCHAR,"
|
|
"ColorNumber VARCHAR,"
|
|
"Client VARCHAR,"
|
|
"ClothWeight VARCHAR,"
|
|
"ClothSpecies VARCHAR,"
|
|
"BathRatio VARCHAR,"
|
|
"Total VARCHAR,"
|
|
"USER VARCHAR,"
|
|
"ColorName VARCHAR,"
|
|
"Remark TEXT"
|
|
"); ";
|
|
|
|
static const char *sql_upgrade_run_table =
|
|
// "PRAGMA foreign_keys = OFF; "
|
|
// "BEGIN TRANSACTION; "
|
|
// "CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM RUN; "
|
|
// "DROP TABLE RUN; "
|
|
"CREATE TABLE RUN ("
|
|
"WorkOrder VARCHAR,"
|
|
"DYELOT VARCHAR,"
|
|
"ReDye INT DEFAULT (0),"
|
|
"RUN INT,"
|
|
"Mode VARCHAR,"
|
|
"ProgramID VARCHAR,"
|
|
"Program VARCHAR,"
|
|
"StepID VARCHAR,"
|
|
"Step INT,"
|
|
"StepName VARCHAR,"
|
|
"ParameterName VARCHAR,"
|
|
"Parameter1 DOUBLE,"
|
|
"Parameter2 DOUBLE,"
|
|
"Parameter3 DOUBLE,"
|
|
"Parameter4 DOUBLE,"
|
|
"Parameter5 DOUBLE,"
|
|
"Parameter6 INT,"
|
|
"Parameter7 INT,"
|
|
"Parameter8 INT,"
|
|
"Parameter9 INT,"
|
|
"Parameter10 INT,"
|
|
"Remark VARCHAR,"
|
|
"StepTime INT,"
|
|
"StepID_S1 VARCHAR,"
|
|
"StepID_S2 VARCHAR,"
|
|
"StepID_S3 VARCHAR,"
|
|
"StepName_S1 VARCHAR,"
|
|
"StepName_S2 VARCHAR,"
|
|
"StepName_S3 VARCHAR,"
|
|
"Parameter1_S1 DOUBLE,"
|
|
"Parameter1_S2 DOUBLE,"
|
|
"Parameter1_S3 DOUBLE,"
|
|
"Parameter2_S1 DOUBLE,"
|
|
"Parameter2_S2 DOUBLE,"
|
|
"Parameter2_S3 DOUBLE,"
|
|
"Parameter3_S1 DOUBLE,"
|
|
"Parameter3_S2 DOUBLE,"
|
|
"Parameter3_S3 DOUBLE,"
|
|
"Parameter4_S1 DOUBLE,"
|
|
"Parameter4_S2 DOUBLE,"
|
|
"Parameter4_S3 DOUBLE,"
|
|
"Parameter5_S1 DOUBLE,"
|
|
"Parameter5_S2 DOUBLE,"
|
|
"Parameter5_S3 DOUBLE"
|
|
");";
|
|
|
|
static const char *sql_upgrade_dyelot_table =
|
|
// "PRAGMA foreign_keys = OFF; "
|
|
// "BEGIN TRANSACTION; "
|
|
// "CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM Dyelot; "
|
|
// "DROP TABLE Dyelot; "
|
|
"CREATE TABLE Dyelot ("
|
|
"WorkOrder VARCHAR,"
|
|
"Dyelot VARCHAR,"
|
|
"ReDye INT,"
|
|
"Machine VARCHAR,"
|
|
"Step INT,"
|
|
"Tank INT,"
|
|
"State INT,"
|
|
"ProductCode VARCHAR,"
|
|
"ProductName VARCHAR,"
|
|
"ProductType INT,"
|
|
"Grams FLOAT,"
|
|
"Amount FLOAT,"
|
|
"CALL_TIME VARCHAR,"
|
|
"DispenseEndTime VARCHAR,"
|
|
"Type INT"
|
|
"); ";
|
|
|
|
static const char *sql_upgrade_iolog_table =
|
|
// "CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM IOLog; "
|
|
// "DROP TABLE IOLog; "
|
|
"CREATE TABLE IOLog ("
|
|
"ID varchar(32),"
|
|
"IOName varchar(32),"
|
|
"type varchar(32),"
|
|
"Value DOUBLE DEFAULT (0),"
|
|
"DIO BOOLEAN,"
|
|
"AIO INTEGER DEFAULT (0),"
|
|
"PLC varchar(32)"
|
|
"); ";
|
|
|
|
static const char *sql_upgrade_chart_table =
|
|
//"CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM Chart; "
|
|
// "DROP TABLE Chart; "
|
|
"CREATE TABLE Chart ("
|
|
"WorkOrder varchar(32),"
|
|
"DYELOT varchar(32),"
|
|
"ReDye INTEGER ,"
|
|
"Name varchar(32),"
|
|
"Time varchar(32),"
|
|
"MTT DOUBLE DEFAULT (0),"
|
|
"MTL DOUBLE DEFAULT (0),"
|
|
"STTA DOUBLE DEFAULT (0),"
|
|
"STLA DOUBLE DEFAULT (0),"
|
|
"STTB DOUBLE DEFAULT (0),"
|
|
"STLB DOUBLE DEFAULT (0),"
|
|
"STTC DOUBLE DEFAULT (0),"
|
|
"STLC DOUBLE DEFAULT (0),"
|
|
"MTH DOUBLE DEFAULT (0),"
|
|
"MST DOUBLE DEFAULT (0),"
|
|
"MSL DOUBLE DEFAULT (0),"
|
|
"MUT DOUBLE DEFAULT (0)"
|
|
"); ";
|
|
|
|
void db_sqlite(void *parameter)
|
|
{
|
|
if (access(DB_NAME, F_OK) == 0)
|
|
{
|
|
rt_kprintf("DB open\n");
|
|
if (db_connect(DB_NAME) == RT_EOK){
|
|
if(db_table_is_exist("WorkorderSteps")<=0){
|
|
if(db_create_database(sql_upgrade_workorder_steps)==0)
|
|
{rt_kprintf("WorkorderSteps Created successfully \n");}else{rt_kprintf("WorkorderSteps Creation failed \n");}
|
|
}
|
|
if(db_table_is_exist("WorkOrderSet")<=0){
|
|
if(db_create_database(sql_upgrade_workorder_set)==0)
|
|
{rt_kprintf("WorkOrderSet Created successfully \n");}else{rt_kprintf("WorkOrderSet Creation failed \n");}
|
|
}
|
|
if(db_table_is_exist("WorkOrder")<=0){
|
|
if(db_create_database(sql_upgrade_workorder)==0)
|
|
{rt_kprintf("WorkOrder Created successfully \n");}else{rt_kprintf("WorkOrder Creation failed \n");}
|
|
}
|
|
if(db_table_is_exist("RUN")<=0){
|
|
if(db_create_database(sql_upgrade_run_table)==0)
|
|
{rt_kprintf("RUN Created successfully \n");}else{rt_kprintf("RUN Creation failed \n");}
|
|
}
|
|
if(db_table_is_exist("Dyelot")<=0){
|
|
if(db_create_database(sql_upgrade_dyelot_table)==0)
|
|
{rt_kprintf("Dyelot Created successfully \n");}else{rt_kprintf("Dyelot Creation failed \n");}
|
|
}
|
|
if(db_table_is_exist("IOLog")<=0){
|
|
if(db_create_database(sql_upgrade_iolog_table)==0)
|
|
{rt_kprintf("IOLog Created successfully \n");}else{rt_kprintf("IOLog Creation failed \n");}
|
|
}
|
|
if(db_table_is_exist("Chart")<=0){
|
|
if(db_create_database(sql_upgrade_chart_table)==0)
|
|
{rt_kprintf("Chart Created successfully \n");}else{rt_kprintf("Chart Creation failed \n");}
|
|
}
|
|
|
|
const char *sql = "CREATE TABLE student(id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(32) NOT NULL,score INT NOT NULL);";
|
|
db_create_database(sql);
|
|
|
|
}else{
|
|
rt_kprintf("DB open failed \n");
|
|
}
|
|
|
|
}else{
|
|
rt_kprintf("DB open failed \n");
|
|
sqlite3_open(DB_NAME, db);
|
|
//创建表
|
|
db_create_database(sql_upgrade_workorder_steps);
|
|
db_create_database(sql_upgrade_workorder_set);
|
|
db_create_database(sql_upgrade_workorder);
|
|
db_create_database(sql_upgrade_run_table);
|
|
db_create_database(sql_upgrade_dyelot_table);
|
|
db_create_database(sql_upgrade_iolog_table);
|
|
db_create_database(sql_upgrade_chart_table);
|
|
}
|
|
|
|
|
|
// db_HelperInit = db_helper_init();
|
|
|
|
// if(db_HelperInit =RT_EOK){
|
|
// rt_kprintf("HelperInit database\n");
|
|
// }else {
|
|
// sqlite3_open(DB_NAME, db);
|
|
// db_ = db_create_database("CREATE TABLE student(id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(32) NOT NULL,score INT NOT NULL);");
|
|
// if(db_=0){rt_kprintf("database ok\n");}else{rt_kprintf("database no\n");}
|
|
// }
|
|
|
|
/* int fd = 0;
|
|
const char *dbname = db_get_name();
|
|
fd = open(dbname, O_RDONLY);
|
|
if (fd > 0)
|
|
rt_kprintf("%s exist\r\n", dbname);
|
|
else
|
|
rt_kprintf("%s not exist\r\n");
|
|
|
|
|
|
const char *sql = "CREATE TABLE IF NOT EXISTS student(id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(32) NOT NULL,score INT NOT NULL);";
|
|
rt_kprintf("sql cmd: %s\r\n\r\n", sql);
|
|
int ret = db_create_database(sql);
|
|
rt_kprintf("sql ret: %d\r\n", ret);*/
|
|
|
|
// sqlite3_os_init();
|
|
|
|
/* int rc = db_connect("/rt.db");
|
|
if (rc <0) {
|
|
const char *sql = "CREATE TABLE student(id INTEGER PRIMARY KEY AUTOINCREMENT,name varchar(32) NOT NULL,score INT NOT NULL);";
|
|
int db_create_database(sql);
|
|
}*/
|
|
}
|
|
|
|
/* 线程 */
|
|
void thread_DB_SQLite(void)
|
|
{
|
|
/* 初始化线程 1，名称是 thread1，入口是 thread1_entry*/
|
|
rt_thread_t tid;
|
|
tid = rt_thread_create("db_sqlite", db_sqlite, RT_NULL, 1024*64, 3, 15);
|
|
|
|
if (tid != RT_NULL)
|
|
{
|
|
rt_thread_startup(tid);
|
|
}
|
|
else
|
|
{
|
|
rt_kprintf("Failed to create sqlite_sys thread!\n");
|
|
}
|
|
// return 0;
|
|
}
|
|
|