Max Retail

maxretail

Building a Production Grade Testnet Faucet With Typescript, Redis and Nextjs

How to Build a Chatbot with Langchain, Next.js, OpenAI, and Supabase

How to Build a Web Page Summarization App With Next.js, OpenAI, LangChain, and Supabase

Next.js, OpenAI, LangChain, এবং Supabase দিয়ে কিভাবে একটি ওয়েব পেজ সামারাইজেশন অ্যাপ তৈরি করবেন

So erstellen Sie eine App zur Zusammenfassung von Webseiten mit Next.js, OpenAI, LangChain und Supabase

Cómo crear una aplicación de resumen de páginas web con Next.js, OpenAI, LangChain y Supabase

Comment créer une application de synthèse de pages Web avec Next.js, OpenAI, LangChain et Supabase

Next.js, OpenAI, LangChain और Supabase के साथ वेब पेज सारांशीकरण ऐप कैसे बनाएं

Next.js、OpenAI、LangChain、Supabase を使って Web ページ要約アプリを構築する方法

Next.js, OpenAI, LangChain 및 Supabase를 사용하여 웹 페이지 요약 앱을 구축하는 방법

Como construir um aplicativo de resumo de páginas da Web com Next.js, OpenAI, LangChain e Supabase

Как создать приложение для суммирования веб-страниц с помощью Next.js, OpenAI, LangChain и Supabase

Next.js, OpenAI, LangChain ve Supabase ile Web Sayfası Özetleme Uygulaması Nasıl Oluşturulur

Cách xây dựng ứng dụng tóm tắt trang web với Next.js, OpenAI, LangChain và Supabase

如何使用 Next.js、OpenAI、LangChain 和 Supabase 构建网页摘要应用程序

Flaky automation tests can be a bane for QA engineers, introducing uncertainty and undermining the reliability of test suites. Drawing from my experience as an SDET and QA Automation mentor, this article offers practical advice to conquer flakiness. While I’ll provide JavaScript and Playwright examples, these universal tips are applicable in any language and framework, helping you write robust and dependable automation tests. Let’s dive in and ensure your tests stand firm. 1. Avoid the implicit wait methods Occasionally, you will encounter situations where you must wait for elements to appear in the DOM or for your application to reach a specific state to proceed with your test scenario. Even with modern, intelligent automation frameworks like Playwright’s auto-wait features, there will be instances where you need to implement custom wait methods. For instance, consider a scenario with text fields and a confirmation button. Due to specific server-side behavior, the confirmation button becomes visible only after approximately five seconds of completing the form. In such cases, resisting the temptation of inserting a five-second implicit wait between two test steps is essential. textField.fill('Some text')
waitForTime(5000)
confirmationButton.click() Use a smart approach instead and wait for the exact state. It can be the text element appearing after some loading or a loader spinning element disappearing after the current step is successfully done. You can check if you are ready for the next test step. button.click()
waitFor(textField.isVisible())
textField.fill() Of course, there are cases where you need to wait for something you cannot check smartly. I suggest adding comments in such places or even adding the reason explanation as a parameter in your wait methods or something like that: waitForTime(5000, {reason: "For unstable server processed something..."} An additional profit of using it is the test reports where you can store such explanations so it will be obvious for someone else or even for future you what and why such five-second waiting here. waitForTime(5000, {reason: "For unstable server processed something..."}
button.click()
waitFor(textField.isVisible())
textField.fill() 2. Use robust and reliable locators to select elements Locators are a crucial part of automation testing, and everyone knows it. However, despite this simple fact, many automation engineers are bailing on the stability and using something like this in their tests. //*[@id="editor_7"]/section/div[2]/div/h3[2] That is a silly approach because the DOM structure is not so static; some different teams can sometimes change it, and e this after your tests fail. So, use the robust locators. You’re welcome to my XPath-related story reading, by the way. https://hackernoon.com/getting-your-way-around-with-common-xpath-methods?embedable=true 3. Make tests independent from each other When running the test suite with multiple tests in a single thread, it’s crucial to ensure each test is independent. This means that the first test should return the system to its original state so the next test doesn’t fail due to unexpected conditions. For example, if one of your tests modifies user settings stored in LocalStorage, clearing the LocalStorage entry for User Settings after the first test runs is a good approach. This ensures that the second test will be executed with default user settings. You can also consider actions like resetting the page to the default entry page and clearing cookies and database entries so that each new test begins with identical initial conditions. For example, in Playwright, you can use the beforeAll(), beforeEach(), afterAll(), and afterEach() annotations to achieve it. Check the next test suite with a couple of tests. The first is changing the user's appearance settings, and the second is checking the default appearance. test.describe('Test suite', () => {
  test('TC101 - update appearance settings to dark', async ({page}) => {
    await user.goTo(views.settings);
    await user.setAppearance(scheme.dark);
  });

  test('TC102 - check if default appearance is light', async ({page}) => {
    await user.goTo(views.settings);
    await user.checkAppearance(scheme.light);
  });
}); If such a test suite runs in parallel, everything will go smoothly. However, if these tests are run one by one, you could face a failed test because one of them is interfering with another. The first test changed the appearance from light to dark. The second test checks if the current appearance is light. But it will fail since the first test changed the default appearance already. To solve such a problem, I’ve added the afterEach() hook executed after each test. In this case, it will navigate to the default view and clear the user's appearance by removing it from the Local Storage. test.afterEach(async ({ page }) => {
  await user.localStorage(appearanceSettings).clear()
  await user.goTo(views.home)
});

test.describe('Test suite', () => {
  test('TC101 - update appearance settings to dark', async ({page}) => {
    await user.goto(views.settings);
    await user.setAppearance(scheme.dark);
  });

  test('TC102 - check if default appearance is light', async ({page}) => {
    await user.goTo(views.settings);
    await user.checkAppearance(scheme.light);
  });
}); Using this approach, each test in this suite will be independent. 4. Use automatic retries wisely No one is immune to encountering flaky tests, and a popular remedy for this issue involves using retries. You can configure retries to occur automatically, even at the CI/CD configuration level. For instance, you can set up automatic retries for each failed test, specifying a maximum retry count of three times. Any test that initially fails will be retried up to three times until the test execution cycle is complete. The advantage is that if your test is only slightly flaky, it may pass after a couple of retries. However, the downside is that it could fail, resulting in extra runtime consumption. Moreover, this practice can inadvertently lead to an accumulation of flaky tests, as many tests may appear to “pass” on the second or third attempt, and you might incorrectly label them as stable. Therefore, I do not recommend setting auto-retries globally for your entire test project. Instead, use retries selectively in cases where you cannot promptly resolve the underlying issues in the test. For instance, pretend you have a test case where you must upload some files using the ‘filechooser’ event. You get the <Timeout exceeded while waiting for event ‘filechooser’> error, which indicates that the Playwright test is waiting for a ‘filechooser’ event to occur but is taking longer ied timeout. async function uploadFile(page: Page, file) {
  const fileChooserPromise = page.waitForEvent('filechooser');
  await clickOnElement(page, uploadButton);
  const fileChooser = await fileChooserPromise;
  await fileChooser.setFiles(file);
} This can be due to various reasons, such as unexpected behavior in the application or a slow environment. Because it is random behavior, the first thing you could think about is to use automatic retry for this test. However, if you retry the entire test, you risk wasting extra time and getting the same behavior you got with the first error in the uploadFile() method itself, so if the error occurs, you don’t need to retry the entire test. To do this, you can use the standard try…catch statement. async function uploadFile(page: Page, file) {
  const maxRetries = 3;
  let retryCount = 0;

  while (retryCount < maxRetries) {
    try {
      const fileChooserPromise = page.waitForEvent('filechooser');
      await clickOnElement(page, uploadButton);
      const fileChooser = await fileChooserPromise;
      await fileChooser.setFiles(file);
      break; // Success, exit the loop
    } catch (error) {
      console.error(`Attempt ${retryCount + 1} failed: ${error}`);
      retryCount++;
    }
  }
} Such an approach will save extra time and make your test less flaky. In closing, the path to reliable automation tests is straightforward. You can minimize or actively address common challenges and implement intelligent strategies. Remember, this journey is ongoing, and your tests will become more dependable with persistence. Say goodbye to flakiness and welcome stability. Your commitment to quality ensures project success. Happy testing! Also published . here

The Four Horsemen of Flaky Tests

ফ্ল্যাকি টেস্টের চার ঘোড়সওয়ার

Die vier Reiter der Flockentests

Los cuatro jinetes de las pruebas escamosas

Les quatre cavaliers des épreuves feuilletées

परतदार परीक्षणों के चार घुड़सवार

不安定なテストの四騎士

플래키 테스트의 네 기수

Os quatro cavaleiros dos testes esquisitos

Четыре всадника нестабильных тестов

Kesintisiz Testlerin Dört Atlısı

Bốn kỵ sĩ của những bài kiểm tra dễ vỡ

Flaky 测试的四骑士

In the field of natural language processing (NLP), embeddings have become a game-changer. They allow us to convert words and documents into numbers that computers can understand. These numerical representations, known as embeddings, are vital for like understanding text, analyzing sentiments, and translating languages. tasks This article explores embeddings in , a user-friendly platform for creating embeddings. We’ll explain what embeddings are and how they work in AI. We’ll also dive into LangChain’s embedding capabilities and how it makes generating embeddings for queries and documents easy. LangChain LangChain goes beyond just providing embedding functions. It integrates with different models to offer a variety of embedding options. We’ll explore some of these integrations, such as GloVeEmbeddings, BERTEmbeddings, Word2VecEmbeddings, and FastTextEmbeddings, and their advantages. By the end of this article, you’ll have a clear understanding of embeddings, their importance in NLP, and how LangChain simplifies the process of using embeddings. Let’s dive into the world of embeddings and unleash the power of language understanding with LangChain. What are Embeddings? In the realm of natural language processing (NLP), embeddings are a method to convert text data into a numerical format that machine learning algorithms can understand and process. Each word (or document) is transformed into a high-dimensional vector that represents its context in the dataset. The beauty of these vectors is that they can capture semantic relationships between words - words that are used similarly will have similar vectors. Embeddings are an essential aspect of , including text classification, sentiment analysis, and language translation, to name a few. They help us quantify and categorize linguistic data in a way that is analogous to how humans understand language. many NLP tasks Embeddings in LangChain: A Closer Look LangChain offers a powerful and easy-to-use interface for generating embeddings. But what is happening under the hood when we call these functions? Let's break it down. Embedding Queries When we call , LangChain takes the text string "Hello world", and converts it into a numerical representation - an embedding. This function returns an array of numbers, each representing a dimension in the embedding space. embedQuery("Hello world") /* Embed queries */
const res = await embeddings.embedQuery("Hello world"); What you see in the array is the numerical representation of "Hello world". It might look like a random array of numbers, but these numbers encode the meaning of "Hello world" in a way that a machine learning model can understand. res Embedding Documents Just as we can create embeddings for queries, we can do the same for documents. The function takes an array of text strings and returns an array of their respective embeddings. embedDocuments /* Embed documents */
const documentRes = await embeddings.embedDocuments(["Hello world", "Bye bye"]); In this case, is a two-dimensional array, with each sub-array being the embedding of the corresponding document. documentRes Embedding Integrations in LangChain LangChain provides multiple classes for generating embeddings, each integrating with a different model provider. OpenAIEmbeddings The class uses the OpenAI API to create embeddings. You can either use OpenAI's API key or Azure's OpenAI API key. Here's an example of how to use Azure's OpenAI API key: OpenAIEmbeddings import { OpenAIEmbeddings } from "langchain/embeddings/openai";

const embeddings = new OpenAIEmbeddings({
  azureOpenAIApiKey: "YOUR-API-KEY",
  azureOpenAIApiInstanceName: "YOUR-INSTANCE-NAME",
  azureOpenAIApiDeploymentName: "YOUR-DEPLOYMENT-NAME",
  azureOpenAIApiVersion: "YOUR-API-VERSION",
}); Other Integrations Other integrations include , , and . For example, to use , you would do: CohereEmbeddings TensorFlowEmbeddings HuggingFaceInferenceEmbeddings CohereEmbeddings import { CohereEmbeddings } from "langchain/embeddings/cohere";

const embeddings = new CohereEmbeddings({
  apiKey: "YOUR-API-KEY",
}); Additional Features and Handling Errors LangChain also offers a variety of additional features such as setting a timeout, handling rate limits, and dealing with API errors. For instance, if you want LangChain to stop waiting for a response after a certain amount of time, you can set a timeout: import { OpenAIEmbeddings } from "langchain/embeddings/openai";

const embeddings = new OpenAIEmbeddings({
  timeout: 1000, // 1s timeout
}); In this example, if the embedding process takes longer than 1 second, LangChain will stop waiting and move on. This can be especially useful when dealing with large documents that might take a while to process, or when you're working with a slow or unreliable internet connection. Dealing with Rate Limits Rate limiting is a strategy implemented by many API providers to prevent users from overloading their servers with too many requests in a short period of time. If you exceed the rate limit, you will receive an error message. LangChain provides a handy feature to manage rate limits. You can set a option when instantiating an Embeddings model. This option allows you to specify the maximum number of concurrent requests you want to make to the provider. If you exceed this number, LangChain will automatically queue up your requests and send them as previous requests are completed. maxConcurrency Here is an example of how to set a maximum concurrency of 5 requests: import { OpenAIEmbeddings } from "langchain/embeddings/openai";

const model = new OpenAIEmbeddings({ maxConcurrency: 5 }); Handling API Errors If the model provider returns an error, LangChain has a built-in mechanism to retry the request up to 6 times, with exponential backoff. This means that each retry will wait twice as long as the previous one before attempting the request again. This strategy can often help to successfully complete the request, especially in cases of temporary network problems or server overloads. If you want to change the maximum number of retries, you can pass a option when you instantiate the model: maxRetries import { OpenAIEmbeddings } from "langchain/embeddings/openai";

const model = new OpenAIEmbeddings({ maxRetries: 10 }); In this example, LangChain will retry failed requests up to 10 times before finally giving up. Conclusion To conclude, embeddings are a powerful tool in NLP tasks, and LangChain provides a robust, flexible, and user-friendly interface for generating and working with embeddings. With the ability to integrate with multiple providers, handle rate limits, and manage API errors, LangChain is an excellent choice for any AI project. To find out more about LangChain and its other exciting features, take a look at the or the . 'Complete Guide to LangChain' ' ' LangChain Chat Models Overview Also published . here

A Complete Overview of Embeddings in LangChain

