Data Types and Custom Types

class LongStr(str):
    """String type for large text fields stored as TEXT/CLOB in database.
    
    Inherits from str but signals to database that this should be stored
    as a large text type (TEXT, CLOB, etc.) rather than VARCHAR.
    
    Usage:
        description = Required(LongStr)
        content = Optional(LongStr)
    """

class LongUnicode(LongStr):
    """Unicode string type for large text fields (alias for LongStr).
    
    Provided for backwards compatibility. Use LongStr instead.
    """

class Json:
    """JSON data type for storing structured data.
    
    Automatically serializes Python objects to JSON when storing
    and deserializes JSON back to Python objects when retrieving.
    
    Supports: dict, list, str, int, float, bool, None
    
    Usage:
        metadata = Optional(Json)
        config = Required(Json, default={})
    """

class IntArray:
    """Array type for storing lists of integers.
    
    Maps to database array types (PostgreSQL ARRAY) or serialized storage
    for databases that don't support arrays natively.
    
    Usage:
        scores = Optional(IntArray)
        ratings = Required(IntArray, default=[])
    """

class StrArray:
    """Array type for storing lists of strings.
    
    Usage:
        tags = Optional(StrArray)
        categories = Required(StrArray, default=[])
    """

class FloatArray:
    """Array type for storing lists of floating point numbers.
    
    Usage:
        coordinates = Optional(FloatArray)
        measurements = Required(FloatArray, default=[])
    """

buffer: type
    """Binary data type for cross-Python compatibility.
    
    Maps to appropriate binary type based on Python version:
    - Python 2: buffer
    - Python 3: bytes
    
    Usage:
        file_data = Optional(buffer)
        binary_content = Required(buffer)
    """

unicode: type
    """Unicode string type for cross-Python compatibility.
    
    Maps to appropriate unicode type based on Python version:
    - Python 2: unicode
    - Python 3: str
    
    Usage:
        text_field = Required(unicode)
    """

from pony.orm import *

db = Database()

class Article(db.Entity):
    title = Required(str, max_len=200)
    summary = Optional(str, max_len=500)
    
    # Large text content
    content = Required(LongStr)  # Stored as TEXT/CLOB
    raw_html = Optional(LongStr)
    
    # Regular strings have length limits, LongStr does not
    author_bio = Optional(LongStr)

class BlogPost(db.Entity):
    title = Required(str)
    
    # LongUnicode for backwards compatibility
    content = Required(LongUnicode)

db.bind('sqlite', filename='blog.db')
db.generate_mapping(create_tables=True)

with db_session:
    # Create article with large content
    article = Article(
        title="Long Article",
        summary="This article has a lot of content...",
        content="This is a very long article content that can be thousands of characters long without any VARCHAR limitations. " * 1000
    )
    
    # LongStr behaves like regular string
    print(len(article.content))  # Can be very large
    print(article.content[:100])  # Slicing works normally

from pony.orm import *

db = Database()

class User(db.Entity):
    name = Required(str)
    email = Required(str, unique=True)
    
    # JSON fields for structured data
    preferences = Optional(Json, default={})
    profile_data = Optional(Json)
    activity_log = Optional(Json, default=[])

class Product(db.Entity):
    name = Required(str)
    price = Required(float)
    
    # Complex JSON data
    specifications = Optional(Json, default={})
    reviews = Optional(Json, default=[])

db.bind('postgresql', host='localhost', user='app', password='secret', database='store')
db.generate_mapping(create_tables=True)

with db_session:
    # Create user with JSON data
    user = User(
        name="Alice",
        email="alice@example.com",
        preferences={
            "theme": "dark",
            "language": "en",
            "notifications": {
                "email": True,
                "push": False
            }
        },
        profile_data={
            "age": 25,
            "location": "San Francisco",
            "interests": ["programming", "music", "travel"]
        }
    )
    
    # Access JSON data like regular Python objects
    print(user.preferences["theme"])  # "dark"
    print(user.profile_data["interests"])  # ["programming", "music", "travel"]
    
    # Modify JSON data
    user.preferences["theme"] = "light"
    user.profile_data["interests"].append("photography")
    
    # Create product with complex specifications
    product = Product(
        name="Laptop",
        price=999.99,
        specifications={
            "cpu": "Intel i7",
            "ram": "16GB",
            "storage": "512GB SSD",
            "display": {
                "size": "15.6 inch",
                "resolution": "1920x1080",
                "type": "IPS"
            },
            "ports": ["USB-C", "USB-A", "HDMI", "Audio Jack"]
        },
        reviews=[
            {
                "user": "john_doe",
                "rating": 5,
                "comment": "Excellent laptop!",
                "date": "2023-01-15"
            },
            {
                "user": "jane_smith", 
                "rating": 4,
                "comment": "Good performance, bit heavy",
                "date": "2023-01-20"
            }
        ]
    )
    
    # Query JSON data (PostgreSQL supports JSON queries)
    # Find users who prefer dark theme
    dark_theme_users = select(u for u in User 
                             if u.preferences.get("theme") == "dark")

