Poniższy kod tworzy tabele występujące w naszym projekcie, jednakże dzięki zaimplementowaniu serwisu w frameworku Ruby on Rails nie istnieje potrzeba tworzenia schematu tabel w języku SQL, gdyż piszemy go w języku Ruby. Najpierw jednak przedstawimy kod w języku SQL, potem w języku Ruby. Dzięki konwencją stosowanym w frameworku Ruby on Rails kod jest znaczenie krótszy, np. jedną z konwencji jest automatycznie generowany klucz główny, a co ważniejsze kod aplikacji jest niezależny od bazy danych:

• Tabela groups.
  • SQL

    CREATE TABLE "groups"(
    "groups_id" Serial NOT NULL,
    "group_name" Character varying(100) NOT NULL,
    PRIMARY KEY ("groups_id")
    );

  • Ruby

    create_table :groups do |t|
    t.string :group_name, :null => false, :default => "", :limit => 100
    end

• Tabela users
  • SQL

    CREATE TABLE "users"(
    "users_id" Serial NOT NULL,
    "groups_id" Integer NOT NULL,
    "languages_id" Integer NOT NULL,
    "phone" Character varying(100) NOT NULL,
    "im" Character varying(100) NOT NULL,
    "nickname" Character varying(100) NOT NULL,
    "email" Character varying(100) NOT NULL,
    "rank" Integer DEFAULT 0 NOT NULL,
    "gender" Character varying(256) DEFAULT Men NOT NULL,
    "hashed_password" Character varying(256) NOT NULL,
    "salt" Character varying(256) NOT NULL,
    "avatar" Bytea,
    "timestamps" Timestamp,
    PRIMARY KEY ("users_id")
    );

    ALTER TABLE "users" 
    ADD CONSTRAINT "Relationship1" FOREIGN KEY ("groups_id") REFERENCES "groups" ("groups_id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;
    
    ALTER TABLE "users" 
    ADD CONSTRAINT "Relationship2" FOREIGN KEY ("languages_id") REFERENCES "languages" ("languages_id")
     ON DELETE NO ACTION
     ON UPDATE NO ACTION;

  • Ruby

    create_table :users do |t|
    t.string :phone, :im, :nickname, :email, :null => false, :default => "", :limit => 100
    t.integer :rank, :null => false, :default => 0
    t.references :language, :null => false, :default => 1
    t.references :group, :null => false, :default => 3
    t.binary :avatar, :null => true
    t.string :gender, :default => "Men"
    t.string :hashed_password, :salt , :null => false
    t.timestamps
    end

• Tabela languages
  • SQL

    CREATE TABLE "languages"(
    "languages_id" Serial NOT NULL,
    "languages_name" Character varying(100) NOT NULL,
    PRIMARY KEY ("languages_id")
    );

  • Ruby

    create_table :languages do |t|
    t.string :language_name, :null => false, :default => "", :limit => 100
    end

• Tabela threats
  • SQL

    CREATE TABLE "threats"(
    "threats_id" Serial NOT NULL,
    "threat_types_id" Integer NOT NULL,
    "description" Text NOT NULL,
    "rank" Integer DEFAULT 0 NOT NULL,
    "plus" Integer DEFAULT 0 NOT NULL,
    "minus" Integer DEFAULT 0 NOT NULL,
    "extra_plus" Integer DEFAULT 0 NOT NULL,
    "extra_minus" Integer DEFAULT 0 NOT NULL,
    "place" Point NOT NULL,
    "created_at" Timestamp NOT NULL,
    "updated_at" Timestamp NOT NULL,
    PRIMARY KEY ("threats_id")
    );

    ALTER TABLE "threats" 
    ADD CONSTRAINT "Relationship3" FOREIGN KEY ("threat_types_id") REFERENCES "threat_types" ("threat_types_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

  • Ruby

    create_table :threats do |t|
    t.text :description, :null => false, :default => ""
    t.integer :rank, :plus, :minus, :extra_plus, :extra_minus, :null => false, :default => 0
    t.references :threat_type, :null => false
    t.point :place, :null => false
    t.timestamps
    end

• Tabela threat_types
  • SQL

    CREATE TABLE "threat_types"(
    "threat_types_id" Serial NOT NULL,
    "threat_name" Character varying(100) NOT NULL,
    PRIMARY KEY ("threat_types_id")
    );

  • Ruby

    create_table :threat_types do |t|
    t.string :threat_name, :null => false, :default => "", :limit => 100
    end

• Tabela comments
  • SQL

    CREATE TABLE "comments"(
    "comments_id" Serial NOT NULL,
    "threats_id" Integer NOT NULL,
    "users_id" Integer NOT NULL,
    "desciption" Text NOT NULL,
    "created_at" Timestamp NOT NULL,
    "updated_at" Timestamp NOT NULL,
    PRIMARY KEY ("comments_id")
    );

    ALTER TABLE "comments" 
    ADD CONSTRAINT "Relationship4" FOREIGN KEY ("threats_id") REFERENCES "threats" ("threats_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;
    
    ALTER TABLE "comments" 
    ADD CONSTRAINT "Relationship5" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

  • Ruby

    create_table :comments do |t|
    t.text :description, :null => false, :default => ""
    t.references :user, :threat, :null => false
    t.timestamps
    end

• Tabela notifications
  • SQL

    CREATE TABLE "notifications"(
    "notifications_id" Serial NOT NULL,
    "users_id" Integer NOT NULL,
    "threats_id" Integer NOT NULL,
    "notified_at" Timestamp NOT NULL,
    "description" Text NOT NULL,
    PRIMARY KEY ("notifications_id")
    );

    ALTER TABLE "notifications" 
    ADD CONSTRAINT "Relationship9" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;
    
    ALTER TABLE "notifications" 
    ADD CONSTRAINT "Relationship10" FOREIGN KEY ("threats_id") REFERENCES "threats" ("threats_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

  • Ruby

    create_table :notifications do |t|
    t.references :user, :threat, :null => false
    t.datetime :notified_at, :accepted_at, :null => false
    t.text :description, :null => false, :default => ""
    end

• Tabela pictures
  • SQL

    CREATE TABLE "pictures"(
    "pictures_id" Serial NOT NULL,
    "users_id" Integer NOT NULL,
    "threats_id" Integer NOT NULL,
    "name" Character varying(100) NOT NULL,
    "description" Text NOT NULL,
    "file" Bytea NOT NULL,
    "created_at" Timestamp NOT NULL,
    PRIMARY KEY ("pictures_id")
    );

    ALTER TABLE "pictures" 
    ADD CONSTRAINT "Relationship6" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;
    
    ALTER TABLE "pictures" 
    ADD CONSTRAINT "Relationship7" FOREIGN KEY ("threats_id") REFERENCES "threats" ("threats_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

• Ruby

  create_table :pictures do |t|
  t.string :name, :null => false, :default => "", :limit => 100
  t.text :description, :null => false, :default => ""
  t.references :user, :threat, :null => false
  t.binary :file, :null => false
  t.datetime :created_at, :null => false
  end

• Tabela area_alerts
  • SQL

    CREATE TABLE "area_alerts"(
    "area_alerts_id" Serial NOT NULL,
    "users_id" Integer NOT NULL,
    "description" Text NOT NULL,
    "sms" Boolean DEFAULT true NOT NULL,
    "im" Boolean DEFAULT true NOT NULL,
    "email" Boolean DEFAULT true NOT NULL,
    "area" Polygon NOT NULL,
    "created_at" Timestamp NOT NULL,
    "updated_at" Timestamp NOT NULL,
    PRIMARY KEY ("area_alerts_id")
    );

    ALTER TABLE "area_alerts" 
    ADD CONSTRAINT "Relationship11" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

  • Ruby

    create_table :area_alerts do |t|
    t.references :user, :null => false
    t.text :description, :null => false, :default => ""
    t.boolean :sms, :im, :email, :null => false, :default => true
    t.polygon :area, :null => false
    t.timestamps
    end

• Tabela threat_alerts
  • SQL

    CREATE TABLE "threat_alerts"(
    "threat_alerts_id" Serial NOT NULL,
    "users_id" Integer NOT NULL,
    "threats_id" Integer NOT NULL,
    "description" Text NOT NULL,
    "sms" Boolean DEFAULT true NOT NULL,
    "im" Boolean DEFAULT true NOT NULL,
    "email" Boolean DEFAULT true NOT NULL,
    "created_at" Timestamp NOT NULL,
    "updated_at" Timestamp NOT NULL,
    PRIMARY KEY ("threat_alerts_id")
    );

    ALTER TABLE "threat_alerts" 
    ADD CONSTRAINT "Relationship15" FOREIGN KEY ("threats_id") REFERENCES "threats" ("threats_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;
    
    ALTER TABLE "threat_alerts" 
    ADD CONSTRAINT "Relationship16" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION;

  • Ruby

    create_table :threat_alerts do |t|
    t.references :user, :threat, :null => false
    t.text :description, :null => false, :default => ""
    t.boolean :sms, :im, :email, :null => false, :default => true
    t.timestamps
    end

W projekcie wykorzystano trzy tablice słownikowe:

• groups - tabela zawierająca dostępne grupy,
• languages - tabela zawierająca dostępne języki,
• threat_types - tabela zawierająca dostępne typy zagrożeń.

Po dokonaniu analizy schematu tabel zauważyliśmy, że nasza baza danych spełnia trzecią postać normalną, gdyż wszystkie atrybuty kluczowe są zależne bezpośrednio od wszystkich kluczy.

W naszym projekcie nie przewidujemy denormalizacji.

Podobnie jak w przypadku projektowania schematów tabel, zapytania w naszej aplikacji będą wykonywane z poziomu języka Ruby, dlatego przytoczymy jedynie przykład zapytania w języku SQL oraz w języku Ruby (przykład zapytania zwracającego komentarze danego użytkownika):

• SQL

  SELECT komentarz.tresc FROM komentarz WHERE komentarz.id_uzytkownik=id_szukanego;

• Ruby

  query=Komentarz.find :all, :select=>"tresc", :conditions=>["id_uzytkownik=?", id_szukanego]

Powrót do strony projektu.