int correctDist::addSeq(string seqName, string seqSeq){
try {
names.push_back(seqName);
sequences.push_back(fixSequence(seqSeq));
return 0;
}
catch(exception& e) {
m->errorOut(e, "correctDist", "addSeq");
exit(1);
}
}
void ImportF::updateSiteTable()
{
printf("ImportF::updateSiteTable()\n");
// set the new sequence values
fixSequence("site","id");
// add a new id column to the table
// execSQLCommand("ALTER TABLE importtable ADD COLUMN siterandid INTEGER;");
// insert into the table (changed)
execSQLCommand("INSERT INTO site(lastmodified,machineid,sitename,status,groundheight,location) SELECT DISTINCT sitelastmodified,sitemachineid,sitesitename,sitestatus,sitegroundheight,sitelocation FROM importtable WHERE sitesitename NOT IN (SELECT sitename FROM site);");
// update the site contacts
updateSiteContacts();
// update the site descriptions
updateSiteDescription();
}
int correctDist::getSequences(string sequenceFileName){
try {
ifstream sequenceFile;
m->openInputFile(sequenceFileName, sequenceFile);
string seqName, seqSeq;
while(!sequenceFile.eof()){
if (m->control_pressed) { break; }
Sequence temp(sequenceFile); m->gobble(sequenceFile);
if (temp.getName() != "") {
names.push_back(temp.getName());
sequences.push_back(fixSequence(temp.getAligned()));
}
}
sequenceFile.close();
return 0;
}
catch(exception& e) {
m->errorOut(e, "correctDist", "getSequences");
exit(1);
}
}
void ImportF::updateRadioInstallations()
{
printf("ImportF::updateRadioInstallations()\n");
// ensure that the sequence is correct
fixSequence("radioinstallation","id");
/* update old entries */
// create a table with all the radio installations that are not newer
dropTable("tmpupdateradioinstallations");
printf("1\n");
execSQLCommand("CREATE TABLE tmpupdateradioinstallations AS SELECT DISTINCT ON (radioinstallation.siteid,radioinstallationsector) radioinstallation.siteid,radioinstallation.lastmodified, radioinstallationlastmodified,radioinstallationsector, radioinstallationpermanent,radioinstallationtechkey,radioinstallationeirp,radioinstallationdiversity, radioinstallationtxpower,radioinstallationtxlosses,radioinstallationtxantennaheight,radioinstallationtxantpatternkey, radioinstallationtxbearing,radioinstallationtxmechtilt,radioinstallationrxsensitivity,radioinstallationrxlosses, radioinstallationrxantennaheight,radioinstallationrxantpatternkey,radioinstallationrxbearing,radioinstallationrxmechtilt, radioinstallationbtlplot,radioinstallationflaga,radioinstallationflagb,radioinstallationflagc,technologytechnologytype FROM importtable,site,radioinstallation WHERE (site.sitename = sitesitename AND radioinstallation.siteid=site.id AND radioinstallationlastmodified>radioinstallation.lastmodified AND radioinstallation.sector = radioinstallationsector); ");
// update all of the radioinstallations based on its sector and the site id
printf("2\n");
execSQLCommand("UPDATE radioinstallation SET lastmodified=radioinstallationlastmodified,sector=radioinstallationsector, permanent=radioinstallationpermanent,techkey=radioinstallationtechkey,eirp=radioinstallationeirp, diversity=radioinstallationdiversity,txpower=radioinstallationtxpower,txlosses=radioinstallationtxlosses, txantennaheight=radioinstallationtxantennaheight,txantpatternkey=radioinstallationtxantpatternkey,txbearing=radioinstallationtxbearing, txmechtilt=radioinstallationtxmechtilt,rxsensitivity=radioinstallationrxsensitivity,rxlosses=radioinstallationrxlosses, rxantennaheight=radioinstallationrxantennaheight,rxantpatternkey=radioinstallationrxantpatternkey,rxbearing=radioinstallationrxbearing, rxmechtilt=radioinstallationrxmechtilt,btlplot=radioinstallationbtlplot,flaga=radioinstallationflaga, flagb=radioinstallationflagb,flagc=radioinstallationflagc FROM tmpupdateradioinstallations WHERE (radioinstallation.siteid = tmpupdateradioinstallations.siteid AND radioinstallation.sector = radioinstallationsector); ");
/* add new entries */
// create a table with existing siteid and sector combinations (i.e existing radioinstallations)
printf("3\n");
dropTable("existri");
execSQLCommand("CREATE TABLE existri AS SELECT DISTINCT site.sitename, radioinstallation.sector FROM site, radioinstallation WHERE radioinstallation.siteid = site.id");
// create a table with all the radioinstallations that were created for sites (i.e. no existing radio installation in site)
printf("4\n");
dropTable("newinstallations");
dropTable("newinstallationsTMP");
execSQLCommand("CREATE TABLE newinstallationsTMP AS SELECT DISTINCT ON (sitesitename, radioinstallationsector) sitesitename,radioinstallationlastmodified,radioinstallationsector, radioinstallationpermanent,radioinstallationeirp,radioinstallationdiversity, radioinstallationtxpower,radioinstallationtxlosses,radioinstallationtxantennaheight,radioinstallationtxantpatternkey, radioinstallationtxbearing,radioinstallationtxmechtilt,radioinstallationrxsensitivity,radioinstallationrxlosses, radioinstallationrxantennaheight,radioinstallationrxantpatternkey,radioinstallationrxbearing,radioinstallationrxmechtilt, radioinstallationbtlplot,radioinstallationflaga,radioinstallationflagb,radioinstallationflagc,txpatternfile,rxpatternfile,technologytechnologytype FROM importtable WHERE (sitesitename NOT IN (SELECT sitename FROM existri)) OR (radioinstallationsector NOT IN (SELECT sector FROM existri WHERE sitename = sitesitename));");
// resolve the techkey
printf("4.1\n");
execSQLCommand("DROP TABLE IF EXISTS tmptechkey");
execSQLCommand("CREATE TABLE tmptechkey AS SELECT id, technologytype FROM technology;");
// merge the keys with the table
printf("4.4\n");
dropTable("newinstallationswithTech");
execSQLCommand("CREATE TABLE newinstallationswithTech AS SELECT * FROM newinstallationsTMP LEFT OUTER JOIN tmptechkey ON technologytype = technologytechnologytype;");
execSQLCommand("ALTER TABLE newinstallationswithTech DROP COLUMN technologytype");
execSQLCommand("ALTER TABLE newinstallationswithTech RENAME id TO radioinstallationtechkey");
// create the new installations table
execSQLCommand("CREATE TABLE newinstallations AS SELECT * FROM newinstallationswithTech");
// create a temporary table
printf("5\n");
dropTable("tmpantennapatterntable");
execSQLCommand("CREATE TABLE tmpantennapatterntable AS SELECT id, patternfile FROM antennapattern");
// create a table with all of the same columns as above, but remove the antennapatternfile column and fill in radioinstallationantennapatternkey
execSQLCommand("DROP TABLE IF EXISTS newinstallationswithantennaB");
execSQLCommand("DROP TABLE IF EXISTS newinstallationswithantennaA");
printf("6\n");
dropTable("newinstallationswithantennaA");
execSQLCommand("CREATE TABLE newinstallationswithantennaA AS SELECT * FROM newinstallations LEFT OUTER JOIN tmpantennapatterntable ON txpatternfile = tmpantennapatterntable.patternfile;");
execSQLCommand("ALTER TABLE newinstallationswithantennaA DROP COLUMN txpatternfile");
execSQLCommand("ALTER TABLE newinstallationswithantennaA DROP COLUMN patternfile");
execSQLCommand("ALTER TABLE newinstallationswithantennaA RENAME id TO txantpatternkey");
printf("6.5\n");
execSQLCommand("CREATE TABLE newinstallationswithantennaB AS SELECT * FROM newinstallationswithantennaA LEFT OUTER JOIN tmpantennapatterntable ON rxpatternfile = tmpantennapatterntable.patternfile;");
execSQLCommand("ALTER TABLE newinstallationswithantennaB DROP COLUMN rxpatternfile");
execSQLCommand("ALTER TABLE newinstallationswithantennaB RENAME id TO rxantpatternkey");
//ALTER TABLE tablef RENAME antennapatternpatternfile TO txpatternfile
#if 0
dropTable("newinstallationswithantenna");
execSQLCommand("CREATE TABLE newinstallationswithantenna AS SELECT * FROM newinstallations LEFT OUTER JOIN tmpantennapatterntable ON antennapatternpatternfile = tmpantennapatterntable.patternfile;");
printf("7\n");
execSQLCommand("ALTER TABLE newinstallationswithantenna DROP COLUMN patternfile");
printf("8\n");
execSQLCommand("ALTER TABLE newinstallationswithantenna RENAME COLUMN id TO newinstallationswithantennaid");
#endif
// add the new radio installations to the sites
printf("9\n");
dropTable("tmpsi");
execSQLCommand("CREATE TABLE tmpsi AS SELECT DISTINCT radioinstallationlastmodified,site.id,radioinstallationsector,radioinstallationpermanent, radioinstallationtechkey,radioinstallationeirp,radioinstallationdiversity,radioinstallationtxpower, radioinstallationtxlosses,radioinstallationtxantennaheight,txantpatternkey,rxantpatternkey,radioinstallationtxbearing, radioinstallationtxmechtilt,radioinstallationrxsensitivity,radioinstallationrxlosses,radioinstallationrxantennaheight, radioinstallationrxantpatternkey,radioinstallationrxbearing,radioinstallationrxmechtilt, radioinstallationbtlplot,radioinstallationflaga,radioinstallationflagb,radioinstallationflagc FROM newinstallationswithantennaB,site WHERE (sitesitename = sitename)");
printf("10\n");
execSQLCommand("ALTER TABLE tmpsi RENAME COLUMN id TO siteid");
// add a new id key to the table
/* execSQLCommand("ALTER TABLE tmpsi ADD COLUMN tid INTEGER;");
// set the keys to a random value
execSQLCommand("UPDATE tmpsi SET tid = RANDOM()*2E9;");*/
// insert into the database
execSQLCommand("INSERT INTO radioinstallation (lastmodified,siteid,sector, permanent,techkey,eirp, diversity,txpower,txlosses, txantennaheight,txantpatternkey,txbearing, txmechtilt,rxsensitivity,rxlosses, rxantennaheight,rxantpatternkey,rxbearing, rxmechtilt,btlplot,flaga, flagb,flagc) SELECT DISTINCT radioinstallationlastmodified,siteid,radioinstallationsector,radioinstallationpermanent, radioinstallationtechkey,radioinstallationeirp,radioinstallationdiversity,radioinstallationtxpower, radioinstallationtxlosses,radioinstallationtxantennaheight,txantpatternkey,radioinstallationtxbearing, radioinstallationtxmechtilt,radioinstallationrxsensitivity,radioinstallationrxlosses,radioinstallationrxantennaheight, rxantpatternkey,radioinstallationrxbearing,radioinstallationrxmechtilt, radioinstallationbtlplot,radioinstallationflaga,radioinstallationflagb,radioinstallationflagc FROM tmpsi ;");
printf("11\n");
/*
execSQLCommand("INSERT INTO radioinstallation (lastmodified,siteid,sector, permanent,techkey,eirp, diversity,txpower,txlosses, txantennaheight,txantpatternkey,txbearing, txmechtilt,rxsensitivity,rxlosses, rxantennaheight,rxantpatternkey,rxbearing, rxmechtilt,btlplot,flaga, flagb,flagc) SELECT DISTINCT radioinstallationlastmodified,siteid,radioinstallationsector,radioinstallationpermanent, radioinstallationtechkey,radioinstallationeirp,radioinstallationdiversity,radioinstallationtxpower, radioinstallationtxlosses,radioinstallationtxantennaheight,newinstallationswithantennaid,radioinstallationtxbearing, radioinstallationtxmechtilt,radioinstallationrxsensitivity,radioinstallationrxlosses,radioinstallationrxantennaheight, radioinstallationrxantpatternkey,radioinstallationrxbearing,radioinstallationrxmechtilt, radioinstallationbtlplot,radioinstallationflaga,radioinstallationflagb,radioinstallationflagc FROM tmpsi ;");*/
#if 0
execSQLCommand("INSERT INTO radioinstallation (lastmodified,siteid,sector, permanent,techkey,eirp, diversity,txpower,txlosses, txantennaheight,txantpatternkey,txbearing, txmechtilt,rxsensitivity,rxlosses, rxantennaheight,rxantpatternkey,rxbearing, rxmechtilt,btlplot,flaga, flagb,flagc) SELECT DISTINCT radioinstallationlastmodified,site.id,radioinstallationsector,radioinstallationpermanent, radioinstallationtechkey,radioinstallationeirp,radioinstallationdiversity,radioinstallationtxpower, radioinstallationtxlosses,radioinstallationtxantennaheight,newinstallationswithantennaid,radioinstallationtxbearing, radioinstallationtxmechtilt,radioinstallationrxsensitivity,radioinstallationrxlosses,radioinstallationrxantennaheight, radioinstallationrxantpatternkey,radioinstallationrxbearing,radioinstallationrxmechtilt, radioinstallationbtlplot,radioinstallationflaga,radioinstallationflagb,radioinstallationflagc FROM newinstallationswithantenna,site WHERE (sitesitename = sitename);");
#endif
}
