Referenced tables not showing in table manager #3257
Assignees
Labels
area-manager
Related to the manager, exposing an easier to use interface
needs-info
More information is needed to act on the issue
Comments
Your example isn't self-contained, so I've used this modified versionimport 'package:drift/drift.dart';
import 'package:drift/native.dart';
part 'repro.g.dart';
class DynamicRelations extends Table {
IntColumn get id => integer()();
@ReferenceName('source')
IntColumn get sourcePropertyId =>
integer().references(DynamicTableProperties, #id)();
@ReferenceName('target')
IntColumn get targetPropertyId =>
integer().references(DynamicTableProperties, #id)();
@override
String get tableName => 'dynamic_database__dynamic_relations';
@override
Set<Column<Object>>? get primaryKey => {id};
}
class DynamicTableProperties extends Table {
IntColumn get id => integer()();
TextColumn get name => text()();
IntColumn get tableId => integer().references(DynamicTables, #id)();
@override
Set<Column<Object>>? get primaryKey => {id};
@override
List<Set<Column<Object>>>? get uniqueKeys => [
{name, tableId}
];
@override
String get tableName => 'dynamic_database__properties_names';
}
class DynamicTables extends Table {
IntColumn get id => integer()();
}
@DriftDatabase(tables: [
DynamicTables,
DynamicRelations,
DynamicTableProperties,
])
class Database extends _$Database {
Database() : super(NativeDatabase.memory());
@override
int get schemaVersion => 1;
Future<DynamicTableProperty> getRelation(int relationId) =>
managers.dynamicRelations
.withReferences()
.filter((f) => f.id.equals(relationId))
.getSingle()
.then((value) => value.$2.sourcePropertyId!.getSingle());
Future<DynamicTableProperty> getRelationWithPrefetch(int relationId) =>
managers.dynamicRelations
.withReferences((prefetch) => prefetch(sourcePropertyId: true))
.filter((f) => f.id.equals(relationId))
.getSingle()
.then((value) => value.$2.sourcePropertyId!.prefetchedData!.first);
}
void main() async {
final db = Database();
await db.dynamicTables.insertOne(DynamicTablesCompanion.insert(id: 1));
await db.dynamicTableProperties.insertOne(
DynamicTablePropertiesCompanion.insert(name: 'source', tableId: 1));
await db.dynamicTableProperties.insertOne(
DynamicTablePropertiesCompanion.insert(name: 'target', tableId: 1));
await db.dynamicRelations.insertOne(DynamicRelationsCompanion.insert(
sourcePropertyId: 1, targetPropertyId: 2));
print(await db.getRelation(1));
print(await db.getRelationWithPrefetch(1));
}I'm not sure what Future<DynamicTableProperty> getRelation(int relationId) =>
managers.dynamicRelations
.withReferences()
.filter((f) => f.id.equals(relationId))
.getSingle()
.then((value) => value.$2.sourcePropertyId!.getSingle());You can also use prefetching to add an initial join that avoids the second query: Future<DynamicTableProperty> getRelationWithPrefetch(int relationId) =>
managers.dynamicRelations
.withReferences((prefetch) => prefetch(sourcePropertyId: true))
.filter((f) => f.id.equals(relationId))
.getSingle()
.then((value) => value.$2.sourcePropertyId!.prefetchedData!.first);Is that the query you had in mind? |
|
That is what I had in mind. But something isn't right in my end :( What is available: more context: @DriftAccessor(tables: [
DynamicTables,
DynamicRelations,
DynamicTableProperties,
])
class DynamicDatabaseDao extends DatabaseAccessor<Database>
with $DynamicDatabaseDaoMixin {
DynamicDatabaseDao(super.attachedDatabase);
$$DynamicRelationsTableTableManager get relationsManager =>
attachedDatabase.managers.dynamicRelations;
Future<DynamicTableProperty> getRelation(Tsid relationId) =>
relationsManager
.withReferences()
.filter((f) => f.id.equals(relationId))
.getSingle().then((value) => value.$2./*none reference is available*/,);
}Database class: @DriftDatabase(tables: [
ArtifactCoordinates,
ArtifactReferences,
DynamicTables,
DynamicColumns,
Assets,
NavigationBars,
NavigationEntries,
Tags,
DynamicTableDataSources,
SchemaDataSources,
Screens,
ScreenContainers,
Elements,
DynamicTableForeignKeys,
ContainerObjects,
Frames,
Animations,
ExerciseContainers,
Fields,
PlayFields,
Projects,
FrameObjects,
ChangeLogs,
DynamicRelations,
TagsHierarchies,
TagsHierarchiesMapping,
MAProjects,
Annotations,
MATagsHierarchies,
VideoCutTagHierarchies,
VideoFiles,
VideoCuts,
VideoCutTags,
DynamicMultipleOptions,
DynamicMultipleOptionsMapping,
DynamicReferencesMapping,
Users,
FunctionalGroups,
FunctionalRoles,
UserToFunctionalRoles,
UserToFunctionalRoleToFunctionalGroups,
FunctionalGroupToFunctionalRoles,
ArtifactPermissions,
ModuleApplicationPermissions,
DynamicTableProperties,
], daos: [
DynamicDatabaseDao,
ReferencesManagerDao,
TagsDao,
AssetsManagerDao,
NavigationBarDao,
DataSourcesDao,
ScreensDao,
ExercisesEditorDao,
MatchAnalysisDao,
AuthenticationControllerDao,
PermissionsDao,
PersistenceDao,
], views: [
DynamicColumnsWithForeignKeys,
DynamicColumnsMultipleOptions,
DynamicColumnProperties,
DynamicRelationProperties,
])
abstract class Database extends $Database {
final String databaseName;
Database({
required this.databaseName,
required QueryExecutor executor,
}) : super(executor);
@override
int get schemaVersion => 1;
// TODO: Add MigrationStrategy when required;
// See https://github.com/simolus3/drift/blob/develop/examples/migrations_example/lib/database.dart
@override
MigrationStrategy get migration {
return MigrationStrategy(
beforeOpen: (details) async {
await customStatement('PRAGMA foreign_keys = ON;');
final tables = await customSelect("""
SELECT name
FROM sqlite_master
WHERE type='table';
""").get();
for (final table in tables) {
final tableName = table.read<String>("name");
if (tableName == changeLogsTableName) continue;
await registerLoggingTriggers(tableName);
}
},
);
}
} |
|
Maybe it is because my pks and fks use TypeConvertes instead of being literals (e.g. int, as in your snippet)? |
|
Oh right, good point! I think we disabled references for columns with type converters because there were some issues even if both columns were using the same converter. cc @dickermoshe was this due to concerns about |
|
Works for me. I think the versions are messed up |
This is a good point, I never considered this... |
dart pub deps |
|
I'm having trouble reproducing this. Create a new file named db.dartimport 'package:drift/drift.dart';
part 'db.g.dart';
enum RelationType {
oneToOne,
oneToMany,
manyToOne,
manyToMany,
}
class Tsid {
final BigInt value;
const Tsid(this.value);
}
class TsidTypeConverter extends TypeConverter<Tsid, BigInt> {
const TsidTypeConverter();
@override
Tsid fromSql(BigInt fromDb) {
return Tsid(fromDb);
}
@override
BigInt toSql(Tsid value) {
return value.value;
}
}
@DriftAccessor(tables: [
DynamicRelations,
DynamicTableProperties,
])
class DynamicDatabaseDao extends DatabaseAccessor<Database>
with _$DynamicDatabaseDaoMixin {
DynamicDatabaseDao(super.attachedDatabase);
$$DynamicRelationsTableTableManager get relationsManager =>
attachedDatabase.managers.dynamicRelations;
Future<DynamicTableProperty?> getRelation(Tsid relationId) => relationsManager
.withReferences()
.filter((f) => f.id.equals(relationId))
.getSingle()
.then((value) => value.$2.sourcePropertyId?.getSingle());
}
class DynamicRelations extends Table {
Int64Column get id => int64().map(const TsidTypeConverter())();
Int64Column get sourcePropertyId => int64()
.map(const TsidTypeConverter())
.references(DynamicTableProperties, #id)();
Int64Column get targetPropertyId => int64()
.map(const TsidTypeConverter())
.references(DynamicTableProperties, #id)();
DateTimeColumn get creationDate =>
dateTime().withDefault(currentDateAndTime)();
@override
String get tableName => 'dynamic_database__dynamic_relations';
@override
Set<Column<Object>>? get primaryKey => {id};
}
class DynamicTableProperties extends Table {
Int64Column get id => int64().map(const TsidTypeConverter())();
TextColumn get name => text()();
Int64Column get tableId => int64().map(const TsidTypeConverter())();
@override
Set<Column<Object>>? get primaryKey => {id};
@override
List<Set<Column<Object>>>? get uniqueKeys => [
{name, tableId}
];
@override
String get tableName => 'dynamic_database__properties_names';
}
@DriftDatabase(tables: [
DynamicRelations,
DynamicTableProperties,
], daos: [
DynamicDatabaseDao
], views: [])
abstract class Database extends _$Database {
final String databaseName;
Database({
required this.databaseName,
required QueryExecutor executor,
}) : super(executor);
@override
int get schemaVersion => 1;
} |
|
Works for me as well. @AlexandreAndrade00, can you try to reproduce this with a self-contained example that we could look at? |
|
Sorry for the delay 😬 As soon as I can I will try to reproduce this with a self-contained example. Thank you for your help :D |
dickermoshe
added
area-manager
Not a concern |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
In the documentation it shows that the referenced columns should be available when using
withReferences(), but in my case is not showing up.Tables:
Query:
The text was updated successfully, but these errors were encountered: