Come ruotare uno schema di entity framework-attributo di entity framework MySQL

Ho bisogno di progettare tabelle che memorizzino tutti i metadati dei file (ad es. Nome file, autore, titolo, data di creazione) e metadati personalizzati (che sono stati aggiunti ai file dagli utenti, ad esempio CustUseBy, CustSendBy). Il numero di campi di metadati personalizzati non può essere impostato in anticipo. In effetti, l’unico modo per determinare quali e quanti tag personalizzati sono stati aggiunti ai file è esaminare ciò che esiste nelle tabelle.

Per memorizzarlo, ho creato una tabella di base (con tutti i metadati comuni dei file), una tabella Attributes (con attributi aggiuntivi facoltativi che possono essere impostati sui file) e una tabella FileAttributes (che assegna un valore a un attributo per un file ).

 CREAT TABLE FileBase ( id VARCHAR(32) PRIMARY KEY, name VARCHAR(255) UNIQUE NOT NULL, title VARCHAR(255), author VARCHAR(255), created DATETIME NOT NULL, ) Engine=InnoDB; CREATE TABLE Attributes ( id VARCHAR(32) PRIMARY KEY, name VARCHAR(255) NOT NULL, type VARCHAR(255) NOT NULL ) Engine=InnoDB; CREATE TABLE FileAttributes ( sNo INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, fileId VARCHAR(32) NOT NULL, attributeId VARCHAR(32) NOT NULL, attributeValue VARCHAR(255) NOT NULL, FOREIGN KEY fileId REFERENCES FileBase (id), FOREIGN KEY attributeId REFERENCES Attributes (id) ) Engine=InnoDB; 

Dati di esempio:

 INSERT INTO FileBase (id, title, author, name, created) VALUES ('F001', 'Dox', 'vinay', 'story.dox', '2009/01/02 15:04:05'), ('F002', 'Excel', 'Ajay', 'data.xls', '2009/02/03 01:02:03'); INSERT INTO Attributes (id, name, type) VALUES ('A001', 'CustomeAttt1', 'Varchar(40)'), ('A002', 'CustomUseDate', 'Datetime'); INSERT INTO FileAttributes (fileId, attributeId, attributeValue) VALUES ('F001', 'A001', 'Akash'), ('F001', 'A002', '2009/03/02'); 

Ora il problema è che voglio mostrare i dati in un modo come questo:

 FileId, Title, Author, CustomAttri1, CustomAttr2, ... F001 Dox vinay Akash 2009/03/02 ... F002 Excel Ajay 

