/**
* @param string
* @return void
* @throws SQLiteException
*/
protected function run($sql)
{
#CDUtils::printError('SQLiteDatabaseLogFilter->run(): '.$sql."\n");
if (!$this->db->queryExec($sql)) {
throw new SQLiteException(sqlite_error_string($this->db->lastError()));
}
}
function getConnection()
{
if (!$this->connection) {
$this->connection = $this->openDB();
// Nahraj databázi
if (filesize(self::$databasePath) == 0) {
//$this->beginTransaction();
$this->connection->queryExec(file_get_contents(dirname(__FILE__) . "/setupDB.sql"), $error);
if ($error) {
throw new InvalidStateException("Can't create SQLite database: " . $error);
}
//$this->endTransaction();
}
}
return $this->connection;
}
function remove($data)
{
$db = new SQLiteDatabase("sql/imgorg.db");
$images = $data->images;
for ($i = 0; $i < sizeof($images); $i++) {
$res = $db->query('SELECT url FROM Images where id ="' . $images[$i] . '"');
$url = $res->fetchObject()->url;
unlink('../' . $url);
$db->queryExec('DELETE FROM Images WHERE id ="' . $images[$i] . '"');
$db->queryExec('DELETE FROM Images_Tags WHERE image_id ="' . $images[$i] . '"');
}
}
function sqlite()
{
try {
//create or open the database
$database = new SQLiteDatabase('db.sqlite', 0666, $error);
} catch (Exception $e) {
die($error);
}
echo 'connected. </br>';
//add Movie table to database
$query = 'CREATE TABLE IF NOT EXISTS Cryptoxi' . '(Id TEXT, Time INTEGER, Message TEXT)';
if (!$database->queryExec($query, $error)) {
die($error);
}
//insert data into database
$a = 'Id';
$b = 'Time';
$c = 'Message';
$query = "INSERT INTO Movies ('{$a}', '{$b}', '{$c}') " . 'VALUES ("Interstella 5555", "Daft Punk", 2003); ';
if (!$database->queryExec($query, $error)) {
die($error);
}
function select($database)
{
//read data from database
$query = "SELECT * FROM Movies";
$tit = 'Title';
if ($result = $database->query($query, SQLITE_BOTH, $error)) {
while ($row = $result->fetch()) {
print "Title: {$row[$tit]} <br />" . "Director: {$row['Director']} <br />" . "Year: {$row['Year']} <br /><br />";
}
} else {
die($error);
}
}
select($database);
}
protected function Exec($query)
{
switch ($this->_driver) {
case 'SQLITE':
try {
$this->_db->queryExec($query);
} catch (SQLiteException $x) {
Sobi::Error('cache', sprintf('SQLite error: %s', $x->getMessage()), SPC::WARNING, 0, __LINE__, __FILE__);
}
break;
case 'PDO':
$this->_db->exec($query);
break;
}
}
function hitcounter($url)
{
//error_reporting(6143);
$hits = 0;
if ($db = new SQLiteDatabase('/home/a3253051/db/hitcounter.db')) {
$q = @$db->query("SELECT counter FROM rsshits WHERE url = '" . $url . "'");
if ($q === false) {
$db->queryExec('CREATE TABLE rsshits (url TEXT, counter int)');
//$hits = 1;
} else {
$result = $q->fetchSingle();
$hits = intval($result);
//$db->queryExec("INSERT INTO rsshits VALUES ('".$_GET['url']."',".$hits.")");
//$db->queryExec("UPDATE rsshits SET counter=".$hits." WHERE url='".$_GET['url']."'");
}
} else {
die($err);
}
return $hits;
}
<?php
$list = <<<EOF
AD,Andorra;AE,United Arab Emirates;AF,Afghanistan;AG,Antigua & Barbuda;AI,Anguilla;AL,Albania;AM,Armenia;AN,Netherlands Antilles;AO,Angola;AQ,Antarctica;AR,Argentina;AS,American Samoa;AT,Austria;AU,Australia;AW,Aruba;AZ,Azerbaijan;BA,Bosnia and Herzegovina;BB,Barbados;BD,Bangladesh;BE,Belgium;BF,Burkina Faso;BG,Bulgaria;BH,Bahrain;BI,Burundi;BJ,Benin;BM,Bermuda;BN,Brunei Darussalam;BO,Bolivia;BR,Brazil;BS,Bahama;BT,Bhutan;BU,Burma (no longer exists);BV,Bouvet Island;BW,Botswana;BY,Belarus;BZ,Belize;CA,Canada;CC,Cocos (Keeling) Islands;CF,Central African Republic;CG,Congo;CH,Switzerland;CI,Côte D'ivoire (Ivory Coast);CK,Cook Iislands;CL,Chile;CM,Cameroon;CN,China;CO,Colombia;CR,Costa Rica;CS,Czechoslovakia (no longer exists);CU,Cuba;CV,Cape Verde;CX,Christmas Island;CY,Cyprus;CZ,Czech Republic;DD,German Democratic Republic (no longer exists);DE,Germany;DJ,Djibouti;DK,Denmark;DM,Dominica;DO,Dominican Republic;DZ,Algeria;EC,Ecuador;EE,Estonia;EG,Egypt;EH,Western Sahara;ER,Eritrea;ES,Spain;ET,Ethiopia;FI,Finland;FJ,Fiji;FK,Falkland Islands (Malvinas);FM,Micronesia;FO,Faroe Islands;FR,France;FX,France, Metropolitan;GA,Gabon;GB,United Kingdom (Great Britain);GD,Grenada;GE,Georgia;GF,French Guiana;GH,Ghana;GI,Gibraltar;GL,Greenland;GM,Gambia;GN,Guinea;GP,Guadeloupe;GQ,Equatorial Guinea;GR,Greece;GS,South Georgia and the South Sandwich Islands;GT,Guatemala;GU,Guam;GW,Guinea-Bissau;GY,Guyana;HK,Hong Kong;HM,Heard & McDonald Islands;HN,Honduras;HR,Croatia;HT,Haiti;HU,Hungary;ID,Indonesia;IE,Ireland;IL,Israel;IN,India;IO,British Indian Ocean Territory;IQ,Iraq;IR,Islamic Republic of Iran;IS,Iceland;IT,Italy;JM,Jamaica;JO,Jordan;JP,Japan;KE,Kenya;KG,Kyrgyzstan;KH,Cambodia;KI,Kiribati;KM,Comoros;KN,St. Kitts and Nevis;KP,Korea, Democratic People's Republic of;KR,Korea, Republic of;KW,Kuwait;KY,Cayman Islands;KZ,Kazakhstan;LA,Lao People's Democratic Republic;LB,Lebanon;LC,Saint Lucia;LI,Liechtenstein;LK,Sri Lanka;LR,Liberia;LS,Lesotho;LT,Lithuania;LU,Luxembourg;LV,Latvia;LY,Libyan Arab Jamahiriya;MA,Morocco;MC,Monaco;MD,Moldova, Republic of;MG,Madagascar;MH,Marshall Islands;ML,Mali;MN,Mongolia;MM,Myanmar;MO,Macau;MP,Northern Mariana Islands;MQ,Martinique;MR,Mauritania;MS,Monserrat;MT,Malta;MU,Mauritius;MV,Maldives;MW,Malawi;MX,Mexico;MY,Malaysia;MZ,Mozambique;NA,Namibia;NC,New Caledonia;NE,Niger;NF,Norfolk Island;NG,Nigeria;NI,Nicaragua;NL,Netherlands;NO,Norway;NP,Nepal;NR,Nauru;NT,Neutral Zone (no longer exists);NU,Niue;NZ,New Zealand;OM,Oman;PA,Panama;PE,Peru;PF,French Polynesia;PG,Papua New Guinea;PH,Philippines;PK,Pakistan;PL,Poland;PM,St. Pierre & Miquelon;PN,Pitcairn;PR,Puerto Rico;PT,Portugal;PW,Palau;PY,Paraguay;QA,Qatar;RE,Réunion;RO,Romania;RU,Russian Federation;RW,Rwanda;SA,Saudi Arabia;SB,Solomon Islands;SC,Seychelles;SD,Sudan;SE,Sweden;SG,Singapore;SH,St. Helena;SI,Slovenia;SJ,Svalbard & Jan Mayen Islands;SK,Slovakia;SL,Sierra Leone;SM,San Marino;SN,Senegal;SO,Somalia;SR,Suriname;ST,Sao Tome & Principe;SU,Union of Soviet Socialist Republics (no longer exists);SV,El Salvador;SY,Syrian Arab Republic;SZ,Swaziland;TC,Turks & Caicos Islands;TD,Chad;TF,French Southern Territories;TG,Togo;TH,Thailand;TJ,Tajikistan;TK,Tokelau;TM,Turkmenistan;TN,Tunisia;TO,Tonga;TP,East Timor;TR,Turkey;TT,Trinidad & Tobago;TV,Tuvalu;TW,Taiwan, Province of China;TZ,Tanzania, United Republic of;UA,Ukraine;UG,Uganda;UM,United States Minor Outlying Islands;US,United States of America;UY,Uruguay;UZ,Uzbekistan;VA,Vatican City State (Holy See);VC,St. Vincent & the Grenadines;VE,Venezuela;VG,British Virgin Islands;VI,United States Virgin Islands;VN,Viet Nam;VU,Vanuatu;WF,Wallis & Futuna Islands;WS,Samoa;YD,Democratic Yemen (no longer exists);YE,Yemen;YT,Mayotte;YU,Yugoslavia;ZA,South Africa;ZM,Zambia;ZR,Zaire;ZW,Zimbabwe;ZZ,Unknown or unspecified country
EOF;
if ($db = new SQLiteDatabase('test.db')) {
$q = @$db->query('SELECT * FROM countries LIMIT 0,1;');
if ($q === false) {
$l = split(';', $list);
$db->queryExec('CREATE TABLE countries (id int, code char, name char, PRIMARY KEY (id));');
foreach ($l as $k => $v) {
$tmp = split(',', $v);
$db->queryExec(sprintf('INSERT INTO countries VALUES (%s, "%s", "%s");', $k, $tmp[0], $tmp[1]));
}
}
} else {
die($err);
}
define('_HYGRID_TABLE_', 'countries');
define('_HYGRID_ALLOWED_TABLES', 'countries');
class hygrid
{
static $page, $rpp, $rows;
function __construct()
{
$this->total = 0;
$this->table = $_REQUEST['table'] ? $_REQUEST['table'] : _HYGRID_TABLE_;
$this->page = $_REQUEST['page'] ? $_REQUEST['page'] : 1;
$this->rpp = $_REQUEST['rpp'] ? $_REQUEST['rpp'] : false;
// total
}
<?php
/* The SQLite extension is enabled by default as of PHP 5.
* Before that time the SQLite library is needed.
*
* For more info on setting up SQLite See:
* http://www.php.net/manual/en/sqlite.installation.php
*
*/
if ($db = new SQLiteDatabase('imgorg.db')) {
// Albums
// $db->queryExec('DROP TABLE Albums');
$db->queryExec('CREATE TABLE Albums (
id INTEGER PRIMARY KEY,
text STRING,
created STRING,
description TEXT
);
INSERT INTO Albums (text) VALUES ("Test");
INSERT INTO Albums (text) VALUES ("Album2");');
// Albums_Images
// $db->queryExec('DROP TABLE Albums_Images');
$db->queryExec('CREATE TABLE Albums_Images (
album_id INTEGER,
image_id INTEGER
)');
// Images_Tags
$db->queryExec('CREATE TABLE Images_Tags (
tag_id INTEGER,
image_id INTEGER
)');
// Tags
@TODO: $DBH = new PDO("sqlite:my/database/path/database.db");
@TODO:
@TODO: под одну запись on duplicate key update
@TODO: , PRIMARY KEY (id), UNIQUE (device_id, datetime) ON CONFLICT IGNORE
mikrotik script
=================
:local sysnumber [/system routerboard get value-name=serial-number]
:local txbyte [/interface ethernet get ether1-gateway value-name=driver-tx-byte]
:local rxbyte [/interface ethernet get ether1-gateway value-name=driver-rx-byte]
/tool fetch url=("http://example.com/tikstat/collector.php\?sn=$sysnumber&tx=$txbyte&rx=$rxbyte") mode=http keep-result=no
=================
*/
if ($db = new SQLiteDatabase('tikstat.sqlite.db')) {
// base for devices
$result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='devices'");
if ($result->numRows() == 0) {
$db->queryExec('CREATE TABLE devices (id INTEGER PRIMARY KEY, sn text, comment text, last_check int, last_tx int, last_rx int)');
}
// base for detalized traffic
$result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='traffic'");
if ($result->numRows() == 0) {
$db->queryExec('CREATE TABLE traffic (id INTEGER PRIMARY KEY, device_id int, datetime int, tx int, rx int)');
$db->queryExec('CREATE UNIQUE INDEX IF NOT EXISTS UniqueIndex ON traffic (device_id,datetime)');
}
// base for summary traffic
} else {
echo "Ошибка работы с БД.";
exit;
}
.content {
width: 100%;
text-align: center;
}
-->
</style>
</head>
<body>
<?php
try {
//create or open the database
$con = new SQLiteDatabase('./database/urls.db', 0666, $error);
} catch (Exception $e) {
echo "Something went wrong. The ./database directory does not exists or has the wrong permissions. It should be writeable by the webserver. <br> Refresh this page when you've fixed the problem.<h2>ERROR:</h2>";
die($error);
}
$sql = "delete from URLS;";
if (!$con->queryExec($sql, $error)) {
echo "Something went wrong. Maybe you're already completed setup? If not, try deleting database/urls.db<br><h2>ERROR:</h2>";
die($error);
}
echo "<strong>SUCCESS!</strong> The URL database has been cleared. Hope you really meant to do that! <p><a href=./url_list.php>Click here to continue</a></h2>";
?>
</body>
</html>
text-align: center;
}
-->
</style>
</head>
<body>
<div class=header><img src=images/logo.png><br><br><strong>Setup</strong></div>
<?php
try {
//create or open the database
$con = new SQLiteDatabase('./database/urls.db', 0666, $error);
} catch (Exception $e) {
echo "Something went wrong. The ./database directory does not exists or has the wrong permissions. It should be writeable by the webserver. <br> Refresh this page when you've fixed the problem.<h2>ERROR:</h2>";
die($error);
}
$query = ' CREATE TABLE urls (id INTEGER UNIQUE, url TEXT, short TEXT UNIQUE, date INTEGER); ';
if (!$con->queryExec($query, $error)) {
echo "Something went wrong. Maybe you're already completed setup? If not, try deleting database/urls.db<br><h2>ERROR:</h2>";
die($error);
}
echo "<strong>SUCCESS!</strong> Database created, you should be good to go.<br>See config.php to customize your installation. <h2><a href=./>Click here to continue</a></h2>";
?>
</body>
</html>
function remove($data)
{
$db = new SQLiteDatabase('sql/imgorg.db');
$q = $db->queryExec('DELETE FROM Albums where id ="' . $data->album . '"');
return array(success => true, album => $data->album);
}
echo 'connected. </br>';
/* //add Movie table to database
$query = 'CREATE TABLE Movies ' .
'(Title TEXT, Director TEXT, Year INTEGER)';
if(!$database->queryExec($query, $error))
{
die($error);
}
*/
//insert data into database
$a = 'Title';
$b = 'Director';
$c = 'Year';
$query = "INSERT INTO Movies ('{$a}', '{$b}', '{$c}') " . 'VALUES ("Interstella 5555", "Daft Punk", 2003); ';
if (!$database->queryExec($query, $error)) {
die($error);
}
function select($database)
{
//read data from database
$query = "SELECT * FROM Movies";
$tit = 'Title';
if ($result = $database->query($query, SQLITE_BOTH, $error)) {
while ($row = $result->fetch()) {
print "Title: {$row[$tit]} <br />" . "Director: {$row['Director']} <br />" . "Year: {$row['Year']} <br /><br />";
}
} else {
die($error);
}
}
<?php
$fname = 'direcciones.sqlite';
@unlink($fname);
$db = new SQLiteDatabase($fname);
$sql = "CREATE TABLE emails (\n id INTEGER PRIMARY KEY,\n nombre STRING NOT NULL,\n correo STRING NOT NULL\n )";
$db->queryExec($sql);
$db->queryExec("INSERT INTO emails (nombre, correo) " . "VALUES ('Atila T. Hun', '*****@*****.**')");
$db->queryExec("INSERT INTO emails (nombre, correo) " . "VALUES ('Hermes Trismegisto', '*****@*****.**')");
$db->queryExec("INSERT INTO emails (nombre, correo) " . "VALUES ('A. Rosenkratz', '*****@*****.**')");
$rows = $db->arrayQuery("SELECT DISTINCT nombre,correo FROM emails");
foreach ($rows as $row) {
echo "- {$row['nombre']}, {$row['correo']}\n";
}
?>
<?php
//error_reporting(6143);
$hits = 0;
if ($db = new SQLiteDatabase('/home/a3253051/db/hitcounter.db')) {
$q = @$db->query("SELECT counter FROM rsshits WHERE url = '" . $_GET['url'] . "'");
if ($q === false) {
$db->queryExec('CREATE TABLE rsshits (url TEXT, counter int)');
$hits = 1;
} else {
$result = $q->fetchSingle();
$hits = intval($result) + 1;
$db->queryExec("INSERT INTO rsshits VALUES ('" . $_GET['url'] . "'," . $hits . ")");
$db->queryExec("UPDATE rsshits SET counter=" . $hits . " WHERE url='" . $_GET['url'] . "'");
}
} else {
die($err);
}
?>
<?
header("Location: ".$_GET['url']);
//exit();
?>
/**
* Get log db
*
* @return SQLiteDatabase
*/
public function getPageLogDb()
{
$file = _BEAR_APP_HOME . '/logs/pagelog.sq3';
if (file_exists($file) === false) {
$db = new SQLiteDatabase($file);
$sql = <<<____SQL
CREATE TABLE pagelog (
\t "log" text NOT NULL
);
____SQL;
$db->queryExec($sql);
} else {
$db = new SQLiteDatabase($file);
}
if ($db === false) {
throw new BEAR_Exception('sqlite error');
}
return $db;
}
<?php
$database = new SQLiteDatabase('c:/copy/library.sqlite');
$sql = "CREATE TABLE 'books' ('bookid' INTEGER PRIMARY KEY, \r\n\t\t'authorid' INTEGER,\r\n\t\t'title' TEXT,\r\n\t\t'ISBN' TEXT,\r\n\t\t'pub_year' INTEGER,\r\n\t\t'available' INTEGER)";
if ($database->queryExec($sql, $error) == FALSE) {
echo "Create Failure - {$error} <br />";
} else {
echo "Table Books was created <br />";
}
$sql = <<<SQL
INSERT INTO books ('authorid', 'title', 'ISBN', 'pub_year', 'available')
VALUES (1, 'The Two Towers', '0-261-10236-2', 1954, 1);
INSERT INTO books ('authorid', 'title', 'ISBN', 'pub_year', 'available')
VALUES (1, 'The Return of The King', '0-261-10237-0', 1955, 1);
INSERT INTO books ('authorid', 'title', 'ISBN', 'pub_year', 'available')
VALUES (2, 'Roots', '0-440-17464-3', 1974, 1);
\t\t
INSERT INTO books ('authorid', 'title', 'ISBN', 'pub_year', 'available')
VALUES (4, 'I, Robot', '0-553-29438-5', 1950, 1);
\t\t\t\t
INSERT INTO books ('authorid', 'title', 'ISBN', 'pub_year', 'available')
VALUES (4, 'Foundation', '0-553-80371-9', 1951, 1);
SQL;
if ($database->queryExec($sql, $error) == FALSE) {
echo "Insert Failure - {$error} <br />";
} else {
echo "INSERT to Books - OK <br />";
}
<?php
if ($db = new SQLiteDatabase($smartysh->config["path_db"])) {
// create tables if these do not exist
$q = @$db->query('SELECT id FROM access_log WHERE id = 1');
if ($q === false) {
$db->queryExec("\n CREATE TABLE access_log (id int,\n site_dir varchar(255) NOT NULL,\n url varchar(255) NOT NULL,\n date datetime NOT NULL,\n PRIMARY KEY (id)\n )");
}
} else {
die($err);
}
sqlite_close($dbhandle);
if ($db = sqlite_open('mysqlitedb', 0666, $sqliteerror)) {
sqlite_query($db, 'CREATE TABLE foo (bar varchar(10))');
sqlite_query($db, "INSERT INTO foo VALUES ('fnord')");
$result = sqlite_query($db, 'select bar from foo');
var_dump(sqlite_fetch_array($result));
} else {
die($sqliteerror);
}
$db = sqlite_open('mysqlitedb');
$result = sqlite_query($db, "SELECT * FROM mytable WHERE name='John Doe'");
$rows = sqlite_num_rows($result);
echo "Number of rows: {$rows}";
$db = new SQLiteDatabase('mysqlitedb');
$result = $db->query("SELECT * FROM mytable WHERE name='John Doe'");
$rows = $result->numRows();
echo "Number of rows: {$rows}";
$dbhandle = sqlite_open('mysqlitedb');
$query = sqlite_exec($dbhandle, "UPDATE users SET email='*****@*****.**' WHERE username='******'", $error);
if (!$query) {
exit("Error in query: '{$error}'");
} else {
echo 'Number of rows modified: ', sqlite_changes($dbhandle);
}
$dbhandle = new SQLiteDatabase('mysqlitedb');
$query = $dbhandle->queryExec("UPDATE users SET email='*****@*****.**' WHERE username='******'", $error);
if (!$query) {
exit("Error in query: '{$error}'");
} else {
echo 'Number of rows modified: ', $dbhandle->changes();
}
/**
* Removes all expired items from the cache store
*
* @return boolean true on success, false on failure
**/
public function flushExpired()
{
$this->Logger->debug('Flush all expired called.');
return $this->db->queryExec("DELETE FROM cache WHERE expire < datetime('now')");
}
