codekingpro/portable-devtools
114k
1##########################################################################2#3# pgAdmin 4 - PostgreSQL Tools4#5# Copyright (C) 2013 - 2024, The pgAdmin Development Team6# This software is released under the PostgreSQL Licence7#8##########################################################################9 10"""Defines the models for the configuration database.11 12If any of the models are updated, you (yes, you, the developer) MUST do two13things:14 151) Increment SCHEMA_VERSION below16 172) Create an Alembic migratio to ensure that the appropriate changes are18 made to the config database to upgrade it to the new version.19"""20 21from flask_security import UserMixin, RoleMixin22from flask_sqlalchemy import SQLAlchemy23from sqlalchemy.ext.mutable import MutableDict24import sqlalchemy.types as types25import uuid26import config27 28##########################################################################29#30# The schema version is used to track when upgrades are needed to the31# configuration database. Increment this whenever changes are made to the32# model or data, AND ensure the upgrade code is added to setup.py33#34##########################################################################35 36SCHEMA_VERSION = 3937 38##########################################################################39#40# And now we return to our regularly scheduled programming:41#42##########################################################################43 44db = SQLAlchemy(45 engine_options={46 'pool_size': config.CONFIG_DATABASE_CONNECTION_POOL_SIZE,47 'max_overflow': config.CONFIG_DATABASE_CONNECTION_MAX_OVERFLOW})48 49 50USER_ID = 'user.id'51SERVER_ID = 'server.id'52 53# Define models54roles_users = db.Table(55 'roles_users',56 db.Column('user_id', db.Integer(), db.ForeignKey(USER_ID)),57 db.Column('role_id', db.Integer(), db.ForeignKey('role.id'))58)59 60 61class PgAdminDbBinaryString(types.TypeDecorator):62 """63 To make binary string storing compatible with both64 SQLite and PostgreSQL, convert the bin data to hex65 to store and convert hex back to binary to get66 """67 cache_ok = True68 impl = types.String69 70 def process_bind_param(self, value, dialect):71 return value.hex() if hasattr(value, 'hex') \72 else value73 74 def process_result_value(self, value, dialect):75 try:76 return bytes.fromhex(value)77 except Exception as _:78 return value79 80 81class Version(db.Model):82 """Version numbers for reference/upgrade purposes"""83 __tablename__ = 'version'84 name = db.Column(db.String(32), primary_key=True)85 value = db.Column(db.Integer(), nullable=False)86 87 88class Role(db.Model, RoleMixin):89 """Define a security role"""90 __tablename__ = 'role'91 id = db.Column(db.Integer(), primary_key=True)92 name = db.Column(db.String(128), unique=True, nullable=False)93 description = db.Column(db.String(256), nullable=False)94 95 96class User(db.Model, UserMixin):97 """Define a user object"""98 __tablename__ = 'user'99 id = db.Column(db.Integer, primary_key=True)100 email = db.Column(db.String(256), nullable=True)101 username = db.Column(db.String(64), unique=True, nullable=False)102 password = db.Column(PgAdminDbBinaryString())103 active = db.Column(db.Boolean(), nullable=False)104 confirmed_at = db.Column(db.DateTime())105 masterpass_check = db.Column(PgAdminDbBinaryString())106 roles = db.relationship('Role', secondary=roles_users,107 backref=db.backref('users', lazy='dynamic'))108 auth_source = db.Column(db.String(16), unique=True, nullable=False)109 # fs_uniquifier is required by flask-security-too >= 4.110 fs_uniquifier = db.Column(db.String(255), unique=True, nullable=False,111 default=(lambda _: uuid.uuid4().hex))112 login_attempts = db.Column(db.Integer, default=0)113 locked = db.Column(db.Boolean(), default=False)114 115 116class Setting(db.Model):117 """Define a setting object"""118 __tablename__ = 'setting'119 user_id = db.Column(db.Integer, db.ForeignKey(USER_ID), primary_key=True)120 setting = db.Column(db.String(256), primary_key=True)121 value = db.Column(db.Text())122 123 124class ServerGroup(db.Model):125 """Define a server group for the treeview"""126 __tablename__ = 'servergroup'127 id = db.Column(db.Integer, primary_key=True)128 user_id = db.Column(db.Integer, db.ForeignKey(USER_ID), nullable=False)129 130 name = db.Column(db.String(128), nullable=False)131 __table_args__ = (db.UniqueConstraint('user_id', 'name'),)132 133 @property134 def serialize(self):135 """Return object data in easily serializable format"""136 return {137 'id': self.id,138 'user_id': self.user_id,139 'name': self.name,140 }141 142 143class Server(db.Model):144 """Define a registered Postgres server"""145 __tablename__ = 'server'146 id = db.Column(db.Integer, primary_key=True)147 user_id = db.Column(148 db.Integer,149 db.ForeignKey(USER_ID),150 nullable=False151 )152 servergroup_id = db.Column(153 db.Integer,154 db.ForeignKey('servergroup.id'),155 nullable=False156 )157 name = db.Column(db.String(128), nullable=False)158 host = db.Column(db.String(128), nullable=True)159 port = db.Column(160 db.Integer(),161 db.CheckConstraint('port >= 1 AND port <= 65534'),162 nullable=False)163 maintenance_db = db.Column(db.String(64), nullable=True)164 username = db.Column(db.String(64), nullable=False)165 password = db.Column(PgAdminDbBinaryString())166 save_password = db.Column(167 db.Integer(),168 db.CheckConstraint('save_password >= 0 AND save_password <= 1'),169 nullable=False170 )171 role = db.Column(db.String(64), nullable=True)172 comment = db.Column(db.String(1024), nullable=True)173 discovery_id = db.Column(db.String(128), nullable=True)174 servers = db.relationship(175 'ServerGroup',176 backref=db.backref('server', cascade="all, delete-orphan"),177 lazy='joined'178 )179 db_res = db.Column(db.Text(), nullable=True)180 passexec_cmd = db.Column(db.Text(), nullable=True)181 passexec_expiration = db.Column(db.Integer(), nullable=True)182 bgcolor = db.Column(db.String(10), nullable=True)183 fgcolor = db.Column(db.String(10), nullable=True)184 service = db.Column(db.Text(), nullable=True)185 use_ssh_tunnel = db.Column(186 db.Integer(),187 db.CheckConstraint('use_ssh_tunnel >= 0 AND use_ssh_tunnel <= 1'),188 nullable=False189 )190 tunnel_host = db.Column(db.String(128), nullable=True)191 tunnel_port = db.Column(192 db.Integer(),193 db.CheckConstraint('port <= 65534'),194 nullable=True, default=22)195 tunnel_username = db.Column(db.String(64), nullable=True)196 tunnel_authentication = db.Column(197 db.Integer(),198 db.CheckConstraint('tunnel_authentication >= 0 AND '199 'tunnel_authentication <= 1'),200 nullable=False201 )202 tunnel_identity_file = db.Column(db.String(64), nullable=True)203 tunnel_password = db.Column(PgAdminDbBinaryString())204 tunnel_keep_alive = db.Column(db.Integer(), nullable=True, default=0)205 shared = db.Column(db.Boolean(), nullable=False)206 shared_username = db.Column(db.String(64), nullable=True)207 kerberos_conn = db.Column(db.Boolean(), nullable=False, default=0)208 cloud_status = db.Column(db.Integer(), nullable=False, default=0)209 connection_params = db.Column(MutableDict.as_mutable(types.JSON))210 prepare_threshold = db.Column(db.Integer(), nullable=True)211 212 213class ModulePreference(db.Model):214 """Define a preferences table for any modules."""215 __tablename__ = 'module_preference'216 id = db.Column(db.Integer, primary_key=True)217 name = db.Column(db.String(256), nullable=False)218 219 220class PreferenceCategory(db.Model):221 """Define a preferences category for each modules."""222 __tablename__ = 'preference_category'223 id = db.Column(db.Integer, primary_key=True)224 mid = db.Column(225 db.Integer,226 db.ForeignKey('module_preference.id'),227 nullable=False228 )229 name = db.Column(db.String(256), nullable=False)230 231 232class Preferences(db.Model):233 """Define a particular preference."""234 __tablename__ = 'preferences'235 id = db.Column(db.Integer, primary_key=True)236 cid = db.Column(237 db.Integer,238 db.ForeignKey('preference_category.id'),239 nullable=False240 )241 name = db.Column(db.String(1024), nullable=False)242 243 244class UserPreference(db.Model):245 """Define the preference for a particular user."""246 __tablename__ = 'user_preferences'247 pid = db.Column(248 db.Integer, db.ForeignKey('preferences.id'), primary_key=True249 )250 uid = db.Column(251 db.Integer, db.ForeignKey(USER_ID), primary_key=True252 )253 value = db.Column(db.String(1024), nullable=False)254 255 256class DebuggerFunctionArguments(db.Model):257 """Define the debugger input function arguments."""258 __tablename__ = 'debugger_function_arguments'259 server_id = db.Column(db.Integer(), nullable=False, primary_key=True)260 database_id = db.Column(db.Integer(), nullable=False, primary_key=True)261 schema_id = db.Column(db.Integer(), nullable=False, primary_key=True)262 function_id = db.Column(db.Integer(), nullable=False, primary_key=True)263 arg_id = db.Column(db.Integer(), nullable=False, primary_key=True)264 is_null = db.Column(265 db.Integer(),266 db.CheckConstraint('is_null >= 0 AND is_null <= 1'),267 nullable=False268 )269 is_expression = db.Column(270 db.Integer(),271 db.CheckConstraint(272 'is_expression >= 0 AND is_expression <= 1'273 ),274 nullable=False275 )276 use_default = db.Column(277 db.Integer(),278 db.CheckConstraint(279 'use_default >= 0 AND use_default <= 1'280 ),281 nullable=False282 )283 284 value = db.Column(db.String(), nullable=True)285 286 287class Process(db.Model):288 """Define the Process table."""289 __tablename__ = 'process'290 pid = db.Column(db.String(), nullable=False, primary_key=True)291 user_id = db.Column(292 db.Integer,293 db.ForeignKey(USER_ID),294 nullable=False295 )296 command = db.Column(db.String(), nullable=False)297 desc = db.Column(db.String(), nullable=False)298 arguments = db.Column(db.String(), nullable=True)299 logdir = db.Column(db.String(), nullable=True)300 start_time = db.Column(db.String(), nullable=True)301 end_time = db.Column(db.String(), nullable=True)302 exit_code = db.Column(db.Integer(), nullable=True)303 acknowledge = db.Column(db.String(), nullable=True)304 utility_pid = db.Column(db.Integer, nullable=False)305 process_state = db.Column(db.Integer, nullable=False)306 server_id = db.Column(307 db.Integer,308 db.ForeignKey('server.id'),309 nullable=True310 )311 312 313class Keys(db.Model):314 """Define the keys table."""315 __tablename__ = 'keys'316 name = db.Column(db.String(), nullable=False, primary_key=True)317 value = db.Column(db.String(), nullable=False)318 319 320class QueryHistoryModel(db.Model):321 """Define the history SQL table."""322 __tablename__ = 'query_history'323 srno = db.Column(db.Integer(), nullable=False, primary_key=True)324 uid = db.Column(325 db.Integer, db.ForeignKey(USER_ID), nullable=False, primary_key=True326 )327 sid = db.Column(328 db.Integer(), db.ForeignKey(SERVER_ID), nullable=False,329 primary_key=True)330 dbname = db.Column(db.String(), nullable=False, primary_key=True)331 query_info = db.Column(PgAdminDbBinaryString(), nullable=False)332 last_updated_flag = db.Column(db.String(), nullable=False)333 334 335class Database(db.Model):336 """337 Define a Database.338 """339 __tablename__ = 'database'340 id = db.Column(db.Integer, primary_key=True)341 schema_res = db.Column(db.String(256), nullable=True)342 server = db.Column(343 db.Integer,344 db.ForeignKey(SERVER_ID),345 nullable=False,346 primary_key=True347 )348 349 350class SharedServer(db.Model):351 """Define a shared Postgres server"""352 353 __tablename__ = 'sharedserver'354 id = db.Column(db.Integer, primary_key=True)355 osid = db.Column(356 db.Integer,357 db.ForeignKey(SERVER_ID),358 nullable=False359 )360 user_id = db.Column(361 db.Integer,362 db.ForeignKey(USER_ID)363 )364 server_owner = db.Column(365 db.String(128),366 db.ForeignKey('user.username')367 )368 servergroup_id = db.Column(369 db.Integer,370 db.ForeignKey('servergroup.id'),371 nullable=False372 )373 name = db.Column(db.String(128), nullable=False)374 host = db.Column(db.String(128), nullable=True)375 port = db.Column(376 db.Integer(),377 nullable=True)378 maintenance_db = db.Column(db.String(64), nullable=True)379 username = db.Column(db.String(64), nullable=False)380 password = db.Column(PgAdminDbBinaryString())381 save_password = db.Column(382 db.Integer(),383 db.CheckConstraint('save_password >= 0 AND save_password <= 1'),384 nullable=False385 )386 role = db.Column(db.String(64), nullable=True)387 comment = db.Column(db.String(1024), nullable=True)388 discovery_id = db.Column(db.String(128), nullable=True)389 servers = db.relationship(390 'ServerGroup',391 backref=db.backref('sharedserver', cascade="all, delete-orphan"),392 lazy='joined'393 )394 db_res = db.Column(db.Text(), nullable=True)395 bgcolor = db.Column(db.String(10), nullable=True)396 fgcolor = db.Column(db.String(10), nullable=True)397 service = db.Column(db.Text(), nullable=True)398 use_ssh_tunnel = db.Column(399 db.Integer(),400 db.CheckConstraint('use_ssh_tunnel >= 0 AND use_ssh_tunnel <= 1'),401 nullable=False402 )403 tunnel_host = db.Column(db.String(128), nullable=True)404 tunnel_port = db.Column(405 db.Integer(),406 db.CheckConstraint('port <= 65534'),407 nullable=True)408 tunnel_username = db.Column(db.String(64), nullable=True)409 tunnel_authentication = db.Column(410 db.Integer(),411 db.CheckConstraint('tunnel_authentication >= 0 AND '412 'tunnel_authentication <= 1'),413 nullable=False414 )415 tunnel_identity_file = db.Column(db.String(64), nullable=True)416 tunnel_password = db.Column(PgAdminDbBinaryString())417 tunnel_keep_alive = db.Column(db.Integer(), nullable=True)418 shared = db.Column(db.Boolean(), nullable=False)419 connection_params = db.Column(MutableDict.as_mutable(types.JSON))420 prepare_threshold = db.Column(db.Integer(), nullable=True)421 422 423class Macros(db.Model):424 """Define a particular macro."""425 __tablename__ = 'macros'426 id = db.Column(db.Integer, primary_key=True)427 alt = db.Column(db.Boolean(), nullable=False)428 control = db.Column(db.Boolean(), nullable=False)429 key = db.Column(db.String(32), nullable=False)430 key_code = db.Column(db.Integer, nullable=False)431 432 433class UserMacros(db.Model):434 """Define the macro for a particular user."""435 __tablename__ = 'user_macros'436 mid = db.Column(437 db.Integer, db.ForeignKey('macros.id'), primary_key=True438 )439 uid = db.Column(440 db.Integer, db.ForeignKey(USER_ID), primary_key=True441 )442 name = db.Column(db.String(1024), nullable=False)443 sql = db.Column(db.Text(), nullable=False)444 445 446class UserMFA(db.Model):447 """Stores the options for the MFA for a particular user."""448 __tablename__ = 'user_mfa'449 user_id = db.Column(db.Integer, db.ForeignKey(USER_ID), primary_key=True)450 mfa_auth = db.Column(db.String(64), primary_key=True)451 options = db.Column(db.Text(), nullable=True)452 user = db.relationship(453 'User',454 backref=db.backref('user', cascade="all, delete-orphan")455 )456