Quale query genererà questo risultato?

    La domanda menziona MySQL, e infatti questo DBMS ha una funzione speciale per questo tipo di problema: GROUP_CONCAT(expr) . Dai un’occhiata al manuale di riferimento MySQL su group-by-functions . La funzione è stata aggiunta in MySQL versione 4.1. GROUP BY FileID nella query.

    Non sono molto sicuro di come vuoi che il risultato appaia. Se vuoi che ogni attributo sia elencato per ogni object (anche se non è impostato), sarà più difficile. Tuttavia, questo è il mio suggerimento su come farlo:

     SELECT bt.FileID, Title, Author, GROUP_CONCAT( CONCAT_WS(':', at.AttributeName, at.AttributeType, avt.AttributeValue) ORDER BY at.AttributeName SEPARATOR ', ') FROM BaseTable bt JOIN AttributeValueTable avt ON avt.FileID=bt.FileID JOIN AttributeTable at ON avt.AttributeId=at.AttributeId GROUP BY bt.FileID; 

    Questo ti dà tutti gli attributi nello stesso ordine, che potrebbe essere utile. L’output sarà simile al seguente:

     'F001', 'Dox', 'vinay', 'CustomAttr1:varchar(40):Akash, CustomUseDate:Datetime:2009/03/02' 

    In questo modo hai solo bisogno di una singola query DB e l’output è facile da analizzare. Se si desidera archiviare gli attributi come datetime reale ecc. Nel DB, è necessario utilizzare l’SQL dinamico, ma non lo mancherò e memorizzerò i valori in varchars.

    La forma generale di tale query sarebbe

     SELECT file.*, attr1.value AS 'Attribute 1 Name', attr2.value AS 'Attribute 2 Name', ... FROM file LEFT JOIN attr AS attr1 ON(file.FileId=attr1.FileId and attr1.AttributeId=1) LEFT JOIN attr AS attr2 ON(file.FileId=attr2.FileId and attr2.AttributeId=2) ... 

    Quindi è necessario creare dynamicmente la query dagli attributi necessari. In pseudocodice php-ish

     $cols="file"; $joins=""; $rows=$db->GetAll("select * from Attributes"); foreach($rows as $idx=>$row) { $alias="attr{$idx}"; $cols.=", {$alias}.value as '".mysql_escape_string($row['AttributeName'])."'"; $joins.="LEFT JOIN attr as {$alias} on ". "(file.FileId={$alias}.FileId and ". "{$alias}.AttributeId={$row['AttributeId']}) "; } $pivotsql="select $cols from file $joins"; 

    Se stai cercando qualcosa di più utilizzabile (e collegabile) rispetto a un risultato di gruppo, prova questa soluzione qui sotto. Ho creato alcune tabelle molto simili al tuo esempio per renderlo sensato.

    Funziona quando:

    • Vuoi una soluzione SQL pura (senza codice, senza loop)
    • Hai un insieme prevedibile di attributi (ad esempio non dinamico)
    • Stai OK aggiornando la query quando è necessario aggiungere nuovi tipi di attributi
    • Preferiresti un risultato che può essere UNITO, UNION o nidificato come sottoselezione

    Tabella A (file)

     FileID, Title, Author, CreatedOn 

    Tabella B (attributi)

     AttrID, AttrName, AttrType [not sure how you use type...] 

    Tabella C (File_Attributes)

     FileID, AttrID, AttrValue 

    Una query tradizionale estrarrà molte righe ridondanti:

     SELECT * FROM Files F LEFT JOIN Files_Attributes FA USING (FileID) LEFT JOIN Attributes A USING (AttributeID); 
     AttrID FileID Titolo Autore CreatedOn AttrValue AttrName AttrType
     50 1 TestFile Joe 2011-01-01 true ReadOnly bool
     60 1 TestFile Joe 2011-01-01 xls FileFormat text
     70 1 TestFile Joe 2011-01-01 false Bool privato
     80 1 TestFile Joe 2011-01-01 2011-10-03 Data LastModified
     60 2 LongNovel Mary 2011-02-01 json FileFormat text
     80 2 LongNovel Mary 2011-02-01 2011-10-04 Data LastModified
     70 2 LongNovel Mary 2011-02-01 true Bool privato
     50 2 LongNovel Mary 2011-02-01 true ReadOnly bool
     50 3 ShortStory Susan 2011-03-01 false ReadOnly bool
     60 3 ShortStory Susan 2011-03-01 testo FileFormat ascii
     70 3 ShortStory Susan 2011-03-01 false Bool privato
     80 3 ShortStory Susan 2011-03-01 2011-10-01 Data LastModified
     50 4 ProfitLoss Bill 2011-04-01 false ReadOnly bool
     70 4 ProfitLoss Bill 2011-04-01 true Bool privato
     80 4 ProfitLoss Bill 2011-04-01 2011-10-02 Data Ultima Data
     60 4 ProfitLoss Bill 2011-04-01 testo FileFormat testo
     50 5 MonthlyBudget George 2011-05-01 false ReadOnly bool
     60 5 MonthlyBudget George 2011-05-01 testo FileFormat binario
     70 5 MonthlyBudget George 2011-05-01 false Bool privato
     80 5 MonthlyBudget George 2011-05-01 2011-10-20 Data LastModified
    

    Questa query coalescente (approccio utilizzando MAX) può unire le righe:

     SELECT F.*, MAX( IF(A.AttrName = 'ReadOnly', FA.AttrValue, NULL) ) as 'ReadOnly', MAX( IF(A.AttrName = 'FileFormat', FA.AttrValue, NULL) ) as 'FileFormat', MAX( IF(A.AttrName = 'Private', FA.AttrValue, NULL) ) as 'Private', MAX( IF(A.AttrName = 'LastModified', FA.AttrValue, NULL) ) as 'LastModified' FROM Files F LEFT JOIN Files_Attributes FA USING (FileID) LEFT JOIN Attributes A USING (AttributeID) GROUP BY F.FileID; 
     FileID Titolo Autore CreatedOn ReadOnly FileFormat Private LastModified
     1 TestFile Joe 2011-01-01 true xls false 2011-10-03
     2 LongNovel Mary 2011-02-01 true json true 2011-10-04
     3 ShortStory Susan 2011-03-01 false ascii false 2011-10-01
     4 ProfitLoss Bill 2011-04-01 false text vero 2011-10-02
     5 MonthlyBudget George 2011-05-01 false binary false 2011-10-20
    

    Questo è il problema standard “righe su colonne” in SQL.

    È fatto più facilmente al di fuori di SQL.

    Nell’applicazione, fai quanto segue:

    1. Definire una class semplice per contenere il file, gli attributi di sistema e una raccolta di attributi utente. Una lista è una buona scelta per questa raccolta di attributi del cliente. Chiamiamo questa class FileDescription.

    2. Esegui un semplice join tra il file e tutti gli attributi del cliente per il file.

    3. Scrivere un loop per assemblare FileDescriptions dal risultato della query.

      • Scarica la prima riga, crea una descrizione file e imposta il primo attributo cliente.

      • Mentre ci sono più righe da recuperare:

        • Scarica una riga
        • Se il nome del file di questa riga non corrisponde alla descrizione del file che stiamo costruendo: terminare la costruzione di una descrizione file; aggiungilo a un risultato Raccolta di descrizioni di file; creare una nuova descrizione file con il nome e il primo attributo del cliente.
        • Se il nome del file di questa riga corrisponde alla descrizione del file che stiamo costruendo: aggiungi un altro attributo del cliente alla descrizione file corrente

    Ho sperimentato diverse risposte e la risposta di Methai è stata la più comoda per me. Il mio progetto attuale, sebbene usi Doctrine con MySQL, ha un bel po ‘di tabelle libere.

    Quanto segue è il risultato della mia esperienza con la soluzione Methai:

    creare una tabella delle quadro

     DROP TABLE IF EXISTS entity; CREATE TABLE entity ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255), author VARCHAR(255), createdOn DATETIME NOT NULL ) Engine = InnoDB; 

    creare la tabella degli attributi

     DROP TABLE IF EXISTS attribute; CREATE TABLE attribute ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, type VARCHAR(255) NOT NULL ) Engine = InnoDB; 

    creare tabella attributevalue

     DROP TABLE IF EXISTS attributevalue; CREATE TABLE attributevalue ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, value VARCHAR(255) NOT NULL, attribute_id INT UNSIGNED NOT NULL, FOREIGN KEY(attribute_id) REFERENCES attribute(id) ) Engine = InnoDB; 

    crea una tabella di join entity_attributevalue

     DROP TABLE IF EXISTS entity_attributevalue; CREATE TABLE entity_attributevalue ( entity_id INT UNSIGNED NOT NULL, attributevalue_id INT UNSIGNED NOT NULL, FOREIGN KEY(entity_id) REFERENCES entity(id), FOREIGN KEY(attributevalue_id) REFERENCES attributevalue(id) ) Engine = InnoDB; 

    popola la tabella delle entity framework

     INSERT INTO entity (title, author, createdOn) VALUES ('TestFile', 'Joe', '2011-01-01'), ('LongNovel', 'Mary', '2011-02-01'), ('ShortStory', 'Susan', '2011-03-01'), ('ProfitLoss', 'Bill', '2011-04-01'), ('MonthlyBudget', 'George', '2011-05-01'), ('Paper', 'Jane', '2012-04-01'), ('Essay', 'John', '2012-03-01'), ('Article', 'Dan', '2012-12-01'); 

    popola la tabella degli attributi

     INSERT INTO attribute (name, type) VALUES ('ReadOnly', 'bool'), ('FileFormat', 'text'), ('Private', 'bool'), ('LastModified', 'date'); 

    popola la tabella attributevalue

     INSERT INTO attributevalue (value, attribute_id) VALUES ('true', '1'), ('xls', '2'), ('false', '3'), ('2011-10-03', '4'), ('true', '1'), ('json', '2'), ('true', '3'), ('2011-10-04', '4'), ('false', '1'), ('ascii', '2'), ('false', '3'), ('2011-10-01', '4'), ('false', '1'), ('text', '2'), ('true', '3'), ('2011-10-02', '4'), ('false', '1'), ('binary', '2'), ('false', '3'), ('2011-10-20', '4'), ('doc', '2'), ('false', '3'), ('2011-10-20', '4'), ('rtf', '2'), ('2011-10-20', '4'); 

    popola la tabella entity_attributevalue

     INSERT INTO entity_attributevalue (entity_id, attributevalue_id) VALUES ('1', '1'), ('1', '2'), ('1', '3'), ('1', '4'), ('2', '5'), ('2', '6'), ('2', '7'), ('2', '8'), ('3', '9'), ('3', '10'), ('3', '11'), ('3', '12'), ('4', '13'), ('4', '14'), ('4', '15'), ('4', '16'), ('5', '17'), ('5', '18'), ('5', '19'), ('5', '20'), ('6', '21'), ('6', '22'), ('6', '23'), ('7', '24'), ('7', '25'); 

    Mostrando tutti i record

     SELECT * FROM `entity` e LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id LEFT JOIN `attribute` a ON av.attribute_id = a.id; 
     id title author createdOn entity_id attributevalue_id id valore attribute_id id nome tipo
     1 TestFile Joe 2011-01-01 00:00:00 1 1 1 true 1 1 ReadOnly bool
     1 TestFile Joe 2011-01-01 00:00:00 1 2 2 xls 2 2 FileFormat text
     1 TestFile Joe 2011-01-01 00:00:00 1 3 3 false 3 3 Bool privato
     1 TestFile Joe 2011-01-01 00:00:00 1 4 4 2011-10-03 4 4 LastModified date
     2 LongNovel Mary 2011-02-01 00:00:00 2 5 5 true 1 1 ReadOnly bool
     2 LongNovel Mary 2011-02-01 00:00:00 2 6 6 json 2 2 FileFormat text
     2 LongNovel Mary 2011-02-01 00:00:00 2 7 7 true 3 3 Bool privato
     2 LongNovel Mary 2011-02-01 00:00:00 2 8 8 2011-10-04 4 4 LastModified date
     3 ShortStory Susan 2011-03-01 00:00:00 3 9 9 false 1 1 ReadOnly bool
     3 ShortStory Susan 2011-03-01 00:00:00 3 10 10 ascii 2 2 FileFormat text
     3 ShortStory Susan 2011-03-01 00:00:00 3 11 11 false 3 3 Bool privato
     3 ShortStory Susan 2011-03-01 00:00:00 3 12 12 2011-10-01 4 4 LastModified date
     4 ProfitLoss Bill 2011-04-01 00:00:00 4 13 13 false 1 1 ReadOnly bool
     4 ProfitLoss Bill 2011-04-01 00:00:00 4 14 14 text 2 2 FileFormat text
     4 ProfitLoss Bill 2011-04-01 00:00:00 4 15 15 true 3 3 Bool privato
     4 ProfitLoss Bill 2011-04-01 00:00:00 4 16 16 2011-10-02 4 4 LastModified date
     5 MonthlyBudget George 2011-05-01 00:00:00 5 17 17 false 1 1 ReadOnly bool
     5 MonthlyBudget George 2011-05-01 00:00:00 5 18 18 binary 2 2 FileFormat text
     5 MonthlyBudget George 2011-05-01 00:00:00 5 19 19 false 3 3 Private bool
     5 MonthlyBudget George 2011-05-01 00:00:00 5 20 20 2011-10-20 4 4 LastModified date
     6 Paper Jane 2012-04-01 00:00:00 6 21 21 binary 2 2 FileFormat text
     6 Paper Jane 2012-04-01 00:00:00 6 22 22 false 3 3 Bool privato
     6 Paper Jane 2012-04-01 00:00:00 6 23 23 2011-10-20 4 4 LastModified date
     7 Essay John 2012-03-01 00:00:00 7 24 24 binary 2 2 FileFormat text
     7 Essay John 2012-03-01 00:00:00 7 25 25 2011-10-20 4 4 LastModified date
     8 Articolo Dan 2012-12-01 00:00:00 NULL NULL NULL NULL NULL NULL NULL NULL NULL
    

    tabella pivot

     SELECT e.*, MAX( IF(a.name = 'ReadOnly', av.value, NULL) ) as 'ReadOnly', MAX( IF(a.name = 'FileFormat', av.value, NULL) ) as 'FileFormat', MAX( IF(a.name = 'Private', av.value, NULL) ) as 'Private', MAX( IF(a.name = 'LastModified', av.value, NULL) ) as 'LastModified' FROM `entity` e LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id LEFT JOIN `attribute` a ON av.attribute_id = a.id GROUP BY e.id; 
     id title author createdOn ReadOnly FileFormat Private LastModified
     1 TestFile Joe 2011-01-01 00:00:00 true xls false 2011-10-03
     2 LongNovel Mary 2011-02-01 00:00:00 true json true 2011-10-04
     3 ShortStory Susan 2011-03-01 00:00:00 false ascii false 2011-10-01
     4 ProfitLoss Bill 2011-04-01 00:00:00 false text true 2011-10-02
     5 MonthlyBudget George 2011-05-01 00:00:00 false binary false 2011-10-20
     6 Paper Jane 2012-04-01 00:00:00 NULL binary false 2011-10-20
     7 Essay John 2012-03-01 00:00:00 NULL binario NULL 2011-10-20
     8 Articolo Dan 2012-12-01 00:00:00 NULL NULL NULL NULL
    

    Tuttavia ci sono soluzioni per utilizzare le linee come colonne, ovvero trasporre i dati. Coinvolge trucchi di query per farlo in puro SQL, o dovrai fare affidamento su alcune funzionalità disponibili solo in determinati database, utilizzando tabelle Pivot (o tabelle incrociate).

    Come esempio puoi vedere come farlo qui in Oracle (11g).

    La versione di programmazione sarà più semplice da mantenere e da realizzare e inoltre funzionerà con qualsiasi database.

    Risposta parziale poiché non conosco MySQL (bene). In MSSQL guarderei le tabelle di Pivot o creerei una tabella temporanea in una stored procedure. Potrebbe essere un momento difficile …