Whatsup - לינוקס, תוכנה חופשית וקוד פתוח בעברית

תיכנות בלינוקס - תכנון טבלא עבור sqlite

ברנש - 05/12/2018 - 17:05
נושא ההודעה: תכנון טבלא עבור sqlite
יש לי אלמנט כלשהו, שאחד השדות בו הוא מערך.

דוגמה כjson:
קוד:

{
    "id":111,
    "array_field":["aaa","bbb","ccc","ddd"],
    "another_field":"string"
}


אצטרך "לשלוף" את האוביקט מתוך הטבלא, בין השאר ע"י השאלה "האם יש חפיפה בין המערך array_field ובין מערך ערכים שאני מעביר לפונקציה".

מה הדרך הנכונה לתכנן את מבנה הטבלא? ואיך לעזאזל אפשר להגיש כזו שאילתה? (אני יודע שאפשר לפצל את האוביקט לדוגמה, ובסוף לאחד, או להגיש "ערימת שאילתות" כדי למצוא את החפיפה...).
Anonymous - 05/12/2018 - 18:10
נושא ההודעה:
אין ספק שהדרך הנכונה מבחינת SQL היא לפצל המידע ולהחזיק את ערכי המערך בטבלה נפרדת עם fk שמצביע לאובייקט.

בשנים האחרונות נוספו בחלק מבסיסי הנתונים פונקציות שיודעות לטפל ב-JSON ולבצע פעולות כמו שאתה מבקש, הנה מה שיש ב-sqlite
https://www.sqlite.org/json1.html
מבחינת ביצועים לא ממש ברור לי אם באמת יש אינדקסים על המידע וזה לא סתם table walking ענקי ומאוד איטי
Anonymous - 05/12/2018 - 19:51
נושא ההודעה:
השאלה הנשאלת האם אתה באמת רוצה לשמור json שלא כפוף לסכמה מסויימת (לשמור document) או שאתה יכול לוודא את הסכמה ואז אתה רוצה לשלוף באמצעות שדות ותתי שדות.
ברנש - 06/12/2018 - 09:15
נושא ההודעה:
כמובן שאני מעדיף שזה יתבצע באופן תקני (ואם מחר ירצו שאחליף לDB אחר?)
Anonymous :
השאלה הנשאלת האם אתה באמת רוצה לשמור json שלא כפוף לסכמה מסויימת (לשמור document) או שאתה יכול לוודא את הסכמה ואז אתה רוצה לשלוף באמצעות שדות ותתי שדות.

Anonymous - 06/12/2018 - 09:20
נושא ההודעה:
ברנש :
כמובן שאני מעדיף שזה יתבצע באופן תקני (ואם מחר ירצו שאחליף לDB אחר?)
Anonymous :
השאלה הנשאלת האם אתה באמת רוצה לשמור json שלא כפוף לסכמה מסויימת (לשמור document) או שאתה יכול לוודא את הסכמה ואז אתה רוצה לשלוף באמצעות שדות ותתי

שדות.


תקני זה לא מוגדר -

או שאתה מחפש RDBMS ואז אתה יכול לעשות פירוק או שאתה מחפש document (ואז תשמור את ה json עצמו).

אתה צריך לעשות החלטה אם אתה מוודא JSON Schema או לא, אם אתה כן מוודא כל לך לבצע את הפירוק לפי הצורך שלך.
ברנש - 06/12/2018 - 10:05
נושא ההודעה:
יתכן שהסברתי לא נכון.
אני מבצע פירוק של הJSON (נתתי אותו בשאלה הראשונה רק כדי להדגים מבנה נתונים).
ברור לי שעבור חלק מהשדות, מבנה הטבלא הוא:

קוד:
REATE TABLE myTable
(
  id serial NOT NULL,
  another_field character varying(512),
  array_field <WTF to put here?
)


אני מחפש פתרון לא לשמירה של המסמך הjsonי, אלא רק לשדה שהוא מערך מחרוזות, כאשר אצטרך בעתיד לבצע חיפוש לפי מערך אחר שאקבל. אני תוהה מה הדרך הנכונה ביותר לבצע את זה. (תיאורטית - להפוך את המערך למחרוזת, ואז להשתמש בlike על כל ערך במערך לפיו מגישים שאילתה. חנק לDB).
Anonymous - 06/12/2018 - 10:29
נושא ההודעה:
ענו לך.
אתה בונה טבלה חדשה עם קישור אחד להרבה בעזרת FK, ומכניס את כל ערכי המערך לטבלה החדשה.

