הבלוג של גרי רשף

22/12/2011

FileTable ב-SQL Server 2012

Filed under: Uncategorized — גרי רשף @ 20:54

הקדמה רקע קצרה: יתכן מצב בו נשקול אפשרות לשלב קבצים בבסיס הנתונים (הכוונה לקבצי טקסט, Office, גרפיקה, וידאו, קול וכו'; ולא לקבצי mdf..). תסריטים אפשריים:
1. למשאבי אנוש מגיעים קבצי קורות חיים של עובדים ורוצים לשמור אותם בבסיס הנתונים (במקום לאתר את המועמד בדטבייס ולחפש במיילים או במחיצות את קובץ ה-Word שהוא שלח).
2. עבדתי פעם בחברה שייצרה מוצר צריכה נפוץ, חנויות ששמו את המוצרים של החברה במקום בולט זכו לתגמול, וסוכני המכירות היו עורכים ביקורות – מצלמים ושומרים על קשר אישי. התמונות נשמרו במחיצה עם שם שכלל את מספר הלקוח, אבל היה נוח יותר לו ניתן לשלב את הקבצים או לפחות את מיקומם בבסיס הנתונים.
3. הארגון רוצה לנהל מערכת מעקב אחר מסמכים המבוססת על SQL Server: אילו מסמכים יש, גרסאות, מאפיינים וכו'; שוב- מבלי שהקבצים יהיו קיימים באופן עצמאי ובלתי תלוי במערכת המידע.
עד גרסת 2008 ניתן היה לשמור קבצים בטבלאות בעמודות BLOB כמתואר בפוסט הזה. הפתרון מאוד בעייתי משתי סיבות עיקריות:
1. הדבר גורם לניפוח קבצי הדטבייס בקבצים ששמורים בהם כמידע בינארי, בשעה שאין למידע הזה שום שימוש כמידע לצורך פילטור או חישוב וכו' (אפשר כמובן לשמור בטבלה רק את מיקום הקובץ בדיסק אבל אז שתי המערכות אינן מסונכרנות בהכרח).
2. הטיפול בקבצים מסורבל- במקום להיכנס עם ה-File Explorer למחיצה ושם לטפל בקבצים- יש לכתוב פרוצדורה שתשמור ותשלוף אותם על פי הצורך, וליצור ממשק משתמש משל עצמנו.
מגרסת 2008 התווספה אופציית ה-FileStream: כעת יש אפשרות לשמור את הקבצים החיצוניים כך שאינם חלק מקבצי ה-mdf אלא נשמרים באופן עצמאי על הדיסק כ-FileStream, כלומר- כקובצים בינאריים שמערכת ה-SQL Server מתייחסת אליהם כאל אוסף של ביטים ובייטים מבלי להתעניין אם מדובר בקובץ PDF או Exe וכו'.
זה פותר את בעייה מספר 1 הנ"ל (ניפוח הקבצים), אך לא את מספר 2- עדיין יש לשמור ולשלוף בעזרת פקודות SQL, וזה אומר שצריך ליצור ממשק מתאים שיבצע את זה בכל פעם שנרצה לגשת לקבצים.
החל מגרסת SQL Server 2012 גם בעייה מספר 2 באה על פתרונה: כעת ניתן לטפל בקבצים דרך סייר הקבצים (File Explorer) – להכניס, למחוק, לשנות, לפתוח, לשמור, ליצור מחיצות משנה, להעביר וכו'; וכל זה מעדכן אוטומטית את הטבלה הרלוונטית. באופן דומה ניתן בעזרת פעולות DML להעתיק ולהעביר קבצים, ליצור ולמחוק מחיצות, לעדכן מאפייני קבצים (Archive, Read Only..) וכו', וכל קובץ ותת-מחיצה מתחת למחיצה שהגדרנו לטבלה קיימים סינכרונית ב-File System ובטבלה עצמה.
אם לדייק- נוכל לפגוש את הקבצים בצורה נוספת- יצירת הטבלה עם ה-FileStream יוצרת מחיצה במקום שהגדרנו שכוללת אובייקטים שונים של המערכת וקבצים בינאריים שמייצגים את הקבצים ששמרנו – ואיתם איננו אמורים להתעסק (כשם שאיננו מתעסקים ישירות עם קבצי ה-mdf אלא עם הטבלאות והפרוצדורות דרך ה-SSMS), אלא פונים למחיצה אחרת שכתובתה אינה c:\MyDir\.. כמקובל אלא משהו בסגנון של ..\MyServer\mssqlserver\MyFileTableDir\MyFileTableDirctory\\.

דיבורים, דיבורים, אבל מה עם קצת קוד?
קודם כל נקנפג את המערכת באופן חד פעמי לשימוש ב-FileStream:
דרך התפריט ה-Programs:

=> Microsoft SQL Server .. => Configuration Tools => פותחים את ה-SQL Server Configuration Manager.

בצד שמאל של הכלי שנפתח בוחרים ב-SQL Server Services, ובצד ימין – קליק ימני על ה-Service הראשי של ה-Instance הרלוונטי (בדרך כלל – SQL Server (MSSQLSERVER)).
בחלונית שנפתחת ניגשים ללשונית FileStream, מסמנים את ה-CheckBoxes הרלוונטיים, מאשרים וסוגרים.

clip_image002

ב-SSMS יש לאפשר את השימוש בכלי כך:

Exec SP_Configure Filestream_Access_Level, 2;
Reconfigure;
Go

clip_image004

נעבור לדטבייס שנועד לבדיקות ונסיונות או ניצור אחד כזה על ידי פקודת Create Database MyDB (לא ניתן להשתמש ב-tempdb), ומכיוון שה-FileStream יוצר קבצים חדשים שהם חלק מהדטבייס – יש להגדיר FileGroup מתאים עבורם, מחיצה פיזית, ושם לוגי בו נפנה אליה:

Alter Database MyDB
      Add FileGroup MyFileTableFG Contains FileStream;
Go

Alter Database MyDB
      Add File(Name='MyFileTableDirName',
      FileName='C:\MyFileTableFile')
      To FileGroup MyFileTableFG;
Go

Alter Database MyDB
      Set FileStream(Non_Transacted_Access=Full,
      Directory_Name='MyFileTableDir');
Go

ולבסוף ניצור טבלה שתהיה מסונכרנת עם המחיצה הנ"ל (המחיצה הפיזית C:\MyFileTableFile המיוצגת לוגית על ידי MyFileTableDir):

Use MyDB;
Go

Create Table MyTable As FileTable
       With(FileTable_Directory='MyFileTableDirectory');
Go

Select * From MyTable Order By name;
Go

clip_image006

כעת עוד אין כלום במחיצה ולכן הטבלה ריקה, אבל כבר אפשר לשים לב שב-SSMS תחת מחיצת Tables מופיעה מחיצה חדשה בשם FileTables ובה הטבלה שנוצרה על פי הפקודה הנ"ל למרות שלא ציינו את שמות העמודות (המבנה שלה קשיח- ולא ניתן לשנותו).

בתור התחלה נוסיף פנימה כמה קבצים.

נפתח את המחיצה הלוגית כך: קליק ימני על הטבלה, ובתפריט שנפתח לבחור ב-Explore File Table Directory, ונקבל את ה-File Explorer המוכר במחיצה

\\Gerireshef\mssqlserver\MyFileTableDir\MyFileTableDirectory

(המחשב שלי נקרא GeriReshef, ולכל אחד יופיע שם המחשב וה-Instance שלו).

ומי שמתעצל לחפש קבצים מתאימים אצלו במחשב יכול להוריד את הקובץ המכווץ הזה,

לפתוח אותו במחיצה שנפתחה,

ליצור בתוכה מחיצת משנה בשם Gibuy,

להעביר לשם את קובץ ה-Rar(כדאי כבר להתרגל לרפרש את התצוגה לאחר כל פעולה על ידי F5),

clip_image008

ואפשר כך (למרות שלא ניתן "להיכנס" למחיצה על ידי פקודת CD):

clip_image009

וגם כך:

Exec XP_CmdShell 'Dir \\Gerireshef\mssqlserver\MyFileTableDir\MyFileTableDirectory';
Go

clip_image010

ולבדוק שוב את הטבלה:

Select * From MyTable Order By name;
Go

clip_image012

כבר אפשר לשים לב שהטבלה כוללת יחסי אב-בן (או- יחס של אחת לרבים 1:N עם עצמה), מה שמבטיח פוסט עתידי עם קוד רקורסיבי המאפשר לחקור את מבנה עץ מחיצות המשנה שמתחת למחיצה הלוגית.

סוג הנתון של העמודות האלו (path_locator, parent_path_locator) הוא Hierarchy_id וגם בזה עוד נלכלך את הידיים..

ננסה לבצע כמה פעולות עריכה באמצעות קוד, למשל אשכפל את עצמי (כלומר- את התמונה שלי):

INSERT
INTO   MyTable(file_stream,name)
Select 0xFFD8FFE000104A46494600010100000100010000FFFE003B43524541544F523A2067642D6A7065672076312E3020287573696E6720494A47204A50454720763632292C207175616C697479203D2039300AFFDB0043000302020302020303030304030304050805050404050A070706080C0A0C0C0B0A0B0B0D0E12100D0E110E0B0B1016101113141515150C0F171816141812141514FFDB00430103040405040509050509140D0B0D1414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414141414FFC00011080080008003012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00FD53A28A2800A28A64B22C68C58E001CD002492AC6096200F5358BAB6BD2DA42CF04624C0FBCC6AB6A9A979A7FD8ECB58D777FE646107CA3D8D71D5AEA3A23BA861255756469E25D5EE63258A423A8F2C5656A7AE6BB6EC64B7BF1B48CED9003834B338073E6E07B1AAE52DE42C59B25BAE6B8FEB677BCB13D8D0D2BE28DC0D9F6BB6F341E0F963041AEE342F14D8EBF1B1B698174E1E26E1D7EA2BCBE4B3B5914ED1961C8C1C52DB5ACD14916A16256D6E1728B2E72037707D8FA1AE9A58852382BE125455CF67A2B9EF097891F5CB3617110B7BC84ED96207233D8A9EE0D7435DCB53813B8514514C6145145002020D2D7243E287860939D5625C02DF3061C0FA8AB9A678F7C3FAC1896D358B2B86972111661B9B1D46DEB419FB48F737CB015CF789F565B745801F98F2DEC2B71AE2300FCC32066BC7FC59E24136A970C18905B007A01C57162EAFB281E960A8FB7AABB2342EB58DC1BE6FCAB267D50B861BB8FAD73773ABB84CA82D9358D77AB94720C873E95F372AB267DBD1A318AB2475171AC6D3B53F5AA936B063F98BFEB5CB9D4C49C8277D55BABB6900576C67B8ACB9FB1D0E36476F65ADACAC0E7F3AE8FC35A984B99E291BF77200483C8CFAD790A6AA90B91BC003BF6AD1D2BC53F65BC8DF7E50FCADCF1835D746AB83D4F2F1347DA41A3DB46A31E917705EA37C80849B1D0A138C9FA135E851C81D41041FA578658F88AD9A548DA71E4CDFBB60DD3918C56D6B9F19E0F01C1A7DBDF594D72194AB4D1B01F281C100F53ED5F4B4A7CE8F88AD1F652D4F5BC8A4DC3D6BE77BEFDA89EF04874BD276440FC925CC99623DD4743F8D735A8FED2DE24702DD2382396538531C583F8126B5EB639FDA7647D5A5C7A8A4F3573C1AF916EBE2EF8DAD2CD12C352326D70E44EAA64EBF7771072BEDFAD4B63F187E204D2CAEF790452615A34312F97C1C907D8F4C8E6993CF2FE53833E2474854DC39570BB8856CF35421F1D42D2B1667C93B5548CF3EA2B8CBCBDB716DE58489E427991739207626ACCB6804315E5AB23838C42C7E65FC7BD75FB28F53CFF006926B43D37E1F78AAEA4F19C2F24D731C660942A34BC0C2F5EBD2BB23AAB5E4624DDBCF73DCD78DF846FAE23D596692DF27694F309C1507AFD6BB5D43C65A4F84F4C6BBBF9846AA30BEE7D2BE6B354DCA3147DA64AD2A72933AE7BEC0C293C0E41AC8BDBE8E490860030AF9CFC69FB5F2C1BA3D2AC3CD9338CE3231EB9AC5F06FC7FBDF136AA915D2EC99DB0A91F53ED5E2384A2AECFAAA389849D91F49EA3AF41A73A23B2A9C7535CFDFF008CF4E3912DFA460762D8AF15F8CFE20D6EC74D334D6F35B46EB857E99F4AF9D1F52D6AEEE59E5BDB858CFCC7CC7C002B4A34FDA6A6789C43A5B23EFCD27C43A36AE0AC1A846E7B8DC3353DF39B101E37578CF46539AF883C3BA8DC59B89D7517B8847DE6B7712347F5507247E15EFDE01F144E444935CADF59CABC48A7907B715A4A9F23B1CB4B10E6BDE47AE26BB768D1A19F6EC75914B701B07A5751F159A5F16F81349BC899A396D6E406008C804720D7996B3A1788757B0F3AD560B4B75206F95B2C07AE0547A0F8EFFE10AD0AE6CFC437B0EB713B07548D48E5738E2BD3A38854229CD9E4E23052C555E4A68B300FECDB8F2BED2D287193BF19CFE153B9912313DDC2B213C2792A495FC2B9DB4F14587891DF55D274C3649F31F26562F82BD7683C8CFA55E1ADEF92DB559A596D51131E5A7AFAD7AF09AAB1538F53E5EB52961AA4A954DD13EA9E234D3D2311CA5640388D460FE22AC5AF982D56679A7922705FF74E735931680752B4BDD425786E06E3E54848563F5F435128B9D374E8A3FB4C3BE5C92B93938EDDF9ADB955B4673F334FDED8A77DA62DC05874FF00DFB83FBC3BC6569534236B61E748CECEA7E65C648FA0AA13FDA745BC4963729E672B22719F622B663D666B9B1DECBE64A770C2E7922BAA574ACB639E1C9ADD6A6BF83AD6E75AD9169EBFBDFBE32554ED1D8EEE2B1FE2769C97C862997CD7823632444F313FB8FE5505AEA0918FF4433D8DE32128C87A1FF0CF6AED346F0D43ACF95AADC5BACBA85CC020BE0BC19B6F2AD8E991CFE06BE6332938CD268FB2CA29A951E68BD7A9F1F6B5A06AB7B1EEB4D3E370C7E7F3C15C0F61DEB43C17E029748F14E8378D1FD94C9791A18E26279F55CF4F7AFAD356F0A5A46EA915887FE100C2C187D722B3869FA6DAEBBA4DBCF6E8822944A37004861DFDABCE7579972B47D1D2C3273BA63BF681F0C4D2F852D9E1B792E537A8923760DF29F6C7073DEBC2ED7E11A6A30C53C303077504C720E467AA95F5AFAC3C5DADD95F69F240E5195971B4D781EADAC6A1E109A09E746934E6728A779E076E7BF150A5C8BDC3BEB61936A48E7F4CF833636C58CF691876C80A9161B35DF7863E0F697A3C714B1C6D6D30C13863863EB8ADCF0C78EF47BF8A391214DCC01049CFEA6B5354D6A2B884BC2C158761D2B29D49496E60B0EA3AD8BA750FB1442DD8FC8CB8C939AF00BE98E93F116E349BCB7F3BCB6DE92E7E5646FF00EB1AF4ED53555BBB5421CABE79E7A55293408FC5D2C77ECE86E2CF28991F3B63F87FC2A5A72858BA2A3ED7531B4FB09B448A38AD5D9C2DCBB295EC3D7F2A351BD96498C724CF2C28DB9948008CF5C569EB56F25B9860855CCA07210E0826A8369329B420A0337DE28C73C7BFA57D8E5F1B508DF73F3ACEE5CD8D9B468DD6A3A74DA6491D92C96CAA4307773C93D415AC5B57686E2112CE63456F95A3E7F2CD319E2684979596404650F71DF9A6A3C24E70C6DF77EF3BE47F435E87223C2776F5362EEEF4FBC5512CAEAE877318C83C8A9FFB6AD2E08B769253E922E57F5AA4DA9DBCB74A8D6D1C91BFDE200045456F747CC7804293DBB642A91B768FC6A794ABEBA334AFB52DC63843046040DE79AEFACF501650C2CACA56440C1D4E41E3D6BCC351D18DBAC32DB3192393380149C7B035D678740D47E1FDD32CBFE95A6CE19477319FF0C9AF1734A5CF4D4D743EA322AFC95A54E5D568747AD78B258AD8E1F271DEBCB9B5BBE9AFAFF508E16BA68A12AB18FBC4F5E3F2AD4D46F7CE8865B04D6341AD45611B59E540724963FAD7CA45EB667DF7B5825B6A7976BFF1B358B881C496D3DA32E4149570411517847C57AFF89EDCC1AC5C23DA8F991003902ADF8D7C49E0913C915DEA6249C1C9FB38079F4358569F109637F2F46F0BDF5EC678570300FBE4D77C629AB24733555BE6BE87411CD27872FF0036B296B563CAE73B4D76961E2B7961E5C85F5AF258AEB5ABBBF49EE74F7D3AD5DBFD54926E653F85763B05BAA84F9D71D476AE69C6D2D0855A56699DF68F27DB9259431201CF1C8F4AD2D1BC71368A05B5DE8F21639F2EE62E55C6783EC47A1AE63C1D7A238DA3237863D8D745AC922DECE48FE5C9209F5E6BB70B4A3566A2CF27118B9E1A12A915765AD525BB9D06B31065757FBADC1C77CFAD650BBFB44924B346FB988FDFC1CED07B1A926D5259E39227B990C51FCD185EC7D0FA8ACF8EE1E25946F7532FDE58B1823D6BECA9A518F2A5B1F9FD6AD2AB2E77D4D0BAB2B7B7B22F6D75E63AB7CC5C72476E3D6A9D9BDDA891951E48946E6C2E315148F1C71A8521DC372F8C363FAD6A45A835DB44AB22A963CBAE40C7704568DD91968CA0B2A5B4910037CA7E632EC208F5153C7752EA1322B466F562C90817696FCAB66E2411A4CFA6B8B9B491B7B84C9963F500FA7E157A3F185A368F1C8E16D618CF94418C0738FE759B77D916A31BEACC3124F7F74AB6CCDA6A05E54B165571FA8FC6A16BC9ACDC49327911C8DF3C96E4AACBE871D01AB373A9E9F7324CE976D3ACEB811BAE173DB07B54BA7DD98B4836B20583712104C78F6C56528F32B4968694E6E9CB9A0F533E7BD47B868DB8461B90F515C62F8460F12F8924176EED6B082DB51CA86E7DABB47F0E5DCF6AEADB44F182F184F9832F19C1AC1D1AE05ADCC91EECC8DCE735F1789A2F0F59A89FA160713EDA946523175A8BC2BE14510C5E14B4B8B81FF2D7CBCB1FC6A858789EEB5790C71D8A59C00E02462BD76DB4FD1DED56E2E618A7B9C747E71EF591A86BBA65BCCD16C89180E022800FE353CF392D59EF4AB5D68CE2EFAD1EF2D4AEC0AD8CEEE98AE6ED2F24174B0C927319DA41E99AEAF52F105BE25F2C81E8B9AF2CBDD683EBD2C501046FF9B078FF003C52841EECF1F11595F43D474DBB6B19795059FF00D5ECC73EB5DFCA7CDF0DC323EC6DAF820F04FB7D6BC8B45BE69AF232C4617000CF15EC7A0C96274B986A09BED570C719CA93C6462BA284F96BC7B1C58AA77C2CDB3196481A659566785C10A62F2C302BDF9A76AD62219BCC8544B01195647048F5057A8AA129F99A4442916F215B7654FA7E94B246F0BA968DE32C3729E0F1F5AFB649EE7E77E43D9209D4BC20C4D8C3061C7E1E955A08D0BAA34A551CF0F8E17EB5A16D3A4F18B84B81F688F878987DF5FE869AC22F37CC31031B71B0B7233ED4D37D4761FA6DC1D0351636F89A19B0B967DB8079E9DEBA8FB6C6DADCD657F12792503C72280EBD3A11FE715CFE83636AB3C31DC7D9A5DFFF003D65F981EC40A357B8B8B1D4EE61568DC900C72A0C95F6CFF4AC5CAEF42D3E5572FC761A6349334691F94C4AC80F4145B45640083CC49E346CC7D0ECFA1AC359EE6F2D56302088B93E74BD09C1FE25C7EB58FAD78834DD154C561CDC63124AB900FD066949E86D0BC9EC761A5F89E1D3BE21DA689E62CCF762439072170B9AC5F1DF84DB4AD606B7A6004292D3DA632181EA57FA8AE07E1AF88964F8DDA534C438686450C79E4E2BE83D7ADD250CC4F415F23985471AE7DC65B454B0D63E76F1678E92D62496DE75C0009543D3D41AF34D43E223CF3EE556039F9B39AF57F88DE014904B35B471E252491B78CFF8D78CCFE0EBA8A428F1E307AD7346A41EA68E1553B363BFE130B9BF9B30B342718C91C11DF35774FB668A50554FCC72C7351E99E1D10CB993E5C7515DA595844D000A06F1C0C53954BAD074E836EEC9B42C42E011B875F7AF6DF87F335C936B1B0CCF1B20DF8C671C039AF1DB7B76B65070377AFAD7A2FC3FB92B22956F9D4E719AE6751D39292E87A12A6A7070665D978B7419F5BBDD16E6F1ECB57B494C3369F7AA6274907553DBE9F5ADABA6F32205234816021399327F2A6FED25F0F2CFC5DA25A78CB4F45B5F166948A1EE106DFB6403AC5263A903EEB751D3A572BE0DF1541AC69B15B5EB18E4007953673B7D8FA8FE55F6783C6AC4C2EF73E1730C03C2BBC766760F6B6BFBA5B9982C8D867206547B822949B7B1B856597CD841FE2EB514564BAA4CD6DE64915CA2E3247071EC7D7D6AB0373F2C0D1B18D5B612572A0D7A77B9E27374B172EAFC5AAACCD32E3AE5986722B96D7FE232998436CC669E43B5475C9AF39F12F8E6E2FD9B0C54740057117DACC88FBE394895790413906B8D451D0A958F6CBCD5E78E031C93E666E5C93CE7D2B95BB98BAB166C8E6BCD348F13CB7572DE7DC3B4C9F310EE4922BAD83C40974A470735475A1BA3EAA744F1CE95A8B9DA125DA5BD335F5ACBABC7A95924CAC36C8A08C73D6BE36F10DB33599997823E618EC6BD87E0E78F5BC43E198ADA5389EDFF0076C1B391FE22BE6F34C3B7FBC47D2E5988D1D2677BAF5A0BFB678F7631C865EA2BCBF59B1688C8643B9941C1C75AF52BDB775B3F386718CE6B93D6A349631BD4723A8AF0E0AC7D0495D5CF36874F79A5DECD91E98C56F58D905218707BE3BD3BC948A42A8A42F4E7B5680B71144369393ED5ACA5646718F2903179081BB8F5AEABC190CB6D741B9C66B374DD1CCADBF961DC0AEF740D2FCA19230B9CD66D7368CD1772D78FB504B7F08DDBBB8D9E51C835F2FE89ACBDA4F95C7964FF007BA0CD7A7FED05E37834DD3D74786606EA71F32FF757D4D7CFD1486ED4A094A9EA40AFA3CB68B841C9F53E6334ACA73505D0F7393E36DB5AC36D622D0DFDE261637818875F6C8E31F5AF40B0066B5919F7446E0070CAFF0037F857CE3E1C821D38011A856EE7D6BD0F47F175D5A7900137511611C916EE41FE120F635EDA938ABA3E6674EEEE8FFFD9,
       'MyJPG2.jpeg';

clip_image013

קצת ארוך- זה ה-FileStream של התמונה שלי כפי שהעתקתי מעמודה file_stream מהטבלה, ולאחר ריפרוש F5 נראה את הקובץ במחיצה. אפשר לחסוך את זה על ידי Select מפולטר מהטבלה, אבל רציתי להמחיש שמעתה ניתן להעביר קבצים גם כך..

כעת ניצור מחיצה חדשה:

Insert
Into   MyTable(name,is_directory)
Select 'MySubDir',
       1;

clip_image014

clip_image016

ונעביר אליה את הקובץ החדש שיצרנו על ידי Update למיקומו:

Update MyTable
Set    path_locator=(Select path_locator.ToString() From MyTable Where name='MySubDir')+Stuff(path_locator.ToString(),1,1,'')
Where  name='MyJPG2.jpeg';

clip_image018

clip_image020

כאן צריך להתחיל לתרגל את ההיכרות עם ה-HierarchyID והפונקציות שלו (נדמה לי שאנשי הדוט-נט קוראים לזה מתודות..)- ToString או Cast As Varchar מציגה את הערכים כשרשור של העץ מהשורש עד אליהם:

Select  name,
        path_locator.ToString() [path_locator],
        Cast(parent_path_locator As Varchar(Max)) [parent_path_locator]
From    MyTable;

clip_image022

השתמשתי פעם ב-ToString ופעם ב-Cast As Varchar לצורך ההמחשה.

אפשר לראות שה-path_locator של שני הקבצים במחיצות המשנה מורכב מזה של המחיצה ומזה שלו; וכך העברנו את הקובץ מהמחיצה הראשית למשנית על ידי בניית ה-path_locator שלו.

מחיקה של קובץ תתבצע על ידי פקודת Delete, אך מחיקת מחיצה יכולה להתבצע בפעולת Delete רק אם אין לה תוכן, אחרת- יש לבצע קודם Delete לתוכן (אנחנו "מריחים" רקורסיה במקרה שרוצים למחוק ענף שלם מהעץ).

אנחנו ננסה למחוק בדרך מתחכמת- מכיוון שמדובר בטבלה היא אמורה לאפשר טרנזקציות. למשל-

Begin Tran
Delete From MyTable Where name='MyJPG.jpeg';
--RollBack;

(לא לבצע RollBack בשלב זה אלא רק בסוף)

ב-Query חדש שנפתח ננסה להריץ

Select * From MyTable;
Go

והשאילתה תתקע בגלל נעילה על הטבלה.

נריץ עם Hint של NoLock

Select * From MyTable (NoLock);
Go

ולא נראה את הקובץ בטבלה.

ניגש למחיצה הלוגית ב-File Explorer, נרפרש, והוא איננו. כלומר- הכלי מציג מידע UnCommited. ראו הוזהרתם!

הסקרנים מוזמנים לחטט מעט בקרביים של המערכת ולהיכנס למחיצה הפיזית C:\MyFileTableFile שיצרנו, לחטט בתתי המחיצות ולחפש היכן שמורים הקבצים ובאיזה אופן (בכניסה יש לקבל אישור חד פעמי מכיוון שמדובר במחיצת מערכת).

במקרה זה נמצא שני קבצי File Stream בגודל 5kb של שני קבצי ה-Jpeg. הווה אומר- הקובץ עדיין קיים..

לסיום- לא לשכוח לבצע Rollback.

גיבוי- בדקתי והגיבוי מתבצע גם על הקבצים. חשוב לשים לב לכך- מצד אחד יתרון בכך שתפסנו שתי ציפורים ביד אחת ולא צריך לסמוך על החבר'ה מה-System, אבל מצד שני קבצי הגיבוי עלולים להתנפח וכבר עדיף להפיל את התיק חזרה על ה-System..

לסיכום: מעניין, סוגר את הפינה של ממשק המשתמש שנותרה פתוחה עד כה, ויש עוד הרבה מה לבדוק ובעיקר לנסות- חמישה קבצים צנועים בגודלם אינם מערכת אמיתית של אלפי קבצים גדולים בעץ מסועף של מחיצות וריבוי משתמשים.

אני מקווה עוד לחזור ולהתמודד עם בעיות הרקורסיה שהכלי מציב בפנינו.

מודעות פרסומת

6 תגובות »

  1. […] שינויים וכו'; ועל כך כתב לאחרונה אסף אביב. השימוש ב-FileTable החל מגרסת 2012 מתבסס על שימוש ב-HierarchyID, וכפי שציינתי- […]

    פינגבאק של ישום היררכיה (עץ) בעזרת מספרים ראשוניים - גרי רשף — 17/01/2012 @ 21:22

  2. סבבה מה שאתה מעלה פה זה מצויין אינפורמטיבי ובטוח ייחסוך לאנשים זמן, רק הוספתי את הערתי בנוגע לסיכונים הכרוכים, ולכך שאם אפשר בכלל מלהמנע מלשמור קבצים בתוך ה SQL מה טוב.

    תגובה של פלג — 24/12/2011 @ 14:05

  3. 1)xp_cmdshell אם כל פרצות האבטחה, מה גם שאני מניח שאתה מריץ פה את הפעולה עם הרשאות ADMIN אז בכלל סיבה למסיבה.
    החולשה היא בשורה :
    Exec XP_CmdShell 'Dir \\Gerireshef\mssqlserver\MyFileTableDir\MyFileTableDirectory';
    2)ב NET אם אני זוכר נכון עושים את זה עם אובייקטים שלא מצריך שימוש ב xp_cmdshell, מה שמקטין את הסיכון, כככה שעדיף לכתוב CLR למען השקט הנפשי:)

    תגובה של פלג — 23/12/2011 @ 13:08

    • 1. השתמשתי ב-XP_CmdShell לבצע Dir למחיצה. זה לא חלק הכרחי מהטכנולוגיה וזה רק נועד להמחיש איך זה נראה.

      2. אינני יודע איך עושים זאת עם clr (ניהול סינכרוני של קבצים ב-File System וב-File Table), ובטח ובטח שלא עם אוראקל. הראיתי איך עושים זאת עם כלים Built In של SQL Server 2012.

      תגובה של גרי רשף — 23/12/2011 @ 13:29

  4. זה NICE שיש את זה, אבל לא נכון להשתמש באופציה מסיבות של אבטחת מידע, עומס על השרת וכדומה(עם כל הכבוד זה לא התפקיד של ה SQL לעשות את זה).

    תגובה של פלג — 22/12/2011 @ 23:55

    • נכון- אין בכוונתי להתחיל לנהל את הקבצים שלי (מוזיקה, תמונות מהטיול, וידאו מהיום הולדת, מצגות פאוארפוינט, סקריפטים של SQL) דרך FileTable.
      יחד עם זאת- אם יום אחד יקראו לי לדגל ויתנו לי משימה- לא אצטרך להמציא את הגלגל – הוא כבר מומצא.

      אגב- מה בעיית אבטחת המידע?

      תגובה של גרי רשף — 23/12/2011 @ 10:20


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: