Help you develop a Forum design based on MySQL database _ MySQL

1. System architecture: EAvK | 'adopts the modular idea and is divided into three layers: q, n, B | QNY7fa. Data storage layer: MySQL is used to store all bbs data, including user information, article data, user emails, user messages, system data (?), Key question: database planning, whether to use files for assistance .! _ QCB [H2b. System function layer: completes base 1 of bbs. System architecture: EA vK |'
The modular approach is divided into three layers: q, n, B | Q
NY7f
A. Data storage layer: MySQL is used to store all bbs data, including user information, article data, user emails, user messages, system data (?), Key issue: database planning, whether to use files for assistance. ! _ Q "<
CB [H 2 <
B. System function layer: completes the basic functions of bbs, which consists of multiple parallel modules. it calls the mysql function down to access the database, receives and processes requests, and returns the processed results to the upper layer, returns the success or failure results and other data based on the request type. The module is highly flexible and can be easily modified. Including: V | 1Q4 [Yc
O3c "afU
** The user module processes user registration, modification of basic data, permission changes, and query of user information. E 'I K % C ;)
** The Layout module has the highest database requirements for publishing an article, reading an article, deleting an article, marking an article, and checking the read, modify, and delete permissions. VqZ. K (9
** Excellent area module, including excellent area articles, directory addition, deletion, up/down movement S] 2u | S
! _ U {| t
'% H8 pm
(?) Check the read, modify, and delete permissions. the directory structure is difficult. N 'y/': K
-RZ % ?? [RC
** Mail module, including sending new letters, reading and deleting letters, letterhead marking, and notification of new letters: U-k * 3e
** The message module includes sending, receiving, new message notification, message review, and message saving. \ I # "(w! Q
** System dynamic modules, including the current number of people on the site, are currently dynamic. due to frequent changes, such data may be better implemented using shared memory. J ": e # Apb
** In the chat module, whether the dual chat can learn from icq can be directly called by both parties. However, it may be difficult to store emails in the chat results. at the same time, it is compatible with the telnet function, when the upper-layer service layer is telnet, a dedicated module is added for processing. (.? | T4
** Does the chat room module use shared memory or database? Open the room, which contains permission issues. New functions can also be added as needed. For example, the event dashboard module does not make much sense for non-telnet terminals .....

3. Database Design] AD] O? <'V
O ~. {XT $
The key is the efficiency of MySQL. how can we reasonably allocate mysql memory, especially the K of table cache !? C-1 [~} (
Size. What happens when the system suddenly loses power? Is mysql robust? 6b] ~ K [cp %
Table name design, with a prefix indicating the type, all in lowercase (?), Example: & WOC) ^ Ia
The system database starts with s, for example, the user table: suser (what about sUSER ?), Details:} 0gv0Q #
~ ^ S = I, O
S: System table, suser, sclass wJidO_Tu _
M: User mail table, msysop, mdrangon ia "3A) 4
W: User message Table, wsysop, wdrangon mpx9 @ 4pk>
A: Layout Index Table, aLinux, acampus e \ 7*9 ~}?
B: layout article table, blinux, bcampus '{bfIHz
C: Special classification layout table, cnewboard _ cY &(_
I: base index table, ilinux, ilinux01, icampus, icampus04 g @ jk 'l
J: Excellent article table, jlinux, jcampus, k9 O5S ~ ^ U
(I? C5 * Yu
In addition, are strings or numbers used as identifiers? For example, an account named sysop, its g @; uj Or'
The id is 1. is the table of his email msysop or m00001? Similarly, for a campus version, the corresponding 5oc: % {L $
If the code is 5, will the table name of this article be bcampus or b00005? It may be easy to use the string PSf V #3
Check the error. LR73 R |;
L? E. % QRW
User information table: suser +>: B-hD ^.
X ~ Xt ['s _
Usernum int unique, // unique identifier, which can contain up to 30000 accounts. Will it be too small? \ P> N ^ n-q
Userid char [20] primary key, // keyword of sorting, id, all lower case. XLd @ on3? F
Passwd char [20], // password, which stores the encrypted ciphertext. Jr3 = I
Realid char [20], // actual id, case-insensitive. 9' xg> PS
Username char [24], // User's mud name 7b Userlevel longint, // 64 permissions? HQrV ~ P9 \ c
NUMLogins int, xas T3G
Numposts int, YuFmcPmT
Firstlogin time, BR? EE @ 3
Lastlogin time, r % 3.H
Staytime time,/* Total stay time */FD # JN3L1!
Lasthost char [32], fig % + RR
Email varchar [100], v (0 TszV
Address varchar [100], sN7 * a) 7b
; Dw * g5L)
// Do I need other data? Whether to leave a certain reserved value, and then alter table to XDHhobR
// What is the efficiency of adding new fields? T # t's? @
D! _ LxJ? W
Layout classification table: sclass {>] h *) % $
; X 4: 9J E
Classnum int unique, // classification identifier cfDb3 ,! PU
Classid char [20], // English id of the classification: computer uf8 | mvac
Classname varchar [100], // Chinese description of classification: Computer World 6} u2yx1 <
Classtable char [20], // layout table 5 pz \&
// Generally, each layout belongs to only one category. for special categories, such as the fist section, EsGx \ 0Y | k
// New layout. you can use a special table to describe aj Jjgs;
Layout table: sboard DY \ Q [= X
K | a [T (
Boardnum int unique, // layout identifier (required ?) W0 @ _: CFYa
Boardid char [20], // English name of the layout \ g. @ + = cSR
Boardname varchar [100], // Chinese name of the layout * I & h'
Boardclass char [20], // layout category {n/L} w_Z
Boardsysop varchar [100], // bamboo list] y {wUf k
Boardposts int, // Number of articles in the layout B22 [ER
Boardlevel int, // the layout's read and write permissions a3 _ = C! A7H
Indextable char [20], // name of the index table corresponding to the layout: aboardid? Rgf5 G #
Texttable char [20], // The name of the article table corresponding to the layout: bboardid? > 38 CZUP
$) ZW0oa
// Are the last two items necessary? can they be used as an inevitable ing relationship or can = (& "F \ r @ X
// Is there more flexibility? In addition, can I directly use the default QO} y _ ~ for case sensitivity issues of the layout _~ @
// Uppercase letters, 'Ava 34 S;
D "xf' @ B & ZZ
Special Classification layout table: snewboard, sstarboard) \ hbT [s %
K/bqGHYb
Boardid char [20], // layout id) _ 17O93d7W
// Is this table necessary? O ^} & o, +
QT 'wa *{
Layout Index Table: acampus, aLinux, afootball ...... 00, Viq6
V \ T7W | (
Id int, // document sequence number, which must be adjusted manually ???? DMq nEP @ <
Mark char [1], // article mark, m, g, B, d .... WqZ9Dn3l
Title varchar [100], // Article title @ ThXa"
Writer char [20], // The Author id i1 + Q @ pw
Posttime time, // posting time., & y>
Textnum longint, // The corresponding number ??? Do not adjust U/jc [j @
QhPa {~
Layout article table $ SCSI H
ZzaoFki [
Textnum longint, // Article number? C {8 = MZvM \
Textword text, // article content? V3 * wj * Bk
Pil = (% s
// Is it necessary to separate the index from the document content? In terms of efficiency, lazy flush/eJ * o ~ U &
// Is inevitable. Delete is also marked first. DFqN | I _'
D; R.O
// Whether the layout text in the user is unread or not is complex, and whether a heap of table I should be created | O ~ ^ DVD
// Can it be implemented? * Z', OZxB
// The voting function is not considered for the moment ....

4. User module design <-1cD
For the underlying database, call MySQL's c api function to modify the database, and save certain state variables (such as the user name or left to the previous layer for completion ?), The upper layer provides user management interfaces. , 3 $] 1 * K-9
+. 6 "# p" PF
Class UserManage {~ (Response>? NcMI
Private: \ d. GW9FV
Char myuserid [20]; // User id, which is empty before Login
Time logintime; // user logon time, and used to calculate the residence time Pw_ngscK
Char loginhost [20]; // The Origin site. 5Ri/^; W
Public: ZnnC % 9 ~,
Qr ^ '9 _ T
Int NewUser (char * userid, char * passwd); ^) % {= * Sx:
Create a new user and check whether the user already exists. the other information is empty. @ # tdSD ^ 'M
Set the default value for firstlogintime and permissions. Y); DKP/
Int UserLogin (char * userid, char * passwd); uxxyQz2 q
User login, password verification ,! IsP ~ Z
Int ChangePasswd (char * oldpasswd, char * newpasswd); $ | cbQ,
Change the password. the original password must be consistent. J =] y' V>
Int ChangePriData (char * newname, char * newemail ,? 8iZi $ t!
Char * newaddr); TIWNQ + t8
Change basic data, nickname, email, address .... D] t3sa) WSf
Int ModifyNumData (int addlogin, int addpost); w = ~ Yi} Dl
Modify the number of articles, the number of previous posts, and other data .... Pay attention to the call object. Yjh (~ P
Int UserLogout (); $-Q "= 7
The user exits and modifies the lastlogin, staytime, loginhost, and other y] qi \ nw5 \
# L = $] aD & e
// Common query command @ yW m7
Int QueryCommonData (const char * userid, int & loginnum, XS1i Char * username, int & postnum, 42 VZv
Time & lastlogin, char * lasthost); 31. tCQo} TU
Query basic user information. Y9 % _ ^ 7 B *
!) Rwh2Z
// Privileged command. the function determines the permission before completing the function. 'K = ZtHu 'C
Int QueryPriData (const char * userid, char * email, @ AL | I> mV
Char * addr); = 8kY1o]
Query basic information. ordinary people can only query themselves. they have the privilege to query other people. G k =] R; Fq _
Int ModifyUserLevel (BOOL isAdd, unsigned long level); zi \ N> M <
Modify user permissions, lg7jZJA \
Int ModifyUserId (char * oldid, char * newid); m? [~, Pg =
Char * newemail, char * newaddr); 8 TB ~ ^ \ 8U)
Modify basic user data. TKesJ. U
Int ModifyUserNumdata (char * userid, int addlogin, int addpost); TjoEGoTf, <
Modify the number of articles and other data. F = )!. U; <
Int ModifyUsERPasswd (char * userid, char * newpasswd); Xr #: @ 8Y;
Modify the user password. "5' $ Bs & =
} Mdzn! %. F6
5 Pc #4 CWUP
The preceding functions are not difficult. they all execute corresponding SQL statements and access the mysql database. do you want to classify general commands into privileged commands? Is the permission check on this layer or the previous layer? YyyhM9 * "YR
This focuses more on the program's clarity or code's conciseness. maybe you should check the code. after all, you should consider the access traffic. In addition, should the upper-layer service layer also consider permission check issues? WvK @ GL?
5. Layout module design 1 XZyP [Ik
| [ZXh] TI
The so-called classification is more important for the telnet Server. in cq66 mode, users can classify the data according to their own wishes. In the end, the data is directly accessed based on the version. C % 8? ": BQ
For access to layout articles, the entire article is stored as a parameter. the sections of the article are completed by the current layer. if the upper layer is transmitted in blocks, all sections are transmitted at the upper layer. after the combination, then pass the parameter to the current layer for decomposition. during reading, the current layer accesses data in blocks. if the upper layer accesses data in full text, merge the data at the upper layer, this layer does not matter. I. _} jCOC1U
Whether or not to independently produce indexes does not affect upper-layer operations. it is mainly related to the structure of lower-layer databases and mainly considers feasibility and efficiency requirements. * Z: 2fk>
Where can I check permissions? Put it on the upper layer. In fact, the telnet server and the cq66 client won't display the menu of special commands to the general user. of course, the user can directly send the command of cq66, the server still needs to check. But you do not need to check it again at the function module layer below it. : R6lJb: 48
Tv5VSP. I
Class BoardManage {1f> 6sl
Private: u4P {-"~ '
R3 WIn.de
Public: R % 0 LmFoK
// Category c | $ &-$! 7
Int GetClassNameInfo (int maxclass, char ** classid, R/iz # mLX
Char ** classname); yGFZ) IIw
Returns the category information, which is a Chinese name. W7 | oo # 9azg
Int GetBoardName (int maxboards, char * classid, _ H '3nn7g =
Char ** boardname); uF & HM +
Returns the layout information of a category. generally, select... 'Ty s8 ^ D directly.
From sboard I/pp? EpU
Where boardclass =... for special classification, query the corresponding table .... Y7bj-
>:\ ZTsV"
// Modify the privilege d \ l above the layout administrator! MOGL
Int NewClass (char * newclassname, int type); '% [j, Z #
New category, common or special category, 2MNg \ rbp
Int DeleteClass (char * newclassname); lyl: a]
This layer is not responsible for consistency. the upper layer is responsible for the Rc <4aU = o,
The category information of the corresponding layout is changed to another one. The category name is also deleted and then created, 4 | 6 = L
Int AddClassBoard (const char * classname, char * newboardname );'/! \! * Gz | N
Add an existing version to a specific category. for a special category, ^ aHBtr:
The effect is the same as that of modifyboardinfo. % 0' Y? % S
Int DeleteClassBoard (const char * classname, char * boardname); UYTZw9-^ O
Deleting a version from a category is also for a special category. for a general category, the effect is also $ | SWF = Q
It is the same as modifyboardinfo. the classification attribute of a version can be null, that is, it is not 6 ^ {n! I & q | {
In any category. 4} BAXm? F
// Operations on version information. R (1 v
Int NewBoard (const char * boardid, char * boardname); FWvL'
Create a new version and create a corresponding table. For other parameters, use the default value. 2 n f) I
Int DeleteBoard (const char * boardid); bopeof8
Delete a version and delete the corresponding table. ~ 2Ap? Gwoax
Int GetBoardInfo (const char * boardid, char * boardname, GdPMT | ^
Int & numposts, char * masters, char * class, \ _ 0 M
Long & level); = kU9A *?
The layout information. YGp {"
Int ModifyBoardId (const char * oldid, char * newid); G' _ ca
Change the English version id, and the corresponding table name, 7-* gd 8Xd}
Int ModifyBoardInfo (const char * boardid, char * boardname, IJV (2 # * Y
Int numposts, char * masters, char * class, # + @ tDx =
Long level); H [8nq {0 @
Privilege is required to modify layout information. JVYv + 9pk
ObX % <,/
// Operations on the layout. IrGM_mh %-
Int AddText (char * boardid, char * title, char * writer,) bJ] 5f 9
Char * text); _ M WRO
Add an article to the layout and divide the long article into 2 k blocks. Fmi3E2N
Int DeleteText (char * boardid, int num); B 'UdWdjr0 (
Deleting an article only makes a tag and does not immediately modify the corresponding table. OohU ?! 7; OP
Int FlushTable (char * boardid); % | 3AMv; p
Refresh the layout and delete the corresponding records of the deleted article. NAiOE (B
Int MarkText (char * boardid, int num, char mark); DmA {jNn1 <
Mark the article. O? U31}
Int ModifyTitle (char * boardid, int num, char * newtitle); '; OR. L <63
Modify the title of an article. ZdV "8!
Int ModifyText (char * boardid, int num, char * newtext); 78j} j! H1
Modifying the content of an article does not require the privilege of your own article. XFc & G Int GetTextInfo (const char * boardid, int num, char * title, DR8xyAy _
Char * writer, char & mark); 0' RcHOk
Obtain the title information of an article. + P7] T (L: k
Int GetText (const char * boardid, int num, int block, qCI; k {Eq
Char * text); D "(*} K
Reads the content of an article, in blocks. Vtr> w f] yn
Hpy e6AA
// Query for articles and authors 61 "\ x % I
// Return all query results at one time? A1 "~ VL
Int QueryWriter (const char * boardid, char * writer, Char ** result); c XcE.
Query an author's article on the layout. Zm | sq ~ VPb:
Int QueryTitle (const char * boardid, char * title, lOs6A} G
Char ** result); Y, ^ RVl $ B
On the query page, the title contains the specified content. GA @ 'JD> H
} OR $? W Q
Kx/_ [yN
Parameter passing is a very annoying thing. from an abstract point of view, we want to return data with> v/OoO! I
It is irrelevant to the underlying layer, so it should be processed, but from the efficiency point of view, data is not expected to be processed multiple times} Zc2Q/9
Replication, on the other hand, whether the application for space release is completed in the upper layer or in the current layer.: o) _: oc
What about it? Accidentally, memory errors may easily occur.