1/* If you add/change anything, update 2 boinc_db.cpp,h 3 and if needed: 4 py/Boinc/ 5 database.py 6 html/ 7 inc/ 8 host.inc (host) 9 db_ops.inc 10 ops/ 11 db_update.php 12 user/ 13 create_account_action.php (user) 14 team_create_action.php (team) 15 sched/ 16 db_dump.cpp (host, user, team) 17 db_purge.cpp (workunit, result) 18*/ 19/* Fields are documented in boinc_db.h */ 20/* Do not replace this with an automatically generated schema */ 21 22/* type is specified as InnoDB for most tables. 23 Supposedly this gives better performance. 24 The others (post, thread, profile) are myISAM 25 because it supports fulltext index 26*/ 27 28/* fields ending with id (but not _id) are treated specially 29 by the Python code (db_base.py) 30*/ 31 32create table platform ( 33 id integer not null auto_increment, 34 create_time integer not null, 35 name varchar(254) not null, 36 user_friendly_name varchar(254) not null, 37 deprecated tinyint not null default 0, 38 primary key (id) 39) engine=InnoDB; 40 41create table app ( 42 id integer not null auto_increment, 43 create_time integer not null, 44 name varchar(254) not null, 45 min_version integer not null default 0, 46 deprecated smallint not null default 0, 47 user_friendly_name varchar(254) not null, 48 homogeneous_redundancy smallint not null default 0, 49 weight double not null default 1, 50 beta smallint not null default 0, 51 target_nresults smallint not null default 0, 52 min_avg_pfc double not null default 1, 53 host_scale_check tinyint not null default 0, 54 homogeneous_app_version tinyint not null default 0, 55 non_cpu_intensive tinyint not null default 0, 56 locality_scheduling integer not null default 0, 57 n_size_classes smallint not null default 0, 58 fraction_done_exact tinyint not null default 0, 59 primary key (id) 60) engine=InnoDB; 61 62create table app_version ( 63 id integer not null auto_increment, 64 create_time integer not null, 65 appid integer not null, 66 version_num integer not null, 67 platformid integer not null, 68 xml_doc mediumblob, 69 min_core_version integer not null default 0, 70 max_core_version integer not null default 0, 71 deprecated tinyint not null default 0, 72 plan_class varchar(254) not null default '', 73 pfc_n double not null default 0, 74 pfc_avg double not null default 0, 75 pfc_scale double not null default 0, 76 expavg_credit double not null default 0, 77 expavg_time double not null default 0, 78 beta tinyint not null default 0, 79 primary key (id) 80) engine=InnoDB; 81 82create table user ( 83 id integer not null auto_increment, 84 create_time integer not null, 85 email_addr varchar(254) not null, 86 name varchar(254), 87 authenticator varchar(254), 88 country varchar(254), 89 postal_code varchar(254), 90 total_credit double not null, 91 expavg_credit double not null, 92 expavg_time double not null, 93 global_prefs blob, 94 project_prefs blob, 95 teamid integer not null, 96 venue varchar(254) not null, 97 url varchar(254), 98 send_email smallint not null, 99 show_hosts smallint not null, 100 posts smallint not null, 101 -- reused: salt for weak auth 102 seti_id integer not null, 103 seti_nresults integer not null, 104 seti_last_result_time integer not null, 105 seti_total_cpu double not null, 106 signature varchar(254), 107 -- deprecated 108 has_profile smallint not null, 109 cross_project_id varchar(254) not null, 110 passwd_hash varchar(254) not null, 111 email_validated smallint not null, 112 donated smallint not null, 113 primary key (id) 114) engine=InnoDB; 115 116create table team ( 117 id integer not null auto_increment, 118 create_time integer not null, 119 userid integer not null, 120 name varchar(254) not null, 121 name_lc varchar(254), 122 url varchar(254), 123 type integer not null, 124 name_html varchar(254), 125 description text, 126 nusers integer not null, /* temp */ 127 country varchar(254), 128 total_credit double not null, /* temp */ 129 expavg_credit double not null, /* temp */ 130 expavg_time double not null, 131 seti_id integer not null, 132 ping_user integer not null default 0, 133 ping_time integer unsigned not null default 0, 134 joinable tinyint not null default 1, 135 mod_time timestamp default current_timestamp on update current_timestamp, 136 primary key (id) 137) engine=MyISAM; 138 139create table host ( 140 id integer not null auto_increment, 141 create_time integer not null, 142 userid integer not null, 143 rpc_seqno integer not null, 144 rpc_time integer not null, 145 total_credit double not null, 146 expavg_credit double not null, 147 expavg_time double not null, 148 149 timezone integer not null, 150 domain_name varchar(254), 151 serialnum varchar(254), 152 /* now used to encode stuff related to GPUs and VBox */ 153 last_ip_addr varchar(254), 154 nsame_ip_addr integer not null, 155 156 on_frac double not null, 157 connected_frac double not null, 158 active_frac double not null, 159 cpu_efficiency double not null, 160 duration_correction_factor double not null, 161 p_ncpus integer not null, 162 p_vendor varchar(254), 163 p_model varchar(254), 164 p_fpops double not null, 165 p_iops double not null, 166 p_membw double not null, 167 168 os_name varchar(254), 169 os_version varchar(254), 170 171 m_nbytes double not null, 172 m_cache double not null, 173 m_swap double not null, 174 175 d_total double not null, 176 d_free double not null, 177 d_boinc_used_total double not null, 178 d_boinc_used_project double not null, 179 d_boinc_max double not null, 180 181 n_bwup double not null, 182 n_bwdown double not null, 183 184 credit_per_cpu_sec double not null, 185 venue varchar(254) not null, 186 nresults_today integer not null, 187 avg_turnaround double not null, 188 host_cpid varchar(254), 189 external_ip_addr varchar(254), 190 max_results_day integer not null, 191 error_rate double not null default 0, 192 product_name varchar(254) not null, 193 gpu_active_frac double not null, 194 p_ngpus integer not null, 195 p_gpu_fpops double not null, 196 197 primary key (id) 198) engine=InnoDB; 199 200-- see comments in boinc_db.h 201create table host_app_version ( 202 host_id integer not null, 203 app_version_id integer not null, 204 pfc_n double not null, 205 pfc_avg double not null, 206 et_n double not null, 207 et_avg double not null, 208 et_var double not null, 209 et_q double not null, 210 max_jobs_per_day integer not null, 211 n_jobs_today integer not null, 212 turnaround_n double not null, 213 turnaround_avg double not null, 214 turnaround_var double not null, 215 turnaround_q double not null, 216 consecutive_valid integer not null 217) engine = InnoDB; 218 219/* 220 * Only information needed by the server or other backend components 221 * is broken out into separate fields. 222 * Other info, i.e. that needed by the client (files, etc.) 223 * is stored in the XML doc 224 */ 225create table workunit ( 226 id integer not null auto_increment, 227 create_time integer not null, 228 appid integer not null, 229 name varchar(254) not null, 230 xml_doc blob, 231 batch integer not null, 232 rsc_fpops_est double not null, 233 rsc_fpops_bound double not null, 234 rsc_memory_bound double not null, 235 rsc_disk_bound double not null, 236 need_validate smallint not null, 237 canonical_resultid integer not null, 238 canonical_credit double not null, 239 transition_time integer not null, 240 delay_bound integer not null, 241 error_mask integer not null, 242 file_delete_state integer not null, 243 assimilate_state integer not null, 244 hr_class integer not null, 245 opaque double not null, 246 min_quorum integer not null, 247 target_nresults integer not null, 248 max_error_results integer not null, 249 max_total_results integer not null, 250 max_success_results integer not null, 251 result_template_file varchar(63) not null, 252 priority integer not null, 253 mod_time timestamp default current_timestamp on update current_timestamp, 254 rsc_bandwidth_bound double not null, 255 fileset_id integer not null, 256 app_version_id integer not null, 257 transitioner_flags tinyint not null, 258 size_class smallint not null default -1, 259 primary key (id) 260) engine=InnoDB; 261 262create table result ( 263 id integer not null auto_increment, 264 create_time integer not null, 265 workunitid integer not null, 266 server_state integer not null, 267 outcome integer not null, 268 client_state integer not null, 269 hostid integer not null, 270 userid integer not null, 271 report_deadline integer not null, 272 sent_time integer not null, 273 received_time integer not null, 274 name varchar(254) not null, 275 cpu_time double not null, 276 xml_doc_in blob, 277 xml_doc_out blob, 278 stderr_out blob, 279 batch integer not null, 280 file_delete_state integer not null, 281 validate_state integer not null, 282 claimed_credit double not null, 283 granted_credit double not null, 284 opaque double not null, 285 random integer not null, 286 app_version_num integer not null, 287 appid integer not null, 288 exit_status integer not null, 289 teamid integer not null, 290 priority integer not null, 291 mod_time timestamp default current_timestamp on update current_timestamp, 292 elapsed_time double not null, 293 flops_estimate double not null, 294 app_version_id integer not null, 295 runtime_outlier tinyint not null, 296 size_class smallint not null default -1, 297 peak_working_set_size double not null, 298 peak_swap_size double not null, 299 peak_disk_usage double not null, 300 primary key (id) 301) engine=InnoDB; 302 303create table batch ( 304 id serial primary key, 305 user_id integer not null, 306 create_time integer not null, 307 logical_start_time double not null, 308 logical_end_time double not null, 309 est_completion_time double not null, 310 njobs integer not null, 311 fraction_done double not null, 312 nerror_jobs integer not null, 313 state integer not null, 314 completion_time double not null, 315 credit_estimate double not null, 316 credit_canonical double not null, 317 credit_total double not null, 318 name varchar(255) not null, 319 app_id integer not null, 320 project_state integer not null, 321 description varchar(255) not null, 322 expire_time double not null 323) engine = InnoDB; 324 325-- permissions for job submission 326-- 327create table user_submit ( 328 user_id integer not null, 329 quota double not null, 330 logical_start_time double not null, 331 submit_all tinyint not null, 332 -- can submit jobs to any app 333 manage_all tinyint not null, 334 -- manager privileges for all apps 335 -- grant/revoke permissions (except manage), change quotas 336 -- create apps 337 max_jobs_in_progress integer not null, 338 primary key (user_id) 339) engine = InnoDB; 340 341-- (user, app) submit permissions 342-- The existence of the record implies permission to submit jobs 343-- 344create table user_submit_app ( 345 user_id integer not null, 346 app_id integer not null, 347 manage tinyint not null, 348 -- can 349 -- create/deprecated app versions of this app 350 -- grant/revoke permissions (except admin) this app 351 -- abort their jobs 352 primary key (user_id, app_id) 353) engine = InnoDB; 354 355-- Record files (created by remote file mgt) present on server. 356-- 357create table job_file ( 358 id integer not null auto_increment, 359 name varchar(255) not null, 360 create_time double not null, 361 delete_time double not null, 362 primary key (id) 363) engine = InnoDB; 364 365-- the following are used to implement trickle messages 366 367create table msg_from_host ( 368 id integer not null auto_increment, 369 create_time integer not null, 370 hostid integer not null, 371 variety varchar(254) not null, 372 handled smallint not null, 373 xml mediumtext, 374 primary key (id) 375) engine=InnoDB; 376 377create table msg_to_host ( 378 id integer not null auto_increment, 379 create_time integer not null, 380 hostid integer not null, 381 variety varchar(254) not null, 382 handled smallint not null, 383 xml mediumtext, 384 primary key (id) 385) engine=InnoDB; 386 387-- An assignment of a WU to a specific host, user, or team, or to all hosts 388-- 389create table assignment ( 390 id integer not null auto_increment, 391 create_time integer not null, 392 target_id integer not null, 393 -- ID of target entity (see below) 394 target_type integer not null, 395 -- 0=none, 1=host, 2=user, 3=team 396 multi tinyint not null, 397 -- 0=normal replication, 1=all hosts in set 398 workunitid integer not null, 399 resultid integer not null, 400 -- if not multi, the result 401 -- deprecated 402 primary key (id) 403) engine = InnoDB; 404 405-- EVERYTHING FROM HERE ON IS USED ONLY FROM PHP, 406-- SO NOT IN BOINC_DB.H ETC. 407 408-- user profile (description, pictures) 409-- 410create table profile ( 411 userid integer not null, 412 language varchar(254), 413 response1 text, 414 response2 text, 415 has_picture smallint not null, 416 recommend integer not null, 417 reject integer not null, 418 posts integer not null, 419 uotd_time integer, 420 verification integer not null, 421 -- UOD screening status: -1 denied, 0 unrated, 1 approved 422 primary key (userid) 423) engine=MyISAM; 424 425-- message board category 426-- help desk is a group of categories that are handled separately 427-- 428create table category ( 429 id integer not null auto_increment, 430 orderID integer not null, 431 -- order in which to display 432 lang integer not null, 433 -- not used 434 name varchar(254) binary, 435 is_helpdesk smallint not null, 436 primary key (id) 437) engine=InnoDB; 438 439-- message board topic 440-- 441create table forum ( 442 id integer not null auto_increment, 443 category integer not null, 444 -- ID of entity to which this forum is attached. 445 -- The type (table) of the entity is determined by parent_type 446 orderID integer not null, 447 title varchar(254) not null, 448 description varchar(254) not null, 449 timestamp integer not null default 0, 450 -- time of last new or modified thread or post 451 threads integer not null default 0, 452 -- number of non-hidden threads in forum 453 posts integer not null default 0, 454 rate_min_expavg_credit integer not null default 0, 455 rate_min_total_credit integer not null default 0, 456 post_min_interval integer not null default 0, 457 post_min_expavg_credit integer not null default 0, 458 post_min_total_credit integer not null default 0, 459 is_dev_blog tinyint not null default 0, 460 parent_type integer not null default 0, 461 -- entity type to which this forum is attached: 462 -- 0 == category (public) 463 -- 1 == team 464 -- 2 == group 465 primary key (id) 466) engine=InnoDB; 467 468-- threads in a topic (or questions) 469-- 470create table thread ( 471 id integer not null auto_increment, 472 forum integer not null, 473 owner integer not null, 474 -- user ID of creator 475 status integer not null, 476 -- whether a question has been answered 477 -- News forum: if set, don't export as notice 478 title varchar(254) not null, 479 timestamp integer not null, 480 -- time of last new or modified post 481 views integer not null, 482 -- number of times this has been viewed 483 replies integer not null, 484 -- number of non-hidden posts in thread, not counting the initial one 485 activity double not null, 486 -- for questions: number of askers / time since asked 487 -- (set periodically by update_forum_activity.php) 488 sufferers integer not null, 489 -- in help desk: # people who indicated they had same problem 490 score double not null, 491 votes integer not null, 492 create_time integer not null, 493 -- when this record was created 494 hidden integer not null, 495 -- nonzero if hidden by moderators 496 sticky tinyint not null default 0, 497 locked tinyint not null default 0, 498 primary key (id) 499) engine=MyISAM; 500 501-- postings in a thread (or answers) 502-- Each thread has an initial post 503-- 504create table post ( 505 id integer not null auto_increment, 506 thread integer not null, 507 user integer not null, 508 timestamp integer not null, 509 -- create time 510 content text not null, 511 modified integer not null, 512 -- when last modified 513 parent_post integer not null, 514 -- post that was replied to, if any 515 score double not null, 516 votes integer not null, 517 signature tinyint not null default 0, 518 hidden integer not null, 519 -- nonzero if hidden by moderators 520 primary key (id) 521) engine=MyISAM; 522 523-- subscription to a thread 524-- 525create table subscriptions ( 526 userid integer not null, 527 threadid integer not null, 528 notified_time integer not null default 0 529 -- deprecated 530) engine=InnoDB; 531 532-- actually: prefs for all community features 533-- 534create table forum_preferences ( 535 userid integer not null default 0, 536 signature varchar(254) not null default '', 537 posts integer not null default 0, 538 last_post integer not null, 539 avatar varchar(254) not null default '', 540 hide_avatars tinyint not null default 0, 541 forum_sorting integer not null, 542 thread_sorting integer not null, 543 no_signature_by_default tinyint not null default 1, 544 images_as_links tinyint not null default 0, 545 link_popup tinyint not null default 0, 546 mark_as_read_timestamp integer not null default 0, 547 special_user char(12) not null default '0', 548 jump_to_unread tinyint not null default 1, 549 hide_signatures tinyint not null default 0, 550 rated_posts varchar(254) not null, 551 low_rating_threshold integer not null default -25, 552 -- deprecated 553 high_rating_threshold integer not null default 5, 554 -- deprecated 555 minimum_wrap_postcount integer DEFAULT 100 NOT NULL, 556 display_wrap_postcount integer DEFAULT 75 NOT NULL, 557 ignorelist varchar(254) not null, 558 ignore_sticky_posts tinyint not null default 0, 559 banished_until integer not null default 0, 560 pm_notification tinyint not null default 0, 561 -- actually controls all notifications. 562 -- 0 = no email 563 -- 1 = email per event 564 -- 2 = digest email 565 highlight_special tinyint not null default 1, 566 primary key (userid) 567) engine=MyISAM; 568 569-- keep track of last time a user read a thread 570create table forum_logging ( 571 userid integer not null default 0, 572 threadid integer not null default 0, 573 timestamp integer not null default 0, 574 primary key (userid,threadid) 575) engine=MyISAM; 576 577create table post_ratings ( 578 post integer not null, 579 user integer not null, 580 rating tinyint not null, 581 primary key(post, user) 582) engine=MyISAM; 583 584create table sent_email ( 585 userid integer not null, 586 time_sent integer not null, 587 email_type smallint not null, 588 -- 0 = other 589 -- 1 = newsletter 590 -- 2 = lapsed reminder 591 -- 3 = failed reminder 592 -- 4 = forum post hide 593 -- 5 = forum ban 594 -- 6 = fundraising appeal 595 primary key(userid) 596) engine=MyISAM; 597 598create table private_messages ( 599 id integer not null auto_increment, 600 userid integer not null, 601 senderid integer not null, 602 date integer not null, 603 opened tinyint not null default 0, 604 subject varchar(255) not null, 605 content text not null, 606 primary key(id), 607 key userid (userid) 608) engine=MyISAM; 609 610create table credited_job ( 611 userid integer not null, 612 workunitid bigint not null 613) engine=MyISAM; 614 615create table donation_items ( 616 id integer not null auto_increment, 617 item_name varchar(32) not null, 618 title varchar(255) not null, 619 description varchar(255) not null, 620 required double not null default '0', 621 PRIMARY KEY(id) 622) engine=MyISAM; 623 624create table donation_paypal ( 625 id integer not null auto_increment, 626 order_time integer not null, 627 userid integer not null, 628 email_addr varchar(255) not null, 629 order_amount double(6,2) not null, 630 processed tinyint not null default '0', 631 payment_time integer not null, 632 item_name varchar(255) not null, 633 item_number varchar(255) not null, 634 payment_status varchar(255) not null, 635 payment_amount double(6,2) not null, 636 payment_fee double(5,2) default null, 637 payment_currency varchar(255) not null, 638 txn_id varchar(255) not null, 639 receiver_email varchar(255) not null, 640 payer_email varchar(255) not null, 641 payer_name varchar(255) not null, 642 PRIMARY KEY(id) 643) engine=MyISAM; 644 645-- record changes in team membership 646create table team_delta ( 647 userid integer not null, 648 teamid integer not null, 649 timestamp integer not null, 650 joining tinyint not null, 651 total_credit double not null 652) engine=MyISAM; 653 654-- tables for moderator banishment votes 655create table banishment_vote ( 656 id serial primary key, 657 userid integer not null, 658 modid integer not null, 659 start_time integer not null, 660 end_time integer not null 661) engine=MyISAM; 662 663create table banishment_votes ( 664 id serial primary key, 665 voteid integer not null, 666 modid integer not null, 667 time integer not null, 668 yes tinyint not null 669) engine=MyISAM; 670 671create table team_admin ( 672 teamid integer not null, 673 userid integer not null, 674 create_time integer not null, 675 rights integer not null 676) engine=MyISAM; 677 678-- A friendship request. 679-- The friendship exists if (x,y) and (y,x) 680create table friend ( 681 user_src integer not null, 682 -- initiator 683 user_dest integer not null, 684 -- target 685 message varchar(255) not null, 686 create_time integer not null, 687 reciprocated tinyint not null 688 -- whether the reciprocal exists 689); 690 691-- a notification of something, e.g. 692-- a friend request or confirmation 693-- a post in a subscribed thread 694-- a personal message 695-- These records are deleted when the user acts on them 696create table notify ( 697 id serial primary key, 698 userid integer not null, 699 -- destination of notification 700 create_time integer not null, 701 type integer not null, 702 opaque integer not null 703 -- some other ID, e.g. that of the thread, user or PM record 704); 705 706create table badge ( 707 id serial primary key, 708 create_time double not null, 709 type tinyint not null, 710 -- 0=user, 1=team 711 name varchar(255) not null, 712 -- internal use (not visible to users) 713 title varchar(255) not null, 714 -- user-visible, short 715 description varchar(255) not null, 716 -- user-visible, possibly longer 717 image_url varchar(255) not null, 718 -- location of image 719 level varchar(255) not null, 720 -- project-defined 721 tags varchar(255) not null, 722 -- project-defined 723 sql_rule varchar(255) not null 724); 725 726create table badge_user ( 727 badge_id integer not null, 728 user_id integer not null, 729 create_time double not null, 730 reassign_time double not null 731); 732 733create table badge_team ( 734 badge_id integer not null, 735 team_id integer not null, 736 create_time double not null, 737 reassign_time double not null 738); 739 740create table credit_user ( 741 userid integer not null, 742 appid integer not null, 743 njobs integer not null, 744 total double not null, 745 expavg double not null, 746 expavg_time double not null, 747 credit_type integer not null, 748 primary key (userid, appid, credit_type) 749) engine=InnoDB; 750 751create table credit_team ( 752 teamid integer not null, 753 appid integer not null, 754 njobs integer not null, 755 total double not null, 756 expavg double not null, 757 expavg_time double not null, 758 credit_type integer not null, 759 primary key (teamid, appid, credit_type) 760) engine=InnoDB; 761