Created
January 4, 2017 22:35
-
-
Save tillig/e7e7619b489cc2fae3fb44c1e14b9471 to your computer and use it in GitHub Desktop.
Simple primary/subtask database integration for TimeSnapper.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [TimeSnapperTaskDb] | |
GO | |
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPaneCount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'task_list' | |
GO | |
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPane1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'task_list' | |
GO | |
ALTER TABLE [dbo].[task] DROP CONSTRAINT [FK_task_project] | |
GO | |
ALTER TABLE [dbo].[task] DROP CONSTRAINT [DF_task_display_order] | |
GO | |
/****** Object: View [dbo].[task_list] Script Date: 4/11/2013 1:42:54 PM ******/ | |
DROP VIEW [dbo].[task_list] | |
GO | |
/****** Object: Table [dbo].[task] Script Date: 4/11/2013 1:42:54 PM ******/ | |
DROP TABLE [dbo].[task] | |
GO | |
/****** Object: Table [dbo].[project] Script Date: 4/11/2013 1:42:54 PM ******/ | |
DROP TABLE [dbo].[project] | |
GO | |
USE [master] | |
GO | |
/****** Object: Database [TimeSnapperTaskDb] Script Date: 4/11/2013 1:42:54 PM ******/ | |
DROP DATABASE [TimeSnapperTaskDb] | |
GO | |
/****** Object: Database [TimeSnapperTaskDb] Script Date: 4/11/2013 1:42:54 PM ******/ | |
CREATE DATABASE [TimeSnapperTaskDb] | |
CONTAINMENT = NONE | |
ON PRIMARY | |
( NAME = N'TimeSnapperTaskDb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\TimeSnapperTaskDb.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) | |
LOG ON | |
( NAME = N'TimeSnapperTaskDb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\TimeSnapperTaskDb_1.ldf' , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET COMPATIBILITY_LEVEL = 100 | |
GO | |
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) | |
begin | |
EXEC [TimeSnapperTaskDb].[dbo].[sp_fulltext_database] @action = 'enable' | |
end | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET ANSI_NULL_DEFAULT OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET ANSI_NULLS OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET ANSI_PADDING OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET ANSI_WARNINGS OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET ARITHABORT OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET AUTO_CLOSE ON | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET AUTO_CREATE_STATISTICS ON | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET AUTO_SHRINK OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET AUTO_UPDATE_STATISTICS ON | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET CURSOR_CLOSE_ON_COMMIT OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET CURSOR_DEFAULT GLOBAL | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET CONCAT_NULL_YIELDS_NULL OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET NUMERIC_ROUNDABORT OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET QUOTED_IDENTIFIER OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET RECURSIVE_TRIGGERS OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET DISABLE_BROKER | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET DATE_CORRELATION_OPTIMIZATION OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET TRUSTWORTHY OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET ALLOW_SNAPSHOT_ISOLATION OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET PARAMETERIZATION SIMPLE | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET READ_COMMITTED_SNAPSHOT OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET HONOR_BROKER_PRIORITY OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET RECOVERY SIMPLE | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET MULTI_USER | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET PAGE_VERIFY CHECKSUM | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET DB_CHAINING OFF | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET TARGET_RECOVERY_TIME = 0 SECONDS | |
GO | |
USE [TimeSnapperTaskDb] | |
GO | |
/****** Object: Table [dbo].[project] Script Date: 4/11/2013 1:42:54 PM ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[project]( | |
[epm] [int] NOT NULL, | |
[name] [nvarchar](50) NOT NULL, | |
CONSTRAINT [PK_project] PRIMARY KEY CLUSTERED | |
( | |
[epm] 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 | |
/****** Object: Table [dbo].[task] Script Date: 4/11/2013 1:42:54 PM ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[task]( | |
[epm] [int] NOT NULL, | |
[name] [nvarchar](50) NOT NULL, | |
[display_order] [int] NOT NULL | |
) ON [PRIMARY] | |
GO | |
/****** Object: View [dbo].[task_list] Script Date: 4/11/2013 1:42:54 PM ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE VIEW [dbo].[task_list] | |
AS | |
SELECT TOP (100) PERCENT dbo.project.epm, dbo.project.name AS project, dbo.task.name AS task, dbo.task.display_order | |
FROM dbo.project INNER JOIN | |
dbo.task ON dbo.project.epm = dbo.task.epm | |
GO | |
INSERT [dbo].[project] ([epm], [name]) VALUES (1000, N'Project #1') | |
INSERT [dbo].[task] ([epm], [name], [display_order]) VALUES (1000, N'Plan', 1) | |
INSERT [dbo].[task] ([epm], [name], [display_order]) VALUES (1000, N'Build', 2) | |
INSERT [dbo].[task] ([epm], [name], [display_order]) VALUES (1000, N'Deploy', 3) | |
ALTER TABLE [dbo].[task] ADD CONSTRAINT [DF_task_display_order] DEFAULT ((0)) FOR [display_order] | |
GO | |
ALTER TABLE [dbo].[task] WITH CHECK ADD CONSTRAINT [FK_task_project] FOREIGN KEY([epm]) | |
REFERENCES [dbo].[project] ([epm]) | |
ON UPDATE CASCADE | |
ON DELETE CASCADE | |
GO | |
ALTER TABLE [dbo].[task] CHECK CONSTRAINT [FK_task_project] | |
GO | |
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00] | |
Begin DesignProperties = | |
Begin PaneConfigurations = | |
Begin PaneConfiguration = 0 | |
NumPanes = 4 | |
Configuration = "(H (1[41] 4[20] 2[18] 3) )" | |
End | |
Begin PaneConfiguration = 1 | |
NumPanes = 3 | |
Configuration = "(H (1 [50] 4 [25] 3))" | |
End | |
Begin PaneConfiguration = 2 | |
NumPanes = 3 | |
Configuration = "(H (1 [50] 2 [25] 3))" | |
End | |
Begin PaneConfiguration = 3 | |
NumPanes = 3 | |
Configuration = "(H (4 [30] 2 [40] 3))" | |
End | |
Begin PaneConfiguration = 4 | |
NumPanes = 2 | |
Configuration = "(H (1 [56] 3))" | |
End | |
Begin PaneConfiguration = 5 | |
NumPanes = 2 | |
Configuration = "(H (2 [66] 3))" | |
End | |
Begin PaneConfiguration = 6 | |
NumPanes = 2 | |
Configuration = "(H (4 [50] 3))" | |
End | |
Begin PaneConfiguration = 7 | |
NumPanes = 1 | |
Configuration = "(V (3))" | |
End | |
Begin PaneConfiguration = 8 | |
NumPanes = 3 | |
Configuration = "(H (1[56] 4[18] 2) )" | |
End | |
Begin PaneConfiguration = 9 | |
NumPanes = 2 | |
Configuration = "(H (1 [75] 4))" | |
End | |
Begin PaneConfiguration = 10 | |
NumPanes = 2 | |
Configuration = "(H (1[66] 2) )" | |
End | |
Begin PaneConfiguration = 11 | |
NumPanes = 2 | |
Configuration = "(H (4 [60] 2))" | |
End | |
Begin PaneConfiguration = 12 | |
NumPanes = 1 | |
Configuration = "(H (1) )" | |
End | |
Begin PaneConfiguration = 13 | |
NumPanes = 1 | |
Configuration = "(V (4))" | |
End | |
Begin PaneConfiguration = 14 | |
NumPanes = 1 | |
Configuration = "(V (2))" | |
End | |
ActivePaneConfig = 0 | |
End | |
Begin DiagramPane = | |
Begin Origin = | |
Top = 0 | |
Left = 0 | |
End | |
Begin Tables = | |
Begin Table = "project" | |
Begin Extent = | |
Top = 6 | |
Left = 38 | |
Bottom = 101 | |
Right = 208 | |
End | |
DisplayFlags = 280 | |
TopColumn = 0 | |
End | |
Begin Table = "task" | |
Begin Extent = | |
Top = 6 | |
Left = 246 | |
Bottom = 118 | |
Right = 416 | |
End | |
DisplayFlags = 280 | |
TopColumn = 0 | |
End | |
End | |
End | |
Begin SQLPane = | |
End | |
Begin DataPane = | |
Begin ParameterDefaults = "" | |
End | |
Begin ColumnWidths = 9 | |
Width = 284 | |
Width = 1500 | |
Width = 1500 | |
Width = 1500 | |
Width = 1500 | |
Width = 1500 | |
Width = 1500 | |
Width = 1500 | |
Width = 1500 | |
End | |
End | |
Begin CriteriaPane = | |
Begin ColumnWidths = 11 | |
Column = 1440 | |
Alias = 900 | |
Table = 1170 | |
Output = 720 | |
Append = 1400 | |
NewValue = 1170 | |
SortType = 1350 | |
SortOrder = 1410 | |
GroupBy = 1350 | |
Filter = 1350 | |
Or = 1350 | |
Or = 1350 | |
Or = 1350 | |
End | |
End | |
End | |
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'task_list' | |
GO | |
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'task_list' | |
GO | |
USE [master] | |
GO | |
ALTER DATABASE [TimeSnapperTaskDb] SET READ_WRITE | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?xml version="1.0" encoding="utf-8" ?> | |
<root> | |
<!-- | |
Here you can integrate the TimeSnapper's Flags dialog with you own issue/feature tracking system. | |
You must specify a connection string so TimeSnapper knows where to connect to and | |
a few queries that will be executed against that connectionstring. | |
NB: We use OleDB to connect to the database. Here is a typical connection string for OleDB: | |
"Provider=sqloledb;data source=server;Initial catalog=dbname;integrated security=sspi" | |
You have access to several variables from TimeSnapper that can be included in your | |
SQL code: | |
{ntuser}: The NT username of the logged on user | |
{domain}: The domain name of the logged on user | |
{flagType}: The type of flag | |
{flagDate}: The date/time of the flag | |
{textField1}: The value currently in textbox 1 in the flag dialog | |
{textField2}: The value currently in textbox 2 in the flag dialog | |
{textField3}: The value currently in textbox 3 in the flag dialog | |
{textField4}: The value currently in textbox 4 in the flag dialog | |
{textField5}: The value currently in textbox 5 in the flag dialog | |
{textField6}: The value currently in textbox 6 in the flag dialog | |
{comment}: The value currently in comment textbox | |
Your query can call a stored procedure that returns a result set | |
(if you're using SQLServer) like this: | |
exec spproc '{textField1}', '{textField2}', '{textField3}' | |
Or you can just use a standard SQL like this: | |
SELECT * FROM projects | |
SELECT * FROM tasks WHERE projectname = '{textField1}' | |
SELECT * FROM subtasks WHERE taskname = '{textField2}' | |
--> | |
<options> | |
<connectionString>Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=TimeSnapperTaskDb;Data Source=(LocalDb)\MSSQLLocalDB;Initial File Name="";Server SPN=""</connectionString> | |
<!-- What format should we send date fields to the database, depends on the database type --> | |
<dateFormat>yyyy-MM-dd</dateFormat> | |
<textFields> | |
<textField num="1" type="string" caption="Project:" textField1="name" textField3="epm"> | |
SELECT epm, name | |
FROM project | |
ORDER BY epm | |
</textField> | |
<textField num="2" type="string" caption="Task:" textField2="name"> | |
SELECT name | |
FROM task | |
WHERE epm = {textField3} | |
ORDER BY display_order | |
</textField> | |
<textField num="3" type="string" caption="Project" enabled='false' /> | |
<textField num="4" type="string" caption="Task" enabled='false' /> | |
<textField num="5" type="string" caption="Tags" enabled='false' /> | |
<textField num="6" type="string" caption="Custom" enabled='false' /> | |
</textFields> | |
</options> | |
</root> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment