Database Configuration

Overview

FreeTAKServer uses SQLAlchemy ORM for database operations with support for SQLite and MySQL. The database stores:

User information and authentication
Cursor on Target (CoT) events
Data packages
Video streams
Emergency notifications
Federation configurations
ExCheck checklists
API users and access logs

Database settings are managed through the DatabaseConfiguration class located in FreeTAKServer/core/configuration/DatabaseConfiguration.py.

Database Type

DataBaseType

string

default:"SQLite"

Database engine to use. Supported values: SQLite, MySQLEnvironment variable: FTS_DATABASE_TYPEYAML: System.FTS_DATABASE_TYPE

Database Path

DBFilePath

string

default:"/opt/fts/FTSDataBase.db"

Path to the database file (SQLite) or connection details (MySQL)Environment variable: FTS_DB_PATHYAML: Filesystem.FTS_DB_PATH

CoT Database Storage

SaveCoTToDB

boolean

default:"true"

Whether to save Cursor on Target (CoT) messages to the databaseEnvironment variable: FTS_COT_TO_DBYAML: Filesystem.FTS_COT_TO_DB

Connection Strings

The DatabaseConfiguration class automatically generates SQLAlchemy connection strings based on the database type.

SQLite Configuration

from FreeTAKServer.core.configuration.DatabaseConfiguration import DatabaseConfiguration

config = DatabaseConfiguration()
# Connection string: sqlite:////opt/fts/FTSDataBase.db

YAML Configuration:

System:
  FTS_DATABASE_TYPE: "SQLite"

Filesystem:
  FTS_DB_PATH: "/opt/fts/FTSDataBase.db"
  FTS_COT_TO_DB: true

Environment Variables:

export FTS_DATABASE_TYPE="SQLite"
export FTS_DB_PATH="/opt/fts/FTSDataBase.db"
export FTS_COT_TO_DB=true

MySQL Configuration

YAML Configuration:

System:
  FTS_DATABASE_TYPE: "MySQL"

Filesystem:
  FTS_DB_PATH: "username:password@localhost/freetakserver"
  FTS_COT_TO_DB: true

Environment Variables:

export FTS_DATABASE_TYPE="MySQL"
export FTS_DB_PATH="username:password@localhost/freetakserver"
export FTS_COT_TO_DB=true

Connection String Format:

mysql://username:password@host:port/database_name

Example:

mysql://ftsuser:[email protected]:3306/freetakserver

Database Controller

The DatabaseController class (FreeTAKServer/core/persistence/DatabaseController.py) provides the main interface for database operations.

Initialization

from FreeTAKServer.core.persistence.DatabaseController import DatabaseController

db = DatabaseController()

The controller automatically:

Creates the SQLAlchemy engine with the configured connection string
Creates a session maker
Creates an active session
Initializes table controllers for each data type
Creates all database tables if they don’t exist
Creates a default admin user on first run

Default Admin User

On first initialization, a default system user is created:

uid: "1"
name: "admin"
password: "password"
token: "token"
device_type: "mobile"

Change the default admin credentials immediately after installation for security.

Database Tables

FreeTAKServer creates and manages the following database tables:

User Tables

User: TAK client users and their CoT information
SystemUser: System administrators and API users
APIUsers: REST API user authentication

Event Tables

Event: CoT events (when SaveCoTToDB is enabled)
Archive: Archived CoT data
Chat: Chat messages
Emergency: Emergency notifications
ActiveEmergencys: Currently active emergencies

Data Tables

DataPackage: Uploaded data packages
VideoStream: Video stream configurations
_Video: Video metadata

Checklist Tables

ExCheck: ExCheck templates
ExCheckChecklist: ExCheck checklist instances
ExCheckKeywords: Keywords for ExCheck
ExCheckData: ExCheck data entries

Federation Tables

Federations: Federation server configurations
ActiveFederations: Currently active federation connections

Support Tables

