MixERP PgDoc on OSX and Linux

MixERP PgDoc is a console-based application that creates beautiful PostgreSQL database documentation. The  first release supported Windows and with the second release today, we now support OSX and Linux (tested on OSX Yosemite and Ubuntu 14.4). Please make sure you  have Mono installed first before you download PgDoc here:

Download PgDoc

Running on OSX and Linux

Extract the downloaded archive mixerp-pgdoc-mono.zip. Open terminal and type

mono /path/to/mixerp-pgdoc.exe <arguments>

Or simply

mono /users/nirvan/desktop/pg-doc/mixerp-pgdoc.exe -s=localhost -d=mixerp -u=postgres -p=secret -o=/users/nirvan/desktop/db-doc

I have created a category in MixERP Forums for further discussion.

Read More
MixERP PostgreSQL Database Documenter

We chose PostgreSQL database for our ERP software MixERP, which is going Beta 1 release very soon. Unlike other ERP solutions which support PostgreSQL, we do not use PostgreSQL just to merely only store the tables. In fact, we try and take the full advantage of PostgreSQL database power and capabilities. Just to remind you, MixERP is an open source ERP solution, which has been undergoing development since a year. Despite of being a very small team, we have been getting a substantial number of requests for our database design.

Since we needed a good documentation generator, I investigated the existing tools which could do that. I was not so pleased with the existing tools related to documentation generation. I thought I would give it a try myself.

For the task, I used C#.net and Npgsql, and of course PostgreSQL, as well. During the last few days, I was in PostgreSQL documentation site all the time:

http://www.postgresql.org/docs/9.4/static/catalogs.html

The documentation site provided metadata information profoundly well. There was nothing that was not already there, except for the fact that I could not find a find a way to ask PostgreSQL to give me the definitions of Composite Types. Interestingly, pgAdmin3 could tell me exactly how a Type was defined. Upon investigating what pgAdmin was doing under the hood, I was able to create a function to extract that information. :)

Coming back to my project MixERPPgDoc, it is a small console application exposing a few arguments, and packs all necessary dependencies inside itself. Since it is a self-contained executable file, one should be able to quickly use it and play around with. To give you an idea, MixERPPgDoc creates HTML documentation on the fly. I used a new and sexy CSS component library called SemanticUI for design, and for code highlighting task, prism.js.

Syntax

mixerp-pgdoc.exe -s=[server] -d=[database] -u=[pg_user] -p=[pwd] -o=[output_dir]

Example

mixerp-pgdoc.exe -s=localhost -d=mixerp -u=postgres -p=secret -o="c:\mixerp-doc"

Example Documentation Site (Produced by MixERPPgDoc)

http://mixerp.org/db-docs/db.html MixERPPgDoc is a free software, even for commercial use. Do not use this against your production database. I am not liable if your screw things up.

https://cdn.mixerp.net/assets/download/mixerp-pgdoc-mono.zip

If you like it, please do share. MixERP project needs you. :)

Read More
phpBB to Kunena Migration Script

We have migrated our forum from phpBB to Kunena and would like to share the migration script with the Joomla community. Before you run this script, note the following points:

  • This script will delete all the contents of Kunena forum and joomla users. Make a backup before you proceed.
  • To migrate attachments from phpBB, copy the files inside phpBB attachment folder and paste those on 'media/kunena/attachments/migrated/'. Add file extensions to the files (generally .jpg). Done!
  • This script will migrate all phpBB users. Remember, the existing users will have to use the joomla feature "forgot password" in order to reset their password.
  • This script is licensed under GNU/GPLv2. Know your rights.

Download the Script Here

https://github.com/mixerp/phpbb-to-kunena

Read More
PostgreSQL Unit Testing Framework (plpgunit)

Plpgunit started out of curiosity on why a unit testing framework cannot be simple and easy to use. Plpgunit does not require any additional dependencies and is ready to be used on your PostgreSQL Server database.

Creating a Plpgunit Unit Test

A unit test is a function which :

  • must be created under the schema "unit_tests".
  • should not have any arguments.
  • should always return "test_result" data type.

First Thing First

