View table definitions
In the directory orcas\examples\orderentry\db\tabellen you can find the previous table definitions in “Orcas syntax”. To find out how something has to be described in ORCAS, you can either read the table syntax, or see things you seek via reverse engineering from an existing database actually.
Here as an example the definition of the table items
Add a column
We want to add only one column currency, but we are expanding the table definition simply to the appropriate line:
In order to synchronize the schema again and to see the new column in the database, you have to connect you back to the Vagrant VM
There switch to the directory /orcas/orcas/examples/orderenty/db, where the build.xml is and run again
Now you should see in the table *items the new column.
Remove a column (no data)
Also you can remove the column
again from the definition. After another run of ant the column is gone.
Remove a column (with data)
Now let the example rerun slightly modified, so initially the column is added again:
Now we want to add the table with one record. There’s a foreign key for categories, we must first enter a record here.
The insertion of data is done manually by corresponding insert statements or database tool.
Next, use the column again from the definition and rerun ant .
Now the following message appears on the console:
As soon a the data get lost through a drop, this is not performed. It is determined by the build.xml:
In the target build_tables is the dropmode=false, we set it to true, save and run again ant. This time the build is successful again:
and the column is no longer available. As a precaution, we put the parameters in build.xml now back again ;). The topic ant tasks follows in more detail in part 2 of the training.
Add new table
Exercise: A business partner can have several addresses
Now add a new table ADDRESSES that accepts the address line of BUSINESS_PARTNERS and is referenced by a foreign key from BUSINESS_PARTNERS.
For the exercises respectively the generated SQL is to be observed, especially if “drop and create” or “alter” is used.
- Expansion of item_uc the column ctgr_id.
- Set item_ctgr_fk to “on delete cascade”.
- Set the data type from description to varchar2(500)
- no data
- with data that fit (not longer than 500)
- with data that does not fit (longer than 500)
- Set the data type from description to varchar2(2000)
- You want to create an index on the column price and version
- An index on upper(name) should be created.