Open MarkLFT opened 2 years ago
Hi Mark, It could be that the view name has some illegal characters in the name, such as
CREATE VIEW [view.with.multiple.periods]
AS
...
However I want to make sure you know why it wasn't generated. So I'll look into the generator and make sure it outputs a comment to say why a view is not generated.
Hi Simon, it would be good to have a reason of why something is not created, but in this case I do not think the name is the problem. Some examples of the missing view names are:
dbo.CashierCurrency dbo.CashierHotelGuest dbo.vCustomerCreditPeriodTotals dbo.vMsgs ...
Hope this helps.
Best regards and many thanks.
Sorry I would add, the name dbo.vPOSDiscounts works, which follows the naming patterns of the above that do not work. Plus a couple of others do work also.
Could you send me the following data for those views, so I can unit test them. This is what the generator will receive from sql server about the views:
SELECT c.TABLE_SCHEMA AS SchemaName,
c.TABLE_NAME AS TableName,
t.TABLE_TYPE AS TableType,
c.ORDINAL_POSITION AS Ordinal,
c.COLUMN_NAME AS ColumnName,
CAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1
ELSE 0
END AS BIT) AS IsNullable,
DATA_TYPE AS TypeName,
ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS [MaxLength],
CAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS [Precision],
ISNULL(COLUMN_DEFAULT, '') AS [Default],
CAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision,
ISNULL(NUMERIC_SCALE, 0) AS Scale,
CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity,
CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1
WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') = 1 THEN 1
WHEN DATA_TYPE = 'TIMESTAMP' THEN 1
WHEN DATA_TYPE = 'UNIQUEIDENTIFIER' AND LOWER(ISNULL(COLUMN_DEFAULT, '')) LIKE '%newsequentialid%' THEN 1
ELSE 0
END AS BIT) AS IsStoreGenerated,
CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0
ELSE 1
END AS BIT) AS PrimaryKey,
ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal,
CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0
ELSE 1
END AS BIT) AS IsForeignKey
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN (SELECT u.TABLE_SCHEMA,
u.TABLE_NAME,
u.COLUMN_NAME,
u.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
AND u.TABLE_NAME = tc.TABLE_NAME
AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') pk
ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.COLUMN_NAME = pk.COLUMN_NAME
LEFT OUTER JOIN (SELECT DISTINCT
u.TABLE_SCHEMA,
u.TABLE_NAME,
u.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
AND u.TABLE_NAME = tc.TABLE_NAME
AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY') fk
ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA
AND c.TABLE_NAME = fk.TABLE_NAME
AND c.COLUMN_NAME = fk.COLUMN_NAME
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')
AND c.TABLE_NAME NOT LIKE 'sysdiagram%'
AND t.TABLE_TYPE='view'
AND t.TABLE_NAME IN ('CashierCurrency', 'CashierHotelGuest', 'vCustomerCreditPeriodTotals', 'vMsgs')
If you want to keep it private, email me the result to simon@reversepoco.co.uk
Hi Simon,
I am afraid this issue comes down to my stupidity. Whilst I had set the include views to true, I did not realise that table exclusion regex filters also exclude views. Whilst testing some regex scenarios, I noticed the views appeared.
I am not sure if anyone else has fallen foul to this, but I find the management of views through the includeviews option, then fine control using table filters confusing. Maybe either using Include/exclude views filter might be less confusing, or if not possible, a not to indicate that views are controlled by the tables filters.
Sorry for the confusion and an inconvenience caused by my initial problem reporting.
Ah yes. The SQL I used for tables also returns the views so the table filter also filters the views. I should make this clearer! and will make sure I document this, or even split out the filtering for views separate from the tables.
No problem. I started refactoring the code to make this more testable anyway. I will continue refactoring and make sure I sort out the filtering of views explicitly.
I have around 20+ views in a database, but when I select includeviews = true; only two views are created as keyless tables, the remainder seem to be ignored.
My template type is set to EfCore5, which, from what I have read should map these views correctly. Am I missing an important step, or are these some severe limitations where this will work?
Thanks