zenithtekla/ecms.js

## ecms.js
var mongoose = require('mongoose'),
  Schema = mongoose.Schema;

/**
 * Schema
 */

var Schema1 = new Schema({
  asset_number: String,
  last_cal: Date,
  schedule: Number,
  next_cal: Date,
  files: [{
    data: Buffer,
    contentType: String
  }],
  model: String,
  location: {
    id: Number,
    desc: String
  }
});

mongoose.model('Equipment', Schema1);

/*
I don't know the data and couldn't remember after days and couldn't derive the right relationship for the tables.
 Days of burying our heads into the sand writing JOIN are passed as the logic later requires testing, fixing and re-validation.
 I need to derive the right relationship to tie my Sequelize data models.
 So, I went ahead and combined the 3 tables into the mongo Schema in 2 assumptive cases below. Software development deals a lot with assumptions
 when I don't usually see real data fly into the tables. I need nothing else than a concatenated schema object.
 */


## procedure.sql
DELIMITER //
CREATE PROCEDURE new_student_with_class_and_lession(
  firstName CHAR(25), lastName CHAR(25),
    className1 CHAR(25), period1 CHAR(25),
    className2 CHAR(25), period2 CHAR(25),
        lessionName1 CHAR(25), lessionName2 CHAR(25))
BEGIN
START TRANSACTION;
   INSERT INTO student_table (firstname, lastname)
     VALUES(firstName, lastName);

   SET lastStudentId = (SELECT LAST_INSERT_ID());

   INSERT INTO class_table (className1, period1, studentId)
     VALUES(className1, period, lastStudentId);

   SET lastStudentId = (SELECT LAST_INSERT_ID());
   INSERT INTO class_table (className2, period2, studentId)
     VALUES(className2, period, lastStudentId);

   INSERT INTO lession_table (lessionName1, classId)
     VALUES(lessionName1, LAST_INSERT_ID());
   INSERT INTO lession_table (lessionName2, classId)
     VALUES(lessionName2, LAST_INSERT_ID());
COMMIT;
END//
DELIMITER ;


SELECT
		lt.lessionName as LESSION
	FROM lession_table lt
	LEFT JOIN class_table ct
		ON ct.classId = lt.classId
	LEFT JOIN student_table st
		ON st.studentId = ct.studentId
	WHERE st.studentId = 1
	ORDER BY lt.id;


	SELECT lt.lessionName as LESSION
	FROM student_table as st
	LEFT JOIN class_table as ct
		ON st.studentId = ct.studentId
	LEFT JOIN lession_table as lt
		ON lt.classId = ct.classId
	WHERE st.studentId = 1;
	var mongoose = require('mongoose'),
	Schema = mongoose.Schema;

	/**
	* Schema
	*/

	var Schema1 = new Schema({
	asset_number: String,
	last_cal: Date,
	schedule: Number,
	next_cal: Date,
	files: [{
	data: Buffer,
	contentType: String
	}],
	model: String,
	location: {
	id: Number,
	desc: String
	}
	});

	mongoose.model('Equipment', Schema1);

	/*
	I don't know the data and couldn't remember after days and couldn't derive the right relationship for the tables.
	Days of burying our heads into the sand writing JOIN are passed as the logic later requires testing, fixing and re-validation.
	I need to derive the right relationship to tie my Sequelize data models.
	So, I went ahead and combined the 3 tables into the mongo Schema in 2 assumptive cases below. Software development deals a lot with assumptions
	when I don't usually see real data fly into the tables. I need nothing else than a concatenated schema object.
	*/
	DELIMITER //
	CREATE PROCEDURE new_student_with_class_and_lession(
	firstName CHAR(25), lastName CHAR(25),
	className1 CHAR(25), period1 CHAR(25),
	className2 CHAR(25), period2 CHAR(25),
	lessionName1 CHAR(25), lessionName2 CHAR(25))
	BEGIN
	START TRANSACTION;
	INSERT INTO student_table (firstname, lastname)
	VALUES(firstName, lastName);

	SET lastStudentId = (SELECT LAST_INSERT_ID());

	INSERT INTO class_table (className1, period1, studentId)
	VALUES(className1, period, lastStudentId);

	SET lastStudentId = (SELECT LAST_INSERT_ID());
	INSERT INTO class_table (className2, period2, studentId)
	VALUES(className2, period, lastStudentId);

	INSERT INTO lession_table (lessionName1, classId)
	VALUES(lessionName1, LAST_INSERT_ID());
	INSERT INTO lession_table (lessionName2, classId)
	VALUES(lessionName2, LAST_INSERT_ID());
	COMMIT;
	END//
	DELIMITER ;


	SELECT
	lt.lessionName as LESSION
	FROM lession_table lt
	LEFT JOIN class_table ct
	ON ct.classId = lt.classId
	LEFT JOIN student_table st
	ON st.studentId = ct.studentId
	WHERE st.studentId = 1
	ORDER BY lt.id;


	SELECT lt.lessionName as LESSION
	FROM student_table as st
	LEFT JOIN class_table as ct
	ON st.studentId = ct.studentId
	LEFT JOIN lession_table as lt
	ON lt.classId = ct.classId
	WHERE st.studentId = 1;