Code


Version 1 (modified by Carl Karsten <carl@…>, 7 years ago) (diff)

--

Q: how do I blow away my db and or load it with data?

{{{# resetdb.sh # blows away the old db, builds a new one and migrates data. set -x

./config/mkdbuser.py | mysql -u root -p ./manage.py syncdb --noinput ./config/migrate.py }}}

{{{#!/usr/bin/env python # mkdbuser.py # prints the CREATE DATABASE and GRANT commands based on the local settings.py # ./mkdbuser.py | mysql -u root -p

# nifty trick to get ../settings import os, sys os.environDJANGO_SETTINGS_MODULE? = 'settings' BASE_DIR = os.path.dirname(os.path.abspath(file)) apppath=os.path.abspath(BASE_DIR+'/../') sys.path.insert(0, apppath ) import settings

SQL = """ DROP DATABASE IF EXISTS %(db)s; CREATE DATABASE %(db)s; GRANT ALL

ON %(db)s.* TO %(user)s IDENTIFIED BY '%(pw)s' with grant option;

"""

print SQL % {

'db':settings.DATABASE_NAME, 'user':settings.DATABASE_USER, 'pw':settings.DATABASE_PASSWORD }

}}}

#!/usr/bin/env python
# migrate.py

# nifty trick to get ../settings
import os, sys
os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
apppath=os.path.abspath(BASE_DIR+'/../')
sys.path.insert(0, apppath )
import settings

from django.contrib.auth.models import User
from eventcal.models import Event, EventType
from core.models import UserProfile, Message
import MySQLdb
from datetime import datetime, timedelta

con = MySQLdb.connect(user=settings.DATABASE_USER,
    passwd=settings.DATABASE_PASSWORD,
    db=settings.DATABASE_NAME )

cur=con.cursor(MySQLdb.cursors.DictCursor)

# reset the tables
cur.execute("truncate core_userprofile")
cur.execute("truncate auth_user")
cur.execute("truncate eventcal_event")
cur.execute("truncate eventcal_eventtype")

# Create django Admin's
def mkAdmin( name, pw ):
    print name, pw
    user = User.objects.create_user( name.lower(), 'test@example.com', pw )
    # User.objects.create_user('john', 'lennon@thebeatles.com', 'johnpassword')
    user.first_name=name
    user.is_superuser=True
    user.is_staff=True
    profile = UserProfile.objects.create(user=user)
    user.save()
    profile.save()
    print name

# bunch of accounts for the QA squad
mkAdmin('user1','pass1')
mkAdmin('user4','pass3')

# Migrate the admins 
sql = """select AdminID, AdminPassword, FirstName  from RidgeMoorJonas.Admin"""
cur.execute(sql)
admins = cur.fetchall()
for admin in admins:
    mkAdmin( admin['FirstName'], admin['AdminPassword'] )

# Migrate Users
cSql="""
select MemberNumber, 
  Surname, `Given name`, `Familiar Name`, 
  `Email Address`,  UserID, UserPassword, Telephone
    from RidgeMoorJonas.Member
    where UserID is not null
    and MemberNumber not in ('271', '139', '139A', '287', '0558')    
limit 3000
"""
cur.execute(cSql)
members = cur.fetchall()
for member in  members:
  email = member['Email Address'] or "test@example.com"
  user = User.objects.create_user( member['UserID'], email, member['UserPassword'] )
  user.first_name=member['Given name']
  user.last_name = member['Surname']
  profile = UserProfile.objects.create(user=user)
  profile.phone_number=member['Telephone']
  user.save()
  profile.save()

# setup the few eventcal_eventtype records

def addEventType(ecode, et, fm=True):
    e = EventType( eventcode=ecode,
        eventtype=et,
        for_main=fm )
    e.save()

addEventType('corsecond', 'Course Condition', False)
addEventType('restspec', 'Resturant Specials', False)
addEventType('feaeve', 'Featured Event')
addEventType('golf', 'Golf')
addEventType('info', 'Informational')


# Move the Events into django
cSql="""
 select ShortTitle,
  cast( CONCAT_WS('-', eventyear, eventmonth, eventday) as date) as eventdate,
  LongTitle,
  EventDescription
  from RidgeMoorJonas.Event
"""
#
cur.execute(cSql)
events = cur.fetchall()
et=EventType.objects.get(eventcode='info')
for event in events:
    e = Event( title=event['ShortTitle'], 
       eventtype=et,
       eventdate=event['eventdate'], 
       description=event['LongTitle'],
       details=event['EventDescription'] )
    e.save()


# Make up a Dinner Special:
e = Event( title="Mom's favorite",
       eventtype=EventType.objects.get(eventcode='restspec'),
       eventdate=datetime.today()+timedelta(1),
       description="Ham Sandwich",
       details="It's better than eternal happiness." )
e.save()
det="""
$ 10.95 Specials

Chicken Pot Pie
Short Ribs
Rigatoni with Vodka Sauce
"""
e = Event( title="Budget Specials",
       eventtype=EventType.objects.get(eventcode='restspec'),
       eventdate=datetime.today(),
       description="Budget Specials",
       details=det ).save()

# assign some events to Larry...
ul=User.objects.get(username='ls0696')
e=Event.objects.get(id=600)
e.contact=ul
e.save()

e=Event.objects.get(id=610)
e.contact=ul
e.save()

e=Event.objects.get(id=612)
e.contact=ul
e.save()

# send a message from Larry to Carl
uc=User.objects.get(username='carl')
Message( to=ul, 
    sender=uc,
    subject='HAI!',
    body='Watson, come quick!' ).save()