//create an index
//@param indexName: name of index
//@param tableName: name of table
//@param attributeName : name of attribute in a table
void API::indexCreate(string indexName, string tableName, string attributeName)
{
if (cm->findIndex(indexName) == INDEX_FILE)
{
cout << "There is an index" << indexName << "already" << endl;
return;
}
if (!tableExist(tableName)) return;
vector<Attribute> attributeVector;
cm->attributeGet(tableName, &attributeVector);
int i;
int type = 0;
for (i = 0; i < attributeVector.size(); i++)
{
if (attributeName == attributeVector[i].name)
{
if (!attributeVector[i].ifUnique)
{
cout << "the attribute is not unique" << endl;
return;
}
type = attributeVector[i].type;
break;
}
}
if (i == attributeVector.size())
{
cout << "there is no this attribute in the table " << endl;
return;
}
//recordManager to create an index file
if (rm->indexCreate(indexName))
{
//CatalogManager to add an index informantion
cm->addIndex(indexName, tableName, attributeName, type);
//get type of index
int indexType = cm->getIndexType(indexName);
if (indexType == -2)
{
cout << "error";
return;
}
//indexManager to create an index tree
im->createIndex(rm->indexFileNameGet(indexName), indexType);
//RecordManager insert already record to index
rm->indexRecordAllAlreadyInsert(tableName, indexName);
printf("Create index %s successfully\n", indexName.c_str());
}
else
{
cout << "Create index" << indexName << "fail" << endl;
}
}
//get the size of a record in table
//@param tableName: name of table
int API::recordSizeGet(string tableName)
{
if (!tableExist(tableName))
return 0;
return cm->calcuteLenth(tableName);
}
//get the vector of an attribute's type in a table
//@param tableName: name of table
//@param attributeNameVector : a point to vector of attributeType(which would change)
int API::attributeGet(string tableName, vector<Attribute>* attributeVector)
{
if (!tableExist(tableName))
{
return 0;
}
return cm->attributeGet(tableName,attributeVector);
}
//get the vector of all name of index in the table
//@param tableName : name of table
//@param indexNameVector: a point to vector of indexName(which would change)
int API::indexNameListGet(string tableName, vector<string>* indexNameVecotor)
{
if (!tableExist(tableName))
{
return 0;
}
return cm->indexNameListGet(tableName, indexNameVecotor);
}
bool DBUtil::deleteTable(string name) {
if (tableExist(name)) {
string sql = "drop table "+name;
result = sqlite3_exec(pDB,sql.c_str(),NULL,NULL,&errMsg);
return result == SQLITE_OK;
}
return true;
}
bool DBUtil::createTable(string sql, string name) {
if (!tableExist(name)) {
result = sqlite3_exec(pDB,sql.c_str(),NULL,NULL,&errMsg);
return result == SQLITE_OK;
}
errMsg = "table already exist";
return false;
}
bool TeSQLite::getAttributeList(const string& tableName,TeAttributeList& attList)
{
if(!tableExist(tableName))
{
return false;
}
TeDatabasePortal* portal = this->getPortal();
if (!portal)
return false;
string sql = "PRAGMA table_info(" + tableName + ")";
if (!portal->query(sql))
{
delete portal;
return false;
}
while(portal->fetchRow())
{
TeAttribute attr;
attr.rep_.name_ = portal->getData("name");
attr.rep_.isPrimaryKey_ = portal->getBool("pk");
attr.rep_.null_ = !portal->getBool("notnull");
attr.rep_.defaultValue_ = portal->getData("dflt_value");
std::string type = portal->getData("type");
if(type == "TEXT")
{
attr.rep_.type_ = TeSTRING;
}
else if(type == "INTEGER")
{
attr.rep_.type_ = TeINT;
}
else if(type == "REAL")
{
attr.rep_.type_ = TeREAL;
}
else if(type == "BLOB")
{
attr.rep_.type_ = TeBLOB;
}
else
{
attr.rep_.type_ = TeSTRING;
}
attList.push_back(attr);
}
delete portal;
return true;
}
bool SQLiteManager::tableFieldExist(const std::string& tableName, const std::string& fieldName)
{
bool success = tableExist(tableName);
if(success)
{
std::vector<std::vector<std::string>> objects;
success = executeStatementGetArray("SELECT " + fieldName + " FROM " + tableName, objects);
}
return success;
}
//delete the record matching the conditions in the table
//@param tableName: name of table
//@param conditionVector: vector of condition
void API::recordDelete(string tableName, vector<Condition>* conditionVector)
{
if (!tableExist(tableName)) return;
int num = 0;
vector<Attribute> attributeVector;
attributeGet(tableName, &attributeVector);
int blockoffset = -1;
if (conditionVector != NULL)
{
for (Condition condition : *conditionVector)
{
if (condition.operate == Condition::OPERATOR_EQUAL)
{
for (Attribute attribute : attributeVector)
{
if (attribute.index != ""&& attribute.name == condition.attributeName)
{
blockoffset = im->searchIndex(rm->indexFileNameGet(attribute.index), condition.value, attribute.type);
}
}
}
}
}
if (blockoffset == -1)
{
//if we cannot find the block by index ,we need to find all block
num = rm->recordAllDelete(tableName, conditionVector);
}
else
{
//find the block by index, search in the block
num = rm->recordBlockDelete(tableName, conditionVector, blockoffset);
}
//delete the number of record in the table
cm->deleteValue(tableName, num);
printf("delete %d record in table %s\n", num, tableName.c_str());
}
mblDB::mblDB()
{
// 1. should open the db, but the db is opened
// 2. name the divec talbe
setTableName("mobileTable");
// 3. create the table
char sql[1024] ="";
if(!tableExist())
{
char* errMsg;
// creat the table
sprintf(sql, "CREATE TABLE %s ( key integer PRIMARY KEY, pkgID int);", getTableName().c_str());
int rc = sqlite3_exec(s_db, sql, NULL, NULL, &errMsg);
if( rc ){
fprintf(stderr, "Can't create table %s: %s\n", getTableName().c_str(), errMsg);
sqlite3_close(s_db);
exit(1);
}
}
}
//drop a table
//@param tableName : name of table
void API::tableDrop(string tableName)
{
if (!tableExist(tableName)) return;
vector<string> indexNameVector;
//get all index in the table, and drop them all
cm->indexNameListGet(tableName, &indexNameVector);
for (int i = 0; i < indexNameVector.size(); i++)
{
printf("%s ", indexNameVector[i].c_str());
indexDrop(indexNameVector[i]);
}
//delete a table file in RecrordManager
if (rm->tableDrop(tableName))
{
//delete table information in CatalogManager
cm->dropTable(tableName);
printf("Drop table %s successfully\n", tableName.c_str());
}
}
//insert a record to a table
//@param tableName:name of table
//@param recordContent : Vector of these content of a record
void API::recordInsert(string tableName, vector<string>* recordContent)
{
if (!tableExist(tableName))
return;
string indexName;
//deal if index is exist
vector<Attribute> attributeVector;
vector<Condition> conditionVector;
attributeGet(tableName, &attributeVector);
for (int i = 0; i < attributeVector.size(); i++)
{
indexName = attributeVector[i].indexNameGet();//get the index value
if (indexName != "")
{
//if the attribute has a index
int blockoffset = im->searchIndex(rm->indexFileNameGet(indexName), (*recordContent)[i], attributeVector[i].type);
if (blockoffset != -1)
{
//if the value has exist the index tree then fail to insert the record
cout << "insert fail because index value exist" << endl;
return;
}
}
else if (attributeVector[i].ifUnique)
{
//if the attribute if unique but not index
Condition condition(attributeVector[i].name, (*recordContent)[i], Condition::OPERATOR_EQUAL);
conditionVector.insert(conditionVector.end(), condition);
}
}
if (conditionVector.size() > 0)
{
for (int i = 0; i < conditionVector.size(); i++)
{
vector<Condition> conditionTmp;
conditionTmp.insert(conditionTmp.begin(), conditionVector[i]);
//寻找冲突的元组数目
int recordConflictNum = rm->recordAllFind(tableName, &conditionTmp);
if (recordConflictNum > 0)
{
cout << "insert fail because unique value exist" << endl;
return;
}
}
}
char recordString[2000];
memset(recordString, 0, sizeof(recordString));
//CatalogManager to get the record string
cm->recordStringGet(tableName, recordContent
, recordString);
//RecordManager to insert the record into file; and get the position of block being insert
int recordSize = cm->calcuteLenth(tableName);
int blockOffset = rm->recordInsert(tableName, recordString, recordSize);
if (blockOffset >= 0)
{
recordIndexInsert(recordString, recordSize, &attributeVector, blockOffset);
cm->insertRecord(tableName, 1);
printf("insert record into table %s successfully\n", tableName.c_str());
}
else
{
cout << "insert record into table " << tableName << "fail" << endl;
}
}
bool
TeSQLite::generateLabelPositions(TeTheme *theme, const std::string& objectId)
{
string geomTable, upd;
string collTable = theme->collectionTable();
if((collTable.empty()) || (!tableExist(collTable)))
return false;
if( theme->layer()->hasGeometry(TeCELLS) )
{
geomTable = theme->layer()->tableName(TeCELLS);
upd= "UPDATE " + collTable + " SET ";
upd += " label_x = (SELECT MAX(lower_x + (upper_x - lower_x)/2) ";
upd += "FROM " + geomTable + " WHERE object_id = c_object_id), ";
upd += " label_y = (SELECT MAX(lower_y + (upper_y - lower_y)/2) ";
upd += "FROM " + geomTable + " WHERE object_id = c_object_id) ";
upd += " WHERE label_x IS NULL OR label_y IS NULL";
}
if( theme->layer()->hasGeometry(TePOLYGONS) )
{
geomTable = theme->layer()->tableName(TePOLYGONS);
upd= "UPDATE " + collTable + " SET ";
upd += " label_x = (SELECT MAX(lower_x + (upper_x - lower_x)/2) ";
upd += "FROM " + geomTable + " WHERE object_id = c_object_id), ";
upd += " label_y = (SELECT MAX(lower_y + (upper_y - lower_y)/2) ";
upd += "FROM " + geomTable + " WHERE object_id = c_object_id) ";
upd += " WHERE label_x IS NULL OR label_y IS NULL";
}
if( theme->layer()->hasGeometry(TeLINES) )
{
geomTable = theme->layer()->tableName(TeLINES);
upd= "UPDATE " + collTable + " SET ";
upd += " label_x = (SELECT MAX(lower_x + (upper_x - lower_x)/2) ";
upd += "FROM " + geomTable + " WHERE object_id = c_object_id), ";
upd += " label_y = (SELECT MAX(lower_y + (upper_y - lower_y)/2) ";
upd += "FROM " + geomTable + " WHERE object_id = c_object_id) ";
upd += " WHERE label_x IS NULL OR label_y IS NULL";
}
if(theme->layer()->hasGeometry(TePOINTS))
{
geomTable = theme->layer()->tableName(TePOINTS);
upd= " UPDATE " + collTable + " SET ";
upd += " label_x = (SELECT MAX(x) ";
upd += " FROM " + geomTable + " p WHERE object_id = c_object_id), ";
upd += " label_y = (SELECT MAX(y) ";
upd += " FROM " + geomTable + " p WHERE object_id = c_object_id) ";
upd += " WHERE label_x IS NULL OR label_y IS NULL";
}
if (!upd.empty())
{
if (!objectId.empty())
{
upd += " AND c_object_id='"+objectId+"'";
}
if(!execute(upd))
return false;
}
return true;
}
