nom_sql/
create.rs

1use nom::character::complete::{digit1, multispace0, multispace1};
2use std::fmt;
3use std::str;
4use std::str::FromStr;
5
6use column::{Column, ColumnConstraint, ColumnSpecification};
7use common::{
8    column_identifier_no_alias, parse_comment, sql_identifier, statement_terminator,
9    table_reference, type_identifier, ws_sep_comma, Literal, Real, SqlType, TableKey,
10};
11use compound_select::{compound_selection, CompoundSelectStatement};
12use create_table_options::table_options;
13use keywords::escape_if_keyword;
14use nom::branch::alt;
15use nom::bytes::complete::{tag, tag_no_case, take_until};
16use nom::combinator::{map, opt};
17use nom::multi::{many0, many1};
18use nom::sequence::{delimited, preceded, terminated, tuple};
19use nom::IResult;
20use order::{order_type, OrderType};
21use select::{nested_selection, SelectStatement};
22use table::Table;
23
24#[derive(Clone, Debug, Default, Eq, Hash, PartialEq, Serialize, Deserialize)]
25pub struct CreateTableStatement {
26    pub table: Table,
27    pub fields: Vec<ColumnSpecification>,
28    pub keys: Option<Vec<TableKey>>,
29}
30
31impl fmt::Display for CreateTableStatement {
32    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
33        write!(f, "CREATE TABLE {} ", escape_if_keyword(&self.table.name))?;
34        write!(f, "(")?;
35        write!(
36            f,
37            "{}",
38            self.fields
39                .iter()
40                .map(|field| format!("{}", field))
41                .collect::<Vec<_>>()
42                .join(", ")
43        )?;
44        if let Some(ref keys) = self.keys {
45            write!(
46                f,
47                ", {}",
48                keys.iter()
49                    .map(|key| format!("{}", key))
50                    .collect::<Vec<_>>()
51                    .join(", ")
52            )?;
53        }
54        write!(f, ")")
55    }
56}
57
58#[derive(Clone, Debug, Eq, Hash, PartialEq, Serialize, Deserialize)]
59pub enum SelectSpecification {
60    Compound(CompoundSelectStatement),
61    Simple(SelectStatement),
62}
63
64impl fmt::Display for SelectSpecification {
65    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
66        match *self {
67            SelectSpecification::Compound(ref csq) => write!(f, "{}", csq),
68            SelectSpecification::Simple(ref sq) => write!(f, "{}", sq),
69        }
70    }
71}
72
73#[derive(Clone, Debug, Eq, Hash, PartialEq, Serialize, Deserialize)]
74pub struct CreateViewStatement {
75    pub name: String,
76    pub fields: Vec<Column>,
77    pub definition: Box<SelectSpecification>,
78}
79
80impl fmt::Display for CreateViewStatement {
81    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
82        write!(f, "CREATE VIEW {} ", escape_if_keyword(&self.name))?;
83        if !self.fields.is_empty() {
84            write!(f, "(")?;
85            write!(
86                f,
87                "{}",
88                self.fields
89                    .iter()
90                    .map(|field| format!("{}", field))
91                    .collect::<Vec<_>>()
92                    .join(", ")
93            )?;
94            write!(f, ") ")?;
95        }
96        write!(f, "AS ")?;
97        write!(f, "{}", self.definition)
98    }
99}
100
101// MySQL grammar element for index column definition (§13.1.18, index_col_name)
102pub fn index_col_name(i: &[u8]) -> IResult<&[u8], (Column, Option<u16>, Option<OrderType>)> {
103    let (remaining_input, (column, len_u8, order)) = tuple((
104        terminated(column_identifier_no_alias, multispace0),
105        opt(delimited(tag("("), digit1, tag(")"))),
106        opt(order_type),
107    ))(i)?;
108    let len = len_u8.map(|l| u16::from_str(str::from_utf8(l).unwrap()).unwrap());
109
110    Ok((remaining_input, (column, len, order)))
111}
112
113// Helper for list of index columns
114pub fn index_col_list(i: &[u8]) -> IResult<&[u8], Vec<Column>> {
115    many0(map(
116        terminated(index_col_name, opt(ws_sep_comma)),
117        // XXX(malte): ignores length and order
118        |e| e.0,
119    ))(i)
120}
121
122// Parse rule for an individual key specification.
123pub fn key_specification(i: &[u8]) -> IResult<&[u8], TableKey> {
124    alt((full_text_key, primary_key, unique, key_or_index))(i)
125}
126
127fn full_text_key(i: &[u8]) -> IResult<&[u8], TableKey> {
128    let (remaining_input, (_, _, _, _, name, _, columns)) = tuple((
129        tag_no_case("fulltext"),
130        multispace1,
131        alt((tag_no_case("key"), tag_no_case("index"))),
132        multispace1,
133        opt(sql_identifier),
134        multispace0,
135        delimited(
136            tag("("),
137            delimited(multispace0, index_col_list, multispace0),
138            tag(")"),
139        ),
140    ))(i)?;
141
142    match name {
143        Some(name) => {
144            let n = String::from_utf8(name.to_vec()).unwrap();
145            Ok((remaining_input, TableKey::FulltextKey(Some(n), columns)))
146        }
147        None => Ok((remaining_input, TableKey::FulltextKey(None, columns))),
148    }
149}
150
151fn primary_key(i: &[u8]) -> IResult<&[u8], TableKey> {
152    let (remaining_input, (_, _, columns, _)) = tuple((
153        tag_no_case("primary key"),
154        multispace0,
155        delimited(
156            tag("("),
157            delimited(multispace0, index_col_list, multispace0),
158            tag(")"),
159        ),
160        opt(map(
161            preceded(multispace1, tag_no_case("auto_increment")),
162            |_| (),
163        )),
164    ))(i)?;
165
166    Ok((remaining_input, TableKey::PrimaryKey(columns)))
167}
168
169fn unique(i: &[u8]) -> IResult<&[u8], TableKey> {
170    // TODO: add branching to correctly parse whitespace after `unique`
171    let (remaining_input, (_, _, _, name, _, columns)) = tuple((
172        tag_no_case("unique"),
173        opt(preceded(
174            multispace1,
175            alt((tag_no_case("key"), tag_no_case("index"))),
176        )),
177        multispace0,
178        opt(sql_identifier),
179        multispace0,
180        delimited(
181            tag("("),
182            delimited(multispace0, index_col_list, multispace0),
183            tag(")"),
184        ),
185    ))(i)?;
186
187    match name {
188        Some(name) => {
189            let n = String::from_utf8(name.to_vec()).unwrap();
190            Ok((remaining_input, TableKey::UniqueKey(Some(n), columns)))
191        }
192        None => Ok((remaining_input, TableKey::UniqueKey(None, columns))),
193    }
194}
195
196fn key_or_index(i: &[u8]) -> IResult<&[u8], TableKey> {
197    let (remaining_input, (_, _, name, _, columns)) = tuple((
198        alt((tag_no_case("key"), tag_no_case("index"))),
199        multispace0,
200        sql_identifier,
201        multispace0,
202        delimited(
203            tag("("),
204            delimited(multispace0, index_col_list, multispace0),
205            tag(")"),
206        ),
207    ))(i)?;
208
209    let n = String::from_utf8(name.to_vec()).unwrap();
210    Ok((remaining_input, TableKey::Key(n, columns)))
211}
212
213// Parse rule for a comma-separated list.
214pub fn key_specification_list(i: &[u8]) -> IResult<&[u8], Vec<TableKey>> {
215    many1(terminated(key_specification, opt(ws_sep_comma)))(i)
216}
217
218fn field_specification(i: &[u8]) -> IResult<&[u8], ColumnSpecification> {
219    let (remaining_input, (column, field_type, constraints, comment, _)) = tuple((
220        column_identifier_no_alias,
221        opt(delimited(multispace1, type_identifier, multispace0)),
222        many0(column_constraint),
223        opt(parse_comment),
224        opt(ws_sep_comma),
225    ))(i)?;
226
227    let sql_type = match field_type {
228        None => SqlType::Text,
229        Some(ref t) => t.clone(),
230    };
231    Ok((
232        remaining_input,
233        ColumnSpecification {
234            column,
235            sql_type,
236            constraints: constraints.into_iter().filter_map(|m| m).collect(),
237            comment,
238        },
239    ))
240}
241
242// Parse rule for a comma-separated list.
243pub fn field_specification_list(i: &[u8]) -> IResult<&[u8], Vec<ColumnSpecification>> {
244    many1(field_specification)(i)
245}
246
247// Parse rule for a column definition constraint.
248pub fn column_constraint(i: &[u8]) -> IResult<&[u8], Option<ColumnConstraint>> {
249    let not_null = map(
250        delimited(multispace0, tag_no_case("not null"), multispace0),
251        |_| Some(ColumnConstraint::NotNull),
252    );
253    let null = map(
254        delimited(multispace0, tag_no_case("null"), multispace0),
255        |_| None,
256    );
257    let auto_increment = map(
258        delimited(multispace0, tag_no_case("auto_increment"), multispace0),
259        |_| Some(ColumnConstraint::AutoIncrement),
260    );
261    let primary_key = map(
262        delimited(multispace0, tag_no_case("primary key"), multispace0),
263        |_| Some(ColumnConstraint::PrimaryKey),
264    );
265    let unique = map(
266        delimited(multispace0, tag_no_case("unique"), multispace0),
267        |_| Some(ColumnConstraint::Unique),
268    );
269    let character_set = map(
270        preceded(
271            delimited(multispace0, tag_no_case("character set"), multispace1),
272            sql_identifier,
273        ),
274        |cs| {
275            let char_set = str::from_utf8(cs).unwrap().to_owned();
276            Some(ColumnConstraint::CharacterSet(char_set))
277        },
278    );
279    let collate = map(
280        preceded(
281            delimited(multispace0, tag_no_case("collate"), multispace1),
282            sql_identifier,
283        ),
284        |c| {
285            let collation = str::from_utf8(c).unwrap().to_owned();
286            Some(ColumnConstraint::Collation(collation))
287        },
288    );
289
290    alt((
291        not_null,
292        null,
293        auto_increment,
294        default,
295        primary_key,
296        unique,
297        character_set,
298        collate,
299    ))(i)
300}
301
302fn fixed_point(i: &[u8]) -> IResult<&[u8], Literal> {
303    let (remaining_input, (i, _, f)) = tuple((digit1, tag("."), digit1))(i)?;
304
305    Ok((
306        remaining_input,
307        Literal::FixedPoint(Real {
308            integral: i32::from_str(str::from_utf8(i).unwrap()).unwrap(),
309            fractional: i32::from_str(str::from_utf8(f).unwrap()).unwrap(),
310        }),
311    ))
312}
313
314fn default(i: &[u8]) -> IResult<&[u8], Option<ColumnConstraint>> {
315    let (remaining_input, (_, _, _, def, _)) = tuple((
316        multispace0,
317        tag_no_case("default"),
318        multispace1,
319        alt((
320            map(
321                delimited(tag("'"), take_until("'"), tag("'")),
322                |s: &[u8]| Literal::String(String::from_utf8(s.to_vec()).unwrap()),
323            ),
324            fixed_point,
325            map(digit1, |d| {
326                let d_i64 = i64::from_str(str::from_utf8(d).unwrap()).unwrap();
327                Literal::Integer(d_i64)
328            }),
329            map(tag("''"), |_| Literal::String(String::from(""))),
330            map(tag_no_case("null"), |_| Literal::Null),
331            map(tag_no_case("current_timestamp"), |_| {
332                Literal::CurrentTimestamp
333            }),
334        )),
335        multispace0,
336    ))(i)?;
337
338    Ok((remaining_input, Some(ColumnConstraint::DefaultValue(def))))
339}
340
341// Parse rule for a SQL CREATE TABLE query.
342// TODO(malte): support types, TEMPORARY tables, IF NOT EXISTS, AS stmt
343pub fn creation(i: &[u8]) -> IResult<&[u8], CreateTableStatement> {
344    let (remaining_input, (_, _, _, _, table, _, _, _, fields_list, _, keys_list, _, _, _, _, _)) =
345        tuple((
346            tag_no_case("create"),
347            multispace1,
348            tag_no_case("table"),
349            multispace1,
350            table_reference,
351            multispace0,
352            tag("("),
353            multispace0,
354            field_specification_list,
355            multispace0,
356            opt(key_specification_list),
357            multispace0,
358            tag(")"),
359            multispace0,
360            table_options,
361            statement_terminator,
362        ))(i)?;
363
364    // "table AS alias" isn't legal in CREATE statements
365    assert!(table.alias.is_none());
366    // attach table names to columns:
367    let fields = fields_list
368        .into_iter()
369        .map(|field| {
370            let column = Column {
371                table: Some(table.name.clone()),
372                ..field.column
373            };
374
375            ColumnSpecification { column, ..field }
376        })
377        .collect();
378
379    // and to keys:
380    let keys = keys_list.and_then(|ks| {
381        Some(
382            ks.into_iter()
383                .map(|key| {
384                    let attach_names = |columns: Vec<Column>| {
385                        columns
386                            .into_iter()
387                            .map(|column| Column {
388                                table: Some(table.name.clone()),
389                                ..column
390                            })
391                            .collect()
392                    };
393
394                    match key {
395                        TableKey::PrimaryKey(columns) => {
396                            TableKey::PrimaryKey(attach_names(columns))
397                        }
398                        TableKey::UniqueKey(name, columns) => {
399                            TableKey::UniqueKey(name, attach_names(columns))
400                        }
401                        TableKey::FulltextKey(name, columns) => {
402                            TableKey::FulltextKey(name, attach_names(columns))
403                        }
404                        TableKey::Key(name, columns) => TableKey::Key(name, attach_names(columns)),
405                    }
406                })
407                .collect(),
408        )
409    });
410
411    Ok((
412        remaining_input,
413        CreateTableStatement {
414            table,
415            fields,
416            keys,
417        },
418    ))
419}
420
421// Parse rule for a SQL CREATE VIEW query.
422pub fn view_creation(i: &[u8]) -> IResult<&[u8], CreateViewStatement> {
423    let (remaining_input, (_, _, _, _, name_slice, _, _, _, def, _)) = tuple((
424        tag_no_case("create"),
425        multispace1,
426        tag_no_case("view"),
427        multispace1,
428        sql_identifier,
429        multispace1,
430        tag_no_case("as"),
431        multispace1,
432        alt((
433            map(compound_selection, |s| SelectSpecification::Compound(s)),
434            map(nested_selection, |s| SelectSpecification::Simple(s)),
435        )),
436        statement_terminator,
437    ))(i)?;
438
439    let name = String::from_utf8(name_slice.to_vec()).unwrap();
440    let fields = vec![]; // TODO(malte): support
441    let definition = Box::new(def);
442
443    Ok((
444        remaining_input,
445        CreateViewStatement {
446            name,
447            fields,
448            definition,
449        },
450    ))
451}
452
453#[cfg(test)]
454mod tests {
455    use super::*;
456    use column::Column;
457    use table::Table;
458
459    #[test]
460    fn sql_types() {
461        let type0 = "bigint(20)";
462        let type1 = "varchar(255) binary";
463        let type2 = "bigint(20) unsigned";
464        let type3 = "bigint(20) signed";
465
466        let res = type_identifier(type0.as_bytes());
467        assert_eq!(res.unwrap().1, SqlType::Bigint(20));
468        let res = type_identifier(type1.as_bytes());
469        assert_eq!(res.unwrap().1, SqlType::Varchar(255));
470        let res = type_identifier(type2.as_bytes());
471        assert_eq!(res.unwrap().1, SqlType::UnsignedBigint(20));
472        let res = type_identifier(type3.as_bytes());
473        assert_eq!(res.unwrap().1, SqlType::Bigint(20));
474        let res = type_identifier(type2.as_bytes());
475        assert_eq!(res.unwrap().1, SqlType::UnsignedBigint(20));
476    }
477
478    #[test]
479    fn field_spec() {
480        // N.B. trailing comma here because field_specification_list! doesn't handle the eof case
481        // because it is never validly the end of a query
482        let qstring = "id bigint(20), name varchar(255),";
483
484        let res = field_specification_list(qstring.as_bytes());
485        assert_eq!(
486            res.unwrap().1,
487            vec![
488                ColumnSpecification::new(Column::from("id"), SqlType::Bigint(20)),
489                ColumnSpecification::new(Column::from("name"), SqlType::Varchar(255)),
490            ]
491        );
492    }
493
494    #[test]
495    fn simple_create() {
496        let qstring = "CREATE TABLE users (id bigint(20), name varchar(255), email varchar(255));";
497
498        let res = creation(qstring.as_bytes());
499        assert_eq!(
500            res.unwrap().1,
501            CreateTableStatement {
502                table: Table::from("users"),
503                fields: vec![
504                    ColumnSpecification::new(Column::from("users.id"), SqlType::Bigint(20)),
505                    ColumnSpecification::new(Column::from("users.name"), SqlType::Varchar(255)),
506                    ColumnSpecification::new(Column::from("users.email"), SqlType::Varchar(255)),
507                ],
508                ..Default::default()
509            }
510        );
511    }
512
513    #[test]
514    fn create_without_space_after_tablename() {
515        let qstring = "CREATE TABLE t(x integer);";
516        let res = creation(qstring.as_bytes());
517        assert_eq!(
518            res.unwrap().1,
519            CreateTableStatement {
520                table: Table::from("t"),
521                fields: vec![ColumnSpecification::new(
522                    Column::from("t.x"),
523                    SqlType::Int(32)
524                ),],
525                ..Default::default()
526            }
527        );
528    }
529
530    #[test]
531    fn mediawiki_create() {
532        let qstring = "CREATE TABLE user_newtalk (  user_id int(5) NOT NULL default '0',  user_ip \
533                       varchar(40) NOT NULL default '') TYPE=MyISAM;";
534        let res = creation(qstring.as_bytes());
535        assert_eq!(
536            res.unwrap().1,
537            CreateTableStatement {
538                table: Table::from("user_newtalk"),
539                fields: vec![
540                    ColumnSpecification::with_constraints(
541                        Column::from("user_newtalk.user_id"),
542                        SqlType::Int(5),
543                        vec![
544                            ColumnConstraint::NotNull,
545                            ColumnConstraint::DefaultValue(Literal::String(String::from("0"))),
546                        ],
547                    ),
548                    ColumnSpecification::with_constraints(
549                        Column::from("user_newtalk.user_ip"),
550                        SqlType::Varchar(40),
551                        vec![
552                            ColumnConstraint::NotNull,
553                            ColumnConstraint::DefaultValue(Literal::String(String::from(""))),
554                        ],
555                    ),
556                ],
557                ..Default::default()
558            }
559        );
560    }
561
562    #[test]
563    fn mediawiki_create2() {
564        let qstring = "CREATE TABLE `user` (
565                        user_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
566                        user_name varchar(255) binary NOT NULL default '',
567                        user_real_name varchar(255) binary NOT NULL default '',
568                        user_password tinyblob NOT NULL,
569                        user_newpassword tinyblob NOT NULL,
570                        user_newpass_time binary(14),
571                        user_email tinytext NOT NULL,
572                        user_touched binary(14) NOT NULL default '',
573                        user_token binary(32) NOT NULL default '',
574                        user_email_authenticated binary(14),
575                        user_email_token binary(32),
576                        user_email_token_expires binary(14),
577                        user_registration binary(14),
578                        user_editcount int,
579                        user_password_expires varbinary(14) DEFAULT NULL
580                       ) ENGINE=, DEFAULT CHARSET=utf8";
581        creation(qstring.as_bytes()).unwrap();
582    }
583
584    #[test]
585    fn mediawiki_create3() {
586        let qstring = "CREATE TABLE `interwiki` (
587 iw_prefix varchar(32) NOT NULL,
588 iw_url blob NOT NULL,
589 iw_api blob NOT NULL,
590 iw_wikiid varchar(64) NOT NULL,
591 iw_local bool NOT NULL,
592 iw_trans tinyint NOT NULL default 0
593 ) ENGINE=, DEFAULT CHARSET=utf8";
594        creation(qstring.as_bytes()).unwrap();
595    }
596
597    #[test]
598    fn mediawiki_externallinks() {
599        let qstring = "CREATE TABLE `externallinks` (
600          `el_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
601          `el_from` int(8) unsigned NOT NULL DEFAULT '0',
602          `el_from_namespace` int(11) NOT NULL DEFAULT '0',
603          `el_to` blob NOT NULL,
604          `el_index` blob NOT NULL,
605          `el_index_60` varbinary(60) NOT NULL,
606          PRIMARY KEY (`el_id`),
607          KEY `el_from` (`el_from`,`el_to`(40)),
608          KEY `el_to` (`el_to`(60),`el_from`),
609          KEY `el_index` (`el_index`(60)),
610          KEY `el_backlinks_to` (`el_from_namespace`,`el_to`(60),`el_from`),
611          KEY `el_index_60` (`el_index_60`,`el_id`),
612          KEY `el_from_index_60` (`el_from`,`el_index_60`,`el_id`)
613        )";
614        creation(qstring.as_bytes()).unwrap();
615    }
616
617    #[test]
618    fn keys() {
619        // simple primary key
620        let qstring = "CREATE TABLE users (id bigint(20), name varchar(255), email varchar(255), \
621                       PRIMARY KEY (id));";
622
623        let res = creation(qstring.as_bytes());
624        assert_eq!(
625            res.unwrap().1,
626            CreateTableStatement {
627                table: Table::from("users"),
628                fields: vec![
629                    ColumnSpecification::new(Column::from("users.id"), SqlType::Bigint(20)),
630                    ColumnSpecification::new(Column::from("users.name"), SqlType::Varchar(255)),
631                    ColumnSpecification::new(Column::from("users.email"), SqlType::Varchar(255)),
632                ],
633                keys: Some(vec![TableKey::PrimaryKey(vec![Column::from("users.id")])]),
634                ..Default::default()
635            }
636        );
637
638        // named unique key
639        let qstring = "CREATE TABLE users (id bigint(20), name varchar(255), email varchar(255), \
640                       UNIQUE KEY id_k (id));";
641
642        let res = creation(qstring.as_bytes());
643        assert_eq!(
644            res.unwrap().1,
645            CreateTableStatement {
646                table: Table::from("users"),
647                fields: vec![
648                    ColumnSpecification::new(Column::from("users.id"), SqlType::Bigint(20)),
649                    ColumnSpecification::new(Column::from("users.name"), SqlType::Varchar(255)),
650                    ColumnSpecification::new(Column::from("users.email"), SqlType::Varchar(255)),
651                ],
652                keys: Some(vec![TableKey::UniqueKey(
653                    Some(String::from("id_k")),
654                    vec![Column::from("users.id")],
655                ),]),
656                ..Default::default()
657            }
658        );
659    }
660
661    #[test]
662    fn django_create() {
663        let qstring = "CREATE TABLE `django_admin_log` (
664                       `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
665                       `action_time` datetime NOT NULL,
666                       `user_id` integer NOT NULL,
667                       `content_type_id` integer,
668                       `object_id` longtext,
669                       `object_repr` varchar(200) NOT NULL,
670                       `action_flag` smallint UNSIGNED NOT NULL,
671                       `change_message` longtext NOT NULL);";
672        let res = creation(qstring.as_bytes());
673        assert_eq!(
674            res.unwrap().1,
675            CreateTableStatement {
676                table: Table::from("django_admin_log"),
677                fields: vec![
678                    ColumnSpecification::with_constraints(
679                        Column::from("django_admin_log.id"),
680                        SqlType::Int(32),
681                        vec![
682                            ColumnConstraint::AutoIncrement,
683                            ColumnConstraint::NotNull,
684                            ColumnConstraint::PrimaryKey,
685                        ],
686                    ),
687                    ColumnSpecification::with_constraints(
688                        Column::from("django_admin_log.action_time"),
689                        SqlType::DateTime(0),
690                        vec![ColumnConstraint::NotNull],
691                    ),
692                    ColumnSpecification::with_constraints(
693                        Column::from("django_admin_log.user_id"),
694                        SqlType::Int(32),
695                        vec![ColumnConstraint::NotNull],
696                    ),
697                    ColumnSpecification::new(
698                        Column::from("django_admin_log.content_type_id"),
699                        SqlType::Int(32),
700                    ),
701                    ColumnSpecification::new(
702                        Column::from("django_admin_log.object_id"),
703                        SqlType::Longtext,
704                    ),
705                    ColumnSpecification::with_constraints(
706                        Column::from("django_admin_log.object_repr"),
707                        SqlType::Varchar(200),
708                        vec![ColumnConstraint::NotNull],
709                    ),
710                    ColumnSpecification::with_constraints(
711                        Column::from("django_admin_log.action_flag"),
712                        SqlType::UnsignedInt(32),
713                        vec![ColumnConstraint::NotNull],
714                    ),
715                    ColumnSpecification::with_constraints(
716                        Column::from("django_admin_log.change_message"),
717                        SqlType::Longtext,
718                        vec![ColumnConstraint::NotNull],
719                    ),
720                ],
721                ..Default::default()
722            }
723        );
724
725        let qstring = "CREATE TABLE `auth_group` (
726                       `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
727                       `name` varchar(80) NOT NULL UNIQUE)";
728        let res = creation(qstring.as_bytes());
729        assert_eq!(
730            res.unwrap().1,
731            CreateTableStatement {
732                table: Table::from("auth_group"),
733                fields: vec![
734                    ColumnSpecification::with_constraints(
735                        Column::from("auth_group.id"),
736                        SqlType::Int(32),
737                        vec![
738                            ColumnConstraint::AutoIncrement,
739                            ColumnConstraint::NotNull,
740                            ColumnConstraint::PrimaryKey,
741                        ],
742                    ),
743                    ColumnSpecification::with_constraints(
744                        Column::from("auth_group.name"),
745                        SqlType::Varchar(80),
746                        vec![ColumnConstraint::NotNull, ColumnConstraint::Unique],
747                    ),
748                ],
749                ..Default::default()
750            }
751        );
752    }
753
754    #[test]
755    fn format_create() {
756        let qstring = "CREATE TABLE `auth_group` (
757                       `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
758                       `name` varchar(80) NOT NULL UNIQUE)";
759        // TODO(malte): INTEGER isn't quite reflected right here, perhaps
760        let expected = "CREATE TABLE auth_group (\
761                        id INT(32) AUTO_INCREMENT NOT NULL PRIMARY KEY, \
762                        name VARCHAR(80) NOT NULL UNIQUE)";
763        let res = creation(qstring.as_bytes());
764        assert_eq!(format!("{}", res.unwrap().1), expected);
765    }
766
767    #[test]
768    fn simple_create_view() {
769        use common::{FieldDefinitionExpression, Operator};
770        use condition::{ConditionBase, ConditionExpression, ConditionTree};
771
772        let qstring = "CREATE VIEW v AS SELECT * FROM users WHERE username = \"bob\";";
773
774        let res = view_creation(qstring.as_bytes());
775        assert_eq!(
776            res.unwrap().1,
777            CreateViewStatement {
778                name: String::from("v"),
779                fields: vec![],
780                definition: Box::new(SelectSpecification::Simple(SelectStatement {
781                    tables: vec![Table::from("users")],
782                    fields: vec![FieldDefinitionExpression::All],
783                    where_clause: Some(ConditionExpression::ComparisonOp(ConditionTree {
784                        left: Box::new(ConditionExpression::Base(ConditionBase::Field(
785                            "username".into()
786                        ))),
787                        right: Box::new(ConditionExpression::Base(ConditionBase::Literal(
788                            Literal::String("bob".into())
789                        ))),
790                        operator: Operator::Equal,
791                    })),
792                    ..Default::default()
793                })),
794            }
795        );
796    }
797
798    #[test]
799    fn compound_create_view() {
800        use common::FieldDefinitionExpression;
801        use compound_select::{CompoundSelectOperator, CompoundSelectStatement};
802
803        let qstring = "CREATE VIEW v AS SELECT * FROM users UNION SELECT * FROM old_users;";
804
805        let res = view_creation(qstring.as_bytes());
806        assert_eq!(
807            res.unwrap().1,
808            CreateViewStatement {
809                name: String::from("v"),
810                fields: vec![],
811                definition: Box::new(SelectSpecification::Compound(CompoundSelectStatement {
812                    selects: vec![
813                        (
814                            None,
815                            SelectStatement {
816                                tables: vec![Table::from("users")],
817                                fields: vec![FieldDefinitionExpression::All],
818                                ..Default::default()
819                            },
820                        ),
821                        (
822                            Some(CompoundSelectOperator::DistinctUnion),
823                            SelectStatement {
824                                tables: vec![Table::from("old_users")],
825                                fields: vec![FieldDefinitionExpression::All],
826                                ..Default::default()
827                            },
828                        ),
829                    ],
830                    order: None,
831                    limit: None,
832                })),
833            }
834        );
835    }
836
837    #[test]
838    fn format_create_view() {
839        let qstring = "CREATE VIEW `v` AS SELECT * FROM `t`;";
840        let expected = "CREATE VIEW v AS SELECT * FROM t";
841        let res = view_creation(qstring.as_bytes());
842        assert_eq!(format!("{}", res.unwrap().1), expected);
843    }
844
845    #[test]
846    fn lobsters_indexes() {
847        let qstring = "CREATE TABLE `comments` (
848            `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
849            `hat_id` int,
850            fulltext INDEX `index_comments_on_comment`  (`comment`),
851            INDEX `confidence_idx`  (`confidence`),
852            UNIQUE INDEX `short_id`  (`short_id`),
853            INDEX `story_id_short_id`  (`story_id`, `short_id`),
854            INDEX `thread_id`  (`thread_id`),
855            INDEX `index_comments_on_user_id`  (`user_id`))
856            ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
857        let res = creation(qstring.as_bytes());
858        assert_eq!(
859            res.unwrap().1,
860            CreateTableStatement {
861                table: Table::from("comments"),
862                fields: vec![
863                    ColumnSpecification::with_constraints(
864                        Column::from("comments.id"),
865                        SqlType::UnsignedInt(32),
866                        vec![
867                            ColumnConstraint::NotNull,
868                            ColumnConstraint::AutoIncrement,
869                            ColumnConstraint::PrimaryKey,
870                        ],
871                    ),
872                    ColumnSpecification::new(Column::from("comments.hat_id"), SqlType::Int(32),),
873                ],
874                keys: Some(vec![
875                    TableKey::FulltextKey(
876                        Some("index_comments_on_comment".into()),
877                        vec![Column::from("comments.comment")]
878                    ),
879                    TableKey::Key(
880                        "confidence_idx".into(),
881                        vec![Column::from("comments.confidence")]
882                    ),
883                    TableKey::UniqueKey(
884                        Some("short_id".into()),
885                        vec![Column::from("comments.short_id")]
886                    ),
887                    TableKey::Key(
888                        "story_id_short_id".into(),
889                        vec![
890                            Column::from("comments.story_id"),
891                            Column::from("comments.short_id")
892                        ]
893                    ),
894                    TableKey::Key("thread_id".into(), vec![Column::from("comments.thread_id")]),
895                    TableKey::Key(
896                        "index_comments_on_user_id".into(),
897                        vec![Column::from("comments.user_id")]
898                    ),
899                ]),
900            }
901        );
902    }
903}
nom_sql/create.rs

nom_sql/
create.rs
