segunda-feira, 25 de outubro de 2010

Explorar Mensagem do Moodle com Comando SQL

A Plataforma Moodle tem um sistema de envio de mensagem interna. Caso você precise explorar   esse sistema sem usar a interface do Moodle, será necessário escrever comando SQL para comunicar diretamente com a base de dados.

    Através do comando SQL você pode:
  • Consultar histórico de mensagens enviadas; 
  • Pesquisar conteúdo das mensagens;

  • Apagar mensagens que ainda não foram lidas;
  • Pesquisar usuário que mais enviaram ou receberam mensagens.

As mensagens do Moodle são organizadas em duas tabelas:
  • mdl_message – Tabela que registra as mensagens enviadas
  • mdl_message_read - Tabela que registra as mensagens lidas. Armazena o histórico das mensagens.

    Quando uma mensagem é enviada, é armazenada na tabela mdl_message. Quando o destinatário recebe, ou seja, visualiza  na tela, a mensagem é transferida para a tabela mdl_message_read. Na tabela    mdl_message  só ficam  as mensagens que ainda não foram lidas. Já a  tabela mdl_message_read  só ficam as mensagens que já foram lidas.


Agora que já entendeu como funciona as tabelas, vamos ver os comandos SQL.

1- Consultar todas as mensagens enviadas que ainda não foram lidas.
SELECT m.id,m.timecreated, r.firstname, r.lastname,d.firstname, d.lastname, m.message FROM mdl_user r INNER JOIN mdl_message m ON r.id=m.useridfrom INNER JOIN mdl_user d ON d.id=m.useridto

Essa consultar retorna os seguintes campos:
  • m.id  - Id da mensagem
  • m.timecreated – Data do envio da mensagem
  • r.firstname e r.lastname – Nome do remitente
  • d.firstname e d.lastname – Nome do destinatário
  • m.message – Texto da mensagem

2- Consultar todas as mensagens lidas – Histórico de mensagens.


SELECT m.id,m.timecreated, m.timeread,r.firstname, r.lastname,d.firstname,d.lastname, m.message,m.mailed FROM mdl_user r INNER JOIN mdl_message_read m ON r.id=m.useridfrom INNER JOIN mdl_user d ON d.id=m.useridto

Essa consultar retorna os seguintes campos:
  • m.id  - Id da mensagem
  • m.timecreated – Data do envio da mensagem
  • m.timeread – Data da leitura da mensagem
  • r.firstname e r.lastname – Nome do remitente
  • d.firstname e d.lastname – Nome do destinatário
  • m.message – Texto da mensagem
  • m.mailed – Controle de envio de mensagem por e-mail

3 - Monitorar as mensagens enviadas por um determinado usuário que ainda não foram  lidas

SELECT m.id,m.timecreated, d.firstname, d.lastname, m.message FROM mdl_message m  INNER JOIN mdl_user d ON d.id=m.useridto WHERE m.useridfrom=?

Passe o parâmetro id do usuário em  m.useridfrom=?

Essa consultar retorna os seguintes campos:
  • m.id  - Id da mensagem
  • m.timecreated – Data do envio da mensagem
  • d.firstname e d.lastname – Nome do destinatário
  • m.message – Texto da mensagem

4 - Monitorar histórico das  mensagens enviadas por um determinado usuário. Mensagens lidas pelos destinatários.

SELECT m.id,m.timecreated, m.timeread, d.firstname, d.lastname, m.message, m.mailed  FROM mdl_message_read m  INNER JOIN mdl_user d ON d.id=m.useridto WHERE m.useridfrom=?

Passe o parâmetro id do usuário em  m.useridfrom=?


Essa consultar retorna os seguintes campos:
  • m.id  - Id da mensagem
  • m.timecreated – Data do envio da mensagem
  • m.timeread – Data da leitura da mensagem
  • d.firstname e d.lastname – Nome do destinatário
  • m.message – Texto da mensagem
  • m.mailed – Controle de envio de mensagem por e-mail

5- Pesquisar o conteúdo das mensagens enviadas no histórico das mensagens pela palavra-chave


SELECT m.id,m.timecreated, m.timeread,r.firstname, r.lastname,d.firstname,d.lastname, m.message,m.mailed FROM mdl_user r INNER JOIN mdl_message_read m ON r.id=m.useridfrom INNER JOIN mdl_user d ON d.id=m.useridto WHERE m.message LIKE '%texto da pesquisa%'

Passe o texto a ser pesquisado no comando  LIKE '%texto da pesquisa%'
Essa consultar retorna os mesmos campos da pesquisa do item 2.

6- Lista de usuários que mais enviarem  as mensagens
SELECT r.firstname, r.lastname,d.firstname, COUNT(m.useridfrom)  FROM mdl_user r INNER JOIN mdl_message_read m ON r.id=m.useridfrom INNER JOIN mdl_user d ON d.id=m.useridto GROUP BY r.firstname, r.lastname,d.firstname ORDER BY COUNT(m.useridfrom) DESC

