Loading...
 

How to seed DB with test data

For tests to run smoothly, it's important that each test, or at the minimum, each test suite, be able to restore the Tiki application to a given starting state. This starting state should contain the data necessary to carry out the test or test suite, and nothing more. In particular, it should not coptain any side effects caused by tests that have been run priorly.

There are at least two possible approaches to this (actually, probably more, see below):

  • Create an XML dump which, when restore will put the TikiWiki in the desired starting statefor a given test. Then restore this dump at the beginning of each suite or test.

  • Create an ordered list of profiles which, when applied to a fresh install of Tiki, will put the TikiWiki in the desired starting statefor a given test. Then restore this profile at the beginning of each suite or test.


Both approaches seem reasonable. The criteria for choosing between them might be:

  • Criteria 1: Which approach will allow easiest creation of tests by developer/tester, especially for non-technical testers.
    • Simplicity of creation is primordial for the following use case: Testing Use Case: Non-dev wants to create a test and share it with the dev community
    • It's not clear which of the two approaches is best w.r.t. Criteria 1. On the one hand, it's probably easier to understand a profile than a DB. On the other hand, it might be easier for a non-technical person to create DB dump than a profile. Creating a DB dump involves creating a new DB, then using the Tiki UI to configure it just the way you want it to be, then do the dump. Creating a profile requires you to understand the syntax of the profile files. Also, there might be some things that need to be done to the DB which simply cannot be done throough existing profile instructions.

Approach 1: Restore a MySQL dump before each new test (synchronously).


http://www.devdaily.com/blog/post/mysql/dump-mysql-database-schema/

Tried this command:

Image
Copy to clipboard
mysqldump -uroot tiki > tiki.sql


where 'tiki' was the name of the DB.

But got this error:

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'tiki\_freetags'': Can't create/write to file 'C:\DOCUME~1\DESILE~1\LOCALS~1\Temp\#sql_138c_0.MYI' (Errcode: 17)
(1)

As it turns out, according to this page:

http://www.poscribes.com/5_steps_to_mysql_replication_on_windows

This is caused by VirusScan. In order for mysqldump to work on windows, you need to set some exceptions in VirusScan:

  • Right click on VirusScan icon in the system tray
  • On-Access Scan Properties
  • All Process (at the left)
  • Detection Tab
  • Exclusions > Add > Browse
  • Select the temp directory mentioned in the error message.
  • Check Also execute subfolders
  • OK


Also:

  • All Process
  • Unwanted Programs
  • Uncheck Detect unwanted programs
  • OK


Once that was done, I was able to restore the DB as follows:

Copy to clipboard
mysql -uroot tiki < tiki.sql


Note however that restoring took a good 30 seconds.

Ideas for decreasing the restore time:

  • Manually edit the .sql file, and delete the tables that are not needed.
    • Have to be careful in doing it.
  • Increase MySQL buffer size in mysql.ini (Rick Sapir suggested that, but not clear to AD why that would help).
    • I edited the my.ini file and increased all variables that had something to do with buffer, by a factor of 10. Saved, restarted EasyPHP, rerun: still about 30 secs.
  • Increase MySQL max-allowed-packet in mysql.ini
    • tried --max_allowed_packet=16M, then 160M, then 1600M. Still about 30 secs each time.


Approach 2: Restore a MySQL dump before each new test (Asynchronously)


This one was suggested by by Chris Drake

> If you wanted an "instant" (0-seconds-restore) solution - that, I
> think, would be very easy:
>
> 1. Create *two* identical databases (eg: tiki1 and tiki2)
>
> 2. To "restore" - you...
> A. simply change the tiki settings to use whichever other one it's
> not already using (and, to be safe, restart apache)
> B. now - in parallel, you can drop and re-create the "stale"
> database that you just stopped using, in preparation for the
> next restore.

The only problem with that approach is that running the tests might take only say, 5 seconds, while creating the DB for the next test might take 30 seconds.

Approach 3: Delete data from tables at the end of each test

  • Instead of redefining the tables from scratch before each test, might do the following.
  • Create a test DB that has all the tables defined in it.
  • At the end of each test, simply delete all the data contained in all the tables (using mysql statements).
  • Then, at the start of each test, load profiles into this empty db.
  • Question is... how long does clearing the tables take? Might be just as long as creating the tables.


Approach 4: Copy all files related to a particular DB

  • In all likelyhood, the information contained in a particular DB is captured in the form of a series of files, and maybe some environment variables and registry entries.
  • Do we know what all those files en entries are?
  • Could we simply make copy of those files for the DB in its start state, then restore the DB to its start state by copying those files over?
  • I know this is not the way to backup and restore a DB that is in production. But it might be good enough for a test DB.
  • Question is... what are those files and entries?


