namespace SqlDynamite.Common

open System
open System.Data

type FirebirdMetadataFinder() =
    inherit MetadataFinder()

    member this.TypeClause = "CASE f.rdb$field_type WHEN 261 THEN 'BLOB' WHEN 14 THEN 'CHAR' WHEN 40 THEN 'CSTRING' WHEN 11 THEN 'D_FLOAT' WHEN 27 THEN 'DOUBLE' WHEN 10 THEN 'FLOAT' 
    WHEN 16 THEN 'INT64' WHEN 8 THEN 'INTEGER' WHEN 9 THEN 'QUAD' WHEN 7 THEN 'SMALLINT' WHEN 12 THEN 'DATE' WHEN 13 THEN 'TIME' WHEN 35 THEN 'TIMESTAMP' WHEN 37 THEN 'VARCHAR' 
    WHEN 17 then 'BOOLEAN' WHEN 45 then 'BLOB_ID' ELSE 'UNKNOWN' END AS type_name, 
    case f.rdb$field_type when 7 then case f.rdb$field_sub_type when 1 then 'numeric' when 2 then 'decimal' end 
    when 8 then case f.rdb$field_sub_type when 1 then 'numeric' when 2 then 'decimal' end 
    when 16 then case f.rdb$field_sub_type when 1 then 'numeric' when 2 then 'decimal' else 'bigint' end 
    when 14 then case f.rdb$field_sub_type when 0 then 'unspecified' when 1 then 'binary' when 3 then 'acl' else case when f.rdb$field_sub_type is null then 'unspecified' end end 
    when 37 then case f.rdb$field_sub_type when 0 then 'unspecified' when 1 then 'text' when 3 then 'acl' else case when f.rdb$field_sub_type is null then 'unspecified' end end 
    when 261 then case f.rdb$field_sub_type when 0 then 'unspecified' when 1 then 'text' when 2 then 'blr' when 3 then 'acl' 
    when 4 then 'reserved' when 5 then 'encoded-meta-data' when 6 then 'irregular-finished-multi-db-tx' when 7 then 'transactional_description' 
    when 8 then 'external_file_description' end end as sub_type_name "
    
    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        let result = "CREATE TABLE " + tablename + " ("
        let rowdef (record:DataRow) =
            let stn = record.Item("sub_type_name")
            let rtn = record.Item("type_name").ToString()
            
            let typeName =
                match stn with
                | :? DBNull -> rtn.ToString().Trim()
                | _ -> if stn.ToString().Trim() = "unspecified" then rtn.ToString().Trim() else stn.ToString().Trim()

            let typeDef =
                match typeName.ToLower() with
                | "char" | "varchar" ->
                    "(" + record.Item("length").ToString().Trim() + ")"
                | "decimal" | "numeric" ->
                    "(" + record.Item("precision").ToString().Trim() + ", " + record.Item("scale").ToString().Replace("-", "").Trim() + ")"
                | _ -> ""
            
            let nullable = record.Item("nullable").ToString().Trim()
            let nulldef =
                if nullable = "1" then
                    "NULL"
                else
                    "NOT NULL"
            let iscomputed = record.Item("computed_source")
            let compdef =
                if iscomputed :? DBNull then
                    ""
                else
                    "COMPUTED BY " + record.Item("computed_source").ToString().Trim()
            let dsource = record.Item("dsource").ToString()
            "\n\t" + record.Item("column_name").ToString().Trim() + " " + (if iscomputed :? DBNull then typeName + " " + typeDef + " " + nulldef + " " + dsource else compdef) + ","
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (rowdef(fields.Rows.Item(n - 1)) :: acc)
            else
                acc
        result + String.Join("",loop fields.Rows.Count []).TrimEnd(',') + Environment.NewLine + ")"

    abstract member GetParameters: string * ObjectType -> string list
    
    override this.GetParameters(name:string, objtype:ObjectType) : string list =
        let query =
            if objtype = ObjectType.FUNCTION then
                let tc = this.TypeClause.Replace("f.rdb$field_type", "coalesce(f.rdb$field_type, p.rdb$field_type)").Replace("f.rdb$field_sub_type", "coalesce(f.rdb$field_sub_type, case when f.rdb$field_type is null then p.rdb$field_sub_type else null end)")
                "select p.rdb$argument_name as name, " + tc + ", p.rdb$default_source as dsource, coalesce(f.rdb$field_length, p.rdb$field_length, p.rdb$character_length) as \"length\", coalesce(f.rdb$field_precision, p.rdb$field_precision) as \"precision\", coalesce(f.rdb$field_scale, p.rdb$field_scale) as scale from rdb$function_arguments as p left join rdb$fields f on p.rdb$field_source = f.rdb$field_name
                     where p.rdb$function_name = '" + name + "' order by p.rdb$argument_position"
            elif objtype = ObjectType.PROCEDURE then
                "select p.rdb$parameter_name as name, " + this.TypeClause + ", p.rdb$parameter_type as ptype, p.rdb$default_source as dsource, f.rdb$field_length as \"length\", f.rdb$field_precision as \"precision\", f.rdb$field_scale as scale from rdb$procedure_parameters as p left join rdb$fields f on p.rdb$field_source = f.rdb$field_name
                     where p.rdb$procedure_name = '" + name + "' order by p.rdb$parameter_number"
            else
                ""
        let lst = this.CreateDataSet(query).Tables.Item(0)
        let rec loop n acc =
            if n > 0 then
                let pname = lst.Rows.Item(n - 1).Item("name").ToString().Trim()
                let ptype =
                    if objtype = ObjectType.PROCEDURE then
                        let pt = lst.Rows.Item(n - 1).Item("ptype") :?> int16
                        if pt = 1s then "RETURNS " else ""
                    else ""
                let ptn = lst.Rows.Item(n - 1).Item("type_name").ToString().Trim()
                let stn = lst.Rows.Item(n - 1).Item("sub_type_name")
                let dsource = lst.Rows.Item(n - 1).Item("dsource").ToString().Trim()
                let length = lst.Rows.Item(n - 1).Item("length").ToString().Trim()
                let precision = lst.Rows.Item(n - 1).Item("precision").ToString().Trim()
                let scale = lst.Rows.Item(n - 1).Item("scale").ToString().Trim().Replace("-", "")
        
                let typeName =
                    match stn with
                    | :? DBNull -> ptn
                    | _ -> if stn.ToString().Trim() = "unspecified" then ptn else stn.ToString().Trim()
                
                let typeDef =
                    match typeName.ToLower() with
                    | "char" | "varchar" ->
                        "(" + length + ")"
                    | "decimal" | "numeric" ->
                        "(" + precision + ", " + scale + ")"
                    | _ -> ""

                loop (n - 1) ("\n\t" + ptype + pname + " " + typeName + " " + typeDef + " " + dsource :: acc)
            else
                acc

        loop lst.Rows.Count []

    override this.GetDefColumns(name:string, objtype:ObjectType) : DataTable list =
        let script = "SELECT f.RDB$COMPUTED_SOURCE AS computed_source, r.RDB$FIELD_NAME AS column_name, r.RDB$DEFAULT_SOURCE AS dsource
            , r.RDB$NULL_FLAG AS nullable, f.RDB$FIELD_LENGTH AS \"length\", f.RDB$FIELD_PRECISION AS \"precision\", f.RDB$FIELD_SCALE AS scale, " + this.TypeClause + "
            FROM RDB$RELATION_FIELDS r LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME WHERE r.RDB$RELATION_NAME='" + name + "' ORDER BY r.RDB$FIELD_POSITION;"
        let columns = this.CreateDataSet(script.ToString()).Tables.Item(0)
        [columns; null; null]

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        let query =
            if objtype = ObjectType.FUNCTION then String.Format("select coalesce(rdb$function_source, rdb$entrypoint) from rdb$functions where rdb$function_name = '{0}';", name)
            else if objtype = ObjectType.TRIGGER then String.Format("select rdb$trigger_source from rdb$triggers where rdb$trigger_name = '{0}';", name)
            else if objtype = ObjectType.VIEW then String.Format("select rdb$view_source from rdb$relations where rdb$relation_name = '{0}';", name)
            else if objtype = ObjectType.PACKAGE then String.Format("select rdb$package_header_source || ASCII_CHAR(13) || ASCII_CHAR(10) || ASCII_CHAR(13) || ASCII_CHAR(10) || rdb$package_body_source from rdb$packages where rdb$package_name = '{0}';", name)
            else String.Format("select rdb$procedure_source from rdb$procedures where rdb$procedure_name = '{0}';", name)
        let lst = this.CreateDataSet(query).Tables.Item(0)
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (lst.Rows.Item(n - 1).Item(0).ToString() :: acc)
            else
                acc
        let header =
            if objtype = ObjectType.FUNCTION then
                let pms = this.GetParameters(name, objtype)
                "CREATE OR ALTER " + objtype.ToString() + " " + name + "(" + String.Join("", pms.Tail) + ")\n\tRETURNS " + pms.Head.Trim() + "\n\t"
            elif objtype = ObjectType.PROCEDURE then
                let pms = this.GetParameters(name, objtype)
                let rc = "RETURNS "
                let in_pms = String.Join("", pms |> List.filter (fun x -> not (x.Contains(rc))))
                let out_pms = if pms.Length <> in_pms.Length then String.Join("", pms |> List.filter (fun x -> x.Contains(rc))).Replace(rc, "") else ""
                "CREATE OR ALTER " + objtype.ToString() + " " + name + "(" + in_pms + ")\n" + if out_pms <> "" then rc + "(" + out_pms + ")\n" else ""
            else ""
        header :: loop lst.Rows.Count []

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        let rec loop n (acc:string list) =
            if n > 0 then
                let un = if String.Join("",acc).Length > 0 && n < types.Length then " union all " else ""
                if caseSensitive then
                    if types.Item(n - 1) = ObjectType.PROCEDURE then
                        loop (n - 1) (String.Format("select rdb$procedure_name as name, cast('PROCEDURE' as varchar(20)) as typ from rdb$procedures where coalesce(rdb$system_flag, 0) = 0 and rdb$procedure_source containing ('{0}')" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select rdb$trigger_name as name, cast('TRIGGER' as varchar(20)) as typ from rdb$triggers where coalesce(rdb$system_flag, 0) = 0 and rdb$trigger_source containing ('{0}')" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select rdb$relation_name as name, cast('VIEW' as varchar(20)) as typ from rdb$relations where coalesce(rdb$system_flag, 0) = 0 and rdb$view_source containing ('{0}')" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select rdb$function_name as name, cast('FUNCTION' as varchar(20)) as typ from rdb$functions where coalesce(rdb$system_flag, 0) = 0 and rdb$entrypoint containing ('{0}')" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.PACKAGE then
                        loop (n - 1) (String.Format("select rdb$package_name as name, cast('PACKAGE' as varchar(20)) as typ from rdb$packages where coalesce(rdb$system_flag, 0) = 0 and (rdb$package_header_source containing ('{0}') or rdb$package_body_source containing ('{0}'))" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                        loop (n - 1) (String.Format("select rdb$relation_name as name, cast('TABLE' as varchar(20)) as typ from rdb$relation_fields r left join rdb$fields f on r.rdb$field_source = f.rdb$field_name where coalesce(r.rdb$system_flag, 0) = 0 and f.rdb$computed_source containing ('{0}') or (f.rdb$computed_source is not null and r.rdb$field_name containing ('{0}'))" + un, searchStr) :: acc)
                    else
                        loop (n - 1) acc
                else
                    if types.Item(n - 1) = ObjectType.PROCEDURE then
                        loop (n - 1) (String.Format("select rdb$procedure_name as name, cast('PROCEDURE' as varchar(20)) as typ from rdb$procedures where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$procedure_source) containing ('{0}')" + un, searchStr.ToUpper()) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select rdb$trigger_name as name, cast('TRIGGER' as varchar(20)) as typ from rdb$triggers where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$trigger_source) containing ('{0}')" + un, searchStr.ToUpper()) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select rdb$relation_name as name, cast('VIEW' as varchar(20)) as typ from rdb$relations where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$view_source) containing ('{0}')" + un, searchStr.ToUpper()) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select rdb$function_name as name, cast('FUNCTION' as varchar(20)) as typ from rdb$functions where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$entrypoint) containing ('{0}')" + un, searchStr.ToUpper()) :: acc)
                    elif types.Item(n - 1) = ObjectType.PACKAGE then
                        loop (n - 1) (String.Format("select rdb$package_name as name, cast('PACKAGE' as varchar(20)) as typ from rdb$packages where coalesce(rdb$system_flag, 0) = 0 and (upper(rdb$package_header_source) containing ('{0}') or upper(rdb$package_body_source) containing ('{0}'))" + un, searchStr.ToUpper()) :: acc)
                    elif types.Item(n - 1) = ObjectType.COMPUTED_COLUMN then
                        loop (n - 1) (String.Format("select rdb$relation_name as name, cast('TABLE' as varchar(20)) as typ from rdb$relation_fields r left join rdb$fields f on r.rdb$field_source = f.rdb$field_name where coalesce(r.rdb$system_flag, 0) = 0 and upper(f.rdb$computed_source) containing ('{0}') or (f.rdb$computed_source is not null and upper(r.rdb$field_name) containing ('{0}'))" + un, searchStr.ToUpper()) :: acc)
                    else
                        loop (n - 1) acc
            else acc
        String.Join("",loop types.Length [])

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let rec loop n (acc:string list) =
            if n > 0 && caseSensitive then
                let un = if String.Join("",acc).Length <> 0 && n < types.Length then " union all " else ""
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) ("select rdb$procedure_name as name, cast('PROCEDURE' as varchar(20)) as typ from rdb$procedures where coalesce(rdb$system_flag, 0) = 0 and rdb$procedure_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) ("select rdb$trigger_name as name, cast('TRIGGER' as varchar(20)) as typ from rdb$triggers where coalesce(rdb$system_flag, 0) = 0 and rdb$trigger_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("select rdb$relation_name as name, cast('VIEW' as varchar(20)) as typ from rdb$relations where coalesce(rdb$system_flag, 0) = 0 and rdb$view_blr is not null and rdb$relation_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) ("select rdb$function_name as name, cast('FUNCTION' as varchar(20)) as typ from rdb$functions where coalesce(rdb$system_flag, 0) = 0 and rdb$function_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select rdb$relation_name as name, cast('TABLE' as varchar(20)) as typ from rdb$relations where coalesce(rdb$system_flag, 0) = 0 and rdb$view_blr is null and rdb$relation_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select rc.rdb$constraint_name as name, cast('FOREIGN_KEY' as varchar(20)) as typ from rdb$ref_constraints rc inner join rdb$relation_constraints c on rc.rdb$constraint_name = c.rdb$constraint_name inner join rdb$relations r on c.rdb$relation_name = r.rdb$relation_name where coalesce(r.rdb$system_flag, 0) = 0 and rc.rdb$constraint_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) ("select rdb$constraint_name as name, cast('PRIMARY_KEY' as varchar(20)) as typ from rdb$relation_constraints c inner join rdb$relations r on c.rdb$relation_name = r.rdb$relation_name where coalesce(r.rdb$system_flag, 0) = 0 and c.rdb$constraint_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) ("select rdb$index_name as name, cast('INDEX' as varchar(20)) as typ from rdb$indices where coalesce(rdb$system_flag, 0) = 0 and rdb$index_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) ("select rdb$generator_name as name, cast('SEQUENCE' as varchar(20)) as typ from rdb$generators where coalesce(rdb$system_flag, 0) = 0 and rdb$generator_name containing ('" + search + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.TYPE then
                    loop (n - 1) ("select rdb$field_name as name, cast('TYPE' as varchar(20)) as typ from rdb$fields where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$field_name) not like 'RDB$%' and rdb$field_name containing ('" + search + "') " + un :: acc)
                else
                    loop (n - 1) acc
            elif n > 0 then
                let un = if String.Join("",acc).Length <> 0 && n < types.Length then " union all " else ""
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) ("select rdb$procedure_name as name, cast('PROCEDURE' as varchar(20)) as typ from rdb$procedures where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$procedure_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) ("select rdb$trigger_name as name, cast('TRIGGER' as varchar(20)) as typ from rdb$triggers where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$trigger_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("select rdb$relation_name as name, cast('VIEW' as varchar(20)) as typ from rdb$relations where coalesce(rdb$system_flag, 0) = 0 and rdb$view_blr is not null and upper(rdb$relation_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) ("select rdb$function_name as name, cast('FUNCTION' as varchar(20)) as typ from rdb$functions where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$function_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select rdb$relation_name as name, cast('TABLE' as varchar(20)) as typ from rdb$relations where coalesce(rdb$system_flag, 0) = 0 and rdb$view_blr is null and upper(rdb$relation_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select rc.rdb$constraint_name as name, cast('FOREIGN_KEY' as varchar(20)) as typ from rdb$ref_constraints rc inner join rdb$relation_constraints c on rc.rdb$constraint_name = c.rdb$constraint_name inner join rdb$relations r on c.rdb$relation_name = r.rdb$relation_name where coalesce(r.rdb$system_flag, 0) = 0 and upper(rc.rdb$constraint_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) ("select c.rdb$constraint_name as name, cast('PRIMARY_KEY' as varchar(20)) as typ from rdb$relation_constraints c inner join rdb$relations r on c.rdb$relation_name = r.rdb$relation_name where coalesce(r.rdb$system_flag, 0) = 0 and upper(c.rdb$constraint_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) ("select rdb$index_name as name, cast('INDEX' as varchar(20)) as typ from rdb$indices where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$index_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) ("select rdb$generator_name as name, cast('SEQUENCE' as varchar(20)) as typ from rdb$generators where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$generator_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                elif types.Item(n - 1) = ObjectType.TYPE then
                    loop (n - 1) ("select rdb$field_name as name, cast('TYPE' as varchar(20)) as typ from rdb$fields where coalesce(rdb$system_flag, 0) = 0 and upper(rdb$field_name) not like 'RDB$%' and upper(rdb$field_name) containing ('" + search.ToUpper() + "') " + un :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let query = String.Join("",loop types.Length [])
        query + " order by 2, 1;"

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select f.rdb$relation_name, (case when rdb$view_blr is null then 'TABLE' else 'VIEW' end) from rdb$relation_fields f join rdb$relations r on f.rdb$relation_name = r.rdb$relation_name and (r.rdb$system_flag is null or coalesce(r.rdb$system_flag, 0) = 0)"
        let cond =
            if caseSensitive then
                " where coalesce(r.rdb$system_flag, 0) = 0 and f.rdb$field_name containing ('" + search
            else
                " where coalesce(r.rdb$system_flag, 0) = 0 and upper(f.rdb$field_name) containing ('" + search.ToUpper()
        query + cond + "') order by f.rdb$relation_name, f.rdb$field_position;"

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        raise (NotImplementedException())

    override this.GetPrimaryKeyDefinition(constraintName:string) : string =
        raise (NotImplementedException())

    override this.GetIndexDefinition(constraintName:string) : string =
        raise (NotImplementedException())

    override this.GetSequenceDefinition(sequenceName:string) : string =
        let query = String.Format("execute block returns (out_name char(31), out_value bigint) as begin for select rdb$generator_name from rdb$generators where rdb$generator_name='{0}' into out_name do begin execute statement 'select gen_id(' || out_name || ', 0) from rdb$database' into out_value; suspend; end end", sequenceName)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let part1 = "CREATE SEQUENCE " + sequenceName.Trim() + ";" + Environment.NewLine
        let part2 = "ALTER SEQUENCE " + sequenceName.Trim() + " RESTART WITH " + row.Item("out_value").ToString() + ";"
        part1 + part2

    override this.GetSynonymDefinition(synonymName:string) : string =
        raise (NotImplementedException())

    override this.GetTypeDefinition(typeName:string) : string =
        let query = "select f.rdb$field_name, f.rdb$validation_source, f.rdb$default_source, f.rdb$field_length, f.rdb$field_precision, f.rdb$field_scale, f.rdb$null_flag, " + this.TypeClause + "
                            , f.rdb$collation_id, f.rdb$character_set_id
                     from rdb$fields f
                     where f.rdb$field_name = '" + typeName + "'"
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let collation_name = if row.Item("rdb$collation_id") :? DBNull then "" else "COLLATE " + this.CreateDataSet("select rdb$collation_name from rdb$collations where rdb$collation_id = '" + row.Item("rdb$collation_id").ToString() + "'").Tables.Item(0).Rows.Item(0).Item(0).ToString()
        let character_set_name = if row.Item("rdb$character_set_id") :? DBNull then "" else "CHARACTER SET " + this.CreateDataSet("select rdb$character_set_name from rdb$character_sets where rdb$character_set_id = '" + row.Item("rdb$character_set_id").ToString() + "'").Tables.Item(0).Rows.Item(0).Item(0).ToString()
        let default_str = if row.Item("rdb$default_source") :? DBNull then "" else row.Item("rdb$default_source").ToString() + Environment.NewLine
        let check_str = if row.Item("rdb$validation_source") :? DBNull then "" else row.Item("rdb$validation_source").ToString() + Environment.NewLine
        let null_str = if row.Item("rdb$null_flag") :? DBNull then "" else "NOT NULL" + Environment.NewLine

        let stn = row.Item("sub_type_name")
        let rtn = row.Item("type_name").ToString()
            
        let type_str =
            match stn with
            | :? DBNull -> rtn.ToString().Trim()
            | _ -> if stn.ToString().Trim() = "unspecified" then rtn.ToString().Trim() else stn.ToString().Trim()

        let typeDef =
            match type_str.ToLower() with
            | "char" | "varchar" ->
                "(" + row.Item("rdb$field_length").ToString().Trim() + ")"
            | "decimal" | "numeric" ->
                "(" + row.Item("rdb$field_precision").ToString().Trim() + ", " + row.Item("rdb$field_scale").ToString().Replace("-", "").Trim() + ")"
            | _ -> ""

        let ddl = "CREATE DOMAIN " + typeName + " AS" + Environment.NewLine + type_str + typeDef + " " + character_set_name.Trim() + Environment.NewLine
        ddl + default_str + null_str + check_str + collation_name.Trim() + ";"

    override this.GetJobDefinition(jobName:string) : string =
        raise (NotImplementedException())

    override this.GetReportDefinition(reportName:string) : string =
        raise (NotImplementedException())

    override this.GetExtendedProcedureDefinition(procName:string) : string =
        raise (NotImplementedException())

    override this.GetExtendedTriggerDefinition(triggerName:string) : string =
        raise (NotImplementedException())
