חלק ראשון
אנו ממשיכים ליצור את האמולטור הפשוט שלנו לבורסה. הנה מה שנעשה:
- בואו ניצור דיאגרמת ארגון מסד נתונים.
- נתאר מה, איך והיכן הוא מאוחסן.
- בואו לגלות כיצד נתונים קשורים זה לזה.
- בואו נתחיל ללמוד את היסודות של SQL באמצעות הדוגמה של פקודת יצירת טבלת SQL CREATE TABLE , שפת הגדרת נתונים ( DDL ) של שפת SQL.
- בואו נמשיך בכתיבת תוכנית Java. אנו מיישמים את הפונקציות העיקריות של ה-DBMS במונחים של java.sql ליצירת מסד הנתונים שלנו באופן פרוגרמטי, תוך שימוש ב-JDBC ובארכיטקטורה תלת-שכבתית.
שני החלקים הללו התבררו כנפחיים יותר, מכיוון שעלינו להכיר את היסודות של SQL וארגון DBMS מבפנים, ולצייר אנלוגיות עם Java. כדי לא לשעמם אותך עם רשימות קוד, בסוף יש קישורים למאגר commit github המתאים לתוכנית.
עיצוב DBMS
תיאור האפליקציה
כבר שמעתם שארגון אחסון נתונים הוא חלק בלתי נפרד מהתכנות. הרשו לי להזכיר לכם שמטרת האפליקציה שלנו היא אמולציית החליפין הפשוטה ביותר:
- ישנן מניות שערכן יכול להשתנות במהלך יום המסחר לפי כללים נתונים;
- יש סוחרים עם הון ראשוני;
- סוחרים יכולים לקנות ולמכור מניות לפי האלגוריתם שלהם.
הבורסה פועלת
בתיקיות - פרקי זמן קבועים (במקרה שלנו - דקה). במהלך סימון, מחיר המניה עשוי להשתנות, ואז הסוחר עשוי לקנות או למכור מניות.
החלפת מבנה נתונים של אמולציה
בואו נקרא מודלים של ישויות חליפין בודדות. כדי למנוע טעויות עיגול, נעבוד עם סכומים כספיים דרך שיעור
BigDecimal
(פרטים ניתן למצוא בקישור בסוף המאמר). נתאר את המבנה של כל דגם ביתר פירוט:
קידום:
תְכוּנָה |
סוּג |
תיאור |
name |
Srting |
שֵׁם |
changeProbability |
int |
הסתברות לשינוי שיעור באחוזים על כל סימון |
startPrice |
BigDecimal |
עלות ראשונית |
delta |
int |
הסכום המרבי באחוזים שבאמצעותו הערך הנוכחי יכול להשתנות |
מחיר מניה:
תְכוּנָה |
סוּג |
תיאור |
operDate |
LocalDateTime |
זמן (סימון) לקביעת התעריף |
share |
קידום |
קישור לקידום |
rate |
BigDecimal |
מחיר המניה |
סוחר:
תְכוּנָה |
סוּג |
תיאור |
name |
חוּט |
זמן (סימון) לקביעת התעריף |
sfreqTick |
int |
תדירות העסקאות. צוין לפי התקופה, בתיקיות, שלאחריה הסוחר מבצע פעולות |
cash |
BigDecimal |
סכום כסף מלבד מניות |
traidingMethod |
int |
האלגוריתם שבו משתמש הסוחר. בואו נגדיר את זה כמספר קבוע, היישום של האלגוריתם יהיה (בחלקים הבאים) בקוד Java |
changeProbability |
int |
הסתברות להשלמת הפעולה, אחוז |
about |
חוּט |
הסתברות לשינוי שיעור, באחוזים, על כל סימון |
פעולות סוחר:
תְכוּנָה |
סוּג |
תיאור |
operation |
int |
סוג עסקה (קנייה או מכירה) |
traider |
סוחר |
קישור לסוחר |
shareRate |
מחיר המניה |
קישור למחיר המניה (בהתאמה, המניה עצמה, שערה ומועד הנפקתה) |
amount |
ארוך |
מספר המניות המעורבות בעסקה |
כדי להבטיח את הייחודיות של כל דגם, נוסיף תכונה
id
מסוג
long . תכונה זו תהיה
ייחודית בתוך מופעי מודל ותזהה אותה באופן ייחודי. תכונות המתייחסות למודלים אחרים (סוחר, מניה, מחיר מניה) יכולות להשתמש באחד זה
id
כדי לזהות באופן ייחודי את המודל המתאים. מיד עולה בראש המחשבה שאנחנו יכולים להשתמש
Map<Long, Object>
כדי לאחסן נתונים כאלה, איפה
Object
המודל המקביל. עם זאת, נסה ליישם זאת בקוד בתנאים הבאים:
- גודל הנתונים עולה באופן משמעותי על כמות ה-RAM הזמין;
- גישה לנתונים צפויה מתריסר מקומות שונים;
- נדרשת היכולת לשנות ולקרוא נתונים בו זמנית;
- יש צורך להבטיח כללים להיווצרות ושלמות הנתונים;
...ותעמוד בפני משימות הדורשות כישורים וזמן מתאימים ליישום. אין צורך "להמציא את הגלגל מחדש". הרבה כבר חשבו ונכתבו עבורנו. אז נשתמש במה שכבר נבדק במהלך השנים.
אחסון נתונים ב-Java
בואו נשקול את הפעולה. ב-Java, יצרנו מחלקה ספציפית למודל זה
Share
עם שדות
name
,
changeProbability
,
startPrice
,
delta
. ושיתופים רבים אוחסנו בתור
Map<Long, Share>
, כאשר המפתח הוא מזהה ייחודי עבור כל שיתוף.
public class Share {
private String name;
private BigDecimal startPrice;
private int changeProbability;
private int delta;
}
Map<Long, Share> shares = new HashMap<>();
shares.put(1L, new Share("ibm", BigDecimal.valueOf(20.0), 15, 10));
shares.put(2L, new Share("apple", BigDecimal.valueOf(14.0), 25, 15));
shares.put(3L, new Share("google", BigDecimal.valueOf(12.0), 20, 8));
...
shares.put(50L, new Share("microsoft", BigDecimal.valueOf(17.5), 10,4 ));
כדי לגשת לקידום הרצוי לפי תעודה מזהה, השתמש בשיטה
shares.get(id)
. עבור המשימה של מציאת מניה לפי שם או מחיר, היינו עוברים בלולאה בכל הרשומות בחיפוש אחר המניה שאנו צריכים, וכן הלאה. אבל נלך בדרך אחרת ונשמור את הערכים ב-DBMS.
אחסון נתונים ב-DBMS
הבה ננסח קבוצה ראשונית של כללי אחסון נתונים עבור DBMS:
- הנתונים ב-DBMS מאורגנים בטבלאות ( TABLE ), שהן קבוצה של רשומות.
- לכל הרשומות יש אותן קבוצות של שדות. הם נקבעים בעת יצירת הטבלה.
- ניתן להגדיר את השדה לערך ברירת מחדל ( DEFAULT ).
- עבור טבלה, אתה יכול להגדיר אילוצים ( CONSTRAINT ) המתארים את הדרישות לנתונים שלה כדי להבטיח את שלמותם. ניתן לעשות זאת בשלב יצירת הטבלה ( CREATE TABLE ) או להוסיף מאוחר יותר ( ALTER TABLE ... ADD CONSTRAINT ).
- האילוץ הנפוץ ביותר :
- המפתח הראשי הוא PRIMARY (Id במקרה שלנו).
- שדה ערך ייחודי UNIQUE (VIN עבור טבלת הרכב).
- בדיקת השדה CHECK (ערך האחוז לא יכול להיות גדול מ-100). אחת ההגבלות הפרטיות על שדה היא NOT NULL או NULL , האוסרות/מאפשרות אחסון NULL בשדה טבלה.
- קישור לטבלת צד שלישי FOREIGN KEY (קישור למניה בטבלת מחירי המניה).
- אינדקס INDEX (אינדקס של שדה כדי להאיץ את החיפוש אחר ערכים בו).
- שינוי של רשומה ( INSERT , UPDATE ) לא יתרחש אם ערכי השדות שלה סותרים את ההגבלות (CONSTRAINT).
- לכל טבלה יכול להיות שדה מפתח (או כמה) שניתן להשתמש בהם לזיהוי ייחודי של רשומה. שדה כזה (או שדות, אם הם יוצרים מפתח מורכב) יוצרים את המפתח הראשי של הטבלה - PRIMARY KEY .
- המפתח הראשי מבטיח את הייחודיות של רשומה בטבלה, נוצר עליה אינדקס המעניק גישה מהירה לכל הרשומה על סמך ערך המפתח.
- קיום מפתח ראשי מקלה בהרבה על יצירת קישורים בין טבלאות. לאחר מכן, נשתמש במפתח ראשוני מלאכותי: עבור הרשומה הראשונה
id = 1
, כל רשומה עוקבת תוכנס לטבלה עם ערך המזהה מוגדל באחד. מפתח זה נקרא לעתים קרובות AutoIncrement או AutoIdentity .
למעשה, טבלת מניות:
האם ניתן להשתמש בשם המניה כמפתח במקרה זה? בגדול - כן, אבל יש אפשרות שחלק מהחברה מנפיקה מניות שונות וקוראת להן רק בשמה שלה. במקרה זה, לא תהיה יותר ייחודיות. בפועל, משתמשים במפתח ראשוני מלאכותי לעתים קרובות למדי. מסכים, שימוש בשם מלא כמפתח ייחודי בטבלה המכילה רשומות של אנשים לא יבטיח ייחודיות. כמו גם שימוש בשילוב של שם מלא ותאריך לידה.
סוגי נתונים ב-DBMS
כמו כל שפת תכנות אחרת, ל-SQL יש הקלדת נתונים. להלן סוגי הנתונים הנפוצים ביותר של SQL:
סוגי מספרים שלמים
סוג SQL |
מילים נרדפות של SQL |
התאמה ב-Java |
תיאור |
INT |
INT4, INTEGER |
java.lang.Integer |
מספר שלם של 4 בתים, -2147483648 … 2147483647 |
בולאני |
BOOL, BIT |
java.lang.Boolean |
אמת שקר |
TINYINT |
|
java.lang.Byte |
מספר שלם של 1-בתים, -128 … 127 |
SMALLINT |
INT2 |
java.lang.Short |
מספר שלם של 2 בתים, -32768 … 32767 |
BIGINT |
INT8 |
java.lang.Long |
מספר שלם של 8 בתים, -9223372036854775808 … 9223372036854775807 |
AUTO_INCREMENT |
תוֹסֶפֶת |
java.lang.Long |
מונה אינקרמנטלי ייחודי לשולחן. אם מוכנס לתוכו ערך חדש, הוא גדל באחד. הערכים שנוצרו לעולם אינם חוזרים על עצמם. |
אמיתי
סוג SQL |
מילים נרדפות של SQL |
התאמה ב-Java |
תיאור |
DECIMAL(N,M) |
דצמבר, NUMBER |
java.math.BigDecimal |
עשרוני דיוק קבוע (N ספרות שלמות ו-M ספרות חלקיות). מיועד בעיקר לעבודה עם נתונים פיננסיים. |
לְהַכפִּיל |
FLOAT8 |
java.lang.Double |
מספר ריאלי דיוק כפול (8 בתים). |
אמיתי |
FLOAT4 |
java.lang.Real |
מספר ממשי דיוק יחיד (4 בתים). |
חוּט
סוג SQL |
מילים נרדפות של SQL |
התאמה ב-Java |
תיאור |
VARCHAR(N) |
NVARCHAR |
Java.lang.String |
מחרוזת UNICODE באורך N. אורך מוגבל ל-2147483647 טוען את כל תוכן המחרוזת לזיכרון. |
תאריך ושעה
סוג SQL |
מילים נרדפות של SQL |
התאמה ב-Java |
תיאור |
זְמַן |
|
java.time.LocalTime, java.sql.Time |
זמן אחסון (עד ננו-שניות), בעת המרה ל-DATETIME, התאריך מוגדר ל-1 בינואר 1970. |
תַאֲרִיך |
|
java.time.LocalDate, java.sql.Timestamp |
אחסון תאריכים בפורמט yyyy-mm-dd, השעה מוגדרת כ-00:00 |
תאריך שעה |
חותמת זמן |
java.time.LocalDateTime, java.sql.Timestamp |
אחסון תאריך + שעה (בלי לקחת בחשבון אזורי זמן). |
אחסון של כמויות גדולות של נתונים
סוג SQL |
התאמה ב-Java |
תיאור |
כֶּתֶם |
java.io.InputStream, java.sql.Blob |
אחסון נתונים בינאריים (תמונות, קבצים...). |
CLOB |
java.io.Reader, java.sql.Clob |
אחסון נתוני טקסט גדולים (ספרים, מאמרים...), בניגוד ל-VARCHAR, טוען נתונים לזיכרון במנות. |
סגנון כתיבה של SQL
עבור שפות רבות, קיימות הנחיות עיצוב קוד. בדרך כלל, מסמכים כאלה מכילים כללים למתן שמות למשתנים, קבועים, שיטות ומבני שפה אחרים. אז, עבור Python יש PEP8, עבור
Java - Oracle Code Conventions עבור Java . נוצרו מספר סטים שונים עבור SQL, אשר מעט שונים זה מזה. בלי קשר, עליך לפתח את ההרגל לפעול לפי הכללים בעת עיצוב הקוד שלך, במיוחד אם אתה עובד בצוות. הכללים יכולים להיות, למשל, הבאים (כמובן, אתה יכול לפתח מערכת חוקים אחרת לעצמך, העיקר להיצמד אליהם בעתיד):
- מילות מפתח ומילים שמורות, כולל פקודות ואופרטורים, חייבות להיכתב באותיות גדולות: CREATE TABLE, CONSTRAINT...
- השמות של טבלאות, שדות ואובייקטים אחרים לא צריכים להתאים למילות מפתח בשפת SQL (ראה קישור בסוף המאמר), אך עשויים להכיל אותם.
- שמות הטבלה צריכים לשקף את מטרתם. הם כתובים באותיות קטנות. מילים בשם מופרדות זו מזו באמצעות קווים תחתונים. המילה בסוף חייבת להיות ברבים : סוחרים (סוחרים), שיעור_מניות (שיעור מניות).
- שמות שדות טבלה צריכים לשקף את מטרתם. הם חייבים להיות כתובים באותיות קטנות, המילים בשם חייבות להיות מעוצבות בסגנון Camel Case , ויש להשתמש במילה בסוף ביחיד : name (name), share_rates (share rate).
- שדות מפתח מלאכותיים חייבים להכיל את המילה id.
- שמות CONSTRAINT חייבים לעמוד במוסכמות מתן השמות בטבלה. עליהם לכלול גם את השדות והטבלאות המעורבים בהם, התחל בקידומת סמנטית: check_ (בדיקת ערך השדה), pk_ (מפתח ראשי), fk_ (מפתח זר), uniq_ (ייחוד שדה), idx_ (אינדקס). דוגמה: pk_traider_share_actions_id (מפתח ראשי בשדה המזהה לטבלת trader_share_actions).
- וכן הלאה, תוך כדי לימודי SQL, רשימת הכללים תתחדש/ תשתנה.
עיצוב DBMS
מיד לפני יצירת DBMS, יש לעצב אותו. הסכימה הסופית מכילה טבלאות, קבוצה של שדות, CONSTRAINT, מפתחות, תנאי ברירת מחדל לשדות, קשרים בין טבלאות וישויות מסד נתונים אחרות. באינטרנט אתה יכול למצוא הרבה מעצבים מקוונים/לא מקוונים בחינם לעיצוב DBMSs קטנים. נסה להקליד משהו כמו "מעצב מסד נתונים בחינם" במנוע חיפוש. ליישומים כאלה יש תכונות שימושיות נוספות:
- יכול ליצור פקודות SQL ליצירת DBMS.
- הצג חזותית את ההגדרות בתרשים.
- מאפשר לך להזיז טבלאות להדמיה טובה יותר.
- הצג מפתחות, אינדקסים, קשרים, ערכי ברירת מחדל וכדומה בתרשים.
- הם יכולים לאחסן מרחוק את סכימת DBMS.
לדוגמה,
dbdiffo.com מדגיש מקשים, מציג שדות לא ריקים ומונה AI (AutoIncrement) עם התווית NN:
יצירת טבלאות ב-DBMS
אז יש לנו תרשים. כעת נעבור ליצירת טבלאות (CREATE TABLE). לשם כך, רצוי שיהיו לנו נתונים ראשוניים:
- שם שולחן
- שמות וסוג שדות
- הגבלות (CONSTRAINTS) על שדות
- ערכי ברירת מחדל עבור שדות (אם זמינים)
- מפתח ראשי (PRIMARY KEY) אם זמין
- חיבורים בין טבלאות (Foreign KEY)
לא נלמד בפירוט את כל האפשרויות של הפקודה CREATE TABLE; נבחן את היסודות של SQL באמצעות הדוגמה של יצירת טבלה לסוחרים:
CREATE TABLE traiders(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
freqTiсk INTEGER NOT NULL,
cash DECIMAL(15,2) NOT NULL DEFAULT 1000,
tradingMethod INTEGER NOT NULL,
changeProbability INTEGER NOT NULL DEFAULT 50,
about VARCHAR(255) NULL
);
ALTER TABLE traiders ADD CONSTRAINT check_traiders_tradingMethod
CHECK(tradingMethod IN (1,2,3));
ALTER TABLE traiders ADD CONSTRAINT check_traiders_changeProbability
CHECK(changeProbability <= 100 AND changeProbability > 0)
בואו נסתכל מקרוב:
CREATE TABLE traiders
(תיאור שדה) - יוצר טבלה עם השם שצוין; בתיאור השדות מופרדים בפסיק. כל פקודה מסתיימת בנקודה-פסיק.
- תיאור השדה מתחיל בשם שלו, ואחריו סוג, CONSTRAINT וערך ברירת המחדל שלו.
id BIGINT AUTO_INCREMENT PRIMARY KEY
– שדה המזהה מסוג מספר שלם הוא מפתח ראשי ומונה מצטבר (עבור כל רשומה חדשה עבור שדה המזהה, ייווצר ערך שגדול באחד מזה שנוצר קודם לכן עבור טבלה זו).
cash DECIMAL(15,2) NOT NULL DEFAULT 1000
– שדה מזומן, עשרוני, 15 ספרות לפני הנקודה העשרונית ושתיים אחרי (נתונים פיננסיים, למשל, דולרים וסנט). לא יכול לקבל ערכי NULL. אם לא ניתן ערך, הוא יקבל את הערך 1000.
about VARCHAR(255) NULL
– השדה אודות, מחרוזת באורך של עד 255 תווים, יכול לקבל ערכים ריקים.
שימו לב שאנו יכולים להגדיר חלק מתנאי
CONSTRAINT לאחר יצירת הטבלה. הבה נבחן את המבנה לשינוי מבנה הטבלה והשדות שלה:
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (תנאי) באמצעות דוגמאות:
CHECK(tradingMethod IN (1,2,3))
- שדה המסחרMethod יכול לקחת רק ערכים 1,2,3
CHECK(changeProbability <= 100 AND changeProbability > 0)
- השדה changeProbability יכול לקחת ערכי מספרים שלמים בטווח שבין 1 ל-100
יחסים בין טבלאות
כדי לנתח את תיאור הקשרים בין טבלאות, הבה נבחן את היצירה של share_rates:
CREATE TABLE share_rates(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
operDate datetime NOT NULL,
share BIGINT NOT NULL,
rate DECIMAL(15,2) NOT NULL
);
ALTER TABLE share_rates ADD FOREIGN KEY (share) REFERENCES shares(id)
ניתן להגדיר קישור לערכים של טבלה אחרת באופן הבא:
ALTER TABLE
table_from_which_is_referred
ADD FOREIGN KEY
(field_which_referred)
REFERENCES
table_to_which_referenced (field_which_is_referenced) הכנס
למניות שיש לנו רשומות על מניות, לדוגמה, עבור id=50 אנו מאחסנים מניות של מיקרוסופט במחיר התחלתי של 17.5 , דלתא 20 וסיכוי לשינוי של 4%. עבור טבלת
share_rates אנו מקבלים שלושה מאפיינים עיקריים:
- עלינו לאחסן רק את הערך של מפתח ה-id מטבלת השיתופים בשדה השיתוף כדי להשתמש בו כדי לקבל את המידע הנותר (שם וכו') מטבלת השיתופים.
- אנחנו לא יכולים ליצור תעריף לקידום לא קיים. לא ניתן להכניס ערך לא קיים לשדה השיתוף (שעבורו אין רשומה בטבלת השיתופים עם המזהה הזה), מכיוון שלא תהיה התאמה בין הטבלאות.
- לא נוכל למחוק רשומת מניות במניות שעבורן נקבעים שיעורי share_rates.
שתי הנקודות האחרונות משמשות להבטיח את שלמות הנתונים המאוחסנים. אתה יכול לראות את היצירה של טבלאות SQL של האמולציה שלנו ודוגמאות של שאילתות SQL ביישום Java של מתודות של המחלקות המתאימות באמצעות הקישור למאגר github בסוף המאמר.
החלק השלישי
GO TO FULL VERSION