Coverage for nexusLIMS/db/migrations/versions/v1_4_3_initial_schema_baseline.py: 100%

1"""Initial schema baseline. 

2 

3Creates the core NexusLIMS database schema with instruments and session_log tables. 

4 

5This migration creates the foundational schema based on SQLModel definitions. 

6For existing installations (pre-2.5.0): Run `alembic stamp v1_4_3` to mark as migrated. 

7For new installations: This migration creates the initial tables. 

8 

9Revision ID: v1_4_3 

10Revises: 

11Create Date: 2025-12-29 11:08:25.723483 

12 

13""" 

14 

15from typing import Sequence, Union 

16 

17import sqlalchemy as sa 

18from alembic import op 

19 

20# revision identifiers, used by Alembic. 

21revision: str = "v1_4_3" 

22down_revision: Union[str, Sequence[str], None] = None 

23branch_labels: Union[str, Sequence[str], None] = None 

24depends_on: Union[str, Sequence[str], None] = None 

25 

26 

27def upgrade() -> None: 

28 """Create initial NexusLIMS schema. 

29 

30 Creates: 

31 - instruments table: Instrument configuration and metadata 

32 - session_log table: Session event tracking (without CHECK constraints) 

33 

34 Note: CHECK constraints on session_log are added in migration 003. 

35 """ 

36 # Create instruments table 

37 op.create_table( 

38 "instruments", 

39 sa.Column("instrument_pid", sa.String(length=100), nullable=False), 

40 sa.Column("api_url", sa.String(), nullable=False), 

41 sa.Column("calendar_name", sa.String(), nullable=False), 

42 sa.Column("calendar_url", sa.String(), nullable=False), 

43 sa.Column("location", sa.String(length=100), nullable=False), 

44 sa.Column("schema_name", sa.String(), nullable=False), 

45 sa.Column("property_tag", sa.String(length=20), nullable=False), 

46 sa.Column("filestore_path", sa.String(), nullable=False), 

47 sa.Column("harvester", sa.String(), nullable=False), 

48 sa.Column("timezone", sa.String(), nullable=False), 

49 sa.Column("computer_name", sa.String(), nullable=True), 

50 sa.Column("computer_ip", sa.String(length=15), nullable=True), 

51 sa.Column("computer_mount", sa.String(), nullable=True), 

52 sa.PrimaryKeyConstraint("instrument_pid"), 

53 sa.UniqueConstraint("api_url"), 

54 sa.UniqueConstraint("computer_name"), 

55 sa.UniqueConstraint("computer_ip"), 

56 ) 

57 

58 # Create session_log table (without CHECK constraints - added in migration 003) 

59 op.create_table( 

60 "session_log", 

61 sa.Column("id_session_log", sa.Integer(), nullable=False), 

62 sa.Column("session_identifier", sa.String(length=36), nullable=False), 

63 sa.Column("instrument", sa.String(length=100), nullable=False), 

64 sa.Column("timestamp", sa.String(), nullable=False), 

65 sa.Column("event_type", sa.String(), nullable=False), 

66 sa.Column("record_status", sa.String(), nullable=False), 

67 sa.Column("user", sa.String(length=50), nullable=True), 

68 sa.ForeignKeyConstraint( 

69 ["instrument"], 

70 ["instruments.instrument_pid"], 

71 ), 

72 sa.PrimaryKeyConstraint("id_session_log"), 

73 ) 

74 op.create_index( 

75 op.f("ix_session_log_session_identifier"), 

76 "session_log", 

77 ["session_identifier"], 

78 unique=False, 

79 ) 

80 

81 

82def downgrade() -> None: 

83 """Drop initial schema tables.""" 

84 op.drop_index(op.f("ix_session_log_session_identifier"), table_name="session_log") 

85 op.drop_table("session_log") 

86 op.drop_table("instruments")