void MTable::fieldFix( TConfig &cfg )
{
bool toUpdate = false,
appMode = cfg.reqKeys() || (cfg.incomplTblStruct() && !tblStrct.empty()), //Only for append no present fields
isVarTextTransl = (!Mess->lang2CodeBase().empty() && Mess->lang2Code() != Mess->lang2CodeBase());
//Get config fields list
vector<string> cf_el;
cfg.cfgList(cf_el);
//Create request variables
string all_flds, pr_keys, tpCfg, req,
crtReq = "CREATE TABLE IF NOT EXISTS '" + mod->sqlReqCode(name()) + "' (";
bool next = false, next_key = false;
//Curent context copy list
if(appMode) {
if(tblStrct.empty()) return;
for(unsigned i_fld = 1; i_fld < tblStrct.size(); i_fld++) {
all_flds += (all_flds.size()?",\"":"\"") + mod->sqlReqCode(tblStrct[i_fld][1],'"') + "\"";
crtReq += (next?",\"":"\"") + mod->sqlReqCode(tblStrct[i_fld][1],'"') + "\" "+
tblStrct[i_fld][2]+" DEFAULT " + tblStrct[i_fld][4] + " ";
next = true;
if(tblStrct[i_fld][5] == "1") {
pr_keys += (next_key?",\"":"\"") + mod->sqlReqCode(tblStrct[i_fld][1],'"') + "\"";
next_key = true;
}
}
}
//Check for need append or modify
for(unsigned i_cf = 0, i_fld; i_cf < cf_el.size(); i_cf++) {
TCfg &cf = cfg.cfg(cf_el[i_cf]);
// Check for update needs
for(i_fld = 1; i_fld < tblStrct.size(); i_fld++)
if(cf_el[i_cf] == tblStrct[i_fld][1]) {
if(appMode) break;
switch(cf.fld().type()) {
case TFld::String: if(tblStrct[i_fld][2] != "TEXT") toUpdate = true; break;
case TFld::Integer: case TFld::Boolean:
if(tblStrct[i_fld][2] != "INTEGER") toUpdate = true; break;
case TFld::Real: if(tblStrct[i_fld][2] != "DOUBLE") toUpdate = true; break;
default: toUpdate = true;
}
all_flds += (all_flds.size()?",\"":"\"") + mod->sqlReqCode(tblStrct[i_fld][1],'"') + "\"";
break;
}
// Type
switch(cf.fld().type()) {
case TFld::String: tpCfg = "TEXT DEFAULT '" + mod->sqlReqCode(cf.fld().def()) + "' "; break;
case TFld::Integer: case TFld::Boolean:
tpCfg = "INTEGER DEFAULT '" + mod->sqlReqCode(cf.fld().def()) + "' "; break;
case TFld::Real: tpCfg = "DOUBLE DEFAULT '" + mod->sqlReqCode(cf.fld().def()) + "' "; break;
default: break;
}
// Append
if(i_fld >= tblStrct.size() || !appMode) {
crtReq += (next?",\"":"\"") + mod->sqlReqCode(cf_el[i_cf],'"') + "\" " + tpCfg;
next = true;
if(i_fld >= tblStrct.size()) toUpdate = true;
}
// Other languages for translation process
if(cf.fld().flg()&TCfg::TransltText) {
bool col_cur = false;
for(unsigned i_c = 1; i_c < tblStrct.size(); i_c++)
if(tblStrct[i_c][1].size() > 3 && tblStrct[i_c][1].substr(2) == ("#"+cf_el[i_cf])) {
all_flds += ",\"" + mod->sqlReqCode(tblStrct[i_c][1],'"') + "\"";
crtReq += ",\"" + mod->sqlReqCode(tblStrct[i_c][1],'"') + "\" " + tpCfg;
if(tblStrct[i_c][1].compare(0,2,Mess->lang2Code()) == 0) col_cur = true;
}
if(!col_cur && isVarTextTransl) {
toUpdate = true;
crtReq += ",\"" + mod->sqlReqCode(Mess->lang2Code()+"#"+cf_el[i_cf],'"') + "\" " + tpCfg;
}
}
// Primary key
else if(cf.fld().flg()&TCfg::Key && !appMode) {
pr_keys += (next_key?",\"":"\"") + mod->sqlReqCode(cf_el[i_cf],'"') + "\"";
next_key = true;
}
}
//Check deleted fields
for(unsigned i_fld = 1, i_cf; i_fld < tblStrct.size() && !toUpdate && !appMode; i_fld++) {
for(i_cf = 0; i_cf < cf_el.size(); i_cf++)
if(cf_el[i_cf] == tblStrct[i_fld][1] ||
(cfg.cfg(cf_el[i_cf]).fld().flg()&TCfg::TransltText && tblStrct[i_fld][1].size() > 3 &&
tblStrct[i_fld][1].substr(2) == ("#"+cf_el[i_cf]) && tblStrct[i_fld][1].compare(0,2,Mess->lang2Code()) != 0))
break;
if(i_cf >= cf_el.size()) toUpdate = true;
}
if(!toUpdate) return;
//Copy need for save to temporary table
if(all_flds.size()) {
req = "CREATE TEMPORARY TABLE 'temp_" + mod->sqlReqCode(name()) + "'(" + all_flds + ");"
"INSERT INTO 'temp_" + mod->sqlReqCode(name()) + "' SELECT " + all_flds + " FROM '" + mod->sqlReqCode(name()) + "';"
"DROP TABLE '" + mod->sqlReqCode(name()) + "';";
owner().sqlReq(req, NULL, true);
}
//Create new, updated table
crtReq += ", PRIMARY KEY (" + pr_keys + "));";
owner().sqlReq(crtReq, NULL, true);
//Restore data from temporary table
if(all_flds.size()) {
req = "INSERT INTO '" + mod->sqlReqCode(name()) + "'(" + all_flds + ") SELECT " + all_flds +
" FROM 'temp_" + mod->sqlReqCode(name()) + "';DROP TABLE 'temp_" + mod->sqlReqCode(name()) + "';";
owner().sqlReq(req, NULL, true);
}
//Update the table structure
req = "PRAGMA table_info('" + mod->sqlReqCode(name()) + "');";
owner().sqlReq(req, &tblStrct, false);
}
void MTable::fieldSet( TConfig &cfg )
{
int i_ln, i_clm;
//Alloc resource
ResAlloc res(mRes, true);
//Get config fields list
vector<string> cf_el;
cfg.cfgList(cf_el);
//Check for write access
if(!(access(nTable.c_str(),F_OK|W_OK) == 0 || (access(nTable.c_str(),F_OK) != 0 && access(owner().addr().c_str(),W_OK) == 0)))
throw err_sys(_("Access to the file '%s' is read only."), nTable.c_str());
bool forceUpdt = cfg.reqKeys(),
appMode = forceUpdt || (cfg.incomplTblStruct() && !basa->isEmpty()); //Only for append no present fields
//Check and fix structure of table
for(unsigned i_cf = 0; i_cf < cf_el.size(); i_cf++) {
TCfg &e_cfg = cfg.cfg(cf_el[i_cf]);
// Find collumn
db_str_rec *fld_rec;
for(i_clm = 0;(fld_rec = basa->getField(i_clm)) != NULL;i_clm++)
if(cf_el[i_cf].compare(0,10,fld_rec->name) == 0) break;
if(fld_rec == NULL) {
// Create new collumn
db_str_rec n_rec;
fieldPrmSet(e_cfg, n_rec);
if(basa->addField(i_cf,&n_rec) < 0) throw err_sys(_("Error the column."));
}
else if(!appMode) {
// Check collumn parameters
switch(e_cfg.fld().type()) {
case TFld::String:
if(fld_rec->tip_fild == 'C' && e_cfg.fld().len() == fld_rec->len_fild) continue;
break;
case TFld::Integer:
if(fld_rec->tip_fild == 'N' && e_cfg.fld().len() == fld_rec->len_fild) continue;
break;
case TFld::Real:
if(fld_rec->tip_fild == 'N' && e_cfg.fld().len() == fld_rec->len_fild &&
e_cfg.fld().dec() == fld_rec->dec_field) continue;
break;
case TFld::Boolean:
if(fld_rec->tip_fild == 'L') continue;
break;
default: break;
}
db_str_rec n_rec;
fieldPrmSet(e_cfg, n_rec);
if(basa->setField(i_clm,&n_rec) < 0) throw err_sys(_("Error the column."));
}
}
//Del no used collumn
db_str_rec *fld_rec;
for(i_clm = 0; !appMode && (fld_rec=basa->getField(i_clm)) != NULL; i_clm++) {
unsigned i_cf;
for(i_cf = 0; i_cf < cf_el.size(); i_cf++)
if(cf_el[i_cf].compare(0,10,fld_rec->name) == 0) break;
if(i_cf >= cf_el.size() && basa->DelField(i_clm) < 0) throw err_sys(_("Error deleting the field."));
}
//Write to all records
//Get key line
bool isEnd = false;
for(i_ln = 0; !isEnd; i_ln++) {
if((i_ln=findKeyLine(cfg,0,false,i_ln)) < 0) {
if(forceUpdt) return;
i_ln = basa->CreateItems(-1);
}
//Write data to bd
for(unsigned i_cf = 0; i_cf < cf_el.size(); i_cf++) {
TCfg &e_cfg = cfg.cfg(cf_el[i_cf]);
if(!e_cfg.view()) continue;
// Find collumn
db_str_rec *fld_rec;
for(i_clm = 0; (fld_rec=basa->getField(i_clm)) != NULL; i_clm++)
if(cf_el[i_cf].compare(0,10,fld_rec->name) == 0) break;
if(fld_rec == NULL) continue;
// Set table volume
if(basa->ModifiFieldIt(i_ln,i_clm,getVal(e_cfg,fld_rec).c_str()) < 0) throw err_sys(_("Error the cell."));
}
if(!forceUpdt) isEnd = true;
}
mModify = SYS->sysTm();
}
void MTable::fieldSet( TConfig &cfg )
{
vector< vector<string> > tbl;
if(tblStrct.empty()) {
if(cfg.reqKeys()) return;
fieldFix(cfg);
}
mLstUse = SYS->sysTm();
string sid, sval;
bool isVarTextTransl = (!Mess->lang2CodeBase().empty() && Mess->lang2Code() != Mess->lang2CodeBase());
//Get config fields list
vector<string> cf_el;
cfg.cfgList(cf_el);
//Check for translation present
bool trPresent = isVarTextTransl, trDblDef = false;
for(unsigned i_fld = 1; i_fld < tblStrct.size(); i_fld++) {
if(trPresent && (!isVarTextTransl || trDblDef)) break;
sid = tblStrct[i_fld][1];
if(sid.size() > 3) {
if(!trPresent && !Mess->translDyn() && sid.compare(0,3,Mess->lang2Code()+"#") == 0) trPresent = true;
if(Mess->lang2Code() == Mess->lang2CodeBase() && !trDblDef && sid.compare(0,3,Mess->lang2CodeBase()+"#") == 0) trDblDef = true;
}
}
if(trDblDef && !cfg.reqKeys()) fieldFix(cfg);
//Get present fields list
string req_where = "WHERE ";
// Add key list to queue
bool next = false, noKeyFld = false,
isForceUpdt = cfg.reqKeys(); //Force update by ReqKeys or reqKey() present
for(unsigned i_el = 0; i_el < cf_el.size(); i_el++) {
TCfg &u_cfg = cfg.cfg(cf_el[i_el]);
if(!u_cfg.isKey()) continue;
req_where += (next?" AND \"":"\"") + mod->sqlReqCode(cf_el[i_el],'"') + "\"=" + getVal(u_cfg,true,TCfg::DblValTwo);
next = true;
if(!isForceUpdt && u_cfg.dblVal()) isForceUpdt = true;
// Check for no key fields
if(noKeyFld) continue;
unsigned i_fld = 1;
for( ; i_fld < tblStrct.size(); i_fld++)
if(u_cfg.name() == tblStrct[i_fld][1]) break;
if(i_fld >= tblStrct.size()) noKeyFld = true;
}
if(noKeyFld) {
if(cfg.reqKeys()) return;
fieldFix(cfg);
}
//Prepare query for presenting detect
string req;
if(!isForceUpdt) {
req = "SELECT 1 FROM '" + mod->sqlReqCode(name()) + "' " + req_where + ";";
owner().sqlReq(req, &tbl, true);
if(tbl.size() < 2) {
//Add new record
req = "INSERT INTO '" + mod->sqlReqCode(name()) + "' ";
string ins_name, ins_value;
next = false;
for(unsigned i_el = 0; i_el < cf_el.size(); i_el++) {
TCfg &u_cfg = cfg.cfg(cf_el[i_el]);
if(!u_cfg.isKey() && !u_cfg.view()) continue;
bool isTransl = (u_cfg.fld().flg()&TCfg::TransltText && trPresent && !u_cfg.noTransl());
ins_name += (next?",\"":"\"") + mod->sqlReqCode(cf_el[i_el],'"') + "\" " +
(isTransl ? (",\""+mod->sqlReqCode(Mess->lang2Code()+"#"+cf_el[i_el],'"')+"\" ") : "");
sval = getVal(u_cfg);
ins_value += (next?",":"") + sval + " " + (isTransl?(","+sval+" "):"");
next = true;
}
req += "(" + ins_name + ") VALUES (" + ins_value + ")";
}
else isForceUpdt = true;
}
//Update present record
if(isForceUpdt) {
req = "UPDATE '" + mod->sqlReqCode(name()) + "' SET ";
next = false;
for(unsigned i_el = 0; i_el < cf_el.size(); i_el++) {
TCfg &u_cfg = cfg.cfg(cf_el[i_el]);
if((u_cfg.isKey() && !u_cfg.dblVal()) || !u_cfg.view()) continue;
bool isTransl = (u_cfg.fld().flg()&TCfg::TransltText && trPresent && !u_cfg.noTransl());
sid = isTransl ? (Mess->lang2Code()+"#"+cf_el[i_el]) : cf_el[i_el];
req += (next?",\"":"\"") + mod->sqlReqCode(sid,'"') + "\"=" + getVal(u_cfg) + " ";
next = true;
}
req += req_where;
}
req += ";";
//Query
try { owner().sqlReq(req, NULL, true); }
catch(TError err) {
if((err.cod-100) == SQLITE_READONLY) throw;
fieldFix(cfg);
owner().sqlReq(req, NULL, true);
}
}
