Issue
I have an sqlite
database where i would like to add some default app settings when the user opens the application for the first time, but every time i am initializing the app, data gets added to the table.
Here is my sql file content:
CREATE TABLE IF NOT EXISTS settings(name TEXT, value BLOB);
-- INSERT or IGNORE INTO settings(name, value) VALUES ('user_lang', 'ru');
-- INSERT or IGNORE INTO settings(name, value) VALUES ('notifications', false);
-- INSERT or IGNORE INTO settings(name, value) VALUES ('font_size', '1em');
This snippet where i am seeding the database with some sql queries from the file:
createDatabaseObject() {
this.plt.ready().then(() => {
this.sqlite.create({
name: 'appdb.db',
location: 'default'
})
.then((db: SQLiteObject) => {
this.database = db;
this.seedDatabase();
});
});
}
seedDatabase() {
// Load settings
this.http.get('assets/app_database.sql', { responseType: 'text'})
.subscribe(sql => {
this.sqlitePorter.importSqlToDb(this.database, sql)
.then(_ => {
this.loadSettings();
this.dbReady.next(true);
})
.catch(e => console.error(e));
});
}
I have tried another solution with if statement inside my sql file, but it gives an error that says:
Failed to import SQL; message=sqlite3_prepare_v2 failure: near "IF": syntax error
IF (SELECT COUNT(*) FROM settings) = 0
BEGIN
INSERT INTO settings (value, name)
VALUES
('user_lang','ru'),
('notifications', false),
('font_size','1em');
END
ELSE
BEGIN
SELECT COUNT(*) FROM settings
END
Solution
So it was a piece of cake!! by adding UNIQUE to column name OR IGNORE
operators will take effect.
CREATE TABLE IF NOT EXISTS
settings (
name TEXT UNIQUE,
value BLOB
);
INSERT OR IGNORE INTO settings (name, value)
VALUES
('user_lang', 'ru'),
('notifications', false),
('font_size', '1em');
Answered By - swordfish
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.