 технарь
Профессионал
Сообщений: 1830
Регистрация: 12.2011
|
Введение
Задавшись целью создать CMS на файлах и не потерять совместимости с SQL был написан грамматический анализатор (парсер) который в дальнейшем и был применен в Limbo . Парсер преобразует запросы согласно стандартам SQL и очень прост в использовании .
Система работает согласно стандартам ADODB а потому возможен безболезненный переход с базы на файлах к SQL базе данных.
Использование и подключение
When you are writing a module or a component the Limbo system automatically insures that it connects to the database . The database connection can be accessed using the $conn object
To connect to a database you will have to do something like
code:include("db.php");
$conn = &ADONewConnection('none');
$conn->prefix=$prefix; // if there is a prefix then table names will be like lc_mytable in the query
if($cache)$conn->cache=true;
if($qcache)$conn->qcache=true;
$conn->PConnect("","","",$absolute_path."data/");
6.3 Поддержка синтаксиса SQL
Supported SQL-code follows the standards but are limited to exactly the code alternatives below. Joins and sub-queries are NOT! supported and all SQL-code MUST! be written in uppercase like below (this is by the standard anyway). Use the included SQL-manager to check what code is working.
The system database have two (2) hard-coded fields; "id" who always is the primary key, and "lastupd" which contains the last updated date & time in a UNIX timestamp format.
[...] = optional code, {...} = alternatives; do not use the "{", "}", "[" and "]" in your code.
6.4.1 Выбор запроса
SELECT {*|filed1, field2...}
FROM table
[WHERE field {=|LIKE|<|>|<=|>=} 'value'|number [AND|OR] ...]]
[ORDER BY field {ASC|DESC}[, field...]]
6.4.2 Вставка запроса
INSERT INTO table (field1,field2,field3)
VALUES ('value1','value2',number3,...)
6.4.3 Дополнение запроса
UPDATE table
SET field1 = 'value1', field2 = number2, ...
[WHERE field {=|LIKE|<|>|<=|>=} 'value'|number [AND|OR] ...]]
6.4.4 Удаление запроса
DELETE FROM table
[WHERE field {=|LIKE|<|>|<=|>=} 'value'|number [AND|OR] ...]]
6.4.5 Создание таблиц
CREATE TABLE table (
id int(10) auto_increment ,
lastupd int(20) ,
field1 {int(N)|varchar(N)} {NULL|NOT NULL} [dafault 'value']
field2 {int(N)|varchar(N)} {NULL|NOT NULL}
field3 {int(N)|varchar(N)} {NULL|NOT NULL}
...
PRIMARY KEY (id)
)
6.4.6 Drop Query
DROP TABLE table
6.4 Функция ссылок
All functions and global vars, exept a few, are coded for compability with the ADODb library. All are named exactly the same way and can be called with the same arguments. Functions, arguments and vars not used by WSI_FTM but required by the ADODb library still exists, but as nonworking dummies.
$ADODB_Database (string). Store the last database driver used. This is a ADODb global value, not used by WSI_FTM, but included for compability.
$ADODB_vers (string). Show the program name and version.
$gSQLMaxRows (int). Set max recordsets to download (LIMIT function).
$gSQLBlockRows (int). Set max rows to be included in a block. Function not implemented in WSI_FTM but included for compability reasons.
string $conn->ErrorMsg (). returns the last error message which occured if any .
ADONewConnection() Set up the program class and returns the class pointer.
obj $conn = &ADONewConnection();
PConnect() Establish a permanent connection to a database. NOTE! If the database is named different from default, or set up in a folder other than default, the path and name is set here as last argument. If all defaults are used, then this is a dummy function in WSI_FTM, but must be used for compability with the ADODb library.
void $conn->PConnect([host], // dummy
[username], // dummy
[password], // dummy
[database name, default = "./data/"]);
Execute() Execute a SQL-string to manipulate the database. Returns true if success, else return false and put at error-message in the error-messages array (see function below). Recordset is stored in a class var and fetched by the function GetArray().
bool $rs = $conn->Execute(sql-string);
GetArray() Fetch the recordset as a 2-dim array. This is the only way you can get the recordset .
array $rows = $rs->GetArray();
GetLimit() Fetch the recordset after applying the limit operator . for example use it to get the top 10 downlods .
array $rows = $rs->GetLimit(sql-string,num,offset);
GetRow() Fetch the recordset as a row ( single recordset ).
array $row = $rs->GetArray(sql-string);
Insert_ID() Returns the new id after an INSERT.
int $new_id = $conn->Insert_ID();
Affected_Rows() Returns affected rows after UPDATE.
int $aff_rows = $conn->Affected_Rows();
RecordCount() Count all records in the recordset.
int $records = $conn->RecordCount();
FieldCount() Returns the number of fields in recordset.
int $fields_no = $conn->FieldCount();
MetaTables() Returns an array of table names in database.
array $tables = $conn->MetaTables();
MetaColumnNames() Returns an array of field names in table.
array $fields = $conn->MetaColumnNames(table_name);
MetaColumns() Returns an array of ADOFieldObjects. This function only returns the object pointer, see below for coding example.
array $meta_fld = $conn->MetaColumns(table_name);
string $fld_name = $meta_fld['fld_name']->name;
int $fld_maxlength = $meta_fld['fld_name']->max_length;
string $fld_type = $meta_fld['fld_name']->type;
bool $fld_null = $meta_fld['fld_name']->not_null;
bool $fld_def = $meta_fld['fld_name']->has_default;
mixed $fld_defval = $meta_fld['fld_name']->default_value;
6.5 Кеширование
Due to the slow nature in which the general text file based database operate special cache interface was built into the system which can be easily enabled and disabled by the user . The performace increase after using the cache system is vquite high and makes the system faster than MySQL for smaller database .
Two type of cacheing is used in the system
Resultset cacheing : In this the result set returned by a query is cached and returned next time the query is executed .
Query cacheing : In this the parsed query is stored for future use ( SQL parsing is not very fast )
In the system cacheing is disabled by default so to enable cacheing do the following where $conn is the database object
$conn->cache = true ; // Result set caching
$conn->qcache = true ; // Query cacheing
6.6 Благодарности
This system is an highly optimized and improved version of WSI Flat Table Manager Manual from WebbStyle International (WSI) in which the orignal parser was replaced by a parser that of Php-txt-db API from www.c-worker.ch |
Описание: Формат команд для работы с БД
