<commit_before>#!/usr/bin/env python
"""
initialises a PostgreSQL database with TKP tables.
"""

import os
import sys
from subprocess import call
import tkp
from tkp.db.database import DB_VERSION


tkp_folder = tkp.__path__[0]
sql_repo = os.path.join(tkp_folder, 'db/sql/statements')

from tkp.db.sql.preprocessor import dialectise

# use these to replace strings in the SQL files
tokens = (
    ('%NODE%', '1'),
    ('%NODES%', '1'),
    ('%VLSS%', os.path.join(tkp_folder, '../database/catalog/vlss/vlss.csv')),
    ('%WENSS%', os.path.join(tkp_folder, '../database/catalog/wenss/wenss.csv')),
    ('%NVSS%', os.path.join(tkp_folder, '../database/catalog/nvss/nvss.csv')),
    ('%EXO%', os.path.join(tkp_folder, '../database/catalog/exoplanets/exo.csv')),
    ('%VERSION%', str(DB_VERSION))
)


def verify(dbconfig):
    """
    Verify with the user if he wants to continue with the given settings.

    :param parsed: a argparse namespace
    """
    print("This script will populate a database with these settings:")
    print("")
    print("\tengine:    " + (dbconfig['engine'] or ""))
    print("\tdatabase:   " + (dbconfig['database'] or ""))
    print("\tuser:       " + (dbconfig['user'] or ""))
    print("\tpassword:   " + (dbconfig['password'] or ""))
    print("\thost:       " + (dbconfig['host'] or ""))
    print("\tport:       " + str(dbconfig['port']))
    print("\tpassphrase: " + (dbconfig['passphrase'] or ""))
    print("")
    print("!!! WARNING !!! This will REMOVE all data in '%s'" % dbconfig['database'])
    print("")
    answer = raw_input("Do you want to continue? [y/N]: ")
    if answer.lower() != 'y':
        sys.stderr.write("Aborting.")
        sys.exit(1)


def connect(dbconfig):
    """
    Connect to the database

    :param parsed: an argparse namespace
    :returns: a database connection
    """
    if dbconfig['engine'] == "postgresql":
        import psycopg2
        return psycopg2.connect(database=dbconfig['database'], user=dbconfig['user'],
                                password=dbconfig['password'], host=dbconfig['host'],
                                port=dbconfig['port'])
    elif dbconfig['engine'] == "monetdb":
        import monetdb
        return monetdb.sql.connect(database=dbconfig['database'], user=dbconfig['user'],
                                   password=dbconfig['password'], host=dbconfig['host'],
                                   port=dbconfig['port'], autocommit=True)
    else:
        sys.stderr.write("engine %s is not implemented" % dbconfig['engine'])
        raise NotImplementedError

auth_query = """
ALTER USER "monetdb" RENAME TO "%(username)s";
ALTER USER SET PASSWORD '%(<PASSWORD>' USING OLD PASSWORD '<PASSWORD>';
CREATE SCHEMA "%(database)s" AUTHORIZATION "%(username)s";
ALTER USER "%(username)s" SET SCHEMA "%(database)s";
"""

def recreate(dbconfig):
    """
    Destroys and creates a new database.

    :param options: a argparse namespace generated with tkp.management

    WARNING: this will DESTROY your database!

    Note: this will raise an Exception ONLY when the creation of the database
          fails
    """
    params = {'username': dbconfig['user'],
              'password': dbconfig['password'],
              'database': dbconfig['database'],
              'host': dbconfig['host']
              }
    if dbconfig['engine'] == 'monetdb':
        import monetdb.sql
        import monetdb.control
        control = monetdb.control.Control(dbconfig['host'], dbconfig['port'],
                                          dbconfig['passphrase'])

        database = dbconfig['database']
        print("stopping %s" % database)
        try:
            control.stop(database)
        except monetdb.sql.OperationalError, e:
            print("database not running")
        print "destroying %s" % database
        try:
            control.destroy(database)
        except monetdb.sql.OperationalError, e:
            print("can't destroy database: %s" % str(e))

        control.create(database)
        control.release(database)
        control.start(database)

        con = monetdb.sql.connect(username='monetdb', password='<PASSWORD>',
                                  hostname=dbconfig['host'], port=dbconfig['port'],
                                  database=dbconfig['database'])
        cur = con.cursor()
        cur.execute(auth_query % params)
        con.commit()
        con.close()

    elif dbconfig['engine'] == 'postgresql':
        print "destroying database %(database)s on %(host)s..." % params
        call('dropdb -h %(host)s -U %(username)s %(database)s' % params,
             shell=True)
        print "creating database %(database)s on %(host)s..." % params
        if call('createdb -h %(host)s -U %(username)s %(database)s' % params,
                shell=True) != 0:
            raise Exception("can't create a new postgresql database!")
    else:
        raise NotImplementedError


def populate(dbconfig):
    """
    Populates a database
    :param dbconfig: a dict containing db connection settings
    """

    if not dbconfig['yes']:
        verify(dbconfig)

    recreate(dbconfig)
    conn = connect(dbconfig)
    cur = conn.cursor()

    if dbconfig['engine'] == 'postgresql':
        # make sure plpgsql is enabled
        try:
            cur.execute("CREATE LANGUAGE plpgsql;")
        except conn.ProgrammingError:
            conn.rollback()

    batch_file = os.path.join(sql_repo, 'batch')

    for line in [l.strip() for l in open(batch_file) if not l.startswith("#")]:<commit_msg>remove password prompt for postgres db init<commit_after>#!/usr/bin/env python
"""
initialises a PostgreSQL database with TKP tables.
"""

import os
import sys
from subprocess import call
import tkp
from tkp.db.database import DB_VERSION


