473,322 Members | 1,314 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How should I get started?

I've been programming web applications in PHP/MySQL for a few years.
It's worked decently, but I've been getting annoyed with the lack of
more advanced features lately. After some reading, I've decided to
switch to Perl/PostgreSQL.

I'll be discarding all database data for other reason anyway, so moving
data isn't an issue. I just want to learn how to best use some of
PostgreSQL's cool features. Is there any documentation about that? I've
looked at the official docs, but they're very dry and don't talk much
about migrating from MySQL. Any suggestions?

Nov 22 '05 #1
1 1573
Hi Leif;
----- Original Message -----
From: "Leif K-Brooks" <eu*****@ecritters.biz>
I've been programming web applications in PHP/MySQL for a few years.
It's worked decently, but I've been getting annoyed with the lack of
more advanced features lately. After some reading, I've decided to
switch to Perl/PostgreSQL.
Hmm... I program in Perl, PHP and Python with PostgreSQL as my preferred
backend. One caution I would give you is that Perl and PHP have many
surface similarities which are misleading. I would suggest that you learn
PostgreSQL and Perl separately. BTW, I tend to use PHP for my web apps and
Perl for utilities, but I relialize that different applications may handle
the languages differently. Some people think that PHP is a sort of
Perl-Lite, but I disagree-- they are just too different to compare like
this.

Furthermore, PostgreSQL allows you to approach the database backend as an
active layer in your application rather than a simple light-weight
information store (MySQL). This allows you to write thinner clients and use
the database from a much larger number of clients. A brief set of features
and how they can be used will be included below, with relavent sections of
the documentation.

I am assuming that you are writing about a new project that you will be
starting and have decided to use Perl and PostgreSQL for this project rather
than PHP/MySQL. What I have done in cases like this is do cursory study of
the language involved (Perl) and other technologies and then get started. I
have often had to disregard large sections of code due to bad design in the
past, but this is part of the learning experience, unfortunately. At least
you can find good Perl tutorials on the web. I'll be discarding all database data for other reason anyway, so moving
data isn't an issue. I just want to learn how to best use some of
PostgreSQL's cool features. Is there any documentation about that? I've
looked at the official docs, but they're very dry and don't talk much
about migrating from MySQL. Any suggestions?


The cool features that we are talking about are not available in MySQL, so
there is no migration to be done. I will include with the features a quick
description and section numbers of relavent docs (so that you can find them
on or offline).

1) Subselects allow you to break up a complicated join into a smaller, more
readible query. These allow your code to be easier to maintain.
Documentation is available in sections:
The SQL Language, section 4.2.9. Scaler Subqueries (read the intro to 4.2
for more info as well).
Also see the select entry in the SQL comamnd section of the Reference.

2: Views allow you to present information to an application in ways other
than it is literally stored in the tables. This can be used to partition
tables, provide limited access to portions of the table, and other tricks.
See the following sections of the documentation:
Tutorial , section 3.2: Views
Reference, SQL commands, Create View

3: Rules are used internally to implement views, and can also be used to
allow a view to be updateable, as well as handle inserts and updates. Views
work by expanding the query dynamically and substituting parts of it with
another query.
See: Reference, SQL Commands, Create Rule

4: User defined functions, or Stored proceedures are functions which are
stored in the database and can then be called from queries. These can be
written in SQL or C, or if the appropriate languages are installed, Pl/Pgsql
(SQL with proceedural extensions), Pl/Perl (Perl running inside the database
process), Pl/TCL, Pl/PHP, Pl/Python and many others. These functions can
then form the basis for triggers and rules.
See: Server Programming, Section 33 (lots of material here)
Also reference, SQL language, Create Function page.

5: Triggers are similar in usefulness to rules with a notable exception:
rules are macros which are expanded when triggered by a query to a database
table of view, while triggers allow for functions to be called when a
database row is subject to an insert, update, or delete statement. Triggers
are divided into two categories-- those that activate before the
insert/update/delete statements (and hence can alter the data of the row
subject to the insert/update, but not delete) and those triggered after. In
each category, triggers are fired in alphabetical order. Currently,
PostgreSQL will only trigger user-defined functions written in trigger-safe
proceedural languages, such as PL/PGSQL or C. Triggers are used internally
to impliment foreign key constraints.
See documentation for number 3 above and
Reference/SQL Language/Create Trigger

6: Schemas: MySQL treats databases in a similar manner to schemas in
PostgreSQL. Schemas are logical groups of tables which can be maintained in
relative isolation to eachother. Tables in one schema can be the basis for
views in another. This allows, for example, different applications to have
their own table structure without the actual storage changing.
See Reference/SQL Language/Schemas.

7: information_schema (added in 7.4) allows client applications to
determine quite a bit of information about the current user, database,
schemas, etc. using standard select statements against database tables and
views in the information_schema. See client interfaces, section 32.

8: Domains allow an administrator to manage sets of similar data centrally.
For example, imagine we have 10 fields in the database where a telephone
number is stored. We can create a domain and use it to manage the other 10
fields more appropriately.

9: Inheritance allows tables to inherit characteristics of other abstract
tables. I have not yet found much use for it but perhaps others can provide
insight. Currently uniqueness of primary keys are not enforced across the
entire inheritance tree due to indexing issues. Use at your own risk.

Since you are looking at using Perl, I won't get into extensible types,
except to note that others may be able to actually create additional data
types for you to use, in addition to the rich data types mentioned in SQL
Language, section 8.

Hope this helps.

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Mark Fisher | last post by:
I have a Java desktop GUI application that the user can run multiple times. In order to keep one instance of the application distinct from another, I'd like to put the instance number of the...
13
by: Deepak Sarda | last post by:
Hello everyone. I have run into something which I believe is a bug or a shortcoming of the threading.Thread module. My program spawns 15 threads. For this I've creating a new class with...
80
by: Bibby | last post by:
Hi, I'm interested in getting started in the programming world. I've dabbled in C, C++ and VB6. Which would be the best language to focus my attention to regarding the following considerations: ...
3
by: DAMAR | last post by:
Hello Is it possible to know when a particular process started? For example: I have such situation: I need to know when a notepad.exe is started - i need to get an event of starting this...
0
by: Bob | last post by:
We have an ivr outcall application that starts calling customers at a certain time every day automatically at a predetermined time to advise those expected to receive delivery next day that their...
2
by: bz | last post by:
Hi, I need to detect at runtime if the application was started from VS 2005. Is it possible? Also, how can I know at runtime of app is built for Debug or Release? Thanks
4
by: Benjamin Hell | last post by:
Hi! I wonder whether there might be a way to find out how a Python program was started (in my case in Windows): By double clicking the file or by calling it on the "DOS" command line prompt. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.