Comprehensive comparison for Database technology in Software Development applications

from cassandra.cluster import Cluster, ExecutionProfile, EXEC_PROFILE_DEFAULT
from cassandra.policies import DCAwareRoundRobinPolicy, TokenAwarePolicy
from cassandra.query import PreparedStatement, BatchStatement, BatchType
from cassandra.auth import PlainTextAuthProvider
import uuid
from datetime import datetime
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class UserActivityTracker:
    """
    Production-ready Cassandra service for tracking user activities in a software platform.
    Demonstrates time-series data modeling and best practices.
    """
    
    def __init__(self, contact_points=['127.0.0.1'], keyspace='software_dev'):
        # Configure connection with production-ready settings
        auth_provider = PlainTextAuthProvider(username='cassandra', password='cassandra')
        profile = ExecutionProfile(
            load_balancing_policy=TokenAwarePolicy(DCAwareRoundRobinPolicy()),
            request_timeout=15
        )
        
        self.cluster = Cluster(
            contact_points=contact_points,
            auth_provider=auth_provider,
            execution_profiles={EXEC_PROFILE_DEFAULT: profile}
        )
        self.session = self.cluster.connect()
        self.keyspace = keyspace
        self._initialize_schema()
        self._prepare_statements()
    
    def _initialize_schema(self):
        """Create keyspace and tables with appropriate replication strategy"""
        try:
            self.session.execute(f"""
                CREATE KEYSPACE IF NOT EXISTS {self.keyspace}
                WITH replication = {{'class': 'NetworkTopologyStrategy', 'datacenter1': 3}}
                AND durable_writes = true
            """)
            
            self.session.set_keyspace(self.keyspace)
            
            # Time-series table partitioned by user and date for efficient queries
            self.session.execute("""
                CREATE TABLE IF NOT EXISTS user_activities (
                    user_id uuid,
                    activity_date date,
                    activity_time timestamp,
                    activity_id timeuuid,
                    activity_type text,
                    resource_id text,
                    metadata map<text, text>,
                    PRIMARY KEY ((user_id, activity_date), activity_time, activity_id)
                ) WITH CLUSTERING ORDER BY (activity_time DESC, activity_id DESC)
                AND compaction = {'class': 'TimeWindowCompactionStrategy'}
            """)
            
            logger.info("Schema initialized successfully")
        except Exception as e:
            logger.error(f"Schema initialization failed: {e}")
            raise
    
    def _prepare_statements(self):
        """Prepare statements for better performance"""
        self.insert_stmt = self.session.prepare("""
            INSERT INTO user_activities 
            (user_id, activity_date, activity_time, activity_id, activity_type, resource_id, metadata)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            USING TTL ?
        """)
        
        self.query_stmt = self.session.prepare("""
            SELECT * FROM user_activities
            WHERE user_id = ? AND activity_date = ?
            LIMIT ?
        """)
    
    def track_activity(self, user_id, activity_type, resource_id, metadata=None, ttl_seconds=2592000):
        """Track a user activity with automatic partitioning by date"""
        try:
            activity_time = datetime.utcnow()
            activity_id = uuid.uuid1()
            activity_date = activity_time.date()
            
            if metadata is None:
                metadata = {}
            
            self.session.execute(
                self.insert_stmt,
                (user_id, activity_date, activity_time, activity_id, 
                 activity_type, resource_id, metadata, ttl_seconds)
            )
            
            logger.info(f"Activity tracked: user={user_id}, type={activity_type}")
            return activity_id
        except Exception as e:
            logger.error(f"Failed to track activity: {e}")
            raise
    
    def batch_track_activities(self, activities):
        """Batch insert multiple activities efficiently"""
        try:
            batch = BatchStatement(batch_type=BatchType.UNLOGGED)
            
            for activity in activities:
                activity_time = datetime.utcnow()
                activity_id = uuid.uuid1()
                activity_date = activity_time.date()
                
                batch.add(self.insert_stmt, (
                    activity['user_id'],
                    activity_date,
                    activity_time,
                    activity_id,
                    activity['activity_type'],
                    activity['resource_id'],
                    activity.get('metadata', {}),
                    activity.get('ttl_seconds', 2592000)
                ))
            
            self.session.execute(batch)
            logger.info(f"Batch tracked {len(activities)} activities")
        except Exception as e:
            logger.error(f"Batch tracking failed: {e}")
            raise
    
    def get_user_activities(self, user_id, activity_date, limit=100):
        """Retrieve user activities for a specific date"""
        try:
            rows = self.session.execute(
                self.query_stmt,
                (user_id, activity_date, limit)
            )
            
            activities = [{
                'activity_id': str(row.activity_id),
                'activity_time': row.activity_time.isoformat(),
                'activity_type': row.activity_type,
                'resource_id': row.resource_id,
                'metadata': row.metadata
            } for row in rows]
            
            return activities
        except Exception as e:
            logger.error(f"Failed to retrieve activities: {e}")
            raise
    
    def close(self):
        """Clean up resources"""
        self.cluster.shutdown()
        logger.info("Connection closed")

# Example usage
if __name__ == "__main__":
    tracker = UserActivityTracker()
    
    try:
        user_id = uuid.uuid4()
        
        # Track single activity
        tracker.track_activity(
            user_id=user_id,
            activity_type='code_commit',
            resource_id='repo-123',
            metadata={'branch': 'main', 'files_changed': '5'}
        )
        
        # Batch track activities
        activities = [
            {'user_id': user_id, 'activity_type': 'pull_request', 'resource_id': 'pr-456'},
            {'user_id': user_id, 'activity_type': 'code_review', 'resource_id': 'pr-789'}
        ]
        tracker.batch_track_activities(activities)
        
        # Query activities
        today_activities = tracker.get_user_activities(user_id, datetime.utcnow().date())
        print(f"Found {len(today_activities)} activities")
        
    finally:
        tracker.close()