List:Commits« Previous MessageNext Message »
From:Reggie Burnett Date:April 8 2011 5:04pm
Subject:bzr commit into connector-net-trunk branch (reggie.burnett:968)
View as plain text  
#At file:///C:/Users/Reggie/work/connector-net/trunk/ based on revid:reggie.burnett@stripped

  968 Reggie Burnett	2011-04-08
      Implemented simple table caching.

    added:
      MySql.Data/Provider/Source/TableCache.cs
      MySql.Data/Tests/Source/TableCaching.cs
    modified:
      .bzrignore
      CHANGES
      MySql.Data/Provider/MySql.Data.CF.csproj
      MySql.Data/Provider/MySql.Data.csproj
      MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs
      MySql.Data/Provider/Source/ResultSet.cs
      MySql.Data/Provider/Source/command.cs
      MySql.Data/Provider/Source/datareader.cs
      MySql.Data/Tests/MySql.Data.Tests.csproj
=== modified file '.bzrignore'
=== modified file '.bzrignore'
--- a/.bzrignore	2011-03-03 18:02:14 +0000
+++ b/.bzrignore	2011-04-08 17:04:15 +0000
@@ -17,3 +17,4 @@
 Documentation/Output
 _UpgradeReport_Files
 VS.targets
+**/*.sln.cache

=== modified file 'CHANGES'
--- a/CHANGES	2011-03-31 17:29:29 +0000
+++ b/CHANGES	2011-04-08 17:04:15 +0000
@@ -1,3 +1,4 @@
 6.4.0
 - Implemented Cache Server Properties connection option
-- Implementd Windows authentication when used against a 5.5 server
\ No newline at end of file
+- Implementd Windows authentication when used against a 5.5 server
+- Implementd table caching
\ No newline at end of file

=== modified file 'MySql.Data/Provider/MySql.Data.CF.csproj'
--- a/MySql.Data/Provider/MySql.Data.CF.csproj	2010-10-22 20:20:50 +0000
+++ b/MySql.Data/Provider/MySql.Data.CF.csproj	2011-04-08 17:04:15 +0000
@@ -117,6 +117,7 @@
     <Compile Include="Source\SSPI.cs" />
     <Compile Include="Source\Statement.cs" />
     <Compile Include="Source\StoredProcedure.cs" />
+    <Compile Include="Source\TableCache.cs" />
     <Compile Include="Source\TimedStream.cs" />
     <Compile Include="Source\transaction.cs" />
     <Compile Include="Source\Types\MetaData.cs" />

=== modified file 'MySql.Data/Provider/MySql.Data.csproj'
--- a/MySql.Data/Provider/MySql.Data.csproj	2011-03-31 17:21:49 +0000
+++ b/MySql.Data/Provider/MySql.Data.csproj	2011-04-08 17:04:15 +0000
@@ -205,6 +205,7 @@
     <Compile Include="Source\common\QueryNormalizer.cs">
       <SubType>Code</SubType>
     </Compile>
+    <Compile Include="Source\TableCache.cs" />
     <Compile Include="Source\MySqlTrace.cs" />
     <Compile Include="Source\TimedStream.cs" />
     <Compile Include="Source\TracingDriver.cs" />

=== modified file 'MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs'
--- a/MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs	2011-03-31 17:29:29 +0000
+++ b/MySql.Data/Provider/Source/MySqlConnectionStringBuilder.cs	2011-04-08 17:04:15 +0000
@@ -613,6 +613,28 @@
             set { SetValue("Sql Server Mode", value); }
         }
 
+        [Category("Advanced")]
+        [DisplayName("Table Cache")]
+        [Description(@"Enables or disables caching of TableDirect command.  
+            A value of yes enables the cache while no disables it.")]
+        [DefaultValue(false)]
+        [ValidKeywords("tablecache, table cache")]
+        public bool TableCaching
+        {
+            get { return (bool)values["Table Cache"]; }
+            set { SetValue("Table Cache", value); }
+        }
+
+        [Category("Advanced")]
+        [DisplayName("Default Table Cache Age")]
+        [Description(@"Specifies how long a TableDirect result should be cached in seconds.")]  
+        [DefaultValue(60)]
+        public int DefaultTableCacheAge
+        {
+            get { return (int)values["Default Table Cache Age"]; }
+            set { SetValue("Default Table Cache Age", value); }
+        }
+
         #endregion
 
         #region Pooling Properties

=== modified file 'MySql.Data/Provider/Source/ResultSet.cs'
--- a/MySql.Data/Provider/Source/ResultSet.cs	2010-08-18 19:52:04 +0000
+++ b/MySql.Data/Provider/Source/ResultSet.cs	2011-04-08 17:04:15 +0000
@@ -26,6 +26,7 @@
 using MySql.Data.MySqlClient.Properties;
 using MySql.Data.Types;
 using System.Diagnostics;
+using System.Collections.Generic;
 
 namespace MySql.Data.MySqlClient
 {
@@ -48,6 +49,8 @@
         private int statementId;
         private int totalRows;
         private int skippedRows;
+        private bool cached;
+        private List<IMySqlValue[]> cachedValues;
 
         public ResultSet(int affectedRows, int insertedId)
         {
@@ -117,6 +120,17 @@
             get { return skippedRows; }
         }
 
+        public bool Cached
+        {
+            get { return cached; }
+            set 
+            { 
+                cached = value;
+                if (cached && cachedValues == null)
+                    cachedValues = new List<IMySqlValue[]>();
+            }
+        }
+
         #endregion
 
         /// <summary>
@@ -181,7 +195,11 @@
 
         public bool NextRow(CommandBehavior behavior)
         {
-            if (readDone) return false;
+            if (readDone)
+            {
+                if (Cached) return CachedNextRow(behavior);
+                return false;
+            }
 
             if ((behavior & CommandBehavior.SingleRow) != 0 && rowIndex == 0)
                 return false;
@@ -219,30 +237,61 @@
             return true;
         }
 
+        private bool CachedNextRow(CommandBehavior behavior)
+        {
+            if ((behavior & CommandBehavior.SingleRow) != 0 && rowIndex == 0)
+                return false;
+            if (rowIndex == (totalRows - 1)) return false;
+            rowIndex++;
+            values = cachedValues[rowIndex];
+            return true;
+        }
+
         /// <summary>
         /// Closes the current resultset, dumping any data still on the wire
         /// </summary>
         public void Close()
         {
-            if (readDone) return;
+            if (!readDone)
+            {
 
-            // if we have rows but the user didn't read the first one then mark it as skipped
-            if (HasRows && rowIndex == -1)
-                skippedRows++;
-            try
-            {
-                while (driver.IsOpen && driver.SkipDataRow())
-                {
-                    totalRows++;
+                // if we have rows but the user didn't read the first one then mark it as skipped
+                if (HasRows && rowIndex == -1)
                     skippedRows++;
-                }
-            }
-            catch (System.IO.IOException)
-            {
-                // it is ok to eat IO exceptions here, we just want to 
-                // close the result set
-            }
-            readDone = true;
+                try
+                {
+                    while (driver.IsOpen && driver.SkipDataRow())
+                    {
+                        totalRows++;
+                        skippedRows++;
+                    }
+                }
+                catch (System.IO.IOException)
+                {
+                    // it is ok to eat IO exceptions here, we just want to 
+                    // close the result set
+                }
+                readDone = true;
+            }
+            else if (driver == null)
+                CacheClose();
+
+            driver = null;
+            if (Cached) CacheReset();
+        }
+
+        private void CacheClose()
+        {
+            skippedRows = totalRows - rowIndex - 1;
+        }
+
+        private void CacheReset()
+        {
+            if (!Cached) return;
+            rowIndex = -1;
+            affectedRows = -1;
+            insertedId = -1;
+            skippedRows = 0;
         }
 
         public bool FieldRead(int index)
@@ -285,6 +334,13 @@
         {
             for (int i = 0; i < Size; i++)
                 values[i] = driver.ReadColumnValue(i, fields[i], values[i]);
+
+            // if we are caching then we need to save a copy of this row of data values
+            if (Cached)
+                cachedValues.Add((IMySqlValue[])values.Clone());
+
+            // we don't need to worry about caching the following since you won't have output
+            // params with TableDirect commands
             if (outputParms)
             {
                 bool rowExists = driver.FetchDataRow(statementId, fields.Length);

=== added file 'MySql.Data/Provider/Source/TableCache.cs'
--- a/MySql.Data/Provider/Source/TableCache.cs	1970-01-01 00:00:00 +0000
+++ b/MySql.Data/Provider/Source/TableCache.cs	2011-04-08 17:04:15 +0000
@@ -0,0 +1,140 @@
+// Copyright (c) 2011 Oracle Inc.
+//
+// MySQL Connector/NET is licensed under the terms of the GPLv2
+// <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most 
+// MySQL Connectors. There are special exceptions to the terms and 
+// conditions of the GPLv2 as it is applied to this software, see the 
+// FLOSS License Exception
+// <http://www.mysql.com/about/legal/licensing/foss-exception.html>.
+//
+// This program is free software; you can redistribute it and/or modify 
+// it under the terms of the GNU General Public License as published 
+// by the Free Software Foundation; version 2 of the License.
+//
+// This program is distributed in the hope that it will be useful, but 
+// WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 
+// or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License 
+// for more details.
+//
+// You should have received a copy of the GNU General Public License along 
+// with this program; if not, write to the Free Software Foundation, Inc., 
+// 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
+
+using System;
+using System.Collections;
+using System.Data;
+using System.Collections.Generic;
+using MySql.Data.MySqlClient.Properties;
+using System.Diagnostics;
+using System.Text;
+using System.Globalization;
+
+namespace MySql.Data.MySqlClient
+{
+    internal class TableCache
+    {
+        private static BaseTableCache cache;
+
+        static TableCache()
+        {
+            cache = new BaseTableCache(480 /* 8 hour max by default */);
+        }
+
+        public static void AddToCache(string commandText, ResultSet resultSet)
+        {
+            cache.AddToCache(commandText, resultSet);
+        }
+
+        public static ResultSet RetrieveFromCache(string commandText, int cacheAge)
+        {
+            return (ResultSet)cache.RetrieveFromCache(commandText, cacheAge);
+        }
+
+        public static void RemoveFromCache(string commandText)
+        {
+            cache.RemoveFromCache(commandText);
+        }
+
+        public static void DumpCache()
+        {
+            cache.Dump();
+        }
+    }
+
+    public class BaseTableCache
+    {
+        protected int MaxCacheAge;
+        private Dictionary<string, CacheEntry> cache = new Dictionary<string, CacheEntry>();
+
+        public BaseTableCache(int maxCacheAge)
+        {
+            MaxCacheAge = maxCacheAge;
+        }
+
+        public virtual void AddToCache(string commandText, object resultSet)
+        {
+            CleanCache();
+            CacheEntry entry = new CacheEntry();
+            entry.CacheTime = DateTime.Now;
+            entry.CacheElement = resultSet;
+            lock (cache)
+            {
+                if (cache.ContainsKey(commandText)) return;
+                cache.Add(commandText, entry);
+            }
+        }
+
+        public virtual object RetrieveFromCache(string commandText, int cacheAge)
+        {
+            CleanCache();
+            lock (cache)
+            {
+                if (!cache.ContainsKey(commandText)) return null;
+                CacheEntry entry = cache[commandText];
+                if (DateTime.Now.Subtract(entry.CacheTime).TotalSeconds > cacheAge) return null;
+                return entry.CacheElement;
+            }
+        }
+
+        public void RemoveFromCache(string commandText)
+        {
+            lock (cache)
+            {
+                if (!cache.ContainsKey(commandText)) return;
+                cache.Remove(commandText);
+            }
+        }
+
+        public virtual void Dump()
+        {
+            lock (cache)
+                cache.Clear();
+        }
+
+        protected virtual void CleanCache()
+        {
+            DateTime now = DateTime.Now;
+            List<string> keysToRemove =new List<string>();
+
+            lock (cache)
+            {
+                foreach (string key in cache.Keys)
+                {
+                    TimeSpan diff = now.Subtract(cache[key].CacheTime);
+                    if (diff.TotalSeconds > MaxCacheAge)
+                        keysToRemove.Add(key);
+                }
+
+                foreach (string key in keysToRemove)
+                    cache.Remove(key);
+            }
+        }
+
+        private struct CacheEntry
+        {
+            public DateTime CacheTime;
+            public object CacheElement;
+        }
+    }
+
+}
\ No newline at end of file

