Grimpi IT Blog

enero 28, 2011

Que reglas usar en el Code Analysis de Visual Studio?

Filed under: .NET, Visual Studio — grimpi @ 12:46 pm

Cuando uno descubre FxCop, la excelente herramienta de analizador de código que viene integrada en el Visual Studio 2008 y 2010, enseguida tiende a creer que todo nuestro código para que sea optimo, no debe tener absolutamente ningún warning de FxCop.
Pero al poco tiempo nos damos cuenta que tal cosa es imposible y por varias razones. La primera es que la sugerencia no es valida, que el análisis del CodeAnalysis falló. Otras veces, que es realmente intranscendente y costoso hacer el cambio (especialmente las reglas agrupadas en la categoría “naming”).
Googleando un poco descubrí este excelente post de la gente de Microsoft, con un listado de todas las reglas que ellos usan para sus propios desarrollos y testeando, me pareció una muy buena selección de reglas.

Design
CA1008 EnumsShouldHaveZeroValue
CA1009 DeclareEventHandlersCorrectly
CA1011 ConsiderPassingBaseTypesAsParameters
CA1012 AbstractTypesShouldNotHaveConstructors
CA1014 MarkAssembliesWithClsCompliant
CA1017 MarkAssembliesWithComVisible
CA1018 MarkAttributesWithAttributeUsage
CA1019 DefineAccessorsForAttributeArguments
CA1023 IndexersShouldNotBeMultidimensional
CA1025 ReplaceRepetitiveArgumentsWithParamsArray
CA1026 DefaultParametersShouldNotBeUsed
CA1027 MarkEnumsWithFlags
CA1028 EnumStorageShouldBeInt32
CA1030 UseEventsWhereAppropriate
CA1032 ImplementStandardExceptionConstructors
CA1034 NestedTypesShouldNotBeVisible
CA1036 OverrideMethodsOnComparableTypes
CA1038 EnumeratorsShouldBeStronglyTyped
CA1039 ListsAreStronglyTyped
CA1040 AvoidEmptyInterfaces
CA1041 ProvideObsoleteAttributeMessage
CA1043 UseIntegralOrStringArgumentForIndexers
CA1044 PropertiesShouldNotBeWriteOnly
CA1045 DoNotPassTypesByReference
CA1046 DoNotOverloadOperatorEqualsOnReferenceTypes
CA1050 DeclareTypesInNamespaces
CA1051 DoNotDeclareVisibleInstanceFields
CA1052 StaticHolderTypesShouldBeSealed
CA1053 StaticHolderTypesShouldNotHaveConstructors
CA1054 UriParametersShouldNotBeStrings
CA1055 UriReturnValuesShouldNotBeStrings
CA1056 UriPropertiesShouldNotBeStrings
CA1057 StringUriOverloadsCallSystemUriOverloads
CA1058 TypesShouldNotExtendCertainBaseTypes
CA1059 MembersShouldNotExposeCertainConcreteTypes

Globalization
CA1300 SpecifyMessageBoxOptions
CA1301 AvoidDuplicateAccelerators
CA1304 SpecifyCultureInfo
CA1305 SpecifyIFormatProvider
CA1306 SetLocaleForDataTypes
CA1307 SpecifyStringComparison
CA1309 UseOrdinalStringComparison
CA2101 SpecifyMarshalingForPInvokeStringArguments

Interoperability
CA1401 PInvokesShouldNotBeVisible
CA1402 AvoidOverloadsInComVisibleInterfaces
CA1403 AutoLayoutTypesShouldNotBeComVisible
CA1404 CallGetLastErrorImmediatelyAfterPInvoke
CA1405 ComVisibleTypeBaseTypesShouldBeComVisible
CA1406 AvoidInt64ArgumentsForVB6Clients
CA1408 DoNotUseAutoDualClassInterfaceType
CA1413 AvoidNonpublicFieldsInComVisibleValueTypes

