#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
