Skip to main content

rustango_cms/
mysql_text.rs

1//! Long content columns on MySQL.
2//!
3//! The framework renders an unbounded `String` as `TEXT` on MySQL, which
4//! holds 65,535 bytes; Postgres `text` and SQLite `TEXT` are unbounded. A
5//! long StreamField body, translation blob or snippet overflows it: strict
6//! mode refuses the save (ERROR 1406) and non-strict mode truncates the
7//! JSON. Until the framework maps these to a long type,
8//! [`widen_long_text`] converts them in place.
9//!
10//! Its text columns also inherit MySQL's default collation, which is
11//! case- and accent-insensitive, so `/ABOUT` served `/about` and slugs
12//! `Hero` and `hero` could not coexist. [`exact_identity_columns`] gives
13//! the columns the CMS looks things up by a case- and accent-sensitive
14//! collation instead.
15
16use rustango::sql::{ExecError, Pool};
17
18/// On MySQL, turn every `TEXT` column that a registered model declares as
19/// an unbounded `String` into `LONGTEXT`, keeping its nullability and
20/// collation. Returns how many columns it changed.
21///
22/// Idempotent: a column already widened is no longer `TEXT`, so a re-run
23/// finds nothing. Covers host page-type extension tables too, as long as
24/// they exist when it runs — one created later is widened on the next
25/// boot. A no-op off MySQL.
26///
27/// # Errors
28/// Driver failures reading `information_schema` or altering a column.
29pub async fn widen_long_text(pool: &Pool) -> Result<usize, ExecError> {
30    #[cfg(feature = "mysql")]
31    if let Some(my) = pool.as_mysql() {
32        let wanted = unbounded_string_columns();
33        // information_schema reports names as binary strings on MySQL 8+;
34        // the casts keep them decodable as `String`.
35        let rows: Vec<(String, String, String, Option<String>)> = rustango::sql::sqlx::query_as(
36            "SELECT CAST(TABLE_NAME AS CHAR), CAST(COLUMN_NAME AS CHAR), \
37             CAST(IS_NULLABLE AS CHAR), CAST(COLLATION_NAME AS CHAR) \
38             FROM information_schema.COLUMNS \
39             WHERE TABLE_SCHEMA = DATABASE() AND DATA_TYPE = 'text'",
40        )
41        .fetch_all(my)
42        .await
43        .map_err(ExecError::from)?;
44        let mut changed = 0;
45        for (table, column, nullable, collation) in rows {
46            if !wanted.contains(&(table.as_str(), column.as_str())) {
47                continue;
48            }
49            let null = if nullable == "YES" { "NULL" } else { "NOT NULL" };
50            let collate = collation.map(|c| format!(" COLLATE {c}")).unwrap_or_default();
51            let sql = format!(
52                "ALTER TABLE {} MODIFY COLUMN {} LONGTEXT{collate} {null}",
53                quote(&table),
54                quote(&column),
55            );
56            rustango::sql::raw_execute_pool(pool, &sql, Vec::new()).await?;
57            changed += 1;
58        }
59        return Ok(changed);
60    }
61    let _ = pool;
62    Ok(0)
63}
64
65/// Columns the CMS matches exactly — URLs, slugs, hostnames, locale
66/// codes — which must compare case- and accent-sensitively, as they do on
67/// Postgres and SQLite.
68#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
69const IDENTITY_COLUMNS: &[(&str, &str)] = &[
70    ("cms_page", "url_path"),
71    ("cms_page", "slug"),
72    ("cms_redirect", "from_path"),
73    ("cms_snippet", "slug"),
74    ("cms_site", "hostname"),
75    ("cms_theme", "slug"),
76    ("cms_category", "slug"),
77    ("cms_taxonomy", "slug"),
78    ("cms_locale", "code"),
79];
80
81/// Case- and accent-sensitive collations that are not binary, best first:
82/// MySQL 8.0+, then MariaDB 10.10+.
83///
84/// A binary collation (`utf8mb4_bin`, `utf8mb4_0900_bin`) would compare
85/// exactly too, but the server then flags the column `BINARY` in every
86/// result set and sqlx refuses to decode it as a `String` ("not
87/// compatible with SQL type `VARBINARY`").
88#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
89const EXACT_COLLATIONS: &[&str] = &["utf8mb4_0900_as_cs", "utf8mb4_uca1400_as_cs"];
90
91/// The collation [`exact_identity_columns`] moves the identity columns to.
92#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
93#[derive(Debug, PartialEq, Eq)]
94enum Target {
95    /// A collation from [`EXACT_COLLATIONS`]: every identity column moves to it.
96    Exact(&'static str),
97    /// The server has none, so matching stays case-insensitive; only columns
98    /// an earlier boot made binary — undecodable — move back to this one.
99    Fallback(String),
100}
101
102#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
103impl Target {
104    /// Picks from the collations the server has, else falls back to the
105    /// schema's default when that is a non-binary utf8mb4 one.
106    fn pick(available: &[String], schema_default: Option<&str>) -> Self {
107        if let Some(c) = EXACT_COLLATIONS.iter().find(|c| available.iter().any(|a| a == *c)) {
108            return Self::Exact(c);
109        }
110        match schema_default {
111            Some(d) if d.starts_with("utf8mb4_") && !is_binary(d) => Self::Fallback(d.to_owned()),
112            _ => Self::Fallback("utf8mb4_general_ci".to_owned()),
113        }
114    }
115
116    fn name(&self) -> &str {
117        match self {
118            Self::Exact(c) => c,
119            Self::Fallback(c) => c,
120        }
121    }
122
123    /// Whether a column now on `current` has to be altered.
124    fn wants(&self, current: Option<&str>) -> bool {
125        match self {
126            Self::Exact(c) => current != Some(*c),
127            Self::Fallback(_) => current.is_some_and(is_binary),
128        }
129    }
130}
131
132#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
133fn is_binary(collation: &str) -> bool {
134    collation == "binary" || collation.ends_with("_bin")
135}
136
137/// On MySQL, give the CMS's identity columns a case- and accent-sensitive
138/// collation (`utf8mb4_0900_as_cs` on MySQL 8), keeping each column's
139/// type, nullability and default. Returns how many columns it changed; a
140/// no-op off MySQL and on a column already converted.
141///
142/// Also repairs columns an earlier version set to `utf8mb4_bin`, which
143/// made every page and admin screen fail to decode them. On a server with
144/// no such collation (MySQL 5.7, older MariaDB) matching stays
145/// case-insensitive and only those binary columns are moved back to the
146/// schema default.
147///
148/// Relaxes nothing that matters: a unique column that refused `Hero` next
149/// to `hero` now accepts both, as the other backends do.
150///
151/// # Errors
152/// Driver failures reading `information_schema` or altering a column.
153pub async fn exact_identity_columns(pool: &Pool) -> Result<usize, ExecError> {
154    #[cfg(feature = "mysql")]
155    if let Some(my) = pool.as_mysql() {
156        let listed = EXACT_COLLATIONS.iter().map(|c| format!("'{c}'")).collect::<Vec<_>>().join(", ");
157        let available: Vec<String> = rustango::sql::sqlx::query_scalar(&format!(
158            "SELECT CAST(COLLATION_NAME AS CHAR) FROM information_schema.COLLATIONS \
159             WHERE COLLATION_NAME IN ({listed})"
160        ))
161        .fetch_all(my)
162        .await
163        .map_err(ExecError::from)?;
164        let schema_default: Option<String> = rustango::sql::sqlx::query_scalar(
165            "SELECT CAST(DEFAULT_COLLATION_NAME AS CHAR) FROM information_schema.SCHEMATA \
166             WHERE SCHEMA_NAME = DATABASE()",
167        )
168        .fetch_optional(my)
169        .await
170        .map_err(ExecError::from)?;
171        let target = Target::pick(&available, schema_default.as_deref());
172        if let Target::Fallback(_) = target {
173            tracing::warn!(
174                target: "rustango_cms::seed",
175                "no case-sensitive utf8mb4 collation on this server; URL and slug matching stays case-insensitive"
176            );
177        }
178        let rows: Vec<(String, String, String, String, Option<String>, Option<String>)> =
179            rustango::sql::sqlx::query_as(
180                "SELECT CAST(TABLE_NAME AS CHAR), CAST(COLUMN_NAME AS CHAR), \
181                 CAST(COLUMN_TYPE AS CHAR), CAST(IS_NULLABLE AS CHAR), \
182                 CAST(COLUMN_DEFAULT AS CHAR), CAST(COLLATION_NAME AS CHAR) \
183                 FROM information_schema.COLUMNS \
184                 WHERE TABLE_SCHEMA = DATABASE() AND COLLATION_NAME IS NOT NULL",
185            )
186            .fetch_all(my)
187            .await
188            .map_err(ExecError::from)?;
189        let mut changed = 0;
190        for (table, column, column_type, nullable, default, collation) in rows {
191            if !IDENTITY_COLUMNS.contains(&(table.as_str(), column.as_str()))
192                || !target.wants(collation.as_deref())
193            {
194                continue;
195            }
196            let null = if nullable == "YES" { "NULL" } else { "NOT NULL" };
197            // A TEXT column takes no literal default on MySQL; a VARCHAR
198            // keeps the one it had.
199            let default = match default {
200                Some(d) if !column_type.contains("text") => format!(" DEFAULT '{}'", d.replace('\'', "''")),
201                _ => String::new(),
202            };
203            let sql = format!(
204                "ALTER TABLE {} MODIFY COLUMN {} {column_type} CHARACTER SET utf8mb4 COLLATE {} {null}{default}",
205                quote(&table),
206                quote(&column),
207                target.name(),
208            );
209            rustango::sql::raw_execute_pool(pool, &sql, Vec::new()).await?;
210            changed += 1;
211        }
212        return Ok(changed);
213    }
214    let _ = pool;
215    Ok(0)
216}
217
218/// `(table, column)` for every unbounded `String` field of a registered
219/// model — the fields the framework renders as `TEXT` on MySQL.
220#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
221fn unbounded_string_columns() -> std::collections::HashSet<(&'static str, &'static str)> {
222    inventory::iter::<rustango::core::ModelEntry>
223        .into_iter()
224        .flat_map(|entry| {
225            let table = entry.schema.table;
226            entry
227                .schema
228                .fields
229                .iter()
230                .filter(|f| matches!(f.ty, rustango::core::FieldType::String) && f.max_length.is_none())
231                .map(move |f| (table, f.column))
232        })
233        .collect()
234}
235
236#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
237fn quote(ident: &str) -> String {
238    format!("`{}`", ident.replace('`', "``"))
239}
240
241#[cfg(test)]
242mod tests {
243    use super::{unbounded_string_columns, Target};
244
245    fn names(list: &[&str]) -> Vec<String> {
246        list.iter().map(|s| (*s).to_owned()).collect()
247    }
248
249    #[test]
250    fn the_target_is_a_case_sensitive_collation_never_a_binary_one() {
251        let mysql8 = names(&["utf8mb4_0900_as_cs"]);
252        let target = Target::pick(&mysql8, Some("utf8mb4_0900_ai_ci"));
253        assert_eq!(target, Target::Exact("utf8mb4_0900_as_cs"));
254        assert!(target.wants(Some("utf8mb4_0900_ai_ci")), "the default is converted");
255        assert!(target.wants(Some("utf8mb4_bin")), "an earlier boot's binary column is repaired");
256        assert!(!target.wants(Some("utf8mb4_0900_as_cs")), "a converted column is left alone");
257
258        let mariadb = names(&["utf8mb4_uca1400_as_cs"]);
259        assert_eq!(Target::pick(&mariadb, None), Target::Exact("utf8mb4_uca1400_as_cs"));
260    }
261
262    #[test]
263    fn without_one_only_binary_columns_move_back() {
264        let target = Target::pick(&[], Some("utf8mb4_unicode_ci"));
265        assert_eq!(target, Target::Fallback("utf8mb4_unicode_ci".to_owned()));
266        assert!(target.wants(Some("utf8mb4_bin")));
267        assert!(!target.wants(Some("utf8mb4_general_ci")), "a decodable column is left alone");
268        for default in [Some("utf8mb4_bin"), Some("latin1_swedish_ci"), None] {
269            assert_eq!(
270                Target::pick(&[], default),
271                Target::Fallback("utf8mb4_general_ci".to_owned()),
272                "{default:?} is not a usable fallback"
273            );
274        }
275    }
276
277    #[test]
278    fn the_editor_content_columns_are_covered() {
279        let cols = unbounded_string_columns();
280        for want in [
281            ("cms_translation", "value"),
282            ("cms_snippet_translation", "value"),
283            ("cms_snippet", "body_markdown"),
284            ("cms_error_page", "body"),
285        ] {
286            assert!(cols.contains(&want), "{want:?} not found among unbounded strings");
287        }
288        assert!(!cols.contains(&("cms_page", "slug")), "bounded columns are left alone");
289    }
290}