levlaz

Posts tagged: databases

SQLite DB Migrations with PRAGMA user_version

2017-12-06 06:26:13 ][ Tags: python databases sqlite

This blog is using a simple homegrown blogging engine that I wrote backed by a SQLite database. I have a function in the flask app that performs database migrations. My current approach has been to keep a folder full of migrations and run them sequentially whenever the app starts.

This works well for the case of adding and removing tables since SQLite has the handy IF NOT EXISTS option. However, when you are altering an existing table, this entire model falls apart since IF NOT EXISTS no longer works.

Practically, this means that outside of a fresh install my database migrations are useless.

I am still being stubborn and not using a well written solution like Alembic (which I would highly recommend for a "serious" project) for this blog. Instead, I discovered that SQLite comes with a built in mechanism to keep track of the user schema. This is the pragma statement, and specifically user_version.

Using PRAGMA user_data for DB Migrations

My migrations folder structure looks like this:

.
├── blog.db
├── blog.py
├── __init__.py
├── migrations
│   ├── 0001_initial_schema.sql
│   ├── 0002_add_unique_index_to_posts_tags.sql
│   ├── 0003_add_fts.sql
│   ├── 0004_add_column_to_post.sql
│   ├── 0005_add_comments_table.sql
│   └── 0006_add_admin_flag_to_comments.sql

As you can see the naming convention is 000N_migration_description.sql. Each migration file has the following statement in it:

PRAGMA user_version=N; (where N is the 000"N" part of the file name)

This steps the current user_version to be equal to the current version as defined by the file name.

The code to do stuff with the database is shown below:

def connect_db():
    """Connects to Database."""
    rv = sqlite3.connect(
        app.config['DATABASE'],
        detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
    rv.row_factory = sqlite3.Row
    return rv


def get_db():
    """Opens new db connection if there is not an
    existing one for the current app ctx.
    """
    if not hasattr(g, 'sqlite_db'):
        g.sqlite_db = connect_db()
    return g.sqlite_db


def migrate_db():
    """Run database migrations."""

    def get_script_version(path):
        return int(path.split('_')[0].split('/')[1])

    db = get_db()
    current_version = db.cursor().execute('pragma user_version').fetchone()[0]

    directory = os.path.dirname(__file__)
    migrations_path = os.path.join(directory, 'migrations/')
    migration_files = list(os.listdir(migrations_path))
    for migration in sorted(migration_files):
        path = "migrations/{0}".format(migration)
        migration_version = get_script_version(path)

        if migration_version > current_version:
            print("applying migration {0}".format(migration_version))
            with app.open_resource(path, mode='r') as f:
                 db.cursor().executescript(f.read())
                 print("database now at version {0}".format(migration_version))
        else:
            print("migration {0} already applied".format(migration_version))

The relevant part to this blog post is the migrate_db() function. Two things are happening.

  1. The get_script_version() helper function extracts the integer from the migration name.
  2. current_version gets the current value of user_version of your database.
  3. We iterate over each migration file in the migrations folder and perform a simple check. If the migration version is larger than the current_version we run the migration, otherwise it gets skipped.

This solves for most cases and allows for a smooth upgrade path if anyone ever decides to start using this blogging engine for themselves. I am still pretty happy with this approach because this is essentially a fully functional migration system in just a handful of lines of python.

Tunnel to Production PostgreSQL Database

2016-11-14 00:57:42 ][ Tags: databases postgresql

As any responsible sys admin, I only allow local connections to my production database server. This means that if I need to access my DB from my local machine I would most likely need to use an SSH tunnel in order to connect.

For some reason, pgadmin3 no longer seems happy on Ubuntu 16.04 LTS and I am not able to make an SSH tunnel. In addition, it is a bit annoying that you are not able to save passwords with an SSH tunnel by default in pgadmin3, especially since my password is a long and random 50 character string.

The solution is pretty simple using the -L SSH flag.

ssh -L 15432:localhost:5432 $USER@$SERVER

This command creates a tunnel on my  production server, and forwards port 5432 (the default PostgreSQL port) to my local port 15432. This allows me to connect using pgadmin3 as if the database were running on my local machine.

Remote Root Access for MariaDB on Ubuntu 16.04 LTS

2016-10-13 21:13:09 ][ Tags: databases mysql

Typically, when you install MySQL on Ubuntu, it asks you for a root password which you can then use to log into the database. It seems that MariaDB uses a plugin instead to authenticate the root user. This means that the only way to log into MariaDB by default as root is locally with sudo mysql -u root In order to give the root user a password and login the "traditional" way (which includes remote access via a tunnel) you have to do the following.

  1. Log into MariaDB as the Root user

    sudo mysql -u root
    
  2. Disable the Auth Plugin

    use mysql;
    update user set plugin='' where User='root';
    
  3. Create a password for the root user

    grant all privileges on *.* to 'root'@'127.0.0.1' identified by '$STRONG_PASSWORD';
    flush privileges;
    
  4. Restart MariaDB

    sudo service mysql Restart
    

You can now access the root account with a password, and also over an SSH tunnel remotely.

Backing up and Restoring MySQL with mysqldump

2016-10-13 21:12:37 ][ Tags: databases mysql

I backup and restore databases across servers every few months, but each time I have to resort to reading this very verbose documentation. The steps below are a no fuss way to do this each time.

Backup Server

SSH into the server with the database that you wish to backup run the following command.

mysqldump -u root -p $DB_NAME > $DB_NAME.sql

Copy the File to Destination Server

Using scp, we can securely transfer the backup

scp $DB_NAME.sql $USER@$SERVER:

Restore on Destination Server

SSH into the server with the database that you wish to restore. From the the previous step the backup file should now be located in the root directory.

  1. Create new database

    mysql -u root -p -e 'CREATE DATABASE $DB_NAME'
    
  2. Restore your backup

    mysql -u root -p $DB_NAME < $DB_NAME.sql
    

Recreating Foreign Keys with Alembic

2016-05-30 18:59:18 ][ Tags: hacking python databases

Alembic is a great tool for keeping track of schema changes in python applications. I am using it to manage DB migrations for braindump along with Flask SQL Alchemy as my ORM. One challenge is managing proper foreign key constraints. By default if you define a foreign key relationship in your schema definition it will not generate the proper migration code. For example, in braindump we have a one to many relationship between users and notes.

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(254), unique=True, index=True)
    password_hash = db.Column(db.String(256))
    confirmed = db.Column(db.Boolean, default=False)
    avatar_hash = db.Column(db.String(32))
    created_date = db.Column(db.DateTime(), default=datetime.utcnow)
    updated_date = db.Column(db.DateTime(), default=datetime.utcnow)

    notes = db.relationship(
        'Note', backref='author',
        lazy='dynamic', cascade="all, delete-orphan")

Even though we define the cascade behavior using SQLAlchemy. When we generate the migration with alembic we get something like this:

sa.ForeignKeyConstraint(['author_id'], ['users.id'], ),

Notice how we are missing the ondelete action. What we actually want is something like this:

sa.ForeignKeyConstraint(['author_id'], ['users.id'], ondelete='CASCADE')

Running the default migration will not create the proper relationship in your database and in our case we are not able to delete a user until we have deleted all of the related records as well. There are two ways to fix this. If you catch this before running your migration adding ondelete='CASCADE' will create the proper relationship. If you are like me, and do not catch this, then you will need to run a second migration to remove and recreate these keys. The migration code to do this is shown below:

from alembic import op
import sqlalchemy as sa

def upgrade():
    with op.batch_alter_table("notes") as batch_op:
        batch_op.drop_constraint(
            "notes_author_id_fkey", type_="foreignkey")
    op.create_foreign_key(
        "notes_author_id_fkey", "notes", "users",
        ["author_id"], ["id"], ondelete="CASCADE")

Now you have the proper foreign key constraints and the CASCADE action exists in the DB.

Types and Roles If Not Exists in PostgreSQL

2016-03-28 18:46:59 ][ Tags: databases postgresql

For certain operations such as creating types and roles in PostgreSQL you are not able to use the handy IF NOT EXISTS parameter. This makes creating reusable scripts a bit more challenging, especially if you are going to be running these scripts more than once against the same database. On solution to this problem is to Just Do It™. DO allows you to execute anonymous code blocks. For example, suppose we had a simple task table with a custom type called task_status along with a reader role that has only select permissions. Using the pg_type and pg_role tables we can write a DO expression that will check to make sure that the elements do not exist before attempting to create them, which will prevent your script from erroring out in the event that they do exist. First we will create the custom type.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'task_status') THEN
        CREATE TYPE task_status AS ENUM ('todo', 'doing', 'blocked', 'done');
    END IF;
