Pushkin-DB

Overview

This DB worker has a simple job with a relatively open API. There is a worker class that handles writes and reads through a message queue. All models are defined in ./models/ any bookshelf model that is defined in there will automatically get methods added to the worker class. add custom methods in worker.js to do custom functionality

Core Features

• regulates and controls DB access in a structured manner
• provides multi client asynchronos access to the db with persistence and the ability to survive restarts/crashes.
• provides multi DB logging, with every transaction on the primnary being logged and saved in a secondary DB.
• uses Bookshelf.js/knex.js as its core ORM for an easiliy extensible way to define models
• provides an easy to use structure for seeds
• tested

Get started

1. Creating/modifying the db migrations in the migrations directory to match your schema
2. Create/modify the bookshelf models found in models directory (they will be automatically sourced in by the db reader)
3. Create/modify the csv files found in seeds/

Pushkin-db Folder Structure

pushkin-db consists the following important folders and files:

/migrations

• migration files created by pushkin generate model [yourQuizName] will be listed in this folder.
• pushkin generate model [yourQuizName] will create 5 basic tables: users, responses, trials, questions, and choices
• The file names are in this format : [timeStamp]_create_[yourQuizName]_[table].js
• example: migrations/20170515072132_create_whichenglish_trials.js

/models

• bookshelf models created by pushkin generate model [yourQuizName] will be listed in this folder.
• pushkin generate model [yourQuizName] will create 5 basic bookshelf models: users, responses, trials, questions, and choices
• bookshelf model files generated are grouped in [yourQuizName] folder under this directory
• example: models/whichenglish/user.js

/seeds

• seed files created by pushkin generate model [yourQuizName] will be listed in this folder.
• pushkin generate model [yourQuizName] will create 3 basic CSV templates and a index.js file
• seed files generated are grouped in [yourQuizName] folder under this directory
• you could fill out these csv files by using a csv editor or your text editor. These data filled in are your actual data for [yourQuizName]. When you are filling out these csv files, make sure you have the correct trial name in Questions.csv, this handles the relation between a trial and it’s questions/choices
• example: seeds/whichenglish/Choice.csv

/seeder.js

• this is the overarching seeder file that handles seeding [yourQuizName]. Please see Seed Quiz section of this read me for more information

/worker.js

• this file lists all the DB methods used by the routes. Please see Worker section of this read me for more information

Extension

• Change the timezone to whatever you want by setting the TZ env variable in the Dockerfile
• Share and publish other scripts that you may find useful

Worker

Kind: global class

• Worker
  • new Worker()
  • .createModel(data) ⇒ Promise
  • .findModel(id, [relations]) ⇒ Promise
  • .updateModel(id, data) ⇒ Promise
  • .deleteModel(id) ⇒ Number
  • .queryModel(query, relations) ⇒ Promise
  • .rawModel(query) ⇒ Promise
  • .allModels() ⇒ Promise
  • .getInitialQuestions(trialName) ⇒ Promise
  • .setUserLanguages(userId, set) ⇒ Promise
  • .getResults(userId) ⇒ Promise

new Worker()

DB writer and reader class with automatically generated methods These methods are created automatically based off what it defined by bookshelf

worker.createModel(data) ⇒ Promise

Create a new Model in the DB

Kind: instance method of Worker

Returns: Promise - The saved model

Fulfill: Object

Reject: Error

Param	Type	Description
data	any	model to be created

Example

createUser({ name: "Methuselah", age: 1000 })

worker.findModel(id, [relations]) ⇒ Promise

Kind: instance method of Worker

Fulfill: Object - The found model

Reject: Error

Param	Type	Description
id	number	The id of the model looking for
[relations]	Array.<string>	an array of related models to fetch

Example

findUser(1, ['posts'])

worker.updateModel(id, data) ⇒ Promise

Kind: instance method of Worker

Fulfill: Object - The newly updated model

Reject: Error

Param	Type	Description
id	number	the id of the model being updated
data	Object	the data to update

Example

updateUser(1, { age: 969 })

worker.deleteModel(id) ⇒ Number

Kind: instance method of Worker

Returns: Number - 0 if success

Param	Type	Description
id	Number	the id of the model to be deleted

Example

deleteUser(1);

worker.queryModel(query, relations) ⇒ Promise

Kind: instance method of Worker

Fulfill: Object[] - An array of models returned

Reject: Error

Param	Type	Description
query	Array.<Array.<string>>	a knex.js query array see http://knexjs.org/#Builder
relations	Array.<string>	an Array of relations

