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 }; }