Naming
CA1700 DoNotNameEnumValuesReserved
CA1701 ResourceStringCompoundWordsShouldBeCasedCorrectly
CA1702 CompoundWordsShouldBeCasedCorrectly
CA1703 ResourceStringsShouldBeSpelledCorrectly
CA1704 IdentifiersShouldBeSpelledCorrectly
CA1707 IdentifiersShouldNotContainUnderscores
CA1708 IdentifiersShouldDifferByMoreThanCase
CA1709 IdentifiersShouldBeCasedCorrectly
CA1710 IdentifiersShouldHaveCorrectSuffix
CA1711 IdentifiersShouldNotHaveIncorrectSuffix
CA1712 DoNotPrefixEnumValuesWithTypeName
CA1713 EventsShouldNotHaveBeforeOrAfterPrefix
CA1714 FlagsEnumsShouldHavePluralNames
CA1715 IdentifiersShouldHaveCorrectPrefix
CA1716 IdentifiersShouldNotMatchKeywords
CA1719 ParameterNamesShouldNotMatchMemberNames
CA1720 IdentifiersShouldNotContainTypeNames
CA1721 PropertyNamesShouldNotMatchGetMethods
CA1722 IdentifiersShouldNotHaveIncorrectPrefix
CA1724 TypeNamesShouldNotMatchNamespaces

Performance
CA1811 AvoidUncalledPrivateCode
CA1812 AvoidUninstantiatedInternalClasses
CA1813 AvoidUnsealedAttributes
CA1815 OverrideEqualsAndOperatorEqualsOnValueTypes
CA1816 DisposeMethodsShouldCallSuppressFinalize
CA1819 PropertiesShouldNotReturnArrays

Portability
CA1900 ValueTypeFieldsShouldBePortable
CA1901 PInvokeDeclarationsShouldBePortable

Reliability>

CA2001 AvoidCallingProblematicMethods
CA2002 DoNotLockOnObjectsWithWeakIdentity
CA2004 RemoveCallsToGCKeepAlive
CA2006 UseSafeHandleToEncapsulateNativeResources>

Security
CA2102 CatchNonClsCompliantExceptionsInGeneralHandlers
CA2103 ReviewImperativeSecurity
CA2104 DoNotDeclareReadOnlyMutableReferenceTypes
CA2105 ArrayFieldsShouldNotBeReadOnly
CA2106 SecureAsserts
CA2107 ReviewDenyAndPermitOnlyUsage
CA2108 ReviewDeclarativeSecurityOnValueTypes
CA2109 ReviewVisibleEventHandlers
CA2111 PointersShouldNotBeVisible
CA2112 SecuredTypesShouldNotExposeFields
CA2114 MethodSecurityShouldBeASupersetOfType
CA2115 CallGCKeepAliveWhenUsingNativeResources
CA2116 AptcaMethodsShouldOnlyCallAptcaMethods
CA2117 AptcaTypesShouldOnlyExtendAptcaBaseTypes
CA2118 ReviewSuppressUnmanagedCodeSecurityUsage
CA2119 SealMethodsThatSatisfyPrivateInterfaces
CA2120 SecureSerializationConstructors
CA2121 StaticConstructorsShouldBePrivate
CA2122 DoNotIndirectlyExposeMethodsWithLinkDemands
CA2123 OverrideLinkDemandsShouldBeIdenticalToBase
CA2124 WrapVulnerableFinallyClausesInOuterTry
CA2126 TypeLinkDemandsRequireInheritanceDemands
CA2127 SecurityTransparentAssembliesShouldNotContainSecurityCriticalCode
CA2128 SecurityTransparentCodeShouldNotAssert
CA2129 SecurityTransparentCodeShouldNotReferenceNonpublicSecurityCriticalCode

