Setup Twitter Bootstrap on Phoenix projects If you want to scale your app you’ll need to leverage queries processing to the DB. There are some simple business requests that could easily be solved with N+1 queries, but watch out this is a trap. This is a short blog post for sharing some sql examples to solve some daily problems in an elegant way. The truth is that I created this to run on PostgreSQL but it may be very easy to adapt to other SQL database. This is a very flexible way to build your generated data with as much data as you need to test your query performance. Then we INNER JOIN with the same expanded temp table to get the chosen user_id. On the second CTE shuffled I’ve used the MIN aggregation function on a grouped by seq column over the expanded temp table to get a single chosen value per seq. Finally I’ve also used RANDOM() to be used later on when choosing a random User. I am pretty sure that this is not very efficient but it solves the problem. The trick I’ve used here was to cross join with the users table in order to get all possible combination of generate sequence and user_id. I’ve started creating a CTE called expanded to get a limited sequence of rows using GENERATE_SERIES, the same idea as before. To make it easier to understand I used a lot of CTE. This was a bit more complex to build than the previous one. This is a simple way to guarantee some rand number of posts per user. Also I’d like to perform some queries that return some random number of rows, so I’d like to chose a random User for each Post. The Post model is the first one which has a FOREIGN KEY, so I have to use real users.id values. This was easy, let’s move on and see how to choose random DB references. I’ve got strongly inspired by this amazing PG Cast - Generating Fake Email Addresses. I can also use RANDOM() function to build some random data in this case I am choosing the email domain from a finite list.ġ | | | | | | | | | | rows) Time: 5.159 ms Then the idea is to use the seq field given by the GENERATE_SERIES to have just unique values as I have this constraint. Note how easy is to generate a thousand or a million of rows just changing a single number. So I started this querying on GENERATE_SERIES(1, 10) as an easy way to generate 10 rows for me. In this case the only field that I need to generate is a VARCHAR one. I am using FOREIGN KEYS off course and NOT NULL as I want to have basic data validations on the DB level, as always. Note that all tables have an id SERIAL PRIMARY KEY column, so PostgreSQL will take care about generating the ids for me.Ī User has email, Post belongs to a User and it has title and Comment belongs to a Post and it’s made by another User and it has body column. These testing data-sets act as input for the test-cases and later on the basis of these data sets output is generated and the behavior of the system is analyzed whether expected or not.CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR ( 40 ) NOT NULL UNIQUE ) CREATE TABLE posts ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users ( id ), title VARCHAR ( 100 ) NOT NULL UNIQUE ) CREATE TABLE comments ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users ( id ), post_id INTEGER NOT NULL REFERENCES posts ( id ), body VARCHAR ( 500 ) NOT NULL ) ISRO CS Syllabus for Scientist/Engineer ExamĪs a tester, you needn’t only to test the software product but also have to manage, collect, and maintain a large volume of data sets in order to efficiently test all the major test-cases, to ensure that all the requirements are fulfilling as maximum as possible no matter whether it is functional or non-functional testing.ISRO CS Original Papers and Official Keys. GATE CS Original Papers and Official Keys.DevOps Engineering - Planning to Production.Python Backend Development with Django(Live).Android App Development with Kotlin(Live).Full Stack Development with React & Node JS(Live).Java Programming - Beginner to Advanced.Data Structure & Algorithm-Self Paced(C++/JAVA).Data Structures & Algorithms in JavaScript.Data Structure & Algorithm Classes (Live).
0 Comments
Leave a Reply. |