Evaluación final de resultados y conteo de horas

Horas totales: 21 horas

Se Implementa la base de datos, mediante un script que creara la base de datos

Se crean scripts de llenado de datos, hechos por nosotros.

Se hacen los Store procedures que implementan las acciones que corresponden con el proyecto.

Aprendimos a hacer una simulación usando nuestros propios datos de prueba y también como crear dichos datos.

Para aprender a generar el archivo XML se utilizo la siguiente pagina : http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/ 

Y por ultimo se realizaron las 2 consultas que se pedian para el proyecto, y se provaron con exito.

Segunda consulta

(2 horas)
Se trabajo en la segunda consulta, en esta consulta se presento un poco mas de dificultad pero se logro completar usando inner join, porque en la parte de mas enviadas y mejor calificadas se debían extraer datos de otras tablas.
USE [III Proyecto - WebNews]
GO

CREATE PROCEDURE WNSP_Consulta2
@inTema varchar(50),
@inCate varchar (50)

AS
BEGIN

DECLARE @Can int
DECLARE @Tema int
DECLARE @NoticiasXCalificacion table
(
Titulo varchar (200),
Calificacion int
)
DECLARE @NoticiasXEnvios table
(
Titulo varchar (200),
Envios int
)
DECLARE @Noticias table
(
Titulo varchar (200),
Texto varchar (max),
Fecha date ,
Visitas int
)
DECLARE @NoticiasXEnvio table
(
Titulo varchar (200),
Texto varchar (max),
Fecha date ,
Envios int
)
Select @Can = Cantidad
From dbo.Parametros 
where Nombre = 'Maximo'
IF @inCate = 'más visitadas'
BEGIN 
IF @inTema = 'Portada'
BEGIN 
INSERT INTO @Noticias(Titulo,Texto,Fecha,Visitas)
Select  Titulo,Texto,Fecha,Visitas
from dbo.Noticias 
Where ID in (Select Fk_Noticia
from dbo.NoticiasXPortadas
)
Order by Visitas desc

Select TOP (@Can) *
from @Noticias
Order by Fecha desc
END
ELSE 
BEGIN 
Select @Tema = ID
from dbo.Temas
where Nombre = @inTema

Insert into @Noticias(Titulo,Texto,Fecha,Visitas)
Select  Titulo,Texto,Fecha,Visitas
from dbo.Noticias
Where Noticias.Fk_Subtema in (Select Subtemas.ID 
from dbo.Subtemas
Where Fk_Tema = @Tema)
Order by Visitas desc



Select TOP (@Can)*
From @Noticias
Order by Fecha desc
END
END
IF @inCate = 'más enviadas' 
BEGIN 
IF @inTema = 'Portada'
BEGIN 
Insert into @NoticiasXEnvios (Titulo,Envios)
Select S.Titulo,Count(S.ID)
from
(Select dbo.Envios.ID, A.Titulo
from
(Select ID,Titulo
from dbo.Noticias
where ID in (Select Fk_Noticia from dbo.NoticiasXPortadas ))as A
inner join
dbo.Envios 
on dbo.Envios.Fk_Noticia = A.ID
) as S
Group by S.Titulo

SELECT TOP (5) * 
From @NoticiasXEnvios
Order by Envios desc
END
ELSE
BEGIN
Select @Tema = ID
from dbo.Temas
where Nombre = @inTema

Insert into @NoticiasXEnvios (Titulo,Envios)
Select S.Titulo,Count(S.ID)
from
(Select dbo.Envios.ID, A.Titulo
from
(Select ID,Titulo
from dbo.Noticias
Where Fk_Subtema in (Select Subtemas.ID 
from dbo.Subtemas
Where Fk_Tema = @Tema))as A
inner join
dbo.Envios 
on dbo.Envios.Fk_Noticia = A.ID
) as S
Group by S.Titulo

SELECT TOP (5) * 
From @NoticiasXEnvios
Order by Envios desc
END
END
IF @inCate = 'más enviadas' 
BEGIN 
IF @inTema = 'Portada'
BEGIN 
Insert into @NoticiasXCalificacion (Titulo,Calificacion)
Select S.Titulo,AVG(S.Calificacion)
from
(Select dbo.Calificaciones.Calificacion, A.Titulo
from
(Select ID,Titulo
from dbo.Noticias
Where ID in (Select Fk_Noticia from dbo.NoticiasXPortadas))as A
inner join
dbo.Calificaciones
on dbo.Calificaciones.Fk_Noticia = A.ID
) as S
Group by S.Titulo

SELECT TOP (5) * 
From @NoticiasXCalificacion
Order by Calificacion desc
END
ELSE
BEGIN 
Select @Tema = ID
from dbo.Temas
where Nombre = @inTema

Insert into @NoticiasXCalificacion (Titulo,Calificacion)
Select S.Titulo,AVG(S.Calificacion)
from
(Select dbo.Calificaciones.Calificacion, A.Titulo
from
(Select ID,Titulo
from dbo.Noticias
Where Fk_Subtema in (Select Subtemas.ID 
from dbo.Subtemas
Where Fk_Tema = @Tema))as A
inner join
dbo.Calificaciones
on dbo.Calificaciones.Fk_Noticia = A.ID
) as S
Group by S.Titulo