Usage
CA1806 DoNotIgnoreMethodResults
CA2207 InitializeValueTypeStaticFieldsInline
CA2208 InstantiateArgumentExceptionsCorrectly
CA2209 AssembliesShouldDeclareMinimumSecurity
CA2211 NonConstantFieldsShouldNotBeVisible
CA2213 DisposableFieldsShouldBeDisposed
CA2214 DoNotCallOverridableMethodsInConstructors
CA2216 DisposableTypesShouldDeclareFinalizer
CA2217 DoNotMarkEnumsWithFlags
CA2218 OverrideGetHashCodeOnOverridingEquals
CA2220 FinalizersShouldCallBaseClassFinalizer
CA2221 FinalizersShouldBeProtected
CA2224 OverrideEqualsOnOverloadingOperatorEquals
CA2225 OperatorOverloadsHaveNamedAlternates
CA2227 CollectionPropertiesShouldBeReadOnly
CA2228 DoNotShipUnreleasedResourceFormats
CA2229 ImplementSerializationConstructors
CA2230 UseParamsForVariableArguments
CA2233 OperationsShouldNotOverflow
CA2234 PassSystemUriObjectsInsteadOfStrings
CA2235 MarkAllNonSerializableFields
CA2236 CallBaseClassMethodsOnISerializableTypes
CA2237 MarkISerializableTypesWithSerializable
CA2240 ImplementISerializableCorrectly

Anuncios

enero 25, 2011

Lo nuevo de SQL Server 2011: COLUMN STORAGE INDEX

Filed under: Engine, SQL Server, SQL Server 2011 — grimpi @ 4:28 pm

Una de las características más importantes que promete SQL Server 2011, son los índices por columna.

Introducción
A partir del 2007, salieron a la luz distintas bases de datos llamadas “Column-oriented DBMS”, que implementaron el concepto de índice por columna, el cual prometía revolucionar el mercado, especialmente el de datawharehousing. Uno de los motores mas conocidos es Vertica (y su versión gratis, C-Store), también MonetDB, que es open source. Incluso Sybase lanzó un producto llamado Sybase IQ. Por supuesto, hubo otras empresas que lanzaron productos similares.
El problema de estos productos es que son “Column-oriented DBMS” exclusivamente. O sea, no almacenan la información de la misma manera que la almacena una típica base de datos relacional tradicional como Oracle, MySql o SQL Server. Esto significa que el mercado de estos productos está muy limitado a escenarios específicos.

Por el contrario, SQL Server 2011 promete ser la primera de bases de datos relacional “tradicional” en incorporar como una feature mas a su motor, el índice por columna, pudiendo ser combinado su uso con la tradicional estructura “row-store”.
 

¿Ahora bien, que es un índice por columna?
En todos los motores relacionales, los índices se almacenan por filas (rows). Esto significa que la estructura del índice en su última instancia, va a tener un puntero a un registro de una tabla. Existen distintas maneras de construir un índice (bitmap, b-tree, etc), pero todos estos métodos en definitiva hacen lo mismo: acelerar el acceso a un registro.
El índice por columna por el contrario, invierte la filosofía. Ya no busca registros, busca columnas. En una tabla “normal”, los registros son almacenados en páginas físicas de disco. Cada página contiene una determinada cantidad de registros (que varia según el motor y los registros. En SQL Server cada página de datos tiene un tamaño fijo de 8K). Un índice por columna, es al revés. En cada página de datos, se guardan columnas, no filas.

¿Bárbaro, ahora que ventajas tiene esto?
La respuesta es simple. Performance. Y por 2 razones.

1) Al estar las páginas de disco ordenadas por columnas, cuando hago una consulta, el motor puede leer solo las columnas que necesitamos para nuestra query, lo cual evita lecturas de disco innecesarias.
2) Es muchísimo mas probable que exista redundancia de información entre los valores de una misma columna en distintos registros, que entre los valores de las distintas columnas en un mismo registro, por lo tanto, es mucho mas fácil y eficiente la compresión de datos, lo que significaría mas información en memoria y menos acceso al disco. Ejemplo:
Si yo tengo 4 registros de 4 columnas cada uno:

  Col1 Col2 Col3 Col4
