CS TimeClock Documents

username password

Shrinking a Clock SQL Database

    It is possible to shrink the clock's sql lite database in preparation for transport to another unit or for maintenance reasons.



    Copying the database from the clock

    Using WinSCP, access the clock via SSH and stop the clock processes by typing stopclock in the terminal window of WinSCP.

    Using WinSCP's commander like interface, navigate to the clock's data folder at /var/local/data. This data folder contains a file called capeclock.db which is a SqlLite database containing the clocks data. Copy this file to your local PC. In my example I have copied the file over to My Documents Folder.

    Managing the SQL Lite Database

    My SQL Lite database tool of choice is SQLite Meastro. Run this application and under the database menu, click on create new database profile.

    I use localhost as my default profile name. Click on the next button. On the Database name column, select the database which you placed on your local PC. 

    Click on the ready button. Your database will now appear under the profile tree called local host. Click on the plus next to the Database section to expand your view.

    Double click on the database to expose the objects. Now select the tables you want to maintain.


    Typically the tables with the large amounts of data, is clocking and hour.

    Open the clocking table or hour table by double clicking on the relevant table. The table data will appear as shown below.

    On the task panels in the middle of the application, select data management and then Generate query. On the query window, you may now type commands to manipulate or edit or maintain the data.

    Managing large tables

    Shrinking the database is a matter of trial and error while deleting either clocking and or hour history. You can achieve this by starting with the hours and deleting history older than a year. This is done via the SQL Query:

    delete from hour where date > 1302220800;


    Note the value 1302220800 represents 16/8/2010 in  unix time. Time and date on the CS TimeClocks are stored in unix time. You can use my preferred converter at http://www.onlineconversion.com/unix_time.htm to convert your prefered date and time to unix.

    Executing the above query will delete all the hours records older than a year. Please note that you must first compress the database before checking the database file size by executing the following query:


    Now check the database size. If it is still to large, delete the clocking table with the following qeury.

    delete from clocking where t > 1302220800;


    and remeber to compress the database again. If the database is still too large, you can delete say three months at a time on each table till you are satisfied with the database size.

    NOTE: Remember to compress the database before evaluating the database size.

    The Final Stages

    When the database is the size you require, it can be copied to your new device or restored to your new device. When copying the database to a new CS TimeClock, remember to execute stopclock, copy the database into the data folder and execute startclock again.

    The database file attributes must be Read, Write and Execute for User, Owner and Root.

    Page last modified 09:30, 26 Jan 2012 by Admin CS TimeClock Documents > How To > Shrinking a Clock SQL Database


    You must login to post a comment.
    Attach file


    Powered by MindTouch Core