BancoMoodle

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

/** First, we create tables like moodle tables, but simpler.
    I am not dealing with course here, suppose the table #users already
    has only the students of a specific course.
**/

CREATE table #users
([id] INT, [idnumber] VARCHAR(100), [firstname] VARCHAR(100),
 [lastname] VARCHAR(100), [email] VARCHAR(100), [institution] VARCHAR(255));
 
/** Got all modules used in courses **/
CREATE table #course_modules
([id] INT, [course] INT, [module] INT, [instance] INT, [section] INT,
[completion] INT, [completionview] INT);

CREATE table #modules
([id] INT, [name] VARCHAR(50));

CREATE TABLE #course_sections
([id] INT, [course] INT, [section] INT, [name] VARCHAR(50), [sequence] VARCHAR(100));

CREATE TABLE #forum
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #checklist
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #assign
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #quiz
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #resource
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #questionnaire
([id] INT, [course] INT, [name] VARCHAR(50));

CREATE TABLE #page
([id] INT, [course] INT, [name] VARCHAR(50));

/** completion data **/
CREATE table #course_modules_completion
([id] INT , [coursemoduleid] INT, [userid] INT, [completionstate] INT, [viewed] INT)




/** Now let's populate these tables with some data **/
INSERT INTO #users
([id], [idnumber], [firstname], [lastname], [email], [institution])
VALUES
    (102345, 'idMaria', 'Maria', 'da Silva', 'dasilva@email.com','EscoladeMaria'),
    (103735, 'idJoao', 'João', 'de Deus','dedeus@email.com','EscoladeJoao');    
    
INSERT INTO #modules /** Puttin those used in EducaSP courses **/
([id], [name])
VALUES
    (10, 'forum'),            /** Fórum **/
    (18, 'page'),             /** Vídeos **/
    (19, 'questionnaire'),    /** Pesquisa **/
    (20, 'quiz'),             /** Questionário **/
    (21, 'resource'),         /** Arquivos **/
    (24, 'url'),              /** URL **/
    (28, 'assign'),           /** Tarefas **/
    (38, 'checklist');        /** Presença **/

INSERT INTO #course_modules
([id], [course], [module], [instance], [section], [completion], [completionview])
VALUES
    (90752,1465,10,6849,21485,0,0),
    (93151,1465,38,5,21485,2,0),
    (93259,1465,28,15781,21486,1,0),
    (93308,1465,20,3925,21487,2,1),
    (93416,1465,18,4151,21486,2,1),
    (93417,1465,21,23560,21488,2,1),
    (93440,1465,19,1351,21489,2,0);

INSERT INTO #course_sections
([id], [course], [section], [name], [sequence])
VALUES
  (21485,1465,0,'','90752,93151'),
  (21486,1465,1,'Teste de tópico','93259,93416'),
  (21487,1465,2,'','93308'),
  (21488,1465,3,'','93417'),
  (21489,1465,4,'','93440');


/** Now let's put the modules **/
INSERT INTO #forum ([id], [course], [name])
VALUES
(6849,1465,'Avisos');

INSERT INTO #checklist
([id], [course], [name])
VALUES
(5,1465,'TestePresença');

INSERT INTO #assign
([id], [course], [name])
VALUES
(15781,1465,'Teste Tarefa');

INSERT INTO #quiz
([id], [course], [name])
VALUES
(3925,1465,'Tchubirabirom quiz');

INSERT INTO #page
([id], [course], [name])
VALUES
(4151,1465,'Teste de página');

INSERT INTO #resource
([id], [course], [name])
VALUES
(23560,1465,'Teste Recurso');

INSERT INTO #questionnaire
([id], [course], [name])
VALUES
(1351,1465,'Teste de Enquete');

INSERT INTO #course_modules_completion
([id] , [coursemoduleid] , [userid] , [completionstate], [viewed])
VALUES
(920307,93151,102345,0,0),
(920327,93151,103735,1,0),
(972378,93259,102345,1,0),
(980037,93416,102345,1,1),
(980253,93259,103735,1,0),
(982442,93417,102345,1,1),
(982488,93308,102345,3,1),
(982498,93308,103735,2,1);

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results