【DataModel】Try to create a Sharperlight data model

# Introduction #
I've been posting tips for business solutions using the product Sharperlight, but I'd like to take a look at how to create a **DataModel**, which is fundamental to using Sharperlight, or you can't start without it.

Sharperlight dynamically generates queries and issues them to extract data from your database.
The **DataModel** helps the Sharperlight engine generate queries.
# DataModel #
The core of Sharperlight. Schema information of the database to connect to, extended business logic, etc. are defined in a way that the Sharperlight engine can understand.
Well, it seems difficult to express it like this, but it's easy to create a basic one, so let's see how to do it together.
Shall we use this database as an example?
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7642994d-1aee-1ce5-eedf-8f2689f352c6.png)
# Studio #
I use Sharperlight **Studio** to create the DataModel.
## Start ##
Launch Studio from the Sharperlight application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c4ce6b84-0bc9-351f-8521-554f3b3d33eb.png)
When the studio starts, let's start creating with the `New` icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/20719d38-30fe-7740-522f-63124877a6c7.png)
## Setting basic information ##
In the `New Product` dialog, specify a Description and Unique Code. The Unique Code is the key code for using this DataModel in the future, so give it a short but easy-to-understand code.
After that, specify the platform of the database to connect to and proceed with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8a32500e-6d12-5059-4a67-690d84ca0f4a.png)
## Setting database connection information ##
Here we configure the connection to the database. This is for loading the database schema information into the new DataModel. Enter the connection information and check the operation with the connection test! If successful, press the `OK` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5d1676ea-5186-fabc-e093-06e20295ca6e.png)
A message will appear asking if you want to load the schema, so click the `Yes` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/92234b4b-6327-19bd-826d-421eb94fd58a.png)
## Importing Schema ##
This dialog imports a schema. Get the schema information with the `Get Tables` button. The retrieved information is displayed in a tree format. Tick the necessary schema information and import it with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b5828506-a28e-64a0-f52b-43dd805a229e.png)
You can use the `Schema Owner` and `Tables` filters if you have multiple schemas in your database, etc.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d8b237a7-5140-8fe0-da7a-76be0d62c503.png)
You will see a message like this when the schema is successfully imported. Proceed with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ed76f9f0-26d0-6941-59ef-16b823666afe.png)
The database schema has been imported into the DataModel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/69dca273-ad08-20e1-e926-882a5c537326.png)
Here you can make various adjustments to your liking, but I won't go into it here.
## Saving ##
Save the data model with the `Save` icon.
A warning message will be displayed, so press the `OK` button to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/37148dff-f2c7-44bd-3580-cddc0681bdc9.png)
This is the specified location, so save it with the `Save` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1668a19f-4490-a567-42d8-4c505d18072a.png)
You will then be prompted to set the database connection information required for all Sharperlight applications to use this DataModel. Proceed with the `Yes` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9309cd39-d810-76db-6d1b-2c17d95ae68b.png)
The Sharperlight `Client Setup` will start, so enter the database connection information, test it, and save it with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/11b4bbe9-7125-9d46-75bd-dcc854e6e009.png)
# Operation Check #
Launch the Sharperlight `Query Builder` from the Applications menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/38645497-bc85-57b7-8690-c46312e4878c.png)
After launching, open the search window with the `Search` button on the Product. You can see the DataModel we created earlier. Please choose it. In all Sharperlight applications, `Product` refers to `DataModel`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e7f07d6c-3ed2-6a2a-d676-f14d22ddf5e9.png)
Then click the table `Search` button. You can see the schema information imported into the DataModel. Here we will select the **Sales Detail** table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8b38a4bb-12be-e83a-37fc-27956410acc4.png)
The field list of the selected table is displayed in the `Selection` area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f16a28ab-1907-a4ed-0f8c-c8de6a59a06c.png)
Select `Output All` from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c68e24e5-f7a1-2580-2793-51509c2d851c.png)
All fields are now set in the `Output` area. Try executing the query with the `Preview` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3b005c5f-5dd9-7ac5-cb9b-1c0d0f1a76e0.png)
Data extracted.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a388e45b-5c39-ac9d-bba8-1d2a1be82193.png)
You can check what kind of SQL statement has been generated in the submenu of the information icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a7a06d67-bda7-987a-6784-0f567eafb24d.png)
The generated SQL statement looks like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0ca84a9c-bfe4-e14b-db19-f19fd3116a55.png)
# In Conclusion #
I've explained it briefly from creating a data model to checking the operation.
There are many more settings that can be configured in the DataModel, and I hope to introduce them later.
I hope this article helps you in your work.