Ta parakatw symperilambanontai sth dianomh ths PostgreSQL se morfh postscript kai se unix selides boh8eias (man-pages). An exete prosbash sto internet, mporeite na breite ta keimena poy anaferontai parakatw sto http://www.postgresql.org/docs
Einai tmhma twn entolwn PSQL sthn ekdosh 6.4.
Yparxei ena script ston katalogo /tools gia na to kanete ayto
Xrhsimopoihste thn entolh psql \h
Koitajte sta regression tests sto src/test. Ekei 8a breite toys katalogoys
regress/sql kai suite/*.sql.
8a breite SQL scripts gia ekma8hsh ths SQL ston katalogo src/tutorial
To Morgan Kaufman Publisher einai ena apo ta kalutera biblia gia SQL. Asxoleitai me thn SQL92.
H etaireia Addison-Wesley Publishing exei epishs polu kala biblia. Polu dhmofilh biblia gia SQL.
Stephen Cannan kai Gerard Otten
McGraw-Hill Book Company Europe , Berkshire, SL6 2QL, England
Martin Gruber, Technical Editor: Joe Celko
SYBEX Inc. 2021 Challenger Drive Alameda, CA 94501
Ayto to biblio einai h Biblos ths Diaxeirishs Systhmatwn basewn Dedomenwn.
Periexei plhrofories gia kanonikopoihsh, SQL, recovery, concurrency, asfaleia,
integrity, kai extensions sto arxiko sxesiako montelo, trexonta 8emata
opws client/server systhmata kai Antikeimenostrafh montela. Polles
anafores symperilambanontai gia osoys 8eloyn na ma8oyn perissotera. To synistoume stoys perissoteroys xrhstes.
Periexei leptomereies me 8ewria sxesiakwn basewn, sxesiakh algebra, calculus
kai kanonikopoihsh. Alla den kaluptei 8emata toy pragmatikou kosmoy kai paradeigmata pio sun8eta. To synistoume stoys perissoteroys xrhstes.
To synistoume stoys perissoteroys xrhstes.
To biblio einai mia syllogh apo xartia poy exoyn dhmosieytei ola ta xronia sxetika me baseis. Den einai gia ton aplo xrhsth alla einai mia kalh anafora gia proxwrhmenoys (metaptyxiakous) foithtes h programmatistes systhmatwn basewn dedomenwn.
To biblio einai mia syllogh apo xartia poy exoyn dhmosieytei ola ta xronia sxetika me baseis. Den einai gia ton aplo xrhsth alla einai mia kalh anafora gia proxwrhmenoys (metaptyxiakous) foithtes h programmatistes systhmatwn basewn dedomenwn.
Ayto to biblio exei leptomereies gia me8odoys prosbashs, kai texnikes apo8hkeyshs.
Den einai gia ton aplo xrhsth alla einai mia kalh anafora gia proxwrhmenoys (metaptyxiakous) foithtes h programmatistes systhmatwn basewn dedomenwn.
Wrox Press Ltd.
Unit 16, 20 James Road, Tyseley
Birmingham, B11 2BA, England
1995
Technology
QED kai John Wiley
1991
Paroysiazei tis apaithseis gia na ftiajete client/server efarmoges basewn
mesw repository metamontelwn kai ths xrhshs ANSI stantar SQL
1993
Ekatontades alloi titloi einai dia8esimoi gia SQL ! Koitajte sta bibliopwleia.
Mporeite na breite keimena prodiagrafwn ANSI/ISO SQL sta parakatw sites -
Deite to Appendix A aytou toy keimenoy Appendix A
Oi prodiagrafes SQL 1998 (SQL 3) einai akomh ypo anaptyjh. Deite to 'Electronic Access to the SQL3 Working Draft' aytou toy keimenoy sto SQL 1998
Deite to Appendix B aytou toy keimenoy Appendix B
O katalogos periexei tis prodiagrafes ths glwssas gia thn proswrinh epektash sth stantar glwssa SQL-92. Ayth h nea glwssa onomazetai TSQL2.
Oi prodiagrafes ths glwssas poy paroysiazontai edw einai h telikh ekdosh ths glwssas.
H allhlografia mporei na stalei ston proedro toy TSQL2 Language Design Committee, Richard T.Snodgrass, Department of Computer Science, University of Arizona, Tucson, AZ 85721,
Oi 8ygatrikes kai oi diey8unseis e-mail twn melwn toy TSQL2 Language Design Committee mporoun na bre8oun se jexwristo tmhma sto telos twn prodiagrafwn ths glwssas.Ta periexomena aytou toy katalogoy einai opws paroysiazontai parakatw.
spec.dvi,.ps Prodiagrafes Glwssas TSQL2, ekdo8hke to Septembrio toy 1994
bookspec.ps Prodiagrafes ths Glwssas TSQL2, opws emfanisthkan sto biblio TSQL2, poy ekdo8hke to Septembrio toy 1995 (deite parakatw).
sql3 allagh twn protasewn poy ypoblh8hkan stis epitropes ANSI kai ISO SQL3.
Mazi me tis prodiagrafes ths glwssas exei sysxetistei mia syllogh apo sxolia poy aforoun se apofaseis sxediasmou, paroxh paradeigmatwn, kai apoceis gia to pws mporei na ylopoih8ei h glwssa. Ayta ta sxolia htan arxika protaseis sthn epitroph toy TSQL2 Language Design. Twra ejyphretoun ena sygkekrimeno skopo: na parexoyn paradeigmata toy oti exei ftiaxtei, w8hsoyn tis polles apofaseis poy par8hkan kata to sxediasmo ths glwssas, kai na sygkrinoyn thn TSQL2 me polles alles protaseis ths glwssas poy exoyn ginei ta thn teleytaia dekapentaetia. 8a prepei na do8ei emfash oti ayta ta sxolia den einai tmhma twn prodiagrafwn ths glwssas TSQL2, alla th symplhrwnoyn kai anaptussoyn me bash ayta. Oi katallhles prodiagrafes ths glwssas einai h teleytaia lejh sthn TSQL2.
Ta sxolia, mazi me tis prodiagrafes ths glwssas , arketoi deiktes (indexes), kai allo yliko yposthrijhs, exoyn ekdo8ei se ena biblio:
Snodgrass, R.T., editor, The TSQL2 Temporal Query Language, Kluwer Academic Publishers, 1995, 674+xxiv selides.
Oi kritikes emfanizontai sto biblio se mia syntomeymenh morfh. Oi kritikes parexontai plhreis se ayton katalogo sto arxeio eval.ps
To arxeio tl2tsql2.pl einai ena programma se prolog poy metafrazei thn epitrepomenh proswrinh logikh se TSQL2. Ayto to programma grafthke apo ton Michael Boehlen
Mporeite na epikoinwnhsete mazi toy gia ena paper poy perigrafei ayth th metafrash. Ayto einai mallon mia dated ekdosh toy programmatos. Newteres ekdoseis einai dia8esimes sto (ta systhmata TimeDB kai Tiger).Ayto to keimeno sas deixnei pws na apokthsete (nomima) ena antigrafo toy protupoy SQL-92 kai toy "trexontos" SQL3 Working Draft.
Sto ANSI protypo exei pneymatika dikaiwmata h ANSI, sto protypo ISO h ISO.
Yparxoyn duo (2) trexonta protypa SQL, h dhmosieysh ANSI kai h dhmosieysh ISO. Ta duo ayta protypa einai oloidia lejh pros lejh ektos apo tetrimmena zhthmata opws o titlos toy keimenoy, oi epikefalides ths selidas, h frash "International Standard" anti gia "American Standard", kai loipa.
Agorazontas to protypo SQL-92
To protypo ISO standard, ISO/IEC 9075:1992, Information Technology - Database Languages - SQL, einai dia8esimo (Martios, 1993) sto ANSI :
American National Standards Institute
1430 Broadway
New York, NY 10018 (USA)
Phone (sales): +1.212.642.4900
An agorasete kapoio apo ta keimena apo to ANSI, 8a exete ejtra xrewsh gia ejoda metaforas 7% epipros8eta (dhladh, peripoy US$9.10). Gia xwres ejwterikou ta ejoda metaforas 8a einai anamfibola akomh perissotera. To ANSI apaitei ka8e paraggelia na synodeuetai apo ena xarti paraggelias ths etaireias. Enallaktika, mporeite na steilete ena tsek amerikanikhs trapezas h se amerikanika dolaria, ta opoia 8a metrh8oun prin na stal8ei h paraggelia sas. (Yparxei mia ejairesh: an h etaireia sas einai enswmatwmeno melos toy ANSI, tote to ANSI 8a sas steilei ta keimena kai 8a xrewsei thn etaireia sas.)
To protypo ISO einai epishs dia8esimo ektos Amerikhs apo e8nika topika tmhmata (tmhmata protypopoihshs ana xwra) poy einai melh eite toy ISO (Die8nhs Organismos Typopoihshs - International Organization for Standardization) h toy IEC (Die8nhs Hlektrotexnikh Epitroph - International Electrotechnical Commission).
Antigrafa ths lista twn e8nikwn tmhmatwn kai oi diey8unseis toys einai dia8esimes apo to ANSI h apo alla e8nika tmhmata. Einai epishs dia8esima apo to ISO:
International Organization for Standardization
Central Secretariat
1, rue de Varembi
CH-1211 Genhve 20
Switzerland
Global Engineering Documents
2805 McGaw Ave
Irvine, CA 92714 (USA)
USA
Thlefwno (apo opoiadhpote perioxh): +1.714.261.1455
Thlefwno (mono gia Amerikh): (800)854-7179
Agorazontas ena antigrafo toy SQL3 Working Draft
Mporeite na agorasete mia ektupwsh toy SQL3 working draft apo to ANSI X3 Secretariat, CBEMA (Computer kai Business Equipment Manufacturers Association). Synh8ws dia8etoyn tis "pio prosfates ekdoseis" toy SQL3 working draft kai tis pwloun apo US$60.00 mexri US$65.00. Mporeite na epikoinwnhsete me to CBEMA sto:
CBEMA, X3 Secretariat
Attn: Lynn Barra
1250 Eye St.
Suite 200
Washington, DC 20005 (USA)
Hlektronikh prosbash sto SQL3 Working Draft
H pio prosfath ekdosh (th stigmh ths syggrafhs toy keimenoy) toy SQL3 (kai gia ANSI kai ISO) working draft (kai ola ta tmhmata toy) einai dia8esimh me "anonymous ftp" h me "ftpmail" apo to:
gatekeeper.dec.com
sto katalogo
/pub/standards/sql/
Genika, mporeite na breite arxeia me onomata opws:
sql-bindings-mar94.ps
sql-bindings-mar94.txt
sql-cli-mar94.ps
sql-cli-mar94.txt
sql-foundation-mar94.ps
sql-foundation-mar94.txt
sql-framework-mar94.ps
sql-framework-mar94.txt
sql-psm-mar94.ps
sql-psm-mar94.txt
Epishs, gia toys anagnwstes poy den mporoun na exoyn mia lista toy katalogoy me FTP, exoyme topo8ethsei ston idio katalogo ena arxeio me to onoma:
ls
Anaktwntas Arxeia katey8eian me xrhsh toy ftp
Ayto einai ena paradeigma gia to pws na xrhsimopoieite to FTP. Sygkekrimena, deixnei pws na synde8eite sto gatekeeper.dec.com, na pate ston katalogo opoy fylassetai to basiko keimeno, kai na metaferete to keimeno sto mhxanhma sas. Shmeiwste oti to mhxanhma sas prepei na exei prosbash sto Internet gia na to kanete ayto. To login einai 'ftp' kai o kwdikos einai h email dieu8ynsh sas (ayto einai gnwsto kai san anwnymo ftp 'anonymous ftp'). H entolh 'type binary' xrhsimopoieitai gia na sigoyrecete oti de 8a xasete kapoia bits apo to(a) arxeio(a) poy 8a labete. H 'get' katebazei ena arxeio th fora. Ta sxolia sto parakatw script einai mesa se angle brackets < san ayta > .
% ftp gatekeeper.dec.com
Connected to gatekeeper.dec.com.
220- *** /etc/motd.ftp ***
Gatekeeper.DEC.COM is an unsupported service of DEC Corporate Research.
<...this goes on for a while...>
220 gatekeeper.dec.com FTP server (ekdosh 5.83 Sat ... 1992) ready.
Name (gatekeeper.dec.com:<yourlogin here>): ftp <anonymous also works>
331 Guest login ok, send ident as password.
Password: <enter your email address here >
230 Guest login ok, access restrictions apply.
Remote system type is UNIX. <or whatever>
Using binary mode to transfer files.
ftp> cd pub/standards/sql
250 CWD command successful.
ftp> dir
200 PORT command successful.
150 Opening ASCII mode data connection for /bin/ls.
total 9529
-r--r--r-- 1 root system 357782 Feb 25 10:18 x3h2-93-081.ps
-r--r--r-- 1 root system 158782 Feb 25 10:19 x3h2-93-081.txt
-r--r--r-- 1 root system 195202 Feb 25 10:20 x3h2-93-082.ps
-r--r--r-- 1 root system 90900 Feb 25 10:20 x3h2-93-082.txt
-r--r--r-- 1 root system 5856284 Feb 25 09:55 x3h2-93-091.ps
-r--r--r-- 1 root system 3043687 Feb 25 09:57 x3h2-93-091.txt
226 Transfer complete.
ftp> type binary
200 Type set to I.
ftp> get x3h2-93-082.txt
200 PORT command successful.
150 Opening BINARY mode data connection for x3h2-93-082.txt (90900 bytes).
226 Transfer complete.
90900 bytes received in 0.53 seconds (166.11 Kbytes/s)
ftp> quit
% <to arxeio einai twra ston katalogo sas san x3h2-93-082.txt>
H Digital Equipment Corporation, opws kai polles alles etaireies, parexei yphresia ftp email. H apanthsh mporei na xreiastei gia na ftasei arketes meres, alla parexei mia yphresia isodunamh me to ftp gia aytous poy den exoyn apey8eias ftp prosbash sto Internet. H dieu8ynsh toy server einai:
ftpmail@decwrl.dec.com
To parakatw script 8a anakthsei to PostScript arxeio ths teleytaias ekdoshs toy SQL3 keimenoy:
reply joe.programmer@imaginary-corp.com
connect gatekeeper.dec.com anonymous
binary
compress
reply joe.programmer@imaginary-corp.com
connect gatekeeper.dec.com anonymous
binary
compress
uuencode
chdir /pub/standards/sql
get x3h2-93-091.ps
quit
reply joe.programmer@imaginary-corp.com
connect gatekeeper.dec.com anonymous
chdir /pub/standards/sql
get x3h2-93-091.ps
quit
Gia na anakthsete opoiodhpote allo sygkekrimeno arxeio, ektos toy x3h2-93-091.ps, apla antikatasthste to "x3h2-93-091.ps" me to onoma toy arxeioy poy epi8ymeite. Gia na parete th lista olwn twn dia8esimwn arxeiwn toy katalogoy, antikatasthste to "get x3h2-93-091.ps" me to "dir".
Ayto to kefalaio einai mia phgh plhroforias sxetika me thn protyph diergasia SQL kai thn trexoysa katastash ths.
Trexoysa katastash:
H anaptyjh toy einai ypo ejelijh gia na epekteinei thn SQL se mia oloklhrwmenh ypologistika glwssa gia ton ka8orismo kai diaxeirish persistent, sun8etwn antikeimenwn. Ayto perilambanei : generalization kai specialization hierarchies, pollaplh klhronomikothta, tupoys dedomenwn ka8orismenwn apo ton xrhsth, triggers kai assertions, yposthrijh gia empeira systhmata, anadromikes erwthseis, kai epipleon ergaleia diaxeirishs dedomenwn. Perilambanei epishs tis prodiagrafes twn afhrhmenwn tupwn dedomenwn (abstract data types -ADTs), object identifiers, me8odoys, klhronomikothta, polymorfismo, encapsulation, kai oles tis eykolies poy sxetizontai me th diaxeirish dedomenwn twn antikeimenwn.
To f8inopwro toy 1996, arketa tmhmata toy SQL3 perasan apo chfoforia ISO CD. Ayta htan ta SQL/Framework, SQL/Foundation, kai SQL/Bindings. Ayth h chfoforia apetyxe (opws htan anamenomeno) me 900 h peripoy tosa sxolia. Sta telh Ianoyarioy, egine kapoia synanthsh suntajhs toy ISO DBL kata thn opoia epejergasthkan luseis gia ena megalo ari8mo problhmatwn ta opoia eite enswmatw8hkan me sxolia mesw chfoforias eite stal8hkan san jexwrista papers. Mia kai h synanthsh suntajhs toy DBL den mporese na epejergastei ola ta sxolia, h synanthsh suntajhs parata8hke. H oloklhrwsh ths synanthshs suntajhs programmatisthke gia ta telh Ioylioy, 1997, sto Londino.
Meta th synanthsh suntajhs toy Ioylioy, anamenetai na zhth8ei mia telikh chfoforia gia ola ayta ta tmhmata ths SQL. H diadikasia gia thn telikh chfoforia 8a krathsei 6 peripoy mhnes kai 8a ginei mia synanthsh suntajhs DBL, meta thn opoia 8a ginei mia chfoforia DIS kai mia sxetika grhgorh chfoforia IS .
Oi diadikasies ISO exoyn allajei apo to SQL/92, etsi oi epitropes gia thn SQL doyleuoyn akomh gia ton ka8orismo twn akribwn leptomereiwn ths diadikasias.
An pane ola kala, ayta ta tmhmata ths SQL3 8a ginoyn ena epishmo protypo ISO/IEC sta telh toy 1998, alla to programma einai polu sfixto.
To 1993, oi epitropes anaptyjhs ANSI kai ISO apofasisan na xwrisoyn th mellontikh anaptyjh ths SQL se ena polymeres protypo. Ta tmhmata ayta einai:
Sthn Amerikh, oloklhrh h SQL3 epejergazetai kai san ANSI Domestic ("D") project kai san ISO project. To anamenomeno xroniko para8yro gia thn oloklhrwsh ths SQL3 einai to 1999.
Ta SQL/CLI kai SQL/PSM epejergazontai oso grhgorotera ginetai san addendums sthn SQL-92. Sthn Amerikh, ayta epejergazontai mono san Die8nh Erga (International - "I"). H SQL/CLI oloklhrw8hke to 1995. H SQL/PSM prepei na exei oloklhrw8ei sta telh toy 1996.
Epipros8eta ths SQL3 ergasias, enas ari8mos epipleon ergwn are persued:
Epitropes Protupwn kai Diadikasiwn Yparxei pragmatika megalos ari8mos epitropwn protupwn SQL se olo ton kosmo. Yparxei ena die8nes gkroyp toy protupoy SQL san tmhma toy ISO. Polles xwres exoyn epitropes poy asxolountai me thn SQL. Aytes oi xwres (synh8ws) stelnoyn antiproswpoys stis synanthseis ISO/IEC JTC1/SC 21/WG3 DBL. Oi xwres poy symmetexoyn energa sth diadikasia toy protupoy ISO SQL einai oi:
NIST Nomimopoihsh
Oi ylopoihseis ths SQL nomimopoiountai (sthn Amerikh) apo to E8niko Institouto Protupwn kai Elegxoy (NIST). To NIST exei gia thn wra ena sunolo apo test nomimothtas gia to entry level SQL-92. Oi akribeis plhrofories gia tis apaithseis ths NIST nomimopoihshs ka8orizontai ws Omospondiako Protypo Epejergasias Plhroforias - Federal Information Processing Standard (FIPS). Oi trexoyses apaithseis gia thn SQL orizontai sto FIPS 127-2. Mporeite na parete tis trexoyses Postscript kai Text ekdoseis aytou toy keimenoy apo to NIST. H lista twn trexontwn nomimopoihmenwn SQL proiontwn mporei epishs na bre8ei sto NIST.
Ekdoseis kai Ar8ra sxetika me to protypo ths SQL
Yparxoyn duo ekdoseis sxetika me to protypo SQL. Kai oi duo diati8entai apo to ANSI:
Oi dyo ekdoseis toy protupoy SQL einai oloidies ektos apo to ejwfyllo kai tis anafores se alla protypa. Kai oi duo diati8entai apo to :
American National Standards Institute
1430 Broadway
New York, NY 10018
USA
Phone (sales): +1.212.642.4900
* Technical Corrigendum 1:1994 to ISO/IEC 9075:1992
Epipros8eta, exoyn graftei arketa biblia sxetika me to protypo 1992 SQL. Ayta ta biblia parexoyn mia kalutera anagnwsimh perigrafh toy protupoy apo oti to idio to protypo.
Sxetizomena protypa
Enas sunolo protupwn endiaferoyn thn SQL koinothta. Ayto to tmhma periexei deiktes se plhrofories gia aytes tis prospa8eies. Aytoi oi deiktes 8a ayjh8oun ka8ws ejtra plhrofories diati8entai sto web.
Ena shmantiko tmhma ths SQL3 prospa8eias brisketai sto keimeno SQL Foundation :
Yparxoyn arketa proapaitoumena stis antikeimenostrafeis dynatothtes:
Aytes oi dynatothtes orizontai san tmhma toy SQL/PSM
Ginetai mia shmantikh ergasia gia na ekka8aristei to montelo antikeimenoy SQL-3 kai na ey8ygrammistei me to montelo antikeimenoy poy prota8hke apo to ODMG. Ayth h prospa8eia perigrafetai sto X3H2 kai ISO DBL paper: Accomodating SQL3 and ODMG. Einai epishs dia8esimh mia prosfath ekdosh sto SQL3/OQL Merger.
SQL3 Timing
H ergasia panw sto SQL3 brisketai se ejelijh, alla ta telika protypa 8a xreiastoun xronia akomh gia na oloklhrw8oun.
H ANSI ekdosh toy protupoy exei paromoio xronodiagramma.
To SQL/CLI einai ena programmatistiko interface epipedoy klhsewn gia SQL baseis. Exei sxediastei gia na yposthrizei prosbash se baseis apo shrink-wrapped efarmoges. To CLI dhmioyrgh8hke arxika apo mia ypoepitroph toy SQL Access Group (SAG). Oi prodiagrafes toy SAG/CLI ekdo8hkan san tis prodiagrafes toy Microsoft Open DataBase Connectivity (ODBC) to 1992. To 1993, to SAG esteile to CLI stis epitropes ANSI kai ISO SQL. (To gkroyp SQL Access exei sygxwney8ei twra me to X/Open consortium.)
To SQL/CLI parexei ena die8nes protypo gia :
SQL/CLI Timing
Sta plaisia ths diadikasia typopoihshs, to SQL/CLI epejergazetai me megalh proteraiothta.
To SQL/PSM epekteinei thn SQL pros8etontas:
Ektos toy oti einai ena ajiologo ergaleio anaptyjhs efarmogwn, to SQL/PSM parexei th 8emeliwdh yposthrijh gia antikeimenostrafeis dynatothtes sthn SQL3.
Multi-statement kai Stored Procedures
Oi Multi-statement kai stored procedures prosferoyn mia poikilia pleonekthmatwn se ena periballon client/server:
Dhlwtikes/Diadikastikes epektaseis
H Diadikastikh Glwssa pros8etei thn isxu mias paradosiakhs glwssas programmatismou sthn SQL mesw entolwn elegxoy rohs kai mia poikilia allwn programmatistikwn kataskeywn.
Entoles Elegxoy rohs
Oi diadikastikes epektaseis perilambanoyn kai alles programmatistikes kataskeyes:
Epipleon, oles oi paradosiakes SQL entoles mporoun na symperilhf8oun se diadikasies multi-statement.
Klhseis Ejwterikwn Diadikasiwn kai Synarthsewn Ena xarakthristiko poy anaferetai syxna stis eyxethries listes toy ti 8a epi8ymousame gia polla proionta basewn, kai poy exei ylopoih8ei se merikes, einai h dynatothta na employtismou twn enswmatwmenwn xarakthristikwn me klhseis se diadikasies poy exei gracei o xrhsths ejwterika ektos ths bashs.
To pleonekthma ayths ths dynatothtas einai oti dinei sth bash (synepws kai stis efarmoges basewn) prosbash se terastio ari8mo diadikasiwn kai synarthsewn.
Xronismos toy SQL/PSM
To SQL/PSM exei en syntomia:
Gia eykolia anaforwn, oi desmeuseis, typopoihseis ths glwssas programmatismou exoyn sygkentrw8ei se ena jexwristo keimeno. H trexoysa ekdosh einai apla ena apospasma apo ta protypa ths SQL-92(dynamika (dynamic) kai enswmatoumena (embedded)).
Paramenei adieykrinisth mia poikilia 8ematwn typopoihshs ths glwssas.
Sthn paradosiakh programmatistikh glwssa, yparxoyn oi apeikoniseis twn tupwn dedomenwn ths SQL-92. Parola ayta, 8a prepei na oristoun kai oi apeikoniseis metaju twn antikeimenwn ths SQL kai twn metablhtwn ths programmatistikhs glwssas..
Gia tis antikeimenostrafeis glwsses, prepei na oristei h apeikonish gia toys tupoys dedomenwn ths SQL kai metaju toy monteloy antikeimenwn ths SQL kai toy monteloy antikeimenwn ths antikeimenostrafous glwssas.
To montelo dedomenwn 8a prepei na oristikopoih8ei prin arxisei na ginetai anafora se tetoioy tupoy problhmata.
Oi typopoihseis ths glwssas 8a oloklhrw8oun ws meroys toy protypoy SQL3.
Oi prodiagrafes aytes 8a typopoihsoyn ena programmatistiko interface (API) metaju enos genikou Diaxeiristh Syndiallagwn (Transaction Manager) kai enos Diaxeiristh Porwn (Resource Manager) SQL. 8a typopoihsei tis klhseis synarthsewn, basizomenos sta protypa toy ISO/IEC 10026, "Distributed Transaction Processing", poy enas Diaxeirisths Porwn SQL 8a prepei na yposthrizei, gia na kanei commit duo stadiwn (two-phase commit) To basiko keimeno anaforas ejagetai apo mia dhmosieysh toy X/Open, me thn adeia toy X/Open, poy prosdiorizei rhta tis parametroys eisodoy kai ejodoy kai thn shmeiografia (semantics), gia tis akoloy8es synarthseis: xa_close, xa_commit, xa_complete, xa_end, xa_forget,xa_open, xa_prepare, xa_recover, xa_rollback, kai xa_start.
O ISO epixeirei na epitaxunei tis prodiagrafes toy X/Open XA. H diadikasia epitaxynshs yio8etei biomhxanikes prodiagrafes xwris allages. H chfoforia gia to XA sto ISO SC21, JTC epipedo 1, arxise stis 27 Aprilioy, 1995 kai teleiwse stis 27 Oktwbrioy, 1995. An to 75 twn chfoforwn egkrinoyn tis prodiagrafes toy XA, kai apo ta 2/3 twn p-melwn toy JTC 1, 8a apotelesei ena Die8nes Protypo. An h chfoforia to egkrinei, to SQL/XA 8a mporouse na ginei ena protypo mesa sto 1996.
H Temporal SQL asxoleitai me xronika ejartwmena dedomena. H idea einai pws einai xrhsimo na analuoyme ta dedomena gia na doume pws emoiazan kapoia dedomenh xronikh stigmh. H Temporal SQL einai ena paper toy Dekembrioy toy 1994 apo ton Rick Snodgrass poy perigrafei aytes tis ennoies.
H X3 anakoinwnei thn egkrish enos Neoy Ergoy, ISO/IEC 9075 Meros 7o: H SQL/Temporal einai mia dhmosieysh tupoy sxetikh me thn SQL/Temporal.
----------------------------------------------------------------------------
Temporal SQL
************
Rick Snodgrass (proedros ths epitrophs TSQL2)
31-Dek-1994
H yposthrijh egkyroy xronoy (Valid-time) jepernaei ayth enos koinou xronikou afhrhmenoy tupoy dedomenwn (ADT). Ston teleytaio, mia sthlh prosdiorizetai san ena xroniko pedio, eite ws DATE eite ws INTERVAL (8a do8oun paradeigmata suntoma). Me ton egkyro xrono, oi grammes enos pinaka diaforopoiountai me to perasma toy xronoy, ka8ws diaforopoieitai kai h pragmatikothta. H xronikh sfragida (timestamp) poy sxetizetai me mia grammh enos pinaka egkyrwn xronwn (valid-time table) metafrazetai apo thn glwssa erwthsewn ws h xronikh stigmh gia thn opoia o syndyasmos timwn twn sthlwn ths grammhs ekeinhs htan egkyros. H uparjh ths emmeshs ayths xronikhs sfragidas epitrepei thn diatupwsh safwn kai prwtotypwn erwthsewn.
To Grafeio Proswpikou toy Panepisthmioy ths Arizona exei kapoies plhrofories se mia bash dedomenwn, mesa stis opoies kai to onoma twn ypallhlwn, ton trexonta mis8o toys, kai ton trexoysa 8esh toys. Ayto mporei na perigrafei me enan aplo pinaka.
Employee(Name, Salary, Title)
SELECT Salary
FROM Employee
WHERE Name = 'Bob'
Employee(Name, Salary, Title, DateofBirth DATE)
SELECT DateofBirth
FROM Employee
WHERE Name = 'Bob'
To Grafeio Proswpikou epi8ymei perasei ta istorika stoixeia twn ypallhlwn se hlektronikh morfh. Gia na to kanei, pros8etoyn duo sthles, mia poy ypodeiknuei pote egine egkyrh h plhroforia ths grammhs, kai mia gia to pote epace na isxuei.
Employee (Name, Salary, Title, DateofBirth, Start DATE, Stop DATE)
Sto montelo dedomenwn, aytes oi duo sthles einai paromoies me to DateofBirth. Parola ayta, h paroysia toys exei epiptwseis megalhs klimakas.
Gia na broume ton trexonta mis8o enos ypallhloy, ta pragmata einai pleon dyskolotera.
SELECT Salary
FROM Employee
WHERE Name = 'Bob' kai Start <= CURRENT_DATE kai CURRENT_DATE <= Stop
Name Salary Title DateofBirth Start Stop
---- ------ ----- ----------- ----- ----
Bob 60000 Assistant Provost 1945-04-09 1993-01-01 1993-05-30
Bob 70000 Assistant Provost 1945-04-09 1993-06-01 1993-09-30
Bob 70000 Provost 1945-04-09 1993-10-01 1994-01-31
Bob 70000 Professor 1945-04-09 1994-02-01 1994-12-31
Eikona 1
Name Salary Start Stop
---- ------ ----- ----
Bob 60000 1993-01-01 1993-05-30
Bob 70000 1993-06-01 1994-12-31
CREATE TABLE Temp(Salary, Start, Stop)
AS SELECT Salary, Start, Stop
FROM Employee;
epanalabate
UPDATE Temp T1
SET (T1.Stop) = (SELECT MAX(T2.Stop)
FROM Temp AS T2
WHERE T1.Salary = T2.Salary kai T1.Start < T2.Start
kai T1.Stop >= T2.Start kai T1.Stop < T2.Stop)
WHERE EXISTS (SELECT *
FROM Temp AS T2
WHERE T1.Salary = T2.Salary kai T1.Start < T2.Start
kai T1.Stop >= T2.Start kai T1.Stop < T2.Stop)
mexri na mhn enhmerw8ei kamia grammh;
DELETE FROM Temp T1
WHERE EXISTS (SELECT *
FROM Temp AS T2
WHERE T1.Salary = T2.Salary
kai ((T1.Start > T2.Start kai T1.Stop <= T2.Stop)
OR (T1.Start >= T2.Start kai T1.Stop < T2.Stop))
Mia trith enallaktikh einai na xrhsimopoihsoyme SQL mono gia na anoijoyme ena kersora (cursor) ston pinaka. Mia syndedemenh lista xronikwn periodwn 8a krath8ei, ka8e mia exontas kai ena mis8o. H syndedemenh lista arxikopoieitai kenh.
DECLARE emp_cursor CURSOR FOR
SELECT Salary, Title, Start, Stop
FROM Employee;
OPEN emp_cursor;
loop:
FETCH emp_cursor INTO :salary, :start, :stop;
if no-data returned then goto finished;
find position in linked list to insert this information;
goto loop;
finished:
CLOSE emp_cursor;
H syndedemenh lista mporei kai na mhn xreiazetai sthn periptwsh ayth ana o kersoras einai ORDER BY Start.
Se ka8e periptwsh, h erwthsh, poy einai kai fysikh, einai duskolo na ekfrastei me tis yparxoyses dynatothtes ths SQL-92. H erwthsh einai tetrimmenh se TSQL2.
SELECT Salary
FROM Employee
Mia pio drastikh proseggish einai na apofugoyme to problhma ths ejagwghs ths mis8ologikhs istorias me anadiorganwsh toy sxhmatos wste na aposyzeujoyme to mis8o, th 8esh kai thn hmeromhnia gennhshs (parakatw, agnooume thn hmeromhnia gennhshs, gia aplothta).
Employee1 (Name, Salary, Start DATE, Stop DATE)
Employee2 (Name, Title, Start DATE, Stop DATE)
Name Salary Start Stop
---- ------ ----- ----
Bob 60000 1993-01-01 1993-05-30
Bob 70000 1993-06-01 1993-12-31
Name Title Start Stop
---- ------ ----- ----
Bob Assistant Provost 1993-01-01 1993-09-30
Bob Provost 1993-10-01 1994-01-31
Bob Professor 1994-02-01 1994-12-31
SELECT Salary, Start, Stop
FROM Employee1
WHERE Name = 'Bob'
SELECT Employee1.Name, Salary, Dept, Employee1.Start, Employee1.Stop
FROM Employee1, Employee2
WHERE Employee1.Name = Employee2.Name
and Employee2.Start <= Employee1.Start and Employee1.Stop < Employee2.Stop
UNION
SELECT Employee1.Name, Salary, Dept, Employee1.Start, Employee2.Stop
FROM Employee1, Employee2
WHERE Employee1.Name = Employee2.Name
and Employee1.Start >= Employee2.Start and Employee2.Stop < Employee1.Stop
kai Employee1.Start < Employee2.Stop
UNION
SELECT Employee1.Name, Salary, Dept, Employee2.Start, Employee1.Stop
FROM Employee1, Employee2
WHERE Employee1.Name = Employee2.Name
and Employee2.Start > Employee1.Start and Employee1.Stop < Employee2.Stop
and Employee2.Start < Employee1.Stop
UNION
SELECT Employee1.Name, Salary, Dept, Employee2.Start, Employee2.Stop
FROM Employee1, Employee2
WHERE Employee1.Name = Employee2.Name
and Employee2.Start > Employee1.Start and Employee2.Stop < Employee1.Stop
SELECT Employee1.Name, Salary, Dept
FROM Employee1, Employee2
WHERE Employee1.Name = Employee2.Name
Twra to G.P. erwtatai, poios einai o megistos mis8os; Prin thn eisagwgh toy xronoy, ayto htan eukolo.
SELECT MAX(Salary)
FROM Employee
SELECT Salary
FROM Employee AS E1
WHERE NOT EXISTS (SELECT *
FROM Employee AS E2
WHERE E2.Salary > E1.Salary)
CREATE TABLE Temp (Salary, Start, Stop)
AS SELECT Salary, Start, Stop
FROM Employee;
INSERT INTO Temp
SELECT T.Salary, T.Start, E.Start
FROM Temp AS T, Employee AS E
WHERE E.Start >= T.Start and E.Start < T.Stop and E.Salary > T.Salary;
INSERT INTO Temp
SELECT T.Salary, T.Stop, E.Stop
FROM Temp AS T, Employee AS E
WHERE E.Stop > T.Start and E.Stop <= T.Stop and E.Salary > T.Salary;
DELETE FROM Temp T
WHERE EXISTS (SELECT *
FROM Employee AS E
WHERE ((T.Start => E.Start and T.Start < E.Stop)
OR (E.Start >= T.Start and E.Start < T.Stop))
kai E.Salary > T.Salary;
Mia trith enallaktikh lush, einai na xrhsimopoihsoyme SQL oso ligotero ginetai, kai na ypologisoyme thn epi8ymhth istorikothta apo megistes times se mia allh glwssa xrhsimopoiwntas enswmatwmenoys kersores.
H erwthsh se TSQL2 einai aploustath.
SELECT MAX(Salary)
FROM Employee
Oi perissoteres efarmoges me bash dedomenwn exoyn na antimetwpisoyn kai dedomena me xronikh diakumansh einai anagkh na antimetwpistoun . H yposthrijh egkyroy-xronoy einai apousa sthn SQL. Polles koines xronikes erwthseis einai loipon eite duskoles na ejomoiw8oun mesw SQL, h apaitoun enswmatwsh ths SQL se mia dhlwtikh / diadikastikh glwssa (procedural), logw ths elleichs yposthrijhs pinakwn egkyroy-xronoy sto montelo dedomenwn kai sthn kataskeyh erwthsewn ths SQL.
Se allo shmeio, deijame pws h yposthrijh egkyroy-xronoy apaitei mikres allages sthn ylopoihsh ths DBMS, kai mporei na aplopoihsei dramatika kapoies erwthseis, na kanei dynates alles, kai akolou8ws na epitrecei me8odoys deiktodothshs kai strathgikes beltistopoihsewn poy mporoun na odhghsoyn se shmantikes kalytereuseis apodoshs.
Me to neo tmhma ths SQL3 poy yposthrizei plhrofories me xronikh diakumansh, mporoume na arxisoyme na stoxeuoyme se tetoies efarmoges, epitrepontas sthn SQL3 na diaxeirizetai kalutera ta xronika dedomena.
----------------------------------------------------------------------------
Epitroph Epikurwshs Protupwn* X3, Texnologia Plhroforiwn
DHMOSIEYSH NEWN
Ar. Eggrafoy: PR/96-0002
Apanthsh eis: Barbara Bennett sto bbennett@itic.nw.dc.us
H X3 anakoinwnei thn Egkrish enos Neoy Ergoy, ISO/IEC
9075 Tmhma 7: SQL/Temporal
Washington D.C., Ianoyarios 1996
----------------------------------------------------------------------------
Erwthseis gia to ergo ayto 8a prepei na staloun sto
Chairman of X3H2,
Dr. Donald R. Deutsch,
Sybase, Inc., Suite 800,
6550 Rock Spring
Drive, Bethesda, MD 20817.
Email: deutsch@sybase.com.
X3 Secretariat at
1250 Eye Street
NW, Suite 200,
Washington DC 20005.
Email: x3sec@itic.nw.dc.us
FAX: (202)638-4922.
Ena neo die8nes ergo typopoihshs ISO/IEC gia thn anaptyjh mias biblio8hkhs klasewn SQL (SQL class library) gia efarmoges polymeswn egkri8hke apo tis arxes toy 1993. Ayth h nea drasthriothta typopoihshs, onomazomenh SQL Multimedia (SQL/MM), 8a ka8orisei paketa me orismous afhrhmenwn tupwn dedomenwn SQL (abstract data type- ADT) xrhsimopoiwntas tis eykolies gia ton ka8orismo toy ADT kai thn proklhsh poy parexetai stis anamenomenes prodiagrafes toy SQL3. To SQL/MM exei san stoxo na typopoihsei tis biblio8hkes klasewn gia thn episthmh kai mhxanikh, thn full-text epejergasia keimenoy, kai tis me8odoys gia diaxeirish antikeimenwn polymeswn opws eikona, hxos, animation, moysikh, kai binteo. 8a parexei pi8ana ena SQL language binding antikeimeno polymeswn apo alles JTC1 organwseis protypopoihshs (p.x. SC18 gia keimena, SC24 gia eikones, kai SC29 gia fwtografies kai kinoumenes eikones).
To plano aytou toy ergoy gia to SQL/MM deixnei oti 8a einai ena spondylwto protypo poy 8a apoteleitai apo ena ayjanomeno ari8mo tmhmatwn. To tmhma 1 8a einai ena Plaisio (Framework) poy 8a prosdiorizei pws 8a kataskeyastoun ta ypoloipa merh. Ka8e ena apo ta ypoloipa merh, 8a einai afosiwmeno se ena sygkekrimeno paketo SQL efarmogwn. H parakatw domh tmhmatwn toy SQL/MM yparxei apo ton Augoysto toy 1994:
yparxei ena plh8os stantar ergasiwn ston tomea ton Xwrikwn kai Gewgrafikwn Systhmatwn: