Sunday, September 30, 2012

Scrum project management in OpenERP

OpenERP allows you to manage your projects by using scrum methodology: projects with sprints, product owner, scrum master, product backlog, scrum meeting, etc.

Here’s some screenshots

image

List of product backlogs/features

image

Product backlog detail: each backlog/feature belongs to a sprint, and contains many tasks

image

Sprint detail

image

Scrum meeting

image

Scrum dashboard

image

For database perspective, you can consider the following tables

image

Agile: A good introduction

Saturday, September 29, 2012

Stored procedure: why use & why not use

Use Stored Procedure
● Reusability
– Avoid rewriting sub queries and improve readability.
– If you can't store a query in a library that all the applications can access, you can put that query in a stored procedure.
● Separation of duties
– You don't trust non-DBA's to write queries.
● Data integrity
– Use triggers or constraints to prevent bad data from entering.
– Run several interdependent queries in a transaction in a single stored procedure.
● Event handling
– Log changes.
– Notify other systems of new data.

Not Use Stored Procedure
● Views may be all you need.
● An object-relational mapping (ORM) can help write queries safely.
● Difficult to version control stored procedures.
● Software rollouts may require more db changes.
● Could slow software development process.

Inspecting your postgreSQL connection information

If you want to confirm you've connected to the right place and in the right way, you can execute some or all of the following commands:

SELECT inet_server_port();  
SELECT current_database();  
SELECT current_user;  
SELECT inet_server_addr();  
SELECT version();

Backup/Restore postgresSQL database with pgAdmin

BACKUP

1. Choose database need to backup

image

2. Save to backup file, choose COMPRESS as default

image

The GUI actually calls pg_dump command to do the backup

image

Choose Done to complete. You can see the backup file in selected folder

The backup as COMPRESS, so it cannot be read with text editor

image

If you want the backup is readable, you can choose backup type is PLAIN

image

image

 

RESTORE

1. Create an empty database

2. Choose the created database, and choose Restore

image

3. Select the backup file

image

The GUI actually calls pg_restore command to do the restore

image

4. Click OK to get the task execute. Database is restored to a new one.

Friday, September 28, 2012

Join quiz 1

image
Look at the above picture and choose the correct answer:
A. SELECT * FROM TableA t1 INNER JOIN TableB t2 ON t1.ID = t2.ID
B. SELECT * FROM TableA t1 LEFT JOIN TableB t2 ON t1.ID = t2.ID
C. SELECT * FROM TableA t1 RIGHT JOIN TableB t2 ON t1.ID = t2.ID
D. SELECT * FROM TableA t1, TableB t2 WHERE t1.ID = t2.ID
E. Both A and D
F. None of the above

Answers: [E]


Psql Help in PostgreSQL



My bat files to start/stop OpenERP services

Start OpenERP

@PATH=%WINDIR%\system32;%WINDIR%;%WINDIR%\System32\Wbem;.
@net start openerp-server-6.0
@net start openerp-web-6.0
@net start postgresql-9.0
pause

 

Stop OpenERP

@PATH=%WINDIR%\system32;%WINDIR%;%WINDIR%\System32\Wbem;.
@net stop openerp-server-6.0
@net stop openerp-web-6.0
@net stop postgresql-9.0
pause

Sunday, September 23, 2012

Install OpenERP step by step

After installing server, web client, and GTK client

image

Open OpenERP Client, no database installed at this time

image

Create new database

image

image

image

Choose Extended for more options

image

For my research purpose, I installed everything

image

Follow the next steps in the wizard, you can finish the installation. Enjoy!

Saturday, September 15, 2012

Get rows without duplicate values

CREATE TABLE T1(a INT,b INT,c INT)
INSERT INTO T1(a,b,c)
SELECT 1,2,3
UNION
SELECT 2,2,4
UNION 
SELECT 3,3,3
UNION
SELECT 4,3,5
 
SELECT *
FROM T1
WHERE a NOT IN (SELECT MAX(a)
                FROM T1
                GROUP BY b)

Oracle Query