Row 1 01 Jose 01/01/2000 4
Row 2 02 Eduardo 02/02/2004 5
Row 3 03 Ramon 03/02/2004 5
Row 4 04 Susana 02/02/2004 5

Si yo comprimo esta información por columna, mi grado de compresión seria mucho mayor a que si yo comprimo por registro. Porque el grado de homogenización de valores dentro de una columna es mucho mayor. En cambio, en un registro, como tengo distintos tipos de datos y valores posibles entre ellos muy disímiles, la compresión no es tan efectiva. No es lo mismo comprimir 100 valores de un mismo tipo de datos, que comprimir 100 valores de distintos tipos de datos.
Por otro lado, existen diferentes técnicas de compresión que funcionan mejor con un tipo de datos que con otro (ejemplo: hay métodos que son más eficientes para números o binarios, y otros métodos que son más eficientes para texto). Por lo tanto, el motor elige una forma de compresión diferente dependiendo del tipo de dato de la columna, algo que es imposible de hacer en una compresión por row, lo que incrementa aun más el nivel de compresión.

Un gran ratio de compresión me permite tener más información en el cache de datos, incrementar fuertemente los aciertos del buffer y evitar el acceso a I/O, que es la operación más costosa en una base de datos.
En las pruebas hechas por los laboratorios de Microsoft sobre datos reales y haciendo la comparativa con SQL Server 2008 R2, en algunos casos se obtuvo un incremento de hasta 60 veces la performance de una consulta. Lo cual resulta un número bastante impresionante.

¿Como se usa un índice por columna?
Si bien todavía la documentación oficial en SQL Server 2011 no está disponible porque el producto no fue liberado, si podemos ver la sintaxis si entramos a la web del sitio.
Para crear un índice por columna simplemente deberemos anteponer la palabra clave COLUMNSTORE.
Ejemplo:

 
CREATE COLUMNSTORE INDEX NuevoIndice ON Personas
(
FechaDeVenta,
FechaDeEntrega,
Producto,
Precio,
Cantidad
)
 

¿Siempre va a convenir entonces usar este tipo de índices?
La respuesta es un rotundo no. Los índices por columna es un índice más que incrementa notablemente la performance en bases de datos muy grandes y en determinado tipo de consultas. Pero son índices pensados para escenarios de datawharehousing, especialmente para consultas del tipo star join. No en todos los casos es mas óptimo este tipo de índice ni tampoco están pensados para las típicas bases de datos transaccionales OLTP. Para una query normal, sigue siendo mas útil usar los índices b-tree clásicos. El motor de SQL Server en función de sus estadísticas, sabrá elegir cuando es más conveniente usar un índice por columna o un índice común.
Por otra parte, este tipo de índices en SQL Server tienen por el momento varias restricciones. No todos los tipos de datos son soportados y más importante aún, no se pueden hacer operaciones de INSERT, UPDATE, DELETE o MERGE sobre tablas con este tipo de índices. La manera de llenar una tabla entonces es deshabilitar el índice temporalmente, llenar la tabla y volver a habilitar el índice. Otra opción es crear una vista indexada con columnstore index sobre la tabla y dejar a esta tabla sin este tipo de índice.
Por ultimo, otro dato a tener en cuenta, que la reconstrucción de un column index lleva entre 2 y 3 veces mas tiempo, que un índice b-tree clásico.

Conclusión:
Lamentablemente la primera CTP de SQL Server 2011 que lanzó Microsoft todavía no incluye esta feature, por lo tanto, no la podemos probar y verificar si el aumento de su rendimiento es tan significativo como ellos dicen. Sin embargo, el índice por columna es una característica muy distintiva y que hasta el momento, ni Oracle ni DB2 pudieron incorporar nativamente en su producto relacional.
Seguramente con el correr de los tiempos y a medida que tengamos mas información, van a aparecer en blogs y en artículos, distintas comparaciones y escenarios en donde conviene utilizar índices por columnas o el clásico índice por filas.

