Idea: Explore using SQL views and/or virtual columns to handle the language vs. langcode column name in the db

[klonos]

Disclaimer: This is the result of some online research, as I was trying to find a potential solution for #5496. I'm not sure that it would work, but worth investigating/trying I think.

---

Here's an idea: I have been doing some research on the internet by searching with terms like "symlink mysql table", which initially brought different results than what my goal was (threads discussing where the db tables are being saved on disk), but then I have discovered that:

MySQL supports views, including updatable views. Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

...and also:

Materialized views are disk based and are updated periodically based upon the query definition. Views are virtual only and run the query definition each time they are accessed.

Source: https://stackoverflow.com/questions/47098749/mysql-create-symbolic-link-to-a-table-from-another-database

Then also this: https://www.w3schools.com/mysql/mysql_view.asp

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.

A view is created with the CREATE VIEW statement.

...which has this example:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

So I was wondering if we could do something like this and save the day (untested - someone with better SQL knowledge should verify this):

CREATE VIEW language AS
SELECT langcode
FROM url_alias;

Which means that each time that a query would try to access the language table (which may not exist), we will be returning the contents of the langcode table, which does exist. ...or the other way around? ...or something like that anyway?

---

...close but no 😞 ...I thought that SQL views would act as "virtual columns" but they are more like "virtual tables" 🤔

---

...apparently there is a way to create "virtual columns": https://stackoverflow.com/questions/17948940/how-to-build-virtual-columns

But if I'm reading/understanding things right, then we'd need to be querying a different table (a view) 🤔

---

...last thing and I'll leave it alone for now: https://en.wikipedia.org/wiki/Virtual_column

In relational databases a virtual column is a table column whose value is automatically computed using other columns values, or another deterministic expression. ...

Good news:

MariaDB is a MySQL fork. Virtual columns were added in the 5.2 tree.

...potentially bad news (OK, not so bad news: https://backdropcms.org/project/backdrop/telemetry):

Support for virtual columns, known in MySQL as generated columns, started becoming available in MySQL 5.7. Various limitations on their use have been relaxed in subsequent versions.

[jenlampton]

I don't think this is a real problem, or worth solving right now. We already have a solution in place for the problem in #5496, we just need to find out why it's not working on Pantheon.

That said, it would be an interesting idea to develop a backwards-compatible database schema. It could allow us to change how things are organized in the database, but continue to check "the old locations" by some kind of abstraction (though at a probably untenable performance cost) . Something like this may be useful for #56