LitePolis database default

This module is designed to be compatible with the original polis platform.

To integrate this module into your LitePolis middleware or FastAPI-based APIs, import the necessary components. Additionally, ensure that the system administrator configures the litepolis_database_default section within their ~/litepolis/litepolis.config file.

Reference:

API references

DatabaseActor

DatabaseActor class for LitePolis.

This class serves as the central point of interaction between the LitePolis system and the database module. It aggregates operations from various manager classes, such as UserManager, ConversationManager, CommentManager, VoteManager, and ReportManager, providing a unified interface for database interactions.

Users Table

This module defines the User model and UserManager class for interacting with the ‘users’ table in the database.

The ‘users’ table stores information about users, including their email, authentication token, and admin status. It also includes timestamps for creation and modification. The table supports both standard SQL databases and StarRocks, with some specific handling for StarRocks (e.g., unique constraints and primary keys).

Table Schema
Column	Type	Description
id	INTEGER	Unique identifier for the user.
email	VARCHAR	User’s email address. Must be unique.
auth_token	VARCHAR	Authentication token for the user.
is_admin	BOOLEAN	Indicates whether the user is an administrator.
created	DATETIME	Timestamp indicating when the user was created.
modified	DATETIME	Timestamp indicating when the user was last modified.

Relationships
Comment	One-to-many.
Vote	One-to-many.
Conversation	One-to-many.

The UserManager class provides static methods for interacting with the ‘users’ table, including CRUD operations, searching, listing with pagination, and counting.

To use the methods in this module, import DatabaseActor. For example:

from litepolis_database_default import DatabaseActor

user = DatabaseActor.create_user({
    "email": "test@example.com",
    "auth_token": "auth_token",
})

from litepolis_database_default import DatabaseActor
from datetime import datetime

# Create a user
user = DatabaseActor.create_user({
    "email": "test@example.com",
    "auth_token": "auth_token",
})

# Read a user by ID
user = DatabaseActor.read_user(user_id=1)

# Read a user by email
user = DatabaseActor.read_user_by_email(email="test@example.com")

# List users with pagination
users = DatabaseActor.list_users(page=1, page_size=10)

# Update a user
user = DatabaseActor.update_user(user_id=1, data={"email": "new_email@example.com"})

# Delete a user
success = DatabaseActor.delete_user(user_id=1)

# Search users by email
users = DatabaseActor.search_users_by_email(query="example.com")

# List users by admin status
users = DatabaseActor.list_users_by_admin_status(is_admin=True)

# List users created in a date range
users = DatabaseActor.list_users_created_in_date_range(start_date=datetime(2023, 1, 1), end_date=datetime(2023, 12, 31))

# Count users
count = DatabaseActor.count_users()

Comments Table

This module defines the database schema for comments, including the Comment model and the CommentManager class for managing comment data.

The Comment model represents the comments table and includes fields for the comment’s content (text_field), timestamps (created, modified), moderation status (moderation_status), and foreign keys linking to the user (user_id), conversation (conversation_id), and parent comment (parent_comment_id). It also defines relationships to related User, Conversation, Vote, and other Comment instances (for replies).

The CommentManager class provides static methods for common database operations on comments, such as creating, reading, updating, deleting, listing by various criteria (conversation, user, date range), searching, and counting.

Table Schemas
Table Name	Description
users	Stores user information (id, email, auth_token, etc.).
conversations	Stores conversation information (id, title, etc.).
comments	Stores comment information (id, text_field, user_id, conversation_id, parent_comment_id, created, modified, moderation_status).
votes	Stores vote information (id, user_id, comment_id, value).

Comments Table Details
Column Name	Description
id (int)	Primary key for the comment.
text_field (str)	The content of the comment.
user_id (int, optional)	Foreign key referencing the user who created the comment.
conversation_id (int, optional)	Foreign key referencing the conversation the comment belongs to.
parent_comment_id (int, optional)	Foreign key referencing the parent comment (for replies).
created (datetime)	Timestamp of when the comment was created.
modified (datetime)	Timestamp of when the comment was last modified.
moderation_status (int)	Status indicating the moderation state of the comment (e.g., 0: default, 1: pending, 2: approved, 3: rejected).
user (Relationship)	Relationship to the User who created the comment.
conversation (Relationship)	Relationship to the Conversation the comment belongs to.
votes (Relationship)	Relationship to the Votes associated with this comment.
replies (Relationship)	Relationship to other Comments that are replies to this comment.
parent_comment (Relationship)	Relationship to the parent Comment if this is a reply.

Classes
Class Name	Description
Comment	SQLModel class representing the comments table.
CommentManager	Provides static methods for managing comments (create, read, update, delete, list, search, count).

To use the methods in this module, import DatabaseActor from litepolis_database_default. For example:

from litepolis_database_default import DatabaseActor

comment = DatabaseActor.create_comment({
    "text_field": "This is a test comment.",
    "user_id": 1,
    "conversation_id": 1,
    "moderation_status": 0 # Include new field if needed, or rely on default
})

from litepolis_database_default import DatabaseActor
from datetime import datetime

# Create a comment
comment = DatabaseActor.create_comment({
    "text": "This is a comment.",
    "user_id": 1,
    "conversation_id": 1
})

# Read a comment by ID
comment = DatabaseActor.read_comment(comment_id=1)

# List comments by conversation ID
comments = DatabaseActor.list_comments_by_conversation_id(conversation_id=1, page=1, page_size=10, order_by="created", order_direction="asc")

# Update a comment
updated_comment = DatabaseActor.update_comment(comment_id=1, data={"text": "Updated comment text."})

# Delete a comment
success = DatabaseActor.delete_comment(comment_id=1)

# Search comments
comments = DatabaseActor.search_comments(query="search term")

