SQLite Forum

[Solved] OPEN, ATTACH, CREATE VIEW, SELECT - possible?
Login

[Solved] OPEN, ATTACH, CREATE VIEW, SELECT - possible?

(1.1) By MichaelS on 2021-04-24 18:17:32 edited from 1.0 [link] [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:

  1. When you use "ATTACH ... AS Something;", then "Something" is a "schema" name, not a table name. (See ATTACH doc).

  2. 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.

  3. 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:

  1. 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 [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.