[Hejes-devel] [870] tests for blog DB

[hejes-devel at nytud.hu]

Revision: 870
Author:   mihaltz
Date:     2013-07-26 15:52:39 +0200 (Fri, 26 Jul 2013)
Log Message:
-----------
tests for blog DB

Modified Paths:
--------------
    trunk/misc/dbblog/Makefile
    trunk/misc/dbblog/test.py

Modified: trunk/misc/dbblog/Makefile
===================================================================
--- trunk/misc/dbblog/Makefile	2013-07-25 11:50:44 UTC (rev 869)
+++ trunk/misc/dbblog/Makefile	2013-07-26 13:52:39 UTC (rev 870)
@@ -1,5 +1,9 @@
 .PHONY: create_db create_dbusers
 
+# drop DB
+drop_db:
+	echo  "drop database dbhelyesblog;" | mysql -u root -p
+
 # create DB
 create_db:
 	mysql -u root -p < dbblog_create_database.sql

Modified: trunk/misc/dbblog/test.py
===================================================================
--- trunk/misc/dbblog/test.py	2013-07-25 11:50:44 UTC (rev 869)
+++ trunk/misc/dbblog/test.py	2013-07-26 13:52:39 UTC (rev 870)
@@ -11,6 +11,7 @@
 
 '''
 
+import datetime
 import sys
 import os
 
@@ -18,9 +19,11 @@
 from gluon import *
 from gluon.dal import Row
 
+
 db = DAL("mysql://dbblogadmin:d4bl0GGr@localhost/dbhelyesblog")
 # location of .table files = ???
 
+
 def define_tables():
   """
   Define the table names and the fields + add restrictions.
@@ -54,17 +57,72 @@
   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')
+  
+  db.commit()
  
 
-# TODO
+def create_index_if_not_exists(db, table, keyname, column, unique=False):
+  """Check if index keyname on db.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)
+  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))
+  return True
+
+
 def create_indexes():
-  # create indexes on frequently used columns; SQLite engine only!!!
-  db.executesql('CREATE UNIQUE INDEX IF NOT EXISTS idx_hqa_main_id ON hqa_main (id);')
-  db.executesql('CREATE INDEX IF NOT EXISTS idx_hqa_main_qword ON hqa_main (qword);')
-  db.executesql('CREATE UNIQUE INDEX IF NOT EXISTS idx_hqa_prob_cat_names_id ON hqa_prob_cat_names (id);')
-  db.executesql('CREATE UNIQUE INDEX IF NOT EXISTS idx_hqa_prof_cat_names_id ON hqa_prof_cat_names (id);')
-  db.executesql('CREATE UNIQUE INDEX IF NOT EXISTS idx_hqa_remark_names_id ON hqa_remark_names (id);')
+  """Create indexes (if they didn't exist) on frequently used columns. Note: tables must already exist (otherwise raises exception)."""
+  #db.executesql('CREATE UNIQUE INDEX idx_images_title ON images (title);')
+  create_index_if_not_exists(db, 'images', 'idx_images_title', 'title', True)
+  #db.executesql('CREATE UNIQUE INDEX idx_tags_name ON tags (name);')
+  create_index_if_not_exists(db, 'tags', 'idx_tags_name', 'name', True)
+  #db.executesql('CREATE UNIQUE INDEX idx_categories_name ON categories (name);')  
+  create_index_if_not_exists(db, 'categories', 'idx_categories_name', 'name', True)
+  #db.executesql('CREATE INDEX idx_posts_created_on ON posts (created_on);')  
+  create_index_if_not_exists(db, 'posts', 'idx_posts_created_on', 'created_on')
+  db.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))
+
+
+def add_test_data():
+  db.categories.bulk_insert([{'name': 'Hírek'}, {'name': 'Közönségszolgálat'}, {'name': 'Helyesírási érdekességek'}])
+  db.posts.insert(
+    slug='elindult-a-blog',
+    title='Elindult a blog',
+    body='#Elindult a blog\n\nEnnek *nagyon* örülünk.',
+    created_on=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
+    category=db.categories(name='Hírek').id
+    )
+  db.posts.insert(
+    slug='teszt-bejegyzes',
+    title='Teszt bejegyzés',
+    body='Ez egy teszt bejegyzés.',
+    created_on=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
+    category=db.categories(name='Közönségszolgálat').id
+    )
+  db.posts.insert(
+    slug='teszt-bejegyzes-2',
+    title='Teszt bejegyzés 2',
+    body='Ez is egy teszt bejegyzés.',
+    created_on=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
+    category=db.categories(name='Helyesírási érdekességek').id
+    )
+  db.commit()
+
+
+
+
+# LEGACY:
+
+
 # dump table and fields and types
 def dump_tables():
   for table in db.tables:
@@ -238,7 +296,13 @@
 # main
 
 define_tables()
-#create_indexes_sqlite()
+create_indexes()
+add_test_data()
+
+#print(db(db.categories.name=='Szolgálati közlemények').select()[0].id)
+#print(db.categories(name='Szolgálati közlemények').id)
+
+
 #dropdb()
 
 #dump_tables()