namespace SqlDynamite.Common

open System
open System.Data
open System.Data.Common
open System.Data.Odbc
open System.Data.OleDb
open System.Data.SqlClient
open System.Reflection
open System.Threading

[<AbstractClass>]
type MetadataFinder() =
    [<DefaultValue(false)>]
    val mutable _connection:DbConnection

    abstract member CreateDataSet: string -> DataSet
    
    default this.CreateDataSet(query:string) : DataSet =
        match this._connection with
        | :? OdbcConnection ->
            let command = new OdbcCommand(query, this._connection :?> OdbcConnection)
            command.CommandTimeout <- 3600
            let adapter = new OdbcDataAdapter(command)
            let dataSet = new DataSet()
            ignore (adapter.Fill(dataSet))
            dataSet
        | :? OleDbConnection ->
            let command = new OleDbCommand(query, this._connection :?> OleDbConnection)
            command.CommandTimeout <- 3600
            let adapter = new OleDbDataAdapter(command)
            let dataSet = new DataSet()
            ignore (adapter.Fill(dataSet))
            dataSet
        | :? SqlConnection ->
            let cmd = new SqlCommand(query, this._connection :?> SqlConnection)
            cmd.CommandTimeout <- 3600
            let adapter = new SqlDataAdapter(cmd)
            let dataSet = new DataSet()
            let _ = adapter.Fill(dataSet)
            dataSet
        | _ -> MetadataFinderHelper.CreateDataSet(this._connection, query)

    member this.GetConnection () : DbConnection =
        this._connection
    
    abstract member GetDatabaseList: string -> string list
    
    default this.GetDatabaseList (scname:string) : string list =
        let schema = if String.IsNullOrWhiteSpace(scname) then this._connection.GetSchema() else this._connection.GetSchema(scname)
        let rec loop n acc =
            if n > 0 then
                let item = schema.Rows.Item(n - 1).Item(0).ToString()
                loop (n - 1) (item :: acc)
            else
                acc
        loop schema.Rows.Count []

    abstract member EstablishConnection: string * ConnectionType * string -> unit

    override this.EstablishConnection(connectionString:string, connectionType:ConnectionType, database:string) =
        if connectionType = ConnectionType.ODBC then
            this._connection <- new OdbcConnection(connectionString)
            this._connection.Open()
        else if connectionType = ConnectionType.OLEDB then
            this._connection <- new OleDbConnection(connectionString)
            this._connection.Open()
        else
            this._connection <- MetadataFinderHelper.CreateConnection(this, connectionString)
            if this._connection = null then
                ignore(MetadataFinder.ShowMessageBox("Connection is not supported for the .NET Data Provider"))
            else
                this._connection.Open()

    static member String2ObjectType (str:string) : ObjectType =
        match str with
        | "P" -> ObjectType.PROCEDURE
        | "X" -> ObjectType.PROCEDURE
        | "XP" -> ObjectType.EXTENDED_PROCEDURE
        | "PC" -> ObjectType.EXTENDED_PROCEDURE
        | "V" -> ObjectType.VIEW
        | "S" -> ObjectType.TABLE
        | "U" -> ObjectType.TABLE
        | "BASE TABLE" -> ObjectType.TABLE
        | "SYSTEM VIEW" -> ObjectType.VIEW
        | "IF" -> ObjectType.FUNCTION
        | "TF" -> ObjectType.FUNCTION
        | "IS" -> ObjectType.FUNCTION
        | "FN" -> ObjectType.FUNCTION
        | "SF" -> ObjectType.FUNCTION
        | "AF" -> ObjectType.AGGREGATE
        | "FS" -> ObjectType.EXTENDED_FUNCTION
        | "FT" -> ObjectType.EXTENDED_FUNCTION
        | "TR" -> ObjectType.TRIGGER
        | "TA" -> ObjectType.EXTENDED_TRIGGER
        | "PK" -> ObjectType.PRIMARY_KEY
        | "F" -> ObjectType.FOREIGN_KEY
        | "FK" -> ObjectType.FOREIGN_KEY
        | "RI" -> ObjectType.FOREIGN_KEY
        | "UQ" -> ObjectType.INDEX
        | "SO" -> ObjectType.SEQUENCE
        | "SN" -> ObjectType.SYNONYM
        | "SQ" -> ObjectType.SERVICE_QUEUE
        | "UNIQUE" -> ObjectType.INDEX
        | "INDEX" -> ObjectType.INDEX
        | "TABLE" -> ObjectType.TABLE
        | "PROCEDURE" -> ObjectType.PROCEDURE
        | "FUNCTION" -> ObjectType.FUNCTION
        | "AGGREGATE" -> ObjectType.AGGREGATE
        | "TRIGGER" -> ObjectType.TRIGGER
        | "VIEW" -> ObjectType.VIEW
        | "SEQUENCE" -> ObjectType.SEQUENCE
        | "PACKAGE" -> ObjectType.PACKAGE
        | "SYNONYM" -> ObjectType.SYNONYM
        | "TYPE" -> ObjectType.TYPE
        | "JOB" -> ObjectType.JOB
        | "REPORT" -> ObjectType.REPORT
        | "STRING" -> ObjectType.STRING
        | "LIST" -> ObjectType.LIST
        | "HASH" -> ObjectType.HASH
        | "SET" -> ObjectType.SET
        | "SORTEDSET" -> ObjectType.ZSET
        | "STREAM" -> ObjectType.STREAM
        | "SERVICE" -> ObjectType.SERVICE
        | "ROUTE" -> ObjectType.ROUTE
        | "LABEL" -> ObjectType.LABEL
        | "MEASUREMENT" -> ObjectType.MEASUREMENT
        | "CUBE" -> ObjectType.CUBE
        | "DIMENSION" -> ObjectType.DIMENSION
        | str -> match str.Replace(' ', '_') with
                 | "EXTENDED_PROCEDURE" -> ObjectType.EXTENDED_PROCEDURE
                 | "EXTENDED_FUNCTION" -> ObjectType.EXTENDED_FUNCTION
                 | "EXTENDED_TRIGGER" -> ObjectType.EXTENDED_TRIGGER
                 | "WINDOW_FUNCTION" -> ObjectType.WINDOW_FUNCTION
                 | "FOREIGN_KEY" -> ObjectType.FOREIGN_KEY
                 | "PRIMARY_KEY" -> ObjectType.PRIMARY_KEY
                 | "SERVICE_MESSAGE_TYPE" -> ObjectType.SERVICE_MESSAGE_TYPE
                 | "SERVICE_CONTRACT" -> ObjectType.SERVICE_CONTRACT
                 | "REMOTE_SERVICE_BINDING" -> ObjectType.REMOTE_SERVICE_BINDING
                 | "SERVICE_BROKER_PRIORITY" -> ObjectType.SERVICE_BROKER_PRIORITY
                 | "SERVICE_QUEUE" -> ObjectType.SERVICE_QUEUE
                 | _ -> ObjectType.OTHER

    static member ShowMessageBox(str) =
        let assembly = Assembly.Load("System.Windows.Forms, Version=2.0.0.0, Culture=Neutral, PublicKeyToken=b77a5c561934e089, ProcessorArchitecture=MSIL")
        assembly.GetType("System.Windows.Forms.MessageBox").GetMethod("Show", [|Type.GetType("System.String")|]).Invoke(null, [|str|])
    
    member this.SearchInNames(boxes:bool array, query:string, cancellationTokenSource:CancellationTokenSource) : (string * ObjectType * string) list =
        if boxes.[16] then
            try
                this.GetObjects(query)
            with
                exc ->
                    cancellationTokenSource.Cancel()
                    ignore (MetadataFinder.ShowMessageBox(Extensions.GetExceptionMessages(exc)))
                    []
        else
            []

    member this.SearchInDefinitionsByName(boxes:bool array, search:string, caseSensitive:bool, cancellationTokenSource:CancellationTokenSource) : (string * ObjectType * string) list =
        if boxes.[17] && boxes.[0] then
            let query = this.GenerateSearchScript(search, caseSensitive)
            if String.IsNullOrWhiteSpace(query) = false then
                try
                    let ds = this.CreateDataSet(query)
                    if ds = null then
                        []
                    else
                        let list = ds.Tables.Item(0).Rows
                        let rec loop n acc =
                            if n > 0 then
                                let row = list.Item(n - 1)
                                let item = (row.Item(0).ToString().Trim(), MetadataFinder.String2ObjectType(row.Item(1).ToString().Trim()), this._connection.Database)
                                loop (n - 1) (item :: acc)
                            else
                                acc
                        loop list.Count []
                with
                    exc ->
                        cancellationTokenSource.Cancel()
                        ignore (MetadataFinder.ShowMessageBox(Extensions.GetExceptionMessages(exc)))
                        []
            else
                []
        else
            []

    member this.SearchInDefinitionsByContent(boxes:bool array, search:string, types:ObjectType list, caseSensitive:bool, cancellationTokenSource:CancellationTokenSource) : (string * ObjectType * string) list =
        if boxes.[17] && (boxes.[0] || boxes.[4] || boxes.[5] || boxes.[6] || boxes.[7] || boxes.[9] || boxes.[12] || boxes.[13] || boxes.[15]) then
            let query = this.GetNameByTextColumns(search, types, caseSensitive)
            if String.IsNullOrWhiteSpace(query) = false then
                try
                    let ds = this.CreateDataSet(query)
                    if ds = null then
                        []
                    else
                        let list = ds.Tables.Item(0).Rows
                        let rec loop n acc =
                            if n > 0 then
                                let row = list.Item(n - 1)
                                let item = (row.Item(0).ToString().Trim(), MetadataFinder.String2ObjectType(row.Item(1).ToString().Trim()), this._connection.Database)
                                loop (n - 1) (item :: acc)
                            else
                                acc
                        loop list.Count []
                with
                    exc ->
                        cancellationTokenSource.Cancel()
                        ignore (MetadataFinder.ShowMessageBox(Extensions.GetExceptionMessages(exc)))
                        []
            else
                []
        else
            []

    member this.RetrieveDefinition(searchStr:string, typ:ObjectType, cancellationTokenSource:CancellationTokenSource) : string =
        try
            match typ with
            | ObjectType.TABLE ->
                let tables = this.GetDefColumns(searchStr, typ)
                let fields = tables.Item(0)
                let keys = tables.Item(1)
                let checks = tables.Item(2)
                this.Compose(searchStr, fields, keys, checks)
            | ObjectType.FOREIGN_KEY -> String.Join("", this.GetForeignKeyDefinition(searchStr))
            | ObjectType.PRIMARY_KEY -> String.Join("", this.GetPrimaryKeyDefinition(searchStr))
            | ObjectType.INDEX -> String.Join("", this.GetIndexDefinition(searchStr))
            | ObjectType.SEQUENCE -> String.Join("", this.GetSequenceDefinition(searchStr))
            | ObjectType.SYNONYM -> String.Join("", this.GetSynonymDefinition(searchStr))
            | ObjectType.TYPE -> String.Join("", this.GetTypeDefinition(searchStr))
            | ObjectType.JOB -> String.Join("", this.GetJobDefinition(searchStr))
            | ObjectType.REPORT -> String.Join("", this.GetReportDefinition(searchStr))
            | ObjectType.EXTENDED_PROCEDURE -> this.GetExtendedProcedureDefinition(searchStr)
            | ObjectType.EXTENDED_FUNCTION -> this.GetExtendedProcedureDefinition(searchStr)
            | ObjectType.AGGREGATE -> this.GetExtendedProcedureDefinition(searchStr)
            | ObjectType.EXTENDED_TRIGGER -> this.GetExtendedTriggerDefinition(searchStr)
            | ObjectType.STRING ->
                this.GetType().GetMethod("GetStringValue", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.LIST ->
                this.GetType().GetMethod("GetListValue", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.HASH ->
                this.GetType().GetMethod("GetHashValue", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.SET ->
                this.GetType().GetMethod("GetSetValue", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.ZSET ->
                this.GetType().GetMethod("GetSortedSetValue", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.STREAM ->
                this.GetType().GetMethod("GetStreamValue", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.SERVICE_MESSAGE_TYPE ->
                this.GetType().GetMethod("GetServiceMessageTypeDefinition", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.SERVICE_CONTRACT ->
                this.GetType().GetMethod("GetServiceContractDefinition", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.SERVICE_QUEUE ->
                this.GetType().GetMethod("GetServiceQueueDefinition", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.SERVICE ->
                this.GetType().GetMethod("GetServiceDefinition", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.ROUTE ->
                this.GetType().GetMethod("GetServiceRouteDefinition", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.REMOTE_SERVICE_BINDING ->
                this.GetType().GetMethod("GetRemoteServiceBinding", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | ObjectType.SERVICE_BROKER_PRIORITY ->
                this.GetType().GetMethod("GetBrokerPriority", [|typedefof<string>|]).Invoke(this, [|searchStr|]) :?> string
            | _ -> String.Join("", this.GetTextColumns(searchStr, typ))
        with
            | exc ->
                if cancellationTokenSource <> null then
                    cancellationTokenSource.Cancel()
                else
                    ()
                exc.Message

    static member JoinLists(list1:(string * ObjectType * string) list, list2:(string * ObjectType * string) list) : (string * ObjectType * string) list =
        List.ofSeq (Set.ofList (list1 @ list2))
    
    member this.GetObjects (query:string) : (string * ObjectType * string) list =
        let ds = this.CreateDataSet(query)
        if ds = null then
            []
        else
            let rows = ds.Tables.Item(0).Rows
            let rec loop n acc =
                if n > 0 then
                    let str = rows.Item(n - 1).Item(1).ToString().Trim()
                    let ot:ObjectType = MetadataFinder.String2ObjectType(str)
                    loop (n - 1) ((rows.Item(n - 1).Item(0).ToString().Trim(), ot, this._connection.Database) :: acc)
                else
                    acc
            let lst = loop rows.Count []
            lst

    member this.MakeConnectionString(driver:string, host:string, database:string, user:string, password:string, sspi:bool, connectionType:ConnectionType) =
        let parts = host.Split(':')
        let server = parts.GetValue(0).ToString()
        let mutable connectionString = null
        match this.GetType().Name with
        | "Db2MetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Hostname={1};Protocol=TCPIP;Database={2};Uid={3};Pwd={4}", "{" + driver + "}", server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Hostname={1};Protocol=TCPIP;Database={2};Uid={3};Pwd={4}", driver, server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            else
                if parts.Length > 1 then
                    connectionString <- String.Format("Server={0}:{4};Database={1};Uid={2};Pwd={3}", server, database, user, password, parts.GetValue(1))
                else
                    connectionString <- String.Format("Server={0};Database={1};Uid={2};Pwd={3}", server, database, user, password)
        | "FirebirdMetadataFinder" ->
            let platform = Environment.OSVersion.Platform
            let clientLib =
                if platform = PlatformID.Unix && Environment.OSVersion.Version.Major >= 8 || platform = PlatformID.MacOSX then "libfbclient.dylib"
                elif platform = PlatformID.Unix && Environment.OSVersion.Version.Major <= 7 then "libfbclient.so"
                else "fbclient.dll"
            if connectionType = ConnectionType.ODBC then
                if parts.Length > 1 then
                    connectionString <- String.Format("Driver={0};DbName={1}/{5}:{2};Uid={3};Pwd={4};Client={6}","{" + driver + "}", server, database, user, password, parts.GetValue(1), clientLib)
                else
                    connectionString <- String.Format("Driver={0};DbName={1}:{2};Uid={3};Pwd={4};Client={5}","{" + driver + "}", server, database, user, password, clientLib)
            elif connectionType = ConnectionType.OLEDB then
                if parts.Length > 1 then
                    connectionString <- String.Format("Provider={0};Location={1}/{5}:{2};User Id={3};Password={4};auto_commit=true",driver, server, database, user, password, parts.GetValue(1))
                else
                    connectionString <- String.Format("Provider={0};Location={1}:{2};User Id={3};Password={4};auto_commit=true",driver, server, database, user, password)
            elif connectionType = ConnectionType.NET then
                connectionString <- String.Format("DataSource={0};Database={1};User={2};Password={3};Client={4}", server, database, user, password, clientLib)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
        | "MySqlMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Server={1};User={2};Password={3};Option=3","{" + driver + "}", server, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            elif connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Server={1};User={2};Password={3}", driver, server, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            elif connectionType = ConnectionType.NET then
                connectionString <- String.Format("Server={0};Database={1};Uid={2};Pwd={3};", server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
        | "InformixMetadataFinder" ->
            let parts2 = database.Split('/')
            let dbname = parts2.GetValue(0)
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={4};Host={0};Server={1};UID={2};Pwd={3}", server, dbname, user, password, "{" + driver + "}")
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Service={0}", parts.GetValue(1))
                if parts2.Length > 1 then
                    connectionString <- connectionString + String.Format(";Database={0}", parts2.GetValue(1))
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Data Source={2}@{1};User ID={3};Password={4}", driver, server, dbname, user, password)
            else
                connectionString <- String.Format("Host={0};Server={1};UID={2};Pwd={3}", server, dbname, user, password, sspi)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Service={0}", parts.GetValue(1))
                if parts2.Length > 1 then
                    connectionString <- connectionString + String.Format(";Database={0}", parts2.GetValue(1))
        | "IngresMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("DRIVER={0};SRVR={1};DB={2};Persist Security Info=False;Uid={3};Pwd={4};SELECTLOOPS=N;", driver, server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            elif connectionType = ConnectionType.NET then
                connectionString <- String.Format("Host={0};Database={1};User Id={2};PWD={3}", server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
        | "OracleMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                let port = if parts.Length > 1 then parts.GetValue(1).ToString() else "1521"
                connectionString <- String.Format("Driver={0};Server={1};Port={2};Database={3};Uid={4};Pwd={5}", "{" + driver + "}", server, port, database, user, password)
            else if connectionType = ConnectionType.OLEDB then
                let port = if parts.Length > 1 then parts.GetValue(1).ToString() else "1521"
                connectionString <- String.Format("Provider={0};Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={1})(PORT={2})))(CONNECT_DATA=(SID={3})(SERVER=DEDICATED)));User Id={4};Password={5};", driver, server, port, database, user, password)
            else
                let port = if parts.Length > 1 then parts.GetValue(1).ToString() else "1521"
                connectionString <- 
                    if sspi then String.Format("data source={0};integrated security=Yes;", server)
                    else String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};", server, port, database, user, password)
        | "PostgreSqlMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Server={1};Database={2};Uid={3};Pwd={4}","{" + driver + "}", server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            elif connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Data Source={1};Initial Catalog={2};User Id={3};Password={4}",driver, server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Extended Properties='Port={0}'", parts.GetValue(1))
            elif connectionType = ConnectionType.NET then
                connectionString <- String.Format("Server={0};Database={1};Uid={2};Pwd={3}", server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
        | "SybaseAnywhereMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};{1};ENG={2};uid={3};pwd={4}", "{" + driver + "}", "LINKS=TCPIP{HOST=" + server.ToString() + "}", database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Server={1};Initial Catalog={2};User Id={3};Password={4}", driver, server, database, user, password)
            else
                connectionString <- String.Format("{0}{1}{2}", "LINKS=TCPIP{IP=", server, "}")
                connectionString <- connectionString + String.Format(";ENG={0};Uid={1};Pwd={2}", database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
        | "SybaseAdvantageMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};DataDirectory={1};User ID={2};Password={3};ServerTypes=7;", "{" + driver + "}", database, user, password)
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Data Source={1};User ID={2};Password={3};Advantage Server Type=ADS_LOCAL_SERVER|ADS_REMOTE_SERVER;", driver, database, user, password)
            else
                connectionString <- String.Format("Data Source={0};User ID={1};Password={2};ServerType=LOCAL|REMOTE|AIS;", database, user, password)
        | "SapHanaMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};SERVERNODE={1};databaseName={2};UID={3};PWD={4}", "{" + driver + "}", host, database, user, password)
            elif connectionType = ConnectionType.NET then
                connectionString <- String.Format("Server={0};databaseName={1};UserID={2};Password={3}", host, database, user, password)
        | "UltraliteMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Dbf={1};Uid={2};Pwd={3}", "{" + driver + "}", database, user, password)
            elif connectionType = ConnectionType.NET then
                connectionString <- String.Format("Dbf={0};Uid={1};Pwd={2}", database, user, password)
        | "SybaseMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Server={1};charset=utf8;Uid={2};Pwd={3}","{" + driver + "}", server, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Server={1};Initial Catalog={2};User Id={3};Password={4}", driver, server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
            else
                connectionString <- String.Format("Data Source={0};Database={1};charset=utf8;Uid={2};Pwd={3}", server, database, user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
        | "SqlAzureMetadataFinder" ->
            let serverName = server.ToString().Replace(".database.windows.net", "")
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Server=tcp:{1};Database={2};Uid={3}@{1};Pwd={4};Encrypt=yes", "{" + driver + "}", server, database, user, password)
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Data Source=tcp:{1};Initial Catalog={2};User Id={3};Password={4}", driver, server, database, user, password)
            else
                connectionString <- String.Format("Server=tcp:{0};Database={1};Trusted_Connection={2};User ID={3}@{5};Password={4};Encrypt=True;", server, database, sspi, user, password, serverName)
        | "MsSqlMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Server={1};Database={2};Uid={3};Pwd={4}", "{" + driver + "}", server, database, user, password)
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Server={1};Database={2};User Id={3};Password={4};Trusted_Connection={5}", driver, server, database, user, password, if sspi = true then "Yes" else "No")
            else
                connectionString <- String.Format("data source={0};initial catalog={1};integrated security={2};user id={3};password={4}", server, database, sspi, user, password)
        | "SqliteMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={1};Data Source={0};Version=3;", database, "{" + driver + "}")
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Data Source={1};Version=3;", driver, database)
            else
                connectionString <- String.Format("Data Source={0};Version=3;", database)
        | "InterbaseMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                if parts.Length > 1 then
                    connectionString <- String.Format("Driver={0};DbName={1}/{5}:{2};Uid={3};Pwd={4}","{" + driver + "}", server, database, user, password, parts.GetValue(1))
                else
                    connectionString <- String.Format("Driver={0};DbName={1}:{2};Uid={3};Pwd={4}","{" + driver + "}", server, database, user, password)
            elif connectionType = ConnectionType.OLEDB then
                if parts.Length > 1 then
                    connectionString <- String.Format("Provider={0};Location={1}/{5}:{2};User Id={3};Password={4};auto_commit=true",driver, server, database, user, password, parts.GetValue(1))
                else
                    connectionString <- String.Format("Provider={0};Location={1}:{2};User Id={3};Password={4};auto_commit=true",driver, server, database, user, password)
            elif connectionType = ConnectionType.NET then
                if parts.Length > 1 then
                    connectionString <- String.Format("Datasource={0};Port={4};Database={1};UserID={2};Password={3}", server, database, user, password, parts.GetValue(1))
                else
                    connectionString <- String.Format("Datasource={0};Database={1};UserID={2};Password={3}", server, database, user, password)
        | "MongoDbMetadataFinder" ->
            if connectionType = ConnectionType.NET then
                let userStr = if String.IsNullOrWhiteSpace(user) || String.IsNullOrWhiteSpace(password) then "" else user + ":" + password + "@"
                let dbStr = if String.IsNullOrWhiteSpace(database) then "" else String.Format("/{0}", database)
                let sslStr = if server.EndsWith("mongodb.net") then "?ssl=true" else ""
                connectionString <- String.Format("mongodb://{0}{1}{2}{3}", userStr, host, dbStr, sslStr)
        | "VerticaMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Host={1};Database={2};User={3};Password={4};", "{" + driver + "}", server, database, user, password)
            else if connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};Host={1};Initial Catalog={2};User={3};Password={4};", driver, server, database, user, password)
            else
                connectionString <- String.Format("Host={0};Database={1};User={2};Password={3};", server, database, user, password)
        | "RedisMetadataFinder" ->
            if connectionType = ConnectionType.NET then
                connectionString <- server
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(":{0}", parts.GetValue(1))
                connectionString <- connectionString + String.Format(";{0}", if String.IsNullOrWhiteSpace(database.ToString()) then "db0" else database.ToString())
                if not(String.IsNullOrWhiteSpace(password)) then
                    connectionString <- connectionString + String.Format(";Password={0}", password)
        | "CassandraMetadataFinder" ->
            if connectionType = ConnectionType.NET then
                connectionString <- String.Format("Default Keyspace={1};Contact Points={0}", server, database)
                if not (String.IsNullOrWhiteSpace(user)) then
                    connectionString <- connectionString + String.Format(";Username={0};Password={1}", user, password)
                if parts.Length > 1 then
                    connectionString <- connectionString + String.Format(";Port={0}", parts.GetValue(1))
        | "Neo4jMetadataFinder" ->
            if connectionType = ConnectionType.NET then
                let userStr = if String.IsNullOrWhiteSpace(user) || String.IsNullOrWhiteSpace(password) then "" else ";" + user + ";" + password
                connectionString <- String.Format("bolt://{0}{1}", host, userStr)
        | "SsasMetadataFinder" ->
            if connectionType = ConnectionType.ODBC then
                connectionString <- String.Format("Driver={0};Url={1};initial catalog={2};user id={3};password={4};{5}", driver, host, database, user, password, if sspi then "integrated security=SSPI" else "")
            elif connectionType = ConnectionType.OLEDB then
                connectionString <- String.Format("Provider={0};data source={1};initial catalog={2};user id={3};password={4};{5}", driver, host, database, user, password, if sspi then "integrated security=SSPI" else "")
            else
                connectionString <- String.Format("data source={0};initial catalog={1};user id={2};password={3};{4}", host, database, user, password, if sspi then "integrated security=SSPI" else "")
        | _ ->
            raise (NotImplementedException())
        connectionString
    
    abstract member Compose: string * DataTable * DataTable * DataTable -> string
    abstract member GetDefColumns: string * ObjectType -> DataTable list
    abstract member GetTextColumns: string * ObjectType -> string list
    abstract member GetNameByTextColumns: string * List<ObjectType> * bool -> string
    abstract member GenerateNameScript: string * List<ObjectType> * bool -> string
    abstract member GenerateSearchScript: string * bool -> string
    abstract member GetForeignKeyDefinition: string -> string
    abstract member GetPrimaryKeyDefinition: string -> string
    abstract member GetIndexDefinition: string -> string
    abstract member GetSequenceDefinition: string -> string
    abstract member GetSynonymDefinition: string -> string
    abstract member GetTypeDefinition: string -> string
    abstract member GetJobDefinition: string -> string
    abstract member GetReportDefinition: string -> string
    abstract member GetExtendedProcedureDefinition: string -> string
    abstract member GetExtendedTriggerDefinition: string -> string
