Comments (6)
You can already omit the create table from .sq files and have a generated interface with only string constants
from sqldelight.
With the current SQLDelight 0.5.1 version, to perform a table migration (which involves adding and/or removing columns or other changes) I'm using the following procedure.
Let's say this is the initial version (v1
) of my table, defined in Billing.sq
:
CREATE TABLE billing (
_id INTEGER NOT NULL PRIMARY KEY,
user_id INTEGER NOT NULL,
company_name TEXT,
address TEXT,
zip_code TEXT,
city TEXT,
vat TEXT,
country TEXT,
number_of_users INTEGER,
FOREIGN KEY (user_id) REFERENCES user(_id) ON DELETE CASCADE
);
get_by_user_id:
SELECT *
FROM billing
WHERE user_id = ?;
This is the corresponding Java model (the code is also using AutoValue, Retrolambda, Timber, SQLBrite, and RxLifecycle):
@AutoValue
public abstract class Billing implements BillingModel {
private static final Factory<Billing> FACTORY =
new Factory<>(AutoValue_Billing::new);
@UiThread
public static Observable<Billing> getByUserId(BriteDatabase db,
Activity activity,
long userId) {
Timber.d("Get billing for user with ID %d", userId);
return getObservable(db, FACTORY.get_by_user_id(userId),
FACTORY.get_by_user_idMapper())
.compose(RxLifecycleAndroid.bindActivity(activity.lifecycle()))
.observeOn(AndroidSchedulers.mainThread());
}
@UiThread
private static <T> Observable<T> getObservable(BriteDatabase db,
SqlDelightStatement stmt,
final RowMapper<T> mapper) {
return db.createQuery(stmt.tables, stmt.statement, stmt.args)
.mapToOne(mapper::map);
}
}
Create the v2 Billing_v2.sq
file and copy the content of Billing.sq
. Replace the occurrences of billing
with billing_v2
. Perform the changes you need on the table by adding or removing columns and by writing an import_from_v1
SQL statement to import the old data into the new table:
CREATE TABLE billing_v2 (
_id INTEGER NOT NULL PRIMARY KEY,
user_id INTEGER NOT NULL,
company_name TEXT,
billing_type INTEGER,
address TEXT,
zip_code TEXT,
city TEXT,
vat TEXT,
country TEXT,
FOREIGN KEY (user_id) REFERENCES user(_id) ON DELETE CASCADE
);
get_by_user_id:
SELECT *
FROM billing_v2
WHERE user_id = ?;
import_from_v1:
INSERT INTO billing_v2(_id, user_id, company_name, address, zip_code,
city, vat, country)
SELECT _id, user_id, company_name, address, zip_code, city, vat, country
FROM billing;
Then modify the Java model by replacing BillingModel
with BillingModel_v2
plus some little changes, if needed:
@AutoValue
public abstract class Billing implements BillingModel_v2 {
private static final Factory<Billing> FACTORY =
new Factory<>(AutoValue_Billing::new);
@UiThread
public static Observable<Billing> getByUserId(BriteDatabase db,
Activity activity,
long userId) {
Timber.d("Get billing for user with ID %d", userId);
return getObservable(db, FACTORY.get_by_user_id(userId),
FACTORY.get_by_user_idMapper())
.compose(RxLifecycleAndroid.bindActivity(activity.lifecycle()))
.observeOn(AndroidSchedulers.mainThread());
}
@UiThread
private static <T> Observable<T> getObservable(BriteDatabase db,
SqlDelightStatement stmt,
final RowMapper<T> mapper) {
return db.createQuery(stmt.tables, stmt.statement, stmt.args)
.mapToOne(mapper::map);
}
}
This will be the database migration:
public void onMigration(SQLiteDatabase db) {
db.beginTransaction();
try {
db.execSQL(Billing_v2Model.CREATE_TABLE);
db.execSQL(Billing_v2Model.MIGRATE_FROM_V1);
db.execSQL("DROP TABLE " + BillingModel.TABLE_NAME);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}
At this point, the migration is over.
In this way you perform few changes in the Java code and you can use SQLDelight mapping without workarounds. However, this approach is error-prone as it requires a lot of manual operations and has drawbacks. I'm going to list a few.
The old Billing.sq
file still gets processed by SQLDelight and produces the interface, even if it will not be used anymore. You can be in trouble if you have auto incrementing primary keys or if other tables are referencing the old table's primary key, which requires additional and very careful manual work. SQLite has also many limitations, for example you can't remove a column in an existing table or change its type.
At the moment, I can't think of a better way to do this.
from sqldelight.
It would also be nice to group multiple statements in some Java collection. For example, I'd prefer to loop over an array and run execSQL
on each statement it contains, since I group migration statements by the SQLiteOpenHelper version. I could then have a helper method do the looping without relying on a naming scheme and manually running each statement, which can get quite tedious and error-prone with large or complex migrations, e.g. anything needing temp tables.
from sqldelight.
potentially useful: http://stackoverflow.com/questions/6460671/sqlite-schema-information-metadata
from sqldelight.
I think what you want to do is create a temp table, copy all the data from v1, drop and recreate the table with the v2 schema, and import (mapping columns) from temp into the table.
You can do this in one raw query as a transaction, but sqldelight might make some parts difficult. I would probably not model the migration or the original table in sqldelight, but instead manually perform the migration in SQLiteOpenHelper#onUpgrade
, bypassing any mapping done on the sqldelight side until everything is consistent with the v2 state. This is essentially what is usually done without any Java mapping layer.
from sqldelight.
I think what you want to do is create a temp table, copy all the data from v1, drop and recreate the table with the v2 schema, and import (mapping columns) from temp into the table.
Exactly. Since SQLite has limited ALTER TABLE functionality, in certain situations it's the only way to go.
You can do this in one raw query as a transaction, but sqldelight might make some parts difficult. I would probably not model the migration or the original table in sqldelight, but instead manually perform the migration in SQLiteOpenHelper#onUpgrade, bypassing any mapping done on the sqldelight side until everything is consistent with the v2 state
Of course, I can do everything without using SQLDelight, and what you described is what I'm actually doing during development and when I have to do things purely on the DB level. The scenario which I've described is when you ship an app in production with a certain schema done with SQLDelight, then you have to ship the next release and you have to do data migration. To be able to use SQLDelight mapping, I could not see a better way of doing a migration, otherwise I will have to do manual mapping for every query and I couldn't use the automatically generated database models, because they will be outdated. Correct me if I'm wrong.
from sqldelight.
Related Issues (20)
- com.intellij.codeInsight.intention.impl.preview.IntentionPreviewUnsupportedOperationException in IntroduceTableAliasIntention.kt:85
- SQL Delight with KotlinJS in Angular Web App
- java.lang.NoClassDefFoundError in SqlDelightFileType.kt:23
- java.lang.ClassCastException in SqlDelightProjectService.kt:42
- Wrong execution order of CREATE EXTENSION HOT 1
- com.intellij.serviceContainer.AlreadyDisposedException in SqlDelightGotoDeclarationHandler.kt:124
- java.lang.IllegalStateException in SqlDelightGotoDeclarationHandler.kt:124
- com.intellij.diagnostic.PluginException in ComponentManagerImpl.kt:566
- java.lang.NoSuchMethodError in MySqlTypeResolver.kt:150
- ROW_NUMBER() function throws a compilation exception java.lang.NullPointerException (no error message)
- SELECT DISTINCT syntax no longer works HOT 1
- some boolean expressions no longer compile HOT 3
- com.intellij.diagnostic.PluginException in ComponentManagerImpl.kt:588
- java.lang.Throwable in OptimisticLockValidator.kt:41
- java.lang.Exception in SqlDelightFileViewProviderFactory.kt:213
- java.lang.Throwable in Logger.java:376
- com.intellij.platform.instanceContainer.internal.ContainerDisposedException in InstanceContainerImpl.kt:253
- java.lang.IllegalStateException in PsiElementBase.java:-1
- java.lang.ClassCastException in SqlDelightCopyPasteProcessor.kt:120
- com.intellij.psi.stubs.UpToDateStubIndexMismatch in FileTrees.java:182
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqldelight.