All geek questions in one place

How to simulate the following data structure?

Sorry, this question is a bit abstract and, as such, is difficult to define, so I will probably need to edit the question several times to clarify:

I have a configuration file that I need to parse, where each corresponding line contains one of the following formats:

FieldName = Value
FieldName(Index) = Value
FieldName(Index1, Index2) = Value
FieldName(Index1, Index2, ...IndexN) = Value

For instance:

Field0 = 0
Field1(0, 0) = 0.01
Field1(0, 1) = 0.02
Field1(1, 0) = 0.03
Field1(1, 1) = 0.04
Field1(2, 0) = ADF0102BC5
Field1(2, 1) = ADF0102BC6
Field2(0, 0) = 0
Field2(0, 1) = 2
Field3(1) = 5
Field3(2) = 7
Field3(3) = 9
Field4(0, 0, 1) = 64.75
Field4(0, 1, 0) = 65.25
Field4(1, 0, 0) = 72.25

The corresponding lines are simple enough to parse from a file using regular expressions, and I have already processed this bit. I had a problem with how to model data in a database, so that when a new index falls into the scope of a field, it can be automatically added without the need to add new columns to the table.

The Name field is always Varchar with a maximum length of 50

, , .

( ) . , .

, Field1, , 1 (0, 0) 1 (0, 0, 0). Field1 2 , Field1 2 .

, , , .

- "" Field1 (0, 0) "FieldName", , ?

, , :

Table Fields(
    FieldId Integer Identity(1, 1) Primary Key,
    FieldName VarChar(50)
)

Table FieldValues(
    FieldId Integer Constraint FK_FV_FID Foreign Key References Fields(FieldId)
    Index1 Integer
    Index2 Integer
    Index3 Integer
    Index4 Integer
    Value  Varchar(50)
)

, - , , .

, , i.e.

Field1
------
0, 0 = 0.01
0, 1 = 0.02
1, 0 = 0.03
1, 1 = 0.04
2, 0 = ADF0102BC5
2, 1 = ADF0102BC6


Field1 Where Index1 = 0
-----------------------
0, 0 = 0.01
0, 1 = 0.02


Field1 Where Index 2 = 1
------------------------
0, 1 = 0.02
1, 1 = 0.04
2, 1 = ADF0102BC6


Field1 Where Index1 = 0 And Index2 = 1
--------------------------------------
0, 1 = 0.02

, .

+4
3

, . , IndexPostion / IndexValue. , .

. , .

" ", .

"SO_FieldIndexValue".

, , IndexPosition/IndexPositionValue.

Schema design

    IF OBJECT_ID('SO_FieldIndexPositionValue') IS NOT NULL 
        DROP TABLE SO_FieldIndexPositionValue
    IF OBJECT_ID('SO_FieldIndexValue') IS NOT NULL 
        DROP TABLE SO_FieldIndexValue
    IF OBJECT_ID('SO_IndexPositionValue') IS NOT NULL 
        DROP TABLE SO_IndexPositionValue

    CREATE TABLE SO_FieldIndexValue
        (
          FIV_ID        BIGINT NOT NULL IDENTITY
            CONSTRAINT XPK_SO_FieldIndexValue PRIMARY KEY NONCLUSTERED
          ,FieldName    NVARCHAR(50)NOT NULL
          ,FieldIndex   NVARCHAR(10) NOT NULL
          ,FieldValue   NVARCHAR(500) NULL
        )
    CREATE UNIQUE CLUSTERED INDEX CIDX_SO_FieldIndexValue
    ON SO_FieldIndexValue(FIV_ID ASC,FieldName ASC,FieldIndex ASC)
    CREATE NONCLUSTERED INDEX NCIDX_SO_FieldIndexValue
    ON SO_FieldIndexValue (FIV_ID,FieldName) 
    INCLUDE (FieldIndex,FieldValue)

    CREATE TABLE SO_IndexPositionValue
        (
            IPV_ID              BIGINT  NOT NULL IDENTITY
                CONSTRAINT XPK_SO_IndexPositionValue PRIMARY KEY NONCLUSTERED
            ,IndexName          SYSNAME NOT NULL
            ,IndexPosition      INT     NOT NULL
            ,IndexPositionValue BIGINT  NOT NULL
        )
    CREATE UNIQUE CLUSTERED INDEX CIDX_SO_IndexPositionValue 
    ON SO_IndexPositionValue(IPV_ID ASC,IndexPosition ASC, IndexPositionValue ASC)

    CREATE TABLE SO_FieldIndexPositionValue
        (
          FIPV_ID       BIGINT NOT NULL IDENTITY
                CONSTRAINT XPK_SO_FieldIndexPositionValue PRIMARY KEY NONCLUSTERED
          ,FIV_ID           BIGINT NOT NULL REFERENCES SO_FieldIndexValue (FIV_ID)
          ,IPV_ID       BIGINT NOT NULL REFERENCES SO_IndexPositionValue (IPV_ID)
        )
    CREATE CLUSTERED INDEX CIDX_SO_FieldIndexPositionValue 
    ON SO_FieldIndexPositionValue(FIPV_ID ASC,FIV_ID ASC,IPV_ID ASC)

SQL API, , API.

API . , , .

    IF object_id('pr_FiledValueInsert','p') IS NOT NULL
        DROP PROCEDURE pr_FiledValueInsert
    GO
    CREATE PROCEDURE pr_FiledValueInsert
    (
        @FieldIndexValue    NVARCHAR(MAX)
        ,@FieldValue        NVARCHAR(MAX)=NULL
    )
    AS
    BEGIN
    SET NOCOUNT ON
    BEGIN TRY
    BEGIN TRAN
            DECLARE @OriginalFiledIndex NVARCHAR(MAX)=@FieldIndexValue
            DECLARE @FieldName              sysname=''
                    ,@FIV_ID                BIGINT
                    ,@FieldIndex            sysname
                    ,@IndexName             sysname
                    ,@IndexPosition         BIGINT
                    ,@IndexPositionValue    BIGINT
                    ,@IPV_ID                BIGINT
                    ,@FIPV_ID               BIGINT
                    ,@CharIndex1            BIGINT
                    ,@CharIndex2            BIGINT
                    ,@StrLen                BIGINT
                    ,@StartPos              BIGINT
                    ,@EndPos                BIGINT

            SET @CharIndex1 = CHARINDEX('(',@OriginalFiledIndex)
            SET @StrLen     = LEN(@OriginalFiledIndex)
            SET @CharIndex2 = CHARINDEX(')',@OriginalFiledIndex)
            SET @FieldName  = RTRIM(LTRIM(SUBSTRING(@OriginalFiledIndex,1,@CharIndex1-1)))
            SET @FieldIndex = RTRIM(LTRIM(SUBSTRING(@OriginalFiledIndex,@CharIndex1+1,@StrLen-@CharIndex1-1)))


            --Insert FieldIndexValue and Get @FIV_ID
            SELECT @FIV_ID = FIV_ID 
            FROM SO_FieldIndexValue 
            WHERE FieldName=@FieldName
            AND FieldIndex=@FieldIndex
            IF @FIV_ID IS NULL
            BEGIN
                INSERT INTO SO_FieldIndexValue ( FieldName,FieldIndex,FieldValue )
                SELECT @FieldName,@FieldIndex,@FieldValue
                SELECT @FIV_ID = SCOPE_IDENTITY()
            END
            ELSE
            BEGIN
                RAISERROR('Filed and Index Combination already Exists',16,1)
            END


            --Find the First IndexPosition and IndexPositionValue and Get @IPV_ID
            SELECT @StartPos=CHARINDEX('(',@OriginalFiledIndex,1)+1
            SELECT @EndPos = CASE   WHEN CHARINDEX(',',@OriginalFiledIndex,@StartPos)<>0
                                    THEN  CHARINDEX(',',@OriginalFiledIndex,@StartPos)- @StartPos
                                    ELSE CHARINDEX(')',@OriginalFiledIndex,@StartPos) - @StartPos
                                END
            SELECT @IndexPosition = 1
            SELECT @IndexPositionValue = SUBSTRING(@OriginalFiledIndex,@StartPos,@EndPos)
            SELECT @IndexName = 'Index'+CAST(@IndexPosition AS Sysname)

            --Insert IndexPositionvalue
            SELECT @IPV_ID = IPV_ID
            FROM SO_IndexPositionValue
            WHERE IndexPosition=@IndexPosition
            AND IndexPositionValue = @IndexPositionValue
            IF @IPV_ID IS NULL
            BEGIN
                INSERT SO_IndexPositionValue
                        ( IndexName ,
                          IndexPosition ,
                          IndexPositionValue
                        )
                SELECT @IndexName,@IndexPosition,@IndexPositionValue
                SET @IPV_ID = SCOPE_IDENTITY()          
            END

            --Insert the First FieldIndexPositionValue
            IF NOT EXISTS(
                            SELECT TOP(1) 1 
                            FROM SO_FieldIndexPositionValue
                            WHERE FIV_ID = @FIV_ID
                            AND IPV_ID = @IPV_ID
                        )
            BEGIN
                INSERT SO_FieldIndexPositionValue( FIV_ID, IPV_ID )
                SELECT @FIV_ID,@IPV_ID
            END

            --If More than One Index exist, process remining indexpositions
            WHILE @StrLen>@StartPos+@EndPos
            BEGIN           
                SET @StartPos = @StartPos+@EndPos+1
                SET @EndPos = CASE WHEN CHARINDEX(',',@OriginalFiledIndex,@StartPos)<>0
                                    THEN  CHARINDEX(',',@OriginalFiledIndex,@StartPos)- @StartPos
                                    ELSE CHARINDEX(')',@OriginalFiledIndex,@StartPos) - @StartPos
                                END

                SELECT @IndexPosition = @IndexPosition+1
                SELECT @IndexPositionValue = SUBSTRING(@OriginalFiledIndex,@StartPos,@EndPos)
                SELECT @IndexName = 'Index'+CAST(@IndexPosition AS Sysname)

                --Insert IndexPositionvalue
                SET @IPV_ID = NULL
                SELECT @IPV_ID = IPV_ID
                FROM SO_IndexPositionValue
                WHERE IndexPosition=@IndexPosition
                AND IndexPositionValue = @IndexPositionValue
                IF @IPV_ID IS NULL
                BEGIN
                    INSERT SO_IndexPositionValue
                            ( IndexName ,
                              IndexPosition ,
                              IndexPositionValue
                            )
                    SELECT @IndexName,@IndexPosition,@IndexPositionValue
                    SET @IPV_ID = SCOPE_IDENTITY()
                END

                --Insert FieldIndexPositionValue
                IF NOT EXISTS(
                                SELECT TOP(1) 1 
                                FROM SO_FieldIndexPositionValue
                                WHERE FIV_ID = @FIV_ID
                                AND IPV_ID = @IPV_ID
                            )
                BEGIN
                    INSERT SO_FieldIndexPositionValue( FIV_ID, IPV_ID )
                    SELECT @FIV_ID,@IPV_ID
                END
            END
    COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT ERROR_MESSAGE()
    END CATCH
    SET NOCOUNT OFF
    END
    GO


    EXECUTE pr_FiledValueInsert 'FIELD1(0,1,0)',101
    EXECUTE pr_FiledValueInsert 'FIELD1(0,1,2)','ABCDEF'
    EXECUTE pr_FiledValueInsert 'FIELD1(1,0,1)','hello1'

    EXECUTE pr_FiledValueInsert 'FIELD2(1,0,0)',102
    EXECUTE pr_FiledValueInsert 'FIELD2(1,1,0)','hey2'
    EXECUTE pr_FiledValueInsert 'FIELD2(1,0,1)','hello2'