Links interesantes:
ColumnStores vs. RowStores: How Different Are They Really?
Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0

enero 18, 2011

Lo nuevo en SQL Server 2011

Filed under: SQL Server, SQL Server 2011, T-SQL — grimpi @ 2:02 pm

Estuve investigando SQL Server 2011, llamada “Denali”. Actualmente está en estado beta y se puede bajar la CTP desde acá. Antes de bajarla, para poder instalar esta CTP se requiere como minimo Windows Vista SP2. No funciona ni en Windows XP ni en Windows 2003. De todas maneras parece bastante prometedora.
Que tiene de nuevo esta nueva versión? Obviamente muchas, pero hay 2 cosas que realmente me gustaron:

1) Objetos sequences: Quienes trabajen con Oracle o PostgreSQL, sabrán perfectamente que son. Para el que no lo sabe, un sequence es un objeto cuyo valor se autoincrementa cada vez que es consultado.
Es un excelente reemplazo de los identities, ya que es un objeto externo a la tabla.
Dentro de poco veremos las flamewars en blogs argumentando si es mejor o peor usar sequences o identities como campo PK de una tabla.
Sin embargo, la incorporación de este objeto facilitara enormemente la migración de Oracle y PostgreSQL a SQL Server.
Ejemplo:

CREATE SEQUENCE PrimerSequence
START WITH 1
INCREMENT BY 1;
GO

SELECT (NEXT VALUE FOR PrimerSequence)
GO

INSERT INTO Tabla (Id, Nombre)
SELECT NEXT VALUE FOR PrimerSequence, ‘Esteban’
GO

Link:
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx

La única contra que le veo, es la forma en que SQL Server implementó la lectura de los sequences. Escribir NEXT VALUE FOR xxxx es demasiado texto para simplemente para leer un sequence.
En Oracle o en PostgreSQL resulta más legible y compacto hacer lo mismo.

2) Paginación: Ya no hay que inventar subqueries y cosas raras para paginar datos. Con SQL Server 2011 va a ser posible paginar nativamente una query declarando un OFFSET en la cláusula ORDER BY.
Habrá que investigar la performance de esto, pero parece prometedor.
Ejemplo:

SELECT Id, Campo1, Campo2 FROM Tabla
ORDER BY
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY
GO

Maravilloso. Lo único que no entiendo es porque decidieron poner en el ORDER BY la paginación y no en la instrucción TOP, que pareciera ser lo mas lógico. Después de todo se supone que el ORDER BY solo se limita a ordenar datos y no a limitar la cantidad de registros a devolver.
Además lo hicieron demasiado trabajoso. Demasiadas palabras claves para algo tan simple. Hay que escribir OFFSET, ROWS, FETCH, NEXT, ONLY, ROWS nuevamente solamente para limitar un resultado. MySql lo resuelve de manera mucho mas elegante y practica con su cláusula LIMIT (0,10). Simple, claro y elegante.

Link:
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx

ACTUALIZACION: Me acabo de enterar que la manera en que SQL Server implementó la paginacion corresponde a la especificación ANSI SQL:2008.

Hay otras cosas interesantes en SQL Server 2011.  Se agregó un nuevo tipo de indice, llamado “columnstore”, pensado especialmente para escenarios de datawharehousing. Se mejoró notablemente el Full Text Search, la IDE cambió, se agregaron snippets, varias mejoras en BI y SSIS.
Seguramente con el correr de los meses, se irán incorporando nuevas características. Pero ya el hecho de tener paginación nativa y sequences, representa un gran avance para los desarrolladores y en mi opinion, el nuevo indice por columnas en algunos escenarios, va a ser imprescindible. Pero esto lo voy a explicar en otro post.

enero 14, 2011

Identity avanzado en SQL Server

Filed under: Identitys, SQL Server — grimpi @ 4:11 pm

