databasesql

Step-by-step Camp VS 2008 +. NET 3.5 (5) – LINQ query operators Select, Where, OrderBy, the OrderByDescending GroupBy, Join, GroupJoin

Step-by-step Camp VS 2008 +. NET 3.5 (5) – LINQ query operators Select, Where the OrderBy, the OrderByDescending, and the GroupBy, Join, GroupJoin and its corresponding query syntax

Author:

Introduction
· Select – Select options; delay
Where – Where query; delay
OrderBy – sort of a collection of positive sequence specified expression; delay
The · the OrderByDescending delay – by the specified expression to the collection in reverse order;
· GroupBy – grouping; delay
· Join – Join query; delay
GroupJoin – Group Join query; delay
The above query operator corresponding to the query syntax

Example
Summary.aspx.cs

usingSystem;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;using System.Collections.Generic;
using DAL;

public partial class LINQ_Summary: System.Web.UI.Page
{
NorthwindDataContext _ctx =   new the NorthwindDataContext ();
string [] _ary =   null;

protected   void Page_Load (object sender, EventArgs e)
{
_ary =   new   string [] {“Asp.net”, “csharp”, “xhtml”, “css”, “javascript”,
“Wcf”, “wpf”, “silverlight”, “linq”, “wf”,
“Sqlserver”, “asp.net ajax”, “ssis”, “ssas”, “ssrs”};

/ / Select – Select options; delay
Summary_Select ();

/ / Where – Where inquiries; delay
Summary_Where ();

/ / OrderBy – sort of a collection of positive sequence specified expression; delay
/ / The the OrderByDescending – expression specified collection reverse order; delay
Summary_OrderBy_OrderByDescending ();

/ / GroupBy – grouping; delay
Summary_GroupBy ();

/ / Join – Join query; delay
Summary_Join ();

/ / The GroupJoin – Group Join query; delay
Summary_GroupJoin ();
}
}

Select – Select options; delay

    / ** / / / /   <summary>
/ / / Select – Select options; delay
/ / /   </ Summary>
void Summary_Select ()
{
/ / Use the Select query operators
var categories = _ctx.Categories.Select (
c =>   new   {CategoryName =   Category Name:   The + c.CategoryName}); 

foreach (var c in categories)
{
result.InnerHtml + = c.CategoryName +   <br /> “;
}
result.InnerHtml + =   <br /> “; 

/ / Above the Select query operators corresponding query syntax
var categories2 = from c in _ctx.Categories
select new   {CategoryName =   Category Name:   + C.CategoryName}; 

foreach (var c in categories2)
{
result.InnerHtml + = c.CategoryName +   <br /> “;
}
result.InnerHtml + =   <br /> “;
}

The result
Category Name: Beverages
Category Name: Condiments
Category Name: Confections
Category: Dairy Products
Category Name: Grains / Cereals
Category Name: Meat / Poultry
Category Name: Produce
Category Name: Seafood

Where – Where inquiries; delay

    / ** / / / /   <summary>
/ / / Where – Where inquiries; delay
/ / /   </ Summary>
void Summary_Where ()
{
/ / Where query operators
var ary = _ary.Where (a => a.StartsWith (“w”) && a.EndsWith (“f”)); 

foreach (string s in ary)
{
result.InnerHtml + = s +   <br /> “;
}
result.InnerHtml + =   <br /> “; 

/ / Where the above query operators corresponding to the query syntax
var ary2 = from a in _ary
where a.StartsWith (“w”) && a.EndsWith (“f”)
select a; 

foreach (string s in ary2)
{
result.InnerHtml + = s +   <br /> “;
}
result.InnerHtml + =   <br /> “;
}

The result
wcf
wpf
wf

OrderBy – sort of a collection of positive sequence specified expression; delay
The OrderByDescending – by the specified expression to the collection in reverse order; delay

    / ** / / / /   <summary>
/ / / OrderBy – sort of a collection of positive sequence specified expression; delay
/ / / The OrderByDescending – the expression specified collection reverse order; delay
/ / /   </ Summary>
void Summary_OrderBy_OrderByDescending ()
{
/ / OrderBy query operators.
var ary = (from a in _ary
select a). OrderBy (a => a.Length); / / OrderByDescending OrderBy usage of the same 

foreach (string s in ary)
{
result.InnerHtml + = s +   <br /> “;
}
result.InnerHtml + =   <br /> “; 

/ / OrderBy above, query operator corresponding to the query syntax
var ary2 = from a in _ary
the orderby a.Length ascending / / orderby xxx descending and the orderby xxx Ascending usage
select a; 

foreach (string s in ary2)
{
result.InnerHtml + = s +   <br /> “;
}
result.InnerHtml + =   <br /> “;
}

