Databasemodel Drupal 6

Uit De Vliegende Brigade
(wijz) ← Oudere versie | Huidige versie (wijz) | Nieuwere versie → (wijz)
Naar navigatie springen Naar zoeken springen
Drupal 6 database-diagram uit 2007

Ik houd van SQL. Het komt uit de jaren '70, net als ik, en het past precies bij me. Daarom doe ik veel dingen graag rechtstreeks op database-niveau. Maar dan moet je wél weten dat je doet. En daar is oa. dit artikel voor.

Drupal + Übercart

  • access
  • accesslog
  • actions
  • actions_aid
  • authmap
  • batch
  • blocks
  • blocks_roles
  • boxes
  • cache
  • cache_block
  • cache_content
  • cache_filter
  • cache_form
  • cache_menu
  • cache_page
  • cache_update
  • ca_predicates
  • comments
  • content_field_image_cache
  • content_node_field
  • content_node_field_instance
  • content_type_product
  • date_formats
  • date_format_locale
  • date_format_types
  • files
  • filters
  • filter_formats
  • flood
  • history
  • imagecache_action
  • imagecache_preset
  • menu_custom
  • menu_links
  • menu_router
  • node
  • node_access
  • node_comment_statistics
  • node_counter
  • node_import_status
  • node_import_tasks
  • node_revisions
  • node_type
  • permission
  • role
  • search_dataset
  • search_index
  • search_node_links
  • search_total
  • semaphore
  • sessions
  • system
  • term_data
  • term_hierarchy
  • term_node
  • term_relation
  • term_synonym
  • uc_cart_products
  • uc_catalog_images
  • uc_countries
  • uc_flatrate_methods
  • uc_flatrate_products
  • uc_orders
  • uc_order_admin_comments
  • uc_order_comments
  • uc_order_line_items
  • uc_order_log
  • uc_order_products
  • uc_order_quotes
  • uc_order_statuses
  • uc_payment_receipts
  • uc_products
  • uc_product_classes
  • uc_product_features
  • uc_quote_product_locations
  • uc_quote_shipping_types
  • uc_store_footers
  • uc_taxes
  • uc_zones
  • url_alias
  • users
  • users_roles
  • variable
  • vocabulary
  • vocabulary_node_types
  • watchdog

Taxonomieën

Taxonomy-gerelateerde tabellen: 

* vocabulary            (vid, name, description, 
                         help, relations, 
                         hierarchy, multiple,    Definitie van een vocabulaire. 
                         required, tags,         vid = Vocabulaire ID 
                         module, weight)
* vocabulary_node_types (vid, type)              Lookup-tabel met vid's & node-types 
                                                 (bv. vid=15, type="product")
* term_data             (tid, vid, name,         Tabel met de eigenlijke taxa
                         description, weight)
* term_hierarchy        (tid, parent)            Bepaalt hierarchie tussen taxa
* term_node             (nid, tid, vid)          Associatie taxa ←→ nodes

* term_relation         (tid1, tid2, trid)       Leeg
* term_synonym          (tid, name, tsid)        Vrijwel leeg

Tabel variable

Drupal 6 heeft een tabel genaamd variable. Dit is wat daar zoal over gezegd wordt: 

Named variable/value pairs created by Drupal core or any other module or theme.
All variables are cached in memory at the start of every Drupal request so developers
should not be careless about what is stored here.

Om een indruk te geven, hierbij de eerste 23 regels van deze tabel voor een productie-site met bijna 1.000 regels: 