את השליפה אתה מבצע עם תנאי JOIN מתאים.
Anonymous - 06/12/2018 - 10:31
נושא ההודעה:
אני לא יודע אם לך זה יתאים אבל אני משתמש במקרים כאלה במטבלה שנייה עם מפתח :

wtftoputhere יהיה מפתח (אני משתמש ב md5sum בגלל שאני צריך לאחזר את המידע) בצורה :

קוד:

create table lists (
id varchar ,
index int,
value blob)




זה שזה מתאים לצורך שלי, לא אומר שזה יתאים לצורך המדוייק שלך.
שימוש ב Like במקרה כזה הוא כבד
ברנש - 06/12/2018 - 12:27
נושא ההודעה:
ואיך אתה מנסח את שאילתת השליפה? קודם שולף עם in מהטבלא השנייה, ואח"כ join מותנה על הראשונה? זה לא יוצר עומס?

Anonymous :
אני לא יודע אם לך זה יתאים אבל אני משתמש במקרים כאלה במטבלה שנייה עם מפתח :

wtftoputhere יהיה מפתח (אני משתמש ב md5sum בגלל שאני צריך לאחזר את המידע) בצורה :

קוד:

create table lists (
id varchar ,
index int,
value blob)




זה שזה מתאים לצורך שלי, לא אומר שזה יתאים לצורך המדוייק שלך.
שימוש ב Like במקרה כזה הוא כבד

Anonymous - 09/12/2018 - 09:01
נושא ההודעה:
קוד:

select another_field, value from myTable, mt where mt.id = 1000 and mt.id = lists.index


למה? כי יש לך יותר מרשומה אחת, אז יש natural join ולא left join.

אם רוצים אפשר לעשות את זה בשני שאילתות, האחת לשלוף את ה id וanother_field והשניה לשלוף את הרשומות של הרשימה.
Anonymous - 09/12/2018 - 09:39
נושא ההודעה:
ברנש :
ואיך אתה מנסח את שאילתת השליפה? קודם שולף עם in מהטבלא השנייה, ואח"כ join מותנה על הראשונה? זה לא יוצר עומס?


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

אתה פשוט מבצע שאילתה שמצבעת join מופרש לפי הצורך (רק תזכור שצריך sqlite3 בשביל blob ושאתה יכול להשתמש רק ב left outer join ולא right / full outer join )


נ.ב. index int זה טעות כי המילה index היא מילה שמורה.
ברנש - 11/12/2018 - 09:40
נושא ההודעה: מנסה לפשט מעט
מה יקרה אם אשמור את המערך לאחר הפיכתו לטקסט יחיד (פסיקים מפרידים) שהוא ייראה כך:

קוד:
'aaa,bbb,ccc,ddd'


ואנסח את שאילתת השליפה כך:
קוד:

select * from tbl1 where
'%aaa%' like array_field
or
'%bbb%' like array_field
or
'%ccc%' like array_field

עד כמה זה יעמיס את השאילתות האלה בהשוואה לאפשרות של left join על שתי טבלאות וקינון של שאילתה שתצמצם כפילויות בתשובה?
Anonymous - 11/12/2018 - 10:40
נושא ההודעה:
לא חושב שיש איזשהו מקרה סביר שבו like כזה ייתן ביצועים יותר טובים מ-join, אבל זה כבר נהיה דיון תאורטי סתמי.

אם אחד מהם משמעותית יותר נוח לך - תשתמש בו, ואם וכאשר תתקל בבעיות ביצועים תנסה את השני.
אם הם שקולים מהבחינה הזאת ואתה יודע מראש שאכפת לך מביצועים - תעשה ניסוי של שניהם ואז תבחר (ואולי תשקול לעבור ל-DB שאינו SQLite, שלמיטב ידיעתי לא ניחן בביצועים הטובים בעולם)
Anonymous - 11/12/2018 - 10:44
נושא ההודעה:
אתה מניח שכל הרשומות הן באותו האורך?

מה קורה אם יש פסיק באחד הערכים? מה קורה אם יש שני איברים במערך שתואמים? האם אתה רוצה לדעת רק האם יש התאמה כלשהי? כמוכן עדכון המערך יכול להיות יקר יותר.
ברנש - 11/12/2018 - 10:59
נושא ההודעה:
1. אין עדכון ערכים לאחר הזנתם, ובשליפה אם יש פסיק - הוא ייכנס לגוף השאילה. (אני יכול למנוע מחרוזות "אסורות"). אני מחפש רק התאמה כלשהי (יש התאמה - "שלוף"). כמובן שיש התנהיות נוספות בשאילתה, והמצמצמת ביותר תהיה הראשונה. לא הבנתי מה הקשר ל"כל הרשומות באותו אורך"?
צפריר :
אתה מניח שכל הרשומות הן באותו האורך?

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

