attaching is one of the first things documented, but both from playing around a bit and looking at the code, it seems like while attaching affects the underlying connection, there's no way to construct a Table object from an attached db+table name.
Because of this, the rest of the documentation doesn't appear to apply.
Concretely, my use case is that I'd like to be able to attach a read-only database but use sqlite-utils for the "easy mode" full text search that it builds in. What that would mean is creating my own database full of indexed search tables based on the data from attached tables, and then using the same rowid-based joins that sqlite-utils performs under the hood when actually calling table.search().
From my code review so far, it seems like in order to do this we would need to add an optional schema parameter to the Table objects, as well as a list of attached schema names, such that it would be possible to do the following:
db = Database('foo.db') # contains table `foot`
db.attach('bar', 'bar.db') # contains table `bart`
bart = db.table('bar.bart') # auto-parse the schema.table_name syntax the same way SQLite does?
# bart would support all operations supported on a table in the 'main' database.
# e.g.
bart.enable_fts(['name', 'age']) # would create the necessary tables - but inside the main database, since otherwise it would make more sense to have a separate connection to `bar` and perform the operation directly over there.
I could imagine that there might be some sharp edges here, since there are a few cross-database limitations in SQLite, and certain operations that Tables support might not make sense if they could be referenced in this way. For instance, the trigger operations for full-text-search (which don't apply to my use case but are documented capabilities of sqlite-utils) would be a little weird, because I believe they would have to be temp triggers which would expire as soon as the database was detached. It might also be a little tricky to decide exactly how to name the in-database FTS searches so that a separate process provided with both databases would be able to recognize that sqlite-utils had already created the necessary FTS tables - but this doesn't seem insurmountable to me.
If the maintainers think this would be feasible but don't have time to dedicate to building in the support, I'd be willing to get started on a PR.
attaching is one of the first things documented, but both from playing around a bit and looking at the code, it seems like while attaching affects the underlying connection, there's no way to construct a Table object from an attached db+table name.Because of this, the rest of the documentation doesn't appear to apply.
Concretely, my use case is that I'd like to be able to attach a read-only database but use
sqlite-utilsfor the "easy mode" full text search that it builds in. What that would mean is creating my own database full of indexed search tables based on the data from attached tables, and then using the same rowid-based joins thatsqlite-utilsperforms under the hood when actually callingtable.search().From my code review so far, it seems like in order to do this we would need to add an optional
schemaparameter to the Table objects, as well as a list of attached schema names, such that it would be possible to do the following:I could imagine that there might be some sharp edges here, since there are a few cross-database limitations in SQLite, and certain operations that Tables support might not make sense if they could be referenced in this way. For instance, the trigger operations for full-text-search (which don't apply to my use case but are documented capabilities of sqlite-utils) would be a little weird, because I believe they would have to be temp triggers which would expire as soon as the database was detached. It might also be a little tricky to decide exactly how to name the in-database FTS searches so that a separate process provided with both databases would be able to recognize that sqlite-utils had already created the necessary FTS tables - but this doesn't seem insurmountable to me.
If the maintainers think this would be feasible but don't have time to dedicate to building in the support, I'd be willing to get started on a PR.