PostgreSqlProg Server Information - MakeProg

footmouthΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 7 μήνες)

161 εμφανίσεις

PostgreSql
Prog

Server Information


1.

Prerequisites

Before installing
PostgreSql
Prog

Server it is necessary to install the
following compon
ents. If they are already installed you can skip to next
step.


a)

Full
Microsoft

.net Framework 4.0. Download from


Downlo
ad

.Net Framework 4.0


b)

Sql Server

Compact Database 4.0
.


Download
Sql Server

Compact 4.0 Database



2.

PostgreSql
Prog

Server comes

in

two versions. Either you can
install
PostgreSql
Prog

Server as standalone windows service or
PostgreSql
Prog

Server
can be hosted in IIS. Based on your choice you need

to

use the specific setup
program.


3.

PostgreSql
Prog

Server can be hosted with http or
secure http (https)
.


4.

If you are connecting to PostgreSQL Database version greater than 8.3 it is
necessary to

create the
following ps
_get_tabledef function in the database. Copy
following code into pgAdmin Sql Window and create the function.


Function Code

CREATE OR REPLACE Function pg_catalog. pg_get_tabledef(text) RETURNS text

AS

$$


DECLARE


tabledef

TEXT;


dotpos integer;


tablename text;


schemaname text;


prevcol text;


coltype text;


notnull1 boolean;


rec record;


oidcheck boolean;


BEGIN


dotpos:=strpos($1,'.');


if dotpos = 0 then


schemaname:='public';


tablename:=substr($1,dotpos+1);


else


schemaname:=substr($1,1,dotpos
-
1);


tablename:=substr($1,dotpos+1);


end if;


select relhasoids into oidcheck from pg_class,pg_namespace where
pg_class.relnamespace=pg_namespace.oid and pg_namespace.n
spname=schemaname and
pg_class.relname=tablename and pg_class.relkind='r';


if not found then


tabledef:='Table Does not exists!';


return tabledef;


end if;


tabledef:= 'CREATE TABLE '|| schemaname||'.'||tablename;


for rec in SELECT
a.attname as columnname ,pg_catalog.format_type(a.atttypid, a.atttypmod) as
coltype, (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM
pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
a.atthasdef),a.a
ttnotnull as notnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid =
(select pg_class.oid from pg_class,pg_namespace where relname=tablename and
pg_class.relnamespace=pg_namespace.oid and pg_namespace.nspname=schemaname) AND
a.attnum > 0 AND NO
T a.attisdropped ORDER BY a.attnum


loop


if prevcol is null then


tabledef:=tabledef||' (';


prevcol:=rec.columnname;


coltype:=rec.coltype;


notnull1:=rec.notnull;


elsif notnull1 then


tabledef:=tabledef||' '||E
'
\
n'||prevcol||' '||coltype||' NOT NULL ,';


prevcol:=rec.columnname;


coltype:=rec.coltype;


notnull1:=rec.notnull;


else


tabledef:=tabledef||' '||E'
\
n'||prevcol||' '||coltype||' ,';


prevcol:=rec.columnname;



coltype:=rec.coltype;


notnull1:=rec.notnull;


end if;


end loop;


if oidcheck = true and notnull1 = true then


tabledef:=tabledef||E'
\
n'||prevcol||' '||coltype||' NOT NULL ) WITH OIDS;';


elsif oidcheck = true and notnull1
= false then


tabledef:=tabledef||E'
\
n'||prevcol||' '||coltype||' NOT NULL ) WITH OIDS;';


elsif oidcheck=false and notnull1=true then


tabledef:=tabledef||E'
\
n'|| prevcol||' '||coltype||' NOT NULL ) WITHOUT OIDS;';


else


ta
bledef:=tabledef||E'
\
n'||prevcol||' '||coltype||' ) WITHOUT OIDS;';


end if;




return tabledef;


end;

$$ language plpgsql;



PostgreSql
Prog

Server


Windows Service Version


1.

Install the
latest
PostgreSql
Prog

S
erver in a windows machine

following the
instructions in the setup
. Supported Windows
platform

are Windows XP, Vista,
Seven, Windows
2003 server

and Windows 2008 server
. 32 and 64 bit
installation kits are separately available for download from
PostgreSql
Prog

Server


2.

PostgreSql
Prog

server
will install

as windows service
called “
PostgreSql
Prog

S
erver” and listens on port
31000

and
31001
. So these ports should be opened in
Windows Firewall.


3.

The default ports can be changed in the config
file

MakeProg.
PostgreSql
Prog
.exe.config”. Stop the
PostgreSql
Prog

Service in
Windows Service Manager Console and change the parameter

ServicePort


in
the configuration file. After saving the changes restart
the service. Please note
that the
PostgreSql
Prog

Server will
use the

port specified in the configuration
and plus

one more port. For
e.g.

If you specify 10000 as a service port
PostgreSql
Prog

Server will use ports 10000 and 10001.


4.

If
PostgreSql
Prog

Server

faces any issue while running it will be logged into a log
file. The log file location is {InstallationDirectory}
\
logs
\
error.log. The log file
will be deleted and r
ecreated when the file size is 5
MB.

In case if it
is
not able to
write to log file for an
y reason, it will be logged in the event viewer.


5.

PostgreSql
Prog

Server is a supporting application for i
PostgreSql
Prog
. It is
licensed to use along with
i
PostgreSql
Prog

only
.

PostgreSql
Prog

Server can only
be installed on one machine to act as a
bridge
for

PostgreSql

and
i
PostgreSql
Prog
.

If you need to install on more than one machine and support
more than one
PostgreSql

Server

please contact us for further details about
licensing.



6.

By default
PostgreSql
Prog

Server service uses localsys
tem account. You
can
change it to a user with least privilege. When you change the service user and
restart the service please verify error log file. If you find a message saying access
denied it is necessary to reserve the
PostgreSql
Prog

Server http or https Ports to
the

service user. If you are running as a localsystem user
,

the user
should have
access for listening
http request on ports. But the least privileged user may not
have access for listening http request. So it is necessary

to

grant access for the
ports. For more information on granting access to ports please see
Configuring
HTTP and HTTPS
. By default you need
to give access to two ports
31000

and
31001
. Sa
mple command



nets
h http add urlacl url=http://+:
31000
/ user=DOMAIN
\
user


netsh http add urlacl url=http://+:
31001
/ user=DOMAIN
\
user


For https please replace “http” with https


7.


If you would like to make
PostgreSql
Prog

Server to run on secure http it is

necessary to bind
ssl

certificates to
PostgreSql
Prog

Server ports.
PostgreSql
Prog

Server comes with backoffice tool called (backoffice.exe). It is installed in the
installation directory. You can run the tool and get help on that. Steps to bind ssl
certi
ficate to
PostgreSql
Prog

Server


Firstly, you need
to
add the certificate to
PostgreSql
Prog

Server. To do that make
sure you have the ssl certificate file path. To add a certificate
,

go to installation
directory in a command prompt and run


backoffice ss
lcert add [certificatename] [certificatefilepath] [password]


[certificatename ]


Any

name for your reference

[cerftificatefilepath]


Full

path of the certificate file

[password]


Password

for the certificate in case if you have one.


By running this th
e certificate will be added to the
PostgreSql
Prog

Server
Database.


Next, bind the certificates to the port. To do that you need

to

run


backoffice sslcert bindcommand [certificatename]


This command will create two command batch files in t
he installation

directory
named

Bi
ndSSLtoPort.bat
. This is sample batch binds the
specified certificate to
ports
31000

and
31001

by default. If you have changes your ports modify the
batch file with the new ports and run the same.


Next, SSL has to
be
enable
d

in the configuration file. By Default it is disabled. To
enable open the config file MakePro
g.
PostgreSql
Prog
.exe.config
in the
installation directory with a text
editor

and change the values of the following
keys and save the file.



<add key="SSL" val
ue="true"/>


<add key="CertificateName" value="[certificatename]"/>


Now restart the service and now
PostgreSql
Prog

Server Communicate with ios
devices securely.





PostgreSql
Prog

Server


IIS Hosting


1.

Install the latest
PostgreSql
Prog

Server in a wind
ows machine following the
instructions in the setup. Supported Windows platform are Windows XP, Vista,
Seven, Windows 2003 server and Windows 2008 server. 32 and 64 bit
installation kits are separately available for download from
PostgreSql
Prog

Server,


2.

You can configure the
PostgreSql
Prog

Server Application in IIS to run on secure
http.


3.

Please note down the virtual directory/application name while installing
PostgreSql
Prog

Server IIS Version.



4.

This IIS
application should

be set to run on asp.net 4
application pool.


5.

The IIS

ASP.NET

worker
process user

must have write access to
PostgreSql
Prog

Installation

directory
.




Device Base Security


1.

You can restrict the access
of
PostgreSql
Prog

Server based on UUID of the client
iOS device. To implement
that, first

you need
to
enable it
in the

config file
MakeProg.
PostgreSql
Prog
.exe.config or web.config for IIS Version. Change the
value of the key
ValidateDeviceId

to true.


<add key="
ValidateDeviceId" value="true"/>


2.

Next find the client device UUID by connecting the client device to ITunes add
the UUID to
PostgreSql
Prog

Server by using backoffice.exe



Query Timeout


1.

When a query is executed at the server the time limit for Query Comp
letion is
determined by the configuration key “QueryTimeOut” in the configuration file.
(.config file has this value). By default it is set to 60 Seconds. You can increase
the timeout values according
to your

requirements.


Troubleshooting


1.

To verify that

whether the
PostgreSql
Prog

S
erver is installed correctly, open

your browser and go to the web address


For Windows Version


http(s)://machine:port/

Service/
Bridge
.svc/GetVersion



For IIS Version


http(s)://machine:port/[VirtDir]/

Service/

Bridge
.svc
/
GetVersion


You will get
an

atom feed saying the current version of the
PostgreSql
Prog

Server.


If you did not get the expected result try to identify the problems by repeating
the steps explained in this document or contact us for further help.












i
PostgreSql
Prog

Information


1.

To get started with i
PostgreSql
Prog

it is necessary to add the
PostgreSql

Server

and
PostgreSql
Prog

Server information






Column

Description

DataSource

PostgreSql

Server name or ip address



User id

Login User for
PostgreSql

Password

Password for
PostgreSql

Port

PostgreSql

Server port

by default 5432

PostgreSql
Prog

Server

Windows

Service
Ver
sion : http(s)://[machine]:[port]


IIS
Version:

path of the virtual application (ex.
http(s)://
[
machine:port
]
/
[virtual dir
].

Note:
PostgreSql
Prog

Server should be installed in a virtual
directory and not in the root of the website


Timeout

Maximum time in seconds to get result from
PostgreSql
Prog

server. By default it is 30 seconds




2.

The
Following table

explains what ar
e
all
the buttons used in i
PostgreSql
Prog

and what they do



Open a Query window to send
Sql

scripts to
PostgreSql


Generate script for the selected
PostgreSql

object and it can be

edited in
query window


Display all columns


Send Mail


Edit
currently selected item


Add New Item


Delete the currently selected item

!

This is to execute the query in the query window.


Go to Server screen


3.

Since iOS devices are low memory devices
,

bringing large chunk of data from
PostgreSql

Server

will lead to memory issues. So please make sure that you only
retrieve small set of records.