Anonymous - 11/12/2018 - 12:58
נושא ההודעה: Re: מנסה לפשט מעט
ברנש :
מה יקרה אם אשמור את המערך לאחר הפיכתו לטקסט יחיד (פסיקים מפרידים) שהוא ייראה כך:

קוד:
'aaa,bbb,ccc,ddd'


ואנסח את שאילתת השליפה כך:
קוד:

select * from tbl1 where
'%aaa%' like array_field
or
'%bbb%' like array_field
or
'%ccc%' like array_field

עד כמה זה יעמיס את השאילתות האלה בהשוואה לאפשרות של left join על שתי טבלאות וקינון של שאילתה שתצמצם כפילויות בתשובה?


בשביל תבלה מספיק גדולה , אתה יכול להפיל את התהליך עם דבר כזה, אם הטבלאות קטנות ואורך השורה קטן מגודל node (8K ) אין לך סכנה של קריסת התהליך.

תיצור טבלה בגודל של 3 ג"ב , תכניס מחרוזות באורך שונה ובעלי סדר שונה (בשביל למנוע

השאילתה שלך צריכה לכל הפחות להיות מחולקת לשלושה שלבים מופרדים (בהם אתה משחרר משאבים) עבור כל אחד מהשאלות.

במקרה הגנרי הפעולה שלך מקבילה ל union (ויכול להיות שתומר לכזו) ביחד עם כל הבעיות שקיימות עבור union עם מחרוזות.

אבל אם מודבר בטבלה קטנה (כמה עשרות אלפי שורות ) בגדלים קטנים פר שורה, אתה לא אמור לראות הבדל משמעותי בביצועים (שנייה מול שניה ומספר מילי שניות לדוגמה בתוצאה). בגלל שרוב הזמן שהמערכת תיקח לא יהיה על ביצוע השליפה עצמה אלא על הקצאת המשאבים (בהנחה שכל המחרוזות באורך זהה).
Anonymous - 11/12/2018 - 13:10
נושא ההודעה: Re: מנסה לפשט מעט
אז נראה שאני בבעיה - המחרוזות שונות באורכן, ואני מצפה לסדר גודל של עד שני מליון רשומות בשנה ...
Anonymous :
ברנש :
מה יקרה אם אשמור את המערך לאחר הפיכתו לטקסט יחיד (פסיקים מפרידים) שהוא ייראה כך:

קוד:
'aaa,bbb,ccc,ddd'


ואנסח את שאילתת השליפה כך:
קוד:

select * from tbl1 where
'%aaa%' like array_field
or
'%bbb%' like array_field
or
'%ccc%' like array_field

עד כמה זה יעמיס את השאילתות האלה בהשוואה לאפשרות של left join על שתי טבלאות וקינון של שאילתה שתצמצם כפילויות בתשובה?


בשביל תבלה מספיק גדולה , אתה יכול להפיל את התהליך עם דבר כזה, אם הטבלאות קטנות ואורך השורה קטן מגודל node (8K ) אין לך סכנה של קריסת התהליך.

תיצור טבלה בגודל של 3 ג"ב , תכניס מחרוזות באורך שונה ובעלי סדר שונה (בשביל למנוע

השאילתה שלך צריכה לכל הפחות להיות מחולקת לשלושה שלבים מופרדים (בהם אתה משחרר משאבים) עבור כל אחד מהשאלות.

במקרה הגנרי הפעולה שלך מקבילה ל union (ויכול להיות שתומר לכזו) ביחד עם כל הבעיות שקיימות עבור union עם מחרוזות.

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

Anonymous - 11/12/2018 - 13:58
נושא ההודעה:
אין חכם כבעל ניסיון - צור טבלה בגודל פעמים ממה שאתה יודע (ארבע מיליון רשומות) עם המבנה שאתה מצפה לראות.

תעשה השוואה באמצעות like ביחד עם or.
תעשה השוואה באמצעות like ביחד עם union.
תעשה השוואה באמצעות like מפורקת למספר תתי שאילתאות.

תעשה השוואה כשיש פירוק של אותה הטבלה לשני טבלאות או יותר. ותעשה שאילתה באמצעות join.

אחרי שיש לך את המידע תבצע החלטה.
כל הזמנים הם GMT + 2 שעות