Manual ALTER TABLE Migration vs. Ti.Database.open Flags for Schema Upgrade

When upgrading a Titanium app’s SQLite database, the primary goal is to modify the schema while keeping all existing indexes and triggers intact, so that a rollback can be performed safely if needed.

Two documented methods exist: executing Ti.Database.execute('ALTER TABLE …') statements manually, and opening the database with flags such as Ti.Database.CREATE or Ti.Database.REPLACE. Manual migrations allow explicit rollback scripts, but the framework does not guarantee that indexes or triggers are recreated automatically. The CREATE flag preserves tables yet cannot change column types, while REPLACE drops the database entirely, discarding all data.

Because the behavior of ALTER TABLE with respect to indexes, triggers, and the order of multiple statements is undocumented across iOS and Android, the following questions remain unanswered:

1. Does a single Ti.Database.execute('ALTER TABLE …') automatically recreate all indexes and triggers on the affected table on both platforms?
2. If not, what pattern should developers follow to preserve these objects during a schema upgrade?
3. Can Ti.Database.open with Ti.Database.CREATE be used to apply a schema change without dropping indexes, or must indexes be recreated manually?