Week #64

It was a pretty busy week, not from a work perspective but from the chaotic household perspective. It is Diwali this weekend and next week. So, the preparations, the cleaning of homes, were done past weekend. It actually took my whole Sunday, sweet Sunday, and even half of Saturday. Hh! I read a lot of books, though. I found a great book that fits the vibe I wanted while being in the atmosphere of Diwali.

Its a beautiful sunny Monday, you had prepared changes well tested on Friday. But today, just for today, Bill had to merge their hotfix into your changes as well. Well, they did.

And I didn’t bother about it. It had some merge conflicts which Bill resolves happily (I thought). Everything is green, but some things are red, due to the size of PR I thought, (+3079 -789) changes. Phew!

Merged and deployed.

Week #63

It was a fun week. After a long, a long I have had the time to rip-off a code base hands on and do something beyond pushing a feature, it was about reforming how a thing is approached. I felt really good. There are always rough days, but these days, the days that give you pain but that pain is satisfying. At the end of each day of the week, I had gone and came out of bed brimming full of ideas and questions to talk and sit through. Felt I was back to the grind.

Week #62

It was a great week. I didn’t knew I would say that at the start of the week. But hell yes, a promotion, finally. A hope at the end of the tunnel. Hard work pays off in the end. I kept believing and finally able to bring smile on my loved ones.

I quoted last week about home, a family, is not something that is by blood, rather it is the group of people who strive for each others success, for uplifting each other, when one falls down the other roots for him or her, when its harsh time, everyone steps back and stays together. And I was able to experience just that. The true value and meaning of a family and life.

Creating table with Select Query

Sometimes you want to create a new table based on data that already exists,maybe you’re archiving old rows, generating a summary, cloning a table, or transforming data into a new structure. Doing this might require you to write a CREATE TABLE statement with all column definitions, then running one or more INSERT INTO statements to populate it. That’s a lot of work.

Well, SQL is more flexible then you might think, Instead of manually defining columns and inserting data, you write one statement that both builds the table and fills it with the rows returned by your SELECT query. This makes it incredibly useful for backups, and whatever you are doing (hopefully not taking down the prod db).

Week #61

A bit of slow and disappointing week on a personal note. I tried my best to be a backend developer but was not worthy of being one, I was stranded as a product developer. I am not saying I hate being a product developer, it just gets too menial and boring once you know the limitations and the quirks of a product.

Apart from the grill, I learnt a lot about tokenization. I also continued to write occasionally about SQLite in the SQLog, three more entries. Wasn’t able to livestream due to guests and was unwell for the weekend with cold.

Creating table if not already exists

The CREATE TABLE has one clause that we can add to create table if it doesn’t exist already. So this comes handy if you already have created a table and instead of throwing an error it simply gracefully handles the query and doesn’t re-create the table.

Let’s first creat a table, as usual, we’ll use the goodol users table.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

Let’s insert some data into the table.

Temporary Table in SQLite

A Temporary table as the name suggests, is a temporary table. It only resisdes on the database until the current session of the database.

In case of sqlite, the temporary table is available in the session until the file reader or session driver closes the connection.

The table is created in a separate database file called temp that is stored in your temporary path.

Create a temporary table

To create the temporary table, simply use the TEMP or TEMPORARY keyword before the TABLE in CREATE TABLE expression. So, CREATE TEMP TABLE or CREATE TEMPORARY TABLE will be the notation to create a temporary table in sqlite.

Collate Column Modifier

Collate is a modifier in SQL that specifies the column how to arrange and compare the values that would be populated in the rows.

So, for a given table, we can specify a few modifiers that would let SQL decide how to handle the values.

Adding Collate Modifier

To add a collate modifier, we can use the COLLATE keyword, followed by the collation name.

There are 3 collations available in SQLite:

Many to Many Relation

We saw the basic example of Many-To-Many Relation in the second last post from this, there we just focused on the concept of the relation and not so much on the structure of the junction table.

Let’s take a look at the schema again:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE TABLE author_post (
    user_id INTEGER NOT NULL,
    post_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, post_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (post_id) REFERENCES posts (id)
);

Let’s now populate the tables.