/// <summary>
/// Creates indexes over data in an ESE database.
/// </summary>
/// <remarks>
/// When creating multiple indexes (i.e. with numIndexCreates
/// greater than 1) this method MUST be called
/// outside of any transactions and with exclusive access to the
/// table. The JET_TABLEID returned by "Api.JetCreateTable"
/// will have exlusive access or the table can be opened for
/// exclusive access by passing <see cref="OpenTableGrbit.DenyRead"/>
/// to <see cref="Api.JetOpenTable"/>.
/// </remarks>
/// <param name="sesid">The session to use.</param>
/// <param name="tableid">The table to create the index on.</param>
/// <param name="indexcreates">Array of objects describing the indexes to be created.</param>
/// <param name="numIndexCreates">Number of index description objects.</param>
public static void JetCreateIndex4(
JET_SESID sesid,
JET_TABLEID tableid,
JET_INDEXCREATE[] indexcreates,
int numIndexCreates)
{
Api.Check(Api.Impl.JetCreateIndex4(sesid, tableid, indexcreates, numIndexCreates));
}
public void Setup()
{
this.managed = new JET_INDEXCREATE()
{
szIndexName = "index",
szKey = "+foo\0-bar\0\0",
cbKey = 8,
grbit = CreateIndexGrbit.IndexSortNullsHigh,
ulDensity = 100,
pidxUnicode = null,
cbVarSegMac = 200,
rgconditionalcolumn = null,
cConditionalColumn = 0,
};
this.native = this.managed.GetNativeIndexcreate();
}
public void JetCreateIndex4SpaceHintsUnicodeIndex2()
{
if (!EsentVersion.SupportsWindows8Features)
{
return;
}
Api.JetBeginTransaction(this.sesid);
const string IndexName = "another_index";
const string IndexDescription = "-TestColumn\0\0";
const string LocaleName = "en-US";
var spacehintsIndex = new JET_SPACEHINTS()
{
ulInitialDensity = 33,
cbInitial = 4096,
grbit = SpaceHintsGrbit.CreateHintAppendSequential | SpaceHintsGrbit.RetrieveHintTableScanForward,
ulMaintDensity = 44,
ulGrowth = 144,
cbMinExtent = 1024 * 1024,
cbMaxExtent = 3 * 1024 * 1024,
};
var unicode = new JET_UNICODEINDEX()
{
szLocaleName = LocaleName,
dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.None),
};
var indexcreate = new JET_INDEXCREATE
{
szIndexName = IndexName,
szKey = IndexDescription,
cbKey = IndexDescription.Length,
pidxUnicode = unicode,
grbit = CreateIndexGrbit.IndexIgnoreAnyNull,
pSpaceHints = spacehintsIndex,
};
Windows8Api.JetCreateIndex4(this.sesid, this.tableid, new[] { indexcreate }, 1);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetSetCurrentIndex(this.sesid, this.tableid, IndexName);
}
public void VerifyJetIndexInfoLocaleName()
{
if (!EsentVersion.SupportsWindows8Features)
{
return;
}
Api.JetBeginTransaction(this.sesid);
const string IndexName = "localizedIndex";
const string IndexDescription = "-TestColumn\0\0";
const string LocaleName = "pt-bR";
var unicode = new JET_UNICODEINDEX()
{
szLocaleName = LocaleName,
dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.None),
};
var indexcreate = new JET_INDEXCREATE
{
szIndexName = IndexName,
szKey = IndexDescription,
cbKey = IndexDescription.Length,
pidxUnicode = unicode,
grbit = CreateIndexGrbit.IndexIgnoreAnyNull,
ulDensity = 100,
};
Windows8Api.JetCreateIndex4(this.sesid, this.tableid, new[] { indexcreate }, 1);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
string localeNameOut;
Api.JetGetIndexInfo(this.sesid, this.dbid, this.table, IndexName, out localeNameOut, Windows8IdxInfo.LocaleName);
Assert.IsNotNull(localeNameOut);
Assert.AreEqual(LocaleName, localeNameOut, true);
localeNameOut = null;
Api.JetGetTableIndexInfo(this.sesid, this.tableid, IndexName, out localeNameOut, Windows8IdxInfo.LocaleName);
Assert.IsNotNull(localeNameOut);
Assert.AreEqual(LocaleName, localeNameOut, true);
Api.JetSetCurrentIndex(this.sesid, this.tableid, IndexName);
}
/// <summary>
/// Creates indexes over data in an ESE database.
/// </summary>
/// <param name="sesid">The session to use.</param>
/// <param name="tableid">The table to create the index on.</param>
/// <param name="indexcreates">Array of objects describing the indexes to be created.</param>
/// <param name="numIndexCreates">Number of index description objects.</param>
/// <returns>An error code.</returns>
public int JetCreateIndex4(
JET_SESID sesid,
JET_TABLEID tableid,
JET_INDEXCREATE[] indexcreates,
int numIndexCreates)
{
TraceFunctionCall("JetCreateIndex4");
this.CheckSupportsWindows8Features("JetCreateIndex4");
CheckNotNull(indexcreates, "indexcreates");
CheckNotNegative(numIndexCreates, "numIndexCreates");
if (numIndexCreates > indexcreates.Length)
{
throw new ArgumentOutOfRangeException(
"numIndexCreates", numIndexCreates, "numIndexCreates is larger than the number of indexes passed in");
}
return CreateIndexes3(sesid, tableid, indexcreates, numIndexCreates);
}
public void CreateIndex2()
{
Api.JetBeginTransaction(this.sesid);
const string IndexName = "another_index";
const string IndexDescription = "-TestColumn\0\0";
var indexcreate = new JET_INDEXCREATE
{
szIndexName = IndexName,
szKey = IndexDescription,
cbKey = IndexDescription.Length,
grbit = CreateIndexGrbit.IndexIgnoreAnyNull,
ulDensity = 100,
};
Api.JetCreateIndex2(this.sesid, this.tableid, new[] { indexcreate }, 1);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetSetCurrentIndex(this.sesid, this.tableid, IndexName);
}
/// <summary>
/// Creates a single index with the specified definition in the table
/// underlying this table definition
/// </summary>
/// <param name="indexDefinition">The index definition.</param>
public void CreateIndex(IndexDefinition indexDefinition)
{
lock (this.database.IsamSession)
{
using (IsamTransaction trx = new IsamTransaction(this.database.IsamSession))
{
// open the table
JET_TABLEID tableid;
Api.JetOpenTable(
this.database.IsamSession.Sesid,
this.database.Dbid,
this.name,
null,
0,
OpenTableGrbit.None,
out tableid);
// add the new index to the table
JET_INDEXCREATE[] indexcreates = new JET_INDEXCREATE[1] {
new JET_INDEXCREATE()
};
indexcreates[0].szIndexName = indexDefinition.Name;
indexcreates[0].szKey = DatabaseCommon.IndexKeyFromIndexDefinition(indexDefinition);
indexcreates[0].cbKey = indexcreates[0].szKey.Length;
indexcreates[0].grbit = DatabaseCommon.GrbitFromIndexDefinition(indexDefinition);
indexcreates[0].ulDensity = indexDefinition.Density;
indexcreates[0].pidxUnicode = new JET_UNICODEINDEX();
indexcreates[0].pidxUnicode.lcid = indexDefinition.CultureInfo.LCID;
indexcreates[0].pidxUnicode.dwMapFlags = Converter.MapFlagsFromUnicodeIndexFlags(Converter.UnicodeFlagsFromCompareOptions(indexDefinition.CompareOptions));
indexcreates[0].rgconditionalcolumn = DatabaseCommon.ConditionalColumnsFromIndexDefinition(indexDefinition);
indexcreates[0].cConditionalColumn = indexcreates[0].rgconditionalcolumn.Length;
indexcreates[0].cbKeyMost = indexDefinition.MaxKeyLength;
Api.JetCreateIndex2(this.database.IsamSession.Sesid, tableid, indexcreates, indexcreates.Length);
// commit our change
Api.JetCloseTable(this.database.IsamSession.Sesid, tableid);
trx.Commit();
DatabaseCommon.SchemaUpdateID++;
}
}
}
public void GetIndexInformationOneIndexWithCompareOptions()
{
const string Indexname = "myindex";
const string Indexdef = "-unicode\0\0";
CultureInfo currentCulture = CultureInfo.CurrentCulture;
var pidxUnicode = new JET_UNICODEINDEX
{
lcid = EseInteropTestHelper.CultureInfoGetLcid(currentCulture),
dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.IgnoreSymbols | CompareOptions.IgnoreCase),
};
var indexcreate = new JET_INDEXCREATE
{
szIndexName = Indexname,
szKey = Indexdef,
cbKey = Indexdef.Length,
grbit = CreateIndexGrbit.IndexDisallowNull,
pidxUnicode = pidxUnicode,
};
Api.JetBeginTransaction(this.sesid);
Api.JetCreateIndex2(this.sesid, this.tableid, new[] { indexcreate }, 1);
IEnumerable <IndexInfo> indexes = Api.GetTableIndexes(this.sesid, this.tableid);
// There should be only one index
IndexInfo info = indexes.Single();
Assert.AreEqual(Indexname, info.Name);
Assert.AreEqual(CreateIndexGrbit.IndexDisallowNull, info.Grbit);
Assert.AreEqual(1, info.IndexSegments.Count);
Assert.AreEqual("unicode", info.IndexSegments[0].ColumnName, true);
Assert.IsFalse(info.IndexSegments[0].IsAscending);
Assert.AreEqual(JET_coltyp.LongText, info.IndexSegments[0].Coltyp);
Assert.IsFalse(info.IndexSegments[0].IsASCII);
Assert.AreEqual(CompareOptions.IgnoreSymbols | CompareOptions.IgnoreCase, info.CompareOptions);
Api.JetRollback(this.sesid, RollbackTransactionGrbit.None);
}
private void CreateSchema(Instance instance)
{
using (var session = new Session(instance))
{
JET_DBID dbid;
Api.JetCreateDatabase(session, _database, null, out dbid, CreateDatabaseGrbit.OverwriteExisting);
using (var tx = new Transaction(session))
{
JET_TABLEID tableid;
Api.JetCreateTable(session, dbid, "table", 0, 100, out tableid);
var primaryColumn = new JET_COLUMNDEF
{
coltyp = JET_coltyp.Long
};
var secondaryColumn = new JET_COLUMNDEF
{
coltyp = JET_coltyp.LongBinary,
};
JET_COLUMNID primaryColumnId;
Api.JetAddColumn(session, tableid, "key", primaryColumn, null, 0, out primaryColumnId);
JET_COLUMNID secondaryColumnId;
Api.JetAddColumn(session, tableid, "data", secondaryColumn, null, 0, out secondaryColumnId);
var index = new JET_INDEXCREATE
{
szKey = "+key\0\0",
szIndexName = "by_key",
grbit = CreateIndexGrbit.IndexPrimary,
ulDensity = 90
};
Api.JetCreateIndex(session, tableid, index.szIndexName, index.grbit, index.szKey, index.szKey.Length, index.ulDensity);
tx.Commit(CommitTransactionGrbit.None);
}
}
}
/// <summary>Create the table, it's columns and indices.</summary>
public void CreateTableAndIndices(JET_SESID idSession, JET_DBID idDatabase)
{
JET_TABLEID idTable;
Api.JetCreateTable(idSession, idDatabase, m_tableName, 0, 0, out idTable);
using (var transaction = new Transaction(idSession))
{
// Add the columns
foreach (ColumnInfo ci in m_columns)
{
if (ci.isObsolete)
{
continue; // DatabaseSchemaUpdater can still create obsolete columns because it doesn't use CreateTableAndIndices method.
}
JET_COLUMNID idColumn;
Api.JetAddColumn(idSession, idTable, ci.columnName, ci.attrib.getColumnDef(), null, 0, out idColumn);
}
// Add the indices
foreach (var ind in m_indices.Values)
{
if (ind.attrib.Obsolete)
{
continue; // DatabaseSchemaUpdater can still create obsolete indices because it doesn't use CreateTableAndIndices method.
}
if (ind.hasObsoleteColumns)
{
throw new SerializationException("Error creating index '" + ind.attrib.strName + "': some of the columns being indexed are obsolete.");
}
JET_INDEXCREATE i = ind.attrib.getIndexDef();
Api.JetCreateIndex2(idSession, idTable, new JET_INDEXCREATE[1] {
i
}, 1);
// See http://managedesent.codeplex.com/WorkItem/View.aspx?WorkItemId=5605 for more info.
}
transaction.Commit(CommitTransactionGrbit.LazyFlush);
}
Api.JetCloseTable(idSession, idTable);
}
/// <summary>Add conditional columns data to the supplied <see cref="JET_INDEXCREATE" />.</summary>
/// <param name="ic"></param>
protected void setConditionalColumns(JET_INDEXCREATE ic)
{
if (String.IsNullOrEmpty(condition))
{
ic.cConditionalColumn = 0;
ic.rgconditionalcolumn = null;
return;
}
var res = new List <JET_CONDITIONALCOLUMN>();
var arrTokens = condition.Split(new char[1] {
'\0'
}, StringSplitOptions.None);
foreach (string strToken in arrTokens)
{
JET_CONDITIONALCOLUMN cc = new JET_CONDITIONALCOLUMN();
if ('+' == strToken[0])
{
cc.grbit = ConditionalColumnGrbit.ColumnMustBeNonNull;
}
else if ('-' == strToken[0])
{
cc.grbit = ConditionalColumnGrbit.ColumnMustBeNull;
}
else
{
throw new System.Runtime.Serialization.SerializationException("existence specifier not found in the conditional column token " + strToken);
}
cc.szColumnName = strToken.Substring(1);
res.Add(cc);
}
ic.cConditionalColumn = res.Count;
ic.rgconditionalcolumn = res.ToArray();
}
public override void Create(JET_SESID sessionId, JET_DBID databaseId)
{
var nameColumnCreate = CreateIdColumn(NameColumnName);
var valueColumnCreate = CreateBinaryColumn(ValueColumnName);
var columns = new JET_COLUMNCREATE[] { nameColumnCreate, valueColumnCreate };
var nameIndexKey = CreateIndexKey(NameColumnName);
var indexes = new JET_INDEXCREATE[]
{
CreatePrimaryIndex(nameIndexKey)
};
var tableCreate = CreateTable(TableName, columns, indexes);
Api.JetCreateTableColumnIndex3(sessionId, databaseId, tableCreate);
_nameColumnId = nameColumnCreate.columnid;
_valueColumnId = valueColumnCreate.columnid;
Api.JetCloseTable(sessionId, tableCreate.tableid);
}
public override void Create(JET_SESID sessionId, JET_DBID databaseId)
{
var idColumnCreate = new JET_COLUMNCREATE()
{
szColumnName = IdColumnName,
coltyp = JET_coltyp.Long,
grbit = ColumndefGrbit.ColumnAutoincrement | ColumndefGrbit.ColumnNotNULL
};
var nameColumnCreate = new JET_COLUMNCREATE()
{
szColumnName = NameColumnName,
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
grbit = ColumndefGrbit.ColumnNotNULL
};
var columns = new JET_COLUMNCREATE[] { idColumnCreate, nameColumnCreate };
var idIndexKey = "+" + IdColumnName + "\0\0";
var nameIndexKey = "+" + NameColumnName + "\0\0";
var indexes = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = IdIndexName,
szKey = idIndexKey,
cbKey = idIndexKey.Length,
grbit = CreateIndexGrbit.IndexPrimary | CreateIndexGrbit.IndexUnique | CreateIndexGrbit.IndexDisallowNull,
ulDensity = 80
},
new JET_INDEXCREATE
{
szIndexName = NameIndexName,
szKey = nameIndexKey,
cbKey = nameIndexKey.Length,
grbit = CreateIndexGrbit.IndexUnique | CreateIndexGrbit.IndexDisallowNull | VistaGrbits.IndexDisallowTruncation,
ulDensity = 80,
// this should be 2000 bytes Max after vista
cbKeyMost = SystemParameters.KeyMost
}
};
var tableCreate = new JET_TABLECREATE()
{
szTableName = TableName,
ulPages = 16,
ulDensity = 80,
rgcolumncreate = columns,
cColumns = columns.Length,
rgindexcreate = indexes,
cIndexes = indexes.Length
};
Api.JetCreateTableColumnIndex3(sessionId, databaseId, tableCreate);
_idColumnId = idColumnCreate.columnid;
_nameColumnId = nameColumnCreate.columnid;
Api.JetCloseTable(sessionId, tableCreate.tableid);
}
public IList <Report> GetReports()
{
List <Report> reportsToReturn = new List <Report>();
List <Report> reportsInDatabase = new List <Report>();
using (EsentSession session = new EsentSession(_database)) {
session.Open();
JET_SESID sessionId = session.GetSessionId();
JET_DBID databaseId = session.GetDatabaseId();
JET_TABLEID tableId;
if (DatabaseTableExists(sessionId, databaseId, TABLE_STATUS_REPORT))
{
Api.OpenTable(sessionId, databaseId, TABLE_STATUS_REPORT, OpenTableGrbit.None, out tableId);
//Get all columns in the table
IDictionary <string, JET_COLUMNID> columnDictionary = Api.GetColumnDictionary(sessionId, tableId);
//Create a search index
if (Api.GetTableIndexes(sessionId, tableId).Any(index => index.Name == "EndTimeIndex") == false)
{
var indexKey = "+EndTime\0\0";
JET_INDEXCREATE startDateIndex = new JET_INDEXCREATE {
szKey = indexKey,
cbKey = indexKey.Length,
szIndexName = "EndTimeIndex",
};
Api.JetCreateIndex2(sessionId, tableId, new JET_INDEXCREATE[] { startDateIndex }, 1);
}
Api.JetSetCurrentIndex(sessionId, tableId, "EndTimeIndex");
//Create search keys.
Api.MakeKey(sessionId, tableId, DateTime.Now.AddHours(-2), MakeKeyGrbit.NewKey);
if (Api.TrySeek(sessionId, tableId, SeekGrbit.SeekGE))
{
do
{
Report reportRow = new Report()
{
Id = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["Id"]),
JobId = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["JobId"]),
NodeName = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["NodeName"]),
IPAddress = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["IPAddress"]),
RerfreshMode = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["RefreshMode"]),
OperationType = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["OperationType"]),
Status = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["Status"]),
RebootRequested = Api.RetrieveColumnAsBoolean(sessionId, tableId, columnDictionary["RebootRequested"]).GetValueOrDefault(),
StartTime = Api.RetrieveColumnAsDateTime(sessionId, tableId, columnDictionary["StartTime"]).GetValueOrDefault(),
EndTime = Api.RetrieveColumnAsDateTime(sessionId, tableId, columnDictionary["EndTime"]).GetValueOrDefault(),
LastModifiedTime = Api.RetrieveColumnAsDateTime(sessionId, tableId, columnDictionary["LastModifiedTime"]).GetValueOrDefault(),
LCMVersion = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["LCMVersion"]),
ConfigurationVersion = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["ConfigurationVersion"]),
ReportFormatVersion = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["ReportFormatVersion"]),
Errors = (List <string>)Api.DeserializeObjectFromColumn(sessionId, tableId, columnDictionary["Errors"]),
};
List <string> statusDataList = (List <string>)Api.DeserializeObjectFromColumn(sessionId, tableId, columnDictionary["StatusData"]);
if (statusDataList.Count > 0)
{
reportRow.StatusData = statusDataList[0];
}
;
// Field AdditionalData is only available on WS2016 and WMF 5.1
if (columnDictionary.Keys.Contains("AdditionalData"))
{
reportRow.AdditionalData = Api.RetrieveColumnAsString(sessionId, tableId, columnDictionary["AdditionalData"]);
}
reportsInDatabase.Add(reportRow);
} while (Api.TryMovePrevious(sessionId, tableId));
foreach (string node in GetNamesNodes().Select(node => node.NodeName).ToArray())
{
string NotCompliantResources = string.Empty;
string status = string.Empty;
Report latestReportForNode = reportsInDatabase.Where(r => r.NodeName == node).OrderByDescending(r => r.EndTime).First();
StatusDataElement dataElement = JsonConvert.DeserializeObject <StatusDataElement>(latestReportForNode.StatusData);
if (dataElement.ResourcesNotInDesiredState?.Count != null)
{
StringBuilder sb = new StringBuilder();
foreach (ResourceState resource in dataElement.ResourcesNotInDesiredState)
{
if (sb.Length != 0)
{
sb.Append(';');
sb.Append(resource.ResourceId);
}
else
{
sb.Append(resource.ResourceId);
}
}
latestReportForNode.NotCompliantResources = sb.ToString();
}
else
{
latestReportForNode.NotCompliantResources = string.Empty;
}
reportsToReturn.Add(latestReportForNode);
}
}
else
{
Report report = new Report()
{
Id = string.Empty,
JobId = string.Empty,
NodeName = null,
IPAddress = string.Empty,
RerfreshMode = string.Empty,
OperationType = string.Empty,
Status = "No status repot found for the last 2 hours!",
RebootRequested = false,
StartTime = null,
EndTime = null,
LastModifiedTime = null,
LCMVersion = string.Empty,
ConfigurationVersion = string.Empty,
ReportFormatVersion = string.Empty,
Errors = new List <string>()
{
string.Empty
},
StatusData = string.Empty,
NotCompliantResources = string.Empty
};
reportsToReturn.Add(report);
}
Api.JetCloseTable(sessionId, tableId);
}
else
{
_logger.Log(10091, String.Format("Database table {0} not found!", TABLE_STATUS_REPORT), LogLevel.Warning);
}
}
return(reportsToReturn.OrderBy(r => r.StartTime).ToList());
}
public void Setup()
{
this.directory = SetupHelper.CreateRandomDirectory();
this.database = Path.Combine(this.directory, "database.edb");
this.table = "table";
this.instance = SetupHelper.CreateNewInstance(this.directory);
this.indexcreates = new List <JET_INDEXCREATE>(10);
Api.JetSetSystemParameter(this.instance, JET_SESID.Nil, JET_param.Recovery, 0, "off");
Api.JetInit(ref this.instance);
Api.JetBeginSession(this.instance, out this.sesid, string.Empty, string.Empty);
Api.JetCreateDatabase(this.sesid, this.database, string.Empty, out this.dbid, CreateDatabaseGrbit.None);
Api.JetBeginTransaction(this.sesid);
Api.JetCreateTable(this.sesid, this.dbid, this.table, 0, 100, out this.tableid);
JET_COLUMNID ignored;
var columndef = new JET_COLUMNDEF {
coltyp = JET_coltyp.Text, cp = JET_CP.Unicode
};
Api.JetAddColumn(this.sesid, this.tableid, "C1", columndef, null, 0, out ignored);
Api.JetAddColumn(this.sesid, this.tableid, "C2", columndef, null, 0, out ignored);
Api.JetAddColumn(this.sesid, this.tableid, "C3", columndef, null, 0, out ignored);
Api.JetCreateIndex(this.sesid, this.tableid, "Primary", CreateIndexGrbit.IndexPrimary, "+C1\0\0", 5, 100);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
JET_INDEXCREATE[] indexcreates = new[]
{
// Multi-segment indices cause failures! JET_INDEXLIST.cRecords is inaccurate. Possibly because cRecords is incremented
// as the key segment rows are traversed in the catalog, even though no rows are added
// to the temptable.
// new JET_INDEXCREATE { szIndexName = "Index2", cbKey = 9, szKey = "+C2\0+C3\0\0" },
new JET_INDEXCREATE {
szIndexName = "Index2", cbKey = 5, szKey = "+C2\0\0"
},
new JET_INDEXCREATE {
szIndexName = "Index3", cbKey = 5, szKey = "+C3\0\0", cbVarSegMac = 100
},
};
Api.JetCreateIndex2(this.sesid, this.tableid, indexcreates, indexcreates.Length);
this.indexcreates.Add(indexcreates[0]);
this.indexcreates.Add(indexcreates[1]);
if (EsentVersion.SupportsWindows8Features)
{
var unicode = new JET_UNICODEINDEX()
{
szLocaleName = "pt-br",
dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.None),
};
var indexcreate = new JET_INDEXCREATE
{
szIndexName = "win8BrazilIndex",
szKey = "+C2\0\0",
cbKey = 5,
pidxUnicode = unicode,
grbit = CreateIndexGrbit.IndexIgnoreAnyNull,
ulDensity = 100,
};
Windows8Api.JetCreateIndex4(this.sesid, this.tableid, new[] { indexcreate }, 1);
this.indexcreates.Add(indexcreate);
}
Api.JetCloseTable(this.sesid, this.tableid);
Api.JetOpenTable(this.sesid, this.dbid, this.table, null, 0, OpenTableGrbit.None, out this.tableid);
}
public void JetCreateTableColumnIndex4UnicodeIndex2()
{
if (!EsentVersion.SupportsWindows8Features)
{
return;
}
const string LocaleName = "en-US";
var unicode = new JET_UNICODEINDEX()
{
szLocaleName = LocaleName,
dwMapFlags = Conversions.LCMapFlagsFromCompareOptions(CompareOptions.None),
};
var columncreates = new JET_COLUMNCREATE[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short",
coltyp = JET_coltyp.Short,
cbMax = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext",
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
},
};
const string Index1Name = "firstIndex";
const string Index1Description = "+col1_short\0-col2_longtext\0";
const string Index2Name = "secondIndex";
const string Index2Description = "+col2_longtext\0-col1_short\0";
var indexcreates = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = Index1Name,
szKey = Index1Description,
cbKey = Index1Description.Length + 1,
pidxUnicode = unicode,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
},
new JET_INDEXCREATE
{
szIndexName = Index2Name,
szKey = Index2Description,
cbKey = Index2Description.Length + 1,
pidxUnicode = unicode,
grbit = CreateIndexGrbit.None,
ulDensity = 79,
},
};
var tablecreate = new JET_TABLECREATE()
{
szTableName = "tableBigBang",
ulPages = 23,
ulDensity = 75,
cColumns = columncreates.Length,
rgcolumncreate = columncreates,
rgindexcreate = indexcreates,
cIndexes = indexcreates.Length,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.None,
};
Api.JetBeginTransaction(this.sesid);
Windows8Api.JetCreateTableColumnIndex4(this.sesid, this.dbid, tablecreate);
var tableCreated = new JET_TABLEID()
{
Value = tablecreate.tableid.Value
};
Assert.AreNotEqual <JET_TABLEID>(JET_TABLEID.Nil, tableCreated);
// 1 table, 2 columns, 2 indices = 5 objects.
Assert.AreEqual <int>(tablecreate.cCreated, 5);
Assert.AreNotEqual(tablecreate.rgcolumncreate[0].columnid, JET_COLUMNID.Nil);
Assert.AreNotEqual(tablecreate.rgcolumncreate[1].columnid, JET_COLUMNID.Nil);
Api.JetCloseTable(this.sesid, tableCreated);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
}
public void JetCreateTableColumnIndexSpaceHints()
{
var columncreates = new[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short",
coltyp = JET_coltyp.Short,
cbMax = 2,
pvDefault = BitConverter.GetBytes((short)37),
cbDefault = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext",
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
},
};
const string Index1Name = "firstIndex";
const string Index1Description = "+col1_short\0-col2_longtext\0";
const string Index2Name = "secondIndex";
const string Index2Description = "+col2_longtext\0-col1_short\0";
var spacehintsIndex = new JET_SPACEHINTS()
{
ulInitialDensity = 33,
cbInitial = 4096,
grbit = SpaceHintsGrbit.CreateHintAppendSequential | SpaceHintsGrbit.RetrieveHintTableScanForward,
ulMaintDensity = 44,
ulGrowth = 144,
cbMinExtent = 1024 * 1024,
cbMaxExtent = 3 * 1024 * 1024,
};
var spacehintsSeq = new JET_SPACEHINTS()
{
ulInitialDensity = 33,
cbInitial = 4096,
grbit = SpaceHintsGrbit.CreateHintAppendSequential | SpaceHintsGrbit.RetrieveHintTableScanForward,
ulMaintDensity = 44,
ulGrowth = 144,
cbMinExtent = 1024 * 1024,
cbMaxExtent = 3 * 1024 * 1024,
};
var spacehintsLv = new JET_SPACEHINTS()
{
ulInitialDensity = 33,
cbInitial = 4096,
grbit = SpaceHintsGrbit.CreateHintAppendSequential | SpaceHintsGrbit.RetrieveHintTableScanForward,
ulMaintDensity = 44,
ulGrowth = 144,
cbMinExtent = 1024 * 1024,
cbMaxExtent = 3 * 1024 * 1024,
};
var indexcreates = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = Index1Name,
szKey = Index1Description,
cbKey = Index1Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
pSpaceHints = spacehintsIndex,
},
new JET_INDEXCREATE
{
szIndexName = Index2Name,
szKey = Index2Description,
cbKey = Index2Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 79,
},
};
var tablecreate = new JET_TABLECREATE()
{
szTableName = "tableBigBang",
ulPages = 23,
ulDensity = 75,
cColumns = columncreates.Length,
rgcolumncreate = columncreates,
rgindexcreate = indexcreates,
cIndexes = indexcreates.Length,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.None,
pSeqSpacehints = spacehintsSeq,
pLVSpacehints = spacehintsLv,
};
Api.JetBeginTransaction(this.sesid);
Api.JetCreateTableColumnIndex3(this.sesid, this.dbid, tablecreate);
var tableCreated = new JET_TABLEID()
{
Value = tablecreate.tableid.Value
};
Assert.AreNotEqual <JET_TABLEID>(JET_TABLEID.Nil, tableCreated);
// 1 table, 2 columns, 2 indices = 5 objects.
Assert.AreEqual <int>(tablecreate.cCreated, 5);
Api.JetCloseTable(this.sesid, tableCreated);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
}
public void CreateTableColumnIndex3OnXp()
{
var columncreates = new JET_COLUMNCREATE[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short",
coltyp = JET_coltyp.Short,
cbMax = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext",
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
},
};
const string Index1Name = "firstIndex";
const string Index1Description = "+col1_short\0-col2_longtext\0";
const string Index2Name = "secondIndex";
const string Index2Description = "+col2_longtext\0-col1_short\0";
var indexcreates = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = Index1Name,
szKey = Index1Description,
cbKey = Index1Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
},
new JET_INDEXCREATE
{
szIndexName = Index2Name,
szKey = Index2Description,
cbKey = Index2Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 79,
},
};
var tablecreate = new JET_TABLECREATE()
{
szTableName = "tableBigBang",
ulPages = 23,
ulDensity = 75,
cColumns = columncreates.Length,
rgcolumncreate = columncreates,
rgindexcreate = indexcreates,
cIndexes = indexcreates.Length,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.None,
};
string directory = SetupHelper.CreateRandomDirectory();
string database = Path.Combine(directory, "test.db");
using (var instance = new Instance("XpCreateTableColumnIndex3"))
{
instance.Parameters.Recovery = false;
instance.Parameters.NoInformationEvent = true;
instance.Parameters.MaxTemporaryTables = 0;
instance.Init();
using (var session = new Session(instance))
{
JET_DBID dbid;
Api.JetCreateDatabase(session, database, String.Empty, out dbid, CreateDatabaseGrbit.None);
using (var transaction = new Transaction(session))
{
Api.JetCreateTableColumnIndex3(session, dbid, tablecreate);
Assert.AreNotEqual(JET_TABLEID.Nil, tablecreate.tableid);
// 1 table, 2 columns, 2 indices = 5 objects.
Assert.AreEqual(tablecreate.cCreated, 5);
Assert.AreNotEqual(tablecreate.rgcolumncreate[0].columnid, JET_COLUMNID.Nil);
Assert.AreNotEqual(tablecreate.rgcolumncreate[1].columnid, JET_COLUMNID.Nil);
Api.JetCloseTable(session, tablecreate.tableid);
transaction.Commit(CommitTransactionGrbit.LazyFlush);
}
}
}
}
public void CreateTableColumnIndex4OnWindows7ThrowsException()
{
if (!EsentVersion.SupportsWindows7Features)
{
return;
}
var columncreates = new JET_COLUMNCREATE[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short",
coltyp = JET_coltyp.Short,
cbMax = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext",
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
},
};
const string Index1Name = "firstIndex";
const string Index1Description = "+col1_short\0-col2_longtext\0";
const string Index2Name = "secondIndex";
const string Index2Description = "+col2_longtext\0-col1_short\0";
var indexcreates = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = Index1Name,
szKey = Index1Description,
cbKey = Index1Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
},
new JET_INDEXCREATE
{
szIndexName = Index2Name,
szKey = Index2Description,
cbKey = Index2Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 79,
},
};
var tablecreate = new JET_TABLECREATE()
{
szTableName = "tableBigBang",
ulPages = 23,
ulDensity = 75,
cColumns = columncreates.Length,
rgcolumncreate = columncreates,
rgindexcreate = indexcreates,
cIndexes = indexcreates.Length,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.None,
};
string directory = SetupHelper.CreateRandomDirectory();
string database = Path.Combine(directory, "test.db");
using (var instance = new Instance("Windows7CreateTableColumnIndex4"))
{
instance.Parameters.Recovery = false;
instance.Parameters.NoInformationEvent = true;
instance.Parameters.MaxTemporaryTables = 0;
instance.Init();
using (var session = new Session(instance))
{
JET_DBID dbid;
Api.JetCreateDatabase(session, database, string.Empty, out dbid, CreateDatabaseGrbit.None);
using (var transaction = new Transaction(session))
{
try
{
Windows8Api.JetCreateTableColumnIndex4(session, dbid, tablecreate);
Assert.Fail("JetCreateTableColumnIndex4() is supposed to throw an exception on Win7.");
}
catch (InvalidOperationException)
{
}
}
}
}
}
public void Setup()
{
this.directory = SetupHelper.CreateRandomDirectory();
this.database = Path.Combine(this.directory, "database.edb");
this.instance = SetupHelper.CreateNewInstance(this.directory);
Api.JetSetSystemParameter(this.instance, JET_SESID.Nil, JET_param.Recovery, 0, "off");
Api.JetInit(ref this.instance);
Api.JetBeginSession(this.instance, out this.sesid, string.Empty, string.Empty);
Api.JetCreateDatabase(this.sesid, this.database, string.Empty, out this.dbid, CreateDatabaseGrbit.None);
Api.JetBeginTransaction(this.sesid);
this.columncreatesBase = new JET_COLUMNCREATE[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short",
coltyp = JET_coltyp.Short,
grbit = ColumndefGrbit.ColumnFixed,
cbMax = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext",
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
},
};
this.columncreatesChild = new JET_COLUMNCREATE[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short_child",
coltyp = JET_coltyp.Short,
cbMax = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext_child",
coltyp = JET_coltyp.LongText,
grbit = ColumndefGrbit.ColumnTagged,
cp = JET_CP.Unicode,
},
};
const string Index1Name = "firstIndex";
const string Index1Description = "+col1_short\0-col2_longtext\0";
const string Index2Name = "secondIndex";
const string Index2Description = "+col2_longtext\0-col1_short\0";
var indexcreates = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = Index1Name,
szKey = Index1Description,
cbKey = Index1Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
},
new JET_INDEXCREATE
{
szIndexName = Index2Name,
szKey = Index2Description,
cbKey = Index2Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 79,
},
};
this.tablecreateTemplate = new JET_TABLECREATE()
{
szTableName = "tableBase",
ulPages = 23,
ulDensity = 75,
cColumns = this.columncreatesBase.Length,
rgcolumncreate = this.columncreatesBase,
rgindexcreate = indexcreates,
cIndexes = indexcreates.Length,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.TemplateTable,
};
Api.JetBeginTransaction(this.sesid);
Api.JetCreateTableColumnIndex3(this.sesid, this.dbid, this.tablecreateTemplate);
var columndef = new JET_COLUMNDEF()
{
cp = JET_CP.Unicode,
coltyp = JET_coltyp.LongText,
};
var tableCreated = new JET_TABLEID()
{
Value = this.tablecreateTemplate.tableid.Value
};
Api.JetCloseTable(this.sesid, tableCreated);
this.tablecreateChild = new JET_TABLECREATE()
{
szTableName = "tableChild",
szTemplateTableName = "tableBase",
ulPages = 23,
ulDensity = 75,
rgcolumncreate = this.columncreatesChild,
cColumns = this.columncreatesChild.Length,
rgindexcreate = null,
cIndexes = 0,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.None,
};
Api.JetCreateTableColumnIndex3(this.sesid, this.dbid, this.tablecreateChild);
this.tableidChild = new JET_TABLEID()
{
Value = this.tablecreateChild.tableid.Value
};
Api.JetCloseTable(this.sesid, this.tableidChild);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetOpenTable(this.sesid, this.dbid, this.tablecreateTemplate.szTableName, null, 0, OpenTableGrbit.None, out this.tableidParent);
Api.JetOpenTable(this.sesid, this.dbid, this.tablecreateChild.szTableName, null, 0, OpenTableGrbit.None, out this.tableidChild);
}
private void JetCreateTableColumnIndex()
{
Console.WriteLine("\tJetCreateTableColumnIndex()");
var columncreates = new JET_COLUMNCREATE[13];
for (int i = 0; i < columncreates.Length; ++i)
{
columncreates[i] = new JET_COLUMNCREATE();
}
columncreates[0] = new JET_COLUMNCREATE {
szColumnName = "recordID", coltyp = JET_coltyp.Long
};
columncreates[1].szColumnName = "tagged";
columncreates[1].coltyp = VistaColtyp.LongLong;
columncreates[1].grbit = ColumndefGrbit.ColumnTagged;
columncreates[2].szColumnName = "separated_lv";
columncreates[2].coltyp = JET_coltyp.LongBinary;
columncreates[3].szColumnName = "compressed_unicode";
columncreates[3].coltyp = JET_coltyp.LongText;
columncreates[3].cp = JET_CP.Unicode;
columncreates[3].grbit = Windows7Grbits.ColumnCompressed;
columncreates[4].szColumnName = "compressed_ascii";
columncreates[4].coltyp = JET_coltyp.LongText;
columncreates[4].cp = JET_CP.ASCII;
columncreates[4].grbit = Windows7Grbits.ColumnCompressed;
columncreates[5].szColumnName = "compressed_binary";
columncreates[5].coltyp = JET_coltyp.LongBinary;
columncreates[5].grbit = Windows7Grbits.ColumnCompressed;
columncreates[6].szColumnName = "columntodelete";
columncreates[6].coltyp = JET_coltyp.Long;
columncreates[7].szColumnName = "autoinc";
columncreates[7].coltyp = JET_coltyp.Long;
columncreates[7].grbit = ColumndefGrbit.ColumnAutoincrement;
columncreates[8].szColumnName = "version";
columncreates[8].coltyp = JET_coltyp.Long;
columncreates[8].grbit = ColumndefGrbit.ColumnVersion;
columncreates[9].szColumnName = "unicode";
columncreates[9].coltyp = JET_coltyp.LongText;
columncreates[9].cp = JET_CP.Unicode;
columncreates[9].pvDefault = Encoding.Unicode.GetBytes(
"This is the default value for the unicode column");
columncreates[9].cbDefault = columncreates[9].pvDefault.Length;
columncreates[10].szColumnName = "ascii";
columncreates[10].coltyp = JET_coltyp.LongText;
columncreates[10].cp = JET_CP.ASCII;
columncreates[10].pvDefault = Encoding.ASCII.GetBytes("This is the default value for the ASCII column");
columncreates[10].cbDefault = columncreates[10].pvDefault.Length;
columncreates[11].szColumnName = "columntodelete2";
columncreates[11].coltyp = JET_coltyp.Long;
columncreates[12].szColumnName = "fixed";
columncreates[12].coltyp = VistaColtyp.LongLong;
columncreates[12].grbit = ColumndefGrbit.ColumnFixed;
var primarySpaceHints = new JET_SPACEHINTS();
primarySpaceHints.ulInitialDensity = 100;
primarySpaceHints.cbInitial = 512 * 1024;
var secondarySpaceHints = new JET_SPACEHINTS();
secondarySpaceHints.ulInitialDensity = 80;
secondarySpaceHints.cbInitial = 96 * 1024;
secondarySpaceHints.ulGrowth = 150;
secondarySpaceHints.cbMinExtent = 64 * 1024;
secondarySpaceHints.cbMaxExtent = 256 * 1024;
var indexcreates = new JET_INDEXCREATE[14];
for (int i = 0; i < indexcreates.Length; ++i)
{
indexcreates[i] = new JET_INDEXCREATE();
}
indexcreates[0].szIndexName = "index_recordID";
indexcreates[0].szKey = "+recordID\0\0";
indexcreates[0].cbKey = indexcreates[0].szKey.Length;
indexcreates[0].grbit = CreateIndexGrbit.IndexPrimary;
indexcreates[0].pSpaceHints = primarySpaceHints;
indexcreates[1] = this.MakeIndexcreate("tagged");
indexcreates[2] = this.MakeIndexcreate("separated_lv");
indexcreates[3] = this.MakeIndexcreate("compressed_unicode");
indexcreates[4] = this.MakeIndexcreate("compressed_ascii");
indexcreates[5] = this.MakeIndexcreate("compressed_binary");
indexcreates[6] = this.MakeIndexcreate("autoinc");
indexcreates[7] = this.MakeIndexcreate("version");
indexcreates[8] = this.MakeIndexcreate("unicode");
indexcreates[9] = this.MakeIndexcreate("ascii");
indexcreates[10] = this.MakeIndexcreate("fixed");
indexcreates[11].szIndexName = "secondary";
indexcreates[11].szKey = "+autoinc\0+compressed_unicode\0+recordID\0\0";
indexcreates[11].cbKey = indexcreates[11].szKey.Length;
indexcreates[11].grbit = CreateIndexGrbit.IndexUnique;
indexcreates[11].pSpaceHints = secondarySpaceHints;
indexcreates[12].szIndexName = "indextodelete";
indexcreates[12].szKey = "+autoinc\0+recordID\0\0";
indexcreates[12].cbKey = indexcreates[12].szKey.Length;
indexcreates[13] = this.MakeIndexcreate("columntodelete2");
var tablecreate = new JET_TABLECREATE();
tablecreate.szTableName = this.table;
tablecreate.ulPages = 1;
tablecreate.ulDensity = 100;
tablecreate.rgcolumncreate = columncreates;
tablecreate.cColumns = tablecreate.rgcolumncreate.Length;
tablecreate.rgindexcreate = indexcreates;
tablecreate.cIndexes = tablecreate.rgindexcreate.Length;
Api.JetBeginTransaction(this.sesid);
Api.JetCreateTableColumnIndex3(this.sesid, this.dbid, tablecreate);
Api.JetCloseTable(this.sesid, tablecreate.tableid);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
}
public void JetCreateTableColumnIndexWithInvalidIndexName()
{
var columncreates = new JET_COLUMNCREATE[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short",
coltyp = JET_coltyp.Short,
cbMax = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext",
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
},
new JET_COLUMNCREATE()
{
szColumnName = "col1_short2",
coltyp = JET_coltyp.Short,
cbMax = 2,
},
};
const string Index1Name = "firstIndex";
const string Index1Description = "+col1_short\0-col2_longtext\0";
const string Index2Name = "secondIndex";
const string Index2Description = "+col2_longtext\0-col1_short\0";
const string Index3Name = "[BAD!NAME]";
const string Index3Description = "+col1_short2\0-col2_longtext\0";
var indexcreates = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = Index1Name,
szKey = Index1Description,
cbKey = Index1Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
},
new JET_INDEXCREATE
{
szIndexName = Index2Name,
szKey = Index2Description,
cbKey = Index2Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
},
new JET_INDEXCREATE
{
szIndexName = Index3Name,
szKey = Index3Description,
cbKey = Index3Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
},
};
var tablecreate = new JET_TABLECREATE()
{
szTableName = "tableBigBang",
ulPages = 23,
ulDensity = 75,
cColumns = columncreates.Length,
rgcolumncreate = columncreates,
rgindexcreate = indexcreates,
cIndexes = indexcreates.Length,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.None,
};
Api.JetBeginTransaction(this.sesid);
bool hitException = false;
try
{
Api.JetCreateTableColumnIndex3(this.sesid, this.dbid, tablecreate);
}
catch (EsentInvalidNameException)
{
hitException = true;
}
Assert.IsTrue(hitException);
Assert.AreEqual(JET_err.Success, tablecreate.rgindexcreate[0].err);
Assert.AreEqual(JET_err.Success, tablecreate.rgindexcreate[1].err);
Assert.AreEqual(JET_err.InvalidName, tablecreate.rgindexcreate[2].err);
Api.JetRollback(this.sesid, RollbackTransactionGrbit.None);
}
public override void Create(JET_SESID sessionId, JET_DBID databaseId)
{
var projectColumnCreate = new JET_COLUMNCREATE()
{
szColumnName = ProjectColumnName,
coltyp = JET_coltyp.Long,
grbit = ColumndefGrbit.ColumnNotNULL
};
var documentColumnCreate = new JET_COLUMNCREATE()
{
szColumnName = DocumentColumnName,
coltyp = JET_coltyp.Long,
grbit = ColumndefGrbit.ColumnNotNULL
};
var nameColumnCreate = new JET_COLUMNCREATE()
{
szColumnName = NameColumnName,
coltyp = JET_coltyp.Long,
grbit = ColumndefGrbit.ColumnNotNULL
};
var valueColumnCreate = new JET_COLUMNCREATE()
{
szColumnName = ValueColumnName,
coltyp = JET_coltyp.LongBinary,
grbit = ColumndefGrbit.None
};
var columns = new JET_COLUMNCREATE[] { projectColumnCreate, documentColumnCreate, nameColumnCreate, valueColumnCreate };
var projectAndDocumentAndNameIndexKey = "+" + ProjectColumnName + "\0+" + DocumentColumnName + "\0+" + NameColumnName + "\0\0";
var documentIndexKey = "+" + DocumentColumnName + "\0\0";
var indexes = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = ProjectAndDocumentAndNameIndexName,
szKey = projectAndDocumentAndNameIndexKey,
cbKey = projectAndDocumentAndNameIndexKey.Length,
grbit = CreateIndexGrbit.IndexPrimary | CreateIndexGrbit.IndexUnique | CreateIndexGrbit.IndexDisallowNull,
ulDensity = 80
},
new JET_INDEXCREATE
{
szIndexName = DocumentIndexName,
szKey = documentIndexKey,
cbKey = documentIndexKey.Length,
grbit = CreateIndexGrbit.IndexDisallowNull,
ulDensity = 80
}
};
var tableCreate = new JET_TABLECREATE()
{
szTableName = TableName,
ulPages = 16,
ulDensity = 80,
rgcolumncreate = columns,
cColumns = columns.Length,
rgindexcreate = indexes,
cIndexes = indexes.Length
};
Api.JetCreateTableColumnIndex3(sessionId, databaseId, tableCreate);
_projectColumnId = projectColumnCreate.columnid;
_documentColumnId = documentColumnCreate.columnid;
_nameColumnId = nameColumnCreate.columnid;
_valueColumnId = valueColumnCreate.columnid;
Api.JetCloseTable(sessionId, tableCreate.tableid);
}
/// <summary>This method is called before a table is actually opened.
/// This is a good place to upgrade database schema, reconstruct indices, or engage in some other table schema management activity.
/// None of that is implemented in the current version of the codebase, though.
/// The current version just throws an exception if column schema's wrong, and completely ignores the indices schema.</summary>
public void VerifyTableSchema(JET_SESID idSession, JET_DBID idDatabase)
{
// Verify the table exists
{
JET_TABLEID idTestTable;
if (Api.TryOpenTable(idSession, idDatabase, m_tableName, OpenTableGrbit.None, out idTestTable))
{
Api.JetCloseTable(idSession, idTestTable);
}
else
{
// Just create the table
using (Transaction transaction = new Transaction(idSession))
{
CreateTableAndIndices(idSession, idDatabase);
transaction.Commit(CommitTransactionGrbit.None);
}
return;
}
}
// Verify columns
foreach (var i in m_columns)
{
Global.TryCatch(delegate()
{
JET_COLUMNDEF defDB;
Api.JetGetColumnInfo(idSession, idDatabase, m_tableName, i.columnName, out defDB);
JET_COLUMNDEF defCode = i.attrib.getColumnDef();
if (defCode.coltyp != defDB.coltyp)
{
throw new SerializationException("Wrong type.");
}
if (defCode.cp != defDB.cp)
{
throw new SerializationException("Wrong codepage.");
}
if (defCode.grbit != defDB.grbit)
{
throw new SerializationException("Flags mismatch.");
}
},
"Error while validating column '" + i.columnName + "'");
}
// Verify indices
// http://stackoverflow.com/a/2345746/126995
Dictionary <string, IndexInfo> dbIndices = Api.GetTableIndexes(idSession, idDatabase, m_tableName)
.ToDictionary(ii => ii.Name);
foreach (var kvp in m_indices)
{
sIndexInfo ind = kvp.Value;
Global.TryCatch(() =>
{
IndexInfo ii;
if (!dbIndices.TryGetValue(kvp.Key, out ii))
{
throw new SerializationException("The index was not found in the DB");
}
JET_INDEXCREATE icreate = ind.attrib.getIndexDef();
if (!areEqual(ii.Grbit, icreate.grbit))
{
throw new SerializationException("The index grbit doesn't match what's in the DB");
}
string[] colsDatabase = ii.IndexSegments
.Select(seg => seg.ColumnName)
.ToArray();
string[] colsTypeInfo = getIndexedColumns(ind.attrib.strKey).ToArray();
if (!colsDatabase.SequenceEqual(colsTypeInfo))
{
throw new SerializationException("The index is over the different column than what's in the DB");
}
},
"Error while validating index '" + kvp.Key + "'");
}
}
public void JetCreateTemplateTableColumnIndex()
{
var columncreates = new JET_COLUMNCREATE[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short",
coltyp = JET_coltyp.Short,
cbMax = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext",
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
},
};
const string Index1Name = "firstIndex";
const string Index1Description = "+col1_short\0-col2_longtext\0";
const string Index2Name = "secondIndex";
const string Index2Description = "+col2_longtext\0-col1_short\0";
var indexcreates = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = Index1Name,
szKey = Index1Description,
cbKey = Index1Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
},
new JET_INDEXCREATE
{
szIndexName = Index2Name,
szKey = Index2Description,
cbKey = Index2Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 79,
},
};
var tablecreateTemplate = new JET_TABLECREATE()
{
szTableName = "tableOld",
ulPages = 23,
ulDensity = 75,
cColumns = columncreates.Length,
rgcolumncreate = columncreates,
rgindexcreate = indexcreates,
cIndexes = indexcreates.Length,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.TemplateTable,
};
Api.JetBeginTransaction(this.sesid);
Api.JetCreateTableColumnIndex3(this.sesid, this.dbid, tablecreateTemplate);
var tableCreated = new JET_TABLEID()
{
Value = tablecreateTemplate.tableid.Value
};
Assert.AreNotEqual <JET_TABLEID>(JET_TABLEID.Nil, tableCreated);
// 1 table, 2 columns, 2 indices = 5 objects.
Assert.AreEqual <int>(tablecreateTemplate.cCreated, 5);
Assert.AreNotEqual(tablecreateTemplate.rgcolumncreate[0].columnid, JET_COLUMNID.Nil);
Assert.AreNotEqual(tablecreateTemplate.rgcolumncreate[1].columnid, JET_COLUMNID.Nil);
var tablecreateChild = new JET_TABLECREATE()
{
szTableName = "tableNew",
szTemplateTableName = "tableOld",
ulPages = 23,
ulDensity = 75,
rgcolumncreate = null,
cColumns = 0,
rgindexcreate = null,
cIndexes = 0,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.None,
};
Api.JetCreateTableColumnIndex3(this.sesid, this.dbid, tablecreateChild);
var tableidChild = new JET_TABLEID()
{
Value = tablecreateChild.tableid.Value
};
Assert.AreNotEqual <JET_TABLEID>(JET_TABLEID.Nil, tableidChild);
// 1 table = 1 object
Assert.AreEqual <int>(tablecreateChild.cCreated, 1);
Api.JetCloseTable(this.sesid, tableCreated);
Api.JetCloseTable(this.sesid, tableidChild);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
}
/// <summary>
/// Creates a single table with the specified definition in the database
/// </summary>
/// <param name="tableDefinition">The table definition.</param>
public override void CreateTable(TableDefinition tableDefinition)
{
lock (this.IsamSession)
{
this.CheckDisposed();
using (IsamTransaction trx = new IsamTransaction(this.IsamSession))
{
// FUTURE-2013/11/15-martinc: Consider using JetCreateTableColumnIndex(). It would be
// a bit faster because it's only a single managed/native transition.
// Hard-code the initial space and density.
JET_TABLEID tableid;
Api.JetCreateTable(this.IsamSession.Sesid, this.dbid, tableDefinition.Name, 16, 90, out tableid);
foreach (ColumnDefinition columnDefinition in tableDefinition.Columns)
{
JET_COLUMNDEF columndef = new JET_COLUMNDEF();
columndef.coltyp = IsamDatabase.ColtypFromColumnDefinition(columnDefinition);
columndef.cp = JET_CP.Unicode;
columndef.cbMax = columnDefinition.MaxLength;
columndef.grbit = Converter.ColumndefGrbitFromColumnFlags(columnDefinition.Flags);
byte[] defaultValueBytes = Converter.BytesFromObject(
columndef.coltyp,
false /*ASCII */,
columnDefinition.DefaultValue);
JET_COLUMNID columnid;
int defaultValueLength = (defaultValueBytes == null) ? 0 : defaultValueBytes.Length;
Api.JetAddColumn(
this.IsamSession.Sesid,
tableid,
columnDefinition.Name,
columndef,
defaultValueBytes,
defaultValueLength,
out columnid);
}
foreach (IndexDefinition indexDefinition in tableDefinition.Indices)
{
JET_INDEXCREATE[] indexcreates = new JET_INDEXCREATE[1];
indexcreates[0] = new JET_INDEXCREATE();
indexcreates[0].szIndexName = indexDefinition.Name;
indexcreates[0].szKey = IsamDatabase.IndexKeyFromIndexDefinition(indexDefinition);
indexcreates[0].cbKey = indexcreates[0].szKey.Length;
indexcreates[0].grbit = IsamDatabase.GrbitFromIndexDefinition(indexDefinition);
indexcreates[0].ulDensity = indexDefinition.Density;
indexcreates[0].pidxUnicode = new JET_UNICODEINDEX();
indexcreates[0].pidxUnicode.lcid = indexDefinition.CultureInfo.LCID;
indexcreates[0].pidxUnicode.dwMapFlags = (uint)Converter.UnicodeFlagsFromCompareOptions(indexDefinition.CompareOptions);
indexcreates[0].rgconditionalcolumn = IsamDatabase.ConditionalColumnsFromIndexDefinition(indexDefinition);
indexcreates[0].cConditionalColumn = indexcreates[0].rgconditionalcolumn.Length;
indexcreates[0].cbKeyMost = indexDefinition.MaxKeyLength;
Api.JetCreateIndex2(this.IsamSession.Sesid, tableid, indexcreates, indexcreates.Length);
}
// The initially-created tableid is opened exclusively.
Api.JetCloseTable(this.IsamSession.Sesid, tableid);
trx.Commit();
DatabaseCommon.SchemaUpdateID++;
}
}
}
public void VerifyTableCreateCanBeSerialized()
{
var columncreates = new[]
{
new JET_COLUMNCREATE()
{
szColumnName = "col1_short",
coltyp = JET_coltyp.Short,
cbMax = 2,
pvDefault = BitConverter.GetBytes((short)37),
cbDefault = 2,
},
new JET_COLUMNCREATE()
{
szColumnName = "col2_longtext",
coltyp = JET_coltyp.LongText,
cp = JET_CP.Unicode,
},
};
const string Index1Name = "firstIndex";
const string Index1Description = "+col1_short\0-col2_longtext\0";
const string Index2Name = "secondIndex";
const string Index2Description = "+col2_longtext\0-col1_short\0";
var spacehintsIndex = new JET_SPACEHINTS()
{
ulInitialDensity = 33,
cbInitial = 4096,
grbit = SpaceHintsGrbit.CreateHintAppendSequential | SpaceHintsGrbit.RetrieveHintTableScanForward,
ulMaintDensity = 44,
ulGrowth = 144,
cbMinExtent = 1024 * 1024,
cbMaxExtent = 3 * 1024 * 1024,
};
var spacehintsSeq = new JET_SPACEHINTS()
{
ulInitialDensity = 33,
cbInitial = 4096,
grbit = SpaceHintsGrbit.CreateHintAppendSequential | SpaceHintsGrbit.RetrieveHintTableScanForward,
ulMaintDensity = 44,
ulGrowth = 144,
cbMinExtent = 1024 * 1024,
cbMaxExtent = 3 * 1024 * 1024,
};
var spacehintsLv = new JET_SPACEHINTS()
{
ulInitialDensity = 33,
cbInitial = 4096,
grbit = SpaceHintsGrbit.CreateHintAppendSequential | SpaceHintsGrbit.RetrieveHintTableScanForward,
ulMaintDensity = 44,
ulGrowth = 144,
cbMinExtent = 1024 * 1024,
cbMaxExtent = 3 * 1024 * 1024,
};
var indexcreates = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = Index1Name,
szKey = Index1Description,
cbKey = Index1Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 99,
pSpaceHints = spacehintsIndex,
},
new JET_INDEXCREATE
{
szIndexName = Index2Name,
szKey = Index2Description,
cbKey = Index2Description.Length + 1,
grbit = CreateIndexGrbit.None,
ulDensity = 79,
},
};
var expected = new JET_TABLECREATE()
{
szTableName = "tableBigBang",
ulPages = 23,
ulDensity = 75,
cColumns = columncreates.Length,
rgcolumncreate = columncreates,
rgindexcreate = indexcreates,
cIndexes = indexcreates.Length,
cbSeparateLV = 100,
cbtyp = JET_cbtyp.Null,
grbit = CreateTableColumnIndexGrbit.None,
pSeqSpacehints = spacehintsSeq,
pLVSpacehints = spacehintsLv,
};
SerializeAndCompareContent(expected);
}
private static void CompareIndexCreateWithOutput(
JET_INDEXCREATE orig,
JET_INDEXCREATE actual)
{
Assert.AreEqual(orig.szIndexName, actual.szIndexName);
Assert.AreEqual(orig.cConditionalColumn, actual.cConditionalColumn);
Assert.AreEqual(orig.cbKey, actual.cbKey);
Assert.AreEqual(orig.szKey, actual.szKey);
if (orig.cbKeyMost == 0)
{
Assert.AreEqual(255, actual.cbKeyMost);
}
else
{
Assert.AreEqual(orig.cbKeyMost, actual.cbKeyMost);
}
if (orig.cbVarSegMac == 0)
{
Assert.AreEqual(255, actual.cbVarSegMac);
}
else
{
Assert.AreEqual(orig.cbVarSegMac, actual.cbVarSegMac);
}
if (orig.ulDensity == 0)
{
Assert.AreEqual(100, actual.ulDensity);
}
else
{
Assert.AreEqual(orig.ulDensity, actual.ulDensity);
}
// Clear the bits that might get set:
CreateIndexGrbit originalGrbit = orig.grbit;
CreateIndexGrbit actualGrbit = actual.grbit & ~(CreateIndexGrbit.IndexUnique | VistaGrbits.IndexUnicode);
if (originalGrbit.HasFlag(CreateIndexGrbit.IndexIgnoreAnyNull))
{
originalGrbit &= ~(CreateIndexGrbit.IndexIgnoreAnyNull | CreateIndexGrbit.IndexIgnoreFirstNull |
CreateIndexGrbit.IndexIgnoreNull);
actualGrbit &= ~(CreateIndexGrbit.IndexIgnoreAnyNull | CreateIndexGrbit.IndexIgnoreFirstNull |
CreateIndexGrbit.IndexIgnoreNull);
}
Assert.AreEqual(originalGrbit, actualGrbit);
if (orig.pSpaceHints == null)
{
JET_SPACEHINTS defaultSpaceHints = new JET_SPACEHINTS()
{
ulInitialDensity = 100, // Or is it actual.ulDensity ?
};
Assert.IsTrue(defaultSpaceHints.ContentEquals(actual.pSpaceHints));
}
else
{
Assert.IsTrue(orig.pSpaceHints.ContentEquals(actual.pSpaceHints));
}
if (orig.pidxUnicode == null)
{
JET_UNICODEINDEX defaultUnicodeIndex = new JET_UNICODEINDEX()
{
dwMapFlags = 0x30401,
szLocaleName = "en-us",
};
Assert.IsTrue(defaultUnicodeIndex.ContentEquals(actual.pidxUnicode));
}
}
/// <summary>
/// Retrieves information about indexes on a table.
/// </summary>
/// <param name="sesid">The session to use.</param>
/// <param name="dbid">The database to use.</param>
/// <param name="tablename">The name of the table to retrieve index information about.</param>
/// <param name="indexname">The name of the index to retrieve information about.</param>
/// <param name="result">Filled in with information about indexes on the table.</param>
/// <param name="infoLevel">The type of information to retrieve.</param>
/// <returns>An error if the call fails.</returns>
public int JetGetIndexInfo(
JET_SESID sesid,
JET_DBID dbid,
string tablename,
string indexname,
out JET_INDEXCREATE result,
JET_IdxInfo infoLevel)
{
TraceFunctionCall();
CheckNotNull(tablename, "tablename");
int err;
switch (infoLevel)
{
case Microsoft.Isam.Esent.Interop.Windows7.Windows7IdxInfo.CreateIndex:
case Microsoft.Isam.Esent.Interop.Windows7.Windows7IdxInfo.CreateIndex2:
case Microsoft.Isam.Esent.Interop.Windows8.Windows8IdxInfo.InfoCreateIndex3:
break;
default:
throw new ArgumentException(string.Format("{0} is not a valid value JET_IdxInfo for this JET_INDEXCREATE overload."));
}
if (this.Capabilities.SupportsWindows8Features)
{
{
int bufferSize = 10 * Marshal.SizeOf(typeof(NATIVE_INDEXCREATE3));
IntPtr unmanagedBuffer = Marshal.AllocHGlobal(bufferSize);
try
{
// var nativeIndexcreate = new NATIVE_INDEXCREATE3();
// nativeIndexcreate.cbStruct = checked((uint)bufferSize);
infoLevel = Windows8IdxInfo.InfoCreateIndex3;
err = Err(NativeMethods.JetGetIndexInfoW(
sesid.Value,
dbid.Value,
tablename,
indexname,
unmanagedBuffer,
(uint)bufferSize,
(uint)infoLevel));
NATIVE_INDEXCREATE3 nativeIndexcreate = (NATIVE_INDEXCREATE3)Marshal.PtrToStructure(unmanagedBuffer, typeof(NATIVE_INDEXCREATE3));
result = new JET_INDEXCREATE();
result.SetAllFromNativeIndexCreate(ref nativeIndexcreate);
}
finally
{
Marshal.FreeHGlobal(unmanagedBuffer);
}
}
}
else
{
result = null;
err = Err((int)JET_err.FeatureNotAvailable);
}
return(err);
}
public void HowDoIDealWithKeyTruncation()
{
JET_SESID sesid = this.testSession;
JET_DBID dbid = this.testDbid;
JET_TABLEID tableid;
JET_COLUMNDEF columndef = new JET_COLUMNDEF();
JET_COLUMNID keyColumn;
JET_COLUMNID dataColumn;
// First create the table.
Api.JetCreateTable(sesid, dbid, "table", 0, 100, out tableid);
columndef.coltyp = JET_coltyp.LongText;
columndef.cp = JET_CP.Unicode;
Api.JetAddColumn(sesid, tableid, "key", columndef, null, 0, out keyColumn);
columndef.coltyp = JET_coltyp.Long;
Api.JetAddColumn(sesid, tableid, "data", columndef, null, 0, out dataColumn);
// Now create a secondary, non-unique column on the string column.
// ESENT keys are stored in a normalized form, which is typically
// larger than the source data, but allow for very fast seeks. By
// default the maximum normalized key size is 255 bytes. Starting
// with Windows Vista the maximum key size can be increased. Setting
// cbKeyMost to SystemParameters.KeyMost will make ManagedEsent
// create the index with the largest allowable key.
const string KeyDescription = "+key\0\0";
JET_INDEXCREATE[] indexcreates = new JET_INDEXCREATE[1];
indexcreates[0] = new JET_INDEXCREATE
{
szIndexName = "secondary",
szKey = KeyDescription,
cbKey = KeyDescription.Length,
cbKeyMost = SystemParameters.KeyMost,
grbit = CreateIndexGrbit.None,
};
Api.JetCreateIndex2(sesid, tableid, indexcreates, indexcreates.Length);
// Insert some records. The key has the same value for the first
// 4096 characters and then is different. This string is too large
// for even the largest key sizes to differentiate.
// If the index was unique we would get a key duplicate error.
string prefix = new string('x', 4096);
using (var transaction = new Transaction(sesid))
{
int i = 0;
foreach (string k in new[] { "a", "b", "c", "d" })
{
using (var update = new Update(sesid, tableid, JET_prep.Insert))
{
string key = prefix + k;
Api.SetColumn(sesid, tableid, keyColumn, key, Encoding.Unicode);
Api.SetColumn(sesid, tableid, dataColumn, i++);
update.Save();
}
}
transaction.Commit(CommitTransactionGrbit.LazyFlush);
}
// Seek for a record. This demonstrates the problem with key truncation.
// We seek for the key ending in 'd' but end up on the one ending in 'a'.
string seekKey = prefix + "d";
Api.JetSetCurrentIndex(sesid, tableid, "secondary");
Api.MakeKey(sesid, tableid, prefix + "d", Encoding.Unicode, MakeKeyGrbit.NewKey);
Api.JetSeek(sesid, tableid, SeekGrbit.SeekEQ);
string actualKey = Api.RetrieveColumnAsString(sesid, tableid, keyColumn);
Assert.AreNotEqual(seekKey, actualKey);
Assert.AreEqual(prefix + "a", actualKey);
// Seek for the record using a key range.
Assert.IsTrue(TrySeekTruncatedString(sesid, tableid, seekKey, keyColumn));
Assert.AreEqual(seekKey, Api.RetrieveColumnAsString(sesid, tableid, keyColumn));
Assert.AreEqual(3, Api.RetrieveColumnAsInt32(sesid, tableid, dataColumn));
Assert.IsFalse(TrySeekTruncatedString(sesid, tableid, prefix + "z", keyColumn));
Assert.IsFalse(TrySeekTruncatedString(sesid, tableid, "foo", keyColumn));
}
