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

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]

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](https://sqlite.org/lang_attach.html)).

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](https://sqlite.org/cli.html) would be one such tool.

And, a not-so-new fact:

4. 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]

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]

>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]

Thank you Larry, David, and anonymous.  I started using SQLite about 3 weeks ago and I'm super glad to be here to learn.