However you could do that, but there is no need to call each test function manually. The following query automatically invokes all unit tests that have been already created:

BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
ROLLBACK TRANSACTION;

Remember, if your test(s) does not contain DML statements, there is no need to BEGIN and ROLLBACK transaction.

Examples

View documentation for more examples.

Example #1

DROP FUNCTION IF EXISTS unit_tests.example1();

CREATE FUNCTION unit_tests.example1()
RETURNS test_result
AS
$$
DECLARE message test_result;
BEGIN
    IF 1 = 1 THEN
        SELECT assert.fail('This failed intentionally.') INTO message;
        RETURN message;
    END IF;

    SELECT assert.ok('End of test.') INTO message;  
    RETURN message; 
END
$$
LANGUAGE plpgsql;

--BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
--ROLLBACK TRANSACTION;

Will Result in

Test completed on : 2013-10-18 19:30:01.543 UTC. 
Total test runtime: 19 ms.

Total tests run : 1.
Passed tests    : 0.
Failed tests    : 1.

List of failed tests:
-----------------------------
unit_tests.example1() --> This failed intentionally.

Example #2

DROP FUNCTION IF EXISTS unit_tests.example2()

CREATE FUNCTION unit_tests.example2()
RETURNS test_result
AS
$$
DECLARE message test_result;
DECLARE result boolean;
DECLARE have integer;
DECLARE want integer;
BEGIN
    want := 100;
    SELECT 50 + 49 INTO have;

    SELECT * FROM assert.is_equal(have, want) INTO message, result;

    --Test failed.
    IF result = false THEN
        RETURN message;
    END IF;

    --Test passed.
    SELECT assert.ok('End of test.') INTO message;  
    RETURN message; 
END
$$
LANGUAGE plpgsql;

--BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
--ROLLBACK TRANSACTION;

Will Result in

Test completed on : 2013-10-18 19:47:11.886 UTC. 
Total test runtime: 21 ms.

Total tests run : 2.
Passed tests    : 0.
Failed tests    : 2.

List of failed tests:
-----------------------------
unit_tests.example1() --> This failed intentionally.
unit_tests.example2() --> ASSERT IS_EQUAL FAILED.

Have -> 99
Want -> 100

Example #3

DROP FUNCTION IF EXISTS unit_tests.example3();

CREATE FUNCTION unit_tests.example3()
RETURNS test_result
AS
$$
DECLARE message test_result;
DECLARE result boolean;
DECLARE have integer;
DECLARE dont_want integer;
BEGIN
    dont_want := 100;
    SELECT 50 + 49 INTO have;

    SELECT * FROM assert.is_not_equal(have, dont_want) INTO message, result;

    --Test failed.
    IF result = false THEN
        RETURN message;
    END IF;

    --Test passed.
    SELECT assert.ok('End of test.') INTO message;  
    RETURN message; 
END
$$
LANGUAGE plpgsql;

--BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
--ROLLBACK TRANSACTION;

Will Result in

Test completed on : 2013-10-18 19:48:30.578 UTC. 
Total test runtime: 11 ms.

Total tests run : 3.
Passed tests    : 1.
Failed tests    : 2.

List of failed tests:
-----------------------------
unit_tests.example1() --> This failed intentionally.
unit_tests.example2() --> ASSERT IS_EQUAL FAILED.

Have -> 99
Want -> 100

Need Contributors for Writing Examples

We need contributors. If you are interested to contribute, let's talk:

https://www.facebook.com/binod.nirvan/

Happy testing!

Read More
PostgreSQL Unit Testing Framework (plpgunit)

Plpgunit started out of curiosity on why a unit testing framework cannot be simple and easy to use? Unit testing frameworks for databases are not amongst the most widely available tools for developers particularly if you are building an application that is powered by PostgreSQL- The world’s most advanced open source database, because then your options are further curtailed to almost none in terms of easy implementation and efficient use. However, this has only enabled us to come up with our very own unit testing framework that works solely on PostgreSQL Database and is effortlessly easy to implement. You can simply install the framework and start testing your databases with minimum configuration and no external dependency.

Documentation