filter_html_1			i:1;
node_options_forum		a:1:{i:0;s:6:\"status\";}
menu_secondary_links_source	s:13:\"primary-links\";
install_profile			s:7:\"default\";
node_options_page		a:1:{i:0;s:6:\"status\";}
comment_page			s:1:\"0\";
drupal_private_key		s:64:\"1dacd904e91b5123458346b4206c985d1354e9677d35172ac0171420dfd9cc5a\";
cron_last			i:1445592878;
date_default_timezone		i:0;
configurable_timezones		s:1:\"1\";
date_format_short		s:11:\"d/m/Y - H:i\";
date_format_medium		s:14:\"D, d/m/Y - H:i\";
date_format_long		s:15:\"l, F j, Y - H:i\";
date_first_day			s:1:\"1\";
file_directory_temp		s:3:\"tmp\";
site_mail			s:31:\"info@example.com\";
site_slogan			s:57:\"Kohlebürsten für 30.000 verschiedene Werkzeuge ab Lager\";
site_mission			s:195:\"Wir haben Kohlebürsten auf Lager für rund 30.000 verschiedene Elektrowerkzeuge, und ein Katalog auf diese Seite um genau die richtige Bürste zu finden. Das können Sie nirgendwo anders finden.\";
anonymous			s:9:\"Anonymous\";
content_schema_version		i:6009;
poormanscron_lastrun		i:2086019411;
uc_address_format_840		s:102:\"!company\r\n!first_name !last_name\r\n!street1\r\n!street2\r\n!city, !zone_code !postal_code\r\n!country_name_if\";
uc_address_format_124		s:100:\"!company\r\n!first_name !last_name\r\n!street1\r\n!street2\r\n!city, !postal_code\r\n!zone_name, !country_name\";

uc_catalog 

uc_catalog_vid				s:2:"15";
uc_catalog_breadcrumb			i:0;
uc_catalog_breadcrumb_nodecount		i:0;
uc_catalog_show_subcategories		i:1;
uc_catalog_category_columns		s:1:"3";   ← Aantal kolommen categorieën. Wordt gecached
uc_catalog_block_title			i:1;
uc_catalog_expand_categories		i:0;
uc_catalog_block_nodecount		i:0;
uc_catalog_grid_display			i:1;
uc_catalog_grid_display_width		s:1:"3";
uc_catalog_grid_display_title		i:1;
uc_catalog_grid_display_model		i:1;
uc_catalog_grid_display_sell_price	i:1;
uc_catalog_grid_display_add_to_cart	i:1;
uc_catalog_grid_display_attributes	i:1;

Orders in Übercart 2

In verband met een migratie van Übercart 2 naar Übercart 3, zou ik het wel aardig vinden hoe ik orders in de database kan terugvinden. De belangrijkste:

  • uc_orders: Orderkopje. Alles wordt gekopiëerd en niet gelinkt (gelukkig!)
  • uc_order_line_items: Bijzondere orderregels: Belasting en vrachtkosten
  • uc_order_products: Gewone orderregels. Weer alles gekopiëerd en niet gelinkt.

Verder nog:

  • uc_order_admin_comments
  • uc_order_comments
  • uc_order_log
  • uc_order_quotes
  • uc_order_statuses.

Productafbeeldingen in Übercart 2

Ik ga producten die in een webwinkel zitten, importeren in Google Shopping. Daarvoor heb ik links nodig naar de afbeeldingen. Die wil ik uit de Drupal-database peuteren. Is waarschijnlijk niet moeilijk:

Tabel files

Tabel files bevat informatie over uploads. Velden:

  • fid - File ID: Primary sleutel
  • uid - User ID: Gebruiker
  • filename - Bv. 521.jpg
  • filepath - Bv. files/521.jpg
  • filemime
  • filesize
  • status
  • timestamp

Het veld fid moet op een of andere manier gekoppeld zijn met node-id

Tabel content_field_image_cache

  • vid - Primaire sleutel
  • delta - Primarie sleutel
  • nid - Node-ID?
  • field_image_cache_fid - Dit is wsch. fid van tabel files

Tabel node

  • nid - Primaire sleutel - Node-ID
  • vid - Content-type-ID
  • type
  • title
  • uid - User-ID
  • status
  • created
  • changed
  • Comment
  • promote
  • moderate
  • sticky
  • language
  • tnid
  • translate

Voorbeeld

  • Tabel node, regel 1: Koolborstel 1600 - nid = 576
  • Tabel content_field_image_cache: nid=576 --> fid = 1443
  • tabel files: fid=1443 --> filename = 26.jpg

Klopt!

Casus: Übercart-productnamen aanpassen

Resultaten van de eerste update-query: Product-titels zijn aangepast. In detailweergave zijn producttitels echter niet aangepast
Resultaten van de tweede update-query: Product-titels zijn nu ook in detailweergave aangepast

In Koolborstels-online.nl zijn de productnamen niet erg handig gekozen. Ik wil de productnamen op databas-niveau bijwerken. Afwegingen om dit op database-niveau wil doen:

  • Ik vind MySQL een prettig taal om in te werken. Het hele paradigma om dingen te programmeren ipv. met muisklikken te configureren, past erg goed bij me. Daarnaast is MySQL een typisch Jaren '70-product, en dat ben ik ook
  • Het is in dit geval veel efficiënter om direct op database-niveau dingen aan te passen
  • Dit is geen quick-and-dirty-hack: Dit is het begin van een nieuw tijdperk.

Tabel uc_products

Er zijn 7 Übercart-gerelateerde tabellen in een Drupal 6-installatie:

  • uc_product_adjustments → Leeg
  • uc_product_attributes → Leeg
  • uc_product_classes → Leeg
  • uc_product_features → Leeg
  • uc_product_options → Leeg
  • uc_product_stock → Leeg
  • uc_products → Geen producttitels, maar wel velden vid - Version ID, nid - Node ID en model - SKU's

Tabel node

Er zijn 5 node-gerelateerde tabellen:

  • node → Dit is 'm!
  • node_access
  • node_comment_statistics
  • node_counter
  • node_revisions

Tabel node bevat velden nid, vid, type en title. Wat ik nu moet doen:

  • Tabel node koppelen aan uc_product via nid, om zo SKU en title bij elkaar te hebben
  • Dit koppelen aan 'mijn' tabel uc_product_beterenamen
  • Veld node.title bijwerken

De betreffende query: 

-- Select query om te testen
-- =========================
--
select node.nid, node.title, uc_products.model, uc_product_beterenamen.title 
from node

inner join uc_products on node.nid = uc_products.nid

inner join uc_product_beterenamen on uc_products.model = uc_product_beterenamen.sku;


-- Nu het echte werk: node.title aanpassen ahv. uc_product_beterename.title ahv. twee joins
-- ========================================================================================
--
update node

inner join uc_products on node.nid = uc_products.nid

inner join uc_product_beterenamen on uc_products.model = uc_product_beterenamen.sku

set node.title = uc_product_beterenamen.title;

Tabel node_revisions

Ik moet het truukje nog een keer doen. Nu om tabel node_revisions bij te werken. Geen punt: 

-- Select-query om te testen
-- =========================
--
select node_revisions.nid, node_revisions.title, uc_products.model, uc_product_beterenamen.title 
from node_revisions

inner join uc_products on node_revisions.nid = uc_products.nid

inner join uc_product_beterenamen on uc_products.model = uc_product_beterenamen.sku;

-- Nu de update-query
-- ==================
-- 
update node_revisions
 
inner join uc_products on node_revisions.nid = uc_products.nid

inner join uc_product_beterenamen on uc_products.model = uc_product_beterenamen.sku

set node_revisions.title = uc_product_beterenamen.title;

Casus: Übercart-productnamen aanpassen (2)

De actie hierboven was succesvol, maar ik wil nóg mooiere productnamen. Daarvoor heb ik geen additionele data nodig. Ik kan dus rechtstreeks de volgende entiteiten aanpassen:

  • node.title
  • node_revisions.title


Casus: Taxonomie uitbreiden (okt. 2015)

Ik weet niet meer wat het script hieronder precies deed, maar ik weet wel dat ik danig van mezelf onder de indruk was, dat ik dit voor elkaar kreeg: 

-- =============================================================================================
-- Maak een backup
-- =============================================================================================
-- Dit onderdeel maar 1 keer nodig
--
-- create table term_data_bk01 select * from term_data;
-- create table term_hierarchy_bk01 select * from term_hierarchy;
-- create table term_node_bk01 select * from term_node;
-- create table url_alias_bk01 select * from url_alias;
-- create table menu_custom_bk01 select * from menu_custom;
-- create table menu_links_bk01 select * from menu_links;

-- =============================================================================================
-- Backup terugzetten
-- =============================================================================================
-- Er is altijd een backup beschikbaar. Daarop hoeft niet getest te worden
--
truncate term_data;
truncate term_hierarchy;
truncate term_node;
truncate url_alias;
truncate menu_custom;

insert into term_data select * from term_data_bk01;
insert into term_hierarchy select * from term_hierarchy_bk01;
insert into term_node select * from term_node_bk01; 
insert into url_alias select * from url_alias_bk01; 
insert into menu_custom select * from menu_custom_bk01; 

-- =============================================================================================
-- hoofdtaxon 'Marke' aanmaken
-- =============================================================================================
-- Na deze code is dit taxon direct te zien via Taxonomy Manger
--
insert into term_data values(null, 15, "Marke","Kohlebürsten nach Marken",0);
insert into term_hierarchy values (	(select tid from term_data where name like "Marke"),	0);

-- =============================================================================================
-- Lijst van alle merken > tbl_merk
-- =============================================================================================
--
drop table if exists tbl_merk;
drop view if exists term_data1; create view term_data1 as select * from term_data;
drop view if exists term_data2; create view term_data2 as select * from term_data;
drop view if exists term_hierarchy1; create view term_hierarchy1 as select * from term_hierarchy;
drop view if exists term_hierarchy2; create view term_hierarchy2 as select * from term_hierarchy;

create temporary table tbl_merk
select distinct term_data.name
from term_data
join term_hierarchy on term_data.tid=term_hierarchy.tid
join term_data1 on term_data1.tid=term_hierarchy.parent -- Soorten gereedschap
join term_hierarchy1 on term_data1.tid=term_hierarchy1.tid
join term_data2 on term_data2.tid=term_hierarchy1.parent -- Gereedschap
where term_data2.name="Kohlebürsten nach Werkzeug"
order by term_data.name asc;

-- =============================================================================================
-- tbl_merk - Uppercase/undercase fixen
-- =============================================================================================
--
update tbl_merk set name = concat(upper(substr(name,1,1)),lower(substr(name,2)));
update tbl_merk set name="AEG - Atlas Copco" where name="Aeg - atlas copco";
update tbl_merk set name="Black & Decker" where name="Black & decker";
update tbl_merk set name="Ciclo Matic" where name="Ciclo matic";
update tbl_merk set name="Ein Port" where name="Ein port";
update tbl_merk set name="Miller Falls" where name="Miller falls";
update tbl_merk set name="Super Confort" where name="Super confort";
update tbl_merk set name="Super Ego" where name="Super ego";

-- =============================================================================================
-- Taxa voor alle merken toevoegen aan term_data
-- =============================================================================================
-- Voor merken maak ik additionele taxa aan, terwijl ik voor machinereferenties gebruik maak
-- van de bestaande taxa. Volgende keer wellicht gelijktrekken?
--
insert into term_data select null, 15, name, 
concat("Alle Kohlebürsten für ",name," - Sortiert nach Maschinenreferenz"),0 from tbl_merk;


-- =============================================================================================
-- Merk-taxa toevoegen aan term_hierarchy
-- =============================================================================================
--
-- parent-tid achterhalen
-- ======================
-- select tid from term_data where name = "Marke" and description="Kohlebürsten nach Marken"; -- Retourneert 1 record
--
-- Merk-taxons identificeren
-- ===============================
-- select * from term_data where description like "Alle Kohlebürsten für %"; -- 61 Stuks
--
-- Uiteindelijke query
-- ===================
--
insert into term_hierarchy
select tid, (select tid from term_data where name = "Marke" and description="Kohlebürsten nach Marken") 
from term_data where description like "Alle Kohlebürsten für %";

-- =============================================================================================
-- Hoofdletters/kleine letters van alle taxons bijwerken
-- =============================================================================================
--
update term_data
join tbl_merk on upper(term_data.name)=upper(tbl_merk.name)
set term_data.name=tbl_merk.name;

-- =============================================================================================
-- Lijst van alle machine-referenties > tbl_ref
-- =============================================================================================
-- tbl_ref.parent_id - Dit is het id in de huidige/oude situatie: Gereedschap > Gereedschapssoort > merk > referentie
--
drop table if exists tbl_ref;
drop view if exists term_data1; create view term_data1 as select * from term_data;
drop view if exists term_data2; create view term_data2 as select * from term_data;
drop view if exists term_data3; create view term_data3 as select * from term_data;
drop view if exists term_hierarchy1; create view term_hierarchy1 as select * from term_hierarchy;
drop view if exists term_hierarchy2; create view term_hierarchy2 as select * from term_hierarchy;
drop view if exists term_hierarchy3; create view term_hierarchy3 as select * from term_hierarchy;

create temporary table tbl_ref
select term_data.tid, term_data.name, term_data1.name as "parent", term_data1.tid as "parent_id"
from term_data                                                  -- Machine-referenties
join term_hierarchy on term_data.tid = term_hierarchy.tid
join term_data1 on term_data1.tid = term_hierarchy.parent       -- Merken
join term_hierarchy1 on term_data1.tid=term_hierarchy1.tid
join term_data2 on term_data2.tid = term_hierarchy1.parent      -- Soorten gereedschap
join term_hierarchy2 on term_data2.tid = term_hierarchy2.tid
join term_data3 on term_data3.tid = term_hierarchy2.parent      -- "Gereedschap"
where term_data3.name="Kohlebürsten nach Werkzeug" -- Wortel = Gereedschap
order by term_data.name asc;

-- =============================================================================================
-- Machine-referenties: Description-veld bijwerken in term_data
-- =============================================================================================
--
update term_data
join tbl_ref on term_data.tid=tbl_ref.tid
set term_data.description=concat("Alle Kohlebürten für ", tbl_ref.parent," ",tbl_ref.name);

-- =============================================================================================
-- Machine-referenties (tbl_ref) > Toevoegen aan term_hierarchy
-- =============================================================================================
--
-- De truuk is, dat de machinereferentie-taxa er al zijn (term_data). 
-- Voor deze taxa moeten alleen nog additionele records in term_hierarchy worden toegevoegd.
--
-- Nieuwe parent-id's te voorschijn toveren
-- ========================================
--
-- select term_data.tid, term_data.name, term_data.description
-- from term_data
-- join term_hierarchy on term_hierarchy.tid = term_data.tid 
-- where description like "Alle koolborstels voor %"
-- and term_hierarchy.parent=98415;

-- tid's te voorschijn toveren
-- ===========================
--
-- select tid, name, parent as "parent_name" from tbl_ref; -- Geen probleem

-- Nu deze 2 queries samen
-- ==============================
--
-- select term_data.tid, term_data.name, term_data.description, tbl_ref.tid as "parent_id"
-- from term_data
-- join term_hierarchy on term_hierarchy.tid = term_data.tid 
-- join tbl_ref on tbl_ref.parent=term_data.name
-- where description like "Alle koolborstels voor %"
-- and term_hierarchy.parent=98415;

-- Nu deze 2 queries updaten
-- ==============================
--
insert into term_hierarchy
select tbl_ref.tid as "parent_id", term_data.tid
from term_data
join term_hierarchy on term_hierarchy.tid = term_data.tid 
join tbl_ref on tbl_ref.parent=term_data.name
where description like "Alle Kohlebürsten für %"
and term_hierarchy.parent=98408; -- Marke

-- =============================================================================================
-- tbl_merk - veld url's toevoegen
-- =============================================================================================
--
alter table tbl_merk add column url varchar(50);

-- =============================================================================================
-- tbl_merk - url's toevoegen
-- =============================================================================================
--
update tbl_merk
set url=replace(lower(name),' ','');
update tbl_merk set url="aeg-atlas-copco" where url="aeg-atlascopco";
update tbl_merk set url="blackendecker" where url="black&decker";
update tbl_merk set url="miller-falls" where url="millerfalls";

-- =============================================================================================
-- url_alias - Merken toevoegen
-- =============================================================================================
--
-- destinations achterhalen
-- ========================
--
-- select distinct null, concat("catalog/",term_data.tid) as "src", tbl_merk.url as "dst", "" as "language"
-- from term_data
-- join term_hierarchy on term_hierarchy.tid = term_data.tid 
-- join tbl_ref on tbl_ref.parent=term_data.name
-- join tbl_merk on term_data.name = tbl_merk.name
-- where description like "Alle koolborstels voor %"
-- and term_hierarchy.parent=98415;
-- 
-- Invoegen in url_alias_tmp
-- ========================
--
drop table if exists url_alias_tmp;
create table url_alias_tmp
select distinct concat("catalog/",term_data.tid) as "src", tbl_merk.url as "dst"
from term_data
join term_hierarchy on term_hierarchy.tid = term_data.tid 
join tbl_ref on tbl_ref.parent=term_data.name
join tbl_merk on term_data.name = tbl_merk.name
where description like "Alle Kohlebürsten für %"
and term_hierarchy.parent=98408;
-- 
-- Onnodige/niet werkende links verwijderen uit url_alias_tmp
-- ==========================================================
--
delete from url_alias_tmp where dst="aichi";
delete from url_alias_tmp where dst="bergel";
delete from url_alias_tmp where dst="bergin";
delete from url_alias_tmp where dst="braun";
delete from url_alias_tmp where dst="ciclomatic";
delete from url_alias_tmp where dst="confort";
delete from url_alias_tmp where dst="diamond";
delete from url_alias_tmp where dst="dyna";
delete from url_alias_tmp where dst="einport";
delete from url_alias_tmp where dst="fran";
delete from url_alias_tmp where dst="ghibli";
delete from url_alias_tmp where dst="imcoinsa";
delete from url_alias_tmp where dst="impex";
delete from url_alias_tmp where dst="macrosa";
delete from url_alias_tmp where dst="marelli";
delete from url_alias_tmp where dst="miller-falls";
delete from url_alias_tmp where dst="neumac";
delete from url_alias_tmp where dst="numax";
delete from url_alias_tmp where dst="phillips";
delete from url_alias_tmp where dst="prat";
delete from url_alias_tmp where dst="quersa";
delete from url_alias_tmp where dst="rockwell";
delete from url_alias_tmp where dst="simbi";
delete from url_alias_tmp where dst="stihl";
delete from url_alias_tmp where dst="superconfort";
delete from url_alias_tmp where dst="superego";
delete from url_alias_tmp where dst="telesch";

-- url_alias_tmp > invoegen > url_alias
-- ====================================
--
insert into url_alias select null, src, dst, "" from url_alias_tmp;

-- =============================================================================================
-- uc_catalog_category_columns → 3 kolommen
-- =============================================================================================
--
update variable
set value='s:1:"3";'
where name='uc_catalog_category_columns';

Casus: 160.000 spam-accounts verwijderen

Zie Spam-accounts verwijderen (Drupal)#Casus okt. 2015: 160.000 spam-accounts

Casus: Alle prijzen met 10% verhogen

In okt. 2015 wilde ik in een webwinkel alle prijzen met 10% verhogen. Da's nie moeilijk: 

update uc_products set sell_price=1.1*sell_price;

Casus: Alle SKU's & prijzen uitlezen

Koud kunstje, want die zitten in dezelfde tabel: 

select model, sell_price from uc_products;

Casus: Omzet Übercart uitlezen

Eens per maand wil ik van een aantal webwinkels de omzet van de afgelopen maand weten. Kan dat nou niet automatisch?

Ja maar natuurlijk kan dat! Hier is een bloemlezing aan Drupal 6-databases: 

use webwinkels;
select "shop1",       sum(order_total),count(*) from shop1.uc_orders where order_status="Completed" and created >= unix_timestamp("2015-09-01") and created < unix_timestamp("2015-10-01")
union select "shop2,  sum(order_total),count(*) from shop2.uc_orders where order_status="Completed" and created >= unix_timestamp("2015-09-01") and created < unix_timestamp("2015-10-01")
union select "shop3", sum(order_total),count(*) from shop3.uc_orders where order_status="Completed" and created >= unix_timestamp("2015-09-01") and created < unix_timestamp("2015-10-01")
union select "shop4", sum(order_total),count(*) from shop4.uc_orders where order_status="Completed" and created >= unix_timestamp("2015-09-01") and created < unix_timestamp("2015-10-01")

Zie je die opmerkelijke herhaling van code? Dan roept om gebruik van een sproc oid. Helaas: De productie-omgeving geeft een geheugenissue aan. Vandaar.

Casus: Alle producten krijgen dezelfde afbeelding (okt. 2015)

Dit was de oplossing: 

insert into content_field_image_cache
select
   vid, 0, nid, 169, 1, 'a:2:{s:3:"alt";s:0:"";s:5:"title";s:0:"";}'i
from node
where title like "%dezeproducten%";

Casus: Menu overnemen uit een andere website (okt. 2015)

  • Menu's worden bijgehouden in tabel menu_custom
  • Menu-items worden bijgehouden in tabel menu_links. Zo'n record bevat verbazend veel velden
  • Menu_custom en menu_links worden gelinkt door veld menu_name
  • In menu_links moet het veld external de waarde 1 krijgen.

Script: 

-- =============================================================================================
-- Maak menu aan
-- =============================================================================================
--
insert into menu_custom values 
	("kohlebuersten-nach-marken",
	"Kohlebürsten nach Marken",
	"Finden Sie Kohlebürsten für alle großen Marken der Elektrogeräte und elektrische Handwerkzeuge"
);

-- =============================================================================================
-- Menu-items uit Koolborstels-online.nl overnemen
-- =============================================================================================
--
insert into menu_links
select * from koolborstels.menu_links where menu_name="menu-koolborstels-op-merk";

update menu_links
set menu_name="kohlebuersten-nach-marken" where menu_name="menu-koolborstels-op-merk";

-- =============================================================================================
-- mlid-Teller aanpassen
-- =============================================================================================
-- Alle menu_links-items lijken een unieke waarde voor mlid te hebben.
-- Dat pas ik hier ook toe, om complicaties te voorkomen
--
update menu_links
set mlid=mlid+962 where menu_name="kohlebuersten-nach-marken";

-- =============================================================================================
-- 'External' aanpassen
-- =============================================================================================
-- Dit is de truuk: Het veld 'External' moet waarde=1 hebben
--
update menu_links set external=1 where menu_name="kohlebuersten-nach-marken";

Casus: Alle stories verwijderen (okt. 2015)

Gevalletje spam: Content van het type story. Wegtoveren: 

mysql> delete from node where type="story";
Query OK, 241439 rows affected (20.27 sec)

Casus: Titels aanpassen van bepaalde Übercart-producten (jan. 2016)

-- Test - selecteer de betreffende nodes
-- ------------------------------
-- SELECT title FROM node where title regexp '^Koolborstelset (3|4|5|6)[0-9]{3}$';
-- SELECT title FROM node_revisions where title regexp '^Koolborstelset (3|4|5|6)[0-9]{3}$';

-- Pas de titels aan
-- -----------------
-- update node set title=replace(title, 'Koolborstelset','Koolborstel') where title regexp '^Koolborstelset (3|4|5|6)[0-9]{3}$';
-- update node_revisions set title=replace(title, 'Koolborstelset','Koolborstel') where title regexp '^Koolborstelset (3|4|5|6)[0-9]{3}$';

-- Pas de titels nog een keer aan
-- ------------------------------
--
-- update node set title=concat(title," voor vorkheftrucks") where title regexp '^Koolborstel (3|4|5|6)[0-9]{3}$';
-- update node_revisions set title=concat(title," voor vorkheftrucks") where title regexp '^Koolborstel (3|4|5|6)[0-9]{3}$';
--
-- SELECT * FROM node where title regexp '^Koolborstel (3|4|5|6)[0-9]{3}';
-- SELECT title FROM node_revisions where title regexp '^Koolborstel (3|4|5|6)[0-9]{3}';

Casus: Productgegevens uitlezen (dec. 2020)

#
# Productgegevens kbo1 (Drupal 6 - Übercart) uitlezen
################################################################################
#
#
################################################################################
# Select database
################################################################################
#
use kbo1;


################################################################################
# Read stuff
################################################################################
#
select
    node.nid,
    node.title,
    # uc_product_beterenamen.title as title_02,
    node_revisions.body,
    node_revisions.teaser,
    uc_products.model as sku,
    uc_products.sell_price,
    
    # Images
    ########################################
    #
    content_field_image_cache.field_image_cache_fid as fid,
    content_field_image_cache.field_image_cache_data as image_attributes,
    files.filename as image_filename

from
    node
    
join
    node_revisions
    on
    node.nid = node_revisions.nid

join
    uc_products
    on
    node.nid = uc_products.nid
    
# uc_product_beterenamen
########################################
# 
# These are the same names as in table "node" → Skip
#
# join
#     uc_product_beterenamen
#     on
#     uc_products.model = uc_product_beterenamen.sku

join
    content_field_image_cache
    on
    node.nid = content_field_image_cache.nid

join
    files
    on
    content_field_image_cache.field_image_cache_fid = files.fid
    
where
    type="product";

Casus: Alle orderregels (dec. 2020)

#
# Retrieve revenue per product - Drupal 6 & Übercart
################################################################################
#
#
################################################################################
# Select database
################################################################################
#
use kbo1;


################################################################################
# Read stuff
################################################################################
#
# * Table "uc_orders": 14.957 completed orders. PK: Field "order_id"
#
select
    # uc_orders
    ########################################
    #
    uc_orders.order_id,
    uc_orders.order_total,
    created	             as created_unixtime,
    from_unixtime(created)   as created,
    
    # uc_order_line_items
    ########################################
    #
    # * Only VAT & delivery costs
    # * Here you can see, that somewhere Dutch VAT tariff changed from 19% to 21%
    # * If the lines below are included, you get a Catesian product of rows
    #
    #     uc_order_line_items.type	as order_line_type,
    #     uc_order_line_items.title	as order_line_title,
    #     uc_order_line_items.amount	as order_line_amount,

    # uc_order_products
    ########################################
    #
    uc_order_products.title		as product_title,
    uc_order_products.model		as sku,
    uc_order_products.price		as price
    
from
    uc_orders

# join
#     uc_order_line_items
#     on
#     uc_orders.order_id = uc_order_line_items.order_id

join
    uc_order_products
    on
    uc_orders.order_id = uc_order_products.order_id

where
    order_status = "completed";

Casus: Omzet per product (dec. 2020)

Zie ook

Bronnen

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Databasemodel_Drupal_6&oldid=25057"