from sqlalchemy.dialects.postgresql import ARRAY, DATERANGE, JSONB
from sqlalchemy.sql import text, select
from sqlalchemy_utils import DateRangeType
from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey, TIMESTAMP
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
import config as cfg
import utils
from marshmallow_sqlalchemy import ModelSchema
from marshmallow import fields
SQLALCHEMY_DATABASE_URI = "postgresql+psycopg2://kell:124124fwqf32@%s/kell" % cfg.DOMAIN
db = create_engine(SQLALCHEMY_DATABASE_URI)
connection = db.connect()
Session = sessionmaker(bind=db)
db_session = Session()
Base = declarative_base()
class Company(Base):
__tablename__ = "companies"
company_id = Column(Text, primary_key=True)
name = Column(Text)
address = Column(Text, default="Moscow")
about = Column(Text)
status = Column(Text, default="enable")
data = Column(JSONB)
emblem = Column(Text, default="")
city = Column(Text, ForeignKey("cities.code"), default="177.MSK")
COMPANY_DETAIL_TEMPLATE = {
"1": {"companyMenu": {"categories": [], "dishs": []}},
"2": {},
"3": {},
"4": {},
"5": {}
}
def compose(self: Company, params: dict, create_user: bool = True):
# Делаем уникальный суффикс компании
company_suffix = utils.crc32(params)
self.company_id = "-".join(
[params["city"], params["category"], company_suffix]) # Делаем id компании
self.name = params["name"]
self.about = params["about"]
self.data = self.COMPANY_DETAIL_TEMPLATE[params["category"]]
self.address = params["address"]
self.city = params["city"]
db_session.add(self)
if create_user:
user_params = {
"role": 0,
"login": self.company_suffix,
"password": utils.generate_password(8)
}
company_user = User(
company_id=self.company_id).compose(params=user_params)
db_session.add(company_user)
class User(Base):
__tablename__ = "users"
user_id = Column(Integer, primary_key=True)
role = Column(Integer, default=-1)
login = Column(Text)
password = Column(Text)
company_id = Column(Text, ForeignKey("companies.company_id"))
company = relationship(Company)
def compose(self: User, params: dict):
self.role = params["role"]
self.login = params["login"]
self.password = params["password"]
class Post(Base):
__tablename__ = "posts"
post_id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey("users.user_id"))
company_id = Column(Text, ForeignKey("companies.company_id"))
title = Column(Text)
data = Column(Text)
release_date = Column(ARRAY(TIMESTAMP))
address = Column(Text)
img_url = Column(Text)
second_img_url = Column(Text)
tags = Column(ARRAY(Text))
priority = Column(Integer, default=0)
cost = Column(Integer, default=0)
company = relationship("Company", lazy="joined")
class Tag(Base):
__tablename__ = "tags"
tag_id = Column(Integer, primary_key=True)
tag = Column(Text, primary_key=True)
class File(Base):
__tablename__ = "files"
file_id = Column(Integer, primary_key=True)
company_id = Column(Text, ForeignKey("companies.company_id"))
name = Column(Text)
filename = Column(Text)
url = Column(Text)
class City(Base):
__tablename__ = "cities"
code = Column(Text, primary_key=True)
name = Column(Text)
class Category(Base):
__tablename__ = "categories"
id = Column(Text, primary_key=True)
name = Column(Text)
short_name = Column(Text)
class Route(Base):
__tablename__ = "routes"
id = Column(Integer, primary_key=True)
name = Column(Text, default="Маршрут")
mapid = Column(Text)
about = Column(Text)
img_url = Column(Text)
class Place(Base):
__tablename__ = "places"
id = Column(Integer, primary_key=True)
name = Column(Text, default="Место")
address = Column(Text)
about = Column(Text)
img_url = Column(Text)
company_id = Column(Text, ForeignKey("companies.company_id"))
category_id = Column(Text, ForeignKey("categories.id"))
tags = Column(ARRAY(Text))
priority = Column(Integer, default=0)
# Marshmallow Schemas
class CompanySchema(ModelSchema):
class Meta:
model = Company
fields = ("name", "company_id", "address", "about", "status", "data",
"emblem")
class UserSchema(ModelSchema):
class Meta:
model = User
fields = ("user_id", "company_id", "role", "login", "password")
class PostSchema(ModelSchema):
company_name = fields.Str()
class Meta:
model = Post
fields = ("post_id", "company_id", "user_id", "title", "data",
"release_date", "address", "img_url", "second_img_url",
"tags", "priority", "cost")
class TagSchema(ModelSchema):
class Meta:
model = Tag
class CitySchema(ModelSchema):
class Meta:
model = City
class FileSchema(ModelSchema):
class Meta:
model = File
fields = ("file_id", "company_id", "name", "filename", "url")
class CategorySchema(ModelSchema):
class Meta:
model = Category
class RouteSchema(ModelSchema):
class Meta:
model = Route
class PlaceSchema(ModelSchema):
class Meta:
model = Place
fields = ("img_url", "id", "name", "address",
"about", "company_id", "tags", "priority")
def result_to_json(result, schema):
return schema().dump(result).data
