↑↓ selectEnter openEsc close

    1. Elbuz
    2. Tunnel SQL queries

    Elbuz Tunnel temporary tables: what's in them

    2 min readUpdated

    Elbuz Tunnel puts your catalog data into temporary tables in your website database, one table per entity. Use this reference to write queries against them without guesswork.

    When you need this:

    • You're writing a merge for your CMS — the table tells you where to get products, categories, attributes and photos.
    • Your query must filter out product variants — the type_id column tells an option product from a regular one.
    • You need a custom catalog field — it's stored in a column named like product_f451.

    Where to start: the list of tables below.

    What this reference covers

    • Tables — products, categories, manufacturers, attributes, photos, stores, warehouses, lookup lists.
    • System columns — uuid, row_exist, type_id, website and Elbuz IDs.
    • Custom catalog fields — what they're called in the temporary tables.

    Temporary table reference

    The tunnel creates more than thirty tables in your website database — exactly the ones needed for the data you selected. Here are the ones used most often.

    TableWhat's in it
    etrade_category_tempCategories: name, descriptions, SEO fields, uuid, uuid_parent, nesting level, sort order
    etrade_product_tempProducts: prices (price, price_rrp, price_old, price_cost), quantity, SKUs (sku, mpn, model), barcodes, dimensions, descriptions, , stock status, type_id
    etrade_product_to_category_tempProduct-to-category links, with a main-category flag
    etrade_manufacturer_tempManufacturers: name, logo, descriptions, SEO fields
    etrade_attribute_block_temp
    etrade_attribute_temp
    etrade_attribute_value_temp
    etrade_product_attribute_temp
    Attribute blocks, the attributes themselves, the list of values, and the values assigned to products
    etrade_image_temp
    etrade_image_seo_temp
    Category and product photos and their alt/title texts
    etrade_store_temp
    etrade_warehouse_temp
    etrade_product_store_temp
    etrade_product_warehouse_temp
    Stores and warehouses, plus product quantity and price per store or warehouse
    etrade_currency_temp
    etrade_language_temp
    etrade_measure_temp
    Lists of currencies with exchange rates, languages and units of measurement
    etrade_article_temp
    etrade_article_to_product_temp
    Articles and article sections, if you selected them: name, texts, SEO fields, URL, uuid, uuid_parent, type, publication date and modification date (date_modified), cover image as a link (image_url), author (uuid_author), the is_noindex and flag_show_toc flags. The second table holds the articles' products
    etrade_article_author_tempArticle authors — Elbuz CMS template only: uuid, photo as a link (image_url), status, language (language_uuid), name (name) and bio (description)
    etrade_review_temp
    etrade_faq_temp
    Reviews and Q&A, if you selected them

    System columns and custom fields

    Columns that exist almost everywhere and that every query needs:

    ColumnPurpose
    uuidString ID of the record in Elbuz — the main matching key
    row_existYour flag: 1 — the record was found on the website, 0 — it needs to be created
    *_uuidReference to another record (category_uuid, manufacturer_uuid, product_uuid, item_uuid, language_uuid)
    *_idNumeric ID on the website; empty at first, you fill it in at the identification step
    *_id_jumperNumeric ID in Elbuz itself. It exists in four tables: product_id_jumper in products, category_id_jumper and parent_id_jumper in categories, language_id_jumper in languages, review_id_jumper in reviews. Handy when the website needs to keep a link back to Elbuz
    type_idProducts only: 1 — regular product, 2 — option product (variant)
    language_idLanguage of the row; a multilingual website can't do without it

    Four tables — products, categories, manufacturers and contacts — also get your own custom base catalog fields, the ones you created. Their names combine the record type and field number: product_f451, category_f120, manufacturer_f88. Look up the name in the base catalog grid settings. Other tables, including attributes and photos, have no custom fields.

    FAQ

    Where do I get the list of fields in a temporary table?

    The easiest way is to look at the table itself in your website database right after a run, using SHOW COLUMNS FROM etrade_product_temp. The set of fields depends on what you selected in the export.

    What are the product_id_jumper and category_id_jumper fields for?

    They hold the numeric IDs of records in Elbuz itself — for example, to write the Elbuz ID into the “Model” field and then match orders. You don't need them for a regular catalog update: matching relies on uuid.

    Are the temporary tables deleted after a run?

    For CMSs from the list, yes — as the last step of the run. In “MySQL (SQL custom query)” mode they're intentionally left in the website database so you can see what arrived from Elbuz; the next run recreates them.

    How is type_id = 2 different from type_id = 1?

    1 is a regular product, 2 is an option product (variant). Without the type_id = 1 condition in your update and insert queries, each variant goes to the website as a separate product page.

    Why doesn't the photo table have my custom fields?

    Custom base catalog fields exist only in four tables: products, categories, manufacturers and contacts. Other tables, including attributes and photos, don't have them.

    Where to find it and who has access

    There's no menu item in Elbuz: the tables live in your website database, next to the tables.

    Other entry points
    phpMyAdmin or another MySQL client on your website hosting
    Access right
    access to the website database
    Prepare
    at least one run of the tunnel template

    Related topics

    Was this article helpful?

    Missing a feature? Tell us.

    We will add it for free if other users need it too.

    If only you need it, we will agree on it separately.

    Article contents

    Suggest an improvement

    Try Elbuz free