QString SqlDbBackend::expandDotsString( const QString& string )
{
QString className;
QString relationName;
QString expanded;
QStringList tables;
assert( string.contains( "." ) );
Tokenizer tokenizer( string, "." );
className = tokenizer.nextToken();
relationName = tokenizer.nextToken();
tables << className;
do {
if ( ! Classes::contains( className ) ) {
kdDebug() << k_funcinfo << "Class '" << className << "' not found." << endl;
return string;
}
ClassInfo *classInfo = Classes::classInfo( className );
if ( classInfo->containsObject( relationName ) ) {
RelationInfo *rel = classInfo->object( relationName );
if ( rel->isOneToOne() ) {
expanded += className + "." + relationName + "=";
expanded += rel->relatedClassInfo()->name() + "." + oidFieldName() + " AND ";
className=rel->relatedClassInfo()->name();
} else {
expanded += className + "." + relationName + "=";
expanded += rel->relatedClassInfo()->name() + "." + oidFieldName() + " AND ";
className=rel->relatedClassInfo()->name();
}
} else if ( classInfo->containsCollection( relationName ) ) {
CollectionInfo *rel = classInfo->collection( relationName );
if ( rel->isNToOne() ) {
expanded += className + "." + oidFieldName() + "=";
expanded += rel->childrenClassInfo()->name() + "." + relationName + " AND ";
className=rel->childrenClassInfo()->name();
} else {
expanded += className + "." + oidFieldName() + "=";
expanded += relationName + "." + className + " AND ";
expanded += relationName + "." + rel->childrenClassInfo()->name() + "=";
expanded += rel->childrenClassInfo()->name() + "." + oidFieldName() + " AND ";
className = rel->childrenClassInfo()->name();
}
} else {
kdDebug() << k_funcinfo << "Class '" << className << "' doesn't contain any relation named '" << relationName << "'" << endl;
expanded += className + "." + tokenizer.tail();
kdDebug() << k_funcinfo << "Expanded string: " << expanded << endl;
return expanded;
}
relationName = tokenizer.nextToken();
tables << className;
} while ( ! relationName.isNull() );
expanded += className + "." + tokenizer.tail() + " ";
kdDebug() << k_funcinfo << "Expanded string: " << expanded << endl;
kdDebug() << k_funcinfo << "Tables: " << tables << endl;
return expanded;
}
bool SqlDbBackend::createSchema()
{
QStringList tables;
QStringList constraints;
QString exec;
PropertyInfo *prop;
uint i;
// This sequence is incremented every time a new object is created
m_db->exec( "CREATE SEQUENCE seq_dboid;" );
// This sequence is incremented every time a record is created or modified and is used in the dbseq field that will be created in each table.
m_db->exec( "CREATE SEQUENCE seq_dbseq;" );
// Create the tables. Iterate creating the classes that
// have inheritance first.
QStringList classList( Classes::parentsFirst() );
QStringList::const_iterator it( classList.constBegin() );
QStringList::const_iterator end( classList.constEnd() );
ClassInfo *currentClass;
for ( ; it != end; ++it ) {
currentClass = Classes::classInfo( *it );
exec = "CREATE TABLE " + currentClass->name().lower() + " ( " + oidFieldName() + " BIGINT PRIMARY KEY, " + sequenceFieldName() + " BIGINT NOT NULL, ";
// Create properties fields
PropertiesInfoConstIterator pIt( currentClass->propertiesBegin() );
PropertiesInfoConstIterator pEnd( currentClass->propertiesEnd() );
for ( ; pIt != pEnd; ++pIt ) {
prop = *pIt;
if ( prop->readOnly() == false )
exec += prop->name() + " " + sqlType( prop ) + ", ";
}
// Create related objects fields
// For 1-1 relations only create the field in one of the two tables.
// We assume that both classes have relation to each other.
RelationInfosConstIterator oIt( currentClass->relationsBegin() );
RelationInfosConstIterator oEnd( currentClass->relationsEnd() );
RelationInfo *rObj;
for ( ; oIt != oEnd; ++oIt ) {
rObj = *oIt;
// needs to be >= to consider cases where the parent and related class(table) are the same
if ( ! rObj->isOneToOne() || rObj->relatedClassInfo()->name() >= rObj->parentClassInfo()->name() ) {
exec += rObj->name().lower() + " BIGINT DEFAULT NULL, ";
constraints << currentClass->name() + "-" + rObj->name() + "-" + rObj->relatedClassInfo()->name();
}
}
// Search in all the classes if they have N - 1 relations with the current class
ClassInfoIterator cIt( Classes::begin() );
ClassInfoIterator cEnd( Classes::end() );
ClassInfo *cInfo;
for ( ; cIt != cEnd; ++cIt ) {
cInfo = *cIt;
CollectionInfosIterator colIt( cInfo->collectionsBegin() );
CollectionInfosIterator colEnd( cInfo->collectionsEnd() );
CollectionInfo *rCol;
for ( ; colIt != colEnd; ++colIt ) {
rCol = *colIt;
if ( rCol->childrenClassInfo()->name() == currentClass->name() && rCol->isNToOne() && constraints.grep( rCol->name() ).count() == 0 ) {
exec += rCol->name().lower() + " BIGINT DEFAULT NULL, ";
constraints << currentClass->name() + "-" + rCol->name() + "-" + rCol->parentClassInfo()->name();
}
}
}
CollectionInfosIterator colIt( currentClass->collectionsBegin() );
CollectionInfosIterator colEnd( currentClass->collectionsEnd() );
CollectionInfo *col;
for ( ; colIt != colEnd; ++colIt ) {
col = *colIt;
if ( ! tables.grep( col->name() ).count() > 0 && ! col->isNToOne() ) {
tables << col->name() + "-" + filterFieldName( col ) + "-" + idFieldName( col );
}
}
// Take off the colon and space
exec = exec.left( exec.length() - 2 );
exec += ")";
if ( currentClass->parent() != 0 )
exec += " INHERITS ( " + currentClass->parent()->name() + ")";
m_db->exec( exec );
if ( m_db->lastError().type() != QSqlError::None ) {
kdDebug() << k_funcinfo << exec << endl;
kdDebug() << k_funcinfo << m_db->lastError().text() << endl;
}
}
/*
As PostgreSQL doesn't properly support foreign keys to inherited tables we will create
foreign keys (references) only when the refered class hasn't any inherited classes.
*/
// Create relation tables (for N-M relations)
QStringList list;
for ( i = 0; i < tables.count(); ++i ) {
list = QStringList::split( QString( "-" ), tables[ i ] );
exec = "CREATE TABLE " + list[ 0 ].lower() + " ( " + list[ 1 ].lower() + " BIGINT NOT NULL ";
if ( Classes::classInfo( list[ 1 ] )->children().count() == 0 )
exec += " REFERENCES " + list[ 1 ].lower() + " DEFERRABLE INITIALLY DEFERRED";
exec += ", " + list[ 2 ].lower() + " BIGINT NOT NULL ";
if ( Classes::classInfo( list[2] )->children().count() == 0 )
exec += " REFERENCES " + list[ 2 ].lower() + " DEFERRABLE INITIALLY DEFERRED";
exec += ", " + sequenceFieldName() + " BIGINT NOT NULL , PRIMARY KEY( " + list[1].lower() + " , " + list[2].lower() + " ) );";
m_db->exec( exec );
if ( m_db->lastError().type() != QSqlError::None ) {
kdDebug() << k_funcinfo << " -> " << exec << endl;
kdDebug() << k_funcinfo << " -> " << m_db->lastError().text() << endl;
}
}
// Create foreign keys in class tables
for ( i = 0; i < constraints.count(); ++i ) {
list = QStringList::split( QString( "-" ), constraints[ i ] );
// If the related class doesn't have children we can use a normal
// foreign key in PostgreSQL. Otherwise we have to use our own trigger.
kdDebug() << k_funcinfo << "'" << list[ 2 ] << "'" << endl;
if ( Classes::classInfo( list[ 2 ] )->children().count() == 0 )
exec = "ALTER TABLE " + list[ 0 ].lower() + " ADD FOREIGN KEY (" + list[ 1 ].lower() + ") REFERENCES " + list[ 2 ].lower() + "( " + oidFieldName() + " ) DEFERRABLE INITIALLY DEFERRED";
//else
// exec = "CREATE DDL!!!";
m_db->exec( exec );
if ( m_db->lastError().type() != QSqlError::None ) {
kdDebug() << k_funcinfo << " -> " << exec << endl;
kdDebug() << k_funcinfo << " -> " << m_db->lastError().text() << endl;
}
}
return true;
}
