SGJP / Kuznia

#-*- coding:utf-8 -*-

import sqlite3
import datetime
from django.db import connection, transaction
from django.core.management.base import BaseCommand
from django.contrib.auth.models import User

from common.util import no_history
from dictionary.models import *

DEFAULT_DATABASE = 'data/sgjp.db'
MINI_LEXEME_COUNT = 500
MINI_MODE = False # do debugowania
SQL_MODE = True
cursor = connection.cursor()
db = None
sqlite_cursor = None
no_history()

class Command(BaseCommand):
  args = '<input db filename>'
  help = 'Imports initial data'

  def handle(self, db_name=DEFAULT_DATABASE, **options):
    global db
    db = db_name
    delete_and_import()

def get_cursor(db):
  conn = sqlite3.connect(db)
  conn.row_factory = sqlite3.Row
  return conn.cursor()


def import_lexical_classes():
  lc = LexicalClass(symbol='inne')
  lc.save()
  for row in sqlite_cursor.execute('select distinct pos from wzory'):
    if not SQL_MODE:
      lc = LexicalClass(symbol=row['pos'])
      lc.save()
    else:
      cursor.execute("INSERT INTO czescimowy (czm) VALUES (%s)",
                     [row['pos']])


def import_parts_of_speech():
  other = 'inne'
  lcs = {}
  for row in sqlite_cursor.execute('select distinct wzory.pos, leksemy.pos from wzory '
                         'natural join odmieniasie join leksemy on '
                         'leksemy.nr = odmieniasie.nr'):
    lcs[row[1]] = row[0]
    #print row
  for row in sqlite_cursor.execute('SELECT pos FROM klasygramatyczne'):
    lc = lcs.get(row['pos'], other)
    if not SQL_MODE:
      pos = PartOfSpeech(symbol=row['pos'])
      pos.lexical_class = LexicalClass.objects.get(symbol=lc)
      pos.save()
    else:
      cursor.execute("INSERT INTO klasygramatyczne (pos, czm) VALUES (%s, %s)",
                     [row['pos'], lc])


def import_base_form_labels():
  query_result = sqlite_cursor.execute("""
    SELECT efobaz FROM paradygmaty
    UNION
    SELECT efobaz FROM zakonczenia
    """)
  for row in query_result:
    if not SQL_MODE:
      bfl = BaseFormLabel(entry=row[0])
      bfl.save()
    else:
      cursor.execute("INSERT INTO efobazy (efobaz) VALUES (%s)", [row[0]])

# tymczasowa tabelka - powinna przychodzić z bazą
# (albo zrobić w kodzie wyjątek na p123... jak już i tak są wyjątki)
basic_form_labels = {
  '0-': '1',
  '3+': '1',
  'f':  'sg:nom',
  'm1': 'sg:nom',
  'm2': 'sg:nom',
  'm3': 'sg:nom',
  'n1': 'sg:nom',
  'n2': 'sg:nom',
  'p1': 'pl:nom:mo',
  'p2': 'pl:nom',
  'p3': 'pl:nom',
  'pri': 'sg:nom', # albo pl. teraz już naprawdę się nie da.
  'sec': 'sg:nom',
}

# to chyba nie jest najlepsze rozwiązanie...
basic_form_labels_pos = {
  'v': '5',
  'ger': '11',
  'pact': '3',
  'ppas': '10',
  'appas': '10',
  'pred': '5',
}

ics = {}

def import_inflection_characteristics():
  for row in sqlite_cursor.execute('SELECT DISTINCT charfl, pos FROM paradygmaty'):
    if row['charfl'] == '':
      bfl_entry = '1' if row['pos'] in ('adj', 'adjcom') else ''
    else:
      bfl_entry = basic_form_labels.get(row['charfl'], '')
    if row['pos'] in basic_form_labels_pos:
      bfl_entry = basic_form_labels_pos[row['pos']]
    bfl = BaseFormLabel.objects.get(entry=bfl_entry)
    if not SQL_MODE:
      ic = InflectionCharacteristic(
        entry=row['charfl'], basic_form_label=bfl,
        part_of_speech=PartOfSpeech.objects.get(pk=row['pos']))
      ic.save()
    else:
      cursor.execute("INSERT INTO charfle (charfl, pos, efobaz) "
                     "VALUES (%s, %s, %s)", [row['charfl'], row['pos'], bfl.pk])
  for ic in InflectionCharacteristic.objects.all():
    ics[(ic.entry, ic.part_of_speech.symbol)] = ic


