25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
use std::ops::ControlFlow;
use sqlparser::ast::{Expr, Visitor};
use sqlparser::dialect::OracleDialect;
use sqlparser::parser::Parser;
use sqlparser::tokenizer::{Token, Tokenizer};
use super::dialect::StructureDialect;
use super::util::{qualified_table, quote_ident};
use super::{
OracleCheckConstraint, OracleConstraintChange, OracleConstraintDefinition, TableStructureSqlOptions,
TableStructureSqlResult,
};
use crate::models::connection::DatabaseType;
pub(super) fn build_changes(options: TableStructureSqlOptions) -> TableStructureSqlResult {
let changes = options.oracle_constraint_changes.as_deref().unwrap_or_default();
let reject = |message: &str| TableStructureSqlResult { statements: vec![], warnings: vec![message.into()] };
let Some(database_type @ (DatabaseType::Oracle | DatabaseType::OceanbaseOracle)) = options.database_type else {
return reject("CHECK and UNIQUE changes require Oracle or OceanBase Oracle.");
};
if options.is_gaussdb_m_mode {
return reject("Apply CHECK or UNIQUE changes separately from other table structure drafts.");
}
let mut ordinary_options = options.clone();
ordinary_options.oracle_constraint_changes = None;
let ordinary = super::build_table_structure_change_sql(ordinary_options);
if !ordinary.statements.is_empty() || !ordinary.warnings.is_empty() {
return reject("Apply CHECK or UNIQUE changes separately from other table structure drafts.");
}
let mut names = std::collections::HashSet::new();
for change in changes {
let (original, target) = match (&change.original, &change.target) {
(None, None) => return reject("A constraint change must have an original or target definition."),
(original, target) => (original, target),
};
if database_type == DatabaseType::Oracle {
if let Some(OracleConstraintDefinition::Unique(old)) = original {
let requires_drop = match target {
None => true,
Some(OracleConstraintDefinition::Unique(new)) => {
old.columns != new.columns || old.state.deferrable != new.state.deferrable
}
Some(_) => true,
};
if requires_drop && !matches!(change.index_policy.as_str(), "keep" | "drop") {
return reject("Oracle UNIQUE rebuilds require an explicit KEEP INDEX or DROP INDEX choice.");
}
if old.state.enabled
&& target.as_ref().is_some_and(
|value| matches!(value, OracleConstraintDefinition::Unique(new) if !new.state.enabled),
)
&& !matches!(change.index_policy.as_str(), "keep" | "drop")
{
return reject(
"Disabling an Oracle UNIQUE constraint requires an explicit KEEP INDEX or DROP INDEX choice.",
);
}
}
}
for (is_original, definition) in [(true, original.as_ref()), (false, target.as_ref())] {
let Some(definition) = definition else { continue };
let name = match definition {
OracleConstraintDefinition::Check(check) => &check.name,
OracleConstraintDefinition::Unique(unique) => &unique.name,
};
if name.trim().is_empty() {
return reject("Constraint names must not be empty.");
}
match definition {
OracleConstraintDefinition::Check(check) => {
if let Err(message) = validate_check(check, database_type == DatabaseType::Oracle) {
return reject(message);
}
}
OracleConstraintDefinition::Unique(unique) => {
if let Err(message) = validate_unique(
unique,
database_type,
options.schema.as_deref(),
&options.table_name,
is_original,
&change.index_policy,
) {
return reject(message);
}
}
}
}
if let Some(target) = target.as_ref() {
let name = match target {
OracleConstraintDefinition::Check(check) => &check.name,
OracleConstraintDefinition::Unique(unique) => &unique.name,
};
if !names.insert(name.clone()) {
return reject("Target constraint names must not repeat.");
}
}
}
let dialect = StructureDialect::Oracle;
let table = qualified_table(dialect, options.schema.as_deref(), &options.table_name);
let mut statements = Vec::new();
let mut validated_statements = Vec::new();
for OracleConstraintChange { original, target, index_policy } in changes {
if original == target {
continue;
}
match (original, target) {
(None, Some(OracleConstraintDefinition::Check(check))) => {
statements.push(add_check(&table, check, database_type))
}
(Some(OracleConstraintDefinition::Check(old)), None) => statements.push(drop_constraint(&table, &old.name)),
(Some(OracleConstraintDefinition::Check(old)), Some(OracleConstraintDefinition::Check(new))) => {
if old.expression != new.expression
|| old.state.deferrable != new.state.deferrable
|| (database_type == DatabaseType::OceanbaseOracle && old.name != new.name)
{
statements.push(drop_constraint(&table, &old.name));
statements.push(add_check(&table, new, database_type));
} else if old.name != new.name && old.state == new.state {
if database_type == DatabaseType::Oracle {
statements.push(format!(
"ALTER TABLE {table} RENAME CONSTRAINT {} TO {};",
quote_ident(dialect, &old.name),
quote_ident(dialect, &new.name)
));
} else {
statements.push(drop_constraint(&table, &old.name));
statements.push(add_check(&table, new, database_type));
}
} else if old.state != new.state {
if old.state.deferrable != new.state.deferrable {
statements.push(drop_constraint(&table, &old.name));
statements.push(add_check(&table, new, database_type));
} else {
let name = quote_ident(dialect, &old.name);
if old.state.enabled != new.state.enabled || old.state.validated != new.state.validated {
let enabled = if new.state.enabled { "ENABLE" } else { "DISABLE" };
let validated = if new.state.validated { "VALIDATE" } else { "NOVALIDATE" };
statements.push(format!("ALTER TABLE {table} {enabled} {validated} CONSTRAINT {name};"));
}
if old.state.rely != new.state.rely {
statements.push(format!(
"ALTER TABLE {table} MODIFY CONSTRAINT {name} {};",
if new.state.rely { "RELY" } else { "NORELY" }
));
}
if old.state.initially_deferred != new.state.initially_deferred {
statements.push(format!(
"ALTER TABLE {table} MODIFY CONSTRAINT {name} INITIALLY {};",
if new.state.initially_deferred { "DEFERRED" } else { "IMMEDIATE" }
));
}
if old.name != new.name {
statements.push(format!(
"ALTER TABLE {table} RENAME CONSTRAINT {} TO {};",
quote_ident(dialect, &old.name),
quote_ident(dialect, &new.name)
));
}
}
}
}
(None, Some(OracleConstraintDefinition::Unique(unique))) => {
if let Some(statement) = add_unique(&table, unique, database_type, index_policy) {
statements.push(statement);
} else {
return reject("A verified supporting index or a collision-free new index name is required.");
}
}
(Some(OracleConstraintDefinition::Unique(old)), None) => {
statements.push(drop_unique(&table, old, database_type, index_policy));
}
(Some(OracleConstraintDefinition::Unique(old)), Some(OracleConstraintDefinition::Unique(new))) => {
if database_type == DatabaseType::Oracle
&& old.columns == new.columns
&& old.name != new.name
&& old.state == new.state
{
statements.push(format!(
"ALTER TABLE {table} RENAME CONSTRAINT {} TO {};",
quote_ident(dialect, &old.name),
quote_ident(dialect, &new.name)
));
} else if old.columns == new.columns && (old.name == new.name || database_type == DatabaseType::Oracle)
{
if old.state != new.state {
if old.state.deferrable != new.state.deferrable {
statements.push(drop_unique(&table, old, database_type, index_policy));
if let Some(statement) = add_unique(&table, new, database_type, index_policy) {
statements.push(statement);
} else {
return reject(
"A verified supporting index or a collision-free new index name is required.",
);
}
} else {
let name = quote_ident(dialect, &old.name);
if database_type == DatabaseType::Oracle
&& !old.state.enabled
&& new.state.enabled
&& old.index.name.is_empty()
{
if let Some(statement) = add_unique(&table, new, database_type, index_policy) {
statements.push(drop_unique(&table, old, database_type, "keep"));
statements.push(statement);
} else {
return reject("Re-enabling a UNIQUE constraint without an index requires a collision-free supporting-index name.");
}
continue;
}
if old.state.enabled != new.state.enabled || old.state.validated != new.state.validated {
let enabled = if new.state.enabled { "ENABLE" } else { "DISABLE" };
let validated = if new.state.validated { "VALIDATE" } else { "NOVALIDATE" };
let index_clause =
if database_type == DatabaseType::Oracle && old.state.enabled && !new.state.enabled
{
match index_policy.as_str() {
"keep" => " KEEP INDEX",
"drop" => " DROP INDEX",
_ => return reject(
"Disabling an Oracle UNIQUE constraint requires KEEP INDEX or DROP INDEX.",
),
}
} else {
""
};
statements.push(format!(
"ALTER TABLE {table} {enabled} {validated} CONSTRAINT {name}{index_clause};"
));
}
if old.state.rely != new.state.rely {
statements.push(format!(
"ALTER TABLE {table} MODIFY CONSTRAINT {name} {};",
if new.state.rely { "RELY" } else { "NORELY" }
));
}
if old.state.initially_deferred != new.state.initially_deferred {
statements.push(format!(
"ALTER TABLE {table} MODIFY CONSTRAINT {name} INITIALLY {};",
if new.state.initially_deferred { "DEFERRED" } else { "IMMEDIATE" }
));
}
if old.name != new.name {
statements.push(format!(
"ALTER TABLE {table} RENAME CONSTRAINT {} TO {};",
quote_ident(dialect, &old.name),
quote_ident(dialect, &new.name)
));
}
}
}
} else {
statements.push(drop_unique(&table, old, database_type, index_policy));
if let Some(statement) = add_unique(&table, new, database_type, index_policy) {
statements.push(statement);
} else {
return reject("A verified supporting index or a collision-free new index name is required.");
}
}
}
_ => return reject("This CHECK/UNIQUE change shape is not supported yet."),
}
}
for statement in statements {
if statement.to_ascii_uppercase().contains(" CHECK (") {
let tokens = match Tokenizer::new(&OracleDialect, &statement).tokenize() {
Ok(tokens) => tokens,
Err(_) => return reject("Generated CHECK DDL failed structural validation."),
};
let check_token = tokens
.iter()
.position(|token| matches!(token, Token::Word(word) if word.value.eq_ignore_ascii_case("CHECK")));
let Some(check_token) = check_token else {
return reject("Generated CHECK DDL failed structural validation.");
};
let open = tokens
.iter()
.enumerate()
.skip(check_token + 1)
.find(|(_, token)| matches!(token, Token::LParen))
.map(|(index, _)| index);
let Some(open) = open else {
return reject("Generated CHECK DDL failed structural validation.");
};
let mut depth = 0usize;
let mut close = None;
for (index, token) in tokens.iter().enumerate().skip(open) {
match token {
Token::LParen => depth += 1,
Token::RParen => {
depth -= 1;
if depth == 0 {
close = Some(index);
break;
}
}
_ => {}
}
}
let Some(close) = close else {
return reject("Generated CHECK DDL failed structural validation.");
};
if close <= open {
return reject("Generated CHECK DDL failed structural validation.");
}
}
validated_statements.push(statement);
}
TableStructureSqlResult { statements: validated_statements, warnings: vec![] }
}
fn validate_unique(
unique: &super::OracleUniqueConstraint,
database_type: DatabaseType,
schema: Option<&str>,
table_name: &str,
is_original: bool,
index_policy: &str,
) -> Result<(), &'static str> {
if unique.columns.is_empty() || unique.columns.iter().any(|column| column.trim().is_empty()) {
return Err("UNIQUE constraints require nonempty ordered columns.");
}
if unique.columns.iter().collect::<std::collections::HashSet<_>>().len() != unique.columns.len() {
return Err("UNIQUE constraint columns must not repeat.");
}
if unique.state.initially_deferred && !unique.state.deferrable {
return Err("Initially deferred UNIQUE constraints must be deferrable.");
}
if database_type == DatabaseType::OceanbaseOracle
&& (!unique.state.enabled
|| !unique.state.validated
|| unique.state.deferrable
|| unique.state.initially_deferred
|| unique.state.rely
|| (is_original && unique.rely_raw != "NULL"))
{
return Err("OceanBase Oracle UNIQUE changes require the verified ENABLED, VALIDATED, NOT DEFERRABLE, IMMEDIATE, RELY NULL state.");
}
if database_type == DatabaseType::OceanbaseOracle && index_policy != "none" {
return Err("OceanBase Oracle manages a UNIQUE constraint and its paired index as one object.");
}
if database_type == DatabaseType::Oracle && !matches!(index_policy, "keep" | "drop" | "none") {
return Err("UNIQUE supporting index policy is not supported.");
}
if index_policy == "drop" && unique.index.sharing != "exclusive" {
return Err("A supporting index cannot be dropped when sharing is known or unknown.");
}
if is_original && unique.index.name.trim().is_empty() {
if !unique.index.metadata_complete || unique.state.enabled {
return Err("An enabled UNIQUE constraint requires a complete supporting-index identity.");
}
} else if !unique.index.name.trim().is_empty() {
let index = &unique.index;
if !index.metadata_complete
|| index.owner.trim().is_empty()
|| index.name.trim().is_empty()
|| index.owner != schema.unwrap_or_default()
|| index.table_owner != schema.unwrap_or_default()
|| index.table_name != table_name
|| index.index_type != "NORMAL"
|| (unique.state.deferrable && index.uniqueness != "NONUNIQUE")
|| !matches!(index.uniqueness.as_str(), "UNIQUE" | "NONUNIQUE")
|| index.status != "VALID"
|| index.partitioned
|| index.visibility != "VISIBLE"
|| match database_type {
DatabaseType::Oracle => index.secondary.as_deref().is_some_and(|value| value != "N"),
DatabaseType::OceanbaseOracle => index.secondary.is_some(),
_ => true,
}
|| index.columns.len() != unique.columns.len()
|| !same_identifier_set(&index.columns, &unique.columns)
|| index.directions.len() != index.columns.len()
|| index.directions.iter().any(|direction| direction != "ASC")
|| index.key_is_expression.len() != index.columns.len()
|| index.key_is_expression.iter().any(|is_expression| *is_expression)
|| !index.included_columns.is_empty()
{
return Err("UNIQUE supporting index metadata is incomplete or outside the verified ordinary-index shape.");
}
} else if database_type == DatabaseType::Oracle
&& unique.state.enabled
&& (unique.index.new_name.as_deref().is_none_or(str::is_empty)
|| !unique.index.new_name_available
|| unique.index.owner != schema.unwrap_or_default())
{
return Err("A collision-free new UNIQUE index name is required.");
}
if unique.index.new_name.as_deref().is_some_and(str::is_empty)
|| (unique.index.new_name.is_some() && !unique.index.new_name_available)
|| (unique.index.new_name.is_some() && unique.index.owner != schema.unwrap_or_default())
{
return Err("The replacement UNIQUE index name is missing or already in use.");
}
Ok(())
}
fn validate_check(check: &OracleCheckConstraint, oracle: bool) -> Result<(), &'static str> {
if check.expression.trim().is_empty() {
return Err("CHECK expression must not be empty.");
}
let mut parser = Parser::new(&OracleDialect)
.try_with_sql(&check.expression)
.map_err(|_| "CHECK expression is not supported by the Oracle expression parser.")?;
let expression =
parser.parse_expr().map_err(|_| "CHECK expression is not supported by the Oracle expression parser.")?;
if !matches!(parser.peek_token().token, Token::EOF) {
return Err("CHECK expression must contain exactly one expression.");
}
struct SubqueryGuard(bool);
impl Visitor for SubqueryGuard {
type Break = ();
fn pre_visit_expr(&mut self, expression: &Expr) -> ControlFlow<Self::Break> {
if matches!(expression, Expr::Subquery(_) | Expr::InSubquery { .. } | Expr::Exists { .. }) {
self.0 = true;
ControlFlow::Break(())
} else {
ControlFlow::Continue(())
}
}
}
let mut guard = SubqueryGuard(false);
let _ = sqlparser::ast::Visit::visit(&expression, &mut guard);
if guard.0 {
return Err("Subqueries are not supported in CHECK expressions.");
}
if (check.state.initially_deferred && !check.state.deferrable)
|| (!oracle && (check.state.deferrable || check.state.initially_deferred || check.state.rely))
{
return Err("This CHECK constraint state is not supported.");
}
Ok(())
}
fn add_check(table: &str, check: &OracleCheckConstraint, database_type: DatabaseType) -> String {
format!(
"ALTER TABLE {table} ADD CONSTRAINT {} CHECK (\n{}\n){};",
quote_ident(StructureDialect::Oracle, &check.name),
check.expression,
constraint_state_suffix(&check.state, database_type)
)
}
fn add_unique(
table: &str,
unique: &super::OracleUniqueConstraint,
database_type: DatabaseType,
index_policy: &str,
) -> Option<String> {
let dialect = StructureDialect::Oracle;
let columns = unique.columns.iter().map(|column| quote_ident(dialect, column)).collect::<Vec<_>>().join(", ");
let index = &unique.index;
let using_index = if !unique.state.enabled || database_type == DatabaseType::OceanbaseOracle {
String::new()
} else if !index.name.is_empty()
&& index.metadata_complete
&& index_policy != "drop"
&& index.sharing == "exclusive"
&& if unique.state.deferrable {
index.uniqueness == "NONUNIQUE"
} else {
matches!(index.uniqueness.as_str(), "UNIQUE" | "NONUNIQUE")
}
&& same_identifier_set(&index.columns, &unique.columns)
{
format!(" USING INDEX {}", qualified_table(dialect, Some(&index.owner), &index.name))
} else {
let new_name = index.new_name.as_deref()?;
let index_ref = qualified_table(dialect, Some(&index.owner), new_name);
let kind = if unique.state.deferrable || !unique.state.validated { "INDEX" } else { "UNIQUE INDEX" };
format!(" USING INDEX (CREATE {kind} {index_ref} ON {table} ({columns}))")
};
// OceanBase UNIQUE only supports the default state already enforced by validate_unique.
let (before_index, after_index) = if database_type == DatabaseType::OceanbaseOracle {
(String::new(), String::new())
} else {
constraint_state_parts(&unique.state, database_type)
};
Some(format!(
"ALTER TABLE {table} ADD CONSTRAINT {} UNIQUE ({columns}){before_index}{using_index}{after_index};",
quote_ident(dialect, &unique.name),
))
}
fn drop_unique(
table: &str,
unique: &super::OracleUniqueConstraint,
database_type: DatabaseType,
index_policy: &str,
) -> String {
let index_clause = match (database_type, index_policy) {
(DatabaseType::Oracle, "keep") => " KEEP INDEX",
(DatabaseType::Oracle, "drop") => " DROP INDEX",
_ => "",
};
format!(
"ALTER TABLE {table} DROP CONSTRAINT {}{index_clause};",
quote_ident(StructureDialect::Oracle, &unique.name)
)
}
fn constraint_state_parts(state: &super::OraclePrimaryKeyState, database_type: DatabaseType) -> (String, String) {
let enabled = if state.enabled { "ENABLE" } else { "DISABLE" };
let validated = if state.validated { "VALIDATE" } else { "NOVALIDATE" };
let rely = if state.rely { "RELY" } else { "NORELY" };
if database_type == DatabaseType::Oracle {
(
format!(
" {} INITIALLY {} {rely}",
if state.deferrable { "DEFERRABLE" } else { "NOT DEFERRABLE" },
if state.initially_deferred { "DEFERRED" } else { "IMMEDIATE" }
),
format!(" {enabled} {validated}"),
)
} else {
(String::new(), format!(" {enabled} {validated}"))
}
}
fn constraint_state_suffix(state: &super::OraclePrimaryKeyState, database_type: DatabaseType) -> String {
let (before_index, after_index) = constraint_state_parts(state, database_type);
format!("{before_index}{after_index}")
}
fn same_identifier_set(left: &[String], right: &[String]) -> bool {
left.len() == right.len() && left.iter().all(|left| right.iter().any(|right| left == right))
}
fn drop_constraint(table: &str, name: &str) -> String {
format!("ALTER TABLE {table} DROP CONSTRAINT {};", quote_ident(StructureDialect::Oracle, name))
}
#[cfg(test)]
mod additional_tests {
use super::*;
#[test]
fn check_add_keeps_closing_parenthesis_outside_line_comment() {
let check = OracleCheckConstraint {
name: "CK_T".into(),
expression: "A > 0 -- tail comment".into(),
state: super::super::OraclePrimaryKeyState {
enabled: true,
validated: true,
deferrable: false,
initially_deferred: false,
rely: false,
},
};
let sql = add_check("\"APP\".\"T\"", &check, DatabaseType::Oracle);
assert!(sql
.contains("CHECK (\nA > 0 -- tail comment\n) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY ENABLE VALIDATE;"));
}
}