Обсуждение: What Programs Do You Use For PG?

I'm just wondering what programs you guys / girls are using PostgreSQL
for. So far I've installed PG 9 on my Debian Linux server and manually
created all my databases, schema's, and tables for my personal email /
address book. It's very basic and small but I was wondering if you
guys know of any programs that I can install that I could beef up my
PG database with? Just looking for basic applications that use PG as a
back-end so I can see how different programs are developed to create
tables and schema's. I tried looking on line for an open source email
address book that would use PostgreSQL as a back end but couldn't find
one. Anyone have any recommendations?

You can have a look at my project on sourceforge:
http://sourceforge.net/projects/proghelp builds applications with PG as a backend automatically. It uses a modified create table sql grammar as an input.

1. It automatically  generates stored procedures to insert and retrieve data
2. a database api layer to access the data in C++
3. a user interface using the Wt C++ library
    i.   search keys marked in the input file automatically become search keys in the user interface which correctly invoke the generated stored procedures.
    ii.  Foreign key references automatically become dialog boxes.
    iii. Any search keys in the referenced tables become will become search keys in the dialog box - again with correct invocations.
    iv. Any tables marked as "references multi" in the input file automatically become master details tables - using a tabview widget for details tables. The details tables automatically use the primary key from the master table to do their inserts/retrieval of data from the system.

4. Random data for testing.
5. scripts to create tables in topological order (when there are foreign key references the referenced tables are output first, for the master/details tables the master tables are output first), and drop tables and stored procedures ( when you want to prevent clutter). All the scripts are collected in a unified script generator to save you the trouble of running them individually.
6. Automatic creation of authorization and authentication modules - with fine grained control on view, add, edit and view summary.

The input file is about 200 lines, the generated code is about 15,000 lines of c++, and about 12,000 lines of stored procedures.

Whats not yet implemented

1. Paging of records.
2. Automatic edit for records.
3. Currently the search is properly implemented for varchar fields, need to fix it for dates and int/float data
4. A better random data generator.
5. god knows what else


You can see a sample application developed using the code generator here:

 http://173.230.133.34:8080/

User logins and passwords are below.

user_login_code | user_login_name | user_password | employee_code
-----------------+-----------------+---------------+---------------
               1 | zenond          | zenond123     |             1
               2 | atuld           | atul123       |             2
               3 | michaeld        | michael123    |             3
               4 | nxd             | nxd123        |             4
               5 | veerad          | veerad123     |             5
               6 | chanchud        | chanchud123   |             6
               7 | wilburd         | wilburd123    |             7
               8 | abhishekc       | abhishekc123  |             8

If it crashes - please mail me and I will restart it.

Kind Regards,
Neil

On Tue, Dec 21, 2010 at 1:32 AM, Neil D'Souza
<neil.xavier.dsouza@gmail.com> wrote:
> You can have a look at my project on sourceforge:
> http://sourceforge.net/projects/proghelp builds applications with PG as a
> backend automatically. It uses a modified create table sql grammar as an
> input.
>
> 1. It automatically  generates stored procedures to insert and retrieve data
> 2. a database api layer to access the data in C++
> 3. a user interface using the Wt C++ library
>     i.   search keys marked in the input file automatically become search
> keys in the user interface which correctly invoke the generated stored
> procedures.
>     ii.  Foreign key references automatically become dialog boxes.
>     iii. Any search keys in the referenced tables become will become search
> keys in the dialog box - again with correct invocations.
>     iv. Any tables marked as "references multi" in the input file
> automatically become master details tables - using a tabview widget for
> details tables. The details tables automatically use the primary key from
> the master table to do their inserts/retrieval of data from the system.
>
> 4. Random data for testing.
> 5. scripts to create tables in topological order (when there are foreign key
> references the referenced tables are output first, for the master/details
> tables the master tables are output first), and drop tables and stored
> procedures ( when you want to prevent clutter). All the scripts are
> collected in a unified script generator to save you the trouble of running
> them individually.
> 6. Automatic creation of authorization and authentication modules - with
> fine grained control on view, add, edit and view summary.
>
> The input file is about 200 lines, the generated code is about 15,000 lines
> of c++, and about 12,000 lines of stored procedures.
>
> Whats not yet implemented
>
> 1. Paging of records.
> 2. Automatic edit for records.
> 3. Currently the search is properly implemented for varchar fields, need to
> fix it for dates and int/float data
> 4. A better random data generator.
> 5. god knows what else
>
>
> You can see a sample application developed using the code generator here:
>
>  http://173.230.133.34:8080/
>
> User logins and passwords are below.
>
> user_login_code | user_login_name | user_password | employee_code
> -----------------+-----------------+---------------+---------------
>                1 | zenond          | zenond123     |             1
>                2 | atuld           | atul123       |             2
>                3 | michaeld        | michael123    |             3
>                4 | nxd             | nxd123        |             4
>                5 | veerad          | veerad123     |             5
>                6 | chanchud        | chanchud123   |             6
>                7 | wilburd         | wilburd123    |             7
>                8 | abhishekc       | abhishekc123  |             8
>
> If it crashes - please mail me and I will restart it.
>
> Kind Regards,
> Neil

interesting project.  do you have any documentation describing how it works?

merlin

interesting project.  do you have any documentation describing how it works?
merlin

