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 super::identifiers::quote_gaussdb_jdbc_identifier;
use super::*;
use crate::models::connection::DatabaseType;
#[test]
fn transfer_identifier_policy_preserves_legacy_output() {
assert_eq!(quote_transfer_identifier("user`events", &DatabaseType::Hive), "`user``events`");
assert_eq!(quote_transfer_identifier("user`events", &DatabaseType::Impala), "`user``events`");
assert_eq!(quote_transfer_identifier("user`events", &DatabaseType::ClickHouse), "`user``events`");
assert_eq!(quote_transfer_identifier("user`events", &DatabaseType::Doris), "`user``events`");
assert_eq!(quote_transfer_identifier("user]events", &DatabaseType::SqlServer), "[user]]events]");
assert_eq!(quote_transfer_identifier("user\"events", &DatabaseType::Postgres), "\"user\"\"events\"");
assert_eq!(qualified_transfer_table("events", "warehouse", &DatabaseType::Hive, None), "`warehouse`.`events`");
assert_eq!(qualified_transfer_table("events", "warehouse", &DatabaseType::Impala, None), "`warehouse`.`events`");
assert_eq!(qualified_transfer_table("events", "warehouse", &DatabaseType::Mysql, None), "`events`");
}
#[test]
fn backtick_family_predicate_matches_quoting() {
// `uses_backtick_identifier_quoting` is the single source of truth the "Standard
// SQL" export dialect consults to decide when to deviate from ANSI double
// quotes. It must agree with what `quote_table_identifier` actually emits, so a
// new backtick-family engine cannot silently drift between the two.
for &database_type in DatabaseType::ALL {
let quoted = quote_table_identifier(Some(database_type), "tbl");
let emits_backtick = quoted.starts_with('`');
assert_eq!(
uses_backtick_identifier_quoting(Some(database_type)),
emits_backtick,
"{database_type:?} quoting/backtick-family mismatch: {quoted}"
);
}
}
#[test]
fn quotes_identifiers_by_database_type() {
assert_eq!(quote_table_identifier(Some(DatabaseType::Nebula), "tag`name"), "`tag\\`name`");
assert_eq!(quote_table_identifier(Some(DatabaseType::Mysql), "user`name"), "`user``name`");
assert_eq!(quote_table_identifier(Some(DatabaseType::ClickHouse), "user`name"), "`user``name`");
assert_eq!(quote_table_identifier(Some(DatabaseType::Doris), "user`name"), "`user``name`");
assert_eq!(quote_table_identifier(Some(DatabaseType::Goldendb), "user`name"), "`user``name`");
assert_eq!(quote_table_identifier(Some(DatabaseType::StarRocks), "user`name"), "`user``name`");
assert_eq!(quote_table_identifier(Some(DatabaseType::SqlServer), "user]name"), "[user]]name]");
assert_eq!(quote_table_identifier(Some(DatabaseType::Postgres), "user\"name"), "\"user\"\"name\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Kingbase), "cqbq_ls"), "\"cqbq_ls\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Kingbase), "actionlogs"), "\"actionlogs\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Kingbase), "order"), "\"order\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Kingbase), "MixedCase"), "\"MixedCase\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Kingbase), "order detail"), "\"order detail\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Gaussdb), "\"MixedCase\""), "\"MixedCase\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::OpenGauss), "\"MixedCase\""), "\"MixedCase\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Informix), "users_1"), "users_1");
// SUNDB folds unquoted names to uppercase, so a simple lowercase name must stay
// unquoted; quoting it preserved the case and the statement found no such table (#11986).
assert_eq!(quote_table_identifier(Some(DatabaseType::Sundb), "rs_salest_budget"), "rs_salest_budget");
assert_eq!(quote_table_identifier(Some(DatabaseType::Sundb), "YEAR"), "YEAR");
assert_eq!(quote_table_identifier(Some(DatabaseType::Sundb), "user name"), "\"user name\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Sundb), "user\"name"), "\"user\"\"name\"");
assert_eq!(quote_table_identifier(Some(DatabaseType::Jdbc), "users_1"), "users_1");
assert_eq!(quote_table_identifier(Some(DatabaseType::Jdbc), "user name"), "user name");
assert_eq!(quote_table_identifier(Some(DatabaseType::Iotdb), "root.test.device2"), "root.test.device2");
// SOQL has no delimited identifiers and reads `"` as a string literal, so
// `SELECT * FROM "Account"` fails with MALFORMED_QUERY. Object and field API
// names always go out bare, and an org is a single scope: no schema qualifier.
assert_eq!(quote_table_identifier(Some(DatabaseType::Salesforce), "Account"), "Account");
assert_eq!(quote_table_identifier(Some(DatabaseType::Salesforce), "First_Name__c"), "First_Name__c");
assert_eq!(quote_table_data_identifier(Some(DatabaseType::Salesforce), "Account", None), "Account");
assert_eq!(qualified_table_name(Some(DatabaseType::Salesforce), Some("sales"), "Account"), "Account");
assert_eq!(quote_table_identifier(Some(DatabaseType::Spanner), "user`name"), "`user``name`");
// ArgoDB shares the Hive-family dialect: backticks quote identifiers and
// double quotes are string literals, and schemas qualify table names.
assert_eq!(quote_table_identifier(Some(DatabaseType::Argo), "user`name"), "`user``name`");
assert_eq!(quote_transfer_identifier("user`name", &DatabaseType::Argo), "`user``name`");
assert!(is_schema_aware(DatabaseType::Argo));
}
#[test]
fn builds_nebula_tag_and_edge_queries() {
let tag = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Nebula),
table_name: "player".into(),
table_type: Some("TABLE".into()),
columns: vec!["name".into()],
limit: Some(20),
..Default::default()
});
assert_eq!(tag, "MATCH (v:`player`) RETURN id(v) AS `_vid`, v.`player`.`name` AS `name` LIMIT 20;");
let edge = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Nebula),
table_name: "serve".into(),
table_type: Some("VIEW".into()),
columns: vec!["start_year".into()],
limit: Some(10),
..Default::default()
});
assert_eq!(edge, "MATCH ()-[e:`serve`]->() RETURN src(e) AS `_src`, dst(e) AS `_dst`, rank(e) AS `_rank`, e.`start_year` AS `start_year` LIMIT 10;");
}
/// Spanner databases are created in one of two immutable dialects. The connected
/// agent reports the correct identifier quote; when it is missing the static mapping
/// must fall back to GoogleSQL (backticks), because GoogleSQL treats double quotes as
/// string literals and would reject `SELECT * FROM "users"`.
#[test]
fn quotes_spanner_identifiers_by_connection_dialect() {
// GoogleSQL dialect: agent reports a backtick.
assert_eq!(quote_table_data_identifier(Some(DatabaseType::Spanner), "order", Some("`")), "`order`");
assert_eq!(quote_table_data_identifier(Some(DatabaseType::Spanner), "user`name", Some("`")), "`user``name`");
// PostgreSQL dialect: agent reports a double quote.
assert_eq!(quote_table_data_identifier(Some(DatabaseType::Spanner), "order", Some("\"")), "\"order\"");
assert_eq!(quote_table_data_identifier(Some(DatabaseType::Spanner), "user\"name", Some("\"")), "\"user\"\"name\"");
// No quote reported (metadata probe failed / caller outside the desktop store):
// fall back to the GoogleSQL default rather than the ANSI double quote.
assert_eq!(quote_table_data_identifier(Some(DatabaseType::Spanner), "order", None), "`order`");
// GoogleSQL's default schema is the empty string and must not produce `` ``.`t` ``,
// which Spanner rejects with `Invalid empty identifier`.
assert_eq!(
table_data_qualified_table_name(Some(DatabaseType::Spanner), Some(""), "singers", Some("`")),
"`singers`"
);
assert_eq!(
table_data_qualified_table_name(Some(DatabaseType::Spanner), Some("public"), "singers", Some("\"")),
"\"public\".\"singers\""
);
}
#[test]
fn kyuubi_table_data_uses_connection_identifier_quote() {
let columns = vec!["id".to_string(), "order\"value".to_string()];
let base = TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Kyuubi),
schema: Some("sales\"daily".to_string()),
table_name: "event\"log".to_string(),
columns,
limit: Some(25),
..Default::default()
};
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
identifier_quote: Some("\"".to_string()),
..base.clone()
}),
"SELECT \"id\", \"order\"\"value\" FROM \"sales\"\"daily\".\"event\"\"log\" LIMIT 25;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
identifier_quote: Some("`".to_string()),
..base.clone()
}),
"SELECT `id`, `order\"value` FROM `sales\"daily`.`event\"log` LIMIT 25;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Hive),
identifier_quote: Some("\"".to_string()),
..base
}),
"SELECT `id` AS `id`, `order\"value` AS `order\"value` FROM `sales\"daily`.`event\"log` LIMIT 25;"
);
}
#[test]
fn quotes_gaussdb_jdbc_identifiers_selectively() {
for (name, expected) in [
("schema_01", "schema_01"),
("MixedCase", "\"MixedCase\""),
("order", "\"order\""),
("order detail", "\"order detail\""),
("already\"quoted", "\"already\"\"quoted\""),
("\"AlreadyQuoted\"", "\"AlreadyQuoted\""),
] {
assert_eq!(quote_table_data_identifier(Some(DatabaseType::Gaussdb), name, Some("\"")), expected);
}
for (name, expected) in [
("schema_01", "schema_01"),
("MixedCase", "`MixedCase`"),
("order", "`order`"),
("order detail", "`order detail`"),
("already`quoted", "`already``quoted`"),
("`AlreadyQuoted`", "`AlreadyQuoted`"),
] {
assert_eq!(quote_table_data_identifier(Some(DatabaseType::Gaussdb), name, Some("`")), expected);
}
}
#[test]
fn qualifies_schema_only_for_schema_aware_databases() {
assert_eq!(qualified_table_name(Some(DatabaseType::Postgres), Some("public"), "users"), "\"public\".\"users\"");
assert_eq!(qualified_table_name(Some(DatabaseType::Kwdb), Some("public"), "users"), "\"public\".\"users\"");
assert_eq!(
qualified_table_name(Some(DatabaseType::Kingbase), Some("cqbq_ls"), "actionlogs"),
"\"cqbq_ls\".\"actionlogs\""
);
assert_eq!(qualified_table_name(Some(DatabaseType::Mysql), Some("public"), "users"), "`public`.`users`");
assert_eq!(qualified_table_name(Some(DatabaseType::Goldendb), Some("public"), "users"), "`public`.`users`");
assert_eq!(qualified_table_name(Some(DatabaseType::StarRocks), Some("warehouse"), "users"), "`warehouse`.`users`");
assert_eq!(qualified_table_name(Some(DatabaseType::Doris), Some("warehouse"), "users"), "`warehouse`.`users`");
assert_eq!(qualified_table_name(Some(DatabaseType::Databend), Some("dbx_test"), "users"), "`dbx_test`.`users`");
assert_eq!(
qualified_table_name(Some(DatabaseType::Xugu), Some("DBX_TEST"), "PRODUCTS"),
"\"DBX_TEST\".\"PRODUCTS\""
);
assert_eq!(qualified_table_name(Some(DatabaseType::Oscar), Some("SYSDBA"), "EMPLOYEE"), "\"SYSDBA\".\"EMPLOYEE\"");
// ArgoDB (Transwarp fork of Hive) shares Hive's backtick identifier syntax; it must be
// classified as schema-aware AND quoted with backticks (not the default `"..."`, which
// ArgoDB parses as a string literal — see dbx-argo-double-quote-bug memory note).
assert_eq!(qualified_table_name(Some(DatabaseType::Argo), Some("dws"), "etl_log"), "`dws`.`etl_log`");
assert_eq!(qualified_table_name(Some(DatabaseType::Argo), None, "etl_log"), "`etl_log`");
assert_eq!(qualified_table_name(Some(DatabaseType::Informix), Some("xtdpcky"), "users"), "xtdpcky.users");
assert_eq!(qualified_table_name(Some(DatabaseType::Sqlite), Some("analytics"), "users"), "\"analytics\".\"users\"");
assert_eq!(qualified_table_name(Some(DatabaseType::Jdbc), Some("cbsdw_dwd"), "dwd_test_df"), "dwd_test_df");
// GoogleSQL's default schema is the empty string: the qualifier (and its dot) must be
// dropped entirely, otherwise Spanner reports `Invalid empty identifier`.
assert_eq!(qualified_table_name(Some(DatabaseType::Spanner), Some(""), "users"), "`users`");
assert_eq!(qualified_table_name(Some(DatabaseType::Spanner), None, "users"), "`users`");
assert_eq!(qualified_table_name(Some(DatabaseType::Spanner), Some("public"), "users"), "`public`.`users`");
assert_eq!(qualified_table_name(Some(DatabaseType::Iotdb), Some("root.test"), "device2"), "root.test.device2");
assert_eq!(
qualified_table_name(Some(DatabaseType::Iotdb), Some("root.test"), "root.test.device2"),
"root.test.device2"
);
assert_eq!(
qualified_table_name(
Some(DatabaseType::SqlServer),
Some("__dbx_sqlserver_linked__:ERP%5D01|Finance%20DB|dbo"),
"Orders]2026"
),
"[ERP]]01].[Finance DB].[dbo].[Orders]]2026]"
);
}
#[test]
fn maps_table_pagination_strategy_by_database_type() {
assert_eq!(table_pagination_strategy(Some(DatabaseType::Mysql)), TablePaginationStrategy::LimitOffset);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Dameng)), TablePaginationStrategy::Rownum);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Db2)), TablePaginationStrategy::Db2FetchFirst);
assert_eq!(table_pagination_strategy(Some(DatabaseType::SqlServer)), TablePaginationStrategy::SqlServerTop);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Iris)), TablePaginationStrategy::IrisTop);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Informix)), TablePaginationStrategy::InformixFirst);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Firebird)), TablePaginationStrategy::FirebirdRows);
assert_eq!(table_pagination_strategy(Some(DatabaseType::OceanbaseOracle)), TablePaginationStrategy::Rownum);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Questdb)), TablePaginationStrategy::QuestDbLimit);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Oracle)), TablePaginationStrategy::Rownum);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Oscar)), TablePaginationStrategy::LimitOffset);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Cassandra)), TablePaginationStrategy::AgentMaxRows);
assert_eq!(
pagination_strategy(Some(DatabaseType::Cassandra), PaginationContext::BoundedRead),
TablePaginationStrategy::LimitOffset
);
assert_eq!(
pagination_strategy(Some(DatabaseType::Cassandra), PaginationContext::UserQuery),
TablePaginationStrategy::AgentMaxRows
);
assert_eq!(
pagination_strategy(Some(DatabaseType::Oracle), PaginationContext::BoundedRead),
TablePaginationStrategy::Rownum
);
assert_eq!(
pagination_strategy(Some(DatabaseType::Oscar), PaginationContext::BoundedRead),
TablePaginationStrategy::Rownum
);
assert_eq!(
pagination_strategy(Some(DatabaseType::Oscar), PaginationContext::UserQuery),
TablePaginationStrategy::Unbounded
);
assert_eq!(
pagination_strategy(Some(DatabaseType::Oracle), PaginationContext::UserQuery),
TablePaginationStrategy::Rownum
);
assert_eq!(table_pagination_strategy(Some(DatabaseType::Jdbc)), TablePaginationStrategy::AgentMaxRows);
// Both Spanner dialects support `LIMIT n OFFSET m`; pin the fallback.
assert_eq!(table_pagination_strategy(Some(DatabaseType::Spanner)), TablePaginationStrategy::LimitOffset);
assert_eq!(table_pagination_strategy(None), TablePaginationStrategy::LimitOffset);
}
#[test]
fn oscar_table_data_pages_use_one_limit_pagination_form_without_rownum() {
let options = TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oscar),
schema: Some("SYSDBA".to_string()),
table_name: "USERS".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "NAME".to_string()],
order_by: Some("\"ID\" ASC".to_string()),
limit: Some(100),
offset: Some(0),
..Default::default()
};
// OSCAR rejects a statement that combines ROWNUM with a LIMIT. The JDBC
// agent always caps table-data statements with setMaxRows(), so table
// previews must not also wrap the statement in ROWNUM: a later page's
// ROWNUM bound (offset + limit) exceeds the driver cap and the server
// reports "rownum and limit cannot be mixed" (#13515).
let first_page = build_table_data_select_sql(options.clone());
assert_eq!(first_page, "SELECT \"ID\", \"NAME\" FROM \"SYSDBA\".\"USERS\" ORDER BY \"ID\" ASC LIMIT 100;");
assert!(!first_page.to_ascii_uppercase().contains("ROWNUM"), "{first_page}");
let second_page = build_table_data_select_sql(TableDataSelectSqlOptions { offset: Some(100), ..options });
assert_eq!(
second_page,
"SELECT \"ID\", \"NAME\" FROM \"SYSDBA\".\"USERS\" ORDER BY \"ID\" ASC LIMIT 100 OFFSET 100;"
);
assert!(!second_page.to_ascii_uppercase().contains("ROWNUM"), "{second_page}");
}
#[test]
fn cassandra_table_data_pagination_uses_the_agent_cursor() {
for offset in [0, 100, 200] {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Cassandra),
table_name: "paged_rows".to_string(),
limit: Some(100),
offset: Some(offset),
..Default::default()
}),
"SELECT * FROM \"paged_rows\";"
);
}
}
#[test]
fn builds_select_sql_with_limit_syntax_for_database_type() {
let columns = vec!["id".to_string(), "name".to_string()];
let keys = vec!["id".to_string()];
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public"),
table_name: "users",
columns: &columns,
order_columns: &keys,
limit: 100,
}),
"SELECT \"id\", \"name\" FROM \"public\".\"users\" ORDER BY \"id\" ASC LIMIT 100;"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::SqlServer),
schema: Some("dbo"),
table_name: "users",
columns: &columns,
order_columns: &keys,
limit: 100,
}),
"SELECT TOP (100) [id], [name] FROM [dbo].[users] ORDER BY [id] ASC"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Db2),
schema: Some("DB2INST1"),
table_name: "USERS",
columns: &columns,
order_columns: &keys,
limit: 100,
}),
"SELECT \"id\", \"name\" FROM \"DB2INST1\".\"USERS\" ORDER BY \"id\" ASC FETCH FIRST 100 ROWS ONLY"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Firebird),
schema: None,
table_name: "USERS",
columns: &columns,
order_columns: &keys,
limit: 100,
}),
"SELECT \"id\", \"name\" FROM \"USERS\" ORDER BY \"id\" ASC ROWS 100"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("DBXTEST"),
table_name: "USERS",
columns: &columns,
order_columns: &keys,
limit: 100,
}),
"SELECT \"id\", \"name\" FROM (SELECT \"id\", \"name\" FROM \"DBXTEST\".\"USERS\" ORDER BY \"id\" ASC) WHERE ROWNUM <= 100"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::OceanbaseOracle),
schema: Some("DBXTEST"),
table_name: "USERS",
columns: &columns,
order_columns: &keys,
limit: 100,
}),
"SELECT \"id\", \"name\" FROM (SELECT \"id\", \"name\" FROM \"DBXTEST\".\"USERS\" ORDER BY \"id\" ASC) WHERE ROWNUM <= 100"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Dameng),
schema: Some("SYSDBA"),
table_name: "USERS",
columns: &columns,
order_columns: &keys,
limit: 100,
}),
"SELECT \"id\", \"name\" FROM (SELECT \"id\", \"name\" FROM \"SYSDBA\".\"USERS\" ORDER BY \"id\" ASC) WHERE ROWNUM <= 100"
);
// JDBC connections skip SQL-level row limiting — the JDBC agent handles
// it via Statement.setMaxRows() which is universally supported.
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Jdbc),
schema: Some("cbsdw_dwd"),
table_name: "dwd_test_df",
columns: &[],
order_columns: &[],
limit: 100,
}),
"SELECT * FROM dwd_test_df;"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Databend),
schema: Some("dbx_test"),
table_name: "jdbc_probe",
columns: &[],
order_columns: &[],
limit: 500,
}),
"SELECT * FROM `dbx_test`.`jdbc_probe` LIMIT 500;"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Xugu),
schema: Some("DBX_TEST"),
table_name: "PRODUCTS",
columns: &[],
order_columns: &[],
limit: 100,
}),
"SELECT * FROM \"DBX_TEST\".\"PRODUCTS\" LIMIT 100;"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Hive),
schema: Some("test"),
table_name: "dws_event_analyse",
columns: &[],
order_columns: &[],
limit: 100,
}),
"SELECT * FROM `test`.`dws_event_analyse` LIMIT 100;"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Impala),
schema: Some("dbx_demo"),
table_name: "connection_test",
columns: &[],
order_columns: &[],
limit: 100,
}),
"SELECT * FROM `dbx_demo`.`connection_test` LIMIT 100;"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::StarRocks),
schema: None,
table_name: "sales_report",
columns: &["customer_name".to_string()],
order_columns: &[],
limit: 100,
}),
"SELECT `customer_name` FROM `sales_report` LIMIT 100;"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Iris),
schema: Some("Ens"),
table_name: "AlarmResponse",
columns: &[],
order_columns: &[],
limit: 100,
}),
"SELECT TOP 100 * FROM Ens.AlarmResponse"
);
// Caché 2016 often runs with delimited identifiers disabled: the JDBC
// preparser turns a quoted column into a `:%qpar` host variable and a
// quoted ORDER BY name into a string literal (constant sort), so ordinary
// column names must stay unquoted (#8340).
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Iris),
schema: Some("SQLUser"),
table_name: "CT_Country",
columns: &columns,
order_columns: &keys,
limit: 200,
}),
"SELECT TOP 200 id, name FROM SQLUser.CT_Country ORDER BY id ASC"
);
// Non-Iris dialects keep their own quoting for the same input.
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Mysql),
schema: None,
table_name: "users",
columns: &columns,
order_columns: &keys,
limit: 100,
}),
"SELECT `id`, `name` FROM `users` ORDER BY `id` ASC LIMIT 100;"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Iotdb),
schema: Some("root.test"),
table_name: "device2",
columns: &[],
order_columns: &[],
limit: 100,
}),
"SELECT * FROM root.test.device2 LIMIT 100;"
);
}
#[test]
fn jdbc_tdengine_table_preview_qualifies_selected_database() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Jdbc),
driver_profile: Some(" TDENGINE ".to_string()),
schema: None,
database: Some("bopu_light".to_string()),
table_name: "mppd_pwr_862288087612675".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM `bopu_light`.`mppd_pwr_862288087612675`;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Jdbc),
driver_profile: Some("tdengine".to_string()),
schema: Some("fallback_db".to_string()),
database: Some(" ".to_string()),
table_name: "meter`readings".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM `fallback_db`.`meter``readings`;"
);
}
#[test]
fn jdbc_non_tdengine_and_unscoped_tdengine_previews_remain_unqualified() {
for (driver_profile, database) in [(Some("postgres"), Some("analytics")), (Some("tdengine"), None)] {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Jdbc),
driver_profile: driver_profile.map(str::to_string),
schema: None,
database: database.map(str::to_string),
table_name: "readings".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM readings;"
);
}
}
#[test]
fn jdbc_table_data_qualifies_schema_without_forcing_identifier_quotes() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Jdbc),
driver_profile: Some("phoenix".to_string()),
schema: Some("DEMO".to_string()),
table_name: "STUDENT".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM DEMO.STUDENT;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Jdbc),
driver_profile: Some("phoenix".to_string()),
identifier_quote: Some("\"".to_string()),
schema: Some("MY_SCHEMA".to_string()),
table_name: "ORDER".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM \"MY_SCHEMA\".\"ORDER\";"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Jdbc),
driver_profile: Some("phoenix".to_string()),
schema: None,
table_name: "STUDENT".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM STUDENT;"
);
}
#[test]
fn opengauss_table_data_uses_compatibility_identifier_quote() {
// openGauss keeps the PostgreSQL catalog but switches identifier quoting with
// the database compatibility mode: B/MySQL databases reject the double quotes
// that A/PG databases require (#13596).
let base = TableDataSelectSqlOptions {
database_type: Some(DatabaseType::OpenGauss),
schema: Some("dbx-demo".to_string()),
table_name: "order".to_string(),
limit: Some(25),
..Default::default()
};
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
identifier_quote: Some("`".to_string()),
..base.clone()
}),
"SELECT * FROM `dbx-demo`.`order` LIMIT 25;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions { identifier_quote: Some("\"".to_string()), ..base }),
"SELECT * FROM \"dbx-demo\".\"order\" LIMIT 25;"
);
}
#[test]
fn builds_table_data_where_and_schema_queries() {
assert_eq!(
build_count_table_sql(Some(DatabaseType::Kingbase), Some("cqbq_ls"), "actionlogs"),
"SELECT COUNT(*) AS row_count FROM \"cqbq_ls\".\"actionlogs\""
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Mysql),
schema: None,
table_name: "users".to_string(),
table_type: None,
primary_keys: vec!["id".to_string()],
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: Some("where status = 'active'".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT * FROM `users` WHERE (status = 'active') LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Goldendb),
schema: None,
table_name: "sys_dic".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT * FROM `sys_dic` LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "orders".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(50),
offset: Some(100),
where_input: Some("WHERE amount > 10".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT * FROM \"public\".\"orders\" WHERE (amount > 10) LIMIT 50 OFFSET 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Impala),
schema: Some("dbx_demo".to_string()),
table_name: "connection_test".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: Vec::new(),
columns: vec!["id".to_string(), "name".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(2),
offset: Some(1),
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT `id`, `name` FROM `dbx_demo`.`connection_test` ORDER BY 1 LIMIT 2 OFFSET 1;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Kingbase),
identifier_quote: Some("`".to_string()),
schema: Some("nacos-v3".to_string()),
table_name: "actionlogs".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT * FROM `nacos-v3`.`actionlogs` LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Kingbase),
identifier_quote: Some("\"".to_string()),
schema: Some("App Schema".to_string()),
table_name: "ANALYZE".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM \"App Schema\".\"ANALYZE\" LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Informix),
driver_profile: Some("gbase8s".to_string()),
identifier_quote: Some(String::new()),
schema: Some("gbasedbt".to_string()),
table_name: "connection_smoke".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT FIRST 100 * FROM connection_smoke"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Informix),
identifier_quote: Some(String::new()),
schema: Some("gbasedbt".to_string()),
table_name: "connection_smoke".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT FIRST 100 * FROM gbasedbt.connection_smoke"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Gbase8s),
schema: Some("gbasedbt".to_string()),
table_name: "v_orders".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT FIRST 100 * FROM v_orders"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Gbase8s),
identifier_quote: Some(String::new()),
schema: Some("gbasedbt".to_string()),
table_name: "v_orders".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT FIRST 100 * FROM v_orders"
);
assert_eq!(
build_count_table_sql(Some(DatabaseType::Gbase8s), Some("gbasedbt"), "v_orders"),
"SELECT COUNT(*) AS row_count FROM v_orders"
);
assert_eq!(qualified_table_name(Some(DatabaseType::Gbase8s), Some("gbasedbt"), "v_orders"), "v_orders");
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Gaussdb),
identifier_quote: Some("\"".to_string()),
schema: Some("schema_01".to_string()),
table_name: "table_01".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM schema_01.table_01 LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Gaussdb),
identifier_quote: Some("`".to_string()),
schema: Some("App Schema".to_string()),
table_name: "order".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM `App Schema`.`order` LIMIT 100;"
);
for database_type in [DatabaseType::Postgres, DatabaseType::OpenGauss] {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(database_type),
identifier_quote: Some("`".to_string()),
schema: Some("App Schema".to_string()),
table_name: "order".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM `App Schema`.`order` LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(database_type),
identifier_quote: Some("\"".to_string()),
schema: Some("schema_01".to_string()),
table_name: "MixedCase".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM schema_01.\"MixedCase\" LIMIT 100;"
);
}
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Gaussdb),
schema: Some("schema_01".to_string()),
table_name: "table_01".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM \"schema_01\".\"table_01\" LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::OpenGauss),
schema: Some("schema_01".to_string()),
table_name: "table_01".to_string(),
limit: Some(100),
..Default::default()
}),
"SELECT * FROM \"schema_01\".\"table_01\" LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Xugu),
schema: Some("DBX_TEST".to_string()),
table_name: "PRODUCTS".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT * FROM \"DBX_TEST\".\"PRODUCTS\" LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::StarRocks),
schema: None,
table_name: "sales_report".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: vec!["customer_name".to_string(), "amount".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: Some("`customer_name` = 'Acme'".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT * FROM `sales_report` WHERE (`customer_name` = 'Acme') LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Db2),
schema: Some("DB2INST1".to_string()),
table_name: "ORDERS".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(50),
offset: None,
where_input: Some("WHERE amount > 10".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT * FROM \"DB2INST1\".\"ORDERS\" WHERE (amount > 10) FETCH FIRST 50 ROWS ONLY"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Firebird),
schema: None,
table_name: "ORDERS".to_string(),
table_type: None,
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "AMOUNT".to_string()],
fallback_order_columns: Vec::new(),
order_by: Some("\"ID\" ASC".to_string()),
limit: Some(50),
offset: Some(100),
where_input: Some("WHERE amount > 10".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT * FROM \"ORDERS\" WHERE (amount > 10) ORDER BY \"ID\" ASC ROWS 101 TO 150"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("DBXTEST".to_string()),
table_name: "ORDERS".to_string(),
table_type: None,
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "AMOUNT".to_string()],
fallback_order_columns: Vec::new(),
order_by: Some("\"ID\" ASC".to_string()),
limit: Some(50),
offset: Some(100),
where_input: Some("WHERE amount > 10".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT \"ID\", \"AMOUNT\" FROM (SELECT dbx_inner.*, ROWNUM AS \"__dbx_row_num\" FROM (SELECT \"ID\", \"AMOUNT\" FROM \"DBXTEST\".\"ORDERS\" WHERE (amount > 10) ORDER BY \"ID\" ASC) dbx_inner WHERE ROWNUM <= 150) WHERE \"__dbx_row_num\" > 100"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::OceanbaseOracle),
schema: Some("DBXTEST".to_string()),
table_name: "ORDERS".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT * FROM (SELECT * FROM \"DBXTEST\".\"ORDERS\") WHERE ROWNUM <= 100"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::OceanbaseOracle),
schema: Some("DBXTEST".to_string()),
table_name: "ORDERS".to_string(),
table_type: None,
primary_keys: vec!["ID".to_string()],
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(50),
offset: None,
where_input: Some("WHERE amount > 10".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT * FROM (SELECT * FROM \"DBXTEST\".\"ORDERS\" WHERE (amount > 10)) WHERE ROWNUM <= 50"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Db2),
schema: Some("DB2INST1".to_string()),
table_name: "ORDERS".to_string(),
table_type: None,
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "AMOUNT".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(50),
offset: Some(100),
where_input: Some("WHERE amount > 10".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT \"ID\", \"AMOUNT\" FROM (SELECT dbx_t.\"ID\", dbx_t.\"AMOUNT\", ROW_NUMBER() OVER () AS \"__dbx_row_num\" FROM \"DB2INST1\".\"ORDERS\" dbx_t WHERE (amount > 10)) dbx_page WHERE \"__dbx_row_num\" > 100 AND \"__dbx_row_num\" <= 150 ORDER BY \"__dbx_row_num\""
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Iris),
schema: Some("Ens".to_string()),
table_name: "AlarmResponse".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT TOP 100 * FROM Ens.AlarmResponse"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Iris),
schema: Some("Ens".to_string()),
table_name: "AlarmResponse".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: Some("\"ID\" ASC".to_string()),
limit: Some(100),
offset: Some(100),
use_driver_row_offset: true,
where_input: Some("Status = 'Open'".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT * FROM Ens.AlarmResponse WHERE (Status = 'Open') ORDER BY \"ID\" ASC"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Iotdb),
schema: Some("root.test".to_string()),
table_name: "device2".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT * FROM root.test.device2 LIMIT 100;"
);
}
#[test]
fn builds_mysql_table_data_large_value_previews_without_truncating_keys() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Mysql),
table_name: "large_rows".to_string(),
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "payload".to_string(), "raw_value".to_string(), "metadata".to_string()],
column_types: vec!["bigint".to_string(), "longtext".to_string(), "longblob".to_string(), "json".to_string()],
large_value_preview_size: Some(4096),
limit: Some(100),
..Default::default()
});
assert!(sql.starts_with("SELECT `id`, LEFT(`payload`, 4097) AS `payload`"));
assert!(sql.contains("CONCAT('T:4096:', LENGTH(`payload`)) AS `__DBX_LARGE_VALUE_BYTES_T_1`"));
assert!(sql.contains("LEFT(`raw_value`, 4097) AS `raw_value`"));
assert!(sql.contains("CONCAT('B:4096:', LENGTH(`raw_value`)) AS `__DBX_LARGE_VALUE_BYTES_B_2`"));
assert!(sql.contains("LEFT(`metadata`, 4097) AS `metadata`"));
assert!(sql.contains("CONCAT('T:4096:', LENGTH(`metadata`)) AS `__DBX_LARGE_VALUE_BYTES_J_3`"));
assert!(!sql.contains("OCTET_LENGTH"));
assert!(!sql.contains("__DBX_LARGE_VALUE_BYTES_0"));
}
#[test]
fn previews_mysql_bounded_string_columns_only_above_the_active_budget() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Mysql),
table_name: "t_0001".to_string(),
primary_keys: vec!["id".to_string()],
columns: vec![
"id".to_string(),
"image_mime".to_string(),
"image_data".to_string(),
"image_url".to_string(),
"large_note".to_string(),
"large_binary".to_string(),
],
column_types: vec![
"int".to_string(),
"varchar(64)".to_string(),
"longblob".to_string(),
"varchar(512)".to_string(),
"varchar(10000)".to_string(),
"varbinary(10000)".to_string(),
],
large_value_preview_size: Some(419),
limit: Some(10_000),
..Default::default()
});
assert!(sql.starts_with("SELECT `id`, `image_mime`, LEFT(`image_data`, 420) AS `image_data`"));
assert!(sql.contains("CONCAT('B:419:', LENGTH(`image_data`)) AS `__DBX_LARGE_VALUE_BYTES_B_2`, LEFT(`image_url`, 420) AS `image_url`"));
assert!(sql.contains("CONCAT('T:419:', LENGTH(`image_url`)) AS `__DBX_LARGE_VALUE_BYTES_T_3`"));
assert!(sql.contains("LEFT(`large_note`, 420) AS `large_note`"));
assert!(sql.contains("CONCAT('T:419:', LENGTH(`large_note`)) AS `__DBX_LARGE_VALUE_BYTES_T_4`"));
assert!(sql.contains("LEFT(`large_binary`, 420) AS `large_binary`"));
assert!(!sql.contains("LEFT(`image_mime`"));
}
#[test]
fn builds_postgres_table_data_large_value_previews() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "large_rows".to_string(),
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "payload".to_string(), "metadata".to_string(), "content".to_string()],
column_types: vec!["integer".to_string(), "text".to_string(), "jsonb".to_string(), "bytea".to_string()],
large_value_preview_size: Some(8192),
limit: Some(100),
..Default::default()
});
assert!(sql.starts_with("SELECT \"id\", left(\"payload\", 8193) AS \"payload\""));
assert!(sql.contains("'T:8192' AS \"__DBX_LARGE_VALUE_BYTES_T_1\""));
assert!(sql.contains("left(\"metadata\"::text, 8193) AS \"metadata\""));
assert!(sql.contains("'T:8192' AS \"__DBX_LARGE_VALUE_BYTES_K_2\""));
assert!(sql.contains("substring(\"content\" from 1 for 8193) AS \"content\""));
assert!(sql.contains("'B:8192:' || octet_length(\"content\")::text AS \"__DBX_LARGE_VALUE_BYTES_B_3\""));
}
#[test]
fn builds_db2_table_data_large_value_previews_with_bounded_substr() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Db2),
schema: Some("DB2INST1".to_string()),
table_name: "large_rows".to_string(),
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "doc".to_string(), "raw_value".to_string()],
column_types: vec!["integer".to_string(), "clob".to_string(), "blob".to_string()],
large_value_preview_size: Some(4096),
limit: Some(100),
..Default::default()
});
// DB2 的 SUBSTR 第三参数越界会报 SQL0138N,故用 CASE WHEN LENGTH(..) 把预览长度
// 夹在实际长度内:CLOB → 文本预览、BLOB → 二进制预览,都不对短值报错。
assert!(
sql.contains(
"SUBSTR(\"doc\", 1, CASE WHEN LENGTH(\"doc\") >= 4097 THEN 4097 ELSE LENGTH(\"doc\") END) AS \"doc\""
),
"clob preview sql: {sql}"
);
assert!(sql.contains("'T:4096' AS "), "clob marker sql: {sql}");
assert!(
sql.contains("SUBSTR(\"raw_value\", 1, CASE WHEN LENGTH(\"raw_value\") >= 4097 THEN 4097 ELSE LENGTH(\"raw_value\") END) AS \"raw_value\""),
"blob preview sql: {sql}"
);
assert!(sql.contains("'B:4096' AS "), "blob marker sql: {sql}");
// 主键列不做预览,且不得生成裸 SUBSTR(col,1,n)(会越界报错)
assert!(!sql.contains("SUBSTR(\"id\""), "pk should not be previewed: {sql}");
assert!(!sql.contains("SUBSTR(\"doc\", 1, 4097)"), "must not emit unbounded substr: {sql}");
}
#[test]
fn builds_oracle_table_data_large_value_previews_with_bounded_substr() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("MESPRD".to_string()),
table_name: "API_REQUEST_RESPONSE".to_string(),
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "REQ_PAYLOAD".to_string(), "RESP_PAYLOAD".to_string(), "RAW_DATA".to_string()],
column_types: vec!["NUMBER".to_string(), "CLOB".to_string(), "NCLOB".to_string(), "BLOB".to_string()],
large_value_preview_size: Some(8192),
limit: Some(100),
..Default::default()
});
// Oracle SQL 结果上限:VARCHAR2 4000 字节(4 字节/字符 → 999+1 字符)、RAW 2000 字节
// (RAWTOHEX 翻倍 + "0x" → 1998+1 字节)。外层 ROWNUM 分页按别名重选内层视图的列,
// marker 列必须透传到最外层,客户端才能剥离并登记大值单元格。
assert!(
sql.starts_with(
"SELECT \"ID\", \"REQ_PAYLOAD\", \"__DBX_LARGE_VALUE_BYTES_T_1\", \"RESP_PAYLOAD\", \"__DBX_LARGE_VALUE_BYTES_T_2\", \"RAW_DATA\", \"__DBX_LARGE_VALUE_BYTES_B_3\" FROM"
),
"outer page projection: {sql}"
);
assert!(sql.contains("DBMS_LOB.SUBSTR(\"REQ_PAYLOAD\", 1000, 1) AS \"REQ_PAYLOAD\""), "clob preview sql: {sql}");
assert!(
sql.contains("'T:999:' || DBMS_LOB.GETLENGTH(\"REQ_PAYLOAD\") AS \"__DBX_LARGE_VALUE_BYTES_T_1\""),
"clob marker sql: {sql}"
);
assert!(sql.contains("DBMS_LOB.SUBSTR(\"RESP_PAYLOAD\", 1000, 1) AS \"RESP_PAYLOAD\""), "nclob preview sql: {sql}");
assert!(
sql.contains("'T:999:' || DBMS_LOB.GETLENGTH(\"RESP_PAYLOAD\") AS \"__DBX_LARGE_VALUE_BYTES_T_2\""),
"nclob marker sql: {sql}"
);
assert!(
sql.contains("'0x' || RAWTOHEX(DBMS_LOB.SUBSTR(\"RAW_DATA\", 1999, 1)) AS \"RAW_DATA\""),
"blob preview sql: {sql}"
);
assert!(
sql.contains("'B:1998:' || DBMS_LOB.GETLENGTH(\"RAW_DATA\") AS \"__DBX_LARGE_VALUE_BYTES_B_3\""),
"blob marker sql: {sql}"
);
assert!(sql.contains("ROWNUM <= 100"), "rownum page: {sql}");
assert!(!sql.contains("SUBSTR(\"ID\""), "pk should not be previewed: {sql}");
}
#[test]
fn builds_oracle_table_data_previews_for_small_budgets() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
table_name: "documents".to_string(),
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "BODY".to_string()],
column_types: vec!["NUMBER".to_string(), "CLOB".to_string()],
large_value_preview_size: Some(200),
limit: Some(50),
..Default::default()
});
assert!(sql.contains("DBMS_LOB.SUBSTR(\"BODY\", 201, 1) AS \"BODY\""), "preview sql: {sql}");
assert!(
sql.contains("'T:200:' || DBMS_LOB.GETLENGTH(\"BODY\") AS \"__DBX_LARGE_VALUE_BYTES_T_1\""),
"marker sql: {sql}"
);
}
#[test]
fn builds_oracle_table_data_previews_for_rowid_keyed_tables() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
table_name: "keyless_logs".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: vec!["__DBX_ROWID".to_string()],
include_row_id: true,
columns: vec!["PAYLOAD".to_string(), "CREATED_AT".to_string()],
column_types: vec!["CLOB".to_string(), "DATE".to_string()],
large_value_preview_size: Some(200),
limit: Some(100),
..Default::default()
});
// 无主键基表:内层投影以合成键 ROWIDTOCHAR 开头、FROM 带别名 t,外层按别名重选
// __DBX_ROWID 与 marker 列,取全值时按同一合成键回查。marker 下标是结果集位置:
// __DBX_ROWID 占第 0 位,PAYLOAD 的 marker 因此是 _T_1(客户端剥离按此校验)。
assert!(
sql.contains(
"SELECT ROWIDTOCHAR(t.ROWID) AS \"__DBX_ROWID\", DBMS_LOB.SUBSTR(\"PAYLOAD\", 201, 1) AS \"PAYLOAD\", 'T:200:' || DBMS_LOB.GETLENGTH(\"PAYLOAD\") AS \"__DBX_LARGE_VALUE_BYTES_T_1\", \"CREATED_AT\" FROM \"keyless_logs\" t"
),
"inner projection: {sql}"
);
assert!(
sql.starts_with("SELECT \"__DBX_ROWID\", \"PAYLOAD\", \"__DBX_LARGE_VALUE_BYTES_T_1\", \"CREATED_AT\" FROM"),
"outer projection: {sql}"
);
}
#[test]
fn keeps_oracle_table_data_previews_on_later_pages() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
table_name: "documents".to_string(),
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "BODY".to_string()],
column_types: vec!["NUMBER".to_string(), "CLOB".to_string()],
large_value_preview_size: Some(200),
limit: Some(100),
offset: Some(200),
..Default::default()
});
// 第二页起是三层嵌套;中间层 dbx_inner.* 必须把 marker 带到最外层投影。
assert!(sql.contains("\"__dbx_row_num\""), "row-number page: {sql}");
assert!(
sql.starts_with("SELECT \"ID\", \"BODY\", \"__DBX_LARGE_VALUE_BYTES_T_1\" FROM"),
"outer projection: {sql}"
);
assert!(sql.contains("DBMS_LOB.SUBSTR(\"BODY\", 201, 1)"), "inner preview survives: {sql}");
}
#[test]
fn skips_oracle_previews_for_columns_without_substr_support() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
table_name: "mixed_objects".to_string(),
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "DOC".to_string(), "LEGACY".to_string()],
column_types: vec!["NUMBER".to_string(), "XMLTYPE".to_string(), "LONG".to_string()],
large_value_preview_size: Some(200),
limit: Some(100),
..Default::default()
});
// XMLTYPE / LONG 没有 DBMS_LOB.SUBSTR 重载,保持原样投影(与旧行为一致)。
assert!(!sql.contains("DBMS_LOB"), "no substr for xmltype/long: {sql}");
assert!(!sql.contains("__DBX_LARGE_VALUE_BYTES_"), "no markers: {sql}");
assert!(sql.contains("\"DOC\""), "xmltype column stays projected: {sql}");
}
#[test]
fn preserves_postgres_array_types_in_large_value_previews() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "array_preview".to_string(),
primary_keys: vec!["id".to_string()],
columns: [
"id",
"varchar_array",
"text_array",
"bytea_array",
"jsonb_array",
"vector_array",
"integer_array",
"text_value",
"varchar_value",
"varying_value",
"json_value",
"jsonb_value",
"tsvector_value",
"vector_value",
"bytea_value",
]
.map(str::to_string)
.to_vec(),
column_types: [
"integer",
"character varying[]",
"text[][]",
"bytea[]",
"jsonb[]",
"vector(3)[]",
"integer[]",
"text",
"varchar(255)",
"character varying(255)",
"json",
"jsonb",
"tsvector",
"vector(3)",
"bytea",
]
.map(str::to_string)
.to_vec(),
large_value_preview_size: Some(8),
limit: Some(25),
..Default::default()
});
assert!(sql.starts_with(
"SELECT \"id\", \"varchar_array\", \"text_array\", \"bytea_array\", \"jsonb_array\", \
\"vector_array\", \"integer_array\", left(\"text_value\", 9) AS \"text_value\""
));
for index in 1..=6 {
assert!(!sql.contains(&format!("__DBX_LARGE_VALUE_BYTES_T_{index}\"")));
assert!(!sql.contains(&format!("__DBX_LARGE_VALUE_BYTES_B_{index}\"")));
assert!(!sql.contains(&format!("__DBX_LARGE_VALUE_BYTES_K_{index}\"")));
assert!(!sql.contains(&format!("__DBX_LARGE_VALUE_BYTES_V_{index}\"")));
}
assert!(sql.contains("left(\"varchar_value\", 9) AS \"varchar_value\""));
assert!(sql.contains("left(\"varying_value\", 9) AS \"varying_value\""));
assert!(sql.contains("left(\"json_value\"::text, 9) AS \"json_value\""));
assert!(sql.contains("left(\"jsonb_value\"::text, 9) AS \"jsonb_value\""));
assert!(sql.contains("left(\"tsvector_value\"::text, 9) AS \"tsvector_value\""));
assert!(sql.contains("left(\"vector_value\"::text, 9) AS \"vector_value\""));
assert!(sql.contains("substring(\"bytea_value\" from 1 for 9) AS \"bytea_value\""));
assert!(sql.contains("'T:8' AS \"__DBX_LARGE_VALUE_BYTES_J_10\""));
assert!(sql.contains("'T:8' AS \"__DBX_LARGE_VALUE_BYTES_K_11\""));
assert!(sql.contains("'T:8' AS \"__DBX_LARGE_VALUE_BYTES_S_12\""));
assert!(sql.contains("'V:8' AS \"__DBX_LARGE_VALUE_BYTES_V_13\""));
assert!(sql.contains("'B:8:' || octet_length(\"bytea_value\")::text AS \"__DBX_LARGE_VALUE_BYTES_B_14\""));
}
#[test]
fn builds_pgvector_table_data_preview_with_compatible_text_cast() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "embeddings".to_string(),
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "embedding".to_string()],
column_types: vec!["integer".to_string(), "vector(16000)".to_string()],
large_value_preview_size: Some(8192),
limit: Some(100),
..Default::default()
});
assert!(sql.contains("left(\"embedding\"::text, 8193) AS \"embedding\""));
assert!(sql.contains("'V:8192' AS \"__DBX_LARGE_VALUE_BYTES_V_1\""));
}
#[test]
fn postgres_xml_preview_casts_to_text() {
// `left(xml, int)` does not exist and xml has no implicit cast to text,
// so an uncast preview breaks table data reads with
// `function left(xml, integer) does not exist`.
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "documents".to_string(),
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "xml_value".to_string()],
column_types: vec!["integer".to_string(), "xml".to_string()],
large_value_preview_size: Some(8192),
limit: Some(100),
..Default::default()
});
assert!(sql.contains("left(\"xml_value\"::text, 8193) AS \"xml_value\""), "sql was: {sql}");
assert!(sql.contains("'T:8192' AS \"__DBX_LARGE_VALUE_BYTES_T_1\""), "sql was: {sql}");
assert!(!sql.contains("left(\"xml_value\","), "sql was: {sql}");
}
#[test]
fn table_data_preview_requires_stable_keys_and_parallel_types() {
let base = TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
table_name: "large_rows".to_string(),
columns: vec!["payload".to_string()],
column_types: vec!["text".to_string()],
large_value_preview_size: Some(8192),
limit: Some(100),
..Default::default()
};
assert_eq!(build_table_data_select_sql(base.clone()), "SELECT * FROM \"large_rows\" LIMIT 100;");
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
primary_keys: vec!["id".to_string()],
column_types: Vec::new(),
..base.clone()
}),
"SELECT * FROM \"large_rows\" LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "__dbx_large_value_bytes_t_0".to_string()],
column_types: vec!["integer".to_string(), "text".to_string()],
..base
}),
"SELECT * FROM \"large_rows\" LIMIT 100;"
);
}
#[test]
fn builds_informix_table_data_with_skip_first_pagination() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Informix),
schema: Some("xtdpcky".to_string()),
table_name: "users".to_string(),
table_type: None,
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "name".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(50),
offset: Some(100),
where_input: Some("WHERE active = 1".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT SKIP 100 FIRST 50 * FROM xtdpcky.users WHERE (active = 1)"
);
assert_eq!(
build_table_select_sql(TableSelectSqlOptions {
database_type: Some(DatabaseType::Informix),
schema: None,
table_name: "systables",
columns: &["tabname".to_string()],
order_columns: &[],
limit: 1,
}),
"SELECT FIRST 1 tabname FROM systables"
);
}
#[test]
fn explicit_table_data_order_is_preserved() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "country_gdp".to_string(),
table_type: None,
primary_keys: vec!["year".to_string()],
columns: vec!["iso3".to_string(), "year".to_string(), "gdp_pc".to_string()],
fallback_order_columns: Vec::new(),
order_by: Some("\"iso3\" ASC".to_string()),
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT * FROM \"public\".\"country_gdp\" ORDER BY \"iso3\" ASC LIMIT 100;"
);
}
#[test]
fn greenplum_servers_do_not_receive_the_system_column_locator() {
let base = TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "ao_table".to_string(),
table_type: Some("TABLE".to_string()),
columns: vec!["id".to_string()],
limit: Some(20),
include_row_id: true,
..Default::default()
};
// Greenplum reports itself through version(); its segments reject
// tableoid/ctid/xmin with "cannot retrieve a system column" (#13227).
let greenplum = build_table_data_select_sql(TableDataSelectSqlOptions {
server_version: Some("PostgreSQL 9.4.24 (Greenplum Database 6.18.2 build commit:4b1c8322)".to_string()),
..base.clone()
});
assert_eq!(greenplum, "SELECT * FROM \"public\".\"ao_table\" LIMIT 20;");
// Real Cloudberry `version()` never contains the "greenplum" word.
let cloudberry = build_table_data_select_sql(TableDataSelectSqlOptions {
server_version: Some("PostgreSQL 14.4 (Cloudberry Database 1.6.0 build 6f2c4a1)".to_string()),
..base.clone()
});
assert_eq!(cloudberry, "SELECT * FROM \"public\".\"ao_table\" LIMIT 20;");
// Plain PostgreSQL and unknown versions keep the locator.
assert!(build_table_data_select_sql(base.clone()).contains("\"__DBX_ROWID\""));
assert!(build_table_data_select_sql(TableDataSelectSqlOptions {
server_version: Some("PostgreSQL 16.3".to_string()),
..base
})
.contains("\"__DBX_ROWID\""));
}
#[test]
fn selects_hidden_postgres_tuple_identity_only_for_known_unkeyed_base_tables() {
let base = TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "duplicate_rows".to_string(),
table_type: Some("TABLE".to_string()),
columns: vec!["id".to_string(), "value".to_string()],
limit: Some(20),
include_row_id: true,
..Default::default()
};
assert_eq!(
build_table_data_select_sql(base.clone()),
"SELECT tableoid::text || ':' || ctid::text || ':' || xmin::text AS \"__DBX_ROWID\", * FROM \"public\".\"duplicate_rows\" LIMIT 20;"
);
// The sidebar's `listTables` path spells relkind 'r' as `BASE TABLE`; the
// locator must be injected for that spelling too.
let base_table_spelling = build_table_data_select_sql(TableDataSelectSqlOptions {
table_type: Some("BASE TABLE".to_string()),
..base.clone()
});
assert!(base_table_spelling.contains("ctid"), "BASE TABLE must receive the locator: {base_table_spelling}");
for table_type in ["PARTITIONED TABLE", "FOREIGN TABLE", "VIEW"] {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
table_type: Some(table_type.to_string()),
..base.clone()
});
assert!(!sql.contains("ctid"), "{table_type} must not receive a synthetic tuple key: {sql}");
}
let unknown_type = build_table_data_select_sql(TableDataSelectSqlOptions { table_type: None, ..base.clone() });
assert!(!unknown_type.contains("ctid"));
let keyed = build_table_data_select_sql(TableDataSelectSqlOptions { primary_keys: vec!["id".to_string()], ..base });
assert!(!keyed.contains("ctid"));
}
#[test]
fn hologres_plugin_connections_receive_the_tableoid_locator_for_keyless_tables() {
// t8y2/dbx#12647: Hologres connects through the plain PostgreSQL plugin
// type, so the frontend gate enables the keyless-table locator and the
// click-to-open SELECT projects `tableoid`. Hologres' query translation
// layer rejects `tableoid` ("Variable tableoid ... can't be found in query
// translation"), so every keyless table fails to open. Hologres also
// reports a plain PG-11-style `server_version`, so the gate cannot be
// driven off the version string alone. This pins the exact statement the
// client emits today; the fix must stop projecting `tableoid` for engines
// without system-column support without regressing vanilla PostgreSQL.
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Postgres),
schema: Some("public".to_string()),
table_name: "hologres_keyless_events".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: Vec::new(),
limit: Some(200),
include_row_id: true,
..Default::default()
});
assert_eq!(
sql,
"SELECT tableoid::text || ':' || ctid::text || ':' || xmin::text AS \"__DBX_ROWID\", * FROM \"public\".\"hologres_keyless_events\" LIMIT 200;"
);
}
#[test]
fn builds_iris_table_data_sql_with_literal_top_and_ordinary_object() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Iris),
schema: Some("Ens".to_string()),
table_name: "AlarmResponse".to_string(),
table_type: None,
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "Status".to_string()],
fallback_order_columns: Vec::new(),
order_by: Some("\"Status\" DESC".to_string()),
limit: Some(25),
offset: None,
where_input: Some("WHERE \"Status\" = 'Open'".to_string()),
include_row_id: false,
..Default::default()
});
assert_eq!(sql, "SELECT TOP 25 * FROM Ens.AlarmResponse WHERE (\"Status\" = 'Open') ORDER BY \"Status\" DESC");
assert!(!sql.contains("?"));
assert!(!sql.contains(":%qpar"));
assert!(!sql.contains(" LIMIT "));
}
#[test]
fn iris_table_data_pages_use_a_vid_window_instead_of_a_repeated_top() {
let options = TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Iris),
schema: Some("Ens".to_string()),
table_name: "AlarmResponse".to_string(),
table_type: None,
primary_keys: vec!["ID".to_string()],
columns: vec!["ID".to_string(), "Status".to_string()],
fallback_order_columns: Vec::new(),
order_by: Some("ID ASC".to_string()),
limit: Some(25),
offset: Some(0),
where_input: Some("WHERE Status = 'Open'".to_string()),
include_row_id: false,
..Default::default()
};
// First page keeps the historical TOP-only statement.
assert_eq!(
build_table_data_select_sql(options.clone()),
"SELECT TOP 25 * FROM Ens.AlarmResponse WHERE (Status = 'Open') ORDER BY ID ASC"
);
// Later pages must move the window: InterSystems SQL has no OFFSET clause,
// so the page is derived from TOP(offset + limit) and `%VID` drops the rows
// before the offset (#8929).
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions { offset: Some(100), ..options.clone() }),
"SELECT * FROM (SELECT TOP 125 * FROM Ens.AlarmResponse WHERE (Status = 'Open') ORDER BY ID ASC) WHERE %VID > 100"
);
// The JDBC driver applies the offset itself, so its statement stays unwrapped.
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
offset: Some(100),
use_driver_row_offset: true,
..options
}),
"SELECT * FROM Ens.AlarmResponse WHERE (Status = 'Open') ORDER BY ID ASC"
);
}
#[test]
fn iris_table_data_sql_quotes_only_delimited_object_names() {
let sql = build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Iris),
schema: Some("App Schema".to_string()),
table_name: "Patient Record".to_string(),
limit: Some(25),
..Default::default()
});
assert_eq!(sql, "SELECT TOP 25 * FROM \"App Schema\".\"Patient Record\"");
}
#[test]
fn builds_table_data_special_column_queries() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Tdengine),
schema: Some("test_db".to_string()),
table_name: "meters".to_string(),
table_type: Some("STABLE".to_string()),
primary_keys: Vec::new(),
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT tbname, * FROM `test_db`.`meters` LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Tdengine),
schema: Some("test_db".to_string()),
table_name: "meters".to_string(),
table_type: Some("STABLE".to_string()),
primary_keys: vec!["ts".to_string()],
columns: vec![
"ts".to_string(),
"current".to_string(),
"voltage".to_string(),
"location".to_string(),
"groupid".to_string(),
],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT tbname, `ts` AS `ts`, `current` AS `current`, `voltage` AS `voltage`, `location` AS `location`, `groupid` AS `groupid` FROM `test_db`.`meters` LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Tdengine),
schema: Some("test_db".to_string()),
table_name: "d1001".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: vec!["ts".to_string()],
columns: vec!["ts".to_string(), "current".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT `ts` AS `ts`, `current` AS `current` FROM `test_db`.`d1001` LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Tdengine),
schema: Some("test_db".to_string()),
table_name: "d1001".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: vec!["ts".to_string()],
columns: vec!["tbname".to_string(), "ts".to_string(), "current".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT `ts` AS `ts`, `current` AS `current` FROM `test_db`.`d1001` LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Hive),
schema: None,
table_name: "departments".to_string(),
table_type: None,
primary_keys: Vec::new(),
columns: vec!["id".to_string(), "name".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"SELECT `id` AS `id`, `name` AS `name` FROM `departments` LIMIT 100;"
);
}
#[test]
fn builds_sqlserver_table_data_pages() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::SqlServer),
schema: Some("dbo".to_string()),
table_name: "accounts".to_string(),
table_type: None,
primary_keys: vec!["id".to_string()],
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(25),
offset: None,
where_input: Some("where id = 1".to_string()),
include_row_id: false,
..Default::default()
}),
"SELECT TOP (25) * FROM [dbo].[accounts] WHERE (id = 1)"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::SqlServer),
schema: Some("sales".to_string()),
table_name: "orders".to_string(),
table_type: None,
primary_keys: vec!["order_id".to_string()],
columns: vec!["order_id".to_string(), "customer".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(50),
offset: Some(100),
where_input: None,
include_row_id: false,
..Default::default()
}),
"WITH [dbx_page] AS (SELECT [order_id], [customer], ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [__dbx_row_num] FROM [sales].[orders]) SELECT [order_id], [customer] FROM [dbx_page] WHERE [__dbx_row_num] > 100 AND [__dbx_row_num] <= 150 ORDER BY [__dbx_row_num]"
);
}
#[test]
fn builds_oracle_and_neo4j_table_data_queries() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_LOAD_TABLE_006".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: true,
..Default::default()
}),
"SELECT * FROM (SELECT ROWIDTOCHAR(t.ROWID) AS \"__DBX_ROWID\", t.* FROM \"DBXTEST\".\"DBX_LOAD_TABLE_006\" t) WHERE ROWNUM <= 100"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_LOAD_TABLE_006".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: vec!["ID".to_string(), "NAME".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: true,
..Default::default()
}),
"SELECT \"__DBX_ROWID\", \"ID\", \"NAME\" FROM (SELECT ROWIDTOCHAR(t.ROWID) AS \"__DBX_ROWID\", t.* FROM \"DBXTEST\".\"DBX_LOAD_TABLE_006\" t) WHERE ROWNUM <= 100"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::OceanbaseOracle),
schema: Some("APP".to_string()),
table_name: "DATA_REPORT_SUB_TASK".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: vec!["ID".to_string(), "SMC_RESPONSE".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: true,
..Default::default()
}),
"SELECT \"__DBX_ROWID\", \"ID\", \"SMC_RESPONSE\" FROM (SELECT ROWIDTOCHAR(t.ROWID) AS \"__DBX_ROWID\", t.* FROM \"APP\".\"DATA_REPORT_SUB_TASK\" t) WHERE ROWNUM <= 100"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Xugu),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_LOAD_TABLE_006".to_string(),
table_type: Some("PARTITIONED TABLE".to_string()),
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: Vec::new(),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: true,
..Default::default()
}),
"SELECT ROWID AS \"__DBX_ROWID\", * FROM \"DBXTEST\".\"DBX_LOAD_TABLE_006\" LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Xugu),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_LOAD_TABLE_006".to_string(),
table_type: Some("TEMPORARY TABLE".to_string()),
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: vec!["ID".to_string(), "NAME".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(25),
offset: Some(10),
where_input: None,
include_row_id: true,
..Default::default()
}),
"SELECT ROWID AS \"__DBX_ROWID\", \"ID\", \"NAME\" FROM \"DBXTEST\".\"DBX_LOAD_TABLE_006\" LIMIT 25 OFFSET 10;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Xugu),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_JOIN_VIEW".to_string(),
table_type: Some("VIEW".to_string()),
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: vec!["ID".to_string(), "NAME".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: true,
..Default::default()
}),
"SELECT * FROM \"DBXTEST\".\"DBX_JOIN_VIEW\" LIMIT 100;"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_JOIN_VIEW".to_string(),
table_type: Some("VIEW".to_string()),
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: vec!["ID".to_string(), "NAME".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: true,
..Default::default()
}),
"SELECT \"ID\", \"NAME\" FROM (SELECT \"ID\", \"NAME\" FROM \"DBXTEST\".\"DBX_JOIN_VIEW\") WHERE ROWNUM <= 100"
);
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Neo4j),
schema: None,
table_name: "Employee".to_string(),
table_type: None,
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "first name".to_string(), "role".to_string()],
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(100),
offset: None,
where_input: None,
include_row_id: false,
..Default::default()
}),
"MATCH (n:`Employee`) RETURN elementId(n) AS `__DBX_ELEMENT_ID`, n.`id` AS `id`, n.`first name` AS `first name`, n.`role` AS `role` LIMIT 100;"
);
}
#[test]
fn neo4j_element_id_function_follows_the_connected_server_version() {
// Neo4j 4.x and older only know `id()`; `elementId()` arrived in 5.0.
assert_eq!(neo4j_element_id_function(Some("4.4.44")), "id");
assert_eq!(neo4j_element_id_function(Some("Neo4j/4.4.44")), "id");
assert_eq!(neo4j_element_id_function(Some("Neo4j 4.4.44 (community)")), "id");
assert_eq!(neo4j_element_id_function(Some("3.5.35")), "id");
assert_eq!(neo4j_element_id_function(Some("Neo4j/5.26.0")), "elementId");
assert_eq!(neo4j_element_id_function(Some("Neo4j/2025.01.0")), "elementId");
// Unknown or unparsable versions keep the current spelling.
assert_eq!(neo4j_element_id_function(None), "elementId");
assert_eq!(neo4j_element_id_function(Some("")), "elementId");
assert_eq!(neo4j_element_id_function(Some("Neo4j Community")), "elementId");
}
#[test]
fn builds_neo4j_table_select_with_the_legacy_element_id_on_neo4j_4() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Neo4j),
table_name: "Employee".to_string(),
primary_keys: vec!["id".to_string()],
columns: vec!["id".to_string(), "role".to_string()],
server_version: Some("Neo4j/4.4.44".to_string()),
limit: Some(100),
..Default::default()
}),
"MATCH (n:`Employee`) RETURN id(n) AS `__DBX_ELEMENT_ID`, n.`id` AS `id`, n.`role` AS `role` LIMIT 100;"
);
}
#[test]
fn oracle_unknown_table_type_does_not_assume_rowid_support() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_DISTINCT_VIEW".to_string(),
table_type: None,
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: vec!["ID".to_string(), "NAME".to_string()],
limit: Some(100),
offset: Some(100),
include_row_id: true,
..Default::default()
}),
"SELECT \"ID\", \"NAME\" FROM (SELECT dbx_inner.*, ROWNUM AS \"__dbx_row_num\" FROM (SELECT \"ID\", \"NAME\" FROM \"DBXTEST\".\"DBX_DISTINCT_VIEW\") dbx_inner WHERE ROWNUM <= 200) WHERE \"__dbx_row_num\" > 100"
);
}
#[test]
fn builds_oracle_rowid_wrapped_large_value_reload_sql() {
// The data-grid large-value reload selects the synthetic rowid key plus the
// target column with a rowid equality filter; `__DBX_ROWID` exists only as
// the inline-view alias, never as a base-table column (ORA-00904).
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("APP".to_string()),
table_name: "T_TEST".to_string(),
table_type: Some("TABLE".to_string()),
primary_keys: vec![DBX_ROWID_COLUMN.to_string()],
columns: vec![DBX_ROWID_COLUMN.to_string(), "ELM_CONTENT".to_string()],
where_input: Some("ROWIDTOCHAR(ROWID) = 'AAAFd1AAFAAAABSAA/'".to_string()),
fallback_order_columns: Vec::new(),
order_by: None,
limit: Some(1),
offset: Some(0),
include_row_id: true,
..Default::default()
}),
"SELECT \"__DBX_ROWID\", \"ELM_CONTENT\" FROM (SELECT ROWIDTOCHAR(t.ROWID) AS \"__DBX_ROWID\", t.* FROM \"APP\".\"T_TEST\" t WHERE (ROWIDTOCHAR(ROWID) = 'AAAFd1AAFAAAABSAA/')) WHERE ROWNUM <= 1"
);
}
#[test]
fn oracle_view_first_page_is_bounded_and_preserves_filter_and_sort() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_JOIN_VIEW".to_string(),
table_type: Some("VIEW".to_string()),
columns: vec!["ID".to_string(), "NAME".to_string()],
order_by: Some("\"ID\" DESC".to_string()),
limit: Some(100),
offset: Some(0),
where_input: Some("STATUS = 'A'".to_string()),
include_row_id: true,
..Default::default()
}),
"SELECT \"ID\", \"NAME\" FROM (SELECT \"ID\", \"NAME\" FROM \"DBXTEST\".\"DBX_JOIN_VIEW\" WHERE (STATUS = 'A') ORDER BY \"ID\" DESC) WHERE ROWNUM <= 100"
);
}
#[test]
fn oracle_view_later_pages_keep_rownum_pagination() {
assert_eq!(
build_table_data_select_sql(TableDataSelectSqlOptions {
database_type: Some(DatabaseType::Oracle),
schema: Some("DBXTEST".to_string()),
table_name: "DBX_JOIN_VIEW".to_string(),
table_type: Some("VIEW".to_string()),
columns: vec!["ID".to_string(), "NAME".to_string()],
order_by: Some("\"ID\" DESC".to_string()),
limit: Some(100),
offset: Some(100),
include_row_id: true,
..Default::default()
}),
"SELECT \"ID\", \"NAME\" FROM (SELECT dbx_inner.*, ROWNUM AS \"__dbx_row_num\" FROM (SELECT \"ID\", \"NAME\" FROM \"DBXTEST\".\"DBX_JOIN_VIEW\" ORDER BY \"ID\" DESC) dbx_inner WHERE ROWNUM <= 200) WHERE \"__dbx_row_num\" > 100"
);
}
#[test]
fn normalizes_where_input_with_multibyte_identifier_prefix() {
assert_eq!(normalize_where_input(Some("`客户名称` = '示例客户'")), "`客户名称` = '示例客户'");
assert_eq!(normalize_where_input(Some("WHERE `客户名称` = '示例客户';")), "`客户名称` = '示例客户'");
}
// Grid saves re-quote the table identity carried by the query result. For
// PostgreSQL-family engines reached through an agent-reported identifier quote,
// `quote_gaussdb_jdbc_identifier` only leaves all-lower-case identifiers
// unquoted, so a folded (`mss_check_sales_item`) write resolves while the
// query's original casing (`term."MSS_CHECK_SALES_ITEM"`) fails with
// `relation ... does not exist` on a lower-case-stored table (issue #10567).
// The frontend folds unquoted SQL-text identifiers before they reach this
// layer; these assertions lock the quoting contract that makes that fix work.
#[test]
fn postgres_family_table_data_quoting_resolves_folded_identifiers() {
let quote = Some("\"".to_string());
assert_eq!(quote_gaussdb_jdbc_identifier("mss_check_sales_item", "\""), "mss_check_sales_item");
assert_eq!(quote_gaussdb_jdbc_identifier("MSS_CHECK_SALES_ITEM", "\""), "\"MSS_CHECK_SALES_ITEM\"");
assert_eq!(quote_gaussdb_jdbc_identifier("term", "\""), "term");
// DBX-owned derived-table aliases travel through this same helper when the
// query builders count or paginate: simple-lower, never referenced by the
// outer query, so they stay bare instead of becoming `"dbx_count"`, which
// GaussDB M mode reads as a string literal (DBX-LEGACY-0001).
assert_eq!(quote_gaussdb_jdbc_identifier("dbx_count", "\""), "dbx_count");
assert_eq!(quote_gaussdb_jdbc_identifier("dbx_page", "\""), "dbx_page");
assert_eq!(
table_data_qualified_table_name(
Some(DatabaseType::Postgres),
Some("term"),
"mss_check_sales_item",
quote.as_deref()
),
"term.mss_check_sales_item"
);
assert_eq!(
table_data_qualified_table_name(
Some(DatabaseType::Postgres),
Some("term"),
"MSS_CHECK_SALES_ITEM",
quote.as_deref()
),
"term.\"MSS_CHECK_SALES_ITEM\""
);
assert_eq!(
table_data_qualified_table_name(
Some(DatabaseType::Gaussdb),
Some("term"),
"mss_check_sales_item",
quote.as_deref()
),
"term.mss_check_sales_item"
);
// Engines outside the GaussDB/PG identifier-quote path quote both parts.
assert_eq!(
table_data_qualified_table_name(
Some(DatabaseType::Jdbc),
Some("term"),
"mss_check_sales_item",
quote.as_deref()
),
"\"term\".\"mss_check_sales_item\""
);
}