use crate::Database;
#[derive(Debug, Default, Clone, PartialEq)]
pub struct ImportReport {
pub executed: usize,
pub skipped: Vec<String>,
pub errors: Vec<String>,
}
impl Database {
pub fn import_sql(&mut self, dump: &str) -> ImportReport {
let mut report = ImportReport::default();
let statements = match split_statements(dump) {
Ok(statements) => statements,
Err(error) => {
report.errors.push(error);
return report;
}
};
for raw in statements {
let stmt = raw.trim();
if stmt.is_empty() {
continue;
}
match rewrite_statement(stmt) {
Rewrite::Run(sql) => match self.query(&sql) {
Ok(_) => report.executed += 1,
Err(e) => report.errors.push(format!("{e}: {}", preview(stmt))),
},
Rewrite::Skip(reason) => {
report.skipped.push(format!("{reason}: {}", preview(stmt)))
}
}
}
report
}
}
enum Rewrite {
Run(String),
Skip(String),
}
fn preview(s: &str) -> String {
let one: String = s.split_whitespace().collect::<Vec<_>>().join(" ");
if one.chars().count() > 60 {
format!("{}...", one.chars().take(60).collect::<String>())
} else {
one
}
}
fn rewrite_statement(stmt: &str) -> Rewrite {
if is_trigger(stmt) {
return Rewrite::Skip("triggers not supported".to_string());
}
let upper = stmt.to_ascii_uppercase();
for (kw, reason) in [
("PRAGMA", "pragma not supported"),
("BEGIN", "transactions not supported"),
("COMMIT", "transactions not supported"),
("ROLLBACK", "transactions not supported"),
("SAVEPOINT", "transactions not supported"),
("RELEASE", "transactions not supported"),
("CREATE TRIGGER", "triggers not supported"),
("CREATE VIEW", "views not supported"),
("CREATE INDEX", "dump index skipped"),
("CREATE UNIQUE INDEX", "dump index skipped"),
("ALTER", "ALTER not supported"),
("ATTACH", "ATTACH not supported"),
("DETACH", "DETACH not supported"),
("ANALYZE", "ANALYZE not supported"),
] {
if upper.starts_with(kw) {
return Rewrite::Skip(reason.to_string());
}
}
if upper == "END" {
return Rewrite::Skip("transactions not supported".to_string());
}
if upper.starts_with("CREATE TABLE") {
return match rewrite_create_table(stmt) {
Some(sql) => Rewrite::Run(sql),
None => Rewrite::Skip("could not parse CREATE TABLE".to_string()),
};
}
Rewrite::Run(stmt.to_string())
}
fn rewrite_create_table(stmt: &str) -> Option<String> {
let open = first_unquoted_paren(stmt)?;
let name = create_table_name(&stmt[..open])?;
let close = matching_paren(stmt, open)?;
let body = &stmt[open + 1..close];
let cols = column_names(body);
if cols.is_empty() {
return None;
}
Some(format!("CREATE TABLE {} ({})", name, cols.join(", ")))
}
fn column_names(body: &str) -> Vec<String> {
let mut cols = Vec::new();
for item in split_top_level_commas(body) {
let item = item.trim();
if let Some((name, quoted, _)) = first_identifier(item) {
let keyword = name.to_ascii_uppercase();
if !quoted
&& matches!(
keyword.as_str(),
"PRIMARY" | "FOREIGN" | "UNIQUE" | "CHECK" | "CONSTRAINT" | "KEY"
)
{
continue; }
cols.push(name);
}
}
cols
}
fn first_identifier(s: &str) -> Option<(String, bool, usize)> {
let trimmed = s.trim_start();
let leading = s.len() - trimmed.len();
let mut chars = trimmed.char_indices().peekable();
let (_, first) = chars.next()?;
if let Some(closing) = quote_closing(first) {
let mut has_content = false;
while let Some((index, ch)) = chars.next() {
if ch == closing {
if chars.peek().is_some_and(|(_, next)| *next == closing) {
chars.next();
has_content = true;
continue;
}
if !has_content {
return None;
}
let end = index + ch.len_utf8();
return Some((trimmed[..end].to_owned(), true, leading + end));
}
has_content = true;
}
None
} else {
if !(first.is_alphabetic() || first == '_') {
return None;
}
let end = trimmed
.char_indices()
.take_while(|(_, ch)| ch.is_alphanumeric() || *ch == '_')
.map(|(index, ch)| index + ch.len_utf8())
.last()?;
Some((trimmed[..end].to_owned(), false, leading + end))
}
}
fn create_table_name(head: &str) -> Option<String> {
let mut rest = strip_keyword(head, "CREATE")?;
rest = strip_keyword(rest, "TABLE")?;
if starts_keyword(rest, "IF") {
rest = strip_keyword(rest, "IF")?;
rest = strip_keyword(rest, "NOT")?;
rest = strip_keyword(rest, "EXISTS")?;
}
let (name, _, consumed) = first_identifier(rest)?;
if !rest[consumed..].trim().is_empty() {
return None;
}
Some(name)
}
fn starts_keyword(input: &str, keyword: &str) -> bool {
strip_keyword(input, keyword).is_some()
}
fn strip_keyword<'a>(input: &'a str, keyword: &str) -> Option<&'a str> {
let trimmed = input.trim_start();
let prefix = trimmed.get(..keyword.len())?;
if !prefix.eq_ignore_ascii_case(keyword) {
return None;
}
let rest = &trimmed[keyword.len()..];
if rest
.chars()
.next()
.is_some_and(|ch| ch.is_alphanumeric() || ch == '_')
{
return None;
}
Some(rest)
}
fn quote_closing(opening: char) -> Option<char> {
match opening {
'\'' => Some('\''),
'"' => Some('"'),
'`' => Some('`'),
'[' => Some(']'),
_ => None,
}
}
fn first_unquoted_paren(sql: &str) -> Option<usize> {
let mut quoted = None;
let mut chars = sql.char_indices().peekable();
while let Some((index, ch)) = chars.next() {
if let Some(closing) = quoted {
if ch == closing {
if chars.peek().is_some_and(|(_, next)| *next == closing) {
chars.next();
} else {
quoted = None;
}
}
} else if let Some(closing) = quote_closing(ch) {
quoted = Some(closing);
} else if ch == '(' {
return Some(index);
}
}
None
}
fn is_trigger(sql: &str) -> bool {
let Some(rest) = strip_keyword(sql, "CREATE") else {
return false;
};
let rest = strip_keyword(rest, "TEMPORARY")
.or_else(|| strip_keyword(rest, "TEMP"))
.unwrap_or(rest);
starts_keyword(rest, "TRIGGER")
}
fn split_statements(sql: &str) -> Result<Vec<String>, String> {
let mut out = Vec::new();
let mut cur = String::new();
let mut quoted = None;
let mut trigger = false;
let mut body_started = false;
let mut depth = 0usize;
let mut chars = sql.chars().peekable();
while let Some(c) = chars.next() {
if let Some(closing) = quoted {
cur.push(c);
if c == closing {
if chars.peek() == Some(&closing) {
cur.push(closing);
chars.next();
} else {
quoted = None;
}
}
} else if c == '-' && chars.peek() == Some(&'-') {
chars.next();
for next in chars.by_ref() {
if next == '\n' {
break;
}
}
cur.push(' ');
} else if c == '/' && chars.peek() == Some(&'*') {
chars.next();
let mut closed = false;
while let Some(next) = chars.next() {
if next == '*' && chars.peek() == Some(&'/') {
chars.next();
closed = true;
break;
}
}
if !closed {
return Err("unterminated SQL comment; no statements imported".into());
}
cur.push(' ');
} else if let Some(closing) = quote_closing(c) {
quoted = Some(closing);
cur.push(c);
} else if c.is_alphabetic() || c == '_' {
let mut word = String::from(c);
while chars
.peek()
.is_some_and(|ch| ch.is_alphanumeric() || *ch == '_')
{
word.push(chars.next().expect("peeked"));
}
cur.push_str(&word);
trigger |= is_trigger(&cur);
if trigger {
match word.to_ascii_uppercase().as_str() {
"BEGIN" => {
body_started = true;
depth += 1;
}
"CASE" if body_started => depth += 1,
"END" if depth > 0 => depth -= 1,
_ => (),
}
}
} else if c == ';' {
if trigger && (!body_started || depth > 0) {
cur.push(c);
} else {
out.push(std::mem::take(&mut cur));
trigger = false;
body_started = false;
}
} else {
cur.push(c);
}
}
if quoted.is_some() || (trigger && (!body_started || depth > 0)) {
return Err("unterminated SQL quote or trigger; no statements imported".into());
}
if !cur.trim().is_empty() {
out.push(cur);
}
Ok(out)
}
fn split_top_level_commas(body: &str) -> Vec<String> {
let mut items = Vec::new();
let mut cur = String::new();
let mut depth = 0i32;
let mut quoted = None;
let mut chars = body.chars().peekable();
while let Some(c) = chars.next() {
if let Some(closing) = quoted {
cur.push(c);
if c == closing {
if chars.peek() == Some(&closing) {
cur.push(closing);
chars.next();
} else {
quoted = None;
}
}
} else {
match c {
ch if quote_closing(ch).is_some() => {
quoted = quote_closing(ch);
cur.push(c);
}
'(' => {
depth += 1;
cur.push(c);
}
')' => {
depth -= 1;
cur.push(c);
}
',' if depth == 0 => items.push(std::mem::take(&mut cur)),
_ => cur.push(c),
}
}
}
if !cur.trim().is_empty() {
items.push(cur);
}
items
}
fn matching_paren(s: &str, open: usize) -> Option<usize> {
let mut depth = 0i32;
let mut quoted = None;
let mut chars = s
.char_indices()
.skip_while(|(index, _)| *index < open)
.peekable();
while let Some((i, c)) = chars.next() {
if let Some(closing) = quoted {
if c == closing {
if chars.peek().is_some_and(|(_, next)| *next == closing) {
chars.next();
} else {
quoted = None;
}
}
} else if let Some(closing) = quote_closing(c) {
quoted = Some(closing);
} else if c == '(' {
depth += 1;
} else if c == ')' {
depth -= 1;
if depth == 0 {
return Some(i);
}
}
}
None
}
#[cfg(test)]
mod tests {
use super::*;
use crate::core::value::Value;
#[test]
fn imports_a_sqlite_style_dump() {
let dump = r#"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "users" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL DEFAULT 'anon',
"score" REAL,
UNIQUE("name")
);
INSERT INTO "users" VALUES(1,'alice',9.5);
INSERT INTO "users" VALUES(2,'o''brien',3.0);
CREATE INDEX idx_name ON users(name);
CREATE TRIGGER t AFTER INSERT ON users BEGIN SELECT 1; END;
COMMIT;
"#;
let mut db = Database::open_memory();
let report = db.import_sql(dump);
assert_eq!(report.executed, 3, "create + 2 inserts: {report:?}");
assert!(report.errors.is_empty(), "{report:?}");
assert!(report.skipped.len() >= 4, "{report:?}");
let rows = db
.query("SELECT id, name FROM users ORDER BY id")
.unwrap()
.rows()
.unwrap()
.to_vec();
assert_eq!(
rows,
vec![
vec![Value::Int(1), Value::Text("alice".into())],
vec![Value::Int(2), Value::Text("o'brien".into())],
]
);
}
}