Essa consulta retorna uma lista de usuários  (remetentes) e quantidade de mensagens enviadas pela ordem decrescente.  Lista os usuários que mais enviaram as mensagens.

Essa consulta é feita no histórico de mensagens. Para efetuá-la nas mensagens recentes, ou seja,  ainda não lidas, basta substituir a tabela  mdl_message_read para  mdl_message e mantar o restante de código inalterado.


7- Lista de usuários que mais receberam mensagens

SELECT d.firstname, d.lastname,d.firstname, COUNT(m.useridto)  FROM mdl_user d INNER JOIN mdl_message_read m ON  d.id=m.useridto GROUP BY d.firstname, d.lastname,d.firstname ORDER BY COUNT(m.useridto) DESC

Essa consulta retorna uma lista de usuários  (destinatários) e quantidade de mensagem recebidas pela ordem decrescente.  Lista os usuários que mais receberam as mensagens.

Essa consulta é feita no histórico de mensagens. Para efetuá-la nas mensagens recentes, ou seja,  ainda não lida, basta substituir a tabela  mdl_message_read para  mdl_message e mantar o restante de código inalterado.

8 – Apagar as mensagens enviada que ainda não foram lidas

DELETE  FROM mdl_message

9 – Apagar  o histórico das mensagens. Mensagens lidas

DELETE  FROM mdl_message_read

    Essas dicas ajudam você a desvendar como é organizado as mensagens no Moodle na camada de base de dados. Isso é tudo que você precisa para mapear erros ou falha do Moodle e também para para planejar integração com outros sistemas.

sexta-feira, 22 de outubro de 2010

Consultar os Tópicos do Fórum mais Acessados no Moodle com Comando SQL

Se você trabalha na pate de TI já deve ter recebido alguma solicitação do tutor ou coordenador do curso para montar um gráfico ou tabela com os típicos mais acessados do fórum.

    Ao receber essa demanda, você fuça um pouco o Moodle e percebe que não existe essa opção de relatório. Mesmo assim a equipe pedagógica fica no seu cangote esperando uma solução.

    Neste caso, você não tem outra alternativa que não seja extrair esses dados diretamente da base de dados do Moodle com comando SQL. Para aliviar a sua barra,  facilitando algumas dias ou semanas  de pesquisa vai aí  o macete.
Para consultar os tópicos mais acessados é necessário fazer a junção das seguintes tabelas:

  • mdl_forum – Tabela de fórum
  • mdl_forum_discussions – Tabela de tópicos
  • mdl_modules – Tabela de atividades do curso
  • mdl_log – Tabela de log
SQL para MySql

SELECT d.id,d.name,COUNT(l.info) FROM  mdl_forum_discussions d INNER JOIN mdl_forum f on f.id=d.forum  INNER JOIN mdl_course_modules cm ON f.id=cm.instance INNER JOIN mdl_modules m  ON cm.module=m.id INNER JOIN mdl_log l ON l.cmid=cm.id WHERE f.id=? AND m.name='forum' AND l.module='forum' AND l.action LIKE '%discussion%' AND l.info=d.id GROUP BY d.id,d.name ORDER BY COUNT(l.cmid) DESC

SQL para PostgreSQL


SELECT d.id,d.name,COUNT(l.info) FROM  mdl_forum_discussions d INNER JOIN mdl_forum f on f.id=d.forum  INNER JOIN mdl_course_modules cm ON f.id=cm.instance INNER JOIN mdl_modules m  ON cm.module=m.id INNER JOIN mdl_log l ON l.cmid=cm.id WHERE f.id=? AND m.name='forum' AND l.module='forum' AND l.action LIKE '%discussion%' AND l.info=CAST(d.id as varchar) GROUP BY d.id,d.name ORDER BY COUNT(l.cmid) DESC

O que diferencie os dois comandos SQL é que na  consulta para PostgreSQL há conversão d.id para texto com comando CAST: l.info=CAST(d.id as varchar).  Isso  porque o PostgreSQL não compara campo numérico com texto. Já MySQL é mais tolerante. Tirando isso, o restante do comando é igual.

Essa consulta extrai uma lista com os seguintes campos:

  • d.id – Id do tópico
  • d.name – Nome do tópico
  • COUNT(l.info) – Quantidade de acesso (click) no tópico

Você precisa especificar o id do fórum passando parâmetro em  f.id=? logo após o comando WHERE.
Caso não souber o id do fórum, localize na base de dados pelo nome do fórum com o seguinte comando SQL:

SELECT id FROM mdl_forum WHERE name='Nome do Fórum'

