-module(syncDB_sqlite3). -compile(export_all). -include("esqltool.hrl"). test() -> lager:start(), io:format("start test ~n"), %% sqlite3 syncDb.db .dump>back`date +%Y-%m-%d_%T`.sql ok. test1() -> startNode('NodeName', ' ', 'DeviceId', 'ClusterId', 'ClusterName'), ok. test2() -> {Id, 'test33'} = person_info_feature_esql:create_personFeature([], 'test33'), io:format("test Id is ~p ", [Id]), person_info_feature_esql:add_personFea('test33', Id, yyy, url, id), ok. test3() -> test(), startNode('NodeName', ' ', 'DeviceId', 'ClusterId', 'ClusterName'), {Id, 'test33'} = person_info_feature_esql:create_personFeature([], 'test33'), sys_o_tables_esql:add_sys_table(Id, 'cluid', 'test33', "", 'person', 1, "2018-01-01 01:01:01", '2018-11-01 01:01:01'), io:format("test Id is ~p ", [Id]), person_info_feature_esql:add_personFea("", Id, "yyy", url, id), %startNode("NodeName", ' ', "DeviceId", "ClusterId", "ClusterName"), ok. setCNode(CNode) -> Ret = mochiglobal:put('cNodeName', CNode), case Ret of ok -> Res = [{atomic, ok}]; _ -> Res = [{aborted, notok}] end, Res. sendMessage() -> Nodes = [node() | nodes()], io:format("sendMessage ~p", [Nodes]), Res = syncTool:rpcSendMessToCNode(Nodes, {call, 'update', 1}), case Res of null -> io:format("send ok"), Ret = [{atomic, ok}]; _ -> io:format("send error"), Ret = [{aborted, notok}] end, Ret. add_deviceInfo(UUid, DevId, NodeId, ClusterID, FatherNodeName) -> if UUid == " " -> ID = syncTool:getUUIDString(); UUid == "" -> ID = syncTool:getUUIDString(); true -> ID = UUid end, UpdateTime = syncTool:getTimeStr(), CreateUser = node(), StrUuid = syncTool:change2Str(ID), StrNodeId = syncTool:change2Str(NodeId), StrDevId = syncTool:change2Str(DevId), StrClusterID = syncTool:change2Str(ClusterID), StrCreateUser = syncTool:change2Str(CreateUser), StrFatherNodeName = syncTool:change2Str(FatherNodeName), Sql = ["INSERT OR REPLACE INTO device_info (father_node, uuid, node_id, device_id, cluster_id, update_time, create_by) VALUES ('" ++ StrFatherNodeName ++ "', '" ++ StrUuid ++ "', '" ++ StrNodeId ++ "', '" ++ StrDevId ++ "', '" ++ StrClusterID ++ "', '" ++ UpdateTime ++ "', '" ++ StrCreateUser ++ "');"], Ret = executeSqlLocalyAndSaveToCacheAndSendSql(Sql), if Ret == {atomic, ok} -> Result = {atomic, ok}; true -> Result = {aborted, error} end, Result. downloadDeviceAndClustInfoDb() -> {ok, Binary} = file:read_file("/sdcard/security_database/sync_database.db"), Binary. downloadDeviceAndClustInfoDb(FatherNodeName) -> Binary = rpc:call(FatherNodeName, syncDB_sqlite3, downloadDeviceAndClustInfoDb, []), file:write_file("/sdcard/security_database/sync_database.db", Binary). startNode(NodeName, FatherNodeName, DeviceId, ClusterId, ClusterName) -> esqlite3Tool:createSqlCacheTable(), esqlite3Tool:createDeviceInfoAndClusterInfoTables(), if FatherNodeName == ' ' -> %%创建集群 device_info_esql:add_deviceInfo(DeviceId, DeviceId, NodeName, ClusterId, FatherNodeName), clusterInfo_esql:add_ClusterInfo(ClusterId, ClusterName), syncToolEsql:getAndExecuteOthNodeSqlCache(); true -> %%加入集群 %% downloadDeviceAndClustInfoDb(FatherNodeName), SqlFileContent = rpc:call(FatherNodeName, esqlite3Tool, readDbSql, []), %% 拷贝库到本地执行 esqlite3Tool:getDbSql(SqlFileContent), device_info_esql:add_deviceInfo(DeviceId, DeviceId, NodeName, ClusterId, FatherNodeName), clusterInfo_esql:add_ClusterInfo(ClusterId, ClusterName) end, %% StrUuid = syncTool:getUUIDString(), %% StrCreateUser = syncTool:change2Str(node()), %% StrTimestamp = syncTool:getTimestampStr(), %% Func = "test", %% InSql = ["INSERT INTO SqlCache (uuid, func, sql, create_by,create_time) values (\"", StrUuid, "\",\"", %% Func, "\" ,\"select count(*) from sqlite_master;\",\"", StrCreateUser, "\",\"", StrTimestamp, "\");"], %% spawn(node(), syncToolEsql, saveSqlToCache, [InSql]), Maps = device_info_esql:findAllDeviceList(), syncToolEsql:connectAllNode(Maps), %% esqltool:executeSqlLocalyAndSaveToCacheAndSendSql("INSERT OR REPLACE INTO cache (id) VALUES( '1')"), {atomic, ok}. modifyCluName(CluId, CluName) -> io:format("modifyCluName CluId is ~p,CluName is ~p~n", [CluId, CluName]), Tmp = clusterInfo_esql:update_ClusterInfo(CluId, CluName), case Tmp of {atomic, ok} -> RET = [{atomic, ok}]; _ -> RET = [{aborted, message}] end, io:format("modifyCluName Ret is ~p~n", [RET]), RET. removeNode() -> BerPid = mochiglobal:get(?ESQLDBKEY), case BerPid of undefined -> io:format("BerPid is ~p ", [BerPid]), Res = {error, false}; _ -> io:format("BerPid is ~p ", [BerPid]), %% os:cmd(["rm -f ", BerPid]), %% os:cmd(["rm -f ", database_name:getCacheDatabaseName()]), %% #todo rpc Res = {atomic, ok} end, Res. findAllNode() -> Res = device_info_esql:findDeviceOnlineList([node() | nodes()]), io:format("Res is ~p", [Res]), Res. %% 创建底库 %% 创建底库 表类型，表名，同步状态，黑白名单 createDatabase(TableType, TableName, SyncType, BwType, StartTime, EndTime) -> io:format("createDatabase ~p ~p ~p ~p ~p ~p ~n", [TableType, TableName, SyncType, BwType, StartTime, EndTime]), if TableType == 'person' -> %% Ret = person_info:create_personInfo(element(1, Tup), element(2, Tup)), %% Ret2 = person_info_feature:create_personFeature(element(1, Tup), element(2, Tup)), {Id, TableName} = person_info_feature_esql:create_personFeature(nodes(), TableName), person_info_esql:create_personInfo(nodes(), Id), %% todo Ret = sys_o_tables_esql:add_sys_table(Id, "", TableName, "", TableType, BwType, StartTime, EndTime), PerRet = [{atomic, ok}], io:format("Ret ~p ~n", [Ret]); TableType == 'car' -> PerRet = {}, todo; true -> PerRet = [{aborted, {'tableType_error', TableType}}], io:format("Error ~p ~n", [TableType]) end, io:format("PerRet ~p ~n", [PerRet]), PerRet. updateDatabase(Uuid, TableType, TableName, SyncType, BwType, StartTime, EndTime) -> io:format("createDatabase ~p ~p ~p ~p ~p ~p ~n", [TableType, TableName, SyncType, BwType, StartTime, EndTime]), [sys_o_tables_esql:update_sys_tableRec(Uuid, TableType, TableName, SyncType, BwType, StartTime, EndTime)]. deleteDatabase(TableType, TableName) -> io:format("deleteDatabase ~p ~p ~n", [TableType, TableName]), UuidTabName = sys_o_tables_esql:searchUuidWithTabName(TableName), [sys_o_tables_esql:del_sys_table(UuidTabName, TableType)]. findAllDatabase() -> sys_o_tables_esql:findAllSysTableInfo(). addPersonData(TableName, PersonName, Age, Sex, IdCard, PhoneNum, Img, UUID, Feature) -> UuidTabName = sys_o_tables_esql:searchUuidWithTabName(TableName), case UuidTabName of "" -> Ret = {aborted, {add_error, tableNotExits}}; _ -> %% 优先级? 先加人的信息还是人的特征 case UUID of "" -> PerNum = 0; _ -> Persons = person_info_feature_esql:findPersonFea(UuidTabName, UUID), PerNum = length(Persons) end, if PerNum =< 0 -> %% 无此id则添加 Ret = addPersonDataWithUuid(UuidTabName, syncTool:change2Str(PersonName), syncTool:change2Str(Age), syncTool:change2Str(Sex), syncTool:change2Str(IdCard), syncTool:change2Str(PhoneNum), syncTool:change2Str(Img) , syncTool:change2Str(UUID), syncTool:change2Str(Feature)); PerNum > 0 -> %% 有此id则进行更新 Ret = updatePersonDataWithUuid(UuidTabName, syncTool:change2Str(PersonName), syncTool:change2Str(Age), syncTool:change2Str(Sex), syncTool:change2Str(IdCard), syncTool:change2Str(PhoneNum), syncTool:change2Str(Img) , syncTool:change2Str(UUID), syncTool:change2Str(Feature)); true -> Ret = '' end end, Ret. addPersonDataWithUuid(UuidTabName, PersonName, Age, Sex, IdCard, PhoneNum, Img, UUID, Feature) -> if UUID == '' -> io:format("UUID is ~p ~n", [UUID]), ID = syncTool:getUUIDString(); true -> ID = UUID end, case UuidTabName of "" -> Ret = {aborted, {add_error, tableNotExits}}; _ -> %% 优先级? 先加人的信息还是人的特征 Uid = person_info_feature_esql:add_personFea(ID, UuidTabName, Feature, Img, IdCard), if Uid == '' -> Ret = {aborted, {add_error, Feature}}; true -> Ret = person_info_esql:add_personInfo(UuidTabName, Uid, PersonName, Age, Sex, IdCard, PhoneNum) end end, io:format("Ret ~p ~n", [Ret]), Ret. delPersonData(TableName, PersonId) -> io:format("TableName is ~p, PersonId is ~p", [TableName, PersonId]), UuidTabName = sys_o_tables_esql:searchUuidWithTabName(TableName), case UuidTabName of "" -> io:format("error tableNotExits ~p", [TableName]), Ret = {aborted, {del_error, tableNotExits}}; _ -> io:format("~p will delete data id is ~p", [UuidTabName, PersonId]), Ret = delPersonDataWithUuid(UuidTabName, PersonId) end, [Ret]. delPersonDataWithUuid(TableNameUuid, PersonId) -> DelRet = person_info_feature_esql:del_personFea(TableNameUuid, PersonId), if DelRet == {atomic, ok} -> Ret = person_info_esql:del_personInfo(TableNameUuid, PersonId); true -> Ret = {aborted, {del_error, {TableNameUuid, PersonId}}} end, io:format("Ret ~p ~n", [Ret]), Ret. updatePersonData(TableName, PersonName, Age, Sex, IdCard, PhoneNum, Img, UUID, Feature) -> UuidTabName = sys_o_tables_esql:searchUuidWithTabName(TableName), case UuidTabName of "" -> Ret = {aborted, {add_error, tableNotExits}}; _ -> Ret = updatePersonDataWithUuid(UuidTabName, syncTool:change2Str(PersonName), syncTool:change2Str(Age), syncTool:change2Str(Sex), syncTool:change2Str(IdCard), syncTool:change2Str(PhoneNum), syncTool:change2Str(Img), syncTool:change2Str(UUID), syncTool:change2Str(Feature)) end, Ret. updatePersonDataWithUuid(Tableuuid, PersonName, Age, Sex, IdCard, PhoneNum, Img, UUID, Feature) -> R1 = person_info_feature_esql:upd_personFea(UUID, Tableuuid, Feature, Img), case R1 of '' -> Res = ''; _ -> R2 = person_info_esql:upd_personInfo(Tableuuid, UUID, PersonName, Age, Sex, IdCard, PhoneNum), case R2 of '' -> Res = ''; _ -> Res = UUID end end, Res. loopAddPersonData(TableName, []) -> []; loopAddPersonData(TableName, [Value | T]) -> PersonId = element(1, Value), IdCard = element(2, Value), PersonPic = element(3, Value), PersonFea = element(4, Value), Persons = person_info_feature_esql:findPersonFea(TableName, PersonId), PerNum = length(Persons), if PerNum =< 0 -> %% 无此id则添加 Tmp = addPersonDataWithUuid(TableName, "", "", "", IdCard, "", PersonPic, PersonId, PersonFea), case Tmp of '' -> %% 添加失败 io:format("Tmp is null ~p", [Tmp]), Result = [{"PersonId", PersonId}, {"Result", "-1"}, {"msg", "error"}]; {aborted, Message} -> %% 添加失败 io:format("Tmp is aborted ~p", [Message]), Result = [{"PersonId", PersonId}, {"Result", "-1"}, {"msg", "error"}]; _ -> %% 添加成功 io:format("add ~p ok,~p", [PersonId, Tmp]), Result = [{"PersonId", PersonId}, {"Result", "0"}, {"msg", "sucess"}] end; PerNum > 0 -> %% 有此id则进行更新 Ret = updatePersonDataWithUuid(TableName, "", "", "", IdCard, "", PersonPic, PersonId, PersonFea), case Ret of '' -> %% 更新失败 Result = [{"PersonId", PersonId}, {"Result", "-1"}, {"msg", "error"}]; _ -> %% 更新成功 Result = [{"PersonId", PersonId}, {"Result", "1"}, {"msg", "sucess"}] end; true -> Result = [{"PersonId", PersonId}, {"Result", "-1"}, {"msg", "error"}] end, [Result | loopAddPersonData(TableName, T)]. addPersonDatas(TableId, PersonLists) -> %%todo io:format("TableId is ~p, PersonLists is ~p", [TableId, PersonLists]), TmpRes = sys_o_tables_esql:tableIsExist(TableId), case TmpRes of true -> io:format("loopAddPersonData"), Res = loopAddPersonData(TableId, PersonLists); false -> io:format("Table is not is Exist ~p", [TableId]), Res = [[{"Result", "0"}, {"msg", "notexist"}]]; error -> io:format("addPersonDatas is error ~p", [TableId]), Res = [[{"Result", "-1"}, {"msg", "error"}]] end, Res. loadFaceFeaData(TableName) -> UuidTabName = sys_o_tables_esql:searchUuidWithTabName(TableName), person_info_feature_esql:loadFaceFeaData(UuidTabName). loadFaceFeaData2(TableName) -> UuidTabName = sys_o_tables_esql:searchUuidWithTabName(TableName), person_info_esql:findAllpersonInfoNotFea(UuidTabName). %%todo searchSignalTabHavePerUid(PersonId, []) -> []; searchSignalTabHavePerUid(PersonId, [TN | T]) -> io:format("table rec is ~p ", [TN]), [TabUuidTup | Tmp] = TN, [CluTup | Tmp1] = Tmp, [TableNameTup | Tmp2] = Tmp1, TableUUid = syncTool:change2Str(element(2, TabUuidTup)), TableName = element(2, TableNameTup), Records = person_info_esql:findPersonInfo(TableUUid, PersonId), case Records of [] -> TemMaps = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "uuid"=>"null"}; _ -> [TRco | TRecords] = Records, [PerUid | PerTemp] = TRco, io:format("TRco is ~p ", [TRco]), TemMaps = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "uuid"=>element(2, PerUid)} end, [maps:to_list(TemMaps) | searchSignalTabHavePerUid(PersonId, T)]. %% 判断集群内某人是否存在 singlePersonIsExists(PersonId) -> TableLists = sys_o_tables_esql:findAllSysTableInfo(), Res = searchSignalTabHavePerUid(syncTool:change2Str(PersonId), TableLists), %% io:format("singlePersonIsExists Res is ~p~n", [Res]), Res. testFunc2(NewPerId, OldId, Idcard, PerPicUrl, PerFea, []) -> []; testFunc2(NewPerId, OldId, Idcard, PerPicUrl, PerFea, [TableRec | T]) -> io:format("table is ~p ~n", [TableRec]), [TabNameTup | Tmp] = TableRec, [CluTup | Tmp1] = Tmp, [TableNameTup | Tmp2] = Tmp1, TableUUid = syncTool:change2Str(element(2, TabNameTup)), TableName = syncTool:change2Str(element(2, TableNameTup)), OldRecords = person_info_esql:findPersonInfo(TableUUid, OldId), case OldRecords of [] -> %% old not have rec %% ResRetMaps = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"notexist", "oldId"=>OldId}, NewRecords = person_info_esql:findPersonInfo(TableUUid, NewPerId), case NewRecords of [] -> io:format("~p have no newId ~p ~n", [TableUUid, NewPerId]), %% new not have rec ResRetMaps1 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"notexist", "oldId"=>OldId}; _ -> %% new have rec io:format("~p have newId ~p ~n", [TableUUid, NewPerId]), %% del new And old ,Add new DelNewPerIdRet = delPersonDataWithUuid(TableUUid, NewPerId), case DelNewPerIdRet of [{atomic, ok}] -> %% add new AddNewRet = addPersonDataWithUuid(TableUUid, "", "", "", Idcard, "", PerPicUrl, NewPerId, PerFea), case AddNewRet of {aborted, Message} -> ResRetMaps112 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"false", "oldId"=>OldId}; '' -> ResRetMaps112 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"false", "oldId"=>OldId}; _ -> ResRetMaps112 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"true", "oldId"=>OldId} end, ResRetMaps11 = ResRetMaps112; _ -> ResRetMaps11 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"false", "oldId"=>OldId} end, ResRetMaps1 = ResRetMaps11 end, ResRetMaps = ResRetMaps1; _ -> %% old have rec io:format("~p have no OldId ~p ~n", [TableUUid, OldId]), %% search new NewRecords = person_info_esql:findPersonInfo(TableUUid, NewPerId), case NewRecords of [] -> %% new not have rec DelNewPerIdRet = [{atomic, ok}]; _ -> %% new have rec io:format("~p have no newId ~p ~n", [TableUUid, NewPerId]), %% del new And old ,Add new DelNewPerIdRet = delPersonDataWithUuid(TableUUid, NewPerId), io:format("~p ~p will delete oldId record~n", [TableUUid, OldId]) end, case DelNewPerIdRet of [{atomic, ok}] -> %% add new, del old AddNewRet = addPersonDataWithUuid(TableUUid, "", "", "", Idcard, "", PerPicUrl, NewPerId, PerFea), case AddNewRet of {aborted, Message} -> ResRetMaps21 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"false", "oldId"=>OldId}; '' -> ResRetMaps21 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"false", "oldId"=>OldId}; _ -> DelOldRet = delPersonDataWithUuid(TableUUid, OldId), io:format("~p ~p will delete oldId record,Ret is ~p~n", [TableUUid, OldId, DelOldRet]), case DelOldRet of {atomic, ok} -> ResRetMaps211 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"true", "oldId"=>OldId}; _ -> ResRetMaps211 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"false", "oldId"=>OldId} end, ResRetMaps21 = ResRetMaps211 end, ResRetMaps2 = ResRetMaps21; _ -> ResRetMaps2 = #{"tableName"=>TableName, "tableUuid"=>TableUUid, "result"=>"false", "oldId"=>OldId} end, ResRetMaps = ResRetMaps2 end, [maps:to_list(ResRetMaps) | testFunc2(NewPerId, OldId, Idcard, PerPicUrl, PerFea, T)]. singlePersonUpdate(NewPerId, OldId, Idcard, PerPicUrl, PerFea) -> io:format("NewPerId is ~p, OldId is ~p, Idcard is ~p, PerPicUrl is ~p, PerFea is ~p,~n", [NewPerId, OldId, Idcard, PerPicUrl, PerFea]), TableLists = sys_o_tables_esql:findAllSysTableInfo(), testFunc2(syncTool:change2Str(NewPerId), syncTool:change2Str(OldId), syncTool:change2Str(Idcard), syncTool:change2Str(PerPicUrl), syncTool:change2Str(PerFea), TableLists).