This shows you the differences between two versions of the page.
|
integration:rdbms:mysql:tables [2011/04/06 23:11] 127.0.0.1 external edit |
integration:rdbms:mysql:tables [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Integration: Relational Databases ====== | ||
| - | |||
| - | ===== MySQL ===== | ||
| - | |||
| - | [[integration:rdbms:mysql:home|Overview]] | | ||
| - | [[integration:rdbms:mysql:environments|Environments]] | | ||
| - | [[integration:rdbms:mysql:databases|Databases]] | | ||
| - | **[[integration:rdbms:mysql:tables|Tables]]** | | ||
| - | [[integration:rdbms:mysql:constraints|Constraints]] | | ||
| - | [[integration:rdbms:mysql:datatypes|Datatypes]] | | ||
| - | [[integration:rdbms:mysql:queries|Queries]] | | ||
| - | [[integration:rdbms:mysql:updates|Updates]] | | ||
| - | [[integration:rdbms:mysql:example|Example]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | ==== Tables ==== | ||
| - | |||
| - | === The CREATE TABLE Statement === | ||
| - | |||
| - | The [[dev:sql:statements:create_table:home|CREATE TABLE]] statement is used to declare a MySQL table or view within an Omnidex Environment File. The Omnidex Environment File will contain a declaration for each MySQL table or view to be accessed, and will correlate all of the schema information between Omnidex and MySQL, including object names and datatypes. This statement can either be issued directly, or it can be extracted from MySQL using the EXTRACT statement as discussed in the previous section. | ||
| - | |||
| - | The following example compares the MySQL declarations with the Omnidex declarations. | ||
| - | |||
| - | **MySQL CREATE TABLE Statement** | ||
| - | |||
| - | <code> | ||
| - | create table STATES | ||
| - | (STATE_CD CHAR(2), | ||
| - | DESCRIPTION VARCHAR(31), | ||
| - | STATE_NUM CHAR(2), | ||
| - | REGION_CD CHAR(2), | ||
| - | COUNTRY_CD CHAR(2), | ||
| - | TAX_RATE DOUBLE); | ||
| - | </code> | ||
| - | |||
| - | |||
| - | **Omnidex CREATE TABLE Statement** | ||
| - | <code> | ||
| - | create table "STATES" | ||
| - | physical "STATES" | ||
| - | ( | ||
| - | "STATE" CHARACTER(2) physical "STATE_CD", | ||
| - | "DESCRIPTION" STRING(31), | ||
| - | "STATE_CODE" CHARACTER(2) physical "STATE_NUM", | ||
| - | "REGION" CHARACTER(2) physical "REGION_CD", | ||
| - | "COUNTRY" CHARACTER(2) physical "COUNTRY_CD", | ||
| - | "TAX_RATE" FLOAT | ||
| - | ) | ||
| - | in "simple.xml"; | ||
| - | </code> | ||
| - | |||
| - | In the Omnidex declaration, note that the PHYSICAL clause for table points to the actual MySQL table name. Also note that the columns use the PHYSICAL clause to point to the underlying MySQL column name, and use Omnidex datatypes rather tha MySQL datatypes. | ||
| - | |||
| - | === Modifying Omnidex's View of the MySQL Data Objects === | ||
| - | |||
| - | When Omnidex accesses a table, it only knows about the data objects that are declared in the Omnidex Environment File. It does not have an independent understanding of the MySQL environment. This allows administrators to shape the Omnidex Environment the way they want. Some of the opportunities this provides are: | ||
| - | |||
| - | * Omnidex can have a controlled view of the MySQL database, limited to only the tables and columns that the application requires. | ||
| - | * Omnidex tables can point to MySQL views, allowing an easy approach to reshaping the application's view of the data. | ||
| - | * Omnidex can use different names for tables and columns by using the PHYSICAL clause in the TABLE or COLUMN sections of the CREATE TABLE statement to map to the underlying MySQL names. | ||
| - | * Omnidex tables can include [[admin:features:expressioncols:home|Expression-based Columns]], which are columns derived from a SQL expression rather than a specific column in the underlying database. | ||
| - | |||
| - | === Referencing MySQL Views === | ||
| - | |||
| - | Omnidex table declarations can reference MySQL Views. Omnidex will retrieve from the MySQL view just as though it is a table. MySQL Views can be used to provide different views of the data, appropriate for the application. | ||
| - | |||
| - | When referencing a MySQL view, it is necessary to declare a UNIQUE constraint. This requirement for the UNIQUE constraint is independent of any PRIMARY or FOREIGN constraints, discussed in the next page. The UNIQUE constraint tells Omnidex how to uniquely identify a row. When Omnidex retrieves individual rows, it will use this unique value; therefore, it is important that access to this column (or columns) be properly indexed in the underlying table. | ||
| - | |||
| - | **MySQL CREATE VIEW Statement** | ||
| - | <code> | ||
| - | create view STATES_VIEW | ||
| - | ( | ||
| - | CTRY_DESCRIPTION, | ||
| - | CTRY_CAPITAL, | ||
| - | STATE_CD, | ||
| - | DESCRIPTION, | ||
| - | STATE_NUM, | ||
| - | REGION_CD, | ||
| - | COUNTRY_CD, | ||
| - | TAX_RATE | ||
| - | ) as | ||
| - | select C.DESCRIPTION, | ||
| - | C.CAPITAL, | ||
| - | S.STATE_CD, | ||
| - | S.DESCRIPTION, | ||
| - | S.STATE_NUM, | ||
| - | S.REGION_CD, | ||
| - | S.COUNTRY_CD, | ||
| - | S.TAX_RATE | ||
| - | from SIMPLE.STATES S, SIMPLE.COUNTRIES C | ||
| - | where S.COUNTRY_CD = C.COUNTRY_CD; | ||
| - | </code> | ||
| - | |||
| - | **Omnidex CREATE TABLE Statement** | ||
| - | <code> | ||
| - | create table "STATES_VIEW" | ||
| - | physical "STATES_VIEW" | ||
| - | ( | ||
| - | "CTRY_DESCRIPTION" STRING(47), | ||
| - | "CTRY_CAPITAL" STRING(31), | ||
| - | "STATE" CHARACTER(2) physical "STATE_CD", | ||
| - | "DESCRIPTION" STRING(31), | ||
| - | "STATE_CODE" CHARACTER(2) physical "STATE_NUM", | ||
| - | "REGION" CHARACTER(2) physical "REGION_CD", | ||
| - | "COUNTRY" CHARACTER(2) physical "COUNTRY_CD", | ||
| - | "TAX_RATE" FLOAT, | ||
| - | constraint STATES_STATE_UK unique ("STATE") | ||
| - | ) | ||
| - | in "simple.xml"; | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[integration:rdbms:mysql:databases|Prev]]** | | ||
| - | **[[integration:rdbms:mysql:constraints|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:integration:rdbms:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||