Hace un tiempo escribí un post sobre como trabajar con las columnas identity. Bueno, acá viene la segunda parte con tips un poco mas avanzados.

1) Modificar una columna para que sea identity:
No se puede. SQL no permite modificar la propiedad Identity de una columna, ya sea para habilitar o deshabilitar esa propiedad. No existe algo asi como un ALTER COLUMN Nombre_Campo SET IDENTITY ON (Como dato curioso, Sybase si tiene esta posibilidad). Pero muchos diran, ¿Como no puede ser posible, si yo lo hago desde el enterprise manager?. Bueno, lo que hace ese programa cuando se habilita (o deshabilita) la propiedad identity a una columna existente, es reconstruir toda la tabla. Esto significa borrar todas las dependencias de una tabla, crear una tabla auxiliar similar a la tabla original pero con la propiedad identity cambiada, copiar todo el contenido de la tabla original a la auxiliar, borrar la tabla original, renombrar la tabla auxiliar por el nombre que tenia la tabla original y reconstruir todas las dependencias.
En una tabla vacia, el costo de hacer esto es casi nulo, en una tabla con 20 millones de registros y 30 tablas asociadas a esta via llaves foraneas es casi suicida. Por tal motivo, y con muy buen criterio, a partir de SQL Server 2008, el Enterprise Manager deshabilita por defecto todas las operaciones sobre edicion de tablas y columnas, que signifiquen una reconstruccion de la tabla. Esto sirve para evitar que algun despistado sin conocimiento sobre lo que hace, reconstruya una tabla en un servidor de produccion.
De todas maneras, si necesitamos hacer esto independientemente del costo que nos representa, este este es un script de ejemplo:

/*Paso 1: Borro todos los objetos dependedientes de la tabla a modificar*/
ALTER TABLE TablaAsociada DROP CONSTRAINT TablaAsociada_Original_FK

/*Paso 2: Creo una tabla nueva exactamente similar a la original, pero agregando la propiedad Identity*/
CREATE TABLE TablaOriginalAuxiliar
(
ID int IDENTITY(1,1),
Campo1 varchar(200)
)

/*Paso 3: Habilitamos el insertado explicito de valores en la columna de tipo identity para mantener los valores antiguos del campo ID*/
SET IDENTITY_INSERT TablaOriginalAuxiliar ON

/*Paso 4: Copio el contenido de una tabla a la otra*/
INSERT INTO TablaOriginalAuxiliar (ID, Campo1) SELECT ID, Campo1 FROM TablaOriginal

/*Paso5: Deshabilitamos el insertado explicito de valores en la columna de tipo identity*/
SET IDENTITY_INSERT TablaOriginalAuxiliar OFF

/*Paso 6: Borro la tabla original*/
DROP TABLE TablaOriginal

/*Paso 7: Renombro la tabla auxiliar por la tabla original*/
sp_rename ‘TablaOriginalAuxiliar’,‘TablaOriginal’

/*Paso 8: Creo todos los objetos dependedientes de la tabla a modificada que habia borrado en el paso 1*/
ALTER TABLE TablaAsociada ADD CONSTRAINT TablaAsociada_Original_FK

Todo esto tambien aplica en el caso inverso, cuando tenemos una columna identity y queremos deshabilitarla.

2) Determinar el estado del IDENTITY_INSERT en una tabla:
En algunas ocasiones (no muchas) puede ser necesario verificar si una tabla que tiene una columna identity, tiene en estado ON o OFF la opcion IDENTITY_INSERT. Nuevamente, no es posible de manera directa hacer esto. No hay ninguna vista de sistema que nos indique el estado de la tabla con respecto a esta situación.

Sin embargo, existen métodos alternativos para hacer esto.
El método mas común es insertar un registro en tabla que queremos seteandole un valor explicito que sabemos que no existe en la columna (ejemplo: un 0, que no suele ser un valor habitual para un ID) en la columna identity. Si tira error, es porque la opción esta deshabilitada. Toda esta operación debe estar encapsulada dentro de una transacción y al finalizar, deberemos hacer un rollback.

