wvdotnet/wvdbi.cs

   1 /*
   2  * Versaplex:
   3  *   Copyright (C)2007-2008 Versabanq Innovations Inc. and contributors.
   4  *       See the included file named LICENSE for license information.
   5  */
   6 using System;
   7 using System.Data;
   8 using System.Data.Common;
   9 using System.Data.Odbc;
  10 using System.Data.SqlTypes;
  11 using System.Data.SqlClient;
  12 using System.Collections.Generic;
  13 using System.Linq;
  14 using System.IO;
  15 using Wv;
  16 using Wv.Extensions;
  17
  18 namespace Wv
  19 {
  20     public abstract class WvDbi: IDisposable
  21     {
  22         static WvIni settings = new WvIni("wvodbc.ini");
  23         protected static WvLog log = new WvLog("WvDbi", WvLog.L.Debug1);
  24
  25         // MSSQL freaks out if there are more than 100 connections open at a
  26         // time.  Give ourselves a safety margin.
  27         static int num_active = 0;
  28         static int max_active = 50;
  29
  30         public static WvDbi create(string moniker)
  31         {
  32             log.print("Creating '{0}'\n", moniker);
  33
  34             if (!moniker.Contains(":") && settings[moniker].Count > 0)
  35             {
  36                 var sect = settings[moniker];
  37
  38                 if (sect["driver"] == "SqlClient")
  39                     return create(wv.fmt("mssql:"
  40                                          + "server={0};database={1};"
  41                                          + "User ID={2};Password={3};",
  42                                          sect["server"],
  43                                          sect["database"],
  44                                          sect["user"], sect["password"]));
  45                 else
  46                     return create(wv.fmt("ado:"
  47                                          + "driver={0};server={1};database={2};"
  48                                          + "uid={3};pwd={4};",
  49                                          sect["driver"], sect["server"],
  50                                          sect["database"],
  51                                          sect["user"], sect["password"]));
  52             }
  53
  54             if (moniker.StartsWith("dsn=") || moniker.StartsWith("driver="))
  55                 return create("ado:" + moniker);
  56
  57             WvDbi dbi = WvMoniker<WvDbi>.create(moniker);
  58             if (dbi == null)
  59                 throw new Exception
  60                            (wv.fmt("No moniker found for '{0}'", moniker));
  61             return dbi;
  62         }
  63
  64         protected WvDbi()
  65         {
  66             wv.assert(num_active < max_active,
  67                       "BUG: Too many open WvDbi connections");
  68             num_active++;
  69         }
  70
  71         ~WvDbi()
  72         {
  73             wv.assert(false, "A WvDbi object was not Dispose()d");
  74         }
  75
  76         // Implement IDisposable.
  77         public virtual void Dispose()
  78         {
  79             num_active--;
  80             GC.SuppressFinalize(this);
  81         }
  82
  83         public abstract WvSqlRows select(string sql, params object[] args);
  84         public abstract int execute(string sql, params object[] args);
  85
  86         public WvSqlRow select_onerow(string sql, params object[] args)
  87         {
  88             // only iterates a single row, if it exists
  89             foreach (WvSqlRow r in select(sql, args))
  90                 return r; // only return the first one
  91             return null;
  92         }
  93
  94         public WvAutoCast select_one(string sql, params object[] args)
  95         {
  96             var a = select_onerow(sql, args);
  97             if (a != null && a.Length > 0)
  98                 return a[0];
  99             else
 100                 return WvAutoCast._null;
 101         }
 102
 103         public int exec(string sql, params object[] args)
 104         {
 105             return execute(sql, args);
 106         }
 107
 108         public int try_execute(string sql, params object[] args)
 109         {
 110             try
 111             {
 112                 return execute(sql, args);
 113             }
 114             catch (DbException)
 115             {
 116                 // well, I guess no rows were affected...
 117                 return 0;
 118             }
 119         }
 120
 121         public int try_exec(string sql, params object[] args)
 122         {
 123             return try_execute(sql, args);
 124         }
 125     }
 126
 127     public class WvDbi_IDbConnection : WvDbi
 128     {
 129         IDbConnection _db;
 130         protected IDbConnection db
 131             { get { return _db; } }
 132
 133         public WvDbi_IDbConnection()
 134         {
 135         }
 136
 137         public override void Dispose()
 138         {
 139             if (db != null)
 140                 db.Dispose();
 141             _db = null;
 142             base.Dispose();
 143         }
 144
 145         protected void opendb(IDbConnection db)
 146         {
 147             this._db = db;
 148             if ((db.State & System.Data.ConnectionState.Open) == 0)
 149                 db.Open();
 150         }
 151
 152         public override WvSqlRows select(string sql, params object[] args)
 153         {
 154             return new WvSqlRows_IDataReader(prepare(sql, args));
 155         }
 156
 157         public override int execute(string sql, params object[] args)
 158         {
 159             return prepare(sql, args).ExecuteNonQuery();
 160         }
 161
 162         protected virtual IDbCommand prepare(string sql, params object[] args)
 163         {
 164             IDbCommand cmd = db.CreateCommand();
 165             cmd.CommandText = sql;
 166
 167             for (int i = 0; i < args.Length; i++)
 168             {
 169                 object a = args[i];
 170                 IDbDataParameter param;
 171                 if (cmd.Parameters.Count <= i)
 172                     cmd.Parameters.Add(param = cmd.CreateParameter());
 173                 else
 174                     param = (IDbDataParameter)cmd.Parameters[i];
 175                 if (a is DateTime)
 176                 {
 177                     param.DbType = DbType.DateTime;
 178                     param.Value = a;
 179                 }
 180                 else if (a is int)
 181                 {
 182                     param.DbType = DbType.Int32;
 183                     param.Value = a;
 184                 }
 185                 else
 186                 {
 187                     string s = a.ToString();
 188                     param.DbType = DbType.String; // I sure hope so...
 189                     param.Value = s;
 190                     param.Size = s.Length;
 191                 }
 192             }
 193
 194             return cmd;
 195         }
 196     }
 197
 198     [WvMoniker]
 199     public class WvDbi_ODBC : WvDbi_IDbConnection
 200     {
 201         public static void wvmoniker_register()
 202         {
 203             WvMoniker<WvDbi>.register("ado",
 204                  (string m, object o) => new WvDbi_ODBC(m));
 205             WvMoniker<WvDbi>.register("odbc",
 206                  (string m, object o) => new WvDbi_ODBC(m));
 207             WvMoniker<WvDbi>.register("mysql",
 208                  (string m, object o) => new WvDbi_ODBC("MySQL:" + m));
 209         }
 210
 211         public WvDbi_ODBC(string moniker)
 212         {
 213             string real;
 214             if (moniker.StartsWith("dsn=") || moniker.StartsWith("driver="))
 215                 real = moniker;
 216             else
 217             {
 218                 // try to parse it as an URL
 219                 WvUrl url = new WvUrl(moniker);
 220                 if (url.path.StartsWith("/"))
 221                     url.path = url.path.Substring(1);
 222                 if (url.method == "file") // method not provided
 223                     real = wv.fmt("dsn={0};database={1};"
 224                                   + "User ID={2};uid={2};Password={3};pwd={3}",
 225                                   url.host, url.path, url.user, url.password);
 226                 else
 227                     real = wv.fmt("driver={0};server={1};database={2};"
 228                                   + "User ID={3};uid={3};Password={4};pwd={4}",
 229                                   url.method, url.host, url.path,
 230                                   url.user, url.password);
 231             }
 232
 233             log.print("ODBC create: '{0}'\n", real);
 234             opendb(new OdbcConnection(real));
 235         }
 236
 237     }
 238
 239     [WvMoniker]
 240     public class WvDbi_MSSQL : WvDbi_IDbConnection
 241     {
 242         public static void wvmoniker_register()
 243         {
 244             WvMoniker<WvDbi>.register("mssql",
 245                  (string m, object o) => new WvDbi_MSSQL(m));
 246         }
 247
 248         public WvDbi_MSSQL(string moniker)
 249         {
 250             string real;
 251             if (!moniker.StartsWith("//"))
 252                 real = moniker;
 253             else
 254             {
 255                 // try to parse it as an URL
 256                 WvUrl url = new WvUrl(moniker);
 257                 if (url.path.StartsWith("/"))
 258                     url.path = url.path.Substring(1);
 259                 real = wv.fmt("server={0};database={1};"
 260                               + "User ID={2};Password={3};",
 261                               url.host, url.path, url.user, url.password);
 262             }
 263
 264             log.print("MSSQL create: '{0}'\n", real);
 265
 266             try
 267             {
 268                 opendb(new SqlConnection(real));
 269             }
 270             catch
 271             {
 272                 try { Dispose(); } catch {}
 273                 throw;
 274             }
 275         }
 276
 277         protected override IDbCommand prepare(string sql, params object[] args)
 278         {
 279             SqlCommand cmd = (SqlCommand)db.CreateCommand();
 280             cmd.CommandText = sql;
 281
 282             for (int i = 0; i < args.Length; i++)
 283             {
 284                 object a = args[i];
 285                 if (cmd.Parameters.Count <= i)
 286                 {
 287                     string name = wv.fmt("@col{0}", i);
 288                     cmd.Parameters.Add(new SqlParameter(name, a));
 289                 }
 290                 cmd.Parameters[i].Value = a;
 291             }
 292
 293             return cmd;
 294         }
 295     }
 296 }
 297