prax_query/
sequence.rs

1//! Database sequence definitions and operations.
2//!
3//! This module provides types for defining and manipulating database sequences
4//! across different database backends.
5//!
6//! # Supported Features
7//!
8//! | Feature                 | PostgreSQL | MySQL | SQLite | MSSQL | MongoDB |
9//! |-------------------------|------------|-------|--------|-------|---------|
10//! | Sequences               | ✅         | ❌    | ❌     | ✅    | ❌*     |
11//! | Custom start/increment  | ✅         | ✅    | ❌     | ✅    | ✅*     |
12//! | Sequence manipulation   | ✅         | ❌    | ❌     | ✅    | ✅*     |
13//! | Auto-increment pattern  | ✅         | ✅    | ✅     | ✅    | ✅      |
14//!
15//! > *MongoDB uses counter collections with `findAndModify`
16//!
17//! # Example Usage
18//!
19//! ```rust,ignore
20//! use prax_query::sequence::{Sequence, SequenceBuilder};
21//!
22//! // Define a sequence
23//! let seq = Sequence::builder("order_number")
24//!     .start(1000)
25//!     .increment(1)
26//!     .min_value(1)
27//!     .max_value(i64::MAX)
28//!     .cycle(false)
29//!     .cache(20)
30//!     .build();
31//!
32//! // Generate SQL
33//! let create_sql = seq.to_postgres_create_sql();
34//!
35//! // Get next value
36//! let next_val_sql = seq.nextval_sql(DatabaseType::PostgreSQL);
37//! ```
38
39use std::borrow::Cow;
40
41use serde::{Deserialize, Serialize};
42
43use crate::error::{QueryError, QueryResult};
44use crate::sql::DatabaseType;
45
46/// A database sequence definition.
47#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
48pub struct Sequence {
49    /// Sequence name.
50    pub name: String,
51    /// Schema name (optional).
52    pub schema: Option<String>,
53    /// Starting value.
54    pub start: i64,
55    /// Increment value.
56    pub increment: i64,
57    /// Minimum value (None for no minimum).
58    pub min_value: Option<i64>,
59    /// Maximum value (None for no maximum).
60    pub max_value: Option<i64>,
61    /// Whether the sequence cycles when reaching max/min.
62    pub cycle: bool,
63    /// Number of values to cache (for performance).
64    pub cache: Option<i64>,
65    /// Whether the sequence is owned by a column.
66    pub owned_by: Option<OwnedBy>,
67    /// Optional comment/description.
68    pub comment: Option<String>,
69}
70
71/// Column ownership for a sequence.
72#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
73pub struct OwnedBy {
74    /// Table name.
75    pub table: String,
76    /// Column name.
77    pub column: String,
78}
79
80impl OwnedBy {
81    /// Create a new owned by specification.
82    pub fn new(table: impl Into<String>, column: impl Into<String>) -> Self {
83        Self {
84            table: table.into(),
85            column: column.into(),
86        }
87    }
88}
89
90impl Sequence {
91    /// Create a new sequence builder.
92    pub fn builder(name: impl Into<String>) -> SequenceBuilder {
93        SequenceBuilder::new(name)
94    }
95
96    /// Get the fully qualified sequence name.
97    pub fn qualified_name(&self) -> Cow<'_, str> {
98        match &self.schema {
99            Some(schema) => Cow::Owned(format!("{}.{}", schema, self.name)),
100            None => Cow::Borrowed(&self.name),
101        }
102    }
103
104    /// Generate PostgreSQL CREATE SEQUENCE SQL.
105    pub fn to_postgres_create_sql(&self) -> String {
106        let mut sql = String::with_capacity(128);
107
108        sql.push_str("CREATE SEQUENCE ");
109        sql.push_str(&self.qualified_name());
110
111        // AS bigint is implicit
112        sql.push_str("\n    INCREMENT BY ");
113        sql.push_str(&self.increment.to_string());
114
115        if let Some(min) = self.min_value {
116            sql.push_str("\n    MINVALUE ");
117            sql.push_str(&min.to_string());
118        } else {
119            sql.push_str("\n    NO MINVALUE");
120        }
121
122        if let Some(max) = self.max_value {
123            sql.push_str("\n    MAXVALUE ");
124            sql.push_str(&max.to_string());
125        } else {
126            sql.push_str("\n    NO MAXVALUE");
127        }
128
129        sql.push_str("\n    START WITH ");
130        sql.push_str(&self.start.to_string());
131
132        if let Some(cache) = self.cache {
133            sql.push_str("\n    CACHE ");
134            sql.push_str(&cache.to_string());
135        }
136
137        if self.cycle {
138            sql.push_str("\n    CYCLE");
139        } else {
140            sql.push_str("\n    NO CYCLE");
141        }
142
143        if let Some(ref owned) = self.owned_by {
144            sql.push_str("\n    OWNED BY ");
145            sql.push_str(&owned.table);
146            sql.push('.');
147            sql.push_str(&owned.column);
148        }
149
150        sql.push(';');
151
152        sql
153    }
154
155    /// Generate MSSQL CREATE SEQUENCE SQL.
156    pub fn to_mssql_create_sql(&self) -> String {
157        let mut sql = String::with_capacity(128);
158
159        sql.push_str("CREATE SEQUENCE ");
160        sql.push_str(&self.qualified_name());
161        sql.push_str(" AS BIGINT");
162
163        sql.push_str("\n    START WITH ");
164        sql.push_str(&self.start.to_string());
165
166        sql.push_str("\n    INCREMENT BY ");
167        sql.push_str(&self.increment.to_string());
168
169        if let Some(min) = self.min_value {
170            sql.push_str("\n    MINVALUE ");
171            sql.push_str(&min.to_string());
172        } else {
173            sql.push_str("\n    NO MINVALUE");
174        }
175
176        if let Some(max) = self.max_value {
177            sql.push_str("\n    MAXVALUE ");
178            sql.push_str(&max.to_string());
179        } else {
180            sql.push_str("\n    NO MAXVALUE");
181        }
182
183        if let Some(cache) = self.cache {
184            sql.push_str("\n    CACHE ");
185            sql.push_str(&cache.to_string());
186        } else {
187            sql.push_str("\n    NO CACHE");
188        }
189
190        if self.cycle {
191            sql.push_str("\n    CYCLE");
192        } else {
193            sql.push_str("\n    NO CYCLE");
194        }
195
196        sql.push(';');
197
198        sql
199    }
200
201    /// Generate CREATE SEQUENCE SQL for the specified database type.
202    pub fn to_create_sql(&self, db_type: DatabaseType) -> QueryResult<String> {
203        match db_type {
204            DatabaseType::PostgreSQL => Ok(self.to_postgres_create_sql()),
205            DatabaseType::MSSQL => Ok(self.to_mssql_create_sql()),
206            DatabaseType::MySQL => Err(QueryError::unsupported(
207                "MySQL does not support sequences. Use AUTO_INCREMENT columns instead.",
208            )),
209            DatabaseType::SQLite => Err(QueryError::unsupported(
210                "SQLite does not support sequences. Use AUTOINCREMENT columns instead.",
211            )),
212        }
213    }
214
215    /// Generate DROP SEQUENCE SQL.
216    pub fn to_drop_sql(&self, db_type: DatabaseType) -> QueryResult<String> {
217        match db_type {
218            DatabaseType::PostgreSQL => Ok(format!(
219                "DROP SEQUENCE IF EXISTS {} CASCADE;",
220                self.qualified_name()
221            )),
222            DatabaseType::MSSQL => Ok(format!(
223                "DROP SEQUENCE IF EXISTS {};",
224                self.qualified_name()
225            )),
226            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
227                "This database does not support sequences.",
228            )),
229        }
230    }
231
232    /// Generate ALTER SEQUENCE SQL.
233    pub fn to_alter_sql(&self, db_type: DatabaseType) -> QueryResult<String> {
234        match db_type {
235            DatabaseType::PostgreSQL => {
236                let mut sql = format!("ALTER SEQUENCE {}", self.qualified_name());
237
238                sql.push_str(&format!("\n    INCREMENT BY {}", self.increment));
239
240                if let Some(min) = self.min_value {
241                    sql.push_str(&format!("\n    MINVALUE {}", min));
242                }
243
244                if let Some(max) = self.max_value {
245                    sql.push_str(&format!("\n    MAXVALUE {}", max));
246                }
247
248                if let Some(cache) = self.cache {
249                    sql.push_str(&format!("\n    CACHE {}", cache));
250                }
251
252                if self.cycle {
253                    sql.push_str("\n    CYCLE");
254                } else {
255                    sql.push_str("\n    NO CYCLE");
256                }
257
258                sql.push(';');
259                Ok(sql)
260            }
261            DatabaseType::MSSQL => {
262                let mut sql = format!("ALTER SEQUENCE {}", self.qualified_name());
263
264                sql.push_str(&format!("\n    INCREMENT BY {}", self.increment));
265
266                if let Some(min) = self.min_value {
267                    sql.push_str(&format!("\n    MINVALUE {}", min));
268                }
269
270                if let Some(max) = self.max_value {
271                    sql.push_str(&format!("\n    MAXVALUE {}", max));
272                }
273
274                if let Some(cache) = self.cache {
275                    sql.push_str(&format!("\n    CACHE {}", cache));
276                } else {
277                    sql.push_str("\n    NO CACHE");
278                }
279
280                if self.cycle {
281                    sql.push_str("\n    CYCLE");
282                } else {
283                    sql.push_str("\n    NO CYCLE");
284                }
285
286                sql.push(';');
287                Ok(sql)
288            }
289            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
290                "This database does not support sequences.",
291            )),
292        }
293    }
294
295    /// Generate SQL to restart the sequence at a specific value.
296    pub fn restart_sql(&self, value: i64, db_type: DatabaseType) -> QueryResult<String> {
297        match db_type {
298            DatabaseType::PostgreSQL => Ok(format!(
299                "ALTER SEQUENCE {} RESTART WITH {};",
300                self.qualified_name(),
301                value
302            )),
303            DatabaseType::MSSQL => Ok(format!(
304                "ALTER SEQUENCE {} RESTART WITH {};",
305                self.qualified_name(),
306                value
307            )),
308            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
309                "This database does not support sequences.",
310            )),
311        }
312    }
313}
314
315/// Builder for creating sequences.
316#[derive(Debug, Clone)]
317pub struct SequenceBuilder {
318    name: String,
319    schema: Option<String>,
320    start: i64,
321    increment: i64,
322    min_value: Option<i64>,
323    max_value: Option<i64>,
324    cycle: bool,
325    cache: Option<i64>,
326    owned_by: Option<OwnedBy>,
327    comment: Option<String>,
328}
329
330impl SequenceBuilder {
331    /// Create a new sequence builder.
332    pub fn new(name: impl Into<String>) -> Self {
333        Self {
334            name: name.into(),
335            schema: None,
336            start: 1,
337            increment: 1,
338            min_value: None,
339            max_value: None,
340            cycle: false,
341            cache: None,
342            owned_by: None,
343            comment: None,
344        }
345    }
346
347    /// Set the schema.
348    pub fn schema(mut self, schema: impl Into<String>) -> Self {
349        self.schema = Some(schema.into());
350        self
351    }
352
353    /// Set the starting value.
354    pub fn start(mut self, value: i64) -> Self {
355        self.start = value;
356        self
357    }
358
359    /// Set the increment value.
360    pub fn increment(mut self, value: i64) -> Self {
361        self.increment = value;
362        self
363    }
364
365    /// Alias for increment.
366    pub fn increment_by(self, value: i64) -> Self {
367        self.increment(value)
368    }
369
370    /// Set the minimum value.
371    pub fn min_value(mut self, value: i64) -> Self {
372        self.min_value = Some(value);
373        self
374    }
375
376    /// Set no minimum value.
377    pub fn no_min_value(mut self) -> Self {
378        self.min_value = None;
379        self
380    }
381
382    /// Set the maximum value.
383    pub fn max_value(mut self, value: i64) -> Self {
384        self.max_value = Some(value);
385        self
386    }
387
388    /// Set no maximum value.
389    pub fn no_max_value(mut self) -> Self {
390        self.max_value = None;
391        self
392    }
393
394    /// Set whether the sequence cycles.
395    pub fn cycle(mut self, cycle: bool) -> Self {
396        self.cycle = cycle;
397        self
398    }
399
400    /// Set the cache size.
401    pub fn cache(mut self, size: i64) -> Self {
402        self.cache = Some(size);
403        self
404    }
405
406    /// Set no caching.
407    pub fn no_cache(mut self) -> Self {
408        self.cache = None;
409        self
410    }
411
412    /// Set the column that owns this sequence.
413    pub fn owned_by(mut self, table: impl Into<String>, column: impl Into<String>) -> Self {
414        self.owned_by = Some(OwnedBy::new(table, column));
415        self
416    }
417
418    /// Add a comment/description.
419    pub fn comment(mut self, comment: impl Into<String>) -> Self {
420        self.comment = Some(comment.into());
421        self
422    }
423
424    /// Build the sequence.
425    pub fn build(self) -> Sequence {
426        Sequence {
427            name: self.name,
428            schema: self.schema,
429            start: self.start,
430            increment: self.increment,
431            min_value: self.min_value,
432            max_value: self.max_value,
433            cycle: self.cycle,
434            cache: self.cache,
435            owned_by: self.owned_by,
436            comment: self.comment,
437        }
438    }
439}
440
441/// Sequence operations for retrieving and manipulating sequence values.
442pub mod ops {
443    use super::*;
444
445    /// Generate SQL for getting the next value from a sequence.
446    pub fn nextval(sequence_name: &str, db_type: DatabaseType) -> QueryResult<String> {
447        match db_type {
448            DatabaseType::PostgreSQL => Ok(format!("SELECT nextval('{}')", sequence_name)),
449            DatabaseType::MSSQL => Ok(format!("SELECT NEXT VALUE FOR {}", sequence_name)),
450            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
451                "This database does not support sequences.",
452            )),
453        }
454    }
455
456    /// Generate SQL for getting the current value of a sequence (last retrieved value in session).
457    pub fn currval(sequence_name: &str, db_type: DatabaseType) -> QueryResult<String> {
458        match db_type {
459            DatabaseType::PostgreSQL => Ok(format!("SELECT currval('{}')", sequence_name)),
460            DatabaseType::MSSQL => {
461                // MSSQL doesn't have a direct equivalent to currval
462                // You need to use a variable or sys.sequences
463                Ok(format!(
464                    "SELECT current_value FROM sys.sequences WHERE name = '{}'",
465                    sequence_name
466                ))
467            }
468            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
469                "This database does not support sequences.",
470            )),
471        }
472    }
473
474    /// Generate SQL for setting a sequence to a specific value.
475    pub fn setval(
476        sequence_name: &str,
477        value: i64,
478        is_called: bool,
479        db_type: DatabaseType,
480    ) -> QueryResult<String> {
481        match db_type {
482            DatabaseType::PostgreSQL => Ok(format!(
483                "SELECT setval('{}', {}, {})",
484                sequence_name, value, is_called
485            )),
486            DatabaseType::MSSQL => {
487                // MSSQL uses ALTER SEQUENCE ... RESTART WITH
488                Ok(format!(
489                    "ALTER SEQUENCE {} RESTART WITH {}",
490                    sequence_name, value
491                ))
492            }
493            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
494                "This database does not support sequences.",
495            )),
496        }
497    }
498
499    /// Generate SQL for getting the last inserted ID (auto-increment value).
500    ///
501    /// This works for databases that don't have sequences but use auto-increment.
502    pub fn last_insert_id(db_type: DatabaseType) -> String {
503        match db_type {
504            DatabaseType::PostgreSQL => "SELECT lastval()".to_string(),
505            DatabaseType::MySQL => "SELECT LAST_INSERT_ID()".to_string(),
506            DatabaseType::SQLite => "SELECT last_insert_rowid()".to_string(),
507            DatabaseType::MSSQL => "SELECT SCOPE_IDENTITY()".to_string(),
508        }
509    }
510
511    /// Generate SQL expression for using a sequence as a default value.
512    pub fn default_nextval(sequence_name: &str, db_type: DatabaseType) -> QueryResult<String> {
513        match db_type {
514            DatabaseType::PostgreSQL => Ok(format!("nextval('{}')", sequence_name)),
515            DatabaseType::MSSQL => Ok(format!("NEXT VALUE FOR {}", sequence_name)),
516            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
517                "This database does not support sequences in default expressions.",
518            )),
519        }
520    }
521}
522
523/// MongoDB counter-based sequence pattern.
524///
525/// MongoDB doesn't have native sequences, but you can implement them using
526/// a counter collection with `findAndModify`.
527pub mod mongodb {
528    use serde::{Deserialize, Serialize};
529
530    /// A counter-based sequence for MongoDB.
531    #[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
532    pub struct Counter {
533        /// Counter/sequence name.
534        pub name: String,
535        /// Current value.
536        pub value: i64,
537        /// Increment amount.
538        pub increment: i64,
539    }
540
541    impl Counter {
542        /// Create a new counter.
543        pub fn new(name: impl Into<String>) -> Self {
544            Self {
545                name: name.into(),
546                value: 0,
547                increment: 1,
548            }
549        }
550
551        /// Set the increment value.
552        pub fn with_increment(mut self, increment: i64) -> Self {
553            self.increment = increment;
554            self
555        }
556
557        /// Set the initial value.
558        pub fn with_initial_value(mut self, value: i64) -> Self {
559            self.value = value;
560            self
561        }
562    }
563
564    /// Builder for MongoDB counter operations.
565    #[derive(Debug, Clone)]
566    pub struct CounterBuilder {
567        /// Collection name for counters.
568        pub collection: String,
569        /// Counter name.
570        pub name: String,
571        /// Increment amount.
572        pub increment: i64,
573    }
574
575    impl CounterBuilder {
576        /// Create a new counter builder.
577        pub fn new(name: impl Into<String>) -> Self {
578            Self {
579                collection: "counters".to_string(),
580                name: name.into(),
581                increment: 1,
582            }
583        }
584
585        /// Set the collection name for counters.
586        pub fn collection(mut self, collection: impl Into<String>) -> Self {
587            self.collection = collection.into();
588            self
589        }
590
591        /// Set the increment amount.
592        pub fn increment(mut self, increment: i64) -> Self {
593            self.increment = increment;
594            self
595        }
596
597        /// Get the findAndModify command document for getting the next value.
598        ///
599        /// Returns a JSON-like structure that can be used with MongoDB driver.
600        pub fn next_value_command(&self) -> serde_json::Value {
601            serde_json::json!({
602                "findAndModify": &self.collection,
603                "query": { "_id": &self.name },
604                "update": { "$inc": { "seq": self.increment } },
605                "new": true,
606                "upsert": true
607            })
608        }
609
610        /// Get the aggregation pipeline stage for incrementing the counter.
611        pub fn increment_pipeline(&self) -> Vec<serde_json::Value> {
612            vec![
613                serde_json::json!({
614                    "$match": { "_id": &self.name }
615                }),
616                serde_json::json!({
617                    "$set": { "seq": { "$add": ["$seq", self.increment] } }
618                }),
619            ]
620        }
621
622        /// Get the document for initializing a counter.
623        pub fn init_document(&self, start_value: i64) -> serde_json::Value {
624            serde_json::json!({
625                "_id": &self.name,
626                "seq": start_value
627            })
628        }
629
630        /// Get the update document for resetting a counter.
631        pub fn reset_document(&self, value: i64) -> serde_json::Value {
632            serde_json::json!({
633                "$set": { "seq": value }
634            })
635        }
636    }
637
638    /// Helper function to create a counter builder.
639    pub fn counter(name: impl Into<String>) -> CounterBuilder {
640        CounterBuilder::new(name)
641    }
642}
643
644/// Auto-increment column helpers for databases without sequence support.
645pub mod auto_increment {
646    use super::*;
647
648    /// Generate SQL for creating an auto-increment column.
649    pub fn column_definition(
650        column_name: &str,
651        db_type: DatabaseType,
652        start: Option<i64>,
653    ) -> String {
654        match db_type {
655            DatabaseType::PostgreSQL => {
656                // PostgreSQL uses SERIAL or GENERATED ... AS IDENTITY
657                if let Some(start_val) = start {
658                    format!(
659                        "{} BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH {})",
660                        column_name, start_val
661                    )
662                } else {
663                    format!("{} BIGSERIAL", column_name)
664                }
665            }
666            DatabaseType::MySQL => {
667                if let Some(start_val) = start {
668                    format!(
669                        "{} BIGINT AUTO_INCREMENT /* Start: {} */",
670                        column_name, start_val
671                    )
672                } else {
673                    format!("{} BIGINT AUTO_INCREMENT", column_name)
674                }
675            }
676            DatabaseType::SQLite => {
677                // SQLite uses INTEGER PRIMARY KEY for auto-increment
678                format!("{} INTEGER PRIMARY KEY AUTOINCREMENT", column_name)
679            }
680            DatabaseType::MSSQL => {
681                if let Some(start_val) = start {
682                    format!("{} BIGINT IDENTITY({}, 1)", column_name, start_val)
683                } else {
684                    format!("{} BIGINT IDENTITY(1, 1)", column_name)
685                }
686            }
687        }
688    }
689
690    /// Generate SQL for setting the auto-increment start value for a table.
691    pub fn set_start_value(
692        table_name: &str,
693        value: i64,
694        db_type: DatabaseType,
695    ) -> QueryResult<String> {
696        match db_type {
697            DatabaseType::PostgreSQL => {
698                // For IDENTITY columns
699                Ok(format!(
700                    "ALTER TABLE {} ALTER COLUMN id RESTART WITH {};",
701                    table_name, value
702                ))
703            }
704            DatabaseType::MySQL => Ok(format!(
705                "ALTER TABLE {} AUTO_INCREMENT = {};",
706                table_name, value
707            )),
708            DatabaseType::SQLite => {
709                // SQLite uses sqlite_sequence table
710                Ok(format!(
711                    "UPDATE sqlite_sequence SET seq = {} WHERE name = '{}';",
712                    value - 1,
713                    table_name
714                ))
715            }
716            DatabaseType::MSSQL => {
717                // MSSQL requires DBCC CHECKIDENT
718                Ok(format!(
719                    "DBCC CHECKIDENT ('{}', RESEED, {});",
720                    table_name,
721                    value - 1
722                ))
723            }
724        }
725    }
726
727    /// Generate SQL to get the current auto-increment value for a table.
728    pub fn get_current_value(table_name: &str, db_type: DatabaseType) -> String {
729        match db_type {
730            DatabaseType::PostgreSQL => {
731                format!(
732                    "SELECT last_value FROM pg_sequences WHERE sequencename LIKE '%{}%';",
733                    table_name
734                )
735            }
736            DatabaseType::MySQL => {
737                format!(
738                    "SELECT AUTO_INCREMENT FROM information_schema.TABLES \
739                     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '{}';",
740                    table_name
741                )
742            }
743            DatabaseType::SQLite => {
744                format!(
745                    "SELECT seq FROM sqlite_sequence WHERE name = '{}';",
746                    table_name
747                )
748            }
749            DatabaseType::MSSQL => {
750                format!("SELECT IDENT_CURRENT('{}');", table_name)
751            }
752        }
753    }
754}
755
756/// Pre-built sequence patterns for common use cases.
757pub mod patterns {
758    use super::*;
759
760    /// Create an order number sequence starting at 1000.
761    pub fn order_number(schema: Option<&str>) -> Sequence {
762        let mut builder = Sequence::builder("order_number_seq")
763            .start(1000)
764            .increment(1)
765            .min_value(1)
766            .cache(20);
767
768        if let Some(s) = schema {
769            builder = builder.schema(s);
770        }
771
772        builder.build()
773    }
774
775    /// Create an invoice number sequence with yearly reset pattern.
776    pub fn invoice_number(schema: Option<&str>, year: i32) -> Sequence {
777        let mut builder = Sequence::builder(format!("invoice_{}_seq", year))
778            .start(1)
779            .increment(1)
780            .min_value(1)
781            .cache(10);
782
783        if let Some(s) = schema {
784            builder = builder.schema(s);
785        }
786
787        builder.build()
788    }
789
790    /// Create a high-performance ID sequence with large cache.
791    pub fn high_volume_id(name: &str, schema: Option<&str>) -> Sequence {
792        let mut builder = Sequence::builder(name)
793            .start(1)
794            .increment(1)
795            .min_value(1)
796            .cache(1000); // Large cache for high-volume inserts
797
798        if let Some(s) = schema {
799            builder = builder.schema(s);
800        }
801
802        builder.build()
803    }
804
805    /// Create a cycling sequence for round-robin distribution.
806    pub fn round_robin(name: &str, max: i64, schema: Option<&str>) -> Sequence {
807        let mut builder = Sequence::builder(name)
808            .start(1)
809            .increment(1)
810            .min_value(1)
811            .max_value(max)
812            .cycle(true)
813            .cache(10);
814
815        if let Some(s) = schema {
816            builder = builder.schema(s);
817        }
818
819        builder.build()
820    }
821
822    /// Create a negative sequence (counts down).
823    pub fn countdown(name: &str, start: i64, schema: Option<&str>) -> Sequence {
824        let mut builder = Sequence::builder(name)
825            .start(start)
826            .increment(-1)
827            .min_value(0)
828            .no_max_value();
829
830        if let Some(s) = schema {
831            builder = builder.schema(s);
832        }
833
834        builder.build()
835    }
836}
837
838#[cfg(test)]
839mod tests {
840    use super::*;
841
842    #[test]
843    fn test_sequence_builder() {
844        let seq = Sequence::builder("order_seq")
845            .start(1000)
846            .increment(1)
847            .min_value(1)
848            .max_value(999999)
849            .cache(20)
850            .build();
851
852        assert_eq!(seq.name, "order_seq");
853        assert_eq!(seq.start, 1000);
854        assert_eq!(seq.increment, 1);
855        assert_eq!(seq.min_value, Some(1));
856        assert_eq!(seq.max_value, Some(999999));
857        assert_eq!(seq.cache, Some(20));
858        assert!(!seq.cycle);
859    }
860
861    #[test]
862    fn test_postgres_create_sql() {
863        let seq = Sequence::builder("order_seq")
864            .schema("public")
865            .start(1000)
866            .increment(1)
867            .min_value(1)
868            .cache(20)
869            .build();
870
871        let sql = seq.to_postgres_create_sql();
872        assert!(sql.contains("CREATE SEQUENCE public.order_seq"));
873        assert!(sql.contains("INCREMENT BY 1"));
874        assert!(sql.contains("MINVALUE 1"));
875        assert!(sql.contains("START WITH 1000"));
876        assert!(sql.contains("CACHE 20"));
877        assert!(sql.contains("NO CYCLE"));
878    }
879
880    #[test]
881    fn test_mssql_create_sql() {
882        let seq = Sequence::builder("order_seq")
883            .schema("dbo")
884            .start(1000)
885            .increment(1)
886            .build();
887
888        let sql = seq.to_mssql_create_sql();
889        assert!(sql.contains("CREATE SEQUENCE dbo.order_seq"));
890        assert!(sql.contains("AS BIGINT"));
891        assert!(sql.contains("START WITH 1000"));
892        assert!(sql.contains("INCREMENT BY 1"));
893    }
894
895    #[test]
896    fn test_mysql_not_supported() {
897        let seq = Sequence::builder("test").build();
898        let result = seq.to_create_sql(DatabaseType::MySQL);
899        assert!(result.is_err());
900    }
901
902    #[test]
903    fn test_sqlite_not_supported() {
904        let seq = Sequence::builder("test").build();
905        let result = seq.to_create_sql(DatabaseType::SQLite);
906        assert!(result.is_err());
907    }
908
909    #[test]
910    fn test_drop_sql() {
911        let seq = Sequence::builder("order_seq").build();
912
913        let pg_drop = seq.to_drop_sql(DatabaseType::PostgreSQL).unwrap();
914        assert_eq!(pg_drop, "DROP SEQUENCE IF EXISTS order_seq CASCADE;");
915
916        let mssql_drop = seq.to_drop_sql(DatabaseType::MSSQL).unwrap();
917        assert_eq!(mssql_drop, "DROP SEQUENCE IF EXISTS order_seq;");
918    }
919
920    #[test]
921    fn test_restart_sql() {
922        let seq = Sequence::builder("order_seq").build();
923
924        let pg_restart = seq.restart_sql(5000, DatabaseType::PostgreSQL).unwrap();
925        assert_eq!(pg_restart, "ALTER SEQUENCE order_seq RESTART WITH 5000;");
926
927        let mssql_restart = seq.restart_sql(5000, DatabaseType::MSSQL).unwrap();
928        assert_eq!(mssql_restart, "ALTER SEQUENCE order_seq RESTART WITH 5000;");
929    }
930
931    #[test]
932    fn test_cycle_sequence() {
933        let seq = Sequence::builder("round_robin")
934            .start(1)
935            .max_value(10)
936            .cycle(true)
937            .build();
938
939        let sql = seq.to_postgres_create_sql();
940        assert!(sql.contains("MAXVALUE 10"));
941        assert!(sql.contains("CYCLE"));
942        assert!(!sql.contains("NO CYCLE"));
943    }
944
945    #[test]
946    fn test_owned_by() {
947        let seq = Sequence::builder("users_id_seq")
948            .owned_by("users", "id")
949            .build();
950
951        let sql = seq.to_postgres_create_sql();
952        assert!(sql.contains("OWNED BY users.id"));
953    }
954
955    mod ops_tests {
956        use super::super::ops;
957        use super::*;
958
959        #[test]
960        fn test_nextval() {
961            let pg = ops::nextval("order_seq", DatabaseType::PostgreSQL).unwrap();
962            assert_eq!(pg, "SELECT nextval('order_seq')");
963
964            let mssql = ops::nextval("order_seq", DatabaseType::MSSQL).unwrap();
965            assert_eq!(mssql, "SELECT NEXT VALUE FOR order_seq");
966        }
967
968        #[test]
969        fn test_currval() {
970            let pg = ops::currval("order_seq", DatabaseType::PostgreSQL).unwrap();
971            assert_eq!(pg, "SELECT currval('order_seq')");
972        }
973
974        #[test]
975        fn test_setval() {
976            let pg = ops::setval("order_seq", 1000, true, DatabaseType::PostgreSQL).unwrap();
977            assert_eq!(pg, "SELECT setval('order_seq', 1000, true)");
978
979            let mssql = ops::setval("order_seq", 1000, true, DatabaseType::MSSQL).unwrap();
980            assert_eq!(mssql, "ALTER SEQUENCE order_seq RESTART WITH 1000");
981        }
982
983        #[test]
984        fn test_last_insert_id() {
985            assert_eq!(
986                ops::last_insert_id(DatabaseType::PostgreSQL),
987                "SELECT lastval()"
988            );
989            assert_eq!(
990                ops::last_insert_id(DatabaseType::MySQL),
991                "SELECT LAST_INSERT_ID()"
992            );
993            assert_eq!(
994                ops::last_insert_id(DatabaseType::SQLite),
995                "SELECT last_insert_rowid()"
996            );
997            assert_eq!(
998                ops::last_insert_id(DatabaseType::MSSQL),
999                "SELECT SCOPE_IDENTITY()"
1000            );
1001        }
1002
1003        #[test]
1004        fn test_default_nextval() {
1005            let pg = ops::default_nextval("order_seq", DatabaseType::PostgreSQL).unwrap();
1006            assert_eq!(pg, "nextval('order_seq')");
1007
1008            let mssql = ops::default_nextval("order_seq", DatabaseType::MSSQL).unwrap();
1009            assert_eq!(mssql, "NEXT VALUE FOR order_seq");
1010        }
1011    }
1012
1013    mod mongodb_tests {
1014        use super::super::mongodb::*;
1015
1016        #[test]
1017        fn test_counter_builder() {
1018            let builder = counter("order_number").increment(1);
1019            let cmd = builder.next_value_command();
1020
1021            assert_eq!(cmd["findAndModify"], "counters");
1022            assert_eq!(cmd["query"]["_id"], "order_number");
1023            assert_eq!(cmd["update"]["$inc"]["seq"], 1);
1024            assert_eq!(cmd["upsert"], true);
1025        }
1026
1027        #[test]
1028        fn test_custom_collection() {
1029            let builder = counter("invoice_number").collection("sequences");
1030            let cmd = builder.next_value_command();
1031
1032            assert_eq!(cmd["findAndModify"], "sequences");
1033        }
1034
1035        #[test]
1036        fn test_init_document() {
1037            let builder = counter("order_number");
1038            let doc = builder.init_document(1000);
1039
1040            assert_eq!(doc["_id"], "order_number");
1041            assert_eq!(doc["seq"], 1000);
1042        }
1043
1044        #[test]
1045        fn test_reset_document() {
1046            let builder = counter("order_number");
1047            let doc = builder.reset_document(5000);
1048
1049            assert_eq!(doc["$set"]["seq"], 5000);
1050        }
1051    }
1052
1053    mod auto_increment_tests {
1054        use super::super::auto_increment;
1055        use super::*;
1056
1057        #[test]
1058        fn test_column_definition() {
1059            let pg = auto_increment::column_definition("id", DatabaseType::PostgreSQL, None);
1060            assert_eq!(pg, "id BIGSERIAL");
1061
1062            let pg_start =
1063                auto_increment::column_definition("id", DatabaseType::PostgreSQL, Some(1000));
1064            assert!(pg_start.contains("START WITH 1000"));
1065
1066            let mysql = auto_increment::column_definition("id", DatabaseType::MySQL, None);
1067            assert!(mysql.contains("AUTO_INCREMENT"));
1068
1069            let sqlite = auto_increment::column_definition("id", DatabaseType::SQLite, None);
1070            assert!(sqlite.contains("INTEGER PRIMARY KEY AUTOINCREMENT"));
1071
1072            let mssql = auto_increment::column_definition("id", DatabaseType::MSSQL, Some(1000));
1073            assert!(mssql.contains("IDENTITY(1000, 1)"));
1074        }
1075
1076        #[test]
1077        fn test_set_start_value() {
1078            let mysql =
1079                auto_increment::set_start_value("orders", 1000, DatabaseType::MySQL).unwrap();
1080            assert_eq!(mysql, "ALTER TABLE orders AUTO_INCREMENT = 1000;");
1081
1082            let mssql =
1083                auto_increment::set_start_value("orders", 1000, DatabaseType::MSSQL).unwrap();
1084            assert!(mssql.contains("DBCC CHECKIDENT"));
1085        }
1086    }
1087
1088    mod patterns_tests {
1089        use super::super::patterns;
1090
1091        #[test]
1092        fn test_order_number() {
1093            let seq = patterns::order_number(Some("sales"));
1094            assert_eq!(seq.start, 1000);
1095            assert_eq!(seq.schema, Some("sales".to_string()));
1096        }
1097
1098        #[test]
1099        fn test_round_robin() {
1100            let seq = patterns::round_robin("worker_queue", 10, None);
1101            assert!(seq.cycle);
1102            assert_eq!(seq.max_value, Some(10));
1103        }
1104
1105        #[test]
1106        fn test_countdown() {
1107            let seq = patterns::countdown("tickets", 100, None);
1108            assert_eq!(seq.start, 100);
1109            assert_eq!(seq.increment, -1);
1110            assert_eq!(seq.min_value, Some(0));
1111        }
1112    }
1113}
prax_query/sequence.rs

prax_query/
sequence.rs
