Tag: sql

PostgreSQL TRUNCATE TABLE

- by mahmood 
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

Remove all data from one table

TRUNCATE TABLE table_name;

Besides removing data, you may want to reset the values in the identity column by using the RESTART IDENTITY option like this:

TRUNCATE TABLE table_name 
RESTART IDENTITY;

Remove all data from a table that has foreign key references

TRUNCATE TABLE table_name 
CASCADE;

References
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-truncate-table/
https://www.postgresql.org/docs/current/sql-truncate.html

Get Table and Columns Name of mapped entity in Entity Framework Core

- by mahmood 
var context = new BiContext();

var entityType = context.Model.FindEntityType(typeof(InvoiceDetail));

var tableName = entityType.GetTableName();
Console.WriteLine(tableName);
var properties = entityType.GetProperties();

# Property Name in Class
foreach (var p in properties)
{
    Console.WriteLine(p.Name);
}

# Column Name in Table
foreach (var p in properties)
{
    Console.WriteLine(p.GetColumnName());
}

References
https://stackoverflow.com/questions/45667126/how-to-get-table-name-of-mapped-entity-in-entity-framework-core

Create an Index using JPA

- by mahmood 
@Entity
@Table(name = "region",
       indexes = {@Index(name = "my_index_name",  columnList="iso_code", unique = true),
                  @Index(name = "my_index_name2", columnList="name",     unique = false)})
public class Region{

    @Column(name = "iso_code", nullable = false)
    private String isoCode;

    @Column(name = "name", nullable = false)
    private String name;

}

or 

@Entity
@Table(name    = "company__activity", 
       indexes = {@Index(name = "i_company_activity", columnList = "activity_id,company_id")})
public class CompanyActivity{

References
http://stackoverflow.com/questions/3405229/specifying-an-index-non-unique-key-using-jpa

Store the result of query into a variable in MySQL

- by mahmood 
INSERT INTO users (first_name, last_name, password, user_name)
VALUES (N'ادمین',
        N'ادمین',
        N'c7ad44cbad762a5da0a452f9e854fdc1e0e7a52a38015f23f3eab1d80b931dd472634dfac71cd34ebc35d16ab7fb8a90c81f975113d6c7538dc69dd8de9077ec',
        N'admin');

INSERT INTO permissions (id, name) VALUES (1, N'ادمین');

SET @insertedId := (SELECT id
                    FROM users
                    WHERE user_name = N'admin');

INSERT INTO user_permissions (permission_id, user_id)
VALUES (1, @insertedId);