|
Tables are database objects that contain all the data in a database.
A table definition is a collection of columns. In tables, data is
organized in a row-and-column format similar to a spreadsheet. Each
row represents a unique record, and each column represents a field
within the record.
A Record is an object that holds one row of a table. There is a
1-to-1 relationship between the ProCG record definition and the
actual database record definition.
ProCG lets you define the record. The table is defined by the definition
of the record. You define what fields you want to hold in memory
for each Record.
ProCG will define the table and the columns-type according to the
database type.
A Structure is a collection of fields that you define for your
application. You use the Structure for passing parameters to the
Access Method and to get values from the result of Access Method.
As you will see in the definition of the Access
Method , most of the parameters for the Access Method will
come from the record fields itself. In some cases, the Record is
not sufficient to hold all the parameters. For example: If you want
to retrieve (read) the ‘State’ record by it's Primary key (Field
ID), you can use the ‘ID’ field in the ‘State’ record to hold the
parameter. However, if you want to get all states that have a population
ranging between two values, you will need to use a structure to
hold these two parameters.
For example, if the Access Method gets calculated fields from a
table, and if we want to use them in our application, we make the
Access Method get these values into the Structure fields.
Top
As a standard, you may define a structure for each table that you
need to pass parameters from or to get values into. This ensures
that the parameters of all Access Methods for one table will be
in one Structure.
Note: ProCG can use any Structure
or Record to pass parameters and get values to and from an Access
Method.
You may define one Structure, which holds all the parameters of
the database. Similarly, you may define one Structure for several
related tables in your database.
Top
This is a table holding the Code and description combinations in
your application. You do not need to define a table for each code
and description combination in your application. The Code can be
numeric or text.
Every row in the Codes table has:
- Code (numeric code)
- StrCode
- Name
- Description
- Order (which has the options in a list box or combo box)
- ExtraInfo (for any extra values that you want to save for this
code)
For the ‘Credit Card Type’ field in the ‘Customer’ table, there
are three options:
- Use a text field for holding the name of the credit card ("Visa",
"Master Card") - this is the worst table design.
- Use a field ‘CreditCardType’ of type INT that will refer to
another table called (for example) ‘CreditCards’.
- Use a field ‘CreditCardType’ of type INT that will refer to
the general Codes table that holds the Code and Description that
you need for the Credit Card. It also holds a ‘TableNum’, under
which all the credit card types would be stored. (The chosen way)
- The Codes table will hold all your code descriptions.
You will get a mechanism to get these values from the cache (memory)
instead of reading them from the database every time.
You will get a simple API to get:
- All Codes for a specific table.
- Get a Name for a specific Code in the TableNum
(For example: get name(CREDIT_CARD_TYPE, Customer.CreditCardType))
- Get a description for a specific Code in ‘TableNum’.
For example: GetDescription (CREDIT_CARD_TYPE, Customer.CreditCardType)
- And more (Filling list box and...)
Top
Indexes in databases let you find records more quickly without
reading all the data.
ProCG enforces you to define a primary key which is a unique key
and must be number 1. A Key can have one or more fields.
ProCG uses the keys for defining the table in your database and
also uses the keys to define Access Methods based on a key or part
of a key. (See Access Methods).
If you have a field type ‘Auto Increment’, it must be the primary
key of the table.
An Access Method is the action that you want to perform against
one or more tables. You can define an Access Method using a key
or a SQL Command.
ProCG uses the SQL command as the basic way for defining the Access
Method. The Access Method can be ANY SQL Command. This means that
you can use ‘joins’ or any proprietary feature of your database.
You can add hints to your SQL Command if you are using Oracle as
your database.
The Results of your Access Method (type Read and Open) will be
in the records you use by default. You can change this default setting
and indicate exactly where you need the values to be transferred
by using the INTO phrase in your SQL Command.
Any Parameter or result that you mention in your Access Method
must be preceded by ":" before the field name.
Example:
SELECT COUNT(*) FROM Player P WHERE P.Salary BETWEEN
:PlayerParams.FromSalary AND :PlayerParams.ToSalary INTO :PlayerParams.PlayersCount
In this Access Method we get the number of players who earn a salary
that ranges between two values. The result (Count) will be in the
Structure PlayerParams.PlayersCount
Note: If you use INTO, you must indicate
for each field in the SELECT Command.
Top
Access Method using Key
In this Access Method, you indicate the Key you want to use, the
number of fields inside the Key and the Relative action you want
to perform. The key fields participate as the parameters to the
access method.
For example: The ‘City’ table has a primary key and contains two
fields, ‘StateId’ and ‘Id’ (CityId). If we define the Access Method
by Key using this key and using one field in the key, and if the
relative action is EQUAL, this means we want to get all Cities where
‘StateId’ is equal to ‘City.StateId’ and order by Id.
The SQL Command that ProCG will create for this Access Method is:
SELECT StateId,Id,Name FROM City WHERE City.StateID
= ? ORDER BY Id
All the parameters for Access Method using Key are from the Record
(key fields).
Top
Access Method using SQL Command
In this Access Method, you write an SQL Command to be used. Parameters
are embedded into to SQL Command using ‘:’ in front of the name
of the field.
For example:
SELECT P.* FROM Player P WHERE P.Salary BETWEEN
:PlayerParams.FromSalary AND :PlayerParams.ToSalary
ORDER BY P.Name
In this Access Method, we want to get all Players that earn a salary
ranging between two values. These values will come from a Structure,
‘PlayerParams’ and the fields are ‘FromSalary’ and ‘ToSalary’. You
must specify exactly where the parameters should come.
Top
- Read by Key
This Access Method defines the way we want to read one and
only one record by a key. For this Access Method, we need to
indicate the key through which we want to read the record (this
key must be unique). There is no need to write an SQL Command
for this Access Method.
- Read By SQL Command
This Access Method defines the way we want to read one and
only one record by an SQL Command. For this Access Method, we
need to write an SQL Command using parameters and we must be
sure that the execution of this SQL Command will get back one
record only.
- Open by Key
This Access Method defines the way we want to get several records
from the database by a Key. For this Access Method, we indicate
the key to use, the number of fields inside that key, and the
Relative action we want to perform.
- Open By SQL Command
This Access Method defines the way we want to get several records
from the database using a SQL Command.
- Delete by Key
This Access Method defines the records we want to delete using
a Key.
For example: If we Delete from the ‘City’ table using the primary
key, one field and Relative Equal will delete all Cities belonging
to City.StateId
- Delete by SQL Command
This Access Method defines the records we want to delete using
SQL Command.
- Update by SQL Command
This Access Method defines the records we want to update using
SQL Command.
Top
- Insert
If you ‘Insert’ one record to the table, the values will come
from that record.
- UpdateCurrent
Update Current record - You must be in a situation where you
already read a record from the database and now you want to
update the record.
If your database supports unique identification of this record
(rowid - Oracle, Identification - SQL Server), then you will
be able to change the Primary key too.
If not, UpdateCurrent will use UpdateByPrimaryKey.
- UpdateByPrimaryKey
UpdateByPrimaryKey lets you update one record value by primary
key values. You do not need to read the record before.
- DeleteCurrent
DeleteCurrent - You must be in a situation where you already
read a record from the database and now you want to delete the
record.
If your database supports unique identification of this record
(rowid - Oracle, Identification - SQL Server), then the delete
action would be done by the unique identification value.
If not, DeleteCurrent will use DeleteByPrimaryKey.
- DeleteByPrimaryKey
DeleteByPrimaryKey lets you delete one record from the database
by primary key values. You do not need to have read the record
before.
You can use in your program all Access Methods returning more than
one record, in several ways:
- Open(), Fetch(), Close() - Work with one record each time.
- Load(), LoadNext() - Work with several or all records in one
round trip to the database server.
- LoadFromPosition() - Load x records starting with 'position'.
Top
A Relationship is the definition of access method on a table that
has a relation with the current table.
In order to execute this access method, there must be a movement
of one or more fields from the current table to the other table,
and only then we can execute the access method on the other table.
The relationship is not only the definition of the foreign keys
of the table.
For example:
In our sample application, Sport, we have a table of states and
a table of cities. State has cities and city belong to state.
The ‘State’ table has a Unique Index containing the ‘Id’ field.
‘State’ has an access method of "State" which reads the ‘State’
table record by this index. The parameter for this access method
is the ‘Id’ from the state record.
The ‘City’ table has a unique index containing the ‘StateId’ and
‘CityId’ fields. It also has two access methods:
- City - which reads the ‘City’ table’s records by the primary
index. The parameters are ‘StateId’ and ‘Id’ from the ‘City’ table’s
record.
- StateCities - which can get all the cities, belong to a state.
The parameter for this access method is StateId from the ‘City’
table’s record.
If we have a State record as the current record, the record is
in the memory (in your program) with all its fields, then, in order
to get all the cities that are in 'this' State, we need to move
the ‘Id’ from the ‘State’ table’s record to the ‘StateId’ in the
‘City’ table’s record and then execute the access method "StateCities".
Note that in a relationship, we can execute any access method that
the second table has. It is not limited to the primary key usage.
We can also define the backward relationship. Consider the following
situation: we have a ‘City’ table’s record as the current record
and we would like to get its ‘State’ record (i.e. the state which
this city belongs to). To perform this action, we have to move the
‘StateId’ from the ‘City’ record to the ‘Id’ of ‘State’ record and
then execute the access method "State", which reads the ‘State’
record by the ‘Id’ in ‘State’ record.
Let us see how these relations are defined: It is bi-directional
relationship between the ‘State’ table and the ‘City’ table. This
is a 'One to Many' relationship (one state has several cities).
If we move ‘Id’ field from the ‘State’ to the ‘StateId’ field in
the ‘City’ table, and then execute the "StateCities" access method,
we would get the cities of the state and the name of the result
is "Cities".
Similarly, if we move the ‘StateId’ field in the ‘City’ table to
the ‘Id’ field in the ‘State’ to the table, and then execute the
"State" access method, we would get the state where this city resides,
and the result is “State”.
Now let see how this relationship is defined:
|