[Hejes-devel] [881] blog: DB dump and restore targets in trunk/misc/dbblog/Makefile
hejes-devel at nytud.hu
hejes-devel at nytud.hu
Thu Aug 1 15:44:43 CEST 2013
Revision: 881
Author: mihaltz
Date: 2013-08-01 15:44:43 +0200 (Thu, 01 Aug 2013)
Log Message:
-----------
blog: DB dump and restore targets in trunk/misc/dbblog/Makefile
Modified Paths:
--------------
trunk/misc/dbblog/Makefile
trunk/misc/dbblog/dbblog_create_database.sql
trunk/misc/dbblog/test.py
Added Paths:
-----------
trunk/misc/dbblog/dump_dbhelyesblog.sql.zip
Modified: trunk/misc/dbblog/Makefile
===================================================================
--- trunk/misc/dbblog/Makefile 2013-08-01 09:59:23 UTC (rev 880)
+++ trunk/misc/dbblog/Makefile 2013-08-01 13:44:43 UTC (rev 881)
@@ -4,17 +4,22 @@
drop_db:
echo "drop database dbhelyesblog;" | mysql -u root -p
-# create DB
+# (drop DB if exitsts and) create DB
create_db:
mysql -u root -p < dbblog_create_database.sql
-# recreate DB: drop & create
-recreate_db:
- ( echo "drop database dbhelyesblog;" ; cat dbblog_create_database.sql ) | mysql -u root -p
-
-
# create DB users
create_dbusers:
mysql -u root -p < dbblog_create_users.sql
-# TODO: dump_db, restore_db
+# Dump the database on this local machine to sql files
+dump_db:
+ mysqldump -u dbblogadmin --password=d4bl0GGr dbhelyesblog > dump_dbhelyesblog.sql
+ zip dump_dbhelyesblog.sql.zip dump_dbhelyesblog.sql
+
+# Drop, re-create and import the database to this localhost's MySQL server from sql files in this directory (see dump_db)
+restore_db:
+ unzip -o dump_dbhelyesblog.sql.zip
+ mysql -u root -p < dbblog_create_database.sql
+ mysql -u dbblogadmin --password=d4bl0GGr dbhelyesblog < dump_dbhelyesblog.sql
+
Modified: trunk/misc/dbblog/dbblog_create_database.sql
===================================================================
--- trunk/misc/dbblog/dbblog_create_database.sql 2013-08-01 09:59:23 UTC (rev 880)
+++ trunk/misc/dbblog/dbblog_create_database.sql 2013-08-01 13:44:43 UTC (rev 881)
@@ -1 +1,2 @@
-CREATE DATABASE `dbhelyesblog` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_hungarian_ci */
+DROP DATABASE IF EXISTS `dbhelyesblog`;
+CREATE DATABASE `dbhelyesblog` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_hungarian_ci */;
Added: trunk/misc/dbblog/dump_dbhelyesblog.sql.zip
===================================================================
(Binary files differ)
Property changes on: trunk/misc/dbblog/dump_dbhelyesblog.sql.zip
___________________________________________________________________
Added: svn:mime-type
+ application/octet-stream
Modified: trunk/misc/dbblog/test.py
===================================================================
--- trunk/misc/dbblog/test.py 2013-08-01 09:59:23 UTC (rev 880)
+++ trunk/misc/dbblog/test.py 2013-08-01 13:44:43 UTC (rev 881)
@@ -22,57 +22,61 @@
sys.path.append('../../web2py/applications/helyesiras_webdev/modules')
import util
-db = DAL("mysql://dbblogadmin:d4bl0GGr@localhost/dbhelyesblog")
-# location of .table files = ???
+dbblog = DAL("mysql://dbblogadmin:d4bl0GGr@localhost/dbhelyesblog", folder='../../web2py/applications/helyesiras_webdev/databases')
def define_tables():
"""
Define the table names and the fields + add restrictions.
"""
- db.define_table('images',
+ dbblog.define_table('images',
Field('file', 'upload'),
- Field('title', unique=True)
+ Field('title', unique=True),
+ migrate='blog_images.table'
)
- db.define_table('tags',
+ dbblog.define_table('tags',
Field('name', unique=True),
- Field('posts', 'list:reference posts')
+ Field('posts', 'list:reference posts'),
+ migrate='blog_tags.table'
)
- db.define_table('categories',
- Field('name', unique=True)
+ dbblog.define_table('categories',
+ Field('name', unique=True),
+ format = '%(name)s',
+ migrate='blog_categories.table'
)
- db.define_table('posts',
+ dbblog.define_table('posts',
Field('slug', unique=True),
Field('title'),
- Field('image', 'reference images'),
+ Field('image_url'),
Field('body', 'text'),
Field('tags', 'list:string'),
Field('category', 'reference categories'),
- Field('created_on', 'datetime') # TODO add in webpy: default=request.now),
- )
-# migrate='hqa_main.table')
+ Field('created_on', 'datetime'), #, default=request.now),
+ migrate='blog_posts.table'
+ )
- db.posts.slug.requires = IS_NOT_EMPTY()
- db.posts.title.requires = IS_NOT_EMPTY()
- db.posts.body.requires = IS_NOT_EMPTY()
- db.posts.slug.requires = IS_NOT_EMPTY()
- db.posts.image.requires = IS_IN_DB(db, db.images.id, '%(title)s')
+ dbblog.posts.slug.requires = IS_NOT_EMPTY()
+ dbblog.posts.title.requires = IS_NOT_EMPTY()
+ dbblog.posts.body.requires = IS_NOT_EMPTY()
+ dbblog.posts.slug.requires = IS_NOT_EMPTY()
+ dbblog.posts.category.requires = IS_IN_DB(dbblog, dbblog.categories.id, '%(name)s')
+ # TODO: posts.image_url.requires = IS_URL?
- db.commit()
+ dbblog.commit()
def create_index_if_not_exists(db, table, keyname, column, unique=False):
- """Check if index keyname on db.table.column exists.
+ """Check if index keyname on dbblog.table.column exists.
If not, execute sql statement 'CREATE (UNIQUE) INDEX...' and return True, otherwise return False.
"""
- rows = db.executesql('SHOW INDEXES IN `{0}`;'.format(table), as_dict=True)
+ rows = dbblog.executesql('SHOW INDEXES IN `{0}`;'.format(table), as_dict=True)
for row in rows:
if row['Key_name'] == unicode(keyname, 'utf8') and row['Column_name'] == unicode(column, 'utf8'):
return False
- db.executesql('CREATE {0}INDEX {1} ON `{2}` ({3});'.format('UNIQUE ' if unique else '', keyname, table, column))
+ dbblog.executesql('CREATE {0}INDEX {1} ON `{2}` ({3});'.format('UNIQUE ' if unique else '', keyname, table, column))
return True
@@ -82,81 +86,85 @@
create_index_if_not_exists(db, 'tags', 'idx_tags_name', 'name', True)
create_index_if_not_exists(db, 'categories', 'idx_categories_name', 'name', True)
create_index_if_not_exists(db, 'posts', 'idx_posts_created_on', 'created_on')
- db.commit()
+ dbblog.commit()
def drop_tables():
"""Drop all tables in db"""
for table_name in ['images', 'tags', 'categories', 'posts']:
- db.executesql('set foreign_key_checks=0; drop table {0};'.format(table_name))
+ dbblog.executesql('set foreign_key_checks=0; drop table {0};'.format(table_name))
def add_test_data():
- db.categories.bulk_insert([{'name': 'Hírek'}, {'name': 'Közönségszolgálat'}, {'name': 'Helyesírási érdekességek'}])
+ dbblog.categories.bulk_insert([{'name': 'Hírek'}, {'name': 'Közönségszolgálat'}, {'name': 'Helyesírási érdekességek'}])
now = datetime.datetime.now()
- db.posts.insert(
+ dbblog.posts.insert(
slug='elindult-a-blog',
title='Elindult a blog',
+ image_url='http://philsayer.files.wordpress.com/2011/11/old-typewriter.jpg',
body="Ez itt az **első** bekezdés.\n\nEz pedig a ''második''.",
created_on=now.strftime('%Y-%m-%d %H:%M:%S'),
- category=db.categories(name='Hírek').id,
+ category=dbblog.categories(name='Hírek').id,
tags=['új verzió']
)
- db.posts.insert(
+ dbblog.posts.insert(
slug='teszt-bejegyzes',
title='Teszt bejegyzés',
+ image_url='http://philsayer.files.wordpress.com/2011/11/old-typewriter.jpg',
body='Ez egy teszt bejegyzés.',
created_on=(now + datetime.timedelta(minutes=1)).strftime('%Y-%m-%d %H:%M:%S'),
- category=db.categories(name='Közönségszolgálat').id,
+ category=dbblog.categories(name='Közönségszolgálat').id,
tags=['teszt']
)
- db.posts.insert(
+ dbblog.posts.insert(
slug='teszt-bejegyzes-2',
title='Teszt bejegyzés 2',
+ image_url='http://philsayer.files.wordpress.com/2011/11/old-typewriter.jpg',
body='Ez is egy teszt bejegyzés.',
created_on=(now + datetime.timedelta(minutes=2)).strftime('%Y-%m-%d %H:%M:%S'),
- category=db.categories(name='Helyesírási érdekességek').id,
+ category=dbblog.categories(name='Helyesírási érdekességek').id,
tags=['teszt', 'különírás-egybeírás']
)
- db.posts.insert(
+ dbblog.posts.insert(
slug='fekete-rigo-sarga-rigo',
title='Fekete, sárga: rigó?',
+ image_url='http://philsayer.files.wordpress.com/2011/11/old-typewriter.jpg',
body='Hogy kell írni őket? A franc se tudja.',
created_on=(now + datetime.timedelta(minutes=3)).strftime('%Y-%m-%d %H:%M:%S'),
- category=db.categories(name='Helyesírási érdekességek').id,
+ category=dbblog.categories(name='Helyesírási érdekességek').id,
tags=['teszt', 'tulajdonnevek']
)
- db.commit()
+ dbblog.commit()
def regenerate_tags_table():
"""Truncate table tags and generate its contents from table posts (using column tags)"""
- db.tags.truncate()
+ dbblog.tags.truncate()
data = {} # {tag: [post_ids], ...}
- for row in db().select(db.posts.ALL):
+ for row in dbblog().select(dbblog.posts.ALL):
for tag in row.tags:
if tag not in data:
data[tag] = [row.id]
else:
data[tag].append(row.id)
for tag in data:
- db.tags.insert(name=tag, posts=data[tag])
- db.commit()
+ dbblog.tags.insert(name=tag, posts=data[tag])
+ dbblog.commit()
def some_test():
- print(db(db.categories.name=='Szolgálati közlemények').select()[0].id)
- print(db.categories(name='Szolgálati közlemények').id)
+ print(dbblog(dbblog.categories.name=='Szolgálati közlemények').select()[0].id)
+ print(dbblog.categories(name='Szolgálati közlemények').id)
def query_categories():
- rows = db().select(db.categories.ALL, orderby=db.categories.id)
+ rows = dbblog().select(dbblog.categories.ALL, orderby=dbblog.categories.id)
for row in rows:
print('{0}: {1}'.format(row['id'], row['name']))
def query_posts():
- rows = db(db.posts.category==db.categories.id).select(orderby=~db.posts.created_on, limitby=(0, 5))
+ rows = dbblog(dbblog.posts.category==dbblog.categories.id).select(orderby=~dbblog.posts.created_on, limitby=(0, 5))
for row in rows:
#print row
print(row.posts['title'], row.categories['name'], util.pretty_hu_date(row.posts.created_on).encode('utf8'))
@@ -164,16 +172,16 @@
def query_post():
slug='fekete-rigo-sarga-rigo'
- #post = db((db.posts.category==db.categories.id) & (db.posts.slug==slug)).select()
- post = db( (db.posts.category==db.categories.id) & (db.posts.slug==slug)).select(
- left=db.images.on(db.posts.image==db.images.id) )
+ #post = dbblog((dbblog.posts.category==dbblog.categories.id) & (dbblog.posts.slug==slug)).select()
+ post = dbblog( (dbblog.posts.category==dbblog.categories.id) & (dbblog.posts.slug==slug)).select(
+ left=dbblog.images.on(dbblog.posts.image==dbblog.images.id) )
print(len(post))
print(post)
def query_posts_tag():
tag='tulajdonnevek'
- posts = db(db.posts.tags.contains(tag)).select(db.posts.ALL)
+ posts = dbblog(dbblog.posts.tags.contains(tag)).select(dbblog.posts.ALL)
print(posts)
@@ -185,10 +193,10 @@
define_tables()
#create_indexes()
#add_test_data()
-#regenerate_tags_table()
+regenerate_tags_table()
#query_categories()
-query_posts()
+#query_posts()
#query_post()
#query_posts_tag()
More information about the Hejes-devel
mailing list