_Group: CoT group information
Color: CoT color information
Contact: Contact details
Dest: Destination information
Link: CoT links
Marti: Marti-specific data
Precisionlocation: High-precision location data
Remarks: CoT remarks
Serverdestination: Server destination routing
Status: Status information
Summary: CoT summaries
Takv: TAK version information
Track: Tracking data
Uid: Unique identifiers
Usericon: User icon data

Logging Tables

APICalls: REST API access logs

Database Operations

The DatabaseController provides CRUD operations for all tables.

Create Operations

from FreeTAKServer.core.persistence.DatabaseController import DatabaseController

db = DatabaseController()

# Create a data package
db.create_datapackage(
    PrimaryKey=1,
    Hash="abc123",
    Name="mission_plan.zip",
    SubmissionDateTime="2024-03-04T10:00:00Z"
)

# Create a system user
db.create_systemUser(
    uid="user123",
    name="operator",
    password="hashed_password",
    token="auth_token",
    device_type="desktop"
)

# Create an API user
db.create_APIUser(
    Username="api_user",
    Password="hashed_password",
    token="api_token"
)

Query Operations

# Query with filter expression
users = db.query_user(query='callsign == "ALPHA-1"')

# Query all records
all_packages = db.query_datapackage(query="1=1")

# Query specific columns
user_names = db.query_systemUser(
    query="1=1",
    column=['name', 'uid']
)

# Query by parameters
user = db.query_by_systemUser(
    columns=['*'],
    name="admin"
)

Update Operations

# Update user information
db.update_user(
    column_value={'callsign': 'BRAVO-2'},
    query='uid == "user123"'
)

# Update data package
db.update_datapackage(
    column_value={'Name': 'updated_name.zip'},
    query='Hash == "abc123"'
)

Delete Operations

# Delete by query
db.remove_user(query='uid == "old_user"')

# Delete data package
db.remove_datapackage(query='Hash == "abc123"')

# Delete all (use with caution!)
db.remove_CoT(query="1=1")

Session Management

The DatabaseController manages SQLAlchemy sessions automatically.

Session Creation

# Sessions are created automatically
db = DatabaseController()

# Access the active session
session = db.session

Manual Session Creation

# Create a new session
new_session = db.create_Session()

# Use the session
try:
    # Database operations
    new_session.commit()
finally:
    new_session.close()

Shutdown Connection

# Close session and dispose engine
db.shutdown_Connection()

CoT Event Storage

When SaveCoTToDB is enabled, all CoT events are stored in the database.

Creating CoT Events

from FreeTAKServer.model.SQLAlchemy.CoTTables import Event

# Create CoT event object
cot_event = Event.Event()
cot_event.uid = "unique-event-id"
cot_event.type = "a-f-G-E-V-C"
cot_event.how = "m-g"
# ... set other properties

# Save to database
db.create_CoT(cot_event)

Querying CoT Events

# Get recent events
recent_events = db.query_CoT(
    query='time > "2024-03-04T00:00:00Z"',
    column=['uid', 'type', 'time']
)

# Get events by type
friendly_units = db.query_CoT(
    query='type LIKE "a-f-%"'
)

Disabling CoT Storage

To reduce database size and improve performance, you can disable CoT storage:

Filesystem:
  FTS_COT_TO_DB: false

Disabling CoT storage reduces database growth but prevents historical event queries and replay functionality.

Database Migrations

FreeTAKServer uses SQLAlchemy’s metadata system for automatic table creation.

Automatic Migration

On startup, the database controller automatically:

Connects to the database
Checks for the SystemUser table
If tables don’t exist, creates all tables from the Base metadata
Creates default admin user if it’s a new installation

def create_engine(self):
    engine = create_engine(DatabaseConfiguration().DataBaseConnectionString, echo=False)
    with engine.connect() as connection:
        if not connection.dialect.has_table(connection, "SystemUser"):
            Base.metadata.create_all(engine)
            # Create default admin user
            tempsession = sessionmaker(bind=engine)()
            tempsession.add(SystemUser(uid="1", name="admin", ...))
            tempsession.commit()
            tempsession.close()
    return engine