Example

queryModel([
 ['where', 'other_id', '=', '5'],
 ['where', 'name', '=', 'foo']
],
['posts']
)

worker.rawModel(query) ⇒ Promise

Allows raw queries on DB

Kind: instance method of Worker

Fulfill: Object[] - an array of results

Reject: Error

Param	Type	Description
query	Array.<Array.<String>>	a raw knexjs query array

Example

rawUser([
  ['where', 'name', '=', 'Methuselah'],
  ['where', 'age', '>', 900 ],
])

worker.allModels() ⇒ Promise

Return all models in DB

Kind: instance method of Worker

Fulfill: Object[] - all models in DB

Reject: Error

Example

allUsers()

worker.getInitialQuestions(trialName) ⇒ Promise

Return all models in DB

Kind: instance method of Worker

Fulfill: Object[] - questions for that Trial

Reject: Error

Todo

• Make this query on the trial Name

Param	Type	Description
trialName	string	The name of the trial looking for inital questions for

worker.setUserLanguages(userId, set) ⇒ Promise

Sets a users Languages

Kind: instance method of Worker

Fulfill: Object - the user with their languages

Rejects: Error

Param	Type	Description
userId	number	the name of the id to set
set	Object
set.primaryLanguages	Array.<String>	the user's primary Languages
set.nativeLanguages	Array.<String>	the user's native Languages

Example

setUserLanguages(1, {
 primaryLanguages: ["Hebrew", "Aramaic"],
nativeLanguages: ["Hebrew"]
})

worker.getResults(userId) ⇒ Promise

Get the results from the DB

Kind: instance method of Worker

Fulfill: Object[] - an array of the top 3 languages for this user

Rejects: Error

Param	Type	Description
userId	number	the user whose results you are grabbing

Seed Quiz

IMPORTANT! Before you start ...

Please ensure all things listed below:

• make sure pushkin docker container is running. please execute docker-compose -f docker-compose.debug.yml up in your main pushkin folder.

• make sure you’ve properly created a model pushkin generate model [yourQuizName], all of the migration files, model files and seed files could be found under pushkin-db/migrations, pushkin-db/models, and pushkin-db/seeds.

• make sure you’ve properly filled out all of your seed csv files in pushkin-db/seeds/[yourQuizName]. Please double make sure you have the correct trial name in Questions.csv, this handles the relation between a trial and it’s questions/choices

• make sure you’ve ran your migrations after the model is created

Instructions

After you've ensured all listed in Before you start section of this read me, you are ready to seed a quiz:

1. bash into pushkin_db-worker_1 : bash -c "clear && docker exec -it pushkin_db-worker_1 sh”
2. execute node seeder.js [yourQuizName] example node seeder.js whichenglish. When you are running this command, make sure [yourQuizName] matches the model you’ve created for this quiz. whichenglish is not the same as whichEnglish.
3. if you see "done seeding!" in your terminal, you are all done!

How does it work:

long story short, when you are generating the model for your quiz, it grabs the seeds templates and copies the file over to pushkin-db/seeds/[yourQuizName]. there will be a index.js in each of the models you’ve generated. By looking into the csv files in the current quiz folder, this file inserts the proper data into each table corresponding to the current quiz. As you can see, each of these index.js files exports a function for the main seeder.js to use.

The main seeder.js listens to the command node seeder.js [yourQuizName], it grabs the second argument which is yourQuizName and looks for its corresponding folder in pushkin-db/seeds/. After it’ve found the index.js in that folder, it simply execute the function that is being exported and run it. That’s it!

How to modify your seed files:

If you are unhappy with the data inserted in the csv files, please feel free to modify them by using a csv tool(recommended), or in your text editor. If you’ve modified your tables, and you need to change the seed files, please go to the quiz seed file your want to change under pushkin-db/seeds/[yourQuizName]/index.js.The current setup in these index.js files is:

• reads the csv files in the current quiz folder
• deletes any data left in the tables
• inserts a fresh copy of the csv data to the tables

You could add your code on top of what we have, we are using knex to handle insertions and deletions. As long as it make sense to the tables you’ve modified ;) if you just can’t stand the confirmations we currently have when you executenode seeder.js [yourQuizName], simply goto the main seeder.js file in pushkin-db, you could take out the warnings or mess with the copy change in the warning messages, change their colors, etc. It’s up to you. Seriously.

Extension

If you’ve changed your table relations, please make sure to change the bookshelf models (pushkin-db/models/[yourQuizName]) to match the modified tables!