Hi Merlin,

      Thank you for your interest in the project.

      Here is how to get it up and running so that you can try out the same application that you see running on:  http://173.230.133.34:8080/

     0. To build the project you need boost, flex, bison, and wt and g++. I'm doing this on GNU/Linux - it should be possible to build on Windows too.
     1. Download and install the webtoolkit (http://www.webtoolkit.eu) wt-3.1.6
     2. Download extjs-2.1 here is one link I found on google: http://olex.openlogic.com/packages/extjs/2.1
     3. You need to follow the extjs deployment notes given in the Wt documentation
                   for example:
                   my extjs-2.1 resides in "/home/nxd/dnld/extjs-2.1-src" , when deploying you will have to make a link to this directory
                   cd /home/nxd/dnld/extjs-2.1-src
                   ln -s adapter/ext/ext-base.js .           
     4. Clone the repository on sourceforge (version control tool is git)
               /media/sda3/home/nxd/test2>git clone git://proghelp.git.sourceforge.net/gitroot/proghelp/proghelp
    5.  Setup the build areas ( these steps might not be necessary as I added dummy files to the directories, but just in case)
    6. Build the compiler - actually I cant really call it one considering how simple it is.
        CustomMakefile is the makefile for doing a build.

          /media/sda3/home/nxd/test2/proghelp>make -f CustomMakefile
         Ignore compile warnings :)
         The last output of the make file is to copy certain files to the output folder - do it.
         cp inputs/CalendarCell.C inputs/CalendarCell.h inputs/TimesheetCalendar.C inputs/TimesheetCalendar.h inputs/output/CppCodeGenerator
     7. Build the sample application.
                  /media/sda3/home/nxd/test3/proghelp>cd inputs
         /media/sda3/home/nxd/test2/proghelp/inputs> ../bin/csassist.exe --database-name proghelpdemo1 emp2.sql

            The generated code is written to output/CppCodeGenerator
            It also creates a directory with the timestamp at which you created the project - and all unified sql scripts are created there.


      8.   setup the sample database we used in step 7. Please note that these commands are most probably not best practices and should not be used in this way in a production environment. You may need to switch to user postgres to run these commands. My postgres runs on port 5433 (because ... )

           8.1 Database creation related stuff
             /media/sda3/home/nxd/test2/proghelp/inputs>createdb -p 5433 proghelpdemo1

            If the plpgsql language is not added to your template1 database then you need to add the language to the database too.
             /media/sda3/home/nxd/test2/proghelp/inputs>createlang  -p 5433  plpgsql proghelpdemo1

           8.2 unified scripts setup

              1. Change to the timestamp directory where the unified scripts reside. The name of the timestamp directory will be output in step 7.
               /media/sda3/home/nxd/test2/proghelp/inputs>cd output/CppCodeGenerator/2010-12-22-13:12:20

             2. run the unified script creator shell scripts.
/media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator/2010-12-22-13:12:20>ls
unified_create_func_sql.sh  unified_create_tables_sql.sh  unified_drop_func_sql.sh  unified_drop_tables.sql  unified_load_rdg_data_sql.sh
/media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator/2010-12-22-13:12:20>sh unified_create_tables_sql.sh
/media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator/2010-12-22-13:12:20>sh unified_create_func_sql.sh
/media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator/2010-12-22-13:12:20>sh unified_load_rdg_data_sql.sh

              3. execute the scripts on the database
                    /media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator/2010-12-22-13:12:20>psql -p 5433 proghelpdemo1
                  a. proghelpdemo1=> \i unified_create_tables.sql
                  b. proghelpdemo1=> \i unified_load_rdg_data.sql
                  step b will have some errors because my Random Data Generator does not take care of foreign keys for master details tables. These can be ingored.
                  c. proghelpdemo1=> \i unified_create_func.sql
                  d. There is one stored procedure that is not automatically added to the unified scripts, you need to load this one manually.
                 
               proghelpdemo1=> \q
         /media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator/2010-12-22-13:12:20>cd ..
         /media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator>psql -p 5433 proghelpdemo1
         proghelpdemo1=> \i sp_User_Login_authenticate_login_postgres.sql
9. Build the generated app
      /media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator>make -f Makefile.emp2.sql -j 8
     ( for -j8: the recommendation is 2ice the number of processes as the number of cores I read somewhere)
10. Deploy web related stuff.
      1. the ext library
                  /media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator>ln -s /home/nxd/dnld/extjs-2.1-src/ ext                            
       2. css
                  cp the not so great default css file - it resides in the "inputs" dir

                  /media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator>cp ../../good1.css .                         

       3. Dummy company logo
                                    /media/sda3/home/nxd/test3/proghelp/inputs/output/CppCodeGenerator>mkdir images
                  /media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator>cp ../../text-company-logo.png images/logo.png


/media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator>cp ../../good1.css .  
     If you did not follow the soft linking step at 3. you should do so now, inside the extjs downloaded dir.

10. Test it

       /media/sda3/home/nxd/test2/proghelp/inputs/output/CppCodeGenerator> ./test_ui --http-address 192.168.1.1 --http-port 8081 --docroot . &

  Point your browser to http://192.168.1.1:8081/ and browse - this should be your nic address , otherwise you can always use 127.0.0.1
     Default logins and passwords are as given in my previous mail.

Any difficulties, please email me - i tried all the steps mentioned here - but something could have gone wrong.

Kind Regards,
Neil