The result
wf
css
wcf
wpf
linq
ssis
ssas
ssrs
xhtml
csharp
asp.net
sqlserver
javascript
silverlight
asp.net ajax

GroupBy – grouping; delay

    / ** / / / /   <summary>
/ / / GroupBy – grouping; delay
/ / /   </ Summary>
the void Summary_GroupBy ()
{
/ / Use the GroupBy query operators
var list = (from a in _ary
select a). GroupBy (a => a.length) Select (
g =>   new   {Group = g.Key, Member = g}); 

foreach (var g in list)
{
result.InnerHtml + = g.Group +   “Characters: <br />”; 

foreach (string the s in g.Member)
{
result.InnerHtml + =   “-“   + S +   <br /> “;
}
}
result.InnerHtml + =   <br /> “; 

/ / GroupBy above, query operator corresponding to the query syntax
var list2 = from a in _ary
group a by a.Length into g
select new   {Group = g.Key, Member = g}; 

foreach (var g in list2)
{
result.InnerHtml + = g.Group +   “Characters: <br />”; 

foreach (string the s in g.Member)
{
result.InnerHtml + =   “-“   + S +   <br /> “;
}
}
result.InnerHtml + =   <br /> “;
}

The result
Seven characters:
– Asp net
6 characters:
– CSharp
5 characters:
– XHTML
Three characters:
– CSS
– WCF
– WPF
10 characters:
– Javascript
11 characters:
– Silverlight
4 characters:
– LINQ
– SSIS
– SSAS
– SSRS
2 characters:
– Wf
9 characters:
– Sqlserver
12 characters:
– Asp net ajax

Join – Join query; delay

    / ** / / / /   <summary>
/ / / Join – Join query; delay
/ / /   </ Summary>
void Summary_Join ()
{
/ / Use the Join query operators
var products = _ctx.Products.Join (
_ctx.Categories
p => p.CategoryID,
c => c.CategoryID
(P, c) =>   new   {C.CategoryName, p.ProductName}). Take (5); 

foreach (var p in products)
{
result.InnerHtml + = p.CategoryName +   “-“   + P.ProductName +   <br /> “;
}
result.InnerHtml + =   <br /> “; 

/ / Join the above query operators corresponding query syntax
var products2 = (from p in _ctx.Products
join c in _ctx.Categories
on p.CategoryID equals c.CategoryID
select new   {C.CategoryName, p.ProductName}). Take (5); 

foreach (var p in products2)
{
result.InnerHtml + = p.CategoryName +   “-“   + P.ProductName +   <br /> “;
}
result.InnerHtml + =   <br /> “;
}

The result
Beverages – Chai
Beverages – Chang
Condiments – Aniseed Syrup
Condiments – Chef Anton’s Cajun Seasoning
Condiments – Chef Anton’s Gumbo Mix

The GroupJoin – grouping and Join queries; delay

    / ** / / / /   <summary>
/ / / GroupJoin – Group Join query; delay
/ / /   </ Summary>
void Summary_GroupJoin ()
{
/ / Use the GroupJoin query operators
var products = _ctx.Categories.GroupJoin (
_ctx.Products
c => c.CategoryID
p => p.CategoryID,
(P, g) =>   new   {P.CategoryName, ProductCount = g.Count ()}); 

foreach (var g in Products)
{
result.InnerHtml + = g.CategoryName +   “:”   + G.ProductCount +   <br /> “; 

}
result.InnerHtml + =   <br /> “; 

/ / The GroupJoin with the above query operators corresponding query syntax
var products2 = from c in _ctx.Categories
join p in _ctx.Products on c.CategoryID equals p.CategoryID into g
select new   {CategoryName = c.CategoryName, ProductCount = g.Count ()}; 

foreach (var g in products2)
{
result.InnerHtml + = g.CategoryName +   “:”   + G.ProductCount +   <br /> “; 

}
result.InnerHtml + =   <br /> “;
}

The result
Beverages: 12
Condiments: 12
Confections: 13
Dairy Products: 10
Grains / Cereals: 7
Meat / Poultry: 6
Produce: 5
Seafood: 12

OK

Posted by databasesql

Bit map block in the the measurement uniform size of the table space 1 bit can manage to how much space

Previous article, has been discussed in the automatically assigned locally managed tablespaces, 1 bit in the bit map block can manage how much space measure in the space of locally managed tablespaces of uniform size, bit map block 1 bit can manage how much space.

SQL> show parameter block_size

NAME TYPE VALUE
————————————————– ———
db_block_size integer 8192

SQL> select * from v $ version;

BANNER
————————————————– ————–
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Prod
PL / SQL Release 10.2.0.3.0 – Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 – Production
NLSRTL Version 10.2.0.3.0 – Production

