🥇 Hordhaca Xogta Sawirka SQL Server 2017

Kahor bilowga koorsada "Developer MS SQL Server" Waxaan kuu diyaarinay turjumaad kale oo faa'iido leh.

Xogta garaafku waa tignoolajiyada muhiimka u ah xirfadlayaasha kaydka xogta. Waxaan isku dayaa inaan la socdo hal-abuurnimada iyo tignoolajiyada cusub ee aaggan, ka dib markaan la shaqeeyo xogta macluumaadka ee xiriirka iyo NoSQL, waxaan arkaa doorka xogta garaafyada inay noqdaan kuwo muhiim ah. Markaad la shaqaynayso xogta kala sarraysa ee adag, ma aha oo kaliya xog-ururinta dhaqameed waxtar ma leh, laakiin sidoo kale NoSQL. Badanaa, marka tirada heerarka isku xirka iyo cabbirka xogta ay kordho, waxqabadka ayaa hoos u dhacaya. Iyo marka cilaaqaadku noqdaan kuwo aad u adag, tirada ku biirista sidoo kale way kordhisaa.

Dabcan, qaabka xidhiidhku waxa uu leeyahay xalal uu kula shaqaynayo kala sareynta (tusaale ahaan, isticmaalka CTE-yada soo noqnoqda), laakiin kuwani wali waa xalal. Isla mar ahaantaana, shaqeynta xogta garaafyada SQL Server waxay kuu ogolaaneysaa inaad si fudud u maareyso heerar kala duwan oo kala sareyn ah. Qaabka xogta iyo weydiimaha labadaba waa la fududeeyay, sidaas darteed waxtarkoodu wuu kordhaa. Qadarka koodka si weyn ayaa loo dhimay.

Xogta garaafku waa luqad qeexan oo lagu matalo nidaamyada adag. Tignoolajiyadan ayaa durba si weyn loogu isticmaalay warshadaha IT-ga meelaha sida shabakadaha bulshada, nidaamyada ka hortagga khiyaanada, falanqaynta shabakada IT, talooyinka bulshada, talooyinka badeecada iyo nuxurka.

Shaqeynta xogta garaafka ee SQL Server waxay ku habboon tahay xaaladaha ay xogtu aad isugu xiran tahay oo ay leedahay xiriiro si wanaagsan loo qeexay.

Qaabka Xogta Sawirka

Jaantusku waa jaanisyo geeso ah (nodes) iyo geeso (xidhiidho, gees). Cidhifyadu waxay matalaan qaybaha, geesahana waxay matalaan cilaaqaadka sifadoodu ay ku jiri karaan macluumaad.

Xogta garaafku waxa ay qaabaysaa qaybaha sida garaaf sida lagu qeexay aragtida garaafka. Qaab dhismeedka xogtu waa geeso iyo geeso. Sifooyinku waa sifooyinka geesaha iyo geesaha. Xidhiidhku waa isku xidhka geesaha.

Si ka duwan noocyada kale ee xogta, xogta garaafyada ayaa mudnaanta siiya xiriirka ka dhexeeya hay'adaha. Sidaa darteed, looma baahna in la xisaabiyo cilaaqaadka iyadoo la adeegsanayo furayaal shisheeye ama hab kale. Waa suurtogal in la abuuro noocyo xog adag iyadoo la adeegsanayo kaliya vertex iyo cidhifyada geesaha.

Dunida casriga ah, xidhiidhada qaabaynta waxay u baahan yihiin farsamooyin sii kordhaya. Si loo qaabeeyo cilaaqaadka, SQL Server 2017 waxa ay bixisaa awooda xogta garaafyada. Cidhifyada iyo cidhifyada garaafku waxay u taagan yihiin noocyo cusub oo miisas ah: NODE iyo EDGE. Weydiimaha garaafka waxay isticmaalaan T-SQL cusub oo loo yaqaan MATCH(). Maadaama shaqadan lagu dhisay SQL Server 2017, waxaa loo isticmaali karaa xogtaada jirta iyada oo aan loo baahnayn beddelaad kasta.

Faa'iidooyinka qaabka garaafka

