databank

dit document beschrijft de databank in Drupal

1. node: unieke node_id (nid)
2. node_field_data: metadata over node (aanmaak-, wijzigingsdatum, …)
3. node_body: inhoud
4. node_revison: nid, vid
5. node_field_revision: metadata over node (aanmaakdatum, userid)
6. node_revision_body: inhoud revisie
7. users: overzicht gebruikers
8. users_field_data: detail over gebruikers (naam, email, userid, …)
9. node_field_<naam>: velden die je gebruikt binnen je node.

elke node heeft data in onderstaande tabellen

• node: nid, type (content type)
• node_body: eigenlijke inhoud van de node
• node_field_data: metadata over node: oa titel
• node_revision: info over revisies
• node_revision_body: inhoud van elke revisie
• node_field_revision: metadata over revisies

ahv volgende functies:

• FROM_UNIXTIME(<timestamp>):unixepoch in human-readable
• NOW(): huidige datum
  SELECT NOW()
• INTERVAL xx HOUR/DAY/MONTH/YEAR: xx uur/dagen/maanden/jaar seconden vroeger (-) of later (+)
  SELECT NOW() - INTERVAL 1 HOUR: 1 uur geleden
• YEAR(<timestamp>): vis het jaar uit de timestamp
  SELECT YEAR(FROM_UNIXTIME(created)) FROM l7gh_node_field_data
• MONTH(<timestamp>): vis de maand uit de timestamp
  SELECT MONTH(FROM_UNIXTIME(created)) FROM l7gh_node_field_data
• DAY(<timestamp>): vis de dag uit de timestamp
  SELECT DAY(FROM_UNIXTIME(created)) FROM l7gh_node_field_data

SELECT f.title,FROM_UNIXTIME(f.created) AS "Time created",FROM_UNIXTIME(f.changed) AS "Time modified", f.type,tax.name AS "Plaats" FROM l7gh_node_field_data f   
LEFT JOIN l7gh_node__field_plaats pl ON f.nid = pl.entity_id   
LEFT JOIN l7gh_taxonomy_term_field_data tax ON pl.field_plaats_target_id = tax.tid  
ORDER BY FROM_UNIXTIME(f.changed) ASC;

SELECT title,nid,FROM_UNIXTIME(created) AS "Time created",FROM_UNIXTIME(changed) AS "Time Last modified" FROM l7gh_node_field_data 
WHERE FROM_UNIXTIME(changed) > NOW() - INTERVAL 1 WEEK 
ORDER BY FROM_UNIXTIME(changed) DESC;

SELECT COUNT(title) AS Aantal,type FROM l7gh_node_field_data 
GROUP BY type 
ORDER BY Aantal DESC;

SELECT COUNT(title) AS "TOTAL ENTRIES",YEAR(FROM_UNIXTIME(created)) AS "YEAR" FROM l7gh_node_field_data 
GROUP BY YEAR(FROM_UNIXTIME(created)) 
ORDER BY YEAR(FROM_UNIXTIME(created)) DESC;

SELECT COUNT(title) AS "TOTAL ENTRIES",MONTH(FROM_UNIXTIME(created)) AS "MONTH" FROM l7gh_node_field_data 
WHERE type = 'dagboek' 
GROUP BY MONTH(FROM_UNIXTIME(created)) 
ORDER BY COUNT(title) DESC;

SELECT nid AS "Node ID",title,COUNT(vid) AS "Aantal revisies",FROM_UNIXTIME(created),FROM_UNIXTIME(changed) FROM l7gh_node_field_revision 
GROUP BY nid;

SELECT title AS "Entries in schrift" FROM l7gh_node_field_data INNER JOIN l7gh_node__field_geprint_ ON l7gh_node_field_data.nid = l7gh_node__field_geprint_.entity_id WHERE field_geprint__value = 1 and type = 'dagboek';

SELECT title AS "Entries in schrift" FROM l7gh_node_field_data LEFT JOIN l7gh_node__field_geprint_ ON l7gh_node_field_data.nid = entity_id WHERE field_geprint__value = 0 AND type = 'dagboek';

SELECT nid AS "Node ID" FROM l7gh_node_field_data LEFT JOIN l7gh_node__field_geprint_ ON l7gh_node_field_data.nid = entity_id WHERE field_geprint__value IS NULL AND type = 'dagboek';

INSERT INTO l7gh_node__field_geprint_ (entity_id,field_geprint__value) SELECT a.nid,p.field_geprint__value FROM l7gh_node_field_data a LEFT JOIN l7gh_node__field_geprint_ p ON a.nid = p.entity_id WHERE field_geprint__value IS NULL AND type = 'dagboek';

SELECT f.title, FROM_UNIXTIME(f.created), b.body_value FROM `l7gh_node__body` b INNER JOIN l7gh_node_field_data f ON f.nid = b.entity_id WHERE b.bundle = 'verslag'

voeg hier linken toe naar verdere uitleg