Reputation: 12863
I would like the db to auto increment an EmployeeId # for me. This would be a business id, and not the same as the db id (which is hilo generated). I found what looks like a promising solution but am having a problem implementing it.
I can't tell if the exception I'm getting is because the mapping is incorrect, SQLite can't handle it, or something else. I would also like to know if anyone has a different solution to this.
Cheers,
Berry
MAPPING
<class name="Resource" table="Resources" ...>
<id name="Id" type="System.Int32" unsaved-value="0">
<column name="ResourceId" />
<generator class="hilo" />
</id>
...
<component name="EmployeeNumber" unique="true">
<property name="StorageValue" generated="always" insert="false">
<column name="EmployeeNumber" />
</property>
</component>
...
</class>
<database-object>
<create>
ALTER TABLE Entity DROP COLUMN EmployeeNumber
ALTER TABLE Entity ADD EmployeeNumber INT IDENTITY
</create>
<drop>
ALTER TABLE Entity DROP COLUMN EmployeeNumber
</drop>
</database-object>
EXCEPTION output
ALTER TABLE Entity DROP COLUMN EmployeeNumber
ALTER TABLE Entity ADD EmployeeNumber INT IDENTITY
ALTER TABLE Entity DROP COLUMN EmployeeNumber
ALTER TABLE Entity ADD EmployeeNumber INT IDENTITY
failed: System.Exception : Generate schema error:
----> System.Data.SQLite.SQLiteException : SQLite error
near "DROP": syntax error
NHibernate\DbConfiguration\NHibInitialization\SchemaManager.cs(58,0): at Smack.Core.Data.NHibernate.DbConfiguration.NHibInitialization.SchemaManager._generateNewDb(Configuration cfg, IDbConnection conn)
NHibernate\DbConfiguration\NHibInitialization\SchemaManager.cs(16,0): at Smack.Core.Data.NHibernate.DbConfiguration.NHibInitialization.SchemaManager.GenerateNewDb(Configuration cfg, IDbConnection connection)
NHibernate\TestFixtures\SQLite\SQLiteBaseTestFixture.cs(56,0): at Smack.Core.TestingSupport.NHibernate.TestFixtures.SQLite.SQLiteBaseTestFixture._buildSchema()
NHibernate\TestFixtures\SQLite\SQLiteBaseTestFixture.cs(37,0): at Smack.Core.TestingSupport.NHibernate.TestFixtures.SQLite.SQLiteBaseTestFixture._SetUpNHibernateSession()
NHibernate\TestFixtures\SQLite\SQLiteBaseTestFixture.cs(25,0): at Smack.Core.TestingSupport.NHibernate.TestFixtures.SQLite.SQLiteBaseTestFixture.OnSetUp()
Mappings\EmployeeMappingTests.cs(23,0): at Smack.ConstructionAdmin.Data.Tests.Mappings.EmployeeMappingTests.OnSetUp()
BaseTestFixture.cs(27,0): at Smack.Core.TestingSupport.BaseTestFixture.SetUp()
--SQLiteException
at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
Upvotes: 0
Views: 875
Reputation: 71601
In the flow chart for the ALTER TABLE SQLite statement here, I cannot find a DROP clause. You can only add and rename columns. So, you could rename EmployeeNumber to EmployeeNumber_old instead of dropping it. You could also drop and recreate the whole table (but if this is a persistent SQLite DB containing production data, I would advise against it)
Upvotes: 1