public override void Create(JET_SESID sessionId, JET_DBID databaseId)
{
var identifierColumnCreate = CreateIdColumn(IdentifierColumnName);
var locationsColumnCreate = CreateBinaryColumn(LocationsColumnName);
var columns = CreateProjectDocumentColumns(identifierColumnCreate, locationsColumnCreate);
var primaryIndexKey = CreateProjectDocumentIndexKey(IdentifierColumnName);
var indexes = new JET_INDEXCREATE[]
{
CreatePrimaryIndex(primaryIndexKey)
};
var tableCreate = CreateTable(TableName, columns, indexes);
Api.JetCreateTableColumnIndex3(sessionId, databaseId, tableCreate);
GetColumnIds(columns, out _projectColumnId, out _projectNameColumnId, out _documentColumnId);
_identifierColumnId = identifierColumnCreate.columnid;
_locationsColumnId = locationsColumnCreate.columnid;
Api.JetCloseTable(sessionId, tableCreate.tableid);
}
public override void Create(JET_SESID sessionId, JET_DBID databaseId)
{
var idColumnCreate = CreateAutoIncrementIdColumn(IdColumnName);
var identifierColumnCreate = CreateTextColumn(IdentifierColumnName);
var columns = new JET_COLUMNCREATE[] { idColumnCreate, identifierColumnCreate };
var primaryIndexKey = CreateIndexKey(IdColumnName);
var identifierIndexKey = CreateIndexKey(IdentifierColumnName);
var indexes = new JET_INDEXCREATE[]
{
CreatePrimaryIndex(primaryIndexKey),
CreateUniqueTextIndex(IdentifierIndexName, identifierIndexKey)
};
var tableCreate = CreateTable(TableName, columns, indexes);
Api.JetCreateTableColumnIndex3(sessionId, databaseId, tableCreate);
_idColumnId = idColumnCreate.columnid;
_identifierColumnId = identifierColumnCreate.columnid;
Api.JetCloseTable(sessionId, tableCreate.tableid);
}
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 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, nameColumnCreate, valueColumnCreate };
var projectAndNameIndexKey = "+" + ProjectColumnName + "\0+" + NameColumnName + "\0\0";
var indexes = new JET_INDEXCREATE[]
{
new JET_INDEXCREATE
{
szIndexName = ProjectAndNameIndexName,
szKey = projectAndNameIndexKey,
cbKey = projectAndNameIndexKey.Length,
grbit = CreateIndexGrbit.IndexPrimary | CreateIndexGrbit.IndexUnique | 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;
_nameColumnId = nameColumnCreate.columnid;
_valueColumnId = valueColumnCreate.columnid;
Api.JetCloseTable(sessionId, tableCreate.tableid);
}
public void Setup()
{
this.indexcreate = new JET_INDEXCREATE
{
szIndexName = "index",
szKey = Key,
cbKey = Key.Length + 1,
cbKeyMost = 255,
cbVarSegMac = 255,
};
}
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 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 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 JetCreateIndex2SpaceHints()
{
Api.JetBeginTransaction(this.sesid);
const string IndexName = "another_index";
const string IndexDescription = "-TestColumn\0\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 indexcreate = new JET_INDEXCREATE
{
szIndexName = IndexName,
szKey = IndexDescription,
cbKey = IndexDescription.Length,
grbit = CreateIndexGrbit.IndexIgnoreAnyNull,
pSpaceHints = spacehintsIndex,
};
Api.JetCreateIndex2(this.sesid, this.tableid, new[] { indexcreate }, 1);
Api.JetCommitTransaction(this.sesid, CommitTransactionGrbit.LazyFlush);
Api.JetSetCurrentIndex(this.sesid, this.tableid, IndexName);
}
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);
}
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 JetCreateIndex2()
{
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);
}
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);
}
}
}
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,
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 void JetIndexCreateToString()
{
var indexcreate = new JET_INDEXCREATE { cbKey = 5, szKey = "+C\0\0", szIndexName = "Index" };
Assert.AreEqual("JET_INDEXCREATE(Index:+C\0\0)", indexcreate.ToString());
}
public void GetIndexInformationOneIndexWithCompareOptions()
{
const string Indexname = "myindex";
const string Indexdef = "-unicode\0\0";
var pidxUnicode = new JET_UNICODEINDEX
{
lcid = CultureInfo.CurrentCulture.LCID,
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);
}
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);
}
/// <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 <see cref="JetCreateTable"/>
/// will have exlusive access or the table can be opened for
/// exclusive access by passing <see cref="OpenTableGrbit.DenyRead"/>
/// to <see cref="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 JetCreateIndex2(
JET_SESID sesid,
JET_TABLEID tableid,
JET_INDEXCREATE[] indexcreates,
int numIndexCreates)
{
Api.Check(Impl.JetCreateIndex2(sesid, tableid, indexcreates, numIndexCreates));
}
/// <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++;
}
}
}
private JET_INDEXCREATE MakeIndexcreate(string column)
{
var indexcreate = new JET_INDEXCREATE
{
szIndexName = String.Format("index_{0}", column),
szKey = String.Format("-{0}\0\0", column),
};
indexcreate.cbKey = indexcreate.szKey.Length;
return indexcreate;
}
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 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));
}
protected JET_TABLECREATE CreateTable(string tableName, JET_COLUMNCREATE[] columns, JET_INDEXCREATE[] indexes)
{
return new JET_TABLECREATE()
{
szTableName = tableName,
ulPages = 16,
ulDensity = 80,
rgcolumncreate = columns,
cColumns = columns.Length,
rgindexcreate = indexes,
cIndexes = indexes.Length
};
}