Pika Offline Reporting Guide
From PikaDocs
By Matthew Friedlander
Pika Software (http://www.pikasoftware.com/)
| Table of contents |
Introduction
Data reporting requirements are ever changing and there has been some interest as of late from the community on how to leverage in house familiarity with databases and reporting software with the Pika CMS. As a result I am assembling this page as a guide for programs to set up local copies of their Pika Database to allow them to leverage their familiarity with programs like Access & Crystal reports to create their own custom reports in house. In this guide I will walk through the basic steps to set up MySQL server on a Windows environment locally and to connect it to your ODBC compatible reporting software of choice. *Note: This article is only concerned about setting up the MySQL server and the client ODBC connection, familiarity with Access & Crystal Reports is assumed on the part of the end user.
Requirements
- MySQL Server 5.0 - Windows ZIP/Setup.EXE (x86) 42.4MB - download (http://dev.mysql.com/downloads/mysql/5.0.html#win32)
- MySQL Administratior 1.2 - Windows (x86) 17.4MB - download (http://dev.mysql.com/downloads/gui-tools/5.0.html)
- MySQL ODBC Connector 3.51 - Windows MSI Installer (x86) 3.6MB - download (http://dev.mysql.com/downloads/connector/odbc/3.51.html#win32)
The system requrements are any PC Windows XP/Server 2003 and more recent versions. Please keep in mind that you should select a fast PC with adequate disk space for your MySQL server machine as its speed will directly affect its performance.
Recommended Machine Specs
- Windows XP/2003/Vista
- P4 or better Processor
- 512MB-1GB RAM
- 20+ Gigabytes Hard disk space available
Note: The Windows Firewall service will need to be disabled or port 3306 will need to be opened
Installation
MySQL Server 5.0 Installation
This section will walk through a typical install of MySQL 5.0. Please download the MySQL 5.0 Server from the link provided above. This will download a zip file, unzip it and it will contain the Setup.exe file which will guide you through the rest of the installation of MySQL server.
- Introduction Screen - Select Next.
- Setup Type - Select Typical, then Select Next.
- Ready to Install Screen - Select Install.
This will start installing all of the MySQL 5.0 Server files onto the computer. Once it is complete it will prompt you whether you would like to configure the server.
- Check the "Configure the MySQL Server Now" box and select Finish
MySQL Server 5.0 Instance Configuration Wizard
Once the Initial setup has completed you will be prompted to select the configuration settings:
- Select Standard Configuration
- Ensure that the "Launch the MySQL Server automatically" is checked and also that the "Windows Path" is also checked.
- Choose a root password and write it down in your documentation. Also, check "Allow root to login remotely" unless you plan on setting up a different account for table access.
- Finishing Up - Select Execute. This will finish the installation of MySQL.
MySQL Administrator Setup - [Optional]
This section details the steps to install the MySQL 5.0 Administration Tools. The link to download these programs is provided above, please download the installation file and run it. You will be presented with the following steps.
- Introduction Screen - Select Next
- Accept the license agreement - Select Next
- Destination Folder - If you would like this installed in a different location enter it here, otherwise select Next and accept the default location
- Setup Type - Select Complete, then select Next.
- Ready to Install - Select Install.
The program will then walk you through a series of advertisements for MySQL's enterprise products. Continue through this by selecting Next and then finally Finish.
MySQL Administrator Login
- Under the Start Menu in All Programs you will see a MySQL 5.0 group.
- In this Group select MySQL Administrator, this will bring up the login prompt.
- Host - localhost
- User - root
- Password - your root password
MySQL System Tray Monitor
The System Tray monitor is a good way to find out how your server is operating. It gives you a visual indicator of whether the server is running as well as the load on the server at any given moment which can alert you to a query that may be poorly written or malfunctioning.
- Under the Start Menu in All Programs you will see a MySQL group.
- In this Group select MySQL System Tray Monitor, this will run the monitor.
- An indicator icon will appear in the system tray next to the clock, right click on it and select Monitor Options and select Launch Monitor after Login. This will run the program at startup each time you login to the server.
Pika Database Setup
Creating the Pika Database
- Under the Start Menu in All Programs you will see a MySQL group.
- In this group there is a sub-group named MySQL 5.0 Server.
- Under the sub-group select MySQL Command Line Client.
- This will take you to the MySQL command prompt and you will be asked to provide a password.
- Enter the password you set for root during the Mysql 5.0 Server configuration.
- This will bring you to a "mysql>" prompt.
- To create the database that will store your Pika copy type "CREATE DATABASE PIKA;" and hit enter.
Enter password: ********* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.0.45-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE DATABASE PIKA;
- This should output the following if successful
Query OK, 1 row affected (0.00 sec)
Now that you have created the database you can restore your Pika database backup to it. This will require that you have Webdrive or WinSCP in order to download your most recent backups. Once you have connected with either of these programs your backups should be located under the backups folder on your account (ex. /home/[your sitename]/backups). The backups are packaged as zip files so you will need to unzip their contents to begin the restore. When unzipped, two files will be created named [your site name].data.sql and [your site name].schema.sql
To restore these files you will need to open the command prompt in windows and move to the directory that contains your sql files (ex. C:\Documents and Settings\username\Desktop) and run the following two commands to import the database.
mysql -u root -p[root's password] pika < [your site name].schema.sql mysql -u root -p[root's password] pika < [your site name].data.sql
- Note: This assumes that you created a database named pika in an earlier step. If you created a database by another name substitute that name here.
- Note: These commands may take some time to run depending on the size of the database you are importing.
Sample Pika Database Refresh Script - [Optional]
You may also want to automate this process by creating a windows batch file. Copy the script sample below into a blank file (Notepad will work) and name it pika_refresh.bat and place it in the same directory that your unzipped sql files reside. Then in the future you will only need to download the newest backups and unzip them in this directory and run the pika_refresh.bat to update your data.
mysql -u root -p[root's password] -e "DROP DATABASE pika; CREATE DATABASE pika; mysql -u root -p[root's password] pika < [your site name].schema.sql mysql -u root -p[root's password] pika < [your site name].data.sql
Conclusion & Additional Notes
Congratulations! At this point you should have a working MySQL backup copy of your Pika database for offline reporting. As noted above, you will need to disable the windows firewall on this machine or open port 3306 in order for clients to connect over ODBC to the MySQL Database.
The final step is to install the MySQL ODBC driver onto the client computers that you want to connect and retrieve data from this server for reports. Instructions on this process are available in the Pika Admin manual section located here.