SQL> create tablespace lmt datafile ‘C :/ ORACLE/PRODUCT/10.2.0/ORADATA/ROBINSON/DATAFILE/lmt.dbf’ size 100m
2 extent management local uniform size 1m;

Tablespace created

SQL> create table test tablespace lmt as select * from dba_objects;

Table created

SQL> select segment_name, tablespace_name, header_file, header_block, blocks from dba_segments where tablespace_name = ‘LMT’;

SEGMENT_NA TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BLOCKS
————————————————– ————-
TEST LMT 6 12 768

LMT table space, you can see the table test file number 6, the first paragraph of 12

SQL> alter system dump datafile 6 block min 1 block max 12;
The system has been changed.

Part of the dump file

Start dump data blocks tsn: 10 file #: 6 minblk 1 maxblk 12
Block 1 (file header) not dumped: use dump file header command
buffer tsn: 10 rdba: 0x01800002 (6/2)
scn: 0x0000.001d400c seq: 0x02 flg: 0x04 tail: 0x400c1d02
frmt: 0x02 chkval: 0xbbd8 type: 0x1d = KTFB Bitmapped File Space Header
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0x08127800 to 0x08129800
8127800 0000A21D 01800002 001D400C 04020000 [……… @ ……]
8127810 0000BBD8 00000006 00000080 00003200 […………. 2 ..]
812,782,000,000,001 00000000 0,000,000,000,000,007 […………….]
8127830 00003188 00000006 0000005D 00000000 [.1 ……] …….]
812,784,000,000,000 00000000 00000000 00000000 […………….]
812,785,000,000,289 0,000,008,000,000,000 00000000 […………….]
812,786,000,000,000 00000000 00000000 00000000 […………….]
Repeat 504 times
81297F0 00000000 00000000 00000000 400C1D02 […………… @]
File Space Header Block:
Header Control:
RelFno: 6, Unit: 128, Size: 12800, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 7, Tail: 12680, First: 6, Free: 93
The deallocation SCN: 0.0
Header Opcode:
Save: No Pending Op
buffer tsn: 10 rdba: 0x01800003 (6/3)
scn: 0x0000.001d400c seq: 0x01 flg: 0x04 tail: 0x400c1e01
frmt: 0x02 chkval: 0x4e4c type: 0x1e = KTFB Bitmapped File Space Bitmap
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0x08127800 to 0x08129800
8127800 0000A21E 01800003 001D400C 04010000 [……… @ ……]
8127810 00004E4C 00000006 00000009 00000000 [LN …………..]
8127820 00000006 0000F7FA 00000000 00000000 […………….]
8127830 00000000 00000000 0000003F 00000000 [……..? …….]
812,784,000,000,000 00000000 00000000 00000000 […………….]
Repeat 506 times
81297F0 00000000 00000000 00000000 400C1E01 […………… @]
File Space Bitmap Block:
Bitmap Control:
RelFno: 6, BeginBlock: 9, Flag: 0, First: 6, Free: 63482 —– 6 management space
3F00000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

…………………………… Omitted certain …………… ………….

buffer tsn: 10 rdba: 0x01800009 (6/9) —– Note, Oracle set aside the same the six block to manage the free space, two one-bit block 9,10
scn: 0x0000.001d400f seq: 0x03 flg: 0x04 tail: 0x400f2003
frmt: 0x02 chkval: 0x4a4a type: 0x20 = FIRST LEVEL BITMAP BLOCK
Note that there are two one-bit block, a 9, a 10, 11 for the two-bit block, 12 for the first paragraph.

You can see First = 6, then guess what, on behalf of the six-what?

SQL> select count (*) from dba_extents where tablespace_name = ‘LMT’;

COUNT (*)
———-
6

Interval number equal to 6, so I guess 1 bit represents an extent, to continue the experiment

SQL> insert into test select * from dba_objects nologging;

49962 rows inserted
SQL> commit;

Commit complete

SQL> select count (*) from dba_extents where tablespace_name = ‘LMT’;

COUNT (*)
———-
11

Range increased to 11

SQL> alter system dump datafile 6 block 3;

The system has been changed.

DUMP file:

Start dump data blocks tsn: 10 file #: 6 minblk 3 maxblk 3
buffer tsn: 10 rdba: 0x01800003 (6/3)
scn: 0x0000.001d42f6 seq: 0x01 flg: 0x00 tail: 0x42f61e01
frmt: 0x02 chkval: 0x0000 type: 0x1e = KTFB Bitmapped File Space Bitmap
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0x04807800 to 0x04809800
4807800 0000A21E 01800003 001D42F6 00010000 [……… B. …..]
480,781,000,000,000 0,000,000,600,000,009 00000000 […………….]
4807820 0000000B 0000F7F5 00000000 00000000 […………….]
4807830 00000000 00000000 000007FF 00000000 […………….]
480,784,000,000,000 00000000 00000000 00000000 […………….]
Repeat 506 times
48097F0 00000000 00000000 00000000 42F61E01 […………… B]
File Space Bitmap Block:
Bitmap Control:
RelFno: 6, BeginBlock: 9, Flag: 0, First: 11, Free: 63477
FF07000000000000 0000000000000000 0000000000000000 0000000000000000

