ALTER DATABASE SCHEMA FROM FILE command results in an Ultralite's Bug

[Former Member]

Hello experts,

I've got a Ultralite BUG during a database schema upgrade using the ALTER DATABASE SCHEMA FROM FILE command.

If I try to add an index to a column of a existent table that already has an indexed column (by schema migration), the Ultralite database becomes invalid and the following error is shown during the database validation:

Validation failed:
Index validation failed for table Test, index Test with code: 120

Is there any workaround for it? I've used the SqlAnywhere 12.0.1.3505

---

Following, I put the steps to reproduce this problem (All steps were done using the SybaseCentral):

• Create a new database (TestDatabase.udb)
  SybaseCentral: ulinit -p "4096" -S "0" --max_hash_size=4 --timestamp_increment=1 -y "TestDatabase.udb"
  

• Create a new table:
  Test (id SMALLINT PK, code VARCHAR(50) NOT NULL, value VARCHAR(50))

• Create a non-unique index to value column

• Feed the Test table with some data

• Create a copy of the database to design the second version of schema (TestDatabaseV2.udb)

• Add the unique constraint for code column of Test table (The Ultralite will add a index to this column automaticaly)

• Unload the schema for the second schema as a SQL file (TestDatabaseV2.sql)
  SybaseCentral: ulunload -v -y -c "DBN=TestDatabaseV2;CON=N_Sybase Central 1;DBF=TestDatabaseV2.udb" -b 1024 -n -s "TestDatabaseV2.sql"

• Connect to the first database and execute the schema upgrade using the command below:
  ALTER DATABASE SCHEMA FROM FILE 'TestDatabaseV2.sql'

• Check the database, using the "Validate Database..." option of Sybase Central. It will return an index error.
  SybaseCentral: ulvalid -v -c "DBN=TestDatabase;CON=N_Sybase Central 2;DBF=TestDatabase.udb"

The following output is shown:

Checking page (1)
Validating table Test
Validating index primary on table Test
Validating index Test on table Test
Incorrect row count for index Test on table Test
Validating index IX_test on table Test
Validating table sysuldata
Validating index primary on table sysuldata
Validation failed:
Index validation failed for table Test, index Test with code: 120

[former_SQLA_member1694876]

I assume this is all done on a Windows desktop? What is your deployment platform?

[Former Member]

Yes, but we've tested with the MacOS Desktop as well and the same problem was reproduced. We are targeting the iOS and Android devices (both with the same issue as well)

[former_SQLA_member1694876 · Accepted solution]

Thank you for posting this, and for the repro. We will look into it.

At this point I can't think of a workaround other than the obvious 🙂

[Former Member · Accepted solution]

Tim, please, keep in touch if you have any news about this issue. We have a huge product using the Sybase solution and we are almost deciding if we are going to upgrade our client dabatase using either the ALTER DATABASE command or using DLL executions directly in the database. This decision has been a big "headache" to us 😃

[former_SQLA_member1694876 · Accepted solution]

Question: Why not use alter-database-schema-from-file?

News: Given what you've provided here, we have reproduced and fixed this bug - thanks. It should be available in build 12.0.1.3558 or later.

[Former Member · Accepted solution]

We were wondering if we could use that feature 😃
The hard decision was only about that bug, but as I can see, it isn't going to be a problem anymore =D
Tks so much!

Is there a plan to when the next revision will be released?

[Former Member · Accepted solution]

Another doubt about why use or not use the ALTER DATABASE command is the renaming of columns and tables.
We are studying implement a mix of the Ultralite command and DDLs to update our database schemas.

Since the RENAME commands are allowed by Ultralite SQL, it would be a really nice feature if this change could be made using the ALTER DATABASE command.

Maybe using a special commented section of the migration script to store a pos-processing batch of SQL commands.
What do you think? 😃

[former_SQLA_member1694876 · Accepted solution]

No problem!

I don't know exactly when the EBFs will be out, but it should be something like 2 - 6 weeks.

[former_SQLA_member1694876 · Accepted solution]

As you say, it would require some special work to add a rename feature to alter-database-from-file. We decided originally not to do it because of the added complication, and we viewed the ability to rename as secondary compared to more substantial and typical schema changes (like adding a column or modifying a column type or adding an index).

If you really need rename, then yes, I would suggest you do that with DDL before or after the alter-database statement (depending on whether the new names are in the .sql file or not).

Alter-database is a powerful statement because it automatically determines the delta required to move from the current schema to the new one. If you need to do renames (and not knowing exactly how your application works) then you may end up having to track the versions and deltas yourself anyway, in which case you'd have the option of using all individual DDL statements if that suited.

[Former Member · Accepted solution]

Ok. We are going to use the alter-database-schema command and maybe some individual DLL executions whenever we need rename some database object.

Thank you very much.