Maanta, ganacsiyada iyo isticmaalayaashu waxay dalbanayaan codsiyada gacanta ku haya xog badan oo badan, iyadoo la filayo waxqabad sare iyo isku hallayn. Matalaya xogta sida garaaf ahaan waxay bixisaa hab ku habboon oo lagu maareeyo cilaaqaadka adag. Habkani waxa uu xalliyaa mashaakil badan waxana uu caawiyaa in la helo natiijooyin ku jira xaalad gaar ah.

Waxay u egtahay in codsiyo badan ay ka faa'iideysan doonaan isticmaalka xogta garaafyada mustaqbalka.

Qaabaynta xogta: laga bilaabo xidhiidh ilaa qaabka garaafka

Tusaale:

Bal aynu eegno tusaale qaab-dhismeedka hay’adda oo leh kala sareyn shaqaale: Shaqaaluhu waxa uu u warramaa maamulaha, maareeyuhu waxa uu u warramaa maamulaha sare, iwm. Iyada oo ku xidhan shirkadda gaarka ah, waxa jiri kara tiro kasta oo heerar ah oo kala sarreyntan ah. Laakiin marka tirada heerarku korodho, xisaabinta cilaaqaadka ku jira kaydka xogta xidhiidhku way sii adkaanaysaa. Aad bay u adagtahay in la qiyaaso kala sareynta shaqaalaha, kala sareynta suuq geynta ama isku xidhka shabakadaha bulshada. Aynu aragno sida SQL Graph u xalin karo dhibaatada maaraynta heerarka kala duwan ee kala sareynta.

Tusaalahan waxaanu samayn doonaa qaab xog fudud. Aynu samayno shaxda shaqaalaha EMP oo leh aqoonsi EMPNO iyo tiirka M.G.R., oo tilmaamaya aqoonsiga horjoogaha shaqaalaha (maareeyaha). Dhammaan macluumaadka ku saabsan kala sareynta waxay ku kaydsan yihiin shaxdan waxaana lagu weydiin karaa tiirar EMPNO и M.G.R..

Jaantuska soo socdaa wuxuu muujinayaa qaab dhismeed urureed la mid ah oo leh afar heer oo buul ah oo qaab la yaqaan ah. Shaqaaluhu waa geesaha garaafyada miiska EMP. Hay'adda "shaqaale" waxay ku xiran tahay isku xirka "soo gudbiya" (ReportsTo). Marka la eego garaafyada, xiriirku waa gees (EDGE) oo isku xira noodhka (NODE) ee shaqaalaha.

Aan abuurno miis caadi ah EMP oo ku dar qiyamka halkaas si waafaqsan jaantuska sare.

CREATE TABLE EMP
(EMPNO INT NOT NULL,
ENAME VARCHAR(20),
JOB VARCHAR(10),
MGR INT,
JOINDATE DATETIME,
SALARY DECIMAL(7, 2),
COMMISIION DECIMAL(7, 2),
DNO INT)
 
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '02-MAR-1970', 8000, NULL, 2),
(7499, 'ALLEN', 'SALESMAN', 7698, '20-MAR-1971', 1600, 3000, 3),
(7521, 'WARD', 'SALESMAN', 7698, '07-FEB-1983', 1250, 5000, 3),
(7566, 'JONES', 'MANAGER', 7839, '02-JUN-1961', 2975, 50000, 2),
(7654, 'MARTIN', 'SALESMAN', 7698, '28-FEB-1971', 1250, 14000, 3),
(7698, 'BLAKE', 'MANAGER', 7839, '01-JAN-1988', 2850, 12000, 3),
(7782, 'CLARK', 'MANAGER', 7839, '09-APR-1971', 2450, 13000, 1),
(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, 1200, 2),
(7839, 'KING', 'PRESIDENT', NULL, '17-JUL-1971', 5000, 1456, 1),
(7844, 'TURNER', 'SALESMAN', 7698, '08-AUG-1971', 1500, 0, 3),
(7876, 'ADAMS', 'CLERK', 7788, '12-MAR-1973', 1100, 0, 2),
(7900, 'JAMES', 'CLERK', 7698, '03-NOV-1971', 950, 0, 3),
(7902, 'FORD', 'ANALYST', 7566, '04-MAR-1961', 3000, 0, 2),
(7934, 'MILLER', 'CLERK', 7782, '21-JAN-1972', 1300, 0, 1)