First = 11 means that 11 bits,

Therefore we can say locally managed tablespaces of uniform size, 1 bit in the bit map block an extent, so the bit can manage how much space that you set.

Posted by databasesql

mysql configuration

I how not connected, to view the information in the user table of the mysql is allowed to connect from the outside, and later directly telnet to port 3306 of the server can not connect directly on the server from a local connection on the server MySQL Server netstat-naot looked at the original 3306 port binding ip 127.0.0.1 regarded know why, look at my.cnf such a line bind-address = 127.0.0.1 like never did not notice this configuration, in the case commented out, restart about mysql, finally connected on the

Posted by databasesql

The SQL2005 ROW_NUMBER () function usage

2005 than in 2000 added a few functions, is the ROW_NUMBER (), rank () dense_rank, (), ntile (takes) The following examples briefly explain.

create table gg (sname varchar (10), sort varchar (10), num int)

go

 

insert into gg

select ‘White peony root’, ‘root vegetables’, 55

union all

select ‘Pinellia law’, ‘tubers’, 78

union all

select ‘Bupleurum’, ‘tubers’, 60

union all

select ‘Chuanxiong’, ‘tubers’, 99

union all

select ‘Days censer’, ‘grass’, 68

union all

select ‘Rushes’, ‘grass’, 55

union all

select ‘Solanum nigrum’, ‘grass’, 60

union all

select ‘Iwami wear’, ‘grass’, 60

union all

select ‘Nepenthes’, ‘grass’, 70

union all

select ‘Motherwort’, ‘grass’, 86

union all

select ‘Lentils’, ‘fruits’, 86

union all

select ‘Tsaoko’, ‘fruits’, 70

union all

select ‘Rosa laevigata’, ‘fruits’, 55

union all

select ‘Ligustrum lucidum’, ‘fruits’, 94

union all

select ‘Panda Hai’, ‘fruits’, 66

union all

select ‘Mulberry’, ‘fruits’, 78

 

select sname, sort, num,

row_number () over (order by num) AS rownum,

RANK () over (order by num) as ranknum

dense_rank () over (order by num) AS dersenum

ntile (3) over (order by num) as ntilenum

from gg

 

– Results

– ROW_NUMBER () is a small to large num one by one rank, not side by side, in continuous

– RANK () num Ascending rank one by one, side by side, the ranking is not continuous

– DENSE_RANK () one by one num Ascending ranked tied for fourth consecutive

– Ntile (takes) num small to big into groups one by one ranking tied for fourth consecutive

 

sname       sort        num        rownum         ranknum        dersenum        ntilenum
————————————————– ——————————–
Root of herbaceous peony          Tubers        55           1           1              1                1
Rush        Grasses          55           2           1              1                1
Jinyingzi        Fruits        55           3           1              1                1
Solanum nigrum          Grasses          60           4           4              2                1
Stone see through        Grasses          60           5           4              2                1
Bupleurum          Tubers        60           6           4              2                1
Panda Hai        Fruits        66           7           7              3                2
Days censer        Grasses          68           8           8              4                2
Tsaoko          Fruits        70           9           9              5                2
Nepenthes        Grasses          70           10          9              5                2
France Pinellia        Tubers        78           11          11             6                2
Mulberry          Fruits        78           12          11             6                3
Motherwort        Grasses          86           13          13             7                3
Hyacinth bean          Fruits        86           14          13             7                3
Ligustrum lucidum        Fruits        94           15          15             8                3
Chuanxiong          Tubers        99           16          16             9                3

(16 rows affected)

 

select sname, sort, num,

row_number () over (partition by sort order by num) AS rownum,

RANK () over (partition by sort order by num) as ranknum

dense_rank () over (partition by sort order by num) AS dersenum

ntile (3) over (partition by sort order by num) as ntilenum

from gg

 

– Results

At this point add the partition by sort by category classified ntile (takes 3) means that the force divided into three groups.

 

