diff options
Diffstat (limited to 'database')
14 files changed, 477 insertions, 0 deletions
diff --git a/database/.gitignore b/database/.gitignore new file mode 100644 index 0000000..97fc976 --- /dev/null +++ b/database/.gitignore @@ -0,0 +1,2 @@ +*.sqlite +*.sqlite-journal diff --git a/database/factories/UserFactory.php b/database/factories/UserFactory.php new file mode 100644 index 0000000..741edea --- /dev/null +++ b/database/factories/UserFactory.php @@ -0,0 +1,28 @@ +<?php + +/** @var \Illuminate\Database\Eloquent\Factory $factory */ + +use App\User; +use Faker\Generator as Faker; +use Illuminate\Support\Str; + +/* +|-------------------------------------------------------------------------- +| Model Factories +|-------------------------------------------------------------------------- +| +| This directory should contain each of the model factory definitions for +| your application. Factories provide a convenient way to generate new +| model instances for testing / seeding your application's database. +| +*/ + +$factory->define(User::class, function (Faker $faker) { + return [ + 'name' => $faker->name, + 'email' => $faker->unique()->safeEmail, + 'email_verified_at' => now(), + 'password' => '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', // password + 'remember_token' => Str::random(10), + ]; +}); diff --git a/database/migrations/2014_10_12_000000_create_users_table.php b/database/migrations/2014_10_12_000000_create_users_table.php new file mode 100644 index 0000000..621a24e --- /dev/null +++ b/database/migrations/2014_10_12_000000_create_users_table.php @@ -0,0 +1,36 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class CreateUsersTable extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::create('users', function (Blueprint $table) { + $table->id(); + $table->string('name'); + $table->string('email')->unique(); + $table->timestamp('email_verified_at')->nullable(); + $table->string('password'); + $table->rememberToken(); + $table->timestamps(); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('users'); + } +} diff --git a/database/migrations/2014_10_12_100000_create_password_resets_table.php b/database/migrations/2014_10_12_100000_create_password_resets_table.php new file mode 100644 index 0000000..0ee0a36 --- /dev/null +++ b/database/migrations/2014_10_12_100000_create_password_resets_table.php @@ -0,0 +1,32 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class CreatePasswordResetsTable extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::create('password_resets', function (Blueprint $table) { + $table->string('email')->index(); + $table->string('token'); + $table->timestamp('created_at')->nullable(); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('password_resets'); + } +} diff --git a/database/migrations/2019_08_19_000000_create_failed_jobs_table.php b/database/migrations/2019_08_19_000000_create_failed_jobs_table.php new file mode 100644 index 0000000..9bddee3 --- /dev/null +++ b/database/migrations/2019_08_19_000000_create_failed_jobs_table.php @@ -0,0 +1,35 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class CreateFailedJobsTable extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::create('failed_jobs', function (Blueprint $table) { + $table->id(); + $table->text('connection'); + $table->text('queue'); + $table->longText('payload'); + $table->longText('exception'); + $table->timestamp('failed_at')->useCurrent(); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('failed_jobs'); + } +} diff --git a/database/migrations/2020_03_19_145925_create_articles_table.php b/database/migrations/2020_03_19_145925_create_articles_table.php new file mode 100644 index 0000000..f7732ac --- /dev/null +++ b/database/migrations/2020_03_19_145925_create_articles_table.php @@ -0,0 +1,34 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class CreateArticlesTable extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::create('article', function (Blueprint $table) { + $table->id(); + $table->timestamps(); + + $table->string('url')->unique(); + $table->string('title')->default(''); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('article'); + } +} diff --git a/database/migrations/2020_03_19_150116_create_discussion_table.php b/database/migrations/2020_03_19_150116_create_discussion_table.php new file mode 100644 index 0000000..1869892 --- /dev/null +++ b/database/migrations/2020_03_19_150116_create_discussion_table.php @@ -0,0 +1,44 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class CreateDiscussionTable extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::create('discussion', function (Blueprint $table) { + $table->id(); + $table->timestamps(); + + $table->unsignedBigInteger('article_id')->default(0); + $table->string('title'); + $table->string('source'); + $table->string('item_id')->unique(); + $table->string('source_url'); + $table->integer('posted_on'); + $table->integer('comments')->default(0); + $table->integer('upvotes')->default(0); + + $table->foreign('article_id')->references('id')->on('article')->onDelete('cascade'); + + $table->unique(['item_id', 'posted_on', 'source']); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('discussion'); + } +} diff --git a/database/migrations/2020_03_19_150930_create_category_table.php b/database/migrations/2020_03_19_150930_create_category_table.php new file mode 100644 index 0000000..20905b4 --- /dev/null +++ b/database/migrations/2020_03_19_150930_create_category_table.php @@ -0,0 +1,33 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class CreateCategoryTable extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::create('category', function (Blueprint $table) { + $table->id(); + $table->timestamps(); + + $table->string('name')->unique()->default(''); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('category'); + } +} diff --git a/database/migrations/2020_03_19_154610_create_code_table.php b/database/migrations/2020_03_19_154610_create_code_table.php new file mode 100644 index 0000000..e8703dd --- /dev/null +++ b/database/migrations/2020_03_19_154610_create_code_table.php @@ -0,0 +1,40 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class CreateCodeTable extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::create('code', function (Blueprint $table) { + $table->id(); + $table->timestamps(); + + $table->string('url'); + $table->string('title'); + $table->string('source'); + $table->string('item_id')->unique(); + $table->string('source_url'); + $table->integer('posted_on'); + $table->integer('comments')->default(0); + $table->integer('upvotes')->default(0); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('code'); + } +} diff --git a/database/migrations/2020_03_28_131201_create_view_popular_articles.php b/database/migrations/2020_03_28_131201_create_view_popular_articles.php new file mode 100644 index 0000000..062b8e0 --- /dev/null +++ b/database/migrations/2020_03_28_131201_create_view_popular_articles.php @@ -0,0 +1,55 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\DB; + +class CreateViewPopularArticles extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + DB::statement(" + CREATE VIEW view_popular AS + SELECT + article_id as id, + article_id, + a.created_at, + a.updated_at, + a.url, + a.title, + a.excerpt_html, + sum(upvotes)+sum(comments) AS impact, + sum(upvotes) AS upvotes, + sum(comments) as comments, + count(article_id) AS repost + FROM + discussion as d + JOIN + article AS a ON d.article_id = a.id + GROUP BY + article_id, + a.created_at, + a.updated_at, + a.url, + a.title, + a.excerpt_html + HAVING (impact > 300) OR (count(article_id) > 2) + ORDER BY ((sum(upvotes)+sum(comments))*count(article_id)) desc, repost desc, impact desc" + ); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + DB::statement("DROP VIEW view_popular;"); + } +} diff --git a/database/migrations/2020_03_28_132812_create_view_articles.php b/database/migrations/2020_03_28_132812_create_view_articles.php new file mode 100644 index 0000000..15fdee8 --- /dev/null +++ b/database/migrations/2020_03_28_132812_create_view_articles.php @@ -0,0 +1,53 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\DB; + +class CreateViewArticles extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + DB::statement( + "CREATE VIEW view_article AS + SELECT + article_id as id, + article_id, + a.created_at, + a.updated_at, + a.url, + a.title, + a.excerpt_html, + sum(upvotes)+sum(comments) AS impact, + sum(upvotes) AS upvotes, + sum(comments) as comments, + count(article_id) AS repost + FROM + discussion as d + JOIN + article AS a ON d.article_id = a.id + GROUP BY + article_id, + a.created_at, + a.updated_at, + a.url, + a.title, + a.excerpt_html" + ); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + DB::statement("DROP VIEW view_article;"); + } +} diff --git a/database/migrations/2020_03_28_164915_add_excerpt_to_article.php b/database/migrations/2020_03_28_164915_add_excerpt_to_article.php new file mode 100644 index 0000000..c4936b9 --- /dev/null +++ b/database/migrations/2020_03_28_164915_add_excerpt_to_article.php @@ -0,0 +1,32 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class AddExcerptToArticle extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::table('article', function (Blueprint $table) { + $table->text('excerpt_html')->nullable(); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::table('article', function (Blueprint $table) { + $table->dropColumn('excerpt_html'); + }); + } +} diff --git a/database/migrations/2020_03_28_194649_join_article_category_table.php b/database/migrations/2020_03_28_194649_join_article_category_table.php new file mode 100644 index 0000000..3f843d4 --- /dev/null +++ b/database/migrations/2020_03_28_194649_join_article_category_table.php @@ -0,0 +1,37 @@ +<?php + +use Illuminate\Database\Migrations\Migration; +use Illuminate\Database\Schema\Blueprint; +use Illuminate\Support\Facades\Schema; + +class JoinArticleCategoryTable extends Migration +{ + /** + * Run the migrations. + * + * @return void + */ + public function up() + { + Schema::create('article_category', function (Blueprint $table) { + $table->id(); + $table->unsignedBigInteger('article_id')->default(0); + $table->unsignedBigInteger('category_id')->default(0); + + $table->foreign('article_id')->references('id')->on('article'); + $table->foreign('category_id')->references('id')->on('category'); + + $table->unique(['article_id', 'category_id']); + }); + } + + /** + * Reverse the migrations. + * + * @return void + */ + public function down() + { + Schema::dropIfExists('article_category'); + } +} diff --git a/database/seeds/DatabaseSeeder.php b/database/seeds/DatabaseSeeder.php new file mode 100644 index 0000000..91cb6d1 --- /dev/null +++ b/database/seeds/DatabaseSeeder.php @@ -0,0 +1,16 @@ +<?php + +use Illuminate\Database\Seeder; + +class DatabaseSeeder extends Seeder +{ + /** + * Seed the application's database. + * + * @return void + */ + public function run() + { + // $this->call(UsersTableSeeder::class); + } +} |