| | | | |
| Offset 31, 33 lines modified | Offset 31, 33 lines modified |
| 31 | ··this·place·is·organized·into·tables,·each·of·which·contains·a·number | 31 | ··this·place·is·organized·into·tables,·each·of·which·contains·a·number |
| 32 | ··of·fields.·A·row·in·a·table·represents·one·object.·The·set·of·tables | 32 | ··of·fields.·A·row·in·a·table·represents·one·object.·The·set·of·tables |
| 33 | ··and·their·fields·is·called·the·**schema**·of·the·database. | 33 | ··and·their·fields·is·called·the·**schema**·of·the·database. |
| | |
| 34 | Traditionally,·writing·the·SQL·queries·is·done·inline:·special·markers | 34 | Traditionally,·writing·the·SQL·queries·is·done·inline:·special·markers |
| 35 | are·inserted·into·your·code·to·delimit·sections·that·contain·SQL·code | 35 | are·inserted·into·your·code·to·delimit·sections·that·contain·SQL·code |
| 36 | (as·opposed·to·Ada·code),·and·these·are·then·preprocessed·to·generate | 36 | (as·opposed·to·Ada·code),·and·these·are·then·preprocessed·to·generate |
| 37 | actual·code.·This·isn’t·the·approach·chosen·in·GNATColl:·there·are | 37 | actual·code.·This·isn't·the·approach·chosen·in·GNATColl:·there·are |
| 38 | several·drawbacks,·in·particular·your·code·is·no·longer·Ada·and | 38 | several·drawbacks,·in·particular·your·code·is·no·longer·Ada·and |
| 39 | various·tools·will·choke·on·it. | 39 | various·tools·will·choke·on·it. |
| | |
| 40 | The·other·usual·approach·is·to·write·the·queries·as·strings,·which·are | 40 | The·other·usual·approach·is·to·write·the·queries·as·strings,·which·are |
| 41 | passed,·via·a·DBMS-specific·API,·to·the·DBMS·server.·This·approach·is | 41 | passed,·via·a·DBMS-specific·API,·to·the·DBMS·server.·This·approach·is |
| 42 | very·fragile: | 42 | very·fragile: |
| | |
| 43 | *·The·string·might·not·contain·**well-formed**·SQL.·This·will | 43 | *·The·string·might·not·contain·**well-formed**·SQL.·This·will |
| 44 | ··unfortunately·only·be·detected·at·run·time·when·the·DBMS·complains. | 44 | ··unfortunately·only·be·detected·at·run·time·when·the·DBMS·complains. |
| | |
| 45 | *·This·is·not·**type·safe**.·You·might·be·comparing·a·text·field·with | 45 | *·This·is·not·**type·safe**.·You·might·be·comparing·a·text·field·with |
| 46 | ··an·integer,·for·instance.·In·some·cases,·the·DBMS·will·accept·that | 46 | ··an·integer,·for·instance.·In·some·cases,·the·DBMS·will·accept·that |
| 47 | ··(sqlite·for·instance),·but·in·some·other·cases·it·won’t | 47 | ··(sqlite·for·instance),·but·in·some·other·cases·it·won't |
| 48 | ··(PostgreSQL).·The·result·might·then·either·raise·an·error,·or·return | 48 | ··(PostgreSQL).·The·result·might·then·either·raise·an·error,·or·return |
| 49 | ··an·empty·list. | 49 | ··an·empty·list. |
| | |
| 50 | *·There·is·a·risk·of·**SQL·injection**.·Assuming·the·string·is | 50 | *·There·is·a·risk·of·**SQL·injection**.·Assuming·the·string·is |
| 51 | ··constructed·dynamically·(using·Ada’s·*&*·operator),·it·might·be·easy | 51 | ··constructed·dynamically·(using·Ada's·*&*·operator),·it·might·be·easy |
| 52 | ··for·a·user·to·pass·a·string·that·breaks·the·query,·and·even·destroys | 52 | ··for·a·user·to·pass·a·string·that·breaks·the·query,·and·even·destroys |
| 53 | ··things·in·the·database. | 53 | ··things·in·the·database. |
| | |
| 54 | *·As·discussed·previously,·the·SQL·code·might·not·be·**portable** | 54 | *·As·discussed·previously,·the·SQL·code·might·not·be·**portable** |
| 55 | ··across·DBMS.·For·instance,·creating·an·automatically·increment | 55 | ··across·DBMS.·For·instance,·creating·an·automatically·increment |
| 56 | ··integer·primary·key·in·a·table·is·DBMS·specific. | 56 | ··integer·primary·key·in·a·table·is·DBMS·specific. |
| | |
| Offset 158, 15 lines modified | Offset 158, 15 lines modified |
| 158 | 1.2.·Database·example | 158 | 1.2.·Database·example |
| 159 | ===================== | 159 | ===================== |
| | |
| 160 | This·section·describes·an·example·that·will·be·extended·throughout | 160 | This·section·describes·an·example·that·will·be·extended·throughout |
| 161 | this·chapter.·We·will·build·an·application·that·represents·a·library. | 161 | this·chapter.·We·will·build·an·application·that·represents·a·library. |
| 162 | Such·a·library·contains·various·media·(books·and·DVDs·for·instance), | 162 | Such·a·library·contains·various·media·(books·and·DVDs·for·instance), |
| 163 | and·customers.·A·customer·can·borrow·multiple·media·at·the·same·time, | 163 | and·customers.·A·customer·can·borrow·multiple·media·at·the·same·time, |
| 164 | but·a·media·is·either·at·a·customer’s,·or·still·in·the·library. | 164 | but·a·media·is·either·at·a·customer's,·or·still·in·the·library. |
| | |
| 165 | The·GNATColl·distribution·includes·an·example·directory·which·contains | 165 | The·GNATColl·distribution·includes·an·example·directory·which·contains |
| 166 | all·the·code·and·data·for·this·example. | 166 | all·the·code·and·data·for·this·example. |
| | |
| | |
| 167 | 1.3.·Database·schema | 167 | 1.3.·Database·schema |
| 168 | ==================== | 168 | ==================== |
| Offset 182, 15 lines modified | Offset 182, 15 lines modified |
| 182 | fully·described·later·(The·gnatcoll_db2ada·tool).·However,·the·input | 182 | fully·described·later·(The·gnatcoll_db2ada·tool).·However,·the·input |
| 183 | is·always·the·same:·this·is·the·schema·of·your·database,·that·is·the | 183 | is·always·the·same:·this·is·the·schema·of·your·database,·that·is·the |
| 184 | list·of·tables·and·fields·that·make·up·your·database.·There·exist·two | 184 | list·of·tables·and·fields·that·make·up·your·database.·There·exist·two |
| 185 | ways·to·provide·that·information: | 185 | ways·to·provide·that·information: |
| | |
| 186 | *·From·a·running·database | 186 | *·From·a·running·database |
| | |
| 187 | ··If·you·pass·the·DBMS·vendor·(postgresql,·sqlite,…)·and·the | 187 | ··If·you·pass·the·DBMS·vendor·(postgresql,·sqlite,...)·and·the |
| 188 | ··connection·parameters·to·*gnatcoll_db2ada*,·it·is·able·to·query·the | 188 | ··connection·parameters·to·*gnatcoll_db2ada*,·it·is·able·to·query·the |
| 189 | ··schema·on·its·own.·However,·this·should·not·be·the·preferred·method: | 189 | ··schema·on·its·own.·However,·this·should·not·be·the·preferred·method: |
| 190 | ··this·is·similar·to·reverse·engineering·assembly·code·into·the | 190 | ··this·is·similar·to·reverse·engineering·assembly·code·into·the |
| 191 | ··original·high-level·code,·and·some·semantic·information·will·be | 191 | ··original·high-level·code,·and·some·semantic·information·will·be |
| 192 | ··missing.·For·instance,·in·SQL·we·have·to·create·tables·just·to | 192 | ··missing.·For·instance,·in·SQL·we·have·to·create·tables·just·to |
| 193 | ··represent·the·many-to-many·relationships.·These·extra·tables·are | 193 | ··represent·the·many-to-many·relationships.·These·extra·tables·are |
| 194 | ··part·of·the·implementation·of·the·schema,·but·are·just·noise·when·it | 194 | ··part·of·the·implementation·of·the·schema,·but·are·just·noise·when·it |
| Offset 208, 124 lines modified | Offset 208, 124 lines modified |
| 208 | ··below·and·the·ORM). | 208 | ··below·and·the·ORM). |
| | |
| 209 | ··The·most·convenient·editor·for·this·file·is·Emacs,·using·the·*org- | 209 | ··The·most·convenient·editor·for·this·file·is·Emacs,·using·the·*org- |
| 210 | ··mode*·which·provides·convenient·key·shortcuts·for·editing·the | 210 | ··mode*·which·provides·convenient·key·shortcuts·for·editing·the |
| 211 | ··contents·of·ASCII·tables.·But·any·text·editor·will·do,·and·you·do | 211 | ··contents·of·ASCII·tables.·But·any·text·editor·will·do,·and·you·do |
| 212 | ··not·need·to·align·the·columns·in·this·file. | 212 | ··not·need·to·align·the·columns·in·this·file. |
| | |
| 213 | ··All·lines·starting·with·a·hash·sign·(‘#’)·will·be·ignored. | 213 | ··All·lines·starting·with·a·hash·sign·('#')·will·be·ignored. |
| | |
| 214 | ··This·file·is·a·collection·of·ASCII·tables,·each·of·which·relates·to | 214 | ··This·file·is·a·collection·of·ASCII·tables,·each·of·which·relates·to |
| 215 | ··one·table·or·one·SQL·view·in·your·database.·The·paragraphs·start | 215 | ··one·table·or·one·SQL·view·in·your·database.·The·paragraphs·start |
| 216 | ··with·a·line·containing: | 216 | ··with·a·line·containing: |
| | |
| 217 | ·····table·::= | 217 | ·····table·::= |
| 218 | ········'|'·('ABSTRACT')?·('TABLE'|'VIEW')·['('·supertable·')'] | 218 | ········'|'·('ABSTRACT')?·('TABLE'|'VIEW')·['('·supertable·')'] |
| 219 | ········'|'·<name>·'|'·<name_row> | 219 | ········'|'·<name>·'|'·<name_row> |
| | |
| 220 | ··“name”·is·the·name·of·the·table.·The·third·pipe·and·third·column·are | 220 | ··"name"·is·the·name·of·the·table.·The·third·pipe·and·third·column·are |
| 221 | ··optional,·and·should·be·used·to·specify·the·name·for·the·element | 221 | ··optional,·and·should·be·used·to·specify·the·name·for·the·element |
| 222 | ··represented·by·a·single·row.·For·instance,·if·the·table·is·called | 222 | ··represented·by·a·single·row.·For·instance,·if·the·table·is·called |
| 223 | ··“books”,·the·third·column·could·contain·“book”.·This·is·used·when | 223 | ··"books",·the·third·column·could·contain·"book".·This·is·used·when |
| 224 | ··generating·objects·for·use·with·*GNATCOLL.SQL.ORM*. | 224 | ··generating·objects·for·use·with·*GNATCOLL.SQL.ORM*. |
| | |
| 225 | ··If·the·first·line·starts·with·the·keyword·*ABSTRACT*,·then·no | 225 | ··If·the·first·line·starts·with·the·keyword·*ABSTRACT*,·then·no |
| 226 | ··instance·of·that·table·actually·exists·in·the·database.·This·is·used | 226 | ··instance·of·that·table·actually·exists·in·the·database.·This·is·used |
| 227 | ··in·the·context·of·table·inheritance,·so·define·shared·fields·only | 227 | ··in·the·context·of·table·inheritance,·so·define·shared·fields·only |
| 228 | ··once·among·multiple·tables. | 228 | ··once·among·multiple·tables. |
| | |
| 229 | ··The·keyword·*TABLE*·can·be·followed·by·the·name·of·a·table·from | 229 | ··The·keyword·*TABLE*·can·be·followed·by·the·name·of·a·table·from |
| 230 | ··which·it·inherits·the·fields.·Currently,·that·supertable·must·be | 230 | ··which·it·inherits·the·fields.·Currently,·that·supertable·must·be |
| 231 | ··abstract,·and·the·fields·declared·in·that·table·are·simply | 231 | ··abstract,·and·the·fields·declared·in·that·table·are·simply |
| 232 | ··duplicated·in·the·new·table. | 232 | ··duplicated·in·the·new·table. |
| | |
| 233 | ··Following·the·declaration·of·the·table,·the·file·then·describe·their | 233 | ··Following·the·declaration·of·the·table,·the·file·then·describe·their |
| 234 | ··fields,·each·on·a·separate·line.·Each·of·these·lines·must·start·with | 234 | ··fields,·each·on·a·separate·line.·Each·of·these·lines·must·start·with |
| 235 | ··a·pipe·character·(“|”),·and·contain·a·number·of·pipe-separated | 235 | ··a·pipe·character·("|"),·and·contain·a·number·of·pipe-separated |
| 236 | ··fields.·The·order·of·the·fields·is·always·given·by·the·following | 236 | ··fields.·The·order·of·the·fields·is·always·given·by·the·following |
| 237 | ··grammar: | 237 | ··grammar: |
| | |
| 238 | ·····fields·::= | 238 | ·····fields·::= |
| 239 | ········'|'·<name>·'|'·<type> | 239 | ········'|'·<name>·'|'·<type> |
| 240 | ········'|'·('PK'|''|'NULL'|'NOT·NULL'|'INDEX'|'UNIQUE'|'NOCASE') | 240 | ········'|'·('PK'|''|'NULL'|'NOT·NULL'|'INDEX'|'UNIQUE'|'NOCASE') |
| 241 | ········'|'·[default]·'|'·[doc]·'|' | 241 | ········'|'·[default]·'|'·[doc]·'|' |
| | |
| 242 | ··The·type·of·the·field·is·the·SQL·type·(“INTEGER”,·“TEXT”, | 242 | ··The·type·of·the·field·is·the·SQL·type·("INTEGER",·"TEXT", |
| 243 | ··“TIMESTAMP”,·“DATE”,·“DOUBLE·PRECISION”,·“MONEY”,·“BOOLEAN”,·“TIME”, | 243 | ··"TIMESTAMP",·"DATE",·"DOUBLE·PRECISION",·"MONEY",·"BOOLEAN",·"TIME", |
| 244 | ··“CHARACTER(1)”).·Any·maximal·length·can·be·specified·for·strings, | 244 | ··"CHARACTER(1)").·Any·maximal·length·can·be·specified·for·strings, |
| 245 | ··not·just·1·as·in·this·example.·The·tool·will·automatically·convert | 245 | ··not·just·1·as·in·this·example.·The·tool·will·automatically·convert |
| 246 | ··these·to·Ada·when·generating·Ada·code.·A·special·type | 246 | ··these·to·Ada·when·generating·Ada·code.·A·special·type |
| 247 | ··(“AUTOINCREMENT”)·is·an·integer·that·is·automatically·incremented | 247 | ··("AUTOINCREMENT")·is·an·integer·that·is·automatically·incremented |
| 248 | ··according·to·available·ids·in·the·table.·The·exact·type·used·will | 248 | ··according·to·available·ids·in·the·table.·The·exact·type·used·will |
| 249 | ··depend·on·the·specific·DBMS. | 249 | ··depend·on·the·specific·DBMS. |
| | |
| 250 | ··The·property·‘NOCASE’·indicates·that·comparison·should·be·case | 250 | ··The·property·'NOCASE'·indicates·that·comparison·should·be·case |
| 251 | ··insensitive·for·this·field. | 251 | ··insensitive·for·this·field. |
| | |
| 252 | ··If·the·field·is·a·foreign·key·(that·is·a·value·that·must·correspond | 252 | ··If·the·field·is·a·foreign·key·(that·is·a·value·that·must·correspond |
| 253 | ··to·a·row·in·another·table),·you·can·use·the·special·syntax·for·its | 253 | ··to·a·row·in·another·table),·you·can·use·the·special·syntax·for·its |
| 254 | ··type: | 254 | ··type: |
| | |
| 255 | ·····fk_type·::=·'FK'·<table_name>·[·'('·<reverse_name>·')'·] | 255 | ·····fk_type·::=·'FK'·<table_name>·[·'('·<reverse_name>·')'·] |
|
Max diff block lines reached; 42459/49155 bytes (86.38%) of diff not shown.
|