# List comments by user ID
comments = DatabaseActor.list_comments_by_user_id(user_id=1, page=1, page_size=10)

# List comments created in a date range
start = datetime(2023, 1, 1)
end = datetime(2023, 1, 31)
comments = DatabaseActor.list_comments_created_in_date_range(start_date=start, end_date=end)

# Count comments in a conversation
count = DatabaseActor.count_comments_in_conversation(conversation_id=1)

# Get a comment with its replies
comment = DatabaseActor.get_comment_with_replies(comment_id=1)

Conversations Table

This module defines the Conversation model and ConversationManager class for interacting with the ‘conversations’ table in the database.

The ‘conversations’ table stores information about conversations, including their title, description, archive status, arbitrary settings in a JSON format, and the user who created them. It also includes timestamps for creation and modification.

Table Schema
Column	Type	Description
id	INTEGER	Unique identifier for the conversation.
user_id	INTEGER	Foreign key referencing the user who created the conversation.
title	VARCHAR	Title of the conversation.
description	VARCHAR	Description of the conversation.
is_archived	BOOLEAN	Indicates whether the conversation is archived.
created	DATETIME	Timestamp indicating when the conversation was created.
modified	DATETIME	Timestamp indicating when the conversation was last modified.
settings	JSON	Arbitrary key-value settings for the conversation.

Relationships
comments	One-to-many relationship with the Comment model.
user	Many-to-one relationship with the User model.

The ConversationManager class provides static methods for performing CRUD (Create, Read, Update, Delete) operations on the ‘conversations’ table, as well as methods for listing, searching, counting, and archiving conversations.

To use the methods in this module, import DatabaseActor. For example:

from litepolis_database_default import DatabaseActor

conversation = DatabaseActor.create_conversation({
    "title": "New Conversation",
    "description": "A new conversation about a topic.",
    "user_id": 1,
    "settings": {"visibility": "public"}
})

from litepolis_database_default import DatabaseActor

# Create a conversation
conversation = DatabaseActor.create_conversation({
    "title": "New Conversation",
    "description": "A new conversation about a topic.",
})

# Read a conversation by ID
conversation = DatabaseActor.read_conversation(conversation_id=1)

# List conversations with pagination and ordering
conversations = DatabaseActor.list_conversations(page=1, page_size=10, order_by="title", order_direction="asc")

# Update a conversation
updated_conversation = DatabaseActor.update_conversation(conversation_id=1, data={"title": "Updated Title"})

# Delete a conversation
success = DatabaseActor.delete_conversation(conversation_id=1)

# Search conversations
conversations = DatabaseActor.search_conversations(query="search term")

# List conversations by archived status
conversations = DatabaseActor.list_conversations_by_archived_status(is_archived=True)

# List conversations created in a date range
from datetime import datetime

start = datetime(2023, 1, 1)
end = datetime(2023, 1, 31)
conversations = DatabaseActor.list_conversations_created_in_date_range(start_date=start, end_date=end)

# Count conversations
count = DatabaseActor.count_conversations()

# Archive a conversation
archived_conversation = DatabaseActor.archive_conversation(conversation_id=1)

Votes Table

This module defines the database schema for votes, including the Vote model and VoteManager class for managing votes.

The database schema includes tables for users, comments, and votes, with relationships defined between them. The Vote table stores information about individual votes, including the voter, target comment, and vote value.

Table Schemas
Table Name	Description
users	Stores user information (id, email, auth_token, etc.).
comments	Stores comment information (id, text, user_id, conversation_id, parent_comment_id, created, modified).
votes	Stores vote information (id, user_id, comment_id, value, created, modified).

Votes Table Details
Column Name	Description
id (int)	Primary key for the vote.
user_id (int, optional)	Foreign key referencing the user who created the vote.
comment_id (int, optional)	Foreign key referencing the comment being voted on.
value (int)	The value of the vote.
created (datetime)	Timestamp of when the vote was created.
modified (datetime)	Timestamp of when the vote was last modified.

Classes
Class Name	Description
Vote	SQLModel class representing the votes table.
VoteManager	Provides static methods for managing votes.

To use the methods in this module, import DatabaseActor from litepolis_database_default. For example:

from litepolis_database_default import DatabaseActor

vote = DatabaseActor.create_vote({
    "value": 1,
    "user_id": 1,
    "comment_id": 1
})

# Create a new vote
from litepolis_database_default import DatabaseActor

vote = DatabaseActor.create_vote({
    "value": 1,
    "user_id": 1,
    "comment_id": 1
})

# Read a vote by ID
vote = DatabaseActor.read_vote(vote_id=1)

# Get a vote by user and comment
vote = DatabaseActor.get_vote_by_user_comment(user_id=1, comment_id=1)

# List votes by comment ID with pagination and ordering
votes = DatabaseActor.list_votes_by_comment_id(comment_id=1, page=1, page_size=10, order_by="created", order_direction="asc")

# Update a vote
updated_vote = DatabaseActor.update_vote(vote_id=1, data={"value": -1})

# Delete a vote
success = DatabaseActor.delete_vote(vote_id=1)

# List votes by user ID with pagination
votes = DatabaseActor.list_votes_by_user_id(user_id=1, page=1, page_size=10)

# List votes created in a date range
from litepolis_database_default import DatabaseActor
from datetime import datetime

start = datetime(2023, 1, 1)
end = datetime(2023, 1, 31)
votes = DatabaseActor.list_votes_created_in_date_range(start_date=start, end_date=end)

# Count votes for a comment
count = DatabaseActor.count_votes_for_comment(comment_id=1)

# Get vote value distribution for a comment
distribution = DatabaseActor.get_vote_value_distribution_for_comment(comment_id=1)