FunctionUsage
assert.fail(text)Fails a test.
assert.ok(text)Should be placed at the end of a test function's body to propose that the test has passed.
assert.is_equal(IN have anyelement, IN want anyelement, OUT message text, OUT result boolean)Fails the test if the first two arguments do not match.
assert.is_not_equal(IN already_have anyelement, IN dont_want anyelement, OUT message text, OUT result boolean)Fails the test if the first two arguments match.
assert.pass(text)Passes the test.
assert.are_equal(VARIADIC anyarray, OUT message text, OUT result boolean)Fails the test if any item of the supplied arguments is not equal to others. Remember, you can pass any number of arguments here.
assert.are_not_equal(VARIADIC anyarray, OUT message text, OUT result boolean)Fails the test if any item of the supplied arguments is equal to another. Remember, you can pass any number of arguments here.
assert.is_null(IN anyelement, OUT message text, OUT result boolean)Fails the test if the first argument is NOT NULL.
assert.is_not_null(IN anyelement, OUT message text, OUT result boolean)Fails the test if the first argument is NULL.
assert.is_true(IN boolean, OUT message text, OUT result boolean)Fails the test if the first argument is FALSE.
assert.is_false(IN boolean, OUT message text, OUT result boolean)Fails the test if the first argument is TRUE.
assert.is_greater_than(IN x anyelement, IN y anyelement, OUT message text, OUT result boolean)Fails the test if the first argument is not greater than the second argument.
assert.is_less_than(IN x anyelement, IN y anyelement, OUT message text, OUT result boolean)Fails the test if the first argument is not less than the second argument.
assert.function_exists(function_name text, OUT message text, OUT result boolean)Fails the test if the function does not exist in the current database. Make sure that the passed function name is a fully qualified relation name, and contains argument types.

Creating a Plpgunit Unit Test

A unit test is a function which :

  • must be created under the schema "unit_tests".
  • should not have any arguments.
  • should always return "test_result" data type.

The following query invokes all unit tests that have been created under the schema "unit_tests":

 
BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
ROLLBACK TRANSACTION;

Example #1

 
DROP FUNCTION IF EXISTS unit_tests.example1();

CREATE FUNCTION unit_tests.example1()
RETURNS test_result
AS
$$
DECLARE message test_result;
BEGIN
    IF 1 = 1 THEN
        SELECT assert.fail('This failed intentionally.') INTO message;
        RETURN message;
    END IF;

    SELECT assert.ok('End of test.') INTO message;	
    RETURN message;	
END
$$
LANGUAGE plpgsql;

BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
ROLLBACK TRANSACTION;

Will Result In

 
Test completed on : 2013-10-18 19:30:01.543 UTC. 
Total test runtime: 19 ms.

Total tests run : 1.
Passed tests    : 0.
Failed tests    : 1.

List of failed tests:
-----------------------------
unit_tests.example1() --> This failed intentionally.

Example #2

CREATE FUNCTION unit_tests.example2()
RETURNS test_result
AS
$$
DECLARE message test_result;
DECLARE result boolean;
DECLARE have integer;
DECLARE want integer;
BEGIN
    want := 100;
    SELECT 50 + 49 INTO have;

    SELECT * FROM assert.is_equal(have, want) INTO message, result;

    --Test failed.
    IF result = false THEN
        RETURN message;
    END IF;

    --Test passed.
    SELECT assert.ok('End of test.') INTO message;	
    RETURN message;	
END
$$
LANGUAGE plpgsql;

BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
ROLLBACK TRANSACTION;

Will Result In


Test completed on : 2013-10-18 19:47:11.886 UTC. 
Total test runtime: 21 ms.

Total tests run : 2.
Passed tests    : 0.
Failed tests    : 2.

List of failed tests:
-----------------------------
unit_tests.example1() --> This failed intentionally.
unit_tests.example2() --> ASSERT IS_EQUAL FAILED.

Have -> 99
Want -> 100

Example #3


DROP FUNCTION IF EXISTS unit_tests.example3();

