1use rustango::sql::{ExecError, Pool};
17
18pub 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 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#[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#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
89const EXACT_COLLATIONS: &[&str] = &["utf8mb4_0900_as_cs", "utf8mb4_uca1400_as_cs"];
90
91#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
93#[derive(Debug, PartialEq, Eq)]
94enum Target {
95 Exact(&'static str),
97 Fallback(String),
100}
101
102#[cfg_attr(not(feature = "mysql"), allow(dead_code))]
103impl Target {
104 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 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
137pub 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 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#[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}