Trying to understand why my app creates a new db on each reload instead of finding the one created in the previous load. I have seen other posts here relating to importing an existing db as an asset Data persistence when app is killed which may be related. To create/open the db in the app I am using:
const db = SQLite.openDatabase("db.db");
Thanks,
Andrew
I’m guessing after some more thinking that this is normal and that to persist between reloads while in development I would need to save the db to async local storage. However, is that true of the production version as well?
Hey @andrew.shillito,
I used SQLite in a personal project of mine and didn’t have this issue. What’s your first SQL statement look like when you’re creating the table if it doesn’t already exist?
Thanks!
Hey @charliecruzan,
Thanks for your message. I included all of the potentially related code here. Let me know your thoughts. I fiddled with it some more this morning in case there was a promise issue in the async redux dispatch. Doesn’t seem to be.
// db creation
const db = SQLite.openDatabase("mobile_flashcards.db"); // create a DB if none exists and otherwise open it
// sql queries
export const createDecks = "create table if not exists decks " + "(" + ([
"title TEXT PRIMARY KEY NOT NULL",
"category TEXT DEFAULT 'Uncategorized'", // category name or null
"create_date TEXT NOT NULL", // date iso string
"last_tested INTEGER", // date iso string - will contain same value as deckScores related date
"last_score REAL", // real number or null
].join(", ")) + ")";
export const createDeck = "INSERT INTO decks (title, create_date, category) VALUES (?, ?, ?)";
export const createCards = "CREATE TABLE IF NOT EXISTS cards " + "(" + ([
"card_id TEXT PRIMARY KEY", // date num in unix epoch
"deck_id TEXT NOT NULL",
"question TEXT NOT NULL",
"answer TEXT NOT NULL",
"FOREIGN KEY (deck_id) REFERENCES decks(title) ON UPDATE CASCADE ON DELETE CASCADE"
]).join(", ") + ")";
export const createCard = "INSERT INTO cards (card_id, deck_id, question, answer) VALUES (?, ?, ?, ?)";
export const getDecksAndCards = "SELECT * FROM decks INNER JOIN cards ON cards.deck_id = decks.title ORDER BY decks.title, card_id";
export const checkForExistingTable = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
// functions using sql queries
const boundNoLogTx = function(Query, params = []) {
// just shortens function call in initial data population
return this.executeSql(Query, params,
() => {},
(transaction, error) => errorHandler(this, error));
}
export function checkForExistingTable() {
// checks if decks exists - existing db
// returns bool representing that decks exists
return new Promise((res, rej) => db.transaction(tx => {
tx.executeSql(Queries.checkForExistingTable, ["decks"],
(_, { rows }) => res(rows._array.length > 0),
(_, error) => rej(error)
);
}));
}
export function populateInitialData(queryList = [
Queries.dropDeckScores,
Queries.dropCards,
Queries.dropDecks,
Queries.createDecks,
Queries.createCards,
Queries.createDeckScores,
]) {
console.log("populate data called");
return new Promise((res, rej) => db.transaction(tx => {
const func = boundLoggingTx.bind(tx); // to shorten function call by binding tx to this keyword
const noLogFunc = boundNoLogTx.bind(tx);
queryList.forEach((query) => noLogFunc(query));
Object.keys(decks).forEach((name) => {
noLogFunc(Queries.createDeck, [name, getSafeTimeISO(), "Uncategorized"]);
decks[name].questions.forEach(card => {
let time = getSafeTimeISO()
noLogFunc(Queries.createCard, [time, name, card.question, card.answer]);
});
});
tx.executeSql(Queries.getDecksAndCards, [],
(_, { rows }) => res(rows._array),
(_, error) => rej(error));
}));
}
export function getDecksAndCards() {
console.log("get decks and cards called");
return new Promise((res, rej) => db.transaction(tx => {
tx.executeSql(Queries.getDecksAndCards, [],
(_, { rows }) => res(rows._array),
(_, error) => rej(error));
})
);
}
export function createDeck(title, category = "Uncategorized", created = getCurrentTimeISOString()) {
return new Promise((res, rej) => db.transaction(tx => {
tx.executeSql(Queries.createDeck, [title, created, category],
(_, { rows }) => {},
(_, error) => rej(error)
);
tx.executeSql(Queries.getDeck, [title],
(_, { rows }) => res(rows._array),
(_, error) => rej(error));
})
);
}
export function createCard(deck_id, question, answer, card_id = getCurrentTimeISOString()) {
return new Promise((res, rej) => db.transaction(tx => {
tx.executeSql(Queries.createCard, [card_id, deck_id, question, answer],
(_, { rows }) => {},
(_, error) => rej(error));
tx.executeSql(Queries.getCard, [card_id],
(_, { rows }) => res(rows._array),
(_, error) => rej(error));
})
);
}
// redux actions related to receiving initial data
export const handleReceiveDecks = () => ( // uses redux-thunk
async (dispatch) => {
// dispatch(startLoading()); // not necessary as loading is currently extremely quick
try {
const existingData = await checkForExistingTable();
let data;
if (existingData) {
console.log("Is existing data:", existingData); // does get here and log true
data = await getDecksAndCards();
} else {
console.log("Is not existing data:", existingData);
data = await populateInitialData();
}
const [ decks, categories ] = formatDecksAndCards(data);
dispatch(receiveDecks(decks));
dispatch(receiveCategories(categories));
// dispatch(endLoading()); // not necessary as laoding is currently extremely quick
} catch (error) {
console.log(error);
}
}
)
const receiveDecks = (decks) => {
return {
type: RECEIVE_DECKS,
decks,
};
}
export function receiveCategories(categories) {
return {
type: RECEIVE_CATEGORIES,
categories,
};
}
function formatDecksAndCards(data) {
let decks = {};
let categories = {};
data.forEach((card) => {
// builds decks and categories from sql data for redux store
if (decks[card.title] !== undefined) {
// primary deck data already in decks - just add the card to the questions arr
decks[card.title].questions = decks[card.title].questions.concat([{
question: card.question,
answer: card.answer,
card_id: card.card_id,
deck_id: card.deck_id
}]);
} else {
// deck not yet created in decks
let { title, category, create_date, last_score, last_tested, card_id, question, answer, deck_id } = card;
decks[card.title] = {
title,
category,
create_date,
last_score,
last_tested,
questions: [
{
card_id,
question,
answer,
deck_id
}
]
};
if (category === null) {
// uncategorized deck
if (categories["Uncategorized"] !== undefined) {
// uncategorized category already exists in categories
categories["Uncategorized"].add(title);
} else {
// uncategorized category not yet created in categories
categories["Uncategorized"] = new Set([title]);
}
}
else if (categories[category] !== undefined) {
// category already exists in categories and category is not null
categories[category].add(title);
} else {
// category does not exist in categories - make new set
categories[category] = new Set([title]);
}
}
});
return [decks, categories];
}
// component that calls the function
class Home extends React.Component {
state = {
selectedCategory: "Show All",
isModalVisible: false,
}
componentDidMount() {
this.props.dispatch(handleReceiveDecks());
// this.askPermission(); // commented to prevent generating new notifications
clearLocalNotifications(); // for development
if (this.props.activeDeck !== null) {
this.props.dispatch(clearActiveDeck());
}
}
// etc...
// relevant section of decks reducer
switch(action.type) {
case RECEIVE_DECKS:
return {
...store,
...action.decks
};
// relevant section of categories reducer
switch (action.type) {
case RECEIVE_CATEGORIES:
return {
...store,
...action.categories,
};
// related helpers
const setIncrement = () => {
// deterministic time increment to prevent primary key collisions in database
let cache = 0;
return () => {
cache+=1;
return cache;
}
}
const getIncrement = setIncrement();
const getSafeTime = () => {
return new Date(Date.now()+getIncrement());
}
export const getSafeTimeISO = () => {
// prevents collisions in initial data population
// for in-app use - use getCurrentTimeISOString
return getSafeTime().toISOString();
}
This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.