END
$$;

Then we can create our table.

CREATE TABLE IF NOT EXISTS
tasks (
  id           integer PRIMARY KEY,
  title        varchar(200), 
  status       task_status NOT NULL DEFAULT 'todo',
  created_date timestamp
);

Next, we can create our new role called reader and grant read only rights on the tasks table.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'reader') THEN
        CREATE ROLE reader;
    END IF;
END
$$;
GRANT SELECT ON tasks TO reader;

The entire script is shown below.

You can run this script as many times as you wish and it will just work™, which means that if you are running full migrations then you do not have to do any manual overrides or comment anything out. Note that the reason why we do not have to wrap up the GRANT in a check is because if you duplicate a grant, PostgreSQL will return a notice but not an error.

Connect Libreoffice Base to MySQL

2014-11-12 00:25:31 ][ Tags: databases

I think LibreOffice Base has so much underutilized potential as a rapid application development platform, business intelligence platform and just a general reporting platform. Not to mention the fact that registered data objects can be used in all of the other LibreOffice applications to make really amazing documents and improve the work-flow of any office. Anyone who has actually used MS Access knows how powerful it can be used to be for these types of purposes. The most recent version of Access seems to have lost a lot of the features that made it useful. This is okay since most power-users are still using Access 2003. LibreOffice Base is not nearly as powerful as Access, specifically from a usability perspective. My biggest frustration with getting started with LibreOffice Base is the obscure and somewhat cryptic documentation around the platform that makes way too many assumptions about what someone new to LibreOffice actually knows. My hope is to provide some practical tutorials with real world use cases. So let’s get started by connecting Base to an existing MySQL database. In my opinion, the built in HSQL engine has a somewhat weird syntax and is generally not worth learning unless you are not planning on ever actually writing any SQL and only using the built in wizards. I would prefer to work with MySQL databases because they are ubiquitous, use a “standard” syntax and very powerful. In addition most practical office use cases will involve a central database and not a local database.

Preparing Your MySQL Server

This is the part of the documentation that I find most obscure and confusing, so here is how to do it using LibreOffice 4.3 running on Ubuntu 14.04 LTS. The steps here will be slightly different depending on if you are developing on a local database or a remote database. If you are using a local MySQL database please feel free to skip this section. I do most of my database development inside of Linux Containers which essentially makes my databases “remote”. In order to allow remote connections we need to make a few changes to the default MySQL configuration. Please note that if you are doing these steps on a live production system you will need to be extra careful with users, permissions, ports that are opened, etc.. This falls outside of the scope of this tutorial but the rule of thumb is that if your database accepts connections from the outside world you should white list each IP address that will be connecting to it and block all others. The easiest way to do this in my opinion is with your firewall. By default MySQL only runs on the local host and is not accessible from remote hosts. To change this setting you need to edit the my.cnf file. 1) Open up my.cnf which is found in /etc/mysql/my.cnf 2) Find the bind-address and change if from the local host to the IP address of the server.
bind-address 10.0.1.30
3) Restart MySQL
sudo service mysql restart

Install the MySQL JDBC Driver

On Ubuntu 14.04 this is very easy and can be done by running the following command:
sudo apt-get install libmysql-java

Configure the Class Path in LibreOffice

Open up any LibreOffice App and go to Tools -> Options On the right hand side navigate to LibreOffice -> Advanced Select on the Class Path… button and load the new driver that was installed in the previous step. In order to do this you will need to select Add Archive… and select /usr/share/java/mysql.jar Once this has been loaded restart LibreOffice

Connect to your Database

Now comes the fun part. Now that we have taken care of all of the previous steps the rest is easy. To connect to your database open up LibreOffice Base.
  1. In the Database Wizard select Connect an existing database and chose the MySQL option from the dropdown menu.
  2. Select Connect using JDBC and hit next
  3. Enter the database name, server IP, port number and select next
  4. Enter the username for an existing user in your database and select next
  5. If you wish to use this database in other LibreOffice applications you should select the Yes, register the database for me radio button.
  6. Select Finish
Congratulations! Now you can rock some custom queries, fancy forms, and TSP reports using Base. We will go through how to do all of that an more in future posts.