tkp_folder = tkp.__path__[0]
sql_repo = os.path.join(tkp_folder, 'db/sql/statements')

from tkp.db.sql.preprocessor import dialectise

# use these to replace strings in the SQL files
tokens = (
    ('%NODE%', '1'),
    ('%NODES%', '1'),
    ('%VLSS%', os.path.join(tkp_folder, '../database/catalog/vlss/vlss.csv')),
    ('%WENSS%', os.path.join(tkp_folder, '../database/catalog/wenss/wenss.csv')),
    ('%NVSS%', os.path.join(tkp_folder, '../database/catalog/nvss/nvss.csv')),
    ('%EXO%', os.path.join(tkp_folder, '../database/catalog/exoplanets/exo.csv')),
    ('%VERSION%', str(DB_VERSION))
)


def verify(dbconfig):
    """
    Verify with the user if he wants to continue with the given settings.

    :param parsed: a argparse namespace
    """
    print("This script will populate a database with these settings:")
    print("")
    print("\tengine:    " + (dbconfig['engine'] or ""))
    print("\tdatabase:   " + (dbconfig['database'] or ""))
    print("\tuser:       " + (dbconfig['user'] or ""))
    print("\tpassword:   " + (dbconfig['password'] or ""))
    print("\thost:       " + (dbconfig['host'] or ""))
    print("\tport:       " + str(dbconfig['port']))
    print("\tpassphrase: " + (dbconfig['passphrase'] or ""))
    print("")
    print("!!! WARNING !!! This will REMOVE all data in '%s'" % dbconfig['database'])
    print("")
    answer = raw_input("Do you want to continue? [y/N]: ")
    if answer.lower() != 'y':
        sys.stderr.write("Aborting.")
        sys.exit(1)


def connect(dbconfig):
    """
    Connect to the database

    :param parsed: an argparse namespace
    :returns: a database connection
    """
    if dbconfig['engine'] == "postgresql":
        import psycopg2
        return psycopg2.connect(database=dbconfig['database'], user=dbconfig['user'],
                                password=dbconfig['password'], host=dbconfig['host'],
                                port=dbconfig['port'])
    elif dbconfig['engine'] == "monetdb":
        import monetdb
        return monetdb.sql.connect(database=dbconfig['database'], user=dbconfig['user'],
                                   password=dbconfig['password'], host=dbconfig['host'],
                                   port=dbconfig['port'], autocommit=True)
    else:
        sys.stderr.write("engine %s is not implemented" % dbconfig['engine'])
        raise NotImplementedError

auth_query = """
ALTER USER "monetdb" RENAME TO "%(username)s";
ALTER USER SET PASSWORD '%(password)s' USING OLD PASSWORD '<PASSWORD>';
CREATE SCHEMA "%(database)s" AUTHORIZATION "%(username)s";
ALTER USER "%(username)s" SET SCHEMA "%(database)s";
"""

def recreate(dbconfig):
    """
    Destroys and creates a new database.

    :param options: a argparse namespace generated with tkp.management

    WARNING: this will DESTROY your database!

    Note: this will raise an Exception ONLY when the creation of the database
          fails
    """
    params = {'username': dbconfig['user'],
              'password': dbconfig['password'],
              'database': dbconfig['database'],
              'host': dbconfig['host']
              }
    if dbconfig['engine'] == 'monetdb':
        import monetdb.sql
        import monetdb.control
        control = monetdb.control.Control(dbconfig['host'], dbconfig['port'],
                                          dbconfig['passphrase'])

        database = dbconfig['database']
        print("stopping %s" % database)
        try:
            control.stop(database)
        except monetdb.sql.OperationalError, e:
            print("database not running")
        print "destroying %s" % database
        try:
            control.destroy(database)
        except monetdb.sql.OperationalError, e:
            print("can't destroy database: %s" % str(e))

        control.create(database)
        control.release(database)
        control.start(database)

        con = monetdb.sql.connect(username='monetdb', password='<PASSWORD>',
                                  hostname=dbconfig['host'], port=dbconfig['port'],
                                  database=dbconfig['database'])
        cur = con.cursor()
        cur.execute(auth_query % params)
        con.commit()
        con.close()

    elif dbconfig['engine'] == 'postgresql':
        import psycopg2
        con = psycopg2.connect(user=dbconfig['user'],
                               password=dbconfig['password'],
                               port=dbconfig['port'], host=dbconfig['host'],
                               database='postgres')
        con.autocommit = True
        cur = con.cursor()
        print "destroying database %(database)s on %(host)s..." % params
        cur.execute('DROP DATABASE %s' % dbconfig['database'])
        print "creating database %(database)s on %(host)s..." % params
        cur.execute('CREATE DATABASE %s' % dbconfig['database'])
    else:
        raise NotImplementedError("we only support monetdb & postgresql")


def populate(dbconfig):
    """
    Populates a database
    :param dbconfig: a dict containing db connection settings
    """

    if not dbconfig['yes']:
        verify(dbconfig)

    recreate(dbconfig)
    conn = connect(dbconfig)
    cur = conn.cursor()

    if dbconfig['engine'] == 'postgresql':
        # make sure plpgsql is enabled
        try:
            cur.execute("CREATE LANGUAGE plpgsql;")
        except conn.ProgrammingError:
            conn.rollback()

    batch_file = os.path.join(sql_repo, 'batch')

    for line in [l.strip() for l in open(batch_file) if not l.startswith("#")]:
{'fraction_non_alphanumeric': 0.14670315120487246, 'fraction_numerical': 0.0013240356606937947, 'mean_word_length': 2.549498746867168, 'pattern_counts': {'":': 4, '<': 8, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7622997', 'n_tokens_mistral': 3407, 'n_tokens_neox': 3305, 'n_words': 808}