[gnue-contrib] r270 - in address: . db-migration

[johannes]

Author: johannes
Date: 2007-04-19 09:52:05 -0500 (Thu, 19 Apr 2007)
New Revision: 270

Added:
   address/db-migration/
   address/db-migration/align-csv.py
   address/db-migration/dumps.sh
   address/db-migration/imp-csv.py
Log:
Added migration script for database (mdb to sqlite3)


Added: address/db-migration/align-csv.py
===================================================================
--- address/db-migration/align-csv.py   2007-04-10 14:25:24 UTC (rev 269)
+++ address/db-migration/align-csv.py   2007-04-19 14:52:05 UTC (rev 270)
@@ -0,0 +1,35 @@
+#!/usr/bin/python
+# -*- encoding: utf-8 -*-
+
+import sys
+
+marker = '<<###EOR###>>'
+
+header = None
+last_line = ''
+rows = []
+
+for line in sys.stdin.readlines():
+    if header is None:
+        header = line.strip()
+        line = ''
+
+    if marker in line:
+        (lead, rest) = line.split(marker, 1)
+        last_line += lead
+        rows.append(last_line)
+
+        while marker in rest:
+            (komp, rest) = rest.split(marker, 1)
+            rows.append(komp)
+
+        last_line = rest
+    else:
+        last_line += line
+
+print header
+for ix, zeile in enumerate(rows):
+    text = zeile.replace('\r\n', '<NEWLINE>')
+    text = text.replace('\n', '<NEWLINE>')
+    text = text.replace('\r', '<NEWLINE>')
+    print text


Property changes on: address/db-migration/align-csv.py
___________________________________________________________________
Name: svn:executable
   + *

Added: address/db-migration/dumps.sh
===================================================================
--- address/db-migration/dumps.sh       2007-04-10 14:25:24 UTC (rev 269)
+++ address/db-migration/dumps.sh       2007-04-19 14:52:05 UTC (rev 270)
@@ -0,0 +1,15 @@
+#!/bin/bash
+
+dbname=promo.mdb
+EOR='<<###EOR###>>'
+
+echo "Anrede ..."
+mdb-export -R$EOR $dbname Tab_Anrede | ./align-csv.py > anrede.csv
+echo "Kategorie ..."
+mdb-export -R$EOR $dbname Tab_Kategorie | ./align-csv.py > kategorie.csv
+echo "Ansprechpartner ..."
+mdb-export -R$EOR $dbname Tab_ASP | ./align-csv.py > ansp.csv
+echo "Firma ..."
+mdb-export -R$EOR $dbname Tab_Firma | ./align-csv.py > firma.csv
+
+./imp-csv.py


Property changes on: address/db-migration/dumps.sh
___________________________________________________________________
Name: svn:executable
   + *