sgjp_domain = ('SGJP', 'WSJP', 'SJPDor', 'zmiotki')


def import_vocabularies():
  result = sqlite_cursor.execute("""
    SELECT slownik FROM leksemy
    UNION
    SELECT slownik_uz FROM slowniki_uzywajace
  """)
  for row in result:
    v = Vocabulary()
    v.id = row[0]
    v.save()


def import_qualifiers():
  sgjp = Vocabulary.objects.get(id='SGJP')
  query_result = sqlite_cursor.execute("""
    SELECT okwal FROM odmieniasie
    UNION
    SELECT zkwal FROM zakonczenia
    UNION
    SELECT lkwal FROM leksemy
    """)
  added = set()
  for row in query_result:
    if row[0]:
      for qualifier in row[0].split('|'):
        if qualifier not in added:
          added.add(qualifier)
          if not SQL_MODE:
            q = Qualifier(label=qualifier, vocabulary=sgjp)
            q.save()
          else:
            cursor.execute("INSERT INTO kwalifikatory (kwal, slownik, usuniety) "
                           "VALUES (%s, %s, %s)", [qualifier, sgjp.pk, False])


mini_lexeme_query = 'SELECT %s FROM leksemy LIMIT ?'

def import_lexemes():
  if MINI_MODE:
    result = sqlite_cursor.execute(mini_lexeme_query % '*', (MINI_LEXEME_COUNT,))
  else:
    result = sqlite_cursor.execute('SELECT * FROM leksemy')
  date = datetime.datetime.now()
  cv_table = dict(ClassificationValue.objects.values_list('label', 'pk'))
  for row in result:
    slownik = row['slownik']
    status = 'conf' if slownik != 'zmiotki' else 'cand'
    if not SQL_MODE:
      l = Lexeme()
      l.id = row['nr']
      l.entry = row['haslo']
      l.part_of_speech = PartOfSpeech.objects.get(pk=row['pos'])
      l.comment = row['komentarz']
      l.source = 'SGJP' # potrzebne to w ogóle?
      l.status = status
      l.gloss = row['glosa'] or ''
      l.entry_suffix = row['haslosuf'] or ''
      l.note = row['nota'] or ''
      cv = ClassificationValue.objects.get(label=row['pospolitosc'])
      cv.lexemes.add(l) #add
      l.fix_homonym_number()

      l.owner_vocabulary = Vocabulary.objects.get(pk=slownik)
      l.save()
      LexemeAssociation.objects.create(
        vocabulary=Vocabulary.objects.get(pk=slownik),
        lexeme=l)
      # brak kwalifikatorów - nieauktualne
    else:
      cv_pk = cv_table[row['pospolitosc']]
      cursor.execute(
        "INSERT INTO leksemy (id, haslo, haslosuf, glosa, nota, hom, pos, zrodlo, "
        "status, komentarz, data_modyfikacji, slownik, usuniety) VALUES (%s, %s, %s, %s, "
        "%s, %s, %s, %s, %s, %s, %s, %s, %s)", [row['nr'], row['haslo'],
        row['haslosuf'] or '', row['glosa'] or '', row['nota'] or '', 1, row['pos'], 'SGJP',
        status, row['komentarz'], date, row['slownik'], False])
      cursor.execute(
        "INSERT INTO leksemy_w_slownikach (l_id, slownik) "
        "VALUES (%s, %s)", [row['nr'], slownik])
      cursor.execute(
        "INSERT INTO wartosci_klasyfikacji_lexemes (classificationvalue_id, "
        "lexeme_id) VALUES (%s, %s)", [cv_pk, row['nr']])
      if row['lkwal']:
        for qual in row['lkwal'].split('|'):
          q_id = Qualifier.objects.get(label=qual).pk
          cursor.execute(
            "INSERT INTO kwalifikatory_leksemow (lexeme_id, "
            "qualifier_id) VALUES (%s, %s)", [row['nr'], q_id])


