void ModelExportHelper::exportToDBMS(DatabaseModel *db_model, Connection conn, const QString &pgsql_ver, bool ignore_dup, bool drop_db, bool simulate)
{
int type_id;
QString version, sql_buf, sql_cmd, lin;
Connection new_db_conn;
unsigned i, count;
ObjectType types[]={OBJ_ROLE, OBJ_TABLESPACE};
BaseObject *object=nullptr;
vector<Exception> errors;
QTextStream ts;
bool ddl_tk_found=false;
/* Error codes treated in this method
42P04 duplicate_database
42723 duplicate_function
42P06 duplicate_schema
42P07 duplicate_table
42710 duplicate_object
42701 duplicate_column
42P16 invalid_table_definition
Reference:
http://www.postgresql.org/docs/current/static/errcodes-appendix.html*/
QString error_codes[]={"42P04", "42723", "42P06", "42P07", "42710", "42701", "42P16"};
vector<QString> err_codes_vect(error_codes, error_codes + sizeof(error_codes) / sizeof(QString));
try
{
if(!db_model)
throw Exception(ERR_ASG_NOT_ALOC_OBJECT,__PRETTY_FUNCTION__,__FILE__,__LINE__);
/* If the export is called using ignore duplications or drop database and simulation mode at same time
an error is raised because the simulate mode (mainly used as SQL validation) cannot
undo column addition (this can be changed in the future) */
if(simulate && (ignore_dup || drop_db))
throw Exception(ERR_MIX_INCOMP_EXPORT_OPTS,__PRETTY_FUNCTION__,__FILE__,__LINE__);
connect(db_model, SIGNAL(s_objectLoaded(int,QString,uint)), this, SLOT(updateProgress(int,QString,uint)));
export_canceled=false;
db_created=false;
progress=sql_gen_progress=0;
created_objs[OBJ_ROLE]=created_objs[OBJ_TABLESPACE]=-1;
conn.connect();
//Retrive the DBMS version in order to generate the correct code
version=(conn.getPgSQLVersion()).mid(0,3);
//Overriding the DBMS version case the version is informed on parameter
if(!pgsql_ver.isEmpty())
{
SchemaParser::setPgSQLVersion(pgsql_ver);
emit s_progressUpdated(progress, trUtf8("PostgreSQL version detection overrided. Using version %1...").arg(pgsql_ver));
}
else
{
SchemaParser::setPgSQLVersion(version);
emit s_progressUpdated(progress, trUtf8("PostgreSQL %1 server detected...").arg(version));
}
if(ignore_dup)
{
emit s_progressUpdated(progress, trUtf8("Ignoring object duplication errors..."));
//Save the current status for ALTER command generation for table columns/constraints
saveGenAtlerCmdsStatus(db_model);
}
if(drop_db)
{
emit s_progressUpdated(progress, trUtf8("Trying to drop database `%1'...").arg(db_model->getName()));
conn.executeDDLCommand(QString("DROP DATABASE IF EXISTS %1;").arg(db_model->getName(true)));
}
if(simulate)
emit s_progressUpdated(progress, trUtf8("Simulation mode activated..."));
//Creates the roles and tablespaces separately from the other objects
for(type_id=0; type_id < 2 && !export_canceled; type_id++)
{
count=db_model->getObjectCount(types[type_id]);
for(i=0; i < count && !export_canceled; i++)
{
object=db_model->getObject(i, types[type_id]);
progress=((10 * (type_id+1)) + ((i/static_cast<float>(count)) * 10));
try
{
if(!object->isSQLDisabled())
{
//Emits a signal indicating that the object is being exported
emit s_progressUpdated(progress,
trUtf8("Creating object `%1' (%2)...").arg(Utf8String::create(object->getName())).arg(object->getTypeName()),
object->getObjectType());
sql_cmd=object->getCodeDefinition(SchemaParser::SQL_DEFINITION);
conn.executeDDLCommand(sql_cmd);
}
}
catch(Exception &e)
{
/* Raises an error if the object is duplicated and the ignore duplicity is not set or the error
returned by the server is other than object duplicity */
if(!ignore_dup ||
(ignore_dup &&
std::find(err_codes_vect.begin(), err_codes_vect.end(), e.getExtraInfo())==err_codes_vect.end()))
throw Exception(e.getErrorMessage(),
e.getErrorType(),__PRETTY_FUNCTION__,__FILE__,__LINE__,&e, sql_cmd);
else
//If the object is duplicated store the error on a vector
errors.push_back(e);
}
created_objs[types[type_id]]++;
}
}
try
{
if(!db_model->isSQLDisabled() && !export_canceled)
{
//Creating the database on the DBMS
emit s_progressUpdated(progress,
trUtf8("Creating database `%1'...").arg(Utf8String::create(db_model->getName())),
OBJ_DATABASE);
sql_cmd=db_model->__getCodeDefinition(SchemaParser::SQL_DEFINITION);
conn.executeDDLCommand(sql_cmd);
db_created=true;
}
}
catch(Exception &e)
{
/* Raises an error if the object is duplicated and the ignore duplicity is not set or the error
returned by the server is other than object duplicity */
if(!ignore_dup ||
(ignore_dup &&
std::find(err_codes_vect.begin(), err_codes_vect.end(), e.getExtraInfo())==err_codes_vect.end()))
throw Exception(e.getErrorMessage(),
e.getErrorType(),__PRETTY_FUNCTION__,__FILE__,__LINE__,&e, sql_cmd);
else
errors.push_back(e);
}
if(!export_canceled)
{
//Connects to the new created database to create the other objects
progress=20;
new_db_conn=conn;
new_db_conn.setConnectionParam(Connection::PARAM_DB_NAME, db_model->getName());
emit s_progressUpdated(progress,
trUtf8("Connecting to database `%1'...").arg(Utf8String::create(db_model->getName())));
new_db_conn.connect();
progress=30;
//Creating the other object types
emit s_progressUpdated(progress, trUtf8("Creating objects on database `%1'...").arg(Utf8String::create(db_model->getName())));
//Generates the sql from entire model
sql_buf=db_model->getCodeDefinition(SchemaParser::SQL_DEFINITION, false);
/* Extract each SQL command from the buffer and execute them separately. This is done
to permit the user, in case of error, identify what object is wrongly configured. */
ts.setString(&sql_buf);
unsigned aux_prog=0, curr_size=0, buf_size=sql_buf.size();
progress+=(sql_gen_progress/progress);
sql_cmd.clear();
while(!ts.atEnd() && !export_canceled)
{
try
{
//Cleanup single line comments
lin=ts.readLine();
curr_size+=lin.size();
aux_prog=progress + ((curr_size/static_cast<float>(buf_size)) * 70);
ddl_tk_found=(lin.indexOf(ParsersAttributes::DDL_END_TOKEN) >= 0);
lin.remove(QRegExp("^(--)+(.)+$"));
//If the line isn't empty after cleanup it will be included on sql command
if(!lin.isEmpty())
sql_cmd += lin + "\n";
//If the ddl end token is found
if(ddl_tk_found || (!sql_cmd.isEmpty() && ts.atEnd()))
{
//Regexp used to extract the object being created
QRegExp reg=QRegExp("(CREATE)(.)+(\n)", Qt::CaseSensitive);
//sql_cmd = sql_cmd.simplified();
//Check if the regex matches the sql command
if(reg.exactMatch(sql_cmd))
{
QString obj_type, obj_name;
QRegExp reg_aux;
unsigned obj_id;
ObjectType obj_types[]={ OBJ_FUNCTION, OBJ_TRIGGER, OBJ_INDEX,
OBJ_RULE, OBJ_TABLE, OBJ_VIEW, OBJ_DOMAIN,
OBJ_SCHEMA, OBJ_AGGREGATE, OBJ_OPFAMILY,
OBJ_OPCLASS, OBJ_OPERATOR, OBJ_SEQUENCE,
OBJ_CONVERSION, OBJ_CAST, OBJ_LANGUAGE,
OBJ_COLLATION, OBJ_EXTENSION, OBJ_TYPE };
unsigned count=sizeof(obj_types)/sizeof(ObjectType);
int pos=0;
//Get the fisrt line of the sql command, that contains the CREATE ... statement
lin=sql_cmd.mid(0, sql_cmd.indexOf('\n'));
for(obj_id=0; obj_id < count; obj_id++)
{
//Check if the keyword for the current object exists on string
reg_aux.setPattern(QString("(CREATE)(.)*(%1)%2")
.arg(BaseObject::getSQLName(obj_types[obj_id]))
.arg(obj_types[obj_id]==OBJ_INDEX ? "( )*(CONCURRENTLY)?" : ""));
pos=reg_aux.indexIn(lin);
if(pos >= 0)
{
//Extracts from the line the string starting with the object's name
lin=lin.mid(reg_aux.matchedLength(), sql_cmd.indexOf('\n')).simplified();
//Stores the object type name
obj_type=BaseObject::getTypeName(obj_types[obj_id]);
if(obj_types[obj_id]!=OBJ_CAST)
{
//The object name is the first element when splitting the string with space separator
obj_name=lin.split(' ').at(0);
obj_name.remove('(');
}
else
{
obj_name="cast" + lin.replace(" AS ",",");
}
obj_name.remove(';');
break;
}
}
emit s_progressUpdated(aux_prog,
trUtf8("Creating object `%1' (%2)...").arg(obj_name).arg(obj_type),
obj_types[obj_id]);
}
else
//General commands like alter / set aren't explicitly shown
emit s_progressUpdated(aux_prog, trUtf8("Executing auxiliary command..."));
//Executes the extracted SQL command
if(!sql_cmd.isEmpty())
new_db_conn.executeDDLCommand(sql_cmd);
sql_cmd.clear();
ddl_tk_found=false;
}
}
catch(Exception &e)
{
if(ddl_tk_found) ddl_tk_found=false;
if(!ignore_dup ||
(ignore_dup &&
std::find(err_codes_vect.begin(), err_codes_vect.end(), e.getExtraInfo())==err_codes_vect.end()))
throw Exception(Exception::getErrorMessage(ERR_EXPORT_FAILURE).arg(Utf8String::create(sql_cmd)),
ERR_EXPORT_FAILURE,__PRETTY_FUNCTION__,__FILE__,__LINE__,&e, sql_cmd);
else
{
sql_cmd.clear();
errors.push_back(e);
sleepThread(10);
}
}
}
}
disconnect(db_model, nullptr, this, nullptr);
if(ignore_dup)
restoreGenAtlerCmdsStatus();
//Closes the new opened connection
if(new_db_conn.isStablished()) new_db_conn.close();
/* If the process was a simulation or even canceled undo the export
removing the created objects */
if(simulate || export_canceled)
undoDBMSExport(db_model, conn);
if(conn.isStablished())
conn.close();
if(!export_canceled)
emit s_exportFinished();
else
emit s_exportCanceled();
sleepThread(20);
}
catch(Exception &e)
{
disconnect(db_model, nullptr, this, nullptr);
if(ignore_dup)
restoreGenAtlerCmdsStatus();
try
{
//Closes the new opened connection
if(new_db_conn.isStablished()) new_db_conn.close();
//Undo the export removing the created objects
undoDBMSExport(db_model, conn);
}
catch(Exception &){}
if(conn.isStablished()) conn.close();
/* When running in a separated thread (other than the main application thread)
redirects the error in form of signal */
if(this->thread() && this->thread()!=qApp->thread())
{
errors.push_back(e);
emit s_exportAborted(Exception(e.getErrorMessage(), e.getErrorType(),__PRETTY_FUNCTION__,__FILE__,__LINE__, errors));
}
else
{
//Redirects any error to the user
if(errors.empty())
throw Exception(e.getErrorMessage(),e.getErrorType(),__PRETTY_FUNCTION__,__FILE__,__LINE__, &e);
else
{
errors.push_back(e);
throw Exception(e.getErrorMessage(),__PRETTY_FUNCTION__,__FILE__,__LINE__, errors);
}
}
}
}
void ModelExportHelper::exportToDBMS(DatabaseModel *db_model, DBConnection &conn, const QString &pgsql_ver, bool ignore_dup, bool simulate)
{
int type_id;
QString version, sql_buf, sql_cmd, lin;
DBConnection new_db_conn;
unsigned i, count;
ObjectType types[]={OBJ_ROLE, OBJ_TABLESPACE};
BaseObject *object=nullptr;
vector<Exception> errors;
QTextStream ts;
bool ddl_tk_found=false;
/* Error codes treated in this method
42P04 duplicate_database
42723 duplicate_function
42P06 duplicate_schema
42P07 duplicate_table
42710 duplicate_object
Reference:
http://www.postgresql.org/docs/current/static/errcodes-appendix.html*/
QString error_codes[]={"42P04", "42723", "42P06", "42P07", "42710"};
vector<QString> err_codes_vect(error_codes, error_codes + sizeof(error_codes) / sizeof(QString));
if(!db_model)
throw Exception(ERR_ASG_NOT_ALOC_OBJECT,__PRETTY_FUNCTION__,__FILE__,__LINE__);
connect(db_model, SIGNAL(s_objectLoaded(int,QString,uint)), this, SLOT(updateProgress(int,QString,uint)));
try
{
db_created=false;
progress=sql_gen_progress=0;
created_objs[OBJ_ROLE]=created_objs[OBJ_TABLESPACE]=-1;
conn.connect();
//Retrive the DBMS version in order to generate the correct code
version=(conn.getDBMSVersion()).mid(0,3);
//Overriding the DBMS version case the version is informed on parameter
if(!pgsql_ver.isEmpty())
{
SchemaParser::setPgSQLVersion(pgsql_ver);
emit s_progressUpdated(progress, trUtf8("PostgreSQL version detection overrided. Using version %1...").arg(pgsql_ver));
}
else
{
SchemaParser::setPgSQLVersion(version);
emit s_progressUpdated(progress, trUtf8("PostgreSQL %1 server detected...").arg(version));
}
if(ignore_dup)
emit s_progressUpdated(progress, trUtf8("Ignoring object duplication error..."));
//Creates the roles and tablespaces separately from the other objects
for(type_id=0; type_id < 2; type_id++)
{
count=db_model->getObjectCount(types[type_id]);
for(i=0; i < count; i++)
{
object=db_model->getObject(i, types[type_id]);
progress=((10 * (type_id+1)) + ((i/static_cast<float>(count)) * 10));
//Emits a signal indicating that the object is being exported
emit s_progressUpdated(progress,
trUtf8("Creating object `%1' (%2)...").arg(Utf8String::create(object->getName())).arg(object->getTypeName()));
try
{
if(!object->isSQLDisabled())
{
sql_cmd=object->getCodeDefinition(SchemaParser::SQL_DEFINITION);
conn.executeDDLCommand(sql_cmd);
}
}
catch(Exception &e)
{
/* Raises an error if the object is duplicated and the ignore duplicity is not set or the error
returned by the server is other than object duplicity */
if(!ignore_dup ||
(ignore_dup &&
std::find(err_codes_vect.begin(), err_codes_vect.end(), e.getExtraInfo())==err_codes_vect.end()))
throw Exception(e.getErrorMessage(),
e.getErrorType(),__PRETTY_FUNCTION__,__FILE__,__LINE__,&e, sql_cmd);
else
//If the object is duplicated store the error on a vector
errors.push_back(e);
}
created_objs[types[type_id]]++;
}
}
try
{
if(!db_model->isSQLDisabled())
{
//Creating the database on the DBMS
emit s_progressUpdated(progress, trUtf8("Creating database `%1'...").arg(Utf8String::create(db_model->getName())));
sql_cmd=db_model->__getCodeDefinition(SchemaParser::SQL_DEFINITION);
conn.executeDDLCommand(sql_cmd);
db_created=true;
}
}
catch(Exception &e)
{
/* Raises an error if the object is duplicated and the ignore duplicity is not set or the error
returned by the server is other than object duplicity */
if(!ignore_dup ||
(ignore_dup &&
std::find(err_codes_vect.begin(), err_codes_vect.end(), e.getExtraInfo())==err_codes_vect.end()))
throw Exception(e.getErrorMessage(),
e.getErrorType(),__PRETTY_FUNCTION__,__FILE__,__LINE__,&e, sql_cmd);
else
errors.push_back(e);
}
//Connects to the new created database to create the other objects
progress=30;
new_db_conn=conn;
new_db_conn.setConnectionParam(DBConnection::PARAM_DB_NAME, db_model->getName());
emit s_progressUpdated(progress, trUtf8("Connecting to database `%1'...").arg(Utf8String::create(db_model->getName())));
new_db_conn.connect();
progress=50;
//Creating the other object types
emit s_progressUpdated(progress, trUtf8("Creating objects on database `%1'...").arg(Utf8String::create(db_model->getName())));
//Generates the sql from entire model
sql_buf=db_model->getCodeDefinition(SchemaParser::SQL_DEFINITION, false);
/* Extract each SQL command from the buffer and execute them separately. This is done
to permit the user, in case of error, identify what object is wrongly configured. */
ts.setString(&sql_buf);
i=1;
progress+=(sql_gen_progress/progress);
sql_cmd.clear();
while(!ts.atEnd())
{
try
{
//Cleanup single line comments
lin=ts.readLine();
ddl_tk_found=(lin.indexOf(ParsersAttributes::DDL_END_TOKEN) >= 0);
lin.remove(QRegExp("^(--)+(.)+$"));
//If the line isn't empty after cleanup it will be included on sql command
if(!lin.isEmpty())
sql_cmd += lin + "\n";
//If the ddl end token is found
if(ddl_tk_found)
{
//Regexp used to extract the object being created
QRegExp reg=QRegExp("(CREATE)(.)+(\n)", Qt::CaseSensitive);
sql_cmd.simplified();
//Check if the regex matches the sql command
if(reg.exactMatch(sql_cmd))
{
QString obj_type, obj_name;
ObjectType obj_types[]={ OBJ_FUNCTION, OBJ_TRIGGER, OBJ_INDEX,
OBJ_RULE, OBJ_TABLE, OBJ_VIEW, OBJ_DOMAIN,
OBJ_SCHEMA, OBJ_AGGREGATE, OBJ_OPFAMILY,
OBJ_OPCLASS, OBJ_OPERATOR, OBJ_SEQUENCE,
OBJ_CONVERSION, OBJ_CAST, OBJ_LANGUAGE,
OBJ_COLLATION, OBJ_EXTENSION, OBJ_TYPE };
unsigned count=sizeof(obj_types)/sizeof(ObjectType);
int pos=0;
for(unsigned i=0; i < count; i++)
{
//Check if the keyword for the current object exists on string
pos=sql_cmd.indexOf(BaseObject::getSQLName(obj_types[i]));
if(pos >= 0)
{
//Extracts from the line the string starting with the object's name
lin=sql_cmd.mid(pos + BaseObject::getSQLName(obj_types[i]).size(),
sql_cmd.indexOf('\n')).simplified();
//Stores the object type name
obj_type=BaseObject::getTypeName(obj_types[i]);
//Special case of indexes, removes the "concurrently" keyword that cames after INDEX keyword
if(obj_types[i]==OBJ_INDEX)
lin.replace("CONCURRENTLY","");
//The object name is the first element when splitting the string with space separator
obj_name=lin.split(' ').at(0);
obj_name.remove('(');
obj_name.remove(';');
break;
}
}
emit s_progressUpdated(progress + (i/progress), trUtf8("Creating object `%1' (%2)...")
.arg(obj_name)
.arg(obj_type));
}
else
//General commands like alter / set aren't explicitly shown
emit s_progressUpdated(progress + (i/progress), trUtf8("Executing auxiliary command..."));
//Executes the extracted SQL command
if(!sql_cmd.isEmpty())
new_db_conn.executeDDLCommand(sql_cmd);
ddl_tk_found=false;
sql_cmd.clear();
i++;
}
}
catch(Exception &e)
{
if(!ignore_dup ||
(ignore_dup &&
std::find(err_codes_vect.begin(), err_codes_vect.end(), e.getExtraInfo())==err_codes_vect.end()))
throw Exception(Exception::getErrorMessage(ERR_EXPORT_FAILURE).arg(Utf8String::create(sql_cmd)),
ERR_EXPORT_FAILURE,__PRETTY_FUNCTION__,__FILE__,__LINE__,&e, sql_cmd);
else
errors.push_back(e);
}
}
disconnect(db_model, nullptr, this, nullptr);
//Closes the new opened connection
if(new_db_conn.isStablished()) new_db_conn.close();
/* If the process was a simulation undo the export
removing the created objects */
if(simulate)
undoDBMSExport(db_model, conn);
}
catch(Exception &e)
{
disconnect(db_model, nullptr, this, nullptr);
//Closes the new opened connection
if(new_db_conn.isStablished()) new_db_conn.close();
//Undo the export removing the created objects
undoDBMSExport(db_model, conn);
//Redirects any error to the user
if(errors.empty())
throw Exception(e.getErrorMessage(),e.getErrorType(),__PRETTY_FUNCTION__,__FILE__,__LINE__, &e);
else
{
errors.push_back(e);
throw Exception(e.getErrorMessage(),__PRETTY_FUNCTION__,__FILE__,__LINE__, errors);
}
}
}
