Implement Postgresql as option for database backend

[madeofpendletonwool]

[Siddharth9890]

This is a easy one @madeofpendletonwool please assign it to me if possible

[madeofpendletonwool]

You got it! I did start a branch where I began working on this called postgresql. The first part of it that I about finished up is that when the app if first started via docker container is kicks off a setupdatabase.py script to build all the tables if they don't already exist. I created setuppostgresdatabase.py and have gotten it to a point where it builds but there's issues yet with the database actually working fully. You're also welcome to go about it in an entirely different way if you prefer. Regardless, the most important aspect to this is the ability to just select postgressql in the compose file when starting and it just working seemlessly with that instead. Thanks in advance!

This should run mostly in that branch and might help you get started

version: '3'
services:
  db:
    image: mariadb:latest
    environment:
      MYSQL_TCP_PORT: 3306
      MYSQL_ROOT_PASSWORD: myS3curepass
      MYSQL_DATABASE: pypods_database
      MYSQL_COLLATION_SERVER: utf8mb4_unicode_ci
      MYSQL_CHARACTER_SET_SERVER: utf8mb4
      MYSQL_INIT_CONNECT: 'SET @@GLOBAL.max_allowed_packet=64*1024*1024;'
    volumes:
      - /home/collinp/wait/sql:/var/lib/mysql
    ports:
      - "3306:3306"
    restart: always
  pinepods:
    image: madeofpendletonwool/pinepods:latest
    ports:
      - "8040:8040"
    environment:
      # Basic Server Info
      HOSTNAME: localhost
      API_SERVER_PORT: 8032
      SEARCH_API_URL: 'https://search.pinepods.online/api/search'
      # Default Admin User Information
      USERNAME: myadminuser01
      PASSWORD: myS3curepass
      FULLNAME: Pinepods Admin
      EMAIL: [email protected]
      # Database Vars
      DB_TYPE: mariadb
      DB_HOST: db
      DB_PORT: 3306
      DB_USER: root
      DB_PASSWORD: myS3curepass
      DB_NAME: pypods_database
      # Image/Audio Proxy Vars
      PINEPODS_PORT: 8040
      PROXY_PROTOCOL: http
      REVERSE_PROXY: "False"
    volumes:
    # Mount the download location on the server if you want to. You could mount a nas to this folder or something like that
      - /home/collinp/wait/downloads:/opt/pypods/downloads
      - /home/user/pinepods/backups:/opt/pinepods/backups

    depends_on:
      - db

[rgarcia6520]

I see there are lots of postgres and postgresql references and functions. I am able to get the app to startup and add an initial user according to my docker ENV when addressing the section below but then upon login (pointing to postgresql v14) I see failures in the setuppostgresdatabase.py

Traceback (most recent call last):
  File "/pinepods/startup/setuppostgresdatabase.py", line 6, in <module>
    import bcrypt
ModuleNotFoundError: No module named 'bcrypt'

Adding bcrypt to requirements.txt fixes that, but then when logging in I get:

[2024-05-07 22:01:11] [ERROR] - clientapi: Traceback (most recent call last):
  File "/pinepods/clients/clientapi.py", line 114, in get_database_connection
    yield db
psycopg2.errors.UndefinedFunction: operator does not exist: boolean = integer
LINE 1: ...T SelfServiceUser FROM AppSettings WHERE SelfServiceUser = 1 

Looks like Psql 14 shows the following for the boolean:
appsettingsid | selfserviceuser | downloadenabled |             encryptionkey                                        
---------------------+-----------------------+----------------------------+----------------------------------------------------------------------
             1           | f                           | t                                | 111111111111111111111111111111111
(1 row)

[madeofpendletonwool]

Postgres is now supported! As of version 0.6.0