1

    SELECT FieldName,FieldIndex,FieldValue 
    FROM dbo.SO_FieldIndexValue
    WHERE FieldName = 'Field1'

1

SampleResult1

2

    SELECT FieldName,FieldIndex AS CompeleteIndex,IndexPosition,IndexPositionValue,FieldValue
    FROM SO_FieldIndexPositionValue fipv
    JOIN dbo.SO_IndexPositionValue ipv
        ON ipv.IPV_ID=fipv.IPV_ID
    JOIN dbo.SO_FieldIndexValue fiv
        ON fiv.FIV_ID=fipv.FIV_ID
    WHERE
    (IndexPosition=2 AND IndexPositionValue=1)
    AND FieldName = 'Field1'

2

SampleResult2

+2

, , :

field
-------
field_id
name

index
---------
index_id
field_id
position
value

field_value
------------
field_id
index_id
value
+1

, SQL, - , , , .

, :

Row_Id, _,

Row_Id, Index_Position, Index_Value

, , .

select r.Row_Id, r.Value from Row r
join Index i1 on r.Row_Id = i1.Row_Id
join Index i2 on r.Row_Id = i2.Row_Id
join Index i3 on r.Row_Id = i3.Row_Id
where
i1.Index_Position = 1 and i1.Index_Value = '3' and
i2.Index_Position = 2 and i2.Index_Value = '7' and
i3.Index_Position = 3 and i3.Index_Value = '42' and

EDIT: . (, FieldName "FieldName (0,1)" ) - ( - ? ?).

EDIT 2: , . Row. ( , ):

Row_Id, _,

1, "Field0", "0"

2, "Field1", "0.01"

3, "Field1", "0.02"

Index

Row_Id, Index_Position, Index_Value

2, 1, 0

2, 2, 0

3, 1, 0

3, 2, 1

+1
source

Source: https://habr.com/ru/post/1530986/

More articles:


All Articles