Databasemodel Drupal 6
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
|
|
|
|
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
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
enmodel - 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
- https://drupal.org/node/22754 - Reference
- https://drupal.org/node/79874 - Documenting database schema via processing of .install files