Bilbo is a building blocks language... check it out!

restore_csv_to_database.bilbo

file client_purchases {
  filename "client_purchases.csv",
  delimiter ","
}

struct csv_format {
  client_name string required,
  client_age integer,
  product_name string required,
  product_price decimal required
}

database ecommerce {
  type mysql,
  dbname ecommerce,
  username root,
  password root
}

struct clients {
  id integer auto_generated required,
  name string required,
  age integer
}

struct products {
  id integer auto_generated required,
  name string required
  price decimal required
}

struct purchases {
  id integer auto_generated required,
  client_id integer required foreign(clients),
  product_id integer required foreign(products)
}

transform upsert_client {
  client_name as name,
  client_age as age,
  unique(name, age)
  returning id
}

transform upsert_product {
  product_name as name,
  product_price as price,
  unique(name, price)
  returning id
}

transform insert_purchase {
  product_id,
  client_id
}

map upsert_purchase {
  run upsert_product as product_id,
  run upsert_client as client_id,
  run insert_purchase
}

plug clients to ecommerce
plug products to ecommerce
plug purchases to ecommerce

plug only_and_unique_clients to clients
plug only_and_unique_products to products

plug csv_format to upsert_purchase

plug client_purchases to csv_format