CREATE FUNCTION unit_tests.example3()
RETURNS test_result
AS
$$
DECLARE message test_result;
DECLARE result boolean;
DECLARE have integer;
DECLARE dont_want integer;
BEGIN
    dont_want := 100;
    SELECT 50 + 49 INTO have;

    SELECT * FROM assert.is_not_equal(have, dont_want) INTO message, result;

    --Test failed.
    IF result = false THEN
        RETURN message;
    END IF;

    --Test passed.
    SELECT assert.ok('End of test.') INTO message;	
    RETURN message;	
END
$$
LANGUAGE plpgsql;

BEGIN TRANSACTION;
SELECT * FROM unit_tests.begin();
ROLLBACK TRANSACTION;

Will Result In


Test completed on : 2013-10-18 19:48:30.578 UTC. 
Total test runtime: 11 ms.

Total tests run : 3.
Passed tests    : 1.
Failed tests    : 2.

List of failed tests:
-----------------------------
unit_tests.example1() --> This failed intentionally.
unit_tests.example2() --> ASSERT IS_EQUAL FAILED.

Have -> 99
Want -> 100

Download Plpgunit Here Happy testing !

Read More
Fun with Date Expressions

Much emphasis has been given to make sure that MixERP comes out with one of the most elegant user interface for an ERP application. But while doing so, we also make sure that the functionalities of a standard ERP applications are not compromised. Hence, MixERP has been embedded with some very powerful tools and features under the surface. The date expression is among one such example that has been fully implemented into the application already. The Date Expressions in MixERP help you quickly enter corresponding dates into the application by entering simple alphabetical expressions. This will help you save time since you will not be required to type-in/select the entire date every time. For example if you want to enter today's date simply type "d" and hit TAB or click on any other field, MixERP will automatically enter today's date. If you want to enter yesterday's date just type in "-d". Similarly, enter +d for tomorrow's date or "2d" if you want to enter the date of the day after tomorrow. List of all Expressions Applicable  Please Note that these expressions are applicable only for the Date Fields inside the application.

MixERP Date Expressions

Now for those of you who want to get to the nitty gritty of things, here is the script behind:

 $(document).ready(function () {
    $(".date").blur(function () {
        if (today == "") return;
        var control = $(this);
        var value = control.val().trim().toLowerCase();
        var result;

        if (value == "d") {
            result = dateAdd(today, "d", 0);
            control.val(result);
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value == "m" || value == "+m") {
            control.val(dateAdd(today, "m", 1));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value == "w" || value == "+w") {
            control.val(dateAdd(today, "d", 7));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value == "y" || value == "+y") {
            control.val(dateAdd(today, "y", 1));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value == "-d") {
            control.val(dateAdd(today, "d", -1));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value == "+d") {
            control.val(dateAdd(today, "d", 1));
            Page_ClientValidate(control.attr("id"));
            return;
        }


        if (value == "-w") {
            control.val(dateAdd(today, "d", -7));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value == "-m") {
            control.val(dateAdd(today, "m", -1));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value == "-y") {
            control.val(dateAdd(today, "y", -1));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value.indexOf("d") >= 0) {
            var number = parseInt(value.replace("d"));
            control.val(dateAdd(today, "d", number));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value.indexOf("w") >= 0) {
            var number = parseInt(value.replace("w"));
            control.val(dateAdd(today, "d", number * 7));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value.indexOf("m") >= 0) {
            var number = parseInt(value.replace("m"));
            control.val(dateAdd(today, "m", number));
            Page_ClientValidate(control.attr("id"));
            return;
        }

        if (value.indexOf("y") >= 0) {
            var number = parseInt(value.replace("y"));
            control.val(dateAdd(today, "y", number));
            Page_ClientValidate(control.attr("id"));
            return;
        }
    });
});

function dateAdd(dt, expression, number) {
    var d = Date.parseExact(dt, shortDateFormat);
    var ret;

    if (expression == "d") {
        ret = new Date(d.getFullYear(), d.getMonth(), d.getDate() + parseInt(number));
    }

    if (expression == "m") {
        ret = new Date(d.getFullYear(), d.getMonth() + parseInt(number), d.getDate());
    }

    if (expression == "y") {
        ret = new Date(d.getFullYear() + parseInt(number), d.getMonth(), d.getDate());
    }

    return ret.toString(shortDateFormat);
}
 
Read More