Databasemodel Drupal 7

Uit De Vliegende Brigade
(wijz) ← Oudere versie | Huidige versie (wijz) | Nieuwere versie → (wijz)
Naar navigatie springen Naar zoeken springen
Drupal 7 Datamodel

Alle Drupal Commerce-tabellen

  • commerce_calculated_price
  • commerce_checkout_pane
  • commerce_customer_profile
  • commerce_customer_profile_revision
  • commerce_flat_rate_service
  • commerce_line_item
  • commerce_order
  • commerce_order_revision
  • commerce_payment_transaction
  • commerce_payment_transaction_revision
  • commerce_product
  • commerce_product_revision
  • commerce_product_type
  • commerce_tax_rate
  • commerce_tax_type
  • field_data_commerce_... - Maatwerk-velden
  • field_data_field_... - Maatwerk-velden
  • field_revision_commerce_... - Maatwerk-velden
  • field_revision_field_... - Maatwerk-velden.

Tabel - Node

Velden: 

  Naam        Omschrijving             Type           Default   Opmerkingen
  ----        ------------             -----------    -------   -----------
* nid         Node-ID                  Int(10)                  Primaire sleutel
* vid         Version-ID               Int(10)        NULL      Foreign key van tabel node_revisions
* type        Content-type             Varchar(32)    ''        Wat je vindt onder Structure > Content-types
* language                             Varchar(12)    ''
* title                                Varchar(255)   ''
* uid         User-ID                  Int(11)        0
* status      Published?               Int(11)        1
* created     Unix timestamp           Int(11)        0
* changed     Unix timestamp           Int(11)        0         'created' en 'changed' hebben vaak dezelfde waarde
* comment     Commentaar toegestaan?   Int(11)        0
* promote                              Int(11)        0
* sticky                               Int(11)        0
* tnid        Translation Node ID      Int(10)        0
* translate                            Int(11)        0

Tabel - node_revision

  Naam        Omschrijving             Datatype       Default
  ----        ------------             -----------    -------