Ejemplo:

BEGIN TRANSACTION
DECLARE @err int
INSERT INTO tabla1 (id) values (0)
SET @err=@@error
ROLLBACK TRANSACTION

IF @err =0
print ‘identity_insert = on’
else
print ‘identity_insert = off’

3) Consultar si una columna es identity

Para esto, debemos ver las vistas de sistema de SQL Server.
La forma mas práctica, es consultar la vista sys.column y verificar el valor del campo is_identity.
Ejemplo:

select name, is_identity from sys.columns where OBJECTNAME(object_id) = ‘NombreDeLaTabla’ and name = ‘NombreColumna’

Existe tambien una vista llamada sys.identity_columns que nos devuelve todas las columnas identity de todas las tablas de la base de datos.

Recordemos que estos ejemplos solo aplican a SQL Server 2005/2008 en adelante. No funciona en SQL Server 2000.

4) Modificar la propiedad NOT FOR REPLICATION de una columna identity sin reconstruir la tabla:
Por alguna razón desconocida, si deseamos modificar la propiedad NOT FOR REPLICATION de una columna identity desde el enterprise manager de SQL Server, este reconstruye la tabla (hace exactamente lo mismo que vimos en el punto 1 al principio del post).
Sin embargo, existe una manera de hacer esto muchísimo mas eficiente y es llamar al store procedure de sistema sys.sp_identitycolumnforreplication.
Internamente este store procedure llama a un proceso interno de SQL Server que modifica la tabla, sin necesidad de reconstruirla.

Ejemplo para habilitar la propiedad NOT FOR REPLICATION:

EXEC sys.sp_identitycolumnforreplication OBJECT_ID(“NombreDeLaTabla”), 1



Ejemplo para deshabilitar la propiedad NOT FOR REPLICATION:

EXEC sys.sp_identitycolumnforreplication OBJECT_ID(“NombreDeLaTabla”), 0

5) Buscar gaps o huecos dentro de una columna
Esta es una pregunta habitual, pero la realidad es que no se me ocurre un escenario real donde un hueco entre los valores de una columna identity pueda ser relevante para nosotros. Generalmente quienes consideran relevante esto, es porque estan haciendo un mal uso conceptual de los identities. Recordemos que un identity funciona perfecto como valor interno para una clave primaria. Pero le queremos dar otros usos (como por ejemplo, que sea el codigo numerico de una factura) estamos cometiendo un error suicida. Los identities no son transaccionales y no se puede confiar en una tabla no tenga huecos si se efectuan operaciones de DELETE sobre la misma.

De todos modos, quienes esten buscando gaps, les recomiendo entrar a este post del excelente blog de Pinal Dave, donde no solamente está disponible un script para realizar dicha consulta (para nada algo trivial), sino que ademas hay una muy buena polémica al respecto del uso de los identities.

enero 8, 2011

Desglosando la metada en PostgreSQL 8.2 o superior

Filed under: Metada, PostgreSQL — grimpi @ 2:59 pm

Después de haber probado como extraer la información del schema de todas las bases de datos del mercado, debo decir que por lejos, SQL Server tiene el método mas sencillo y potente de acceso a su metada y PostgreSQL el método mas entrincado y críptico. No me refiero a traer un simple listado de tablas, sino a cosas más complejas, como por ejemplo traer el detalle de las constraints o de los índices.

Traer todas las tablas de una base de datos:

SELECT c.* FROM pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’ and pg_catalog.pg_table_is_visible(c.oid)
and n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’)

Con esto le decimos que nos traiga todos los objetos de la base de tipo tabla que no sean invisibles y que no pertenezcan a los schemas pg_catalog y pg_toast, que son schemas internos de PostgreSQL.
El campo relkind determina qué tipo de objeto queremos buscar. Esta es la tabla de equivalencias:

r Table
S Sequence
i Index
v View
c Composite Type


Traer todas las primary key con sus columnas por tabla de una base de datos:

SELECT cc.relname as tabla, cc.oid as cid, a.attnum, c.conname as Name, a.attname, c.oid as ID FROM pg_constraint c, pg_class cc, pg_attribute a
WHERE contype = ‘p’
and cc.oid = c.conrelid
and a.attrelid = cc.oid
and a.attnum = ANY(c.conkey)
order by cc.oid, c.oid

La table pg_constraint nos trae todas las constraints y el campo contype el tipo de constraint. Esta es la tabla de equivalencias:

p Primary Key
u Unique Key
f Foreign Key
c Check Constraint
t Trigger Constraint
X Exclusive Constraint


Traer el listado de permisos de un objeto:

Dentro de la tabla pg_class, existe un campo vectorial llamado relacl, que contiene una lista de todos los permisos del objeto.

SELECT relacl FROM pg_class

Este campo devuelve un valor similar a este: “{=awdxt/postgres,postgres=awdxt/postgres,crm=arw*d*x*t*/postgres}”. Que significa este resultado?
En esa cadena de string aparentemente inentendible, tenemos toda la información sobre los permisos del objeto.
Recordemos que este campo es un vector, por lo tanto, cada carácter “,” significa un nuevo ítem en el vector. Por lo tanto, este resultado de ejemplo, tiene 3 ítems. Cada posición del vector, corresponde a un usuario/grupo distinto con permisos en el objeto.
Cada ítem del vector, tiene 3 datos: Usuario, Permisos y Owner del objeto.
Ejemplo: “crm=arw*d*x*t*/postgres”
En este caso, el usuario es crm (es el valor que esta antes del “=”), los permisos son “arw*d*x*t*” y el owner es postgres.
Ahora, que significa “arw*d*x*t*” realmente? Simple: cada uno de estos caracteres, es una operación posible (INSERT, DELETE, etc) sobre el objeto.
Esta es la tabla de equivalencias:

“a” INSERT
“r” SELECT
“w” UPDATE
“d” DELETE
“x” REFERENCES
“R” RULE
“t” TRIGGER
“X” EXECUTE
“U” ALTER
“c” CONNECT
“C” CREATE
“*” HABILITA LA OPCION GRAN OPTION A CADA OPERACION

Por lo tanto, este objeto tiene permisos de INSERT, SELECT, UPDATE, DELETE, TRIGGER y REFERENCES, de las cuales para las operaciones UPDATE, DELETE, REFERENCES y TRIGGER, la opción WITH GRAN OPTION esta activada.

=awdxt/postgres GRANT INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO public;

(si no aparece un nombre usuario, significa que el permiso pertenece al rol “public”)

postgres=awdxt/postgres GRANT INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO postgres;

 

crm=arw*d*x*t*/postgres GRANT INSERT, SELECT ON TABLE “Nombre de la tabla” TO “crm”;

GRANT UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO “crm” WITH GRANT OPTION;

 



Traer todos los sequences de la base de datos

SELECT * FROM pg_class c where relkind = ‘S’

El problema de esta query, es que nos trae solo el nombre del sequence, pero no el detalle del mismo (valor máximo y mínimo, valor actual, etc.). Si solo queremos el nombre, perfecto, pero si necesitamos mas, la tabla pg_class no nos sirve.
Para traer entonces el detalle de la información de un sequence simplemente tenemos que hacer un SELECT sobre el mismo como si fuera una vista o una tabla.

SELECT * FROM “nombre_del_sequence”

sequence_name last_value start_value increment_by max_value min_value cache_value is_cycled
nombre_del_sequence 1 1 1 922337203685 1 1 f

Dejo en este link algunos scripts de ejemplo de para extraer la metadata de distintos objetos de PostgreSQL.

Blog de WordPress.com.