Refactor database configuration and initialization

zaber-dev · zaber-dev · commit 034c232e7878 · 2024-09-15T22:46:33.000+06:00
- Update .env_example to include DATABASE_URL
- Modify config/boot.py to create database tables using SQLAlchemy
- Add database/db.py to define SQLAlchemy models and session management
diff --git a/.env_example b/.env_example
@@ -11,4 +11,5 @@ DB_HOST=                    # Database Host, for local use '127.0.0.1'
 DB_PORT=                    # Database Port
 DB_DATABASE=                # Database Name 
 DB_USERNAME=                # Database Username
-DB_PASSWORD=                # Database Password
+DB_PASSWORD=                # Database Password
+DATABASE_URL=               # Database URL, for local use 'sqlite:///database.db'
diff --git a/config/boot.py b/config/boot.py
@@ -33,9 +33,15 @@
 import glob
 from disnake.ext import commands
 import glob
+from database.db import engine, Base
+
 # Add the project root to the Python path
 sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
 
+# Create the database tables
+Base.metadata.create_all(bind=engine)
+
+# Check if the Flask app and Discord bot are enabled
 flask_enabled = os.environ.get('FLASK_HOST') # Check if the FLASK_HOST environment variable is set
 discord_enabled = os.environ.get('DISCORD_TOKEN') # Check if the DISCORD_BOT_TOKEN environment variable is set
 
@@ -56,12 +62,14 @@ def run_flask():
     print("Discord bot enabled")
     intents = discord.Intents.all() # Define the bot's intents
     bot = commands.Bot(command_prefix=os.environ.get('DISCORD_PREFIX','!'), intents=intents,)
-
+    
+    # Define the bot's event listeners
     @bot.event
     async def on_ready():
         print(f'Logged in as {bot.user}') # Print the bot's username when it's ready
 
     def load_cogs(bot):
+        # Load all cogs in the 'app/Commands/Context' and 'app/Commands/Slash' directories
         cog_directories = ['app/Commands/Context', 'app/Commands/Slash'] 
         for directory in cog_directories:
             for filename in glob.glob(f'{directory}/*.py'):
@@ -74,6 +82,7 @@ def load_cogs(bot):
                         print(f'Failed to load cog {cog}: {e}') # Print the name of the cog that failed to load
 
     def run_discord_bot():
+        # Start the Discord bot
         try:
             print("Starting Discord bot...")
             load_cogs(bot)
diff --git a/database/db.py b/database/db.py
@@ -0,0 +1,63 @@
+import os
+from sqlalchemy import create_engine, Column, Integer
+from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import sessionmaker, scoped_session
+
+DATABASE_URL = os.environ.get("DATABASE_URL")
+
+engine = create_engine(DATABASE_URL)
+SessionLocal = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
+Base = declarative_base()
+
+def get_db():
+    db = SessionLocal()
+    try:
+        yield db
+    finally:
+        db.close()
+
+class BaseModel(Base):
+    __abstract__ = True
+
+    id = Column(Integer, primary_key=True, index=True)
+
+    @classmethod
+    def create(cls, db_session, **kwargs):
+        instance = cls(**kwargs)
+        db_session.add(instance)
+        db_session.commit()
+        db_session.refresh(instance)
+        return instance
+
+    @classmethod
+    def get(cls, db_session, id):
+        return db_session.query(cls).filter(cls.id == id).first()
+
+    @classmethod
+    def all(cls, db_session):
+        return db_session.query(cls).all()
+
+    @classmethod
+    def update(cls, db_session, id, **kwargs):
+        instance = db_session.query(cls).filter(cls.id == id).first()
+        if instance:
+            for key, value in kwargs.items():
+                setattr(instance, key, value)
+            db_session.commit()
+            db_session.refresh(instance)
+        return instance
+
+    @classmethod
+    def delete(cls, db_session, id):
+        instance = db_session.query(cls).filter(cls.id == id).first()
+        if instance:
+            db_session.delete(instance)
+            db_session.commit()
+        return instance
+
+    @classmethod
+    def filter(cls, db_session, **kwargs):
+        query = db_session.query(cls)
+        for key, value in kwargs.items():
+            query = query.filter(getattr(cls, key) == value)
+        return query.all()