def import_lexeme_associations():
  if MINI_MODE:
    result = sqlite_cursor.execute('SELECT * FROM slowniki_uzywajace WHERE nr in (%s)' %
                         (mini_lexeme_query % 'nr'), [MINI_LEXEME_COUNT])
  else:
    result = sqlite_cursor.execute('SELECT * FROM slowniki_uzywajace')
  for row in result:
    if not SQL_MODE:
      v = Vocabulary.objects.get(pk=row['slownik_uz'])
      l = Lexeme.objects.get(pk=row['nr'])
      LexemeAssociation.objects.create(vocabulary=v, lexeme=l)
    else:
      cursor.execute(
        "INSERT INTO leksemy_w_slownikach (l_id, slownik) "
        "VALUES (%s, %s)", [row['nr'], row['slownik_uz']])


def import_cross_reference_types():
  result = sqlite_cursor.execute(
    'select distinct l1.pos pos1, l2.pos pos2, t.* '
    'from odsylacze o join leksemy l1 on nrod=l1.nr '
    'join leksemy l2 on nrdo=l2.nr '
    'join typyodsylaczy t on t.typods=o.typods')
  for row in result:
    t = CrossReferenceType()
    t.symbol = row['typods']
    t.desc = row['naglowek']
    t.index = row['kolejnosc']
    t.from_pos = PartOfSpeech.objects.get(symbol=row['pos1'])
    t.to_pos = PartOfSpeech.objects.get(symbol=row['pos2'])
    t.save()


def import_cross_references():
  if MINI_MODE:
    result = sqlite_cursor.execute(
      'SELECT * FROM odsylacze WHERE nrod in (%(subq)s) and nrdo in (%(subq)s)'
      % {'subq': mini_lexeme_query % 'nr'},
      [MINI_LEXEME_COUNT, MINI_LEXEME_COUNT])
  else:
    result = sqlite_cursor.execute('SELECT * FROM odsylacze')
  for row in result:
    if row['nrod'] and row['nrdo']: # no bo nie wiem jak to interpretować
      l_from = Lexeme.objects.get(pk=row['nrod'])
      l_to = Lexeme.objects.get(pk=row['nrdo'])
      try:
        cr_type = CrossReferenceType.objects.get(
          symbol=row['typods'], from_pos=l_from.part_of_speech,
          to_pos=l_to.part_of_speech)
      except CrossReferenceType.DoesNotExist:
        print row['typods'], l_from.part_of_speech, l_to.part_of_speech
      if not SQL_MODE:
        CrossReference.objects.create(
          from_lexeme=l_from, to_lexeme=l_to, type=cr_type)
      else:
        cursor.execute(
          "INSERT INTO odsylacze (l_id_od, l_id_do, typods_id) "
          "VALUES (%s, %s, %s)", [row['nrod'], row['nrdo'], cr_type.pk])


def import_pattern_types():
  for row in sqlite_cursor.execute('SELECT DISTINCT typr, pos FROM paradygmaty'):
    lc = PartOfSpeech.objects.get(symbol=row['pos']).lexical_class
    PatternType.objects.get_or_create(lexical_class=lc, entry=row['typr'])
  # prowizorka z powodu pustej klasy 'skr'
  for row in sqlite_cursor.execute('SELECT DISTINCT typr, pos FROM wzory'):
    lc = LexicalClass.objects.get(symbol=row['pos'])
    PatternType.objects.get_or_create(lexical_class=lc, entry=row['typr'])


def import_patterns():
  for row in sqlite_cursor.execute('SELECT * FROM wzory'):
    pt = PatternType.objects.get(
      lexical_class__symbol=row['pos'], entry=row['typr'])
    status = 'temp'
    if not SQL_MODE:
      p = Pattern()
      p.name = row['wzor']
      p.type = pt
      p.basic_form_ending = row['zakp']
      p.example = row['przyklad'] or ''
      p.comment = row['wkomentarz'] or ''

      p.status = status
      p.save()
    else:
      cursor.execute(
        "INSERT INTO wzory (w_id, typ, przyklad, zakp, status, komentarz) "
        "VALUES (%s, %s, %s, %s, %s, %s)",
        [row['wzor'], pt.pk, row['przyklad'] or '', row['zakp'], status,
         row['wkomentarz'] or ''])