Manual Table Creation

from sqlalchemy import create_engine
from FreeTAKServer.model.SQLAlchemy.Root import Base
from FreeTAKServer.core.configuration.DatabaseConfiguration import DatabaseConfiguration

engine = create_engine(DatabaseConfiguration().DataBaseConnectionString)
Base.metadata.create_all(engine)

Database Backup

SQLite Backup

# Stop FreeTAKServer first
sudo systemctl stop fts

# Backup database
cp /opt/fts/FTSDataBase.db /backup/FTSDataBase_$(date +%Y%m%d).db

# Restart FreeTAKServer
sudo systemctl start fts

MySQL Backup

# Dump database
mysqldump -u ftsuser -p freetakserver > fts_backup_$(date +%Y%m%d).sql

# Restore database
mysql -u ftsuser -p freetakserver < fts_backup_20240304.sql

Performance Optimization

SQLite Optimization

Disable CoT Storage (if not needed):

Filesystem:
  FTS_COT_TO_DB: false

Use WAL Mode (Write-Ahead Logging):

from sqlalchemy import create_engine, event

engine = create_engine(
    'sqlite:////opt/fts/FTSDataBase.db',
    connect_args={'check_same_thread': False}
)

@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_conn, connection_record):
    cursor = dbapi_conn.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA synchronous=NORMAL")
    cursor.close()

MySQL Optimization

Connection Pooling:

engine = create_engine(
    'mysql://user:pass@localhost/fts',
    pool_size=10,
    max_overflow=20,
    pool_recycle=3600
)

Indexes (automatically created by SQLAlchemy for primary and foreign keys)

Database Troubleshooting

Common Issues

Database locked (SQLite):

Ensure only one FreeTAKServer instance is running
Check for stuck processes: lsof /opt/fts/FTSDataBase.db
Enable WAL mode for better concurrency

Connection refused (MySQL):

Verify MySQL service is running: systemctl status mysql
Check connection string format
Verify user permissions: GRANT ALL ON freetakserver.* TO 'ftsuser'@'localhost'

Table doesn’t exist:

Delete database file and restart (SQLite)
Run manual table creation script
Check database permissions

Slow queries:

Disable CoT storage if not needed
Increase MainLoopDelay to reduce database writes
Use MySQL instead of SQLite for large deployments

Example Configurations

Development (SQLite)

System:
  FTS_DATABASE_TYPE: "SQLite"

Filesystem:
  FTS_DB_PATH: "./dev_data/FTSDataBase.db"
  FTS_COT_TO_DB: true

Production (SQLite)

System:
  FTS_DATABASE_TYPE: "SQLite"

Filesystem:
  FTS_DB_PATH: "/opt/fts/FTSDataBase.db"
  FTS_COT_TO_DB: false  # Disable for performance

Production (MySQL)

System:
  FTS_DATABASE_TYPE: "MySQL"

Filesystem:
  FTS_DB_PATH: "ftsuser:[email protected]:3306/freetakserver"
  FTS_COT_TO_DB: true

Best Practices

Always backup your database before upgrading FreeTAKServer or making configuration changes.

For deployments with more than 50 concurrent clients, consider using MySQL instead of SQLite for better concurrent write performance.

Regular database backups (automated daily backups recommended)
Monitor database size and implement retention policies
Use MySQL for production deployments with many clients
Disable CoT storage if historical event data is not needed
Implement database backup automation
Set appropriate filesystem permissions on database files
Use connection pooling for MySQL deployments
Monitor database performance and adjust MainLoopDelay accordingly
Keep database files on fast storage (SSD recommended)
Regularly vacuum SQLite databases to reclaim space

Get Started

Core Concepts

Guides

Configuration

​Overview