Added: address/db-migration/imp-csv.py
===================================================================
--- address/db-migration/imp-csv.py     2007-04-10 14:25:24 UTC (rev 269)
+++ address/db-migration/imp-csv.py     2007-04-19 14:52:05 UTC (rev 270)
@@ -0,0 +1,180 @@
+#!/usr/bin/python
+# -*- encoding: utf-8 -*-
+#
+# Import CSV into SQLite3
+
+import pysqlite2.dbapi2 as dbapi
+import csv
+
+# =============================================================================
+# Clear all rows from a given table
+# =============================================================================
+
+def clear_table(db, table):
+
+    cur = db.cursor()
+    cur.execute('DELETE FROM %s' % table)
+    cur.close()
+
+
+# =============================================================================
+# Import the salutaions
+# =============================================================================
+
+def salutations(db):
+
+    clear_table(db, 'salutation')
+    reader = csv.DictReader(open('anrede.csv'))
+
+    cur = db.cursor()
+
+    result = {}
+    for row in reader:
+        text = unicode(row['Anr1'], 'utf-8')
+        cur.execute('INSERT INTO salutation (text) VALUES (?)', [text])
+        result[text.upper()] = cur.lastrowid
+
+    cur.close()
+    return result
+
+
+# =============================================================================
+# Import the categories
+# =============================================================================
+
+def categories(db):
+
+    clear_table(db, 'category')
+    reader = csv.DictReader(open('kategorie.csv'))
+
+    cur = db.cursor()
+
+    result = {}
+    for row in reader:
+        text = unicode(row['Kategorie'], 'utf-8')
+        cur.execute('INSERT INTO category (text) VALUES (?)', [text])
+        result[text.upper()] = cur.lastrowid
+
+    cur.close()
+
+    return result
+
+
+# =============================================================================
+# Get the ID for a given category
+# =============================================================================
+
+def fetch_lookup(value, lookup):
+
+    result = lookup.get(value.upper())
+    return result
+
+
+# =============================================================================
+# Re-Format a memo text
+# =============================================================================
+
+def fetch_memo(value):
+
+    result = value.replace('<NEWLINE>', '\n')
+    return result
+
+
+# =============================================================================
+# Import companies
+# =============================================================================
+
+def company(db, category):
+
+    clear_table(db, 'company')
+
+    reader = csv.DictReader(open('firma.csv'))
+    cur = db.cursor()
+
+    for row in reader:
+        cmd = 'INSERT INTO company (id, matchcode, name1, name2, street, ' \
+              '    country, zip, city, vatid, compreg, phone, fax, web, ' \
+              '    email, customer, bwcustomer, bwsupplier, shipment, ' \
+              '    category, memo, quicksearch) VALUES (?, ?, ?, ?, ?, ?, ' \
+              '?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
+
+        cur.execute(cmd, [row['ID_Firma'],
+                          row['Matchcode'],
+                          row['Firma 1'],
+                          row['Firma 2'],
+                          row['Straße'],
+                          row['Land'],
+                          row['PLZ'],
+                          row['Stadt'],
+                          row['UID'],
+                          row['FNr'],
+                          row['Tel_Haupt'],
+                          row['Fax_zentr'],
+                          row['INet'],
+                          row['eMail'],
+                          row['unsere_KdNr'],
+                          row['Ihre_KdNr'],
+                          row['Ihre_LieferantenNr'],
+                          row['Versand'],
+                          fetch_lookup(row['Kategorie'], category),
+                          fetch_memo(row['Kund_Info']),
+                          False])
+
+    cur.close()
+
+
+# =============================================================================
+# Import the contacts
+# =============================================================================
+
+def contact(db, salut):
+
+    clear_table(db, 'contact')
+
+    reader = csv.DictReader(open('ansp.csv'))
+    cur = db.cursor()
+
+    for row in reader:
+        cmd = 'INSERT INTO contact (id, company, salutation, title, ' \
+              '    firstname, surname, department, duty, phone1, phone2, ' \
+              '    fax, email, memo) ' \
+              '    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
+
+        cur.execute(cmd, [row['ID_Person'],
+                          row['ID_Firma'],
+                          fetch_lookup(row['Anr'], salut),
+                          row['Titel'],
+                          row['Vorname'],
+                          row['Zuname'],
+                          row['Abt'],
+                          row['Funkt'],
+                          row['Asp_Tel1'],
+                          row['Asp_Tel2'],
+                          row['Fax'],
+                          row['E-Mail'],
+                          fetch_memo(row['Asp_Memo'])])
+
+    cur.close()
+
+
+
+
+# =============================================================================
+# Main program
+# =============================================================================
+
+db = dbapi.connect('address.db')
+
+print "Importing into salutation  ..."
+salut = salutations(db)
+print "Importing into category    ..."
+categ = categories(db)
+print "Importing into company     ..."
+company(db, categ)
+print "Importing into contact     ..."
+contact(db, salut)
+
+print "Committing ..."
+db.commit()
+
+print "Done"


Property changes on: address/db-migration/imp-csv.py
___________________________________________________________________
Name: svn:executable
   + *