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; }