According to Rick Sapir:

If you're talking about the actual *MySQL* database tables (*.MYD, *.MYI, etc.), they are located in (assuming you are still using EasyPHP):


C:Program FilesEasyPHP 2.0b1mysqldata*YOUR DATABASE NAME*

(or something similar).


Simply copying these files to "rebuild/restore" your database each time should be much faster than rebuilding the datbase via MySQL each time. A good idea (wish I had thought of it)...

-R


Some info here:


"When it comes to backing up MySQL databases, don't ignore the most obvious solution: making copies of the database files themselves."

2009-02-20: As it turns out, just copying the DB files takes a good 15-30 seconds! So it's not really faster than doing a SQL DB dump and a restore.

Approach 5: MySQL test framework???


There might be some useful information here:

http://dev.mysql.com/doc/mysqltest/en/index.html

Approach 5: DbUnit


DBUnit:

  • http://www.dbunit.org/
  • DbUnit is a JUnit extension (also usable with Ant) targeted at database-driven projects that, among other things, puts your database into a known state between test runs. This is an excellent way to avoid the myriad of problems that can occur when one test case corrupts the database and causes subsequent tests to fail or exacerbate the damage.


PHP port available here:
http://www.ds-o.com/archives/63-PHPUnit-Database-Extension-DBUnit-Port.html

Recommended on this page:

http://www.scribd.com/doc/2569454/Testing-PHP-MySQL-Applications-with-PHPUnitDbUnit

by the developer of phunit.

Approach 6: SQLite


http://www.sqlite.org/

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

Recommended on this page:

http://www.scribd.com/doc/2569454/Testing-PHP-MySQL-Applications-with-PHPUnitDbUnit

by the developer of phunit.

Keywords

The following is a list of keywords that should serve as hubs for navigation within the Tiki development and should correspond to documentation keywords.

Each feature in Tiki has a wiki page which regroups all the bugs, requests for enhancements, etc. It is somewhat a form of wiki-based project management. You can also express your interest in a feature by adding it to your profile. You can also try out the Dynamic filter.

Accessibility (WAI & 508)
Accounting
Administration
Ajax
Articles & Submissions
Backlinks
Banner
Batch
BigBlueButton audio/video/chat/screensharing
Blog
Bookmark
Browser Compatibility
Calendar
Category
Chat
Comment
Communication Center
Consistency
Contacts Address book
Contact us
Content template
Contribution
Cookie
Copyright
Credits
Custom Home (and Group Home Page)
Database MySQL - MyISAM
Database MySQL - InnoDB
Date and Time
Debugger Console
Diagram
Directory (of hyperlinks)
Documentation link from Tiki to doc.tiki.org (Help System)
Docs
DogFood
Draw -superseded by Diagram
Dynamic Content
Preferences
Dynamic Variable
External Authentication
FAQ
Featured links
Feeds (RSS)
File Gallery
Forum
Friendship Network (Community)
Gantt
Group
Groupmail
Help
History
Hotword
HTML Page
i18n (Multilingual, l10n, Babelfish)
Image Gallery
Import-Export
Install
Integrator
Interoperability
Inter-User Messages
InterTiki
jQuery
Kaltura video management
Kanban
Karma
Live Support
Logs (system & action)
Lost edit protection
Mail-in
Map
Menu
Meta Tag
Missing features
Visual Mapping
Mobile
Mods
Modules
MultiTiki
MyTiki
Newsletter
Notepad
OS independence (Non-Linux, Windows/IIS, Mac, BSD)
Organic Groups (Self-managed Teams)
Packages
Payment
PDF
Performance Speed / Load / Compression / Cache
Permission
Poll
Profiles
Quiz
Rating
Realname
Report
Revision Approval
Scheduler
Score
Search engine optimization (SEO)
Search
Security
Semantic links
Share
Shopping Cart
Shoutbox
Site Identity
Slideshow
Smarty Template
Social Networking
Spam protection (Anti-bot CATPCHA)
Spellcheck
Spreadsheet
Staging and Approval
Stats
Survey
Syntax Highlighter (Codemirror)
Tablesorter
Tags
Task
Tell a Friend
Terms and Conditions
Theme
TikiTests
Federated Timesheets
Token Access
Toolbar (Quicktags)
Tours
Trackers
TRIM
User Administration
User Files
User Menu
Watch
Webmail and Groupmail
WebServices
Wiki History, page rename, etc
Wiki plugins extends basic syntax
Wiki syntax text area, parser, etc
Wiki structure (book and table of content)
Workspace and perspectives
WYSIWTSN
WYSIWYCA
WYSIWYG
XMLRPC
XMPP




Useful Tools