pub trait DatabaseOperations {
fn quote_name(&self, name: &str) -> String;
fn max_name_length(&self) -> usize;
fn pk_default_value(&self) -> &str;
fn no_limit_value(&self) -> Option<&str>;
fn last_insert_id(&self, table_name: &str, pk_name: &str) -> String;
fn sql_flush(&self, tables: &[&str]) -> Vec<String>;
fn sequence_reset_sql(&self, table_name: &str) -> Option<String>;
fn date_extract_sql(&self, lookup_type: &str, field_name: &str) -> String;
fn date_trunc_sql(&self, lookup_type: &str, field_name: &str) -> String;
}
#[derive(Debug, Default, Clone, Copy)]
pub struct SqliteOperations;
#[derive(Debug, Default, Clone, Copy)]
pub struct PostgresOperations;
#[derive(Debug, Default, Clone, Copy)]
pub struct MysqlOperations;
fn escape_double_quotes(name: &str) -> String {
name.replace('"', "\"\"")
}
fn escape_backticks(name: &str) -> String {
name.replace('`', "``")
}
fn escape_single_quotes(value: &str) -> String {
value.replace('\'', "''")
}
fn sqlite_extract_format(lookup_type: &str) -> &str {
match lookup_type {
"year" => "%Y",
"month" => "%m",
"day" => "%d",
"week" => "%W",
"weekday" => "%w",
"hour" => "%H",
"minute" => "%M",
"second" => "%S",
_ => "%Y",
}
}
impl DatabaseOperations for SqliteOperations {
fn quote_name(&self, name: &str) -> String {
format!("\"{}\"", escape_double_quotes(name))
}
fn max_name_length(&self) -> usize {
255
}
fn pk_default_value(&self) -> &str {
"NULL"
}
fn no_limit_value(&self) -> Option<&str> {
None
}
fn last_insert_id(&self, _table_name: &str, _pk_name: &str) -> String {
"SELECT last_insert_rowid()".to_string()
}
fn sql_flush(&self, tables: &[&str]) -> Vec<String> {
if tables.is_empty() {
return Vec::new();
}
let mut statements = tables
.iter()
.map(|table| format!("DELETE FROM {}", self.quote_name(table)))
.collect::<Vec<_>>();
let names = tables
.iter()
.map(|table| format!("'{}'", escape_single_quotes(table)))
.collect::<Vec<_>>()
.join(", ");
statements.push(format!(
"DELETE FROM \"sqlite_sequence\" WHERE \"name\" IN ({names})"
));
statements
}
fn sequence_reset_sql(&self, table_name: &str) -> Option<String> {
Some(format!(
"DELETE FROM \"sqlite_sequence\" WHERE \"name\" = '{}'",
escape_single_quotes(table_name)
))
}
fn date_extract_sql(&self, lookup_type: &str, field_name: &str) -> String {
format!(
"CAST(STRFTIME('{}', {field_name}) AS INTEGER)",
sqlite_extract_format(lookup_type)
)
}
fn date_trunc_sql(&self, lookup_type: &str, field_name: &str) -> String {
match lookup_type {
"year" => format!("DATE({field_name}, 'start of year')"),
"month" => format!("DATE({field_name}, 'start of month')"),
"day" => format!("DATE({field_name})"),
"hour" => format!("STRFTIME('%Y-%m-%d %H:00:00', {field_name})"),
"minute" => format!("STRFTIME('%Y-%m-%d %H:%M:00', {field_name})"),
"second" => format!("DATETIME({field_name})"),
_ => format!("DATETIME({field_name})"),
}
}
}
impl DatabaseOperations for PostgresOperations {
fn quote_name(&self, name: &str) -> String {
format!("\"{}\"", escape_double_quotes(name))
}
fn max_name_length(&self) -> usize {
63
}
fn pk_default_value(&self) -> &str {
"DEFAULT"
}
fn no_limit_value(&self) -> Option<&str> {
None
}
fn last_insert_id(&self, table_name: &str, pk_name: &str) -> String {
format!(
"SELECT currval(pg_get_serial_sequence('{}', '{}'))",
escape_single_quotes(table_name),
escape_single_quotes(pk_name)
)
}
fn sql_flush(&self, tables: &[&str]) -> Vec<String> {
if tables.is_empty() {
return Vec::new();
}
vec![format!(
"TRUNCATE {} RESTART IDENTITY CASCADE",
tables
.iter()
.map(|table| self.quote_name(table))
.collect::<Vec<_>>()
.join(", ")
)]
}
fn sequence_reset_sql(&self, table_name: &str) -> Option<String> {
Some(format!(
"ALTER SEQUENCE \"{}_id_seq\" RESTART WITH 1",
escape_double_quotes(table_name)
))
}
fn date_extract_sql(&self, lookup_type: &str, field_name: &str) -> String {
format!("EXTRACT({} FROM {field_name})", lookup_type.to_uppercase())
}
fn date_trunc_sql(&self, lookup_type: &str, field_name: &str) -> String {
format!("DATE_TRUNC('{}', {field_name})", lookup_type.to_lowercase())
}
}
impl DatabaseOperations for MysqlOperations {
fn quote_name(&self, name: &str) -> String {
format!("`{}`", escape_backticks(name))
}
fn max_name_length(&self) -> usize {
64
}
fn pk_default_value(&self) -> &str {
"NULL"
}
fn no_limit_value(&self) -> Option<&str> {
Some("18446744073709551615")
}
fn last_insert_id(&self, _table_name: &str, _pk_name: &str) -> String {
"SELECT LAST_INSERT_ID()".to_string()
}
fn sql_flush(&self, tables: &[&str]) -> Vec<String> {
if tables.is_empty() {
return Vec::new();
}
let mut statements = vec!["SET FOREIGN_KEY_CHECKS = 0".to_string()];
statements.extend(
tables
.iter()
.map(|table| format!("TRUNCATE {}", self.quote_name(table))),
);
statements.push("SET FOREIGN_KEY_CHECKS = 1".to_string());
statements
}
fn sequence_reset_sql(&self, table_name: &str) -> Option<String> {
Some(format!(
"ALTER TABLE {} AUTO_INCREMENT = 1",
self.quote_name(table_name)
))
}
fn date_extract_sql(&self, lookup_type: &str, field_name: &str) -> String {
format!("EXTRACT({} FROM {field_name})", lookup_type.to_uppercase())
}
fn date_trunc_sql(&self, lookup_type: &str, field_name: &str) -> String {
match lookup_type {
"year" => format!("DATE_FORMAT({field_name}, '%Y-01-01 00:00:00')"),
"month" => format!("DATE_FORMAT({field_name}, '%Y-%m-01 00:00:00')"),
"day" => format!("DATE({field_name})"),
"hour" => format!("DATE_FORMAT({field_name}, '%Y-%m-%d %H:00:00')"),
"minute" => format!("DATE_FORMAT({field_name}, '%Y-%m-%d %H:%i:00')"),
"second" => format!("DATE_FORMAT({field_name}, '%Y-%m-%d %H:%i:%s')"),
_ => format!("DATE({field_name})"),
}
}
}
#[cfg(test)]
mod tests {
use super::{DatabaseOperations, MysqlOperations, PostgresOperations, SqliteOperations};
#[test]
fn sqlite_quote_name_escapes_identifiers() {
let operations = SqliteOperations;
assert_eq!(operations.quote_name("widgets"), "\"widgets\"");
assert_eq!(
operations.quote_name("widget\"table"),
"\"widget\"\"table\""
);
}
#[test]
fn postgres_quote_name_escapes_identifiers() {
let operations = PostgresOperations;
assert_eq!(operations.quote_name("widgets"), "\"widgets\"");
assert_eq!(
operations.quote_name("widget\"table"),
"\"widget\"\"table\""
);
}
#[test]
fn mysql_quote_name_escapes_identifiers() {
let operations = MysqlOperations;
assert_eq!(operations.quote_name("widgets"), "`widgets`");
assert_eq!(operations.quote_name("widget`table"), "`widget``table`");
}
#[test]
fn max_name_lengths_match_backend_limits() {
assert_eq!(SqliteOperations.max_name_length(), 255);
assert_eq!(PostgresOperations.max_name_length(), 63);
assert_eq!(MysqlOperations.max_name_length(), 64);
}
#[test]
fn sqlite_sql_flush_deletes_rows_and_resets_sequences() {
let operations = SqliteOperations;
let sql = operations.sql_flush(&["widgets", "users"]);
assert_eq!(sql.len(), 3);
assert_eq!(sql[0], "DELETE FROM \"widgets\"");
assert_eq!(sql[1], "DELETE FROM \"users\"");
assert_eq!(
sql[2],
"DELETE FROM \"sqlite_sequence\" WHERE \"name\" IN ('widgets', 'users')"
);
}
#[test]
fn postgres_sql_flush_uses_single_truncate_statement() {
let operations = PostgresOperations;
let sql = operations.sql_flush(&["widgets", "users"]);
assert_eq!(
sql,
vec!["TRUNCATE \"widgets\", \"users\" RESTART IDENTITY CASCADE".to_string()]
);
}
#[test]
fn mysql_sql_flush_wraps_foreign_key_checks() {
let operations = MysqlOperations;
let sql = operations.sql_flush(&["widgets"]);
assert_eq!(sql.len(), 3);
assert_eq!(sql[0], "SET FOREIGN_KEY_CHECKS = 0");
assert_eq!(sql[1], "TRUNCATE `widgets`");
assert_eq!(sql[2], "SET FOREIGN_KEY_CHECKS = 1");
}
#[test]
fn last_insert_id_sql_matches_backend_conventions() {
assert_eq!(
SqliteOperations.last_insert_id("widgets", "id"),
"SELECT last_insert_rowid()"
);
assert_eq!(
PostgresOperations.last_insert_id("widgets", "id"),
"SELECT currval(pg_get_serial_sequence('widgets', 'id'))"
);
assert_eq!(
MysqlOperations.last_insert_id("widgets", "id"),
"SELECT LAST_INSERT_ID()"
);
}
#[test]
fn sequence_reset_sql_matches_backend_capabilities() {
assert_eq!(
SqliteOperations.sequence_reset_sql("widgets").as_deref(),
Some("DELETE FROM \"sqlite_sequence\" WHERE \"name\" = 'widgets'")
);
assert_eq!(
PostgresOperations.sequence_reset_sql("widgets").as_deref(),
Some("ALTER SEQUENCE \"widgets_id_seq\" RESTART WITH 1")
);
assert_eq!(
MysqlOperations.sequence_reset_sql("widgets").as_deref(),
Some("ALTER TABLE `widgets` AUTO_INCREMENT = 1")
);
}
#[test]
fn date_sql_helpers_follow_backend_syntax() {
assert_eq!(
SqliteOperations.date_extract_sql("year", "created_at"),
"CAST(STRFTIME('%Y', created_at) AS INTEGER)"
);
assert_eq!(
PostgresOperations.date_trunc_sql("month", "created_at"),
"DATE_TRUNC('month', created_at)"
);
assert_eq!(
MysqlOperations.date_trunc_sql("hour", "created_at"),
"DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00')"
);
}
#[test]
fn backend_defaults_are_exposed_for_primary_keys_and_limits() {
assert_eq!(SqliteOperations.pk_default_value(), "NULL");
assert_eq!(PostgresOperations.pk_default_value(), "DEFAULT");
assert_eq!(MysqlOperations.pk_default_value(), "NULL");
assert_eq!(SqliteOperations.no_limit_value(), None);
assert_eq!(PostgresOperations.no_limit_value(), None);
assert_eq!(
MysqlOperations.no_limit_value(),
Some("18446744073709551615")
);
}
}