sname       sort            num        rownum         ranknum         dersenum        ntilenum
——————    ————————————————– ————–
Rush        Grasses           55           1             1                1                1
Solanum nigrum          Grasses           60           2             2                2                1
Stone see through        Grasses           60           3             2                2                2
Days censer        Grasses           68           4             4                3                2
Nepenthes        Grasses           70           5             5                4                3
Motherwort        Grasses           86           6             6                5                3
Root of herbaceous peony          Tubers         55           1             1                1                1
Bupleurum          Tubers         60           2             2                2                1
France Pinellia        Tubers         78           3             3                3                2
Chuanxiong          Tubers         99           4             4                4                3
Jinyingzi        Fruits         55           1             1                1                1
Panda Hai        Fruits         66           2             2                2                1
Tsaoko          Fruits         70           3             3                3                2
Mulberry          Fruits         78           4             4                4                2
Hyacinth bean          Fruits         86           5             5                5                3
Ligustrum lucidum        Fruits         94           6             6                6                3

(16 rows affected)

 

The following SQL 2000 to achieve, compared to 2005 to more trouble.

– ROW_NUMBER in SQL 2000 in the implementation of

– The use of temporary tables and Identity (function)

select sname, num, identity (int, 1, 1) as rownumber

INTO # tem

from gg

order by num

 

select sname, num, rownumber

from # tem

 

drop table # tem

go

 

– RANK achieve in sql 2000

select sname, num,

(Select count (1) + 1 from gg where num <g. num) as ranknum

from gg g

order by num

go

 

– The in sql DENSE_RANK of the 2000 in the realization of

select num, identity (int, 1, 1) as densenum

into # t

from gg

group by num

order by num

 

select r. sname, r. num, t. densenum

from gg r join # tt

on r. num = t. num

order by num

 

drop table # t

Posted by databasesql

The seashells sharp ORAY written examination and interview

Time: 2011.6 interview Classes: C + + software development

The beginning to the written test subject to do

The first page of the written test:

If you were given 500,000 yuan, you will use the money to do what?

2 with not more than 30 words to give one of the most reason to hire you.

You ideal working environment?

Your personal and long-term career goals what?

Brief description of your weaknesses and strengths.

Please briefly describe your upbringing and family situation

English translation and to answer the questions raised by the English.

The companies tend to do network test some basic knowledge and networks as well as GDI programming stuff

The subject of the written test remember much, just remember the following:

1 what is the interrupt, the interrupt occurs, the CPU what is being done?

2. Describe briefly the POST and GET methods in the HTTP protocol, the role of the difference

Briefly outline the use of the Mutex, Semaphore

4 List standard Socket correlation function and specified purpose

5 What is DNS and realization of the principle

Coding questions:

Demonstration with the most simple code to SQLSERVER use ADO to access a table in C + +, and returns the contents of the first row and first column integer field

Known to the handle of the window, the window to draw a red line of any length with GDI DC.

Posted by databasesql

Business system user rights management – design articles

Business system user rights management – design articles

?? Permissions in the B / S system is even more important than in the C / S, C / S system has a special client, so access to the user’s permissions detection by client or by the client + server detection, and B / S, the browser each computer are in place, If you do not build a complete permission detection, unauthorized users are likely to be able to easily through the browser access to all the features of the B / S system . B / S business systems need to have one or more rights system to achieve access detection, so that authorized users can legitimate use authorized, unauthorized unauthorized users will completely shut out. ” Here let us look at how the permissions system designed to meet most of the B / S system permissions control user features.

Statement of need

  • Different responsibilities of personnel for the operating system privileges should be different. Excellent business system, which is the most basic functions.
  • Group to assign permissions. If you ask the administrator for a large enterprise business systems, distribution system operating authority for its employees one by one, it is time-consuming and not convenient enough. Therefore, the system put forward the concept of group permission incorporated into the same group, and then assign permissions that group.
  • Rights management system should be scalable. It should be added to any rights management system. Like components can be continuously reused, rather than for each to develop a management system, it is necessary for the management component of re-development.
  • Meet the functional competence of the business system. Traditional business systems, there are two rights management, one of which is the functional competence of management, and another is the management of the resource permissions, functional competence can be reused between different systems, and resource permissions can not.

About Design

?? With NoahWeb action programming philosophy at the design stage, system designers need to consider the design of the structure of the program, but from the program flow and structure of the database to start with. In order to achieve the demand, the design of the database can be described as important, whether it is the concept of “group” operation, reuse or Entire rights management system, in that the design of the database.

Let’s analyze the structure of the database:

?? First of all, action table (hereinafter referred to as “permissions table), gorupmanager table (hereinafter referred to as” management group table “), as well as the master table (hereinafter referred to as” staff table “) of three entities, which in turn records “authorized” message, “Management Group” and “officer”. As shown below:

?? The relationship between these three tables is a many-to-many, a permission may belong to more than one management group, a management group may contain multiple permissions at the same time. By the same token, a person may belong to more than one management group, a management group may contain more than one person at the same time. As shown below:

