Roadmap to an external Database

The following tasks describe the way which is needed to use external databases

ToDo

• Implement Database-Settings dialog

  • Fix ResetSettingsUtils.js to support nested properties

• Modify database model

• Handle concurrent user modifications

  • Add version columns and implement "OptimisticLockException", last user wins!
  • Send version number to frontend

• Upgrade mechanism

  • On startup: check which database scheme is used in external DB. Ask user what to do, show him the 'Alter-Script' -> disable Plugin if not matched! All OP-Server needs to be used the same DB Scheme Version

• Support of postgres

• Support of mySql

Ideas

• RDBS Scheme DRAFT

Development will try to support the most common db products besides sqlite (atow: postgresql, mysql and mariadb)

Available Fields (lowest common denominator)

Field Type	Sqlite	Postgresql	mysql	Special
AutoField	integer	serial	integer
BigAutoField	integer	bigserial	bigint
IntegerField	integer	integer	integer
BigIntegerField	integer	bigint	bigint
SmallIntegerField	integer	smallint	smallint
FloatField	real	real	real
DoubleField	real	double precision	double precision
DecimalField	decimal	numeric	numeric	max_digits, decimal_places, auto_round, rounding
CharField	varchar	varchar	varchar	max_length
FixedCharField	char	char	char	max_length
TextField	text	text	text
BlobField	blob	bytea	blob
BitField	integer	bigint	bigint
BigBitField	blob	bytea	blob
UUIDField	text	uuid	varchar(40)
BinaryUUIDField	blob	bytea	varbinary(16)
DateTimeField	datetime	timestamp	datetime	formats
DateField	date	date	date	formats
TimeField	time	time	time	formats
TimestampField	integer	integer	integer	resolution, utc
IPField	integer	bigint	bigint
BooleanField	integer	boolean	bool
ForeignKeyField	integer	integer	integer	model, field, backref, on_delete, on_update, deferrable lazy_load

see http://docs.peewee-orm.com/en/latest/peewee/models.html

• Special values: default, choices, constraints, null
• ...

How To

Setup PostgresDB or mysql via docker

Use the docker-compose.yml in the root master branch.

Execute docker-compose up to start the both databases

Execute docker-compose up postgres to start postgres database

Execute docker-compose up mysql to start mysql database

Execute docker-compose down to shutdown all databases

Connection settings:

host = localhost
port = 5432 or 3306
database name = spoolmanagerdb
user = Olli
password = illO

see https://medium.com/analytics-vidhya/getting-started-with-postgresql-using-docker-compose-34d6b808c47c

---

Setup MariaDB/MySQL

It makes no difference whether you choose MariaDB or MySQL and the picture above is self-explanatory, only the port deviates by default to 3306. You can use any remote DB System, as long as your Firewall/Access rules allow your connection. If you want a local installation choose any of the following methods.

Finally continue with the Common OctoPrint part below.

SNAP

See Debian installing snap and select a snap package for mysql

DOCKER

For docker MariaDB read Installing and Using MariaDB via Docker, for MySQL read MySQL Docker Container Tutorial

Debian native

apt install default-mysql-server

Common within OctoPrint System

The Python mysql driver has to be installed for these connections.

# cd to your active (v)env
pip(3)? install pymysql

Mockup / Images