https://wvlet.org/wvlet/assets/js/e127ace5.2dfceff7.js

1"use strict";(globalThis.webpackChunkwebsite=globalThis.webpackChunkwebsite||[]).push([[3790],{5787(e,n,t){t.r(n),t.d(n,{assets:()=>o,contentTitle:()=>d,default:()=>h,frontMatter:()=>r,metadata:()=>a,toc:()=>c});const a=JSON.parse('{"id":"syntax/data-models","title":"Data Models","description":"This page is still work in progress.","source":"@site/docs/syntax/data-models.md","sourceDirName":"syntax","slug":"/syntax/data-models","permalink":"/wvlet/docs/syntax/data-models","draft":false,"unlisted":false,"editUrl":"https://github.com/wvlet/wvlet/tree/main/website/docs/syntax/data-models.md","tags":[],"version":"current","frontMatter":{},"sidebar":"tutorialSidebar","previous":{"title":"AsOf Join","permalink":"/wvlet/docs/syntax/asof-join"},"next":{"title":"Stdlib Reference","permalink":"/wvlet/docs/syntax/stdlib-reference"}}');var s=t(1058),i=t(6798);const r={},d="Data Models",o={},c=[{value:"Defining Data Models",id:"defining-data-models",level:3},{value:"Declaring Table Schemas",id:"declaring-table-schemas",level:3},{value:"Traits: Method Interfaces",id:"traits-method-interfaces",level:3},{value:"<code>table</code> vs <code>trait</code> vs <code>type</code>",id:"table-vs-trait-vs-type",level:4},{value:"Composing Declarations with Mixins",id:"composing-declarations-with-mixins",level:4}];function l(e){const n={a:"a",admonition:"admonition",code:"code",em:"em",h1:"h1",h3:"h3",h4:"h4",header:"header",li:"li",p:"p",pre:"pre",strong:"strong",ul:"ul",...(0,i.R)(),...e.components};return(0,s.jsxs)(s.Fragment,{children:[(0,s.jsx)(n.header,{children:(0,s.jsx)(n.h1,{id:"data-models",children:"Data Models"})}),"\n",(0,s.jsx)(n.admonition,{type:"warning",children:(0,s.jsx)(n.p,{children:"This page is still work in progress."})}),"\n",(0,s.jsx)(n.h3,{id:"defining-data-models",children:"Defining Data Models"}),"\n",(0,s.jsxs)(n.p,{children:["In Wvlet, you can define reusable data models, which wraps an Wvlet query with ",(0,s.jsx)(n.code,{children:"model (model name) = { ... }"})," block:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-wvlet",children:"model my_model = {\n  -- Write your query here\n  from ...\n  ... \n}\n"})}),"\n",(0,s.jsx)(n.p,{children:"Models can be used in other queries in the same manner with scanning a table:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-wvlet",children:"from my_model\nlimit 10\n"})}),"\n",(0,s.jsxs)(n.p,{children:["Data models are often the units to ",(0,s.jsx)(n.strong,{children:"materialize query results into the target database tables"}),". If your data model needs to be accessed by multiple queries, materializing (or persisting) data models will reduce the cost of data processing and often accelerates the query processing."]}),"\n",(0,s.jsx)(n.h3,{id:"declaring-table-schemas",children:"Declaring Table Schemas"}),"\n",(0,s.jsxs)(n.p,{children:["A ",(0,s.jsx)(n.code,{children:"table"})," declaration describes a stored relation \u2014 its column names and types \u2014 so queries\nreferencing the table can be type-checked without connecting to the database:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-wvlet",children:"table orders = {\n  order_id: bigint\n  status: string\n}\n\n-- Type-checks against the declaration above, and compiles to `select * from orders`\nfrom orders\n"})}),"\n",(0,s.jsxs)(n.p,{children:["To describe a table that lives in a specific schema of your database, bind the declaration to\nits location with ",(0,s.jsx)(n.code,{children:"in <catalog>.<schema>"}),":"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-wvlet",children:"table orders in mydb.sales = {\n  order_id: bigint\n  status: string\n}\n\n-- Both resolve through the bound declaration and compile to a scan of mydb.sales.orders\nfrom sales.orders\nfrom mydb.sales.orders\n"})}),"\n",(0,s.jsxs)(n.p,{children:["Declarations are contracts, not actions: nothing is created when a declaration is read. Writes\nmaterialize them \u2014 appending to a declared table that does not exist yet creates it from the\ndeclared columns, and ",(0,s.jsx)(n.code,{children:"create table <name>"})," reads its columns from the declaration (see\n",(0,s.jsx)(n.a,{href:"/wvlet/docs/syntax/table-management",children:"Table Management"}),"). Writes matching a bound declaration land at the bound\nlocation, so a declared name never reads from one place and writes to another."]}),"\n",(0,s.jsx)(n.p,{children:"Notes on how bound declarations resolve:"}),"\n",(0,s.jsxs)(n.ul,{children:["\n",(0,s.jsx)(n.li,{children:"Catalog and schema names are matched case-insensitively, following SQL identifier semantics."}),"\n",(0,s.jsxs)(n.li,{children:["A bare reference like ",(0,s.jsx)(n.code,{children:"from orders"})," resolves through a bound declaration only when the binding\nmatches the current catalog and schema of the compilation context, mirroring the search-path\nbehavior of SQL engines."]}),"\n",(0,s.jsxs)(n.li,{children:["Connector names take precedence: a reference like ",(0,s.jsx)(n.code,{children:"from myconnector.sales.orders"})," resolves\nthrough the connector configured in your profile, not through a bound declaration."]}),"\n",(0,s.jsx)(n.li,{children:"Table declarations take precedence over the live database catalog, so committed declaration\nfiles act like a lockfile: the compile-time schema stays deterministic even when the database\nchanges, while queries still execute against the real tables."}),"\n"]}),"\n",(0,s.jsxs)(n.p,{children:["Instead of writing these declarations by hand, you can generate them from a live database with\n",(0,s.jsx)(n.a,{href:"/wvlet/docs/usage/catalog-import",children:(0,s.jsx)(n.code,{children:"wvlet catalog import"})}),"."]}),"\n",(0,s.jsx)(n.h3,{id:"traits-method-interfaces",children:"Traits: Method Interfaces"}),"\n",(0,s.jsxs)(n.p,{children:["A ",(0,s.jsx)(n.code,{children:"trait"})," attaches reusable ",(0,s.jsx)(n.code,{children:"def"})," members to a type \u2014 a method interface in the Scala/Rust\nsense. Declare a new value domain by extending a base type, then use it as a column type; the\ncolumns get the trait's methods:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-wvlet",children:'trait ip_address extends string = {\n  def country_name: string = sql"ip_to_country(${this})"\n}\n\ntable access_logs = {\n  time: timestamp\n  client_ip: ip_address\n\n  -- Row methods can call trait methods of the declared column types\n  def client_country: string = client_ip.country_name\n}\n\n-- Both trait and row methods resolve directly on a scan of the declared table\nfrom access_logs\nselect time, client_ip.country_name, _.client_country\n'})}),"\n",(0,s.jsxs)(n.p,{children:["A trait re-opening an existing type with a ",(0,s.jsx)(n.em,{children:"dialect scope"})," provides engine-specific\nimplementations \u2014 the standard library defines its methods this way:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-wvlet",children:"trait ip_address in duckdb extends string = {\n  def country_name: string = sql\"'N/A'\"   -- DuckDB has no IP database\n}\n"})}),"\n",(0,s.jsxs)(n.p,{children:["Trait bodies carry ",(0,s.jsx)(n.code,{children:"def"})," members only \u2014 column fields are a compile-time error, because a trait\nnever describes storage. Likewise ",(0,s.jsx)(n.code,{children:"in"})," on a trait always names an engine dialect; binding a\ntrait to a ",(0,s.jsx)(n.code,{children:"<catalog>.<schema>"})," location is an error (declare a ",(0,s.jsx)(n.code,{children:"table"})," instead)."]}),"\n",(0,s.jsxs)(n.h4,{id:"table-vs-trait-vs-type",children:[(0,s.jsx)(n.code,{children:"table"})," vs ",(0,s.jsx)(n.code,{children:"trait"})," vs ",(0,s.jsx)(n.code,{children:"type"})]}),"\n",(0,s.jsxs)(n.p,{children:["Everything the family declares is a type \u2014 as in Scala, where traits and classes all introduce\ntypes while the ",(0,s.jsx)(n.code,{children:"type"})," keyword itself covers the general forms. ",(0,s.jsx)(n.code,{children:"table"})," and ",(0,s.jsx)(n.code,{children:"trait"})," are\nspecialized kinds of type that add a commitment on top; ",(0,s.jsx)(n.code,{children:"type"})," remains for declarations that\nmake neither:"]}),"\n",(0,s.jsxs)(n.ul,{children:["\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:(0,s.jsx)(n.code,{children:"table"})})," \u2014 a type committed to storage: a stored relation with columns, an optional\n",(0,s.jsx)(n.code,{children:"in <catalog>.<schema>"})," location, and optional row methods."]}),"\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:(0,s.jsx)(n.code,{children:"trait"})})," \u2014 a type committed to being a method interface: new value domains\n(",(0,s.jsx)(n.code,{children:"trait ip_address extends string"}),") and engine-dialect method packages\n(",(0,s.jsx)(n.code,{children:"trait any in duckdb"}),"). Never storage."]}),"\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:(0,s.jsx)(n.code,{children:"type"})})," \u2014 the general form, making no commitment: aliases and marker types\n(",(0,s.jsx)(n.code,{children:"type td_trino extends trino"}),"), and structural row or value shapes used as column or\nparameter types (",(0,s.jsx)(n.code,{children:"type point = { x: long, y: long }"})," with ",(0,s.jsx)(n.code,{children:"start: point"}),")."]}),"\n"]}),"\n",(0,s.jsxs)(n.p,{children:["Only the commitment-claiming ",(0,s.jsx)(n.code,{children:"type"})," spellings are deprecated, each warning toward its\nspecialized keyword: a columns-carrying ",(0,s.jsx)(n.code,{children:"type"})," that resolves a table reference is a storage\nclaim and warns toward ",(0,s.jsx)(n.code,{children:"table"}),", and a def-only ",(0,s.jsx)(n.code,{children:"type"})," is a method interface and warns toward\n",(0,s.jsx)(n.code,{children:"trait"}),". Structural types, aliases, and markers are the permanent ",(0,s.jsx)(n.code,{children:"type"})," usage and never warn."]}),"\n",(0,s.jsxs)(n.p,{children:["The ",(0,s.jsx)(n.code,{children:"in"}

1)," clause is unambiguous across the family: on a ",(0,s.jsx)(n.code,{children:"trait"})," or ",(0,s.jsx)(n.code,{children:"def"})," it always names an\nengine dialect; on a ",(0,s.jsx)(n.code,{children:"table"})," declaration, ",(0,s.jsx)(n.code,{children:"create schema"}),", or ",(0,s.jsx)(n.code,{children:"use"})," it always names a storage\nlocation."]}),"\n",(0,s.jsx)(n.h4,{id:"composing-declarations-with-mixins",children:"Composing Declarations with Mixins"}),"\n",(0,s.jsxs)(n.p,{children:["Since ",(0,s.jsx)(n.code,{children:"table"})," and ",(0,s.jsx)(n.code,{children:"trait"})," are kinds of type, a declaration can compose others with a\ncomma-separated ",(0,s.jsx)(n.code,{children:"extends"})," list: structural ",(0,s.jsx)(n.code,{children:"type"})," shapes and ",(0,s.jsx)(n.code,{children:"table"})," declarations contribute\ntheir columns, and ",(0,s.jsx)(n.code,{children:"trait"})," parents contribute their ",(0,s.jsx)(n.code,{children:"def"})," members (including dialect-scoped\nvariants):"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-wvlet",children:"type timestamped = { created_at: timestamp, updated_at: timestamp }\ntrait auditable = { def is_recent: boolean = created_at > now() - interval '7 days' }\n\ntable events extends timestamped, auditable = {\n  id: int\n  label: string\n}\n\nfrom events\nselect created_at, id, _.is_recent\n"})}),"\n",(0,s.jsxs)(n.p,{children:["The composed shape places mixed-in columns before own body columns, in parent-list order.\nA column reached through multiple parents with the same type appears once, so diamond mixins\nare fine; the same name with conflicting types is a compile-time error. Method precedence\nfollows Scala 3's intuition: own body defs shadow mixed-in ones, and among parents the later\none wins (",(0,s.jsx)(n.code,{children:"extends a, b"})," means b refines a)."]}),"\n",(0,s.jsxs)(n.p,{children:["Because a trait never describes storage, a ",(0,s.jsx)(n.code,{children:"trait"})," cannot extend a column-carrying\ndeclaration \u2014 that is a compile-time error pointing toward ",(0,s.jsx)(n.code,{children:"table"}),". A ",(0,s.jsx)(n.code,{children:"table"})," extending a\ntrait gains its methods; the single-parent forms (",(0,s.jsx)(n.code,{children:"trait ip_address extends string"}),",\n",(0,s.jsx)(n.code,{children:"type td_trino extends trino"}),") are unchanged as the one-element case of the same list."]})]})}function h(e={}){const{wrapper:n}={...(0,i.R)(),...e.components};return n?(0,s.jsx)(n,{...e,children:(0,s.jsx)(l,{...e})}):l(e)}},6798(e,n,t){t.d(n,{R:()=>r,x:()=>d});var a=t(3706);const s={},i=a.createContext(s);function r(e){const n=a.useContext(i);return a.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function d(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(s):e.components||s:r(e.components),a.createElement(i.Provider,{value:n},e.children)}}}]);