?? Since there is a many-to-many relationship between the tables, the interaction between them, it is best to use the other two tables to complete. Two tables play a role mapping are “ActionGroup” table (hereinafter referred to as “permission mapping table”) and “mastergroup” table (hereinafter referred to as the “personnel mapping table), the former mapping the authority table and the management group table The interaction between. The latter mapping the interaction between the staff table set of tables. As shown below:

?? In addition, you need a table to control permissions columns while the system is running in the left menu, or “permissions column table, as shown:

?? Based on the above analysis, the database structure design, as shown below:

?? Order to be able to carry out the analysis, we will split the database structure diagram open, the three entities table has been very clear, and now we look at the role of two mapping table.

Permission mapping table as shown below:

?? First, we take a look at the the between fields associated with the the permissions mapping table management group tables and permissions table.

?? Red circle in the picture, look at gorupid field associated with this association in a real database performance as shown below:

?? As shown in the figure, the management group table “super administrator” groupid, then the permissions mapping table groupid permissions of a “super administrator” have permissions.

?? Use the groupid field related, is to be found in a management group can execute permissions. Detailed information about these permissions are the action field associated with the query to.

?? The action field associated with the performance in the database as shown below:

?? This association only query to the the permissions mapping table among those permissions. Taken together, we know that a management group can execute permissions which these rights in detail.

?? Perhaps you might ask, why not use actionid field associated with it? Since:

  • Id field in the permissions table may change after several database operations.
  • Permission mapping table only records a management group can execute permissions.
  • Once id change the permissions table, then the record will change the permissions mapping table.
  • A management group can execute permissions is bound to go wrong, which is highly undesirable.

?? Taking into account the above situation, so it should be using the action field associated, because:

  • In the permissions table, id may change the action field is in any case it is impossible to change the.
  • Permission mapping table records the action field will not change.
  • A management group can execute permissions would not be wrong.

The two mapping table as shown below:

?? We take a look at the field associated personnel mapping table management group table and personnel between the tables as shown below:

?? The red circle in the pictures, look at the the groupid field associated with, the performance of this association in the database as shown below:

?? Figure, “super administrator” group groupid 1, we look at the personnel mapping table, admin belonging to the super administrator group, administrator super administrator group also belong to the Administrators group.

?? The use of this association is to be found anyone in a management group. And above-detailed information is associated with the query to rely on the id field (personnel mapping table is masterid field).

?? id field (personnel mapping table is masterid field) associated in the database in the form of the following diagram:

?? One person may belong to more than one management group, as shown, administrator belongs to both the management group. So, in the staff mapping table records administrator will be two.

?? Way associated with this query to the management group For more information. Taken together, we can know anyone in a management group, as well as detailed information about this person.

?? Combination of the above mentioned permissions table and permissions mapping table, on the realization of the demand “group” operation, as shown below:

?? In fact, the management group table only record set of basic information such as name, group id. As for the detailed information of the personnel in a group, and the group is able to perform more information about permissions, are recorded in the the personnel tables and permissions table. The two mapping table is really a record of a group which people can do what permissions. By the convergence of two mapping table, the interaction between the three entities can be achieved, thus completing the requirements mentioned group operating.

?? Let us look at the interaction between the permissions column table with permissions table. Associated with the fields between the two tables as shown below:

?? The two tables the actioncolumnid field associated with the performance of this association in the database as shown below:

?? As shown, through this association, we can very clearly see the permissions in the permissions table which columns.

?? Now, the database structure has been very clear, and the ability to assign permissions and group operation has been achieved. Now let us analyze the problems mentioned in the demand for rights management system reusability.

?? Why use this database designed to build up the system can be reused?

  • The three entities statement recorded in the three decisive elements in the system. “Permission”, “Group” and “people.” These three elements can be added, affecting each other. Whether that type of business systems, these three decisive elements will not change, which means the structure will not change, and become just data.
  • Two mapping table records the relationship between the three elements. But these relationships is artificially created, it needs to be changed, only the records in the database to operate, no need to change the structure.
  • The permissions columns in the table records the display sub-system uses bar. Whether you want to add a column, modify the column or decrease the column, it is only operational records only.

?? In summary, this design database, the system is completely reusable and withstand “Change” test.

Summary:

?? The focus of this system is that the three entities statement firmly grasp the core components of the system, two perfect mapping table to map out the interaction between the three entities form. The difficulty lies in understanding the mapping table, which records the relationship and the realization of the “group” concept of operations. The overall design of the system is in line with the different MIS system “reuse” to meet different system function permission settings.

Appendix:

Rights management system design of the field data sheet

?? Let us look at the rights management system database table design, is divided into six tables, as shown below:

action table:

?? The action table records all the action, and action description.

