Madyson  Reilly

Madyson Reilly

1597006740

How to Use SQL Server HierarchyID Through Easy Examples

Do you still hold on to the parent/child design, or would like to try something new, like SQL Server hierarchyID? Well, it is really new because hierarchyID has been a part of SQL Server since 2008. Of course, the novelty itself is not a persuasive argument. But note that Microsoft added this feature to represent one-to-many relationships with multiple levels in a better way.

You may wonder what difference it makes and which benefits you get from using hierarchyID instead of the usual parent/child relationships. If you never explored this option, it might be surprising for you.

The truth is, I didn’t explore this option since it was released. However, when I finally did it, I found it a great innovation. It is a better-looking code, but it has much more in it. In this article, we are going to find out about all those excellent opportunities.

However, before we dive into the peculiarities of using SQL Server hierarchyID, let’s clarify its meaning and scope.

What is SQL Server HierarchyID?

SQL Server hierarchyID is a built-in data type designed to represent trees, which are the most common type of hierarchical data. Each item in a tree is called a node. In a table format, it is a row with a column of hierarchyID data type.

CodingSight - SQL Server HierarchyID

Usually, we demonstrate hierarchies using a table design. An ID column represents a node, and another column stands for the parent. With the SQL Server HierarchyID, we only need one column with a data type of hierarchyID.

When you query a table with a hierarchyID column, you see hexadecimal values. It is one of the visual images of a node. Another way is a string:

‘/’ stands for the root node;

‘/1/’, ‘/2/’, ‘/3/’ or ‘/n/’ stand for the children – direct descendants 1 to n;

‘/1/1/’ or ‘/1/2/’ are the “children of children – “grandchildren.” The string like ‘/1/2/’ means that the first child from the root has two children, which are, in their turn, two grandchildren of the root.

Here’s a sample of what it looks like:

CodingSight - A sample output showing nodes (1st column) and node strings (2nd column)Figure 1A sample output showing nodes (1st column) and node strings (2nd column)

Unlike other data types, hierarchyID columns can take advantage of built-in methods. For example, if you have a hierarchyID column named RankNode, you can have the following syntax:

RankNode..

SQL Server HierarchyID Methods

One of the available methods is IsDescendantOf. It returns 1 if the current node is a descendant of a hierarchyID value.

You can write code with this method similar to the one below:

SELECT
 r.RankNode
,r.Rank
FROM dbo.Ranks r
WHERE r.RankNode.IsDescendantOf(0x58) = 1

Other methods used with hierarchyID are the following:

  • GetRoot – the static method that returns the root of the tree.
  • GetDescendant  – returns a child node of a parent.
  • GetAncestor – returns a hierarchyID representing the nth ancestor of a given node.
  • GetLevel – returns an integer that represents the depth of the node.
  • ToString – returns the string with the logical representation of a node. ToString is called implicitly when the conversion from hierarchyID to the string type occurs.
  • GetReparentedValue – moves a node from the old parent to the new parent.
  • Parse – acts as the opposite of ToString. It converts the string view of a hierarchyID value to hexadecimal.

SQL Server HierarchyID Indexing Strategies

To ensure that queries for tables using hierarchyID run as fast as possible, you need to index the column. There are two indexing strategies:

DEPTH-FIRST

In a depth-first index, the subtree rows are closer to each other. It suits queries like finding a department, its subunits, and employees. Another example is a manager and its employees stored closer together.

In a table, you can implement a depth-first index by creating a clustered index for the nodes. Further, we perform one of our examples, just like that.

CodingSight - Depth-first indexing strategy. The Chief Engineer subtree is highlighted in the org. chart and the result set. The list is sorted based on each subtree.Figure 2Depth-first indexing strategy. The Chief Engineer subtree is highlighted in the org. chart and the result setThe list is sorted based on each subtree.

BREADTH-FIRST

In a breadth-first index, the same level’s rows are closer together. It suits queries like finding all the manager’s directly reporting employees. If most of the queries are similar to this, create a clustered index based on (1) level and (2) node.

