[Solved] OPEN, ATTACH, CREATE VIEW, SELECT - possible?
(1.1) By MichaelS on 2021-04-24 18:17:32 edited from 1.0 [source]
Good Saturday morning.
I'm using PHP 7.3.27 and SQLite 3.26.0. I had a dream last night where I could open an SQLite database, then ATTACH another database, then create a view to the ATTACH'd database (essentially making a RO version), the issue a SELECT query. Here's the code:
$SqlHandle = new SQLite3($aDbWithNoData, SQLITE3_OPEN_READWRITE {pipe} SQLITE3_OPEN_CREATE);
$SqlStatement = "ATTACH DATABASE 'OtherDbFilename.sqlite3' AS LogFile;";
$SqlResult = $SqlHandle->exec($SqlStatement);
$SqlStatement = "CREATE VIEW IF NOT EXISTS Creates AS SELECT * FROM LogFile.Transactions WHERE MyColumnName LIKE 'CREATE%';";
$SqlResult = $SqlHandle->exec($SqlStatement);
$SqlStatement = "SELECT * FROM Creates;";
$SqlResult = $SqlHandle->query($SqlStatement);
while($SqlRows = $SqlResult->fetchArray(SQLITE3_ASSOC)) print_r($SqlRows);
$SqlHandle->close();
It gives me errors:
- SQLite3::exec(): view Creates cannot reference objects in database LogFile in
- SQLite3::query(): Unable to prepare statement: 1, no such table: Creates in
- Uncaught Error: Call to a member function fetchArray()
Obviously the 2nd and 3rd error is because error 1 happened.
I guess my question is can I create a view on an ATTACH'd database? If yes, where did I go wrong?
(2.1) By Larry Brasfield (larrybr) on 2021-04-24 16:02:41 edited from 2.0 in reply to 1.0 [link] [source]
Several new (to you) facts will help:
When you use "ATTACH ... AS Something;", then "Something" is a "schema" name, not a table name. (See ATTACH doc).
A view cannot be created in one database (or schema) which references objects in another database. This is because a view should remain valid whether other DBs are attached or not.
You would probably find it easier to develop your database table/view structure (aka "schema") using a different tool which does not disguise errors as well as PHP. The SQLite CLI shell would be one such tool.
And, a not-so-new fact:
- fetchArray() and PHP generally are not quite on-topic here. However, virtually anything happening with that CLI shell is.
(3) By David Jones (vman59) on 2021-04-24 17:56:50 in reply to 2.1 [link] [source]
And yet you can create a view referencing a virtual table which is only valid if the extension is loaded.
(4) By anonymous on 2021-04-24 18:03:50 in reply to 2.1 [link] [source]
A view cannot be created in one database (or schema) which references objects in another database. This is because a view should remain valid whether other DBs are attached or not.
I believe a temporary view, on the other hand, can. So, maybe this is the solution to the OP's problem.
(5) By MichaelS on 2021-04-24 18:16:46 in reply to 1.0 [link] [source]
Thank you Larry, David, and anonymous. I started using SQLite about 3 weeks ago and I'm super glad to be here to learn.