Table of actioncolumn:

?? the record action the actioncolumn table columns, while the system is running, the menu bar on the left provides a few different functions, each piece is a columns, each adding a columns, records will be added in the table, as opposed corresponding left menu bar will also be added the machine a column.

Table of actiongroup:

?? actiongroup table records where the action group.

table groupmanager:

?? recorded in the groupmanager table management information about the groups, and per add a management group, and the record will be added.

mastergroup table:

?? the mastergroup table records management group where the administrator, an administrator may belong to multiple groups at the same time with the table on a one administrator records may be multiple.

master table:

?? The master table records the information of all administrators, each add an administrator, the table will add a record.

Posted by databasesql

The efficiency of the implementation of the SQL cursors, static cursors efficient implementation

Static (STATIC) cursor to create a temporary copy of the data used by the cursor. From this temporary table in tempdb all requests to the cursor are answered; Therefore, the data returned by fetches the cursor does not reflect the changes made to the base table, and the cursor is not allowed to modify

Dynamic (dynamic) cursor reflects all data changes made in the scrollable cursor for each row in the result set. Rows of data values, order, and membership will change on each fetch. Dynamic cursors are not supported ABSOLUTE fetch option.

STATIC keyword is specified, the default definition of the cursor is dynamic (dynamic)

Very concerned about the type of cursor should be in the definition of the cursor, and cursor type defined keywords, and plus TYPE_WARNING keywords in order to receive a warning in the case of the cursor type is not expected

Demo:

The following demonstration STATIC and DYNAMIC cursors, both in cursor loop of the difference

[Code = SQL]

– Define the demo data

IF OBJECT_ID (‘tempdb .. # tb’) IS NOT NULL

DROP TABLE # tb

CREATE TABLE # tb (

id int PRIMARY KEY,

col sysname)

Insert # tb (

ID, col)

SELECT 1, ‘AA’ UNION ALL

SELECT 2, ‘BB’ UNION ALL

SELECT 3, ‘CC’ UNION ALL

SELECT 4, ‘DD’

– Cursor test

DECLARE CUR_tb CURSOR LOCAL FORWARD_ONLY READ_ONLY TYPE_WARNING STATIC – DYNAMIC

FOR

SELECT

ID, col

FROM # tb

– Open the front of the cursor delete records

DELETE TOP (1)

FROM # tb

WHERE id = 4

SELECT ‘before cursor open’, * FROM # tb

– Open the cursor

OPEN CUR_tb

– Delete records after a cursor is open

DELETE TOP (1)

FROM # tb

WHERE id = 3

SELECT ‘after cursor open’, * FROM # tb

FETCH CUR_tb

WHILE @ @ FETCH_STATUS = 0

BEGIN

– Delete records in the cursor loop

DELETE TOP (1)

FROM # tb

WHERE id = 2

FETCH CUR_tb

END

CLOSE CUR_tb to

Deallocate CUR_tb is

[/ Code]

This article comes from databaseskill.com blog, reproduced, please indicate the source:

Posted by databasesql

Package

Package: procedure function package

PL / SQL stored procedures stored together with the relevant objects.

Create a package: header, including the body.

The establishment of Baotou

CREATE OR REPLACE PACKAGE emp_package IS

g_depno NUMBER (3): 30;

The PROCEDURE add_emp (eno number, name VARCHAR2);

Function get_sal (eno NUMBER) RETURN NUMBER;

The END EMP_PACKAGE;

The establishment of inclusions

CREATE OR REPLACE PACKAGE BODY emp_package

IS

FUNCTION validata_comm (p_comm in number)

Return boolean;

is

v_max_comm NUMBER;

BEGIN

SELECT ……

……

……

END validata_comm is

The END emp_package;

Posted by databasesql

The SuperMap data storage, sdb, sdd, dwg meaning of

SuperMap Objects uses a unique multi-source spatial data seamless integration of technology to provide a variety of spatial data storage format. Different data stored by different spatial data engine is responsible for maintaining. These engines include: the SDB, DGN, DWG, MDB and SDX. These engines were introduced as follows:

The SDB engine: SuperMap Objects own a hybrid spatial data organization based files and databases. A the SDB data works include two file extensions named SDB file storage space data, OLE compound document technology; extended file named SDD attribute database, Access MDB database format. Because the SDB file using the compound document technology, thus providing the ability of storing a plurality of data sets in a SDB engineering. The SDB engine supports only multi-user shared read access, but only supports single-user write operation. SDB for small systems and desktop applications;

The DGN engine: is a file-based spatial data engine, SuperMap Objects to access other software data format engine. The DGN engine provides direct access to MicroStation DGN data files. The DGN engine is a read-only engine, you can use the DGN data source, but does not provide editing features.