def import_lexeme_inflection_patterns():
  if MINI_MODE:
    result = sqlite_cursor.execute(
      'SELECT * FROM odmieniasie WHERE nr IN (%s)' % (mini_lexeme_query % 'nr'),
      (MINI_LEXEME_COUNT,))
  else:
    result = sqlite_cursor.execute('SELECT * FROM odmieniasie')
  pos_table = dict(Lexeme.objects.values_list('pk', 'part_of_speech'))
  pattern_pk_table = dict(Pattern.objects.values_list('name', 'pk'))
  for row in result:
    if not SQL_MODE:
      lip = LexemeInflectionPattern()
      lip.lexeme = Lexeme.objects.get(id=row['nr'])
      lip.index = row['oskl']
      lip.pattern = Pattern.objects.get(name=row['wzor'])
      lip.inflection_characteristic = ics[(row['charfl'],
                                           lip.lexeme.part_of_speech)]
      lip.root = row['rdzen']
      lip.save()
      # nieaktualne
      if row['okwal']:
        lip.qualifiers.add(Qualifier.objects.get(label=row['okwal'])) #add
    else:
      pos = pos_table[row['nr']]
      pattern_pk = pattern_pk_table[row['wzor']]
      charfl_id = ics[(row['charfl'], pos)].pk
      cursor.execute(
        "INSERT INTO odmieniasie (l_id, oind, w_id, charfl, rdzen) "
        "VALUES (%s, %s, %s, %s, %s) ", [row['nr'], row['oskl'], pattern_pk,
        charfl_id, row['rdzen']])
      if row['okwal']:
        cursor.execute("select currval('odmieniasie_id_seq')")
        last_id = cursor.fetchone()[0]
        for qual in row['okwal'].split('|'):
          q_id = Qualifier.objects.get(label=qual).pk
          cursor.execute(
            "INSERT INTO kwalifikatory_odmieniasiow (lexemeinflectionpattern_id, "
            "qualifier_id) VALUES (%s, %s)", [last_id, q_id])

def import_endings():
  if SQL_MODE:
    pattern_pk_table = dict(Pattern.objects.values_list('name', 'pk'))
    bfl_table = dict(BaseFormLabel.objects.values_list('entry', 'pk'))
  for row in sqlite_cursor.execute('SELECT * FROM zakonczenia'):
    if row['zak'] is not None:
      if not SQL_MODE:
        e = Ending()
        e.pattern = Pattern.objects.get(name=row['wzor'])
        e.base_form_label = BaseFormLabel.objects.get(entry=row['efobaz'])
        e.string = row['zak']
        e.index = row['nrskl']
        e.save()
        for qual in row['zkwal'].split('|'):
          e.qualifiers.add(Qualifier.objects.get(label=qual)) #add
      else:
        pattern_pk = pattern_pk_table[row['wzor']]
        if pattern_pk:
          efobaz_id = bfl_table[row['efobaz']]
          cursor.execute(
            "INSERT INTO zakonczenia (w_id, efobaz, zind, zak) VALUES "
            "(%s, %s, %s, %s)",
            [pattern_pk, efobaz_id, row['nrskl'], row['zak']])
          if row['zkwal']:
            cursor.execute("select currval('zakonczenia_id_seq')")
            last_id = cursor.fetchone()[0]
            for qual in row['zkwal'].split('|'):
              q_id = Qualifier.objects.get(label=qual).pk
              cursor.execute(
                "INSERT INTO kwalifikatory_zakonczen (ending_id, qualifier_id) "
                "VALUES (%s, %s)", [last_id, q_id])


def import_tables():
  bfl_table = dict(BaseFormLabel.objects.values_list('entry', 'pk'))
  for row in sqlite_cursor.execute('SELECT * FROM paradygmaty'):
    lc = PartOfSpeech.objects.get(symbol=row['pos']).lexical_class
    variant, _created = Variant.objects.get_or_create(id=row['wariant'])
    tt_data = {
      'variant': variant,
      'pattern_type': PatternType.objects.get(
        entry=row['typr'], lexical_class=lc),
      'inflection_characteristic': InflectionCharacteristic.objects.get(
        entry=row['charfl'], part_of_speech__symbol=row['pos']),
    }
    tt, _created = TableTemplate.objects.get_or_create(**tt_data)
    if not SQL_MODE:
      c = Cell()
      c.table_template = tt
      c.base_form_label = BaseFormLabel.objects.get(entry=row['efobaz'])
      c.tag = row['morf']
      c.prefix = row['pref']
      c.suffix = row['suf']
      c.index = row['kskl']
      c.save()
      if row['row']:
        tc = TableCell()
        tc.cell = c
        tc.row = row['row']
        tc.col = row['col']
        tc.rowspan = row['rowspan']
        tc.colspan = row['colspan']
        tc.save()
    else:
      efobaz_id = bfl_table[row['efobaz']]
      cursor.execute(
        "INSERT INTO klatki (st_id, efobaz, tag, prefiks, sufiks, kind) "
        "VALUES (%s, %s, %s, %s, %s, %s)", [tt.pk, efobaz_id, row['morf'],
        row['pref'], row['suf'], row['kskl']])
      if row['row']:
        cursor.execute("select currval('klatki_id_seq')")
        last_id = cursor.fetchone()[0]
        cursor.execute(
          "INSERT INTO komorki_tabel (k_id, row, col, rowspan, colspan) "
          "VALUES (%s, %s, %s, %s, %s)", [last_id, row['row'],
          row['col'], row['rowspan'], row['colspan']])


