/** * @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')"); }