ROOT IO for SQL databases Sergey Linev GSI
ROOT I/O for SQL databases Sergey Linev, GSI, Germany 13. 02. 2006 S. Linev ROOT I/O for SQL databases
What is SQL? o o SQL – Structured Query Language SQL is language to create, modify and retrieve data from Relational Database Management System (RDBMS) Standard since 1986, several revisions in 1992 – SQL 2, 1999 – SQL 3 Supported in most commercial and open source RDBMS 13. 02. 2006 S. Linev ROOT I/O for SQL databases 2
SQL support in ROOT o Abstract interface via three classes: n n n o o TSQLServer – query execution TSQLResult – result of single SELECT query TSQLRow – content of single row Implemented for My. SQL, Oracle, Postgre. SQL TTree. SQL class provides TTree interface to database tables. Allow tree drawing and table modifications 13. 02. 2006 S. Linev ROOT I/O for SQL databases 3
Objects store in SQL database o Write code yourself n n o Take existing SQL I/O framework n n o pro: probably, best performance contra: development, maintenance pro: fast to implement contra: second I/O scheme for your classes Convert to binary buffer and write as BLOB n n pro: uses ROOT I/O facility contra: no access to data without ROOT 13. 02. 2006 S. Linev ROOT I/O for SQL databases 4
TFile as interface to database o Main features of TFile: n n o Pro of TFile interface to SQL database: n n n o Subdirectories structure and keys list Schema evolution learning – you do not need to learn using – same I/O code can be used developing – code can be tested with standard or xml file format Contra: n n fixed table design performance 13. 02. 2006 S. Linev ROOT I/O for SQL databases 5
How it works o New TBuffer. SQL 2 class implemented: n n o When writing object: n o inherited from TBuffer class redefines all virtual methods for basic data types and objects I/O splits object data on parts, belonging to different class members and produces SQL INSERT queries When reading object: n generates SELECT queries and extract class member values from obtained tables subset 13. 02. 2006 S. Linev ROOT I/O for SQL databases 6
Class table structure o For each class version separate table n o Class members stored in separate columns: n n n o TBox_ver 2, TGraph_ver 4 and so on basic data types fixed-size array of basic types object, pointer on object (object id) parent class (version) special treatment for TObject and TString Anything else will be stored in “raw” format in special table like TGraph_streamer_ver 4 13. 02. 2006 S. Linev ROOT I/O for SQL databases 7
TSQLFile class o o Inherited from TFile Full support of schema evolution Beside TTree all other ROOT classes are supported Additional configuration possible for: n n usage of table indexes transactions My. SQL table types selection suffixes for column names 13. 02. 2006 S. Linev ROOT I/O for SQL databases 8
Example with simple class TBox: public TObject, public TAtt. Line, public TAtt. Fill { Double_t f. X 1; Double_t f. Y 1; Double_t f. X 2; Double_t f. Y 2; Class. Def(TBox, 2); }; Created tables: { } TSQLFile f(“mysql: //host. domain/test”, “create”, ”user”, ”pass”); for (int n=1; n<=10; n++) { TBox* b = new TBox(n, n*2, n*3, n*4); b->Write(Form(“box%d”, n)); } SELECT * FROM TBox_ver 2 TObject_ver 1 • TBox_ver 2 • TObject_ver 1 • TAtt. Line_ver 1 • TAtt. Fill_ver 2 • Keys. Table • Objects. Table • Configurations 13. 02. 2006 S. Linev ROOT I/O for SQL databases 9
Common tables o Three common tables n n n Keys. Table – list of all keys in the file Objects. Table – list of all objects id Configurations – TSQLFile config Keys. Table 13. 02. 2006 Objects. Table S. Linev ROOT I/O for SQL databases Configurations 10
More complicated example { } TGraph_streamer_ver 4 TSQLFile f(“mysql: //host. domain/test”, “update”, ”user”, ”pass”); TGraph* gr = new TGraph(10); for (int n=0; n<10; n++) gr->Set. Point(n, n+1, (n+1)*(n+1)); gr->Write(“gr”); TGraph_ver 4 parent classes NULL pointer TList_streamer_ver 5 Objects. Table 13. 02. 2006 S. Linev ROOT I/O for SQL databases 11
Support of custom streamers o o Important, while lot of ROOT and some user classes has custom streamers Data, produced by custom streamer, directly written to _streamer_ tables like TList_streamer_ver 5 Special case when custom streamer reads data, written by standard I/O Even in custom streamer user can split data into different columns of “normal” class table 13. 02. 2006 S. Linev ROOT I/O for SQL databases 12
External access to SQL tables o o Easy navigation with simple SELECT statements One raw in table corresponds to one object Class name and version for each object can be found in Objects. Table TSQLFile: : Make. Select. Query() produce SELECT statement, which aggregates data of object from different tables in one 13. 02. 2006 S. Linev ROOT I/O for SQL databases 13
Example with TBox class Query, produced by f->Make. Select. Query(TBox: : Class()): SELECT t 1. `obj: id`, t 2. Unique. Id, t 2. Bits, t 2. Process. Id, t 3. f. Line. Color, t 3. f. Line. Style, t 3. f. Line. Width, t 4. f. Fill. Color, t 4. f. Fill. Style, t 1. f. X 1, t 1. f. Y 1, t 1. f. X 2, t 1. f. Y 2 FROM TBox_ver 2 AS t 1 LEFT JOIN TObject_ver 1 AS t 2 USING(`obj: id`) LEFT JOIN TAtt. Line_ver 1 AS t 3 USING(`obj: id`) LEFT JOIN TAtt. Fill_ver 1 AS t 4 USING(`obj: id`) TObject 13. 02. 2006 TAtt. Line TAtt. Fill S. Linev ROOT I/O for SQL databases TBox 14
SQL I/O performance o Two aspects: n n Time and CPU usage on user host Quality and number of SQL statements // TFile f(“test. root”, ”recreate”); // TXMLFile f(“test. xml”, ”recreate”); TSQLFile f(“mysql: //host. domain/test”, “recreate”, ”user”, ”pass”); TClones. Array clones("TBox", 10000); for(int n=0; n<10000; n++) new (clones[n]) TBox(n+1, n+2, n+3, n+4); clones. Write("clones 0", TObject: : k. Single. Key); g. Benchmark->Start(“Write"); clones. Write("clones", TObject: : k. Single. Key); g. Benchmark->Show(“Write"); 13. 02. 2006 // TFile f(“test. root”, ”recreate”); // TXMLFile f(“test. xml”, ”recreate”); TSQLFile f(“mysql: //host. domain/test”, “read”, ”user”, ”pass”); TClones. Array* clon = 0, *clon 0 = 0; f. Get. Object("clones 0", clon 0); g. Benchmark->Start(“Read"); f. Get. Object("clones", clon); g. Benchmark->Show(“Read"); S. Linev ROOT I/O for SQL databases 15
Performance measurement Writing Reading CPU, s Real, s Query Binary 0. 03 - 0. 02 - XML 1. 21 - 1. 44 - My. SQL* 3. 23 6. 33 60** 2. 54 3. 05 6 Oracle*** 2. 86 9. 08 14 4. 31 4. 43 6 * My. SQL 4. 1 on Fedora Core 4. Pure text queries are used. With ODBC or native My. SQL++ client factor 2 to 3 can be gained ** Query text length was limited to 50 KB and long queries were split *** Oracle 10 g on Su. SE 8. New TSQLStatement class (not yet in ROOT) was used to gain factor 25 in writing compare to standard ROOT 13. 02. 2006 S. Linev ROOT I/O for SQL databases 16
Conclusion o o o new TSQLFile provides new possibilities for usage of SQL database in ROOT Tested with My. SQL 4 and Oracle 10, can be adopted for other RDBMS To be done: n n enhancement of ROOT TSQLServer classes new TSQLStatement class is required investigation of ODBC usage in ROOT performance optimization 13. 02. 2006 S. Linev ROOT I/O for SQL databases 17
- Slides: 17