def import_table_headers():
  for row in sqlite_cursor.execute('SELECT * FROM naglowkiwierszy'):
    if row['styl'] != 'b' and row['nagl']:
      tts = TableTemplate.objects.filter(
        variant__id=row['wariant'], pattern_type__entry=row['typr'],
        inflection_characteristic__entry=row['charfl'],
        inflection_characteristic__part_of_speech__symbol=row['pos'])
      if tts:
        tt = tts[0]
        if not SQL_MODE:
          th = TableHeader()
          th.table_template = tt
          th.row = row['row']
          th.col = row['col']
          th.rowspan = row['rowspan']
          th.colspan = row['colspan']
          th.label = row['nagl']
          th.horizontal = row['styl'] == 'h'
          th.save()
        else:
          cursor.execute(
            "INSERT INTO naglowki_tabel (st_id, row, col, rowspan, colspan, "
            "nagl, wierszowy) VALUES (%s, %s, %s, %s, %s, %s, %s)",
            [tt.pk, row['row'], row['col'], row['rowspan'], row['colspan'],
            row['nagl'], row['styl'] == 'h'])


def single_import(fun, db):
  global sqlite_cursor, cursor
  transaction.commit_unless_managed()
  transaction.enter_transaction_management()
  transaction.managed()
  sqlite_cursor = get_cursor(db)
  cursor = connection.cursor()
  fun()
  sqlite_cursor.close()
  cursor.close()
  transaction.commit()
  transaction.leave_transaction_management()


def delete_and_import():
  transaction.commit_unless_managed()
  transaction.enter_transaction_management()
  transaction.managed()
  models = (
    Qualifier,
    LexicalClass,
    PartOfSpeech,
    BaseFormLabel,
    InflectionCharacteristic,
    Lexeme,
    PatternType,
    Pattern,
    LexemeInflectionPattern,
    Ending,
    TableTemplate,
    Cell,
    TableCell,
    Vocabulary,
    LexemeAssociation,
  )
  print 'deleting old data...'
  for model in models:
    model.objects.all().delete()

  global sqlite_cursor
  sqlite_cursor = get_cursor(db)
  print 'importing lexical classes...'
  import_lexical_classes()
  print 'importing parts of speech'
  import_parts_of_speech()
  print 'importing base form labels'
  import_base_form_labels()
  print 'importing inflection characteristics'
  import_inflection_characteristics()
  print 'importing vocabularies...'
  import_vocabularies()
  print 'importing qualifiers...'
  import_qualifiers()
  print 'importing lexemes...'
  import_lexemes()
  print 'importing lexeme associations...'
  import_lexeme_associations()
  print 'importing cross-reference types...'
  import_cross_reference_types()
  print 'importing cross-references...'
  import_cross_references()
  print 'importing pattern types...'
  import_pattern_types()
  print 'importing patterns...'
  import_patterns()
  print 'importing lexeme inflection patterns...'
  import_lexeme_inflection_patterns()
  print 'importing endings...'
  import_endings()
  print 'importing table templates...'
  import_tables()
  print 'importing table headers...'
  import_table_headers()
  sqlite_cursor.close()
  cursor.close()
  transaction.commit()
  transaction.leave_transaction_management()

import sys
if __name__ == '__main__':
  if sys.argv[-1] == '-mini':
    MINI_MODE = True
    del sys.argv[-1]
  if len(sys.argv) > 1:
    db = sys.argv[1]
  else:
    db = DEFAULT_DATABASE
  delete_and_import()