Sawirka hoose ayaa muujinaya shaqaalaha:

shaqaale leh EMPNO 7369 adeeca 7902;
shaqaale leh EMPNO 7902 ayaa adeecay 7566
shaqaale leh EMPNO 7566 ayaa adeecay 7839

Hadda aan eegno garaaf matalaad xog isku mid ah. vertex-ka SHAQAALAHA wuxuu leeyahay sifooyin dhowr ah waxaana isku xira xiriirka "ka hooseeya" (EmplReportsTo). EmplReportsTo waa magaca xiriirka.

Miiska cidhifka ah (EDGE) waxa kale oo laga yaabaa inuu ku jiro sifooyin.

Aan samayno miiska noode EmpNode

Syntax ee abuurista noodu waa wax fudud: marka loo eego muujinta SHAXDA ISUUR ayaa lagu darayaa ilaa dhamaadka "Sida NODE".

CREATE TABLE dbo.EmpNode(
ID Int Identity(1,1),
EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
MGR NUMERIC(4),
DNO INT
) AS NODE;

Hadda aynu xogta ka beddelno shaxda caadiga ah una beddelno garaaf. Xiga INSERT gelinaya xogta miiska xiriirka EMP.

INSERT INTO EmpNode(EMPNO,ENAME,MGR,DNO) select empno,ename,MGR,dno from emp

Jadwalka qanjirada ee tiir gaar ah $node_id_* Aqoonsiga noodhka waxa loo kaydiyaa sidii JSON. Tiirarka soo hadhay ee shaxdan waxay ka kooban yihiin sifada noodhka.

Samaynta geesaha (EDGE)

Abuuritaanka miis cidhif ah waxay aad ula mid tahay samaynta miis noodhka, marka laga reebo in ereyga muhiimka ah la isticmaalo "Sida EDGE".

CREATE TABLE empReportsTo(Deptno int) AS EDGE

Hadda aynu qeexno xidhiidhka ka dhexeeya shaqaalaha isticmaalaya tiirar EMPNO и M.G.R.. Jaantuska qaab dhismeedka ururka ayaa si cad u muujinaya sida wax loo qoro INSERT.

INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 1),
   	(SELECT $node_id FROM EmpNode WHERE id = 13),20);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 2),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 3),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),10)
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 4),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 5),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 6),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 7),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 8),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 9),
   	(SELECT $node_id FROM EmpNode WHERE id = 9),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 10),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 11),
   	(SELECT $node_id FROM EmpNode WHERE id = 8),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 12),
   	(SELECT $node_id FROM EmpNode WHERE id = 6),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 13),
   	(SELECT $node_id FROM EmpNode WHERE id = 4),30);
INSERT INTO empReportsTo  VALUES ((SELECT $node_id FROM EmpNode WHERE ID = 14),
   	(SELECT $node_id FROM EmpNode WHERE id = 7),30);

Jadwalka cidhifka ah ee caadiga ah wuxuu ka kooban yahay saddex tiir. Marka hore, $edge_id - Aqoonsiga cidhifka ee qaabka JSON. Labada kale ($from_id и $to_id) waxay matalaan xidhiidhka ka dhexeeya qanjidhada. Intaa waxaa dheer, geesaha waxay yeelan karaan sifooyin dheeraad ah. Xaaladeena waa Deptno.

Aragtida Nidaamka

Aragtida nidaamka sys.tables laba qaybood oo cusub ayaa soo baxay:

waa_ gees
waa_node

SELECT t.is_edge,t.is_node,*
FROM sys.tables t
WHERE name like 'emp%'

SMS

Walxaha laxidhiidha garaafyada waxay ku yaalaan galka Graph Tables. Astaanta miiska noodhka waxa lagu calaamadeeyay dhibic, iyo summada miiska cidhifkeeda waxa lagu calaamadeeyay laba goobaabin oo isku xidhan (wax yar oo muraayad ah).

MATCH odhaahda

