Tweet
Tweet

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

Parsing and generating SQLite's SQL dialect with Ruby Stephen Margheim RubyKaigi 2025

Slide 3

Slide 3 text

About me Stephen Margheim

Slide 4

Slide 4 text

About me Stephen Margheim @fractaledmind

Slide 5

Slide 5 text

About me Stephen Margheim @fractaledmind fractaledmind.github.io

Slide 6

Slide 6 text

About me Stephen Margheim @fractaledmind fractaledmind.github.io Regular contributor to:

Slide 7

Slide 7 text

About me Stephen Margheim @fractaledmind fractaledmind.github.io Regular contributor to: rails/rails

Slide 8

Slide 8 text

About me Stephen Margheim @fractaledmind fractaledmind.github.io Regular contributor to: rails/rails sparklemotion/sqlite3-ruby

Slide 9

Slide 9 text

No content

Slide 10

Slide 10 text

No content

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

No content

Slide 13

Slide 13 text

0

Slide 14

Slide 14 text

0 https://github.com/fractaledmind/plume

Slide 15

Slide 15 text

Agenda

Slide 16

Slide 16 text

Agenda Why How What next

Slide 17

Slide 17 text

Why

Slide 18

Slide 18 text

No content

Slide 19

Slide 19 text

No content

Slide 20

Slide 20 text

No content

Slide 21

Slide 21 text

No content

Slide 22

Slide 22 text

No content

Slide 23

Slide 23 text

No content

Slide 24

Slide 24 text

No content

Slide 25

Slide 25 text

CREATE TABLE table ( id INTEGER PRIMARY KEY AUTOINCREMENT, foo TEXT, UNIQUE (id, foo) CHECK (LENGTH(foo) < 10) )

Slide 26

Slide 26 text

CREATE TABLE table ( id INTEGER PRIMARY KEY AUTOINCREMENT, foo TEXT, UNIQUE (id, foo) CHECK (LENGTH(foo) < 10) )

Slide 27

Slide 27 text

CREATE TABLE table ( id INTEGER PRIMARY KEY AUTOINCREMENT, foo TEXT, UNIQUE (id, foo) CHECK (LENGTH(foo) < 10) ) ,

Slide 28

Slide 28 text

No content

Slide 29

Slide 29 text

No content

Slide 30

Slide 30 text

No content

Slide 31

Slide 31 text

Compatibility is king

Slide 32

Slide 32 text

Compatibility is king 👑

Slide 33

Slide 33 text

No content

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

No content

Slide 36

Slide 36 text

No content

Slide 37

Slide 37 text

No content

Slide 38

Slide 38 text

No content

Slide 39

Slide 39 text

No content

Slide 40

Slide 40 text

0

Slide 41

Slide 41 text

0

Slide 42

Slide 42 text

create_table :posts, force: true do |t| t.json :payload, null: false, default: {} t.virtual :external_id, type: :string, as: "JSON_EXTRACT(payload, '$.id')", stored: true, null: false, index: true end

Slide 43

Slide 43 text

create_table :posts, force: true do |t| t.json :payload, null: false, default: {} t.virtual :external_id, type: :string, as: "JSON_EXTRACT(payload, '$.id')", stored: true, null: false, index: true end

Slide 44

Slide 44 text

# Splitting with left parentheses and discarding the first part will return all columns separated with comma(,). result.partition(UNQUOTED_OPEN_PARENS_REGEX) .last .sub(FINAL_CLOSE_PARENS_REGEX, "") .split(",") .map(&:strip)

Slide 45

Slide 45 text

# Splitting with left parentheses and discarding the first part will return all columns separated with comma(,). result.partition(UNQUOTED_OPEN_PARENS_REGEX) .last .sub(FINAL_CLOSE_PARENS_REGEX, "") .split(/ ,(?=\s(?:CONSTRAINT|"(?:#{Regexp.union(column_names).source})")) /i).map(&:strip)

Slide 46

Slide 46 text

0

Slide 47

Slide 47 text

Plume.parse_schema(sql).columns

Slide 48

Slide 48 text

Why Foundation for next wave of SQLite tooling Secure Rails’ foundation for introspecting SQLite (?) Challenge myself

Slide 49

Slide 49 text

How

Slide 50

Slide 50 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 51

Slide 51 text

Plume.parse(sql).first

Slide 52

Slide 52 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 53

Slide 53 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 54

Slide 54 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 55

Slide 55 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 56

Slide 56 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 57

Slide 57 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 58

Slide 58 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 59

Slide 59 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 60

Slide 60 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 61

Slide 61 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 62

Slide 62 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 63

Slide 63 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 64

Slide 64 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 65

Slide 65 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 66

Slide 66 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 67

Slide 67 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 68

Slide 68 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 69

Slide 69 text

create table users ( id integer primary key autoincrement, email string not null unique, )

Slide 70

Slide 70 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 71

Slide 71 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 72

Slide 72 text

create temp table if not exists `schema0`."tb0" ( [c], c0 integer primary key desc on conflict abort autoincrement, c1 int constraint 'nonnull' not null on conflict rollback, c2 text constraint 'uniqued' unique on conflict ignore, c3 blob constraint 'checked' check (c3 > 0), c4 real constraint 'defaulted' default (1.1 * 2.2), c5 any constraint 'collated' collate rtrim, c6 decimal(4, 6) generated always as (1.1 + 2.2) stored, c7 constraint 'fk0' references tb1(c1) on delete set null on update cascade match full deferrable initially deferred, primary key (c0, c1 autoincrement) on conflict abort, unique (c0, c1) on conflict rollback check (c0 > 0) foreign key (c0) references tb1(c1) on delete cascade on update restrict match full deferrable initially deferred ) STRICT, WITHOUT ROWID;

Slide 73

Slide 73 text

Plume::CreateTableStatement( temporary = true, if_not_exists = true, table = Plume::TableName(schema = "schema0", table = "tb0"), columns = [ Plume::ColumnDefinition(name = "c"), Plume::ColumnDefinition( name = "c0", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint( name = "pk0", direction = :DESC, autoincrement = true, conflict_clause = Plume::ConflictClause(resolution = :ABORT), ), ], ), Plume::ColumnDefinition( name = "c1", type = Plume::ColumnType(text = "int", affinity = :INTEGER),

Slide 74

Slide 74 text

No content

Slide 75

Slide 75 text

Patterson’s Parsing Principles write Ruby parser to minimize Ruby <—> C calls minimize allocations (pass integers and symbols) use lazy allocations when possible (e.g. token values)

Slide 76

Slide 76 text

class Plume::Lexer def initialize(sql) @sql = sql.freeze @cursor, @anchor = 0 end def next_token @anchor = @cursor lex_next_token end def value = @sql.byteslice(@anchor, (@cursor - @anchor)) def lex_next_token = # ... def peek(n = 0) = @sql.getbyte(@cursor+n) def step(n = 1) = @cursor += n def scan(n = 0) = peek(n).tap { step(n+1) } end

Slide 77

Slide 77 text

class Plume::Lexer def initialize(sql) @sql = sql.freeze @cursor, @anchor = 0 end def next_token @anchor = @cursor lex_next_token end def value = @sql.byteslice(@anchor, (@cursor - @anchor)) def lex_next_token = # ... def peek(n = 0) = @sql.getbyte(@cursor+n) def step(n = 1) = @cursor += n def scan(n = 0) = peek(n).tap { step(n+1) } end

Slide 78

Slide 78 text

class Plume::Lexer def initialize(sql) @sql = sql.freeze @cursor, @anchor = 0 end def next_token @anchor = @cursor lex_next_token end def value = @sql.byteslice(@anchor, (@cursor - @anchor)) def lex_next_token = # ... def peek(n = 0) = @sql.getbyte(@cursor+n) def step(n = 1) = @cursor += n def scan(n = 0) = peek(n).tap { step(n+1) } end

Slide 79

Slide 79 text

class Plume::Lexer def initialize(sql) @sql = sql.freeze @cursor, @anchor = 0 end def next_token @anchor = @cursor lex_next_token end def value = @sql.byteslice(@anchor, (@cursor - @anchor)) def lex_next_token = # ... def peek(n = 0) = @sql.getbyte(@cursor+n) def step(n = 1) = @cursor += n def scan(n = 0) = peek(n).tap { step(n+1) } end

Slide 80

Slide 80 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end def create_table_stmt = # ... private def require(token) = # ... def maybe(token = nil, &block) = # ... def require_all_of(*tokens) = # ... def maybe_all_of(*tokens) = # ... def maybe_one_of(*tokens) = # ... def require_one_of(*tokens) = # ... def require_some(**options, &block) = # ... def maybe_some(**options, &block) = # ... end

Slide 81

Slide 81 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end def create_table_stmt = # ... private def require(token) = # ... def maybe(token = nil, &block) = # ... def require_all_of(*tokens) = # ... def maybe_all_of(*tokens) = # ... def maybe_one_of(*tokens) = # ... def require_one_of(*tokens) = # ... def require_some(**options, &block) = # ... def maybe_some(**options, &block) = # ... end

Slide 82

Slide 82 text

No content

Slide 83

Slide 83 text

No content

Slide 84

Slide 84 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = table_name() # ... end end

Slide 85

Slide 85 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = table_name() # ... end end

Slide 86

Slide 86 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = table_name() # ... end end

Slide 87

Slide 87 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = table_name() # ... end end

Slide 88

Slide 88 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = table_name() # ... end end

Slide 89

Slide 89 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = table_name() # ... end end

Slide 90

Slide 90 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = require { table_name() } # ... end end

Slide 91

Slide 91 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = table_name() # ... end end

Slide 92

Slide 92 text

class Plume::Parser def initialize(sql) @lexer = Lexer.new(sql, skip_spaces: true) @peek_buffer = [] end # ... def create_table_stmt create_kw = require :CREATE temp_kw = maybe_one_of :TEMP, :TEMPORARY table_kw = require :TABLE if_not_exists_kw = maybe_all_of :IF, :NOT, :EXISTS table_name = table_name() # ... end end

Slide 93

Slide 93 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 94

Slide 94 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 95

Slide 95 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 96

Slide 96 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 97

Slide 97 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 98

Slide 98 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 99

Slide 99 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 100

Slide 100 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 101

Slide 101 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 102

Slide 102 text

CREATE TABLE table ( id INTEGER PRIMARY KEY AUTOINCREMENT, foo TEXT, UNIQUE (id, foo) CHECK (LENGTH(foo) < 10) ) ,

Slide 103

Slide 103 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 104

Slide 104 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 105

Slide 105 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 106

Slide 106 text

class Plume::Parser def create_table_stmt # ... if maybe :AS # ... elsif (columns_lp = maybe :LP) columns = require_some(trailing_sep: :COMMA) { column_def } constraints = maybe_some(trailing_sep: [:COMMA, nil].freeze) do # ... end columns_rp = require :RP options = maybe_some(trailing_sep: :COMMA) do # ... end CreateTableStatement.new(...) end end end

Slide 107

Slide 107 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 108

Slide 108 text

Plume::CreateTableStatement( full_source = "create table users (id integer primary key autoincrement, email string not null unique)", create_kw = [:CREATE, 0, 6, :keyword], table_kw = [:TABLE, 7, 12, :keyword], columns_lp = [:LP, 19, 20, :punctuation], columns_rp = [:RP, 93, 94, :punctuation], table = Plume::TableName( full_source = "...", table_tk=[:ID, 13, 18, :identifier] ), columns=[ Plume::ColumnDefinition( full_source = "...", trailing = [:COMMA, 59, 60, :punctuation], name_tk = [:ID, 23, 25, :identifier], type = Plume::ColumnType( full_source = "...", text_span = [[], 26, 33, :keyword] ), constraints = [

Slide 109

Slide 109 text

Plume::CreateTableStatement( full_source = "create table users (id integer primary key autoincrement, email string not null unique)", create_kw = [:CREATE, 0, 6, :keyword], table_kw = [:TABLE, 7, 12, :keyword], columns_lp = [:LP, 19, 20, :punctuation], columns_rp = [:RP, 93, 94, :punctuation], table = Plume::TableName( full_source = "...", table_tk=[:ID, 13, 18, :identifier] ), columns=[ Plume::ColumnDefinition( full_source = "...", trailing = [:COMMA, 59, 60, :punctuation], name_tk = [:ID, 23, 25, :identifier], type = Plume::ColumnType( full_source = "...", text_span = [[], 26, 33, :keyword] ), constraints = [

Slide 110

Slide 110 text

Plume::CreateTableStatement( full_source = "create table users (id integer primary key autoincrement, email string not null unique)", create_kw = [:CREATE, 0, 6, :keyword], table_kw = [:TABLE, 7, 12, :keyword], columns_lp = [:LP, 19, 20, :punctuation], columns_rp = [:RP, 93, 94, :punctuation], table = Plume::TableName( full_source = "...", table_tk=[:ID, 13, 18, :identifier] ), columns=[ Plume::ColumnDefinition( full_source = "...", trailing = [:COMMA, 59, 60, :punctuation], name_tk = [:ID, 23, 25, :identifier], type = Plume::ColumnType( full_source = "...", text_span = [[], 26, 33, :keyword] ), constraints = [

Slide 111

Slide 111 text

Plume::CreateTableStatement( full_source = "create table users (id integer primary key autoincrement, email string not null unique)", create_kw = [:CREATE, 0, 6, :keyword], table_kw = [:TABLE, 7, 12, :keyword], columns_lp = [:LP, 19, 20, :punctuation], columns_rp = [:RP, 93, 94, :punctuation], table = Plume::TableName( full_source = "...", table_tk=[:ID, 13, 18, :identifier] ), columns=[ Plume::ColumnDefinition( full_source = "...", trailing = [:COMMA, 59, 60, :punctuation], name_tk = [:ID, 23, 25, :identifier], type = Plume::ColumnType( full_source = "...", text_span = [[], 26, 33, :keyword] ), constraints = [

Slide 112

Slide 112 text

Plume::CreateTableStatement( full_source = "create table users (id integer primary key autoincrement, email string not null unique)", create_kw = [:CREATE, 0, 6, :keyword], table_kw = [:TABLE, 7, 12, :keyword], columns_lp = [:LP, 19, 20, :punctuation], columns_rp = [:RP, 93, 94, :punctuation], table = Plume::TableName( full_source = "...", table_tk=[:ID, 13, 18, :identifier] ), columns=[ Plume::ColumnDefinition( full_source = "...", trailing = [:COMMA, 59, 60, :punctuation], name_tk = [:ID, 23, 25, :identifier], type = Plume::ColumnType( full_source = "...", text_span = [[], 26, 33, :keyword] ), constraints = [

Slide 113

Slide 113 text

Plume::CreateTableStatement( full_source = "create table users (id integer primary key autoincrement, email string not null unique)", create_kw = [:CREATE, 0, 6, :keyword], table_kw = [:TABLE, 7, 12, :keyword], columns_lp = [:LP, 19, 20, :punctuation], columns_rp = [:RP, 93, 94, :punctuation], table = Plume::TableName( full_source = "...", table_tk=[:ID, 13, 18, :identifier] ), columns=[ Plume::ColumnDefinition( full_source = "...", trailing = [:COMMA, 59, 60, :punctuation], name_tk = [:ID, 23, 25, :identifier], type = Plume::ColumnType( full_source = "...", text_span = [[], 26, 33, :keyword] ), constraints = [

Slide 114

Slide 114 text

Plume::CreateTableStatement( full_source = "create table users (id integer primary key autoincrement, email string not null unique)", create_kw = [:CREATE, 0, 6, :keyword], table_kw = [:TABLE, 7, 12, :keyword], columns_lp = [:LP, 19, 20, :punctuation], columns_rp = [:RP, 93, 94, :punctuation], table = Plume::TableName( full_source = "...", table_tk=[:ID, 13, 18, :identifier] ), columns=[ Plume::ColumnDefinition( full_source = "...", trailing = [:COMMA, 59, 60, :punctuation], name_tk = [:ID, 23, 25, :identifier], type = Plume::ColumnType( full_source = "...", text_span = [[], 26, 33, :keyword] ), constraints = [

Slide 115

Slide 115 text

Plume::CreateTableStatement( table = Plume::TableName(table = "users"), columns = [ Plume::ColumnDefinition( name = "id", type = Plume::ColumnType(text = "integer", affinity = :INTEGER), constraints = [ Plume::PrimaryKeyColumnConstraint(autoincrement = true), ], ), Plume::ColumnDefinition( name = "email", type = Plume::ColumnType(text = "string", affinity = :ANY), constraints = [ Plume::NotNullColumnConstraint(), Plume::UniqueColumnConstraint(), ] ) ] )

Slide 116

Slide 116 text

How hand-written recursive descent parser minimal allocations, parsing token by token grammar encoded with helper methods concrete syntax tree with abstract representation

Slide 117

Slide 117 text

What next

Slide 118

Slide 118 text

What next More statements

Slide 119

Slide 119 text

No content

Slide 120

Slide 120 text

No content

Slide 121

Slide 121 text

What next More statements

Slide 122

Slide 122 text

What next More statements Bug hunt for incompatibilities

Slide 123

Slide 123 text

What next More statements Bug hunt for incompatibilities Web AST explorer

Slide 124

Slide 124 text

What next More statements Bug hunt for incompatibilities Web AST explorer SQL generation

Slide 125

Slide 125 text

Plume::Query .from(Artists) .where( EXISTS: Plume::Query .from(JSON_EACH: :skills) .select(true) .where(value: ['skill_1', 'skill_2']) .limit(1) ) .order(id: ["a".."z", nil])

Slide 126

Slide 126 text

{ SELECT: ALL, FROM: Artists, WHERE: { EXISTS: { SELECT: true, FROM: { JSON_EACH: Artists.skills }, WHERE: { value: ['skill_1', 'skill_2'] }, LIMIT: 1 } }, ORDER_BY: { Artists.id => ["a".."z", nil] } }

Slide 127

Slide 127 text

What next More statements Bug hunt for incompatibilities Web AST explorer SQL generation … your contribution?

Slide 128

Slide 128 text

0

Slide 129

Slide 129 text

0 https://github.com/fractaledmind/plume

Slide 130

Slide 130 text

No content

Slide 131

Slide 131 text

Thank you ありがとう