TSQLFile

Class Description

________________________________________________________________________

 The main motivation for the TSQLFile development is to have
 "transparent" access to SQL data base via standard TFile interface.

 The main approach that each class (but not each object) has one or two tables
 with names like $(CLASSNAME)_ver$(VERSION) and $(CLASSNAME)_raw$(VERSION)
 For example: TAxis_ver8 or TList_raw5
 Second kind of tables appears, when some of class members can not be converted to
 normalized form or when class has custom streamer.
 For instance, for TH1 class two tables are required: TH1_ver4 and TH1_raw4
 Most of memebers are stored in TH1_ver4 table columnwise, and only memeber:

  Double_t*  fBuffer;  //[fBufferSize]

 can not be represented as column while size of array is not known apriory.
 Therefore, fBuffer will be written as list of values in TH1_raw4 table.

 All objects, stored in the DB, will be registered in table "ObjectsTable".
 In this there are following columns:
     "key:id"  - key identifier to which belong object
     "obj:id"  - object identifier
     "Class"   - object class name
     "Version" - object class version
  Data in each "ObjectsTable" row uniqly identify, in which table
  and which column object is stored.

 In normal situation all class data should be sorted columnwise.
 Up to now following member are supported:
 1) Basic data types
     Here is everything clear. Column SQL type will be as much as possible
     close to the original type of value.
 2) Fixed array of basic data types
     In this case n columns like fArr[0], fArr[1] and so on will be created.
     If there is multidimensional array, names will be fArr2[1][2][1] and so on
 3) Parent class
     In this case version of parent class is stored and
     data of parent class will be stored with the same obj:id in corrspondent table.
     There is a special case, when parent store nothing (this is for instance TQObject).
     In that case just -1 is written to avoid any extra checks if table exist or not.
 4) Object as data member.
     In that case object is saved in normal way to data base and column
     will contain id of this object.
 5) Pointer on object
     Same as before. In case if object was already stored, just its id
     will be placed in the column. For NULL pointer 0 is used.
 6) TString
     Now column with limited width like VARCAHR(255) in MySQL is used.
     Later this will be improved to support maximum possible strings
 7) Anything else.
     Data will be converted to raw format and saved in _streamer_ table.
     Each row supplied with obj:id and row:id, where row:id indicates
     data, corresponding to this particular data member, and column
     will contain this raw:id

 All conversion to SQL statements are done with help of TSQLStructure class.
 This is special hierarchical structure wich internally is very similar
 to XML structures. TBufferSQL2 creates these structures, when object
 data is streamed by ROOT and only afterwards all SQL statements will be produced
 and applied all together.

 When data is reading, TBufferSQL2 will produce requests to database
 during unstreaming of object data.

 Optionally (default this options on) name of column includes
 suffix which indicates type of column. For instance:
   *:parent  - parent class, column contain class version
   *:object  - other object, column contain object id
   *:rawdata - raw data, column contains id of raw data from _streamer_ table
   *:Int_t   - column with integer value
 Use TSQLFile::SetUseSuffixes(kFALSE) to disable suffixes usage.

 This and several other options can be changed only when
 TSQLFile created with options "CREATE" or "RECREATE" and only before
 first write operation. These options are:
     SetUseSuffixes() - suffix usage in column names (default - on)
     SetArrayLimit()  - defines maximum array size, which can
                        has column for each element (default 20)
     SetTablesType()  - table type name in MySQL database (default "InnoDB")
     SetUseIndexes()  - usage of indexes in database (default kIndexesBasic)
 Normally these functions should be called immidiately after TSQLFile constructor.

 When objects data written to database, by default START TRANSACTION/COMMIT
 SQL commands are used before and after data storage. If TSQLFile detects
 any problems, ROLLBACK command will be used to restore
 previous state of data base. If transactions not supported by SQL server,
 they can be disabled by SetUseTransactions(kTransactionsOff). Or user
 can take responsibility to use transactions function to hime

 By default only indexes for basic tables are created.
 In most cases usage of indexes increase perfomance to data reading,
 but it also can increase time of writing data to database.
 There are several modes of index usage available in SetUseIndexes() method

 There is MakeSelectQuery(TClass*) method, which
 produces SELECT statement to get objects data of specified class.
 Difference from simple statement like:
   mysql> SELECT * FROM TH1I_ver1
 that not only data for that class, but also data from parent classes
 will be extracted from other tables and combined in single result table.
 Such select query can be usufull for external access to objects data.

 Up to now MySQL 4.1 and Oracle 9i were tested.
 Some extra work is required for other SQL databases.
 Hopefully, this should be straigthforward.

 Known problems and open questions.
 1) TTree is not supported by TSQLFile. There is independent development
    of TTreeSQL class, which allows to store trees directly in SQL database
 2) TClonesArray is store objects in raw format,
    which can not be accessed outside ROOT.
    This will be changed later.
 3) TDirectory cannot work. Hopefully, will (changes in ROOT basic I/O is required)
 4) Streamer infos are not written to file, therefore schema evolution
    is not yet supported. All eforts are done to enable this feature in
    the near future

 Example how TSQLFile can be used:

 example of a session saving data to a SQL data base
 =====================================================

  const char* dbname = "mysql://host.domain:3306/dbname";
  const char* username = "username";
  const char* userpass = "userpass";

  // Clean data base and create primary tables
  TSQLFile* f = new TSQLFile(dbname, "recreate", username, userpass);
  // Write with standard I/O functions
  arr->Write("arr", TObject::kSingleKey);
  h1->Write("histo");
  // Close connection to DB
  delete f;

 example of a session read data from SQL data base
 =====================================================

  // Open database again in read-only mode
  TSQLFile* f = new TSQLFile(dbname, "open", username, userpass);
  // Show list of keys
  f->ls();
  // Read stored object, again standard ROOT I/O
  TH1* h1 = (TH1*) f->Get("histo");
  if (h1!=0) { h1->SetDirectory(0); h1->Draw(); }
  TObject* obj = f->Get("arr");
  if (obj!=0) obj->Print("*");
  // close connection to DB
  delete f;

 The "SQL I/O" package is currently under development.
 Any bug reports and suggestions are welcome.
 Author: S.Linev, GSI Darmstadt,   S.Linev@gsi.de

______________________________________________________________________________
TSQLFile

class TSQLFile : public TFile

Data Members

Class Description
