Oracle: investigate adding synonym support

[sjh37]

Current state

Oracle synonyms are not read. FilterSettings.IncludeSynonyms is honoured on SQL Server only; setting it for
DatabaseType.Oracle has no effect. A synonym does not appear in the generated model, and neither does the
object behind it unless that object already lives in the schema being generated from.

All six synonym hooks on OracleDatabaseReader return string.Empty (Efrpg/Readers/OracleDatabaseReader.cs
lines 406-436):

• SynonymTableSQLSetup() / SynonymTableSQL()
• SynonymForeignKeySQLSetup() / SynonymForeignKeySQL()
• SynonymStoredProcedureSQLSetup() / SynonymStoredProcedureSQL()

This is item 5 in TODO.md. Raising it as an issue so it is not lost.

Why it is worth doing

Synonyms are common in Oracle shops as the standard way to expose another schema's objects under the
application user, precisely because Oracle reads one schema per run (SYS_CONTEXT('USERENV','CURRENT_SCHEMA')).
So the workaround for the one-schema limitation is itself unsupported, which makes the limitation bite harder
than it does on SQL Server or PostgreSQL.

Current advice in the wiki is to generate against the owning schema with a second .tt, or create a view over
the synonym. Both work, but they are workarounds.

How the hooks are composed

DatabaseReader concatenates the base query and the synonym query, so the synonym half has to be a valid
UNION continuation:

Line	Composition
DatabaseReader.cs:211	SynonymTableSQLSetup() + TableSQL() + SynonymTableSQL() + SpecialQueryFlags()
DatabaseReader.cs:282	SynonymForeignKeySQLSetup() + ForeignKeySQL() + SynonymForeignKeySQL() + SpecialQueryFlags()
DatabaseReader.cs:526	SynonymStoredProcedureSQLSetup() + storedProcedureSQL + SynonymStoredProcedureSQL() + SpecialQueryFlags()

Blocker to deal with first

All three Oracle base queries end with ORDER BY:

• TableSQL() → ORDER BY c.TABLE_NAME, c.COLUMN_ID (line 130)
• ForeignKeySQL() → ORDER BY fkCon.TABLE_NAME, fkCon.CONSTRAINT_NAME, fkCol.POSITION
• StoredProcedureSQL() → ORDER BY 2, 3, 5

Appending UNION ALL ... after an ORDER BY is a syntax error in Oracle. The SQL Server, PostgreSQL and MySQL
readers all end on a WHERE/JOIN clause, which is why this has not come up before.

Options:

1. Move the sort out of the three Oracle base queries and let the generator order the results. Smallest change,
   but it touches the non-synonym path, so the Oracle integration goldens need re-baselining to prove nothing
   moved.
2. Have the Oracle synonym methods emit a wrapper (SELECT * FROM ( <base> ) UNION ALL ...). Keeps the base
   queries untouched but means the synonym string has to open a paren the base query does not know about,
   which is fragile.
3. Change the composition contract in DatabaseReader so the sort is applied last. Cleanest, but affects all
   five dialects.

Option 1 looks right, but that is a judgement to make when the goldens are in front of you.

What the SQL looks like

The good news: Oracle's local case is far simpler than SQL Server's. SQL Server needs ~518 lines because its
synonyms can cross databases and linked servers, so it parses base_object_name and builds #SynonymDetails /
#SynonymTargets temp tables via dynamic SQL. Oracle needs no setup step at all - SynonymTableSQLSetup() can
keep returning empty. ALL_SYNONYMS joined to ALL_TAB_COLS on (TABLE_OWNER, TABLE_NAME), projecting
SYNONYM_NAME as TableName, is a plain UNION ALL over the existing projection.

ALL_SYNONYMS gives OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK.

Suggested scope

In scope for a first pass

• Decide and apply the ORDER BY fix above; re-baseline the Oracle goldens
• SynonymTableSQL() - synonyms pointing at a table or view, columns projected under the synonym name
• SynonymStoredProcedureSQL() - synonyms pointing at a procedure, function or package
• SynonymForeignKeySQL() - see the risk note below
• Filter WHERE DB_LINK IS NULL, and document that remote synonyms are not covered
• Add synonyms to TestDatabases/Oracle/, plus goldens for: synonym to a table, synonym to a view, synonym
  to a procedure, and a FK between two synonym'd tables
• Update the Oracle wiki page (currently documents this as unsupported with two workarounds)

Out of scope initially

• DB_LINK synonyms. These need the remote dictionary over a database link and are the analogue of SQL
  Server's cross-database case. Worth a separate issue once the local case is proven.
• Public synonyms (OWNER = 'PUBLIC'). Probably worth excluding by default - a schema can see thousands of
  them and they would swamp the model. If included later, it should be opt-in.

Main risk

Not the SQL - the foreign key remapping. A FK whose parent or child table is reached through a synonym has
to resolve to the synonym's name on both sides, or the generated navigation properties point at entities
that do not exist. That is where the SQL Server implementation spends most of its complexity too, and where
this will need the most test coverage. See also #110 (Foreign Keys for synonyms) and #677 (Synonym four-part
support) for the SQL Server history.

Estimate

Local synonyms: roughly half a day plus test data and goldens, assuming the FK remapping behaves. DB_LINK
support is materially harder and should stay a separate piece of work.