CodingSight - Breadth-first indexing strategy. A portion of the second rank level is highlighted in the org. chart and the result set. The list is sorted based on the scale.Figure 3: _Breadth-first indexing strategy. A portion of the second rank level is highlighted in the org. chart and the result set. The list is sorted based on the scale. _

It depends on your requirements if you need a depth-first index, a breadth-first, or both. You need to balance between the importance of the queries type and the DML statements you execute on the table.

SQL Server HierarchyID Limitations

Unfortunately, using hierarchyID can’t resolve all issues:

  • SQL Server can’t guess what the child of a parent is. You have to define the tree in the table.
  • If you don’t use a unique constraint, the generated hierarchyID value won’t be unique. Handling this problem is the developer’s responsibility.
  • Relationships of a parent and child nodes are not enforced like a foreign key relationship. Hence, before deleting a node, query for any descendants existing.

Visualizing Hierarchies

Before we proceed, consider one more question. Looking at the result set with node strings, do you find the hierarchy visualizing hard for your eyes?

For me, it’s a big yes because I am not getting younger.

For this reason, we are going to use Power BI and Hierarchy Chart from Akvelon along with our database tables. They will help to display the hierarchy in an organizational chart. I hope it will make the job easier.

Now, let’s get down to business.

Uses of SQL Server HierarchyID

You can use HierarchyID with the following business scenarios:

  • Organizational structure
  • Folders, subfolders, and files
  • Tasks and subtasks in a project
  • Pages and subpages of a website
  • Geographical data with countries, regions, and cities

Even if your business scenario is similar to the above, and you rarely query across the hierarchy sections, you don’t need hierarchyID.

For example, your organization processes payrolls for employees. Do you need to access the subtree to process someone’s payroll? Not at all. However, if you process commissions of people in a multi-level marketing system, it can be different.

In this post, we use the portion of the organizational structure and the chain of command on a cruise ship. The structure was adapted from the organizational chart from here. Take a look at it in Figure 4 below:

CodingSight - Figure 4: Organizational structure of a typical cruise ship rendered using Power BI, Hierarchy Chart by Akvelon, and SQL Server. Names are made-up and do not relate to actual people and cruise ships.Figure 4Organizational structure of a typical cruise ship rendered using Power BI, Hierarchy Chart by Akvelon, and SQL Server. Names are made-up and do not relate to actual people and cruise ships.

Now you can visualize the hierarchy in question. We use the below tables throughout this post:

  • Vessels – is the table standing for the cruise ships’ list.
  • Ranks – is the table of crew ranks. There we establish hierarchies using the hierarchyID.
  • Crew – is the list of the crew of each vessel and their ranks.

The table structure of each case is as follows:

CREATE TABLE [dbo].[Vessel](
[VesselId] [int] IDENTITY(1,1) NOT NULL,
[VesselName] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Vessel] PRIMARY KEY CLUSTERED
(
[VesselId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Ranks](
[RankId] [int] IDENTITY(1,1) NOT NULL,
[Rank] [varchar](50) NOT NULL,
[RankNode] [hierarchyid] NOT NULL,
[RankLevel] [smallint] NOT NULL,
[ParentRankId] [int]   -- this is redundant but we will use this to compare        
                       -- with parent/child
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_RankId] ON [dbo].[Ranks]
(
[RankId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_RankNode] ON [dbo].[Ranks]
(
[RankNode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Crew](
[CrewId] [int] IDENTITY(1,1) NOT NULL,
[CrewName] [varchar](50) NOT NULL,
[DateHired] [date] NOT NULL,
[RankId] [int] NOT NULL,
[VesselId] [int] NOT NULL,
 CONSTRAINT [PK_Crew] PRIMARY KEY CLUSTERED
(
[CrewId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Ranks] FOREIGN KEY([RankId])
REFERENCES [dbo].[Ranks] ([RankId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Ranks]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Vessel] FOREIGN KEY([VesselId])
REFERENCES [dbo].[Vessel] ([VesselId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Vessel]
GO

#programming #sql server #how to #sql server #sql server hierarchyid #sql

What is GEEK

Buddha Community

How to Use SQL Server HierarchyID Through Easy Examples
Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ray  Patel

Ray Patel

1625843760

Python Packages in SQL Server – Get Started with SQL Server Machine Learning Services

Introduction

When installing Machine Learning Services in SQL Server by default few Python Packages are installed. In this article, we will have a look on how to get those installed python package information.

Python Packages

When we choose Python as Machine Learning Service during installation, the following packages are installed in SQL Server,

  • revoscalepy – This Microsoft Python package is used for remote compute contexts, streaming, parallel execution of rx functions for data import and transformation, modeling, visualization, and analysis.
  • microsoftml – This is another Microsoft Python package which adds machine learning algorithms in Python.
  • Anaconda 4.2 – Anaconda is an opensource Python package

#machine learning #sql server #executing python in sql server #machine learning using python #machine learning with sql server #ml in sql server using python #python in sql server ml #python packages #python packages for machine learning services #sql server machine learning services

Madyson  Reilly

Madyson Reilly

1597006740

How to Use SQL Server HierarchyID Through Easy Examples

Do you still hold on to the parent/child design, or would like to try something new, like SQL Server hierarchyID? Well, it is really new because hierarchyID has been a part of SQL Server since 2008. Of course, the novelty itself is not a persuasive argument. But note that Microsoft added this feature to represent one-to-many relationships with multiple levels in a better way.

You may wonder what difference it makes and which benefits you get from using hierarchyID instead of the usual parent/child relationships. If you never explored this option, it might be surprising for you.

The truth is, I didn’t explore this option since it was released. However, when I finally did it, I found it a great innovation. It is a better-looking code, but it has much more in it. In this article, we are going to find out about all those excellent opportunities.

However, before we dive into the peculiarities of using SQL Server hierarchyID, let’s clarify its meaning and scope.

What is SQL Server HierarchyID?

SQL Server hierarchyID is a built-in data type designed to represent trees, which are the most common type of hierarchical data. Each item in a tree is called a node. In a table format, it is a row with a column of hierarchyID data type.

CodingSight - SQL Server HierarchyID

Usually, we demonstrate hierarchies using a table design. An ID column represents a node, and another column stands for the parent. With the SQL Server HierarchyID, we only need one column with a data type of hierarchyID.

When you query a table with a hierarchyID column, you see hexadecimal values. It is one of the visual images of a node. Another way is a string:

‘/’ stands for the root node;

‘/1/’, ‘/2/’, ‘/3/’ or ‘/n/’ stand for the children – direct descendants 1 to n;

‘/1/1/’ or ‘/1/2/’ are the “children of children – “grandchildren.” The string like ‘/1/2/’ means that the first child from the root has two children, which are, in their turn, two grandchildren of the root.

Here’s a sample of what it looks like:

CodingSight - A sample output showing nodes (1st column) and node strings (2nd column)Figure 1A sample output showing nodes (1st column) and node strings (2nd column)

Unlike other data types, hierarchyID columns can take advantage of built-in methods. For example, if you have a hierarchyID column named RankNode, you can have the following syntax:

RankNode..

SQL Server HierarchyID Methods

One of the available methods is IsDescendantOf. It returns 1 if the current node is a descendant of a hierarchyID value.

You can write code with this method similar to the one below:

SELECT
 r.RankNode
,r.Rank
FROM dbo.Ranks r
WHERE r.RankNode.IsDescendantOf(0x58) = 1

Other methods used with hierarchyID are the following:

  • GetRoot – the static method that returns the root of the tree.
  • GetDescendant  – returns a child node of a parent.
  • GetAncestor – returns a hierarchyID representing the nth ancestor of a given node.
  • GetLevel – returns an integer that represents the depth of the node.
  • ToString – returns the string with the logical representation of a node. ToString is called implicitly when the conversion from hierarchyID to the string type occurs.
  • GetReparentedValue – moves a node from the old parent to the new parent.
  • Parse – acts as the opposite of ToString. It converts the string view of a hierarchyID value to hexadecimal.

SQL Server HierarchyID Indexing Strategies

To ensure that queries for tables using hierarchyID run as fast as possible, you need to index the column. There are two indexing strategies:

DEPTH-FIRST

In a depth-first index, the subtree rows are closer to each other. It suits queries like finding a department, its subunits, and employees. Another example is a manager and its employees stored closer together.

In a table, you can implement a depth-first index by creating a clustered index for the nodes. Further, we perform one of our examples, just like that.

CodingSight - Depth-first indexing strategy. The Chief Engineer subtree is highlighted in the org. chart and the result set. The list is sorted based on each subtree.Figure 2Depth-first indexing strategy. The Chief Engineer subtree is highlighted in the org. chart and the result setThe list is sorted based on each subtree.

BREADTH-FIRST

In a breadth-first index, the same level’s rows are closer together. It suits queries like finding all the manager’s directly reporting employees. If most of the queries are similar to this, create a clustered index based on (1) level and (2) node.

CodingSight - Breadth-first indexing strategy. A portion of the second rank level is highlighted in the org. chart and the result set. The list is sorted based on the scale.Figure 3: _Breadth-first indexing strategy. A portion of the second rank level is highlighted in the org. chart and the result set. The list is sorted based on the scale. _

It depends on your requirements if you need a depth-first index, a breadth-first, or both. You need to balance between the importance of the queries type and the DML statements you execute on the table.

SQL Server HierarchyID Limitations

Unfortunately, using hierarchyID can’t resolve all issues:

  • SQL Server can’t guess what the child of a parent is. You have to define the tree in the table.
  • If you don’t use a unique constraint, the generated hierarchyID value won’t be unique. Handling this problem is the developer’s responsibility.
  • Relationships of a parent and child nodes are not enforced like a foreign key relationship. Hence, before deleting a node, query for any descendants existing.

Visualizing Hierarchies

Before we proceed, consider one more question. Looking at the result set with node strings, do you find the hierarchy visualizing hard for your eyes?

For me, it’s a big yes because I am not getting younger.

For this reason, we are going to use Power BI and Hierarchy Chart from Akvelon along with our database tables. They will help to display the hierarchy in an organizational chart. I hope it will make the job easier.

Now, let’s get down to business.

Uses of SQL Server HierarchyID

You can use HierarchyID with the following business scenarios:

  • Organizational structure
  • Folders, subfolders, and files
  • Tasks and subtasks in a project
  • Pages and subpages of a website
  • Geographical data with countries, regions, and cities

Even if your business scenario is similar to the above, and you rarely query across the hierarchy sections, you don’t need hierarchyID.

For example, your organization processes payrolls for employees. Do you need to access the subtree to process someone’s payroll? Not at all. However, if you process commissions of people in a multi-level marketing system, it can be different.

In this post, we use the portion of the organizational structure and the chain of command on a cruise ship. The structure was adapted from the organizational chart from here. Take a look at it in Figure 4 below:

CodingSight - Figure 4: Organizational structure of a typical cruise ship rendered using Power BI, Hierarchy Chart by Akvelon, and SQL Server. Names are made-up and do not relate to actual people and cruise ships.Figure 4Organizational structure of a typical cruise ship rendered using Power BI, Hierarchy Chart by Akvelon, and SQL Server. Names are made-up and do not relate to actual people and cruise ships.

Now you can visualize the hierarchy in question. We use the below tables throughout this post:

  • Vessels – is the table standing for the cruise ships’ list.
  • Ranks – is the table of crew ranks. There we establish hierarchies using the hierarchyID.
  • Crew – is the list of the crew of each vessel and their ranks.

The table structure of each case is as follows:

CREATE TABLE [dbo].[Vessel](
[VesselId] [int] IDENTITY(1,1) NOT NULL,
[VesselName] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Vessel] PRIMARY KEY CLUSTERED
(
[VesselId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Ranks](
[RankId] [int] IDENTITY(1,1) NOT NULL,
[Rank] [varchar](50) NOT NULL,
[RankNode] [hierarchyid] NOT NULL,
[RankLevel] [smallint] NOT NULL,
[ParentRankId] [int]   -- this is redundant but we will use this to compare        
                       -- with parent/child
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_RankId] ON [dbo].[Ranks]
(
[RankId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_RankNode] ON [dbo].[Ranks]
(
[RankNode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Crew](
[CrewId] [int] IDENTITY(1,1) NOT NULL,
[CrewName] [varchar](50) NOT NULL,
[DateHired] [date] NOT NULL,
[RankId] [int] NOT NULL,
[VesselId] [int] NOT NULL,
 CONSTRAINT [PK_Crew] PRIMARY KEY CLUSTERED
(
[CrewId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Ranks] FOREIGN KEY([RankId])
REFERENCES [dbo].[Ranks] ([RankId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Ranks]
GO

ALTER TABLE [dbo].[Crew]  WITH CHECK ADD  CONSTRAINT [FK_Crew_Vessel] FOREIGN KEY([VesselId])
REFERENCES [dbo].[Vessel] ([VesselId])
GO

ALTER TABLE [dbo].[Crew] CHECK CONSTRAINT [FK_Crew_Vessel]
GO

#programming #sql server #how to #sql server #sql server hierarchyid #sql

Chloe  Butler

Chloe Butler

1667425440

Pdf2gerb: Perl Script Converts PDF Files to Gerber format

pdf2gerb

Perl script converts PDF files to Gerber format

Pdf2Gerb generates Gerber 274X photoplotting and Excellon drill files from PDFs of a PCB. Up to three PDFs are used: the top copper layer, the bottom copper layer (for 2-sided PCBs), and an optional silk screen layer. The PDFs can be created directly from any PDF drawing software, or a PDF print driver can be used to capture the Print output if the drawing software does not directly support output to PDF.

The general workflow is as follows:

  1. Design the PCB using your favorite CAD or drawing software.
  2. Print the top and bottom copper and top silk screen layers to a PDF file.
  3. Run Pdf2Gerb on the PDFs to create Gerber and Excellon files.
  4. Use a Gerber viewer to double-check the output against the original PCB design.
  5. Make adjustments as needed.
  6. Submit the files to a PCB manufacturer.

Please note that Pdf2Gerb does NOT perform DRC (Design Rule Checks), as these will vary according to individual PCB manufacturer conventions and capabilities. Also note that Pdf2Gerb is not perfect, so the output files must always be checked before submitting them. As of version 1.6, Pdf2Gerb supports most PCB elements, such as round and square pads, round holes, traces, SMD pads, ground planes, no-fill areas, and panelization. However, because it interprets the graphical output of a Print function, there are limitations in what it can recognize (or there may be bugs).

See docs/Pdf2Gerb.pdf for install/setup, config, usage, and other info.


pdf2gerb_cfg.pm

#Pdf2Gerb config settings:
#Put this file in same folder/directory as pdf2gerb.pl itself (global settings),
#or copy to another folder/directory with PDFs if you want PCB-specific settings.
#There is only one user of this file, so we don't need a custom package or namespace.
#NOTE: all constants defined in here will be added to main namespace.
#package pdf2gerb_cfg;

use strict; #trap undef vars (easier debug)
use warnings; #other useful info (easier debug)


##############################################################################################
#configurable settings:
#change values here instead of in main pfg2gerb.pl file

use constant WANT_COLORS => ($^O !~ m/Win/); #ANSI colors no worky on Windows? this must be set < first DebugPrint() call

#just a little warning; set realistic expectations:
#DebugPrint("${\(CYAN)}Pdf2Gerb.pl ${\(VERSION)}, $^O O/S\n${\(YELLOW)}${\(BOLD)}${\(ITALIC)}This is EXPERIMENTAL software.  \nGerber files MAY CONTAIN ERRORS.  Please CHECK them before fabrication!${\(RESET)}", 0); #if WANT_DEBUG

use constant METRIC => FALSE; #set to TRUE for metric units (only affect final numbers in output files, not internal arithmetic)
use constant APERTURE_LIMIT => 0; #34; #max #apertures to use; generate warnings if too many apertures are used (0 to not check)
use constant DRILL_FMT => '2.4'; #'2.3'; #'2.4' is the default for PCB fab; change to '2.3' for CNC

use constant WANT_DEBUG => 0; #10; #level of debug wanted; higher == more, lower == less, 0 == none
use constant GERBER_DEBUG => 0; #level of debug to include in Gerber file; DON'T USE FOR FABRICATION
use constant WANT_STREAMS => FALSE; #TRUE; #save decompressed streams to files (for debug)
use constant WANT_ALLINPUT => FALSE; #TRUE; #save entire input stream (for debug ONLY)

#DebugPrint(sprintf("${\(CYAN)}DEBUG: stdout %d, gerber %d, want streams? %d, all input? %d, O/S: $^O, Perl: $]${\(RESET)}\n", WANT_DEBUG, GERBER_DEBUG, WANT_STREAMS, WANT_ALLINPUT), 1);
#DebugPrint(sprintf("max int = %d, min int = %d\n", MAXINT, MININT), 1); 

#define standard trace and pad sizes to reduce scaling or PDF rendering errors:
#This avoids weird aperture settings and replaces them with more standardized values.
#(I'm not sure how photoplotters handle strange sizes).
#Fewer choices here gives more accurate mapping in the final Gerber files.
#units are in inches
use constant TOOL_SIZES => #add more as desired
(
#round or square pads (> 0) and drills (< 0):
    .010, -.001,  #tiny pads for SMD; dummy drill size (too small for practical use, but needed so StandardTool will use this entry)
    .031, -.014,  #used for vias
    .041, -.020,  #smallest non-filled plated hole
    .051, -.025,
    .056, -.029,  #useful for IC pins
    .070, -.033,
    .075, -.040,  #heavier leads
#    .090, -.043,  #NOTE: 600 dpi is not high enough resolution to reliably distinguish between .043" and .046", so choose 1 of the 2 here
    .100, -.046,
    .115, -.052,
    .130, -.061,
    .140, -.067,
    .150, -.079,
    .175, -.088,
    .190, -.093,
    .200, -.100,
    .220, -.110,
    .160, -.125,  #useful for mounting holes
#some additional pad sizes without holes (repeat a previous hole size if you just want the pad size):
    .090, -.040,  #want a .090 pad option, but use dummy hole size
    .065, -.040, #.065 x .065 rect pad
    .035, -.040, #.035 x .065 rect pad
#traces:
    .001,  #too thin for real traces; use only for board outlines
    .006,  #minimum real trace width; mainly used for text
    .008,  #mainly used for mid-sized text, not traces
    .010,  #minimum recommended trace width for low-current signals
    .012,
    .015,  #moderate low-voltage current
    .020,  #heavier trace for power, ground (even if a lighter one is adequate)
    .025,
    .030,  #heavy-current traces; be careful with these ones!
    .040,
    .050,
    .060,
    .080,
    .100,
    .120,
);
#Areas larger than the values below will be filled with parallel lines:
#This cuts down on the number of aperture sizes used.
#Set to 0 to always use an aperture or drill, regardless of size.
use constant { MAX_APERTURE => max((TOOL_SIZES)) + .004, MAX_DRILL => -min((TOOL_SIZES)) + .004 }; #max aperture and drill sizes (plus a little tolerance)
#DebugPrint(sprintf("using %d standard tool sizes: %s, max aper %.3f, max drill %.3f\n", scalar((TOOL_SIZES)), join(", ", (TOOL_SIZES)), MAX_APERTURE, MAX_DRILL), 1);

#NOTE: Compare the PDF to the original CAD file to check the accuracy of the PDF rendering and parsing!
#for example, the CAD software I used generated the following circles for holes:
#CAD hole size:   parsed PDF diameter:      error:
#  .014                .016                +.002
#  .020                .02267              +.00267
#  .025                .026                +.001
#  .029                .03167              +.00267
#  .033                .036                +.003
#  .040                .04267              +.00267
#This was usually ~ .002" - .003" too big compared to the hole as displayed in the CAD software.
#To compensate for PDF rendering errors (either during CAD Print function or PDF parsing logic), adjust the values below as needed.
#units are pixels; for example, a value of 2.4 at 600 dpi = .0004 inch, 2 at 600 dpi = .0033"
use constant
{
    HOLE_ADJUST => -0.004 * 600, #-2.6, #holes seemed to be slightly oversized (by .002" - .004"), so shrink them a little
    RNDPAD_ADJUST => -0.003 * 600, #-2, #-2.4, #round pads seemed to be slightly oversized, so shrink them a little
    SQRPAD_ADJUST => +0.001 * 600, #+.5, #square pads are sometimes too small by .00067, so bump them up a little
    RECTPAD_ADJUST => 0, #(pixels) rectangular pads seem to be okay? (not tested much)
    TRACE_ADJUST => 0, #(pixels) traces seemed to be okay?
    REDUCE_TOLERANCE => .001, #(inches) allow this much variation when reducing circles and rects
};

#Also, my CAD's Print function or the PDF print driver I used was a little off for circles, so define some additional adjustment values here:
#Values are added to X/Y coordinates; units are pixels; for example, a value of 1 at 600 dpi would be ~= .002 inch
use constant
{
    CIRCLE_ADJUST_MINX => 0,
    CIRCLE_ADJUST_MINY => -0.001 * 600, #-1, #circles were a little too high, so nudge them a little lower
    CIRCLE_ADJUST_MAXX => +0.001 * 600, #+1, #circles were a little too far to the left, so nudge them a little to the right
    CIRCLE_ADJUST_MAXY => 0,
    SUBST_CIRCLE_CLIPRECT => FALSE, #generate circle and substitute for clip rects (to compensate for the way some CAD software draws circles)
    WANT_CLIPRECT => TRUE, #FALSE, #AI doesn't need clip rect at all? should be on normally?
    RECT_COMPLETION => FALSE, #TRUE, #fill in 4th side of rect when 3 sides found
};

#allow .012 clearance around pads for solder mask:
#This value effectively adjusts pad sizes in the TOOL_SIZES list above (only for solder mask layers).
use constant SOLDER_MARGIN => +.012; #units are inches

#line join/cap styles:
use constant
{
    CAP_NONE => 0, #butt (none); line is exact length
    CAP_ROUND => 1, #round cap/join; line overhangs by a semi-circle at either end
    CAP_SQUARE => 2, #square cap/join; line overhangs by a half square on either end
    CAP_OVERRIDE => FALSE, #cap style overrides drawing logic
};
    
#number of elements in each shape type:
use constant
{
    RECT_SHAPELEN => 6, #x0, y0, x1, y1, count, "rect" (start, end corners)
    LINE_SHAPELEN => 6, #x0, y0, x1, y1, count, "line" (line seg)
    CURVE_SHAPELEN => 10, #xstart, ystart, x0, y0, x1, y1, xend, yend, count, "curve" (bezier 2 points)
    CIRCLE_SHAPELEN => 5, #x, y, 5, count, "circle" (center + radius)
};
#const my %SHAPELEN =
#Readonly my %SHAPELEN =>
our %SHAPELEN =
(
    rect => RECT_SHAPELEN,
    line => LINE_SHAPELEN,
    curve => CURVE_SHAPELEN,
    circle => CIRCLE_SHAPELEN,
);

#panelization:
#This will repeat the entire body the number of times indicated along the X or Y axes (files grow accordingly).
#Display elements that overhang PCB boundary can be squashed or left as-is (typically text or other silk screen markings).
#Set "overhangs" TRUE to allow overhangs, FALSE to truncate them.
#xpad and ypad allow margins to be added around outer edge of panelized PCB.
use constant PANELIZE => {'x' => 1, 'y' => 1, 'xpad' => 0, 'ypad' => 0, 'overhangs' => TRUE}; #number of times to repeat in X and Y directions

# Set this to 1 if you need TurboCAD support.
#$turboCAD = FALSE; #is this still needed as an option?

#CIRCAD pad generation uses an appropriate aperture, then moves it (stroke) "a little" - we use this to find pads and distinguish them from PCB holes. 
use constant PAD_STROKE => 0.3; #0.0005 * 600; #units are pixels
#convert very short traces to pads or holes:
use constant TRACE_MINLEN => .001; #units are inches
#use constant ALWAYS_XY => TRUE; #FALSE; #force XY even if X or Y doesn't change; NOTE: needs to be TRUE for all pads to show in FlatCAM and ViewPlot
use constant REMOVE_POLARITY => FALSE; #TRUE; #set to remove subtractive (negative) polarity; NOTE: must be FALSE for ground planes

#PDF uses "points", each point = 1/72 inch
#combined with a PDF scale factor of .12, this gives 600 dpi resolution (1/72 * .12 = 600 dpi)
use constant INCHES_PER_POINT => 1/72; #0.0138888889; #multiply point-size by this to get inches

# The precision used when computing a bezier curve. Higher numbers are more precise but slower (and generate larger files).
#$bezierPrecision = 100;
use constant BEZIER_PRECISION => 36; #100; #use const; reduced for faster rendering (mainly used for silk screen and thermal pads)

# Ground planes and silk screen or larger copper rectangles or circles are filled line-by-line using this resolution.
use constant FILL_WIDTH => .01; #fill at most 0.01 inch at a time

# The max number of characters to read into memory
use constant MAX_BYTES => 10 * M; #bumped up to 10 MB, use const

use constant DUP_DRILL1 => TRUE; #FALSE; #kludge: ViewPlot doesn't load drill files that are too small so duplicate first tool

my $runtime = time(); #Time::HiRes::gettimeofday(); #measure my execution time

print STDERR "Loaded config settings from '${\(__FILE__)}'.\n";
1; #last value must be truthful to indicate successful load


#############################################################################################
#junk/experiment:

#use Package::Constants;
#use Exporter qw(import); #https://perldoc.perl.org/Exporter.html

#my $caller = "pdf2gerb::";

#sub cfg
#{
#    my $proto = shift;
#    my $class = ref($proto) || $proto;
#    my $settings =
#    {
#        $WANT_DEBUG => 990, #10; #level of debug wanted; higher == more, lower == less, 0 == none
#    };
#    bless($settings, $class);
#    return $settings;
#}

#use constant HELLO => "hi there2"; #"main::HELLO" => "hi there";
#use constant GOODBYE => 14; #"main::GOODBYE" => 12;

#print STDERR "read cfg file\n";

#our @EXPORT_OK = Package::Constants->list(__PACKAGE__); #https://www.perlmonks.org/?node_id=1072691; NOTE: "_OK" skips short/common names

#print STDERR scalar(@EXPORT_OK) . " consts exported:\n";
#foreach(@EXPORT_OK) { print STDERR "$_\n"; }
#my $val = main::thing("xyz");
#print STDERR "caller gave me $val\n";
#foreach my $arg (@ARGV) { print STDERR "arg $arg\n"; }

Download Details:

Author: swannman
Source Code: https://github.com/swannman/pdf2gerb

License: GPL-3.0 license

#perl 

Brain  Crist

Brain Crist

1600347600

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

Introduction

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below,

A glimpse of previous articles
Part 1

In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.

Part 2
  • We learned to create a database and maintain it using SQL statements.
  • Best practice methods were also mentioned.

#sql server #benefits of schemas #create schema in sql #database schemas #how to create schema in sql server #schemas #schemas in sql server #sql server schemas #what is schema in sql server