SELECT TOP (5) * 
From @NoticiasXCalificacion
Order by Calificacion desc
END
END
END
GO

Se termina la primer consulta

(1 hora)
Se termina de hacer la consulta y se prueba para confirmar que este lista.

Se trabaja por primera vez con la generación de un XML para los datos de prueba

(2 horas)

Para el día de hoy se aprendió a hacer XML con los datos de prueba. Y así poder permitir a algunos compañero trabajar con los datos que hemos generado. Para hacer XML es muy sencillo, solo se hace un SELECT normal con sus clausulas, pero al final se le agregar FOR XML PATH(' ruta ') TYPE. De esta manera es muy sencillo crear archivos XML.

Nuestro SELECT quedó de la siguiente forma:

USE [III Proyecto - WebNews]
GO

SELECT (SELECT T.Nombre, T.Cod AS CodigoTema
        FROM dbo.Temas T
        FOR
        XML PATH('Temas'),
        TYPE),
        (SELECT S.Nombre AS NombreSubtema, S.Cod AS CodigoSubtema, S.Fk_Tema, T.Nombre AS NombreTema, T.Cod AS CodigoTema
        FROM dbo.Subtemas S, dbo.Temas T
        WHERE S.Fk_Tema = T.ID
        FOR
        XML PATH('Subtemas'),
        TYPE),
        (SELECT A.Nombre, A.Email
        FROM dbo.Autores A
        FOR
        XML PATH('Autores'),
        TYPE),
        (SELECT U.Nombre, U.Email
        FROM dbo.Usuarios U
        FOR
        XML PATH('Usuarios'),
        TYPE),
        (SELECT N.Titulo, N.Texto, N.Fecha, N.Fk_Autor, A.Nombre AS NombreAutor, A.Email AS EmailAutor, N.Fk_Subtema, S.Nombre AS NombreSubtema, S.Cod AS CodigoSubtema
        FROM dbo.Noticias N, dbo.Autores A, dbo.Subtemas S
        WHERE N.Fk_Autor = A.ID
        AND N.Fk_Subtema = S.ID
        FOR
        XML PATH('Noticias'),
        TYPE),
        (SELECT U.Nombre AS NombreUsuario,
               U.Email AS EmailUsuario,
               N.IDTipoFk,
               N.Fk_Tema,
               (SELECT Nombre FROM Temas WHERE ID = N.Fk_Tema) AS NombreTema,
               (SELECT Cod FROM Temas WHERE ID = N.Fk_Tema) AS CodTema,
               N.Fk_Subtema,
               (SELECT Nombre FROM Subtemas WHERE ID = N.Fk_Subtema) AS NombreSubtema,
               (SELECT Cod FROM Subtemas WHERE ID = N.Fk_Subtema) AS CodSubtema,
               N.Fk_Autor,
               (SELECT Nombre FROM Autores WHERE ID = N.Fk_Autor) AS NombreAutor,
               (SELECT Email FROM Autores WHERE ID = N.Fk_Autor) AS EmailAutor
        FROM dbo.Usuarios U, dbo.NotificacionesXUsuarios NU, dbo.Notificaciones N
        WHERE NU.Fk_Usuario = U.ID
        AND NU.Fk_Notificacion = N.ID
        FOR
        XML PATH('SolicitudNotificacion'),
        TYPE),
        (SELECT C.Calificacion, C.Fecha, C.Fk_Usuario, U.Nombre AS NombreUsuario, U.Email AS EmailUsuario, C.Fk_Noticia, N.Titulo AS TituloNoticia
        FROM dbo.Usuarios U, dbo.Calificaciones C, dbo.Noticias N
        WHERE C.Fk_Usuario = U.ID
        AND C.Fk_Noticia = N.ID
        FOR
        XML PATH('Calificaciones'),
        TYPE),
        (SELECT C.Comentario, C.Fecha, C.Fk_Usuario, U.Nombre AS NombreUsuario, U.Email AS EmailUsuario, C.Fk_Noticia, N.Titulo AS TituloNoticia
        FROM dbo.Usuarios U, dbo.Comentarios C, dbo.Noticias N
        WHERE C.Fk_Usuario = U.ID
        AND C.Fk_Noticia = N.ID
        FOR
        XML PATH('Comentarios'),
        TYPE)
FOR XML PATH(''),
ROOT('ProyectoIII')
GO

 Como podemos apreciar, en la ruta PATH, se genrará una nueva entrada </ > para el XML. Para las noticias buscamos en el noticiero en linea nacion.com y copiamos algunas noticias.

De esta manera se finaliza la parte lógica del proyecto programado.

Se trabaja en la generación de algunos datos de prueba

(5 horas)

Para el día de hoy ya tenemos nuestro diagrama de base de datos final. Lo mostraremos a continuación:

 

Usamos las habilidades de Python para generar unos datos de prueba muy sencillos y usamos los SPs ya programados para insertarlos. De esta manera, hizimos una pequeña simulación como que presentamos a continuación:

USE [III Proyecto - WebNews]
GO

SET LANGUAGE Spanish

DECLARE @XMLwithOpenXML TABLE
(
ID INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

INSERT INTO @XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'C:\Users\Administrator\Desktop\Datos de Prueba - Proyecto III.xml', SINGLE_BLOB) AS x;

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = XMLData FROM @XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

DECLARE @Noticias table
(
ID int IDENTITY PRIMARY KEY,
Titulo varchar(200),
Texto varchar(MAX),
Fecha date,
Autor varchar(50),
Subtema varchar(3)
)

DECLARE @Calificaciones table
(
ID int IDENTITY PRIMARY KEY,
Calificacion int,
Fecha date,
Email varchar(50),
Fk_Noticia int
)

DECLARE @Comentarios table
(
ID int IDENTITY PRIMARY KEY,
Comentario varchar(500),
Fecha date,
Email varchar(50),
Fk_Noticia int
)

INSERT INTO @Noticias(Titulo, Texto, Fecha, Autor, Subtema)
SELECT Titulo, Texto, Fecha, Autor, Subtema
FROM OPENXML(@hDoc, 'ProyectoIII/Noticias')
WITH
(
Titulo varchar(200) 'Titulo',
Texto varchar(MAX) 'Texto',
Fecha date 'Fecha',
Autor varchar(50) 'EmailAutor',
Subtema varchar(3) 'CodigoSubtema'
)

INSERT INTO @Calificaciones(Calificacion, Fecha, Email, Fk_Noticia)
SELECT Calificacion, Fecha, Email, Fk_Noticia
FROM OPENXML(@hDoc, 'ProyectoIII/Calificaciones')
WITH
(
Calificacion int 'Calificacion',
Fecha date 'Fecha',
Email varchar(50) 'EmailUsuario',
Fk_Noticia int 'Fk_Noticia'
)

INSERT INTO @Comentarios(Comentario, Fecha, Email, Fk_Noticia)
SELECT Comentario, Fecha, Email, Fk_Noticia
FROM OPENXML(@hDoc, 'ProyectoIII/Comentarios')
WITH
(
Comentario varchar(500) 'Comentario',
Fecha date 'Fecha',
Email varchar(50) 'EmailUsuario',
Fk_Noticia int 'Fk_Noticia'
)

DECLARE @Fecha date = '2016-11-01'

DECLARE @Titulo varchar(200)
DECLARE @Texto varchar(MAX)
DECLARE @EmailAutor varchar(50)
DECLARE @Subtema varchar(3)

DECLARE @Calificacion int
DECLARE @Comentario varchar(500)
DECLARE @EmailUsuario varchar(50)

DECLARE @ID_Noticia int
DECLARE @ID_Calificacion int
DECLARE @ID_Comentario int

WHILE @Fecha != '2016-11-22'
BEGIN
  
    SELECT @ID_Noticia = MIN(ID) FROM @Noticias WHERE Fecha = @Fecha
    WHILE @ID_Noticia IS NOT NULL
    BEGIN
      
        SELECT @Titulo = Titulo, @Texto = Texto, @EmailAutor = Autor, @Subtema = Subtema FROM @Noticias WHERE ID = @ID_Noticia
        EXEC dbo.WNSP_AgregarNoticia @Titulo, @Texto, @Fecha, @EmailAutor, @Subtema

        SELECT @ID_Noticia = MIN(ID) FROM @Noticias WHERE Fecha = @Fecha AND ID > @ID_Noticia

    END

    EXEC dbo.WNSP_GenerarPortada @Fecha

    SELECT @ID_Calificacion = MIN(ID) FROM @Calificaciones WHERE Fecha = @Fecha
    WHILE @ID_Calificacion IS NOT NULL
    BEGIN
      
        SELECT @Calificacion = Calificacion, @EmailUsuario = Email, @ID_Noticia = Fk_Noticia FROM @Calificaciones WHERE ID = @ID_Calificacion
        EXEC dbo.WNSP_CalificarNoticia @Calificacion, @Fecha, @EmailUsuario, @ID_Noticia

        EXEC dbo.WNSP_VisitarNoticia @ID_Noticia

        SELECT @ID_Calificacion = MIN(ID) FROM @Noticias WHERE Fecha = @Fecha AND ID > @ID_Calificacion

    END

    SELECT @ID_Comentario = MIN(ID) FROM @Comentarios WHERE Fecha = @Fecha
    WHILE @ID_Comentario IS NOT NULL
    BEGIN
      
        SELECT @Comentario = Comentario, @EmailUsuario = Email, @ID_Noticia = Fk_Noticia FROM @Comentarios WHERE ID = @ID_Comentario
        EXEC dbo.WNSP_ComentarNoticia @Comentario, @Fecha, @EmailUsuario, @ID_Noticia

        EXEC dbo.WNSP_VisitarNoticia @ID_Noticia

        SELECT @ID_Comentario = MIN(ID) FROM @Comentarios WHERE Fecha = @Fecha AND ID > @ID_Comentario

    END

    SET @Fecha = DATEADD(DAY, 1, @Fecha)