DWG engine: with DGN engine similar to DWG engine provides direct access to AutoCAD DWG data file. DWG engine is read-only engine.

The MDB engine: a pure database engine, Access MDB database as a data storage container. Based on the Microsoft Jet database engine implementation. The MDB engine to spatial data and non-spatial data storage in the same MDB database, truly seamless organization of spatial data and non-spatial data, at the same time easy to maintain data. The MDB support multi-user concurrent read and write operations. An MDB file can be stored in multiple data sets. SDB, MDB engine for small and medium-sized desktop applications, an MDB maximum space capacity of 1G. The MDB engines suitable for small and medium-sized applications.

SDX Engine: Based on the large databases pure relational database space engine. Seamless organization of spatial data and attribute data to support the large amount of data management, support multi-user shared read and write access support short Services and long Affairs, processing, and strict access management. SDX engine for the establishment of large-scale spatial data application. SDX engine for different database systems have broken down, and currently supports Microsoft SQL Server, Oracle, Oracle Spatial and Sybase.

SDX Plus engine: the the SuperMap company’s third-generation database engine relative to the SDX engine is concerned, all aspects of performance optimization. Support for Microsoft SQL Server, Oracle. Intro Please refer to the SuperMap SDX (+) the introduction of engines and technical white papers.

Most SDB application over several data formats, and provides hypergraph the SuperMap D-Builder to support spatial data format conversion, geographic reference system transformation, spatial data warehousing and other functions, the first of these three functions is simple described as follows:

Spatial data format conversion

Provide spatial data in a variety of other formats, such as CAD data, SHP data, E00 data, converted to SuperMap the SDB data source. The conversion is a one-to-one way: a raw data file corresponds to a the SDB data file and keep the original file directory structure, to facilitate the user to check the format conversion accuracy of the results. In order to ensure the efficiency of the conversion, format conversion, data organization ignored the original data, the default processing in accordance with the GIS tiered storage, while ignoring the projection information of the original data file.

Transform geographical frame of reference

Ignored due to format conversion projection information of the original data format conversion is complete, you need to set the SDB data source and convert projection information. Only need to set the projection transformation, the need to convert the SDB data source and target projection information, the system can work automatically.

Spatial data warehousing

Spatial data storage in the the SDB data source on the basis of. The user can according to their needs to build their own model of building a database, database rules, data restructuring. Through custom data reorganization rules, the user can set up to meet the needs of the practical application of spatial databases.

Posted by databasesql

Common sense (7) – the DW DBError event

string error_text
CHOOSE CASE sqlDBCode
the case 1 error_text = ‘violation of a unique index!’
case 1400 error_text = ‘field can not be empty!’
case 1407 error_text = ‘field can not be empty!’
case 1401 error_text = ‘field is too long!’
case 1438 error_text = ‘value is greater than the maximum precision of the column allows!’
the case 2291 error_text = ‘appear illegal field!’
Case 1031 error_text = ‘rights!’
Case 911 error_text = ‘registered name is invalid!’ / / permissions dedicated
case 922 error_text = ‘Invalid special characters!’ / / permissions dedicated
CASE 1017 error_text = ‘the unlawful user name or password, Login denied!’
CASE 12154 error_text = ‘can not be decomposed name!’
CASE 01005 error_text = ‘is not given the password or wrong password Login denied!’
CASE 01935 error_text = ‘items enter the name of the registered name for the system keyword is prohibited as a registered name!’
The CASE 540 error_text = ‘data table or view does not exist!’
CASE 942 error_text = ‘data table or view does not exist!’
To CASE 903 error_text = ‘illegal column name!’
CASE 1403 error_text = ‘did not find a qualified data!’
CASE -3 error_text = ‘you read into the data and save procedure, the data in the server by other users or window changes, please re-read the data and try again!’
CASE 6 error_text = ‘network with the database connection to the server has been interrupted, close the application and then re-open.’
CASE 50 error_text = ‘network with the database connection to the server has been interrupted, close the application and then re-open.’
Case 1920 error_text = ‘user name with another user or role name conflict’
the case 988 error_text = ‘the lack of a password or illegal!’
the Case 1918 error_text = ‘The Oracle user does not exist!’
case 1940 error_text = ‘operator can not give up a current connection, delete the current operator is being used!’
CASE ELSE
if isnull (sqlca.sqlErrText) or sqlca.sqlErrText =” then
error_text = ‘data operation failed!’
else
error_text = sqlca.sqlErrText
end if
END CHOOSE
return MessageBox (‘error’, error_text RetryCancel!)

/ / DBError parameters
Buffer buffer where the error occurred
Row the number of rows of the first error
The database error SqlDBCode code
The database error SqlErrText information

Posted by databasesql