* nid         Node-ID (FK)             INT(10)        '0'
* vid         Revision-ID (PK)         INT(10
* uid         User-ID                  INT(11)        '0'
* title       Node-title               VARCHAR(255)   ''
* log         ?                        LONGTEXT
* timestamp   Created                  INT(11)        '0'
* status      Published?               INT(11)        '1'
* comment     Commentaar toegestaan?   INT(11)        '0'
* promote     Promoted                 INT(11)        '0'
* sticky      Sticky on top?           INT(11)        '0'
* ds_switch   ?                        VARCHAR(255)   ''

Taxonomieën

Tabellen 

* taxonomy_index            (nid, tid, sticky, created)     Associatie tussen node & taxon
* taxonomy_term_data        (tid, vid, name, description)   Taxon
* taxonomy_term_hierarchy   (tid, parent)                   Hierarchie: ouder van een taxon
* taxonomy_vocabulary       (vid, name, machine_name, 
                             description, hierarchy,        Definitie van een vocabulaire
                             module, weight)

Veldnamen

Veldnamen zijn doorgaans consistent door de gehele database. Oftewel, veld 'nid' in de ene tabel, is dezelfde 'nid' in een andere tabel: 

* nid - Node ID
* tid - Term ID = Taxon ID
* vid - Vocabulary ID

Eigen velden - Voorbeeld

In Drupal 7 kun je aan een taxonomie eigen velden toevoegen. Elk veld vorm een eigen databasetabel. Dit is een voorbeeld waarbij een afbeelding is toegevoegd aan een taxonomie: 

Veldnaam: field_revision_field_taxonomie_afbeelding

Velden:

* entity_type
* bundle
* deleted
* entity_id
* revision_id
* language
* delta
* field_taxonomie_afbeelding_fid
* field_taxonomie_afbeelding_alt
* field_taxonomie_afbeelding_title
* field_taxonomie_afbeelding_width
* field_taxonomie_afbeelding_height

Omzet Drupal Commerce uitlezen

Net als bij Übercart voor Drupal 6, zou ik graag via SQL de omzet willen uitlezen uit Drupal Commerce in Drupal 7.

Tabellen

commerce_order

  • order_id - PK
  • order_number - Gebruikers-order-id
  • revision_id - FK voor tabel commerce_order_revision
  • created - Unix timestamp
  • status - Bv. completed.
  • data - Serialised dataveld (longblob) waarvan de inhoud in MySQL Workbench alleen te lezen is als je Open value in editor kiest , maar in de mysql-client prima prima is uit te lezen → Geen ordertotalen.

Voorbeeld data-veld: 

a:4:
{
	s:17:"last_cart_refresh";i:1420733641;
	s:14:"payment_method";
	s:38:"paypal_wps|commerce_payment_paypal_wps";
	s:20:"payment_redirect_key";
	s:43:"TGUOciUPKaZg3AU1237eLdwHNJdDqBLG0w5lCx9VEk";
	s:43:"commerce_payment_order_paid_in_full_invoked";
	b:1;
}

commerce_order_revision

Drupal slaat vaak veel info op in revision-velden. Elk record bevat daarbij de complete status van de onderhavige entiteit. Daarom is in dit geval alleen het laatste revision-veld relevant. Velden:

  • order_id - FK tabel commerce_order
  • order_number
  • revision_id
  • data (serialised).

Voorbeeld data-veld van de het laatste revisie-veld van een order met status completed: 

a:4:
{
   s:17:"last_cart_refresh";i:1420733641;
   s:14:"payment_method";
   s:38:"paypal_wps|commerce_payment_paypal_wps";
   s:20:"payment_redirect_key";
   s:43:"TGUOciUPKaZg3AU692c7eLdwHN123qBLG0w5lCx9VEk";
   s:43:"commerce_payment_order_paid_in_full_invoked";
   b:1;
}

field_data_commerce_order_total

  • commerce_order_total_amount - Inclusief BTW, uitgedrukt in centen
  • entity_id (FK) = order_id. Omdit dit een dynamisch gegenereerd veld is, is de benaming generiek. De waardes komen echter prima overeen met de order-PK.

Overige tabellen

  • commerce_order_revision - Ziet er niet relevant uit
  • commerce_line_item - Ziet er niet relevant uit.

API-calls

De koninklijke weg om deze data uit een webshop te peuteren, is via de vastgestelde API-calls te doen. Mocht ik dat ooit inderdaad doen: Heel moeilijk lijkt het niet te zijn. Nog een keer.

Commentaren verwijderen

Gevalletje spam-posting: Hoe verwijder ik 500 commentaren?

Structuur 

  Tabel                         Sleutel
  ---------------               ------------------------------
* comment                       PK: cid - Dit is de hoofdtabel
* users                         FK: uid
* node                          FK: nid
* role_permission               FK: role_permission_rid - ?
* field_data_comment_body       ?
* field_revision_comment_body   ?

Alle commentaar verwijderen

Maak voor de zekerheid eerst een backup - Ik ben slechts voor 95% zeker dat dit werkt. 

delete from field_revision_comment_body;
delete from field_data_comment_body;
delete from comment;

Drupal Commerce: Voorraadpositie op xyz-code wijzigen

Casus

De klant heeft een spreadsheet gemailed met producten + voorraadpositie. Echter: Niet de gebruikelijke SKU's worden gehanteerd, maar 'interne' xyz-codes (de codes van de logistieke partner om precies te zijn).

Datamodel

  • Tabel commerce_product: Dit betreft L1. Daar vind je product_id, sku en title. Verder weinig spannends

Drupal Commerce: Orders exporteren (feb. 2017)

Verrassend hoe ingewikkeld het bleek om 'gewoon' orders te exporteren. Volgende keer de API gebruiken (veel efficiënter + robuuster): 

-- =====================================================
-- Select
-- =====================================================
--
select
 
    -- commerce_order
    -- ==============================================================
    -- 
    commerce_order.order_id,
    commerce_order.mail                     as "order_mail",
    commerce_order.status                   as "order_status",
    from_unixtime(commerce_order.created)   as "order_created",
    from_unixtime(commerce_order.changed)   as "order_changed",
   
    -- commerce_order.order_number,
    -- commerce_order.type,
    -- commerce_order.uid,
    -- commerce_order.data,
    -- commerce_order.hostname,
 
    -- order_total
    -- =====================================================
    --
    field_data_commerce_order_total.commerce_order_total_amount        as "order_total_amount",
    field_data_commerce_order_total.commerce_order_total_currency_code as "order_currency_code",
 
    -- Users
    -- =====================================================
    --
    users.uid  as "user_id",
    users.name as "user_name",
 
    -- Shipping - salution
    -- =====================================================
    --
    field_data_field_salutation.field_salutation_value 
    as "shipping_salutation",
     
    -- shipping - first_name
    -- =====================================================
    --
    field_data_field_firstname.field_firstname_value 
    as "shipping_first_name",
 
    -- shipping - middle_name
    -- =====================================================
    --
    field_data_field_middlename.field_middlename_value 
    as "shipping_middle_name",
     
    -- shipping - last_name
    -- =====================================================
    --
    field_data_field_lastname.field_lastname_value 
    as "shipping_last_name",
 
    -- shipping_address_stree
    -- =====================================================
    --
    field_data_field_street.field_street_value 
    as "shipping_address_street",
 
    -- Housenumber
    -- ==============
    --
    field_data_field_housenumber.field_housenumber_value 
    as "shipping_address_housenumber",
 
    -- housenumberaddition
    -- ===================
    --
    field_data_field_housenumberaddition.field_housenumberaddition_value 
    as "shipping_address_housenumberaddition",
 
    -- city
    -- ====
    --
    field_data_field_cust_city.field_cust_city_value 
    as "shipping_address_city",
     
    -- zipcode
    -- =======
    --
    field_data_field_zipcode.field_zipcode_value 
    as "shipping_zipcode",
     
    -- Shipping country
    -- ===========================
    --
    field_data_field_country.field_country_iso2 
    as "shipping_address_country",
 
    -- Billing country
    -- =====================
    --
    -- field_data_field_country.field_country_iso2 
    -- as "shipping_address_country",
     
 
    -- order_line_item
    -- =====================================================
    -- 
    commerce_line_item.line_item_id     as "orderline_id",
    commerce_line_item.type             as "orderline_type",
    commerce_line_item.line_item_label  as "orderline_sku",
    commerce_line_item.quantity         as "orderline_quantity",
    
    -- commerce_line_item.created
    -- commerce_line_item.changed,
    -- commerce_line_item.data
 
    -- commerce_product - title
    -- ==========================================================
    --
    commerce_product.title as "orderline_title",
   
    -- orderline - unit price
    -- =========================================================
    --
    field_data_commerce_unit_price.commerce_unit_price_amount
    as "orderline_unit_price",

    field_data_commerce_unit_price.commerce_unit_price_currency_code
    as "orderline_unit_price_currency",
     
    -- orderline - total price
    -- ==========================================================
    --
    field_data_commerce_total.`commerce_total_amount`
    as "orderline_total_amount",

    `field_data_commerce_total`.`commerce_total_currency_code`
    as "orderline_currency"
     
-- =====================================================
-- Join
-- =====================================================
--
-- commerce_order - De basis
-- =========================
-- 
from commerce_order

-- commerce_order_revision - Niet nodig
-- ====================================
-- 
-- left join commerce_order_revision
-- on        commerce_order.revision_id = 
--           commerce_order_revision.revision_id

-- commerce_line_item
-- ==================
-- 
left join commerce_line_item
on        commerce_order.order_id = 
          commerce_line_item.order_id

-- users
-- =====
--
left join users
on   commerce_order.uid = 
     users.uid

-- field_revision_commerce_customer_address
-- ========================================
-- 
left join field_revision_commerce_customer_address
on        commerce_order.order_id = 
          field_revision_commerce_customer_address.entity_id
 
-- Shipping address
-- ======================================================
-- 
left join field_data_commerce_customer_shipping       
on        field_data_commerce_customer_shipping.entity_id = 
          commerce_order.order_id
 
-- Billing address
-- ======================================================
-- * Billing addresses worden in dezelfde tabel bijgehouden als shipping addresses.
--   Waarschijnlijk een alias defineren op die tabel om billing addresses te kunnen
--   extraheren
-- * In dit geval leek er geen gebruik te worden gemaakt van billing addresses
--
-- left join field_data_commerce_customer_billing
-- on        field_data_commerce_customer_shipping.entity_id = 
--           commerce_order.order_id

-- order_total
-- ======================================================
-- 
left join field_data_commerce_order_total
on        field_data_commerce_order_total.entity_id = 
          commerce_order.order_id
 
-- Shipping salutation
-- ===================
--
left join field_data_field_salutation
on        field_data_field_salutation.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping firstname
-- ==================
--
left join field_data_field_firstname 
on        field_data_field_firstname.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping middlename
-- ===================
--
left join field_data_field_middlename
on        field_data_field_middlename.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping lastname
-- =================
--
left join field_data_field_lastname
on        field_data_field_lastname.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping Street
-- ===============
--
left join field_data_field_street 
on        field_data_field_street.entity_id  =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping Housenumber
-- ====================
--
left join field_data_field_housenumber 
on        field_data_field_housenumber.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping housenumberaddition
-- ============================
-- 
left join field_data_field_housenumberaddition 
on        field_data_field_housenumberaddition.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping cust_city
-- ==================
--
left join field_data_field_cust_city
on        field_data_field_cust_city.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Shipping zipcode
-- ================
--
left join field_data_field_zipcode
on        field_data_field_zipcode.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id

-- Shipping country
-- ================
--
left join field_data_field_country
on        field_data_field_country.entity_id =
          field_data_commerce_customer_shipping.commerce_customer_shipping_profile_id
 
-- Billing country
-- ===============
--
-- left join    field_data_field_country
-- on           field_data_field_country.entity_id = 
--              field_data_commerce_customer_billing.commerce_customer_billing_profile_id
 
-- commerce_product
-- ========================
--
left join commerce_product
on        commerce_product.sku = 
          commerce_line_item.line_item_label
 
-- Order_line - Unit price
-- =============================
--
left join field_data_commerce_unit_price
on        field_data_commerce_unit_price.entity_id = 
          commerce_line_item.line_item_id
 
-- Order_line - Total
-- ===================================
--
left join field_data_commerce_total
on        field_data_commerce_total.entity_id = 
          commerce_line_item.line_item_id
 
-- =====================================================
-- Sort order
-- =====================================================
--
order by commerce_order.order_id asc;

Drupal Commerce: Populairste producten (aug. 2019)

Dat gaat dan toevallig weer heel gemakkelijk: 

select
	line_item_label as sku,
	sum(quantity)   as quant 

from commerce_line_item
group by line_item_label
order by quant desc;

Drupal Commerce: Prijzen uitlezen (dec. 2019)

Wéér een stap naar een Drupal-loze wereld: 

select
   sku,
   title,
   commerce_price_amount				as sell_price_eurocent_incl_vat,
   commerce_price_amount/100				as sell_price_euro_incl_vat,
   (commerce_price_amount/100)/1.21			as sell_price_euro_ex_vat,
   # cast(commerce_price_amount as decimal(4,2))	as price_04	# Werkt niet!
   round((commerce_price_amount/100)/1.21,2) 		as sell_price_euro_ex_vat_rounded_2_decimals
   # commerce_price_data
from
   commerce_product
join
   field_data_commerce_price
on
   commerce_product.product_id = field_data_commerce_price.entity_id
order by
   sku;

Drupal Commerce: Omzet per artikel uitlezen (2020)

Ik heb weinig zin om dit via de API te doen: Elke seconde die ik aan Drupal besteed, is zonde van de tijd. Dus via SQL. Zal wel niet gemakkelijk zijn.

Tjakka: In een half uurtje gepiept. Later (juli 2020) uitgebreid, en dat kostte ca. een uurtje. Die shit zakt weg. Vandaar: 

#
# Retrieve revenue per product data from a Drupal 7-database
####################################################################################################################################################
#
# Performed on a copy of such a database on my laptop
#
use kbo2;


####################################################################################################################################################
# Drop existing table "dvb_revenue"
####################################################################################################################################################
#
drop table if exists dvb_revenue;


####################################################################################################################################################
# Create & fill table "dvb_revenue"
####################################################################################################################################################
#
create table 
	dvb_revenue
select
    commerce_order.order_id				as order_id,
    commerce_order.status				as order_status,
    commerce_line_item.line_item_id			as line_item_id,
    # commerce_line_item.type				as line_item_type,
    commerce_line_item.line_item_label			as sku,
    commerce_product.title				as product_title,
    field_data_field_pt_body.field_pt_body_summary	as description_summary,
    field_data_field_pt_body.field_pt_body_value	as description,
    commerce_line_item.quantity				as quantity,
    field_data_commerce_total.commerce_total_amount	as amount_cents_incl_vat
from 
    commerce_order
join
    commerce_line_item 
    on 
    commerce_order.order_id = commerce_line_item.order_id
join
    field_data_commerce_total
    on
    commerce_line_item.line_item_id = field_data_commerce_total.entity_id


# Join with commerce_product to get product name
########################################################################
#
join
    commerce_product
    on
    commerce_product.sku = commerce_line_item.line_item_label
 
 
# Join with field_data_field_pt_body for product descriptions
########################################################################
#
join
    field_data_field_pt_body
    on
    field_data_field_pt_body.entity_id = commerce_product.product_id

 
# Where clause
########################################################################
#    
where
    commerce_order.status="Bedankt"
or
    commerce_order.status="completed"
or
    commerce_order.status="invoiced";

Drupal Commerce: Omzet uit België (lente 2020)

De code uit een paar hoofdstukken hierboven, werkte niet: Sommige velden bestonden niet. Deze code werkt wel. Het gaat in twee stappen:

Eerste stap: 

#
# Calculate revenue from Belgium in 2019
##############################################################################################################
#
# kbo2 is een locale kopie van de betreffende Drupal-productiesite
#
use kbo2;


##############################################################################################################
# Form table "orders"
##############################################################################################################
#
drop table if exists orders;

create table orders
select
    #
    # commerce_order
    ###################################################################
    #
    commerce_order.order_id				as order_id,
    commerce_order.mail                     		as "order_mail",
    commerce_order.uid					as uid,
    commerce_order.status                   		as "order_status",
    left(from_unixtime(commerce_order.created),4)       as "order_year",
    
    
    # order_total_amount
    ###################################################################
    #
    field_data_commerce_order_total.commerce_order_total_amount	as "order_total_amount",


    # Country
    ###################################################################
    #
    field_data_commerce_customer_address.commerce_customer_address_country as "country"
    # field_data_commerce_customer_address.*
    
from
    commerce_order


# Join: field_data_commerce_order_total
###################################################################
#   
left join
   field_data_commerce_order_total
   on
   field_data_commerce_order_total.entity_id = commerce_order.order_id    


# Join: customer_address
########################################################
# 
left join 
   field_data_commerce_customer_address
   on
   field_data_commerce_customer_address.entity_id = commerce_order.uid

# Where clause
###################################################################
#
where
   (
      commerce_order.status like "Bedankt"
      or
      commerce_order.status like "Completed"
      or
      commerce_order.status like "Invoiced"
   )
   and
      left(from_unixtime(commerce_order.created),4)="2019"
   and
      field_data_commerce_customer_address.commerce_customer_address_country = "BE";

select * from orders;

Tweede stap: 

use kbo2;

select distinct
   order_status,
   sum(order_total_amount)/100	as revenue_incl_btw_incl_transport,
   sum(order_total_amount)/100/1.21 as revenue_incl_transport_ex_btw    
from
   orders
group by
   order_status

Drupal Commerce: Assortiment uitlezen (dec. 2020)

Uit een niet-actieve Drupal Commerce-database wil ik het assortiment uitlezen, ivm. business intelligence. Het ging me alleen om een paar basis-gegevens: 

################################################################################
# Find product data
################################################################################
#
# * Drupal 7 makes a distinction between product_display and something-else
# * product_display can be fount in table "node"
# * Actual product data: "commerce_product"
# * product_display doesn't add anything - commerce_product suffices
#
#
# select * from node where type = "product_display";
#
select
   sku,
   title	as product_title,
   created	as created
from
   commerce_product;

Zie ook

Bronnen

Orderbedrag Drupal Commerce uitlezen

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