from pony.orm import *

db = Database()

class Student(db.Entity):
    name = Required(str)
    
    # Array fields
    test_scores = Optional(IntArray, default=[])
    subjects = Optional(StrArray, default=[])
    gpa_history = Optional(FloatArray, default=[])

class DataPoint(db.Entity):
    name = Required(str)
    
    # Numeric arrays for scientific data
    measurements = Required(FloatArray)
    sensor_ids = Optional(IntArray)
    tags = Optional(StrArray)

# PostgreSQL supports native arrays
db.bind('postgresql', host='localhost', user='app', password='secret', database='school')
db.generate_mapping(create_tables=True)

with db_session:
    # Create student with array data
    student = Student(
        name="Alice Johnson",
        test_scores=[85, 92, 78, 95, 88],
        subjects=["Math", "Physics", "Chemistry", "English"],
        gpa_history=[3.2, 3.5, 3.7, 3.8]
    )
    
    # Work with arrays like Python lists
    student.test_scores.append(90)
    student.subjects.extend(["History", "Art"])
    
    print(f"Average score: {sum(student.test_scores) / len(student.test_scores)}")
    print(f"Subjects: {', '.join(student.subjects)}")
    
    # Create data point with measurements
    data_point = DataPoint(
        name="Temperature Sensor 1",
        measurements=[20.5, 21.0, 20.8, 21.2, 20.9],
        sensor_ids=[1, 2, 3, 4, 5],
        tags=["outdoor", "temperature", "hourly"]
    )
    
    # Query array data (PostgreSQL specific)
    # Find students with high test scores
    high_achievers = select(s for s in Student 
                           if any(score > 90 for score in s.test_scores))
    
    # Find data points with specific tags
    temp_sensors = select(d for d in DataPoint 
                         if "temperature" in d.tags)

from pony.orm import *

db = Database()

class Document(db.Entity):
    name = Required(str)
    mime_type = Required(str)
    
    # Binary data storage
    file_data = Required(buffer)  # Cross-platform binary type
    thumbnail = Optional(buffer)

class Image(db.Entity):
    filename = Required(str)
    
    # Unicode text with binary data
    description = Optional(unicode)  # Cross-platform unicode
    image_data = Required(buffer)

db.bind('sqlite', filename='files.db')
db.generate_mapping(create_tables=True)

with db_session:
    # Read binary file
    with open('document.pdf', 'rb') as f:
        file_content = f.read()
    
    # Store binary data
    doc = Document(
        name="Important Document",
        mime_type="application/pdf",
        file_data=file_content
    )
    
    # Retrieve and save binary data
    with open('retrieved_document.pdf', 'wb') as f:
        f.write(doc.file_data)
    
    # Work with images
    with open('photo.jpg', 'rb') as f:
        image_data = f.read()
    
    image = Image(
        filename="vacation_photo.jpg",
        description=u"Beautiful sunset at the beach",  # Unicode string
        image_data=image_data
    )

from pony.orm import *

db = Database()

class BlogPost(db.Entity):
    title = Required(str)
    slug = Required(str, unique=True)
    
    # Combine different custom types
    content = Required(LongStr)  # Large text content
    metadata = Optional(Json, default={})  # Structured metadata
    tags = Optional(StrArray, default=[])  # Array of tags
    
    # Rich content with attachments
    attachments = Optional(Json, default=[])  # JSON array of attachment info

class UserProfile(db.Entity):
    username = Required(str, unique=True)
    
    # Personal information
    bio = Optional(LongStr)  # Long biographical text
    preferences = Optional(Json, default={})  # User preferences
    
    # Activity tracking
    login_history = Optional(Json, default=[])  # JSON log of logins
    favorite_tags = Optional(StrArray, default=[])  # Array of interests
    
    # Binary profile data
    avatar = Optional(buffer)  # Profile picture binary data

db.bind('postgresql', host='localhost', user='blog', password='secret', database='blog')
db.generate_mapping(create_tables=True)

with db_session:
    # Create rich blog post
    post = BlogPost(
        title="Advanced Database Types in Pony ORM",
        slug="advanced-database-types-pony-orm",
        content="""
        This is a comprehensive guide to using advanced database types...
        """ * 100,  # Very long content
        metadata={
            "author_id": 123,
            "publish_date": "2023-12-01",
            "category": "tutorial",
            "reading_time": 15,
            "seo": {
                "meta_description": "Learn about advanced types",
                "keywords": ["database", "ORM", "Python"]
            }
        },
        tags=["python", "database", "orm", "tutorial"],
        attachments=[
            {
                "type": "image",
                "url": "/images/diagram1.png",
                "caption": "Database schema diagram"
            },
            {
                "type": "code",
                "language": "python",
                "content": "# Example code snippet"
            }
        ]
    )
    
    # Query combining different types
    python_posts = select(p for p in BlogPost 
                         if "python" in p.tags 
                         and p.metadata.get("category") == "tutorial")