src/usermanual/UserManual.tex

   1 \documentclass[12pt]{article}
   2 \usepackage{graphicx}    % needed for including graphics e.g. EPS, PS
   3 \topmargin -1.5cm        % read Lamport p.163
   4 \oddsidemargin -0.04cm   % read Lamport p.163
   5 \evensidemargin -0.04cm  % same as oddsidemargin but for left-hand pages
   6 \textwidth 16.59cm
   7 \textheight 21.94cm
   8 %\pagestyle{empty}       % Uncomment if don't want page numbers
   9 \parskip 7.2pt           % sets spacing between paragraphs
  10 %\renewcommand{\baselinestretch}{1.5} % Uncomment for 1.5 spacing between lines
  11 \parindent 0pt           % sets leading space for paragraphs
  12
  13
  14 \begin{document}
  15
  16 \title{CSQL User Manual }
  17 \author{www.databasecache.com}
  18 \date{}
  19 \maketitle
  20
  21 \tableofcontents
  22 \pagebreak
  23
  24 % Start your text
  25 \section{Introduction}
  26 \label{Introduction}
  27
  28 \emph {CSQL} is compact SQL main memory database which delivers ultra high performance and throughput. It is a free software, licensed with the GNU GENERAL PUBLIC LICENSE http://www.gnu.org/
  29 It aids in the development of high performance, fault-resilent applications requiring concurrent access to the shared data.
  30
  31 This database is built keeping ultra fast performance in mind. This database suits well as a front end database for other commercial database to increase the throughput. Throughput of queries involving single table may improve by 20x. This loads all the data into memory and avoids on demand fetches from disk and avoids the overhead of buffer manager found in commercial disk based database systems. It provides fast access through shared memory.
  32
  33 This main memory database shall be used as a cache for disk based database to improve the throughput of your exising application by 20x times without any code change.
  34 \subsection{Platforms Supported}
  35 \label{psupported}
  36 Linux \\
  37 Solaris
  38
  39 \subsection{Compilers Supported}
  40 \label{csupported}
  41 g++ in Linux \\
  42 CC in Solaris
  43
  44 \subsection{Key Features}
  45 \label{keyfeatures}
  46 Single row lookup takes less than 10 microsecs (100K selects/second)
  47
  48 Single row insert, update, delete takes less than 20 microsecs (50K writes/second, 180 Million inserts/hour)
  49
  50 Memory resident architecture to give ultra fast performance
  51
  52 Embedded Architecture - database is co-located with application
  53
  54 Small Footprint
  55
  56 Multi Layered: ODBC, JDBC, SQL API, DB API
  57
  58 Supports ACI of Transaction Properties
  59
  60 Isolation Level support:
  61
  62     READ UNCOMMITED, READ COMMITTED, READ REPEATABLE
  63
  64 Protection from process failures
  65
  66 Faster lookups through Hash Indexes
  67
  68 Row Level locking for high concurrency
  69
  70 Hand written user level mutexes which avoids major recovery
  71
  72 Multi user support with authentication
  73
  74 Primitive SQL support
  75
  76 Primitive JDBC Support
  77
  78
  79 \subsection{Architecture}
  80 \label{arch}
  81 Main memory databases are times faster than disk based database systems as, all the data is available
  82 in physical memory and avoids the buffer manager overhead which is found in disk based database systems.
  83 Moreover data structures and algorithms can work more efficiently as compared to traditional disk based algorithms. \\
  84
  85 CSQL is compact main memory database SQL Engine which supports limited set of features and gives ultra
  86 fast response to database queries. It supports only limited features which is used by most of the real time
  87 applications which includes INSERT, UPDATE, DELETE, SELECT on single table with local predicates.
  88 The primary difference between traditional database system and main memory database system is that in the
  89 latter the whole data is stored in main memory.It differs in \\
  90
  91     Indexing - A conventional index stores a data value followed by the address at which the record can be
  92                located. When all the data is in memory anyway, there is no need to store the data value
  93                redundantly, only the address is required. \\
  94
  95     Caching/buffer management - no longer exists because all the data is in memory already. All of the buffer or cache
  96                processing as well as disk latency associated with moving data from disk to
  97                memory (and vice versa) are removed. \\
  98
  99     Optimization - is still required but becomes much simpler and is more likely to choose the fastest path,
 100                since the number of alternatives is much less than with a disk-based database \\
 101
 102 CSQL is developed keeping one thing in mind - performance. It uses shared memory architecure where all
 103 applications access the database though direct pointer referencing. Data access is synchronized using mutexes
 104  for internal structures and through row level locks for rows. The is absolutely no disk I/O for any DML operation,
 105 only for writing trace log messages disk is used. Rest all are handled and processed in-memory. \\
 106
 107 \subsection{Compiling the source:}
 108 \label{compsrc}
 109 Go to the root directory and enter the following commands.
 110 \begin{verbatim}
 111 $./configure --prefix=`pwd`/install
 112 $make
 113 $make install
 114 \end{verbatim}
 115
 116 This will create "install" directory under the current directory and places all the executables created in bin directory and libraries in lib directory. \\
 117
 118 \subsection{Generating API Reference}
 119 \label{genAPIref}
 120 Go to the root directory and enter
 121 \begin{verbatim}
 122 $doxygen
 123 \end{verbatim}
 124
 125 This will create "docs/html" directory under which API Reference html files are stored. Refer index.html in that directory.
 126
 127 \section{Getting Started}
 128 \label{getstarted}
 129 \subsection{Starting the Server}
 130 \label{startserver}
 131 You should find csqlserver executable under the bin directory of the installation.
 132 Change csql.conf file under the installation root directory if necessary.
 133
 134 Set the \textbf {CSQL\_INSTALL\_ROOT } environment variable to absolute path of installation root directory. For example if csql is installed in /opt/csql, enter
 135
 136 \begin{verbatim}
 137 $export CSQL_INSTALL_ROOT=/opt/csql
 138 \end{verbatim}
 139
 140 Set the \textbf{CSQL\_CONFIG\_FILE}  environment variable to absolute path of csql.conf file.
 141
 142 \begin{verbatim}
 143 $export CSQL_CONFIG_FILE=$CSQL_INSTALL_ROOT/csql.conf
 144 \end{verbatim}
 145
 146 Set the \textbf {LD\_LIBRARY\_PATH} environment variable to absolute path $CSQL\_INSTALL\_ROOT/lib
 147
 148 \begin{verbatim}
 149 $export LD_LIBRARY_PATH=$CSQL_INSTALL_ROOT/lib
 150 \end{verbatim}
 151
 152 Note: Alternatively, setupenv.ksh script in installation directory shall be run to set all the above environment variables.
 153
 154 You should find csqlserver executable under the bin directory of the installation. Running that command will start the server.
 155 \begin{verbatim}
 156 $./csqlserver
 157 \end{verbatim}
 158
 159 This starts the server and creates the database file.
 160
 161 \subsection{Shutting down the Server}
 162 \label{shutserver}
 163 Pressing Ctrl-C on the terminal where csqlserver is running, is safe and will stop the server gracefully by removing the database file.
 164
 165 \subsection{Creating tables}
 166 \label{creatingtables}
 167 Tables and Indexes can be created though interactive sql tool named csql.
 168 To create table t1 with two fields integer and char
 169 \begin{verbatim}
 170 $csql
 171 CSQL>create table t1 (f1 int, f2 char (20), primary key (f1));
 172 \end{verbatim}
 173 If you see "Statement Executed" message after you enter the above command, it means the table is created successfully.
 174
 175 \subsection{Inserting data into tables}
 176 \label{insertingdata}
 177 Rows shall be inserted using INSERT SQL statement through csql tool
 178 \begin{verbatim}
 179 $csql
 180 CSQL>insert into t1 values (10, 'Value');
 181
 182 \end{verbatim}
 183 If you see "Statement Executed: Rows Affected = 1 " message after you enter the above command, it means the row is inserted successfully.
 184
 185 \subsection{Selecting data from tables}
 186 \label{selectdata}
 187
 188 \begin{verbatim}
 189 $csql
 190 CSQL>select * from t1;
 191 \end{verbatim}
 192 It will display all the rows in the table t1
 193
 194
 195 \subsection{Deleting data from tables}
 196 \label{deletedata}
 197
 198 \begin{verbatim}
 199 $csql
 200 CSQL>delete from t1;
 201 \end{verbatim}
 202 It will delete all the rows in the table t1
 203 \section{API Interfaces}
 204
 205 \subsection{SQL Support}
 206 \label{SQL Syntax Support:}
 207
 208 \begin{verbatim}
 209 CREATE TABLE {tablename} ({fielddefinitionlist}[, PRIMARY KEY ({fieldname})])
 210      fielddefinitionlist shall be fieldname type [(size)] [NOT NULL]
 211
 212 CREATE INDEX {idxname} ON {tablename}({fieldname}) [HASH] [UNIQUE] [PRIMARY]
 213
 214 DROP TABLE {tablename}
 215
 216 DROP INDEX {indexname}
 217
 218 INSERT INTO {tablename} [fieldNameList] VALUES ({valuelist})
 219
 220 UPDATE {tablename} SET {fldname=value ,...} [WHERE {condition}]
 221
 222 DELETE FROM {tablename} [WHERE {condition}]
 223
 224 SELECT {* | fieldNameList} FROM {tablename} [WHERE {condition}]
 225      condition shall be [NOT] {predicate} {AND | OR } {predicate}
 226      predicate shall be {fieldName} {"=, !=, >,<, >=, <="} [fieldName | value]
 227                          LIKE {format}, BETWEEN {value} AND {value} , IN {list}
 228 \end{verbatim}
 229
 230 DataType support:
 231 INT, LONG , BIGINT, SHORT,
 232 FLOAT,  DOUBLE,
 233 DATE, TIME,  TIMESTAMP,
 234 CHAR
 235
 236
 237 \subsection{JDBC Interface }
 238 \label{JDBC Interface }
 239 \subsubsection{Supported Interfaces}
 240 \label{supportedInterfaces}
 241 \begin{verbatim}
 242 Connection:: connect, close
 243 Statement:: execute
 244 PreparedStatement:: execute, params
 245 ResultSetMetaData::
 246 ResultSet:: Forward only
 247 \end{verbatim}
 248
 249
 250 DataType Support:
 251 All Primitive types
 252 Date, Time, TimeStamp
 253
 254 \subsubsection{Connecting from jdbc}
 255 \label{jdbcconnect}
 256 The default username is root and password is manager for the csql database system. Below code snippet shows how to connect to csql database from jdbc applications \\
 257 \begin{verbatim}
 258           Class.forName("csql.jdbc.JdbcSqlDriver");
 259           Connection con = DriverManager.getConnection("jdbc:csql", "root", "manager");
 260 \end{verbatim}
 261
 262 \subsubsection{Inserting data through jdbc}
 263 \label{jdbcinsert}
 264  The below code snippet inserts into table t1 with two fields, f1 integer and f2 char \\
 265 \begin{verbatim}
 266           PreparedStatement stmt = null;
 267           stmt = con.prepareStatement("INSERT INTO T1 (f1, f2) VALUES (?, ?);");
 268           int ret =0;
 269           for (int i =0 ; i< 10 ; i++) {
 270              stmt.setInt(1, i);
 271              stmt.setString(2, String.valueOf(i+100));
 272              ret = stmt.executeUpdate();
 273              if (ret != 1) break; //error
 274            }
 275            stmt.close();
 276            con.commit();
 277 \end{verbatim}
 278
 279 \subsection{SQLAPI }
 280 \label{SQLAPI }
 281
 282 \begin{verbatim}
 283 SqlConnection :: connect, disconnect, commit, rollback
 284
 285 SqlStatement:: prepare, execute,  bindField, bindParam,
 286                fetch, set[Type]Param, getProjFldInfo, getParamFldInfo
 287
 288 FieldInfo :: Meta data information for params and projection fields.
 289 \end{verbatim}
 290
 291
 292 \subsection{DB API}
 293 \label{dbapi}
 294 This allows user process and threads to access or manipulate the database. Main interfaces are Connection, DatabaseManager, Table, etc.
 295 Connection provides interface to connect and disconnect to the database file
 296 DatabaseManager provides interface to create and drop database objects including tables and indexes.
 297 Table provides interface to insert, update, delete and fetch tuples.
 298
 299 Refer API reference under the directory docs/html. (If there is no html, you shall generate it by yourself using doxygen tools. Refer the previous section for this.)
 300
 301 \subsubsection{Getting a Connection}
 302 \label{getconn}
 303 Connection interface is the heart of all the interfaces as it is the entry point for database access and it provides interface for transaction commit/rollback.
 304
 305 You can obtain a connection to database using the following code snippet:
 306
 307 \begin{verbatim}
 308 Connection conn;
 309 DbRetVal rv = OK;
 310 rv = conn.open("root", ",manager");
 311 if (rv!= OK) return -1;
 312 ...
 313 \end{verbatim}
 314
 315 \subsubsection{Creating Tables}
 316 \label{createtable}
 317 Database Manager provides interface for table creation and deletion. We shall obtain the DatabaseManager object from the connection object. The table or schema definition is encapsulated in TableDef interface. It provides methods to specify the field definition of the table.
 318 For example to create table with two fields,
 319 \begin{verbatim}
 320     DatabaseManager *dbMgr = conn.getDatabaseManager();
 321     if (dbMgr == NULL) { printf("Bad connection \n"); return -1;}
 322     TableDef tabDef;
 323     tabDef.addField("f1", typeInt, 0, NULL, true);
 324     tabDef.addField("f2", typeString, 196);
 325     rv = dbMgr->createTable("t1", tabDef);
 326     if (rv != OK) { printf("Table creation failed\n"); return -1; }
 327 \end{verbatim}
 328
 329 First parameter of addField method is field name, second is the type of the field, third parameter is length, fourth is default value, fifth is not null flag and last is primary key flag.
 330 In our example, field "f1" is not null and it is the primary key for the table.
 331 Call addField for all the field definition in the table and call createTable passing table name as first parameter and TableDef as second parameter.
 332
 333 \subsubsection{Inserting into the tables}
 334 \label{inserttable}
 335 Any DML operation requires a transaction to be started. All the operations happen within the context of this transaction.
 336 Application buffer should first be binded to the respective fields using the bindFld method as mentioned in the below example; insertTuple method will pick values from the binded application buffer and creates a new row or tuple in the table.
 337 \begin{verbatim}
 338     Table *table = dbMgr->openTable("t1");
 339     if (table == NULL) { printf("Unable to open table\n"); return -1; }
 340     int id = 100;
 341     char name[196] = "Tuticorin";
 342     table->bindFld("f1", &id);
 343     table->bindFld("f2", name);
 344     conn.startTransaction();
 345     ret = table->insertTuple();
 346     if (ret != OK) { printf("Unable to insert tuple\n"); return -1; }
 347     conn.commit();
 348 \end{verbatim}
 349
 350 \subsubsection{Selecting tuples from the tables}
 351 \label{seltable}
 352 Application buffer should first be binded to the respective fields using the bindFld() method as we did for insert; fetch() method will copy the values from the row to the binded application buffer.
 353 Predicate if required shall be created using the condition interface and set in the table object before the execution. Calling execute() method is must before calling the fetch() method, as it will create the execution plan for the scan. It selects appropriate index based on the predicate set.
 354
 355 \begin{verbatim}
 356    Condition p1;
 357    int val1 = 100;
 358    p1.setTerm("f1", OpEquals, &val1);
 359    table->setCondition(p1);
 360    int id = 100;
 361    char name[196] = "Bangalore";
 362    table->bindFld("f1", &id);
 363    table->bindFld("f2", name);
 364    table->execute();
 365    void *tuple = (char*)table->fetch() ;
 366    if (tuple == NULL) {printf(" No tuple found \n" ); table->close();return -1;}
 367    printf("tuple value is %d %s \n",id ,name);
 368    table->close();
 369 \end{verbatim}
 370
 371 \subsubsection{Updating or Deleting tuples}
 372 \label{updtable}
 373 This operation is allowed always on existing scan. When this method is called the current tuple in the scan in either updated with application buffer values which are binded, or gets deleted based on the method called.
 374 For example:
 375 \begin{verbatim}
 376         tuple = (char*)table->fetch() ;
 377         if (tuple == NULL)  {printf(" No tuple found \n" ); table->close();return -1;}
 378         strcpy(name, "50576543210"); //update the value
 379         table->updateTuple();
 380 \end{verbatim}
 381
 382 \section{Configuration}
 383 \label{confFile}
 384 \subsection{csql.conf}
 385 \label{csqlconf}
 386 Configuration is read from environment variable \begin{verbatim}CSQL_CONFIG_FILE \end{verbatim}. A default configuration is available at the src root directory with name csql.conf.
 387
 388 \subsubsection{Server Section}
 389 \label{serversection}
 390 This contains parameters which affect the functioning of the csql server process. For Server section parameters, make sure that the value is same for the server process and all the csql client process which connects to it. otherwise, behavior is undefined
 391
 392 \begin{verbatim} PAGE_SIZE \end{verbatim}
 393 Default Value :8192 \\
 394 Description: Each database is logically divided into pages and allocation happens in unit of pages. This defines the size of the allocation unit. Increasing this value will reduce frequent allocation of pages \\
 395
 396
 397 \begin{verbatim} MAX_TRANS \end{verbatim}
 398 Default Value :100 \\
 399 Description: Total number of active transactions that can run concurrently in the system \\
 400
 401 \begin{verbatim} MAX_PROCS \end{verbatim}
 402 Default Value :100 \\
 403 Description: Total number of client process which can connect and work with the database concurrently \\
 404
 405 \begin{verbatim} MAX_SYS_DB_SIZE \end{verbatim}
 406 Default Value :1048576 bytes \\
 407 Description: Maximum size of the system database. Catalog information and control information(lock, logs, etc) are stored in this internal database. \\
 408
 409 \begin{verbatim} MAX_DB_SIZE \end{verbatim}
 410 Default Value :10485760 bytes \\
 411 Description: Maximum size of the user database. Rows and Index information of tables are stored in this database.\\
 412
 413
 414 \begin{verbatim} SYS_DB_KEY \end{verbatim}
 415 Default Value :2222 \\
 416 Description: Shared memory key to be used by the system to create and locate system database. Change this value if u want to run two csql instances in same host \\
 417
 418 \begin{verbatim} USER_DB_KEY \end{verbatim}
 419 Default Value :5555 \\
 420 Description: Shared memory key to be used by the system to create and locate user database \\
 421
 422 \begin{verbatim} LOG_FILE \end{verbatim}
 423 Default Value :/tmp/log/log.out \\
 424 Description: Give full path for the log file. This is  where important system actions are stored for tracing application events\\
 425
 426
 427 \begin{verbatim} MAP_ADDRESS \end{verbatim}
 428 Default Value :400000000 \\
 429 Description: The virtual memory start address at which the shared memory segment will be created and attached\\
 430
 431 \subsubsection{Client Section}
 432 \label{serversection}
 433 This contains parameters which affect the functioning of the client process which connects to the database.
 434
 435 \begin{verbatim} MUTEX_TIMEOUT_SECS \end{verbatim}
 436 Default Value : 0 \\
 437 Description: Mutex interval in seconds
 438
 439 \begin{verbatim} MUTEX_TIMEOUT_USECS \end{verbatim}
 440 Default Value : 1000 \\
 441 Description: Mutex interval in milli seconds
 442
 443 \begin{verbatim} MUTEX_TIMEOUT_RETRIES \end{verbatim}
 444 Default Value : 10 \\
 445 Description: Mutex retry count in case it fails to acquire
 446
 447
 448 \begin{verbatim} LOCK_TIMEOUT_SECS \end{verbatim}
 449 Default Value : 0 \\
 450 Description: Lock interval in seconds
 451
 452 \begin{verbatim} LOCK_TIMEOUT_USECS \end{verbatim}
 453 Default Value : 1000 \\
 454 Description: Lock interval in milli seconds
 455
 456 \begin{verbatim} LOCK_TIMEOUT_RETRIES \end{verbatim}
 457 Default Value : 10 \\
 458 Description: Lock retry count in case it fails to acquire
 459
 460 \section{Tools}
 461 \label{tools}
 462 \subsection{csql tool}
 463 \label{csqltool}
 464 \begin{verbatim}
 465 csql is a command line tool for interactive SQL access to database.
 466
 467 Usage: csql [-u username] [-p passwd] [-s sqlfile]
 468        username -> username to connect to database
 469        password -> password to connect to database
 470        sqlfile -> filename containing sql statements
 471 \end{verbatim}
 472
 473 Example:
 474 \begin{verbatim}
 475 $csql -u root -p manager -s /tmp/import.sql
 476 \end{verbatim}
 477
 478 \subsection{catalog tool}
 479 \label{catalogtool}
 480 \begin{verbatim}
 481 catalog is a command line tool used to display the internal control structures of
 482 csql database engine. It will print the information in XML format.
 483
 484 Usage: catalog [-u username] [-p passwd] [-l] [-i] [-d] [-T table] [-I index]
 485                [-D <lock|trans|proc>]
 486        l -> list all table with field information
 487        i -> reinitialize catalog tables. Drops all tables.
 488        d -> print db usage statistics
 489        T -> list table information
 490        I -> list index information
 491        D -> print debug information for system tables
 492 Note: If multiple options are specified, last one will be considered.
 493 \end{verbatim}
 494
 495 Example:
 496 \begin{verbatim}
 497 $catalog -u root -p manager -T table1
 498 \end{verbatim}
 499 Sample output:
 500 \begin{verbatim}
 501 <Table Info>
 502   <TableName> table1 </TableName>
 503   <TupleCount> 0 </TupleCount>
 504   <PagesUsed> 1 </PagesUsed>
 505   <SpaceUsed> 16 </SpaceUsed>
 506   <Indexes> 1 <Indexes>
 507   <TupleLength> 20 </TupleLength>
 508   <Fields> 2 </Fields>
 509   <Indexes>
 510 <IndexName> t1_idx1_Primary </IndexName>
 511   </Indexes>
 512 </Table Info>
 513 \end{verbatim}
 514
 515 \section{Common Error Messages}
 516 \label{errormessages}
 517 \textbf {Message: Shared memory open failed}  \\
 518 Check whether the csqlserver process is running \\
 519
 520 \textbf {Message: Unable to create log file or Unable to start the logger}\\
 521 Check whether the csqlserver process is running \\
 522 Check whether log file directory exists which is specified in csql.conf \\
 523 For default configuration /tmp/log directory should exist in the system.
 524
 525 \textbf {Message: Shared memory create failed}\\
 526 Check the system limit for max shared memory segment \\
 527 Run the following command to increase the limit to 1 GB \\
 528 /sbin/sysctl -w kernel.shmmax=1000000000
 529
 530 % Stop your text
 531 \end{document}