Quartz is an open-source job scheduling framework that can be integrated into a wide variety of Java applications. It is used to schedule jobs that can be executed at a later time or on a repeating basis. Quartz is designed to be a simple and powerful scheduling framework that is easy to use and configure. But there is no built-in feature in the Quartz framework that allows for the automatic retrying of jobs that encounter exceptions during their execution. This article is inspired by . Here we consider using Quartz in a Spring application and implement an exponential random backoff policy that reduces the number of retry attempts, prevents overloading the system, allows the system to recover slowly over time, and distributes retry attempts evenly. Harvey Delaney’s blog post about Job Exception Retrying in Quartz.NET Storing retry parameters We will use -s that are associated with and objects of our job. ’s will store exponential random backoff policy parameters , , and . JobDataMap associated with Trigger will store . JobDataMap JobDetail Trigger JobDetail JobDataMap MAX_RETRIES RETRY_INITIAL_INTERVAL_SECS RETRY_INTERVAL_MULTIPLIER RETRY_NUMBER Job failure handling listener Now let’s add a Spring component that implements the interface. is called when the job is about to be executed and is called after job completion. In we will increment the counter. will contain the main code of exception handling and creating a new trigger for the next execution of a job. JobFailureHandlingListener org.quartz.JobListener JobListener#jobToBeExecuted JobListener#jobWasExecuted jobToBeExecuted RETRY_NUMBER jobWasExecuted Exponential random backoff policy Exponential random backoff is an algorithm used to progressively introduce delays in retrying an operation that has previously failed. The idea is to increase the delay between retries in an exponential manner, with each delay being chosen randomly from a range of possible values. This helps to avoid the scenario where multiple devices or systems all attempt to retry an operation at the same time, which can further exacerbate the problem that caused the operation to fail in the first place. Exponential random backoff is often used in distributed systems that need to retry operations that have failed. For example, it might be used in a distributed database to retry transactions that fail due to conflicts with other transactions or in a distributed file system to retry operations that fail due to network errors or the temporary unavailability of servers. We will use an exponential random backoff algorithm to calculate the time of the next attempt to start the job. Show me the code JobFailureHandlingListener.java package quartzdemo.listeners;

import org.quartz.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.util.Date;

@Component
public class JobFailureHandlingListener implements JobListener {

    private final String RETRY_NUMBER_KEY = "RETRY_NUMBER";
    private final String MAX_RETRIES_KEY = "MAX_RETRIES";
    private final int DEFAULT_MAX_RETRIES = 5;
    private final String RETRY_INITIAL_INTERVAL_SECS_KEY = "RETRY_INITIAL_INTERVAL_SECS";
    private final int DEFAULT_RETRY_INITIAL_INTERVAL_SECS = 60;
    private final String RETRY_INTERVAL_MULTIPLIER_KEY = "RETRY_INTERVAL_MULTIPLIER";
    private final double DEFAULT_RETRY_INTERVAL_MULTIPLIER = 1.5;

    private final Logger logger = LoggerFactory.getLogger(getClass());

    @Override
    public String getName() {
        return "FailJobListener";
    }

    @Override
    public void jobToBeExecuted(JobExecutionContext context) {
        context.getTrigger().getJobDataMap().merge(RETRY_NUMBER_KEY, 1,
                (oldValue, initValue) -> ((int) oldValue) + 1);
    }

    @Override
    public void jobExecutionVetoed(JobExecutionContext context) { }

    @Override
    public void jobWasExecuted(JobExecutionContext context, JobExecutionException jobException) {
        if (context.getTrigger().getNextFireTime() != null || jobException == null) {
            return;
        }
        int maxRetries = (int) context.getJobDetail().getJobDataMap()
                .computeIfAbsent(MAX_RETRIES_KEY, key -> DEFAULT_MAX_RETRIES);
        int timesRetried = (int) context.getTrigger().getJobDataMap().get(RETRY_NUMBER_KEY);
        if (timesRetried > maxRetries) {
            logger.error("Job with ID and class: " + context.getJobDetail().getKey() +", " + context.getJobDetail().getJobClass() +
                    " has run " + maxRetries + " times and has failed each time.", jobException);
            return;
        }

        TriggerKey triggerKey = context.getTrigger().getKey();
        int initialIntervalSecs = (int) context.getJobDetail().getJobDataMap()
                .computeIfAbsent(RETRY_INITIAL_INTERVAL_SECS_KEY, key -> DEFAULT_RETRY_INITIAL_INTERVAL_SECS);
        double multiplier = (double) context.getJobDetail().getJobDataMap()
                .computeIfAbsent(RETRY_INTERVAL_MULTIPLIER_KEY, key -> DEFAULT_RETRY_INTERVAL_MULTIPLIER);
        Date newStartTime = ExponentialRandomBackoffFixtures.getNextStartDate(timesRetried, initialIntervalSecs, multiplier);
        Trigger newTrigger = TriggerBuilder.newTrigger()
                .withIdentity(triggerKey)
                .startAt(newStartTime)
                .usingJobData(context.getTrigger().getJobDataMap())
                .build();
        newTrigger.getJobDataMap().put(RETRY_NUMBER_KEY, timesRetried);

        try {
            context.getScheduler().rescheduleJob(triggerKey, newTrigger);
        } catch (SchedulerException e) {
            throw new RuntimeException(e);
        }
    }

} ExponentialRandomBackoffFixtures.java package quartzdemo.listeners;

import org.apache.commons.lang3.RandomUtils;

import java.time.Duration;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;

public class ExponentialRandomBackoffFixtures {

    public static Date getNextStartDate(int timesRetried, int initialIntervalSecs, double multiplier) {
        double minValue = initialIntervalSecs * Math.pow(multiplier, timesRetried - 1);
        double maxValue = minValue * multiplier;
        Duration duration = Duration.ofMillis((long) (RandomUtils.nextDouble(minValue, maxValue) * 1000));
        LocalDateTime nextDateTime = LocalDateTime.now().plus(duration);
        return Date.from(nextDateTime.atZone(ZoneId.systemDefault()).toInstant());
    }

} Now we can add the listener to a scheduler package quartzdemo;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.quartz.SchedulerFactoryBean;
import quartzdemo.listeners.JobFailureHandlingListener;

@Configuration
@EnableScheduling
public class SchedulingConfiguration {

    private final JobFailureHandlingListener jobFailureHandlingListener;

    public SchedulingConfiguration2(JobFailureHandlingListener jobFailureHandlingListener) {
        this.jobFailureHandlingListener = jobFailureHandlingListener;
    }

    @Bean
    public SchedulerFactoryBean scheduler() {
        SchedulerFactoryBean schedulerFactory = new SchedulerFactoryBean();
        // ...
        schedulerFactory.setGlobalJobListeners(jobFailureHandlingListener);
        return schedulerFactory;
    }

} and run a job with a custom parameter RETRY_INITIAL_INTERVAL_SECS package quartzdemo.services;

import org.quartz.*;
import org.springframework.stereotype.Service;
import quartzdemo.jobs.SimpleJob;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;

import static quartzdemo.listeners.JobFailureHandlingListener.RETRY_INITIAL_INTERVAL_SECS_KEY;

@Service
public class SchedulingService {

    private final Scheduler scheduler;

    public SchedulingService(Scheduler scheduler) {
        this.scheduler = scheduler;
    }

    // ...

    private void scheduleJob() throws SchedulerException {
        Date afterFiveSeconds = Date.from(LocalDateTime.now().plusSeconds(5).atZone(ZoneId.systemDefault()).toInstant());

        JobDetail jobDetail = JobBuilder.newJob(SimpleJob.class).usingJobData(RETRY_INITIAL_INTERVAL_SECS_KEY, 30).build();
        Trigger trigger = TriggerBuilder.newTrigger().startAt(afterFiveSeconds).usingJobData("", "").build();
        scheduler.scheduleJob(jobDetail, trigger);
    }
} Conclusion Quartz is a robust framework that provides a wide range of capabilities. Even if it lacks something, it is always possible to implement the necessary functionality using the provided API. This allows developers to extend the framework to meet their specific requirements, so Quartz is a great choice for any scheduling needs.

Handling Job Failures in Quartz with Retries

Comparison between Optimistic and Pessimistic locking with Golang and PostgreSQL Locking mechanism in SQL database. The locking mechanism is used to manage access to resources shared by user databases, tables, pages, and rows to guarantee the consistency of the transmitted data. There are two types of locking in databases, , and . Optimistic Locking Pessimistic locking Optimistic Locking. is a technique for SQL database applications that do not hold row locks between selecting and updating or deleting a row. If the row does change, the update or delete will fail, and the application logic handles such failures by, for example, retrying the whole process.⁽¹⁾ Optimistic locking Pessimistic Locking. The model prevents simultaneous updates to records. As soon as one user starts to update a record, a lock is placed on it. Other users who attempt to update this record are informed that another user has an update in progress. The other users must wait until the first user has finished committing their changes, releasing the record lock. Only then can another user make changes based on the previous user's changes.⁽²⁾ pessimistic locking Implementation Locking using GO and PostgreSQL For this implementation, we will create a use case with two repositories implementation, one using pessimistic and the other optimistic approaches, to simulate deposit action in the account table. For the sake of learning, we use 2 steps in this implementation, firstly we will select the record, modify in GO, then update to the PostgreSQL. account Pessimistic Implementation In the pessimistic implementation, the SQL looks like this : BEGIN;
SELECT * FROM account WHERE username = $1 LIMIT 1 FOR NO KEY UPDATE;
UPDATE account SET balance = $1, version = version+1 WHERE id = $2;
COMMIT; will tell the database to lock the row for updates that only modify non-key fields. FOR NO KEY UPDATE Golang implementation with sqlx library : const (
	selectAccountLock = "SELECT * FROM account WHERE username = $1 LIMIT 1 FOR NO KEY UPDATE;"
	depositAccount    = "UPDATE account SET balance = $1, version = version+1 WHERE id = $2;"
)

func (a *accountRepository) Deposit(ctx context.Context, request *entity.DepositRequest) error {
	// declare the transaction
    tx, err := a.db.Beginx()
	if err != nil {
		fmt.Println(err)
		return entity.ErrInternalServer
	}

    // select account from database
	rows, err := tx.QueryxContext(ctx, selectAccountLock, request.Username)
	if err != nil {
		return entity.ErrInternalServer
	}

	var account entity.Account
	for rows.Next() {
		err = rows.StructScan(&account)
		if err != nil {
			return entity.ErrInternalServer
		}
	}

	account.Balance += request.Amount
	_, err = tx.ExecContext(ctx, depositAccount, account.Balance, account.ID)
	if err != nil {
		tx.Rollback()
		fmt.Println(err)
		return entity.ErrInternalServer
	}

	tx.Commit()
	return nil
} Optimistic Implementation In the optimistic implementation, SQL will look like this: SELECT * FROM account WHERE username = $1 LIMIT 1;
UPDATE account SET balance = $1, version = version + 1 WHERE id = $2 AND version = $3; If we look at the update query there is a on the where clause, it is used to check if there is any other modification on the row between our and queries.  If the query return it means the row has been modified between our and queries, then we have to retry those two queries. version = $3 select update update zero rows affected select update Our optimistic locking Golang implementation needs to create a retry mechanism and a maximum number of retries, it will look like this: const (
	maxRetries              = 3
	selectAccountByUsername = "SELECT * FROM account WHERE username = $1 LIMIT 1;"
	depositAccountOpt       = "UPDATE account SET balance = $1, version = version+1 " +
                              "WHERE id = $2 AND version = $3;"
)

func (a *accountRepositoryOpt) Deposit(ctx context.Context, request *entity.DepositRequest) error {
	return a.depositOpt(ctx, request.Username, request.Amount, 0)
}

func (a *accountRepositoryOpt) depositOpt(ctx context.Context, username string, amount float64, retry int) error {
	// check retry count
	if retry >= maxRetries {
		return entity.ErrMaxRetry
	}

	// select account
	rows, err := a.db.QueryxContext(ctx, selectAccountByUsername, username)
	if err != nil {
		return entity.ErrAccountByUsernameNotFound(username)
	}

	var account entity.Account
	for rows.Next() {
		err = rows.StructScan(&account)
		if err != nil {
			return entity.ErrInternalServer
		}
	}

	// add account balance with the depositOpt amount
	account.Balance += amount

	// update new account to db
	result, err := a.db.ExecContext(ctx, depositAccountOpt, account.Balance, account.ID, account.Version)
	if err != nil {
		fmt.Println(err)
		return entity.ErrInternalServer
	}

	// check rows affected count
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		fmt.Println(err)
		return entity.ErrInternalServer
	}
	// retry if count rows affected by update less than one
	if rowsAffected == 0 {
		return a.depositOpt(ctx, username, amount, retry+1) // recursion
	}
	return nil
} We use the recursion function to call retry if the rows affected are zero, and we will terminate recursion when is equal to the maximum number of retries ( ). retry maxRetries Comparison We will create some tests to compare the performance of optimistic and pessimistic locking mechanisms. Each test will be done deposit 1000 times and using a varying number of users that 100, 200, and 300. It also uses semaphore for 100 concurrent processes at the time due to the limit in PostgreSQL connection. The test will look like this: func Test_accountUseCase_Deposit(t *testing.T) {
	type fields struct {
		accountRepo accountRepo
	}
	ctx := context.Background()
	tests := []struct {
		name    string
		fields  fields
		wantErr bool
	}{
		{
			name: "pessimistic",
			fields: fields{
				accountRepo: repository.NewAccountRepository(db),
			},
			wantErr: false,
		},
		{
			name: "optimistic",
			fields: fields{
				accountRepo: repository.NewAccountRepositoryOpt(db),
			},
			wantErr: false,
		},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			a := &accountUseCase{
				accountRepo: tt.fields.accountRepo,
			}
			start := time.Now()
			if err := testSaveBulkData(a, ctx); (err != nil) != tt.wantErr {
				t.Errorf("Deposit() error = %v, wantErr %v", err, tt.wantErr)
			}
			fmt.Println("Execution time", time.Since(start))
		})
	}
}

func testSaveBulkData(a *accountUseCase, ctx context.Context) error {
	sem := make(chan struct{}, 100)
	var wg sync.WaitGroup
	for i := 0; i < 1000; i++ {
		wg.Add(1)
		go save(a, ctx, strconv.Itoa((i%100)+1), sem, &wg)
	}
	wg.Wait()
	return nil
}

func save(a *accountUseCase, ctx context.Context, username string, sem chan struct{}, wg *sync.WaitGroup) {
	defer wg.Done()
	sem <- struct{}{}
	err := a.Deposit(ctx, &entity.DepositRequest{Username: username, Amount: 1000})
	if err != nil {
		fmt.Println("Error", err)
	}
	<-sem
} 100 users in 1000 iteration Optimistic Pessimistic In this test, we had similar performance between optimistic and pessimistic locking. But, in optimistic locking, we get around 20-25 failed executions due to max retry. 200 users in 1000 iteration Optimistic Pessimistic In this test, we can see the optimistic function is faster than the pessimistic, but we still see some failed transactions in optimistic even though the probability is very small 0 to 2 failed execution. 300 users in 1000 iteration Optimistic Pessimistic In this test, we can see the difference in performance is getting bigger by about 100 ms. Also, we rarely see failed execution of optimistic locking, although sometimes you can still see one unlucky failed execution. Conclusion As we can see from the test above, optimistic and pessimistic locking have their respective advantages. Optimistic locking is a useful approach when updating concurrent records is expected to be infrequent or locking overhead is high. Whereas, pessimistic locking is a useful approach when the next update can be delayed until the previous update is complete or when concurrent updates of records are expected to occur frequently. References ⁽¹⁾ https://www.ibm.com/docs/en/db2/11.5?topic=overview-optimistic-locking ⁽²⁾ https://www.ibm.com/docs/en/rational-clearquest/7.1.0?topic=clearquest-optimistic-pessimistic-record-locking

Comparing Optimistic and Pessimistic Locking With GO and PostgreSQL

is a distributed, RESTful search and analytics engine that can handle an expanding range of use cases. It offers a full-text search engine with a multitenant capability, an HTTP web interface, and schema-free JSON documents, all with simple installation. Elasticsearch Elasticsearch is a search engine based on the Lucene library. It provides a distributed, multitenant-capable full-text search engine with an HTTP web interface and schema-free JSON documents. In this article, I will be walking you through how to set up elasticsearch on your PC. How to setup, install, and configure Elasticsearch for Mac In order to download Elasticsearch, go to the , select the respective operating system and click on the download button as shown below: Elasticsearch Download Page After downloading, extract the compressed file using the command below: $ tar -xzvf elasticsearch-8.4.1-darwin-aarch64.tar.gz Configuring Elasticsearch After extracting elasticsearch, depending on your use case, you may want to configure elasticsearch to suit you. You can take a look at the config file using the command below: $ vi elasticsearch-8.4.1/config/elasticsearch.yml You can have multiple nodes in your cluster, in order to do this, you need to ensure that the cluster name on your nodes matches. Cluster: # -------------------------------- Cluster ----------------------------------
#
# Use a descriptive name for your cluster:
#
cluster.name: air-elastic
# This is an identifier for the individual nodes in your cluster Node: # -------------------------------- Node ----------------------------------
#
# Use a descriptive name for your cluster:
#
node.name: airscholar-node
# For other configurations, check to fine-tune the configs to suit your use case. elasticsearch.yml Starting up Elasticsearch In order to start up elasticsearch engine use the command below: $ bin/elasticsearch When starting up for the first time, you will be presented with a password that needs to be changed: elasticsearch Changing your password for the first time You should change your password after starting up the elasticsearch for the first time, of course, you can do that using this command: bin/elasticsearch-reset-password -u elastic NOTE: ENSURE YOU KEEP YOUR ELASTIC PASSWORD SAFE, IT WILL BE REQUIRED IN ORDER TO CONNECT TO ELASTICSEARCH Testing Elasticsearch In your browser, goto or the specified hostname and port in your . You will be asked for (elastic) and (the password you changed to when starting elasticsearch for the first time) https://localhost:9200 elasticonfig.yml username password You should see something like this: {
  "name" : "airscholar-node",
  "cluster_name" : "air-elastic",
  "cluster_uuid" : "7g3LmMYFRmyxTyR6ZI-b7A",
  "version" : {
    "number" : "8.4.1",
    "build_flavor" : "default",
    "build_type" : "tar",
    "build_hash" : "2bd229c8e56650b42e40992322a76e7914258f0c",
    "build_date" : "2022-08-26T12:11:43.232597118Z",
    "build_snapshot" : false,
    "lucene_version" : "9.3.0",
    "minimum_wire_compatibility_version" : "7.17.0",
    "minimum_index_compatibility_version" : "7.0.0"
  },
  "tagline" : "You Know, for Search"
} If you have any challenges setting up elasticsearch, feel free to drop a comment, and I will try to respond. You can also check the for more detailed information about elasticsearch. official elasticsearch documentation Kibana Kibana is a source-available data visualization dashboard software for Elasticsearch, whose free and open source successor in OpenSearch is OpenSearch Dashboards. How does Kibana help us in this case you may ask? is a great visualization tool that helps us with our elasticsearch queries and manipulation. It is part of B.E.L.K (Beats, Elasticsearch, Logstash, and Kibana) stack. In case you don't know much about it, follow along and you will be just fine! (A separate article on Kibana can be written as an addendum to this series if you want, let me know in the comment section!) Kibana Setting up and Configuring Kibana In order to download Kibana, go to , and click on the download button. Kibana Download Page After downloading, extract the zip file using this command: $ tar -xzvf kibana-8.4.1-darwin-aarch64.tar.gz
$ cd kibana-8.4.1 There are two ways to configure and connect Kibana to elasticsearch, one way is to manually input the required keys into (in ) or run Kibana and configure it on the UI. I'm choosing the latter 😉! kibana.yml config/kibana.yml Run kibana using this command: bin/kibana When starting Kibana for the first time, you will be presented with this screen: There are two ways to tackle this; Use the elastic enrollment token (usually generated on your terminal when starting elastic for the first time) see below; Copy and paste the enrollment token above to Kibana and click . You will be fine. Configure Elastic Manually configure Kibana with elastic using the elastic URL. Click on check address and click . Configure elastic Enter password generated. Or click forgot password (if you can not recall the password), and paste the command below to your terminal to reset the password. kibana_system bin/elasticsearch-reset-password --username kibana_system You will be asked to enter a verification code and check your terminal (where you are running kibana to retrieve it) That's it! You are all set up. Enter your elastic username and password to log in to kibana and you are in! Querying elastic using Kibana Let's run a sample query to test our connection, shall we? Click on the hamburger (three lines on the top left corner of your screen) Scroll down to the bottom of the page Click on Dev tools You should see something like this: On the left pane is where queries are written, and the right pane is where the outputs are presented. Ensure you click on any part of the query and select the play button. The output of the query will be presented on the right pane. Loading data into elasticsearch To enable us to write our code effectively, we need data loaded into our . We will be using a sample dataset from Kaggle ( ). elasticsearch Download it here Follow the FOUR steps below to load it up into elasticsearch: Open up Kibana ( ) http://localhost:5601 Under Click on Upload a file: Get started by adding integrations Click on import and enter the name of the index you want to put the data in. Click on import (final page) If you made it to this point, you have successfully imported data into elasticsearch. Querying for sample Goto DevTools (Hamburger on the top left corner of the screen > Scroll down to Management > DevTools) Run the query below (select it and click on the play button) GET tmdb_movies/_search If you see this, we are good to go! Now, Let's dive into coding, shall we 😊? NestJS NestJS is a progressive Node. js framework that helps build server-side applications. Nest extends Node. js frameworks like Express or Fastify adding modular organization and a wide range of other libraries to take care of repetitive tasks. It's open-source, uses TypeScript, and is a very versatile Node. Creating a NestJs Application Run the command below to install nestcli and create a new NestJs Application (in the article, the name of the app will be nest-elastic). $ npm i -g @nestjs/cli
$ nest new nest-elastic You will be asked to select a package manager, you can select npm, yarn, or pnpm. I will be selecting yarn (you can choose any other one you want 😉). Your project will be set up and we should be ready to code! Adding elasticsearch to your app Run the command below to add elasticsearch to your and other dependencies: nest-elastic yarn add @elastic/elasticsearch @nestjs/elasticsearch @nestjs/config In your root folder add your file with the following contents: .env ELASTICSEARCH_NODE=https://localhost:9200
ELASTICSEARCH_USERNAME=elastic
ELASTICSEARCH_PASSWORD=elasticPasswordGoesHere
ELASTICSEARCH_MAX_RETRIES=10
ELASTICSEARCH_REQ_TIMEOUT=50000
ELASTICSEARCH_INDEX=tmdb_movies Let's create a separate module that handles only search using elasticsearch. A simple shortcut is to use the command below (you are welcome to do it manually if you want to): nest g resource search Update the to have the content below: search.module.ts import { Module } from '@nestjs/common';
import { SearchService } from './search.service';
import { SearchController } from './search.controller';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { ElasticsearchModule } from '@nestjs/elasticsearch';

@Module({
  imports: [
    ConfigModule,
    ElasticsearchModule.registerAsync({
      imports: [ConfigModule],
      useFactory: async (configService: ConfigService) => ({
        node: configService.get('ELASTICSEARCH_NODE'),
        auth: {
          username: configService.get('ELASTICSEARCH_USERNAME'),
          password: configService.get('ELASTICSEARCH_PASSWORD'),
        },
        maxRetries: configService.get('ELASTICSEARCH_MAX_RETRIES'),
        requestTimeout: configService.get('ELASTICSEARCH_REQ_TIMEOUT'),
      }),
      inject: [ConfigService],
    }),
  ],
  controllers: [SearchController],
  providers: [SearchService],
  exports: [SearchService],
})
export class SearchModule {} Update with the content below: search.service.ts import { ConfigService } from '@nestjs/config';
import { Injectable } from '@nestjs/common';
import { ElasticsearchService } from '@nestjs/elasticsearch';

type dataResponse = {
  UnitPrice: number;
  Description: string;
  Quantity: number;
  Country: string;
  InvoiceNo: string;
  InvoiceDate: Date;
  CustomerID: number;
  StockCode: string;
};

@Injectable()
export class SearchService {
  constructor(
    private readonly esService: ElasticsearchService,
    private readonly configService: ConfigService,
  ) {}

  async search(search: {key: string}) {
    let results = new Set();
    const response = await this.esService.search({
      index: this.configService.get('ELASTICSEARCH_INDEX'),
      body: {
        size: 50,
        query: {
          match_phrase: search
        },
      },
    });
    const hits = response.hits.hits;
    hits.map((item) => {
      results.add(item._source as dataResponse);
    });

    return { results: Array.from(results), total: response.hits.total };
  }
} Now, let's add movies modules: nest g resource movies Update with the content below: movies.controller.ts import { SearchService } from './../search/search.service';
import { Body, Controller, Post } from '@nestjs/common';

@Controller('movies')
export class MoviesController {
  constructor(private readonly searchService: SearchService) {}

  @Post('search')
  async search(@Body() body) {
    return await this.searchService.search(body.data);
  }
} Then movies.module.ts import { SearchModule } from './../search/search.module';
import { Module } from '@nestjs/common';
import { MoviesService } from './movies.service';
import { MoviesController } from './movies.controller';

@Module({
  imports: [SearchModule],
  controllers: [MoviesController],
  providers: [MoviesService],
})
export class MoviesModule {} Finally, update app.module.ts import { MoviesModule } from './movies/movies.module';
import { ConfigModule } from '@nestjs/config';
import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { SearchModule } from './search/search.module';

@Module({
  imports: [MoviesModule, ConfigModule.forRoot(), SearchModule],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {} Your should look like this: package.json {
  "name": "nest-elastic",
  "version": "0.0.1",
  "description": "",
  "author": "Yusuf Ganiyu",
  "private": true,
  "license": "UNLICENSED",
  "scripts": {
    "prebuild": "rimraf dist",
    "build": "nest build",
    "format": "prettier --write \"src/**/*.ts\" \"test/**/*.ts\"",
    "start": "nest start",
    "start:dev": "nest start --watch",
    "start:debug": "nest start --debug --watch",
    "start:prod": "node dist/main",
    "lint": "eslint \"{src,apps,libs,test}/**/*.ts\" --fix",
    "test": "jest",
    "test:watch": "jest --watch",
    "test:cov": "jest --coverage",
    "test:debug": "node --inspect-brk -r tsconfig-paths/register -r ts-node/register node_modules/.bin/jest --runInBand",
    "test:e2e": "jest --config ./test/jest-e2e.json"
  },
  "dependencies": {
    "@elastic/elasticsearch": "^8.4.0",
    "@nestjs/common": "^9.0.0",
    "@nestjs/config": "^2.2.0",
    "@nestjs/core": "^9.0.0",
    "@nestjs/elasticsearch": "^9.0.0",
    "@nestjs/mapped-types": "*",
    "@nestjs/platform-express": "^9.0.0",
    "reflect-metadata": "^0.1.13",
    "rimraf": "^3.0.2",
    "rxjs": "^7.2.0"
  },
  "devDependencies": {
    "@nestjs/cli": "^9.0.0",
    "@nestjs/schematics": "^9.0.0",
    "@nestjs/testing": "^9.0.0",
    "@types/express": "^4.17.13",
    "@types/jest": "28.1.4",
    "@types/node": "^16.0.0",
    "@types/supertest": "^2.0.11",
    "@typescript-eslint/eslint-plugin": "^5.0.0",
    "@typescript-eslint/parser": "^5.0.0",
    "eslint": "^8.0.1",
    "eslint-config-prettier": "^8.3.0",
    "eslint-plugin-prettier": "^4.0.0",
    "jest": "28.1.2",
    "prettier": "^2.3.2",
    "source-map-support": "^0.5.20",
    "supertest": "^6.1.3",
    "ts-jest": "28.0.5",
    "ts-loader": "^9.2.3",
    "ts-node": "^10.0.0",
    "tsconfig-paths": "4.0.0",
    "typescript": "^4.3.5"
  },
  "jest": {
    "moduleFileExtensions": [
      "js",
      "json",
      "ts"
    ],
    "rootDir": "src",
    "testRegex": ".*\\.spec\\.ts$",
    "transform": {
      "^.+\\.(t|j)s$": "ts-jest"
    },
    "collectCoverageFrom": [
      "**/*.(t|j)s"
    ],
    "coverageDirectory": "../coverage",
    "testEnvironment": "node"
  }
} Running the app You can fire up the app in the dev environment using yarn start:dev Testing You can access the full . source code here Setting up a react project You can set up a simple react app using this command (or check out this ): detailed react documentation $ npx create-react-app nest-elastic-frontend Once your app is set up, open it in your favorite IDE, mine is VSCode. We will need to install as a dependency. If you prefer package manager, you will have to run but if you preferred yarn, . axios npm npm install axios yarn add axios We need to update three files public/index.html You need to add bootstrap CDN. (PS: I have removed the comments to reduce the length of the file). <!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <link rel="icon" href="%PUBLIC_URL%/favicon.ico" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <meta name="theme-color" content="#000000" />
    <meta name="description" content="Web site created using create-react-app" />
    <link rel="apple-touch-icon" href="%PUBLIC_URL%/logo192.png" />
    <link rel="manifest" href="%PUBLIC_URL%/manifest.json" />
    <link
      href="//maxcdn.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css"
      rel="stylesheet"
      id="bootstrap-css"
    />
    <title>React App</title>
  </head>
  <body>
    <noscript>You need to enable JavaScript to run this app.</noscript>
    <div id="root"></div>
    <script src="//maxcdn.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script>
    <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  </body>
</html> src/App.js import './App.css';
import axios from 'axios';
import { useState } from 'react';
const App = () => {
  const [searchResponse, setSearchResponse] = useState([]);
  const [totalValue, setTotalValue] = useState();

  const handleChange = async e => {
    const { data } = await axios.post('http://localhost:8000/movies/search', {
      data: {
        title: e.target.value,
      },
    });

    setSearchResponse(data.results);
    setTotalValue(data.total.value);
  };
  return (
    <div className='App'>
      <div className='container search-table'>
        <div className='search-box'>
          <div className='row'>
            <div className='col-md-3'>
              <h5>Search All Fields</h5>
            </div>
            <div className='col-md-9'>
              <input
                type='text'
                id='myInput'
                onChange={handleChange}
                className='form-control'
                placeholder='Search IMDB movies'></input>
            </div>
          </div>
        </div>
        <div className='search-list'>
          <h3>
            {totalValue ?? 0} {totalValue > 1 ? 'Records' : 'Record'} Found
          </h3>
          <table className='table' id='myTable'>
            <thead>
              <tr>
                <th>Title</th>
                <th>Overview</th>
                <th>Revenue:Budget ($)</th>
              </tr>
            </thead>
            <tbody>
              {searchResponse.map((res, idx) => (
                <tr key={idx}>
                  <td className='title'>{res.title}</td>
                  <td>
                    <p>{res.overview}</p>
                    <sub>"{res.tagline}"</sub>
                  </td>
                  <td>
                    <p>
                      <sub>
                        {res.revenue.toLocaleString()}:{res.budget.toLocaleString()}
                      </sub>
                    </p>
                  </td>
                </tr>
              ))}
            </tbody>
          </table>
        </div>
      </div>
    </div>
  );
};

export default App; Lastly, src/index.css body {
  margin: 0;
  font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', 'Roboto', 'Oxygen', 'Ubuntu',
    'Cantarell', 'Fira Sans', 'Droid Sans', 'Helvetica Neue', sans-serif;
  -webkit-font-smoothing: antialiased;
  -moz-osx-font-smoothing: grayscale;
}

code {
  font-family: source-code-pro, Menlo, Monaco, Consolas, 'Courier New', monospace;
}

.search-table {
  padding: 10%;
  margin-top: -6%;
}
.search-box {
  background: #c1c1c1;
  border: 1px solid #ababab;
  padding: 3%;
}
.search-box input:focus {
  box-shadow: none;
  border: 2px solid #eeeeee;
}
.search-list {
  background: #fff;
  border: 1px solid #ababab;
  border-top: none;
}
.search-list h3 {
  background: #eee;
  padding: 3%;
  margin-bottom: 0%;
}

.title {
  word-wrap: normal;
  width: 200px;
} Running your app Start your react app with or depending on your preferred package manager. yarn start npm start Testing your app Thanks for staying tuned! Here is the link to . the source code Also published . here

Create a Full Autocomplete Search Application with Elasticsearch, Kibana, NestJS and React

Background Ever since the release of , has been providing strong dynamic management capabilities to the ShardingSphere ecosystem. ShardingSphere 5.0.0 DistSQL Thanks to DistSQL, users have been empowered to do the following: Create logical databases online. Dynamically configure rules (i.e. sharding, data encryption, read/write splitting, database discovery, shadow DB, and global rules). Adjust storage resources in real-time. Switch transaction types instantly. Turn SQL logs on and off at any time. Preview the SQL routing results. At the same time, in the context of increasingly diversified scenarios, more and more DistSQL features are being created and a variety of valuable syntaxes have been gaining popularity among users. Overview This post takes data sharding as an example to illustrate DistSQL’s application scenarios and related sharding methods. A series of DistSQL statements are concatenated through practical cases, to give you a complete set of practical DistSQL sharding management schemes, which create and maintain distributed databases through dynamic management. The following DistSQL will be used in this example: Practical Case Required scenarios Create two sharding tables: and . t_order t_order_item For both tables, database shards are carried out with the field, and table shards with the field. user_id order_id The number of shards is 2 databases * 3 tables. As shown in the figure below: Setting up the environment Prepare a MySQL database instance for access. Create two new databases: and . demo_ds_0 demo_ds_1 Here we take MySQL as an example, but you can also use PostgreSQL or openGauss databases. Deploy and . ZooKeeper acts as a governance center and stores ShardingSphere metadata information. Apache ShardingSphere-Proxy 5.1.2 Apache ZooKeeper Configure in the directory as follows: server.yaml Proxy conf mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: localhost:2181  # ZooKeeper address
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: false
rules:
  - !AUTHORITY
    users:
      - root@%:root Start ShardingSphere-Proxy and connect it to Proxy using a client, for example: mysql -h 127.0.0.1 -P 3307 -u root -p Creating a distributed database CREATE DATABASE sharding_db;
USE sharding_db; Adding storage resources Add storage resources corresponding to the prepared database. MySQL ADD RESOURCE ds_0 (
    HOST=127.0.0.1,
    PORT=3306,
    DB=demo_ds_0,
    USER=root,
    PASSWORD=123456
), ds_1(
    HOST=127.0.0.1,
    PORT=3306,
    DB=demo_ds_1,
    USER=root,
    PASSWORD=123456
); View storage resources mysql> SHOW DATABASE RESOURCES\G;
*************************** 1. row ***************************
                           name: ds_1
                           type: MySQL
                           host: 127.0.0.1
                           port: 3306
                             db: demo_ds_1
                            -- Omit partial attributes
*************************** 2. row ***************************
                           name: ds_0
                           type: MySQL
                           host: 127.0.0.1
                           port: 3306
                             db: demo_ds_0
                            -- Omit partial attributes Adding \G to the query statement aims to make the output format more readable, and it is not a must. ShardingSphere’s sharding rules support regular sharding and automatic sharding. Both sharding methods have the same effect. The difference is that the configuration of automatic sharding is more concise, while regular sharding is more flexible and independent. Creating sharding rules Please refer to the following links for more details on automatic sharding: Intro to DistSQL-An Open Source and More Powerful SQL AutoTable: Your Butler-Like Sharding Configuration Tool Next, we’ll adopt regular sharding and use the expression algorithm to implement the sharding scenarios described in the requirements. INLINE Primary key generator The primary key generator can generate a secure and unique primary key for a data table in a distributed scenario. For details, refer to the document . Distributed Primary Key Create the primary key generator. CREATE SHARDING KEY GENERATOR snowflake_key_generator (
TYPE(NAME=SNOWFLAKE)
); Query primary key generator mysql> SHOW SHARDING KEY GENERATORS;
+-------------------------+-----------+-------+
| name                    | type      | props |
+-------------------------+-----------+-------+
| snowflake_key_generator | snowflake | {}    |
+-------------------------+-----------+-------+
1 row in set (0.01 sec) Sharding algorithm Create a database sharding algorithm, used by and in common. t_order t_order_item -- Modulo 2 based on user_id in database sharding
CREATE SHARDING ALGORITHM database_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}"))
); Create different table shards algorithms for and . t_order t_order_item -- Modulo 3 based on order_id in table sharding
CREATE SHARDING ALGORITHM t_order_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_${order_id % 3}"))
);
CREATE SHARDING ALGORITHM t_order_item_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_item_${order_id % 3}"))
); Query sharding algorithm mysql> SHOW SHARDING ALGORITHMS;
+---------------------+--------+---------------------------------------------------+
| name                | type   | props                                             |
+---------------------+--------+---------------------------------------------------+
| database_inline     | inline | algorithm-expression=ds_${user_id % 2}            |
| t_order_inline      | inline | algorithm-expression=t_order_${order_id % 3}      |
| t_order_item_inline | inline | algorithm-expression=t_order_item_${order_id % 3} |
+---------------------+--------+---------------------------------------------------+
3 rows in set (0.00 sec) Default sharding strategy Sharding strategy consists of sharding key and sharding algorithm. Please refer to for its concept. Sharding Strategy Sharding strategy consists of and . databaseStrategy tableStrategy Since and have the same database sharding field and sharding algorithm, we create a default strategy that will be used by all shard tables with no sharding strategy configured: t_order t_order_item Create a default database sharding strategy CREATE DEFAULT SHARDING DATABASE STRATEGY (
TYPE=STANDARD,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline
); Query default strategy mysql> SHOW DEFAULT SHARDING STRATEGY\G;
*************************** 1. row ***************************
                    name: TABLE
                    type: NONE
         sharding_column:
 sharding_algorithm_name:
 sharding_algorithm_type:
sharding_algorithm_props:
*************************** 2. row ***************************
                    name: DATABASE
                    type: STANDARD
         sharding_column: user_id
 sharding_algorithm_name: database_inline
 sharding_algorithm_type: inline
sharding_algorithm_props: {algorithm-expression=ds_${user_id % 2}}
2 rows in set (0.00 sec) The default table sharding strategy is not configured, so the default strategy of is . TABLE NONE Sharding rules The primary key generator and sharding algorithm are both ready. Now create sharding rules. 1. t_order CREATE SHARDING TABLE RULE t_order (
DATANODES("ds_${0..1}.t_order_${0..2}"),
TABLE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_inline),
KEY_GENERATE_STRATEGY(COLUMN=order_id,KEY_GENERATOR=snowflake_key_generator)
); specifies the data nodes of shard tables. specifies the table strategy, among which uses created sharding algorithm ; specifies the primary key generation strategy of the table. Skip this configuration if primary key generation is not required. DATANODES TABLE_STRATEGY SHARDING_ALGORITHM t_order_inline KEY_GENERATE_STRATEGY 2. t_order_item CREATE SHARDING TABLE RULE t_order_item (
DATANODES("ds_${0..1}.t_order_item_${0..2}"),
TABLE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_item_inline),
KEY_GENERATE_STRATEGY(COLUMN=order_item_id,KEY_GENERATOR=snowflake_key_generator)
); Query sharding rules mysql> SHOW SHARDING TABLE RULES\G;
*************************** 1. row ***************************
                            table: t_order
                actual_data_nodes: ds_${0..1}.t_order_${0..2}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_${order_id % 3}
              key_generate_column: order_id
               key_generator_type: snowflake
              key_generator_props:
*************************** 2. row ***************************
                            table: t_order_item
                actual_data_nodes: ds_${0..1}.t_order_item_${0..2}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_item_${order_id % 3}
              key_generate_column: order_item_id
               key_generator_type: snowflake
              key_generator_props:
2 rows in set (0.00 sec) 💡So far, the sharding rules for and have been configured. t_order t_order_item A bit complicated? Well, you can also skip the steps of creating the primary key generator, sharding algorithm, and default strategy, and complete the sharding rules in one step. Let's see how to make it easier. Syntax Now, if we have to add a shard table , we can create sharding rules as follows: t_order_detail CREATE SHARDING TABLE RULE t_order_detail (
DATANODES("ds_${0..1}.t_order_detail_${0..1}"),
DATABASE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM(TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}")))),
TABLE_STRATEGY(TYPE=STANDARD,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM(TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_detail_${order_id % 3}")))),
KEY_GENERATE_STRATEGY(COLUMN=detail_id,TYPE(NAME=snowflake))
); **Note: **The above statement specified database sharding strategy, table strategy, and primary key generation strategy, but it didn’t use existing algorithms. Therefore, the DistSQL engine automatically uses the input expression to create an algorithm for the sharding rules of . Now the primary key generator, sharding algorithm, and sharding rules are as follows: t_order_detail Primary key generator mysql> SHOW SHARDING KEY GENERATORS;
+--------------------------+-----------+-------+
| name                     | type      | props |
+--------------------------+-----------+-------+
| snowflake_key_generator  | snowflake | {}    |
| t_order_detail_snowflake | snowflake | {}    |
+--------------------------+-----------+-------+
2 rows in set (0.00 sec) Sharding algorithm mysql> SHOW SHARDING ALGORITHMS;
+--------------------------------+--------+-----------------------------------------------------+
| name                           | type   | props                                               |
+--------------------------------+--------+-----------------------------------------------------+
| database_inline                | inline | algorithm-expression=ds_${user_id % 2}              |
| t_order_inline                 | inline | algorithm-expression=t_order_${order_id % 3}        |
| t_order_item_inline            | inline | algorithm-expression=t_order_item_${order_id % 3}   |
| t_order_detail_database_inline | inline | algorithm-expression=ds_${user_id % 2}              |
| t_order_detail_table_inline    | inline | algorithm-expression=t_order_detail_${order_id % 3} |
+--------------------------------+--------+-----------------------------------------------------+
5 rows in set (0.00 sec) Sharding rules mysql> SHOW SHARDING TABLE RULES\G;
*************************** 1. row ***************************
                            table: t_order
                actual_data_nodes: ds_${0..1}.t_order_${0..2}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_${order_id % 3}
              key_generate_column: order_id
               key_generator_type: snowflake
              key_generator_props:
*************************** 2. row ***************************
                            table: t_order_item
                actual_data_nodes: ds_${0..1}.t_order_item_${0..2}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_item_${order_id % 3}
              key_generate_column: order_item_id
               key_generator_type: snowflake
              key_generator_props:
*************************** 3. row ***************************
                            table: t_order_detail
                actual_data_nodes: ds_${0..1}.t_order_detail_${0..1}
              actual_data_sources:
           database_strategy_type: STANDARD
         database_sharding_column: user_id
 database_sharding_algorithm_type: inline
database_sharding_algorithm_props: algorithm-expression=ds_${user_id % 2}
              table_strategy_type: STANDARD
            table_sharding_column: order_id
    table_sharding_algorithm_type: inline
   table_sharding_algorithm_props: algorithm-expression=t_order_detail_${order_id % 3}
              key_generate_column: detail_id
               key_generator_type: snowflake
              key_generator_props:
3 rows in set (0.01 sec) In the statement, , , and can all reuse existing algorithms. Note: CREATE SHARDING TABLE RULE DATABASE_STRATEGY TABLE_STRATEGY KEY_GENERATE_STRATEGY Alternatively, they can be defined quickly through syntax. The difference is that additional algorithm objects are created. Users can use it flexibly based on scenarios. After the configuration verification rules are created, you can verify them in the following ways: Checking node distribution DistSQL provides for checking node distribution and users can quickly learn the distribution of shard tables. SHOW SHARDING TABLE NODES We can see the node distribution of the shard table is consistent with what is described in the requirement. SQL Preview Previewing SQL is also an easy way to verify configurations. Its syntax is : PREVIEW SQL 1. Query with no shard key with all routes Specify to query with a single database route user_id Specify and with a single table route user_id order_id Single-table routes scan the least shard tables and offer the highest efficiency. DistSQL auxiliary query During the system maintenance, algorithms or storage resources that are no longer in use may need to be released, or resources that need to be released may have been referenced and cannot be deleted. The following DistSQL can solve these problems. Query unused resources Syntax: SHOW UNUSED RESOURCES 2. Sample: Query unused primary key generator Syntax: SHOW UNUSED SHARDING KEY GENERATORS Sample: Query unused sharding algorithm Syntax: SHOW UNUSED SHARDING ALGORITHMS Sample: Query rules that use the target storage resources 1.Syntax: SHOW RULES USED RESOURCE 2.Sample: All rules that use the resource can be queried, not limited to the . Sharding Rule Query sharding rules that use the target primary key generator Syntax: SHOW SHARDING TABLE RULES USED KEY GENERATOR 2. Sample: Query sharding rules that use the target algorithm Syntax: SHOW SHARDING TABLE RULES USED ALGORITHM 2. Sample: Conclusion This post takes the data sharding scenario as an example to introduce ’s applications and methods. DistSQL DistSQL provides flexible syntax to help simplify operations. In addition to the algorithm, DistSQL supports standard sharding, compound sharding, Hint sharding, and custom sharding algorithms. More examples will be covered in the coming future. INLINE If you have any questions or suggestions about , please feel free to post them on the GitHub Issue list. Apache ShardingSphere Project Links: ShardingSphere Github ShardingSphere Twitter ShardingSphere Slack Contributor Guide GitHub Issues Contributor Guide References Concept-DistSQL Concept-Distributed Primary Key Concept-Sharding Strategy Concept INLINE Expression Built-in Sharding Algorithm User Manual: DistSQL Author Middleware R&D Engineer & Apache ShardingSphere Committer. Jiang Longtao SphereEx Longtao focuses on the R&D of DistSQL and related features. Also Published Here

Applications of DistSQL: Build a Dynamic Distributed Database

How the ZooKeeper Solves the Dining Philosophers Problem

Stories, News & Mentions about MAXRETAIL

Maxretail’s Stories on HackerNoon