Bem, agora que você tem o comando SQL, só falta implementar isso numa linguagem de programação ou então executar a consulta no banco  e copiar o dados para excel para montar um gráfico bonito. Assim você dá solução à demanda de forma rápida e ninguém fica no seu cangote.

domingo, 17 de outubro de 2010

Apagar Nota, Atividades e Log do Aluno no Curso do Moodle com Comando SQL

Ao cancelar a inscrição de um aluno no curso no ambiente Moodle, os registros de na base de dados (atividades realizadas, nota e log) vinculado à matricula   não serão excluídos automaticamente, ou seja, em efeito cascata.  Uma das alternativas para remover os registros órfãos é executar o comando SQL diretamente na base de dados.
  
Para excluir os dados órfãos de uma matricula  no curso  é necessário dois parâmetros:

  • id do curso – Chave de identificação do curso na tabela mdl_course
  • id do usuário - Chave de identificação do curso na tabela mdl_user
Há uma postagem no blogue que explique isso. Clique aqui para ler.


Tendo os parâmetros da chave do curso e do usuário, só resta   apagar os registro com comando DELETE passando os parâmetros da chave do usuário e curso.
Passe o parâmetro da chave do curso em course=?   ou courseid =? e do usuário em userid=?


Apagar Log

DELETE FROM mdl_log WHERE course=? AND userid=?
   
Apagar Notas
DELETE FROM mdl_grade_grades WHERE  userid=? and itemid IN  (SELECT id FROM mdl_grade_items WHERE courseid=?)

    A sub consulta apaga apenas as notas de um determinado curso.
A tabela mdl_grade_grades é o repositório final de notas.


Apagar Atividades

Apagar nota do fórum
DELETE FROM mdl_forum_ratings WHERE post IN (SELECT p.id FROM mdl_forum_posts p INNER JOIN mdl_forum_discussions d ON p.discussion=p.id  WHERE p.userid=? AND d.course=?)

Apagar os comentários

DELETE FROM mdl_forum_posts WHERE userid=?  AND  discussion IN (SELECT id FROM mdl_forum_discussions WHERE course=?)

Não é recomendável apagar os comentários já que os comentários aninhados ficarão órfãos, ou seja, as respostas das respostas.

Apagar tópicos
DELETE  FROM mdl_forum_discussions WHERE userid=? AND course=?

Não é recomendável apagar os tópicos já que os comentários aninhados ficarão órfãos.

Apagar Tarefas

DELETE FROM mdl_assignment_submissions WHERE userid =? AND assignment IN (SELECT id FROM mdl_assignment WHERE course =?)

Ao remover as tarefas, as notas serão removidas automaticamente.


Apagar questionário

Apagar nota final
DELETE FROM mdl_quiz_grades WHERE userid =?  AND quiz IN (SELECT id FROM mdl_quiz WHERE course =?)

Apagar Tentativas de Respostas
DELETE FROM mdl_question_states WHERE  attempt IN (SELECT t.id FROM mdl_quiz_attempts t INNER JOIN  mdl_quiz q  ON t.quiz=q.id WHERE t.userid= ? AND course =?)

Apagar cada tentativa (inclusive a nota final)
DELETE FROM mdl_quiz_attempts WHERE userid =?  AND quiz IN (SELECT id FROM mdl_quiz WHERE course =?)

Para fazer limpeza total, os dados do aluno devem ser removida de todas de todas as atividades instanciadas no curso. Os comandos SQL acima demostrados se restringiram em remover as atividades do fórum, tarefa e questionário.  Para as demais atividades, siga a mesma lógica. Identifica as tabelas e apague os dados.
Feito isso, todo o histórico do aluno será removida da base de dados. Ao ser reinscrito no curso,  não terá nenhuma nota e nem log de acesso.

Extrair id do Usuário e do Curso no Moodle com Comando SQL


Id é  o parâmetro de identificação do registro na base de dados.


Id do Usuário

Comandos SQL que  recuperam  o id do usuário

Por -Email:
SELECT id FROM mdl_user WHERE email='e-mail'
 
Por login:
SELECT id FROM mdl_user WHERE username='login'

Por login e senha:
SELECT id FROM mdl_user WHERE username='login' AND password=MD5('senha')

Parâmetro GET do URL do Moodle com Id do usuário

URL do perfil do usuário:
http://[enderco do domininio]/user/view.php?id=21&course=3
O parâmetro id=21  é  a chave de identificação do usuário


Id do Curso
Comandos SQL que  recuperam o id do curso


Por nome
SELECT id FROM mdl_course WHERE fullname='Nome do Curso'

Pela Abreviatura
SELECT id FROM mdl_course WHERE shortname='Abreviatura'

Parâmetro GET do URL do Moodle com id do curso

URL do curso
http://[endereco dominico ]/course/view.php?id=3
O parâmetro id=3 é  a chave de identificação do curso.