On Friday, December 7, 2012 2:11:32 AM UTC-8, Doug Tykocki wrote: > > Hi, > > I've run into an issue where Sequel appears to be generating an incorrect > query for parsing the information schema from SQL Server 2005. Small > example: > > module Omegadb > class ProfilePicture < > Sequel::Model(OMEGADB[:dbo__tblfiles.qualify(:omegadb)]) > end > end > > will generate: > > SELECT > [COLUMN_NAME] AS [COLUMN] > , [DATA_TYPE] AS [DB_TYPE] > , [CHARACTER_MAXIMUM_LENGTH] AS [MAX_CHARS] > , [COLUMN_DEFAULT] AS [DEFAULT] > , [IS_NULLABLE] AS [ALLOW_NULL] > , [NUMERIC_PRECISION] AS [COLUMN_SIZE] > , [NUMERIC_SCALE] AS [SCALE] > FROM > [INFORMATION_SCHEMA].[TABLES] AS [T] > INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] AS [C] ON ( > ( > [C].[TABLE_CATALOG] = [T].[TABLE_CATALOG] ) > AND ( > [C].[TABLE_SCHEMA] = [T].[TABLE_SCHEMA] ) > AND ( > [C].[TABLE_NAME] = [T].[TABLE_NAME] ) > ) > WHERE > ( > ( [C].[TABLE_NAME] = N'DBO__TBLFILES' ) > AND ( [C].[TABLE_SCHEMA] = [OMEGADB] ) > ) > > When executed, it fails with error: Invalid column name 'OMEGADB'. > Assuming you are connected to the correct database, I believe the generated > where statement should be: > > WHERE > ( > ( [C].[TABLE_NAME] = N'TBLFILES' ) > AND ( [C].[TABLE_SCHEMA] = 'dbo' ) > ) > > I'm using Sequel 3.40.0 with tiny_tds 0.5.1. Hopefully I'm just doing > something wrong here :) >
I don't believe Sequel currently supports parsing metadata for 3-level qualified tables (omegadb.dbo.tblfiles) on Microsoft SQL Server. Do you have to qualify dbo.tblfiles with omegadb? I'm guessing yes because you want to refer to dbo.tblfiles in the omegadb database (when you are connected to another database on the same server), but if not, you could just drop the qualification with omegadb. Fixing this probably requires changing the Microsoft SQL Server schema_parse_table method to recognize 3-level qualified tables, by selecting from database_name.information_schema.table instead of just information_schema.table in the three-level qualification case. It also would require refactoring the general Database#schema method to call methods that can be overridden in adapters, and overriding those methods in the mssql shared adapter to recognize 3-level qualified tables and handle them appropriately (passing the 3rd level qualifier via a separate option to schema_parse_table such as :information_schema_schema). Do you want to take a shot at creating a patch that does that? Note that Microsoft SQL Server supports 4-level qualified tables (http://msdn.microsoft.com/en-us/library/ms187879%28v=sql.105%29.aspx), so you might want to consider that case as well. I'm assuming it can be handled the same way as the 3-level qualified case, but having the :information_schema_schema option be a qualified identifier instead of a regular identifier. Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/sequel-talk/-/A1ZWQXXziJ4J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
