包含线程，队列
//==============================================================================
// Sqlite数据存储工作类
//==============================================================================
unit uSqliteForTestData;
interface
uses
Classes, SysUtils, Windows //
, SQLiteTable3 //
, uQueue // umyqueue改为uQueue
, uSqlserver //数据库服务器操作
, uFNetwork //网络测试
,IniFiles //ini
,uGlobal //加密
, ActiveX, Forms; //
type
{测试记录}
PTYGTestRecord = ^TYGTestRecord;
TYGTestRecord = record
{User}
Name: string[50];
PassWord: string[50];
{YGTestData}
GUID: string[50];
YGXH: string[50];
YGTH: string[50];
YGBH: string[50];
YGDC: string[50];
CSDC: string[50];
CSYXZ: string[50];
CSYXY: string[50];
CSKDZ: string[50];
CSKDY: string[50];
CSBYZ: string[50];
CSBYY: string[50];
YGTestData: string[200];
YGFieldName: string[50]; //单个数据对应字段
YGSingleCH: string[50]; //保存单个数据
CSY: string[50];
JYY: string[50];
{Command}
Command: array[0..7] of Byte; //命令代码
RecordCount: Int64; //命令影响的行数
RunTime: Int64; //运行了多少次
RunTimed: Double; //执行一次耗时
QueueLegth: integer; //当前队列个数
end;
TOnChangeTSqliteForTestData = procedure(YGTestRecord: TYGTestRecord) of object;
TSqliteForTestData = class(TThread)
private
{ini数据库服务器参数}
v11, v12, v13, v14: string;
{计数器}
nRunTime: Int64;
{计时器}
Counter, Counter2, Freq: Int64;
nRunTimed: Double;
{队列}
nsfQueue: TsfQueue2;
{sqlite3数据库}
nslDBpath: string;
nSqlitedb, sldbBak: TSQLiteDatabase;
npYGTestRecord: PTYGTestRecord;
nTYGTestRecord: TYGTestRecord;
{事件变量}
FOnChangeTSqliteForTestData: TOnChangeTSqliteForTestData;
{方法}
procedure nCallTYGTestRecordOnChange;
protected
procedure Execute; override;
public
{触发SetEvent,ResetEvent,PulseEvent}
hEventForSqlite: THandle;
constructor Create;
destructor Destory;
function GetGuid: string;
function getSqliteVersion: string;
procedure ExecuteCommand(YGTestRecord: TYGTestRecord);
published
property OnChangeTSqliteForTestData:TOnChangeTSqliteForTestData //
read FOnChangeTSqliteForTestData //事件显式的属性声明
write FOnChangeTSqliteForTestData; //事件显式的属性声明
end;
implementation
uses
fMain;
{TSqliteForTestData}
function TSqliteForTestData.getSqliteVersion: string; //获得sqlite3.dll 的版本号
begin
Result := nSqlitedb.Version;
end;
constructor TSqliteForTestData.Create;
var
ini: TIniFile;
ss1, ss2: string;
ts: TStrings;
begin
ini := TIniFile.Create(ExtractFilePath(paramStr(0)) + 'dbconfig.ini');
ts := TStringList.Create;
ts.Text := DecString(PChar(ini.ReadString('制造基地', 'Figure', '')), 'PDFMS');
ini.Free;
if ts.Count <> 5 then
begin
ss2 := '数据服务配置无效...';
ts.Free;
Exit;
end
else
begin
v11 := ts.Strings[1];
v12 := ts.Strings[2];
v13 := ts.Strings[3];
v14 := ts.Strings[4];
ts.Free;
end;
{数据库文件地址}
nslDBpath := ExtractFilepath(application.exename) + 'ygcst.db';
if not (FileExists(nslDBpath)) then
begin
Application.MessageBox('本地数据库文件错误或者配置文件不存在！', 'Application.Title', MB_OK + MB_ICONSTOP);
exit;
end;
nSqlitedb := TSQLiteDatabase.Create(nslDBpath);
{运行次数}
nRunTime := 0;
{队列}
nsfQueue := TsfQueue2.Create(1024);
{初始化}
hEventForSqlite := CreateEvent(nil, False, False, nil);
inherited Create(True);
end;
destructor TSqliteForTestData.Destory;
begin
CloseHandle(hEventForSqlite);
nsfQueue.Free;
end;
procedure TSqliteForTestData.Execute;
var
sltb: TSQLiteTable;
str: string;
begin
inherited;
sltb := TSQLiteTable.Create;
repeat
if WaitForSingleObject(hEventForSqlite, INFINITE) = WAIT_OBJECT_0 then
begin
while nsfQueue.Count > 0 do
begin
{计时开始}
QueryPerformanceFrequency(Freq);
QueryPerformanceCounter(Counter);
{处理队列}
npYGTestRecord := nsfQueue.Pop;
npYGTestRecord.RecordCount := 0;
{1 登录验证=1}
if odd(npYGTestRecord.Command[0] shr 0) then
begin
try
sltb := nSqlitedb.GetTable('select user from ur where user= ''' + //
npYGTestRecord.Name + ''' and pswd = ''' + npYGTestRecord.PassWord + '''');
if sltb.Count > 0 then
begin
npYGTestRecord.RecordCount := sltb.Count;
end;
finally
sltb.Free;
//npYGTestRecord.Command[0] := npYGTestRecord.Command[0] and (not 1)
end;
end;
{2 检查输入的图号和编号数据内是否存在=2}
if odd(npYGTestRecord.Command[0] shr 1) then
begin
try
sltb := nSqlitedb.GetTable('select TUHAO,BIANHAO from info1 where TUHAO= ''' + //
npYGTestRecord.YGTH + ''' and BIANHAO = ''' + npYGTestRecord.YGBH + '''');
if sltb.Count > 0 then
begin
npYGTestRecord.RecordCount := sltb.Count;
end;
finally
sltb.Free;
//npYGTestRecord.Command[0] := npYGTestRecord.Command[0] and (not 2)
end;
end;
{3 写入最终测试数据=4}
if odd(npYGTestRecord.Command[0] shr 2) then
begin
try
nSqlitedb.BeginTransaction;
nSqlitedb.ExecSQL('update info1 set csFinish =''' + npYGTestRecord.YGTestData //
+ ''' , OverWorkTime=''' + DateTimeToStr(Now) //
+ ''' , csDongCheng=''' + npYGTestRecord.CSDC //
+ ''' , csYunXingZ =''' + npYGTestRecord.CSYXZ //
+ ''' , csYunXingY =''' + npYGTestRecord.CSYXY //
+ ''' , csKongDongZ =''' + npYGTestRecord.CSKDZ //
+ ''' , csKongDongY =''' + npYGTestRecord.CSKDY //
+ ''' , csShengLouZ =''' + npYGTestRecord.CSBYZ //
+ ''' , csShengLouY =''' + npYGTestRecord.CSBYY //
+ ''' , IsFinishTag =''1' //
+ ''' where TUHAO= ''' + npYGTestRecord.YGTH //
+ ''' and BIANHAO = ''' + npYGTestRecord.YGBH + '''');
//记录数
// try
sltb := nSqlitedb.GetTable('select count(*) from info1');
if sltb.Count > 0 then
begin
npYGTestRecord.RecordCount := StrToInt(sltb.Fields[0]) + 1000000;
end;
finally
sltb.Free;
end;
nSqlitedb.Commit;
//npYGTestRecord.Command[0] := npYGTestRecord.Command[0] and (not 4)
end;
{4 同步数据}
if odd(npYGTestRecord.Command[0] shr 3) then
begin
CoInitialize(nil);
//1 检查计算机是否联网
if ISInternetConnected then
begin
//2 数据库服务器网络是否正常
if PingHost(v11) then
begin
try
//3 查询本地未上传的数据
sltb := nSqlitedb.GetTable('Select * from info1 where (isupload is null or isupload=0) and (isFinishTag = 1)');
//sltb := nSqlitedb.GetTable('Select * from info1 where (isupload is null or isupload=0)');
npYGTestRecord.RecordCount := sltb.Count;
npYGTestRecord.RunTime := sltb.Count;
if sltb.Count > 0 then
begin
// nSqlitedb.BeginTransaction;
while not sltb.EOF do
begin
//4 查询服务器上是否已经存在数据
if DB_Query('select guid,isupload from YGCST_info1 where guid=''' //
+ sltb.FieldAsString(sltb.FieldIndex['GUID']) + '''') < 1 then //没有记录
begin
//5 服务器上没有此条数据，则上传数据
str := 'INSERT INTO YGCST_info1 (GUID,XINGHAO,TUHAO,BIANHAO,YGBiaoZhunDongCheng,csDongCheng,csYunXingZ,' //
+ 'csYunXingY,csKongDongZ,csKongDongY,csShengLouZ,csShengLouY,csFinish,isPAIQI1,isYUNXING7,isYUNXING3,' //
+ 'isSHENLOU,isKONGDONG,isPAIQI2,CSY,JYY,StartWorkTime,OverWorkTime,IsFinishTag,IsUpload) values (''' //
+ sltb.FieldAsString(sltb.FieldIndex['GUID']) + // 0
''',''' + sltb.FieldAsString(sltb.FieldIndex['XINGHAO']) + // 0
''',''' + sltb.FieldAsString(sltb.FieldIndex['TUHAO']) + // 0
''',''' + sltb.FieldAsString(sltb.FieldIndex['BIANHAO']) + //3
''',''' + sltb.FieldAsString(sltb.FieldIndex['YGBiaoZhunDongCheng']) + //4
''',''' + sltb.FieldAsString(sltb.FieldIndex['csDongCheng']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csYunXingZ']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csYunXingY']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csKongDongZ']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csKongDongY']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csShengLouZ']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csShengLouY']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csFinish']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isPAIQI1']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isYUNXING7']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isYUNXING3']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isSHENLOU']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isKONGDONG']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isPAIQI2']) + //
''',''' + Utf8ToAnsi(sltb.FieldAsString(sltb.FieldIndex['CSY'])) + //
''',''' + Utf8ToAnsi(sltb.FieldAsString(sltb.FieldIndex['JYY'])) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['StartWorkTime']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['OverWorkTime']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['IsFinishTag']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['IsUpload']) + ''');' + //DateTimeToStr(Now)
'select guid,isupload from YGCST_info1 where guid=''' + //
sltb.FieldAsString(sltb.FieldIndex['GUID']) + ''''; //
if DB_Query(str) > 0 then
npYGTestRecord.YGTestData := npYGTestRecord.YGTestData; //+ ' 1条OK!';
end;
//6 更新本地IsUpload字段为真1
nSqlitedb.ExecSQL('update info1 set IsUpload = 1 where guid= ''' //
+ sltb.FieldAsString(sltb.FieldIndex['GUID']) + '''');
// str := 'update info1 set IsUpload = 1 where guid= ''' + sltb.FieldAsString(sltb.FieldIndex['GUID']) + '''';
// nTYGTestRecord := npYGTestRecord^;
// npYGTestRecord.RunTime := npYGTestRecord.RunTime - 1;
// npYGTestRecord.YGTestData := ' @同步进行中!';
// Synchronize(nCallTYGTestRecordOnChange);
sltb.Next;
end;
// nSqlitedb.Commit;
end;
//7 是否还有未上传的
sltb := nSqlitedb.GetTable('Select count(*) from info1 where (isupload is null or isupload=0) and (isFinishTag = 1)');
npYGTestRecord.YGTestData :=sltb.Fields[0];
finally
sltb.Free;
end;
end
else
begin
//服务器没有联网
npYGTestRecord.YGTestData := '无法链接到数据库服务器!';
end;
end
else
begin
//本机没有联网
npYGTestRecord.YGTestData := '本机没有联网!';
end;
CoUninitialize;
//npYGTestRecord.Command[0] := npYGTestRecord.Command[0] and (not 8)
end;
{5 插入测试记录=16}
if odd(npYGTestRecord.Command[0] shr 4) then
begin
try
nSqlitedb.BeginTransaction;
nSqlitedb.ExecSQL('INSERT INTO info1 (GUID,XINGHAO,TUHAO,BIANHAO,YGBiaoZhunDongCheng,CSY,JYY,StartWorkTime) VALUES (''' //
+ npYGTestRecord.guid + //
''',''' + npYGTestRecord.YGXH + //
''',''' + npYGTestRecord.ygth + //
''',''' + npYGTestRecord.YGBH + //
''',''' + npYGTestRecord.YGDC + //
''',''' + npYGTestRecord.CSY + //
''',''' + npYGTestRecord.JYY + //
''',''' + DateTimeToStr(Now) + ''');');
//记录数
sltb := nSqlitedb.GetTable('select count(*) from info1');
if sltb.Count > 0 then
begin
npYGTestRecord.RecordCount := StrToInt(sltb.Fields[0]) + 1000000;
end;
finally
sltb.Free;
end;
nSqlitedb.Commit;
//npYGTestRecord.Command[0] := npYGTestRecord.Command[0] and (not 16)
end;
{6 插入测试数据=32 cs}
if odd(npYGTestRecord.Command[0] shr 5) then
begin
try
nSqlitedb.BeginTransaction;
nSqlitedb.ExecSQL('INSERT INTO info1 (GUID,XINGHAO,TUHAO,BIANHAO,YGBiaoZhunDongCheng,CSY,JYYStartWorkTime) VALUES (''' //
+ npYGTestRecord.guid + //
''',''' + npYGTestRecord.YGXH + //
''',''' + npYGTestRecord.ygth + //
''',''' + npYGTestRecord.YGBH + //
''',''' + npYGTestRecord.YGDC + //
''',''' + npYGTestRecord.CSY + //
''',''' + npYGTestRecord.JYY + //
''',''' + DateTimeToStr(Now) + ''');');
nSqlitedb.Commit;
finally
sltb.Free;
end;
nSqlitedb.Commit;
//npYGTestRecord.Command[0] := npYGTestRecord.Command[0] and (not 32)
end;
{7 空闲测试=64}
if odd(npYGTestRecord.Command[0] shr 6) then
begin
try
//select * from info1 where guid='4A8ADD329FAD4FE29921' and iskongdong is null
sltb := nSqlitedb.GetTable('select * from info1 where guid=''4A8ADD329FAD4FE29921''');
str := 'INSERT INTO info1 (GUID,XINGHAO,TUHAO,BIANHAO,YGBiaoZhunDongCheng,csDongCheng,csYunXingZ,' //
+ 'csYunXingY,csKongDongZ,csKongDongY,csShengLouZ,csShengLouY,csFinish,isPAIQI1,isYUNXING7,isYUNXING3,' //
+ 'isSHENLOU,isKONGDONG,isPAIQI2,CSY,JYY,StartWorkTime,OverWorkTime,IsUpload) values (''' //
+ sltb.FieldAsString(sltb.FieldIndex['GUID']) + // 0
''',''' + sltb.FieldAsString(sltb.FieldIndex['XINGHAO']) + // 0
''',''' + sltb.FieldAsString(sltb.FieldIndex['TUHAO']) + // 0
''',''' + sltb.FieldAsString(sltb.FieldIndex['BIANHAO']) + //3
''',''' + sltb.FieldAsString(sltb.FieldIndex['YGBiaoZhunDongCheng']) + //4
''',''' + sltb.FieldAsString(sltb.FieldIndex['csDongCheng']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csYunXingZ']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csYunXingY']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csKongDongZ']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csKongDongY']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csShengLouZ']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csShengLouY']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['csFinish']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isPAIQI1']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isYUNXING7']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isYUNXING3']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isSHENLOU']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isKONGDONG']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['isPAIQI2']) + //
''',''' + Utf8ToAnsi(sltb.FieldAsString(sltb.FieldIndex['CSY'])) + //
''',''' + Utf8ToAnsi(sltb.FieldAsString(sltb.FieldIndex['JYY'])) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['StartWorkTime']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['OverWorkTime']) + //
''',''' + sltb.FieldAsString(sltb.FieldIndex['IsUpload']) + ''');' + //DateTimeToStr(Now)
'select guid,isupload from info1 where guid=''' + //
sltb.FieldAsString(sltb.FieldIndex['GUID']) + ''''; //
nSqlitedb.ExecSQL(str);
finally
sltb.Free;
end;
//npYGTestRecord.Command[0] := npYGTestRecord.Command[0] and (not 64)
end;
{8 清理备份Sqlite数据库空间=128}
if odd(npYGTestRecord.Command[0] shr 7) then
begin
try
sldbBak := TSQLiteDatabase.Create(nslDBpath + 'testbak.db');
nSqlitedb.ExecSQL('VACUUM;'); //清理数据库空间,sqlite删除记录并不会清空空间。
if nSqlitedb.Backup(sldbBak) = 101 then //SQLITE_DONE
npYGTestRecord.YGTestData := npYGTestRecord.YGTestData + ' OK!'
else
npYGTestRecord.YGTestData := npYGTestRecord.YGTestData + ' failed';
finally
sldbBak.Free;
end;
//npYGTestRecord.Command[0] := npYGTestRecord.Command[0] and (not 128)
end;
{计时结束}
QueryPerformanceCounter(Counter2);
nRunTimed := (Counter2 - Counter) / Freq;
Inc(nRunTime); //运行次数
nTYGTestRecord := npYGTestRecord^;
nTYGTestRecord.QueueLegth := nsfQueue.Count;
nTYGTestRecord.RunTime := nRunTime;
nTYGTestRecord.RunTimed := nRunTimed;
Dispose(npYGTestRecord);
Synchronize(nCallTYGTestRecordOnChange);
if Terminated then
exit;
Sleep(1);
end;
end;
Sleep(1);
until (Terminated = true)
end;
procedure TSqliteForTestData.ExecuteCommand(YGTestRecord: TYGTestRecord);
var
pYGTestRecordTemp: pTYGTestRecord;
begin
New(pYGTestRecordTemp);
pYGTestRecordTemp^ := YGTestRecord;
nsfQueue.Push(pYGTestRecordTemp);
end;
procedure TSqliteForTestData.nCallTYGTestRecordOnChange;
begin
if Assigned(OnChangeTSqliteForTestData) then
FOnChangeTSqliteForTestData(nTYGTestRecord);
end;
function TSqliteForTestData.GetGuid: string; //需要USES Winapi.Activex
var
id: TGUID;
mID: string;
begin
if CoCreateGuid(id) = 0 then
mID := GUIDToString(id);
while True do
begin
if CoCreateGuid(id) = s_OK then
begin
mID := StringReplace(mID, '{', '', [rfReplaceAll]);
mID := StringReplace(mID, '}', '', [rfReplaceAll]);
mID := StringReplace(mID, '-', '', [rfReplaceAll]);
break;
end;
end;
Result := mID;
end;
{
private:
nRunTime: Int64; //nRunTime sqliteForTestData
YGslTestData: TSqliteForTestData; //YGslTestData sqliteForTestData
YGTestRecord: TYGTestRecord; //YGTestRecord sqliteForTestData
Create:
//================数据库表初始化=====================
nRunTime := 0;
YGslTestData := TSqliteForTestData.Create;
YGslTestData.OnChangeTSqliteForTestData := OnChangeTSqliteForTestData;
YGslTestData.Resume;
Destory:
//================析构释放=====================
if Assigned(YGslTestData) then
begin
YGslTestData.Terminate;
if YGslTestData.Suspended then
begin
YGslTestData.Resume;
end;
PulseEvent(YGslTestData.hEventForSqlite);
YGslTestData.WaitFor;
YGslTestData.Free;
end;
}
end.
370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