END

EXEC sp_xml_removedocument @hDoc

Se crean todos los procedimientos

(6 horas)

Se programaron de forma muy habil los siguientes procedimientos:

Este procedimiento se encarga de agregar nuevos temas a la base de datos. Para que este procedimiento sea exitoso, tiene que agregar el tema y además crear una notificación para que pueda ser solicitado por los usuarios.

CREATE PROCEDURE WNSP_AgregarTema
    @inCodigo varchar(2),
    @inNombre varchar(100)
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        IF NOT EXISTS (SELECT 1 FROM dbo.Temas WHERE Cod = @inCodigo)
        BEGIN

            DECLARE @IPCLIENT VARCHAR(15)
            SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

            DECLARE @ID_Tema int
       
            BEGIN TRANSACTION Transaccion

            --Insertamos el nuevo tema
            INSERT INTO [dbo].[Temas]
                       ([Cod]
                       ,[Nombre]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                 VALUES
                       (@inCodigo
                       ,@inNombre
                       ,@IPCLIENT
                       ,CURRENT_USER
                       ,CURRENT_TIMESTAMP)

            --Insertamos notificaciones para ese tema
            SELECT @ID_Tema = ID FROM dbo.Temas WHERE Cod = @inCodigo

            INSERT INTO [dbo].[Notificaciones]
                        ([IDTipoFk]
                        ,[Fk_Tema]
                        ,[PostIn]
                        ,[PostBy]
                        ,[PostDate])
                    VALUES
                        (1
                        ,@ID_Tema
                        ,@IPCLIENT
                        ,CURRENT_USER
                        ,CURRENT_TIMESTAMP)

        END


        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

Así también este otro procedimiento de agregar subtemas

CREATE PROCEDURE WNSP_AgregarSubtema
    @inCodigo varchar(3),
    @inNombre varchar(100),
    @inCodTema varchar(2)
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        IF NOT EXISTS (SELECT 1 FROM dbo.Subtemas WHERE Cod = @inCodigo)
        BEGIN

            DECLARE @IPCLIENT VARCHAR(15)
            SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

            DECLARE @ID_Subtema int
            DECLARE @ID_Tema int

            SELECT @ID_Tema = ID FROM dbo.Temas WHERE Cod = @inCodTema
       
            BEGIN TRANSACTION Transaccion

            --Insertamos el nuevo subtema
            INSERT INTO [dbo].[Subtemas]
                       ([Cod]
                       ,[Nombre]
                       ,[Fk_Tema]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                 VALUES
                       (@inCodigo
                       ,@inNombre
                       ,@ID_Tema
                       ,@IPCLIENT
                       ,CURRENT_USER
                       ,CURRENT_TIMESTAMP)

            --Insertamos notificaciones para ese subtema
            SELECT @ID_Subtema = ID FROM dbo.Subtemas WHERE Cod = @inCodigo

            INSERT INTO [dbo].[Notificaciones]
                        ([IDTipoFk]
                        ,[Fk_Subtema]
                        ,[PostIn]
                        ,[PostBy]
                        ,[PostDate])
                    VALUES
                        (2
                        ,@ID_Subtema
                        ,@IPCLIENT
                        ,CURRENT_USER
                        ,CURRENT_TIMESTAMP)

        END


        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

Y este otro siver para agregar autores

CREATE PROCEDURE WNSP_AgregarAutor
    @inNombre varchar(50),
    @inEmail varchar(50)
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON
  
        IF NOT EXISTS (SELECT 1 FROM dbo.Autores WHERE Email = @inEmail)
        BEGIN

            DECLARE @IPCLIENT VARCHAR(15)
            SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

            DECLARE @ID_Autor int
      
            BEGIN TRANSACTION Transaccion

            --Insertamos el nuevo autor
            INSERT INTO [dbo].[Autores]
                       ([Nombre]
                       ,[Email]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                    VALUES
                        (@inNombre
                        ,@inEmail
                        ,@IPCLIENT
                        ,CURRENT_USER
                        ,CURRENT_TIMESTAMP)

            --Insertamos notificaciones para ese autor
            SELECT @ID_Autor = ID FROM dbo.Autores WHERE Email = @inEmail

            INSERT INTO [dbo].[Notificaciones]
                       ([IDTipoFk]
                       ,[Fk_Autor]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                 VALUES
                       (3
                       ,@ID_Autor
                       ,@IPCLIENT
                        ,CURRENT_USER
                        ,CURRENT_TIMESTAMP)

        END

        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

El siguiente lo creamos para agregar usuarios que reciban notificaciones

USE [III Proyecto - WebNews]
GO

CREATE PROCEDURE WNSP_AgregarUsuario
    @inNombre varchar(50),
    @inEmail varchar(50)
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))
   
        IF NOT EXISTS (SELECT 1 FROM dbo.Usuarios WHERE Email = @inEmail)
        BEGIN

            BEGIN TRANSACTION Transaccion

            --Insertamos el nuevo autor
            INSERT INTO [dbo].[Usuarios]
                       ([Nombre]
                       ,[Email]
                       ,[Activo]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                    VALUES
                        (@inNombre
                        ,@inEmail
                        ,'True'
                        ,@IPCLIENT
                        ,CURRENT_USER
                        ,CURRENT_TIMESTAMP)

        END

        ELSE
        BEGIN
           
            BEGIN TRANSACTION Transaccion

            --Actualizamos su estado a True
            UPDATE [dbo].[Usuarios]
               SET [Activo] = 'True'
                  ,[PostIn] = @IPCLIENT
                  ,[PostBy] = CURRENT_USER
                  ,[PostDate] = CURRENT_TIMESTAMP
             WHERE Email = @inEmail

        END

        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

El que viene acá abajo se usa para calificar noticias:

USE [III Proyecto - WebNews]
GO

CREATE PROCEDURE WNSP_CalificarNoticia
    @inCalificacion int,
    @inFecha date,
    @inEmailUsuario varchar(50),
    @inIDNoticia int
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        DECLARE @ID_Usuario int
        SELECT @ID_Usuario = ID FROM dbo.Usuarios WHERE Email = @inEmailUsuario

        IF NOT EXISTS (SELECT 1 FROM dbo.Calificaciones WHERE Fk_Usuario = @ID_Usuario AND Fk_Noticia = @inIDNoticia)
        BEGIN
           
            BEGIN TRANSACTION Transaccion

            INSERT INTO [dbo].[Calificaciones]
                       ([Calificacion]
                       ,[Fecha]
                       ,[Fk_Usuario]
                       ,[Fk_Noticia]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                 VALUES
                       (@inCalificacion % 6
                       ,@inFecha
                       ,@ID_Usuario
                       ,@inIDNoticia
                       ,@IPCLIENT
                       ,CURRENT_USER
                       ,CURRENT_TIMESTAMP)

        END

        ELSE
        BEGIN
           
            BEGIN TRANSACTION Transaccion

            UPDATE [dbo].[Calificaciones]
               SET [Calificacion] = @inCalificacion % 6
                  ,[Fecha] = @inFecha
                  ,[PostIn] = @IPCLIENT
                  ,[PostBy] = CURRENT_USER
                  ,[PostDate] = CURRENT_TIMESTAMP
             WHERE Fk_Usuario = @ID_Usuario
             AND Fk_Noticia = @inIDNoticia

        END

        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

Este es para comentar las noticias

USE [III Proyecto - WebNews]
GO

CREATE PROCEDURE WNSP_ComentarNoticia
    @inComentario varchar(500),
    @inFecha date,
    @inEmailUsuario varchar(50),
    @inIDNoticia int
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        DECLARE @ID_Usuario int
        SELECT @ID_Usuario = ID FROM dbo.Usuarios WHERE Email = @inEmailUsuario

        BEGIN TRANSACTION Transaccion

        INSERT INTO [dbo].[Comentarios]
                    ([Comentario]
                    ,[Fecha]
                    ,[Fk_Usuario]
                    ,[Fk_Noticia]
                    ,[PostIn]
                    ,[PostBy]
                    ,[PostDate])
                VALUES
                    (@inComentario
                    ,@inFecha
                    ,@ID_Usuario
                    ,@inIDNoticia
                    ,@IPCLIENT
                    ,CURRENT_USER
                    ,CURRENT_TIMESTAMP)

        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

El siguiente aumenta el contador de visitas de una noticia

USE [III Proyecto - WebNews]
GO

CREATE PROCEDURE WNSP_VisitarNoticia
    @inIDNoticia int
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        BEGIN TRANSACTION Transaccion

        UPDATE [dbo].[Noticias]
           SET [Visitas] = [Visitas] + 1
              ,[PostIn] = @IPCLIENT
              ,[PostBy] = CURRENT_USER
              ,[PostDate] = CURRENT_TIMESTAMP
         WHERE ID = @inIDNoticia

        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

El siguiente se hizo para poder solicitar notificaciones

USE [III Proyecto - WebNews]
GO

CREATE PROCEDURE WNSP_SolicitarNotificaciones
    @inEmailUsuario varchar(50),
    @inTipoElemento int,
    @inNombreElemento varchar(50)
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        DECLARE @ID_Elemento int
        DECLARE @ID_Notificacion int
        DECLARE @ID_Usuario int
        SELECT @ID_Usuario = ID FROM dbo.Usuarios WHERE Email = @inEmailUsuario

        --Si es tipo Tema
        IF @inTipoElemento = 1
        BEGIN

            SELECT @ID_Elemento = ID FROM dbo.Temas WHERE Cod = @inNombreElemento
            SELECT @ID_Notificacion = ID FROM dbo.Notificaciones WHERE IDTipoFk = @inTipoElemento AND Fk_Tema = @ID_Elemento

            BEGIN TRANSACTION Transaccion

            INSERT INTO [dbo].[NotificacionesXUsuarios]
                       ([Fk_Usuario]
                       ,[Fk_Notificacion]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                 VALUES
                       (@ID_Usuario
                       ,@ID_Notificacion
                       ,@IPCLIENT
                       ,CURRENT_USER
                       ,CURRENT_TIMESTAMP)

        END

        --Si es tipo Subtema
        ELSE IF @inTipoElemento = 2
        BEGIN

            SELECT @ID_Elemento = ID FROM dbo.Subtemas WHERE Cod = @inNombreElemento
            SELECT @ID_Notificacion = ID FROM dbo.Notificaciones WHERE IDTipoFk = @inTipoElemento AND Fk_Subtema = @ID_Elemento

            BEGIN TRANSACTION Transaccion

            INSERT INTO [dbo].[NotificacionesXUsuarios]
                       ([Fk_Usuario]
                       ,[Fk_Notificacion]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                 VALUES
                       (@ID_Usuario
                       ,@ID_Notificacion
                       ,@IPCLIENT
                       ,CURRENT_USER
                       ,CURRENT_TIMESTAMP)

        END

        --Si es tipo Autor
        ELSE IF @inTipoElemento = 3
        BEGIN

            SELECT @ID_Elemento = ID FROM dbo.Autores WHERE Email = @inNombreElemento
            SELECT @ID_Notificacion = ID FROM dbo.Notificaciones WHERE IDTipoFk = @inTipoElemento AND Fk_Autor = @ID_Elemento

            BEGIN TRANSACTION Transaccion

            INSERT INTO [dbo].[NotificacionesXUsuarios]
                       ([Fk_Usuario]
                       ,[Fk_Notificacion]
                       ,[PostIn]
                       ,[PostBy]
                       ,[PostDate])
                 VALUES
                       (@ID_Usuario
                       ,@ID_Notificacion
                       ,@IPCLIENT
                       ,CURRENT_USER
                       ,CURRENT_TIMESTAMP)

        END

        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

Este SP es muy sencillo. Se encarga de crear una nueva portada diaria

USE [III Proyecto - WebNews]
GO

CREATE PROCEDURE WNSP_GenerarPortada
    @inFecha date
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

        DECLARE @ID_Portada int

        BEGIN TRANSACTION Transaccion

        --Se inserta una entrada para una nueva portada
        INSERT INTO [dbo].[Portadas]
                   ([Fecha]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
             VALUES
                   (@inFecha
                   ,@IPCLIENT
                   ,CURRENT_USER
                   ,CURRENT_TIMESTAMP)

        SET @ID_Portada = SCOPE_IDENTITY()

        --Se insertan las ultimas 5 noticias
        INSERT INTO [dbo].[NoticiasXPortadas]
                   ([Activo]
                   ,[OrdenDespliegue]
                   ,[Fk_Portada]
                   ,[Fk_Noticia]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
            SELECT TOP (5) 'True'
                   ,'D'
                   ,@ID_Portada
                   ,N.ID
                   ,@IPCLIENT
                   ,CURRENT_USER
                   ,CURRENT_TIMESTAMP
              FROM dbo.Noticias N
             ORDER BY N.ID DESC


        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

Esta es una función generadora de texto que será enviado por la simulación de correo electrónico

USE [III Proyecto - WebNews]
GO

CREATE FUNCTION WNFN_TextoEnvio (@inID_Usuario int, @inID_Autor int, @inID_Tema int, @inID_Subtema int, @inID_Noticia int, @inFecha date)

    RETURNS varchar(max)

AS
BEGIN
   
    DECLARE @outputTexto varchar(max)
    DECLARE @NombreUsuario varchar(50)
    DECLARE @EmailUsuario varchar(50)
    DECLARE @NombreAutor varchar(50)
    DECLARE @NombreTema varchar(100)
    DECLARE @NombreSubtema varchar(100)
    DECLARE @TituloNoticia varchar(200)
   
    SELECT @NombreUsuario = Nombre, @EmailUsuario = Email FROM dbo.Usuarios WHERE ID = @inID_Usuario
    SELECT @NombreAutor = Nombre FROM dbo.Autores WHERE ID = @inID_Autor
    SELECT @NombreTema = Nombre FROM dbo.Temas WHERE ID = @inID_Tema
    SELECT @NombreSubtema = Nombre FROM dbo.Subtemas WHERE ID = @inID_Subtema
    SELECT @TituloNoticia = Titulo FROM dbo.Noticias WHERE ID = @inID_Noticia

    SET @outputTexto = 'Para: ' + @NombreUsuario + ' (' + @EmailUsuario + ')' + CHAR(13)+CHAR(10) +
                       'Fecha: ' + CONVERT(varchar(8), @inFecha, 101) + CHAR(13)+CHAR(10) +
                       CHAR(13)+CHAR(10) +
                       'El autor ' + @NombreAutor + ' ha publicado una nueva noticia titulada:' + CHAR(13)+CHAR(10) +
                       char(9) + @TituloNoticia + CHAR(13)+CHAR(10) +
                       'basado en la subcategoria ' + @NombreSubtema + ' de la categoria de ' + @NombreTema + '.' + CHAR(13)+CHAR(10) +
                       'Lo invitamos a enterarse de los detalles de esta noticia visitando nuestro noticiero web.' + CHAR(13)+CHAR(10) +
                       'Atte: nacion.com'

    RETURN @outputTexto
   
END
GO

Por último, el siguiente SP será quien inserta las nuevas noticias y además notificará por medio de el envio de correo electrónico a los usuarios que hallan solicitado notificaciones sobre ese tema, subtema o autor.

USE [III Proyecto - WebNews]
GO

CREATE PROCEDURE WNSP_AgregarNoticia
    @inTitulo varchar(200),
    @inTexto varchar(MAX),
    @inFecha date,
    @inEmailAutor varchar(50),
    @inCodSubtema varchar(3)
AS
BEGIN
    BEGIN TRY

        SET LANGUAGE Spanish
        SET NOCOUNT ON

        DECLARE @IPCLIENT VARCHAR(15)
        SELECT @IPCLIENT = CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))
       
        DECLARE @ID_Subtema int
        DECLARE @ID_Autor int
        DECLARE @ID_Tema int
        DECLARE @ID_Noticia int

        SELECT @ID_Subtema = ID, @ID_Tema = Fk_Tema FROM dbo.Subtemas WHERE Cod = @inCodSubtema
        SELECT @ID_Autor = ID FROM dbo.Autores WHERE Email = @inEmailAutor

        BEGIN TRANSACTION Transaccion

        --Se inserta la noticia
        INSERT INTO [dbo].[Noticias]
                   ([Titulo]
                   ,[Texto]
                   ,[Fecha]
                   ,[Visitas]
                   ,[Fk_Autor]
                   ,[Fk_Subtema]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
             VALUES
                   (@inTitulo
                   ,@inTexto
                   ,@inFecha
                   ,0
                   ,@ID_Autor
                   ,@ID_Subtema
                   ,@IPCLIENT
                   ,CURRENT_USER
                   ,CURRENT_TIMESTAMP)
       
        SET @ID_Noticia = SCOPE_IDENTITY()

        --Se mandan las notificaciones
        INSERT INTO [dbo].[Envios]
                   ([TextoEnvio]
                   ,[Fecha]
                   ,[Fk_Usuario]
                   ,[Fk_Noticia]
                   ,[PostIn]
                   ,[PostBy]
                   ,[PostDate])
             SELECT dbo.WNFN_TextoEnvio(U.ID, @ID_Autor, @ID_Tema, @ID_Subtema, @ID_Noticia, @inFecha), @inFecha, U.ID, @ID_Noticia, @IPCLIENT, CURRENT_USER, CURRENT_TIMESTAMP
             FROM dbo.Usuarios U, dbo.NotificacionesXUsuarios X, dbo.Notificaciones N
             WHERE U.ID = X.Fk_Usuario
             AND X.Fk_Notificacion = N.ID
             AND (N.Fk_Autor = @ID_Autor
             OR N.Fk_Subtema = @ID_Subtema
             OR N.Fk_Tema = @ID_Tema)


        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN Transaccion
        END
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN Transaccion
        END
    END CATCH
END
GO

Se trabaja en la primer consulta

(1 hora ) De la primera consulta se entiende que "interés general" es la clasificación dada a todas la noticias que han aparecido dando como resultado de la búsqueda para "interés general" las ultimas x noticias que han aparecido en una portada, siendo x la cantidad que se obtiene por parámetro. Al crear la base de datos nos falto agregar una tabla para parámetros, dicha tabla ya ha sido agregada. Falta terminar unos detalles de la consulta y confirmar lo que se cree sobre el "interés general".

Entendiendo el problema y creando base de datos junto a su diagrama

(4 horas)

El dia de hoy se entendió el problema, se creo una base de datos basada en el diagrama del profesor que se hizo en clases el dia de ayer. Para el problema se tomó la desición de no incluir una tabla Visitas debido a que puede existir visitar a articulos o noticias que no dependan de ningún Usuario, de modo que la tabla es inecesaria. Entonces, para ello solo se agrega una atributo a cada noticia que se llama Visitas, que es un contador que inicia en 0 y va a umentando con cada visita por medio de un SP que se programará mas adelante.

La siguiente es una foto del diagrama físico final de la base de datos:

 

 

Relacionar la tabla de Notificaciones con las tablas Autores, Temas y Subtemas no significa que existe una relación recursiva. Cada Autor, Tema y Subtema va a tener una entrada también en la tabla de notificaciones, de manera que los Usuarios puede pedir notificaciones sobre aquellos temas o autores que les interese por medio de la tabla NotificacionesXUsuarios.

Además, se implementa el uso de llaves compuestas para aquellas tablas que son de la forma AxB siendo A y B tablas que tienen una relación n a n. La tabla de NoticiasXPortadas y NotificacionesXUsuarios implementan esta forma de llave primaria.

En la siguiente página se puede apreciar la sintaxis de como se hace uso de PRIMARY KEY(A, B)

http://www.w3schools.com/sql/sql_primarykey.asp

 Adjuntamos abajo el Script de creación de la BD, Tablas y Relaciones:

USE [master]
GO

CREATE DATABASE [III Proyecto - WebNews]
GO

USE [III Proyecto - WebNews]
GO

CREATE TABLE Noticias (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Titulo varchar(200) NOT NULL,
    Texto varchar(MAX) NOT NULL,
    Fecha date NOT NULL,
    Visitas int NOT NULL,
    Fk_Autor int NOT NULL,
    Fk_Subtema int NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE Temas (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Nombre varchar(100) NOT NULL,
    Visitas int NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE Subtemas (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Nombre varchar(100) NOT NULL,
    Fk_Tema int NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE Autores (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Nombre varchar(50) NOT NULL,
    Email varchar(50) NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE Portadas (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Fecha date NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE NoticiasXPortadas (
    Activo bit NOT NULL,
    OrdenDespliegue varchar(1) NULL,
    Fk_Portada int NOT NULL,
    Fk_Noticia int NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL,
    PRIMARY KEY (Fk_Portada, Fk_Noticia)
    )
GO

CREATE TABLE Usuarios (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Nombre varchar(50) NOT NULL,
    Email varchar(50) NOT NULL,
    Activo bit NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE Calificaciones (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Calificacion int NOT NULL,
    Fecha date NOT NULL,
    Fk_Usuario int NOT NULL,
    Fk_Noticia int NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE Comentarios (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    Comentario varchar(500) NOT NULL,
    Fecha date NOT NULL,
    Fk_Usuario int NOT NULL,
    Fk_Noticia int NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE Envios (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    TextoEnvio varchar(MAX) NOT NULL,
    Fecha date NOT NULL,
    Fk_Usuario int NOT NULL,
    Fk_Noticia int NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE Notificaciones (
    ID int PRIMARY KEY IDENTITY(1,1) NOT NULL,
    IDTipoFk int NOT NULL,
    Fk_Tema int NULL,
    Fk_Subtema int NULL,
    Fk_Autor int NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL
    )
GO

CREATE TABLE NotificacionesXUsuarios (
    Fk_Usuario int NOT NULL,
    Fk_Notificacion int NOT NULL,
    PostIn varchar(15) NULL,
    PostBy varchar(50) NULL,
    PostDate datetime NULL,
    PRIMARY KEY (Fk_Usuario, Fk_Notificacion)
    )
GO

ALTER TABLE Noticias
    ADD CONSTRAINT FK_Noticias_Autores
    FOREIGN KEY (Fk_Autor)
    REFERENCES Autores (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Noticias
    ADD CONSTRAINT FK_Noticias_Subtemas
    FOREIGN KEY (Fk_Subtema)
    REFERENCES Subtemas (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Subtemas
    ADD CONSTRAINT FK_Subtemas_Temas
    FOREIGN KEY (Fk_Tema)
    REFERENCES Temas (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE NoticiasXPortadas
    ADD CONSTRAINT FK_NoticiasXPortadas_Portadas
    FOREIGN KEY (Fk_Portada)
    REFERENCES Portadas (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE NoticiasXPortadas
    ADD CONSTRAINT FK_NoticiasXPortadas_Noticias
    FOREIGN KEY (Fk_Noticia)
    REFERENCES Noticias (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Calificaciones
    ADD CONSTRAINT FK_Calificaciones_Usuarios
    FOREIGN KEY (Fk_Usuario)
    REFERENCES Usuarios (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Calificaciones
    ADD CONSTRAINT FK_Calificaciones_Noticias
    FOREIGN KEY (Fk_Noticia)
    REFERENCES Noticias (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Comentarios
    ADD CONSTRAINT FK_Comentarios_Usuarios
    FOREIGN KEY (Fk_Usuario)
    REFERENCES Usuarios (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Comentarios
    ADD CONSTRAINT FK_Comentarios_Noticias
    FOREIGN KEY (Fk_Noticia)
    REFERENCES Noticias (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Envios
    ADD CONSTRAINT FK_Envios_Usuarios
    FOREIGN KEY (Fk_Usuario)
    REFERENCES Usuarios (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Envios
    ADD CONSTRAINT FK_Envios_Noticias
    FOREIGN KEY (Fk_Noticia)
    REFERENCES Noticias (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE Notificaciones
    ADD CONSTRAINT FK_Notificaciones_Temas
    FOREIGN KEY (Fk_Tema)
    REFERENCES Temas (ID)
GO

ALTER TABLE Notificaciones
    ADD CONSTRAINT FK_Notificaciones_Subtemas
    FOREIGN KEY (Fk_Subtema)
    REFERENCES Subtemas (ID)
GO

ALTER TABLE Notificaciones
    ADD CONSTRAINT FK_Notificaciones_Autores
    FOREIGN KEY (Fk_Autor)
    REFERENCES Autores (ID)
GO

ALTER TABLE NotificacionesXUsuarios
    ADD CONSTRAINT FK_NotificacionesXUsuarios_Usuarios
    FOREIGN KEY (Fk_Usuario)
    REFERENCES Usuarios (ID)
    ON DELETE CASCADE;
GO

ALTER TABLE NotificacionesXUsuarios
    ADD CONSTRAINT FK_NotificacionesXUsuarios_Notificaciones
    FOREIGN KEY (Fk_Notificacion)
    REFERENCES Notificaciones (ID)
    ON DELETE CASCADE;
GO