PreFinal

from clickhouse_driver import Client
import subprocess


def temp(arr):
    res = 'CREATE TABLE IF NOT EXISTS ' + arr[0] + '\n(\n'
    for i in range(1, len(arr), 2):
        if i != len(arr) - 2:
            res += arr[i] + ' ' + arr[i + 1] + ',\n'
        else:
            res += arr[i] + ' ' + arr[i + 1] + '\n'
    res += ') ENGINE = MergeTree ORDER BY tuple()'
    return res


def type(now, dict):
    for i in range(len(now)):
        if dict[2 * (i + 1)] == "Int16" or dict[2 * (i + 1)] == "Int32":
            now[i] = int(now[i])
        elif dict[2 * (i + 1)][:3] == "Dec":
            now[i] = float(now[i])
        elif dict[2 * (i + 1)] == 'UInt8':
            if now[i] == 't':
                now[i] = 1
            else:
                now[i] = 0
    return now


tables = [
 ['actor',
  'actor_id', 'Int32',
  'first_name', 'String',
  'last_name', 'String',
  'last_update', 'DateTime64'
  ],
 ['actor_info',
  'actor_id', 'Int32',
  'first_name', 'String',
  'last_name', 'String',
  'film_info', 'String'
  ],
 ['customer_list',
  'id', 'Int32',
  'name', 'String',
  'address', 'String',
  'zipcode', 'String',
  'phone', 'String',
  'city', 'String',
  'country', 'String',
  'notes', 'String',
  'sid', 'Int16'
  ],
 ['film_list',
  'fid', 'Int32',
  'title', 'String',
  'description', 'String',
  'category', 'String',
  'price', 'Decimal(4, 2)',
  'length', 'Int16',
  'rating', 'String',
  'actors', 'String'
  ],
 ['nicer_but_slower_film_list',
  'fid', 'Int32',
  'title', 'String',
  'description', 'String',
  'category', 'String',
  'price', 'Decimal(4, 2)',
  'length', 'Int16',
  'rating', 'String',
  'actors', 'String'
  ],
 ['sales_by_film_category',
  'category', 'String',
  'total_sales', 'Decimal(6, 2)'
  ],
 ['store',
  'store_id', 'Int32',
  'manager_staff_id', 'Int16',
  'address_id', 'Int16',
  'last_update', 'DateTime'
  ],
 ['sales_by_store',
  'store', 'String',
  'manager', 'String',
  'total_sales', 'Decimal(7, 2)'
  ],
 ['staff_list',
  'id', 'Int32',
  'name', 'String',
  'address', 'String',
  'zipcode', 'String',
  'phone', 'String',
  'city', 'String',
  'country', 'String',
  'sid', 'Int16'
  ],
 ['address',
  'address_id', 'Int32',
  'address', 'String',
  'address2', 'String',
  'district', 'String',
  'city_id', 'Int16',
  'postal_code', 'String',
  'phone', 'String',
  'last_update', 'DateTime'
  ],
 ['category',
  'category_id', 'Int32',
  'name', 'String',
  'last_update', 'DateTime'
  ],
 ['city',
  'city_id', 'Int32',
  'city', 'String',
  'country_id', 'Int16',
  'last_update', 'DateTime'
  ],
 ['country',
  'country_id', 'Int32',
  'country', 'String',
  'last_update', 'DateTime'
  ],
 ['customer',
  'customer_id', 'Int32',
  'store_id', 'Int16',
  'first_name', 'String',
  'last_name', 'String',
  'email', 'String',
  'address_id', 'Int16',
  'activebool', 'UInt8',
  'create_date', 'Date',
  'last_update', 'DateTime64',
  'active', 'Int32'
  ],
 ['film_actor',
  'actor_id', 'Int16',
  'film_id', 'Int16',
  'last_update', 'DateTime'
  ],
 ['film_category',
  'film_id', 'Int16',
  'category_id', 'Int16',
  'last_update', 'DateTime'
  ],
 ['inventory',
  'inventory_id', 'Int32',
  'film_id', 'Int16',
  'store_id', 'Int16',
  'last_update', 'DateTime'
  ],
 ['language',
  'language_id', 'Int32',
  'name', 'String',
  'last_update', 'DateTime'
  ],
 ['rental',
  'rental_id', 'Int32',
  'rental_date', 'DateTime',
  'inventory_id', 'Int32',
  'customer_id', 'Int16',
  'return_date', 'DateTime',
  'staff_id', 'Int16',
  'last_update', 'DateTime'
  ],
 ['staff',
  'staff_id', 'Int32',
  'first_name', 'String',
  'last_name', 'String',
  'address_id', 'Int16',
  'email', 'String',
  'store_id', 'Int16',
  'active', 'UInt8',
  'username', 'String',
  'password', 'String',
  'last_update', 'DateTime64',
  'picture', 'String'
  ],
 ['payment',
  'payment_id', 'Int32',
  'customer_id', 'Int16',
  'staff_id', 'Int16',
  'rental_id', 'Int32',
  'amount', 'Decimal(5, 2)',
  'payment_date', 'DateTime64'
  ],
 ['film',
  'film_id', 'Int32',
  'title', 'String',
  'description', 'String',
  'release_year', 'Int32',
  'language_id', 'Int16',
  'rental_duration', 'Int16',
  'rental_rate', 'Decimal(4, 2)',
  'length', 'Int16',
  'replacement_cost', 'Decimal(5, 2)',
  'rating', 'String',
  'last_update', 'DateTime64',
  'special_features', 'Array(String)',
  'fulltext', 'String',
  ]
]

client = Client('localhost')
for i in tables:
    client.execute(temp(i))

for i in tables:
    res = "clickhouse-client --query=\"INSERT INTO " + i[0] + " FORMAT CSV\" < /media/sf_im/" + i[0] + ".csv"
    print(res)
    subprocess.run(res, shell=True)

'''
for i in tables:
    print(temp(i))
    print()
'''


'''
import psycopg2

path = 'C:/im/'
fields = open(path + 'field.txt', 'w')
con = psycopg2.connect(
    database="dvd",
    user="postgres",
    password="postgres",
    host="127.0.0.1",
    port="5432"
)
cursor = con.cursor()
cursor.execute('SELECT table_name FROM information_schema.tables \
WHERE table_schema NOT IN (\'information_schema\',\'pg_catalog\');')
tables = []
for row in cursor:
    tables += [row[0]]
for row in tables:
    new_file = open(path + row + '.csv', 'w')
    new_file.close()

    cursor.execute('SELECT column_name, data_type \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE table_name = \'' + row + '\';')
    temp = [row]
    for i in cursor:
        temp += [i[0].replace(' ', ''), i[1].replace(' ', '')]
    fields.write(' '.join(temp) + '\n')

    cursor.execute('COPY (SELECT * FROM "' + row + '") TO \'' + path + row + '.csv\' CSV;')
fields.close()
'''