Manutenzione MySQL

Il seguente script permette di analizzare e deframmentare le tabelle presenti in un database MySQL:

# -*- coding: utf-8 -*-
import MySQLdb
import sys

def optimize(host, db, user, password):
    con = MySQLdb.connect(host=host, db=db, user=user, passwd=password)
    cur = con.cursor()
    cur.execute("SHOW TABLES")
    for item in cur.fetchall():
        print "Optimizing table " + item[0] + "...",
        cur.execute("ANALYZE TABLE " + item[0])
        cur.execute("OPTIMIZE TABLE " + item[0])
        
        print "Done"
    cur.close()
    con.close()

if __name__ == "__main__":
    if len(sys.argv[1:]) > 0:
        optimize(sys.argv[1], sys.argv[2], sys.argv[3], sys.argv[4])
    else:
        print "usage: " + sys.argv[0] +
              " <hostname> <database> <user> <password>"

Manutenzione Firebird

Il seguente script permette di reindicizzare e ricalcolare le statistiche di un database Firebird:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
import kinterbasdb

def reindex(db_name, hostname, username, pwd):
    query_index=("SELECT RDB$INDEX_NAME FROM RDB$INDICES " +
                "WHERE COALESCE(RDB$UNIQUE_FLAG,0)<>1 " +
                "AND RDB$FOREIGN_KEY IS NULL " +
                "AND RDB$SYSTEM_FLAG<>1")
    con=kinterbasdb.connect(host=hostname, database=db_name,
                            user=username, password=pwd)
    cur=con.cursor()
    cur.execute(query_index)
    all_index=cur.fetchall()

    for index_name in all_index:
        print "Reindex " + index_name[0].strip()
        cur.execute("ALTER INDEX " + index_name[0].strip() + " INACTIVE")
        cur.execute("ALTER INDEX " + index_name[0].strip() + " ACTIVE")
        cur.execute("SET STATISTIC INDEX " + index_name[0].strip())

    cur.close()
    con.close()

if __name__ == "__main__":
    reindex(sys.argv[1], sys.argv[2],
            sys.argv[3], sys.argv[4])