=== modified file 'MySql.Data/Provider/Source/command.cs'
--- a/MySql.Data/Provider/Source/command.cs	2011-03-09 18:20:04 +0000
+++ b/MySql.Data/Provider/Source/command.cs	2011-04-08 17:04:15 +0000
@@ -65,6 +65,8 @@
         private string batchableCommandText;
         CommandTimer commandTimer;
         private bool useDefaultTimeout;
+        private bool shouldCache;
+        private int cacheAge;
 
 		/// <include file='docs/mysqlcommand.xml' path='docs/ctor1/*'/>
 		public MySqlCommand()
@@ -211,7 +213,8 @@
                     commandTimeout = (int)connection.Settings.DefaultCommandTimeout;
                     useDefaultTimeout = false;
                 }
-
+                EnableCaching = connection.Settings.TableCaching;
+                CacheAge = connection.Settings.DefaultTableCacheAge;
 			}
 		}
 
@@ -237,6 +240,18 @@
 			set { curTransaction = value; }
 		}
 
+        public bool EnableCaching
+        {
+            get { return shouldCache; }
+            set { shouldCache = value; }
+        }
+
+        public int CacheAge
+        {
+            get { return cacheAge; }
+            set { cacheAge = value; }
+        }
+
 		/*		/// <include file='docs/mysqlcommand.xml' path='docs/UpdatedRowSource/*'/>
 		#if !CF
 				[Category("Behavior")]
@@ -784,6 +799,8 @@
             clone.useDefaultTimeout = useDefaultTimeout;
             clone.batchableCommandText = batchableCommandText;
             clone.UpdatedRowSource = UpdatedRowSource;
+            clone.EnableCaching = EnableCaching;
+            clone.CacheAge = CacheAge;
 
 			foreach (MySqlParameter p in parameters)
 			{

=== modified file 'MySql.Data/Provider/Source/datareader.cs'
--- a/MySql.Data/Provider/Source/datareader.cs	2010-12-14 15:25:24 +0000
+++ b/MySql.Data/Provider/Source/datareader.cs	2011-04-08 17:04:15 +0000
@@ -875,13 +875,23 @@
 			if (!isOpen)
                 throw new MySqlException(Resources.NextResultIsClosed);
 
+            bool isCaching = command.CommandType == CommandType.TableDirect && command.EnableCaching &&
+                (commandBehavior & CommandBehavior.SequentialAccess) == 0;
+
             // this will clear out any unread data
             if (resultSet != null)
+            {
                 resultSet.Close();
+                if (isCaching)
+                    TableCache.AddToCache(command.CommandText, resultSet);
+            }
 
             // single result means we only return a single resultset.  If we have already
-            // returned one, then we return false;
-            if (resultSet != null && (commandBehavior & CommandBehavior.SingleResult) != 0)
+            // returned one, then we return false
+            // TableDirect is basically a select * from a single table so it will generate
+            // a single result also
+            if (resultSet != null && 
+                ((commandBehavior & CommandBehavior.SingleResult) != 0 || isCaching))
                 return false;
 
             // next load up the next resultset if any
@@ -890,9 +900,18 @@
                 do
                 {
                     resultSet = null;
-                    resultSet = driver.NextResult(Statement.StatementId);
-                    if (resultSet == null) return false;
-                    if (resultSet.IsOutputParameters) return false;
+                    // if we are table caching, then try to retrieve the resultSet from the cache
+                    if (isCaching)
+                        resultSet = TableCache.RetrieveFromCache(command.CommandText, 
+                            command.CacheAge);
+
+                    if (resultSet == null)
+                    {
+                        resultSet = driver.NextResult(Statement.StatementId);
+                        if (resultSet == null) return false;
+                        if (resultSet.IsOutputParameters) return false;
+                        resultSet.Cached = isCaching;
+                    }
 
                     if (resultSet.Size == 0)
                     {

=== modified file 'MySql.Data/Tests/MySql.Data.Tests.csproj'
--- a/MySql.Data/Tests/MySql.Data.Tests.csproj	2010-12-06 21:35:13 +0000
+++ b/MySql.Data/Tests/MySql.Data.Tests.csproj	2011-04-08 17:04:15 +0000
@@ -105,6 +105,7 @@
     <Compile Include="Source\ConnectionStringBuilder.cs" />
     <Compile Include="Source\ConnectionTests.cs" />
     <Compile Include="Source\BulkLoading.cs" />
+    <Compile Include="Source\TableCaching.cs" />
     <Compile Include="Source\SqlServerMode.cs" />
     <Compile Include="Source\Logging.cs" />
     <Compile Include="Source\ScriptExecution.cs" />

=== added file 'MySql.Data/Tests/Source/TableCaching.cs'
--- a/MySql.Data/Tests/Source/TableCaching.cs	1970-01-01 00:00:00 +0000
+++ b/MySql.Data/Tests/Source/TableCaching.cs	2011-04-08 17:04:15 +0000
@@ -0,0 +1,141 @@
+// Copyright (c) 2004-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
+//
+// MySQL Connector/NET is licensed under the terms of the GPLv2
+// <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most 
+// MySQL Connectors. There are special exceptions to the terms and 
+// conditions of the GPLv2 as it is applied to this software, see the 
+// FLOSS License Exception
+// <http://www.mysql.com/about/legal/licensing/foss-exception.html>.
+//
+// This program is free software; you can redistribute it and/or modify 
+// it under the terms of the GNU General Public License as published 
+// by the Free Software Foundation; version 2 of the License.
+//
+// This program is distributed in the hope that it will be useful, but 
+// WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 
+// or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License 
+// for more details.
+//
+// You should have received a copy of the GNU General Public License along 
+// with this program; if not, write to the Free Software Foundation, Inc., 
+// 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
+
+using System;
+using System.Data;
+using System.IO;
+using NUnit.Framework;
+using System.Collections;
+using System.Collections.Generic;
+using System.Diagnostics;
+using System.Threading;
+
+namespace MySql.Data.MySqlClient.Tests
+{
+	[TestFixture]
+	public class TableCaching : BaseTest
+	{
+        [Test]
+        public void SimpleTableCaching()
+        {
+            execSQL("CREATE TABLE test (id INT, name VARCHAR(20), name2 VARCHAR(20))");
+            execSQL("INSERT INTO test VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
+
+            MySqlTrace.Listeners.Clear();
+            MySqlTrace.Switch.Level = SourceLevels.All;
+            GenericListener listener = new GenericListener();
+            MySqlTrace.Listeners.Add(listener);
+
+            string connStr = GetConnectionString(true) + ";logging=true;table cache=true";
+            using (MySqlConnection c = new MySqlConnection(connStr))
+            {
+                c.Open();
+
+                MySqlCommand cmd = new MySqlCommand("test", c);
+                cmd.CommandType = CommandType.TableDirect;
+                ConsumeReader(cmd);
+                // now run the query again but this time it shouldn't generate a call to the database
+                ConsumeReader(cmd);
+            }
+
+            Assert.AreEqual(1, listener.Find("Resultset Opened: field(s) = 3"));
+        }
+
+        [Test]
+        public void ConnectionStringExpiry()
+        {
+            execSQL("CREATE TABLE test3 (id INT, name VARCHAR(20), name2 VARCHAR(20))");
+            execSQL("INSERT INTO test3 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
+
+            MySqlTrace.Listeners.Clear();
+            MySqlTrace.Switch.Level = SourceLevels.All;
+            GenericListener listener = new GenericListener();
+            MySqlTrace.Listeners.Add(listener);
+
+            string connStr = GetConnectionString(true) + ";logging=true;table cache=true;default table cache age=1";
+            using (MySqlConnection c = new MySqlConnection(connStr))
+            {
+                c.Open();
+
+                MySqlCommand cmd = new MySqlCommand("test3", c);
+                cmd.CommandType = CommandType.TableDirect;
+                ConsumeReader(cmd);
+                Thread.Sleep(1000);
+                // now run the query again but this time it should generate a call to the database
+                // since our next query is past the cache age of 1 second
+                ConsumeReader(cmd);
+            }
+
+            Assert.AreEqual(2, listener.Find("Resultset Opened: field(s) = 3"));
+        }
+
+        [Test]
+        public void SettingAgeOnCommand()
+        {
+            execSQL("CREATE TABLE test2 (id INT, name VARCHAR(20), name2 VARCHAR(20))");
+            execSQL("INSERT INTO test2 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
+
+            MySqlTrace.Listeners.Clear();
+            MySqlTrace.Switch.Level = SourceLevels.All;
+            GenericListener listener = new GenericListener();
+            MySqlTrace.Listeners.Add(listener);
+
+            string connStr = GetConnectionString(true) + ";logging=true;table cache=true;default table cache age=1";
+            using (MySqlConnection c = new MySqlConnection(connStr))
+            {
+                c.Open();
+
+                MySqlCommand cmd = new MySqlCommand("test2", c);
+                cmd.CommandType = CommandType.TableDirect;
+                cmd.CacheAge = 20;
+                ConsumeReader(cmd);
+                Thread.Sleep(1000);
+                // now run the query again but this time it shouldn't generate a call to the database
+                // since we have overriden the connection string cache age of 1 second and set it
+                // to 20 seconds on our command
+                ConsumeReader(cmd);
+            }
+
+            Assert.AreEqual(1, listener.Find("Resultset Opened: field(s) = 3"));
+        }
+
+        private void ConsumeReader(MySqlCommand cmd)
+        {
+            using (MySqlDataReader reader = cmd.ExecuteReader())
+            {
+                reader.Read();
+                Assert.AreEqual(1, reader.GetInt32(0));
+                Assert.AreEqual("boo", reader.GetString(1));
+                Assert.AreEqual("hoo", reader.GetString(2));
+                reader.Read();
+                Assert.AreEqual(2, reader.GetInt32(0));
+                Assert.AreEqual("first", reader.GetString(1));
+                Assert.AreEqual("last", reader.GetString(2));
+                reader.Read();
+                Assert.AreEqual(3, reader.GetInt32(0));
+                Assert.AreEqual("fred", reader.GetString(1));
+                Assert.AreEqual("flintstone", reader.GetString(2));
+                Assert.IsFalse(reader.Read());
+            }
+        }
+    }
+}


Attachment: [text/bzr-bundle] bzr/reggie.burnett@oracle.com-20110408170415-pm6vrzqvqeez9940.bundle
Thread
bzr commit into connector-net-trunk branch (reggie.burnett:968) Reggie Burnett8 Apr
  • Re: bzr commit into connector-net-trunk branch (reggie.burnett:968)Julio Casal8 Apr
    • Re: bzr commit into connector-net-trunk branch (reggie.burnett:968)Reggie Burnett8 Apr