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!(
450                "SELECT NEXT VALUE FOR {}",
451                sequence_name
452            )),
453            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
454                "This database does not support sequences.",
455            )),
456        }
457    }
458
459    /// Generate SQL for getting the current value of a sequence (last retrieved value in session).
460    pub fn currval(sequence_name: &str, db_type: DatabaseType) -> QueryResult<String> {
461        match db_type {
462            DatabaseType::PostgreSQL => Ok(format!("SELECT currval('{}')", sequence_name)),
463            DatabaseType::MSSQL => {
464                // MSSQL doesn't have a direct equivalent to currval
465                // You need to use a variable or sys.sequences
466                Ok(format!(
467                    "SELECT current_value FROM sys.sequences WHERE name = '{}'",
468                    sequence_name
469                ))
470            }
471            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
472                "This database does not support sequences.",
473            )),
474        }
475    }
476
477    /// Generate SQL for setting a sequence to a specific value.
478    pub fn setval(
479        sequence_name: &str,
480        value: i64,
481        is_called: bool,
482        db_type: DatabaseType,
483    ) -> QueryResult<String> {
484        match db_type {
485            DatabaseType::PostgreSQL => Ok(format!(
486                "SELECT setval('{}', {}, {})",
487                sequence_name, value, is_called
488            )),
489            DatabaseType::MSSQL => {
490                // MSSQL uses ALTER SEQUENCE ... RESTART WITH
491                Ok(format!(
492                    "ALTER SEQUENCE {} RESTART WITH {}",
493                    sequence_name, value
494                ))
495            }
496            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
497                "This database does not support sequences.",
498            )),
499        }
500    }
501
502    /// Generate SQL for getting the last inserted ID (auto-increment value).
503    ///
504    /// This works for databases that don't have sequences but use auto-increment.
505    pub fn last_insert_id(db_type: DatabaseType) -> String {
506        match db_type {
507            DatabaseType::PostgreSQL => "SELECT lastval()".to_string(),
508            DatabaseType::MySQL => "SELECT LAST_INSERT_ID()".to_string(),
509            DatabaseType::SQLite => "SELECT last_insert_rowid()".to_string(),
510            DatabaseType::MSSQL => "SELECT SCOPE_IDENTITY()".to_string(),
511        }
512    }
513
514    /// Generate SQL expression for using a sequence as a default value.
515    pub fn default_nextval(sequence_name: &str, db_type: DatabaseType) -> QueryResult<String> {
516        match db_type {
517            DatabaseType::PostgreSQL => Ok(format!("nextval('{}')", sequence_name)),
518            DatabaseType::MSSQL => Ok(format!("NEXT VALUE FOR {}", sequence_name)),
519            DatabaseType::MySQL | DatabaseType::SQLite => Err(QueryError::unsupported(
520                "This database does not support sequences in default expressions.",
521            )),
522        }
523    }
524}
525
526/// MongoDB counter-based sequence pattern.
527///
528/// MongoDB doesn't have native sequences, but you can implement them using
529/// a counter collection with `findAndModify`.
530pub mod mongodb {
531    use serde::{Deserialize, Serialize};
532
533    /// A counter-based sequence for MongoDB.
534    #[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
535    pub struct Counter {
536        /// Counter/sequence name.
537        pub name: String,
538        /// Current value.
539        pub value: i64,
540        /// Increment amount.
541        pub increment: i64,
542    }
543
544    impl Counter {
545        /// Create a new counter.
546        pub fn new(name: impl Into<String>) -> Self {
547            Self {
548                name: name.into(),
549                value: 0,
550                increment: 1,
551            }
552        }
553
554        /// Set the increment value.
555        pub fn with_increment(mut self, increment: i64) -> Self {
556            self.increment = increment;
557            self
558        }
559
560        /// Set the initial value.
561        pub fn with_initial_value(mut self, value: i64) -> Self {
562            self.value = value;
563            self
564        }
565    }
566
567    /// Builder for MongoDB counter operations.
568    #[derive(Debug, Clone)]
569    pub struct CounterBuilder {
570        /// Collection name for counters.
571        pub collection: String,
572        /// Counter name.
573        pub name: String,
574        /// Increment amount.
575        pub increment: i64,
576    }
577
578    impl CounterBuilder {
579        /// Create a new counter builder.
580        pub fn new(name: impl Into<String>) -> Self {
581            Self {
582                collection: "counters".to_string(),
583                name: name.into(),
584                increment: 1,
585            }
586        }
587
588        /// Set the collection name for counters.
589        pub fn collection(mut self, collection: impl Into<String>) -> Self {
590            self.collection = collection.into();
591            self
592        }
593
594        /// Set the increment amount.
595        pub fn increment(mut self, increment: i64) -> Self {
596            self.increment = increment;
597            self
598        }
599
600        /// Get the findAndModify command document for getting the next value.
601        ///
602        /// Returns a JSON-like structure that can be used with MongoDB driver.
603        pub fn next_value_command(&self) -> serde_json::Value {
604            serde_json::json!({
605                "findAndModify": &self.collection,
606                "query": { "_id": &self.name },
607                "update": { "$inc": { "seq": self.increment } },
608                "new": true,
609                "upsert": true
610            })
611        }
612
613        /// Get the aggregation pipeline stage for incrementing the counter.
614        pub fn increment_pipeline(&self) -> Vec<serde_json::Value> {
615            vec![
616                serde_json::json!({
617                    "$match": { "_id": &self.name }
618                }),
619                serde_json::json!({
620                    "$set": { "seq": { "$add": ["$seq", self.increment] } }
621                }),
622            ]
623        }
624
625        /// Get the document for initializing a counter.
626        pub fn init_document(&self, start_value: i64) -> serde_json::Value {
627            serde_json::json!({
628                "_id": &self.name,
629                "seq": start_value
630            })
631        }
632
633        /// Get the update document for resetting a counter.
634        pub fn reset_document(&self, value: i64) -> serde_json::Value {
635            serde_json::json!({
636                "$set": { "seq": value }
637            })
638        }
639    }
640
641    /// Helper function to create a counter builder.
642    pub fn counter(name: impl Into<String>) -> CounterBuilder {
643        CounterBuilder::new(name)
644    }
645}
646
647/// Auto-increment column helpers for databases without sequence support.
648pub mod auto_increment {
649    use super::*;
650
651    /// Generate SQL for creating an auto-increment column.
652    pub fn column_definition(
653        column_name: &str,
654        db_type: DatabaseType,
655        start: Option<i64>,
656    ) -> String {
657        match db_type {
658            DatabaseType::PostgreSQL => {
659                // PostgreSQL uses SERIAL or GENERATED ... AS IDENTITY
660                if let Some(start_val) = start {
661                    format!(
662                        "{} BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH {})",
663                        column_name, start_val
664                    )
665                } else {
666                    format!("{} BIGSERIAL", column_name)
667                }
668            }
669            DatabaseType::MySQL => {
670                if let Some(start_val) = start {
671                    format!("{} BIGINT AUTO_INCREMENT /* Start: {} */", column_name, start_val)
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!(
751                    "SELECT IDENT_CURRENT('{}');",
752                    table_name
753                )
754            }
755        }
756    }
757}
758
759/// Pre-built sequence patterns for common use cases.
760pub mod patterns {
761    use super::*;
762
763    /// Create an order number sequence starting at 1000.
764    pub fn order_number(schema: Option<&str>) -> Sequence {
765        let mut builder = Sequence::builder("order_number_seq")
766            .start(1000)
767            .increment(1)
768            .min_value(1)
769            .cache(20);
770
771        if let Some(s) = schema {
772            builder = builder.schema(s);
773        }
774
775        builder.build()
776    }
777
778    /// Create an invoice number sequence with yearly reset pattern.
779    pub fn invoice_number(schema: Option<&str>, year: i32) -> Sequence {
780        let mut builder = Sequence::builder(format!("invoice_{}_seq", year))
781            .start(1)
782            .increment(1)
783            .min_value(1)
784            .cache(10);
785
786        if let Some(s) = schema {
787            builder = builder.schema(s);
788        }
789
790        builder.build()
791    }
792
793    /// Create a high-performance ID sequence with large cache.
794    pub fn high_volume_id(name: &str, schema: Option<&str>) -> Sequence {
795        let mut builder = Sequence::builder(name)
796            .start(1)
797            .increment(1)
798            .min_value(1)
799            .cache(1000); // Large cache for high-volume inserts
800
801        if let Some(s) = schema {
802            builder = builder.schema(s);
803        }
804
805        builder.build()
806    }
807
808    /// Create a cycling sequence for round-robin distribution.
809    pub fn round_robin(name: &str, max: i64, schema: Option<&str>) -> Sequence {
810        let mut builder = Sequence::builder(name)
811            .start(1)
812            .increment(1)
813            .min_value(1)
814            .max_value(max)
815            .cycle(true)
816            .cache(10);
817
818        if let Some(s) = schema {
819            builder = builder.schema(s);
820        }
821
822        builder.build()
823    }
824
825    /// Create a negative sequence (counts down).
826    pub fn countdown(name: &str, start: i64, schema: Option<&str>) -> Sequence {
827        let mut builder = Sequence::builder(name)
828            .start(start)
829            .increment(-1)
830            .min_value(0)
831            .no_max_value();
832
833        if let Some(s) = schema {
834            builder = builder.schema(s);
835        }
836
837        builder.build()
838    }
839}
840
841#[cfg(test)]
842mod tests {
843    use super::*;
844
845    #[test]
846    fn test_sequence_builder() {
847        let seq = Sequence::builder("order_seq")
848            .start(1000)
849            .increment(1)
850            .min_value(1)
851            .max_value(999999)
852            .cache(20)
853            .build();
854
855        assert_eq!(seq.name, "order_seq");
856        assert_eq!(seq.start, 1000);
857        assert_eq!(seq.increment, 1);
858        assert_eq!(seq.min_value, Some(1));
859        assert_eq!(seq.max_value, Some(999999));
860        assert_eq!(seq.cache, Some(20));
861        assert!(!seq.cycle);
862    }
863
864    #[test]
865    fn test_postgres_create_sql() {
866        let seq = Sequence::builder("order_seq")
867            .schema("public")
868            .start(1000)
869            .increment(1)
870            .min_value(1)
871            .cache(20)
872            .build();
873
874        let sql = seq.to_postgres_create_sql();
875        assert!(sql.contains("CREATE SEQUENCE public.order_seq"));
876        assert!(sql.contains("INCREMENT BY 1"));
877        assert!(sql.contains("MINVALUE 1"));
878        assert!(sql.contains("START WITH 1000"));
879        assert!(sql.contains("CACHE 20"));
880        assert!(sql.contains("NO CYCLE"));
881    }
882
883    #[test]
884    fn test_mssql_create_sql() {
885        let seq = Sequence::builder("order_seq")
886            .schema("dbo")
887            .start(1000)
888            .increment(1)
889            .build();
890
891        let sql = seq.to_mssql_create_sql();
892        assert!(sql.contains("CREATE SEQUENCE dbo.order_seq"));
893        assert!(sql.contains("AS BIGINT"));
894        assert!(sql.contains("START WITH 1000"));
895        assert!(sql.contains("INCREMENT BY 1"));
896    }
897
898    #[test]
899    fn test_mysql_not_supported() {
900        let seq = Sequence::builder("test").build();
901        let result = seq.to_create_sql(DatabaseType::MySQL);
902        assert!(result.is_err());
903    }
904
905    #[test]
906    fn test_sqlite_not_supported() {
907        let seq = Sequence::builder("test").build();
908        let result = seq.to_create_sql(DatabaseType::SQLite);
909        assert!(result.is_err());
910    }
911
912    #[test]
913    fn test_drop_sql() {
914        let seq = Sequence::builder("order_seq").build();
915
916        let pg_drop = seq.to_drop_sql(DatabaseType::PostgreSQL).unwrap();
917        assert_eq!(pg_drop, "DROP SEQUENCE IF EXISTS order_seq CASCADE;");
918
919        let mssql_drop = seq.to_drop_sql(DatabaseType::MSSQL).unwrap();
920        assert_eq!(mssql_drop, "DROP SEQUENCE IF EXISTS order_seq;");
921    }
922
923    #[test]
924    fn test_restart_sql() {
925        let seq = Sequence::builder("order_seq").build();
926
927        let pg_restart = seq.restart_sql(5000, DatabaseType::PostgreSQL).unwrap();
928        assert_eq!(pg_restart, "ALTER SEQUENCE order_seq RESTART WITH 5000;");
929
930        let mssql_restart = seq.restart_sql(5000, DatabaseType::MSSQL).unwrap();
931        assert_eq!(mssql_restart, "ALTER SEQUENCE order_seq RESTART WITH 5000;");
932    }
933
934    #[test]
935    fn test_cycle_sequence() {
936        let seq = Sequence::builder("round_robin")
937            .start(1)
938            .max_value(10)
939            .cycle(true)
940            .build();
941
942        let sql = seq.to_postgres_create_sql();
943        assert!(sql.contains("MAXVALUE 10"));
944        assert!(sql.contains("CYCLE"));
945        assert!(!sql.contains("NO CYCLE"));
946    }
947
948    #[test]
949    fn test_owned_by() {
950        let seq = Sequence::builder("users_id_seq")
951            .owned_by("users", "id")
952            .build();
953
954        let sql = seq.to_postgres_create_sql();
955        assert!(sql.contains("OWNED BY users.id"));
956    }
957
958    mod ops_tests {
959        use super::super::ops;
960        use super::*;
961
962        #[test]
963        fn test_nextval() {
964            let pg = ops::nextval("order_seq", DatabaseType::PostgreSQL).unwrap();
965            assert_eq!(pg, "SELECT nextval('order_seq')");
966
967            let mssql = ops::nextval("order_seq", DatabaseType::MSSQL).unwrap();
968            assert_eq!(mssql, "SELECT NEXT VALUE FOR order_seq");
969        }
970
971        #[test]
972        fn test_currval() {
973            let pg = ops::currval("order_seq", DatabaseType::PostgreSQL).unwrap();
974            assert_eq!(pg, "SELECT currval('order_seq')");
975        }
976
977        #[test]
978        fn test_setval() {
979            let pg = ops::setval("order_seq", 1000, true, DatabaseType::PostgreSQL).unwrap();
980            assert_eq!(pg, "SELECT setval('order_seq', 1000, true)");
981
982            let mssql = ops::setval("order_seq", 1000, true, DatabaseType::MSSQL).unwrap();
983            assert_eq!(mssql, "ALTER SEQUENCE order_seq RESTART WITH 1000");
984        }
985
986        #[test]
987        fn test_last_insert_id() {
988            assert_eq!(ops::last_insert_id(DatabaseType::PostgreSQL), "SELECT lastval()");
989            assert_eq!(ops::last_insert_id(DatabaseType::MySQL), "SELECT LAST_INSERT_ID()");
990            assert_eq!(ops::last_insert_id(DatabaseType::SQLite), "SELECT last_insert_rowid()");
991            assert_eq!(ops::last_insert_id(DatabaseType::MSSQL), "SELECT SCOPE_IDENTITY()");
992        }
993
994        #[test]
995        fn test_default_nextval() {
996            let pg = ops::default_nextval("order_seq", DatabaseType::PostgreSQL).unwrap();
997            assert_eq!(pg, "nextval('order_seq')");
998
999            let mssql = ops::default_nextval("order_seq", DatabaseType::MSSQL).unwrap();
1000            assert_eq!(mssql, "NEXT VALUE FOR order_seq");
1001        }
1002    }
1003
1004    mod mongodb_tests {
1005        use super::super::mongodb::*;
1006
1007        #[test]
1008        fn test_counter_builder() {
1009            let builder = counter("order_number").increment(1);
1010            let cmd = builder.next_value_command();
1011
1012            assert_eq!(cmd["findAndModify"], "counters");
1013            assert_eq!(cmd["query"]["_id"], "order_number");
1014            assert_eq!(cmd["update"]["$inc"]["seq"], 1);
1015            assert_eq!(cmd["upsert"], true);
1016        }
1017
1018        #[test]
1019        fn test_custom_collection() {
1020            let builder = counter("invoice_number").collection("sequences");
1021            let cmd = builder.next_value_command();
1022
1023            assert_eq!(cmd["findAndModify"], "sequences");
1024        }
1025
1026        #[test]
1027        fn test_init_document() {
1028            let builder = counter("order_number");
1029            let doc = builder.init_document(1000);
1030
1031            assert_eq!(doc["_id"], "order_number");
1032            assert_eq!(doc["seq"], 1000);
1033        }
1034
1035        #[test]
1036        fn test_reset_document() {
1037            let builder = counter("order_number");
1038            let doc = builder.reset_document(5000);
1039
1040            assert_eq!(doc["$set"]["seq"], 5000);
1041        }
1042    }
1043
1044    mod auto_increment_tests {
1045        use super::super::auto_increment;
1046        use super::*;
1047
1048        #[test]
1049        fn test_column_definition() {
1050            let pg = auto_increment::column_definition("id", DatabaseType::PostgreSQL, None);
1051            assert_eq!(pg, "id BIGSERIAL");
1052
1053            let pg_start = auto_increment::column_definition("id", DatabaseType::PostgreSQL, Some(1000));
1054            assert!(pg_start.contains("START WITH 1000"));
1055
1056            let mysql = auto_increment::column_definition("id", DatabaseType::MySQL, None);
1057            assert!(mysql.contains("AUTO_INCREMENT"));
1058
1059            let sqlite = auto_increment::column_definition("id", DatabaseType::SQLite, None);
1060            assert!(sqlite.contains("INTEGER PRIMARY KEY AUTOINCREMENT"));
1061
1062            let mssql = auto_increment::column_definition("id", DatabaseType::MSSQL, Some(1000));
1063            assert!(mssql.contains("IDENTITY(1000, 1)"));
1064        }
1065
1066        #[test]
1067        fn test_set_start_value() {
1068            let mysql = auto_increment::set_start_value("orders", 1000, DatabaseType::MySQL).unwrap();
1069            assert_eq!(mysql, "ALTER TABLE orders AUTO_INCREMENT = 1000;");
1070
1071            let mssql = auto_increment::set_start_value("orders", 1000, DatabaseType::MSSQL).unwrap();
1072            assert!(mssql.contains("DBCC CHECKIDENT"));
1073        }
1074    }
1075
1076    mod patterns_tests {
1077        use super::super::patterns;
1078
1079        #[test]
1080        fn test_order_number() {
1081            let seq = patterns::order_number(Some("sales"));
1082            assert_eq!(seq.start, 1000);
1083            assert_eq!(seq.schema, Some("sales".to_string()));
1084        }
1085
1086        #[test]
1087        fn test_round_robin() {
1088            let seq = patterns::round_robin("worker_queue", 10, None);
1089            assert!(seq.cycle);
1090            assert_eq!(seq.max_value, Some(10));
1091        }
1092
1093        #[test]
1094        fn test_countdown() {
1095            let seq = patterns::countdown("tickets", 100, None);
1096            assert_eq!(seq.start, 100);
1097            assert_eq!(seq.increment, -1);
1098            assert_eq!(seq.min_value, Some(0));
1099        }
1100    }
1101}
1102
1103
1104
1105
prax_query/sequence.rs

prax_query/
sequence.rs