Sifeynta MATCH laga soo qaatay CQL (Cypher Query Language). Tani waa hab wax ku ool ah oo lagu waydiiyo sifooyinka garaafyada. CQL waxay ku bilaabataa tibaax MATCH.

Saan

MATCH (<graph_search_pattern>)
 
<graph_search_pattern>::=
    {<node_alias> {
                 	{ <-( <edge_alias> )- }
               	| { -( <edge_alias> )-> }
             	<node_alias>
             	}
 	}
 	[ { AND } { ( <graph_search_pattern> ) } ]
 	[ ,...n ]
 
<node_alias> ::=
    node_table_name | node_alias
 
<edge_alias> ::=
    edge_table_name | edge_alias

tusaalooyin

Aynu eegno dhawr tusaale.

Weydiinta hoose waxay soo bandhigaysaa shaqaalaha ay Smith iyo maareeyahoodu ka warbixiyeen.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR
FROM
    empnode e, empnode e1, empReportsTo m
WHERE
    MATCH(e-(m)->e1)
and e.ENAME='SMITH'

Weydiinta soo socota waa helida shaqaale heer-labaad ah iyo maamulayaal Smith. Haddii aad xukunka ka saarto HALKEE, markaas natiijadu waxay soo bandhigi doontaa dhammaan shaqaalaha.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2
WHERE
    MATCH(e-(m)->e1-(m1)->e2)
and e.ENAME='SMITH'

Iyo ugu dambeyntii, codsi loogu talagalay shaqaalaha heerka saddexaad iyo maareeyayaasha.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR,E3.EMPNO,e3.ENAME,E3.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2, empReportsTo M2, empnode e3
WHERE
    MATCH(e-(m)->e1-(m1)->e2-(m2)->e3)
and e.ENAME='SMITH'

Hadda aan beddelno jihada si aan u helno madaxda Smith.

SELECT
E.EMPNO,E.ENAME,E.MGR,E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,e2.ENAME,E2.MGR,E3.EMPNO,e3.ENAME,E3.MGR
FROM
    empnode e, empnode e1, empReportsTo m ,empReportsTo m1, empnode e2, empReportsTo M2, empnode e3
WHERE
    MATCH(e<-(m)-e1<-(m1)-e2<-(m2)-e3)

gunaanad

SQL Server 2017 waxay isu dejisay sidii xal ganacsi oo dhamaystiran oo loogu talagalay hawlaha ganacsiga IT-ga ee kala duwan. Nooca koowaad ee SQL Graph waa mid rajo leh. Xataa inkasta oo ay jiraan xaddidaadyo, waxaa horeba u jiray hawlkarnimo ku filan oo lagu sahaminayo awoodaha garaafyada.

Shaqeynta garaafka SQL si buuxda ayaa loogu dhex daray SQL Engine. Si kastaba ha noqotee, sida horeyba loo sheegay, SQL Server 2017 waxay leedahay xaddidaadaha soo socda:

Ma jirto taageero polymorphism.

Kaliya isku xirka aan jiho lahayn ayaa la taageeraa.
Cidhifyadu ma cusboonaysiin karaan $from_id iyo $to_id tiirarka UPDATE.
Xiritaanka ku meel gaadhka ah lama taageero, laakiin waxa lagu heli karaa iyadoo la isticmaalayo CTE.
Taageerada in-Memory walxaha OLTP waa mid xadidan.
Miisaska ku meel gaadhka ah (Shaxda ku meel gaadhka ah ee nidaamka-nooca), miisaska ku meel gaadhka ah ee maxaliga ah iyo kuwa caalamiga ah lama taageero.
Noocyada shaxda iyo doorsoomayaasha miiska laguma dhawaaqi karo NODE ama EDGE.
Weydiimaha xog-ururinta lama taageero
Ma jiro hab toos ah ama saaxir ah oo loogu badalo miisaska caadiga ah miisaska garaafyada.
Ma jiro GUI si loo muujiyo garaafyada, laakiin waxaad isticmaali kartaa Power BI.

Akhri wax dheeraad ah:

Sida SQL Server u isticmaalo filtarrada bitmap

Source: www.habr.com

Hordhac garaafka Databases SQL Server 2017